python 之 MySQL
操作表的SQL语句补充
修改表名
MySQL 通过 ALTER TABLE 语句来实现表名的修改，语法规则如下：
ALTER TABLE <旧表名> RENAME [TO] <新表名>；
TO 为可选参数，使用与否均不影响结果。
例
mysql> create database db4; # 建库
Query OK, 1 row affected (0.02 sec)
mysql> use db4; # 用库
Database changed
mysql> create table t1( # 建表
-> id int primary key auto_increment,
-> name varchar(32)
-> );
Query OK, 0 rows affected (0.07 sec)
mysql> show tables; # 查看所有表
+---------------+
| Tables_in_db4 |
+---------------+
| t1 |
+---------------+
1 row in set (0.01 sec)
mysql> alter table t1 rename re_t1; # 修改表名
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+---------------+
| Tables_in_db4 |
+---------------+
| re_t1 |
+---------------+
1 row in set (0.01 sec)
修改字段
修改字段名称
MySQL 中修改表字段名的语法规则如下：
ALTER TABLE <表名> CHANGE <旧字段名> <新字段名> <新数据类型>；
- 旧字段名：指修改前的字段名；
- 新字段名：指修改后的字段名；
- 新数据类型：指修改后的数据类型，如果不需要修改字段的数据类型，可以将新数据类型设置成与原来一样，但数据类型不能为空。
例
mysql> desc re_t1; # 查看原表结构
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | | NULL | |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
mysql> alter table re_t1 # 改字段名
-> change name username varchar(30);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc re_t1; # 查看表结构
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| username | varchar(30) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
修改字段数据类型
修改字段的数据类型就是把字段的数据类型转换成另一种数据类型。在 MySQL 中修改字段数据类型的语法规则如下：
ALTER TABLE <表名> MODIFY <字段名> <数据类型>
- 表名：指要修改数据类型的字段所在表的名称；
- 字段名：指需要修改的字段；
- 数据类型：指修改后字段的新数据类型。
例
mysql> alter table re_t1
-> modify username char(32);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc re_t1;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| username | char(32) | YES | | NULL | |
+----------+----------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
新增字段
在末尾添加字段
一个完整的字段包括字段名、数据类型和约束条件。MySQL 添加字段的语法格式如下：
ALTER TABLE <表名> ADD <新字段名><数据类型>[约束条件];
- <表名> 为数据表的名字；
- <新字段名> 为所要添加的字段的名字；
- <数据类型> 为所要添加的字段能存储数据的数据类型；
- [约束条件] 是可选的，用来对添加的字段进行约束。
例
mysql> alter table re_t1 add age int(4);
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> desc re_t1;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| username | char(32) | YES | | NULL | |
| age | int | YES | | NULL | |
+----------+----------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
在开头添加字段
MySQL 默认在表的最后位置添加新字段，如果希望在开头位置（第一列的前面）添加新字段，那么可以使用 FIRST 关键字，语法格式如下：
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] FIRST;
FIRST 关键字一般放在语句的末尾。
例
mysql> alter table re_t1 add name varchar(30) first;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc re_t1;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| name | varchar(30) | YES | | NULL | |
| id | int | NO | PRI | NULL | auto_increment |
| username | char(32) | YES | | NULL | |
| age | int | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
在中间位置添加字段
MySQL 除了允许在表的开头位置和末尾位置添加字段外，还允许在中间位置（指定的字段之后）添加字段，此时需要使用 AFTER 关键字，语法格式如下：
ALTER TABLE <表名> ADD <新字段名> <数据类型> [约束条件] AFTER <已经存在的字段名>;
AFTER 的作用是将新字段添加到某个已有字段后面。
ps：只能在某个已有字段的后面添加新字段，不能在它的前面添加新字段。
例
mysql> alter table re_t1 add gender char(1) after username;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc re_t1;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| name | varchar(30) | YES | | NULL | |
| id | int | NO | PRI | NULL | auto_increment |
| username | char(32) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
删除字段
删除字段是将数据表中的某个字段从表中移除，语法格式如下：
ALTER TABLE <表名> DROP <字段名>；
“字段名”指需要从表中删除的字段的名称。
mysql> alter table re_t1 drop name;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc re_t1;
+----------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| username | char(32) | YES | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | int | YES | | NULL | |
+----------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
表查询关键字
创建“emp”表，插入相关数据：
mysql> 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
-> );
Query OK, 0 rows affected, 2 warnings (0.04 sec)
#三个部门：教学，销售，运营
mysql> 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);
Query OK, 18 rows affected (0.01 sec)
Records: 18 Duplicates: 0 Warnings: 0
SELECT：数据表查询语句
在 MySQL 中，可以使用 SELECT 语句来查询数据。查询数据是指从数据库中根据需求，使用不同的查询方式来获取不同的数据，是使用频率最高、最重要的操作。
SELECT 的语法格式如下：
SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]
SELECT {*|<字段列名>}包含星号通配符的字段列表，表示所要查询字段的名称。FROM <表 1>，<表 2>…，表 1 和表 2 表示查询数据的来源，可以是单个或多个。WHERE <表达式>是可选项，如果选择该项，将限定查询数据必须满足该查询条件。GROUP BY< 字段 >，该子句告诉 MySQL 如何显示查询出来的数据，并按照指定的字段分组。[ORDER BY< 字段 >]，该子句告诉 MySQL 按什么样的顺序显示查询出来的数据，可以进行的排序有升序（ASC）和降序（DESC），默认情况下是升序。[LIMIT[<offset>，]<row count>]，该子句告诉 MySQL 每次显示查询出来的数据条数。
WHERE：条件查询数据
在 MySQL 中，如果需要有条件的从数据表中查询数据，可以使用 WHERE 关键字来指定查询条件。
使用 WHERE 关键字的语法格式如下：
SELECT {* | <字段列名>} FROM <表 1>, <表 2>…
[WHERE 条件1 [AND [OR]] 条件2.....
- 查询语句中可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
- 可以在 WHERE 子句中指定任何条件。
- 查询条件可以是：
- 带比较运算符和逻辑运算符的查询条件
- 带 BETWEEN AND 关键字的查询条件
- 带 IS NULL 关键字的查询条件
- 带 IN 关键字的查询条件
- 带 LIKE 关键字的查询条件
- 可以使用 AND 或者 OR 指定一个或多个条件。
- WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
- WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。
实例
多条件的查询语句:
在 WHERE 关键词后可以有多个查询条件，这样能够使查询结果更加精确。多个查询条件时用逻辑运算符 AND（&&）、OR（||）或 XOR 隔开。
- AND：记录满足所有查询条件时，才会被查询出来。
- OR：记录满足任意一个查询条件时，才会被查询出来。
- XOR：记录满足其中一个条件，并且不满足另一个条件时，才会被查询出来。
# 1.查询id大于等于3小于等于6的数据
mysql> select id,name from emp where id >= 3 and id <= 6;
+----+-------+
| id | name |
+----+-------+
| 3 | kevin |
| 4 | tony |
| 5 | owen |
| 6 | jack |
+----+-------+
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.查询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)
# 4.查询薪资不在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)
# 6.查询岗位描述为空的员工名与岗位名 针对null不能用等号，只能用is
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)
LIKE 模糊查询
在 MySQL 中，LIKE 关键字主要用于搜索匹配字段中的指定内容。其语法格式如下：
[NOT] LIKE '字符串'
- NOT ：可选参数，字段中的内容与指定的字符串不匹配时满足条件。
- 字符串：指定用来匹配的字符串。“字符串”可以是一个很完整的字符串，也可以包含通配符。
LIKE 关键字支持百分号“%”和下划线“_”通配符。
通配符是一种特殊语句，主要用来模糊查询。当不知道真正字符或者懒得输入完整名称时，可以使用通配符来代替一个或多个真正的字符。
# 1.查询员工姓名中包含o字母的员工姓名和薪资
# 在你刚开始接触mysql查询的时候，建议你按照查询的优先级顺序拼写出你的sql语句
"""
先是查哪张表 from emp
再是根据什么条件去查 where name like ‘%o%’
再是对查询出来的数据筛选展示部分 select name,salary
"""
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)
# 2.查询员工姓名是由四个字符组成的员工姓名与其薪资
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)
GROUP BY 分组查询
在 MySQL 中，GROUP BY 关键字可以根据一个或多个字段对查询结果进行分组。
使用 GROUP BY 关键字的语法格式如下：
GROUP BY <字段名>
“字段名”表示需要分组的字段名称，多个字段时用逗号隔开。
GROUP BY单独使用
单独使用 GROUP BY 关键字时，查询结果会只显示每个分组的第一条记录。
mysql> select name,post from emp group by post;
+-------+--------------------+
| name | post |
+-------+--------------------+
| jason | 浦东第一帅形象代言 |
| tom | teacher |
| 哈哈 | sale |
| 僧龙 | operation |
+-------+--------------------+
4 rows in set (0.00 sec)
'''结果中只显示了四条记录，这四条记录的 post 字段的值'''
GROUP BY 与聚合函数
在数据统计时，GROUP BY 关键字经常和聚合函数一起使用。
聚合函数包括 COUNT()，SUM()，AVG()，MAX() 和 MIN()。
COUNT() 用来统计记录的条数；
SUM() 用来计算字段值的总和；
AVG() 用来计算字段值的平均值；
MAX() 用来查询字段的最大值；
MIN() 用来查询字段的最小值。
# 以组为单位统计组内数据>>>聚合查询(聚集到一起合成为一个结果)
# 每个部门的最高工资
mysql> select post,max(salary) from emp group by post;
+--------------------+-------------+
| post | max(salary) |
+--------------------+-------------+
| 浦东第一帅形象代言 | 7300.33 |
| teacher | 1000000.31 |
| sale | 4000.33 |
| operation | 20000.00 |
+--------------------+-------------+
4 rows in set (0.00 sec)
# 补充:在显示的时候还可以给字段取别名
mysql> select post as '部门',max(salary) as '最高工资' from emp group by post;
+--------------------+------------+
| 部门 | 最高工资 |
+--------------------+------------+
| 浦东第一帅形象代言 | 7300.33 |
| teacher | 1000000.31 |
| sale | 4000.33 |
| operation | 20000.00 |
+--------------------+------------+
4 rows in set (0.00 sec)
'''as也可以省略 但是不推荐省 因为寓意不明确'''
# 每个部门的最低工资
mysql> select post,min(salary) from emp group by post;
+--------------------+-------------+
| post | min(salary) |
+--------------------+-------------+
| 浦东第一帅形象代言 | 7300.33 |
| teacher | 2100.00 |
| sale | 1000.37 |
| operation | 10000.13 |
+--------------------+-------------+
4 rows in set (0.00 sec)
# 每个部门的平均工资
mysql> select post,avg(salary) from emp group by post;
+--------------------+---------------+
| post | avg(salary) |
+--------------------+---------------+
| 浦东第一帅形象代言 | 7300.330000 |
| teacher | 151842.901429 |
| sale | 2600.294000 |
| operation | 16800.026000 |
+--------------------+---------------+
4 rows in set (0.00 sec)
# 每个部门的工资总和
mysql> select post,sum(salary) from emp group by post;
+--------------------+-------------+
| post | sum(salary) |
+--------------------+-------------+
| 浦东第一帅形象代言 | 7300.33 |
| teacher | 1062900.31 |
| sale | 13001.47 |
| operation | 84000.13 |
+--------------------+-------------+
4 rows in set (0.00 sec)
# 每个部门的人数
mysql> select post,count(id) from emp group by post;
+--------------------+-----------+
| post | count(id) |
+--------------------+-----------+
| 浦东第一帅形象代言 | 1 |
| teacher | 7 |
| sale | 5 |
| operation | 5 |
+--------------------+-----------+
4 rows in set (0.00 sec)
# 查询分组之后的部门名称和每个部门下所有的学生姓名
# group_concat（分组之后用）不仅可以用来显示除分组外字段还有拼接字符串的作用
mysql> select post,group_concat(name) from emp group by post;
+--------------------+-------------------------------------+
| post | group_concat(name) |
+--------------------+-------------------------------------+
| operation | 僧龙,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 哈哈,呵呵,西西,乐乐,拉拉 |
| teacher | tom,kevin,tony,owen,jack,jenny,sank |
| 浦东第一帅形象代言 | jason |
+--------------------+-------------------------------------+
4 rows in set (0.00 sec)
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 | tom_SB,kevin_SB,tony_SB,owen_SB,jack_SB,jenny_SB,sank_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 | 僧龙: 10000.13,程咬金: 20000.00,程咬银: 19000.00,程咬铜: 18000.00,程咬铁: 17000.00 |
| sale | 哈哈: 3000.13,呵呵: 2000.35,西西: 1000.37,乐乐: 3000.29,拉拉: 4000.33 |
| teacher | tom: 1000000.31,kevin: 8300.00,tony: 3500.00,owen: 2100.00,jack: 9000.00,jenny: 30000.00,sank: 10000.00 |
| 浦东第一帅形象代言 | 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 | 10000.13,20000.00,19000.00,18000.00,17000.00 |
| sale | 3000.13,2000.35,1000.37,3000.29,4000.33 |
| teacher | 1000000.31,8300.00,3500.00,2100.00,9000.00,30000.00,10000.00 |
| 浦东第一帅形象代言 | 7300.33 |
+--------------------+--------------------------------------------------------------+
4 rows in set (0.00 sec)
HAVING：过滤分组
在 MySQL 中，可以使用 HAVING 关键字对分组后的数据进行过滤。
使用 HAVING 关键字的语法格式如下：
HAVING <查询条件>
HAVING 关键字和 WHERE 关键字都可以用来过滤数据，且 HAVING 支持 WHERE 关键字中所有的操作符和语法。
WHERE 和 HAVING 关键字也存在以下几点差异：
- 一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
- WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
- WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
- WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
- WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。
# 1、统计各部门年龄在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 去重
DISTINCT 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户。
DISTINCT 关键字的语法格式为：
SELECT DISTINCT <字段名> FROM <表名>;
“字段名”为需要消除重复记录的字段名称，多个字段时用逗号隔开。
使用 DISTINCT 关键字时需要注意以下几点：
- DISTINCT 关键字只能在 SELECT 语句中使用。
- 在对一个或多个字段去重时，DISTINCT 关键字必须在所有字段的最前面。
- 如果 DISTINCT 关键字后有多个字段，则会对多个字段进行组合去重，也就是说，只有多个字段组合起来完全是一样的情况下才会被去重。
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 <字段名> [ASC|DESC]
字段名：表示需要排序的字段名称，多个字段时用逗号隔开。
ASC | DESC：
ASC表示字段按升序排序，为默认值；
DESC表示字段按降序排序。
使用 ORDER BY 关键字应该注意以下几个方面：
- ORDER BY 关键字后可以跟子查询（关于子查询后面教程会详细讲解，这里了解即可）。
- 当排序的字段中存在空值时，ORDER BY 会将该空值作为最小值来对待。
- ORDER BY 指定多个字段进行排序时，MySQL 会按照字段的顺序从左到右依次进行排序。
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.00 sec)
#先按照age降序排，在年轻相同的情况下再按照薪资升序排
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)
# 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
mysql> select post,avg(salary) from emp
-> where age > 10
-> group by post
-> having avg(salary) > 1000
-> order by avg(salary);
+--------------------+---------------+
| post | avg(salary) |
+--------------------+---------------+
| sale | 2600.294000 |
| 浦东第一帅形象代言 | 7300.330000 |
| operation | 16800.026000 |
| teacher | 151842.901429 |
+--------------------+---------------+
4 rows in set (0.00 sec)
LIMIT：限制查询结果的条数
当数据表中有上万条数据时，一次性查询出表中的全部数据会降低数据返回的速度，同时给数据库服务器造成很大的压力。这时就可以用 LIMIT 关键字来限制查询结果返回的条数（分页处理）。
LIMIT 是 MySQL 中的一个特殊关键字，用于指定查询结果从哪条记录开始显示，一共显示多少条记录。
LIMIT 关键字有 3 种使用方式，即指定初始位置、不指定初始位置以及与 OFFSET 组合使用。
# 限制展示条数
mysql> select * from emp limit 3;
+----+-------+------+-----+------------+--------------------+--------------+------------+--------+-----------+
| 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 |
+----+-------+------+-----+------------+--------------------+--------------+------------+--------+-----------+
3 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)
# 分页显示
mysql> select * from emp limit 0,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.00 sec)
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)
REGEXP：正则表达式
正则表达式主要用来查询和替换符合某个模式（规则）的文本内容。例如，从一个文件中提取电话号码，查找一篇文章中重复的单词、替换文章中的敏感语汇等，这些地方都可以使用正则表达式。正则表达式强大且灵活，常用于非常复杂的查询。
MySQL 中，使用 REGEXP 关键字指定正则表达式的字符匹配模式，其基本语法格式如下：
属性名 REGEXP '匹配方式'
“属性名”表示需要查询的字段名称；“匹配方式”表示以哪种方式来匹配查询。“匹配方式”中有很多的模式匹配字符，它们分别表示不同的意思。下表列出了 REGEXP 操作符中常用的匹配方式。
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语句的条件
eg: 类似以日常生活中解决问题的方式
第一步干什么
第二步基于第一步的结果在做操作 ...
连表操作
先将所有涉及到结果的表全部拼接到一起形成一张大表，然后从大表中查询数据
创建 “dep1”表 和 “emp1”，插入相关数据：
mysql> create table dep1(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> 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
-> );
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> #插入数据
mysql> insert into dep1 values
-> (200,'技术'),
-> (201,'人力资源'),
-> (202,'销售'),
-> (203,'运营'),
-> (205,'安保')
-> ;
Query OK, 5 rows affected (0.01 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql>
mysql> 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);
Query OK, 6 rows affected (0.01 sec)
Records: 6 Duplicates: 0 Warnings: 0
子查询
子查询是 MySQL 中比较常用的查询方法，通过子查询可以实现多表查询。子查询指将一个查询语句嵌套在另一个查询语句中。子查询可以在 SELECT、UPDATE 和 DELETE 语句中使用，而且可以进行多层嵌套。在实际开发时，子查询经常出现在 WHERE 子句中。
子查询在 WHERE 中的语法格式如下：
WHERE <表达式> <操作符> (子查询)
操作符可以是比较运算符和 IN、NOT IN、EXISTS、NOT EXISTS 等关键字。
'''查询jason的部门名称'''
# 1.先获取jason的部门编号
mysql> select dep_id from emp1 where name = 'jason'; # 200
+--------+
| dep_id |
+--------+
| 200 |
+--------+
1 row in set (0.00 sec)
# 2.根据部门编号获取部门名称
mysql> select name from dep1 where id = 200;
+------+
| name |
+------+
| 技术 |
+------+
1 row in set (0.00 sec)
'''子查询'''
mysql> select name from dep1 where id = (select dep_id from emp1 where name = 'jason');
+------+
| name |
+------+
| 技术 |
+------+
1 row in set (0.00 sec)
连表操作
将所有涉及到结果的表全部连接到一起形成一张大表，然后从大表中查询数据
select * from emp1,dep1; # 笛卡尔积
不会使用笛卡尔积来求数据，效率太低，连表有专门的语法
| 关键字 | 操作 | 描述 |
|---|---|---|
| inner join | 内连接 | 只拼接两边都有的字段数据 |
| left join | 左连接 | 以左表为基准，展示所有数据，没有对应数据则null填充 |
| right join | 右连接 | 以右表为基准，展示所有数据，没有对应数据则null填充 |
| union | 全连接 | 将两张表全连接 |
# 左连接
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. 查询岗位名以及岗位包含的所有员工名字
mysql> select post as '岗位',group_concat(name) as '员工姓名' from emp
-> group by post;
+--------------------+-------------------------------------+
| 岗位 | 员工姓名 |
+--------------------+-------------------------------------+
| operation | 僧龙,程咬金,程咬银,程咬铜,程咬铁 |
| sale | 哈哈,呵呵,西西,乐乐,拉拉 |
| teacher | tom,kevin,tony,owen,jack,jenny,sank |
| 浦东第一帅形象代言 | jason |
+--------------------+-------------------------------------+
4 rows in set (0.00 sec)
# 2. 查询岗位名以及各岗位内包含的员工个数
mysql> select post as '岗位',count(id) as '员工数' from emp
-> group by post;
+--------------------+--------+
| 岗位 | 员工数 |
+--------------------+--------+
| 浦东第一帅形象代言 | 1 |
| teacher | 7 |
| sale | 5 |
| operation | 5 |
+--------------------+--------+
4 rows in set (0.00 sec)
# 3. 查询公司内男员工和女员工的个数
mysql> select sex as '性别',count(sex) as '员工数' from emp
-> group by sex;
+--------+--------+
| 性别 | 员工数 |
+--------+--------+
| male | 10 |
| female | 8 |
+--------+--------+
2 rows in set (0.00 sec)
# 4. 查询岗位名以及各岗位的平均薪资
mysql> select post as '岗位',avg(salary) as '平均薪资' from emp
-> group by post;
+--------------------+---------------+
| 岗位 | 平均薪资 |
+--------------------+---------------+
| 浦东第一帅形象代言 | 7300.330000 |
| teacher | 151842.901429 |
| sale | 2600.294000 |
| operation | 16800.026000 |
+--------------------+---------------+
4 rows in set (0.00 sec)
# 5. 查询岗位名以及各岗位的最高薪资
mysql> select post as '岗位',max(salary) as '最高薪资' from emp
-> group by post;
+--------------------+------------+
| 岗位 | 最高薪资 |
+--------------------+------------+
| 浦东第一帅形象代言 | 7300.33 |
| teacher | 1000000.31 |
| sale | 4000.33 |
| operation | 20000.00 |
+--------------------+------------+
4 rows in set (0.00 sec)
# 6. 查询岗位名以及各岗位的最低薪资
mysql> select post as '岗位',min(salary) as '最低薪资' from emp
-> group by post;
+--------------------+----------+
| 岗位 | 最低薪资 |
+--------------------+----------+
| 浦东第一帅形象代言 | 7300.33 |
| teacher | 2100.00 |
| sale | 1000.37 |
| operation | 10000.13 |
+--------------------+----------+
4 rows in set (0.00 sec)
# 7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
mysql> select sex as '性别',group_concat(name) as '员工',avg(salary) as '平均薪资' from emp
-> group by sex;
+--------+---------------------------------------------------------+---------------+
| 性别 | 员工 | 平均薪资 |
+--------+---------------------------------------------------------+---------------+
| male | jason,tom,kevin,tony,owen,jenny,sank,僧龙,程咬金,程咬铜 | 110920.077000 |
| female | jack,哈哈,呵呵,西西,乐乐,拉拉,程咬银,程咬铁 | 7250.183750 |
+--------+---------------------------------------------------------+---------------+
2 rows in set (0.00 sec)
# 8. 统计各部门年龄在30岁以上的员工平均工资
mysql> select post as '部门',avg(salary) as '平均薪资' from emp
-> where age >= 30
-> group by post;
+---------+---------------+
| 部门 | 平均薪资 |
+---------+---------------+
| teacher | 255450.077500 |
| sale | 2500.240000 |
+---------+---------------+
2 rows in set (0.00 sec)
# 9. 统计各部门年龄在10岁以上的员工平均工资，并且保留平均工资大于1000的部门，然后对平均工资进行排序
mysql> select post as '部门',avg(salary) from emp
-> where age > 10
-> group by post
-> having avg(salary) > 1000
-> order by avg(salary);
+--------------------+---------------+
| 部门 | avg(salary) |
+--------------------+---------------+
| sale | 2600.294000 |
| 浦东第一帅形象代言 | 7300.330000 |
| operation | 16800.026000 |
| teacher | 151842.901429 |
+--------------------+---------------+
4 rows in set (0.00 sec)
浙公网安备 33010602011771号