python-day42--单表查询
1. 简单查询
select * from employee;
select name,salary from employee;
2. where条件
1.比较运算符：> < >= <= <> !=
select name,salary from employee where salary > 10000;
select name,salary from employee where salary > 10000 and salary < 20000;
2.between
select name,salary from employee where salary between 10000 and 20000; # 包括10000和20000本身
select name,salary from employee where salary not between 10000 and 20000;
3.in
select name,salary from employee where salary = 10000 or salary = 20000 or salary = 30000;
等同于 select name,salary from employee where salary in (10000,20000,30000); #条件相同时，可以这么用
select * from employee where salary = 10000 or age = 18 or sex='male'; #查找条件不相同时，就不可以用in
select * from employee where post_comment is Null;
select * from employee where post_comment = Null; #不可以用 =
select * from employee where post_comment is not Null;
is 判断是不是空（null） = 是比较有没有值（''）
4.like
select * from employee where name like '%n%';
select * from employee where name like 'e__n'; # 一个_ 表示一个字符位
5.逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询 SELECT name FROM employee WHERE post='sale'; #2:多条件查询 SELECT name,salary FROM employee WHERE post='teacher' AND salary>10000; #3:关键字BETWEEN AND SELECT name,salary FROM employee WHERE salary BETWEEN 10000 AND 20000; SELECT name,salary FROM employee WHERE salary NOT BETWEEN 10000 AND 20000; #4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) SELECT name,post_comment FROM employee WHERE post_comment IS NULL; SELECT name,post_comment FROM employee WHERE post_comment IS NOT NULL; SELECT name,post_comment FROM employee WHERE post_comment=''; 注意''是空字符串，不是null ps： 执行 update employee set post_comment='' where id=2; 再用上条查看，就会有结果了 #5:关键字IN集合查询 SELECT name,salary FROM employee WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; SELECT name,salary FROM employee WHERE salary IN (3000,3500,4000,9000) ; SELECT name,salary FROM employee WHERE salary NOT IN (3000,3500,4000,9000) ; #6:关键字LIKE模糊查询 通配符’%’ SELECT * FROM employee WHERE name LIKE 'eg%'; 通配符’_’ SELECT * FROM employee WHERE name LIKE 'al__';
3.group by分组
大前提：可以按照任意字段分组,但分完组后，只能查看分组的那个字段，要想取的组内的其他字段信息，需要借助函数
mysql> select depart_id,group_concat(name) from employee group by depart_id; +-----------+--------------------------------------------------------------+ | depart_id | group_concat(name) | +-----------+--------------------------------------------------------------+ | 1 | egon,alex,wupeiqi,yuanhao,liwenzhou,jingliyang,jinxin,成龙 | | 2 | 歪歪,丫丫,丁丁,星星,格格 | | 3 | 张野,程咬金,程咬银,程咬铜,程咬铁 | +-----------+--------------------------------------------------------------+ 3 rows in set (0.00 sec) mysql> select depart_id,count(name) from employee group by depart_id; +-----------+-----------+ | depart_id | count(id) | +-----------+-----------+ | 1 | 8 | | 2 | 5 | | 3 | 5 | +-----------+-----------+ 3 rows in set (0.01 sec) mysql> select depart_id,group_concat(id) from employee group by depart_id; +-----------+------------------+ | depart_id | group_concat(id) | +-----------+------------------+ | 1 | 1,2,3,4,5,6,7,8 | | 2 | 9,10,11,12,13 | | 3 | 14,15,16,17,18 | +-----------+------------------+ 3 rows in set (0.00 sec) mysql> select depart_id,count(id) from employee group by depart_id; +-----------+-----------+ | depart_id | count(id) | +-----------+-----------+ | 1 | 8 | | 2 | 5 | | 3 | 5 | +-----------+-----------+ 3 rows in set (0.00 sec) mysql> select depart_id,max(salary) from employee group by depart_id; +-----------+-------------+ | depart_id | max(salary) | +-----------+-------------+ | 1 | 1000000.31 | | 2 | 4000.33 | | 3 | 20000.00 | +-----------+-------------+ 3 rows in set (0.00 sec) mysql> select depart_id,min(salary) from employee group by depart_id; +-----------+-------------+ | depart_id | min(salary) | +-----------+-------------+ | 1 | 2100.00 | | 2 | 1000.37 | | 3 | 10000.13 | +-----------+-------------+ 3 rows in set (0.00 sec) mysql> select depart_id,sum(salary) from employee group by depart_id; +-----------+-------------+ | depart_id | sum(salary) | +-----------+-------------+ | 1 | 1070200.64 | | 2 | 13001.47 | | 3 | 84000.13 | +-----------+-------------+ 3 rows in set (0.00 sec) mysql> select depart_id,avg(salary) from employee group by depart_id; +-----------+---------------+ | depart_id | avg(salary) | +-----------+---------------+ | 1 | 133775.080000 | | 2 | 2600.294000 | | 3 | 16800.026000 | +-----------+---------------+ 3 rows in set (0.00 sec)
单独使用GROUP BY关键字分组 SELECT post FROM employee GROUP BY post; 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数 GROUP BY关键字和GROUP_CONCAT()函数一起使用 SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post;#按照岗位分组，并查看组内成员名 SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post; GROUP BY与聚合函数一起使用 select post,count(id) as count from employee group by post;#按照岗位分组，并查看每个组有多少人
强调：
如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
#参考链接：http://www.ywnds.com/?p=8184 #分组查询的常见问题： mysql> select id,count from tt group by id; ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.tt.count' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by #查看MySQL 5.7默认的sql_mode如下： mysql> select @@global.sql_mode; ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION #去掉ONLY_FULL_GROUP_BY模式，如下操作： mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; #！！！注意 ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。
