MySQL学习6——查询

## 一 单表查询

# 在写SQL命令是注意两点:

- 书写顺序
# 查询 条件为 id是4-5记录的 id 与 名字
# select id, name: 只查看 id 与 name字段的数据
# from emp 查看的数据来自emp这表表
# where id > 3 and id < 6 查询条件
select id, name from emp where id > 3 and id < 6;
*: 查所有字段的数据

# 书写顺序
- select
- from
- where
- and

- 执行顺序
# 执行顺序
- from # 找图书馆
- where # 书在图书馆中的位置
- select # 查找书中的某一页内容

from --> where --> group by --> select 

# 查询数据量大时，可以在表后面 + \G，修改显示格式;
select * from emp\G # 不要加分号

 

### where约束条件

# 1.查询id大于等于3小于等于6的 数据
# 注意: 写SQL时，可以根据执行顺序一步一步写
select * from emp where id>=3 and id<=6;
# between: 两者之间
# and: 与
select * from emp where id between 3 and 6;

# 2.查询薪资是20000或者18000或者17000的数据
# or: 或者
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: 模糊匹配
# %: 匹配1个或多个任意字符
# _: 匹配一个任意字符
select name, salary from emp where name like "%o%";

# 4.查询员工姓名是由四个字符组成的员工姓名与薪资
select name, salary from emp where name like "____";

# char_length(字段): 获取字段值的长度
select name, salary from emp where char_length(name) = 4;

# 5.查询id小于3或者大于6的数据
# or
select * from emp where id < 3 or id > 6;
# between
# not：取反
select * from emp where id not between 3 and 6;

# 7.查询岗位描述为空的 员工名 与 岗位名
# 注意: 针对null不能用“等号”，只能用 “is“
# post_comment： 岗位描述 字段

select name, post from emp where post_comment is null;

 

### group by分组 

# 1.按部门分组
# 严格模式下只能获取分组字段post数据, 无法获取其他字段信息，就好比是进程之间数据隔离，但是可以使用聚合函数来获取

# 注意: 分组后，select只能使用 “聚合函数” 来获取数据

聚合函数: max（取最高值）, min（取最低值）, sum（求和）, avg（求平均值）, count（计数）

# 注意: 聚合函数:
1、只能在group by后(执行顺序)使用
2、若查询语句没有group by，则默认整张表就是一个分组。

# 严格模式设置

show variables like "%mode%";
set global sql_mode="strict_trans_tables,only_full_group_by";

# 3.查询分组之后的部门名称和每个部门下所有员工的姓名
# group_concat(name): 不仅可以获取分组后的某一个字段，并且可以对字符串进行拼接
# group_concat聚合函数

# 4.补充concat（不分组时用）拼接字符串达到更好的显示效果 as语法使用
select concat('Name: ', name) as '名字', concat('Sal: ', salary) as '薪资' from emp;

# 5.补充as语法 即可以给字段起别名也可以给表起
select emp.name as '名字', emp.salary as '薪资' from emp;

# 求各部门所有员工的年薪
select name, salary * 12 as annual_salary from emp;

 

### having过滤

　　1.having与where语法一样，只不过having需要在group by后使用;
　　2.where 不能使用聚合函数，但having可以;

1、统计各部门年龄在30岁以上的 员工平均工资，并且保留 平均工资大于10000的部门;
# 先查找emp表，然后获取30岁以上的员工信息，再根据部门分组，过滤出 平均薪资大于10000的部门数据;
select post, avg(salary) as "平均薪资" from emp where age>30 group by post having avg(salary) > 10000;

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

 

### distinct去重

# 注意: 查询的字段值必须是重复的才有效，只要有一个字段值是不重复的就没有效果。

# 当一张表中的某条记录数据是重复时，才有意义
select distinct id, post from emp;

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

### order by排序

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

select * from emp order by salary desc; # 指定降序

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

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

执行顺序:
- from
- where
- group by
- having
- select
- order by # 通过select 查出来的数据再进行排序

 

### limit限制结果返回数量

# 应用场景: 类似于博客园首页的数据展示，每一页有固定的数量;

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

# 2、limit可以有两个参数, 参数1:是限制的开始位置 起始位置为0, 参数2:是从开始位置展示的条数;
select * from emp limit 0, 4;
select * from emp limit 4, 4; # 从第五条记录开始，展示4条

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

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

### 10、正则

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

^：开头　　$：结尾　　.：省略一个字符　　（）内竖向划分的字符选一

 

## 二 多表查询

- 多表查询
　　- 关联查询
　　- 子查询

### 1、关联查询

# 左表的一条记录与右表的一条记录都对应一遍称之为 --> "笛卡尔积" 
# 将所有的数据都对应了一遍，虽然不合理但是其中有合理的数据，现在我们需要做的就是找出合理的数据

# 一 比较麻烦的表关联
　　查询员工以及所在部门的信息;
# 将两张表合并，并且根据id字段去判断
select * from emp2, dep2 where emp2.dep_id = dep2.id;

# 二 将两张表关联到一起的操作，有专门对应的方法
1、inner join
# 1、内连接：只取两张表有对应关系的记录
# 左表 inner join 右表 on 关联条件
select * from emp2 inner join dep2 on emp2.dep_id = dep2.id;

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

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

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

### 2、子查询

# 子查询就是将一个查询语句的结果用括号括起来，当做另一个查询语句的条件去用

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

 

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