单表查询
单表查询
前期表数据准备
# 下面的代码测试都是基于这个表的
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: teacher
post_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: teacher
post_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 emp
2、根据什么条件去查 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: operation
group_concat(name,": ",salary): 张野: 10000.13,程咬金: 20000.00,程咬银: 19000.00,程咬铜: 18000.00,程咬铁: 17000.00
*************************** 2. row ***************************
post: sale
group_concat(name,": ",salary): 歪歪: 3000.13,丫丫: 2000.35,丁丁: 1000.37,星星: 3000.29,格格: 4000.33
*************************** 3. row ***************************
post: teacher
group_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.33
3.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; # 报错 因为表名已经被你改成了t1
select 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分组依据
# 语法这么写，但是执行顺序却不一样
from
where # 筛选
group by # 分组
select
# 可得：where的筛选进行的 顺序在分组之间，而我们想在分好组后的数据中进行筛选==>having
having的语法格式与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: teacher
post_comment: NULL
salary: 9000.00
office: 401
depart_id: 1
2 rows in set (0.00 sec)
