MySQL 数据库

1.概念与安装

我们每天都在访问各种网站、APP，如微信、QQ、抖音、今日头条、腾讯新闻等，这些东西上面都存在大量的信息，这些信息都需要有地方存储，一般都是存储在数据库中。 

所以如果我们需要开发一个网站、app，数据库我们必须掌握的技术。常用的数据库有 mysql、 oracle、sqlserver、db2 等。上面介绍的几个数据库，oracle性能排名第一，服务也是相当到位的，但是收费也是非常高的，金融公司对数据库稳定性要求比较高，一般会选择 oracle。

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库，每个数据库都有一个或多个不同的 API 用于创建，访问，管理，搜索和复制所保存的数据。

SQL（Structured Query Language 结构化查询语言）是一种定义、操作、管理关系数据库的句法。大多数关系型数据库都支持。结构化查询语言的工业标准由 ANSI (美国国家标准学会，ISO 的成员之一)维护。世界著名数据库厂商如 Oracle、MySQL 都遵循 SQL 规范，只是在各家设计数据库时为了性能更好，相同功能的 SQL 有一些小小的区别。  

Mysql 数据库

MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司。MySQL 是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

• Mysql 是开源的，所以你不需要支付额外的费用。
• Mysql 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。
• MySQL 使用标准的 SQL 数据语言形式。
• Mysql 可以允许于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。
• Mysql 对 PHP 有很好的支持，PHP 是目前最流行的 Web 开发语言。
• MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为 8TB。
• Mysql 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 Mysql 系统。

搜索 phpStudy  V8.1  ，下载大约 78m 左右：官网下载地址

phpStudy 可以随时升级和更换 mysql 版本，安装其他常用插件。而且 phpStudy 对于数据库的移植，备份，启动，操作等等都特别方便。

安装完 phpStudy 后会在 D 盘解压一个文件夹 phpstudy_pro，将来如果有需要可以直接考走

MySQL 的默认账户与密码分别是 root  root

启动数据库后，在左侧导航栏数据库中点击操作可以修改数据库密码。

phpStudy  默认自带一个好用的 MySQL 管理工具 HeidiSQL，不想安装 SQLyog 或 Navicat 数据库管理工具的可以使用它。

点击管理，输入基本信息即可连接数据库。

当然也可以使用 SQLyog 管理数据库：下载地址

打开 SQLyog，新建一个连接，填入相关信息，即可在 JspStudy 的 MySQL 启动的情况下连上数据库

还可以使用 idea 连接 MySQL

 2.小皮系统内置项目

小皮系统内置很多开发软件，同时包含很多集成环境，本节主要介绍小皮内置的 cms 项目。

CMS是Content Management System的缩写，中文意思是内容管理系统。它是一种用来创建、编辑、管理和发布内容的软件系统，通常用于网站和其他在线平台。CMS允许用户在不需要编写代码的情况下，轻松地添加、修改和删除网站内容，从而提高网站的管理和维护效率。常见的CMS包括WordPress、Joomla和Drupal等。

这些系统个人觉得从上到下越来越界面美观加功能实用。本文就以优客365介绍其部署流程。

1.以上项目都是使用 php 开发，因此需要安装 php 环境，为了兼容性更好建议尽量安装每个大版本偶数号 php。

2.安装 Apache2.4.43 并将其启动，注意不能使用 nginx。并检查网站栏目下为空，且数据库中只有超级管理员。然后点击一键部署对应的CMS项目。

域名填写localhost，php版本可以不填，项目部署完成后会启动报错并导致 apache 关闭。原因是 PHP 没有找到 _p 的版本。

3.打开网站管理里面的PHP版本调整合适即可。在重启 apache 服务即可。

4.一般项目部署完成后都可以通过浏览器访问 http://localhost/index.html 或 http://localhost/index 关于后台管理界面及其密码可以通过 CMS 的安装消息即可获取。

练习：

1.安装小皮前 6 个项目，并将过程及项目成功截图保存到 word 提交。

3.库表操作

数据库作为数据表存储的基本单位，即一个数据库存储一张和多张表，同一个数据库中数据表可以关联查询。一般的，在实际开发中每个项目都会使用独立的一个数据库，一个项目也不建议连接多个数据库。

CREATE DATABASE my_database

当再次执行时会报错，数据库已经存在（exists）

错误代码： 1007
Can't create database 'my_database'; database exists

使用命令查看所有的数据库

SHOW DATABASES

删除数据库

DROP DATABASE my_database;

使用数据库，方便在数据库中创建表。

USE my_database;

数据库中每列数据的标识叫字段，表操作一般就是针对字段的操作。创建表时一般会有一个或多个字段，它们之间用逗号隔开。数据库中最常用的两种字段类型为 int 和 char 分别用于存储整数和字符串类型的数据，char 类型后需要指定存储的字符个数。

CREATE TABLE my_table (
    id INT,
    name CHAR(5)
);

创建完表后可以使用 desc 表名查看表结构。

mysql> desc my_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id    | int(11) | YES  |     | NULL    |       |
| name  | char(5) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00 sec)

字段的增删改操作，下面 SQL 演示如何操作字段

ALTER TABLE my_table  DROP id;  删除字段
ALTER TABLE my_table  ADD ids INT;  增加字段

ALTER TABLE my_table  ADD id INT FIRST;  添加一个字段在最前面
ALTER TABLE my_table  ADD sex INT AFTER name; 增加一个字段在 name 后

ALTER TABLE my_table MODIFY name CHAR(10);修改 name 字段的长度为 10
ALTER TABLE my_table MODIFY ids BIGINT NOT NULL DEFAULT 100;修改 ids 字段的属性为 long 不为空默认值为 100

修改表名为 mytable

ALTER TABLE my_table RENAME TO mytable;

刪除表 mytable

DROP TABLE mytable

注意：

删除表有 drop [drɒp]，truncate [trʌŋˈkeɪt]，delete [dɪˈliːt] 三种操作，他们的区别如下：

drop：删除数据和表结构，释放空间。

drop table user;

truncate：保留表结构，删除数据，释放空间。

truncate table user;

delete：保留表结构，删除数据，不释放空间。

delete from user;

三者的执行速度，一般来说：drop > truncate > delete

释放空间可以体现在：

1.通过 delete 删除的行数据是不释放空间的，如果表 id 是递增式的话，那么表数据的 id 就可能不是连续的；而通过 truncate 删除数据是释放空间的，如果表 id 是递增式的话，新增数据的 id 又是从头开始，而不是在已删数据的最大 id 值上递增。

2.使用 delete 删除数据时，mysql 并没有把数据文件删除，而是将数据文件的标识位删除，没有整理文件，因此不会彻底释放空间。被删除的数据将会被保存在一个链接清单中，当有新数据写入的时候，mysql 会利用这些已删除的空间再写入。即，删除操作会带来一些数据碎片，正是这些碎片在占用硬盘空间。

4.基本增删改查

数据库的每条数据通常称为一条记录，记录的常用操作通常就是增删改查，往数据表中插入记录使用如下语法：

INSERT INTO table [(字段名 [, 字段名...])]
VALUES		(value [, value...]);

注意：

• 插入的数据应与字段的数据类型相同。 
• 数据的大小应在列的规定范围内，例如：不能将一个长度为 80 的字符串加入到长度为 40 的列中。 
• 在 values 中列出的数据位置必须与被加入的列的排列位置相对应。 字符和日期型数据应包含在单引号中。 
• 插入空值，不指定或 insert into table value(null) 即可插入空值。

示例：

INSERT INTO `emp` (`name`,`sal`) VALUES ('张三',10000.5);

修改记录

修改数据表中记录使用如下语法：

UPDATE table_name SET 字段名1=expr1 [, 字段名2=expr2 ...] [WHERE where_definition]

注意：

• UPDATE 语法可以用新值更新原有表行中的各列。 
• SET 子句指示要修改哪些列和要给予哪些值。 
• WHERE 子句指定应更新哪些行。如没有 WHERE 子句，则更新所有的行。

示例：

UPDATE emp SET `name` ='李四'
UPDATE emp SET `name`='王二' WHERE `id` = 1;
UPDATE emp SET `name`='王二',`sal`=16.5 WHERE `id` = 1;
UPDATE emp SET `name`='王二',`sal`=16.5 WHERE `id` IN(1,3,5);

注意：如果在数据修改时，该表的全部字段相同，修改命令发送时会导致多条数据被修改。同时 SQLYog 会弹窗。所以为了避免这种问题数据库各大厂商提出数据库三大范式作为约束。

删除记录

删除数据表的数据使用如下语法：

delete from table_name [WHERE where_definition]

注意：

• 如果不使用 where 子句，将删除表中所有数据。 
• 使用 delete 语句仅删除记录，不删除表本身。
• 如要删除表，使用 drop table 语句。 

示例：

delete from `emp` where id = 1;

简单查询

查询数据表数据一般使用如下语法：

SELECT [DISTINCT] *|{字段名1, 字段名2, 字段名3..} FROM table;

参数：

• Select 指定查询哪些列的数据。 
• * 号代表查询所有列，在实际开发中很少用 *，它会增加查询的负担。 
• From 指定查询哪张表。 
• DISTINCT [dɪˈstɪŋkt] 可选，指显示结果时，是否剔除重复数据 

示例：

SELECT * FROM `emp`;
SELECT `name`,`sal` FROM `emp`;
SELECT DISTINCT `name`,`sal` FROM `emp`;

练习：

1.创建数据库 user 并在 user 库中创建表 user （int id，String name，int age，String password），并往数据库中插入 3 条如下记录。

User{id=1,name='张三',password='123456'}

User{id=2,name='李四',password='123456'}

User{id=3,name='王二',password='123456'}

2.将上面 id 为 1 的密码改为：abc123。

3.删除密码为 123456 的全部用户。

4.在 user 表中增加一个整数类型的字段 age 在 name 后，并插入数据。

User{id=4,name='麻子',password='123456',age=12}

参考代码：

CREATE DATABASE `user`; 

CREATE TABLE `user`( `id` INT, `name` CHAR(10), `password` CHAR(10) );

INSERT INTO `user` (`id`, `name`, `password`) VALUES ('1', '张三', '123456'); 
INSERT INTO `user` (`id`, `name`, `password`) VALUES ('2', '李四', '123456'); 
INSERT INTO `user` (`id`, `name`, `password`) VALUES ('3', '王二', '123456');

UPDATE `user` SET `password` = 'abc123' WHERE `id` = '1';

DELETE FROM `user`.`user` WHERE  `password` = '123456';

ALTER TABLE mytb  ADD age INT AFTER name;

INSERT INTO `user` (`id`, `name`, `password`,`age`) VALUES ('4', '麻子', '123456',12);

5.别名运算符和排序

在查询过程中，使用原字段的名称有时候不太方便，MySQL 提供别名处理这样的情况。

SELECT 字段名 AS 别名 FROM 表名;

AS 可以省略

示例：

SELECT `name` 姓名,`sal` 薪水 FROM `emp`;

注意：别名处可以不使用单引号，但是为了不和数据库的一些关键字冲突建议所有表名、字段名都加上数据库专用单引号（一般位于 tab 键上方）。

运算符

在 WHERE 子句中经常使用相关的运算符，常见运算符有比较运算符、逻辑运算符等。

比较运算符 

>   <   <=   >=   =    <>/!=	大于、小于、大于(小于)等于、不等于
BETWEEN  ...AND...	        显示在某一区间的值(含头含尾)
IN(set)	                        显示在 in 列表中的值，例：in(100,200)
LIKE ‘%张_’	                模糊查询
IS NULL	                        判断是否为空（is not null）

LIKE 语句中，% 代表零个或多个任意字符，_ 代表一个字符。

示例：

name LIKE ‘_a%'    表示第二个字符为 a 的人
name like '张%'     姓张的所有人
name like '_伟'     所有姓名为两个字且第二个字为 “伟”
name like '%商%'    姓名中包含 “商”字
name like '%友'     姓名以“友”结尾

逻辑运算符

and	                        多个条件同时成立
or	                        多个条件任一成立
not	                        不成立，例：where not(salary>100);

示例：

SELECT `id`, `name`,`sal` FROM `emp` WHERE id > 1;
SELECT `id`, `name`,`sal` FROM `emp` WHERE id BETWEEN 1 AND 3;
SELECT `id`, `name`,`sal` FROM `emp` WHERE `name` IS NULL;
SELECT `id`, `name`,`sal` FROM `emp` WHERE `name` LIKE '%王%' AND id = 1;

排序

排序的列即可是表中的列名，也可以是 SELECT 语句后指定的列名。 

ASC 升序、DESC 降序 

ORDER BY 子句应位于 SELECT 语句的结尾。 

案例：

SELECT `id`, `name`,`sal` FROM `emp` WHERE `id` > 0 ORDER BY `name` ASC ;
SELECT * FROM `mytabe` ORDER BY `sex` DESC,`id` DESC

1.执行如下数据脚本，并完成下面查询。

create table student(
	id int,
	name varchar(20),
	chinese float,
	english float,
	math float
)ENGINE = InnoDB DEFAULT CHARSET = utf8;
insert into student(id,name,chinese,english,math) values(1,'张小明',89,78,90);
insert into student(id,name,chinese,english,math) values(2,'李进',67,98,56);
insert into student(id,name,chinese,english,math) values(3,'王五',87,78,77);
insert into student(id,name,chinese,english,math) values(4,'李一',88,98,90);
insert into student(id,name,chinese,english,math) values(5,'李来财',82,84,67);
insert into student(id,name,chinese,english,math) values(6,'张进宝',55,85,45);
insert into student(id,name,chinese,english,math) values(7,'黄蓉',75,65,30);

作业：

1.查询表中所有学生的全部信息。
2.查询表中所有学生的姓名和对应的英语成绩。
3.过滤表中英语重复数据。
4.查询时在所有学生数学分数上加10分特长分。
5.统计每个学生的总分。
6.使用别名表示学生分数。
7.查询姓名为王五的学生成绩
8.查询英语成绩大于90分的同学
9.查询总分大于200分的所有同学
10.查询英语分数在 80－90之间的同学。
11.查询数学分数为89,90,91的同学。
12.查询所有姓李的学生成绩。
13.查询数学分>80，语文分>80的同学。
14.对数学成绩降序输出。
15.对总分排序后输出，然后再按从高到低的顺序输出
16.对姓李的学生成绩排序输出

 参考答案：

1.查询表中所有学生的全部信息。
mysql>SELECT * FROM student;
2.查询表中所有学生的姓名和对应的英语成绩。
mysql>SELECT name,english FROM student;
3.过滤表中英语重复数据。
mysql>SELECT DISTINCT english FROM student;
4.在所有学生数学分数上加10分特长分。
mysql>SELECT name,math+10 FROM student;
5.统计每个学生的总分。
mysql>SELECT name,chinese+english+math FROM student;
6.使用别名表示学生分数。
mysql>SELECT name AS 姓名,chinese+english+math 总分 FROM student;
7.查询姓名为王五的学生成绩
mysql>SELECT * FROM student WHERE name='王五';
8.查询英语成绩大于90分的同学
mysql>SELECT * FROM student WHERE english>90;
9.查询总分大于200分的所有同学
mysql>SELECT name AS 姓名,chinese+english+math 总分 FROM student WHERE (chinese+english+math)>200;
10.查询英语分数在 80－90之间的同学。
mysql>SELECT name,english FROM student WHERE english BETWEEN 80 AND 90;
11.查询数学分数为89,90,91的同学。
mysql>SELECT name,math FROM student WHERE math IN (89,90,91);
12.查询所有姓李的学生成绩。
mysql>SELECT * FROM student WHERE name LIKE '李%';
13.查询数学分>80，语文分>80的同学。
mysql>SELECT * FROM student WHERE math>80 AND chinese>80;
14.对数学成绩排序后输出。
mysql>SELECT name,math FROM student ORDER BY math;
15.对总分排序后输出，然后再按从高到低的顺序输出
mysql>SELECT name 姓名,chinese+english+math 总分 FROM student ORDER BY 总分 DESC;
16.对姓李的学生成绩排序输出
mysql>SELECT name 姓名,chinese+english+math 总分 FROM student WHERE name LIKE '李%' ORDER BY 总分;

6.数据库三范式

第一范式（1NF）： 字段具有原子性，不可再分。所有关系型数据库系统都满足第一范式

数据库表中的字段都是单一属性的，不可再分。例如，姓名字段，其中的姓和名必须作为一个整体，无法区分哪部分是姓，哪部分是名，如果要区分出姓和名，必须设计成两个独立的字段。 

第二范式（2NF）： 第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。 

要求数据库表中的每个实例或行必须可以被惟一地区分。通常需要为表加上一个列，以存储各个实例的唯一标识。这个唯一属性列被称为主关键字或主键。 实体的属性完全依赖于主关键字。所谓完全依赖是指不能存在仅依赖主关键字一部分的属性，如果存在，那么这个属性和主关键字的这一部分应该分离出来形成一个新的实体，新实体与原实体之间是一对多的关系。为实现区分通常需要为表加上一个列，以存储各个实例的唯一标识。简而言 之，第二范式就是非主属性非部分依赖于主关键字。 

第三范式（3NF）： 满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主关键字信息。 所以第三范式具有如下特征： 1，每一列只有一个值 2，每一行都能区分。 3，每一个表都不包含其他表已经包含的非主关键字信息。 例如，帖子表中只能出现发帖人的 id，而不能出现发帖人的 id，还同时出现发帖人姓名，否则，只要出现同一发帖人 id 的所有记录，它们中的姓名部分都必须严格保持一致，这就是数据冗余。 

7.用户和权限

创建用户

create user 用户名[@主机名] [identified by '密码'];

说明： 1. 主机名默认值为%，表示这个用户可以从任何主机连接mysql服务器

2. 密码可以省略，表示无密码登录

不指定主机名时，表示这个用户可以从任何主机连接mysql服务器

mysql> use mysql;
Database changed
mysql> select user,host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)
mysql> create user test1;
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host from user;
+---------------+-----------+
| user | host |
+---------------+-----------+
| test1 | % |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
4 rows in set (0.00 sec)

上面创建了用户名为 test1 无密码的用户，没有指定主机，可以看出host的默认值为 % ，表示 test1 可以从任何机器登录到mysql中。 用户创建之后可以在 mysql 库中通过 select user,host from user; 查看到。

示例

create user 'test2'@'localhost' identified by '123';

说明：test2的主机为localhost表示本机，此用户只能登陆本机的mysql

create user 'test3'@% identified by '123';

说明：test3可以从任何机器连接到mysql服务器

create user 'test4'@'192.168.11.%' identified by '123';

说明：test4可以从192.168.11段的机器连接mysql

用户授权

创建用户之后，需要给用户授权，才有意义。

grant privileges ON database.table TO 'username'[@'host'] [with grant option]

grant命令说明：

• priveleges (权限列表)，可以是 all ，表示所有权限，也可以是 select、update 等权限，多个权 限之间用逗号分开。
• ON 用来指定权限针对哪些库和表，格式为 数据库.表名 ，点号前面用来指定数据库名，点号后面 用来指定表名， *.* 表示所有数据库所有表。
• TO 表示将权限赋予某个用户, 格式为 username@host ，@前面为用户名，@后面接限制的主机， 可以是IP、IP段、域名以及%，%表示任何地方。
• WITH GRANT OPTION 这个选项表示该用户可以将自己拥有的权限授权给别人。注意：经常有人 在创建操作用户的时候不指定WITH GRANT OPTION选项导致后来该用户不能使用GRANT命令创 建用户或者给其它用户授权。 备注：可以使用GRANT重复给用户添加权限，权限叠加，比如你先 给用户添加一个select权限，然后又给用户添加一个insert权限，那么该用户就同时拥有了select和 insert权限。

示例：

grant all on *.* to 'test1'@‘%’;

说明：给test1授权可以操作所有库所有权限，相当于数据库管理员（dba）

grant select on seata.* to 'test1'@'%';

说明：test1可以对seata库中所有的表执行select

grant select,update on seata.* to 'test1'@'%';

说明：test1可以对seata库中所有的表执行select、update

grant select(user,host) on mysql.user to 'test1'@'localhost';

说明：test1用户只能查询mysql.user表的user,host字段

查看用户有哪些权限

show grants for '用户名'[@'主机']

主机可以省略，默认值为%，示例：

mysql> show grants for 'test1'@'localhost';
+--------------------------------------------------------------------+
| Grants for test1@localhost |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'localhost' |
| GRANT SELECT (host, user) ON `mysql`.`user` TO 'test1'@'localhost' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)

查看当前用户的权限，show grants 如：

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT ALL PRIVILEGES ON `test`.* TO 'root'@'localhost' |
| GRANT DELETE ON `seata`.* TO 'root'@'localhost' |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |
+---------------------------------------------------------------------+
4 rows in set (0.00 sec)

撤销用户的权限

revoke privileges ON database.table FROM '用户名'[@'主机'];

可以先通过 show grants 命令查询一下用户对于的权限，然后使用 revoke 命令撤销用户对应的权限， 示例：

mysql> show grants for 'test1'@'localhost';
+--------------------------------------------------------------------+
| Grants for test1@localhost |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'localhost' |
| GRANT SELECT (host, user) ON `mysql`.`user` TO 'test1'@'localhost' |
+--------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql> revoke select(host) on mysql.user from test1@localhost;
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'test1'@'localhost';
+--------------------------------------------------------------+
| Grants for test1@localhost |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'test1'@'localhost' |
| GRANT SELECT (user) ON `mysql`.`user` TO 'test1'@'localhost' |
+--------------------------------------------------------------+
2 rows in set (0.00 sec)

上面我们先通过 grants 命令查看test1的权限，然后调用revoke命令撤销对 mysql.user 表 host 字段 的查询权限，最后又通过grants命令查看了test1的权限，和预期结果一致。

删除用户

drop user '用户名'[@‘主机’]

示例：

mysql> drop user test1@localhost;
Query OK, 0 rows affected (0.00 sec)

drop的方式删除用户之后，用户下次登录就会起效。

通过删除mysql.user表数据的方式删除，如下：

delete from user where user='用户名' and host='主机';
flush privileges;

注意通过表的方式删除的，需要调用 flush privileges; 刷新权限信息（权限启动的时候在内存中保 存着，通过表的方式修改之后需要刷新一下）。

8.表约束

在数据库中为了保证数据库插入时按照指定规则插入，引入表约束，常见表约束有

1.主键约束 primary key [ˈpraɪməri]（不允许为空，不允许重复）如果是整数类型一般定义主键自动增长 auto_increment [ˈɔːtəʊ] [ˈɪŋkrəmənt]，一般通过可视化连接工具添加。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10),
  `password` char(10),
  PRIMARY KEY (`id`)
)

2.唯一约束，也叫唯一索引，用于设置该字段不可重复。

ALTER TABLE `user`.`user` ADD UNIQUE INDEX index_name (`name`);

3.非空约束 not null 用于指定该字段不能为空一般在创表时指定或可视化连接工具添加。也可以和 DEFAULT 配合使用，表示不输入时该字段的默认值。

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(10) NOT NULL,
  `password` char(10) NOT NULL DEFAULT 123456,
  PRIMARY KEY (`id`)
)

4.外键约束，在创建外键约束时一定要保证两个表的数据库引擎相同且不能为 MYISAM，一般通过可视化连接工具添加。

ALTER TABLE `client` ADD CONSTRAINT `emp_id_fk` FOREIGN KEY (`emp_id`) REFERENCES `emp`(`id`);

一般创表命令如下

CREATE TABLE mytb(
    id INT NOT NULL AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    pwd VARCHAR(40) NOT NULL,
    birth DATE,
    PRIMARY KEY ( id )
 )ENGINE=INNODB DEFAULT CHARSET=utf8;

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为 NULL ，就会报错。
• AUTO_INCREMENT 定义列为自增的属性，一般用于主键，每次插入数据后数值会自动加 1。
• PRIMARY KEY 关键字用于定义列为主键。 可以使用多列来定义主键称为复合主键，列间以逗号分隔。
• ENGINE 设置存储引擎，使用 show engines 命令可以查看 MySQL 支持的数据库引擎，CHARSET 设置编码。

注意：utf8 与 utf8mb4 的区别在于 utf8mb4 兼容部分图标字体如：☺◐❀♬ 。如果引擎和字符编码在安装时设置好的则在创表时可以不指定。

9.数据类型

主要包括以下五大类

• 整数类型： bit 、 bool 、 tinyint 、 smallint 、 mediumint 、 int 、 bigint
• 浮点数类型： float 、 double 、 decimal
• 字符串类型： char 、 varchar 、 tinyblob 、 blob 、 mediumblob 、 longblob 、 tinytext 、 text 、 mediumtext 、 longtext
• 日期类型： Date 、 DateTime 、 TimeStamp 、 Time 、 Year
• 其他数据类型：暂不介绍，用的比较少。

整数类型

类型	字节数	有符号值范围	无符号值范围
tinyint[(n)] [unsigned]	1	[-2^7 , 2^7-1]	[0 , 2^8-1]
smallint[(n)] [unsigned]	2	[-2^15 , 2^15-1]	[0 , 2^16-1]
mediumint[(n)] [unsigned]	3	[-2^23 , 2^23-1]	[0 , 2^24-1]
int[(n)] [unsigned]	4	[-2^31 , 2^31-1]	[0 , 2^32-1]
bigint[(n)] [unsigned]	8	[-2^63 , 2^63-1]	[0 , 2^64-1]

上面[]包含的内容是可选的，默认是有符号类型的，无符号的需要在类型后面跟上 unsigned

mysql> create table demo1(
c1 tinyint
);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into demo1 values(-pow(2,7)),(pow(2,7)-1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from demo1;
+------+
| c1 |
+------+
| -128 |
| 127 |
+------+
2 rows in set (0.00 sec)
mysql> insert into demo1 values(pow(2,7));
ERROR 1264 (22003): Out of range value for column 'c1' at row 1

demo1表中 c1 字段为tinyint有符号类型的，可以看一下上面的演示，有超出范围报错的。 

mysql> create table demo2(
c1 tinyint unsigned
);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into demo2 values (-1);
ERROR 1264 (22003): Out of range value for column 'c1' at row 1
mysql> insert into demo2 values (pow(2,8)+1);
ERROR 1264 (22003): Out of range value for column 'c1' at row 1
mysql> insert into demo2 values (0),(pow(2,8));
mysql> insert into demo2 values (0),(pow(2,8)-1);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from demo2;
+------+
| c1  |
+------+
| 0   |
| 255 |
+------+
2 rows in set (0.00 sec)

c1是无符号的tinyint类型的，插入了负数会报错。

注意：类型(n)在开发中，整型的写法是int(11)，这种写法个人感觉在开发过程中没有什么用途， 不过还是来说一下， int(N) 我们只需要记住两点：

无论N等于多少，int永远占4个字节。N表示的是显示宽度，不足的用0补足，超过的无视长度而直接显示整个数字，但这要整型设置了 unsigned zerofill才有效。

mysql> CREATE TABLE test3 (
`a` int,
`b` int(5),
`c` int(5) unsigned,
`d` int(5) zerofill,
`e` int(5) unsigned zerofill,
`f` int zerofill,
`g` int unsigned zerofill
);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test3 values (1,1,1,1,1,1,1),(11,11,11,11,11,11,11),
(12345,12345,12345,12345,12345,12345,12345);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test3;
+-------+-------+-------+-------+-------+------------+------------+
| a | b | c | d | e | f | g |
+-------+-------+-------+-------+-------+------------+------------+
| 1     | 1     | 1  | 00001 | 00001 | 0000000001 | 0000000001 |
| 11    | 11    | 11 | 00011 | 00011 | 0000000011 | 0000000011 |
| 12345 | 12345 | 12345 | 12345 | 12345 | 0000012345 | 0000012345 |
+-------+-------+-------+-------+-------+------------+------------+
3 rows in set (0.00 sec)

浮点类型

类型	字节大小	范围（有符号）	范围（无符号）	用途
float[(m,d)]	4	(-3.402823466E+38,3.402823466351E+38)	[0,3.402823466E+38)	单精度浮点数值
double[(m,d)]	8	(-1.7976931348623157E+308,1.7976931348623157E+308)	[0,1.7976931348623157E+308)	双精度浮点数值
decimal[(m,d)]	对DECIMAL(M,D)，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double 都是浮点型，而decimal是定点型。

浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度。float和double在不指定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整 数为10，小数为0。

mysql> create table test5(a float(5,2),b double(5,2),c decimal(5,2));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into test5 values (1,1,1),(2.1,2.1,2.1),(3.123,3.123,3.123),
(4.125,4.125,4.125),(5.115,5.115,5.115),(6.126,6.126,6.126),(7.116,7.116,7.116),
(8.1151,8.1151,8.1151),(9.1251,9.1251,9.1251),(10.11501,10.11501,10.11501),
(11.12501,11.12501,11.12501);
Query OK, 7 rows affected, 5 warnings (0.01 sec)
Records: 7 Duplicates: 0 Warnings: 5
mysql> select * from test5;
+-------+-------+-------+
| a | b | c |
+-------+-------+-------+
| 1.00  | 1.00  | 1.00  |
| 2.10  | 2.10  | 2.10  |
| 3.12  | 3.12  | 3.12  |
| 4.12  | 4.12  | 4.13  |
| 5.12  | 5.12  | 5.12  |
| 6.13  | 6.13  | 6.13  |
| 7.12  | 7.12  | 7.12  |
| 8.12  | 8.12  | 8.12  |
| 9.13  | 9.13  | 9.13  |
| 10.12 | 10.12 | 10.12 |
| 11.13 | 11.13 | 11.13 |
+-------+-------+-------+
11 rows in set (0.00 sec)

c是decimal类型，认真看一下输入和输出，发现decimal采用的是四舍五入

认真看一下 a 和 b 的输入和输出，尽然不是四舍五入，一脸闷逼，float和double采用的是四舍六 入五成双。 decimal插入的数据超过精度之后会触发警告。

四舍六入五成双： 就是5以下舍弃5以上进位，如果需要处理数字为5的时候，需要看5后面是否还有不为0的任何数 字，如果有，则直接进位，如果没有，需要看5前面的数字，若是奇数则进位，若是偶数则将5舍掉。

我们将浮点类型的（M,D）精度和标度都去掉，看看效果：

mysql> create table test6(a float,b double,c decimal);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into test6 values (1,1,1),(1.234,1.234,1.4),(1.234,0.01,1.5);
Query OK, 3 rows affected, 2 warnings (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 2
mysql> select * from test6;
+-------+-------+------+
| a | b | c |
+-------+-------+------+
| 1 | 1 | 1 |
| 1.234 | 1.234 | 1 |
| 1.234 | 0.01 | 2 |
+-------+-------+------+
3 rows in set (0.00 sec)

a和b的数据正确插入，而c被截断了。浮点数float、double如果不写精度和标度，则会按照实际显示。decimal不写精度和标度，小数点后面的会进行四舍五入，并且插入时会有警告!

mysql> select sum(a),sum(b),sum(c) from test5;
+--------+--------+--------+
| sum(a) | sum(b) | sum(c) |
+--------+--------+--------+
| 67.21 | 67.21 | 67.22 |
+--------+--------+--------+
1 row in set (0.00 sec)
mysql> select sum(a),sum(b),sum(c) from test6;
+--------------------+--------------------+--------+
| sum(a) | sum(b) | sum(c) |
+--------------------+--------------------+--------+
| 3.4679999351501465 | 2.2439999999999998 | 4 |
+--------------------+--------------------+--------+
1 row in set (0.00 sec)

从上面sum的结果可以看出 float 、 double 会存在精度问题， decimal 精度正常的，比如银行对统计 结果要求比较精准的建议使用 decimal 。

日期类型

类型	字节大小	格式	用途
DATE	3	YYYY-MM-DD	日期值
TIME	3	HH:MM:SS	时间值或持续时间
YEAR	1	YYYY	年份值
DATETIME	8	YYYY-MM-DD HH:MM:SS	混合日期和时间值
TIMESTAMP	4	1970-01-01 00:00:00/2038结束时间是第 2147483647秒，北京时间2038-1-19 11:14:07，格林尼治时间2038年1月19日凌晨 03:14:07	混合日期和时间 值，时间戳

字符串类型

类型	范围	存储所需字节	说明
char(M)	[0,m]，m的范围[0,28-1]	m	定长字符串
varchar(M)	[0,m]，m的范围[0,216-1]	m允	0-65535 字节
tinyblob	0-255(28-1)字节	L+1	不超过 255 个字符的二进制字符串
blob	0-65535(216-1)字节	L+2	进制形式的长文本数据
mediumblob	0-16777215(224-1)字节	L+3	进制形式的中等长度文本数据
longblob	0-4294967295(232-1)字节	L+4	二进制形式的极大文本数据
tinytext	0-255(28-1)字节	L+1	短文本字符串
text	0-65535(216-1)字节	L+2	长文本数据
mediumtext	0-16777215(224-1)字节	L+3	中等长度文本数据
longtext	0-4294967295(232-1)字为	L+4	极大文本数据

char类型占用固定长度，如果存放的数据为固定长度的建议使用char类型，如：手机号码、身份证等固 定长度的信息。 表格中的L表示存储的数据本身占用的字节，L 以外所需的额外字节为存放该值的长度所需的字节数。 MySQL 通过存储值的内容及其长度来处理可变长度的值，这些额外的字节是无符号整数。

练习：

1.创建一张表 system_user 包含 int id 自增主键，char(10) name 不为空，varchar(255) password 不为空且默认值为 123456，blob image 图片，text information 介绍，tinyint sex 性别，decimal(10,2) money 资产，DATETIME birthdays 生日，并使用 SQLYog 往表中插入 3 条测试数据。

2.创建一张表 system_classroom 包含 int id 不为空且自增，varchar(50) name 不为空，并往表中插入三个班级：JAVA，UI，新媒体

3.在 system_user 中加入字段 classroom_id，并添加外键指向班级表中的 id，同时为所有的 system_user 分配自己的班级。

以 word 形式提交。

参考答案：

#1、创建一张表 system_user 包含 int id，char（10） name， varchar （255） password 不为空且默认值 123456，blob image、text information
#tinyint sex,decimal(10,2) money. id 不为空且自增。并插入3条数据
create table system_user(
id int primary key not null auto_increment comment '编号',
name char(10) not null comment '姓名',
password varchar(255) not null default '123456' comment '密码',
image blob comment '头像',
information text comment '简历',
sex tinyint comment '性别',
money decimal(10,2) comment '余额'
)
#2、创建一张表 system_classroom 包含int id, varchar(50) name 不为空.并插入三个班级 JVAV ，UI ，新媒体
create table system_classroom(
id int primary key not null  comment '班级编号',
name varchar(50) comment '班级名称'
)
#3、在system_user中加入 system_user ,并添加外键指向班级表中的id，同时为system_user分配自己的班级
alter table system_user add classroom_id int after id;
alter table system_user add constraint id foreign key (classroom_id) references system_classroom(id)

 10.MySQL8的Json格式

MySQL 8.0 引入了对 JSON 数据类型的更强大支持，使得处理 JSON 数据更加灵活。以下是关于 MySQL 8 的 JSON 格式的一些重要特性和用法：

1. JSON 数据类型

MySQL 提供了JSON数据类型，可以存储和处理 JSON 格式的数据。你可以通过如下方式定义一个表中的 JSON 列：

CREATE TABLE my_table (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    data JSON  
);  

2. JSON 函数

MySQL 提供了一系列强大的 JSON 函数来操作 JSON 数据。这些函数包括但不限于：

• JSON_OBJECT(): 创建一个 JSON 对象。
• JSON_ARRAY(): 创建一个 JSON 数组。
• JSON_EXTRACT(): 提取 JSON 数据中的值。
• JSON_SET(): 更新 JSON 数据中的值。
• JSON_REMOVE(): 从 JSON 数据中删除某个键。

3. 查询 JSON 数据 可以在 SQL 查询中使用 JSON 函数来获取和筛选 JSON 数据。例如，如果希望从data列中提取特定的 JSON 值：

SELECT JSON_EXTRACT(data, '$.key') AS value FROM my_table WHERE id = 1;  

4. 索引支持 MySQL 8.0 还支持在 JSON 列上创建虚拟列并对其进行索引，以提高查询性能。例如：

ALTER TABLE my_table ADD COLUMN key_value VARCHAR(255) AS (JSON_UNQUOTE(JSON_EXTRACT(data, '$.key'))) STORED;  
CREATE INDEX idx_key_value ON my_table(key_value);  

以下是一个创建和操作 JSON 数据的简单示例：

-- 创建表  
CREATE TABLE users (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    info JSON  
);  

-- 插入数据  
INSERT INTO users (info) VALUES ('{"name": "Alice", "age": 30, "city": "New York"}');  
INSERT INTO users (info) VALUES (JSON_OBJECT('name', 'Bob', 'age', 25, 'city', 'Los Angeles'));  
INSERT INTO users (info) VALUES (JSON_ARRAY('Alice', 'Bob', 'Charlie'));  

-- 提取数据  
SELECT JSON_EXTRACT(info, '$.name') AS user_name FROM users WHERE id = 1;  

-- 更新数据  
UPDATE users SET info = JSON_SET(info, '$.city', 'San Francisco') WHERE id = 1;  

-- 删除数据中的某个键  
UPDATE users SET info = JSON_REMOVE(info, '$.age') WHERE id = 1;  

-- 查询更新后的数据  
SELECT * FROM users WHERE id = 1;  

通过这些示例可以看到如何在 MySQL 8 中利用 JSON 数据类型和相关函数，灵活地进行数据插入、更新和查询。

11.视图与触发器

视图其实就是一张虚拟表，一张从别的表中导出的一张不完整或经过运算的虚拟表。

视图有什么用，当我们经常查询一个表中的某几个字段的时候，我们可以把这几个字段单独导出一个视图，以后查询的时候直接可以 select *，在数据库中只存放了视图的定义，没有存放视图的数据，视图是依赖原来表中的数据的，原表中的数据一旦发生变化，视图查询出的结果也会跟着变化。下面进行创建一个视图。 

案例：给语文老师创建一个视图

CREATE VIEW vi_chinese_user 
AS SELECT id,`name`,`chinese` FROM `student`;

视图在查询时和普通表查询语句完全相同。删除视图使用命令如下：

DROP VIEW `vi_chinese_user`;

触发器是由事件来触发某个操作，这些事件包括 insert、delete、update 语句。当数据库执行这些事件时，就会激活触发器执行相应的操作。 

我们需要一个表，这里用 user 表，用来对该表进行增删改操作，另外需要一个 user_log 表，当对 user 表操作的时候，会激活我们的触发器，对 user_log 表做出相应的操作。

CREATE TRIGGER auto_save_time BEFORE INSERT 
ON `user` FOR EACH ROW 
INSERT INTO user_log(log_time) VALUES(NOW());

这条语句的意思是，创建一个触发器名字是 auto_save_time，在 user 表的任意行执行 insert 语句之前会激活触发器，并执行 insert into user_log(log_time) values(now()); 语句。下面我们对 user 表进行一个 insert 操作，可以看出，激活了触发器，并执行了那条语句。now() 表示当前时间括号前不能有空格。

查看触发器使用命令 show triggers；删除触发器使用命令 drop trigger 触发器名；触发器有 6 个 分别是插入前后，删除前后，修改前后六个，与前面案例中的 before 对应的还有 after。

练习：

1.创建一张表 student(id,name) 和 student_log (id,log_time,method)，创建 3 个触发器，完成 student 表的所有修改操作的记录。

参考答案：

CREATE TABLE `student_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `log_time` DATETIME NOT NULL,
  `method` VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TABLE `student` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;


CREATE TRIGGER student_insert BEFORE INSERT 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'添加');

CREATE TRIGGER student_update BEFORE UPDATE 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'修改');

CREATE TRIGGER student_delete BEFORE DELETE 
ON `student` FOR EACH ROW 
INSERT INTO `student_log`(`log_time`,`method`) VALUES(NOW(),'删除');

12.存储过程

存储过程 (Stored Procedure) 是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集 , 存储在数据库中，经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它，存储过程是数据库中的一个重要对象；存储过程中可以包含逻辑控制语句和 数据操纵语句 , 它可以接受参数，输出参数，返回单个或多个结果集以及返回值；

create table p_user(
	id int primary key auto_increment,
	name varchar(10),
	sex char(2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into p_user(name,sex) values('A',"男");
insert into p_user(name,sex) values('B',"女");
insert into p_user(name,sex) values('C',"男");

创建存储过程(查询得到男性或女性的数量，如果传入的是 0 就获取女性的数量，否则获取男性的数量)  

CREATE PROCEDURE ges_user_count(IN sex_id INT, OUT user_count INT)
BEGIN
    IF sex_id = 0 THEN
        SELECT COUNT(*) FROM p_user WHERE p_user.sex = '女' INTO user_count;
    ELSE
        SELECT COUNT(*) FROM p_user WHERE p_user.sex = '男' INTO user_count;
    END IF;
END

调用存储过程

SET @user_count = 0;
CALL ges_user_count(1, @user_count);
SELECT @user_count;

查询得到男性或女性的数量, 如果传入的是 0 就女性否则是男性。

13.数据处理函数

在查询数据时有事需要对查出的字段进行处理，如小写转大写，切割等。这时就需要使用数据库的字符串处理函数，如下是 mysql 常用字符串处理函数。

CHARSET(字段)	                            返回字段的字符集
CONCAT(string2  [,... ])	            连接字串
INSTR(string ,substring )	            返回substring在string中出现的位置,没有返回0
UCASE(string2 )	                            转换成大写
LCASE(string2 )	                            转换成小写
LEFT(string2 ,length )	                    从string2中的左边起取length个字符
LENGTH(string )	                            返回string字节数量
REPLACE(str ,search_str ,replace_str )	    在str中用replace_str替换search_str
STRCMP(string1 ,string2 )	            逐字符比较两字串大小,返回1、0和-1
SUBSTRING(str , position  [,length ])	    从str的position开始,取length个字符
LTRIM(string2 ) RTRIM (string2 )            trim 去除前端空格或后端空格

案例：

SELECT CHARSET(`name`) FROM `emp`;
SELECT CONCAT(`name`,`pwd`) FROM `emp`;
SELECT INSTR(`pwd`,'2') FROM `emp`;
SELECT UCASE(`name`) FROM `emp`;
SELECT LCASE(`name`) FROM `emp`;
SELECT LEFT(`pwd`,2) FROM `emp`;
SELECT LENGTH(`pwd`) FROM `emp`;
SELECT STRCMP('b' ,'b' ) FROM `emp`;
SELECT SUBSTRING(`pwd`,2,2) FROM `emp`;
SELECT LTRIM(`name`) FROM `emp`;
SELECT RTRIM(LTRIM(`name`)) FROM `emp`;

数字处理函数

以下是 mysql 常用的数字处理函数

ABS(number2 )	                            绝对值
CEILING (number2 )	                    向上取整
FLOOR (number2 )	                    向下取整
FORMAT (number,小数位数)	            保留小数位数
HEX (DecimalNumber )	                    转十六进制
LEAST (number , number2  [,..])	            求最小值
MOD (numerator ,denominator )	            求余
RAND([seed])	                            随机数

案例：

SELECT `name`,ABS(`english`) FROM `student`;
SELECT `name`,CEILING(100.00000001) FROM `student`;
SELECT `name`,FLOOR (100.00000001) FROM `student`;
SELECT `name`,`english`,FORMAT (`english`,2) FROM `student`;
SELECT `name`,`english`,HEX  (`english`) FROM `student`;
SELECT `name`,RAND() FROM `student`;
SELECT `name`,CEILING(RAND()*10) FROM `student`;

日期时间处理函数

获取当前的日期（包含具体的时分秒）

SELECT NOW(),LOCALTIME(),SYSDATE()

获取当前的日期（不包含具体的时分秒）

SELECT CURRENT_DATE(),CURDATE()

日期与时间戳之间的转化

SELECT UNIX_TIMESTAMP(NOW()),FROM_UNIXTIME(1629882598)

获取当前的时间（时分秒）

SELECT CURRENT_TIME(),CURTIME()

获取月份

MONTH(date)返回月份的数值； MONTHNAME(date)返回月份的英文名称

SELECT MONTH(NOW()),MONTHNAME(NOW())

获取天的函数

DAYOFMONTH(date)返回日期属于当前月的第几天； DAYOFYEAR(date)返回日期属于当前年的第几天

SELECT DAYOFMONTH(NOW()),DAYOFYEAR(NOW())

获取星期的函数

DAYNAME(date)返回星期几的英文名字；DAYOFWEEK(date)返回星期几的数值，返回值的范围是1-7，1表示星期日，以此类推； WEEKDAY(date)返回星期几的数值，返回值的范围是0-6，0表示星期一，以此类推

SELECT DAYNAME(NOW()),DAYOFWEEK(NOW()),WEEKDAY(NOW())

两个日期间的间隔天数

TO_DAYS(date)日期date与默认日期（000年1月1日）之间间隔的天数；FROM_DAYS(number)默认日期经过 number 天后的日期；DATEDIFF(date1，date2)获取指定两个日期间的相隔天数，date1-date2

SELECT TO_DAYS(NOW()),FROM_DAYS(738166),DATEDIFF(NOW(),'2020-01-01')

日期的加减法

DATE_ADD(date,INTERVAL expr type)，在 date 的基础上添加上指定的时间间隔后的日期。

DATE_SUB(date,INTERVAL expr type)在 date 的基础上减去上指定的时间间隔后的日期。

type 值常用的有：year、month、day、hour、minute、second、microsecond（毫秒）、week、quarter

SELECT DATE_ADD(NOW(),INTERVAL 1 DAY),DATE_SUB(NOW(),INTERVAL 1 DAY)

练习：

1.导入下面的数据并完成相应的查询

CREATE TABLE `employee` (
  `ID` int(4) NOT NULL,
  `NAME` varchar(10) NOT NULL,
  `JOB` varchar(9) DEFAULT NULL,
  `MANAGER` int(4) DEFAULT NULL,
  `HIRE_DATE` date DEFAULT NULL,
  `SALARY` double(7,2) DEFAULT NULL,
  `PRIZE_MONEY` double(7,2) DEFAULT NULL,
  `DEPT` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7369,'smith','clerk',7902,'1980-12-17',800.00,NULL,'research');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7499,'allen','salesman',7698,'1981-02-20',1600.00,300.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7521,'ward','salesman',7698,'1981-02-22',1250.00,500.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7566,'jones','manager',7839,'1981-04-02',2975.00,NULL,'research');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7654,'martin','salesman',7698,'1981-09-28',1250.00,1400.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7698,'blake','manager',7839,'1981-05-01',2850.00,NULL,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7782,'clark','manager',7839,'1981-06-09',2450.00,NULL,'accounting');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7839,'king','president',NULL,'1981-11-17',5000.00,NULL,'accounting');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7844,'turner','salesman',7698,'1981-09-08',1500.00,0.00,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7900,'james','clerk',7698,'1981-12-03',950.00,NULL,'sales');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7902,'ford','analyst',7566,'1981-12-03',3000.00,NULL,'research');
insert  into `employee`(`ID`,`NAME`,`JOB`,`MANAGER`,`HIRE_DATE`,`SALARY`,`PRIZE_MONEY`,`DEPT`) values (7934,'miller','clerk',7782,'1982-01-23',1300.00,NULL,'accounting');

题目：

1.查询没有上级领导的员工的编号，姓名，工资
2.查询表中有奖金的员工的姓名，职位，工资，以及奖金
3.查询含有上级领导的员工的姓名，工资以及上级领导的编号
4.查询表中名字以‘S’开头的所有员工的姓名
5.查询表中名字的最后一个字符是'S'的员工的姓名
6.查询倒数的第2个字符是‘E’的员工的姓名
7.查询表中员工的名字中包含‘A’的员工的姓名
8.查询表中名字不是以'K'开头的员工的所有信息
9.查询表中名字中不包含‘A’的所有员工的信息
10.做文员的员工人数（job 中 含有 CLERK 的）
11.销售人员 job: SALESMAN 的最高薪水的员工信息
12.查询表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
13.查询表中员工在 ACCOUNTING 部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门
14.查询表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
15.查询表中ACCOUNTING部门或者RESEARCH部门中员工的编号，姓名，所属部门
16.查询表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
17.查询工资高于3000或者部门是SALES的员工的姓名，职位，工资，入职时间以及所属部门
18.查询不是SALES部门的员工的所有信息
19.查询奖金不为空的员工的所有信息
20.查询表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
21.查询表中部门是ACCOUNTING或者RESEARCH中，所有员工姓名，职务，工资，根据工资进行升序排列
22.查询表中所有的数据，然后根据部门进行升序排列，如果部门一致，根据员工的编号进行降序排列
23.查询表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门，上级领导的编号，根据部门进行降序排列，如果部门一致根据工资进行升序排列。
24.查询表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
25.统计表中员工的总数量
26.查出表中员工的最高工资的员工
27.查出表中员工编号的最大的员工
28.查询表中员工的最低工资的员工
29.查询表中在周一入职的员工
30.查询表中在每月初(1号)入职的员工

参考答案：

1.查询没有上级领导的员工的编号，姓名，工资.
SELECT ID,NAME,SALARY FROM employeeloyee WHERE MANAGER IS NULL;

2.查询employee表中有奖金的员工的姓名，职位，工资，以及奖金
SELECT NAME,job,SALARY,PRIZE_MONEY FROM `employee` WHERE PRIZE_MONEY IS NOT NULL

3.查询含有上级领导的员工的姓名，工资以及上级领导的编号
SELECT NAME,SALARY,MANAGER FROM `employee` WHERE MANAGER IS NOT NULL

4.查询employee表中名字以‘S’开头的所有员工的姓名
SELECT NAME FROM `employee` WHERE NAME LIKE 'S%'

5.查询employee表中名字的最后一个字符是'S'的员工的姓名
SELECT NAME FROM `employee` WHERE NAME LIKE '%S'


6.查询倒数的第2个字符是‘E’的员工的姓名
SELECT NAME FROM `employee` WHERE NAME LIKE '%E_'


7.查询employee表中员工的名字中包含‘A’的员工的姓名
SELECT NAME FROM `employee` WHERE NAME LIKE '%A%'

8.查询employee表中名字不是以'K'开头的员工的所有信息
SELECT NAME FROM `employee` WHERE NAME NOT LIKE 'K%'


9.查询employee表中名字中不包含‘A’的所有员工的信息
SELECT * FROM `employee` WHERE NAME NOT LIKE '%A%'


10.做文员的员工人数（job 中 含有 CLERK 的）
SELECT COUNT(0) FROM `employee` WHERE job='clerk'

11.销售人员 job: SALESMAN 的最高薪水的员工信息
SELECT * FROM `employee` WHERE job='SALESMAN' ORDER BY SALARY DESC LIMIT 1


12.查询表中员工的编号，姓名，职位，工资，并且工资在1000~2000之间。
SELECT id,NAME,job,SALARY FROM `employee` WHERE SALARY BETWEEN 1000 AND 2000
SELECT id,NAME,job,SALARY FROM `employee` WHERE SALARY >= 1000 AND SALARY<= 2000

13.查询表中员工在 ACCOUNTING 部门，并且含有上级领导的员工的姓名，职位，上级领导编号以及所属部门
SELECT NAME,job,MANAGER,dept FROM `employee` WHERE dept ='ACCOUNTING' AND MANAGER IS NOT NULL

14.查询表中名字中包含'E'，并且职位不是MANAGER的员工的编号，姓名，职位，以及工资。
SELECT id,NAME,job,SALARY FROM `employee` WHERE NAME LIKE '%E%' AND job != 'MANAGER'

15.查询表中ACCOUNTING部门或者RESEARCH部门中员工的编号，姓名，所属部门
SELECT id,NAME,dept FROM `employee` WHERE dept='ACCOUNTING' OR dept ='RESEARCH'


16.查询表中没有奖金或者名字的倒数第2个字母不是T的员工的编号，姓名，职位以及奖金
SELECT id,NAME,PRIZE_MONEY FROM `employee` WHERE PRIZE_MONEY IS NULL OR NAME NOT LIKE '%T_'

17.查询工资高于3000或者部门是SALES的员工的姓名，职位，工资，入职时间以及所属部门
SELECT NAME,JOB,SALARY,HIRE_DATE dept FROM `employee` WHERE SALARY >3000 OR dept ='SALES'


18.查询不是SALES部门的员工的所有信息
SELECT * FROM `employee` WHERE dept !='SALES'


19.查询奖金不为空的员工的所有信息
SELECT * FROM `employee` WHERE PRIZE_MONEY IS NOT NULL

20.查询表中所有员工的编号，姓名，职位，根据员工的编号进行降序排列
SELECT id,NAME,job FROM `employee` ORDER BY id DESC


21.查询表中部门是ACCOUNTING或者RESEARCH中，所有员工姓名，职务，工资，根据工资进行升序排列
SELECT NAME,JOB,SALARY FROM `employee` WHERE dept ='ACCOUNTING' OR dept ='RESEARCH' ORDER BY SALARY ASC


22.查询表中所有的数据，然后根据部门进行升序排列，如果部门一致，根据员工的编号进行降序排列
SELECT * FROM `employee` ORDER BY dept ASC,id DESC 

23.查询表中工资高于1000或者没有上级领导的员工的编号，姓名，工资，所属部门，上级领导的编号，
根据部门进行降序排列，如果部门一致根据工资进行升序排列。
SELECT id,NAME,SALARY,DEPT,MANAGER FROM `employee` WHERE SALARY>1000 OR MANAGER IS NULL ORDER BY dept DESC,SALARY ASC


24.查询表中名字中不包含S的员工的编号，姓名，工资，奖金，根据工资进行升序排列，如果工资一致，根据编号进行降序排列
SELECT id,NAME,SALARY,PRIZE_MONEY FROM `employee` WHERE NAME NOT LIKE '%S%' ORDER BY SALARY ASC,id DESC

25.统计表中员工的总数量
SELECT COUNT(0) FROM `employee`

26.查出表中员工的最高工资的员工
SELECT * FROM `employee` ORDER BY SALARY DESC LIMIT 1

27.查出表中员工编号的最大的员工
SELECT * FROM `employee` ORDER BY id DESC LIMIT 1

28.查询表中员工的最低工资的员工。
SELECT * FROM `employee` ORDER BY SALARY ASC LIMIT 1

29.查询表中在周一入职的员工。
select * from employee where WEEKDAY(HIRE_DATE) = 0

30.查询表中在每月初(1号)入职的员工。
select * from employee where DAYOFMONTH(HIRE_DATE) = 1

14.索引

MySQL 索引的建立对于 MySQL 的高效运行是很重要的，索引可以大大提高 MySQL 的检索速度。  拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。

组合索引，即一个索引包含多个列。 创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。  

注意：如果过多的使用索引将会降低更新表的速度，如对表进行 INSERT、UPDATE 和 DELETE。因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件。 建立索引会占用磁盘空间的索引文件。

索引在 mysql 中分为普通索引，唯一索引和全文索引

1.普通索引

CREATE INDEX indexName ON `users` (username(length))

如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度；如果是 BLOB 和 TEXT 类型，必须指定 length。

2.唯一索引：它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。

CREATE UNIQUE INDEX indexName ON mytable(username(length))

3.全文索引：针对大文本 TEXT 的内容查找

CREATE FULLTEXT INDEX indexName ON `users` (username)

由于全文索引需要对该字段进行分词，对中文的支持不是很好，所以全文索引在实际开发中不建议使用。在一些大文本的内容管理系统中一般使用 elasticsearch [ɪˈlæstɪk] [sɜːtʃ] 完成全文检索。

使用全文索引的格式：  MATCH (columnName) AGAINST ('string')
eg:
SELECT * FROM `student` WHERE MATCH(`name`) AGAINST('聪')

删除索引：

ALTER TABLE `users` DROP INDEX `indexName`

创建测试表并建立了复合（联合）索引（a，b，c）

create table test(
    a int ,
    b int,
    c int,
    d int,
    key index_abc(a,b,c)
)engine=InnoDB default charset=utf8;

插入 10000 条数据

DROP PROCEDURE IF EXISTS proc_initData;
DELIMITER $
CREATE PROCEDURE proc_initData()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=10000 DO
    INSERT INTO test(a,b,c,d) VALUES(i,i,i,i);
    SET i = i+1;
END WHILE;
END $
CALL proc_initData();

explain 指令查看下面SQL

explain select * from test where a<10 ;
explain select * from test where a<10 and b <10;
explain select * from test where a<10 and b <10 and c<10;

将 a，b出现顺序换一下，a，b，c 出现顺序换一下

explain select * from test where b<10 and a <10;
explain select * from test where b<10 and a <10 and c<10;

查了下资料发现：mysql 查询优化器会判断纠正这条sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。所以，当然是我们能尽量的利用到索引时的查询顺序效率最高咯，所以 mysql 查询优化器会最终以这种顺序进行查询执行。

mysql> explain select * from test where b<10 and a <10;
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | index_abc     | index_abc | 5       | NULL |    9 |    33.33 | Using index condition |
+----+-------------+-------+------------+-------+---------------+-----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

为什么 b<10 and c <10，没有用到索引？而 a<10 and c <10用到了？

mysql> explain select * from test where b<10 and c <10;
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | test  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 10005 |    11.11 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

当 b+ 树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+ 数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+ 树会优先比较 name 来确定下一步的所搜方向，如果 name 相同再依次比较 age 和 sex，最后得到检索的数据；但当(20,F)这样的没有 name 的数据来的时候，b+ 树就不知道下一步该查哪个节点，因为建立搜索树的时候 name 就是第一个比较因子，必须要先根据 name 来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+ 树可以用 name 来指定搜索方向，但下一个字段 age 的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是 F 的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

练习：

1.创建表 person （id int 自增主键, name varchar(10), sex char(2), age int），并为该表创建一张日志表 person_log （id int 自增主键，method varchar(6)方式，log_date datetime 时间日期）用于记录该表的操作，同时创建触发器完成相关增删改的监控日志。

2.向 person 表中插入数据（1，张三，男，20），（2，李四，男，30），（3，王五，男，25），（4，赵六，男，22），（5，王七，男，22），（6，朱八，男，-22）并查看 person_log 的数据。

3.创建一个 v_person 的视图，原表为 person。v_person 上不包含 age 字段。

4.创建一个存储过程 get_count（'王',@person_count）传入用户的姓，返回该姓的用户数量。

5.查询 person 表，将 person 的 id 和 age 拼接在一起取别名为 code。

6.查询 person 表全部信息，并对 age 取绝对值。

7.索引之所以能够极大加快查询速度，原因是数据库底层采用树形结构存储，而且在随着 MySQL 版本的升级树的类型也在发生变化，搜索相应博客了解在 MySQL 中使用的树型数据都有哪些优点以及各版本的优势。

参考答案：

CREATE TABLE `mydb`.`person` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR (10),
  `sex` CHAR(2),
  `age` INT,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TABLE `mydb`.`person_log` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `method` VARCHAR (6) NOT NULL,
  `log_date` DATETIME,
  PRIMARY KEY (`id`)
) ENGINE = INNODB CHARSET = utf8mb4 ;

CREATE TRIGGER `person_add` BEFORE INSERT 
ON `person` FOR EACH ROW 
INSERT INTO `person_log` (`method`, `log_date`) 
VALUES
  ('添加', NOW()) ;

CREATE TRIGGER `person_update` BEFORE UPDATE 
ON `person` FOR EACH ROW 
INSERT INTO `person_log` (`method`, `log_date`) 
VALUES
  ('修改', NOW()) ;

CREATE TRIGGER `person_delete` BEFORE DELETE 
ON `person` FOR EACH ROW 
INSERT INTO `person_log` (`method`, `log_date`) 
VALUES
  ('删除', NOW()) ;


CREATE VIEW v_person AS
SELECT `id`,`name`,`sex` FROM `person`;


SELECT * FROM v_person;



CREATE  PROCEDURE `get_count`(IN first_name VARCHAR(5),OUT person_count INT)
BEGIN
   SELECT COUNT(*) FROM `person` WHERE `name` LIKE CONCAT(first_name,'%') INTO person_count;
    END


DELIMITER ;
SET @user_count = 0;
CALL `get_count`('王', @user_count);
SELECT @user_count;


SELECT CONCAT(id,age) `code`,`name`,sex FROM `person`;

SELECT id,NAME,sex,ABS(age) age FROM `person`;

 15.连接查询

数据准备：下载

CREATE TABLE `client` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `tel` varchar(20) NOT NULL,
  `emp_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `emp_id_fk` (`emp_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (2,'王二','123',2);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (4,'麻子','234',2);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (5,'小明','345',1);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (6,'小红','456',1);
insert  into `client`(`id`,`name`,`tel`,`emp_id`) values (7,'不知道找谁','789',NULL);

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `pwd` varchar(50) NOT NULL,
  `birth` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (1,'张三','123456','2020-06-04');
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (2,'李四','13456','2020-06-04');
insert  into `emp`(`id`,`name`,`pwd`,`birth`) values (3,'晓峰','123','2020-06-05');

两表连接分为内连接和外连接：

内连接（inner join [ˈɪnə(r)] [dʒɔɪn]）：返回连接表中符合连接条件及查询条件的数据行。 

外连接 outer [ˈaʊtə(r)]： 分为

• 左外连接（left outer join）可以简写 left join
• 右外连接（right outer join）可以简写为 right join

与内连接不同的是，外连接不仅返回连接表中符合连接条件及查询条件的数据行，也返回左表（左外连接时）或右表（右外连接时）中仅符合查询条件但不符合连接条件的数据行。 

from 表1 连接类型 表2 [on (连接条件)] [where (查询条件)]

内连接查询 （重点）

SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e ,`client` c WHERE e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e INNER JOIN `client` c ON e.`id`=c.`emp_id`;
SELECT e.id '员工编号',e.`name` '员工名字',c.`name` '客户名',c.`tel` '客户电话' FROM `emp` e LEFT OUTER JOIN `client` c ON e.`id`=c.`emp_id`;

左外连接查询

使用 left outer join 关键字，在 on 子句中设定连接条件不仅包含符合连接条件的数据行，还包含左表全部数据，右表没连上的不显示。左连接时左表优先全部显示。

16.子查询与联合查询

子查询也叫嵌套查询，是指在 select 子句或者 where 子句中又嵌入 select 查询语句。

SELECT * FROM `emp` WHERE id IN(SELECT emp_id FROM `client` WHERE id IN(2,4,5))

联合查询

合并两条查询语句的查询结果，去掉其中的重复数据行，然后返回没有重复数据行的查询结果。联合查询使用 union 关键字 

SELECT * FROM `client` WHERE emp_id = 2 UNION SELECT * FROM `client` WHERE emp_id = 1;
SELECT `id`,`name` FROM `client` WHERE emp_id = 2 UNION SELECT `id`,`name` FROM `emp`;

注意：两条查询语句返回的结构和字段的数量必须相同，否则无法合并。

17.报表函数

报表函数也叫聚合函数，一般用于统计汇总数据库表信息。通常和分组函数配合使用，如果不使用分组函数则认为该表的全部数据为一个组。常用报表函数如下：

1.计数函数－COUNT，如果参数是字段名，则统计该字段不为空的记录数。

Select count(*)|count(列名) from tablename [WHERE where_definition]

案例：

SELECT COUNT(*) FROM `client`

2.求和函数－SUM 

Select sum(列名){,sum(列名)…} from tablename [WHERE where_definition]

案例：

SELECT SUM(`chinese`),SUM(`english`),SUM(`math`) FROM `student` WHERE `name` LIKE '%李%'

3.平均函数－AVG 

Select avg(列名){,avg(列名)…} from tablename [WHERE where_definition]

案例：

SELECT AVG(`chinese`),AVG(`english`),AVG(`math`) FROM `student` WHERE `name` LIKE '%李%'

4.边角函数－MAX/MIN 

Select max(列名)　from tablename [WHERE where_definition]

案例：

SELECT MAX(`english`) FROM `client`;
SELECT MIN(`english`) FROM `client`;

18.分组查询

分组查询是按一个或多个字段进行分组，字段值相同的为一组进行分组统计，其语法格式为：

[select …] from … [where…] [ group by … [having… ]] [ order by … ][limit n]

其中 group by 子句指定按照哪些字段分组。having 子句设定分组查询条件。

数据准备：

CREATE TABLE `staff` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `dept` varchar(10) DEFAULT NULL,
  `salary` double DEFAULT NULL,
  `edlevel` int(11) DEFAULT NULL,
  `hiredate` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4;
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (1,'张三','开发部',2000,3,'2020-02-10 15:19:59');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (2,'李四','开发部',2500,3,'2019-07-16 15:20:01');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (3,'王五','设计部',2600,5,'2019-09-25 15:20:03');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (4,'王六','设计部',2300,4,'2020-06-05 15:20:05');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (5,'马七','设计部',2100,4,'2019-11-11 15:20:07');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (6,'赵八','销售部',3000,5,'2020-06-05 15:20:19');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (7,'钱九','销售部',3000,7,'2020-06-05 15:20:21');
insert  into `staff`(`id`,`name`,`dept`,`salary`,`edlevel`,`hiredate`) values (8,'孙十','销售部',3000,7,'2020-06-05 15:20:26');

案例：

1.列出每个部门最高薪水的结果
SELECT `dept`, MAX(`salary`) AS '最高薪资' FROM `staff` GROUP BY `dept`;

2.查询每个部门的总的薪水数
SELECT `dept`, SUM( `salary` ) AS '总薪水' FROM `staff` GROUP BY `dept`;

3.查询公司 2020 年入职的各个部门每个级别里的最高薪水
SELECT `dept`, `edlevel`, MAX( `salary` ) AS maximum FROM staff WHERE `hiredate` > '2020-01-01' GROUP BY `dept`, `edlevel`;

4.查询雇员数超过2个的部门的最高和最低薪水并按照部门升序查询
SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING COUNT(*) > 2 ORDER BY DEPT

5.查询雇员平均工资大于或等于 2300 的部门的最高和最低薪水并按照最高薪水排序
SELECT `dept`, MAX( `salary` ) AS maximum, MIN( `salary` ) AS minimum FROM `staff` GROUP BY `dept` HAVING AVG( `salary` ) >= 2300 ORDER BY MAX(`salary`)

分组连接函数：在 MySQL 中将分组相同的行数据合并起来使用 group_concat 函数，该函数语法如下：

group_concat([DISTINCT] 要连接的字段 [Order BY ASC/DESC 排序字段] [Separator '分隔符'])

案例：以 id 分组，把 name 字段的值打印在一行，逗号分隔(默认)

select id,group_concat(name) from aa group by id;

案例：以id分组，把name字段的值打印在一行，分号分隔

select id,group_concat(name separator ';') from aa group by id;

案例：以id分组，把去冗余的name字段的值打印在一行，并用逗号分隔

select id,group_concat(distinct name) from aa group by id;

案例：以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序

select id,group_concat(name order by name desc) from aa group by id;

练习：

1.数据准备

CREATE TABLE `class` (
    `classid` int(11) NOT NULL AUTO_INCREMENT,
    `classname` varchar(20) DEFAULT NULL,
    PRIMARY KEY (`classid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4;
insert  into `class`(`classid`,`classname`) values (1,'G1T01');
insert  into `class`(`classid`,`classname`) values (2,'G1T02');
insert  into `class`(`classid`,`classname`) values (3,'G1T03');
insert  into `class`(`classid`,`classname`) values (4,'G1T04');
insert  into `class`(`classid`,`classname`) values (5,'G1T05');
insert  into `class`(`classid`,`classname`) values (6,'G1T06');
insert  into `class`(`classid`,`classname`) values (7,'G1T07');

CREATE TABLE `computer` (
    `studentid` varchar(20) DEFAULT NULL,
    `score` float DEFAULT NULL,
    KEY `studentid` (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert  into `computer`(`studentid`,`score`) values ('2010001',90);
insert  into `computer`(`studentid`,`score`) values ('2010002',80);
insert  into `computer`(`studentid`,`score`) values ('2010003',70);
insert  into `computer`(`studentid`,`score`) values ('2010004',60);
insert  into `computer`(`studentid`,`score`) values ('2010005',75);
insert  into `computer`(`studentid`,`score`) values ('2010006',85);
insert  into `computer`(`studentid`,`score`) values ('2010007',70);
insert  into `computer`(`studentid`,`score`) values ('2010008',60);
insert  into `computer`(`studentid`,`score`) values ('2010009',75);
insert  into `computer`(`studentid`,`score`) values ('2010010',85);

CREATE TABLE `student` (
    `studentid` varchar(20) NOT NULL,
    `studentname` varchar(20) DEFAULT NULL,
    `studentage` int(11) DEFAULT NULL,
    `studentsex` char(10) DEFAULT NULL,
    `studentaddress` varchar(50) DEFAULT NULL,
    `classid` int(11) DEFAULT NULL,
    PRIMARY KEY (`studentid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010001','Jack',21,'男','湖北襄樊',1);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010002','Scott',22,'男','湖北武汉',2);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010003','Lucy',23,'女','湖北武汉',3);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010004','Alan',19,'女','湖北襄樊',4);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010005','Bill',20,'男','湖北襄樊',5);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010006','Bob',21,'男','湖北宜昌',6);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010007','Colin',22,'女','湖北襄樊',6);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010008','Fred',19,'男','湖北宜昌',5);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010009','Hunk',19,'男','湖北武汉',4);
insert  into `student`(`studentid`,`studentname`,`studentage`,`studentsex`,`studentaddress`,`classid`) values ('2010010','Jim',18,'男','湖北襄樊',3);

练习：

1查询出学生的编号，姓名，计算机成绩
2查询参加过考试的学生信息
3查询出学生的编号、姓名、所在班级名称、计算机成绩
4查询出年龄大于19岁的学生编号、姓名、计算机成绩
5查询出姓名中包含有c的学生编号、姓名、计算机成绩
6查询出计算机成绩大于80分的学生编号、姓名、班级名称
7查询出所有学生的信息和计算机成绩信息
8查询出每个班的学生的计算机成绩的平均分，最高分，最低分
9查询显示出班级的计算机平均分大于75的班级名称、平均分信息，并按照平均分降序显示
10查询出和Jim住在同一个地方的学生的基本信息        
11查询出班级编号大于3的学生基本信息
12查询出班级编号大于3的学生的计算机平均分信息    
13查询出班级编号大于3的男生的学生信息
14查询男、女生的计算机平均成绩、最高分、最低分
15将参加过考试的学生的年龄更改为20
16查询出每个班级的学生的平均分（查询的结果中包含平均分和班级名称）
17删除姓名包含“c”字符的学生计算机成绩
18查询出G1T06班学生的编号、姓名、班级名称、计算机成绩
19查询出年龄在20-25之间的学生的编号、姓名、年龄、计算机成绩
20查询出成绩最高的学生的编号、姓名、计算机成绩、所在班级名称
21查询统计出每个班的平均分、显示平均分超过70分的信息、并按照降序显示信息

参考答案：

# 1查询出学生的编号，姓名，计算机成绩
select s.studentid, studentname, score
from student s
         left join computer c on s.studentid = c.studentid;
# 2查询参加过考试的学生信息
select *
from student s
         left join computer c on s.studentid = c.studentid
where score is not null;
# 3查询出学生的编号、姓名、所在班级名称、计算机成绩
select s.studentid, s.studentname, c2.classname, c.score
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid;
# 4查询出年龄大于19岁的学生编号、姓名、计算机成绩
select s.studentid, studentname, score
from student s
         left join computer c on s.studentid = c.studentid
where s.studentage > 19;
# 5查询出姓名中包含有c的学生编号、姓名、计算机成绩
select s.studentid, studentname, score
from student s
         left join computer c on s.studentid = c.studentid
where s.studentname like '%c%';
# 6查询出计算机成绩大于80分的学生编号、姓名、班级名称
select distinct s.studentid, s.studentname, c2.classname
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid
where c.score > 80;
# 7查询出所有学生的信息和计算机成绩信息
select *
from student s
         left join computer c on s.studentid = c.studentid;

# 8查询出每个班的学生的计算机成绩的平均分，最高分，最低分
select c.classname, avg(c2.score), max(c2.score), min(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
group by c.classname;

# 9查询出班级的计算机平均分大于75的班级名称、平均分信息，并按照平均分降序显示
select c.classname, avg(c2.score) a
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
group by c.classname
having a > 75
order by a desc;

# 10查询出和Jim住在同一个地方的学生的基本信息
select *
from student
where studentaddress in (select studentaddress from student where studentname = 'JIM');

# 11查询出班级编号大于3的学生基本信息
select *
from student s
         left join class c on s.classid = c.classid
where c.classid > 3;
# 12查询出班级编号大于3的学生的计算机平均分信息
select avg(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
where c.classid > 3;

# 查询班级id大于3的每个班的学生的计算机平均信息（where 优先）
select c.classid, c.classname, avg(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
where c.classid > 3
group by c.classid, c.classname;
# 查询班级id大于3的每个班的学生的计算机平均信息（having 警告）
select c.classid, c.classname, avg(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
group by c.classid, c.classname
having c.classid > 3;

# 13查询出班级编号大于3的男生的学生信息
select *
from student s
         left join class c on s.classid = c.classid
where c.classid > 3
  and s.studentsex = '男';
# 14查询男、女生的计算机平均成绩、最高分、最低分
select s.studentsex, avg(c.score), max(c.score), min(c.score)
from student s
         left join computer c on s.studentid = c.studentid
group by s.studentsex;
# 15将参加过考试的学生的年龄更改为20
update student
set studentage=20
where studentid in (select distinct studentid from computer);
# 16查询出每个班级的学生的平均分（查询的结果中包含平均分和班级名称）
select c.classid, c.classname, avg(c2.score)
from student s
         left join class c on s.classid = c.classid
         left join computer c2 on s.studentid = c2.studentid
group by c.classname, c.classid;
# 17删除姓名包含“c”字符的学生计算机成绩
delete
from computer
where studentid in (select studentid from student where studentname like '%c%');
# 18查询出G1T06班学生的编号、姓名、班级名称、计算机成绩
select s.studentid, s.studentname, c2.classname, c.score
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid
where c2.classname = 'G1T06';
# 19查询出年龄在20-25之间的学生的编号、姓名、年龄、计算机成绩
select s.studentid, s.studentname, s.studentage, c.score
from student s
         left join computer c on s.studentid = c.studentid
where s.studentage >= 20
  and s.studentage <= 25;
# 20查询出成绩最高的学生的编号、姓名、计算机成绩、所在班级名称
select s.studentid, s.studentname, c2.classname, c.score
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid
order by c.score desc
limit 1;
# 21查询统计出每个班的平均分、显示平均分超过70分的信息、并按照降序显示信息
select c2.classname, avg(c.score)
from student s
         left join computer c on s.studentid = c.studentid
         left join class c2 on s.classid = c2.classid
group by c2.classname
having avg(c.score) > 70
order by avg(c.score) desc

注意：limit 1 是 MySQL 专有语法，并不是 SQL 标准规范，在 Oralce 没有该用法。

19.流程控制函数

MySQL 流程控制函数

函数名称	作用
if	判断，流程控制
ifnull	判断是否为空
case	搜索语句

if:判断

IF(expr,v1,v2) 当 expr 为真是返回 v1 的值，否则返回 v2

mysql> select if(1<2,1,0) c1,if(1>5,'√','×')
c2,if(strcmp('abc','ab'),'yes','no') c3;
+----+----+-----+
| c1 | c2 | c3 |
+----+----+-----+
| 1 | × | yes |
+----+----+-----+
1 row in set (0.00 sec)

ifnull:判断是否为空

IFNULL(v1,v2)：v1为空返回v2，否则返回v1。

mysql> select ifnull(null,'路人甲Java'),ifnull('非空','为空');
+------------------------------+---------------------------+
| ifnull(null,'路人甲Java') | ifnull('非空','为空') |
+------------------------------+---------------------------+
| 路人甲Java | 非空 |
+------------------------------+---------------------------+
1 row in set (0.00 sec)

case:搜索语句,类似于java中的if..else if..else

CASE <表达式>
WHEN <值1> THEN <操作>
WHEN <值2> THEN <操作>
...
ELSE <操作>
END CASE;

方式2：

CASE
WHEN <条件1> THEN <命令>
WHEN <条件2> THEN <命令>
...
ELSE commands
END CASE;

准备数据：

CREATE TABLE t_stu (
id INT AUTO_INCREMENT COMMENT '编号',
name VARCHAR(10) COMMENT '姓名',
sex TINYINT COMMENT '性别,0:未知,1:男,2:女',
PRIMARY KEY (id)
) COMMENT '学生表';
insert into t_stu (name,sex) VALUES
('张学友',1),
('刘德华',1),
('郭富城',1),
('蔡依林',2),
('xxx',0);

查询所有学生信息，输出：姓名，性别（男、女、未知），如下：

mysql> SELECT
t.name 姓名,
(CASE t.sex
WHEN 1
THEN '男'
WHEN 2
THEN '女'
ELSE '未知' END) 性别
FROM t_stu t;
+-----------+--------+
| 姓名 | 性别 |
+-----------+--------+
| 张学友 | 男 |
| 刘德华 | 男 |
| 郭富城 | 男 |
| 蔡依林 | 女 |
| xxx | 未知 |
+-----------+--------+
5 rows in set (0.00 sec)
mysql> SELECT
t.name 姓名,
(CASE
WHEN t.sex = 1
THEN '男'
WHEN t.sex = 2
THEN '女'
ELSE '未知' END) 性别
FROM t_stu t;
+-----------+--------+
| 姓名 | 性别 |
+-----------+--------+
| 张学友 | 男 |
| 刘德华 | 男 |
| 郭富城 | 男 |
| 蔡依林 | 女 |
| xxx | 未知 |
+-----------+--------+
5 rows in set (0.00 sec)