MySQL知识总结

MySQL基础知识总结

目录

1. MySQL使用前提

1.1 安装以及开启服务

1.1.1 docker安装MySQL

（1）使用docker安装

  • 拉取mysql

    docker pull mysql:5.7.30

  • 启动镜像，配置挂载卷：

    docker run -d -p 3306:3306 --name mysql \
-v /docker/mysql/etc/mysql:/etc/mysql \
-v /docker/mysql/data:/var/lib/mysql \
-v /docker/mysql/logs:/logs \
-e MYSQL_ROOT_PASSWORD="你的密码" \
-e MYSQL_USER="root" \
-e MYSQL_PASSWORD="你的密码" \
-d mysql:5.7.30

  • 在本地测试连接：

    image-20200617114738103

1.1.2 windows安装MySQL

  1. 官网下载压缩包,并解压

  2. 添加 mysql/bin 至 path

  3. 在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编码方式,不支持中文

  4. mysqld --install 进行安装

  5. mysqld --initalize[-insecure] [--user=你的用户名 --console],初始化数据库，记录生成最后的密码

  6. 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目录。
  1. 输入mysql回车，如果成功，将出现MySQL提示符 >。
  2. 连接权限数据库： use mysql; 。
  3. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。
  4. 刷新权限（必须步骤）：flush privileges;　。
  5. 退出 quit。
  6. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录。

8.0版本之后废除password修改密码的方式：

具体如下：

[root@localhost ~]# mysql -u root -p '原来的密码'   

mysql> show databases;

mysql> use mysql;

mysql> ALTER USER '用户名'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';

mysql> flush privileges;   --刷新MySQL的系统权限相关表

mysql> exit;

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 [column] 字段名 类型 [字段属性]

    示例：

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

（3）修改字段的属性

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

  • 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 提供了四种匹配方式。

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

    示例：

    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）regexp表达式

  • 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 次。例如，'o{2}' 不能匹配 "Bob" 中的 'o'，但是能匹配 "food" 中的两个 o。
    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后返回的结果集进行筛选出符合条件的记录集合

    示例：

    -- 查询‘憨批’参加的考试科目
select student.studentname as 学生姓名,subject.subjectname as 科目名称 
from  student right join subject 
on student.gradeid = subject.gradeid
where student.studentname = '憨批'

+----------+----------------+
| 学生姓名 | 科目名称       |
+----------+----------------+
| 憨批     | 高等数学-3     |
| 憨批     | C语言-3        |
| 憨批     | Java程序设计-3 |
| 憨批     | 数据库结构-3   |
+----------+----------------+

  • 因为join相当于返回了一个新的组合表，可以对新表在于其他表进行连接查询

    示例：

    select student.studentno as 学号,student.studentname as 姓名, subject.subjectname as 科目名称,result.studentresult as 成绩 
from student right join result 
on student.studentno = result.studentno
join subject 
on result.subjectno = subject.subjectno;

+------+--------+------------+------+
| 学号 | 姓名   | 科目名称   | 成绩 |
+------+--------+------------+------+
| 1001 | 张无忌 | 高等数学-1 |   85 |
| 1002 | 憨批   | 高等数学-2 |   70 |
| 1004 | 王建国 | 高等数学-3 |   68 |
| 1006 | 王大卫 | 高等数学-4 |   98 |
| 1007 | 李自成 | C语言-1    |   58 |
| 1001 | 张无忌 | 高等数学-1 |   45 |
| 1002 | 憨批   | 高等数学-2 |   70 |
| 1004 | 王建国 | 高等数学-3 |   98 |
| 1006 | 王大卫 | 高等数学-4 |   98 |
| 1007 | 李自成 | C语言-1    |   28 |
| 1001 | 张无忌 | 高等数学-1 |   85 |
| 1002 | 憨批   | 高等数学-2 |   70 |
| 1004 | 王建国 | 高等数学-3 |   68 |
| 1006 | 王大卫 | 高等数学-4 |   98 |
| 1007 | 李自成 | C语言-1    |   58 |
| 1001 | 张无忌 | 高等数学-1 |   85 |
| 1002 | 憨批   | 高等数学-2 |   70 |
| 1004 | 王建国 | 高等数学-3 |   68 |
| 1006 | 王大卫 | 高等数学-4 |   98 |
| 1007 | 李自成 | C语言-1    |   58 |
| 1001 | 张无忌 | 高等数学-1 |   58 |
| 1002 | 憨批   | 高等数学-2 |   25 |
| 1004 | 王建国 | 高等数学-3 |   30 |
| 1006 | 王大卫 | 高等数学-4 |   58 |
| 1007 | 李自成 | C语言-1    |   68 |
| 1001 | 张无忌 | 高等数学-1 |   98 |
| 1002 | 憨批   | 高等数学-2 |   65 |
| 1004 | 王建国 | 高等数学-3 |   39 |
| 1006 | 王大卫 | 高等数学-4 |   58 |
| 1007 | 李自成 | C语言-1    |   88 |
| 1006 | 王大卫 | C语言-1    |   56 |
+------+--------+------------+------+

（5）自连接

  • 通过给表取别名，将一张表当做两张表来连接使用

    加入有以下表：pid表示该课程是属于哪一课程的子课程

    select * from category;
+------------+-----+--------------+
| categoryid | pid | categoryname |
+------------+-----+--------------+
|          2 |   1 | 信息技术     |
|          3 |   1 | 软件开发     |
|          4 |   3 | 数据库       |
|          5 |   1 | 美术设计     |
|          6 |   3 | web开发      |
|          7 |   5 | ps技术       |
|          8 |   2 | 办公信息     |
+------------+-----+--------------+

    示例：自连接

    select c1.categoryid as 父类课程序号, c1.categoryname as 父类课程名, c2.categoryid as 子类课程序号, c2.categoryname as 子类课程名 
from category as c2 join category as c1
on c2.pid = c1.categoryid;

+--------------+------------+--------------+------------+
| 父类课程序号   | 父类课程名  | 子类课程序号   | 子类课程名 |
+--------------+------------+--------------+------------+
|            3 | 软件开发   |            4 | 数据库     |
|            3 | 软件开发   |            6 | web开发    |
|            5 | 美术设计   |            7 | ps技术     |
|            2 | 信息技术   |            8 | 办公信息   |
+--------------+------------+--------------+------------+

2.4.3 排序与分页

（1）结果排序

  • order by 字段名关键字排序

    • desc关键字用于倒序，asc关键字用于正序（缺省默认）

    示例：

    select studentresult from result;
+---------------+
| studentresult |
+---------------+
|            85 |
|            70 |
|            68 |
|            98 |
|            58 |
|            45 |
|            70 |
|            98 |
...
|            39 |
|            58 |
|            88 |
|            56 |
+---------------+

-- 倒序排序
select studentresult from result order by studentresult desc;
+---------------+
| studentresult |
+---------------+
|            98 |
|            98 |
|            98 |
|            98 |
|            98 |
|            98 |
|            88 |
|            85 |
|            85 |
|            85 |
|            70 |
|...
|            39 |
|            30 |
|            28 |
|            25 |
+---------------+

（2）分页

  • limit 当前记录，页大小用于选择出之前查询的结果集的从当前记录开始，总共页大小的记录集，即返回结果集的【当前记录，当前记录+页大小】的结果集（包括两个这边界）。

  • limit 页大小用于选择从开始到页大小的记录。

    • 注：当前记录从0开始。
    select studentresult from result order by studentresult desc limit 2,5;
+---------------+
| studentresult |
+---------------+
|            98 |
|            98 |
|            98 |
|            98 |
|            88 |
+---------------+
select studentresult from result order by studentresult desc limit 3,5;
+---------------+
| studentresult |
+---------------+
|            98 |
|            98 |
|            98 |
|            88 |
|            85 |

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

2.4.4 子查询

（1）子查询格式

  • 在查询语句中的WHERE条件子句中,又嵌套了另一个查询语句；

  • 嵌套查询可由多个子查询组成,求解的方式是由里及外;

  • 子查询返回的结果一般都是集合,故而建议使用IN关键字;

    示例：

    -- 查询所有的高等数学的课程成绩,获取前十名
select studentno as `学号`,subjectno as '科目号' , examdate as 考试时间 ,studentresult as 成绩 
from `result`
where subjectno in (	
		select `subjectno` from subject 
		where `subjectname` like '高等数学%'
)
order by studentresult desc
limit 0,10

+------+--------+---------------------+------+
| 学号 | 科目号 | 考试时间            | 成绩 |
+------+--------+---------------------+------+
| 1006 |      4 | 2013-11-13 16:00:00 |   98 |
| 1006 |      4 | 2013-11-13 16:00:00 |   98 |
| 1004 |      3 | 2013-11-11 09:00:00 |   98 |
| 1001 |      1 | 2014-11-14 16:00:00 |   98 |
| 1006 |      4 | 2013-11-13 16:00:00 |   98 |
| 1006 |      4 | 2013-11-13 16:00:00 |   98 |
| 1001 |      1 | 2013-11-11 16:00:00 |   85 |
| 1001 |      1 | 2013-11-11 16:00:00 |   85 |
| 1001 |      1 | 2013-11-11 16:00:00 |   85 |
| 1002 |      2 | 2013-11-12 16:00:00 |   70 |
+------+--------+---------------------+------+

-- 查询所有的成绩大于80的学生的学号，姓名，科目号，成绩
select student.studentno as 学号,student.studentname as 姓名,result.subjectno as 科目号, result.studentresult 
from student left join result on result.studentno = student.studentno
where result.studentresult in (
		select studentresult from result where studentresult > 80
);
+------+--------+--------+---------------+
| 学号 | 姓名   | 科目号 | studentresult |
+------+--------+--------+---------------+
| 1001 | 张无忌 |      1 |            85 |
| 1006 | 王大卫 |      4 |            98 |
| 1004 | 王建国 |      3 |            98 |
| 1006 | 王大卫 |      4 |            98 |
| 1001 | 张无忌 |      1 |            85 |
| 1006 | 王大卫 |      4 |            98 |
| 1001 | 张无忌 |      1 |            85 |
| 1006 | 王大卫 |      4 |            98 |
| 1001 | 张无忌 |      1 |            98 |
| 1007 | 李自成 |      5 |            88 |
+------+--------+--------+---------------+

（2）exits关键字

  • exists关键字用于返回表示前面查找的一条记录是否在子查询结果集中存在的一个布尔值；

    • not exists表示不存在；
    • exists (select null)或者exists (select 'x')等查询变量或常量时均会返回真（只要查到数据就会返回真）。

  • exists执行顺序如下：

    1. 首先执行一次外部查询
    2. 对于外部查询中的每一行分别执行一次子查询，而且每次执行子查询时都会引用外部查询中当前行的值。
    3. 使用子查询的结果来确定外部查询的结果集。

  • in执行顺序如下：

    1. 子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出

  • exists 与 in 比较

    • 两者功能基本相同
    • 通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：
      IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况示例：
  -- 从用户表中选择出余额>10000的用户信息
  mysql> select * from user where exists (select id from account where user.id=account.id and money>10000);
  +----+--------------------+-----+--------------+---------+---------------------+---------------------+-------------------------------------------+
  | id | name               | sex | email        | address | create_time         | update_time         | password                                  |
  +----+--------------------+-----+--------------+---------+---------------------+---------------------+-------------------------------------------+
  |  1 | Bank Administrator |   0 | admin@bank.c | NULL    | 2020-05-23 23:04:18 | 2020-05-23 23:04:18 | *7DBF36D8BCEC34C965C82BA4C545ABC1DB1365C8 |
  +----+--------------------+-----+--------------+---------+---------------------+---------------------+-------------------------------------------+

2.4.5 分组查询

（1）group by指定分组字段

  • group by 语句根据一个或多个列对结果集进行分组，在分组的列上可以使用 COUNT, SUM, AVG,等函数

    示例：

    -- 查看每一科目的平均成绩，最高分，最低分

select subject.subjectno ,subjectname, avg(studentresult) 平均分,max(studentresult) 最高分,min(studentresult) 最低分 
from result inner join subject 
on subject.subjectno = result.subjectno
group by result.subjectno;

+-----------+-------------+---------+--------+--------+
| subjectno | subjectname | 平均分  | 最高分 | 最低分 |
+-----------+-------------+---------+--------+--------+
|         1 | 高等数学-1  | 76.0000 |     98 |     45 |
|         2 | 高等数学-2  | 61.6667 |     70 |     25 |
|         3 | 高等数学-3  | 61.8333 |     98 |     30 |
|         4 | 高等数学-4  | 84.6667 |     98 |     58 |
|         5 | C语言-1     | 59.1429 |     88 |     28 |
+-----------+-------------+---------+--------+--------+

（2）having 根据条件过滤分组

  • having 后加上条件来筛选前面的分组

    • 由于group by 语句只能在where后面只能使用having指定筛选分组的条件。

    例：

    select subject.subjectno ,subjectname, avg(studentresult) 平均分,max(studentresult) 最高分,min(studentresult) 最低分 
from result inner join subject 
on subject.subjectno = result.subjectno
group by result.subjectno
having avg(studentresult>70);

+-----------+-------------+---------+--------+--------+
| subjectno | subjectname | 平均分  | 最高分 | 最低分 |	
+-----------+-------------+---------+--------+--------+
|         1 | 高等数学-1  | 76.0000 |     98 |     45 |
|         4 | 高等数学-4  | 84.6667 |     98 |     58 |
+-----------+-------------+---------+--------+--------+

（3）with rollup 再次分组统计

  • with rollup 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）

    • 必须所有的字段都得经过聚合函数的处理

    示例：

    -- 对求分组平局值的平均值，以及分组最高分，最低分
select avg(studentresult) 平均分,max(studentresult) 最高分,min(studentresult) 最低分 
from result inner join subject 
on subject.subjectno = result.subjectno
group by result.subjectno
with rollup;

+---------+--------+--------+
| 平均分   | 最高分  | 最低分  |
+---------+--------+--------+
| 76.0000 |     98 |     45 |
| 61.6667 |     70 |     25 |
| 61.8333 |     98 |     30 |
| 84.6667 |     98 |     58 |
| 59.1429 |     88 |     28 |
| 68.3548 |     98 |     25 |
+---------+--------+--------+

2.4.6 其他

（1）union操作符

  • union操作符用于连接两个查询语句，将两个查询语句的结果集合并后返回

  • select expression1, expression2, ... expression_n
from 表名
[where conditions]
union [ALL | DISTINCT]
select expression1, expression2, ... expression_n
from 表名
[where conditions];
    • expression1, expression2, ... expression_n: 要检索的列。
    • tables: 要检索的数据表。
    • WHERE conditions: 可选， 检索条件。
    • DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
    • ALL: 可选，返回所有结果集，包含重复数据

    例如：

    select name as '姓名' ,id as '编号' ,class from student where name like '%1' 
union distinct
select name as '姓名' ,id as '编号' ,class from student where name like '%2' limit 15;

+----------+--------+-------+
| 姓名     | 编号   | class |
+----------+--------+-------+
| 姓名11   |     11 |     5 |
| 姓名21   |     21 |     0 |
| 姓名31   |     31 |     4 |
| 姓名41   |     41 |     1 |
| 姓名51   |     51 |     3 |
| 姓名61   |     61 |     3 |
| 姓名71   |     71 |     9 |
| 姓名81   |     81 |     5 |
| 姓名91   |     91 |     5 |
| 姓名2    |      2 |     2 |
| 姓名12   |     12 |     4 |
| 姓名22   |     22 |     1 |
| 姓名32   |     32 |     7 |
| 姓名42   |     42 |     1 |
| 姓名52   |     52 |     7 |
+----------+--------+-------+

2.5 MySQL函数

2.5.1 常用函数

（1）数据函数

  • abs(数字):返回绝对值

    示例：

    select ABS(-9);
+---------+
| ABS(-9) |
+---------+
|       9 |
+---------+

  • ceiling(数字):返回向上取整

    示例：

     SELECT CEILING(9.4),CEILING(-9.4);
+--------------+---------------+
| CEILING(9.4) | CEILING(-9.4) |
+--------------+---------------+
|           10 |            -9 |
+--------------+---------------+

  • floor(数字）：返回向下取整

    示例：

    SELECT Floor(9.4),Floor(-9.4);
+------------+-------------+
| Floor(9.4) | Floor(-9.4) |
+------------+-------------+
|          9 |         -10 |
+------------+-------------+

  • rand()：返回一个0-1之间的随机数

    示例：

    SELECT rand() as r1,rand() as r2;
+--------------------+---------------------+
| r1                 | r2                  |
+--------------------+---------------------+
| 0.7825655320496908 | 0.12337031971977122 |
+--------------------+---------------------+

  • sign(0)：符号函数,负数返回-1,正数返回1,0返回0

    示例：

    SELECT sign(-3),sign(0),sign(6);
+----------+---------+---------+
| sign(-3) | sign(0) | sign(6) |
+----------+---------+---------+
|       -1 |       0 |       1 |
+----------+---------+---------+

  • ifnull(value,returnvalue)：如果该函数第一个参数值为null，会返回第二个参数值，否则直接返回第一个参数值。

     select ifnull(null,'default_value'),ifnull('not null','default_value');
+------------------------------+------------------------------------+
| ifnull(null,'default_value') | ifnull('not null','default_value') |
+------------------------------+------------------------------------+
| default_value                | not null                           |
+------------------------------+------------------------------------+

（2）字符串函数

  • char_length('字符串)：返回字符串包含的字符数

    示例：

    select char_length('i love china.');
+------------------------------+
| char_length('i love china.') |
+------------------------------+
|                           13 |
+------------------------------+

  • concat：返回参数字符拼接后的字符串（参数数量可变）

    示例：

    select concat('i',' ','love',' ','china','.');
+----------------------------------------+
| concat('i',' ','love',' ','china','.') |
+----------------------------------------+
| i love china.                          |
+----------------------------------------+

  • insert('字符串',begin,lenth'替换字符串')：将[ begin, begin+length )范围内的字符串替换为传入的字符串（1作为字符串的起始位）。

    示例：

    select insert('123456789',2,6,'replaced String');
+-------------------------------------------+
| insert('123456789',2,6,'replaced String') |
+-------------------------------------------+
| 1replaced String89                        |
+-------------------------------------------+

  • lower('字符串')：转换为小写。

    示例：

    select lower('I Love CHINA');
+-----------------------+
| lower('I Love CHINA') |
+-----------------------+
| i love china          |
+-----------------------+

  • upper('字符串')：转换为大写。

    示例：

    select upper('I Love CHINA');
+-----------------------+
| upper('I Love CHINA') |
+-----------------------+
| I LOVE CHINA          |
+-----------------------+

  • left('字符串', n)：从字符串的左边开始截取n个字符。

    示例：

    select left('I Love China',6);
+------------------------+
| left('I Love China',6) |
+------------------------+
| I Love                 |
+------------------------+

  • right('字符串'，n)：从字符串的右边开始截取n个字符。

    示例：

    select right('I Love China',6);
+-------------------------+
| right('I Love China',6) |
+-------------------------+
|  China                  |
+-------------------------+

  • replace('字符串'，pattern,replacedStr)：将字符串中的所有的pattern替换为传入的replacedStr。

    示例：

    select replace('I Love China','China','staying at home.');
+----------------------------------------------------+
| replace('I Love China','China','staying at home.') |
+----------------------------------------------------+
| I Love staying at home.                            |
+----------------------------------------------------+

  • substr('字符串'，begin,length)：从begin位置，截取字符串n个字符并返回。

    示例：

    select substr('I Love China',3,4);
+----------------------------+
| substr('I Love China',3,4) |
+----------------------------+
| Love                       |
+----------------------------+

  • reverse('字符串')：返回反转的字符串。

    示例：

    select reverse('I Love China');
+-------------------------+
| reverse('I Love China') |
+-------------------------+
| anihC evoL I            |
+-------------------------+  

select reverse(studentname) as 姓名翻转 from student;
+----------+
| 姓名翻转 |
+----------+
| 批憨     |
| 国建王   |
| 丽美王   |
| 忌无张   |
| 卫大王   |
| 成自李   |
| 莲秀李   |
+----------+

（2）日期和时间函数

  • current_date(),curdate()：获取当前日期。

    示例：

    select current_date(),curdate();
+----------------+------------+
| current_date() | curdate()  |
+----------------+------------+
| 2020-05-11     | 2020-05-11 |
+----------------+------------+

  • now()：获取当前日期和时间。

    示例：

    select now();
+---------------------+
| now()               |
+---------------------+
| 2020-05-11 18:12:07 |
+---------------------+

  • localtime()：获取当地的当前日期时间。

    示例：

    select localtime();
+---------------------+
| localtime()         |
+---------------------+
| 2020-05-11 18:15:22 |
+---------------------+

  • sysdate()：获取系统当前日期和时间。

    示例：

    select sysdate();
+---------------------+
| sysdate()           |
+---------------------+
| 2020-05-11 18:16:50 |
+---------------------+

  • yaer(datetime),month(datetime),day(datetime),hour(datetime),minute(datetime),second(datetime)：返回年，月，日，时，分，秒。

    • 也可以传入一个标准的日期时间格式的字符串。

    示例：

    select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now());
+-------------+--------------+------------+-------------+---------------+---------------+
| year(now()) | month(now()) | day(now()) | hour(now()) | minute(now()) | second(now()) |
+-------------+--------------+------------+-------------+---------------+---------------+
|        2020 |            5 |         11 |          18 |            20 |            42 |
+-------------+--------------+------------+-------------+---------------+---------------+

 select year('2017-3-25-4-2-1') ,month('2017-3-25-4-2-1'),second('2017-3-25-4-2-1');
+-------------------------+--------------------------+---------------------------+
| year('2017-3-25-4-2-1') | month('2017-3-25-4-2-1') | second('2017-3-25-4-2-1') |
+-------------------------+--------------------------+---------------------------+
|                    2017 |                        3 |                        17 |
+-------------------------+--------------------------+---------------------------+

（3）系统信息函数

  • version()：查看数据库系统版本号。

    示例：

    select version();
+-----------+
| version() |
+-----------+
| 5.7.29    |
+-----------+

  • user()：查看当前用户。

    示例：

    select user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+

2.5.2 聚合函数

（1）常用聚合函数

  • 函数名称 描述
    COUNT() 返回满足Select条件的记录总和数，如 select count(*) 【不建议使用 *，效率低】
    SUM() 返回数字字段或表达式列作统计，返回一列的总和。
    AVG() 通常为数值字段或表达列作统计，返回一列的平均值
    MAX() 可以为数值字段，字符字段或表达式列作统计，返回最大的值。
    MIN() 可以为数值字段，字符字段或表达式列作统计，返回最小的值。

    • count()函数参数可以是字段名，也可以*（但是不推荐），也可以设为第几列。

      • count(字段)会忽略所有的null值，也就是不含null的所有的计数。
      • count(*),count(列号)不会忽略null值。

    • count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。count(*)也是统计所有记录的条数，而count(1)会以1代替每一行。

      • 而count(字段)则与前两者不同，它会统计该字段不为null的记录条数
      • 在表没有主键时，count(1)比count(*)效率高
        • 有主键时，主键作为计算条件，count(主键)效率最高
        • 若表格只有一个字段，则count(*)效率较高

      示例

      create table test(
	col1 int null,
	col2 int null
)engine = INNODB,default charset =utf8

insert into test values(0,0);
insert into test values(1,1);
insert into test values(2,2);
insert into test values(null,null);
insert into test values(null,4);
insert into test values(5,null);
insert into test values(6,null);

 select * from test;
+------+------+
| col1 | col2 |
+------+------+
|    0 |    0 |
|    1 |    1 |
|    2 |    2 |
| NULL | NULL |
| NULL |    4 |
|    5 | NULL |
|    6 | NULL |
+------+------+

      count:

      select count(col1), count(col2),count(1),count(*) from test;
+-------------+-------------+----------+----------+
| count(col1) | count(col2) | count(1) | count(*) |
+-------------+-------------+----------+----------+
|           5 |           4 |        7 |        7 |
+-------------+-------------+----------+----------+

      sum():

      select sum(col1),sum(col2) from test;
+-----------+-----------+
| sum(col1) | sum(col2) |
+-----------+-----------+
|        14 |         7 |
+-----------+-----------+

      avg():

      select avg(col1),avg(col2) from test;
+-----------+-----------+
| avg(col1) | avg(col2) |
+-----------+-----------+
|    2.8000 |    1.7500 |
+-----------+-----------+

      max(),min():

      select max(col1),max(col2),min(col1),min(col2) from test;
+-----------+-----------+-----------+-----------+
| max(col1) | max(col2) | min(col1) | min(col2) |
+-----------+-----------+-----------+-----------+
|         6 |         4 |         0 |         0 |
+-----------+-----------+-----------+-----------+

2.5.3 数据库加密

（1）MD5算法

  • MD5(value)函数：该函数会对传入值进行MD5算法处理，返回128位摘要。

    示例：

    select now(), md5(now());
+---------------------+----------------------------------+
| now()               | md5(now())                       |
+---------------------+----------------------------------+
| 2020-05-11 21:08:42 | bd00fd5eb6159c4b521ad08103f4e34c |
+---------------------+----------------------------------+

select md5('1623339719');
+----------------------------------+
| md5('1623339719')                |
+----------------------------------+
| ddf1fa53e771fe83a0cd3eafb17d9d1f |
+----------------------------------+

（2）SHA算法

  • sha1(value)

2.6 事务

2.6.1 事务概念

（1）事务

  • 事务指的是按照逻辑组成的一组的sql语句的执行流程；
  • 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务（在高版本中MYISAM也支持事务处理）。
  • 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
  • 事务用来管理 insert,update,delete 语句。

（2）事务的ACID特性：

  • 一般来说，事务是必须满足4个条件（ACID）：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。

    • 原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
    • 一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
    • 隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
    • 持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

（3）数据库的并发带来的问题

  • 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题 －－最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一 文档的电子副本。每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。 最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同 一文件，则可避免此问题。
  • 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前， 这条记录的数据就处于不一致状态； 这时， 另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
  • 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读” 。
  • 幻读 （Phantom Reads）： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读” 。

2.6.2 MySQL事务的处理

（1）事务的提交模式

  • 自动提交模式：每一个DML语句都是一个单独的事务，如果需要将一套DML操作作为一个事务，必须使用begin [DMLs] end来表示中间的所有的DML作为一个事务。

    • BEGIN;在使用的时候有个小技巧，在使用BEGIN;开启新事务时会对前面的事务进行隐式提交，因此也可以用BEGIN;来提交事务，只是其副作用是会开启新的事务。当然，如果想要不保存当前事务所做的更改，使用

  • 手动提交模式：开启事务后，只有commit才会将以上所有的DML语句提交执行操作。

    • 在这种模式下，若不使用COMMIT;或其他具有隐式提交属性的语句(例如上述BEGIN;)就不会提交DML语句对数据的改变，提交之前允许使用ROLLBACK;对事务进行回滚。由于BEGIN;带有隐式提交属性，因此在这种模式下BEGIN;和COMMIT;是等价的。

  • 因为MySQL时默认自动提交事务的（例如insert语句执行后数据已经被写入数据库文件），但是更多的时候需要手动提交，所以需要关闭自动提交功能：

  • @@autocommit是表示是否开启事务自动提交的变量，1为开启，0为关闭，默认为1；

    select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

  • 关闭事务的自动提交：

    set autocommit = 0; 
或者
set autocommit = on;

  • 开启事务的自动提交：

    set autocommit = 1; 
或者
set autocommit = off;

    示例：两个数据库连接按如下顺序结果：

    -- 创建账户表
create table `account` (
	`id` int(16) not null auto_increment,
    `name` varchar(10) not null,
    `money` decimal(9,2) not null,
    primary key(`id`)
)engine = INNODB, default charset = utf8;

-- 插入数据
insert into `account` (`name`,`money`) values('Alice',2000.0),('Bob',500.0);

    在默认打开自动提交的模式下：

    • 在自动提交的模式下，每一条DMl语句都是一个事务，即时不使用commit显式提交，数据也会自动提交到文件中，能够被其他连接看到数据的修改。

    在关闭自动提交的模式下：

    • 可以看出在关闭自动提交的时候每个DML语句仅仅是再内存中改变数据，新的数据变化并没有写入到文件中

（2）事务的基本执行流程

  • set autoconmmit = 0：关闭事务的自动提交。

    • 在自动提交模式下，使用begin来作为事务SQL的起始，表示之后的每一个SQL都是同一个事务中的一条DML。
      • 具体为:begin; statement{DMLS} ;commit;
    • begin还会将之前没有提交（commit）的事务全部提交。

  • start transaction：事务开启的标志。

  • 中间SQL操作;

  • commit：提交操作，实现数据持久化。

  • rollback：回滚到事务开始前的状态。

  • rollback to 上一个保存点 ：回滚到上一层保存点。

    • 无论是rollback还是rollback to 都只对该事务范围内有效，一旦事务提交，就不可逆了。

  • savepoint 保存点名：记录进度的保存点。

  • realease savepoint 保存点名：释放删除保存点。

    示例：提交事务

    开启一个新事务：在关闭自动提交（set autocommit = off）的模式下：

    或者在开启自动提交(set autocommit = on)的模式下：

    示例：回滚到事务开始前的状态：

    示例：回滚到事务的保存点：

MySQL事务提交方式参考

2.6.3 事务的隔离级别

（1）为什么需要隔离原因？

  • MySQL是支持多连接的，为C/S架构，每个客户端连接成功后就作为一个会话(session)，不同的连接访问或者修改到其他客户端连接所需要的的数据，从而极有可能对其他会话造成数据不一致的影响（脏读，不可重复读，幻读）；
  • 正常来说如果要做到完全的隔离性，即事务的执行完全不会被其他事务所影响到，按照会话的请求的先后次序排队，依次执行事务是最简单有效的方式，但是，这也是并发性最差的一种方式。所以MySQL提供了事务的隔离性级别来保障一定的隔离性，又能允许不通会话的事务的并发执行。

（2）MySQL事务隔离界别的设置

  • 事务隔离级别的设置

    set session transaction isolation level 隔离级别
或者
SET GLOBAL tx_isolation='隔离级别';

  • 查看当前的事务的隔离级别（默认为Repeatable read）

    select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+

（3）读未提交（read uncommitted）

  • 读未提交：一个事务可以读取到另一个会话的未提交事务中的修改的数据的隔离级别。

    • 这实际上是隔离性最差的做法，其他会话的DML的执行都会影响到本事务的数据的一致性。

    示例：

    初始数据表：

    select * from account;
+----+--------+---------+
| id | name   | money   |
+----+--------+---------+
|  1 | Alice  | 2000.00 |
|  2 | Bob    |  500.00 |
|  3 | Cocol  | 1000.00 |
|  4 | Dev    | 3000.00 |
|  5 | Elisha | 7000.00 |
+----+--------+---------+

    • 将右边的会话连接隔离级别设置为read uncommitted后，只要是左边的事务的每个DML的执行，右边的连接的查询的数据皆会发生改变；
    • 当左边的事务回滚后，右边再次查询数据也将查询不到；
    • 可以看出这种情况下，即使其他事务没有处理完自己的流程，本会话也会读取到数据，如果直接读取数据后利用，之后其他会话又进行了数据的改动操作，则会产生脏读（这种别人工作还没有做完，就拿半成品数据来用的逻辑，很难在实际开发中去应用）

（4）读已提交（read committed）

  • 读已提交：如果在其他会话的事务还在事务之中，既没有提交，该隔离级别的本事务就无法获取到实时的其他事务的数据，只能获取之前最新的数据；当其他会话的事务提交之后，本事务就可以读取到最新的数据；

    示例：

    • 在读已提交隔离级别下，其他事务只有提交之后，本事务才能读取到最新的数据。
    • 左边事务开始执行某些DML，还没有提交，右侧事务开始进行查询，读取到之前的数据；当左侧事务提交之后，右侧的事务再次查询数据发现多了一条记录，且某些数据的值已经发生改变，即发生了不可重复读的现象。

（5）可重复读（repeatable read）

  • 可重复读：一个会话的事务只能读到另一个已经提交的事务修改过的数据，但是第一次读过某条记录后，即使其他事务修改了该记录的值并且提交，该事务之后再读该条记录时，读到的仍是第一次读到的值，而不是每次都读到不同的数据。

    示例：

    • 在可重复读隔离级别上,如果本会话开启了一个事务，即使其他事务已经提交对数据进行了改动，在本会话的事务中，所有的读取到的数据都是第一次读取到的值（除了本事务修改的数据），当本事务提交之后，再次查询才能看到其他事务的改动；

（6）串行化（serializable）

  • 串行化：在该隔离级别模式下，如果有其他事务正在操作本数据所在的对象，本事务将等待其他事务处理完毕后，再执行本事务的DML。

    示例：

2.7 索引

2.7.1 索引介绍

（1）索引概念

  • 索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到;
  • 索引的本质：索引是数据结构。

（2）索引分类

  1. 主键索引（primary key）:唯一标识，主键的值不可重复，一张表只能有一个主键，可以有一个字段或者多个字段作为一个主键；
  2. 唯一索引（unique key）：避免重复的列值出现，一个表可包含多个唯一索引，一个唯一索引可以有多个字段；
  3. 常规索引（key/index）：默认索引，使用index或者key来设置；
  4. 全文索引（fulltext）：一般只有MYISAM数据引擎支持该类索引（高版本的MySQL的innodb也开始支持全文索引），可以快速定位数据。

MySQL索引原理

2.7.2 索引的使用

（0）一些规范：

  • 主键索引名为 pk_字段名

  • 唯一索引名为 uk_字段名

  • 普通索引名为 idx_字段名

    • 说明：pk_ 即 primary key；uk_ 即 unique key；idx_ 即 index 的简称

（1）创建时指定索引

  • 创建表时指定主键索引：

    primary key(`字段名1`,`字段名2`,...)

  • 创建表时指定唯一索引：

    unique [唯一索引名] (字段名(长度))

  • 创建表时指定普通索引：

    index [普通索引名] (字段名(长度))

  • 创建表时指定全文索引：

    fulltext [全文索引名] (字段名(长度))

（2）索引的添加

使用create语句：

  • 添加普通索引：

    create index 索引名 on 表((`字段名1`,`字段名2`,...);

使用alter语句：

  • 添加主键索引：

    alter table `表名` add primary key [主键名] (`字段名1`,`字段名2`,...);

    该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

  • 添加唯一索引

    alter table `表名` add unique [唯一索引名] (`字段名1`,`字段名2`,...);

    这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

  • 添加普通索引

    alter table `表名` add index [普通索引名] (`字段名1`,`字段名2`,...);

    添加普通索引，索引值可出现多次。

  • 添加全文索引

    alter table `表名` add fulltext [全文索引名] (`字段名1`,`字段名2`,...);

    该语句指定了索引为 FULLTEXT ，用于全文索引

    示例：

    create table `app_user`(
	`id` bigint(20) unsigned not null auto_increment,
    `name` varchar(20) not null default '默认名',
    `email` varchar(40) not null,
    `phone` varchar(20) null,
    `gender` tinyint(4) default 0 comment 'sex(0:女，1：男)',
    `password` varchar(40) not null comment 'password',
    `age` tinyint(4) default 0 comment 'age',
    `create_time` datetime default current_timestamp,
    `update_time` datetime default current_timestamp on update current_timestamp,
    primary key(`id`)
)engine=INNODB default charset=utf8;

DELIMITER $$
-- 写函数之前必须要写，标志
CREATE FUNCTION mock_data ()
RETURNS INT
BEGIN
	DECLARE num INT DEFAULT 1000000;
	DECLARE i INT DEFAULT 0;
	WHILE i<num DO
		INSERT INTO `app_user`(`name`,`email`,`password`,`phone`,`gender`)VALUES(CONCAT('用户',i),'19224305@qq.com',md5(i),'123456789',FLOOR(RAND()*2));
		SET i=i+1;
	END WHILE;
RETURN i;
END;
DELIMITER ;
SELECT mock_data(); -- 执行此函数 生成一百万条数据

    测试查询速度：

    select * from app_user where id = 909045\G
*************************** 1. row ***************************
         id: 909045
       name: 用户909044
      email: 19224305@qq.com
      phone: 123456789
     gender: 0
   password: 1cbaf829b3488bca3949fd2b3b068d77
        age: 0
create_time: 2020-05-16 23:57:26
update_time: 2020-05-16 23:57:26
1 row in set (0.00 sec)


select * from app_user where name = '用户909044'\G
*************************** 1. row ***************************
         id: 909045
       name: 用户909044
      email: 19224305@qq.com
      phone: 123456789
     gender: 0
   password: 1cbaf829b3488bca3949fd2b3b068d77
        age: 0
create_time: 2020-05-16 23:57:26
update_time: 2020-05-16 23:57:26
1 row in set (0.39 sec)
    • 可以看出查询同样的一条记录，使用主键索引要比使用普通字段快得多。

    添加唯一索引：

    create index idx_app_user_name on app_user(`name`);

Query OK, 0 rows affected (2.25 sec)
Records: 0  Duplicates: 0  Warnings: 0
    • 数据量比较大的时候创建普通索引可能比较费时。

    再次查询：

    select * from app_user where name = '用户909044'\G
*************************** 1. row ***************************
         id: 909045
       name: 用户909044
      email: 19224305@qq.com
      phone: 123456789
     gender: 0
   password: 1cbaf829b3488bca3949fd2b3b068d77
        age: 0
create_time: 2020-05-16 23:57:26
update_time: 2020-05-16 23:57:26
1 row in set (0.00 sec)
    • 速度提高了非常多；

（3）查看索引的信息

  • 查看表的索引信息

    show index from 表名

    示例：

    show index from `app_user`\G

*************************** 1. row ***************************
        Table: app_user
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 955427
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
*************************** 2. row ***************************
        Table: app_user
   Non_unique: 1
     Key_name: idx_app_user_name
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 992824
     Sub_part: NULL
       Packed: NULL
         Null:
   Index_type: BTREE
      Comment:
Index_comment:
2 rows in set (0.00 sec)
    • 可以看出app_user表中共有两个索引（primary,idx_app_user_name）,且存储结构皆为BTREE

2.8 MySQL的用户、权限管理

2.8.1 MySQL用户登录管理

（1）mysql.user表

  • mysql.user表中存储着用户的所有信息，包括用户名，登录密码，各种权限等

    show columns from mysql.user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+

（2）更改user表用户登录信息

  • 添加用户

    create user 用户名 identified by '口令';

    • 刚添加的用户没有对其他数据库的操作权限（创建表，查询，修改，删除等会被数据库系统给拒绝），需要用当前拥有操作权限的用户修改数据库操作权限后，重新连接数据库方可生效。

      示例：

      -- 使用刚创建的testu
show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+

use mysql;
ERROR 1044 (42000): Access denied for user 'testu'@'%' to database 'mysql'

  • 重命名

    rename user 原用户名 to 新用户名;

  • 修改当前用户密码

    set password = password('新密码');

  • 删除用户

    drop user 用户名;

2.8.2 用户权限管理

（1）查看权限

  • 查询当前用户的权限

    show grants;

  • 查询指定用户的权限

    show grants for 用户名;

    示例：

    show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+

show grants for testu;
+-----------------------------------+
| Grants for testu@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO 'testu'@'%' |
+-----------------------------------+

（2）授予用户数据表的操作权限

  • grant语句

    grant 操作项 privileges on 数据库名.表名 to 用户名@主机地址 [with grant option];
    • 操作项：
      • all：授予除了授予其他用户权限能力的所有操作权限。
      • 其他操作权限：参考
    • 授予范围：
      • 如果想要授予全部数据库的权限，可以使用*.表名;如果是授予全部的表的权限可以使用数据库名.*；授予全部的对象的操作权限可以使用*.*。
    • with grant option：这条语句可以使得被授予权限的人拥有授予其他人的权限的权限。

（3）撤销用户数据表的操作权限

  • revoke语句

    revoke 操作项 privileges on 数据库名.表名 to 用户名@主机地址;
    • 与授权语句使用方法类似；

2.9 MySQL数据备份与恢复

2.9.1 MySQL数据备份

（1）mysqldump工具

  • mysqldump会对指定的数据库表生成sql文件，包括创建时的结构，以及插入数据的sql等。

  • 备份一个数据库中的多个表

    mysqldump -h主机地址 -u用户名 -p密码 数据库名 [数据表名1,数据表名2,数据表名3,...] >目标sql文件路径
    • 也可以-p不指定密码，然后之后输入（推荐）；
    • 如果不指定数据表，则将会导出全部数据表的sql；

  • 备份多个数据库

    mysqldump --h主机地址 -u用户名 -p密码 --databases 数据库名1[, 数据库名2,...] >目标sql文件路径

  • 备份所有的数据库

    mysqldump --h主机地址 -u用户名 -p密码 --all-databases >目标sql文件路径

2.9.2 MySQl数据库恢复

（1）通过命令行工具导入

  • mysql

    mysql -u用户名 -p密码 数据库名< 数据库备份sql文件路径

（2）登录的情况下

  • source语句（需要事先选择数据库）

    source 数据库备份sql文件路径

数据库的备份与恢复

参考-java狂神说

posted @ 2020-05-18 19:26  NIShoushun  阅读(231)  评论(0)  编辑  收藏  举报