【4.0】数据库知识点小结数据库辉煌
【一】准备数据
- 创建库
create database day03;
# Query OK, 1 row affected (0.00 sec)
- 创建表
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 (0.61 sec)
- 查看表
desc emp;
+--------------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(3) unsigned | NO | | 28 | |
| hire_date | date | NO | | NULL | |
| post | varchar(50) | YES | | NULL | |
| post_comment | varchar(100) | YES | | NULL | |
| salary | double(15,2) | YES | | NULL | |
| office | int(11) | YES | | NULL | |
| depart_id | int(11) | YES | | NULL | |
+--------------+-----------------------+------+-----+---------+----------------+
10 rows in set (0.01 sec)
- 插入数据
insert into emp(name, sex, age, hire_date, post, salary, office, depart_id) values
("dream", "male", 78, '20220306', "陌夜痴梦久生情", 730.33, 401, 1), # 以下是教学部
("mengmeng", "female", 25, '20220102', "teacher", 12000.50, 401, 1),
("xiaomeng", "male", 35, '20190607', "teacher", 15000.99, 401, 1),
("xiaona", "female", 29, '20180906', "teacher", 11000.80, 401, 1),
("xiaoqi", "female", 27, '20220806', "teacher", 13000.70, 401, 1),
("suimeng", "male", 33, '20230306', "teacher", 14000.62, 401, 1), # 以下是销售部
("娜娜", "female", 69, '20100307', "sale", 300.13, 402, 2),
("芳芳", "male", 45, '20140518', "sale", 400.45, 402, 2),
("小明", "male", 34, '20160103', "sale", 350.80, 402, 2),
("亚洲", "female", 42, '20170227', "sale", 320.99, 402, 2),
("华华", "female", 55, '20180319', "sale", 380.75, 402, 2),
("田七", "male", 44, '20230808', "sale", 420.33, 402, 2), # 以下是运行部
("大古", "female", 66, '20180509', "operation", 630.33, 403, 3),
("张三", "male", 51, '20191001', "operation", 410.25, 403, 3),
("李四", "male", 47, '20200512', "operation", 330.62, 403, 3),
("王五", "female", 39, '20210203', "operation", 370.98, 403, 3),
("赵六", "female", 36, '20220724', "operation", 390.15, 403, 3);
# Query OK, 17 rows affected (0.17 sec)
# Records: 17 Duplicates: 0 Warnings: 0
- 查看数据
select * from emp;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+23 rows in set (0.00 sec)
格式化美化输出数据
select * from emp\G;
*************************** 1. row ***************************
id: 1
name: dream
sex: male
age: 78
hire_date: 2022-03-06
post: 陌夜痴梦久生情
post_comment: NULL
salary: 7300.33
office: 401
depart_id: 1
*************************** 2. row ***************************
id: 2
name: mengmeng
sex: female
age: 25
hire_date: 2022-01-02
post: teacher
post_comment: NULL
salary: 12000.50
office: 401
depart_id: 1
*************************** 3. row ***************************
id: 3
name: xiaomeng
sex: male
age: 35
hire_date: 2019-06-07
post: teacher
post_comment: NULL
salary: 15000.99
office: 401
depart_id: 1
*************************** 4. row ***************************
id: 4
name: xiaona
sex: female
age: 29
hire_date: 2018-09-06
post: teacher
post_comment: NULL
salary: 11000.80
office: 401
depart_id: 1
*************************** 5. row ***************************
id: 5
name: xiaoqi
sex: female
age: 27
hire_date: 2022-08-06
post: teacher
post_comment: NULL
salary: 13000.70
office: 401
depart_id: 1
*************************** 6. row ***************************
id: 6
name: suimeng
sex: male
age: 33
hire_date: 2023-03-06
post: teacher
post_comment: NULL
salary: 14000.62
office: 401
depart_id: 1
*************************** 7. row ***************************
id: 7
name: dream
sex: male
age: 78
hire_date: 2022-03-06
post: 陌夜痴梦久生情
post_comment: NULL
salary: 730.33
office: 401
depart_id: 1
*************************** 8. row ***************************
id: 8
name: mengmeng
sex: female
age: 25
hire_date: 2022-01-02
post: teacher
post_comment: NULL
salary: 12000.50
office: 401
depart_id: 1
*************************** 9. row ***************************
id: 9
name: xiaomeng
sex: male
age: 35
hire_date: 2019-06-07
post: teacher
post_comment: NULL
salary: 15000.99
office: 401
depart_id: 1
*************************** 10. row ***************************
id: 10
name: xiaona
sex: female
age: 29
hire_date: 2018-09-06
post: teacher
post_comment: NULL
salary: 11000.80
office: 401
depart_id: 1
*************************** 11. row ***************************
id: 11
name: xiaoqi
sex: female
age: 27
hire_date: 2022-08-06
post: teacher
post_comment: NULL
salary: 13000.70
office: 401
depart_id: 1
*************************** 12. row ***************************
id: 12
name: suimeng
sex: male
age: 33
hire_date: 2023-03-06
post: teacher
post_comment: NULL
salary: 14000.62
office: 401
depart_id: 1
*************************** 13. row ***************************
id: 13
name: 娜娜
sex: female
age: 69
hire_date: 2010-03-07
post: sale
post_comment: NULL
salary: 300.13
office: 402
depart_id: 2
*************************** 14. row ***************************
id: 14
name: 芳芳
sex: male
age: 45
hire_date: 2014-05-18
post: sale
post_comment: NULL
salary: 400.45
office: 402
depart_id: 2
*************************** 15. row ***************************
id: 15
name: 小明
sex: male
age: 34
hire_date: 2016-01-03
post: sale
post_comment: NULL
salary: 350.80
office: 402
depart_id: 2
*************************** 16. row ***************************
id: 16
name: 亚洲
sex: female
age: 42
hire_date: 2017-02-27
post: sale
post_comment: NULL
salary: 320.99
office: 402
depart_id: 2
*************************** 17. row ***************************
id: 17
name: 华华
sex: female
age: 55
hire_date: 2018-03-19
post: sale
post_comment: NULL
salary: 380.75
office: 402
depart_id: 2
*************************** 18. row ***************************
id: 18
name: 田七
sex: male
age: 44
hire_date: 2023-08-08
post: sale
post_comment: NULL
salary: 420.33
office: 402
depart_id: 2
*************************** 19. row ***************************
id: 19
name: 大古
sex: female
age: 66
hire_date: 2018-05-09
post: operation
post_comment: NULL
salary: 630.33
office: 403
depart_id: 3
*************************** 20. row ***************************
id: 20
name: 张三
sex: male
age: 51
hire_date: 2019-10-01
post: operation
post_comment: NULL
salary: 410.25
office: 403
depart_id: 3
*************************** 21. row ***************************
id: 21
name: 李四
sex: male
age: 47
hire_date: 2020-05-12
post: operation
post_comment: NULL
salary: 330.62
office: 403
depart_id: 3
*************************** 22. row ***************************
id: 22
name: 王五
sex: female
age: 39
hire_date: 2021-02-03
post: operation
post_comment: NULL
salary: 370.98
office: 403
depart_id: 3
*************************** 23. row ***************************
id: 23
name: 赵六
sex: female
age: 36
hire_date: 2022-07-24
post: operation
post_comment: NULL
salary: 390.15
office: 403
depart_id: 3
23 rows in set (0.00 sec)
【二】关键字的执行顺序
- 书写顺序
select id,name from emp where id > 3;
- 执行顺序
from
where
select
- 虽然执行顺序和书写顺序不一致，但是可以按照书写顺序写SQL语句
# 先用 * 占位，再去补全完整的 SQL 语句
select * from * where *
# * 替换成想要的字段
【三】筛选条件之 where
【1】作用
- 对整体数据的筛选
【2】查询3<=id<=6的数据
- 查询数据方式一
select id,name,age from emp where id >=3 and id <=6;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 3 | xiaomeng | 35 |
| 4 | xiaona | 29 |
| 5 | xiaoqi | 27 |
| 6 | suimeng | 33 |
+----+----------+-----+
4 rows in set (0.00 sec)
- 查询数据方式二
select id,name,age from emp where id between 3 and 6;
+----+----------+-----+
| id | name | age |
+----+----------+-----+
| 3 | xiaomeng | 35 |
| 4 | xiaona | 29 |
| 5 | xiaoqi | 27 |
| 6 | suimeng | 33 |
+----+----------+-----+
4 rows in set (0.00 sec)
【3】查询 薪资是1w2或者1w3或者7300 的数据
- 查询数据方式一
select * from emp where salary=12000.50 or salary = 13000.70 or salary = 7300.33;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+5 rows in set (0.00 sec)
- 查询方式二
select * from emp where salary in (12000.50,13000.70,7300.33);
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+5 rows in set (0.00 sec)
【3】查询 员工姓名中包含字母o的姓名和薪资
模糊查询：like
% 任意
- 任意单个字符
- 查询数据
select name,salary from emp where name like "%o%";
+----------+----------+
| name | salary |
+----------+----------+
| xiaomeng | 15000.99 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
| xiaomeng | 15000.99 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+----------+----------+
6 rows in set (0.00 sec)
【4】查询员工姓名是由六个字符组成的姓名和薪资
- 查询数据方式一
select name,salary from emp where name like "______";
+--------+----------+
| name | salary |
+--------+----------+
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+--------+----------+
4 rows in set (0.00 sec)
- 查询数据方式二
select name,salary from emp where char_length(name) = 6;
+--------+----------+
| name | salary |
+--------+----------+
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
| xiaona | 11000.80 |
| xiaoqi | 13000.70 |
+--------+----------+
4 rows in set (0.00 sec)
【5】查询 id<3 或者 id>6 的数据
select * from emp where id not between 3 and 6;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+19 rows in set (0.00 sec)
【6】查询岗位描述为空的员工姓名和岗位名
针对 null 不能用 = ，而是要用 is
- 查询数据
select * from emp where post_comment is null;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+23 rows in set (0.00 sec)
【四】约束条件之group by(分组)
什么时候需要分组？
参考关键字：每个、平均、最高、最低
【1】按照部门分组
- 查询数据
select * from emp group by post;
# ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'day03.emp.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
-
关闭这个严格模式
-
模糊查询所有严格模式
show variables like "%mode";
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value
|
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| block_encryption_mode | aes-128-ecb
|
| gtid_mode | OFF
|
| innodb_autoinc_lock_mode | 1
|
| innodb_strict_mode | ON
|
| offline_mode | OFF
|
| pseudo_slave_mode | OFF
|
| rbr_exec_mode | STRICT
|
| slave_exec_mode | STRICT
|
| sql_mode | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+--------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set, 1 warning (0.00 sec)
- 替换严格模式
set global sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
SET GLOBAL sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER';
- 查询数据
select * from emp group by post;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+4 rows in set (0.00 sec)
拿到每一个部门的第一行数据
最小的操作单位应该是组，而不是组内的单个数据
这条命令在没有设置严格模式的时候是可以执行的，返回的数据是每组的第一条数据
但是分组不应该以单条数据为参考，而是要以组为操作单位
如果设置了严格模式，上述命令会直接报错
也就是上面的那个错误
- 设置严格模式
set global sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# Query OK, 0 rows affected (0.00 sec)
设置严格模式后，按照什么分组就只能拿到什么
其他字段不能直接获取，获取其他数据需要借助其他方法
- 查询数据
select post from emp group by post;
+-----------------------+
| post |
+-----------------------+
| operation |
| sale |
| teacher |
| 陌夜痴梦久生情 |
+-----------------------+
4 rows in set (0.00 sec)
【2】获取每个部门的最高薪资
聚合函数：max - 取最大值
可以利用as关键字给字段起别名，或者默认不写
但是不推荐，如果忽略语义不明确，容易错乱
- 查询数据
select post,max(salary) from emp group by post;
+-----------------------+-------------+
| post | max(salary) |
+-----------------------+-------------+
| operation | 630.33 |
| sale | 420.33 |
| teacher | 15000.99 |
| 陌夜痴梦久生情 | 7300.33 |
+-----------------------+-------------+
4 rows in set (0.00 sec)
- 查询数据指定别名
select post as "部门" ,max(salary) as "最高薪资" from emp group by post;
+-----------------------+--------------+
| 部门 | 最高薪资 |
+-----------------------+--------------+
| operation | 630.33 |
| sale | 420.33 |
| teacher | 15000.99 |
| 陌夜痴梦久生情 | 7300.33 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【3】获取每个部门的最低薪资
聚合函数：min- 取最大值
- 查询数据
select post as "部门" ,min(salary) as "最低薪资" from emp group by post;
+-----------------------+--------------+
| 部门 | 最低薪资 |
+-----------------------+--------------+
| operation | 330.62 |
| sale | 300.13 |
| teacher | 11000.80 |
| 陌夜痴梦久生情 | 730.33 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【4】获取每个部门的平均薪资
聚合函数：avg- 取最大值
- 查询数据
select post as "部门" ,avg(salary) as "平均薪资" from emp group by post;
+-----------------------+--------------+
| 部门 | 平均薪资 |
+-----------------------+--------------+
| operation | 426.466000 |
| sale | 362.241667 |
| teacher | 13000.722000 |
| 陌夜痴梦久生情 | 4015.330000 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【5】获取每个部门的薪资总和
聚合函数：sum- 取最大值
- 查询数据
select post as "部门" ,sum(salary) as "薪资总和" from emp group by post;
+-----------------------+--------------+
| 部门 | 薪资总和 |
+-----------------------+--------------+
| operation | 2132.33 |
| sale | 2173.45 |
| teacher | 130007.22 |
| 陌夜痴梦久生情 | 8030.66 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【6】获取每个部门的人数
聚合函数：count- 取最大值
- 查询数据
select post as "部门" ,count(salary) as "部门的人数" from emp group by post;
+-----------------------+-----------------+
| 部门 | 部门的人数 |
+-----------------------+-----------------+
| operation | 5 |
| sale | 6 |
| teacher | 10 |
| 陌夜痴梦久生情 | 2 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)
select post as "部门" ,count(id) as "部门的人数" from emp group by post;
+-----------------------+-----------------+
| 部门 | 部门的人数 |
+-----------------------+-----------------+
| operation | 5 |
| sale | 6 |
| teacher | 10 |
| 陌夜痴梦久生情 | 2 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)
select post as "部门" ,count(age) as "部门的人数" from emp group by post;
+-----------------------+-----------------+
| 部门 | 部门的人数 |
+-----------------------+-----------------+
| operation | 5 |
| sale | 6 |
| teacher | 10 |
| 陌夜痴梦久生情 | 2 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)
不能对null计数
select post as "部门" ,count(post_comment) as "部门的人数" from emp group by post;
+-----------------------+-----------------+
| 部门 | 部门的人数 |
+-----------------------+-----------------+
| operation | 0 |
| sale | 0 |
| teacher | 0 |
| 陌夜痴梦久生情 | 0 |
+-----------------------+-----------------+
4 rows in set (0.00 sec)
【7】查询分组之后的部门名称和每个部门下所有的员工姓名
聚合函数：group_concat- 获得分组之后的具体的值
不单单支持获取分组之后的其他字段值，还支持拼接操作
- 查询数据
select post,group_concat(name) from emp group by post;
+-----------------------+---------------------------------------------------------------------------------+
| post | group_concat(name) |
+-----------------------+---------------------------------------------------------------------------------+
| operation | 大古,张三,李四,王五,赵六 |
| sale | 娜娜,芳芳,小明,亚洲,华华,田七 |
| teacher | mengmeng,xiaomeng,xiaona,xiaoqi,suimeng,mengmeng,xiaomeng,xiaona,xiaoqi,suimeng |
| 陌夜痴梦久生情 | dream,dream |
+-----------------------+---------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
- 拼接数据
select post,group_concat(name,'_drm') from emp group by post;
+-----------------------+-------------------------------------------------------------------------------------------------------------------------+
| post | group_concat(name,'_drm')
|
+-----------------------+-------------------------------------------------------------------------------------------------------------------------+
| operation | 大古_drm,张三_drm,李四_drm,王五_drm,赵六_drm
|
| sale | 娜娜_drm,芳芳_drm,小明_drm,亚洲_drm,华华_drm,田七_drm
|
| teacher | mengmeng_drm,xiaomeng_drm,xiaona_drm,xiaoqi_drm,suimeng_drm,mengmeng_drm,xiaomeng_drm,xiaona_drm,xiaoqi_drm,suimeng_drm |
| 陌夜痴梦久生情 | dream_drm,dream_drm
|
+-----------------------+-------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
- 查询多条数据
select post,group_concat(name,':',salary) from emp group by post;
+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| post | group_concat(name,':',salary)
|
+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| operation | 大古:630.33,张三:410.25,李四:330.62,王五:370.98,赵六:390.15
|
| sale | 娜娜:300.13,芳芳:400.45,小明:350.80,亚洲:320.99,华华:380.75,田七:420.33
|
| teacher | mengmeng:12000.50,xiaomeng:15000.99,xiaona:11000.80,xiaoqi:13000.70,suimeng:14000.62,mengmeng:12000.50,xiaomeng:15000.99,xiaona:11000.80,xiaoqi:13000.70,suimeng:14000.62 |
| 陌夜痴梦久生情 | dream:7300.33,dream:730.33
|
+-----------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)
不分组之前用concat
- 查询数据
select concat("NAME:",name),concat("SALARY:",salary) from emp;
+----------------------+--------------------------+
| concat("NAME:",name) | concat("SALARY:",salary) |
+----------------------+--------------------------+
| NAME:dream | SALARY:7300.33 |
| NAME:mengmeng | SALARY:12000.50 |
| NAME:xiaomeng | SALARY:15000.99 |
| NAME:xiaona | SALARY:11000.80 |
| NAME:xiaoqi | SALARY:13000.70 |
| NAME:suimeng | SALARY:14000.62 |
| NAME:dream | SALARY:730.33 |
| NAME:mengmeng | SALARY:12000.50 |
| NAME:xiaomeng | SALARY:15000.99 |
| NAME:xiaona | SALARY:11000.80 |
| NAME:xiaoqi | SALARY:13000.70 |
| NAME:suimeng | SALARY:14000.62 |
| NAME:娜娜 | SALARY:300.13 |
| NAME:芳芳 | SALARY:400.45 |
| NAME:小明 | SALARY:350.80 |
| NAME:亚洲 | SALARY:320.99 |
| NAME:华华 | SALARY:380.75 |
| NAME:田七 | SALARY:420.33 |
| NAME:大古 | SALARY:630.33 |
| NAME:张三 | SALARY:410.25 |
| NAME:李四 | SALARY:330.62 |
| NAME:王五 | SALARY:370.98 |
| NAME:赵六 | SALARY:390.15 |
+----------------------+--------------------------+
23 rows in set (0.00 sec)
as 语法不单单可以给字段起别名，还可以给表取别名
只能临时起别名
- 查数据
select emp.id,emp.name from emp;
+----+----------+
| id | name |
+----+----------+
| 1 | dream |
| 2 | mengmeng |
| 3 | xiaomeng |
| 4 | xiaona |
| 5 | xiaoqi |
| 6 | suimeng |
| 7 | dream |
| 8 | mengmeng |
| 9 | xiaomeng |
| 10 | xiaona |
| 11 | xiaoqi |
| 12 | suimeng |
| 13 | 娜娜 |
| 14 | 芳芳 |
| 15 | 小明 |
| 16 | 亚洲 |
| 17 | 华华 |
| 18 | 田七 |
| 19 | 大古 |
| 20 | 张三 |
| 21 | 李四 |
| 22 | 王五 |
| 23 | 赵六 |
+----+----------+
23 rows in set (0.00 sec)
- 查数据起别名
select emp.id,emp.name from emp as ti;
# ERROR 1054 (42S22): Unknown column 'emp.id' in 'field list'
select ti.id,ti.name from emp as ti;
+----+----------+
| id | name |
+----+----------+
| 1 | dream |
| 2 | mengmeng |
| 3 | xiaomeng |
| 4 | xiaona |
| 5 | xiaoqi |
| 6 | suimeng |
| 7 | dream |
| 8 | mengmeng |
| 9 | xiaomeng |
| 10 | xiaona |
| 11 | xiaoqi |
| 12 | suimeng |
| 13 | 娜娜 |
| 14 | 芳芳 |
| 15 | 小明 |
| 16 | 亚洲 |
| 17 | 华华 |
| 18 | 田七 |
| 19 | 大古 |
| 20 | 张三 |
| 21 | 李四 |
| 22 | 王五 |
| 23 | 赵六 |
+----+----------+
23 rows in set (0.00 sec)
【8】查询每个人的年薪(12)
直接对查询到的数据进行运算
- 查询数据
select name,salary*12 from emp;
+----------+-----------+
| name | salary*12 |
+----------+-----------+
| dream | 87603.96 |
| mengmeng | 144006.00 |
| xiaomeng | 180011.88 |
| xiaona | 132009.60 |
| xiaoqi | 156008.40 |
| suimeng | 168007.44 |
| dream | 8763.96 |
| mengmeng | 144006.00 |
| xiaomeng | 180011.88 |
| xiaona | 132009.60 |
| xiaoqi | 156008.40 |
| suimeng | 168007.44 |
| 娜娜 | 3601.56 |
| 芳芳 | 4805.40 |
| 小明 | 4209.60 |
| 亚洲 | 3851.88 |
| 华华 | 4569.00 |
| 田七 | 5043.96 |
| 大古 | 7563.96 |
| 张三 | 4923.00 |
| 李四 | 3967.44 |
| 王五 | 4451.76 |
| 赵六 | 4681.80 |
+----------+-----------+
23 rows in set (0.00 sec)
【五】约束条件之 group by(分组) 注意事项
-
关键字 where 和 group by 同时出现的时候，group by 必须在 where 后面
- where 先对整体数据进行过滤
- group by 再对数据进行分组
-
where 筛选条件不能使用聚合函数
- 不分组，默认整张表就是一组
-
聚合函数只能在分组之后使用
- 查询数据
select id,name,age from emp where max(salary) > 3000; # ERROR 1111 (HY000): Invalid use of group function- 查询数据
select max(salary) from emp;+-------------+ | max(salary) | +-------------+ | 15000.99 | +-------------+ 1 row in set (0.00 sec)
【1】统计各部门年龄在 30 岁以上的员工的平均薪资
- 先求所有年龄大于30岁的员工
select * from emp where age >30;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+17 rows in set (0.00 sec)
- 再对结果进行分组
select * from emp where age >30 group by post;
- 语法纠正
select post,avg(salary) from emp where age >30 group by post;
+-----------------------+--------------+
| post | avg(salary) |
+-----------------------+--------------+
| operation | 426.466000 |
| sale | 362.241667 |
| teacher | 14500.805000 |
| 陌夜痴梦久生情 | 4015.330000 |
+-----------------------+--------------+
4 rows in set (0.00 sec)
【六】约束条件之having(分组之后筛选)
having的语法和where是一致的
只不过having是在分组之后进行的过滤操作
即having是可以直接使用聚合函数的
【1】统计各部门年龄在 30 岁以上的员工的工资，并且保留平均薪资大于1w的部门
- 查询数据
select post,avg(salary) from emp
where age >30
group by post
having avg(salary) > 10000
;
+---------+--------------+
| post | avg(salary) |
+---------+--------------+
| teacher | 14500.805000 |
+---------+--------------+
1 row in set (0.00 sec)
【七】约束条件之distinct(去重)
必须是完全一样的数据才可以去重
一定要注意主键的问题
在主键存在的情况下是一定不可能去重的
- 查询数据
select distinct id,age from emp;
+----+-----+
| id | age |
+----+-----+
| 1 | 78 |
| 2 | 25 |
| 3 | 35 |
| 4 | 29 |
| 5 | 27 |
| 6 | 33 |
| 7 | 78 |
| 8 | 25 |
| 9 | 35 |
| 10 | 29 |
| 11 | 27 |
| 12 | 33 |
| 13 | 69 |
| 14 | 45 |
| 15 | 34 |
| 16 | 42 |
| 17 | 55 |
| 18 | 44 |
| 19 | 66 |
| 20 | 51 |
| 21 | 47 |
| 22 | 39 |
| 23 | 36 |
+----+-----+
23 rows in set (0.00 sec)
- 查询数据
select distinct age from emp;
+-----+
| age |
+-----+
| 78 |
| 25 |
| 35 |
| 29 |
| 27 |
| 33 |
| 69 |
| 45 |
| 34 |
| 42 |
| 55 |
| 44 |
| 66 |
| 51 |
| 47 |
| 39 |
| 36 |
+-----+
17 rows in set (0.00 sec)
【八】约束条件之order by(排序)
order by : 默认是升序
asc 默认可以省略不写 ---> 修改降序
desc : 降序
- 查询数据
select * from emp order by salary;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+23 rows in set (0.00 sec)
- 查询数据
select * from emp order by salary desc;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+23 rows in set (0.00 sec)
order by 后面可以跟多个参数
- 查询数据
select * from emp order by age desc,salary asc;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 13 | 娜娜 | female | 69 | 2010-03-07 | sale | NULL | 300.13 | 402 | 2 || 19 | 大古 | female | 66 | 2018-05-09 | operation | NULL | 630.33 | 403 | 3 || 17 | 华华 | female | 55 | 2018-03-19 | sale | NULL | 380.75 | 402 | 2 || 20 | 张三 | male | 51 | 2019-10-01 | operation | NULL | 410.25 | 403 | 3 || 21 | 李四 | male | 47 | 2020-05-12 | operation | NULL | 330.62 | 403 | 3 || 14 | 芳芳 | male | 45 | 2014-05-18 | sale | NULL | 400.45 | 402 | 2 || 18 | 田七 | male | 44 | 2023-08-08 | sale | NULL | 420.33 | 402 | 2 || 16 | 亚洲 | female | 42 | 2017-02-27 | sale | NULL | 320.99 | 402 | 2 || 22 | 王五 | female | 39 | 2021-02-03 | operation | NULL | 370.98 | 403 | 3 || 23 | 赵六 | female | 36 | 2022-07-24 | operation | NULL | 390.15 | 403 | 3 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 15 | 小明 | male | 34 | 2016-01-03 | sale | NULL | 350.80 | 402 | 2 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 12 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 11 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+23 rows in set (0.00 sec)
先按照age降序排
如果碰到 age 相同 ，再按照salary 升序排
【1】统计各部门年龄在 10 岁以上的员工的工资，并且保留平均薪资大于1000的部门，对平均工资进行排序
- 查询数据
select post,avg(salary) from emp
where age >10
group by post
having avg(salary) > 1000
order by avg(salary) desc
;
+-----------------------+--------------+
| post | avg(salary) |
+-----------------------+--------------+
| teacher | 13000.722000 |
| 陌夜痴梦久生情 | 4015.330000 |
+-----------------------+--------------+
2 rows in set (0.00 sec)
【九】约束条件之 limit(限制展示条数)
针对数据太多的情况，我们大都是做分页处理
limit x,y : 第一个参数是起始位置，第二个是条数
- 查询数据方式一：单数字限制
select * from emp limit 10;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 || 7 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 730.33 | 401 | 1 || 8 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 9 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 10 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+10 rows in set (0.00 sec)
- 查询数据：多限制
select * from emp limit 0,6;
+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+| 1 | dream | male | 78 | 2022-03-06 | 陌夜痴梦久生情 | NULL | 7300.33 | 401 | 1 || 2 | mengmeng | female | 25 | 2022-01-02 | teacher | NULL | 12000.50 | 401 | 1 || 3 | xiaomeng | male | 35 | 2019-06-07 | teacher | NULL | 15000.99 | 401 | 1 || 4 | xiaona | female | 29 | 2018-09-06 | teacher | NULL | 11000.80 | 401 | 1 || 5 | xiaoqi | female | 27 | 2022-08-06 | teacher | NULL | 13000.70 | 401 | 1 || 6 | suimeng | male | 33 | 2023-03-06 | teacher | NULL | 14000.62 | 401 | 1 |+----+----------+--------+-----+------------+-----------------------+--------------+----------+--------+-----------+6 rows in set (0.00 sec)
从 0 后面 取六条
第一个参数是起始位置，第二个是条数
【十】正则
语法
select * from emp where name regexp "^j.*(n|y)$";
以j 开头 以 n 结尾 ， 中间任意
以某个字母开头，某个字母结尾
【十一】多表查询
【一】准备数据
- 创建表
create table dep(
id int,
name varchar(20)
);
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20),
sex ENUM("male","female") NOT NULL DEFAULT "male",
age INT,
dep_id INT
);
- 插入数据
insert into dep values
("200","技术部"),
("201","人力资源"),
("202","销售部"),
("203","运营部"),
("204","售后部");
insert into emp(name,sex,age,dep_id) values
("dream","male",18,200),
("chimeng","female",18,201),
("menmgneg","male",38,202),
("hope","male",18,203),
("own","male",28,204),
("thdream","male",18,205);
- 查看表
desc dep;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.13 sec)
desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+-----------------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | YES | | NULL | |
| sex | enum('male','female') | NO | | male | |
| age | int(11) | YES | | NULL | |
| dep_id | int(11) | YES | | NULL | |
+--------+-----------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
- 查看数据
select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术部 |
| 201 | 人力资源 |
| 202 | 销售部 |
| 203 | 运营部 |
| 204 | 售后部 |
+------+--------------+
5 rows in set (0.00 sec)
select * from emp;
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | dream | male | 18 | 200 |
| 2 | chimeng | female | 18 | 201 |
| 3 | menmgneg | male | 38 | 202 |
| 4 | hope | male | 18 | 203 |
| 5 | own | male | 28 | 204 |
| 6 | thdream | male | 18 | 205 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)
【1】拼表
结果叫 笛卡尔积
- 查询数据
select * from dep,emp;
+------+--------------+----+----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+----------+--------+------+--------+
| 200 | 技术部 | 1 | dream | male | 18 | 200 |
| 201 | 人力资源 | 1 | dream | male | 18 | 200 |
| 202 | 销售部 | 1 | dream | male | 18 | 200 |
| 203 | 运营部 | 1 | dream | male | 18 | 200 |
| 204 | 售后部 | 1 | dream | male | 18 | 200 |
| 200 | 技术部 | 2 | chimeng | female | 18 | 201 |
| 201 | 人力资源 | 2 | chimeng | female | 18 | 201 |
| 202 | 销售部 | 2 | chimeng | female | 18 | 201 |
| 203 | 运营部 | 2 | chimeng | female | 18 | 201 |
| 204 | 售后部 | 2 | chimeng | female | 18 | 201 |
| 200 | 技术部 | 3 | menmgneg | male | 38 | 202 |
| 201 | 人力资源 | 3 | menmgneg | male | 38 | 202 |
| 202 | 销售部 | 3 | menmgneg | male | 38 | 202 |
| 203 | 运营部 | 3 | menmgneg | male | 38 | 202 |
| 204 | 售后部 | 3 | menmgneg | male | 38 | 202 |
| 200 | 技术部 | 4 | hope | male | 18 | 203 |
| 201 | 人力资源 | 4 | hope | male | 18 | 203 |
| 202 | 销售部 | 4 | hope | male | 18 | 203 |
| 203 | 运营部 | 4 | hope | male | 18 | 203 |
| 204 | 售后部 | 4 | hope | male | 18 | 203 |
| 200 | 技术部 | 5 | own | male | 28 | 204 |
| 201 | 人力资源 | 5 | own | male | 28 | 204 |
| 202 | 销售部 | 5 | own | male | 28 | 204 |
| 203 | 运营部 | 5 | own | male | 28 | 204 |
| 204 | 售后部 | 5 | own | male | 28 | 204 |
| 200 | 技术部 | 6 | thdream | male | 18 | 205 |
| 201 | 人力资源 | 6 | thdream | male | 18 | 205 |
| 202 | 销售部 | 6 | thdream | male | 18 | 205 |
| 203 | 运营部 | 6 | thdream | male | 18 | 205 |
| 204 | 售后部 | 6 | thdream | male | 18 | 205 |
+------+--------------+----+----------+--------+------+--------+
30 rows in set (0.00 sec)
【2】拼表升级
- 查询数据
select * from dep,emp where emp.dep_id = dep.id;
+------+--------------+----+----------+--------+------+--------+
| id | name | id | name | sex | age | dep_id |
+------+--------------+----+----------+--------+------+--------+
| 200 | 技术部 | 1 | dream | male | 18 | 200 |
| 201 | 人力资源 | 2 | chimeng | female | 18 | 201 |
| 202 | 销售部 | 3 | menmgneg | male | 38 | 202 |
| 203 | 运营部 | 4 | hope | male | 18 | 203 |
| 204 | 售后部 | 5 | own | male | 28 | 204 |
+------+--------------+----+----------+--------+------+--------+
5 rows in set (0.00 sec)
【3】拼表语法
（1）inner join(内连接)
只拼接两张表中共有的数据部分
- 查询数据
select * from emp inner join dep on emp.dep_id = dep.id;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
+----+----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
（2）left join(左连接)
左表所有的数据都展示出来，没有对应的项就用null表示
- 查询数据
select * from emp left join dep on emp.dep_id = dep.id;
+----+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+----+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
| 6 | thdream | male | 18 | 205 | NULL | NULL |
+----+----------+--------+------+--------+------+--------------+
6 rows in set (0.00 sec)
（3）right join(右连接)
右表所有的数据都展示出来，没有对应的项就用null表示
- 查询数据
select * from emp right join dep on emp.dep_id = dep.id;
+------+----------+--------+------+--------+------+--------------+
| id | name | sex | age | dep_id | id | name |
+------+----------+--------+------+--------+------+--------------+
| 1 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
+------+----------+--------+------+--------+------+--------------+
5 rows in set (0.00 sec)
（4）union(全连接)
左右两表的数据都展示出来
- 查询数据
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 | dream | male | 18 | 200 | 200 | 技术部 |
| 2 | chimeng | female | 18 | 201 | 201 | 人力资源 |
| 3 | menmgneg | male | 38 | 202 | 202 | 销售部 |
| 4 | hope | male | 18 | 203 | 203 | 运营部 |
| 5 | own | male | 28 | 204 | 204 | 售后部 |
| 6 | thdream | male | 18 | 205 | NULL | NULL |
+------+----------+--------+------+--------+------+--------------+
6 rows in set (0.02 sec)
【十二】子查询
子查询就相当于我们平时解决问题的思路
讲一个查询语句的结果当做另外一个查询语句的条件去用
【1】查询部门是技术或者人力资源的员工信息
先获取部门的ID号，再去员工表里面删选出对应的员工
- 查询语句
select dep_id from dep where name = "技术部" or name = "人力资源";
select name from emp where dep_id in (200,201);
- 完整语句
select * from emp where dep_id in (select dep_id from dep where name = "技术部" or name = "人力资源");
+----+----------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+----------+--------+------+--------+
| 1 | dream | male | 18 | 200 |
| 2 | chimeng | female | 18 | 201 |
| 3 | menmgneg | male | 38 | 202 |
| 4 | hope | male | 18 | 203 |
| 5 | own | male | 28 | 204 |
| 6 | thdream | male | 18 | 205 |
+----+----------+--------+------+--------+
6 rows in set (0.00 sec)