当前位置：Gxlcms > 数据库问题 > MySQL知识总结

MySQL知识总结

时间：2021-07-01 10:21:17 帮助过：10人阅读

MySQL基础知识总结

MySQL基础知识总结
- 1. MySQL使用前提
  - 1.1 安装以及开启服务
    - 1.2.1 windows安装MySQL
  - 1.2 登陆连接
    - 1.2.1 登陆
- 2. MySQL基本使用
  - 0. 一些建议
  - 2.1 MySQL数据库
    - 2.1.1 创建数据库
    - 2.1.2 查看创建数据库的语句
    - 2.1.3 删除数据库
    - 2.1.4 INNODB与MYISAM比较
  - 2.2 MySQL数据表
    - 2.2.1 数据类型介绍
    - 2.2.2 数据库字段属性
    - 2.2.3 数据表的主键与外键
    - 2.2.4 数据表的创建,查看与删除
    - 2.2.5 数据表的修改
  - 2.3 数据操作语言 DML
    - 2.3.1 插入数据
    - 2.3.2 条件判断
    - 2.3.3 更新数据
    - 2.3.4 删除数据
  - 2.4 数据查询语言 DQL
    - 2.4.1 Select 查询语句
    - 2.4.2 多表连接查询
    - 2.4.3 排序与分页
    - 2.4.4 子查询
    - 2.4.5 分组查询
    - 2.4.6 其他
  - 2.5 MySQL函数
    - 2.5.1 常用函数
    - 2.5.2 聚合函数
    - 2.5.3 数据库加密
  - 2.6 事务
    - 2.6.1 事务概念
    - 2.6.2 MySQL事务的处理
    - 2.6.3 事务的隔离级别
  - 2.7 索引
    - 2.7.1 索引介绍
    - 2.7.2 索引的使用
  - 2.8 MySQL的用户、权限管理
    - 2.8.1 MySQL用户登录管理
    - 2.8.2 用户权限管理
  - 2.9 MySQL数据备份与恢复
    - 2.9.1 MySQL数据备份
    - 2.9.2 MySQl数据库恢复
待续。。。

1. MySQL使用前提

1.1 安装以及开启服务

1.2.1 windows安装MySQL

官网下载压缩包,并解压
添加 mysql/bin 至 path

在mysql安装目录下新建my.in:

[client]
# 设置mysql客户端默认字符集
default-character-set=utf8

 
[mysqld]
# 设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=H:/mysql-5.7.29
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
# datadir=Hmysql-8.0.20-winx64data
# 允许最大连接数
max_connections=20
# 设置默认时区
default-time_zone = ‘+8:00‘
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 设置默认时区
default-time_zone = ‘+8:00‘

mysql默认使用latin1编码方式,不支持中文

mysqld --install 进行安装
mysqld --initalize[-insecure] [--user=你的用户名 --console],初始化数据库，记录生成最后的密码
net start mysql 启动mysql服务

菜鸟教程-mysql安装

1.2 登陆连接

1.2.1 登陆

（1）本地登陆

当新安装Mysql时，如果没有去配置，新用户的密码一般为空，即不使用密码登陆，此时可以使用命令登陆：
```
mysql
```

（2）以指定用户登陆

```
mysql -h 主机名 -u 用户名 -p
```
参数：
- -h : 指定客户端所要登录的 MySQL 主机名, 登录本机(localhost 或 127.0.0.1)该参数可以省略;
- -u : 登录的用户名;
- -p : 告诉服务器将会使用一个密码来登录, 如果所要登录的用户名密码为空, 可以忽略此选项，如果能够连接，之后便会让用户输入密码(也可以在-p后面紧跟密码，但是是明文显示，不安全)
示例：
```
mysql -u root -h localhost -p
Enter password: ***********
//mysql命令行。。。。。
```
```
mysql -u root -h localhost -p18724787072  (不安全)
```

（3）修改密码

set password语句：登陆mysql后：
```
set password for 用户名@IP地址 = password(‘新密码‘);  
```
- 注：password函数在8.0之后废除

mysqladmin命令：

mysqladmin -u用户名 -p旧密码 password 新密码

update更新user表（5.7版本后user表中已经没有password选项了，使用authentication_string代替）

更新完后需重启生效

use mysql;

update user set password=passsword(‘新密码‘) where user = ‘用户名‘ and host = ‘IP地址，本地可用localhost‘;

flush privileges;

update mysql.user set authentication_string=password(‘admin‘) where user=‘root‘ and host=‘localhost‘

grant语句

grant all on *.* to ‘root‘@‘localhost‘ IDENTIFIED BY ‘你的密码‘with grant option ;

flush privileges;

在忘记root密码的时候，可以这样

以windows为例：
1. 关闭正在运行的MySQL服务。
2. 打开DOS窗口，转到mysql\bin目录。
3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。
- 在新版本中使用mysqld --console --skip-grant-tables --shared-memory
1. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。
2. 输入mysql回车，如果成功，将出现MySQL提示符 >。
3. 连接权限数据库： use mysql; 。
4. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号）。
5. 刷新权限（必须步骤）：flush privileges;　。
6. 退出 quit。
7. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

2. MySQL基本使用

0. 一些建议

mysql中数据库名，表名，字段名推荐使用反引号“``”来括起来，避免被系统认为是关键字。
使用“--”来注释单行语句，使用“/**/”来注释多行语句。
sql大小写不敏感，为了好看，关键字可以全部使用小写。
delimiter关键字可以指定一条SQL语句的分割符，表示SQL的结束。

示例：
```
delimiter $$; -- 表示以$$作为SQL结束符 例：select * from table$$ 
```
如果在控制台查询时屏幕宽度不够，可以在查询语句后加上\G来作为列输出(后面就不用再加分号了)。

2.1 MySQL数据库

数据库名，表名，字段名使用标准需要带“``”，反引号，tab键上方字符，如果名字中没有带特殊字符，可以不用。

2.1.1 创建数据库

（1）使用mysqladmin工具创建数据库

mysqladmin -u root -p create 数据库名

（2）使用create database语句（需要特定的权限）

```
create database [if not exists] 数据库名;
```
- if not exists语句作用为先判断数据库不存在后，在进行创建;如果已经存在，则给出一个warning。

2.1.2 查看创建数据库的语句

（1）show语句查看当时建库的SQL语句

show create database 数据库名

实际上在创建目录是在data文件夹下自动创建了相应名称的文件夹用来存放数据库的数据文件，默认有一个db.opt文件存放数据库信息。

示例：

show create database mybatis

+----------+--------------------------------------------------------------------+
| Database | Create Database                                                    |
+----------+--------------------------------------------------------------------+
| mybatis  | CREATE DATABASE \`mybatis\` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+--------------------------------------------------------------------+

2.1.3 删除数据库

（1）使用mysqladmin命令删除数据库

```
mysqladmin -u root -p drop 数据库名
```
- 输入密码后会出现一个提示框，来确认是否真的删除数据库。

（2）使用drop database语句（需要特定的权限）

```
drop database [if not exists] 数据库名
```
- if exists语句作用为先判断数据库存在后，在进行删除操作;如果不存在，则给出一个warning。
- 该语句并不会给出确定机制，需谨慎使用。

2.1.4 INNODB与MYISAM比较

（1）比较

比较

特性	MYISAM	INNODB
事务支持	不支持	支持
外键约束	不支持	支持
数据行锁定	不支持	支持
全文索引	支持	不支持
表空间大小	较小	较大，约为MYISAM的两倍

MYISAM 速度较快，空间占用小

INNODB 支持事务处理，安全性高，支持外键约束，可以进行多表操作

2.2 MySQL数据表

2.2.1 数据类型介绍

（1）数值类型

MySQL支持所有标准SQL数值数据类型。这些类型包括：
- 严格数值数据类型(INTEGER、SMALLINT、DECIMAL和NUMERIC)
  - 关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。
- 近似数值数据类型(FLOAT、REAL和DOUBLE PRECISION)。
BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。
作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT

类型	大小	范围（有符号）	范围（无符号）	用途
TINYINT	1 byte	(-128，127)	(0，255)	小整数值
SMALLINT	2 bytes	(-32 768，32 767)	(0，65 535)	大整数值
MEDIUMINT	3 bytes	(-8 388 608，8 388 607)	(0，16 777 215)	大整数值
INT或INTEGER	4 bytes	(-2 147 483 648，2 147 483 647)	(0，4 294 967 295)	大整数值
BIGINT	8 bytes	(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)	(0，18 446 744 073 709 551 615)	极大整数值
FLOAT	4 bytes	(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)	0，(1.175 494 351 E-38，3.402 823 466 E+38)	单精度浮点数值
DOUBLE	8 bytes	(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)	双精度浮点数值
DECIMAL	对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2	依赖于M和D的值	依赖于M和D的值	小数值

（2）日期和时间类型

表示时间值的日期和时间类型为DATETIME、DATE、TIMESTAMP、TIME和YEAR。
每个时间类型有一个有效值范围和一个"零"值，当指定不合法的MySQL不能表示的值时使用"零"值。

TIMESTAMP类型有专有的自动更新特性，将在后面描述。

类型	大小 ( bytes)	范围	格式	用途
DATE	3	1000-01-01/9999-12-31	YYYY-MM-DD	日期值
TIME	3	‘-838:59:59‘/‘838:59:59‘	HH:MM:SS	时间值或持续时间
YEAR	1	1901/2155	YYYY	年份值
DATETIME	8	1000-01-01 00:00:00/9999-12-31 23:59:59	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	YYYYMMDD HHMMSS	混合日期和时间值，时间戳

（3）字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET

该节描述了这些类型如何工作以及如何在查询中使用这些类型。

类型	大小	用途
CHAR	0-255 bytes	定长字符串
VARCHAR	0-65535 bytes	变长字符串
TINYBLOB	0-255 bytes	不超过 255 个字符的二进制字符串
TINYTEXT	0-255 bytes	短文本字符串
BLOB	0-65 535 bytes	二进制形式的长文本数据
TEXT	0-65 535 bytes	长文本数据
MEDIUMBLOB	0-16 777 215 bytes	二进制形式的中等长度文本数据
MEDIUMTEXT	0-16 777 215 bytes	中等长度文本数据
LONGBLOB	0-4 294 967 295 bytes	二进制形式的极大文本数据
LONGTEXT	0-4 294 967 295 bytes	极大文本数据

注意：char(n) 和 varchar(n) 中括号中 n 代表字符的个数，并不代表字节个数，比如 CHAR(30) 就可以存储 30 个字符。
CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。
BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。
BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。
有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择

（4）NULL

NULL值指的是未知，并不是空置
在mysql中为null的的字段不会走索引，做统计的时候也不会被统计进去，如果想统计进去必须做特定的处理，这样做比较复杂。可以给字段的值设置成0、一个特殊的值或者一个空串代替空值
一般情况下，包含null值的计算的结果依然为null
MySQL中的NULL其实是占用空间的，空值(‘‘)是不占用空间的
判断NULL 用is null或者 is not null，在sql语句中可以用IFNULL(expr1,expr2)函数，如果想判断空字符用=‘‘或者 <>、!=‘‘来进行处理。

2.2.2 数据库字段属性

（1）unsigned 无符号

无符号整数，不能为负数

（2）zerofill 零填充

不足的位数使用0填充
- 例如：int 1 --> 00000000001

（3）auto_increment 自增

初始值为1（默认情况下）。
自动在上一条记录值+1（默认情况下）。
通常用来设计主键~index,必须为整数类型。
可以设置初始值以及增长步长。
删除了某一行数据，之后的数据的记录值不变，不会减一

（4）NULL,Not NULL

表示是否允许不去填写值

（5）default

设置默认的值，如果未指定数据值，则会使用预先设置的默认值。

2.2.3 数据表的主键与外键

（1）主键

主键是能够唯一表识表中某条数据的字段集合，一张数据表只能有一个主键，一个主键可以有多个字段。
- 比如身份证号能够唯一标识一个人的身份，域名+资源地址路径+请求参数能够唯一标识网络的资源

通过创建表时使用primary key (字段名1,字段名2,字段名3,...)来添加主键

create table 表名(
    
    ...
    
	primary key (`字段名1`,`字段名2`,`字段名3`,...),
    -- 或者
    -- constraint primary key (`字段名1`,`字段名2`,`字段名3`,...)`,
    ...
)

创建表后添加主键
```
alter table 表名 add primary key(`字段名1`,`字段名2`,...);
```
- 注意：当表中该字段含有null值或者有重复值时，会报错。
删除表的主键
```
alter table 表名 drop primary key
```

（2）外键

如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的相关联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。

创建外键方法：在创建表时添加外键约束：

create table 表名(
    ...
	key `外键约束名` (`外键字段名`),
	constraint `外键约束名` foreign key (`外键字段名`) references `主表名` (`主表的主键字段名`),
    ...
)

或者创建完表之后添加约束：

alter table 表名 add constraint `外键约束名` foreign key (`外键字段名`) references `主表名`(`主表的主键字段名`);

注：在mysql中只有INNODB引擎支持外键，且创建外键时必须保持外键与主表的主键数据一致
当设定好外键约束时，向从表中添加元素外键字段的值必须为包含在主表主键中的字段值。
添加为外键约束时不能与其他表的约束重名。
外键设置会导致数据表的关系变得复杂，在执行delete与update时会变得非常麻烦，阿里编码规约表示对于实体间的外键关系应该放在应用层去解决。

示例：以grade表的主键-grade_id作为student表的外键-grade_id

create table if not exists grade(
	`grade_id` int(4) not null auto_increment comment "年级序号",
	`name` varchar(10) not null COMMENT "年级名称",
	primary key(`grade_id`)
)engine = innodb default charset = utf8;

create table if not exists student(
     `stu_id` int(8) not null comment ‘学生学号‘,
	 `name` varchar(10) not null comment ‘学生姓名‘,
	 `grade_id` int(4) not null comment ‘年级序号‘,
	 primary key(`stu_id`),
	 key `FK_grade_id` (`grade_id`),
	 constraint `FK_grade_id` foreign key (`grade_id`) references `grade` (`grade_id`)
)engine = innodb default charset = utf8;

2.2.4 数据表的创建,查看与删除

（1）使用SQL语句创建数据表

```
CREATE TABLE `数据表名` (
    `column_name` column_type [属性1 属性2 ...],
    ...
    [primary key (‘字段名‘)]
)[engine = 存储引擎][default charset = 编码方式];
```
- INNODB下创建数据表时，在对应数据库的文件目录下创建了一个*.frm文件（描述表结构文件，字段长度等），以及一个*.idb文件
  - 如果采用独立表存储模式，还会产生 data\数据库名\*.ibd文件（存储数据信息和索引信息）
    
    如果采用共存储模式的，数据信息和索引信息都存储在 data\ibdata1中
    
    如果采用分区存储，还会有一个 data\数据库名\*.par文件（用来存储分区信息）
- MYSIAM下创建数据表时，在对应数据库的文件目录下创建了一个*.frm文件（描述表结构文件，字段长度等），以及一个*.MYD文件（存储数据的文件），*.MYI（索引文件）

注意：

在声明字段时，除了最后一行其他行的末尾要加,，最后一行不能添加。
字符串类型的值必须用‘‘,即单引号，不能使用双引号。

示例：

create table if not exists `user`(
	`id` int(8) not null auto_increment comment ‘编号‘,
	`name` varchar(30) not null default ‘匿名‘ comment ‘用户名‘,
	`password` varchar(30) not null default‘12345678‘ comment ‘密码‘,
	`sex` varchar(4) not null default ‘女‘ comment ‘性别‘,
	`birthday` datetime default NULL comment ‘出生日期‘,
	`address` varchar(100) default NULL comment ‘家庭住址‘,
	primary key(`id`)
)engine = INNODB default charset = utf8

在阿里巴巴规范中建议表的字段包含如下：
- 表必备三字段：id, gmt_create, gmt_modified。
- 说明：其中 id 必为主键，类型为 bigint unsigned、单表时自增、步长为 1。
  - gmt_create, gmt_modified 的类型均为 datetime 类型，前者现在时表示主动式创建，后者过去分词表示被动式更新。
- version 乐观锁
- is_deleted 伪删除（表示删除了该记录，但是实际上数据仍然存在，只是is_delete的标记值被改）

（2）查看数据表的信息

查看建表时的sql语句
```
show create table 数据表名
```
示例：
```
show create table user
```
Table Create Table
user CREATE TABLE user (
id int(8) NOT NULL AUTO_INCREMENT COMMENT ‘编号‘,
name varchar(30) NOT NULL DEFAULT ‘匿名‘ COMMENT ‘用户名‘,
password varchar(30) NOT NULL DEFAULT ‘12345678‘ COMMENT ‘密码‘,
sex varchar(4) NOT NULL DEFAULT ‘女‘ COMMENT ‘性别‘,
birthday datetime DEFAULT NULL COMMENT ‘出生日期‘,
address varchar(100) DEFAULT NULL COMMENT ‘家庭住址‘,
PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

查看数据库的字段信息

show columns from 数据表名
或者
desc 数据表名

desc 为 describe 的缩写

示例：

show columns from user;

+----------+--------------+------+-----+----------+----------------+
| Field    | Type         | Null | Key | Default  | Extra          |
+----------+--------------+------+-----+----------+----------------+
| id       | int(8)       | NO   | PRI | NULL     | auto_increment |
| name     | varchar(30)  | NO   |     | 匿名     |                |
| password | varchar(30)  | NO   |     | 12345678 |                |
| sex      | varchar(4)   | NO   |     | 女       |                |
| birthday | datetime     | YES  |     | NULL     |                |
| address  | varchar(100) | YES  |     | NULL     |                |
+----------+--------------+------+-----+----------+----------------+

（3）使用SQL语句删除数据表

```
drop table [if exists] 数据表名;
```
- 可以使用 if exists 先判断。

2.2.5 数据表的修改

（1）修改名称

alter table 旧表名 rename as 新表名

（2）增加表字段

alter table 表名 add 字段名 类型 [字段属性]

示例：

-- 在user表中添加telnum字段，类型为int(4),not null,默认为0
alter table user add telnum int(4) not null default 0;

（3）修改字段的属性

alter table 表名 change  原字段名 新字段名 类型 [字段属性]

alter table 表名 modify [column] 字段名 类型 [字段属性]

注：change可以修改字段名，实际上相当于废除原来的字段又添加新的字段。原来该表的对应列的数据会转换为修改后的类型，比如字符串类型‘1990-1-1’转换为datetime变为‘1990-1-1 00:00：00（对于无法转换的类型数值会报错）。

示例：

alter table user change telnum telphone_num int(4); 
-- insert into user (name)values(‘张建国‘)
-- select * from user
+----+--------+-----------+-----+----------+---------+--------------+
| id | name   | password  | sex | birthday | address | telphone_num |
+----+--------+-----------+-----+----------+---------+--------------+
|  1 | 匿名   | 12345678  | 女  | NULL     | NULL    |            0 |
|  2 | 王美丽 | wangmeili | 女  | NULL     | NULL    |            0 |
|  3 | 张建国 | 12345678  | 女  | NULL     | NULL    |         NULL |
+----+--------+-----------+-----+----------+---------+--------------+


alter table user modify telphone_num varchar(11) null default ‘000-000-000‘;
-- insert into user (name)values(‘蕉太狼‘)
-- select * from user
+----+--------+-----------+-----+----------+---------+--------------+
| id | name   | password  | sex | birthday | address | telphone_num |
+----+--------+-----------+-----+----------+---------+--------------+
|  1 | 匿名   | 12345678  | 女  | NULL     | NULL    | 0            |
|  2 | 王美丽 | wangmeili | 女  | NULL     | NULL    | 0            |
|  3 | 张建国 | 12345678  | 女  | NULL     | NULL    | NULL         |
|  4 | 蕉太狼 | 12345678  | 女  | NULL     | NULL    | 000-000-000  |
+----+--------+-----------+-----+----------+---------+--------------+

（4）修改字段的默认值

设置字段默认值

alter table 表名 alter 字段名 set default 默认值;

删除字段默认值

alter table 表名 alter 字段名 drop default;

示例L:

-- select * from test;
+------+------+
| col1 | col2 |
+------+------+
|    0 |    0 |
|    1 |    1 |
|    2 |    2 |
| NULL | NULL |
| NULL |    4 |
|    5 | NULL |
|    6 | NULL |
+------+------+
alter table test alter col1 set default set 10;

insert into test (col2) values (123);
-- select * from test;
+------+------+
| col1 | col2 |
+------+------+
|    0 |    0 |
|    1 |    1 |
|    2 |    2 |
| NULL | NULL |
| NULL |    4 |
|    5 | NULL |
|    6 | NULL |
|   10 |  123 |
+------+------+

alter table test alter col1 drop default;

（5）删除表的字段

alter table 表名 drop [column] 字段名

示例：

alter table user drop column address
alter table user drop column telphone_num

desc user;
+----------+-------------+------+-----+----------+----------------+
| Field    | Type        | Null | Key | Default  | Extra          |
+----------+-------------+------+-----+----------+----------------+
| id       | int(8)      | NO   | PRI | NULL     | auto_increment |
| name     | varchar(30) | NO   |     | 匿名     |                |
| password | varchar(30) | NO   |     | 12345678 |                |
| sex      | varchar(4)  | NO   |     | 女       |                |
| birthday | datetime    | YES  |     | NULL     |                |
+----------+-------------+------+-----+----------+----------------+

2.3 数据操作语言 DML

2.3.1 插入数据

（1）使用insert语句插入数据

```
insert into 表名 ( field1, field2,...fieldN )
values ( value1, value2,...valueN);
```
- 如果values中的值包含了所有的字段且与该表的所有字段顺序相对应，可以省略前面的字段组,但是不推荐这样做。

对于自增字段,插入的值可以不是下一个值，插入后自增字段将从该插入值开始增长，且插入后会自动根据自增字段进行排序处理

示例：

insert into user (name ,password ,sex ,birthday)
values  (‘test00‘ ,18 ,‘男‘ ,‘1999-03-25‘),
	(‘test01‘ ,19 ,‘女‘ ,‘1999-04-1‘);
		
insert into user
values(7,‘test03‘ ,18 ,‘男‘ ,‘1999-03-25‘);

insert into user
values(9,‘test03‘ ,18 ,‘男‘ ,‘1999-03-25‘);

insert into user (name ,password ,sex ,birthday)
values(‘test03‘ ,18 ,‘男‘ ,‘1999-03-25‘);

insert into user (id, name ,password ,sex ,birthday)
values(8 , ‘test04‘ ,10 ,‘女‘ ,‘1999-05-25‘);

-- select * from user;
+----+--------+-----------+-----+---------------------+
| id | name   | password  | sex | birthday            |
+----+--------+-----------+-----+---------------------+
|  1 | 匿名    | 12345678  | 女  | NULL                |
|  2 | 王美丽  | wangmeili | 女  | NULL                |
|  3 | 张建国  | 12345678  | 女  | NULL                |
|  4 | 蕉太狼  | 12345678  | 女  | NULL                |
|  5 | test00 | 18        | 男  | 1999-03-25 00:00:00 |
|  6 | test01 | 19        | 女  | 1999-04-01 00:00:00 |
|  7 | test03 | 18        | 男  | 1999-03-25 00:00:00 |
|  8 | test04 | 10        | 女  | 1999-05-25 00:00:00 |
|  9 | test03 | 18        | 男  | 1999-03-25 00:00:00 |
| 10 | test03 | 18        | 男  | 1999-03-25 00:00:00 |
+----+--------+-----------+-----+---------------------+

2.3.2 条件判断

（1）where条件语句

查询、删除、更新语句 [WHERE condition1 [AND [OR]] condition2.....

查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。
你可以在 WHERE 子句中指定任何条件。
你可以使用 AND 或者 OR 指定一个或多个条件。
可以使用（）来确定条件的优先级。
WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。

示例：

update user set name = ‘test_new‘ where (name = ‘test03‘ and id between 5 and 9) or password = ‘10‘;

-- select * from user;
+----+----------+-----------+-----+---------------------+
| id | name     | password  | sex | birthday            |
+----+----------+-----------+-----+---------------------+
|  1 | 匿名     | 12345678  | 女  | NULL                |
|  2 | 王美丽   | wangmeili | 女  | NULL                |
|  3 | 张建国   | 12345678  | 女  | NULL                |
|  4 | 蕉太狼   | 12345678  | 女  | NULL                |
|  5 | niss     | 18        | 男  | 1999-03-25 00:00:00 |
|  6 | test01   | 19        | 女  | 1999-04-01 00:00:00 |
|  7 | test_new | 18        | 男  | 1999-03-25 00:00:00 |
|  8 | test_new | 10        | 女  | 1999-05-25 00:00:00 |
|  9 | test_new | 18        | 男  | 1999-03-25 00:00:00 |
| 10 | test03   | 18        | 男  | 1999-03-25 00:00:00 |
+----+----------+-----------+-----+---------------------+

（2）条件判断操作符

操作符	描述
=	等号，检测两个值是否相等，如果相等返回true
<>, !=	不等于，检测两个值是否相等，如果不相等返回true
>	大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true
<	小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true
>=	大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true
<=	小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true
is [not] null	值(不)为null返回true（Mysql中NULL值不能使用=判断）
between A and B	值在A到B之间返回true
like A	如果值与A匹配则返回true
in (a,b,c,d,...)	如果值为(a,b,c,d,...)中的任意一个，则返回true
regexp	正则表达式，用于进行正则表达式匹配

（3）多个条件连接

条件连接符

连接符	描述
AND 或 &&	逻辑与，同时为真返回true
OR 或 \|\|	逻辑或，同时为假返回fasle
NOT 或！	逻辑非，后面为假返回true

示例：

select 1=2 and 2=2, 1=2 && 2=2, 1=2 or 2=2,1=2 || 2=2, not(1=2 and 2=2) , !(1=2 and 2=2);
+-------------+------------+------------+------------+------------------+----------------+
| 1=2 and 2=2 | 1=2 && 2=2 | 1=2 or 2=2 | 1=2 || 2=2 | not(1=2 and 2=2) | !(1=2 and 2=2) |
+-------------+------------+------------+------------+------------------+----------------+
|           0 |          0 |          1 |          1 |                1 |              1 |
+-------------+------------+------------+------------+------------------+----------------+

（4）like子句

```
查询、删除、更新语句 [where filed like condition1 [and [or]] condition2.....
```
- like会将获取的数据与后面的模式进行匹配（类似正则表达式），如果匹配失败，则过滤掉该条数据
- 可以在 WHERE 子句中使用LIKE子句。
- 可以使用LIKE子句代替等号 =
- LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
- 可以使用 AND 或者 OR 指定一个或多个条件。
- 可以在 DELETE 或 UPDATE 命令中使用 WHERE...LIKE 子句来指定条件

在 where like 的条件查询中，SQL 提供了四种匹配方式。

%：表示任意 0 个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。
_：表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句。
[]：表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。
[^] ：表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。
查询内容包含通配符时,由于通配符的缘故，导致我们查询特殊字符 “%”、“_”、“[” 的语句无法正常实现，而把特殊字符用 “[ ]” 括起便可正常查询

示例：

select * from user where name like ‘test%‘ 

+----+--------+----------+-----+---------------------+
| id | name   | password | sex | birthday            |
+----+--------+----------+-----+---------------------+
|  6 | test01 | 19       | 女  | 1999-04-01 00:00:00 |
|  7 | test03 | 18       | 男  | 1999-03-25 00:00:00 |
|  8 | test04 | 10       | 女  | 1999-05-25 00:00:00 |
|  9 | test03 | 18       | 男  | 1999-03-25 00:00:00 |
| 10 | test03 | 18       | 男  | 1999-03-25 00:00:00 |
+----+--------+----------+-----+---------------------+

select * from user where name like ‘test_3‘ ;
+----+--------+----------+-----+---------------------+
| id | name   | password | sex | birthday            |
+----+--------+----------+-----+---------------------+
|  7 | test03 | 18       | 男  | 1999-03-25 00:00:00 |
|  9 | test03 | 18       | 男  | 1999-03-25 00:00:00 |
| 10 | test03 | 18       | 男  | 1999-03-25 00:00:00 |
+----+--------+----------+-----+---------------------+

（5）refexp表达式

MySQL支持其他正则表达式的匹配， MySQL中使用 REGEXP 操作符来进行正则表达式匹配。

模式	描述
^	匹配输入字符串的开始位置。如果设置了 RegExp 对象的 Multiline 属性，^ 也匹配 ‘\n‘ 或 ‘\r‘ 之后的位置。
$	匹配输入字符串的结束位置。如果设置了RegExp 对象的 Multiline 属性，$ 也匹配 ‘\n‘ 或 ‘\r‘ 之前的位置。
.	匹配除 "\n" 之外的任何单个字符。要匹配包括 ‘\n‘ 在内的任何字符，请使用象 ‘[.\n]‘ 的模式。
[...]	字符集合。匹配所包含的任意一个字符。例如， ‘[abc]‘ 可以匹配 "plain" 中的 ‘a‘。
[^...]	负值字符集合。匹配未包含的任意字符。例如， ‘[^abc]‘ 可以匹配 "plain" 中的‘p‘。
p1\|p2\|p3	匹配 p1 或 p2 或 p3。例如，‘z\|food‘ 能匹配 "z" 或 "food"。‘(z\|f)ood‘ 则匹配 "zood" 或 "food"。
*	匹配前面的子表达式零次或多次。例如，zo* 能匹配 "z" 以及 "zoo"。* 等价于{0,}。
+	匹配前面的子表达式一次或多次。例如，‘zo+‘ 能匹配 "zo" 以及 "zoo"，但不能匹配 "z"。+ 等价于 {1,}。
{n}	n 是一个非负整数。匹配确定的 n 次。例如，‘o{2}‘ 不能匹配 "Bob" 中的 ‘o‘，但是能匹配 "food" 中的两个 o。
{n,m}	m 和 n 均为非负整数，其中n <= m。最少匹配 n 次且最多匹配 m 次。

示例：

-- 选取C语言与Java程序设计的一号与二号课程的完整名。
select subjectname from subject where subjectname regexp ‘(C语言|Java程序设计).*[12]‘;
+--------------------+
| subjectname        |
+--------------------+
| C语言-1            |
| C语言-2            |
| Java程序设计-1     |
| Java程序设计-2     |
+--------------------+

2.3.3 更新数据

（1）update语句

update 表名 set field1=new-value1, field2=new-value2 ,... [where clause]

如果不指定where条件子句，将会对所有的数据进行更新操作

示例：

update user set name = ‘niss‘ where name = ‘test00‘;

+----+--------+-----------+-----+---------------------+
| id | name   | password  | sex | birthday            |
+----+--------+-----------+-----+---------------------+
					...
|  5 | niss   | 18        | 男  | 1999-03-25 00:00:00 |
					...
+----+--------+-----------+-----+---------------------+

2.3.4 删除数据

（1）delete语句

delete from 表名 [where clause]

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。
可以在 WHERE 子句中指定条件
可以在单个表中一次性删除记录
delete不会影响自增，但是：
- 在InnoDB中，如果使用delete之后进行mysql服务重新启动，或者断电重启，自增列计数会归零；在MYISAM中不会。新版本中会修复此现象。

示例：

delete from user where name = ‘test01‘

-- select * from user;
+----+----------+-----------+-----+---------------------+
| id | name     | password  | sex | birthday            |
+----+----------+-----------+-----+---------------------+
|  1 | 匿名     | 12345678  | 女  | NULL                |
|  2 | 王美丽   | wangmeili | 女  | NULL                |
|  3 | 张建国   | 12345678  | 女  | NULL                |
|  4 | 蕉太狼   | 12345678  | 女  | NULL                |
|  5 | niss     | 18        | 男  | 1999-03-25 00:00:00 |
|  7 | test_new | 18        | 男  | 1999-03-25 00:00:00 |
|  8 | test_new | 10        | 女  | 1999-05-25 00:00:00 |
|  9 | test_new | 18        | 男  | 1999-03-25 00:00:00 |
| 10 | test03   | 18        | 男  | 1999-03-25 00:00:00 |
+----+----------+-----------+-----+---------------------+

（2）truncate语句

truncate table 表名

truncate会删除表中所有记录，并且将重新设置高水线和所有的索引
truncate会重新设计自增列，计数器归零。
执行速度要比delete语句快
truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器，不会影响事务

示例：

truncate table user;

insert into user (name,password,sex,birthday) values (‘王美丽‘,‘wangmeili‘,‘女‘,‘1999-03-21-14-26-16‘);
insert into user (name,password,sex,birthday) values (‘niss‘,‘nisspd‘,‘男‘,‘1999-03-19-23-41-17‘);
+----+--------+-----------+-----+---------------------+
| id | name   | password  | sex | birthday            |
+----+--------+-----------+-----+---------------------+
|  1 | 王美丽 | wangmeili | 女  | 1999-03-21 14:26:16 |
|  2 | niss   | nisspd    | 男  | 1999-03-19 23:41:17 |
+----+--------+-----------+-----+---------------------+

2.4 数据查询语言 DQL

2.4.1 Select 查询语句

（1）select语句结构

select语句用于选择指定表中的指定字段且指定条件的数据
```
SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
[left | right | inner join table_name2]  -- 联合查询
[WHERE ...]  -- 指定结果需满足的条件
	[GROUP BY ...]  -- 指定结果按照哪几个字段来分组
	[HAVING]  -- 过滤分组的记录必须满足的次要条件
	[ORDER BY ...]  -- 指定查询记录按一个或多个条件排序
	[LIMIT {[offset,]row_count | row_countOFFSET offset}];
```
- 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。
- 可以在字段后添加as 字段别名 来指定字段的别名。也可以在数据表后加as 表别名 来指定数据表的别名。
  - as可以省略；
  - 字段的别名可以使用“``”或者“‘‘”，也可以不使用引号；
  - 表的别名可以使用“``”，也可以不使用引号；
- SELECT 命令可以读取一条或者多条记录。
- 可以使用星号（*）来代替其他字段，SELECT语句会返回表的所有字段数据。
- 可以使用 WHERE 语句来包含任何条件。
- 可以使用 LIMIT 属性来设定返回的记录数。
- 可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。
- 可以在字段上使用mysql内置函数。

在select关键字后加入distinct关键字来去除重复的元素

去重会比较该条select语句的查询的所有字段，只有全部匹配相同才会认为两条记录是重复的

示例：

-- 选择所有 ALl
select concat(‘地址：‘,address) as 学生地址 from student;
+----------------+
| 学生地址       |
+----------------+
| 地址：山东济南 |
| 地址：山东日照 |
| 地址：山东日照 |
| 地址：山东青岛 |
+----------------+

-- 去除重复 DISTINCT
select distinct concat(‘地址：‘,address) as 学生地址 from student;
+----------------+
| 学生地址       |
+----------------+
| 地址：山东济南 |
| 地址：山东日照 |
| 地址：山东青岛 |
+----------------+

-- 查询地址在 山东日照 且 姓名与地址不重复的学生地址+姓名
select distinct concat(‘地址：‘,address) as 地址 ,concat(‘姓名：‘,studentname) as 姓名 from student where address = ‘山东日照‘;
+----------------+--------------+
| 地址           | 姓名         |
+----------------+--------------+
| 地址：山东日照 | 姓名：王建国 |
| 地址：山东日照 | 姓名：王美丽 |
+----------------+--------------+

使用select选择函数、表达式结果、变（常）量

示例：

-- 查询系统版本号
select version();
+-----------+
| version() |
+-----------+
| 5.7.29    |
+-----------+

-- 计算表达式
select 1*2+(2*(5+5));
+---------------+
| 1*2+(2*(5+5)) |
+---------------+
|            22 |
+---------------+



-- 查询自增长步长默认值
select @@auto_increment_increment;
+----------------------------+
| @@auto_increment_increment |
+----------------------------+
|                          1 |
+----------------------------+

（2）模糊查询

like：模糊匹配

示例：

select studentname from student where studentname like ‘王%‘;
+-------------+
| studentname |
+-------------+
| 王建国      |
| 王美丽      |
+-------------+

in：匹配具体的一个或者多个值，不能使用like语句中的匹配符

示例：

select studentno as 学号 , studentname as 姓名 from student where studentno in (1000,1002,1004);

+------+--------+
| 学号 | 姓名   |
+------+--------+
| 1002 | 憨批   |
| 1004 | 王建国 |
| 1000 | 王美丽 |
+------+--------+

between：匹配在某个范围内的记录

示例：

select studentno as 学号 , studentname as 姓名 from student where studentno between 1001 and 1005;
+------+--------+
| 学号 | 姓名   |
+------+--------+
| 1001 | 张无忌 |
| 1002 | 憨批   |
| 1004 | 王建国 |
+------+--------+

is [not] null：匹配（不）为null的记录

注：null值不能使用=来匹配

示例：

select studentname as 姓名, borndate as 出生日期  from student where borndate is null;
+--------+----------+
| 姓名   | 出生日期 |
+--------+----------+
| 李秀莲 | NULL     |
+--------+----------+

select studentname as 姓名, borndate as 出生日期  from student where borndate = null;
Empty set (0.00 sec)

2.4.2 多表连接查询

? 技术图片

? 表A [[left\][right]] join 表B on 连接条件 ----> 返回一个新的组合表

（1）inner join连接

inner join会返回符合条件的记录组合集，可以省略inner

示例：

假设有三张表：

select studentno as 学号, studentname as 学生姓名 ,gradeid as 年级 from student;
+------+----------+------+
| 学号 | 学生姓名 | 年级 |
+------+----------+------+
| 1002 | 憨批     |    3 |
| 1004 | 王建国   |    4 |
| 1000 | 王美丽   |    1 |
| 1001 | 张无忌   |    2 |
| 1006 | 王大卫   |    4 |
| 1007 | 李自成   |    3 |
| 1008 | 李秀莲   |    4 |
+------+----------+------+

+----------+------+

select subjectname as 科目名称, gradeid as 年级  from subject;
+----------------+------+
| 科目名称       | 年级 |
+----------------+------+
| 高等数学-1     |    1 |
| 高等数学-2     |    2 |
| 高等数学-3     |    3 |
| 高等数学-4     |    4 |
| C语言-1        |    1 |
| C语言-2        |    2 |
| C语言-3        |    3 |
| C语言-4        |    4 |
| Java程序设计-1 |    1 |
| Java程序设计-2 |    2 |
| Java程序设计-3 |    3 |
| Java程序设计-4 |    4 |
| 数据库结构-1   |    1 |
| 数据库结构-2   |    2 |
| 数据库结构-3   |    3 |
| 数据库结构-4   |    4 |
| C#基础         |    1 |
| 信心安全概论   |    5 |
+----------------+------+

select studentno as 学号, subjectno as 科目id, studentresult
as  成绩 from result;

select studentno as 学号, subjectno as 科目id, studentresult
as  成绩 from result;

join连接：

-- 查询所有人参加的考试科目
select student.studentname as 学生姓名,subject.subjectname as 科目名称 
from student join subject 
on student.gradeid = subject.gradeid;

+------+--------+------+
| 学号 | 科目id | 成绩 |
+------+--------+------+
| 1001 |      1 |   85 |
| 1002 |      2 |   70 |
| 1004 |      3 |   68 |
| 1006 |      4 |   98 |
| 1007 |      5 |   58 |
| 1001 |      1 |   45 |
| 1002 |      2 |   70 |
| 1004 |      3 |   98 |
| 1006 |      4 |   98 |
| 1007 |      5 |   28 |
| 1001 |      1 |   85 |
| 1002 |      2 |   70 |
| 1004 |      3 |   68 |
| 1006 |      4 |   98 |
| 1007 |      5 |   58 |
| 1001 |      1 |   85 |
| 1002 |      2 |   70 |
| 1004 |      3 |   68 |
| 1006 |      4 |   98 |
| 1007 |      5 |   58 |
| 1001 |      1 |   58 |
| 1002 |      2 |   25 |
| 1004 |      3 |   30 |
| 1006 |      4 |   58 |
| 1007 |      5 |   68 |
| 1001 |      1 |   98 |
| 1002 |      2 |   65 |
| 1004 |      3 |   39 |
| 1006 |      4 |   58 |
| 1007 |      5 |   88 |
| 1006 |      5 |   56 |
+------+--------+------+

（2）left join 左外连接

以左表作为基准,右边表来一一匹配,匹配不上的,返回左表的记录,右表以NULL填充

示例：

select student.studentname as 学生姓名,subject.subjectname as 科目名称 
from student left join subject 
on student.gradeid = subject.gradeid;

+----------+----------------+
| 学生姓名 | 科目名称       |
+----------+----------------+
| 王美丽   | 高等数学-1     |
| 张无忌   | 高等数学-2     |
| 憨批     | 高等数学-3     |
| 李自成   | 高等数学-3     |
| 王建国   | 高等数学-4     |
| 王大卫   | 高等数学-4     |
| 李秀莲   | 高等数学-4     |
| 王美丽   | C语言-1        |
| 张无忌   | C语言-2        |
| 憨批     | C语言-3        |
| 李自成   | C语言-3        |
| 王建国   | C语言-4        |
| 王大卫   | C语言-4        |
| 李秀莲   | C语言-4        |
| 王美丽   | Java程序设计-1 |
| 张无忌   | Java程序设计-2 |
| 憨批     | Java程序设计-3 |
| 李自成   | Java程序设计-3 |
| 王建国   | Java程序设计-4 |
| 王大卫   | Java程序设计-4 |
| 李秀莲   | Java程序设计-4 |
| 王美丽   | 数据库结构-1   |
| 张无忌   | 数据库结构-2   |
| 憨批     | 数据库结构-3   |
| 李自成   | 数据库结构-3   |
| 王建国   | 数据库结构-4   |
| 王大卫   | 数据库结构-4   |
| 李秀莲   | 数据库结构-4   |
| 王美丽   | C#基础         |
+----------+----------------+

左连接以左表为基准，当右表的一条记录与左表的所有记录全部匹配完毕之后，再会去让右表的下一记录去比较左表一一记录,都没匹配到以null作为右表记录添加到结果集中。

当左表中的某一记录都没有匹配到右表的记录，则以null作为右表中的记录。

如上：左连接实际上会从subject表开始：

student表第1条开始匹配：
- 与student表的第1条匹配gradeid字段，不相同，舍弃该记录;相同则添加到结果集中。
- 与student表的第二条开始匹配。。。。
- 。。。
- 与student表最后一条匹配；
subject表第2条开始匹配：
- 。。。
subject表最后一条开始匹配:
- 。。。

（3）right join 右连接

以右表作为基准,左边表来一一匹配,匹配不上的,返回右表的记录,左表以NULL填充

select student.studentname as 学生姓名,subject.subjectname as 科目名称 
from  student right join subject 
+----------+----------------+
| 学生姓名 | 科目名称       |
+----------+----------------+
| 憨批     | 高等数学-3     |
| 憨批     | C语言-3        |
| 憨批     | Java程序设计-3 |
| 憨批     | 数据库结构-3   |
| 王建国   | 高等数学-4     |
| 王建国   | C语言-4        |
| 王建国   | Java程序设计-4 |
| 王建国   | 数据库结构-4   |
| 王美丽   | 高等数学-1     |
| 王美丽   | C语言-1        |
| 王美丽   | Java程序设计-1 |
| 王美丽   | 数据库结构-1   |
| 王美丽   | C#基础         |
| 张无忌   | 高等数学-2     |
| 张无忌   | C语言-2        |
| 张无忌   | Java程序设计-2 |
| 张无忌   | 数据库结构-2   |
| 王大卫   | 高等数学-4     |
| 王大卫   | C语言-4        |
| 王大卫   | Java程序设计-4 |
| 王大卫   | 数据库结构-4   |
| 李自成   | 高等数学-3     |
| 李自成   | C语言-3        |
| 李自成   | Java程序设计-3 |
| 李自成   | 数据库结构-3   |
| 李秀莲   | 高等数学-4     |
| 李秀莲   | C语言-4        |
| 李秀莲   | Java程序设计-4 |
| 李秀莲   | 数据库结构-4   |
| NULL     | 信心安全概论   |
+----------+----------------+

右连接与左连接类似，但是是以右表为基准，当左表的一条记录与右表的所有记录全部匹配完毕之后，再会去让左表的下一记录去比较右表一一记录

当右表中某一条记录都没匹配到左表记录，则以null作为左表记录添加到结果集中。

（4）join 连接后返回的数据表的查询

使用where可以对join后返回的结果集进行筛选出符合条件的记录集