sql cookbook 笔录（一）

备注：常用经典sql

1.SELECT a.name,SUM(a.money) FROM `a`GROUP by a.name ； 当没有group by字句时，只会显示一条记录，

只有第一条a.name 会被显示出来。

2.只要一个地方可以出现一个字段，那么case字句就可以使用。

select e.deptnpo,e.sal,e.sal*case when eb.type=1 then 0.1 when eb.type=2 then 0.2 else 0.3 end as bonus

from emp e,emp_bonus eb where e.deptno=eb.deptno 

红线部分就是整个表达式作为一个bonus列。

1.6 where子句中引用取别名的列

在where子句中是不能引用select中的列别名。selectsal as salary from emp wheresalary <5000 

这句sql是错的。因为where在select之前执行，所以在where字句执行的时候，salary并不存在。要想在where子句中使用别名列

可以将别名列用作内联视图。

select * from(select sal salary from emp )x where salary <5000

(ps 必须为内联视图取名字，才可以正常工作，如果不加表名x，会报错)

1.7 连接多个列值

如果想把多个列的值，作为一列返回，可以使用concat，或者||，+运算符

DB2，oracle  使用|| 连接2个字段

select ename || 'work as' || job from emp

mySql 使用concat函数

select  concat(ename,'work as',job) from emp

1.8 select 语句中使用，条件逻辑

case可以根据查询结果值，执行条件逻辑。可以给case表达式取别名，如果结果不满足case表达式，会返回null

select ename,sal,case when sal<200 then 'undep' when sal>400 then 'overdep' else 'OK' end as status from emp 

1.10 从表中随机返回n条记录

要求每次执行下边的语句时，返回不同的结果，select name,job from emp

原理使用内置的随机函数，然后再order中使用，配合limit

Mysql 解决方案

select name,job from emporder by rand() limit 5 返回前5条   order 不一定非要按照表中的字段排序，可以使用随机函数。

1.12 将空值转换为实际值 

coalesce函数  这个函数可以有一个或多个参数，返回第一个不为空的值。

select coalesce(comm,0) from emp  如果comm为null的话，会返回0，对所有的数据库都适用。

3.4 从一个表中查找另外一个表没有的值

  1.在mysql和sqlserver中要用not in查询。select deptno from dept where deptnonot in (select deptno from emp);
        在oracle中集合减法  可以使用minus。select deptno from dept minus select deptno from emp;
在DB2中 使用 except 完成类似的操作。
      2.使用not in的时候，要注意子查询中有null的情况，示例如下。
先看一个简单查询，SELECT * FROM `a` WHERE  NOT null  结果中什么都没有，可知not null  返回false。

select deptno from dept where deptno not in(10, 50,null)   这个查询 当dept表中的deptno是40的时候，也不会输出，

因为not in相当于or操作，上面的查询等价于select deptno from dept where not(deptno=10 or deptno=50 or deptno=null) 

前二个都是or返回false，deptno=null 返回null， false or null  返回 null， not null 返回false，所以就算40没在子查询里面，

也不会有结果。所以使用谓词in或者or的时候，一定要注意null的问题。

要解决这个问题可以使用not exists。

select deptno from dept d where not exists(select null fromemp e where d.deptno=e.deptno),因为并不需要查询什么字段，

直接查询一个null就好了。

3.5 向查询中增加联接而不影响其他联接

1.使用左外链接 不至于使得原来的查询结果变少。

2.标量子查询来模仿外联接。select e.name,d.loc,(select eb.received from eb_bonus eb where eb.empno=e.empno)

          as receivedfrom emp e,dept d where e.deptno=d.deptno order by 2; 

红线标记子查询 相当于一个查询列，这种方式可在所有平台上运行，但是对于emp表中的一个记录，eb.empno=e.empno要

只返回一条数据，否则会报错，相当于一条记录，这个列有多个值。

3.8 识别和消除笛卡尔乘积

一般来说要消除笛卡尔乘积要使用n-1规则，也就是from 子句中有n个表，n-1是要避免笛卡尔乘积的最小联接数。很多查询中也有用到笛卡尔乘积。（反向转置，产生顺序值）

3.9 聚集和联接

考虑一组数据， a表  员工id和薪水

idsal120220

b表   员工id和奖金率  所得奖金等于sal*rate 

idrate10.110.220.2

我们要统计员工的总基本工资 和所有员工的奖金，sum(sal)=40  sum(奖金)=10

select sum(sal),sum(bonus) from (select a.sal,a.sal * b.rate as bonus from a,b where a.id=b.id )  这样的话，因为b表中id为1的用户有2条奖金记录，所以1的

sal也被计算了二次。计算出来的sum（sal）=60，书上给出了一种使用distinct解决方案，不计算重复的sal，即sum(distinct sal)这个方案是有问题的，就比如

我的a表记录，id=2的用户sal也不会被计算。

还有一种方案就是：在连接之前，新进行表的聚集操作（在内联视图中），因为在联接操作时，聚集运算已经完成了。

我自己写的一个例子

SELECT b.name,c.total,sum(a.money*b.rate) FROM a,b,(SELECT name,sum(money) as total FROM a ) c WHERE a.name=b.name红字里面其实只有一条数据也就是total，这一条数

据和a，b二个表进行联接，就相当于a，b联接后，再加一列，这个列的值都是total。这样就可以完成要求了。

第四章 插入更新删除

4.1 

1.insert into dept values(10,'tt','aa'); 如果没有写出要插入的列名，要把所有的列按顺序在values里都写出来。

4.2 插入默认值 

1.insert into dept values()  values里面不写值，就代表，每个列插入默认值。

2.insert into dept (name) values('es') 没有列出的列 ，插入时都是设置成默认值

4.4 从一个表向另外的表复制行

1.insert to dept_new (no,name,loc) selectno,name,loc from dept where 条件   把查询到的结果插入到新的表

4.5 复制表的定义

1.mysql   create table dept_2as select * from dept where 1=0;   1=0   是为了保证新建的表是一个空表，否则的话，查询到的数据会插入到新表里面。

4.8 更新表

  1.update dept set sal=sal*1.1  where ...  

4.10 用其他表中的值更新

1.update emp e set(sal,comm)=(selectns.sal,ns.sal/2 from new_sal ns where ns.deptno=e.deptno)where exists(select null from new_sal ns where ns.deptno=e.deptno)

这个sql的意思是，用new_sql表中的sal更新emp表中的sal，但是只有在new_sal中有值的才进行更新。 最后一个where子查询返回确定有哪些emp中的行被更新。

第一where字句（set之后的），是查询更新后的值。

sqlserver和oracle中有其他的方法。

sql中 updte可以连接表，所以  update e set e.sal=ns.sal,e.comm=ns.sal/2 from emp e,new_sal ns where e.deptno=ns.deptno;

4.11 合并表中的记录

1.根据表中记录的存在情况，如果有就对这个记录更新，如果没有，就插入这条记录。现在只有oracle可以满足这种情况，使用merge关键字。

4.12 删除表的所有记录

1.delete from dept 。不跟条件删除所有记录。

2.delete from dept where deptno=10   根据where条件删除记录。

4.15 删除违反参照完整性的记录

1.从一个表中删除那些记录，他们在引用其他表中 不存在的记录。比如员工被分配到不存在的部门，就把他们删除。

用谓词not  exists来判断是否满足条件。delete from emp where not exists (select null from dept where dept.deptno=emp.deptno)

也可以使用谓词not in。

4.16 删除重复记录

1.删除具有重复姓名的组。只保留其中的一个。思路很多。这里使用min函数，保留id最小的记录。

delete from emp where deptno not in(select  min(deptno)  from emp group by name )

4.17 删除从其他表引用的记录

1.删除发生了三次事故以上的，所有部门的员工。

delete form emp where deptno in (select deptno from accidents group by deptno having count(*)>=3)

5.1  列出模式中的表

1.MySql 

    SHOW DATABASES                                //列出 MySQL Server 数据库。

SHOW TABLES [FROM db_name]                    //列出数据库数据表。

SHOW CREATE TABLES tbl_name                    //导出数据表结构。

SHOW TABLE STATUS [FROM db_name]              //列出数据表及表状态信息。

SHOW COLUMNS FROM tbl_name [FROM db_name]     //列出资料表字段

SHOW FIELDS FROM tbl_name [FROM db_name]，DESCRIBE tbl_name [col_name]。

SHOW FULL COLUMNS FROM tbl_name [FROM db_name]//列出字段及详情

SHOW FULL FIELDS FROM tbl_name [FROM db_name] //列出字段完整属性

SHOW INDEX FROM tbl_name [FROM db_name]       //列出表索引。

SHOW STATUS                                  //列出 DB Server 状态。

SHOW VARIABLES                               //列出 MySQL 系统环境变量。

SHOW PROCESSLIST                             //列出执行命令。

SHOW GRANTS FOR user                         //列出某用户权限

5.2 列出表中的列

MySql

show columns from table_name

5.3 列出表中的索引列

show index from table_name

6. 使用字符串

6.1 遍历字符串

1.把字符串的每一个字符当做一个行输出，例如 name字段是java，则输出四行，j，a，v，a

        要解决这个问题，就要使用一个辅助表，进行笛卡尔乘积来输出行号。辅助表为t10,t10只包含一个字段，id

id的值，从一到十。这个id值要能保证容纳下字符串的每个字符，也就是要大于字符串的长度。

先看这个查询

selectname,pos from (SELECT userNameas name from t_user WHERE userName='java')e,

   (SELECT id as pos FROM t10)iter WHERE iter.pos<=length(e.name)

分析：二个内联视图e和iter进行笛卡尔乘积，e表只有一行，iter表有十行，所以进行笛卡尔乘积有十行，足够容纳

每个字符。这个查询的输出结果是

name   pos

java       1

java       2

java       3

java       4

然后对这每行的java进行处理就好了。

select substr(e.name,pos,1),pos from (SELECT userName as name from t_user WHERE userName='java1234') e,

(SELECT id as pos FROM t10) iter WHERE iter.pos<=length(e.name)

一定要把这种原理搞清楚。

6.2 字符串中包含引号

要输出g‘ day 输出g后边的引号，select 'g'' day' 这样就可以了。

注意：sql中的引号，必须成对出现，就算你只想要一个引号。

   select ''''  会得到 一个 '    最外边的二个引号定义了一个字符串，里面的二个引号，输出一个引号。

6.3 计算字符在字符串中出现的次数

思路，用字符串的原长度减去 替换掉要计算的字符之后的长度。把要查找的字符替换为空。

比如要计算在'HELLO　HELLO' 中LL出现的次数

select (length('HELLO　HELLO') - length(replace('HELLO　HELLO','LL','')))/length('LL')  

一定要除以LL的长度，才知道出现了多少次，否则计算出来的只是被替换掉的字符个数。

6.4 从字符串中删除所有不需要的字符

1.思路   把所有要删除的字符，替换为一个字符，然后，删除这个字符。

语法：TRANSLATE(expr,from,to)expr: 代表一串字符，from 与 to 是从左到右一一对应的关系，如果不能对应，则视为空值。

比如：SELECT TRANSLATE('abcdefghij','abcdefghij','123456') FROM dual;  结果   123456

MySql和SqlServer不支持这个函数，只能一个一个删除。

6.5 将字符和数字分离

思路同上，也是使用translate函数将多个字符，转化为一个字符。

6.6 判断字符串是不是只有数字和字母组成

1.使用translate函数把所有的数字字母替换成一个单一字母，然后判断是不是所有的字母都被替换掉了。

select data from V where translate(lower(data), '123456789a-z',repeat('a',36))=repeat('a',length(data))，因为

总长度是不变的，所以如果是纯字母和数字，判断相等就好了。

2.在MySql中  可以使用正则表达式

select data from V where data regexp'[^0-9a-zA-Z]' =0    返回全是字母和数字的。

6.8 按字符串部分内容排序

比如按照姓名的最后二个字符排序。在orderby子句中 使用函数substr

select ename from emp order by  substr(ename,length(ename)-1,2)  提取出最后二个字符。

6.9 按字符串中的数值进行排序

考虑下面的数据，

data

CLARK 7782 ACCOUNTING

要按照其中的7782来排序。

步骤有点多，一步一步来：

1.将data中的数字0-9 全部替换为一个字符，data中没有出现过的字符，比如’#‘，得到CLARK #### ACCOUNTING 

2.删掉第一步中的#  得到 CLARK ACCOUNTING

3.将第二步中，得到的字符串，传递给translate函数，data为源字符串，作为form_str,to_str 设置成和字符串一样长的# ,意思就是把

data上面的出现的字符，全部替换为#，data字符串变为 #### 7782 ########

4.然后再删除第三步中的#号，就得到了7782.

5.把上面的函数写到orderby函数里面。

6.10 根据表中的行，创建一个分割列表

考虑数据

deptnoemps

10clerk

10king

10miller

要求返回数据

deptnoemps

10clerk,king,miller

1.MySql使用GROUP_CONCAT函数  相关内容可以参考：http://blog.sina.com.cn/s/blog_4e808acf01009qna.html

 select deptno,group_concat(empsorder by empnoseparator ',') as emps from emp group by deptno

group_concat 函数可以连接组中的内容，可以按照order by指定的顺序连接，可以自定义分隔符

6.11 将分割数据转换为多值IN列表

1.已经有了分割列表数据，想把它转换为where子句中的IN列表的项目。

MySql借助substring_index函数，

P138，页。

6.12  按字母顺序排列字符串

1.考虑如下结果

ename

----------

adams

allen

我们要得到如下结果：

aadms

aelln

MySql解决方案

使用group_concat函数，对每个字符，按照顺序排列。先遍历字符串，得到每个字符。

select ename,group_concat(c order by c sepatator '') from 
(select ename,substr(a.ename,iter.pos,1) c from emp) a,((select id pos
from t10) iter where iter.pos<=length(a.ename)) x 
group by ename

中间表连接的结果是：

enamec

---------

adamsa

    adamsd

adamsa

adamsm

adamss

通过group_concat按照顺序连接 可得到 aadms。

6.13 判别可作为数值的字符串

比一个字符串'99GREAK78'  返回9978

第一步：把这个字符串的每个字符分离出来得到9,9，G，R，E，A，K，7,8

第二步：保留纯数字得到 9,9,7,8  这一步可以使用 ascii()函数  判断值是不是在48，57

第三步：把结果连起来   通过group_concat 把他们连起来

sql  P151。

6.15 分解ip地址

1.把 192.111.0.2  分解到4个字段   192   111   0  2

关键是ip之中每个 .  的数据，分离出来

MySql使用内置的substring_index函数就可以了。

select substring_index( substring_index(y.ip, '.' ,1), '.' ,-1) a

substring_index( substring_index(y.ip, '.' ,2), '.' ,-1) a

substring_index( substring_index(y.ip, '.' ,3), '.' ,-1) a

substring_index( substring_index(y.ip, '.' ,4), '.' ,-1) a

from (select '192.111.0.2' as ip  from t1) y