记录相关操作之单表查询
一. 单表查询的语法
select 字段1,字段2... from 表名
where 条件
group by field
having 筛选
order by field
limit 限制条数;
二. 关键字的执行优先级(重点)
from # 1. 找到表:from
where # 2. 拿着where指定的约束条件，去文件/表中取出一条条记录
group by # 3. 将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
having # 4. 将分组的结果进行having过滤
select # 5. 执行select
distinct # 6. 去重
order by # 7. 将结果按条件排序：order by
limit # 8. 限制结果的显示条数
python伪代码表示运行的优先级
def from():
pass
def where():
pass
def group_by():
pass
def having():
pass
def distinct():
pass
def order_by():
pass
def limit():
pass
def select():
f = from()
res = where(f)
res1 = group_by(res)
res2 = having(res1)
res3 = distinct(res2)
res4 = order_by(res3)
limit(res4)
三. 准备表
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
# 提示: 如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk(个别比较老的windos电脑可能会出现这样的问题)
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);
四. 条件约束: where
1. 操作
# 查询id大于等于3小于等于6的数据 --> 比较运算符 & between
select * from emp where id>=3 and id<=6;
select * from emp where id between 3 and 6;
# 查询薪资是20000或者18000或者17000的数据 --> in & 逻辑运算符or
select * from emp where salary=20000 or salary=17000 or salary=18000;
select * from emp where salary in (20000, 17000, 18000);
# 查询员工姓名中包含字母o的员工的姓名和薪资 --> 模糊运算like
"""
模糊查询: 1ike
%: 匹配任意多 个字符
_: 匹配任意单个字符
"""
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; # 前提: 没有取消剔除模式pad_char_to_full_length
# 查询id小于3或者id大于6的数据 --> 比较运算符 & between
select * from emp where id not between 3 and 6;
select * from emp where id<3 or id>6;
# 查询薪资不在20000，18000，17000范围的数据 --> not + in
select * from emp where salary not in (20000, 18000, 17000);
# 查询岗位描述为空的员工姓名和岗位名(易错题) --> is null & null
# select name,post from emp where post_comment = null; # 注意: 争对null不能用等号
select name,post from emp where post_comment is null;
select name,post from emp where post_comment is not null;
2. 总结
# where字句中可用操作
"""
<1> 比较运算符: >, <, >=, <=, <>, !=
<2> 逻辑运算符: and 与 or 与 not
<3> between 与 and
<4> 成员运算: in(值1, 值2, 值3)
<5> 模糊匹配: like "_"或" % "
<6> is null 或 is not null 判断某个字段是否为null, 不能用等号,需要用is
"""
# 当前语句执行优先级
from -> where -> select
# 实际语句执行优先级过程
from -> where -> group by -> having -> select -> distinct -> order by -> limit
3. 小练习
# 1. 查看岗位是teacher的员工姓名、年龄
select name,age from emp where post='teacher';
# 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,age from emp where post=teacher and age>30;
# 3. 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
select name,age,salary from emp where post='teacher' and salary>=9000 and salary<=10000;
select name,age,salary from emp where post='teacher' and salary between 9000 and 10000;
# 4. 查看岗位描述不为NULL的员工信息
select * from emp where post_comment not is null;
# 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where post='teacher' and salary in (10000, 9000, 30000);
# 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where post='teacher' and salary not in (10000, 9000, 30000);
# 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,salary*12 from emp where post='teacher' and name like 'jin%';
五. 分组查询: group by
强调: 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义. 只有多条记录之间的某个字段值相同时，就可以使用该字段用来作为分组的依据
1. 操作
# 分组实际应用场景分组应用场景非常的多例如:
"""
男女比例 部门平均薪资 部门秃头 国家之间数据统计
"""
# 注意1: 分组之后最小可操作单位应该是组, 而不是组内的单个数据.
# 注意2: 如果没有设置分组严格模式的时候是可正常执行, 返回的是分组之后每个组的第一条数据. 如果设置了分组严格模会直接报错. 没有设置分组严格模式的返回值是不符合分组的规范的, 分组之后不应该考虑单个数据, 而应该以组为操作单位. (补充: 分组之后, 只能查到分组的字段以及组内多条记录举和的成果)
# 按照部门分组
select * from emp group by post; # 注意: 没有设置分组严格模式, 这里会拿到每一个部门中的第一条数据
# 保留严格模式+追加分组严格模式(注意: 分组严格模式只和only_full_group_by有关, 严格模式的指定只是为了减轻mysql服务器存取字段时超出的压力, 保证数据的存储的严格性)
set global sql_mode='strict_trans_tables,only_full_group_by';
# 分组默认只能拿到分组的依据. 按照什么分组就只能拿到什么组. 其他字段不能直接获取, 需要借助于一些方法(聚合函数).
select post from emp group by post;
"""
什么时候需要分组啊???
关键字: 每个 平均 最高 最低
"""
# 获取每个部门的最高薪资 --> 聚合函数max() + 取别名as
select post,max(salary) as max_salary from emp group by post;
# 获取每个部门的最低薪资 --> 聚合函数min()
select post,min(salary) from emp group by post;
# 获取每个部门的平均薪资 --> 聚合函数avg()
select post,avg(salary) from emp group by post;
# 获取每个部门的工资总和 --> 聚合函数sum()
select post,sum(salary) from emp group by post;
# 获取每个部门的人数 --> 聚合函数count()
select post,count(id) from emp group by post; # count最好存放可以标识唯一性的字段(注意: 最好不要放null关键字指定的字段)
select post,count(post_comment) from emp group by post;
# 查询分组之后的部门名称和每个部门下所有的员工姓名 --> 聚合函数group_concat() & group_concat()的拼接操作
# group_concat不单单可以支持你获取分组之后的其他字段值, 还支持拼接操作.
select post,group_concat(name) from emp group by post; # group_concat获取分组之后的字段中的值
select post,group_concat(name,'_DSB') from emp group by post;
select post,group_concat(name,':',salary) from emp group by post;
# 补充1: 定义显示格式. 不分组的时候用 concat() 或者 concat_ws() 或者 case语句
"""
concat() 函数用于连接字符串
concat_ws("定义统一字段分割符号", 字段1, 字段2) 开头定义多字段间自动分隔符
注意: 一个concat或concat_ws只能表示一个字段, 如果把所有字段放到里面拼接显示格式, 就会显示成一个字段中的一列内容
case语句伪代码
case
when 条件1 then
结果
when 条件2 then
结果
else
结果
end
"""
select concat('姓名:', name),concat('年龄:', age) from emp;
select concat(name, ':', age, ':', sex) from emp;
select concat_ws(':', name, age, sex) from emp; # 对于上面一条语句重复了多次的`:`, 还是使用下面的这种.
select
(
case
when name='egon' then
name
when name='alex' then
concat(name, '_bigsb')
else
concat(name, 'sb')
end
) as new_name
from emp;
# 补充2: as语法不单单可以给字段起别名还可以给表起别名, 且as语句可以省略, 但是不推荐省略, 不省略可以让语义更加的明确.
select * from emp as t1;
select * from emp t1; # as不推荐省略
select emp.id,emp.name from emp;
select emp.id,emp.name from emp as t1; # 报错: 因为先执行from语句,执行完了from语句当前的表名被改为了t1, 接着select语句执行, select语句无法识别emp.id
# 补充3: 查询每个人的年薪12薪
select name,salary*12 from emp;
select id, name*12 from emp; # 注意: 字符串类型不支持乘除
"""综合练习where+group by"""
# 统计各部门年龄在30岁以上的员工平均薪资
# 1. 先求所有年龄大于30岁的员工
select * from emp where age>30;
# 2. 再对结果进行分组
select * from emp where age>30 group by post;
# 3. 得出最终结果
select post,avg(salary),group_concat(name, ':', age) from emp where age>30 group by post;
2. 分组注意事项
<1> 关键字where和group by同时出现的时候group by必须在where的后面
<2> where先对整体数据进行过滤之后再分组操作
<3> 聚合函数只能在分组之后使用
select id,name,age from emp where max(salary) > 3000; # 错误: ERROR 1111 (HY000): Invalid use of group function(无效使用组函数)
select max(salary) from emp; # 可以: 不分组默认整体就是一组
3. 总结
# 聚合函数
"""
max() as 别名 # as可以给字段起别名, 也可以直接省略不写, 但是不推荐. 因为使用as的话可能语意不明确易错乱. 也可以对表取别名.
min()
sum()
avg() avg --> average
count() # count最好存放可以标识唯一性的字段(注意: 最好不要放null关键字指定的字段)
group_concat() # 不仅获取分组后的其它字段们, 还可以进行拼接操作.
concat() # 不分组时使用, 可以对查询出来的字段进行额外的拼接操作
concat_ws("定义统一字段分割符号", 字段1, 字段2) 开头定义多字段间自动分隔符
注意: 一个concat或concat_ws只能表示一个字段, 如果把所有字段放到里面拼接显示格式, 就会显示成一个字段中的一列内容
case语句伪代码
case
when 条件1 then
结果
when 条件2 then
结果
else
结果
end
salary * 12 # 支持算符运算, 但是不能争对字符串
"""
# 当前语句执行优先级
from -> where -> group by -> select
# 实际语句执行优先级
from -> where -> group by -> having -> select -> distinct -> order by -> limit
4. 小练习
# 查出所有员工的名字，薪资,格式为: <名字:egon> <薪资:3000>
select concat('<名字:',name,'> ', '<薪资:',salary,'>') from emp;
# 查出所有的岗位（去掉重复）
select distinct post from emp;
# 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
select name,salary*12 as annual_year from emp;
# 1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;
# 2. 查询岗位名以及各岗位内包含的员工个数
seect post,count(id) from emp group by post;
# 3. 查询公司内男员工和女员工的个数
select sex, count(id) from emp group by sex;
# 4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;
# 5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;
# 6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;
# 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from emp group by sex;
六. 过滤: having
1. 操作
# 前提: 分组之后的筛选条件
"""
having的语法和where是一致的, 只不过having是在分组之后进行的过滤操作, 即having是可以直接使用聚合函数的.
"""
# 统计各部门年龄在30岁以上的员工平均工资并且保留平均薪资大于10000的部门
select post,avg(salary) from emp
where age>30
group by post
having avg(salary) > 10000;
select post,avg(salary),group_concat(name, ':', age) from emp
where age>30
group by post
having avg(salary)>10000;
# 当前语句的执行优先级
from -> where -> group by -> having -> select
# 实际语句执行优先级
from -> where -> group by -> having -> select -> distinct -> order by -> limit
2. 小练习
# 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from emp group by post having count(id) <2;
# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary) > 10000;
# 3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary) between 10000 and 20000;
七. 去重: distinct
# 一定要注意必须是完全一样的数据才可以去重!!! 所以去重的时候，一定要注意去除主键, 因为主键是部位空且唯一的.
"""
[
{'id':1,'name':'jason','age':18},
{'id':2,'name':'jason','age':18},
{'id':3,'name':'egon','age':18}
]
拓展: ORM框架的对象关系映射让不懂SQL语句的人也能够非常牛逼的操作数据库
表 -- 映射成--> 类型
一条条的数据 -- 映射成--> 对象
字段对应的值 -- 映射成--> 对象的属性
实现原理:
你在写类就意味着在创建表
你用类生成对象就意味着在创建数据
你对象点属性就是在获取数据字段对应的值
目的: 就是减轻python程序员的压力只需要会python面向对象的知识点就可以操作MySQL
"""
select distinct id,age from emp; # 注意: 去除被设置成主键的id
select distinct age from emp;
# 当前语句的执行优先级
from -> select -> distinct
# 实际语句的执行优先级
from -> where -> group by -> having -> select -> distinct -> order by -> limit
八. 查询排序: order by
1. 操作
"""
order by默认升序.
默认升序: 后面可以指定asc,可以省略不写
指定降序: desc
指定多种, 前者相等则按照后者的顺序: age desc,salary asc;
"""
select * from emp order by salary; # 升序
select * from emp order by salary asc; # 升序
select * from emp order by salary desc; # 降序
# 先按照age降序排, 如果碰到age相同, 则再按照salary升序排.
select * from emp order by age desc,salary;
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) > 10000
order by avg(salary) desc;
select post,avg(salary) as avg_salary,group_concat(name, ':', age) from emp
where age>10
group by post
having avg(salary)>10000
order by avg_salary desc; # 注意: 这里为什么可以使用avg_salary, 是因为执行语句的优先级select的优先级高于avg_salary. 当select语句执行完毕avg(salary)被改名成了avg(salary), 所以接着执行order by语句就可以拿到avg_salary去进行排序.
# 当前语句的执行优先级
from -> where -> group by -> having -> select -> order by
# 实际语句的执行优先级
from -> where -> group by -> having -> select -> distinct -> order by -> limit
2. 小练习
# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from emp order by age asc,hire_date desc;
# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) as avg_salary from emp group by post having avg(salary)>10000 order by avg_salary asc;
# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) as avg_salary from emp group by post having avg(salary)>10000 order by avg_salary desc;
九. 限制查询的记录数: limit
1. 操作
"""
目的: 针对数据过多的情况我们通常都是做分页处理
limit 5,5:
第一个参数是起始位置
第二个参数是展示条数
"""
select * from emp limit 3; # 只展示3条数据
select * from emp limit 0,5; # 第0条开始再往后取5条数据
select * from emp limit 5,5; # 第6条开始再往后取5条数据
# 当前语句的执行优先级
from -> select -> limit
# 实际语句的执行优先级
from -> where -> group by -> having -> select -> distinct -> order by
2. 小练习
# 分页显示，每页5条
select * from emp limit 5;
select * from emp limit 5,5;
select * from emp limit 10,5;
select * from emp limit 15,5;
十. 使用正则表达式查询
select * from emp where name regexp '^j.*(n|y)$' # 匹配j开头, n或者y结尾的所有字符.
