MySQL第十一章以及事务、新特性

搬砖的小刘
93 阅读17分钟

11、修改表结构和约束

11.1 修改表结构

drop table if exists teacher;
create table teacher(
    tid int,
    tname varchar(5),
    salary double,
    weight double(5,2),
    birthday date,
    gender enum('男','女'),
    blood enum('A','B','AB','O'),
    tel char(11)
);

1、修改表名称：重命名表

alter table 旧表名 rename 【to】 新表名;
rename table 旧表名称 to 新表名称;

例如：

alter table teacher rename to t_tea;
rename table t_tea to teacher;

2、修改表结构：删除字段

alter table 表名称 drop 【column】 字段名称; 

alter table teacher drop column weight;

3、修改表结构：增加字段

alter table 表名称 add 【column】 字段名称 数据类型; 
alter table 表名称 add 【column】 字段名称 数据类型 first;
alter table 表名称 add 【column】 字段名称 数据类型 after 另一个字段;

alter table teacher add weight double(5,2);
alter table teacher drop column weight;
​
alter table teacher add weight double(5,2) first;
alter table teacher drop column weight;
​
alter table teacher add weight double(5,2) after salary;
alter table teacher drop column weight;

4、修改表结构：修改字段的名称

alter table 表名称 change 【column】 旧字段名称 新的字段名称 新的数据类型; 

mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid      | int(11)                | YES  |     | NULL    |       |
| tname    | varchar(5)             | YES  |     | NULL    |       |
| salary   | double                 | YES  |     | NULL    |       |
| weight   | double                 | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| gender   | enum('男','女')        | YES  |     | NULL    |       |
| blood    | enum('A','B','AB','O') | YES  |     | NULL    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
​
mysql> alter table teacher change tel phone char(11);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid      | int(11)                | YES  |     | NULL    |       |
| tname    | varchar(5)             | YES  |     | NULL    |       |
| salary   | double                 | YES  |     | NULL    |       |
| weight   | double                 | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| gender   | enum('男','女')        | YES  |     | NULL    |       |
| blood    | enum('A','B','AB','O') | YES  |     | NULL    |       |
| phone    | char(11)               | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

5、修改表结构：修改字段的数据类型等

alter table 表名称 modify 【column】 字段名称 新的数据类型; 
alter table 表名称 modify 【column】 字段名称 数据类型 first;
alter table 表名称 modify 【column】 字段名称 数据类型 after 另一个字段;

mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid      | int(11)                | YES  |     | NULL    |       |
| tname    | varchar(5)             | YES  |     | NULL    |       |
| salary   | double                 | YES  |     | NULL    |       |
| weight   | double(5,2)            | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| gender   | enum('男','女')        | YES  |     | NULL    |       |
| blood    | enum('A','B','AB','O') | YES  |     | NULL    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
​
mysql> alter table teacher modify column weight double;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
​
mysql> desc teacher;
+----------+------------------------+------+-----+---------+-------+
| Field    | Type                   | Null | Key | Default | Extra |
+----------+------------------------+------+-----+---------+-------+
| tid      | int(11)                | YES  |     | NULL    |       |
| tname    | varchar(5)             | YES  |     | NULL    |       |
| salary   | double                 | YES  |     | NULL    |       |
| weight   | double                 | YES  |     | NULL    |       |
| birthday | date                   | YES  |     | NULL    |       |
| gender   | enum('男','女')        | YES  |     | NULL    |       |
| blood    | enum('A','B','AB','O') | YES  |     | NULL    |       |
| tel      | char(11)               | YES  |     | NULL    |       |
+----------+------------------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

11.2 修改约束

drop table if exists teacher;
create table teacher(
    tid int,
    tname varchar(5),
    cardid char(18),
    gender enum ('男','女'),
    age int
);

1、增加或删除主键

alter table 表名称 add primary key(字段名);
alter table 表名称 add primary key(字段列表);#复合主键
alter table 表名称 drop primary key;

desc teacher;
​
alter table teacher add primary key(tid);
​
desc teacher;
​
alter table teacher drop primary key;
​
desc teacher;

2、增加或删除唯一键

#如何在建表后添加唯一键约束
alter table 表名称 add unique 【key】(字段名);
alter table 表名称 add unique 【key】(字段列表);#复合唯一键

alter table 表名称 drop index 索引名;
#删除唯一键约束需要手动删除对应的索引

show index from 表名称;
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

alter table teacher add unique key (cardid);

alter table teacher drop index cardid;

3、修改表结构：修改非空、默认值约束、自增

alter table 表名称 modify 【column】 字段名称 新的数据类型 【not null】 【default 默认值】 【auto_increment】;

alter table teacher modify column tid int auto_increment;#键列才能加自增
alter table teacher modify column tname varchar(5) not null;
alter table teacher modify column gender enum ('男','女') not null default '男';

4、增加或删除检查约束

#如何在建表后添加检查约束，使用add check
alter table 表名称 add check(条件);

alter table 表名称 drop check 检查约束名;

SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';

alter table teacher add check(age>=18);
​
SELECT * FROM information_schema.table_constraints WHERE table_name = 'teacher';
​
alter table teacher drop check teacher_chk_1;

5、增加或删除外键约束

alter table 从表名称 add foreign key (从表的字段) references 主表（被引用字段) 【on update xx】【on delete xx】;

删除外键约束，不会自动删除外键约束列的索引，需要单独删除。

(1)第一步先查看约束名和删除外键约束
show create table 从表名称; #可以看到外键约束名
SELECT * FROM information_schema.table_constraints WHERE table_name = '表名称';#查看某个表的约束名
​
alter table 从表名 drop foreign key 外键约束名;
​
（2）第二步查看索引名和删除索引
show index from 表名称; #查看某个表的索引名
​
alter table 从表名 drop index 索引名;

drop table if exists dept;
create table dept(
    did int primary key auto_increment,
    dname varchar(50) unique key not null
);
​
drop table if exists emp;
create table emp(
    id int primary key auto_increment,
    name varchar(20) not null,
    departmentid int
);
​
alter table emp add foreign key (departmentid) references dept(did) on update cascade on delete restrict;
​
SELECT * FROM information_schema.table_constraints WHERE table_name = 'emp';
​
alter table emp drop foreign key emp_ibfk_1;
​
show index from emp;
​
alter table emp drop index departmentid;

第12章 事务

12.1 事务的特点

1、事务处理（事务操作）：保证所有事务都作为一个工作单元来执行，即使出现了故障，都不能改变这种执行方式。当在一个事务中执行多个操作时，要么所有的事务都被提交(commit)，那么这些修改就永久地保存下来；要么数据库管理系统将放弃所作的所有修改，整个事务回滚(rollback)到最初状态。

2、事务的ACID属性：

（1）原子性（Automicity） 原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

（2）一致性（Consistency） 事务必须使数据库从一个一致性状态变换到另外一个一致性状态。

（3）隔离性（Isolation） 事务的隔离性是指一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。

（4）持久性（Durability） 持久性是指一个事务一旦被提交，它对数据库中数据的改变就是永久性的，接下来的其他操作和数据库故障不应该对其有任何影响

/*
原子性：
例如：
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000
成功：张三账号变为500，李四变为1500，
失败：张三账号是1000，李四还是1000.
​
​
#一致性
例如：
张三给李四转账500
张三转账之前余额是1000
李四转账之前余额是1000
​
要么他俩的余额不变， 还是1000，总和就是2000
要么他俩的余额有变化，张三500，李四1500，总和仍然是2000
错误：
张三500，李四1000，总和是1500，结果不对
张三1000，李四1500，总和是2500，结果不对
​
#隔离性
例如：张三要给李四转500，
      王五要给李四转500，
      张三转账是否成功，和王五是否转账成功无关。
      
      
#持久性：
例如：张三要给李四转500，一旦成功提交，就转账成功，撤不回来了。
      */

12.2 事务的开启、提交、回滚

MySQL默认情况下是自动提交事务。 每一条语句都是一个独立的事务，一旦成功就提交了。一条语句失败，单独一条语句不生效，其他语句是生效的。

12.2.1 手动提交模式

#开启手动提交事务模式
set autocommit = false;  或  set autocommit = 0;
​
上面语句执行之后，它之后的所有sql，都需要手动提交才能生效，直到恢复自动提交模式。
​
#恢复自动提交模式
set autocommit = true; 或 set autocommit = 1;

例如：

SET autocommit = FALSE;#设置当前连接为手动提交模式
​
UPDATE t_employee SET salary = 15000 
WHERE ename = '孙红雷';
​
COMMIT;#提交

例如：

SET autocommit = FALSE;#设置当前连接为手动提交模式
​
UPDATE t_employee SET salary = 15000 
WHERE ename = '孙红雷';
​
#后面没有提交，直接关了连接，那么这句修改没有生效

12.2.2 自动提交模式下开启事务

/*
也可以在自动提交模式下，开启一个事务。
(1)start transaction;
​
....
​
(3)commit; 或 rollback;   
在(1)和(3)之间的语句是属于手动提交模式，其他的仍然是自动提交模式。
*/
​
START TRANSACTION; #开始事务
​
UPDATE t_employee SET salary = 0 
WHERE ename = '李冰冰'; 
​
#下面没有写commit;那么上面这句update语句没有正式生效。
commit;#提交
​
START TRANSACTION;
DELETE FROM t_employee;
ROLLBACK; #回滚

12.2.3 DDL语句不支持事务

#说明：DDL不支持事务
#DDL：create,drop,alter等创建库、创建表、删除库、删除表、修改库、修改表结构等这些语句不支持事务。
#换句话只对insert,update,delete语句支持事务。
TRUNCATE 表名称; 清空整个表的数据，不支持事务。 把表drop掉，新建一张表。
​
START TRANSACTION;
TRUNCATE t_employee;
ROLLBACK; #回滚  无效

12.3 事务的隔离级别

/*
mysql支持四个隔离级别：
read-uncommitted：会出现脏读、不可重复读、幻读
read-committed：可以避免脏读，会出现不可重复读、幻读
repeatable-read：可以避免脏读、不可重复读、幻读。但是两个事务不能操作（写update,delete）同一个行。
serializable：可以避免脏读、不可重复读、幻读。但是两个事务不能操作（写update,delete）同一个表。
​
修改隔离级别：
set transaction_isolation='隔离级别';  
#mysql8之前 transaction_isolation变量名是 tx_isolation
​
查看隔离级别：
select @@transaction_isolation;
*/

数据库事务的隔离性：数据库系统必须具有隔离并发运行各个事务的能力, 使它们不会相互影响, 避免各种并发问题。一个事务与其他事务隔离的程度称为隔离级别。 数据库规定了多种事务隔离级别, 不同隔离级别对应不同的干扰程度, 隔离级别越高, 数据一致性就越好, 但并发性越弱。

  • 脏读：一个事务读取了另一个事务未提交数据；
  • 不可重复读：同一个事务中前后两次读取同一条记录不一样。因为被其他事务修改了并且提交了。
  • 幻读：一个事务读取了另一个事务新增、删除的记录情况，记录数不一样，像是出现幻觉。

数据库提供的 4 种事务隔离级别：

隔离级别描述
read-uncommitted允许A事务读取其他事务未提交和已提交的数据。会出现脏读、不可重复读、幻读问题
read-committed只允许A事务读取其他事务已提交的数据。可以避免脏读，但仍然会出现不可重复读、幻读问题
repeatable-read确保事务可以多次从一个字段中读取相同的值。在这个事务持续期间，禁止其他事务对这个字段进行更新。可以避免脏读和不可重复读。但是幻读问题仍然存在。注意：mysql中使用了MVCC多版本控制技术，在这个级别也可以避免幻读。
serializable确保事务可以从一个表中读取相同的行，相同的记录。在这个事务持续期间，禁止其他事务对该表执行插入、更新、删除操作。所有并发问题都可以避免，但性能十分低下。
  • Oracle 支持的 2 种事务隔离级别：READ-COMMITED, SERIALIZABLE。 Oracle 默认的事务隔离级别为: READ COMMITED 。
  • Mysql 支持 4 种事务隔离级别。 Mysql 默认的事务隔离级别为: REPEATABLE-READ。在mysql中REPEATABLE READ的隔离级别也可以避免幻读了。

第13章 用户管理

13.1 查看用户

MySQL中的用户和权限都保存在mysql系统库相应的数据表中。MySQL服务器通过权限表来控制用户对数据库的访问，由MySQL_install_db脚本初始化。存储账户权限信息的表主要有user、db、host、tables_priv、columns_priv和procs_priv。

  • user表：存储连接MySQL服务的账户信息，账户对全局有效；
  • db表：存储用户对某个具体数据库的操作权限；
  • tables_priv表：存储用户对某个数据表的操作权限；
  • columns_priv表：存储用户对数据表的某一列的操作权限；
  • procs_priv表：存储用户对存储过程和函数的操作权限。
select * from mysql.user;

13.2 用户管理

创建、删除用户，以及为用户授权和撤销权限等sql（略）。请看可视化工具笔记。

第14章 MySQL8的部分新特性

1、系统表全部为InnoDB表

从 MySQL 8.0 开始，mysql 系统表和数据字典表使用 InnoDB 存储引擎，存储在 MySQL 数据目录下的 mysql.ibd 表空间文件中。在 MySQL 5.7 之前，这些系统表使用 MyISAM 存储引擎，存储在 mysql 数据库文件目录下各自的表空间文件中。关于数据库存储引擎的详细内容，在MySQL高级课程讲解。

在MySQL5.7版本中查看系统表类型，结果如下：

mysql> #MySQL5.7
mysql> #查看系统表类型
mysql> SELECT DISTINCT(ENGINE) FROM information_schema.tables;
+----------------------+
| ENGINE                    |
+----------------------+
| MEMORY                    |
| InnoDB                    |
| MyISAM                    |
| CSV                       |
| PERFORMANCE_SCHEMA    |
| NULL                      |
+----------------------+
6 rows in set (0.04 sec)

在MySQL8.0版本中查看系统表类型，结果如下：

mysql> #MySQL8.0
mysql> #查看系统表类型
mysql> SELECT DISTINCT(ENGINE) FROM information_schema.tables;
+--------------------+
| ENGINE                |
+--------------------+
| InnoDB                |
| NULL                  |
| PERFORMANCE_SCHEMA |
| CSV                   |
+--------------------+
4 rows in set (0.00 sec)

系统表全部换成事务型的InnoDB表，默认的MySQL实例将不包含任何MyISAM表，除非手动创建MyISAM表。

2、默认字符集改为utf8mb4

在8.0版本之前，MySQL默认的字符集为Latin1，而8.0版本默认字符集为utf8mb4。

Latin1是ISO-8859-1的别名，有些环境下写作Latin-1。ISO-8859-1编码是单字节编码，不支持中文等多字节字符，但向下兼容ASCII。

MySQL中utf8字符集，它是utf8mb3的别称，使用三个字节编码表示一个字符。自MySQL4.1版本被引入，能够支持绝大多数语言的字符，但依然有些字符不能正确编码，如emoji表情字符等，为此MySQL5.5引入了utf8mb4字符集。在MySQL5.7对utf8mb4进行了大幅优化，并丰富了校验字符集。mb4就是“most byte 4”的意思，专门用来兼容四字节的Unicode，utf8mb4编码是utf8编码的超集，兼容utf8，并且能存储4字节的表情字符。如果原来某些库和表的字符集是utf8，可以直接修改为utf8mb4，不需要做其他转换。但是从uft8mb4转回utf8就会有问题。

使用SHOW语句查看MySQL5.7版本数据库的默认编码。

mysql> #查看MySQL5.7数据库的默认编码
mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+--------+
| Variable_name            | Value  |
+------------------------+--------+
| character_set_database | latin1 |
+------------------------+--------+
1 row in set, 1 warning (0.00 sec)

使用SHOW语句查看MySQL8.0版本数据库的默认编码。

mysql> #查看MySQL8.0数据库的默认编码
mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+---------+
| Variable_name            | Value   |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set, 1 warning (0.00 sec)

字符集校对规则是在字符集内用于字符比较和排序的一套规则，比如有的规则区分大小写，有的则无视。校对规则特征：

  • 两个不同的字符集不能有相同的校对规则。
  • 每个字符集有一个默认校对规则。
  • 校对规则存在命名约定，以其相关的字符集名开始，中间包括一个语言名，并且以ci、 cs或bin结尾。其中ci表示大小写不敏感、_cs表示大小写敏感、bin表示直接比较字符的二进制编码，即区分大小写。

使用SHOW语句查看utf8mb4字符集的部分校对规则如下：

mysql> SHOW COLLATION LIKE 'utf8mb4_0900%';
+-----------+---------+-----+---------+----------+---------+--------------+
| Collation  | Charset | Id  | Default | Compiled | Sortlen | Pad_attribute|
+-------------------+---------+-----+---------+----------+-------+--------+
|utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes    | Yes     |   0 | NO PAD           |
|utf8mb4_0900_as_ci | utf8mb4 | 305 |         | Yes     |   0 | NO PAD          |
|utf8mb4_0900_as_cs | utf8mb4 | 278 |         | Yes     |   0 | NO PAD          |
|utf8mb4_0900_bin   | utf8mb4 | 309 |         | Yes     |   1 | NO PAD          |
+-------------------+---------+-----+---------+----------+---------+------+
4 rows in set (0.00 sec)

3、支持检查约束（见上面检查约束）

4、支持窗口函数（见上面窗口函数）

5、用户管理

在MySQL 8.x中，默认的身份认证插件是“caching_sha2_password”，替代了之前的“mysql_native_password”。可以通过系统变量default_authentication_plugin和mysql数据库中的user表来看到这个变化。

在MySQL8之前默认的身份插件是“mysql_native_password”，即MySQL用户的密码使用PASSWORD函数进行加密。在MySQL 8.x中，默认的身份认证插件是“caching_sha2_password”，替代了之前的“mysql_native_password”，PASSWORD函数被弃用了。

在MySQL版本5.6.6版本起，在mysql.user表中添加了“password_expired”字段，它允许设置密码是否失效。如果“password_lifetime”字段值不为NULL，那么从MySQL服务启动时间开始，经过“password_lifetime”字段值的时间间隔之后，密码就过期了，即“password_expired”字段就为“Y”。任何密码超期的账号想要连接服务器端进行数据库操作都必须更改密码。MySQL8.0版本允许数据库管理员手动设置账户密码过期时间。

从MySQL 8.x版本开始允许限制重复使用以前的密码。

在MySQL8之前，如果要给多个用户授予相同的角色，需要为每个用户单独授权。在MySQL8之后，可以为多个用户赋予统一的角色，然后给角色授权即可，角色可以看成是一些权限的集合，这样就无须为每个用户单独授权。如果角色的权限修改，将会使得该角色下的所有用户的权限都跟着修改，这就非常方便。

mysql的密码字段有变化：

  • mysql5.7之前mysql系统库的user表，密码字段名是password
  • mysql5.7版本mysql系统库的user表，密码字段名是authentication_string
  • mysql8.0版本mysql系统库的user表，密码字段名是authentication_string，另外用户管理还有角色概念，mysql系统库中有default_roles表。

6、其他新特性（略）

通用表达式、计算列、DDL操作支持原子性、数据字典合并等等。

通用表达式简称为CTE（Common Table Expressions）。CTE是命名的临时结果集，作用范围是当前语句。CTE可以理解为一个可以复用的子查询，但是和子查询又有区别，一个CTE可以引用其他CTE，CTE还可以是自引用(递归CTE)，也可以在同一查询中多次引用，但子查询不可以。

WITH [RECURSIVE]
 cte_name [(col_name [, col_name] ...)] AS (subquery)
 [, cte_name [(col_name [, col_name] ...)] AS (subquery)] ...

通用表达式以“WITH”开头，如果“WITH”后面加“RECURSIVE”就表示接下来在通用表达式中需要递归引用自己，否则就不递归引用。每一个通用表达式都需要有一个名字，它相当于是子查询结果集的名字。

#（1）在“t_employee”表中查询每个人薪资和公司平均薪资的的差值。
WITH temp AS (SELECT ROUND(AVG(salary),2) AS pingjun FROM t_employee)
SELECT ename AS "员工姓名",
    salary AS "薪资",
    pingjun "公司平均薪资",
    ROUND(salary - pingjun,2) "差值"
FROM t_employee,temp
HAVING ABS(差值)>5000;
​
#（2）查询薪资低于9000的员工编号，员工姓名，员工薪资，领导编号，领导姓名，领导薪资
WITH 
emp AS (SELECT eid,ename,salary,`mid` FROM t_employee WHERE salary <9000),
mgr(meid,mename,msalary) AS (SELECT eid,ename,salary FROM t_employee)
  
SELECT eid AS "员工薪资",
    ename AS "员工姓名",
    salary AS "员工薪资",
    meid AS "领导编号",
    mename AS "领导姓名",
    msalary AS "领导薪资"
FROM emp INNER JOIN mgr ON emp.mid = mgr.meid;
​
#（3）查询eid为21的员工，和他所有领导，直到最高领导。
CREATE TABLE emp AS (SELECT eid,ename,salary,tel,`mid` FROM t_employee WHERE salary < 10000);
UPDATE emp SET MID=19 WHERE eid=21; 
UPDATE emp SET MID=17 WHERE eid=19; 
UPDATE emp SET MID=16 WHERE eid=17; 
UPDATE emp SET MID=15 WHERE eid=16;
UPDATE emp SET MID=4 WHERE eid=15; 
UPDATE emp SET MID=NULL WHERE eid=4;
SELECT * FROM emp;
​
​
WITH RECURSIVE cte
AS ( 
    SELECT eid,ename,`mid`
    FROM emp
    WHERE eid = 21
​
UNION ALL
​
    SELECT emp.eid,emp.ename,emp.mid
    FROM emp INNER JOIN cte
    ON emp.eid = cte.mid
    WHERE emp.eid IS NOT NULL
)
SELECT * FROM cte;
avatar
文章
阅读
粉丝