MySQL 数据库 查询语句的基本操作,单表查询,多表查询

1.查询语句的基本操作
　　- select
　　- from
　　- where
　　- group by
　　- having
　　- distinct
　　- order by
　　- limit
　　- 聚合函数: count, max, min, avg, sum

 

2.单表查询:

#前期表与数据准备
# 创建一张部门表
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
('tank','male',17,'20170301','张江第一帅形象代言部门',7300.33,401,1), # 以下是教学部
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('jason','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('大饼','male',18,'19000301','teacher',30000,401,1),
('sean','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);

# PS：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk

　　- select * from emp; # 若数据比较多，比较凌乱，可以在表后面+ \G
　　- select * from emp\G

 

 

- 重点(*******):
写SQL语句必须遵循两点:
- 书写顺序:
　　# 获取id为 4、5的两条记录
　　select * from emp where id > 3 and id < 6;

　　- select
　　- from
　　- where

- 执行顺序:

比如: 图书管理员，得先找到是哪一个图书馆(哪张表),
再找这本书在图书馆的哪个位置(哪一条记录), 最后查找这个本书中某一页(哪些字段值);

　　select * from emp where id > 3 and id < 6;
　　- from ---> 找到图书馆
　　- where ---> 找到书的位置
　　- select ---> 找到书本中的某一页

注意: 必须记住SQL语句的 书写顺序 与 执行顺序(*******);

 

- where: 约束条件
# 1.查询id大于等于3，小于等于6的数据
# and: 与
　　select * from emp where id >= 3 and id <= 6;
# between: 在什么什么之间
　　select * from emp where id between 3 and 6;

# 2.查询薪资是20000或者18000或者17000的数据
　　select * from emp where salary = 20000 or salary = 18000 or salary = 17000;
# in: 在什么什么中
　　select * from emp where salary in (20000, 18000, 17000);

# 3.查询员工姓名中包含o字母 的 员工姓名 和 薪资
# 模糊匹配: like
# %: 匹配多个任意的字符
# _: 匹配一个任意的字符

　　select name, salary from emp where name like '%o%';

# 4.查找名字个数为4个的员工 名字 与 薪资
　　select name, salary from emp where name like '____';
# char_length(name): 计算名字字符的长度
　　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.查询薪资不在20000，18000，17000范围的数据
　　select * from emp where salary not in (20000, 18000, 17000);

# 7.查询岗位描述为空的 员工名 与 岗位名 post_comment
# 注意: 针对null的值 需要使用 is
　　select name, post from emp where post_comment = null;
　　select name, post from emp where post_comment is null;

- group by: 分组
- 书写顺序:
　　- select
　　- from
　　- where
　　- group by

- 执行顺序:
　　- from
　　- where
　　- group by
　　- select

# 1.根据部门分组
# 非严格模式下可以获取 分组条件post 以外的字段数据
　　select post, salary from emp group by post;

# 设置严格模式:
　　show variables like "%mode%";
# 全局设置: 永久有效
　　set global sql_mode="strict_trans_tables,only_full_group_by";

# 在严格模式下只能 “直接” 获取 分组条件post 字段;
# 可以同聚合函数，间接获取其他字段数据
　　select post, salary from emp group by post; # 报错

　　select post from emp group by post;

聚合函数:
count: 计数
max: 最大值
min: 最小值
avg: 平均值
sum: 求和

2.获取每个 部门 的最高工资
# 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;

每个部门的最低工资
　　select post, min(salary) from emp group by post;

每个部门的平均工资
　　select post, avg(salary) from emp group by post;

每个部门的工资总和
　　select post, sum(salary) from emp group by post;

每个部门的员工个数
# count(): 括号中可以填任意非空值
　　select post, count(salary) from emp group by post;
　　select post, count(post_comment) from emp group by post;

总结: 聚合函数，必须跟在group by 后面(执行顺序);

练习题:
1. 查询 "岗位名" 以及 岗位包含的所有员工名字 一行展示
# group_concat(name): 可以将分组后的 所有名字获取并进行拼接
# 默认以, 拼接
　　select post, group_concat(name) from emp group by post;
# 指定以:拼接
　　select post, group_concat(name, ':') from emp group by post;
　　select post, group_concat('Name: ', name) from emp group by post;

2. 查询岗位名以及各岗位内包含的员工个数
　　select post, count(id) from emp group by post;

3. 查询公司内男员工和女员工的个数
　　select sex, count(*) from emp group by sex;

4. 查询岗位名以及各岗位的平均薪资
5. 查询岗位名以及各岗位的最高薪资
6. 查询岗位名以及各岗位的最低薪资

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
　　select sex, avg(salary) from emp group by sex;

8.统计各部门年龄在30岁以上的员工平均工资:
# 步骤: 先找到表，再找年龄30岁以上，再根据部门分组，最后求平均薪资;
　　select post, avg(salary) from emp where age > 30 group by post;

 

 

- having: 过滤
- having与where的作用是一样的
- having必须跟在group by 后面
- having后面可以跟聚合函数，但where后面不能跟聚合函数;

- 书写顺序:
　　- select
　　- from
　　- where
　　- group by
　　- having

- 执行顺序:
　　- from
　　- where
　　- group by
　　- having
　　- select

1、统计各 部门 年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门;
　　select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

 

 

- 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;

 

- order by: 排序

- 书写顺序:
　　- select
　　- from
　　- where
　　- group by
　　- having
　　- order by

- 执行顺序:
　　- from
　　- where
　　- group by
　　- having
　　- select
　　- order by

# order by 默认升序
# 1、根据薪资进行升序
# asc升序
# desc降序
　　select salary from emp order by salary asc;

# 2、根据年龄进行降序排列
　　select age from emp order by age desc;

# 3、先按照age升序，再按照salary降序
　　select age, salary from emp order by age asc, salary desc;

# 4、统计 各部门(分组) 年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行升序序
　　select post, avg(salary) from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary) asc;
　　select post, avg(salary) from emp where age > 18 group by post having avg(salary) > 1000 order by avg(salary) asc;

 

 

- limit: 限制查询记录的数量
书写顺序:
　　- select
　　- from
　　- order by
　　- limit

执行顺序:
　　- from
　　- select
　　- order by
　　- limit

# 1、从第一条开始，获取4条记录;
　　select * from emp limit 4;

# 2、limit可以有两个参数, 参数1: 是限制的开始位置 + 1, 参数2:是从开始位置展示的条数;
　　select * from emp limit 4, 4; # 从第五条数据开始查找，获取4条
　　select * from emp limit 0, 4;

# 3、查询工资最高的人的 详细信息
　　select * from emp order by salary desc limit 1;

# 聚合函数: 若没有group by 分组，默认将查出来的数据当做一个分组, 也能使用;
　　select max(salary) from emp;

- 正则
# 在编程中，凡是看到reg开头的，基本上都是跟正则有关
# *: 代表 0 或 多个
　　select * from emp where name regexp '^程.*(金|银|铜|铁)$';

3.多表查询:

#创建表与插入数据准备
#建表
create table dep2(
id int,
name varchar(20) 
);

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
('tank','male',17,200),
('egon','female',48,201),
('kevin','male',38,201),
('jason','female',28,202),
('owen','male',18,200),
('sean','female',18,204);

# PS: 昨天讲了如何根据表关系对字段进行拆分，目的是为了更好的管理，表数据都存放在硬盘中，存不是目的，目的是为了取，所以我们将数据从硬盘读到内存中，接下来我们因应该将他们拼成一张表来查询更加合理;

# 注意: 将拆分的表，再拼接到一起进行查询, 可以通过一张表查另一张表的数据;

1、查询 员工 以及所在 部门 的信息;
# 通过where 约束条件
　　select * from emp2, dep2 where emp2.dep_id = dep2.id;

2、查询 部门 为 技术部 的 员工 及 部门信息
select emp2.name, dep2.* from emp2, dep2 where emp2.dep_id = dep2.id and dep2.name = '技术';

- 联表查询
- 内连接       inner join
# 1、内连接：只取两张表有对应关系的记录
查询 员工 以及所在 部门 的信息;
# 通过内连接
　　select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;

- 左连接       left join
# 2、左连接: 在内连接的基础上保留左表没有对应关系的记录
　　select * from emp2 left join dep2 on emp2.dep_id = dep2.id;

- 右连接　　right join
# 3、右连接: 在内连接的基础上保留右表没有对应关系的记录
　　select * from emp2 right join dep2 on emp2.dep_id = dep2.id;

- 全连接: 　  union
# 4、全连接: 在内连接的基础上 保留左、右表没有对应关系的记录
　　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;

- 子查询: 子查询就是将一个查询语句的结果用括号括起来，当做另一个查询语句的条件去用
# 1.查询部门是 技术 或者 人力资源 的员工信息

'''
先获取技术部和人力资源的id号，再去员工表里根据前面的id筛选出符合要求的员工信息;
'''
select * from emp2 where dep_id in (select id from dep2 where name='技术' or name='人力资源');

# 2.每个部门最新入职的员工 思路：先查每个部门最新入职的员工，再按部门对应上联表查询
# 查第一张emp表

select t1.id, t1.name, t1.hire_date, t1.post, t2.* 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;

'''
as:
        - 可以给表起别名
        - 可以给查出来的虚拟表起别名
        - 可以给字段起别名
'''