目录
单表查询
完整查询语句语法
select distinct （* or 字段1，字段2…… or 四则运行算）from 表名
where 条件
group by 字段
having 条件
order by 排序
limit 控制从哪里开始显示以及 显示几条
关键字的执行顺序
如下用函数模拟一下MySQL执行顺序，这是一个面向过程编程，下一步操作依赖于上一步的结果，所以如果MySQL语句顺序书写错误将报错
# 伪代码
# 第一步：找到对应文件
def from(table_name):
open(file)
pass
# 第二步：读取并筛选数据
def where(条件):
for line in file:
if xxxxx
# 第三步：将数据按照某些字段进行分组
def group():
pass
# 第四步：对分组后的数据进行筛选
def having():
pass
# 第五步：对数据进行去重
def distinct():
pass
# 第六步：对数据进行排序
def order():
pass
# 第七步：选取部分数据
def limit():
pass
def select():
from(table_name)
where()
group()
having()
distinct()
order()
limit()
return data
简单查询
上述的关键字太多了，那到底哪些是可选的，哪些是必选的？
select distinct (* or 字段1，字段2…… or 四则运算) from 表名;
distinct 是可选的，用去去除重复记录，只有当显示的所有列的数据一摸一样才会进行去重
当字段名太长不容易理解时，可以使用 as 来取别名
*代表通配符，显示所有字段- 可以指定任意个字段名
- 可以对字段的数据进行四则运算
- 聚合函数
建表和数据准备
company.employee
员工id id int
姓名 emp_name varchar
性别 sex enum
年龄 age int
入职日期 hire_date date
岗位 post varchar
职位描述 post_comment varchar
薪水 salary double
办公室 office int
部门编号 depart_id int
# 创建表
create table employee(
id int not null unique auto_increment,
emp_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
);
# 查看表结构
desc employee;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | null | auto_increment |
| emp_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 | |
+--------------+-----------------------+------+-----+---------+----------------+
# 插入记录
# 三个部门：教学，销售，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('nick','male',18,'20170301','老男孩驻上海虹桥最帅',7300.33,401,1), # 以下是教学部
('jason','male',78,'20150302','teacher',1000000.31,401,1),
('sean','male',81,'20130305','teacher',8300,401,1),
('tank','male',73,'20140701','teacher',3500,401,1),
('oscar','male',28,'20121101','teacher',2100,401,1),
('mac','female',18,'20110211','teacher',9000,401,1),
('rocky','male',18,'19000301','teacher',30000,401,1),
('成龙','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)
;
简单查询
# 简单查询
select * from employee； # 查所有字段
select id,emp_name,sex,age,salary from employee; # 只查部分字段
# 通过四则运算查询
select emp_name,salary*12 from employee
select emp_name, salary*12 as Annual_salary from employee; # 取别名
select emp_name, salary*12 Annual_salary from employee; # 可以省略as
# 去重查询
select distinct post from employee;
# 定义显示格式
# concat() 函数用于连接字符串
select concat('姓名:',emp_name,'年薪:', salary*12) as Annual_salary from employee;
# concat_ws() 第一个参数为分隔符
select concat_ws(":",emp_name,salary*12) as Annual_salary from emplyee;
# 结合case语句
select (
case when emp_name = "mac" then
emp_name
when emp_name = "jason" then
concat(emp_name,"_DSB")
else
concat(emp_name,"SB")
end) as new_name from employee;
练习
- 查出所有员工的名字，薪资，格式为
<名字：nick> <薪资：3000> - 查出所有岗位
- 查出所有员工名字，以及他们的年薪，年薪字段名为annual_year
select concat("<名字：",emp_name,"> <年薪：",salary*12,">") from employee;
select distinct post from employee;
select emp_name, salary*12 annual_year from employee;
where + 条件
where子句中可以使用：
-
比较运算符：
> < >= <= <> != -
逻辑运算符：在多个条件直接可以使用逻辑运算符
and or not -
between 80 and 100 值在80到100之间
-
in not in
-
like "匹配条件"
通配符可以是%或_，
- %表示任意多字符
- _表示一个字符
1. 单条件查询
select emp_name from employee
where post='sale';
2. 多条件查询
select emp_name,salary from employee
where post='teacher' and salary>10000;
3. 关键字between and
select emp_name,salary from employee
where salary between 10000 and 20000;
select emp_name,salary from employee
where salary not between 10000 and 20000;
4. 关键字is null(判断某个字段是否为null不能用等号，需要用is)
select emp_name,post_comment from employee
where post_comment is null;
select emp_name,post_comment from employee
where post_comment is not null;
select emp_name,post_comment from employee
where post_comment=''; 注意''是空字符串，不是null
ps：
执行
update employee set post_comment='' where id=2;
再用上条查看，就会有结果了
5. 关键字in集合查询
select emp_name,salary from employee
where salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
select emp_name,salary from employee
where salary in (3000,3500,4000,9000) ;
select emp_name,salary from employee
where salary not in (3000,3500,4000,9000) ;
6. 关键字like模糊查询
通配符’%’
select * from employee
where emp_name like 'ni%';
通配符’_’
select * from employee
where emp_name like 'ja__';
练习
- 查看岗位是teacher的员工姓名、年龄
- 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
- 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
- 查看岗位描述不为NULL的员工信息
- 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
- 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
- 查看岗位是teacher且名字是jin开头的员工姓名、年薪
1. select emp_name,age from employee where post = 'teacher';
2. select emp_name,age from employee where post='teacher' and age > 30;
3. select emp_name,age,salary from employee where post='teacher' and salary between 9000 and 10000;
4. select * from employee where post_comment is not null;
5. select emp_name,age,salary from employee where post='teacher' and salary in (10000,9000,30000);
6. select emp_name,age,salary from employee where post='teacher' and salary not in (10000,9000,30000);
7. select emp_name,salary*12 from employee where post='teacher' and emp_name like 'mac%';
分组 group by
group by 是对数据进行分组，这样就比较方便进行统计数据
-
语法
select * from 表名 group by 字段名
单独使用group by关键字分组
select post from employee group by post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
group by关键字和group_concat()函数一起使用
select post,group_concat(emp_name) from employee group by post; # 按照岗位分组，并查看组内成员名
select post,group_concat(emp_name) as emp_members from employee group by post;
group by与聚合函数一起使用
select post,count(id) as count from employee group by post; # 按照岗位分组，并查看每个组有多少人
聚合函数
又称统计函数，将一堆数据进行计算得到一个结果
注意：聚合函数聚合的是组的内容，如果没有分组，则默认一组
| 函数名 | 作用 |
|---|---|
| sum(字段名) | 求和 |
| avg(字段名) | 求平均值 |
| max(字段名) | 求最大值 |
| min(字段名) | 求最小值 |
| count(字段名 or *) | 计数 |
聚合函数可以用在字段的后面，也可以用在分组的后面
错误案例：
select name,max(salary) from emp;
#默认显示的第一个name 因为name有很多行 而max(salary) 只有一行 两列的行数不匹配
# 不应该这么写 逻辑错误
select name from emp where salary = max(salary);
# 报错
# 原因: 伪代码
for line in file:
if salary = max(salary)
#分析: where 读取满足条件的一行,max()先要拿到所有数据才能求最大值,
# 这里由于读取没有完成所有无法求出最大值
#结论: where 后面不能使用聚合函数
练习
- 查询岗位名以及岗位包含的所有员工名字
- 查询岗位名以及各岗位内包含的员工个数
- 查询公司内男员工和女员工的个数
- 查询岗位名以及各岗位的平均薪资
- 查询岗位名以及各岗位的最高薪资
- 查询岗位名以及各岗位的最低薪资
- 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
1. select post,group_concat(emp_name) from employee group by post;
2. select post,count(id) from employee group by post;
3. select sex,count(id) from employee group by sex;
4. select post,avg(salary) from employee group by post;
5. select post,max(salary) from employee group by post;
6. select post,min(salary) from employee group by post;
7. select sex,avg(salary) from employee group by sex;
过滤 having
where 和 having 的区别
执行优先级从高到低：where > group by > having
- Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
- Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
练习
- 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
- 查询各岗位平均薪资大于10000的岗位名、平均工资
- 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
1. mysql> select post,group_concat(emp_name),count(id) from employee group by post having count(id) < 2;
2. mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
3. mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) <20000;
排序 order by
根据某个字段进行排序
-
语法：
select * from table_name order by 字段1 (asc),字段2...;默认情况下是升序（asc），如果要进行降序排列，可以在要降序的对应字段后面添加desc约束，如：
select * from table_name order by 字段1 desc,字段2...;
多个字段进行排序，则先按照第一个字段进行排序，第一个字段相同就按照第二个字段进行排序，以此类推
练习
- 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
- 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
- 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
1. mysql> select * from employee ORDER BY age asc,hire_date desc;
2. mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) asc;
3. mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
limit
用于限制要显示的记录数量
-
语法：
select * from table_name limit 个数;select * from table_name limit 起始位置,个数;
# 查询前三条
select * from emp limit 3;
# 从第三条开始 查询3条 3-5
select * from emp limit 2,3;
# 注意：起始位置从0开始
-
经典的使用场景：分页显示
1.每一页显示的条数 a = 3
2.明确当前页数 b = 2
3.计算起始位置 c = (b-1) * a
正则表达式查询
通过正则表达式进行匹配查询
-
语法：
select *from table where 字段名 regexp "表达式!";
**注意：不能使用类似 w 这样的符号，需要找其他符号来代替 **
select * from employee where emp_name regexp '^jas';
select * from employee where emp_name regexp 'on$';
select * from employee where emp_name regexp 'm{2}';