数据库之单表查询
阅读目录
一、单表查询的语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
二、关键字的执行优先级
#重点中的重点：关键字的执行优先级 from where group by having select distinct order by limit
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
三、简单查询
#创建表 create table emp( id int not null unique auto_increment, name varchar(20) not null, sex enum('male','female') not null default 'male', #大部分是男的 age int(3) unsigned not null default 28, hire_date date not null, post varchar(50), post_comment varchar(100), salary double(15,2), office int, #一个部门一个屋子 depart_id int ); #插入记录 #三个部门：教学，销售，运营 insert into emp(name,sex,age,hire_date,post,salary,office,depart_id) values ('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1), #以下是教学部 ('tom','male',78,'20150302','teacher',1000000.31,401,1), ('kevin','male',81,'20130305','teacher',8300,401,1), ('tony','male',73,'20140701','teacher',3500,401,1), ('owen','male',28,'20121101','teacher',2100,401,1), ('jack','female',18,'20110211','teacher',9000,401,1), ('jenny','male',18,'19000301','teacher',30000,401,1), ('sank','male',48,'20101111','teacher',10000,401,1), ('哈哈','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门 ('呵呵','female',38,'20101101','sale',2000.35,402,2), ('西西','female',18,'20110312','sale',1000.37,402,2), ('乐乐','female',18,'20160513','sale',3000.29,402,2), ('拉拉','female',28,'20170127','sale',4000.33,402,2), ('僧龙','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门 ('程咬金','male',18,'19970312','operation',20000,403,3), ('程咬银','female',18,'20130311','operation',19000,403,3), ('程咬铜','male',18,'20150411','operation',18000,403,3), ('程咬铁','female',18,'20140512','operation',17000,403,3); # 当表字段特别多 展示的时候错乱 可以使用\G分行展示 select * from emp\G; #查看表结构 mysql> desc emp; +--------------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(3) unsigned | NO | | 28 | | | hire_date | date | NO | | NULL | | | post | varchar(50) | YES | | NULL | | | post_comment | varchar(100) | YES | | NULL | | | salary | double(15,2) | YES | | NULL | | | office | int(11) | YES | | NULL | | | depart_id | int(11) | YES | | NULL | | +--------------+-----------------------+------+-----+---------+----------------+ 10 rows in set (0.01 sec)
#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
#简单查询 SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id FROM emp; SELECT * FROM emp; SELECT name,salary FROM emp; #避免重复DISTINCT（去重） SELECT DISTINCT post FROM emp; #通过四则运算查询（只能是数值字段） SELECT name, salary*12 FROM emp; SELECT name, salary*12 AS Annual_salary FROM emp; SELECT name, salary*12 Annual_salary FROM emp; #定义显示格式（as 临时给字段取别名） CONCAT() 函数用于连接字符串 SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM emp; #CONCAT_WS() 第一个参数为分隔符，指定分隔符，如果多个字段之间的连接符号是相同的可以用concat_ws SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM emp; #结合CASE语句： SELECT ( CASE WHEN NAME = 'jason' THEN NAME WHEN NAME = 'tom' THEN CONCAT(name,'_BIGSB') ELSE concat(NAME, 'SB') END ) as new_name FROM emp;
四、where筛选条件
# 作用:是对整体数据的一个筛选操作
分组之前使用的筛选关键字
#where字句中可以使用： 1. 比较运算符：> < >= <= <> != 2. between 80 and 100 值在10到20之间 3. in(80,90,100) 值是10或20或30 4. 模糊查询like '%egon%'%表示任意多字符 _表示一个字符 5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询 SELECT name FROM emp WHERE post='sale'; #2:多条件查询 # 查询id大于等于3小于等于6的数据 select id,name,age from emp where id>=3 and id<=6; select id,name from emp where id between 3 and 6; 两者等价 #3:关键字BETWEEN AND SELECT name,salary FROM emp WHERE salary BETWEEN 10000 AND 20000; SELECT name,salary FROM emp WHERE salary NOT BETWEEN 10000 AND 20000; #4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS) SELECT name,post_comment FROM emp WHERE post_comment IS NULL; SELECT name,post_comment FROM emp WHERE post_comment IS NOT NULL; SELECT name,post_comment FROM emp WHERE post_comment=''; 注意''是空字符串，不是null ps： 执行 update emp set post_comment='' where id=2; 再用上条查看，就会有结果了 #5:关键字IN集合查询 SELECT name,salary FROM emp WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ; SELECT name,salary FROM emp WHERE salary IN (3000,3500,4000,9000) ; SELECT name,salary FROM emp WHERE salary NOT IN (3000,3500,4000,9000) ; #6:关键字LIKE模糊查询 通配符’%’ #找出name中带有"o"字母的name、salary select name,salary from emp where name like '%o%'; 通配符’_’ #查询员工姓名是由四个字符组成的 姓名和薪资 可以使用char_length() 或模糊查找的通配符_ select name,salary from emp where name like '____'; select name,salary from emp where char_length(name) = 4;
五、分组查询：group by
什么是分组？为什么要分组？
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的 #2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等 #3、为何要分组呢？ 取每个部门的最高工资 取每个部门的员工数 取男人数和女人数 小窍门：‘每’这个字后面的字段，就是我们分组的依据 #4、大前提： 可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
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的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。 #设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)： mysql> set global sql_mode='STRICT_TRANS_TABLES,ONLY_FULL_GROUP_BY';
#没设置ONLY_FULL_GROUP_BY之前 mysql> select @@global.sql_mode; +---------------------+ | @@global.sql_mode | +---------------------+ | STRICT_TRANS_TABLES | +---------------------+ 1 row in set (0.00 sec) mysql> select * from emp group by post; +----+--------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 | | 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | +----+--------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ 4 rows in set (0.00 sec) mysql> #由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的 #设置ONLY_FULL_GROUP_BY mysql> set global sql_mode='ONLY_FULL_GROUP_BY'; Query OK, 0 rows affected (0.00 sec) mysql> quit #设置成功后，一定要退出，然后重新登录方可生效 Bye mysql> use school; #进入库 Database changed mysql> select @@global.sql_mode; +----------------------------------------+ | @@global.sql_mode | +----------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES | +----------------------------------------+ 1 row in set (0.00 sec) mysql> select * from emp group by post; ERROR 1055 (42000): 'school.emp.id' isn't in GROUP BY mysql> #设置严格模式之后 分组默认只能拿到分组的依据 mysql> select post from emp group by post; +-----------------------------+ | post | +-----------------------------+ | operation | | sale | | teacher | | 张江第一帅形象代言 | +-----------------------------+ 4 rows in set (0.00 sec) mysql> #按照什么分组就只能拿到分组，其他字段不能直接获取，如需获取其他字段需要借助于一些方法(聚合函数)
group by
#单独使用GROUP BY关键字分组 SELECT post FROM emp GROUP BY post; 注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助聚合函数 #GROUP BY关键字和GROUP_CONCAT()函数一起使用 SELECT post,GROUP_CONCAT(name) FROM emp GROUP BY post;#按照岗位分组，并查看组内成员名 SELECT post,GROUP_CONCAT(name) as emp_members FROM emp GROUP BY post; #GROUP BY与聚合函数一起使用 select post,count(id) as count from emp group by post;#按照岗位分组，并查看每个组有多少人 #ps:语句中是给select查找的字段取别名
强调：
如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
聚合函数
#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组 示例： SELECT COUNT(*) FROM emp; SELECT COUNT(*) FROM emp WHERE depart_id=1; SELECT MAX(salary) FROM emp; SELECT MIN(salary) FROM emp; SELECT AVG(salary) FROM emp; SELECT SUM(salary) FROM emp; SELECT SUM(salary) FROM emp WHERE depart_id=3;
注意事项：
# 关键字where和group by同时出现的时候group by必须在where的后面 where先对整体数据进行过滤之后再分组操作 where筛选条件不能使用聚合函数 select id,name,age from emp where max(salary) > 3000; select max(salary) from emp; # 不分组 默认整体就是一组 # 统计各部门年龄在30岁以上的员工平均薪资 1 先求所有年龄大于30岁的员工 select * from emp where age>30; 2 再对结果进行分组 select post,avg(salary) from emp where age>30 group by post;
练习：
# 1.获取每个部门的最高薪资 select post,max(salary) from emp group by post; select post as '部门',max(salary) as '最高薪资' from emp group by post; select post '部门',max(salary) '最高薪资' from emp group by post; # as可以给字段起别名 也可以直接省略不写 但是不推荐 因为省略的话语意不明确 容易错乱 # 2.获取每个部门的最低薪资 select post,min(salary) from emp group by post; # 3.获取每个部门的平均薪资 select post,avg(salary) from emp group by post; # 4.获取每个部门的工资总和 select post,sum(salary) from emp group by post; # 5.获取每个部门的人数 select post,count(id) from emp group by post; # 常用 符合逻辑 select post,count(salary) from emp group by post; select post,count(age) from emp group by post; select post,count(post_comment) from emp group by post; null不行 # 6.查询分组之后的部门名称和每个部门下所有的员工姓名 # group_concat不单单可以支持你获取分组之后的其他字段值 还支持拼接操作 select post,group_concat(name) from emp group by post; select post,group_concat(name,'_DSB') from emp group by post; select post,group_concat(name,':',salary) from emp group by post; # concat不分组的时候用 select concat('NAME:',name),concat('SAL:',salary) from emp; # 补充 as语法不单单可以给字段起别名 还可以给表临时起别名 select emp.id,emp.name from emp; select emp.id,emp.name from emp as t1; 报错 select t1.id,t1.name from emp as t1; # 查询每个人的年薪 12薪 select name,salary*12 from emp;
# 统计各部门年龄在30岁以上的员工平均薪资
select post,avg(salary) from emp where age>30 group by post;
having分组之后的筛选条件
HAVING与WHERE不一样的地方在于!!!!!!
#！！！执行优先级从高到低：where > group by > having #1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。 #2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门 select post,avg(salary) from emp where age>30 group by post having avg(salary) > 10000 ;
distinct去重
""" 一定要注意 必须是完全一样的数据才可以去重！！！ 一定不要将逐渐忽视了 有逐渐存在的情况下 是不可能去重的 [ {'id':1,'name':'jason','age':18}, {'id':2,'name':'jason','age':18}, {'id':3,'name':'egon','age':18} ] """ #select distinct id,age from emp;因为id字段不同，所以无法去重 mysql> select distinct id,age from emp; +----+-----+ | id | age | +----+-----+ | 1 | 18 | | 2 | 78 | | 3 | 81 | | 4 | 73 | | 5 | 28 | | 6 | 18 | | 7 | 18 | | 8 | 48 | | 9 | 48 | | 10 | 38 | | 11 | 18 | | 12 | 18 | | 13 | 28 | | 14 | 28 | | 15 | 18 | | 16 | 18 | | 17 | 18 | | 18 | 18 | +----+-----+ 18 rows in set (0.00 sec) #select distinct age from emp; #去掉id字段后 mysql> select distinct age from emp; +-----+ | age | +-----+ | 18 | | 78 | | 81 | | 73 | | 28 | | 48 | | 38 | +-----+ 7 rows in set (0.00 sec) #总结: 去重的时候最后去掉id字段（因为id字段一般为主键，自增，并不相同）
order by排序
select * from emp order by salary; select * from emp order by salary asc; select * from emp order by salary desc; """ order by默认是升序 asc 因此该asc可以省略不写 也可以修改为降序 desc """ select * from emp order by age desc,salary asc; # 先按照age降序排 如果碰到age相同 则再按照salary升序排 # 统计各部门年龄在10岁以上的员工平均工资并且保留平均薪资大于1000的部门,然后对平均工资降序排序 select post,avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by avg(salary) desc ;
limit限制展示条数
select * from emp; """针对数据过多的情况 我们通常都是做分页处理""" select * from emp limit 3; # 只展示三条数据 select * from emp limit 0,5; select * from emp limit 5,5; 第一个参数是起始位置 第二个参数是展示条数 mysql> select * from emp limit 0,5; +----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ | 1 | jason | male | 18 | 2017-03-01 | 张江第一帅形象代言 | NULL | 7300.33 | 401 | 1 | | 2 | tom | male | 78 | 2015-03-02 | teacher | | 1000000.31 | 401 | 1 | | 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 | | 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 | | 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 | +----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+ 5 rows in set (0.00 sec) mysql>select * from emp limit 5,5; +----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id | +----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ | 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 | | 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 | | 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 | | 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 | | 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 | +----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+ 5 rows in set (0.00 sec) mysql>
使用正则查询
SELECT * FROM employee WHERE name REGEXP '^ale'; SELECT * FROM employee WHERE name REGEXP 'on$'; SELECT * FROM employee WHERE name REGEXP 'm{2}'; 小结：对字符串匹配的方式 WHERE name = 'egon'; WHERE name LIKE 'yua%'; WHERE name REGEXP 'on$';
