本节内容：
一、单表查询
二、多表查询
三、子查询
一、 单标查询
1、where 条件的使用
功能:对表中的数据进行筛选和过滤
语法:
1.判断的符号
> < >= <= = !=( <>不等于 )
2.拼接不同条件的关键字
and or not
3.查询区间值
between 小值 and 大值 [小值,大值] 查询两者之间的范围
4.查询区间值
id in (1,2,3,4,5,6)
5.模糊查询 like %通配符 _通配符
like "%b" 匹配以b结尾的任意长度字符串
like "a%" 匹配以a开头的任意长度字符串
like "%c%" 匹配字符串中含有c的任意长度字符串
like "__d" 匹配总长度为3位,而且以d结尾的字符串
like "e__" 匹配总长度为3位,而且以e开头的字符
1 #创建表 2 create table employee( 3 id int not null unique auto_increment, 4 emp_name varchar(20) not null, 5 sex enum(‘male‘,‘female‘) not null default ‘male‘, #大部分是男的 6 age int(3) unsigned not null default 28, 7 hire_date date not null, 8 post varchar(50), 9 post_comment varchar(100), 10 salary double(15,2), 11 office int, #一个部门一个屋子 12 depart_id int 13 ); 14 15 16 #三个部门：教学，销售，运营 17 insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values 18 (‘egon‘,‘male‘,18,‘20170301‘,‘老男孩驻沙河办事处外交大使‘,7300.33,401,1), #以下是教学部 19 (‘alex‘,‘male‘,78,‘20150302‘,‘teacher‘,1000000.31,401,1), 20 (‘wupeiqi‘,‘male‘,81,‘20130305‘,‘teacher‘,8300,401,1), 21 (‘yuanhao‘,‘male‘,73,‘20140701‘,‘teacher‘,3500,401,1), 22 (‘liwenzhou‘,‘male‘,28,‘20121101‘,‘teacher‘,2100,401,1), 23 (‘jingliyang‘,‘female‘,18,‘20110211‘,‘teacher‘,9000,401,1), 24 (‘jinxin‘,‘male‘,18,‘19000301‘,‘teacher‘,30000,401,1), 25 (‘成龙‘,‘male‘,48,‘20101111‘,‘teacher‘,10000,401,1), 26 27 (‘歪歪‘,‘female‘,48,‘20150311‘,‘sale‘,3000.13,402,2),#以下是销售部门 28 (‘丫丫‘,‘female‘,38,‘20101101‘,‘sale‘,2000.35,402,2), 29 (‘丁丁‘,‘female‘,18,‘20110312‘,‘sale‘,1000.37,402,2), 30 (‘星星‘,‘female‘,18,‘20160513‘,‘sale‘,3000.29,402,2), 31 (‘格格‘,‘female‘,28,‘20170127‘,‘sale‘,4000.33,402,2), 32 33 (‘张野‘,‘male‘,28,‘20160311‘,‘operation‘,10000.13,403,3), #以下是运营部门 34 (‘程咬金‘,‘male‘,18,‘19970312‘,‘operation‘,20000,403,3), 35 (‘程咬银‘,‘female‘,18,‘20130311‘,‘operation‘,19000,403,3), 36 (‘程咬铜‘,‘male‘,18,‘20150411‘,‘operation‘,18000,403,3), 37 (‘程咬铁‘,‘female‘,18,‘20140512‘,‘operation‘,17000,403,3) 38 ;
1 # 1. 查询部门是sale的所有员工姓名: 2 select emp_name from employee where post="sale" 3 4 # 2. 部门是teacher , 收入大于10000的所有数据 5 select * from employee where post="teacher" and salary > 10000; 6 7 # 3. 收入在1万到2万之间的所有员工姓名和收入 8 select emp_name,salary from employee where salary between 10000 and 20000; 9 10 # 4. 收入不在1万到2万之间的所有员工姓名和收入 11 select emp_name,salary from employee where salary not between 10000 and 20000; 12 13 # 5. 查看岗位描述为NULL的员工信息 14 select * from employee where post_comment is null; 15 update employee set post_comment = "" where id = 1; 16 select * from employee where post_comment = ""; 17 # 查看岗位描述不为NULL的员工信息 18 select * from employee where post_comment is not null; 19 20 # 6. 查询收入是3000 ,4000 ,5000,8300 所有员工的姓名和收入 21 select emp_name,salary from employee where salary in(3000,4000,5000,8300); # (推荐) 22 select emp_name,salary from employee where salary = 3000 or salary = 4000 or salary = 5000 or salary = 8300; 23 24 # 查询收入不是3000 ,4000 ,5000,8300 所有员工的姓名和收入 25 select emp_name,salary from employee where salary not in(3000,4000,5000,8300); # (推荐) 26 27 # 7. 以on结尾的员工名搜一下 28 select emp_name from employee where emp_name like "%on"; 29 select emp_name from employee where emp_name like "wu%"; 30 select emp_name from employee where emp_name like "%le%"; 31 select emp_name from employee where emp_name like "al__"; 32 select emp_name from employee where emp_name like "%alex%"; 33 34 # 8. 统计员工一年的年薪 35 select concat("姓名:",emp_name,"收入:",salary) from employee; 36 # + - * / 四则运算 37 select concat("姓名:",emp_name,"收入:",salary * 12 ) from employee; 38 # 语法: concat_ws(拼接符号,字段1,字段2,字段3 .... ) 39 select concat_ws(" : ",emp_name,salary * 12 ) from employee; 40 41 # 9. 查询部门的种类 42 # distinct 去重 43 select distinct(post) from employee
2、 group by 子句 分类,分组
注意点: 针对于当前表,by谁搜谁
select sex from employee group by sex select emp_name from employee group by sex # error # group_concat 按照分组把对应的字段拼接在一起 select group_concat(emp_name) from employee group by sex;
# 聚合函数 # count 统计数量 * 号代表所有 select count(*) from employee # max 统计最大值 select max(salary) from employee; # min 统计最大值 select min(salary) from employee; # avg 统计平均值 select avg(salary) from employee; # sum 统计总和 select sum(salary) from employee;
1 #练习:group 分类 2 # 1. 查询部门名以及各部门的平均薪资 3 select avg(salary),post from employee group by post 4 5 # 2. 查询部门名以及各部门的最高薪资 6 select max(salary),post from employee group by post 7 8 # 3. 查询部门名以及各部门的最低薪资 9 select min(salary),post from employee group by post 10 11 # 4. 查询公司内男员工和女员工的个数 12 select count(*),sex from employee group by sex 13 14 # 5. 查询部门名以及部门包含的所有员工名字 15 select group_concat(emp_name),post from employee group by post; 16 17 # 6. 可以group by 两个字段,by谁搜谁; 18 select emp_name,post from employee group by post,emp_name;
3、.having 对分类后的数据进行二次过滤[应用在group by这个场景里]
1 # 找出各部门平均薪资,且大于10000 2 select post,avg(salary) from employee group by post having avg(salary) > 10000 3 4 # 1.查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数 5 select group_concat(emp_name),post,count(*) from employee group by post having count(*) > 2 6 7 # 2.查询各岗位平均薪资小于10000的岗位名、平均工资 8 select post,avg(salary) from employee group by post having avg(salary) < 10000 9 10 # 3.查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资 11 select post,avg(salary) from employee group by post having 10000 < avg(salary)< 20000 error[没有搜到想要的结果] 12 select post,avg(salary) from employee group by post having 10000 < avg(salary) and avg(salary) < 20000 13 # 10000 <= avg(salary) <= 20000 14 select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;
4、.order by 排序
- 正序 升序 asc
- 倒序 降序 desc
#练习:order by select * from employee order by age; select * from employee order by age asc;(默认升序) select * from employee order by age desc;(默认升序)
1 # 1. 查询所有员工信息，先按照age升序排序，如果age相同则按照 hire_date 降序排序 2 select * from employee order by age asc , hire_date desc; 3 # 2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列 4 select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) 5 # 3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列 6 select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
5、.limit 限制查询的条数
limit m,n m代表从第几条搜索数据 , n 代表搜索几条 m=0 代表搜索第一条数据
# 分页 select * from employee limit 0,10 # 0代表第一条 ,往后搜10条数据 select * from employee limit 10,10 # 10代表第11条,往后搜10条数据 select * from employee limit 20,10 # 20代表第21条,往后搜10条数据
# limit 数字 代表搜索条数 select * from employee limit 1; # 搜索表里面最后一条数据 select * from employee order by id desc limit 1; # 搜索表里面最后三条数据 select * from employee order by id desc limit 3;
6、(了解) 可以使用正则表达式 (不推荐使用)
select * from employee where emp_name regexp ".*n$"; #?号不识别 select * from employee where emp_name regexp "程咬.*";
二、多表查询
1 #建表 2 create table department( 3 id int, 4 name varchar(20) 5 ); 6 7 create table employee( 8 id int primary key auto_increment, 9 name varchar(20), 10 sex enum(‘male‘,‘female‘) not null default ‘male‘, 11 age int, 12 dep_id int 13 ); 14 15 #插入数据 16 insert into department values 17 (200,‘技术‘), 18 (201,‘人力资源‘), 19 (202,‘销售‘), 20 (203,‘运营‘); 21 22 insert into employee(name,sex,age,dep_id) values 23 (‘egon‘,‘male‘,18,200), 24 (‘alex‘,‘female‘,48,201), 25 (‘wupeiqi‘,‘male‘,38,201), 26 (‘yuanhao‘,‘female‘,28,202), 27 (‘liwenzhou‘,‘male‘,18,200), 28 (‘jingliyang‘,‘female‘,18,204) 29 ;
1、内联查询(内联接)
inner join 至少两表以上做查询,把满足条件的所有数据查询出来(查询的是共同拥有的数据)
- select 字段 from 表1 inner join 表2 on 必要的关联字段 (2张表)
- select 字段 from 表1 inner join 表2 on 必要的关联字段1 inner join 表3 on 必要的关联字段2 ... inner join ...
# 语法: select * from employee inner join department on employee.dep_id = department.id ; # as 起别名 (推荐) select * from employee as e inner join department as d on e.dep_id = d.id ; # as 可以省略 select * from employee e inner join department d on e.dep_id = d.id ; # where 写法默认等价于inner join 也是内联查询 select * from employee , department where employee.dep_id = department.id ; select * from employee as e, department as d where e.dep_id = d.id ;
2、外联查询(外联接)
# (1) left join (左联接) : 以左表为主,右表为辅,完整查询左表所有数据,右表没有的数据补null select * from employee left join department on employee.dep_id = department.id ; # (2) right join (右联接): 以右表为主,左表为辅,完整查询右表所有数据,左表没有的数据补null select * from employee right join department on employee.dep_id = department.id ;
3、全联查询(全联接) left join + right join
select * from employee left join department on employee.dep_id = department.id union select * from employee right join department on employee.dep_id = department.id ;
三、子查询
子查询 : sql语句的嵌套
(1) sql语句当中嵌套另外一条sql,用括号()包起来,表达一个整体;
(2) 一般用在子句的后面 比如from , where ...身后 表达一个条件或者一张表
(3) 速度快慢 : 单表查询 > 联表查询 > 子查询
1 #建表 2 create table department( 3 id int, 4 name varchar(20) 5 ); 6 7 create table employee( 8 id int primary key auto_increment, 9 name varchar(20), 10 sex enum(‘male‘,‘female‘) not null default ‘male‘, 11 age int, 12 dep_id int 13 ); 14 15 #插入数据 16 insert into department values 17 (200,‘技术‘), 18 (201,‘人力资源‘), 19 (202,‘销售‘), 20 (203,‘运营‘); 21 22 insert into employee(name,sex,age,dep_id) values 23 (‘egon‘,‘male‘,18,200), 24 (‘alex‘,‘female‘,48,201), 25 (‘wupeiqi‘,‘male‘,38,201), 26 (‘yuanhao‘,‘female‘,28,202), 27 (‘liwenzhou‘,‘male‘,18,200), 28 (‘jingliyang‘,‘female‘,18,204) 29 ;
1、基本用法
1、找出平均年龄大于25岁以上的部门
# where select department.id,department.name from employee,department where employee.dep_id = department.id group by department.id,department.name having avg(age) > 25;
# 用as 起别名 select d.id,d.name from employee as e,department as d where e.dep_id = d.id group by d.id,d.name having avg(age) > 25;
# inner join select d.id,d.name from employee as e inner join department as d on e.dep_id = d.id group by d.id,d.name having avg(age) > 25;
2、子查询练习
1.找平均年龄大于25岁以上的部门id
select dep_id from employee group by employee.dep_id having avg(age) > 25 # 2.通过id上 department 表里面找部门名 select name from department where id in(201,202); # 3.综合拼接 select id,name from department where id in(select dep_id from employee group by employee.dep_id having avg(age) > 25);
2.查看技术部门员工姓名
1 # where 2 select 3 e.name 4 from 5 employee as e , department as d 6 where 7 e.dep_id = d.id 8 and 9 d.name = "技术" 10 11 # inner join 12 select 13 e.name 14 from 15 employee as e inner join department as d on e.dep_id = d.id 16 where 17 d.name = "技术" 18 19 20 # 子查询 21 # (1) 通过技术部门找id 22 select id from department where name = "技术" 23 # (2) 通过id 找员工姓名 24 select name from employee where dep_id = 200; 25 # (3) 综合拼接 26 select name from employee where dep_id = (select id from department where name = "技术");
3.查看哪个部门没员工
1 # 联表查询 2 select 3 d.id,d.name 4 from 5 department as d left join employee as e on d.id = e.dep_id 6 where 7 e.id is null 8 9 # 子查询 10 # 1.找员工都在哪些部门 11 select dep_id from employee group by dep_id 12 13 # 2把不在该部门的员工找出来 14 select id from department where id not in (200,201,202,204); 15 16 # 综合拼接 17 select id , name from department where id not in (select dep_id from employee group by dep_id);
4.查询大于平均年龄的员工名与年龄
1 select name , age from employee where age > 28 2 # 计算平均年龄 3 select avg(age) from employee; 4 # 综合拼接 5 select name , age from employee where age > (select avg(age) from employee);
5.把大于其本部门平均年龄的员工名和姓名查出来
1 # 1.先计算本部门的平均年龄是多少 2 select dep_id,avg(age) from employee group by dep_id 3 # 2.把搜索出来的数据和employee表进行联表,最后做单表查询 4 select 5 * 6 from 7 employee as t1 inner join (1号sql查询出来的数据) as t2 on t1.dep_id = t2.dep_id 8 9 # 3.综合拼接 10 select 11 * 12 from 13 employee as t1 inner join (select dep_id,avg(age) from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id 14 15 # 4.做单表查询 16 select 17 t1.name 18 from 19 employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id 20 where 21 t1.age > t2.avg_age
6.查询每个部门最新入职的那位员工 # 利用上一套数据表进行查询;
1 # 1.找每个部门 hire_date 字段的最大值(即是最新入职的员工) 2 select max(hire_date) as max_date , post from employee group by post 3 # 2.把 employee 和 子查询搜出的最大日期做联表,合并成大表之后,在做单表查询; 4 select 5 * 6 from 7 employee as t1 inner join (1号查询出来的数据) as t2 on t1.post = t2.post 8 9 # 3.综合拼接 10 select 11 * 12 from 13 employee as t1 inner join (select max(hire_date) as max_date , post from employee group by post) as t2 on t1.post = t2.post 14 15 # 4.最后做单表查询 16 select 17 t1.emp_name,t1.hire_date 18 from 19 employee as t1 inner join (select max(hire_date) as max_date , post from employee group by post) as t2 on t1.post = t2.post 20 where 21 t1.hire_date = t2.max_date
7 总结:
子查询可以作为临时表,也可以作为where子句的条件,通过()包括sql,表达一个整体;
一般用在各个子句后面 select .. from ... where ...
思路:
可以把临时搜索出来的数据变成临时表,在和其他表做联表,最后做单表查询;
3.带EXISTS关键字的子查询
exists 关键字,表达数据是否存在,用在子查询里
如果内层sql 能够查到数据,返回True ,外层sql执行sql语句
如果内层sql 不能够查到数据,返回False ,外层sql不执行sql语句
select * from employee where exists (select * from employee where id = 1); select * from employee where exists (select * from employee where id = 100);