目录
单表查询
前期表数据准备
# 下面的代码测试都是基于这个表的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), #以下是教学部('egon','male',78,'20150302','teacher',1000000.31,401,1),('kevin','male',81,'20130305','teacher',8300,401,1),('tank','male',73,'20140701','teacher',3500,401,1),('owen','male',28,'20121101','teacher',2100,401,1),('jerry','female',18,'20110211','teacher',9000,401,1),('nick','male',18,'19000301','teacher',30000,401,1),('sean','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);#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk 表中数据展示：
1、语法执行顺序
# 简单查询语句select id,name from emp where id>=3 and id<=6;# 执行顺序from # 读取文件where # 筛选条件select # 显示指定结果# 补充：查看数据时另一种数据展现方式(解决字段太多，终端横向距离不够显示的问题)# select * from emp \G;mysql> select * from emp \G;*************************** 1. row *************************** id: 1 name: jason sex: male age: 18 hire_date: 2017-03-01 post: 张江第一帅形象代言post_comment: NULL salary: 7300.33 office: 401 depart_id: 1*************************** 2. row *************************** id: 2 name: egon sex: male age: 78 hire_date: 2015-03-02 post: teacherpost_comment: NULL salary: 1000000.31 office: 401 depart_id: 1*************************** 3. row *************************** id: 3 name: kevin sex: male age: 81 hire_date: 2013-03-05 post: teacherpost_comment: NULL salary: 8300.00 office: 401 depart_id: 1.........剩下的数据.........
2、where 约束条件
1.查询id大于等于3小于等于6的数据# 方式一：select * from emp where id>=3 and id<=6;# 方式二：select * from emp where id between 3 and 6;2、查询薪资是20000或者18000或者17000的数据# 方式一select * from emp where salary=20000 or salary=18000 or salary=17000;# 方式二select * from emp where salary in (20000,18000,17000); # 简写3.查询员工姓名中包含o字母的员工姓名和薪资# 思路： 在刚开始接触mysql查询的时候，建议先按照查询的优先级顺序拼写出你的sql语句'''1、先查那张表 from emp2、根据什么条件去查 where name like '%o%'3、再对得到的结果进行筛选显示 select name,salary'''=> # select name,salary from emp where name like '%o%';mysql> select name,salary from emp where name like '%o%';+-------+------------+| name | salary |+-------+------------+| jason | 7300.33 || egon | 1000000.31 || owen | 2100.00 |+-------+------------+3 rows in set (0.00 sec)4.查询员工姓名是由四个字符组成的员工姓名与其薪资# 方式一select name,salary from emp where id like '____';# 方式二select name,salary from emp where char_length(id)=4;5、查询薪资不在20000，18000，17000范围的数据select * from emp where salary not in (20000,18000,17000);6、查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is# select name,post from emp where post_comment = null;mysql> select name,post from emp where post_comment = null;Empty set (0.00 sec)# select name,post from emp where post_comment is null;mysql> select name,post from emp where post_comment is null;+-----------+-----------------------------+| name | post |+-----------+-----------------------------+| jason | 张江第一帅形象代言 || egon | teacher || kevin | teacher || tank | teacher || owen | teacher || jerry | teacher || nick | teacher || sean | teacher || 歪歪 | sale || 丫丫 | sale || 丁丁 | sale || 星星 | sale || 格格 | sale || 张野 | operation || 程咬金 | operation || 程咬银 | operation || 程咬铜 | operation || 程咬铁 | operation |+-----------+-----------------------------+18 rows in set (0.00 sec)# 也可以查post_comment不为空的数据# select name,post from emp where post_comment is not null;mysql> select name,post from emp where post_comment is not null;Empty set (0.00 sec)
3、group by
数据分组应用场景：每个部门的平均薪资，男女比例1、按部门分组select * from emp group by post;# 非分组的严格模式下会取出每个分组的第一条数据# 严格模式下会直接报错，因为一旦分组了，就无法再‘直接’查到其他子弹的信息了，只能获取到分组的组名# 设置分组为严格模式：set global sql_mode="strict_trans_tables,only_full_group_by";# 设置完需要重新连接客户端测试：select * from emp group by post; # 报错select id,name,sex from emp group by post; # 报错select post from emp group by post; # 成功# 成功代码示例：mysql> select post from emp group by post;+-----------------------------+| post |+-----------------------------+| operation || sale || teacher || 张江第一帅形象代言 |+-----------------------------+4 rows in set (0.00 sec)强调：只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名# 通过聚合函数可以查找到其他数据2、获取每个部门的最高工资# 以组为单位统计组内数据>>>聚合查询(聚合到一起合为一个结果)select post,max(salary) from emp group by post;# 代码示例：mysql> select post,max(salary) from emp group by post;+-----------------------------+-------------+| post | max(salary) |+-----------------------------+-------------+| operation | 20000.00 || sale | 4000.33 || teacher | 1000000.31 || 张江第一帅形象代言 | 7300.33 |+-----------------------------+-------------+4 rows in set (0.01 sec)# 同理可得：# 每个部门最低工资select post min(salary) from emp group by post;# 每个部门平均工资select post avg(salary) from emp group by post;# 每个部门的工资总和select post sum(salary) from emp group by post;# 每个部门的人数select post count(id) from emp grop by post;3、查询分组后的部门名称 和 每个部门下所有的学生姓名# group_concat（分组了之后才会用）不仅可以用来显示除分组外字段还有拼接字符串的作用3.1）select post,group_concat(name) from emp group by post; # 显示除分组外的字段# 代码示例：mysql> select post,group_concat(name) from emp group by post;+-----------------------------+------------------------------------------------+| post | group_concat(name) |+-----------------------------+------------------------------------------------+| operation | 张野,程咬金,程咬银,程咬铜,程咬铁 || sale | 歪歪,丫丫,丁丁,星星,格格 || teacher | egon,kevin,tank,owen,jerry,nick,sean || 张江第一帅形象代言 | jason |+-----------------------------+------------------------------------------------+4 rows in set (0.00 sec)3.2）select post,group_concat(name,"_SB") from emp group by post; # 显示除分组外的字段并且拼接字符串# 代码示例：mysql> select post,group_concat(name,"_SB") from emp group by post;+-----------------------------+-------------------------------------------------------------+| post | group_concat(name,"_SB") |+-----------------------------+-------------------------------------------------------------+| operation | 张野_SB,程咬金_SB,程咬银_SB,程咬铜_SB,程咬铁_SB || sale | 歪歪_SB,丫丫_SB,丁丁_SB,星星_SB,格格_SB || teacher | egon_SB,kevin_SB,tank_SB,owen_SB,jerry_SB,nick_SB,sean_SB || 张江第一帅形象代言 | jason_SB |+-----------------------------+-------------------------------------------------------------+4 rows in set (0.00 sec)3.3）select post,group_concat(name,": ",salary) from emp group by post;# 代码示例：mysql> select post,group_concat(name,": ",salary) from emp group by post \G;# 字段与记录以字段的冒号分隔对齐*************************** 1. row *************************** post: operationgroup_concat(name,": ",salary): 张野: 10000.13,程咬金: 20000.00,程咬银: 19000.00,程咬铜: 18000.00,程咬铁: 17000.00*************************** 2. row *************************** post: salegroup_concat(name,": ",salary): 歪歪: 3000.13,丫丫: 2000.35,丁丁: 1000.37,星星: 3000.29,格格: 4000.33*************************** 3. row *************************** post: teachergroup_concat(name,": ",salary): egon: 1000000.31,kevin: 8300.00,tank: 3500.00,owen: 2100.00,jerry: 9000.00,nick: 30000.00,sean: 10000.00*************************** 4. row *************************** post: 张江第一帅形象代言group_concat(name,": ",salary): jason: 7300.333.4）select post,group_concat(salary) from emp group by post;# 代码示例mysql> select post,group_concat(salary) from emp group by post;+-----------------------------+-------------------------------------------------------------+| post | group_concat(salary) |+-----------------------------+-------------------------------------------------------------+| operation | 10000.13,20000.00,19000.00,18000.00,17000.00 || sale | 3000.13,2000.35,1000.37,3000.29,4000.33 || teacher | 1000000.31,8300.00,3500.00,2100.00,9000.00,30000.00,10000.00|| 张江第一帅形象代言 | 7300.33 |+-----------------------------+-------------------------------------------------------------+4 rows in set (0.00 sec)4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用# as语法基本使用：select name as '姓名',salary as '薪资' from emp;# 代码示例：mysql> select name as '姓名',salary as '薪资' from emp;+-----------+------------+| 姓名 | 薪资 |+-----------+------------+| jason | 7300.33 || egon | 1000000.31 || kevin | 8300.00 || tank | 3500.00 || owen | 2100.00 || jerry | 9000.00 || nick | 30000.00 || sean | 10000.00 || 歪歪 | 3000.13 || 丫丫 | 2000.35 || 丁丁 | 1000.37 || 星星 | 3000.29 || 格格 | 4000.33 || 张野 | 10000.13 || 程咬金 | 20000.00 || 程咬银 | 19000.00 || 程咬铜 | 18000.00 || 程咬铁 | 17000.00 |+-----------+------------+18 rows in set (0.00 sec)# as 与 concat 合用select concat('NAME:',name) as '姓名',concat('SALARY:',salary) as '薪资' from emp;# 代码示例：mysql> select concat('NAME:',name) as '姓名',concat('SALARY:',salary) as '薪资' from emp;+----------------+-------------------+| 姓名 | 薪资 |+----------------+-------------------+| NAME:jason | SALARY:7300.33 || NAME:egon | SALARY:1000000.31 || NAME:kevin | SALARY:8300.00 || NAME:tank | SALARY:3500.00 || NAME:owen | SALARY:2100.00 || NAME:jerry | SALARY:9000.00 || NAME:nick | SALARY:30000.00 || NAME:sean | SALARY:10000.00 || NAME:歪歪 | SALARY:3000.13 || NAME:丫丫 | SALARY:2000.35 || NAME:丁丁 | SALARY:1000.37 || NAME:星星 | SALARY:3000.29 || NAME:格格 | SALARY:4000.33 || NAME:张野 | SALARY:10000.13 || NAME:程咬金 | SALARY:20000.00 || NAME:程咬银 | SALARY:19000.00 || NAME:程咬铜 | SALARY:18000.00 || NAME:程咬铁 | SALARY:17000.00 |+----------------+-------------------+18 rows in set (0.00 sec)# 补充as语法 即可以给字段起别名也可以给表起select emp.id,emp.name from emp as t1; # 报错 因为表名已经被你改成了t1select t1.id,t1.name from emp as t1; # 成功# 查询四则运算# 查询每个人的年薪select name,salary*12 as annual_salary from emp;select name,salary*12 annual_salary from emp; # as可以省略 4、having
# 需求：先分类，在筛选截止目前已经学习的语法：select 查询字段1,查询字段2,... from 表名 where 过滤条件 group by分组依据# 语法这么写，但是执行顺序却不一样fromwhere # 筛选group by # 分组select# 可得：where的筛选进行的 顺序在分组之间，而我们想在分好组后的数据中进行筛选==>havinghaving的语法格式与where一致，只不过having是在分组之后进行的过滤，即where虽然不能用聚合函数，但是having可以！1、统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门select post,avg(salary) from emp where age >= 30 group by post having avg(salary) > 10000;# 代码示例：mysql> select post,avg(salary) from emp -> where age >= 30 -> group by post -> having avg(salary) > 10000;+---------+---------------+| post | avg(salary) |+---------+---------------+| teacher | 255450.077500 |+---------+---------------+1 row in set (0.00 sec)# 如果不信你可以将having取掉，查看结果，对比即可验证having用法！# 代码示例:mysql> select post,avg(salary) from emp -> where age >= 30 -> group by post;+---------+---------------+| post | avg(salary) |+---------+---------------+| sale | 2500.240000 || teacher | 255450.077500 |+---------+---------------+2 rows in set (0.00 sec)#强调：having必须在group by后面使用select * from emp having avg(salary) > 10000; # 报错
5.distinct
# 对重复的数据进行去重操作select distinct post from emp;# 代码示例：mysql> select distinct post from emp;+-----------------------------+| post |+-----------------------------+| 张江第一帅形象代言 || teacher || sale || operation |+-----------------------------+4 rows in set (0.00 sec)
6.order by
# 将数据按薪资从大到小排序select * from emp order by salary asc; # 默认升序select * from emp order by salary desc; # 降序排select * from emp order by age desc; #降序排#先按照age降序排，在age相同的情况下再按照薪资升序排select * from emp order by age desc,salary asc; # 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序select post,avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary); # 代码示例：mysql> select post,avg(salary) from emp -> where age > 10 -> group by post -> having avg(salary) > 1000 -> order by avg(salary);+-----------------------------+---------------+| post | avg(salary) |+-----------------------------+---------------+| sale | 2600.294000 || 张江第一帅形象代言 | 7300.330000 || operation | 16800.026000 || teacher | 151842.901429 |+-----------------------------+---------------+4 rows in set (0.00 sec)
7.limit
# 限制展示条数select * from emp limit 3;# 查询工资最高的人的详细信息select * from emp order by salary desc limit 1;# 分页显示select * from emp limit 0,5; # 第一个参数表示起始位置，第二个参数表示的是条数，不是索引位置select * from emp limit 5,5; # 第一个参数：从第5条记录开始显示，第二个参数：往后显示5条
8.正则
% _正则(regexp)贪婪匹配： .* 与非贪婪匹配: .*?# 匹配 以 j 开头，以 n或y 结尾 的记录select * from emp where name regexp '^j.*(n|y)$';# 代码示例：mysql> select * from emp where name regexp '^j.*(n|y)$' \G;*************************** 1. row *************************** id: 1 name: jason sex: male age: 18 hire_date: 2017-03-01 post: 张江第一帅形象代言post_comment: NULL salary: 7300.33 office: 401 depart_id: 1*************************** 2. row *************************** id: 6 name: jerry sex: female age: 18 hire_date: 2011-02-11 post: teacherpost_comment: NULL salary: 9000.00 office: 401 depart_id: 12 rows in set (0.00 sec)