前期表准备
# 如果cmd窗口展示数据时，窗口最大，展示数据的虚表仍有错位，应使用 \G 分行显示
select * from t1\G;
# 个别电脑在插入中文的时候会出现乱码或者空白的现象，可以将字符编码统一设置成 gbk
create table emp (
id int primary key auto_increment,
name varchar(20) not null,
sex enum("male", "female", "other") default "male", # 大部分是男的
age int 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
("jason", "male", 18, "20170301", "奥特曼", 7300.33, 401, 1), # 以下是教学部
("tom", "male", 78, "20150302", "teacher", 1000000.31, 401, 1),
("kevin", "male", 81, "20130305", "teacher", 8300, 401, 1),
("tony", "male", 73, "20140701", "teacher", 3500, 401, 1),
("owen", "male", 28, "20121101", "teacher", 2100, 401, 1),
("jack", "female", 18, "20110211", "teacher", 9000, 401, 1),
("jenny", "male", 18, "19000301", "teacher", 30000, 401, 1),
("sank", "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", "opeartion", 10000.13, 403, 3), # 以下是运营部
("程咬金", "male", 18, "19970312", "opeartion", 20000, 403, 3),
("程咬银", "female", 18, "20130311", "opeartion", 19000, 403, 3),
("程咬铜", "male", 18, "20150411", "opeartion", 18000, 403, 3),
("程咬铁", "female", 18, "20140512", "opeartion", 17000.13, 403, 3)；
几个关键字的执行顺序
# 书写顺序(英语语法)
select id,name from emp where id > 3;
# 执行顺序
from
where
select
# 执行顺序和书写顺序不一致，就按照书写顺序写 sql
select * 先用 * 来占位
之后补全后面的 sql 语句
最后将 * 号替换成想要的具体字符
where过滤
# 作用：对整体数据的一个筛选操作（可以理解为 python 中的 if）
# 查询 id 大于等于 3 小于等于 6 的数据
select id,name,age from emp where id >= 3 and id <= 6;
select id,name,age from emp where id between 3 and 6; # 两种等价
+----+-------+-----+
| id | name | age |
+----+-------+-----+
| 3 | kevin | 81 |
| 4 | tony | 73 |
| 5 | owen | 28 |
| 6 | jack | 18 |
+----+-------+-----+
# 查询薪资是 20000 或 18000 或 17000 的数据
select id, name, salary from emp where salary = 20000 or salary = 18000 or salary = 17000;
select id, name, salary from emp where salary in (20000, 18000, 17000);
+----+-----------+----------+
| id | name | salary |
+----+-----------+----------+
| 15 | 程咬金 | 20000.00 |
| 17 | 程咬铜 | 18000.00 |
+----+-----------+----------+
# 查询员工姓名中包含字母 o 的员工的姓名和薪资
select name, salary from emp where name like "%o%";
+-------+------------+
| name | salary |
+-------+------------+
| jason | 7300.33 |
| tom | 1000000.31 |
| tony | 3500.00 |
| owen | 2100.00 |
+-------+------------+
# 查询员工姓名是由 4 个字符组成的 姓名和薪资
select name, salary from emp where name like "____";
select name, salary from emp where char_length(name) = 4;
+------+----------+
| name | salary |
+------+----------+
| tony | 3500.00 |
| owen | 2100.00 |
| jack | 9000.00 |
| sank | 10000.00 |
+------+----------+
# 查询薪资不在 20000，18000，17000范围的数据
select * from emp where salary not in (20000, 18000, 17000);
# 查询岗位描述的为空的员工姓名和岗位，针对 null 不能用等号，用 is
select name,post from emp where post_comment is Null;
group by 分组
# 实际应用场景 分组应用场景非常多
男女比例
部门平均薪资
部门秃头率
国家之间数据统计
# 按照部门分组
select * from emp group by post;
# 分组之后最小的可操作单位是组 而不是组内的单个数据
# 上述命令在你没有设置严格模式可正常执行，返回的分组之后每组的第一条数据 但是浙不符合分组的规范——分组之后不应该考虑单个数据而应该以组位操作单位（分组之后没办法获取组内单个数据）
# 如果设置了严格模式，会直接报错
set global sql_mode = "strict_trans, only_full_group_by";
# 设置严格模式之后，分组默认只能拿到分组的依据
select post from emp group by post;
+-----------+
| post |
+-----------+
| opeartion |
| sale |
| teacher |
| 奥特曼 |
+-----------+
# 按照什么字段分组就只能拿到分组 其他字段不能直接获取 需要借助于其他方法
# 什么时候需要分组 —— 每个，平均，最高，最低
# 1. 获取每个部门的最高薪资
select post, max(salary) from emp group by post;
+-----------+-------------+
| post | max(salary) |
+-----------+-------------+
| opeartion | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 奥特曼 | 7300.33 |
+-----------+-------------+
# 可以使用 as 给字段起别名，也可以省略不写，但是不推荐不写，因为省略之后语义不明，容易错乱
select post as "部门", max(salary) as "最高薪资" from emp group by post;
+-----------+--------------+
| 部门 | 最高薪资 |
+-----------+--------------+
| opeartion | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 奥特曼 | 7300.33 |
+-----------+--------------+
# 获取每个部门的最低薪资
select post as "部门", min(salary) as "最低薪资" from emp group by post;
+-----------+--------------+
| 部门 | 最低薪资 |
+-----------+--------------+
| opeartion | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 奥特曼 | 7300.33 |
+-----------+--------------+
# 获取每个部门的薪资总和
select post as "部门", sum(salary) as "薪资总和" from emp group by post;
+-----------+--------------+
| 部门 | 薪资总和 |
+-----------+--------------+
| opeartion | 84000.26 |
| sale | 13001.47 |
| teacher | 1062900.31 |
| 奥特曼 | 7300.33 |
+-----------+--------------+
# 统计每个部门的人数
select post as "部门", count(id) as "总人数" from emp group by post; # 常用 id 进行统计
+-----------+-----------+
| 部门 | 总人数 |
+-----------+-----------+
| opeartion | 5 |
| sale | 5 |
| teacher | 7 |
| 奥特曼 | 1 |
+-----------+-----------+
select post as "部门", count(post_comment) as "总人数" from emp group by post; # 对 Null 无法计数
# 聚合函数——max，min，avg，sum，count
# group_concat 不仅支持获取分组后的其他字段值，还支持拼接操作
# 查询分组之后的部门名称和每个部门下所有的员工姓名
select post as "部门", group_concat(name) as "员工名称" from emp group by post;
+-----------+------------------------------------------------+
| 部门 | 员工名称 |
+-----------+------------------------------------------------+
| opeartion | 僧龙,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 哈哈,呵呵,西西,乐乐,拉拉 |
| teacher | tom,kevin,tony,owen,jack,jenny,sank |
| 奥特曼 | jason |
+-----------+------------------------------------------------+
select post as "部门", group_concat(name, "：", salary) as "各个员工工资" from emp group by post\G;
*************************** 1. row ***************************
部门: opeartion
各个员工工资: 僧龙：10000.13,程咬金：20000.00,程咬银：19000.00,程咬铜：18000.00,程咬铁：17000.13
*************************** 2. row ***************************
部门: sale
各个员工工资: 哈哈：3000.13,呵呵：2000.35,西西：1000.37,乐乐：3000.29,拉拉：4000.33
*************************** 3. row ***************************
部门: teacher
各个员工工资: tom：1000000.31,kevin：8300.00,tony：3500.00,owen：2100.00,jack：9000.00,jenny：30000.00,sank：10000.00
*************************** 4. row ***************************
部门: 奥特曼
各个员工工资: jason：7300.33
# concat 可以在不分组的情况下使用
select concat("Name：", name), concat("SAL：", salary) from emp;
+-------------------------+--------------------------+
| concat("Name：", name) | concat("SAL：", salary) |
+-------------------------+--------------------------+
| Name：jason | SAL：7300.33 |
| Name：tom | SAL：1000000.31 |
| Name：kevin | SAL：8300.00 |
| Name：tony | SAL：3500.00 |
| Name：owen | SAL：2100.00 |
| Name：jack | SAL：9000.00 |
| Name：jenny | SAL：30000.00 |
| Name：sank | SAL：10000.00 |
| Name：哈哈 | SAL：3000.13 |
| Name：呵呵 | SAL：2000.35 |
| Name：西西 | SAL：1000.37 |
| Name：乐乐 | SAL：3000.29 |
| Name：拉拉 | SAL：4000.33 |
| Name：僧龙 | SAL：10000.13 |
| Name：程咬金 | SAL：20000.00 |
| Name：程咬银 | SAL：19000.00 |
| Name：程咬铜 | SAL：18000.00 |
| Name：程咬铁 | SAL：17000.13 |
+-------------------------+--------------------------+
# 查询每个人的年薪（12薪）
select name, salary*12 from emp;
分组注意事项
# 关键词 where 和 group by 同时出现的时候 group by 必须在 where 的后面——where 先对整体数据过滤再进行分组
# 聚合函数只能在分组之后使用，即 where 筛选田间不能使用聚合函数
select name from emp where max(salary) > 3000; # 报错
select max(salary) from emp; # 不分组默认整体是一个分组
+-------------+
| max(salary) |
+-------------+
| 1000000.31 |
+-------------+
# 统计各部门年龄在 30 岁以上的员工平均薪资
select post as "部门", avg(salary) as "平均薪资" from emp where age > 30 group by post;
+---------+---------------+
| 部门 | 平均薪资 |
+---------+---------------+
| sale | 2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
having 分组之后的筛选条件
# having 的语法和 where 是一致的，只不过 having 是在分组之后进行的过滤操作，即 having 是可以直接使用聚合函数的
# 统计各部门年龄在 30岁以上的员工平均薪资并且保留平均薪资大于 10000 的部门
select post as "部门", avg(salary) as "平均薪资" from emp where age > 30 group by post having avg(salary) > 10000;
+---------+---------------+
| 部门 | 平均薪资 |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
distinct 去重
# 必须是完全一样的数据才能去重，一定不要忽视主键，存在主键的情况下一定无法去重
select distinct id, age from emp;
select distinct age from emp;
order by 排序
select * from emp order by salary; # 默认升序， asc 可以省略不写，降序 desc
select * from emp order by salary desc;
select * from emp order by age asc, salary desc; # 先按照 age 升序排序，如果 age 相同再按照 salary 降序排序
# 统计各部门年龄在 10 岁以上的员工平均薪资并且保留平均薪资大于 1000 的部门，并对平均工资降序
select post as "部门", avg(salary) as "平均薪资" from emp where age > 10 group by post having avg(salary) > 1000 order by avg(salary) desc;
+-----------+---------------+
| 部门 | 平均薪资 |
+-----------+---------------+
| teacher | 151842.901429 |
| opeartion | 16800.052000 |
| 奥特曼 | 7300.330000 |
| sale | 2600.294000 |
+-----------+---------------+
limit 限制展示条数
select * from emp;
# 针对数据过多的情况，通常分页处理
select name, age, salary from emp limit 3;# 只展示前三条数据
+-------+-----+------------+
| name | age | salary |
+-------+-----+------------+
| jason | 18 | 7300.33 |
| tom | 78 | 1000000.31 |
| kevin | 81 | 8300.00 |
+-------+-----+------------+
select name, age, salary from emp limit 0, 5; # 展示第一条到第五条的数据
+-------+-----+------------+
| name | age | salary |
+-------+-----+------------+
| jason | 18 | 7300.33 |
| tom | 78 | 1000000.31 |
| kevin | 81 | 8300.00 |
| tony | 73 | 3500.00 |
| owen | 28 | 2100.00 |
+-------+-----+------------+
select name, age, salary from emp limit 5, 5; # 展示从 第6条开始的五条数据
+--------+-----+----------+
| name | age | salary |
+--------+-----+----------+
| jack | 18 | 9000.00 |
| jenny | 18 | 30000.00 |
| sank | 48 | 10000.00 |
| 哈哈 | 48 | 3000.13 |
| 呵呵 | 38 | 2000.35 |
+--------+-----+----------+
# limit 第一个参数是起始位置，第二个参数是显示数量
正则表达式
# 关键词 regexp
select name, age, salary from emp where name regexp "^j."; # 匹配 j 开头的数据
+-------+-----+----------+
| name | age | salary |
+-------+-----+----------+
| jason | 18 | 7300.33 |
| jack | 18 | 9000.00 |
| jenny | 18 | 30000.00 |
+-------+-----+----------+
连表操作
# 连表
create table dep (
id int,
name varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum("male", "female", "other") 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", 18 ,201),
("nick", "male", 28, 202),
("owen", "male", 18, 203),
("jerry", "female", 18, 204);
表查询
select * from dep, emp; # 结果 是 笛卡尔积
select * from dep,emp where emp.dep_id = dep.id;
+------+--------------+----+-------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+-------+--------+------+--------+
| 200 | 技术 | 1 | jason | male | 18 | 200 |
| 201 | 人力资源 | 2 | egon | female | 48 | 201 |
| 201 | 人力资源 | 3 | kevin | male | 18 | 201 |
| 202 | 销售 | 4 | nick | male | 28 | 202 |
| 203 | 运营 | 5 | owen | male | 18 | 203 |
+------+--------------+----+-------+--------+------+--------+
# MySQL 考虑到使用中会经常使用拼表操作，所以提供了很多方法
inner join # 内连接
left join # 左连接
right join # 右连接
union # 全连接
select * from emp inner join dep on emp.dep_id = dep.id; # 只拼接两张表中公有的数据部分
select * from emp left join dep on emp.dep_id = dep.id; # 左表的所有数据展示，没有对应的填入 null
+----+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
+----+-------+--------+------+--------+------+--------------+
# right join 和 left join 类似，显示右表所有内容，没有对应数据的填入 null
# union 全连接，左右两表的数据全显示
insert into dep values(205, "sale");
select * from emp left join dep on emp.dep_id = dep.id union select * from emp right join dep on emp.dep_id = dep.id;
+------+-------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+-------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | egon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| 6 | jerry | female | 18 | 204 | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL | 205 | sale |
+------+-------+--------+------+--------+------+--------------+
子查询
"
子查询就是我们平时解决问题的思路
分步骤解决问题
第一步
第二步
...
将一个查询语句的结果当作另外一个查询语句的条件去用
"
# 查询部门是技术或是人力资源的员工信息
1. 获取部门的 id 号
select id from dep where name in ("技术", "人力资源");
+------+
| id |
+------+
| 200 |
| 201 |
+------+
2. 查询部门是技术或者人力资源的员工
select name from emp where dep_id in (200, 201);
+-------+
| name |
+-------+
| jason |
| egon |
| kevin |
+-------+
select name from emp where dep_id in(select id from dep where name in ("技术", "人力资源"));
+-------+
| name |
+-------+
| jason |
| egon |
| kevin |
+-------+
总结
表的查询结果可以作为其他表的查询条件
也可以通过起别名的方式把它作为一张虚拟表和其他表进行关联
"
多表查询
1. 先拼接表再查询
2. 子查询一步一步来
"
