测试数据创建

如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk（或者参照我安装配置MySQL的博客，将所有字符编码设置为 utf8）

创建数据库与表

create database db1;
use db1;

create table emp(
  id int not null unique auto_increment,
  # 表内没有字段是primary key，innodb的机制，一个表没有primaryk key时会自动将 not null + unique的键自动升级为 primary key 主键
  name varchar(20) not null,
  sex enum('male','female') not null default 'male',  # 大部分是男的
  age int(3) unsigned not null default 28,  # 这个3 只限制了显示宽度，并不影响存储
  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
('jason','male',18,'20170301','张江第一帅形象代言',7300.33,401,1),
('egon','male',78,'20150302','teacher',1000000.31,401,1),
('kevin','male',81,'20130305','teacher',8300,401,1),
('tank','male',73,'20140701','teacher',3500,401,1),
('owen','male',28,'20121101','teacher',2100,401,1),
('jerry','female',18,'20110211','teacher',9000,401,1),
('nick','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);

数据展示

常见结果排版

select * from emp;

另一种结果排版 G

当表字段特别多的时候，结果的排版可能会出现混乱的现象，你可以在语句最后加 G 来改变排版，方便查看

比较差的展示结果，这种情况就非常适合用 G 来看数据

简单查询语句的书写与执行顺序

查询语句书写

查询出 emp 表中id 在 3~6 的员工详细信息

思路：从emp 表中，查 id 大于3 且 小于 6 的数据

语句

select * from emp where id > 3 and id < 6;

这里仅为了演示书写顺序，不考虑其他写法

执行顺序

最先执行的是 from，来确定到底是哪张表

然后执行 where，根据条件筛选数据

最后执行 select，来拿筛选出来的数据中的（某些，select 后面跟的字段名）字段

科普-- 起别名

关键字 as

• 可以给表起别名
• 可以给查询出来的虚拟表（查询结果）起别名
• 可以给字段起别名
• 可以给函数的结果取别名（max、min 等）

写法

要起别名的对象 as 别名 或者 直接 要起别名的对象 别名

不过尽量还是用as ，不用as 可能语义不明确

给函数结果起别名

... max(hire_date) as max_date ...

给表起别名

select ... from emp as t1 ....

给查询出来的虚拟表取别名

... (select * from emp) as t2 ...

给字段起别名

select name as '姓名', post '部门' from emp;

可以对字段做四则运算（加减乘数）

查一下 jason 的年薪

select name as '姓名', salary * 12 as '年薪' from emp where name = 'jason';

concat 格式化拼接字段

可以按指定格式拼接字段

select concat('oldboy_', name, '_', id), sex, post, salary from emp;

concat_ws 用指定字符拼接字段

select concat_ws(':', name, sex, age) from emp;

定制化查询结果

复杂查询实现小窍门：

写sql语句的时候，千万不要急着一口气写完（切忌心浮气躁）

前期按照歩鄹一步步写，将前一步操作产生的结果都当成是一张新的表，然后基于该表再进行其他操作，写一步查询看一下结果然后基于当前结果再往后写

我们查询数据一般都需要做一些过滤，单纯靠 select * from 表名; 就无法达到要求，此时我们可以通过

常见的数据定制化关键字（非多表查询）

• where 条件过滤数据

  一般配合一堆聚合函数使用

• group by 对数据进行分组

  • having 对分组的结果再进行条件过滤（必须跟在 group by 语句后面）

• distinct 对查询结果去重

• order by 对查询结果排序

• limit 限制显示数据条数

where 结合过滤条件过滤结果

> < = != <= >= <> 比较运算符

# 查询出 emp 表中， id 大于3 的员工信息
select * from emp where id > 3;

and or not 与或非连接多个条件

一般用来连接多个条件

and 并且

or 或

not 非

is

针对 null 判断的时候只能用 is 不能用 =

案例

# and
# 1.查询id大于等于3小于等于6的数据
select id,name from emp where id >= 3 and id <= 6;

# or
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary = 20000 or salary = 18000 or salary = 17000;

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

# is
# 7.查询岗位描述为空的员工名与岗位名  针对null不能用等号，只能用is
select name,post from emp where post_comment = NULL;  # 查询为空，不能用 = 判断空！
select name,post from emp where post_comment is NULL;
select name,post from emp where post_comment is not NULL;

范围

between ... and ... 表示范围（整型字段可用）

in

and or not 结果其他关键字组合

案例

# between ... and ... 
# 1.查询id大于等于3小于等于6的数据
select *  from emp where id between 3 and 6;

# in
# 2.查询薪资是20000或者18000或者17000的数据
select * from emp where salary in (20000,18000,17000);

# and or not
# 在上一模块中有案例

exists 是否存在

# EXISTS 关键字表示存在
# 返回值是 True 或者 False
select * from emp where exists (select id from dep where id > 203);  # 用到了下面的子查询（知识点）

like 模糊匹配

一般包含有 ... 之类的查询都会用 like 关键字，模糊匹配

小技巧

是否含有用 % 包围起来

固定长度用 — 来占位，一个 _ 表示一个字符

案例

# 3.查询员工姓名中包含o字母的员工姓名和薪资
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;  # 注意 sql_mode 里 PAD_CHAR_TO_FULL_LENGTH 这么个约束，否则 char类型定长可能会受影响

regexp 正则

可用正则规则匹配字符串作为查询条件

select * from emp where name regexp '^j.*(n|y)$';

group by 分组

分组之后应该做到最小单位是组，而不应该再展示组内的单个信息

MySQL 中分组之后，只能拿到分组的字段信息，无法直接获取其他字段信息

但是你可以通过其他方法（如：聚合函数）间接地获取

分组相当于打包，聚合函数可以对包里每一个元素进行处理，最终拿出想要的

刚开始查询表，一定要按照最基本的步骤，先确定是哪张表，再确定查这张表也没有限制条件，再确定是否需要分类，最后再确定需要什么字段对应的信息

应用场景

查每个部门的平均薪资，男女比例等

分组严格模式（推荐开启）

select * from emp group by post; 如果你的MySQL不报错，说明分组的严格模式没有设置

--> only_full_group_by 限制分组

非分组严格模式下

设置分组严格模式（其他的严格模式别忘写了）

set global sql_mode='strict_trans_tables,pad_char_to_full_length,only_full_group_by';

pad_char_to_full_length 验证 char varchar占用空间用，会影响 char_length() 获取的长度

strict_trans_tables 限制 sql 不能不合规则的直接报错

分组严格模式下执行，直接报错（day37.emp.id --> 数据库day37.表emp.字段id），字段不在分组里

having 分组条件

having 跟 where 是一模一样的，也是用来筛选数据的，但是 having 必须在 group by 后面使用

where是对整体数据做一个初步的筛选，而having是对分组之后的数据再进行一次针对性的筛选

select post, avg(salary) from emp where age > 30 group by post having avg(salary) > 10000;

统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门

分组结合聚合函数

只能在分组之后使用（如果没有写group by ，默认所有数据就是一组）

也可以说是 where 不能用聚合函数（执行顺序过了 where之后就可也以算分组之后了--> 执行顺序）

max min avg sum count

能够获取到分组之后除了分组依据以外的字段，将该字段作为函数的条件

# 强调:只要分组了，就不能够再“直接”查找到单个数据信息了，只能获取到组名
# 2.获取每个部门的最高工资  
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
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;

# 每个部门的人数
# 在统计分组内个数的时候，填写任意非空字段都可以完成计数（推荐使用能够标识数据的字段，比如id字段）
select post,count(id) from emp group by post;

group_concat 分组拼接记录字段

能够获取到分组之后除了分组依据以外的字段，还能做拼接操作

# 3.查询分组之后的部门名称和每个部门下所有的学生姓名
# group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
select post,group_concat(name) from emp group by post;

select post,group_concat(name,"_SB") from emp group by post;

select post,group_concat(name,": ",salary) from emp group by post;

select post,group_concat(salary) from emp group by post;


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

/*
concat  在不分组情况下使用
group_concat  用在分组之后
*/

distinct 去重

对整个查询（查询出的虚拟表）结果中重复的数据去重，重复必须数据是一模一样的才能去重，只要有一个(字段)不一样都不能算是重复的数据

如果你查询出来的数据中包含主键（非空且唯一），那么不可能去重成功

个人推荐理解成作用于上一步查询结果的（不要以为像order by一样修饰某个字段）

select distinct age, id from emp;

查询结果有重复的情况下，会自动去除重复

select distinct sex, age, id from emp order by sex, age asc;

记录没有重复，distinct 无效

order by 排序

order by 有升序（ASC）、降序（DESC）两种排序规则，默认升序

多个排序字段时，放前面的作为优先排序条件，相同再按照后面的字段排序

select post, avg(salary) from emp where age>10 group by post having avg(salary) > 1000 order by avg(salary);

单个字段排序

select * from emp order by sex, age asc;

多字段排序

limit 限制展示数据的条数

select * from emp limit 3;

当limit 只有一个参数的时候，表示的是从第一条开始只展示几条

select * from emp limit 5,5;

当limit 有两个参数的时候，第一个参数表的起始位置，第二个参数表示从起始位置开始往后展示的条数

练习

查询工资最高的人的详细信息

select * from emp order by salary desc limit 1;

应用场景

分页数据展示，每页只展示多少条，每页展示的内容，是第几条到第几条

究极版执行顺序书写顺序

书写顺序(除了 select ... from ... 其他是可选的)

select distinct 字段1,字段2（有分组时只能写分组字段或聚合函数） from 表名
where 条件（不能用聚合函数）
group by （单个）字段
having 条件
order by 字段1,字段2 排序规则
limit 起始位置,条数;

执行顺序

from
where
group by
	having  # 必须跟在 group by 后面
# 后4个顺序不太重要，也不一定对
order by
limit
distinct
select

多表查询

前言

在昨天的知识点中，员工信息全存为一张表不太合理，我们选择了拆表，分析了表关系，最终拆分成了员工表与部门表两张表，表示拆分好了，但怎么去查询数据呢？

要实现多表查询，有下面两种方式

• 联表查询
• 子查询

每一次的查询结果都是一张虚拟表，我们可以用 as 关键字给虚拟表取别名，然后将其当做普通表作为查询条件使用

测试数据创建

创建数据库与表

create database db2;
use db2;

create table dep(
id int,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

插入表记录数据

insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(name,sex,age,dep_id) values
('jason','male',18,200),
('egon','female',48,201),
('kevin','male',38,201),
('nick','female',28,202),
('owen','male',18,200),
('jerry','female',18,204);

# 当初为什么我们要分表，就是为了方便管理，在硬盘上确实是多张表，但是到了内存中我们应该把他们再拼成一张表进行查询才合理

笛卡尔集/积 -- 科普

笛卡尔集的列数为每个表的列数之和，笛卡尔集的行数为每个表的行数相乘。

我们经常做的多表查询就是在笛卡尔集中通过筛选条件得出的数据，所以笛卡尔集是多表查询的基础。

select * from emp, dep; 结果是一个笛卡尔集/积

select * from emp;

select * from dep;

后面跟条件也可以达到多表查询

select * from emp, dep where emp.dep_id = dep.id

联表查询

通过下面四种连接语句来实现多表查询

inner/left/right join ...左右是表 on ... 后面可以跟条件

内连接 inner join ... on

仅保留两张表有对应关系的记录

select * from emp inner join dep on emp.dep_id=dep.id;

左连接 left join ... on

在内连接的基础上保留左表没有对应关系的记录

select * from emp left join dep on emp.dep_id = dep.id;

右连接 right join ... on

在内连接的基础上保留右表没有对应关系的记录

select * from emp right join dep on emp.dep_id = dep.id;

全连接 union ... on

不常用

在内连接的基础上保留左、右面表没有对应关系的的记录

写法：只需要在左连接和右连接的sql 语句中间加个union就变成了全连接

select * from emp left join dep on emp.dep_id = dep.id  # 左连接 sql，后面不要加分号
union
select * from emp right join dep on emp.dep_id = dep.id;  # 右连接 sql

子查询

将一个查询语句用括号括起来，将查询结果（虚拟表）作为另外一个 sql 语句的查询条件

ps：表的查询结果可以作为其他表的查询条件，也可以通过起别名的方式把它作为一张虚拟表去跟其他表做关联查询

# 1.查询部门是技术或者人力资源的员工信息

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

select * from emp;

select id from dep where name = "技术" or name = "人力资源";

# 2.每个部门最新入职的员工

# 思路
# 先查每个部门最新入职的员工，再按部门对应上联表查询

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

完整表信息

练习小案例

# 平均年龄在25岁以上的部门名

# 联表
select dep.name from dep inner join emp on emp.dep_id = dep.id group by dep.name having avg(age) > 25;

# 子查询
select name from dep where dep.id in (select dep_id from emp group by dep_id having avg(age) > 25);

联表查询思路

1.先把两张表连起来，把结果查出来看看，再接着往下写

select * from dep inner join emp on emp.dep_id = dep.id;

2.再根据部门分组，筛选出平均年龄大于25的部门名

group by dep.name having avg(age) > 25

合并上一步的语句（* --> dep.name）

select dep.name from dep inner join emp on emp.dep_id = dep.id group by dep.name having avg(age) > 25;

子查询思路

1.先根据 部门id 分组，查出平均年龄大于 25多的部门id

select dep_id from emp group by dep_id having avg(age) > 25;

2.将上一步的 部门id 作为筛选条件 联合上部门表查出来

select name from dep where dep.id in (select dep_id from emp group by dep_id having avg(age) > 25);