Day02-DDLDMLDQL(定义,操作,查询)(联合查询,子查询,字符集和校对集,MySQL5.7乱码问题)

文章目录

Day02-DDL&DML和DQL
- 学习目标
- 1. SQL语言的组成
- 2. DDL
- - 2.1 数据库结构
  - 2.2 表结构
  - 2.3 约束
  - - 2.3.1 主键约束(重要)
    - - (1)特点
      - (2) 添加主键
      - (3)删除主键(了解)
    - 2.3.2 自增约束
    - - (1)特点
      - (2) 添加自增约束
      - (3)删除自增约束(了解)
    - 2.3.3 非空约束
    - - (1)添加非空约束
      - (2) 删除非空约束
    - 2.3.4 唯一约束
    - - (1)特点
      - (2) 添加唯一约束
      - (3)删除唯一约束
    - 2.3.5 默认值约束
    - - (1) 添加默认值约束
      - (2) 删除默认值约束
    - 2.3.6 检查约束
    - 2.3.7 外键约束(了解)
    - - (1)特点
      - (2) 添加外键约束
      - (3)删除外键
  - 2.4 索引
- 3. DML语句
- 4.数据库的备份和导入
- - 4.1 备份数据库
  - 4.2 导入数据
- 5. 函数
- - 5.1 聚合函数
  - 5.2 单行函数
  - - 5.2.1 数学函数
    - 5.2.2 字符串函数
    - 5.2.3 日期时间函数
    - 5.2.4 加密函数
    - 5.2.5 系统信息函数
  - 5.3 窗口函数
- 6. 流程控制语句
- 7. DQL
- - 7.1 基本查询语句
  - - DUAL表
    - distinct: 去重
  - 7.2 查询子语句
  - - from子句
    - on子句
    - where子句
    - group by子句
    - having 子句
    - order by: 按字段排序
    - limit: 限制取出数量
- 8. 联合查询
- - 8.1 使用union联合查询
  - 8.2 使用from联合查询
  - 8.3 使用join...on语句
  - - 8.3.1 内连接
    - 8.3.2 左外连接
    - 8.3.3 右外连接
    - 8.3.4 全连接
- 9. 子查询
- - 9.1 select型
  - 9.2 from型
  - 9.3 where/having型
  - 9.4 exists型
- 附录:
- - 1. 字符集和校对集
  - - 1.1 字符集
    - 1.2 校对集
  - 2 MySQL5.7乱码问题
  - - 解决方法一:
    - 解决方法二:
    - 解决方法三:

Day02-DDL&DML和DQL

学习目标

能够说出SQL语言的组成部分
能够使用DDL语句查看数据库和表结构
能够使用DDL语句建库建表删库删表
能够看懂修改表结构的SQL语句
能够说出字符集和校对集的作用
能够说出中文乱码的原因以及解决方案
能够说出约束和索引的概念
能够使用SQL语句添加常见的约束
能够使用mysql内置函数进行查询
能够使用mysql流程控制语句进行查询
能够使用DQL语句进行查询
能够使用DQL语句实现多表联合查询
能够使用DQL语句实现子查询

1. SQL语言的组成

SQL语言主要由以下六部分组成。

DDL:
- 数据定义语言 Data Definition Language
- 包括动词CREATE和DROP。在数据库中创建新表或删除表（CREAT TABLE 或 DROP TABLE）；为表加入索引等。
DML：
- 数据操作语言Data Manipulation Language
- 其语句包括动词INSERT，UPDATE和DELETE，它们分别用于添加，修改和删除表中的行，也称为动作查询语言。
DQL:
- 数据查询语言Data Query Language
- 也被称为“数据检索语i句”，用以从表中获得数据，确定数据怎样在应用程序给出。
- 保留字SELECT是DQL（也是所有SQL）用得最多的动词，其他DQL常用的保留字有WHERE，ORDER BY，GROUP BY和HAVING
TPL：
- 事务处理语言Transaction Processing Language
- 它的语句能确保被DML语句影响的表的所有行及时得以更新。
- TPL语句包括BEGIN TRANSACTION，COMMIT和ROLLBACK。
DCL：
- 数据控制语言Data Control Language
- 它的语句通过GRANT或REVOKE获得许可，确定单个用户和用户组对数据库对象的访问。某些RDBMS可用GRANT或REVOKE控制对表单个列的访问。
CCL：
- 指针控制语言 Cursor Control Language
- 像DECLARE CURSOR，FETCH INTO和UPDATE WHERE CURRENT用于对一个或多个表单独行的操作

2. DDL

在操作数据之前，必须要先创建数据库，在数据库里添加表格，并指定表格里的字段，然后才能在表格里对数据进行增删改查操作。

数据库表结构操作通常包含一下内容：

创建数据库
查看和修改数据库的字符集和校对集
创建表格，指定字段
设置字段约束
修改表格的字段，包括新增删除以及修改已有字段的属性

2.1 数据库结构

查看所有的数据库
```
show databases;
/*输出
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |

*/
```
MySQL服务区在搭建完成以后，默认就会有information_schema，mysql,performance_schema和sys四个数据库。这四个数据库用来存储MysQL里的基本信息，一般情况下请勿手动修改。
创建数据库并指定编码方式
```
create database demo charset=utf8;
/*输出
Query OK, 1 row affected (0.00 sec)
*/
```
创建一个数据库，并指定编码方式为 utf8

使用指定的数据库

use demo;
/* 输出
Database changed
*/

查看数据库创建语句

show create database demo;
/*输出
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| demo     | CREATE DATABASE `demo` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
*/

删除指定的数据库

drop database demo;
/* 输出
Query OK, 0 rows affected (0.00 sec)
*/

2.2 表结构

显示当前数据库下所有的表格。

show tables ;  #前提是前面有use 数据库名;的语句
show tables from 数据库名;

创建表格。

create table demo.student(
	id int primary key auto_increment,
	name varchar(32)
)charset=utf8;

查看表结构。

desc student;
/* 输出
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)
*/

查看建表语句。

show create table student;
/*输出
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                  |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
*/

新增字段(了解)。

alter table [数据库名.]表名称 add [column] 字段名 数据类型;
alter table [数据库名.]表名称 add [column] 字段名 数据类型 first;
alter table [数据库名.]表名称 add [column] 字段名 数据类型 after 另一个字段;

alter table student add address varchar(128);
alter table student add height double after name;

删除字段(了解)。

alter table [数据库名.]表名称 drop [column] 字段名;

alter table student drop height;

修改字段属性(了解)。

alter table [数据库名.]表名称 modify [column] 字段名 新数据类型;

alter table student modify address varchar(256);

修改字段名。

alter table [数据库名.]表名称 change[column] 旧字段名 新字段名 新数据类型;

alter table student change address city varchar(16);

修改字段的位置。

alter table [数据库名.]表名称 modify [column]字段名 数据类型 first;
alter table [数据库名.]表名称 modify [column]字段名 数据类型 after 另一个字段;
alter table student modify name varchar(32) after city;

修改表名称

alter table 旧表名 rename 新表名;
rename table 旧表名 to 新表名;
alter table student rename students;

2.3 约束

约束是用来对数据业务规则和数据完整性进行实施、维护。约束的作用范围仅限在当前数据库，约束可以被当做数据库对象来处理，它们具有名称和关联模式，是逻辑约束，不会因为设置约束而额外占用空间。

数据完整性（Data Integrity）是指数据的精确性（Accuracy）和可靠性（Reliability）。它是应防止数据库中存在不符合语义规定的数据和防止因错误信息的输入输出造成无效操作或错误信息而提出的。

数据的完整性要从以下四个方面考虑：

实体完整性（Entity Integrity）：例如，同一个表中，不能存在两条完全相同无法区分的记录
域完整性（Domain Integrity）：例如：年龄范围0-120，性别范围“男/女”
引用完整性（Referential Integrity）：例如：员工所在部门，在部门表中要能找到这个部门
用户自定义完整性（User-defined Integrity）：例如：用户名唯一、密码不能为空等，本部门经理的工资不得高于本部门职工的平均工资的5倍。

查看一个表里的约束:

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';
#information_schema数据库名（系统库）
#table_constraints表名称（专门存储各个表的约束）
#WHERE条件
#table_name = '表名称'条件是指定表名称
show create table [tablenmame];  -- 查看建表语句也能显示约束

2.3.1 主键约束(重要)

主键分为单列主键和复合主键：

在这里插入图片描述

(1)特点

唯一并且非空，即如果给一个字段设置了主键约束，就不用再设置唯一和非空约束了。
一个表最多只能有一个主键约束
创建主键会自动创建对应的索引，同样删除主键对应的索引也会删除。

(2) 添加主键

方式一：创建表格的时候，就指定好主键约束。

create table [数据名.]表名(
	字段名1 数据类型  primary key ,
	....
);
或
create table [数据名.]表名(
	字段名1 数据类型,
	....,
    primary key(字段名1)
);
或
create table [数据名.]表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表)#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
);

方式二：创建表格以后，再给字段增加主键约束。(了解)
```
alter table 表名称 add primary key (主键字段列表);
```

(3)删除主键(了解)

alter table 表名称 drop primary key;

2.3.2 自增约束

自增约束auto_increment，用来设置让一个字段的值自动生成。

(1)特点

一个表最多只能有一个自增长列，通常我们会将主键设置为自增。
自增长列必须是键列（主键列，唯一键列，外键列），并且要求非空。
自增列必须是整数类型
InnoDB表的自动增长列可以手动插入，但是插入的值如果是空或者0，则实际插入的将是自动增长后的值。

(2) 添加自增约束

方式一：创建表格时设置自增

create table [数据名.]表名(
	字段名1 数据类型  primary key auto_increment,
	....
);

方式二：给已有表格追加自增约束(了解)

alter table [数据名.]表名 modify 自增字段名 数据类型 auto_increment;

(3)删除自增约束(了解)

alter table [数据库名.]表名 modify 自增字段名 数据类型;

2.3.3 非空约束

NOT NULL 非空约束，规定某个字段不能为空

(1)添加非空约束

方式一：创建表格时指定非空约束

create table [数据名.]表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 [unique key] [not null],
	....,
    foreign key (从表字段) references 主表名(主表字段) [on update 外键约束等级][on delete 外键约束等级]
    #外键只能在所有字段列表后面单独指定
);

create table [数据名.]表名(
    字段名1 数据类型 [not null],
    字段名2 数据类型 [not null],
	....,
    primary key(复合主键字段列表),#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) [on update 外键约束等级][on delete 外键约束等级]
    #外键只能在所有字段列表后面单独指定
);

方式二：给已有的表格追加非空约束。

ALTER TABLE 表名称 MODIFY 字段名 数据类型 NOT NULL [default 默认值];
#如果该字段原来设置了默认值约束，要跟着一起再写一遍，否则默认值约束会丢失

(2) 删除非空约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 [default 默认值];
#如果该字段原来设置了默认值约束，要跟着一起再写一遍，否则默认值约束会丢失

2.3.4 唯一约束

被唯一约束的字段，它里面填入的值不允许重复。

(1)特点

唯一键约束列允许为null。
同一个表可以有多个唯一约束。
唯一约束可以是某一个列的值唯一，也可以多个列组合值的唯一。
MySQL会给唯一约束的列上默认创建一个唯一索引。
删除唯一键只能通过删除对应索引的方式删除，删除时需要指定唯一键索引名。

(2) 添加唯一约束

方式一：建表时指定约束

create table [数据名.]表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 unique key,
	....
);

create table [数据名.]表名(
    字段名1 数据类型  primary key ,
    字段名2 数据类型,
    字段名3 数据类型,
	....,
    unique key(复合唯一字段列表)#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
);

方式二：创建表格以后追加约束。(了解)

alter table 表名称 add [constraint 约束名] unique [key] (字段名列表);
#如果没有指定约束名，(字段名列表)中只有一个字段的，默认是该字段名
# 如果是多个字段的默认是字段名列表的第1个字段名

(3)删除唯一约束

ALTER TABLE 表名称 DROP INDEX 唯一性约束名;
#注意：如果忘记名称，可以通过“show index from 表名称;”查看

2.3.5 默认值约束

默认值约束用来在创建表格时，给字段指定默认值。

(1) 添加默认值约束

方式一：创建表格时设置默认值。

create table [数据名.]表名(
	字段名2 数据类型 [default 默认值],
);

create table [数据名.]表名(
    字段名1 数据类型 [not null] [default 默认值],
);

方式二：给已有的表格设置默认值(了解)。

ALTER TABLE 表名称 MODIFY 字段名 数据类型  [default 默认值];
#如果该字段原来设置了非空约束，要跟着一起再写一遍，否则非空约束会丢失

(2) 删除默认值约束

ALTER TABLE 表名称 MODIFY 字段名 数据类型 [NOT NULL];
#如果该字段原来设置了非空约束，要跟着一起再写一遍，否则非空约束会丢失

2.3.6 检查约束

在MySQL8.0之前，就算给表定义了检查约束，但是设置以后都没有效果。8.0.16之后的版本，CREATE TABLE语句既支持给单个字段定义列级CHECK约束的语法，还支持定义表级CHECK约束的语法。

create table student(sid int primary key auto_increment,name varchar(32),score int check(score<=100 and score>=0) enforced);

enforced可以省略，表示强制要求遵守检查约束。也可以设置为not enforced，此时不遵守检查约束的数据也可以写入。

2.3.7 外键约束(了解)

外键约束是保证一个或两个表之间的参照完整性,外键是构建于一个表的两个字段或是两个表的两个字段之间的参照关系。
在创建外键约束时，如果不给外键约束名称，默认名不是列名，而是自动产生一个外键名（例如 student_ibfk_1;），也可以指定外键约束名。
当创建外键约束时，系统默认会在所在的列上建立对应的普通索引。但是索引名是列名，不是外键的约束名。
删除外键时，关于外键列上的普通索引需要单独删除。

(1)特点

在从表上建立外键，而且主表要先存在。
一个表可以建立多个外键约束
从表的外键列，在主表中引用的只能是键列（主键，唯一键，外键），推荐引用主表的主键。
从表的外键列与主表被参照的列名字可以不相同，但是数据类型必须一样
约束关系：约束是针对双方的
- 添加了外键约束后，主表的修改和删除数据受约束
- 添加了外键约束后，从表的添加和修改数据受约束
- 在从表上建立外键，要求主表必须存在
- 删除主表时，要求从表从表先删除，或将从表中外键引用该主表的关系先删除
在开发中，要慎用外键约束，外键在保证数据完整性的同时，会极大的影响数据的性能。
约束的五个等级
- Cascade方式：在父表上update/delete记录时，同步update/delete掉子表的匹配记录
- Set null方式：在父表上update/delete记录时，将子表上匹配记录的列设为null，但是要注意子表的外键列不能为not null
- No action方式：如果子表中有匹配的记录,则不允许对父表对应候选键进行update/delete操作
- Restrict方式：同no action, 都是立即检查外键约束
- Set default方式（在可视化工具SQLyog中可能显示空白）：父表有变更时,子表将外键列设置成一个默认的值，但Innodb不能识别。
如果没有指定等级，就相当于Restrict方式。

(2) 添加外键约束

方式一：创建表格时指定外键约束。

create table [数据名.]从表名(
	字段名1 数据类型  primary key ,
	字段名2 数据类型 [unique key],
	....,
    [constraint 外键约束名] foreign key (从表字段) references 主表名(主表字段) [on update 外键约束等级][on delete 外键约束等级[
    #外键只能在所有字段列表后面单独指定
    #如果要自己命名外键约束名，建议 主表名_从表名_关联字段名_fk
);

create table [数据名.]表名(
    字段名1 数据类型,
    字段名2 数据类型,
	....,
    primary key(复合主键字段列表),#如果是复合主键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加primary key
    unique key(复合唯一字段列表),#如果是复合唯一键，那么就需要在所有字段列表后面使用这种形式指定，不能在字段后面直接加unique key
    foreign key (从表字段) references 主表名(主表字段) [on update 外键约束等级][on delete 外键约束等级[
    #外键只能在所有字段列表后面单独指定
);

方式二：给已有表格新增外键。

alter table 从表名称 add [constraint 外键约束名] foreign key (从表字段名) references 主表名(主表被参照字段名) [on update xx][on delete xx];

(3)删除外键

#查看约束名 
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

ALTER TABLE 表名称 DROP FOREIGN KEY 外键约束名;

#删除外键约束不会删除对应的索引，如果需要删除索引，需要用ALTER TABLE 表名称 DROP INDEX 索引名;
 show index from 表名称; #查看索引名

2.4 索引

在MySQL中键约束会自动创建索引，提高查询效率。索引的详细讲解在高级部分。

MySQL高级会给大家讲解索引、存储引擎等，因为高级要给大家分析SQL性能。而基础阶段先不管效率，只要能查出来就行。

约束和索引不同：

约束是一个逻辑概念，它不会单独占用物理空间，

索引是一个物理概念，它是会占用物理空间。

例如：字典

字典里面有要求，不能有重复的字（字一样，读音也一样），这是约束。

字典里面有“目录”，它可以快速的查找某个字，目录需要占用单独的页。

3. DML语句

添加数据

insert into [数据库名.]表名称 values(值列表);
#要求值列表的顺序、个数、类型，要与表格中的字段的顺序、个数、类型一一匹配

insert into [数据库名.]表名称(部分字段列表) values(值列表);
#要求列表的顺序、个数、类型，要与前面的(部分字段列表)的顺序、个数、类型一一匹配

insert into [数据库名.]表名称 values(值列表1),(值列表2)...;

insert into [数据库名.]表名称(部分字段列表) values(值列表1),(值列表2)...;

注意：如果一个字段有自增约束，在添加数据时，不要再手动的插入数值，可以使用0或者null来代替。

复制一张表格。

create table 新表名 like 源表名;
insert into 新表名 select * from 源表名;

修改数据

update [数据库名.]表名称 set 字段名1 = 值1,字段名2 = 值2 。。。 [where 条件];

如果没有加where条件，表示修改所有行，这个字段的值

删除数据
```
delete from [数据库名.]表名称 [where 条件];
```
如果没有where条件，表示删除整张表的数据;
```
truncate [数据库名.]表名称;
```
truncate 也可以删除整张表的数据，它的效率更高，但是它不能回滚

用delete删除整张表和用truncate删除整张表的数据的区别？

（1）truncate速度快

（2）truncate无法回滚

truncate因为底层是把表drop掉，然后新建了一张空表，而delete底层实现是一行一行的删数据。

4.数据库的备份和导入

使用mysqldump.exe程序可以将数据库的数据备份到一个文件；使用mysql.exe或者source命令可以将文件里的数据导入到数据库。

4.1 备份数据库

使用mysqldump指令可以将一个数据库里的数据备份到文件中。

mysqldump -uroot -p 数据库名 > 文件名
mysqldump -uroot -p demo > demo.sql -- 将demo数据里的数据导入到 demo.sql文件中

注意: mysqldump 命令要在windows环境下执行，而不是在mysql客户端执行!

4.2 导入数据

方式一:使用mysql命令

mysql命令不仅可以打开一个客户端连接，还可以将文件中的SQL语句写入到数据库服务器中。

mysql -uroot -p 数据库名 < 文件名
mysql -uroot -p demo < demo.sql -- 将demo.sql文件中的数据导入到 demo数据库中

方式二: 在mysql客户端中执行source命令

source 文件路径
source  C:\Users\chris\Desktop\demo.sql

5. 函数

函数：代表一个独立的可复用的功能。

和Java中的方法有所不同，不同点在于：MySQL中的函数必须有返回值，参数可以有可以没有。

MySQL中函数分为：

（1）系统预定义函数：MySQL数据库管理软件给我提供好的函数，直接用就可以，任何数据库都可以用公共的函数。

分组函数：或者又称为聚合函数，多行函数，表示会对表中的多行记录一起做一个“运算”，得到一个结果。
- 求平均值的avg，求最大值的max，求最小值的min，求总和sum，求个数的count等
单行函数：表示会对表中的每一行记录分别计算，有n行得到还是n行结果
- 数学函数、字符串函数、日期时间函数、条件判断函数、窗口函数等

（2）用户自定义函数：由开发人员自己定义的，通过CREATE FUNCTION语句定义，是属于某个数据库的对象。

5.1 聚合函数

聚合函数也被称为分组函数，它的作用是将多个数据进行计算，得到一个结果。例如，将多个数字相加，求多个数字里的最大（最小）值或平均值，将多个字符串拼接成为一个字符串等。group by 语句通常都需要配合聚合函数来使用。

Name	Description
AVG()	返回参数的平均值
BIT_AND()	按位返回AND
BIT_OR()	按位返回OR
BIT_XOR()	按位返回异或
COUNT()	返回返回的行数
COUNT(DISTINCT)	返回许多不同值的计数
GROUP_CONCAT()	返回连接的字符串
JSON_ARRAYAGG()	将结果集作为单个JSON数组返回
JSON_OBJECTAGG()	将结果集作为单个JSON对象返回
MAX()	返回最大值
MIN()	返回最小值
STD()	返回样本的标准差
STDDEV()	返回样本的标准差
STDDEV_POP()	返回样本的标准差
STDDEV_SAMP()	返回样本标准差
SUM()	归还总和
VAR_POP()	返回样本的标准差异
VAR_SAMP()	返回样本方差
VARIANCE()	返回样本的标准差异

#演示分组函数，聚合函数，多行函数
#统计t_employee表的员工的数量
SELECT COUNT(*) FROM t_employee;
SELECT COUNT(1) FROM t_employee;
SELECT COUNT(eid) FROM t_employee;
SELECT COUNT(commission_pct) FROM t_employee;

/*
count(*)或count(常量值)：都是统计实际的行数。
count(字段/表达式)：只统计“字段/表达式”部分非NULL值的行数。
*/

#找出t_employee表中最高的薪资值
SELECT MAX(salary) FROM t_employee;

#找出t_employee表中最低的薪资值
SELECT MIN(salary) FROM t_employee;

#统计t_employee表中平均薪资值
SELECT AVG(salary) FROM t_employee;

#统计所有人的薪资总和，财务想看一下，一个月要准备多少钱发工资
SELECT SUM(salary) FROM t_employee; #没有考虑奖金
SELECT SUM(salary+salary*IFNULL(commission_pct,0)) FROM t_employee; 

#找出年龄最小、最大的员工的出生日期
SELECT MAX(birthday),MIN(birthday) FROM t_employee;

#查询最新入职的员工的入职日期
SELECT MAX(hiredate) FROM t_employee;

5.2 单行函数

调用完函数后，记录数不变，一行计算完之后还是一行。mysql里提供的单行函数非常的多，这里我们只列举除部分函数。

5.2.1 数学函数

在这里插入图片描述

#在“t_employee”表中查询员工无故旷工一天扣多少钱，分别用CEIL、FLOOR、ROUND、TRUNCATE函数。
#假设本月工作日总天数是22天，旷工一天扣的钱=salary/22。
SELECT ename,salary/22,CEIL(salary/22),
FLOOR(salary/22),ROUND(salary/22,2),
TRUNCATE(salary/22,2) FROM t_employee; 


#查询公司平均薪资，并对平均薪资分别
#使用CEIL、FLOOR、ROUND、TRUNCATE函数
SELECT AVG(salary),CEIL(AVG(salary)),
FLOOR(AVG(salary)),ROUND(AVG(salary)),
TRUNCATE(AVG(salary),2) FROM t_employee;

5.2.2 字符串函数

在这里插入图片描述

#mysql中不支持 + 拼接字符串，需要调用函数来拼接
#（1）在“t_employee”表中查询员工姓名ename和电话tel，
#并使用CONCAT函数，CONCAT_WS函数。
SELECT CONCAT(ename,tel),CONCAT_WS('-',ename,tel) FROM t_employee;


#（2）在“t_employee”表中查询薪资高于15000的男员工姓名，
#并把姓名处理成“张xx”的样式。
#LEFT（s，n）函数表示取字符串s最左边的n个字符，
#而RPAD（s，len，p）函数表示在字符串s的右边填充p使得字符串长度达到len。
SELECT  RPAD(LEFT(ename,1),3,'x'),salary
FROM t_employee
WHERE salary>15000 AND gender ='男';

#（3）在“t_employee”表中查询薪资高于10000的男员工姓名、
#姓名包含的字符数和占用的字节数。
SELECT ename,CHAR_LENGTH(ename) AS 占用字符数,LENGTH(ename) AS 占用字节数量
FROM t_employee
WHERE salary>10000 AND gender ='男';


#（4）在“t_employee”表中查询薪资高于10000的男员工姓名和邮箱email，
#并把邮箱名“@”字符之前的字符串截取出来。
SELECT ename,email,
SUBSTRING(email,1, POSITION('@' IN email)-1)
FROM t_employee
WHERE salary > 10000 AND gender ='男';

#mysql中 SUBSTRING截取字符串位置，下标从1开始，不是和Java一样从0开始。
#mysql中 position等指定字符串中某个字符，子串的位置也不是从0开始，都是从1开始。

SELECT TRIM('    hello   world   '); #默认是去掉前后空白符
SELECT CONCAT('[',TRIM('    hello   world   '),']'); #默认是去掉前后空白符
SELECT TRIM(BOTH '&' FROM '&&&&hello   world&&&&'); #去掉前后的&符号
SELECT TRIM(LEADING '&' FROM '&&&&hello   world&&&&'); #去掉开头的&符号
SELECT TRIM(TRAILING '&' FROM '&&&&hello   world&&&&'); #去掉结尾的&符号

5.2.3 日期时间函数

函数	功能描述
CURDATE()或CURRENT_DATE()	返回当前系统日期
CURTIME()或CURRENT_TIME()	返回当前系统时间
NOW()/SYSDATE()/CURRENT_TIMESTAMP()/ LOCALTIME()/LOCALTIMESTAMP()	返回当前系统日期时间
UTC_DATE()/UTC_TIME()	返回当前UTC日期值/时间值
UNIX_TIMESTAMP(date)	返回一个UNIX时间戳
YEAR(date)/MONTH(date)/DAY(date)/ HOUR(time)/MINUTE(time)/SECOND(time)	返回具体的时间值
EXTRACT(type FROM date)	从日期中提取一部分值
DAYOFMONTH(date)/DAYOFYEAR(date)	返回一月/年中第几天
WEEK(date)/WEEKOFYEAR(date)	返回一年中的第几周
DAYOFWEEK()	返回周几，注意，周日是1，周一是2，…周六是7
WEEKDAY(date)	返回周几，注意，周一是0，周二是1，…周日是6
DAYNAME(date)	返回星期，MONDAY,TUESDAY,…SUNDAY
MONTHNAME(date)	返回月份，January,…
DATEDIFF(date1,date2)/TIMEDIFF(time1,time2)	返回date1-date2的日期间隔/返回time1-time2的时间间隔
DATE_ADD(date,INTERVAL expr type)或ADDDATE/DATE_SUB/SUBDATE	返回与给定日期相差INTERVAL时间段的日期
ADDTIME(time,expr)/SUBTIME(time,expr)	返回给定时间加上/减去expr的时间值
DATE_FORMAT(datetime,fmt)/ TIME_FORMAT(time,fmt)	按照字符串fmt格式化日期datetime值/时间time值
STR_TO_DATE(str,fmt)	按照字符串fmt对str进行解析，解析为一个日期
GET_FORMAT(val_type,format_type)	返回日期时间字符串的显示格式

函数中日期时间类型说明

参数类型	描述	参数类型	描述
YEAR	年	YEAR_MONTH	年月
MONTH	月	DAY_HOUR	日时
DAY	日	DAY_MINUTE	日时分
HOUR	时	DAY_SECOND	日时分秒
MINUTE	分	HOUR_MINUTE	时分
SECOND	秒	HOUR_SECOND	时分秒
WEEK	星期	MINUTE_SECOND	分秒
QUARTER	一刻

函数中format参数说明

格式符	说明	格式符	说明
%Y	4位数字表示年份	%y	两位数字表示年份
%M	月名表示月份（January,…）	%m	两位数字表示月份（01,02,03，…）
%b	缩写的月名（Jan.,Feb.,…）	%c	数字表示月份（1,2,3…）
%D	英文后缀表示月中的天数（1st,2nd,3rd,…）	%d	两位数字表示表示月中的天数（01,02,…）
%e	数字形式表示月中的天数（1,2,3,…）	%p	AM或PM
%H	两位数字表示小数，24小时制（01,02,03,…）	%h和%I	两位数字表示小时，12小时制（01,02,03,…）
%k	数字形式的小时，24小时制（1,2,3,…）	%l	数字表示小时，12小时制（1,2,3,…）
%i	两位数字表示分钟（00,01,02,…）	%S和%s	两位数字表示秒（00,01,02,…）
%T	时间，24小时制（hh:mm:ss）	%r	时间，12小时制（hh:mm:ss）后加AM或PM
%W	一周中的星期名称（Sunday,…）	%a	一周中的星期缩写（Sun.,Mon.,Tues.,…）
%w	以数字表示周中的天数（0=Sunday,1=Monday,…）	%j	以3位数字表示年中的天数（001,002,…）
%U	以数字表示的的第几周（1,2,3,…）其中Sunday为周中的第一天	%u	以数字表示年中的年份（1,2,3,…）其中Monday为周中第一天
%V	一年中第几周（01~53），周日为每周的第一天，和%X同时使用	%X	4位数形式表示该周的年份，周日为每周第一天，和%V同时使用
%v	一年中第几周（01~53），周一为每周的第一天，和%x同时使用	%x	4位数形式表示该周的年份，周一为每周第一天，和%v同时使用
%%	表示%

GET_FORMAT函数中val_type 和format_type参数说明

值类型	格式化类型	显示格式字符串
DATE	EUR	%d.%m.%Y
DATE	INTERVAL	%Y%m%d
DATE	ISO	%Y-%m-%d
DATE	JIS	%Y-%m-%d
DATE	USA	%m.%d.%Y
TIME	EUR	%H.%i.%s
TIME	INTERVAL	%H%i%s
TIME	ISO	%H:%i:%s
TIME	JIS	%H:%i:%s
TIME	USA	%h:%i:%s %p
DATETIME	EUR	%Y-%m-%d %H.%i.%s
DATETIME	INTERVAL	%Y%m%d %H%i%s
DATETIME	ISO	%Y-%m-%d %H:%i:%s
DATETIME	JIS	%Y-%m-%d %H:%i:%s
DATETIME	USA	%Y-%m-%d %H.%i.%s

/*
获取系统日期时间值
获取某个日期或时间中的具体的年、月等值
获取星期、月份值，可以是当天的星期、当月的月份
获取一年中的第几个星期，一年的第几天
计算两个日期时间的间隔
获取一个日期或时间间隔一定时间后的另个日期或时间
和字符串之间的转换
*/
#（1）获取系统日期。CURDATE（）和CURRENT_DATE（）函数都可以获取当前系统日期。将日期值“+0”会怎么样？
SELECT CURDATE(),CURRENT_DATE();

#（2）获取系统时间。CURTIME（）和CURRENT_TIME（）函数都可以获取当前系统时间。将时间值“+0”会怎么样？
SELECT CURTIME(),CURRENT_TIME();

#（3）获取系统日期时间值。CURRENT_TIMESTAMP（）、LOCALTIME（）、SYSDATE（）和NOW（）
SELECT CURRENT_TIMESTAMP(),LOCALTIME(),SYSDATE(),NOW();

#（4）获取当前UTC（世界标准时间）日期或时间值。
#本地时间是根据地球上不同时区所处的位置调整 UTC 得来的，
#例如，北京时间比UTC时间晚8个小时。
#UTC_DATE(),CURDATE(),UTC_TIME(), CURTIME()
SELECT UTC_DATE(),CURDATE(),UTC_TIME(), CURTIME();


#（5）获取UNIX时间戳。
SELECT UNIX_TIMESTAMP(),UNIX_TIMESTAMP('2022-1-1');

#（6）获取具体的时间值，比如年、月、日、时、分、秒。
#分别是YEAR（date）、MONTH（date）、DAY（date）、HOUR（time）、MINUTE（time）、SECOND（time）。
SELECT YEAR(CURDATE()),MONTH(CURDATE()),DAY(CURDATE());
SELECT HOUR(CURTIME()),MINUTE(CURTIME()),SECOND(CURTIME());


#（7）获取日期时间的指定值。EXTRACT（type FROM date/time）函数
SELECT EXTRACT(YEAR_MONTH FROM CURDATE());

#（8）获取两个日期或时间之间的间隔。
#DATEDIFF（date1，date2）函数表示返回两个日期之间间隔的天数。
#TIMEDIFF（time1，time2）函数表示返回两个时间之间间隔的时分秒。

#查询今天距离员工入职的日期间隔天数
SELECT ename,DATEDIFF(CURDATE(),hiredate) FROM t_employee;

#查询现在距离中午放学还有多少时间
SELECT TIMEDIFF(CURTIME(),'12:0:0');

#（9）在“t_employee”表中查询本月生日的员工姓名、生日。
SELECT ename,birthday
FROM t_employee
WHERE MONTH(CURDATE()) = MONTH(birthday);


#(10)#查询入职时间超过5年的
SELECT ename,hiredate,DATEDIFF(CURDATE(),hiredate) 
FROM t_employee
WHERE DATEDIFF(CURDATE(),hiredate)  > 365*5;

5.2.4 加密函数

列出了部分的加密函数。

函数	用法
password(str)	返回字符串str的加密版本，41位长的字符串（mysql8不再支持）
md5(str)	返回字符串str的md5值，也是一种加密方式
SHA(str)	返回字符串str的sha算法加密字符串，40位十六进制值的密码字符串
SHA2(str,hash_length)	返回字符串str的sha算法加密字符串，密码字符串的长度是hash_length/4。hash_length可以是224、256、384、512、0，其中0等同于256。

#加密函数
/*
当用户需要对数据进行加密时，
比如做登录功能时，给用户的密码加密等。
*/
#password函数在mysql8已经移除了
SELECT PASSWORD('123456');

#使用md5加密
SELECT MD5('123456'),SHA('123456'),sha2('123456',0);

SELECT CHAR_LENGTH(MD5('123456')),SHA('123456'),sha2('123456',0);


CREATE TABLE t_user(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(20),
PASSWORD VARCHAR(100)
);

INSERT INTO t_user VALUES(NULL,'chai',MD5('123456'));

SELECT * FROM t_user 
WHERE username='chai' AND PASSWORD =MD5('123456');


SELECT * FROM t_user 
WHERE username='chai' AND PASSWORD ='123456';

5.2.5 系统信息函数

函数	用法
database()	返回当前数据库名
version()	返回当前数据库版本
user()	返回当前登录用户名

#其他函数
SELECT USER();
SELECT VERSION();
SELECT DATABASE();

5.3 窗口函数

窗口函数也叫OLAP函数（Online Anallytical Processing，联机分析处理），可以对数据进行实时分析处理。窗口函数是每条记录都会分析，有几条记录执行完还是几条，因此也属于单行函数。

函数分类	函数	功能描述
序号函数	ROW_NUMBER()	顺序排序，每行按照不同的分组逐行编号，例如：1,2,3,4
	RANK()	并列排序，每行按照不同的分组进行编号，同一个分组中排序字段值出现重复值时，并列排序并跳过重复序号，例如：1,1,3
	DENSE_RANK()	并列排序，每行按照不同的分组进行编号，同一个分组中排序字段值出现重复值时，并列排序不跳过重复序号，例如：1,1,2

窗口函数的语法格式如下

函数名([参数列表]) OVER ()
函数名([参数列表]) OVER (子句)

over关键字用来指定窗口函数的窗口范围。如果OVER后面是空（），则表示SELECT语句筛选的所有行是一个窗口。OVER后面的（）中支持以下4种语法来设置窗口范围。

WINDOW：给窗口指定一个别名；
PARTITION BY子句：一个窗口范围还可以分为多个区域。按照哪些字段进行分区/分组，窗口函数在不同的分组上分别处理分析；
ORDER BY子句：按照哪些字段进行排序，窗口函数将按照排序后结果进行分析处理；
FRAME子句：FRAME是当前分区的一个子集，FRAME子句用来定义子集的规则。

#（1）在“t_employee”表中查询薪资在[8000,10000]之间的员工姓名和薪资并给每一行记录编序号
SELECT ROW_NUMBER() OVER () AS "row_num",ename,salary
FROM t_employee WHERE salary BETWEEN 8000 AND 10000;

#（2）给每个部门的员工从1开始编号
select eid, did, row_number() over (partition by did) did_number,ename
from t_employee;


#（3）在“t_employee”表中查询女员工姓名，部门编号，薪资，查询结果按照部门编号分组后在按薪资升序排列，并分别使用ROW_NUMBER（）、RANK（）、DENSE_RANK（）三个序号函数给每一行记录编序号。
select ename, eid, salary, did,
       row_number() over w row_num,
       rank() over (w order by salary) rank_num,
       dense_rank() over (w order by salary) densen_num
from t_employee
where gender = '女' window w as(partition by did) order by did,eid;


#（4）在“t_employee”表中查询每个部门最低3个薪资值的女员工姓名，部门编号，薪资值。
SELECT ROW_NUMBER() OVER () AS "rn",temp.*
FROM(SELECT ename,did,salary,
ROW_NUMBER() OVER w AS "row_num",
RANK() OVER w AS "rank_num" ,
DENSE_RANK() OVER w AS "ds_rank_num" 
FROM t_employee WHERE gender='女'
WINDOW w AS (PARTITION BY did ORDER BY salary))temp 
WHERE temp.rank_num<=3;


#（5）在“t_employee”表中查询每个部门薪资排名前3的员工姓名，部门编号，薪资值。
SELECT temp.*
FROM(SELECT ename,did,salary,
DENSE_RANK() OVER w AS "ds_rank_num" 
FROM t_employee
WINDOW w AS (PARTITION BY did ORDER BY salary DESC))temp 
WHERE temp.ds_rank_num<=3;

#（6）在“t_employee”表中查询全公司薪资排名前3的员工姓名，部门编号，薪资值。
SELECT temp.*
FROM(SELECT ename,did,salary,
DENSE_RANK() OVER w AS "ds_rank_num" 
FROM t_employee
WINDOW w AS (ORDER BY salary DESC))temp 
WHERE temp.ds_rank_num<=3;

6. 流程控制语句

函数	功能
IF(value,t,f)	如果value是真，返回t,否则返回f
IFNULL(value1,value2)	如果value1不为空，返回value1,否则返回value2
CASE WHEN 条件1 THEN result1 WHEN 条件2 THEN result2 … ELSE resultn END	依次判断条件，哪个条件满足了，就返回对应的result,所有条件都不满足就返回ELSE的result。如果没有单独的ELSE子句，当所有WHEN后面的条件都不满足时则返回NULL值结果。等价于Java中if…else if…
CASE expr WHEN 常量值1 THEN 值1 WHEN 常量值2 THEN 值2 … ELSE 值n END	判断表达式expr与哪个常量值匹配，找到匹配的就返回对应值，都不匹配就返回ELSE的值。如果没有单独的ELSE子句，当所有WHEN后面的常量值都不匹配时则返回NULL值结果。等价于Java中switch…case

-- 查询 姓名，薪资，如果薪资大于2000，显示高薪，否则显示正常
SELECT ename,salary,IF(salary>20000,'高薪','正常') FROM t_employee;

-- 计算实发工资 = salary + salary * commission_pct
SELECT ename,salary,commission_pct, salary + salary * IFNULL(commission_pct,0) AS "实发工资" FROM t_employee;

/*
查询员工编号，姓名，薪资，等级，等级根据薪资判断，
如果薪资大于20000，显示“羡慕级别”，
如果薪资15000-20000，显示“努力级别”，
如果薪资10000-15000，显示“平均级别”
如果薪资10000以下，显示“保底级别”
mysql中没有if...else if语句，有case when语句，等价于if...elseif 
*/
SELECT eid,ename,salary,
CASE WHEN salary>20000 THEN '羡慕级别'
     WHEN salary>15000 THEN '努力级别'
     WHEN salary>10000 THEN '平均级别'
     ELSE '保底级别'
END AS "等级"
FROM t_employee; 

-- 相当于java里的switch...case语句
select ename, did,  -- 逗号不要忘记
       case did
           when 1 then '研发部'
           when 2 then '人事部'
           when 3 then '市场部'
           when 4 then '财务部'
           when 5 then '后勤部'
           when 6 then '测试部'
           else '其他部门'
           end as '部门'
from t_employee;

-- 分析员工的出差情况
SELECT ename,work_place,hiredate,
       CASE (CHAR_LENGTH(work_place) - CHAR_LENGTH(REPLACE(work_place, ',', '')) + 1)
           WHEN 1 THEN '只在一个地方工作'
           WHEN 2 THEN '在两个地方来回奔波'
           WHEN 3 THEN '在三个地方流动'
           ELSE '频繁出差'
           END AS "工作地点数量情况"
FROM t_employee
WHERE DATEDIFF(CURDATE(), hiredate) > 365 * 7;

7. DQL

数据库中用来实现查询的语句，我们称之为Data Query Language(简称DQL).在DQL语句中，最基本最常用的就是select语句。基本的select语句，配合七大查询字句，MysQL内置函数，以及多表查询等，组成了功能复杂强大的DQL.

7.1 基本查询语句

基本的查询使用select语句来完成，再配合from,as,where等关键字来丰富。

select 字段1 [as 别名1],字段2[ [as 别名2] ... from [数据库名.]表名称 [where 条件];
select * from [数据库名.]表名称 [where 条件];

DUAL表

select 除了配合from语句从一个表里查询数据以外，还可以直接单独使用，用来表示输出一段内容。

select 1+1;
/*
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
*/

这种只有select语句没有from语句的语法在MySQL里是支持的，但是在某些其他SQL类型数据库里却是不允许的。例如，Oracle数据库里就不能只使用select。为了保证语句的完整性，SQL提供了一个dual虚拟表，这个表是不存在的，它里面也没有任何的字段，存在的意义就是为了让那些不支持单独使用select的SQL语句不报错。

select 1+1 from dual;
/*
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
*/

distinct: 去重

select distinct did from t_employee;  -- 查看去重以后的部门id
select count(distinct did) from t_employee; -- 查看去重以后部门id的个数

7.2 查询子语句

在select语句后面，可以再加其他的语句，这些子语句并不是必须的，但是如果出现的话，一定要按照如下的顺序编写。

from: 从哪些表中筛选
on:多表查询。
where:从表中筛选条件。
group by:分组依据。
having:在统计结果中再次筛选。
order by: 排序。
limit: 分页。

from子句

from用来指定数据源，可以是一个数据源，也可以是多个数据源。通常情况下数据源是一个表名，但是也可以是一个查询结果。

select eid,ename,salary,gender from t_employee;  -- 读取t_employee表里的指定字段
select * from t_job;  -- 读取 t_job表里的所有字段

on子句

主要配合join语句来实现多表查询，在on语句中设定表之间的关联关系。

-- 联合查询 t_employee和t_job表，并找到 job_id和jid相等的数据
select * from t_employee join t_job on job_id=jid; 

-- 三个表联合查询，找到对应的字段
select eid,ename,jname,dname from t_employee join t_job join t_department on job_id = jid and t_employee.did = t_department.did;

where子句

where用来设置查询条件。

示例

select * from t_employee where gender='女';

空值判断: is null | is not null

select * from t_employee where gender='女' and job_id is null;

范围判断:

between ... and ...

select * from t_employee where gender='女' and birthday between '1990-01-01' and '2000-01-01';

group by子句

按照某一字段进行分组, 会把该字段中值相同的归为一组, 将查询的结果分类显示, 方便统计。
如果有 WHERE 要放在 WHERE 的后面
语法: select 字段 from 表名 group by 分组字段;

示例

-- 查询每个部门的平均工资
SELECT did,ROUND(AVG(salary),2 ) FROM t_employee GROUP BY did;

-- 查询每一个部门女性员工的平均薪资，显示部门编号，部门的名称
SELECT t_department.did,dname,ROUND(AVG(salary),2 )
FROM t_department LEFT JOIN t_employee
ON t_department.did = t_employee.did where gender='女'
GROUP BY t_department.did;

-- 查询每个部门的平均薪资，显示部门编号，部门名称，如果该部门没有员工，平均工资显示为0
SELECT t_department.did,dname,IFNULL(ROUND(AVG(salary),2),0)
FROM t_department LEFT JOIN t_employee ON t_department.did = t_employee.did
GROUP BY t_department.did;

问题1：合计，WITH ROLLUP，加在group by后面

#问题1：合计，WITH ROLLUP，加在group by后面
#按照部门统计人数
SELECT did, COUNT(*) FROM t_employee GROUP BY did;
#按照部门统计人数，并合计总数
SELECT did, COUNT(*) FROM t_employee GROUP BY did WITH ROLLUP;
SELECT IFNULL(did,'合计'), COUNT(*) FROM t_employee GROUP BY did WITH ROLLUP;
SELECT IFNULL(did,'合计') AS "部门编号" , COUNT(*)  AS "人数" FROM t_employee GROUP BY did WITH ROLLUP;

问题2：是否可以按照多个字段分组统计

#问题2：是否可以按照多个字段分组统计
#按照不同的部门，不同的职位，分别统计男和女的员工人数
SELECT did, job_id, gender, COUNT(*)
FROM t_employee 
GROUP BY did, job_id, gender;

问题4：分组统计时，select后面字段列表的问题

#问题4：分组统计时，select后面字段列表的问题
SELECT eid,ename, did, COUNT(*) FROM t_employee; 
#eid,ename, did此时和count(*)，不应该出现在select后面

SELECT eid,ename, did, COUNT(*) FROM t_employee GROUP BY did; 
#eid,ename此时和count(*)，不应该出现在select后面

SELECT  did, COUNT(*) FROM t_employee GROUP BY did;
#分组统计时，select后面只写和分组统计有关的字段，其他无关字段不要出现，否则会引起歧义

having 子句

HAVING 和 WHERE的使用功能一样，都是MySQL中的条件查询语句。
语法: SELECT 字段 FROM 表名 HAVING 条件;
区别在于：
- WHERE: 后面不能加上聚合函数，只能写在数据源的后面。
- HAVING: 条件字段必须要在结果集中出现, HAVING 可以写在 GROUP BY 的后面
- HAVING可以使用字段的别名，但是WHERE不能使用别名。

#查询每一个部门的女员工的平均薪资，显示部门编号，部门的名称，该部门的平均薪资
#要求，如果没有员工的部门，平均薪资不显示null，显示0
#最后只显示平均薪资高于12000的部门信息
SELECT t_department.did,dname,IFNULL(ROUND(AVG(salary),2),0)
FROM t_department LEFT JOIN t_employee
ON t_department.did = t_employee.did
WHERE gender = '女'
GROUP BY t_department.did
HAVING IFNULL(ROUND(AVG(salary),2),0) >12000;


#查询每一个部门的男和女员工的人数
SELECT did,gender,COUNT(*)
FROM t_employee
GROUP BY did,gender;

#查询每一个部门的男和女员工的人数，显示部门编号，部门的名称，性别，人数
SELECT t_department.did,dname,gender,COUNT(eid)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
GROUP BY t_department.did,gender;

#查询每一个部门薪资超过10000的男和女员工的人数，显示部门编号，部门的名称，性别，人数
#只显示人数低于3人
SELECT t_department.did,dname,gender,COUNT(eid)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE salary > 10000
GROUP BY t_department.did,gender
HAVING COUNT(eid) < 3;

order by: 按字段排序

ORDER BY 主要作用是排序
ORDER BY 写在 GROUPBY 后面 ,如果有 HAVING 也要写在 HAVING 的后面
语法: select 字段 from 表名 order by 排序字段 asc|desc;
分为升序 asc 降序 desc, 默认 asc (可以不写)

示例

#查询员工信息，按照薪资从高到低
SELECT * FROM t_employee ORDER BY salary DESC;
#查询每一个部门薪资超过10000的男和女员工的人数，显示部门编号，部门的名称，性别，人数
#只显示人数低于3人，按照人数升序排列
SELECT t_department.did,dname,gender,COUNT(eid)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE salary > 10000
GROUP BY t_department.did,gender
HAVING COUNT(eid) < 3
ORDER BY COUNT(eid);


#查询员工的薪资，按照薪资从低到高，薪资相同按照员工编号从高到低
SELECT *
FROM t_employee
ORDER BY salary ASC , eid DESC;

limit: 限制取出数量

语法

select 字段 from 表名 limit m;  -- 从第 1 行到第 m 行
select 字段 from 表名 limit m, n;  -- 从第 m 行开始，往下取 n 行
select 字段 from 表名 limit m offset n;  -- 跳过前 n 行, 取后面的 m 行

#查询所有的男员工信息，分页显示，每页显示3条，第2页
#limit m,n  n=3,page=2,m=(page-1)*n=3
SELECT *
FROM t_employee
WHERE gender ='男'
LIMIT 3,3

#查询每一个编号为偶数的部门，显示部门编号，名称，员工数量，
#只显示员工数量>=2的结果，按照员工数量升序排列，
#每页显示2条，显示第1页
SELECT t_department.did,dname,COUNT(eid)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
WHERE t_department.did%2=0
GROUP BY t_department.did
HAVING COUNT(eid)>=2
ORDER BY COUNT(eid)
LIMIT 0,2;

8. 联合查询

关联查询也被称为多表联合查询，指的是两个或更多个表一起查询。这些一起查询的表之间是有关系的（一对一、一对多），它们之间一定是有关联字段，这个关联字段可能建立了外键，也可能没有建立外键。

比如：员工表和部门表，这两个表依靠“部门编号”进行关联。

8.1 使用union联合查询

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

union要求:

两边 select 语句的字段数必须一样
两边可以具有不同数据类型的字段
字段名默认按照左边的表来设置
如果两个表里出现了相同的数据，union的结果会自动去重。

desc user1;
/*输出
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| username | varchar(32)  | YES  |     | NULL    |                |
| password | varchar(512) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
*/

desc user2;
/*输出
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| uid      | varchar(32)  | YES  |     | NULL    |       |
| uname    | varchar(32)  | YES  |     | NULL    |       |
| password | varchar(512) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
*/

select * from user1 union select * from user2;
/*输出
+------+----------+----------+
| id   | username | password |
+------+----------+----------+
| 1    | jack     | 12342355 |
| good | tony     | 324345re |
+------+----------+----------+
*/

8.2 使用from联合查询

在from后面添加多个数据源，也能实现基本的多表查询。这种多表查询的方式不常用，虽然语句简单，但是功能比较有限，能够完全被join语句替代。

-- from数据源指定两个表，然后再使用where语句进行条件过滤
select t_employee.*,t_job.jname,description from t_employee,t_job where job_id = jid;

8.3 使用join…on语句

在这里插入图片描述

8.3.1 内连接

在这里插入图片描述

inner join可以简写为join，inner可以省略不写，得到的结果取两个表的共有的部分。from后添加多个数据源并使用where过滤的形式可以完全被 inner join替换。

select t_employee.*,t_job.jname,description from t_employee join t_job on job_id = jid;

8.3.2 左外连接

在这里插入图片描述

left outer join可以简写为 left join，outer可以省略。得到的结果是左右表共有的以及左表特有的数据，如果右表中没有匹配，右表字段结果显示为 NULL

select t_employee.*,t_job.jname,description from t_employee left outer join t_job on job_id = jid;

8.3.3 右外连接

在这里插入图片描述

right outer join可以简写为 right join，outer可以省略。得到的结果是左右表共有的以及右表特有的数据，如果左表中没有匹配，左表字段结果显示为 NULL

select t_employee.*,t_job.jname,description from t_employee right outer join t_job on job_id = jid;

8.3.4 全连接

在这里插入图片描述

mysql数据库不支持full join，可以通过 union的形式来实现该功能。

select t_employee.*,t_job.jname,description from t_employee left outer join t_job on job_id = jid
union
select t_employee.*,t_job.jname,description from t_employee right outer join t_job on job_id = jid;

9. 子查询

嵌套在另一个查询中的查询，根据位置不同，分为：seelct型，where/having型，from型，exists型。注意：不管子查询在哪里，子查询必须使用()括起来。

9.1 select型

SELECT语句可以嵌套在另一个SELECT中，UPDATE，DELETE，INSERT，CREATE语句等。

#（1）在“t_employee”表中查询每个人薪资和公司平均薪资的差值，
#并显示员工薪资和公司平均薪资相差5000元以上的记录。
SELECT ename AS "姓名",
	salary AS "薪资",
 ROUND((SELECT AVG(salary) FROM t_employee),2) AS "全公司平均薪资",
 ROUND(salary-(SELECT AVG(salary) FROM t_employee),2) AS "差值"
FROM t_employee
WHERE ABS(ROUND(salary-(SELECT AVG(salary) FROM t_employee),2))>5000;

#（2）在“t_employee”表中查询每个部门平均薪资和公司平均薪资的差值。
SELECT did,AVG(salary),
AVG(salary)-(SELECT AVG(salary) FROM t_employee)
FROM t_employee
GROUP BY did;

9.2 from型

当子查询结果是多列的结果时，通常将子查询放到FROM后面，然后采用给子查询结果取别名的方式，把子查询结果当成一张“动态生成的临时表”使用。

当一个查询要基于另一个查询结果来筛选的时候，另一个查询还是多行多列的结果，那么就可以把这个查询结果当成一张临时表，放在from后面进行再次筛选。

#（1）在“t_employee”表中查询每个部门中薪资排名前2的员工姓名、部门编号和薪资。
SELECT * FROM (
SELECT ename,did,salary,
DENSE_RANK() over (PARTITION BY did ORDER BY salary DESC) AS paiming
FROM t_employee) temp
WHERE temp.paiming <=2;

#（1）在“t_employee”表中，查询每个部门的平均薪资，然后与“t_department”表联合查询
#所有部门的部门编号、部门名称、部门平均薪资。

SELECT did,AVG(salary) FROM t_employee GROUP BY did;

+------+-------------+
| did  | AVG(salary) |
+------+-------------+
|    1 |  11479.3125 |
|    2 |       13978 |
|    3 |    37858.25 |
|    4 |       12332 |
|    5 |       11725 |
+------+-------------+
5 ROWS IN SET (0.00 sec)

#用上面的查询结果，当成一张临时表，与t_department部门表做联合查询
#要给这样的子查询取别名的方式来当临时表用，不取别名是不可以的。
#而且此时的别名不能加""
#字段的别名可以加""，表的别名不能加""

SELECT t_department.did ,dname,AVG(salary)
FROM t_department LEFT JOIN (SELECT did,AVG(salary) FROM t_employee GROUP BY did) temp
ON t_department.did = temp.did;
#错误，from后面的t_department和temp表都没有salary字段，
#SELECT t_department.did ,dname,AVG(salary)出现AVG(salary)是错误的

SELECT t_department.did ,dname,pingjun
FROM t_department LEFT JOIN (SELECT did,AVG(salary) AS pingjun FROM t_employee GROUP BY did) temp
ON t_department.did = temp.did;

9.3 where/having型

当子查询结果作为外层另一个SQL的过滤条件，通常把子查询嵌入到WHERE或HAVING中。根据子查询结果的情况，分为如下三种情况。

当子查询的结果是单列单个值，那么可以直接使用比较运算符，如“<”、“<=”、“>”、“>=”、“=”、“!=”等与子查询结果进行比较。
当子查询的结果是单列多个值，那么可以使用比较运算符IN或NOT IN进行比较。
当子查询的结果是单列多个值，还可以使用比较运算符, 如“<”、“<=”、“>”、“>=”、“=”、“!=”等搭配ANY、SOME、ALL等关键字与查询结果进行比较。

#（1）在“t_employee”表中查询薪资最高的员工姓名（ename）和薪资（salary）。
#SELECT ename,MAX(salary) FROM t_employee;#错误
#取表中第一行员工的姓名和全公司最高的薪资值一起显示。

SELECT ename,salary
FROM t_employee
WHERE salary = (SELECT MAX(salary) FROM t_employee);

#（2）在“t_employee”表中查询比全公司平均薪资高的男员工姓名和薪资。
SELECT ename,salary
FROM t_employee
WHERE salary > (SELECT AVG(salary) FROM t_employee) AND gender = '男';

#（3）在“t_employee”表中查询和“白露”，“谢吉娜”同一部门的员工姓名和电话。
SELECT ename,tel,did
FROM t_employee
WHERE did IN(SELECT did FROM t_employee WHERE ename='白露' or ename='谢吉娜');

SELECT ename,tel,did
FROM t_employee
WHERE did =ANY(SELECT did FROM t_employee WHERE ename='白露' or ename='谢吉娜');


#（4）在“t_employee”表中查询薪资比“白露”，“李诗雨”，“黄冰茹”三个人的薪资都要高的员工姓名和薪资。
SELECT ename,salary
FROM t_employee
WHERE salary >ALL(SELECT salary FROM t_employee WHERE ename IN('白露','李诗雨','黄冰茹'));


#（5）查询“t_employee”和“t_department”表，按部门统计平均工资，
#显示部门平均工资比全公司的总平均工资高的部门编号、部门名称、部门平均薪资，
#并按照部门平均薪资升序排列。
SELECT t_department.did,dname,AVG(salary)
FROM t_employee RIGHT JOIN t_department
ON t_employee.did = t_department.did
GROUP BY t_department.did
HAVING AVG(salary) >(SELECT AVG(salary) FROM t_employee)
ORDER BY AVG(salary);

9.4 exists型

EXISTS型子查询也是存在外层SELECT的WHERE子句中，不过它和上面的WHERE型子查询的工作模式不相同，所以这里单独讨论它。

如果EXISTS关键字后面的参数是一个任意的子查询，系统将对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS的结果为false，此时外层查询语句不进行查询。EXISTS和NOT EXISTS的结果只取决于是否返回行，而不取决于这些行的内容，所以这个子查询输入列表通常是无关紧要的。

如果EXISTS关键字后面的参数是一个关联子查询，即子查询的WHERE条件中包含与外层查询表的关联条件，那么此时将对外层查询表做循环，即在筛选外层查询表的每一条记录时，都看这条记录是否满足子查询的条件，如果满足就再用外层查询的其他WHERE条件对该记录进行筛选，否则就丢弃这行记录。

#（1）查询“t_employee”表中是否存在部门编号为NULL的员工，
#如果存在，查询“t_department”表的部门编号、部门名称。
SELECT * FROM t_department 
WHERE EXISTS(SELECT * FROM t_employee  WHERE did IS NULL);

#（2）查询“t_department”表是否存在与“t_employee”表相同部门编号的记录，
#如果存在，查询这些部门的编号和名称。
SELECT * FROM t_department
WHERE EXISTS(SELECT * FROM t_employee WHERE t_employee.did = t_department.did);

#查询结果等价于下面的sql
SELECT DISTINCT t_department.*
FROM t_department INNER JOIN t_employee
ON t_department.did = t_employee.did;

附录:

1. 字符集和校对集

字符集作用：当向数据库存储数据，特别是非ASCII码的字符串，例如汉字时，如果不指定正确的字符集，可能会导致乱码。

校对集作用：查询数据时，是否区分大小写。

1.1 字符集

字符集在什么时候可以发挥作用?
- 保存数据的时候需要使用字符集
- 数据传输的时候也需要使用字符集
常见字符集
- ASCII: 基于罗马字母表的一套字符集, 它采用1个字节的低7位表示字符, 高位始终为0。
- LATIN1: 相对于ASCII字符集做了扩展, 仍然使用一个字节表示字符, 但启用了高位, 扩展了字符集的表示范围。
- GB2312: 简体中文字符, 一个汉字最多占用2个字节
- GB: 只是所有的中文字符, 一个汉字最多占用2个字节
- UTF8: 国际通用编码, 一个汉字最多占用3个字节
- UTF8MB4: 国际通用编码, 在utf8的基础上加强了对新文字识别, 一个汉字最多占用4个字节

查看当前数据库使用的字符集。

show variables like "%character%";
/*输出
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | gbk                                                     |
| character_set_connection | gbk                                                     |
| character_set_database   | latin1                                                    |
| character_set_filesystem | binary                                                  |
| character_set_results    | gbk                                                     |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.7\share\charsets\ |
+--------------------------+---------------------------------------------------------+
*/

1.2 校对集

在某一种字符集下, 为了使字符之间可以互相比较, 让字符和字符形成一种关系的集合, 称之为校对集。

比如说 ASCII 中的 a 和 B, 如果区分大小写 a > B, 如果不区分 a < B;

不同字符集有不同的校对规则, 命名约定：以其相关的字符集名开始, 通常包括一个语言名, 并且以 _ci、_cs 或 _bin 结束。

_ci: 大小写不敏感
_cs: 大小写敏感
_bin: binary collation 二元法, 直接比较字符的编码, 可以认为是区分大小写的, 因为字符集中’A’和’a’的编码显然不同。

/* 数据库默认的排序方式,是升序 */
create table t1(
    str char(1)
) charset=utf8mb4 collate=utf8mb4_general_ci;  -- _general_ci 后缀的都是不区分大小写的

create table t2(
    str char(1)
) charset=utf8mb4 collate=utf8mb4_bin;  -- 看到后缀边是_bin的都是区分大小的

查看当前数据库的校对集:

show variables like "collation%";
/* 输出
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | gbk_chinese_ci    |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
*/

查看MySQL所有支持的字符集和校对集:

show character set;  -- 查看字符集 和 校对集
show collation;      -- 显示所有的校对集

校对集的设置和修改方式:

create database [数据库名] charset=utf8 collate=utf8_bin;  -- 创建数据库时指定编码方式和校对集

alter database [数据库名] collate utf8_bin; -- 修改数据库的校对集
alter table [表名] collate utf8_bin; -- 修改表的校对集
alter table [表明] modify [字段名] [字段属性] collate utf8_bin;  -- 修改指定字段的校对集

2 MySQL5.7乱码问题

create database test;
use test;
create table student(
	id int primary key auto_increment,
    name varchar(32)
);
insert into student values(null,"zhangsan");  -- 数据可以正常存入
insert into student values(null,"张三");  -- 报错，无法存入数据!

在Windows中安装MySQL数据库，数据库存储数据默认使用的是Latin1编码方式，是不允许存入中的，所以会报错！

解决方法一:

对于已经存在的数据库，只能逐个修改字符集编码。

修改数据库使用的编码集。

show create database test; -- 查看数据库的创建语句，发现默认使用的是Latin1编码
/*输出
+----------+-----------------------------------------------------------------+
| Database | Create Database                                                 |
+----------+-----------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+-----------------------------------------------------------------+
1 row in set (0.00 sec)

*/

alter database test charset utf8; -- 将数据库的字符集修改为utf8编码
/*输出
Query OK, 1 row affected (0.00 sec)
*/

show create database test; -- 再次查看数据库的编码，发现被修改成了 utf8
/*输出
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)
*/

虽然此时已经修改了数据库的编码方式，但是现在数据库里的表格依然无法输入中文！这是因为，创建表格时使用的是数据库的编码集，只修改数据库的编码集还不够，还需要手动的再修改表格的编码集。

show create table student;  -- 查看 student 的建表语句，发现虽然数据库的字符集改了，但是表格的字符集还是latin1
/*
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                    |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
*/
alter table student charset utf8;  -- 将表格的字符集编码修改为utf8

show create table student; -- 再次查看，发现表格的字符集编码也被修改成为了utf8

此时依然无法存入中文。这是因为，name字段里的字符集编码还是latin1，所以，还要修改字段的字符集才能输入中文！
```
alter table student modify name varchar(32) charset utf8;  -- 将 name 字段的编码方式修改为 utf8
```
此时终于可以写入中文了！从上述的步骤可以看出，如果使用了默认的latin1编码格式创建了一个数据库，并在库里创建表格添加数据以后，如果想再修改编码方式，会非常的繁琐。所以，<span style="color:red;font-size:24px;font-weight:bold">在创建数据库时，一定要手动的指定好编码格式为utf8！</span>

解决方法二:

第一种方法产生的原因是因为在创建数据库的时候，没有指定字符集编码导致的。如果在创建数据库的时候，直接就指定数据库的字符集编码为utf8，就不会出现上述的问题了！

create database test charset utf8;  -- 在创建数据库的时候，直接就指定编码集!
use test;
create table student(
	id int primary key auto_increment,
    name varchar(32)
);
insert into student values(null,"zhangsan"); 
insert into student values(null,"张三");

解决方法三:

修改Windows里MySQL的配置文件，修改MySQL创建数据库的默认编码方式。

找到C:\ProgramData\MySQL\MySQL Server 5.7文件夹。
编辑my.ini文件。.ini文件里，使用# 来显示一个注释。

[mysql]
no-beep
# 找到 default-character-set 选项，将值改为 utf8,并把注释去掉。
default-character-set=utf8

# The default character set that will be used when a new schema or table is
# created and no character set is defined
# 找到 character-set-server 选项，将值改为utf8,并把注释去掉
character-set-server=utf8

重启MySQL服务器，以后再创建数据库时，默认都会使用utf8编码。