操作表的SQL语句补充、查询关键字之select from、where筛选、group by分组、having过滤、distinct去重、order by排序、limit分页、regexpz正则和多表查询之子查询、连表操作
操作表的SQL语句补充、查询关键字之select from、where筛选、group by分组、having过滤、distinct去重、order by排序、limit分页、regexpz正则和多表查询之子查询、连表操作
操作表的SQL语句补充
1、修改表名：
alter table 表名 rename 新表名;
2、新增字段：
# 直接在表字段的最后插入字段
alter table 表名 add 字段名 字段类型(数字) 约束条件;
# 在某个已存在的字段后面插入字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 after 已经存在的字段;
# 在表字段的最开始插入第一个字段
alter table 表名 add 字段名 字段类型(数字) 约束条件 first;
3、修改字段
# 修改字段的字段名以及数据类型，字段类型不能省略
alter table 表名 change 旧字段 新字段 字段类型(数字) 约束条件;
# 修改字段的数据类型，只能修改字段的数据类型
alter table 表名 modify 字段名 新的字段类型(数字) 约束条件；
4、删除字段
alter table 表名 drop 字段名;
代码演示：
mysql> create database db1;
Query OK, 1 row affected (0.00 sec)
mysql> use db1;
Database changed
mysql> create table t1(
-> id int primary key auto_increment,
-> name varchar(32),
-> age int,
-> hobbies varchar(32));
Query OK, 0 rows affected (0.02 sec)
# 修改表名
mysql> alter table t1 rename user;
Query OK, 0 rows affected (0.01 sec)
# 添加新的字段
mysql> alter table user add phone varchar(32);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 在字段age后面添加字段address
mysql> alter table user add address varchar(60) after age;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 在整个字段的最开始添加字段user_id
mysql> alter table user add user_id int first;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 将字段hobbies修改为字段birth，数据类型为date
mysql> alter table user change hobbies birth date;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 只修改字段phone的字段类型为bigint
mysql> alter table user modify phone bigint;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 修改删除插入的第一个字段user_id
mysql> alter table user drop user_id;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
数据准备
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
('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','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
SQL语句的关键字编写顺序与执行顺序是不一致的，但是我们在写SQL语句时不用考虑编写顺序与执行顺序之间的关系。
如：select name from emp;
肯定是执行from确定表之后执行select确定字段
编写SQL语句针对select和from可以先写个固定模板：
select * from 表名 其它操作
(select后面的字段可根据具体的需求写入具体的字段，也可能通过SQL动态产生的，所以可以先用*占位最后再修改)
select 自定义查询表中字段对应的数据 from 指定操作的对象(指定的表可以是一张也可以是多张表)
1、查询数据所有的行和列：
select * from 表名;
2、查询部分行和列：
select 列名1,列名2... from 表名 where 查询条件;
3、在查询中使用列的别名：
select 列名1 as 自定义列名1,列名2 as 自定义列名2... from 表名；
查询关键字之where筛选
作用：where过滤数据，增加约束条件
- where在查询数据库的结果返回之前对数据库中的查询条件进行约束
- where后面不能写聚合函数
where的操作符：
| 操作符 | 说明 |
|---|---|
| = | 等于 |
| <> | 不等于 |
| != | 不等于 |
| > | 大于 |
| >= | 大于等于 |
| < | 小于 |
| <= | 小于等于 |
| benween A and B | 在指定两个值之间 |
| is | 指定数值/检查空值 |
| like | 模糊查询，搜索匹配字段中指定的内容 |
| not like | 模糊查询，搜索匹配字段中除去的内容 |
| and | where组合，并且 |
| or | where组合，或者 |
实操：
1.查询id大于等于3小于等于6的数据
select * from emp where id>=3 and id<=6;
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;
select * from emp where salary in (20000,18000,17000);
3.查询员工姓名中包含o字母的员工姓名和薪资
select name,salary from emp where name like '%o%';
4.查询员工姓名是由四个字符组成的员工姓名与其薪资
select name.salary from emp where name like '____';
select name,salary from emp where char_length(name)=4;
5.查询id小于3或者大于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.查询岗位描述为空的员工姓名与岗位名
'''针对null不能用等号，只能用is'''
select name,post from emp where post_comment is null;
select name,post from emp where post_comment is not null;
代码演示：
# 1.查询id大于等于3小于等于6的数据
mysql> select * from emp where id>=3 and id <=6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)
mysql> select * from emp where id between 3 and 6;
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
+----+-------+--------+-----+------------+---------+--------------+---------+--------+-----------+
4 rows in set (0.00 sec)
# 2.查询薪资是20000或者18000或者17000的数据
mysql> select * from emp where salary=20000 or salary=18000 or salary=17000;
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)
mysql> select * from emp where salary in(20000,18000,17000);
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------+--------------+----------+--------+-----------+
3 rows in set (0.00 sec)
# 3.查询员工姓名中包含o字母的员工姓名和薪资
mysql> select name,salary from emp where name like '%o%';
+-------+------------+
| name | salary |
+-------+------------+
| jason | 7300.33 |
| tom | 1000000.31 |
| tony | 3500.00 |
| owen | 2100.00 |
+-------+------------+
4 rows in set (0.00 sec)
# 4.查询员工姓名是由四个字符组成的员工姓名与其薪资
mysql> select name,salary from emp where name like '____';
+------+----------+
| name | salary |
+------+----------+
| tony | 3500.00 |
| owen | 2100.00 |
| jack | 9000.00 |
| sank | 10000.00 |
+------+----------+
4 rows in set (0.00 sec)
mysql> select name,salary from emp where char_length(name)=4;
+------+----------+
| name | salary |
+------+----------+
| tony | 3500.00 |
| owen | 2100.00 |
| jack | 9000.00 |
| sank | 10000.00 |
+------+----------+
4 rows in set (0.00 sec)
# 5.查询id小于3或者大于6的数据
mysql> select * from emp where id not between 3 and 6;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
14 rows in set (0.00 sec)
# 6.查询薪资不在20000，18000，17000范围的数据
mysql> select * from emp where salary not in (20000,18000,17000);
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
15 rows in set (0.00 sec)
# 7.查询岗位描述为空的员工名与岗位名
mysql> select name,post from emp where post_comment=null;
Empty set (0.00 sec)
mysql> select name,post from emp where post_comment is null;
+-----------+-----------------------------+
| name | post |
+-----------+-----------------------------+
| jason | 浦东第一帅形象代言 |
| tom | teacher |
| kevin | teacher |
| tony | teacher |
| owen | teacher |
| jack | teacher |
| jenny | teacher |
| sank | teacher |
| 哈哈 | sale |
| 呵呵 | sale |
| 西西 | sale |
| 乐乐 | sale |
| 拉拉 | sale |
| 僧龙 | operation |
| 程咬金 | operation |
| 程咬银 | operation |
| 程咬铜 | operation |
| 程咬铁 | operation |
+-----------+-----------------------------+
18 rows in set (0.00 sec)
mysql> select name,post from emp where post_comment is not null;
Empty set (0.00 sec)
查询关键字之group by分组
分组：按照一些指定的条件将单个单个数据分为一个个整体
关键字：group_concat(字段)，展示字段的所有的数据与group by连用
分组之后研究的对象应该是以组为单位，不应该再直接获取单个数据项，如果获取了应该直接报错，select后面可以直接填写的字段只能是分组的依据(其他字段需要借助一些方法才可以获取)
set global sql_mode='strict_trans_tables,only_full_group_by';
eg:
select post from emp group by post;
写SQL是否需要使用分组，可看题目是否出现：每个，平均、最大、最小
分组常见使用的聚合函数：
| 函数 | 含义 |
|---|---|
| max | 最大值 |
| min | 最小值 |
| sum | 总和 |
| count | 计数 |
| avg | 平均 |
实操：
1.每个部门的最高工资
select post,max(salary) from emp group by post;
# 在显示的时候可以给字段取别名,关键字：as，as可省但是不推荐，寓意不明确
select post as '部门',max(salary) as '最高工资' from emp group by post;
2.每个部门的最低工资
select post,min(salary) from emp group by post;
3.每个部门的平均工资
select post,avg(salary) from emp group by post;
4.每个部门的工资总和
select post,sum(salary) from emp group by post;
5.每个部门的人数
select post,count(id) from emp group by post;
6.查询分组之后的部门名称和每个部门下所有的学生姓名
'''group_concat(分组之后用)，不仅可以用来显示分组外字段还有拼接字符串的作用'''
# 将每个部门的名字展示出来
select post,group_concat(name) from emp group by post;
# 将每个部门的名字后面拼接'_SB'
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;
代码演示：
# 每个部门的最高工资
mysql> select post,max(salary) from emp group by post;
+-----------------------------+-------------+
| post | max(salary) |
+-----------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)
mysql> select post as '部门',max(salary) as '最高工资' from emp group by post;
+-----------------------------+--------------+
| 部门 | 最高工资 |
+-----------------------------+--------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+--------------+
4 rows in set (0.00 sec)
# 每个部门的最低工资
mysql> select post,min(salary) from emp group by post;
+-----------------------------+-------------+
| post | min(salary) |
+-----------------------------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)
# 每个部门的平均工资
mysql> select post,avg(salary) from emp group by post;
+-----------------------------+---------------+
| post | avg(salary) |
+-----------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 浦东第一帅形象代言 | 7300.330000 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)
# 每个部门的工资总和
mysql> select post,sum(salary) from emp group by post;
+-----------------------------+-------------+
| post | sum(salary) |
+-----------------------------+-------------+
| operation | 84000.13 |
| sale | 13001.47 |
| teacher | 1062900.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)
# 每个部门的人数
mysql> select post,count(id) from emp group by post;
+-----------------------------+-----------+
| post | count(id) |
+-----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 浦东第一帅形象代言 | 1 |
+-----------------------------+-----------+
4 rows in set (0.00 sec)
# 将每个部门的名字展示出来
mysql> select post,group_concat(name) from emp group by post;
+-----------------------------+------------------------------------------------+
| post | group_concat(name) |
+-----------------------------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 |
| sale | 拉拉,乐乐,西西,呵呵,哈哈 |
| teacher | sank,jenny,jack,owen,tony,kevin,tom |
| 浦东第一帅形象代言 | jason |
+-----------------------------+------------------------------------------------+
4 rows in set (0.00 sec)
# 将每个部门的名字后面拼接'_SB'
mysql> select post,group_concat(name,'_SB') from emp group by post;
+-----------------------------+---------------------------------------------------------------+
| post | group_concat(name,'_SB') |
+-----------------------------+---------------------------------------------------------------+
| operation | 程咬铁_SB,程咬铜_SB,程咬银_SB,程咬金_SB,僧龙_SB |
| sale | 拉拉_SB,乐乐_SB,西西_SB,呵呵_SB,哈哈_SB |
| teacher | sank_SB,jenny_SB,jack_SB,owen_SB,tony_SB,kevin_SB,tom_SB |
| 浦东第一帅形象代言 | jason_SB |
+-----------------------------+---------------------------------------------------------------+
4 rows in set (0.00 sec)
# 将每个部门的名字和工资以分隔符：':'展示
mysql> select post,group_concat(name,': ',salary) from emp group by post;
+-----------------------------+---------------------------------------------------------------------------------------------------------+
| post | group_concat(name,': ',salary) |
+-----------------------------+---------------------------------------------------------------------------------------------------------+
| operation | 程咬铁: 17000.00,程咬铜: 18000.00,程咬银: 19000.00,程咬金: 20000.00,僧龙: 10000.13 |
| sale | 拉拉: 4000.33,乐乐: 3000.29,西西: 1000.37,呵呵: 2000.35,哈哈: 3000.13 |
| teacher | sank: 10000.00,jenny: 30000.00,jack: 9000.00,owen: 2100.00,tony: 3500.00,kevin: 8300.00,tom: 1000000.31 |
| 浦东第一帅形象代言 | jason: 7300.33 |
+-----------------------------+---------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
# 将每个部门的工资展示出来
mysql> select post,group_concat(salary) from emp group by post;
+-----------------------------+--------------------------------------------------------------+
| post | group_concat(salary) |
+-----------------------------+--------------------------------------------------------------+
| operation | 17000.00,18000.00,19000.00,20000.00,10000.13 |
| sale | 4000.33,3000.29,1000.37,2000.35,3000.13 |
| teacher | 10000.00,30000.00,9000.00,2100.00,3500.00,8300.00,1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+--------------------------------------------------------------+
4 rows in set (0.00 sec)
查询关键字之having过滤
where与having的功能其实是一样的，都是用来筛选数据。只不过where用于分组之前的筛选，而having用于分组之后的筛选，为了人为的区分，所以叫where是筛选，having是过滤。
- having是在查询数据库结果返回之后进行过滤，在结果返回值后起作用
- having后面可以写聚合函数
统计各部门年龄在30岁以上的员工平均工资，并且保留平均工资大于10000的部门。
select post,avg(salary) from emp
where age>=30
group by post having avg(salary)>10000;
代码演示：
推导过程：
# 先查询年龄大于30的每个部门的员工姓名
mysql> select post,group_concat(name) from emp where age>30 group by post;
+---------+---------------------+
| post | group_concat(name) |
+---------+---------------------+
| sale | 哈哈,呵呵 |
| teacher | tom,kevin,tony,sank |
+---------+---------------------+
2 rows in set (0.00 sec)
# 查询每个部门年龄大于30的每个部门的平均薪资
mysql> select post,avg(salary) from emp where age>30 group by post;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| sale | 2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
2 rows in set (0.00 sec)
# 查询每个部门年龄大于30且平均工资大于10000的部门及其平均薪资
mysql> select post,avg(salary) from emp where age>=30 group by post having avg(salary)>10000;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| teacher | 255450.077500 |
+---------+---------------+
1 row in set (0.00 sec)
查询关键字之distinct去重
去重的前提是数据必须是一模一样的：
去除返回结果中的重复项，null也是一类数据，若返回结果中有多个null，使用distinct关键字将多个null合并为一条。
关键字：distinct
去重的话必须要保证去重的字段之间的数值是一模一样的才会去重
把要去重的字段看作整体，整体一致才会去重
代码演示：
mysql> select distinct id,age from emp;
+----+-----+
| id | age |
+----+-----+
| 1 | 18 |
| 2 | 78 |
| 3 | 81 |
| 4 | 73 |
| 5 | 28 |
| 6 | 18 |
| 7 | 18 |
| 8 | 48 |
| 9 | 48 |
| 10 | 38 |
| 11 | 18 |
| 12 | 18 |
| 13 | 28 |
| 14 | 28 |
| 15 | 18 |
| 16 | 18 |
| 17 | 18 |
| 18 | 18 |
+----+-----+
18 rows in set (0.00 sec)
mysql> select distinct age from emp;
+-----+
| age |
+-----+
| 18 |
| 78 |
| 81 |
| 73 |
| 28 |
| 48 |
| 38 |
+-----+
7 rows in set (0.00 sec)
查询关键字之order by排序
order by 字段 # 默认升序
order by 字段 等价于 order by 字段 asc # 升序
order by desc # 降序
若排序遇到相同的数据，那么order by 支持多条件排序，解决相同数据关系。
order by 字段1 升序/降序,字段2 升序/降序...
代码演示：
# 默认升序，根据工资排
mysql> select * from emp order by salary;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
# 升序，根据工资排
mysql> select * from emp order by salary asc;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
# 降序，工资降序排
mysql> select * from emp order by salary desc;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.02 sec)
# 年龄工资都升序排
mysql> select * from emp order by age,salary;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.01 sec)
# 年龄降序排，工资升序排
mysql> select * from emp order by age desc,salary asc;
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
| 13 | 拉拉 | female | 28 | 2017-01-27 | sale | NULL | 4000.33 | 402 | 2 |
| 14 | 僧龙 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 11 | 西西 | female | 18 | 2011-03-12 | sale | NULL | 1000.37 | 402 | 2 |
| 12 | 乐乐 | female | 18 | 2016-05-13 | sale | NULL | 3000.29 | 402 | 2 |
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 18 | 程咬铁 | female | 18 | 2014-05-12 | operation | NULL | 17000.00 | 403 | 3 |
| 17 | 程咬铜 | male | 18 | 2015-04-11 | operation | NULL | 18000.00 | 403 | 3 |
| 16 | 程咬银 | female | 18 | 2013-03-11 | operation | NULL | 19000.00 | 403 | 3 |
| 15 | 程咬金 | male | 18 | 1997-03-12 | operation | NULL | 20000.00 | 403 | 3 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
+----+-----------+--------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
18 rows in set (0.00 sec)
查询关键字之limit分页
1.限制展示的数据条数
select * from 表名 约束条件 limit 5;
2.限制展示从第几条之后的数据条数
select * from 表名 约束条件 limit m,n; # 展示第m条后的n条数据
3.展示第几条之后的全部数据条数
select * from 表名 约束条件 limit m,-1 # 展示第m条后所有的数据
代码展示：
# 展示5条数据
mysql> select * from emp limit 5;
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 3 | kevin | male | 81 | 2013-03-05 | teacher | NULL | 8300.00 | 401 | 1 |
| 4 | tony | male | 73 | 2014-07-01 | teacher | NULL | 3500.00 | 401 | 1 |
| 5 | owen | male | 28 | 2012-11-01 | teacher | NULL | 2100.00 | 401 | 1 |
+----+-------+------+-----+------------+-----------------------------+--------------+------------+--------+-----------+
5 rows in set (0.01 sec)
# 展示5之后的6条数据
mysql> select * from emp limit 5,5;
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
| 6 | jack | female | 18 | 2011-02-11 | teacher | NULL | 9000.00 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
| 8 | sank | male | 48 | 2010-11-11 | teacher | NULL | 10000.00 | 401 | 1 |
| 9 | 哈哈 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 10 | 呵呵 | female | 38 | 2010-11-01 | sale | NULL | 2000.35 | 402 | 2 |
+----+--------+--------+-----+------------+---------+--------------+----------+--------+-----------+
5 rows in set (0.00 sec)
# 展示工资最高的员工数据
mysql> select * from emp order by salary desc limit 1;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | tom | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)
查询关键字之regexp正则
模式 匹配
^ 匹配字符串开头
$ 匹配字符串结尾
. 匹配任意单个字符
[] 匹配方括号间列出的任意字符
[^] 匹配方括号间未列出的任意字符
a|b 交替匹配任意 p1 或 p2 或 p3
* 匹配前面的元素的零次或多次
+ 匹配前面的元素的一次或多次
{n} 匹配前面的元素 n 次
{m,n} 匹配前面的元素 m 至 n 次
mysql中正则表达式不分大小写，若想要区分大小写使用'binary'关键字。
代码演示：
# 查询以'j'开头结尾为'n'或'y'结尾的姓名
mysql> select * from emp where name regexp'^j.*(n|y)$';
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
| 1 | jason | male | 18 | 2017-03-01 | 浦东第一帅形象代言 | NULL | 7300.33 | 401 | 1 |
| 7 | jenny | male | 18 | 1900-03-01 | teacher | NULL | 30000.00 | 401 | 1 |
+----+-------+------+-----+------------+-----------------------------+--------------+----------+--------+-----------+
2 rows in set (0.00 sec)
多表查询思路
子查询：将一张表的查询结果括号括起来当作另一条SQL语句的条件。
类似于生活中的解决问题的方式，第一步干什么，第二步基于第一步的结果在做操作。
连表操作：先将所有涉及到结果的表全部拼接到一起形成一张达标，然后从大表中查询数据。
数据准备
create table dep1(
id int primary key auto_increment,
name varchar(20)
);
create table emp1(
id int primary key auto_increment,
name varchar(20),
gender enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into dep1 values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营'),
(205,'安保')
;
insert into emp1(name,gender,age,dep_id) values
('jason','male',18,200),
('dragon','female',48,201),
('kevin','male',18,201),
('nick','male',28,202),
('owen','male',18,203),
('jerry','female',18,204);
子查询
子查询将一个select语句的查询结果作为另一个select语句的查询条件。
1.带in的子查询
子查询返回的结果返回中包含一个值时，比较运算符才适用。通常返回的结果是值的列表，in运算符检测到结果存在某个特定的值就执行外部的查询。
2.not in的子查询
与in的子查询相反，当检测到结果不存在某个特定的值才会执行外部的查询。
3.exist的子查询
内层查询语句不返回查询的记录，屙屎返回一个真价值，如果内层的查询语句查询到满足条件的语句的记录就返回一个真值(TRUE),否则返回一个假值(False)。当返回的值为TRUE是，外层查询语句将进行查询，当返回结果为false时，外层语句不会进行查询。
代码演示：
查询jason部门名称
# 推导1：先获取jason的部门编号
select dep_id from emp1 where name='jason'; # 200
mysql> select dep_id from emp1 where name='jason';
+--------+
| dep_id |
+--------+
| 200 |
+--------+
1 row in set (0.00 sec)
# 推导2：根据部门编号获取部门名称
select name from dep1 where id=200; # 技术
mysql> select name from dep1 where id=200;
+--------+
| name |
+--------+
| 技术 |
+--------+
1 row in set (0.00 sec)
# 子查询
select name from dep1 where id = (select dep_id from emp1 where name='jason');
mysql> select name from dep1 where id = (select dep_id from emp1 where name='jason');
+--------+
| name |
+--------+
| 技术 |
+--------+
1 row in set (0.00 sec)
连表操作
1.inner join 内连接 # 只拼接两边都有的字段数据
select 字段\* from 表名1 inner join 表名2 on 表名1.key=表名2.key;
2.left join 左连接 # 以左表为基准，展示所有的数据，没有对应则null填充右表的数据
select 字段\* from 表名1 left join 表名2 on 表名1.key=表名2.key;
3.right join 右连接 # 以右表为基准，展示所有的数据，没有对应的则null填充左表的数据
select 字段\* from 表名1 right join 表名2 on 表名1.key=表名2.key;
4.union 全连接 # 左右两边的表的数据都展示，没有对应项用null填充
select 字段\* from 表名1 left join 表名2 on 表名1.key=表名2.key
union
select 字段\* from 表名1 right join 表名2 on 表名1.key=表名2.key;
'''
select * from 表名1,表名2; # 笛卡尔积
我们不会使用笛卡尔积来查询数据，效率太低
eg：
# 不推荐使用
mysql> select * from emp1,dep1 where emp1.dep_id=dep1.id;
+----+--------+--------+------+--------+-----+--------------+
| id | name | gender | age | dep_id | id | name |
+----+--------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
+----+--------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)
'''
代码演示：
# 内连接
mysql> select * from emp1 inner join dep1 on emp1.dep_id = dep1.id;
+----+--------+--------+------+--------+-----+--------------+
| id | name | gender | age | dep_id | id | name |
+----+--------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
+----+--------+--------+------+--------+-----+--------------+
5 rows in set (0.00 sec)
# 左连接
mysql> select * from emp1 left join dep1 on emp1.dep_id = dep1.id;
+----+--------+--------+------+--------+------+--------------+
| id | name | gender | age | dep_id | id | name |
+----+--------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | 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 |
+----+--------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
# 右连接
mysql> select * from emp1 right join dep1 on emp1.dep_id = dep1.id;
+------+--------+--------+------+--------+-----+--------------+
| id | name | gender | age | dep_id | id | name |
+------+--------+--------+------+--------+-----+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | female | 48 | 201 | 201 | 人力资源 |
| 3 | kevin | male | 18 | 201 | 201 | 人力资源 |
| 4 | nick | male | 28 | 202 | 202 | 销售 |
| 5 | owen | male | 18 | 203 | 203 | 运营 |
| NULL | NULL | NULL | NULL | NULL | 205 | 安保 |
+------+--------+--------+------+--------+-----+--------------+
6 rows in set (0.00 sec)
# 全连接
mysql> select * from emp1 left join dep1 on emp1.dep_id=dep1.id
-> union
-> select * from emp1 right join dep1 on emp1.dep_id=dep1.id;
+------+--------+--------+------+--------+------+--------------+
| id | name | gender | age | dep_id | id | name |
+------+--------+--------+------+--------+------+--------------+
| 1 | jason | male | 18 | 200 | 200 | 技术 |
| 2 | dragon | 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 | 安保 |
+------+--------+--------+------+--------+------+--------------+
7 rows in set (0.00 sec)
作业
1. 查询岗位名以及岗位包含的所有员工名字
# 使用group_concat关键字查询每个组下员工姓名
mysql> select post,group_concat(name) from emp group by post;
+-----------------------------+------------------------------------------------+
| post | group_concat(name) |
+-----------------------------+------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,僧龙 |
| sale | 拉拉,乐乐,西西,呵呵,哈哈 |
| teacher | sank,jenny,jack,owen,tony,kevin,tom |
| 浦东第一帅形象代言 | jason |
+-----------------------------+------------------------------------------------+
4 rows in set (0.00 sec)
2. 查询岗位名以及各岗位内包含的员工个数
# 每个员工的id号是唯一的，根据id的个数统计员工人数
mysql> select post,count(id) from emp group by post;
+-----------------------------+-----------+
| post | count(id) |
+-----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 浦东第一帅形象代言 | 1 |
+-----------------------------+-----------+
4 rows in set (0.00 sec)
3. 查询公司内男员工和女员工的个数
# 根据员工性别统计男员和女员工的人数
mysql> select sex,count(id) from emp group by sex;
+--------+-----------+
| sex | count(id) |
+--------+-----------+
| male | 10 |
| female | 8 |
+--------+-----------+
2 rows in set (0.00 sec)
4. 查询岗位名以及各岗位的平均薪资
# 根据岗位查询岗位的平均薪资
mysql> select post,avg(salary) from emp group by post;
+-----------------------------+---------------+
| post | avg(salary) |
+-----------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 浦东第一帅形象代言 | 7300.330000 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)
5. 查询岗位名以及各岗位的最高薪资
# 根据岗位查询每个员工的薪资，然后选择最高的薪资，使用max
mysql> select post,max(salary) from emp group by post;
+-----------------------------+-------------+
| post | max(salary) |
+-----------------------------+-------------+
| operation | 20000.00 |
| sale | 4000.33 |
| teacher | 1000000.31 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)
6. 查询岗位名以及各岗位的最低薪资
# 根据岗位查询每个员工的薪资，然后选择最低的薪资，使用min
mysql> select post,min(salary) from emp group by post;
+-----------------------------+-------------+
| post | min(salary) |
+-----------------------------+-------------+
| operation | 10000.13 |
| sale | 1000.37 |
| teacher | 2100.00 |
| 浦东第一帅形象代言 | 7300.33 |
+-----------------------------+-------------+
4 rows in set (0.00 sec)
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
# 根据性别查出所有的男员工和女员工以及他们的薪资，然后查询他们的平均薪资
mysql> select sex,avg(salary) from emp group by sex;
+--------+---------------+
| sex | avg(salary) |
+--------+---------------+
| male | 110920.077000 |
| female | 7250.183750 |
+--------+---------------+
2 rows in set (0.00 sec)
8. 统计各部门年龄在30岁以上的员工平均工资
# 先查询30岁上所有的员工和员工薪资，然后分组查询求平均薪资
mysql> select post,avg(salary) from emp where age>30 group by post;
+---------+---------------+
| post | avg(salary) |
+---------+---------------+
| sale | 2500.240000 |
| teacher | 255450.077500 |
+---------+---------------+
2 rows in set (0.00 sec)
9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
# 先查询10岁以上所有员工和员工的薪资，然后分组查询平均薪资，对查询的结果过滤出平均薪资大于1000的薪资的部门
mysql> select post,avg(salary) from emp where age>10 group by post having avg(salary)>1000;
+-----------------------------+---------------+
| post | avg(salary) |
+-----------------------------+---------------+
| operation | 16800.026000 |
| sale | 2600.294000 |
| teacher | 151842.901429 |
| 浦东第一帅形象代言 | 7300.330000 |
+-----------------------------+---------------+
4 rows in set (0.00 sec)
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 无需6万激活码！GitHub神秘组织3小时极速复刻Manus，手把手教你使用OpenManus搭建本
· Manus爆火，是硬核还是营销？
· 终于写完轮子一部分：tcp代理 了，记录一下
· 别再用vector＜bool＞了！Google高级工程师：这可能是STL最大的设计失误
· 单元测试从入门到精通