MySQL 之单表查询
数据库数据：
# (1) 创建数据表
create table employee(
id int primary key auto_increment,
emp_name char(12) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post char(15),
post_comment varchar(100),
salary float(15,2),
office int, #一个部门一个屋子
depart_id int
);
# (2) 插入数据
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('xiaoyang','male',18,'20170301','独立安保部',7300.33,401,1), #以下是教学部
('laozhang','male',78,'20150302','teacher',1000000.31,401,1),
('xiaowu','male',81,'20130305','teacher',8300,401,1),
('daniu','male',73,'20140701','teacher',3500,401,1),
('laowu','male',28,'20121101','teacher',2100,401,1),
('xiaowang','female',18,'20110211','teacher',9000,401,1),
('zzdu','male',18,'19000301','teacher',30000,401,1),
('xiaolong','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.对表当中的数据进行筛选
#2.对值判断用:
= > < >= <= != <>(不等于)
#3.判断一个的范围
between 小值 and 大值 [小值,大值] 在两者之间的范围
in(值1,值2,值3....) 在括号里这个范围之内
#4.like 模糊查询 like '%' 通配符 匹配任意长度任意字符
like '%a' 以a结尾的任意长度字符串
like 'a%' 以a开头的任意长度字符串
like '%a%' 匹配中间带个a的字符串
like '_a' 一共2个长度,以a结尾,字符无所谓
like 'a__' 一共3个长度,以a开头,后面字符无所谓
#5.多个条件的拼接
and 两个链接都成立
or 有一个条件成立
not 非
# (1) 单条件查询:
# 查询部门是sale的所有员工姓名:
select emp_name from employee where post = 'sale';
# (2)多条件查询
# 部门是teacher , 收入大于10000
select * from employee where post="teacher" and salary>10000;
# (3)关键字 between .. and ..
# 收入在1万和2万之间的姓名和收入
select emp_name,salary from employee where salary between 10000 and 20000;
# 收入不再1万和2万之间的
select emp_name,salary from employee where salary not between 10000 and 20000;
# (4) 关键字is null(判断某个字段是否为null 不能用等号 用is)
#查询post_comment 是空的
select emp_name,post_comment from employee where post_comment is null;
#查询post_comment 不是空的
select emp_name,post_comment from employee where post_comment is not null;
#查询post_comment 是空字符串
select emp_name,post_comment from employee where post_comment = "";
# 设置一个值是空;
update employee set post_comment= '' where id = 2;
# (5) 关键字in集合查询
# 查收入是3000 或者3500或者4000或者9000所有的员工和收入
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);
# 不再这个范围里 float 的精度判断?
select emp_name,salary from employee where salary not in (3000,3500,4000,9000,3000.99);
# (6) 关键字like的模糊查询
## (1) 通配符'%' [表示任意字符串]
select * from employee where emp_name like 'eg%';
## (2) 通配符'_' [表示一个任意字符]
select * from employee where emp_name like 'al__';
# (7) sql 函数concat(参数1,参数2,参数3) 把所有参数拼接在一起
# concat_ws("符号",参数1,参数,参数3.....)
# sql 中可以做四则运算(加减乘除)
# as 用来起别名
select emp_name,concat("姓名:",emp_name,"薪水:",salary) as my_s from employee;
select emp_name,concat("姓名:",emp_name,"薪水:",salary*12) as my_s from employee;
# 第一个参数是分隔符,后面依次写上要拼接的值;
select emp_name,concat_ws(" : ",emp_name,salary*12) as my_s from employee;
二.group by 分组
有几个种类就有几个数据
'''group by 分类 by后面的字段一般写在select 后面不会错'''
# select post from employee where depart_id > 1 group by post;
select office from employee where depart_id > 1 group by office;
# 函数: group_concat() 对分组的内容进行拼接;
select group_concat(emp_name),post from employee where depart_id >1 group by post
# 聚合函数:
# count(*) : 统计总数
select count(*) from employee;
select count(*) from employee where depart_id = 1;
# 统计最大值 max
select max(salary) from employee;
# 统计最小值 min
select min(salary) from employee;
# 统计平均值 avg
select avg(salary) from employee;
# 统计总和 sum
select sum(salary) from employee;
# 分组 + 聚合函数 一起使用
# 求各部门的平均工资
select post,avg(salary) from employee group by post;
# 求各部门薪资的最大值
select post,max(salary) from employee group by post;
三.having
查完数据后再过滤:配合group by使用,主要用于分组之后的在过滤
'''执行过程:先执行where 在执行group by 等数据搜出来了再用having过滤的;'''
# 比如:求部门的平均薪资,在10000以上的所有部门
select post from employee group by post having avg(salary) > 10000;
# 1查询各岗位内包含的员工个数小于2的 岗位名、岗位内包含员工名字、个数
select post,group_concat(emp_name),count(*) from employee group by post having count(*) <2;
# 2.查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000;
#3查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
#(1)
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;
#(2)
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
"""
select * from biao where 条件 group by 字段 having 条件表达式 order by 字段 limit
"""
四.order by
按照什么字段排序
# 默认升序asc 从小到大排序
select emp_name,age from employee where post = "teacher" order by age;
# 降序 desc 从大到小排序
select emp_name,age from employee where post = "teacher" order by age desc;
五.limit
限制查询的次数;[用来做数据分页的]
# 查询最后一条数据
select * from employee order by id desc limit 1;
select * from employee order by id desc limit 3;
# limit(m,n) 默认m的值是0 代表第一条数据,n所代表的是查询几条,从的m+1条数据开始,查询n条数据;
select * from employee limit 0,5
select * from employee limit 5,5
select * from employee limit 10,5
六.使用正则表达式查询数据
(了解,基本不用,效率非常低,与python正则部分结果不一样)
select * from employee where emp_name regexp '^ale.*';
select * from employee where emp_name regexp 'on$';
select * from employee where salary regexp '0{3}';
# 用like来取代
select * from employee where emp_name like 'da%';
七.练习
# 练习:where
# 1. 查看岗位是teacher的员工姓名、年龄
select emp_name,age from employee where post = 'teacher';
# 2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select emp_name,age from employee where post = 'teacher' and age > 30;
# 3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
# 法一:
select emp_name,age,salary from employee where post = 'teacher' and salary in (9000,10000);
#法二:
select emp_name,age,salary from employee where post = 'teacher' and salary between 9000 and 10000;
# 4. 查看岗位描述不为NULL的员工信息
select * from employee where post_comment is not null;
# 5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select emp_name,age,salary from employee where post = 'teacher' and salary in (10000,9000,3000);
# 6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select emp_name,age,salary from employee where post = 'teacher' and salary not in (10000,9000,3000);
# 7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select emp_name,salary*12 from employee where emp_name like 'zz%';
#练习:group
# 1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(emp_name) from employee group by post;
# 2. 查询岗位名以及各岗位内包含的员工个数
select post,count(*) from employee group by post;
# 3. 查询公司内男员工和女员工的个数
select sex,count(*) 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
#1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(emp_name),count(*) from employee group by post having count(*) < 2;
#2.查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000;
#3查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;
#练习:order by
# 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from employee order by age,hire_date desc;
# 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary);
# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
