一、如何正确使用join语句。
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.IDwhere b.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、进行行列转换
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>
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>
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;
三、如何正确生成序列号
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>
四、如何删除重复数据
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;
五、如何在子查询中匹配两个值
使用子查询可以避免由于子查询中的数据产生的重复,且更符合语意,更易理解
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.id where (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>
2、使用left join
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>