SQL开发技巧

一、如何正确使用join语句。

join包括内连接（inner）、全外连接（full outer）、左外连接（left outer）、右外连接（right outer）和交叉连接（cross）。

1、SQL本身不支持全外连接，SELECT XXX FROM TABLE a FULL OUTER JOIN TABLE b ON a.key = b.key; 会报错，错误码1064。

如何解决？全外连接是左、右外连接的合集，

SELECT a.XXX FROM TABLE a LEFT JOIN TABLE b  ON a.key = b.key

UNION ALL

SELECT bb.XXX FROM TABLE a RIGHT JOIN TABLE b  ON a.key = b.key;

2、交叉连接又称笛卡尔连接、叉乘，即A*B，A中没每一条记录都会查询出B中所有记录；如果A中有3条记录，B中有5条记录，那么结果有15条记录。

3、不能更新FROM从句中出现的表，否则报错，错误码1093。

UPDATE table1 set name=‘jack’ WHERE table1.ID in (SELECT table2.ID FROMtable1 a join table2 b on a.ID=b.ID);

同时出现报错，若要查询需要改为：

UPDATE table1 a join (SELECT table2.ID FROM table1 a join table2 b on a.ID=b.ID) bon a.ID=b.ID set name=‘jack’ ;

4、使用join优化查询

select a.ID, a.name, (select name from table2 b where a.ID=b.ID)as name2 from table1 a;

该查询语句对于每一条查询都会对table2对应遍历查询，耗时较长，可以使用左外连接进行优化。

select a.ID, a.name, b.name from table1 a left join table2 b on a.ID=b.ID;

5、使用join优化聚合子查询

select a.ID, b.time, b.num from table1 a join table2 b on  a.ID=b.IDwhereb.num=(select max(c.num) from table2 c where c.ID=b.ID);

绿色得出两个表的交集，红色得出table2中数量最大的结果。最后得出每个ID数量最大对应的时间。

避免子查询造成的时间消耗：

select a.ID, b.time, b.num from table1 a 

join table2 b on  a.ID=b.ID 查询出两个表的交集

join table2 c on c.ID=b.ID纯属为了场景过滤

group by a.ID, b.time, b.num 分组排序

having b.num = max(c.num); 场景过滤

mysql> select a.id,b.time,b.num from table1 a join table2 b on a.id=b.id;

+----+----------+------+

| id | time     | num  |

+----+----------+------+

|  1 | 20110101 |    5 |

|  1 | 20110101 |    4 |

|  2 | 20130101 |    5 |

|  2 | 20130101 |   15 |

+----+----------+------+

4 rows in set (0.00 sec)

mysql> select a.id,b.time,b.num from table1 a join table2 b on a.id=b.id join table2 c on c.id=b.id;

+----+----------+------+

| id | time     | num  |

+----+----------+------+

|  1 | 20110101 |    5 |

|  1 | 20110101 |    4 |

|  1 | 20110101 |    5 |

|  1 | 20110101 |    4 |

|  2 | 20130101 |    5 |

|  2 | 20130101 |   15 |

|  2 | 20130101 |    5 |

|  2 | 20130101 |   15 |

+----+----------+------+

8 rows in set (0.01 sec)

mysql> select a.id,b.time,b.num from table1 a join table2 b on a.id=b.id join table2 c on c.id=b.id group by a.id,b.time,b.num;

+----+----------+------+

| id | time     | num  |

+----+----------+------+

|  1 | 20110101 |    4 |

|  1 | 20110101 |    5 |

|  2 | 20130101 |    5 |

|  2 | 20130101 |   15 |

+----+----------+------+

4 rows in set (0.01 sec)

mysql> select a.id,b.time,b.num from table1 a join table2 b on a.id=b.id join table2 c on c.id=b.id group by a.id,b.time,b.num having b.num=max(c.num);

+----+----------+------+

| id | time     | num  |

+----+----------+------+

|  1 | 20110101 |    5 |

|  2 | 20130101 |   15 |

+----+----------+------+

2 rows in set (0.00 sec)

二、如何进行行列转换

1、进行行列转换

1、利用交叉连接进行行列转换

mysql> select * from score;

+------+---------+-------+

| name | course  | score |

+------+---------+-------+

| jack | math    |    88 |

| john | math    |    88 |

| john | English |    77 |

| john | Chinese |    66 |

| jack | English |    87 |

| jack | Chinese |    57 |

+------+---------+-------+

6 rows in set (0.00 sec)

mysql> select a.name,sum(score) from score a group by a.name;

+------+------------+

| name | sum(score) |

+------+------------+

| jack |        232 |

| john |        231 |

+------+------------+

2 rows in set (0.01 sec)

mysql> select sum(score) as 'jack' from score a where a.name='jack';

+------+

| jack |

+------+

|  232 |

+------+

1 row in set (0.00 sec)

mysql> select * from (select sum(score) as 'jack' from score a where a.name='jack') aacross join (select sum(score) as 'john' from score b where b.name='john')bb;

+------+------+

| jack | john |

+------+------+

|  232 |  231 |

+------+------+

1 row in set (0.00 sec)

mysql> 

2、利用case语句进行行列转换

mysql> select sum(case when name='jack' then score end) as 'jack' from score;

+------+

| jack |

+------+

|  232 |

+------+

1 row in set (0.01 sec)

mysql> select sum(case when name='jack' then score end) as 'jack',

    -> sum(case when name='john' then score end) as 'john'

    -> from score;

+------+------+

| jack | john |

+------+------+

|  232 |  231 |

+------+------+

1 row in set (0.00 sec)

mysql> 

2、进行列行转换

1、利用序列表

mysql> select * from table1;                                                   

| id | name | mobile              |

+----+------+---------------------+

|  1 | jack | 3333,4444,5555      |

|  2 | john | 1111,2222           |

|  3 | amry | 6666,7777,8888,9999 |

+----+------+---------------------+

3 rows in set (0.00 sec)

mysql> select name, concat(mobile, ',') as mobile,length(mobile)-length(replace(mobile,',',''))+1 size from table1 b;

+------+----------------------+------+

| name | mobile               | size |

+------+----------------------+------+

| jack | 3333,4444,5555,      |    3 |

| john | 1111,2222,           |    2 |

| amry | 6666,7777,8888,9999, |    4 |

+------+----------------------+------+

3 rows in set (0.00 sec)

concat(mobile, ','): 在后面添加一个逗号

length(mobile): 获取mobile的长度

length(replace(mobile,',','')): 将mobile数据中的逗号替换成''，然后获取其长度，即是没有逗号的长度

mysql> select * from tb_sequence;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

|  4 |

|  5 |

|  6 |

|  7 |

|  8 |

|  9 |

| 10 |

+----+

10 rows in set (0.00 sec)

mysql> select * from table1;

+----+------+---------------------+

| id | name | mobile              |

+----+------+---------------------+

|  1 | jack | 3333,4444,5555      |

|  2 | john | 1111,2222           |

|  3 | amry | 6666,7777,8888,9999 |

+----+------+---------------------+

3 rows in set (0.00 sec)

mysql> from tb_sequence a cross join (select name, concat(mobile, ',') as mobile, length(mobile)-length(replace(mobile, ',',''))+1 size from table1 b) b on a.id <=b.size;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'from tb_sequence a cross join (select name, concat(mobile, ',') as mobile, lengt' at line 1

mysql> select * from tb_sequence a cross join (select name, concat(mobile, ',') as mobile, length(mobile)-length(replace(mobile, ',',''))+1 size from table1 b) b on a.id <=b.size;

+----+------+----------------------+------+

| id | name | mobile               | size |

+----+------+----------------------+------+

|  1 | jack | 3333,4444,5555,      |    3 |

|  2 | jack | 3333,4444,5555,      |    3 |

|  3 | jack | 3333,4444,5555,      |    3 |

|  1 | john | 1111,2222,           |    2 |

|  2 | john | 1111,2222,           |    2 |

|  1 | amry | 6666,7777,8888,9999, |    4 |

|  2 | amry | 6666,7777,8888,9999, |    4 |

|  3 | amry | 6666,7777,8888,9999, |    4 |

|  4 | amry | 6666,7777,8888,9999, |    4 |

+----+------+----------------------+------+

9 rows in set (0.00 sec)

mysql> select name, replace(substring(substring_index(mobile, ',',a.id),char_length(substring_index(mobile,',',a.id-1))+1), ',', '') as mobile from tb_sequence a cross join (select name, concat(mobile, ',') as mobile, length(mobile)-length(replace(mobile, ',',''))+1 size from table1 b) b on a.id <=b.size;

+------+--------+

| name | mobile |

+------+--------+

| jack | 3333   |

| jack | 4444   |

| jack | 5555   |

| john | 1111   |

| john | 2222   |

| amry | 6666   |

| amry | 7777   |

| amry | 8888   |

| amry | 9999   |

+------+--------+

9 rows in set (0.00 sec)

mysql> 

select name,replace(substring(substring_index(mobile, ',',a.id),char_length(substring_index(mobile,',',a.id-1))+1), ',', '')as mobile fromtb_sequence a cross join (select name, concat(mobile, ',') as mobile, length(mobile)-length(replace(mobile, ',',''))+1 size from table1 b) b on a.id <=b.size;

replace(substring(substring_index(mobile, ',',a.id),char_length(substring_index(mobile,',',a.id-1))+1), ',', '')

假如是第7行：|  2 | amry | 6666,7777,8888,9999, |    4 |

mobile是‘6666,7777,8888,9999,’，a.id为2，

substring_index(mobile, ',',a.id) 为'6666,7777'，

substring_index(mobile,',',a.id-1)为‘6666’，

char_length(substring_index(mobile,',',a.id-1))+1 为5，

substring(substring_index(mobile, ',',a.id),char_length(substring_index(mobile,',',a.id-1))+1) 为 ‘,7777’ （如果为第二个参数是6，则为‘7777’）

replace(substring(substring_index(mobile, ',',a.id),char_length(substring_index(mobile,',',a.id-1))+1), ',', '') 为'7777'

2、使用union

先单独求出每一类的列转行，然后将所有类取合集。当类数据较多时，不太适用。

还是序列表比较好

mysql> select * from score2;

+------+------+---------+---------+

| name | math | english | chinese |

+------+------+---------+---------+

| jack |   11 |      22 |      33 |

| john |   44 |      55 |      66 |

| mary |   77 |      88 |      99 |

+------+------+---------+---------+

3 rows in set (0.01 sec)

mysql> SELECT name, 

    -> case when c.id=1 then 'math'

    ->      when c.id=2 then 'english' 

    ->  when c.id=3 then 'chinese'

    ->      end as course,

    ->     coalesce(case when c.id=1 then math end,

    ->     case when c.id=2 then english end,

    ->     case when c.id=3 then chinese end) as score 

    -> from score2 a cross join tb_sequence c where c.id<=3 order by name;

+------+---------+-------+

| name | course  | score |

+------+---------+-------+

| jack | math    |    11 |

| jack | chinese |    33 |

| jack | english |    22 |

| john | english |    55 |

| john | math    |    44 |

| john | chinese |    66 |

| mary | chinese |    99 |

| mary | english |    88 |

| mary | math    |    77 |

+------+---------+-------+

9 rows in set (0.00 sec)

mysql> SELECT name, 

    -> case when c.id=1 then 'math'

    ->      when c.id=2 then 'english' 

    ->  when c.id=3 then 'chinese'

    ->      end as course,

    -> case when c.id=1 then math

    ->      when c.id=2 then english

    ->      when c.id=3 then chinese 

    ->      end as score 

    -> from score2 a cross join tb_sequence c where c.id<=3 order by name;

+------+---------+-------+

| name | course  | score |

+------+---------+-------+

| jack | math    |    11 |

| jack | chinese |    33 |

| jack | english |    22 |

| john | english |    55 |

| john | math    |    44 |

| john | chinese |    66 |

| mary | chinese |    99 |

| mary | english |    88 |

| mary | math    |    77 |

+------+---------+-------+

9 rows in set (0.00 sec)

mysql> 

SELECT name, 

case when c.id=1 then 'math'

     when c.id=2 then 'english' 

     when c.id=3 then 'chinese'

     end as course,

    coalesce(case when c.id=1 then math end,

    case when c.id=2 then english end,

    case when c.id=3 then chinese end) as score 

from score2 a cross join tb_sequence c where c.id<=3 order by name;

SELECT name, 

case when c.id=1 then 'math'

     when c.id=2 then 'english' 

     when c.id=3 then 'chinese'

     end as course,

case when c.id=1 then math

     when c.id=2 then english

     when c.id=3 then chinese 

     end as score 

from score2 a cross join tb_sequence c where c.id<=3 order by name;

coalesce：作用是将返回传入的参数中第一个非null的值

三、如何正确生成序列号

场景：数据库主键

1、优先选择系统自动生成，mysql：auto_increment

缺点：可能产生空洞

mysql> select * from tb_sequence;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

|  4 |

|  5 |

|  6 |

|  7 |

|  8 |

|  9 |

| 10 |

+----+

10 rows in set (0.00 sec)

mysql> begin; //启动事务

Query OK, 0 rows affected (0.00 sec)

mysql> insert into tb_sequence values();

Query OK, 1 row affected (0.00 sec)

mysql> select * from tb_sequence;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

|  4 |

|  5 |

|  6 |

|  7 |

|  8 |

|  9 |

| 10 |

| 11 |

+----+

11 rows in set (0.00 sec)

mysql> rollback;

Query OK, 0 rows affected (0.02 sec)

mysql> select * from tb_sequence;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

|  4 |

|  5 |

|  6 |

|  7 |

|  8 |

|  9 |

| 10 |

+----+

10 rows in set (0.00 sec)

mysql> insert into tb_sequence values();

Query OK, 1 row affected (0.01 sec)

mysql> select * from tb_sequence;

+----+

| id |

+----+

|  1 |

|  2 |

|  3 |

|  4 |

|  5 |

|  6 |

|  7 |

|  8 |

|  9 |

| 10 |

| 12 |

+----+

11 rows in set (0.01 sec)

mysql> 

2、在特殊情况下可以使用sql方式生成序列号

如常见的订单号：YYYYMMDDHHMMSSNUM：年月日时分秒编号

start transaction;

生成序列号

commit;

四、如何删除重复数据

1、查询重复数据

mysql> select * from score;

+------+---------+-------+

| name | course  | score |

+------+---------+-------+

| jack | math    |    88 |

| john | math    |    88 |

| john | English |    77 |

| john | Chinese |    66 |

| jack | English |    87 |

| jack | Chinese |    57 |

| jack | math    |    88 |

| john | math    |    88 |

+------+---------+-------+

8 rows in set (0.00 sec)

mysql> select name,course,count(*) from score group by name,course having count(*)>1;

+------+--------+----------+

| name | course | count(*) |

+------+--------+----------+

| jack | math   |        2 |

| john | math   |        2 |

+------+--------+----------+

2 rows in set (0.00 sec)

mysql> 

2、删除重复数据

mysql> select * from score;

+----+------+--------+-------+

| id | name | course | score |

+----+------+--------+-------+

|  6 | jack | math   |    88 |

|  7 | jack | math   |    88 |

|  8 | john | math   |    88 |

|  9 | john | math   |    88 |

+----+------+--------+-------+

4 rows in set (0.00 sec)

mysql> select name,course,count(*) from score group by name,course having count(*)>1;

+------+--------+----------+

| name | course | count(*) |

+------+--------+----------+

| jack | math   |        2 |

| john | math   |        2 |

+------+--------+----------+

2 rows in set (0.00 sec)

mysql> delete a from score a join ( select name,count(*),max(id) as id from score group by name having count(*)>1) b on a.name=b.name where a.id < b.id;

Query OK, 2 rows affected (0.02 sec)

mysql> select * from score;

+----+------+--------+-------+

| id | name | course | score |

+----+------+--------+-------+

|  7 | jack | math   |    88 |

|  9 | john | math   |    88 |

+----+------+--------+-------+

2 rows in set (0.00 sec)

delete a from score a join ( select name,count(*),max(id) as id from score group by name having count(*)>1 ) b on a.name=b.name where a.id < b.id;

第一步：求得原表与重复数据表的交集

第二步：求得名字相同且id较小的

第三部：删除

五、如何在子查询中匹配两个值

使用子查询可以避免由于子查询中的数据产生的重复，且更符合语意，更易理解

mysql> select a.name from table1 a join table2 b on a.id=b.id;

+------+

| name |

+------+

| jack |

| jack |

| john |

| john |

| john |

+------+

5 rows in set (0.00 sec)

mysql> SELECT name from table1 where id in (select id from table2);

+------+

| name |

+------+

| jack |

| john |

+------+

2 rows in set (0.00 sec)

mysql> select distinct a.name from table1 a join table2 b on a.id=b.id;

+------+

| name |

+------+

| jack |

| john |

+------+

2 rows in set (0.00 sec)

多行过滤

mysql> select * from namelist;                                                 

+----+------+

| id | name |

+----+------+

|  1 | jack |

|  2 | john |

|  3 | mary |

+----+------+

3 rows in set (0.00 sec)

mysql> select * from scorelist;                                                 

+------+----------+-------+

| id   | time     | score |

+------+----------+-------+

|    2 | 20110102 |    89 |

|    1 | 20110102 |    89 |

|    2 | 20110103 |    82 |

|    3 | 20110103 |    82 |

|    3 | 20110103 |    80 |

+------+----------+-------+

5 rows in set (0.01 sec)

mysql> select max(score) from scorelist;

+------------+

| max(score) |

+------------+

|         89 |

+------------+

1 row in set (0.00 sec)

mysql> select max(score) from scorelist c group by c.id;

+------------+

| max(score) |

+------------+

|         89 |

|         89 |

|         82 |

+------------+

3 rows in set (0.00 sec)

mysql> select c.id,max(score) from scorelist c group by c.id;

+------+------------+

| id   | max(score) |

+------+------------+

|    1 |         89 |

|    2 |         89 |

|    3 |         82 |

+------+------------+

3 rows in set (0.00 sec)

mysql> select a.id,name,time,score from namelist a join scorelist b on a.id=b.id;                                                             +----+------+----------+-------+

| id | name | time     | score |

+----+------+----------+-------+

|  2 | john | 20110102 |    89 |

|  1 | jack | 20110102 |    89 |

|  2 | john | 20110103 |    82 |

|  3 | mary | 20110103 |    82 |

|  3 | mary | 20110103 |    80 |

+----+------+----------+-------+

5 rows in set (0.00 sec)

mysql> select a.name,b.time,b.score from namelist a join scorelist b on a.id=b.idwhere (b.id,b.score) in (select c.id,max(score) from scorelist c group by c.id);

+------+----------+-------+

| name | time     | score |

+------+----------+-------+

| john | 20110102 |    89 |

| jack | 20110102 |    89 |

| mary | 20110103 |    82 |

+------+----------+-------+

3 rows in set (0.00 sec)

mysql> 

六、解决同属性多值过滤的问题

1、使用join的实现方式

mysql> select * from score order by name;                                       

+------+---------+-------+

| name | course  | score |

+------+---------+-------+

| jack | math    |    88 |

| jack | english |    50 |

| jack | chinese |    61 |

| john | math    |    88 |

| john | chinese |    63 |

| john | english |    83 |

+------+---------+-------+

6 rows in set (0.00 sec)

mysql> SELECT distinct a.name, b.course, c.course from score a join score b on a.name=b.name and b.course='math' join score c on b.name=c.name and c.course='english' where b.score>=60 and c.score>=60;

+------+--------+---------+

| name | course | course  |

+------+--------+---------+

| john | math   | english |

+------+--------+---------+

1 row in set (0.01 sec)

mysql> SELECT distinct a.name, b.course, c.course from score a join score b on a.name=b.name and b.course='math' join score c on b.name=c.name and c.course='chinese' where b.score>=60 and c.score>=60;

+------+--------+---------+

| name | course | course  |

+------+--------+---------+

| jack | math   | chinese |

| john | math   | chinese |

+------+--------+---------+

2 rows in set (0.00 sec)

mysql> SELECT distinct a.name, b.course, c.course, d.course from score a join score b on a.name=b.name and b.course='math' join score c on b.name=c.name and c.course='chinese' join score d on c.name=d.name and d.course='english' where b.score>=60 and c.score>=60 and d.score>=60;

+------+--------+---------+---------+

| name | course | course  | course  |

+------+--------+---------+---------+

| john | math   | chinese | english |

+------+--------+---------+---------+

1 row in set (0.01 sec)

mysql> 

SELECT distinct a.name, b.course, c.course from 

score a 

join score b on a.name=b.name and b.course='math' 选出math

join score c on b.name=c.name and c.course='chinese' 选出Chinese

...选出其他课程

where b.score>=60 and c.score>=60;选出都大于60的行

当课程很多时，命令变长。

2、使用left join

查询任意N门课程全部及格的同学和课程：

mysql> SELECT distinct a.name,b.course,c.course,d.course from score a left join score b on a.name=b.name and b.course='math' and b.score>=60 left join score c on c.name=b.name and c.course='chinese' and c.score>=60 left join score d on d.name=c.name and d.course='english' and d.score>=60 where ((case when b.course is not null then 1 else 0 end)+(case when c.course is not null then 1 else 0 end)+(case when d.course is not null then 1 else 0 end))>=2;

+------+--------+---------+---------+

| name | course | course  | course  |

+------+--------+---------+---------+

| john | math   | chinese | english |

| jack | math   | chinese | NULL    |

+------+--------+---------+---------+

2 rows in set (0.00 sec)

mysql> SELECT distinct a.name,b.course,c.course,d.course from score a left join score b on a.name=b.name and b.course='math' and b.score>=60 left join score c on c.name=b.name and c.course='chinese' and c.score>=60 left join score d on d.name=c.name and d.course='english' and d.score>=60 where ((case when b.course is not null then 1 else 0 end)+(case when c.course is not null then 1 else 0 end)+(case when d.course is not null then 1 else 0 end))>=3;

+------+--------+---------+---------+

| name | course | course  | course  |

+------+--------+---------+---------+

| john | math   | chinese | english |

+------+--------+---------+---------+

1 row in set (0.00 sec)

mysql> 

SELECT distinct a.name,b.course,c.course,d.course 

from score a 

left join score b on a.name=b.name and b.course='math' and b.score>=60 

left join score c on c.name=b.name and c.course='chinese' and c.score>=60 

left join score d on d.name=c.name and d.course='english' and d.score>=60 

where ((case when b.course is not null then 1 else 0 end)+(case when c.course is not null then 1 else 0 end)+(case when d.course is not null then 1 else 0 end))>=2;

依旧存在同样问题，条件太多时，语句越复杂

3、使用group by+having

mysql> select a.name from score a where a.course in ('math', 'english','chinese') and a.score>=60;

+------+

| name |

+------+

| jack |

| john |

| jack |

| john |

| john |

+------+

5 rows in set (0.00 sec)

mysql> select a.name from score a where a.course in ('math', 'english','chinese') and a.score>=60 group by a.name having count(*)>=2;

+------+

| name |

+------+

| jack |

| john |

+------+

2 rows in set (0.00 sec)

mysql> select a.name from score a where a.course in ('math', 'english','chinese') and a.score>=60 group by a.name having count(*)>=3;

+------+

| name |

+------+

| john |

+------+

1 row in set (0.00 sec)

mysql> 

七、如何计算累进税类问题

1、工资关联每个区间的纳税区间

2、用least函数得到每个区间实际纳税金额：least(money-low, high-low)

3、计算出每个区间的纳税额，然后累加