基本查询语句及方法
基本查询语句及方法
查询的出的表相当于Excel中刷选操作，刷选出的是虚拟的表，展示出来
1、查询语句的基本操作
- select
- from
- where
- group by
- having
- distinct
- order by
- limit
- 聚合函数：count，max，min，avg，sum
2、单表查询
select * from emp; # 若数据比较多，比较凌乱，可以在表后面+ \G
select * from emp \G
写SQL语句必须遵循两点：
书写顺序：
select
from
where
执行顺序：
from
where
select
1、where ：约束条件
# 创建一张部门表
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
('Mr沈','male',17,'20170301','admin',7300.33,401,1), # 以下是教学部
('jerry','female',18,'20110211','teacher',9000,401,1),
('tank','male',18,'19000301','teacher',30000,401,1),
('sean','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',7000.13,402,2),# 以下是销售部门
('丫丫','female',38,'20101101','sale',5000.35,402,2),
('丁丁','female',18,'20110312','sale',8000.37,402,2),
('星星','female',18,'20160513','sale',6000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),
('程咬金','male',28,'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、查询id大于等于3，小于等于6的数据（and、between）
select * from emp where id >= 3 and id <= 6;
select * from emp where id between 3 and 6;
2、查询薪资是20000或者18000或者17000的数据（or、in）
select * from emp where salary=20000 or salary=18000 or salary=17000;
select * from emp where salary in (20000,18000,17000);
3、查询员工姓名中包含e字母 的 员工姓名 和 薪资
模糊匹配：like
%：匹配0个或多个任意字符
_：匹配一个任意字符
select name,salary from emp where name like '%e%';
4、查找名字个数为4个的员工 名字 与 薪资
char_length(name)：计算名字的字符长度
select name,salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;
5、查询id小于3或者大于6的数据（not in）
select * from emp where id not in (3,4,5,6);
select * from emp where id not between 3 and 6;
6、查询岗位描述为空的 员工名 与 岗位名 post_comment
针对查找null的值需要用is，如果用=会查不到数据
select name,post from emp where post_comment is null;
2、group by
书写顺序：select - from - where - group by
执行顺序：from - where - group by - select
1、根据部门分组
非严格模式下可以获取分组以外的字段数据，严格模式会报错
select post, salary from emp group by post;
设置严格模式:
查看模式：show variables like "%mode%";
全局设置严格模式: set global sql_mode="strict_trans_tables,only_full_group_by";
在严格模式下，只能获取分组内 的字段，可以同聚合函数一起使用，间接获取其他字段数据
聚合函数：必须跟在group by 后面（执行顺序）
count()：计数，括号中可以填任意非空值
max()：最大值
min()：最小值
avg()：平均值
sum()：求和
2、获取每个部门的最高工资
as ：别名，可以给字段加一个别名，可以简写不写as，但是不推荐使用
select post,max(salary) from emp group by post;
select post as '部门',max(salary) as '最高薪资' from emp group by post;
select post '部门',max(salary) '最高薪资' from emp group by post;
3、获取每个部门的最低工资
select post,min(salary) from emp group by post;
4、获取每个部门的平均工资
select post,avg(salary) from emp group by post;
5、获取每个部门的工资总和
select post,sum(salary) from emp group by post;
6、每个部门的员工个数
select post,count(id) from emp group by post;
7、查询 "岗位名" 以及 岗位包含的所有员工名字 一行展示
group_concat(name)：可以将分组后的所有名字获取并进行拼接，默认以 ，拼接
可以设置指定以什么拼接
select post,group_concat(name) from emp group by post;
select post,group_concat('名字：',name) from emp group by post;
8、统计各部门年龄在30岁以下的员工平均工资
select post,avg(salary) from emp where age<30 group by post;
3、having：过滤
having与where的作用是一样的，having必须跟在group by后面，having后面可以跟聚合函数，但是where后面不能跟聚合函数
书写顺序：
select - from - where - group by - having
执行顺序：
from - where - group by - having - select
统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于6000的部门
select post,avg(salary) from emp where age>30 group by post having avg(salary)>6000;
4、distinct：去重
书写顺序：
select - distinct - from - where - group by - having
执行顺序：
from - where - group by - having - distinct - select
distinct 后面跟着去重的字段名
注意：去重的字段名，必须是重复的，只要有不重复的字段，后续就无法去重
将重复的部门数据去重
select distinct post from emp;
select distinct post,id from emp; # id没有重复的，所以都不会去重
5、order by ：排序
书写顺序：
select - from - where - group by - having - order by
执行顺序：
from - where - group by - having - select - order by
asc：升序
desc：降序
根据薪资进行降序
select salary from emp order by salary desc;
统计各部门年龄在20岁以上的员工平均工资，并且保留平均工资大于7000的部门，然后对平均工资进行升序
select post,avg(salary) from emp where age>20 group by post having avg(salary)>7000 order by avg(salary) asc;
6、limit ：限制查询记录的数量
书写顺序：
select - from - order by - limit
执行顺序：
from - select - order by - limit
limit可以有两个参数，参数1：是限制的开始位置+1，参数2：是开始位置展示的条数
1、获取从第一条开始，获取4条数据
select * from emp limit 4;
2、查询从第五条开始，获取4条数据
select * from emp limit 4,4;
3、查询工资最高的人的详细信息
select * from emp order by salary desc limit 1;
7、regexp：正则（编程中凡是看到reg开头，基本都是与正则有关）
查询名字中以程开头，金银铜铁结尾的名字所有信息
select * from emp where name regexp '^程.*(金|银|铜|铁)$';
3、多表查询
创建表
#建表dep2
create table dep2(
id int,
name varchar(20)
);
#建表emp2
create table emp2(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep2 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into emp2(name,sex,age,dep_id) values
('小王','male',17,200),
('小李','female',48,201),
('小张','male',38,201),
('小沈','female',28,202),
('狗蛋','male',18,200),
('丫丫','female',18,204);
上篇讲了如何根据表关系对字段进行拆分，目的是为了更好的管理，表数据都存放在硬盘中，存不是目的，目的是为了取，所以我们将数据从硬盘读到内存中，我们将其拼接查询的更加的合理
左表的一条记录与右表一条记录都对应，一般称为 “ 笛卡尔积 ”
左右表中的数据基本不可能全部一一对应的，所以我们可以提取出有对应关系，合理的数据，我们所需要的数据即可，就有了将两张表关联到一起的方法
1、inner join 内连接：只取两张表有对应关系的记录
1、查询员工以及所在部门的信息
select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;
2、查询部门为技术部的员工及部门信息
select * from emp2 inner join dep2 on emp2.dep_id = dep2.id and dep2.name='技术';
2、left join 左连接：在内连接的基础上保留左表没有对应关系的记录
连接保留左表中所有的数据，右表没有与之对应的就会以null补充
select * from emp2 left join dep2 on emp2.dep_id = dep2.id;
3、right join 右连接：在内连接的基础上保留右表所有的数据
保留右表所有的数据，左表没有的数据会以null填充
select * from emp2 right join dep2 on emp2.dep_id = dep2.id;
4、union 全连接：在内连接的基础上保留左右俩表所有的数据，没有与之对应的会以null补充
select * from emp2 left join dep2 on emp2.dep_id = dep2.id
union
select * from emp2 right join dep2 on emp2.dep_id = dep2.id;
4、子查询
子查询就是将一个查询语句的结果用括号括起来，当做另一个查语句的条件去用
1、查询部门是技术或者人力资源的员工信息
select * from emp2 where dep_id in (select id from dep2 where name='技术' or name='人力资源');
2、查询每个部门最新入职的员工
- 先子查询获取emp表中 部门名称与最新入职的时间字段值，生成一张虚拟表
- 拼接emp表和刚生成的部门名与最新入职的时间字段值
- 判断两张表的时间相等的
- 查询出所需的所有信息
select t1.* from
emp as t1 inner join
(select post,max(hire_date) as max_date from emp group by post) as t2
on t1.post = t2.post
where t1.hire_date = t2.max_date;
