MySQL 学习笔记

mysql 学习笔记

关系型数据库

一、如何使用终端操作数据库

1. 如何登陆数据库服务器

mysql -uroot -p

2. 如何查询数据库服务器汇中的所有的数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

3. 如何选中某一个数据库进行操作

mysql> select * from admin;
ERROR 1046 (3D000): No database selected

mysql> use test;
Database changed

mysql> select * from admin;
ERROR 1146 (42S02): Table 'test.admin' doesn‘t exist

mysql> select * from admin where ID=1;
ERROR 1146 (42S02): Table 'test.admin' doesn't exist

4. 如何退出数据库服务器

mysql> exit;
Bye

5. 如何在数据库服务器中创建我们的数据库

mysql> create database test1;
Query OK, 1 row affected (0.01 sec)

mysql> use test1;
Database changed

6. 如何查看某个数据库中所有的数据库

mysql> show tables;
Empty set (0.03 sec)

7. 如何创建一个数据表

mysql> CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
species VARCHAR(20),
sex CHAR (1),
birth DATE,
death DATE);
Query OK, 0 rows affected (0.04 sec)

8. 查看数据表是否创建成功

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| pet             |
+-----------------+
1 row in set (0.00 sec)
1 row in set (0.00 sec)

9. 查看创建好的数据表的结构

mysqlmysql> describe pet;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| name    | varchar(20) | YES  |     | NULL    |       |
| owner   | varchar(20) | YES  |     | NULL    |       |
| species | varchar(20) | YES  |     | NULL    |       |
| sex     | char(1)     | YES  |     | NULL    |       |
| birth   | date        | YES  |     | NULL    |       |
| death   | date        | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)

10. 查看表中的记录

mysql> select * from pet;
Empty set (0.01 sec)

11. 如何往数据表中添加数据记录

mysql> select * from pet;
+----------+--------+---------+------+------------+-------+
| name     | owner  | species | sex  | birth      | death |
+----------+--------+---------+------+------------+-------+
| Puffball | Diane  | hamster | f    | 2019-01-01 | NULL  |
+----------+--------+---------+------+------------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO pet
        -> VALUES ('旺财','周星驰','公',1990-02-02,NULL);
ERROR 1136 (21S01): Column count doesn’t match value count at row 1

mysql> INSERT INTO pet
        -> VALUES ('旺财','周星驰','狗','公','1990-02-02',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| Puffball | Diane     | hamster | f    | 2019-01-01 | NULL  |
| 旺财     | 周星驰    | 狗      | 公   | 1990-02-02 | NULL  |
+----------+-----------+---------+------+------------+-------+
2 rows in set (0.00 sec)

12. mysql 常用的数据类型有哪些

MySQL 支持多种类型,大致可以分为三类:

数值、

类型 大小 范围(有符号) 范围(无符号) 用途
TINYINT 1 字节 (-128,127) (0,255) 小整数值
SMALLINT 2 字节 (-32 768,32 767) (0,65 535) 大整数值
MEDIUMINT 3 字节 (-8 388 608,8 388 607) (0,16 777 215) 大整数值
INT 或 INTEGER 4 字节 (-2 147 483 648,2 147 483 647) (0,4 294 967 295) 大整数值
BIGINT 8 字节 (-9,223,372,036,854,775,808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) 极大整数值
FLOAT 4 字节 (-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 字节 (-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 的值 小数值
mysql> create table testType(
    -> number TINYINT);
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| pet             |
| testtype        |
+-----------------+
2 rows in set (0.00 sec)

mysql> describe testtype;
+--------+------------+------+-----+---------+-------+
| Field  | Type       | Null | Key | Default | Extra |
+--------+------------+------+-----+---------+-------+
| number | tinyint(4) | YES  |     | NULL    |       |
+--------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> INSERT INTO testtype VALUES (127);
Query OK, 1 row affected (0.01 sec)

mysql> select * from testtype;
+--------+
| number |
+--------+
|    127 |
+--------+
1 row in set (0.00 sec)

mysql> INSERT INTO testtype VALUES (128);
ERROR 1264 (22003): Out of range value for column 'number' at row 1

日期 / 时间

类型 大小 (字节) 范围 格式 用途
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 混合日期和时间值,时间戳

和字符串 (字符) 类型。

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

13. 数据类型如何选择

日期 选择按照格式

数值和字符串按照大小

14. 如何插入以下数据到数据表

INSERT INTO pet VALUES (‘ball’,‘cxk’,‘rap’,‘f’,‘2019-07-01’,NULL);

INSERT INTO pet VALUES (‘AA’,’zz ’,‘chicken’,‘f’,‘2019-01-01’,NULL);

INSERT INTO pet VALUES (‘BB’,’yy ’,‘cat’,‘m’,‘2014-06-01’,NULL);

INSERT INTO pet VALUES (‘CC’,’xx ’,‘monkey’,‘m’,‘2018-08-01’,NULL);

INSERT INTO pet VALUES (‘DD’,’ww ’,‘snake’,‘f’,‘2017-04-01’,NULL);

mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| ball     | cxk       | rap     | f    | 2019-07-01 | NULL  |
| 旺财     | 周星驰    | 狗      | 公   | 1990-02-02 | NULL  |
| Puffball | Diane     | hamster | f    | 2019-01-01 | NULL  |
| AA       | zz        | chicken | f    | 2019-01-01 | NULL  |
| BB       | yy        | cat     | m    | 2014-06-01 | NULL  |
| CC       | xx        | monkey  | m    | 2018-08-01 | NULL  |
| DD       | ww        | snake   | f    | 2017-04-01 | NULL  |
+----------+-----------+---------+------+------------+-------+
7 rows in set (0.00 sec)

15. 如何删除数据

mysql> delete from pet where name=‘旺财’;
Query OK, 1 row affected (0.00 sec)

mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| ball     | cxk       | rap     | f    | 2019-07-01 | NULL  |
| Puffball | Diane     | hamster | f    | 2019-01-01 | NULL  |
| AA       | zz        | chicken | f    | 2019-01-01 | NULL  |
| BB       | yy        | cat     | m    | 2014-06-01 | NULL  |
| CC       | xx        | monkey  | m    | 2018-08-01 | NULL  |
| DD       | ww        | snake   | f    | 2017-04-01 | NULL  |
+----------+-----------+---------+------+------------+-------+
6 rows in set (0.00 sec)

16. 如何修改数据

mysql> update pet set name='ball' where owner='Diane';
Query OK, 1 rows affected (0.01 sec)
Rows matched: 1 Changed: 1  Warnings: 0

mysql> select * from pet;
+----------+-----------+---------+------+------------+-------+
| name     | owner     | species | sex  | birth      | death |
+----------+-----------+---------+------+------------+-------+
| ball     | cxk       | rap     | f    | 2019-07-01 | NULL  |
| ball     | Diane     | hamster | f    | 2019-01-01 | NULL  |
| AA       | zz        | chicken | f    | 2019-01-01 | NULL  |
| BB       | yy        | cat     | m    | 2014-06-01 | NULL  |
| CC       | xx        | monkey  | m    | 2018-08-01 | NULL  |
| DD       | ww        | snake   | f    | 2017-04-01 | NULL  |
+----------+-----------+---------+------+------------+-------+
6 rows in set (0.00 sec)

总结一下:

  • 增加

INSERT

  • 删除

DELETE

  • 修改

UPDATE

  • 查询

SELECT

17. MySQL 添加字段和修改字段

  1. 添加表字段

alter table 表名 字段名称 字段类型 [是否允许非空];

alter table 表名 add id int unsigned not Null auto_increment primary key

  1. 修改某个表的字段类型及指定为空或非空
    alter table 表名 change 字段名称 字段名称 字段类型 [是否允许非空];
    alter table 表名 modify 字段名称 字段类型 [是否允许非空];

alter table 表名 modify 字段名称 字段类型 [是否允许非空];

  1. 修改某个表的字段名称及指定为空或非空
    alter table 表名 change 字段原名称 字段新名称 字段类型 [ 是否允许非空

  2. 如果要删除某一字段,可用命令:alter table 表名 drop 字段名;

18. mysql 中如何改变字段或者列的顺序

  1. 将字段放在最前面

alter table 表名 modify 字段名 字段类型 first;

eg. alter table student modify id int(10) unsigned auto_increment first;

  1. 放在某一字段的后面

alter table 表名 modify 字段名 字段类型 after 字段

eg. alter table student modify name varchar(10) after id;

二、mysql 建表约束条件

1. 主键约束

它能够唯一确定一张表中的一条记录,也就是我们通过给某个字段添加约束,就可以使得改字段

不重复且不为空

mysql> create table user(
    -> id int primary key,
    -> name varchar (20)
    -> );
Query OK, 0 rows affected (0.03 sec)
_________________________________________________________________
mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| pet             |
| testtype        |
| user            |
+-----------------+
3 rows in set (0.00 sec)
_________________________________________________________________
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
_________________________________________________________________
mysql> insert into user values (1,'张三');
Query OK, 1 row affected (0.01 sec)
_________________________________________________________________
mysql> insert into user values (1,'张三');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
_________________________________________________________________
mysql> insert into user values (2,'张三');
Query OK, 1 row affected (0.00 sec)
_________________________________________________________________
mysql> select * from user;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
+----+--------+
2 rows in set (0.00 sec)
_________________________________________________________________
mysql> insert into user values (NULL,'张三');
ERROR 1048 (23000): Column 'id' cannot be null

复合主键(一个表)

只要复合的主键值加起来不重复就可以,其中任何一个主键都不能为空。

mysql> create table user2(
    -> id int,
    -> name varchar(20),
    -> password varchar(20),
    -> primary key(id,name)
    -> );
Query OK, 0 rows affected (0.03 sec)   
_________________________________________________________________
mysql> desc user2;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | NO   | PRI | NULL    |       |
| password | varchar(20) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
_________________________________________________________________
mysql> insert into user2 values (1,'张三','123');
Query OK, 1 row affected (0.01 sec)

mysql> insert into user2 values (1,'张三','123');
ERROR 1062 (23000): Duplicate entry '1-张三' for key 'PRIMARY'

mysql> insert into user2 values (2,'张三','123');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user2 values (1,'李四','123');
Query OK, 1 row affected (0.01 sec)
_________________________________________________________________
mysql> select * from user2;
+----+--------+----------+
| id | name   | password |
+----+--------+----------+
|  1 | 张三   | 123      |
|  1 | 李四   | 123      |
|  2 | 张三   | 123      |
+----+--------+----------+
3 rows in set (0.00 sec)

联合主键(两个表)

当两个数据表形成的是多对多的关系,那么需要通过两个数据表的主键来组成联合主键,就可以确定每个数据表的其中一条记录了.

2. 自增约束 auto_increment

mysql> create table user3(
     -> id int primary key auto_increment,
     -> name varchar(20)
     -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)

mysql> insert into user3 (name) values ('zhangsan');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user3;
+----+----------+
| id | name     |
+----+----------+
|  1 | zhangsan |
|  2 | zhangsan |
+----+----------+
2 rows in set (0.00 sec)

如果说我们创建表的时候,忘记创建主键约束了该怎么办

mysql> create table user4(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.04 sec)

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

/*修改表结构添加主键*/
mysql> alter table user4 add primary key(id);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

/*如何删除?*/
mysql> alter table user4 drop primary key;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

/*使用modify修改字段,添加约束*/
mysql> alter table user4 modify id int primary key;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user4;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3. 唯一约束

 约束修饰的字段的值不可以重复
mysql> create table user5(
    -> id int,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user5;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user5 values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'

name=lisi
mysql> insert into user5 values(1,'lisi');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user5;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    1 | lisi     |
+------+----------+
2 rows in set (0.00 sec)

/*创建表的时候直接添加*/
mysql> create table user6(
    -> id int,
    -> name varchar(20),
    -> unique(name)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc user6;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> create table user7(
    -> id int,
    -> name varchar(20) unique
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
/*复合唯一键只要两个加起来不同即可*/
mysql> create table user8(
    -> id int,
    -> name varchar(20),
    -> unique(id,name)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc user8;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

/*如何删除唯一约束?*/
mysql> alter table user7 drop index name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

/*通过modify添加唯一约束*/
mysql> alter table user7 modify name varchar(20) unique;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc user7;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  | UNI | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

总结

1. 建表的时候就添加约束

2. 可以使用 alter。。。add。。。

3.alter。。。modify。。。

4. 删除 alter。。。drop。。。

4. 非空约束

 修饰的字段不能为空NULL
mysql> create table user9(
    -> id int,
    -> name varchar(20) not null
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user9 (id) values(1);
ERROR 1364 (HY000): Field ’name‘ doesn’t have a default value
mysql> insert into user9 values(1,'张三');
Query OK, 1 row affected (0.02 sec)

mysql> desc user9;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | NO   |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into user9 (name) values('李四');
Query OK, 1 row affected (0.01 sec)

mysql> select * from user9;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 张三   |
| NULL | 李四   |
+------+--------+
2 rows in set (0.00 sec)

5. 默认约束

当我们插入字段值的时候,如果没有传值,就会使用默认值

mysql> create table user10(
    -> id int,
    -> name varchar(20),
    -> age int default 10
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc user10;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | 10      |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into user10 (id,name) values (1,'zhangsan');
Query OK, 1 row affected (0.00 sec)

mysql> select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
+------+----------+------+
1 row in set (0.00 sec)

/*传了值就不会使用默认值*/
mysql> insert into user10 values (1,'zhangsan',19);
Query OK, 1 row affected (0.01 sec)

mysql> select * from user10;
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    1 | zhangsan |   10 |
|    1 | zhangsan |   19 |
+------+----------+------+
2 rows in set (0.00 sec)

6. 外键约束

涉及到两个表:父表,子表

主表,副表。

表与表的记录之间存在着三种关系:一对多、多对多、一对一的关系。

约束 1:在创建表时,先建被关联的表 dep,才能建关联表 emp

约束 2:在插入记录时,必须先插被关联的表 dep,才能插关联表 emp

约束 3:更新与删除都需要考虑到关联与被关联的关系。

解决方案:

1、先删除关联表 emp,再删除被关联表 dep,准备重建

2、重建:

新增功能,同步更新,同步删除

班级

mysql> create table classes(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.02 sec)
 学生表
mysql> create table students(
    -> id int primary key,
    -> name varchar(20),
    -> class_id int,
    -> foreign key(class_id) references classes(id)
    /*将class_id关联到表classes中的id*/
    -> );
Query OK, 0 rows affected (0.02 sec)
mysql> desc classes;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | NO   | PRI | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> desc students;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(20) | YES  |     | NULL    |       |
| class_id | int(11)     | YES  | MUL | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into classes values (1,'一班');
Query OK, 1 row affected (0.01 sec)

mysql> insert into classes values (2,'二班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into classes values (3,'三班');
Query OK, 1 row affected (0.00 sec)

mysql> insert into classes values (4,'四班');
Query OK, 1 row affected (0.01 sec)

mysql> select * from classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | 一班   |
|  2 | 二班   |
|  3 | 三班   |
|  4 | 四班   |
+----+--------+
4 rows in set (0.00 sec)

mysql> insert into students values (1001,'张三',1);
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values (1002,'张三',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values (1003,'张三',3);
Query OK, 1 row affected (0.00 sec)

mysql> insert into students values (1004,'张三',4);
Query OK, 1 row affected (0.00 sec)

mysql> select * from students;
+------+--------+----------+
| id   | name   | class_id |
+------+--------+----------+
| 1001 | 张三   |        1 |
| 1002 | 张三   |        2 |
| 1003 | 张三   |        3 |
| 1004 | 张三   |        4 |
+------+--------+----------+
4 rows in set (0.00 sec)

mysql> insert into students values (1005,'李四',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test1`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))/*因为class_id=5不再classes的id中*/

mysql> insert into students values(1009,'王老五',2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from students;
+------+-----------+----------+
| id   | name      | class_id |
+------+-----------+----------+
| 1001 | 张三      |        1 |
| 1002 | 张三      |        2 |
| 1003 | 张三      |        3 |
| 1004 | 张三      |        4 |
| 1009 | 王老五    |        2 |
+------+-----------+----------+
5 rows in set (0.00 sec)

1.主表 classes 中没有的数据值,在副表中,是不可以使用的。

2.主表中的记录被副表引用,是不可以被删除的。

mysql> delete from classes where id=4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test1`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

三、数据库的三大设计范式

1. 第一范式 1NF

数据表中的所有字段都是不可分割的原子值

mysql> insert into student2 values (1,'张三','中国四川省成都市100号');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student2 values (2,'李四','中国四川省成都市101号');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student2 values (3,'王五','中国四川省成都市102号');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student2;
+----+--------+--------------------------------+
| id | name   | address                        |
+----+--------+--------------------------------+
|  1 | 张三   | 中国四川省成都市100号          |
|  2 | 李四   | 中国四川省成都市101号          |
|  3 | 王五   | 中国四川省成都市102号          |
+----+--------+--------------------------------+
3 rows in set (0.00 sec)

字段值还可以继续拆分的,就不满足第一范式。

mysql> create table student3(
    ->id int primary  key,
    ->name varchar(20),
    ->cuntry varchar(30),
    ->povince varchar(30),
    ->city varchar(30),
    ->details varchar(30)
    ->);
Query OK, 0 rows affected (0.01 sec)

mysql> desc student3;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id      | int(11)     | NO   | PRI | NULL    |       |
| name    | varchar(20) | YES  |     | NULL    |       |
| cuntry  | varchar(30) | YES  |     | NULL    |       |
| povince | varchar(30) | YES  |     | NULL    |       |
| city    | varchar(30) | YES  |     | NULL    |       |
| details | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> select * from student3;
+----+--------+--------+-----------+-----------+---------+
| id | name   | cuntry | povince   | city      | details |
+----+--------+--------+-----------+-----------+---------+
|  1 | 张三   | 中国   | 四川省    | 成都市    | 100号   |
|  2 | 李四   | 中国   | 四川省    | 成都市    | 101号   |
|  3 | 王五   | 中国   | 四川省    | 成都市    | 102号   |
+----+--------+--------+-----------+-----------+---------+
3 rows in set (0.00 sec)

范式,设计的越详细,对于某些实际操作可能更好,但是不一定都是好处。

2. 第二范式

必须是满足第一范式的前提下,第二范式要求,除主键外的每一列都必须完全依赖于主键。

如果要出现不完全依赖,只可能发生在复合主键的情况下。

订单表

mysql> create table myorder(
    -> product_id int,
    -> customer_id int,
    -> product_name varchar(20),
    -> customer_name varchar(20),
    -> primary key(product_id,customer_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

除主键以外的其他字段,都必须完全依赖于复合主键,如果说只依赖于主键的部分字段。

拆表

mysql> create table myorder(
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create table product(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create table customer(
    -> id int primary key,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.00 sec)

分成三个表之后,就满足第二范式

3. 第三范式 3NF

必须先满足第二范式,除开主键列的其他列之间不能有传递关系。

mysql> create table myorder(
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int,
    -> customer_phone varchar(15)
    -> );

customer_phone 与 order_id 和 customer_id 都有关系,应该将 customer_phone 放在 customer 表里面。

mysql> create table myorder(
    -> order_id int primary key,
    -> product_id int,
    -> customer_id int
    -> );

mysql> create table customer(
    -> id int primary key,
    -> name varchar(20),
    -> phone varchar(15)
    -> );

四、mysql 的查询练习

学生表

Student

学号

姓名

性别

出生年月日

所在班级

create table student(
    sno varchar(20) primary key,
    sname varchar(20) not null,
    sex VARCHAR(10) NOT NULL,
    sbirthday datetime,
    class varchar(20)
);

课程表

Course

课程号

课程名称

教师编号

create table course(
	cno varchar(20) primary key,
	cname varchar(20) not null,
	tno varchar(20) not null,
	foreign key(tno) references teacher(tno)
);

成绩表

Score

学号

课程号

成绩

create table score(
    sno varchar(20) not null,
    cno varchar(20) not null,
    degree decimal,
    foreign key(sno) references student(sno),
    /*处理表之间关系问题就会利用到foreign key*/
    foreign key(cno) references course(cno),
    primary key(sno,cno)
    /*当两个数据表形成的是多对多的关系,那么需要通过两个数据表的主键来组成联合主键,就可以确定每个数据表的其中一条记录了*/
);

教师表

Teacher

教师编号

教师名字

教师性别

出生年月日

职称

所在部门

create table teacher(
	tno varchar(20) primary key,
	tname varchar(20) not null,
	tsex varchar(10) not null,
	tbirthday datetime,
	prof varchar(20) not null,
	depart varchar(20) not null
);

往数据表中添加数据

-- 添加学生表数据
INSERT INTO student VALUES('101', '曾华', '男', '1977-09-01', '95033');
INSERT INTO student VALUES('102', '匡明', '男', '1975-10-02', '95031');
INSERT INTO student VALUES('103', '王丽', '女', '1976-01-23', '95033');
INSERT INTO student VALUES('104', '李军', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('105', '王芳', '女', '1975-02-10', '95031');
INSERT INTO student VALUES('106', '陆军', '男', '1974-06-03', '95031');
INSERT INTO student VALUES('107', '王尼玛', '男', '1976-02-20', '95033');
INSERT INTO student VALUES('108', '张全蛋', '男', '1975-02-10', '95031');
INSERT INTO student VALUES('109', '赵铁柱', '男', '1974-06-03', '95031');

-- 添加教师表数据
INSERT INTO teacher VALUES('804', '李诚', '男', '1958-12-02', '副教授', '计算机系');
INSERT INTO teacher VALUES('856', '张旭', '男', '1969-03-12', '讲师', '电子工程系');
INSERT INTO teacher VALUES('825', '王萍', '女', '1972-05-05', '助教', '计算机系');
INSERT INTO teacher VALUES('831', '刘冰', '女', '1977-08-14', '助教', '电子工程系');

-- 添加课程表数据
INSERT INTO course VALUES('3-105', '计算机导论', '825');
INSERT INTO course VALUES('3-245', '操作系统', '804');
INSERT INTO course VALUES('6-166', '数字电路', '856');
INSERT INTO course VALUES('9-888', '高等数学', '831');

-- 添加添加成绩表数据
INSERT INTO score VALUES('103', '3-105', '92');
INSERT INTO score VALUES('103', '3-245', '86');
INSERT INTO score VALUES('103', '6-166', '85');
INSERT INTO score VALUES('105', '3-105', '88');
INSERT INTO score VALUES('105', '3-245', '75');
INSERT INTO score VALUES('105', '6-166', '79');
INSERT INTO score VALUES('109', '3-105', '76');
INSERT INTO score VALUES('109', '3-245', '68');
INSERT INTO score VALUES('109', '6-166', '81');

1. 十道基本例题

#### – 查询 student 表的所有行

SELECT * FROM student;

mysql> select * from student;
+-----+-----------+-----+---------------------+-------+
| sno | sname     | sex | sbirthday           | class |
+-----+-----------+-----+---------------------+-------+
| 101 | 曾华      | 男  | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽      | 女  | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      | 男  | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳      | 女  | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军      | 男  | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛    | 男  | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋    | 男  | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+-----+---------------------+-------+

– 查询 student 表中的 name、sex 和 class 字段的所有行

SELECT sname, sex, class FROM student;

mysql> SELECT sname, sex, class FROM student;
+-----------+-----+-------+
| sname     | sex | class |
+-----------+-----+-------+
| 曾华      | 男  | 95033 |
| 匡明      | 男  | 95031 |
| 王丽      | 女  | 95033 |
| 李军      | 男  | 95033 |
| 王芳      | 女  | 95031 |
| 陆军      | 男  | 95031 |
| 王尼玛    | 男  | 95033 |
| 张全蛋    | 男  | 95031 |
| 赵铁柱    | 男  | 95031 |
+-----------+-----+-------+

– 查询 teacher 表中不重复的 depart 列

-- department: 去重查询
SELECT DISTINCT depart FROM teacher;

mysql> select distinct depart from teacher;
+-----------------+
| depart          |
+-----------------+
| 计算机系        |
| 电子工程系      |
+-----------------+
2 rows in set (0.00 sec)

– 查询 score 表中成绩在 60-80 之间的所有行(区间查询和运算符查询)

-- BETWEEN xx AND xx: 查询区间, AND 表示 "并且"
SELECT * FROM score WHERE degree BETWEEN 60 AND 80;
SELECT * FROM score WHERE degree > 60 AND degree < 80;

mysql> select * from score where degree between 60 and 80;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
+-----+-------+--------+
4 rows in set (0.00 sec)

– 查询 score 表中成绩为 85, 86 或 88 的行

-- IN: 查询规定中的多个值
SELECT * FROM score WHERE degree IN (85, 86, 88);

mysql> select * from score where degree in (85,86,88);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
3 rows in set (0.00 sec)

– 查询 student 表中 ‘95031’ 班或性别为 ‘女’ 的所有行

-- or: 表示或者关系
SELECT * FROM student WHERE class = '95031' or sex = '女';

mysql> select * from student where class='95031' or sex = '女';
+-----+-----------+-----+---------------------+-------+
| sno | sname     | sex | sbirthday           | class |
+-----+-----------+-----+---------------------+-------+
| 102 | 匡明      | 男  | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽      | 女  | 1976-01-23 00:00:00 | 95033 |
| 105 | 王芳      | 女  | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军      | 男  | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 男  | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+-----+---------------------+-------+
6 rows in set (0.00 sec)

– 以 class 降序的方式查询 student 表的所有行

-- DESC: 降序,从高到低
SELECT * FROM student ORDER BY class DESC;

mysql> select * from student order by class desc;
+-----+-----------+-----+---------------------+-------+
| sno | sname     | sex | sbirthday           | class |
+-----+-----------+-----+---------------------+-------+
| 101 | 曾华      | 男  | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽      | 女  | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      | 男  | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛    | 男  | 1976-02-20 00:00:00 | 95033 |
| 102 | 匡明      | 男  | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳      | 女  | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军      | 男  | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 男  | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 00:00:00 | 95031 |
+-----+-----------+-----+---------------------+-------+
9 rows in set (0.00 sec)
-- ASC(默认): 升序,从低到高
SELECT * FROM student ORDER BY class ASC;
mysql> select * from student order by class asc;/*一般默认升序,所以asc不用写*/
+-----+-----------+-----+---------------------+-------+
| sno | sname     | sex | sbirthday           | class |
+-----+-----------+-----+---------------------+-------+
| 102 | 匡明      | 男  | 1975-10-02 00:00:00 | 95031 |
| 105 | 王芳      | 女  | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军      | 男  | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋    | 男  | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱    | 男  | 1974-06-03 00:00:00 | 95031 |
| 101 | 曾华      | 男  | 1977-09-01 00:00:00 | 95033 |
| 103 | 王丽      | 女  | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军      | 男  | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛    | 男  | 1976-02-20 00:00:00 | 95033 |
+-----+-----------+-----+---------------------+-------+
9 rows in set (0.00 sec)

– 以 cno 升序、degree 降序查询 score 表的所有行

SELECT * FROM score ORDER BY cno ASC, degree DESC;
mysql> select * from score order by cno asc, degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 103 | 6-166 |     85 |
| 109 | 6-166 |     81 |
| 105 | 6-166 |     79 |
+-----+-------+--------+
9 rows in set (0.00 sec)
/*先以cno升序,再以sno降序,8.0版本以下这里asc必须写*/

– 查询 “95031” 班的学生人数

-- COUNT: 统计
SELECT COUNT(*) FROM student WHERE class = '95031';
mysql> select count(*) from student where class ='95031';
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

– 查询 score 表中的最高分的学生学号和课程编号(子查询或排序查询)。

-- (SELECT MAX(degree) FROM score): 子查询,算出最高分
SELECT sno, cno FROM score WHERE degree = (SELECT MAX(degree) FROM score);

mysql> select sno,cno from score where degree=(select max(degree) from score);
+-----+-------+
| sno | cno   |
+-----+-------+
| 103 | 3-105 |
+-----+-------+
1 row in set (0.00 sec)

-- 1 找到最高分
select max(degree) from score;
-- 2 找到最好分的 sno和 cno
select sno,cno from score where degree=(select max(degree) from score);

– 排序查询

-- LIMIT r, n: 表示从第r行开始,查询n条数据
SELECT sno, cno, degree FROM score ORDER BY degree DESC LIMIT 0, 1;

mysql> SELECT sno, cno, degree FROM score ORDER BY degree DESC LIMIT 0, 1;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.00 sec)
/*排序的弊端在于如果有两个最高分,就只能显示一个,因为这是通过排序查询最上面的那个数据*/

2. 分组计算平均成绩

查询每门课的平均成绩。

-- AVG: 平均值
SELECT AVG(degree) FROM score WHERE cno = '3-105';
SELECT AVG(degree) FROM score WHERE cno = '3-245';
SELECT AVG(degree) FROM score WHERE cno = '6-166';

-- GROUP BY: 分组查询
mysql> SELECT cno, AVG(degree) FROM score GROUP BY cno;
+-------+-------------+
| cno   | AVG(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)

3. 分组条件与模糊查询

查询 score 表中至少有 2 名学生选修,并以 3 开头的课程的平均分数。

SELECT * FROM score;
-- cno 课程编号
+------+-------+--------+
| sno  | cno   | degree |
+------+-------+--------+
| 103  | 3-105 |     92 |
| 103  | 3-245 |     86 |
| 103  | 6-166 |     85 |
| 105  | 3-105 |     88 |
| 105  | 3-245 |     75 |
| 105  | 6-166 |     79 |
| 109  | 3-105 |     76 |
| 109  | 3-245 |     68 |
| 109  | 6-166 |     81 |
+------+-------+--------+

分析表发现,至少有 2 名学生选修的课程是 3-105 、3-245 、6-166 ,以 3 开头的课程是 3-105 、3-245 。也就是说,我们要查询所有 3-105 和 3-245 的 degree 平均分。

-- 首先把 c_no, AVG(degree) 通过分组查询出来
SELECT cno, AVG(degree) FROM score GROUP BY cno
+-------+-------------+
| cno   | AVG(degree) |
+-------+-------------+
| 3-105 |     85.3333 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+

-- 再查询出至少有 2 名学生选修的课程
-- HAVING: 表示持有
HAVING COUNT(cno) >= 2

-- 并且是以 3 开头的课程
-- LIKE 表示模糊查询,"%" 是一个通配符,匹配 "3" 后面的任意字符。
AND cno LIKE '3%';

-- 把前面的SQL语句拼接起来,
-- 后面加上一个 COUNT(*),表示将每个分组的个数也查询出来。
SELECT cno, AVG(degree), COUNT(*) FROM score GROUP BY cno
HAVING COUNT(cno) >= 2 AND cno LIKE '3%';
+-------+-------------+----------+
| cno   | AVG(degree) | COUNT(*) |
+-------+-------------+----------+
| 3-105 |     85.3333 |        3 |
| 3-245 |     76.3333 |        3 |
+-------+-------------+----------+

4. 查询分数大于 70,小于 90 的 sno 列。

mysql> select sno,degree from score where degree >70 and degree <90;
+-----+--------+
| sno | degree |
+-----+--------+
| 103 |     86 |
| 103 |     85 |
| 105 |     88 |
| 105 |     75 |
| 105 |     79 |
| 109 |     76 |
| 109 |     81 |
+-----+--------+
7 rows in set (0.00 sec)

5. 多表查询 - 1

查询所有学生的 sname、cno 和 degree 列。

mysql> select sno,sname from student;
+-----+--------+
| sno | sname  |
+-----+--------+
| 101 | 曾华   |
| 102 | 匡明   |
| 103 | 王丽   |
| 104 | 李军   |
| 105 | 王芳   |
| 106 | 陆军   |
| 107 | 王尼玛 |
| 108 | 张全蛋 |
| 109 | 赵铁柱 |
+-----+--------+
9 rows in set (0.00 sec)

mysql> select sno,cno,degree from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
9 rows in set (0.00 sec)

mysql> select sname,cno,degree from score,student where student.sno = score.sno;
+--------+-------+--------+
| sname  | cno   | degree |
+--------+-------+--------+
| 王丽   | 3-105 |     92 |
| 王丽   | 3-245 |     86 |
| 王丽   | 6-166 |     85 |
| 王芳   | 3-105 |     88 |
| 王芳   | 3-245 |     75 |
| 王芳   | 6-166 |     79 |
| 赵铁柱 | 3-105 |     76 |
| 赵铁柱 | 3-245 |     68 |
| 赵铁柱 | 6-166 |     81 |
+--------+-------+--------+
9 rows in set (0.00 sec)

6. 多表查询 - 2

查询所有学生的 sno、cname 和 degree 列。

mysql> select sno,cname,degree from course,score
    -> where score.cno=course.cno order by sno asc;
+-----+------------+--------+
| sno | cname      | degree |
+-----+------------+--------+
| 103 | 计算机导论 |     92 |
| 103 | 操作系统   |     86 |
| 103 | 数字电路   |     85 |
| 105 | 计算机导论 |     88 |
| 105 | 操作系统   |     75 |
| 105 | 数字电路   |     79 |
| 109 | 计算机导论 |     76 |
| 109 | 操作系统   |     68 |
| 109 | 数字电路   |     81 |
+-----+------------+--------+
9 rows in set (0.00 sec)

7. 三表关联查询

查询所有学生的 sname、cname 和 degree 列。

只有 score 表中关联学生的学号和课堂号,我们只要围绕着 score 这张表查询就好了。

mysql> select sname,cname,degree from course,score,student
    -> where course.cno=score.cno and student.sno=score.sno;
+--------+------------+--------+
| sname  | cname      | degree |
+--------+------------+--------+
| 王丽   | 计算机导论 |     92 |
| 王丽   | 操作系统   |     86 |
| 王丽   | 数字电路   |     85 |
| 王芳   | 计算机导论 |     88 |
| 王芳   | 操作系统   |     75 |
| 王芳   | 数字电路   |     79 |
| 赵铁柱 | 计算机导论 |     76 |
| 赵铁柱 | 操作系统   |     68 |
| 赵铁柱 | 数字电路   |     81 |
+--------+------------+--------+
9 rows in set (0.00 sec)

通过共同字段的相等来进行联系。

mysql> select sname,cname,degree,student.sno as stu_sno,score.sno,course.cno as cou_cno,score.cno from course,score,student
    -> where course.cno=score.cno and student.sno=score.sno;
+--------+------------+--------+---------+-----+---------+-------+
| sname  | cname      | degree | stu_sno | sno | cou_cno | cno   |
+--------+------------+--------+---------+-----+---------+-------+
| 王丽   | 计算机导论 |     92 | 103     | 103 | 3-105   | 3-105 |
| 王丽   | 操作系统   |     86 | 103     | 103 | 3-245   | 3-245 |
| 王丽   | 数字电路   |     85 | 103     | 103 | 6-166   | 6-166 |
| 王芳   | 计算机导论 |     88 | 105     | 105 | 3-105   | 3-105 |
| 王芳   | 操作系统   |     75 | 105     | 105 | 3-245   | 3-245 |
| 王芳   | 数字电路   |     79 | 105     | 105 | 6-166   | 6-166 |
| 赵铁柱 | 计算机导论 |     76 | 109     | 109 | 3-105   | 3-105 |
| 赵铁柱 | 操作系统   |     68 | 109     | 109 | 3-245   | 3-245 |
| 赵铁柱 | 数字电路   |     81 | 109     | 109 | 6-166   | 6-166 |
+--------+------------+--------+---------+-----+---------+-------+
9 rows in set (0.00 sec)

再把 cno 替换成 course 表中的 name 字段:

-- 课程表
SELECT no, name FROM course;
+-------+-----------------+
| no    | name            |
+-------+-----------------+
| 3-105 | 计算机导论      |
| 3-245 | 操作系统        |
| 6-166 | 数字电路        |
| 9-888 | 高等数学        |
+-------+-----------------+

-- 由于字段名存在重复,使用 "表名.字段名 as 别名" 代替。
SELECT student.name as sname, course.name as cname, degree 
FROM student, score, course
WHERE student.NO = score.sno
AND score.cno = course.no;

8. 子查询加分组求平均分

查询“95031”班学生每门课的平均成绩。

mysql> select cno,degree from student,score where class=95031 and student.sno=score.sno;-- 先选班在匹配
+-------+--------+
| cno   | degree |
+-------+--------+
| 3-105 |     88 |
| 3-245 |     75 |
| 6-166 |     79 |
| 3-105 |     76 |
| 3-245 |     68 |
| 6-166 |     81 |
+-------+--------+
6 rows in set (0.00 sec)

mysql> select cno,avg(degree) 
    -> from student,score 
    -> where class=95031 and student.sno=score.sno 
    -> group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+
3 rows in set (0.00 sec)
mysql> select * from score where sno in (select sno from student where class='95031');-- 直接在选好的班里匹配
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
6 rows in set (0.00 sec)

mysql> select cno,avg(degree)
    -> from score
    -> where sno in (select sno from student where class='95031')
    -> group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     82.0000 |
| 3-245 |     71.5000 |
| 6-166 |     80.0000 |
+-------+-------------+
3 rows in set (0.00 sec)

9. 子查询

查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录。

mysql> select * from score 
where degree>(select degree from score where sno=109 and cno='3-105') 
and cno='3-105';
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
2 rows in set (0.00 sec)

10.year 函数与带 in 关键字的子查询

查询和学号为 108、101 的同学同年出生的所有学生的 sno、sname 和是 sbirthday 列。

-- 相同生日
mysql> select sno,sname,sbirthday from student where sbirthday in (select sbirthday from student where sno=108 or sno=101);
+-----+--------+---------------------+
| sno | sname  | sbirthday           |
+-----+--------+---------------------+
| 101 | 曾华   | 1977-09-01 00:00:00 |
| 105 | 王芳   | 1975-02-10 00:00:00 |
| 108 | 张全蛋 | 1975-02-10 00:00:00 |
+-----+--------+---------------------+
3 rows in set (0.00 sec)
-- 同一年,year 是特殊字
mysql> select sno,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));
+-----+--------+---------------------+
| sno | sname  | sbirthday           |
+-----+--------+---------------------+
| 101 | 曾华   | 1977-09-01 00:00:00 |
| 102 | 匡明   | 1975-10-02 00:00:00 |
| 105 | 王芳   | 1975-02-10 00:00:00 |
| 108 | 张全蛋 | 1975-02-10 00:00:00 |
+-----+--------+---------------------+
4 rows in set (0.00 sec)

11. 多层嵌套子查询

查询张旭教师任课的学生成绩。

/*多层嵌套查询*/
mysql> select tno from teacher where tname ='张旭';
+-----+
| tno |
+-----+
| 856 |
+-----+
1 row in set (0.00 sec)

mysql> select cno from course where tno=(select tno from teacher where tname ='张旭');
+-------+
| cno   |
+-------+
| 6-166 |
+-------+
1 row in set (0.00 sec)

mysql> select * from score where cno=(select cno from course where tno=(select tno from teacher where tname ='张旭'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
3 rows in set (0.00 sec)
-- 若老师教了不止一门课程
mysql> select * from score where cno in (select cno from course where tno=(select tno from teacher where tname ='张旭'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 6-166 |     85 |
| 105 | 6-166 |     79 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
3 rows in set (0.00 sec)

12. 多表查询

查询选修某课程的同学人数多于 5 人的教师姓名。

-- 先插入这三条数据
insert into score values('101','3-105','90');
insert into score values('102','3-105','91');
insert into score values('104','3-105','89');

mysql> select cno from score group by cno having count(*)>5;
+-------+
| cno   |
+-------+
| 3-105 |
+-------+
1 row in set (0.00 sec)

mysql> select * from teacher;
+-----+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday           | prof   | depart     |
+-----+-------+------+---------------------+--------+------------+
| 804 | 李诚  | 男   | 1958-12-02 00:00:00 | 副教授 | 计算机系   |
| 825 | 王萍  | 女   | 1972-05-05 00:00:00 | 助教   | 计算机系   |
| 831 | 刘冰  | 女   | 1977-08-14 00:00:00 | 助教   | 电子工程系 |
| 856 | 张旭  | 男   | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+
4 rows in set (0.00 sec)

mysql> select tno from course where cno= (select cno from score group by cno having count(*)>5);
+-----+
| tno |
+-----+
| 825 |
+-----+
1 row in set (0.00 sec)

mysql> select tname from teacher where tno= (select tno from course where cno= (select cno from score group by cno having count(*)>5));
+-------+
| tname |
+-------+
| 王萍  |
+-------+
1 row in set (0.00 sec)

13.IN 表示或者关系

查询 95033 班和 95031 班全体学生的记录。

mysql> select * from student where class in (95033,95031);
+-----+--------+-----+---------------------+-------+
| sno | sname  | sex | sbirthday           | class |
+-----+--------+-----+---------------------+-------+
| 101 | 曾华   | 男  | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明   | 男  | 1975-10-02 00:00:00 | 95031 |
| 103 | 王丽   | 女  | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军   | 男  | 1976-02-20 00:00:00 | 95033 |
| 105 | 王芳   | 女  | 1975-02-10 00:00:00 | 95031 |
| 106 | 陆军   | 男  | 1974-06-03 00:00:00 | 95031 |
| 107 | 王尼玛 | 男  | 1976-02-20 00:00:00 | 95033 |
| 108 | 张全蛋 | 男  | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男  | 1974-06-03 00:00:00 | 95031 |
+-----+--------+-----+---------------------+-------+
9 rows in set (0.00 sec)

14.where 条件查询

查询存在有 85 分以上成绩的课程 cno

mysql> select cno from score where degree>85;
+-------+
| cno   |
+-------+
| 3-105 |
| 3-105 |
| 3-105 |
| 3-245 |
| 3-105 |
| 3-105 |
+-------+
6 rows in set (0.00 sec)

15. 子查询 - 3

查询出计算机系教师所教课程的成绩表

-- 先看计算机系的老师有哪些
mysql> select * from teacher where depart ='计算机系';
+-----+-------+------+---------------------+--------+----------+
| tno | tname | tsex | tbirthday           | prof   | depart   |
+-----+-------+------+---------------------+--------+----------+
| 804 | 李诚  | 男   | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
| 825 | 王萍  | 女   | 1972-05-05 00:00:00 | 助教   | 计算机系 |
+-----+-------+------+---------------------+--------+----------+
2 rows in set (0.00 sec)
-- 再看他们教了哪些课程
mysql> select cno from course where tno in (select tno from teacher where depart ='计算机系');
+-------+
| cno   |
+-------+
| 3-245 |
| 3-105 |
+-------+
2 rows in set (0.00 sec)
-- 再看所教课程对应的分数
mysql> select * from score where cno in (select cno from course where tno in (select tno from teacher where depart ='计算机系'));
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-245 |     86 |
| 105 | 3-245 |     75 |
| 109 | 3-245 |     68 |
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
9 rows in set (0.00 sec)

16.UNION 和 NOT IN 的使用

查询计算机系与电子工程系不同职称的教师的 tname 和 prof。

mysql> select prof from teacher where depart ='电子工程系';
+------+
| prof |
+------+
| 助教 |
| 讲师 |
+------+
2 rows in set (0.00 sec)

mysql> select * from teacher where depart='计算机系' and prof not in (select prof from teacher where depart ='电子工程系');
+-----+-------+------+---------------------+--------+----------+
| tno | tname | tsex | tbirthday           | prof   | depart   |
+-----+-------+------+---------------------+--------+----------+
| 804 | 李诚  | 男   | 1958-12-02 00:00:00 | 副教授 | 计算机系 |
+-----+-------+------+---------------------+--------+----------+
1 row in set (0.00 sec)

mysql> select * from teacher where depart='电子工程系' and prof not in (select prof from teacher where depart ='计算机系');
+-----+-------+------+---------------------+------+------------+
| tno | tname | tsex | tbirthday           | prof | depart     |
+-----+-------+------+---------------------+------+------------+
| 856 | 张旭  | 男   | 1969-03-12 00:00:00 | 讲师 | 电子工程系 |
+-----+-------+------+---------------------+------+------------+
1 row in set (0.00 sec)
-- union求并集
mysql> select * from teacher where depart='计算机系' and prof not in (select prof from teacher where depart ='电子工程系')
    -> union
    -> select * from teacher where depart='电子工程系' and prof not in (select prof from teacher where depart ='计算机系');
+-----+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday           | prof   | depart     |
+-----+-------+------+---------------------+--------+------------+
| 804 | 李诚  | 男   | 1958-12-02 00:00:00 | 副教授 | 计算机系   |
| 856 | 张旭  | 男   | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+
2 rows in set (0.00 sec)

17.ANY 表示至少一个 - DESC (降序)

查询选修编号为 3-105 课程且成绩至少高于选修编号为 3-245 的同学的 cno、sno、degree,并按 degree 从高到低次序排序。

mysql> select degree from score where cno='3-245';
+--------+
| degree |
+--------+
|     86 |
|     75 |
|     68 |
+--------+
3 rows in set (0.00 sec)

mysql> select * from score where cno ='3-105'and degree > any (select degree from score where cno='3-245');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
6 rows in set (0.00 sec)

-- 至少? 大于其中至少一个,any
-- ANY: 符合SQL语句中的任意条件。
-- 也就是说,在 3-105 成绩中,只要有一个大于从 3-245 筛选出来的任意行就符合条件,
-- 最后根据降序查询结果。
mysql> select * from score where cno ='3-105'and degree > any (select degree from score where cno='3-245')
    -> order by degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 102 | 3-105 |     91 |
| 101 | 3-105 |     90 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
6 rows in set (0.00 sec)

18. 表示所有的 ALL

查询课程 3-105 且成绩高于 3-245score 表。

-- 只需对上一道题稍作修改。
-- ALL: 符合SQL语句中的所有条件。
-- 也就是说,在 3-105 每一行成绩中,都要大于从 3-245 筛选出来全部行才算符合条件。

mysql> select * from score where cno ='3-105'and degree > all(select degree from score where cno='3-245')
    -> order by degree desc;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
| 102 | 3-105 |     91 |
| 101 | 3-105 |     90 |
| 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |
+-----+-------+--------+
5 rows in set (0.00 sec)

19.UNION 求并集

查询所有教师和同学的 name、sex 和 birthday。

mysql> select tname as name,tsex as sex,tbirthday as birthday from teacher
    -> union
    -> select sname,sex,sbirthday from student;
+--------+-----+---------------------+
| name   | sex | birthday            |
+--------+-----+---------------------+
| 李诚   | 男  | 1958-12-02 00:00:00 |
| 王萍   | 女  | 1972-05-05 00:00:00 |
| 刘冰   | 女  | 1977-08-14 00:00:00 |
| 张旭   | 男  | 1969-03-12 00:00:00 |
| 曾华   | 男  | 1977-09-01 00:00:00 |
| 匡明   | 男  | 1975-10-02 00:00:00 |
| 王丽   | 女  | 1976-01-23 00:00:00 |
| 李军   | 男  | 1976-02-20 00:00:00 |
| 王芳   | 女  | 1975-02-10 00:00:00 |
| 陆军   | 男  | 1974-06-03 00:00:00 |
| 王尼玛 | 男  | 1976-02-20 00:00:00 |
| 张全蛋 | 男  | 1975-02-10 00:00:00 |
| 赵铁柱 | 男  | 1974-06-03 00:00:00 |
+--------+-----+---------------------+
13 rows in set (0.00 sec)

20. 复制表的数据作为条件查询

查询某课程成绩比该课程平均成绩低的 score 表。

-- 查询平均分
mysql> select cno,avg(degree) from score group by cno;
+-------+-------------+
| cno   | avg(degree) |
+-------+-------------+
| 3-105 |     87.6667 |
| 3-245 |     76.3333 |
| 6-166 |     81.6667 |
+-------+-------------+
3 rows in set (0.00 sec)

-- 查询 score 表
mysql> select * from score;
a                                    b
+-----+-------+--------+             +-----+-------+--------+
| sno | cno   | degree |             | sno | cno   | degree |
+-----+-------+--------+             +-----+-------+--------+
| 101 | 3-105 |     90 |             | 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |             | 102 | 3-105 |     91 |
| 103 | 3-105 |     92 |             | 103 | 3-105 |     92 |
| 103 | 3-245 |     86 |             | 103 | 3-245 |     86 |
| 103 | 6-166 |     85 |             | 103 | 6-166 |     85 |
| 104 | 3-105 |     89 |             | 104 | 3-105 |     89 |
| 105 | 3-105 |     88 |             | 105 | 3-105 |     88 |
| 105 | 3-245 |     75 |             | 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |             | 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |             | 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |             | 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |             | 109 | 6-166 |     81 |
+-----+-------+--------+             +-----+-------+--------+
12 rows in set (0.00 sec)

-- 将表 b 作用于表 a 中查询数据
-- b表是a表的复制表,求的是从a表中依次提出一个课程然后与b表中所有与a表的课程号相同的成绩的平均值作比较,小于的就输出
-- a.cno=b.cno是将b表中所有等于a.cno的成绩形成一个组,然后求平均值,求平均值必须分组。
mysql> select * from score a 
    -> where degree<
    -> (select avg(degree) from score b where a.cno=b.cno);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 105 | 3-245 |     75 |
| 105 | 6-166 |     79 |
| 109 | 3-105 |     76 |
| 109 | 3-245 |     68 |
| 109 | 6-166 |     81 |
+-----+-------+--------+
5 rows in set (0.00 sec)

21. 子查询 - 4

查询所有任课 (在 course 表里有课程) 教师的 tname 和 depart 。

mysql> select tname,depart from teacher 
where tno in (select tno from course);
+-------+------------+
| tname | depart     |
+-------+------------+
| 李诚  | 计算机系   |
| 王萍  | 计算机系   |
| 刘冰  | 电子工程系 |
| 张旭  | 电子工程系 |
+-------+------------+
4 rows in set (0.00 sec)

22. 条件加组筛选

查询至少有两名男生的班号

mysql> select class from student 
where sex='男' 
group by class having count(*)>1;
+-------+
| class |
+-------+
| 95033 |
| 95031 |
+-------+
2 rows in set (0.01 sec)

需要注意

当同时含有where 子句group by 子句having 子句聚集函数时,执行顺序如下:

  • 执行where 子句查找符合条件的数据;
  • 使用group by 子句对数据进行分组;对 group by 子句形成的组运行聚集函数计算每一组的值;
  • 最后用having 子句去掉不符合条件的组。

having 子句中的每一个元素也必须出现在 select 列表中。有些数据库例外,如 oracle.
having 子句和 where 子句都可以用来设定限制条件以使查询结果满足一定的条件限制。

having 子句限制的是组,而不是行。where 子句中不能使用聚集函数,而 having 子句中可以。

group by class having count(*)>1; 的意思是以班级分类筛选出分组以后,每一组的行数大于 1

23.not like 模糊查询取反

查询 student 表中不姓王的同学记录

mysql> select * from student where sname not like '王%';
+-----+--------+-----+---------------------+-------+
| sno | sname  | sex | sbirthday           | class |
+-----+--------+-----+---------------------+-------+
| 101 | 曾华   | 男  | 1977-09-01 00:00:00 | 95033 |
| 102 | 匡明   | 男  | 1975-10-02 00:00:00 | 95031 |
| 104 | 李军   | 男  | 1976-02-20 00:00:00 | 95033 |
| 106 | 陆军   | 男  | 1974-06-03 00:00:00 | 95031 |
| 108 | 张全蛋 | 男  | 1975-02-10 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男  | 1974-06-03 00:00:00 | 95031 |
+-----+--------+-----+---------------------+-------+
6 rows in set (0.00 sec)

24.year 函数和 now 函数

查询 student 表中每个学生的姓名和年龄

-- 年龄=当前年份-出生年份
mysql> select year(now());
+-------------+
| year(now()) |
+-------------+
|        2019 |
+-------------+
1 row in set (0.01 sec)

mysql> select year(sbirthday) from student;
+-----------------+
| year(sbirthday) |
+-----------------+
|            1977 |
|            1975 |
|            1976 |
|            1976 |
|            1975 |
|            1974 |
|            1976 |
|            1975 |
|            1974 |
+-----------------+
9 rows in set (0.03 sec)

mysql> select sname,year(now())-year(sbirthday) 
as '年龄' 
from student;
+--------+------+
| sname  | 年龄 |
+--------+------+
| 曾华   |   42 |
| 匡明   |   44 |
| 王丽   |   43 |
| 李军   |   43 |
| 王芳   |   44 |
| 陆军   |   45 |
| 王尼玛 |   43 |
| 张全蛋 |   44 |
| 赵铁柱 |   45 |
+--------+------+
9 rows in set (0.00 sec)

25.max 和 min 函数

查询 student 表中最大和最小的 birthday 值。

mysql> select max(sbirthday) as '最大',min(sbirthday) as '最小' from student;
+---------------------+---------------------+
| 最大                | 最小                |
+---------------------+---------------------+
| 1977-09-01 00:00:00 | 1974-06-03 00:00:00 |
+---------------------+---------------------+
1 row in set (0.00 sec)

26. 多字段排序

以 class 和 birthday 从大到小的顺序查询 student 表。

-- 先按照class的大小排,排完了再按照sbirthday的大小排。
mysql> select * from student order by class desc,sbirthday;
+-----+--------+-----+---------------------+-------+
| sno | sname  | sex | sbirthday           | class |
+-----+--------+-----+---------------------+-------+
| 103 | 王丽   | 女  | 1976-01-23 00:00:00 | 95033 |
| 104 | 李军   | 男  | 1976-02-20 00:00:00 | 95033 |
| 107 | 王尼玛 | 男  | 1976-02-20 00:00:00 | 95033 |
| 101 | 曾华   | 男  | 1977-09-01 00:00:00 | 95033 |
| 106 | 陆军   | 男  | 1974-06-03 00:00:00 | 95031 |
| 109 | 赵铁柱 | 男  | 1974-06-03 00:00:00 | 95031 |
| 105 | 王芳   | 女  | 1975-02-10 00:00:00 | 95031 |
| 108 | 张全蛋 | 男  | 1975-02-10 00:00:00 | 95031 |
| 102 | 匡明   | 男  | 1975-10-02 00:00:00 | 95031 |
+-----+--------+-----+---------------------+-------+
9 rows in set (0.00 sec)

27. 子查询 - 5

查询男教师及其所上的课程

mysql> select tno from teacher where tsex = '男';
+-----+
| tno |
+-----+
| 804 |
| 856 |
+-----+
2 rows in set (0.00 sec)

mysql> select * from course where tno in (select tno from teacher where tsex = '男');
+-------+----------+-----+
| cno   | cname    | tno |
+-------+----------+-----+
| 3-245 | 操作系统 | 804 |
| 6-166 | 数字电路 | 856 |
+-------+----------+-----+
2 rows in set (0.01 sec)

28.max 函数与子查询

查询最高分的同学的 sno、cno 和 degree 列。

mysql> select max(degree) from score;
+-------------+
| max(degree) |
+-------------+
|          92 |
+-------------+
1 row in set (0.00 sec)

mysql> select * from score where degree=(select max(degree) from score);
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 103 | 3-105 |     92 |
+-----+-------+--------+
1 row in set (0.00 sec)

29. 子查询 - 6

查询和 “李军” 同性别的所有同学 name 。

mysql> select sex from student where sname='李军';
+-----+
| sex |
+-----+
| 男  |
+-----+
1 row in set (0.00 sec)

mysql> select sname from student where sex=(select sex from student where sname='李军');
+--------+
| sname  |
+--------+
| 曾华   |
| 匡明   |
| 李军   |
| 陆军   |
| 王尼玛 |
| 张全蛋 |
| 赵铁柱 |
+--------+
7 rows in set (0.00 sec)

30. 子查询 - 7

查询和 “李军” 同性别且同班的同学 name 。

mysql> select sname from student 
where sex=(select sex from student where sname='李军') 
and class=(select class from student where sname='李军');
+--------+
| sname  |
+--------+
| 曾华   |
| 李军   |
| 王尼玛 |
+--------+
3 rows in set (0.00 sec)

31. 子查询 - 8

查询所有选修 “计算机导论” 课程的 “男” 同学成绩表。

mysql> select * from score 
where cno=(select cno from course where cname='计算机导论')
and sno in (select sno from student where sex='男');
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |     90 |
| 102 | 3-105 |     91 |
| 104 | 3-105 |     89 |
| 109 | 3-105 |     76 |
+-----+-------+--------+
4 rows in set (0.00 sec)

32. 按等级查询

建立一个 grade 表代表学生的成绩等级,并插入数据:

mysql> create table grade(
    -> low int(3),
    -> upp int(3),
    -> grade char(1)
    -> );
    
INSERT INTO grade VALUES (90, 100, 'A');
INSERT INTO grade VALUES (80, 89, 'B');
INSERT INTO grade VALUES (70, 79, 'C');
INSERT INTO grade VALUES (60, 69, 'D');
INSERT INTO grade VALUES (0, 59, 'E');    

SELECT * FROM grade;
+------+------+-------+
| low  | upp  | grade |
+------+------+-------+
|   90 |  100 | A     |
|   80 |   89 | B     |
|   70 |   79 | C     |
|   60 |   69 | D     |
|    0 |   59 | E     |
+------+------+-------+

查询所有学生的 sno 、cno 和 grade 列。

思路是,使用区间 (BETWEEN) 查询,判断学生的成绩 (degree) 在 grade 表的 low 和 upp 之间。

mysql> select sno,cno,grade from score,grade 
where degree between low and upp;
+-----+-------+-------+
| sno | cno   | grade |
+-----+-------+-------+
| 101 | 3-105 | A     |
| 102 | 3-105 | A     |
| 103 | 3-105 | A     |
| 103 | 3-245 | B     |
| 103 | 6-166 | B     |
| 104 | 3-105 | B     |
| 105 | 3-105 | B     |
| 105 | 3-245 | C     |
| 105 | 6-166 | C     |
| 109 | 3-105 | C     |
| 109 | 3-245 | D     |
| 109 | 6-166 | B     |
+-----+-------+-------+
12 rows in set (0.00 sec)

MySQL 的四种连接查询

内连接

  • inner join 或者 join

外连接

  • 左连接 left join 或者 left outer join

  • 右连接 right join 或 right outer join

  • 完全外连接 full join 或者 full outer join

CREATE DATABASE testJoin;
CREATE TABLE person (
    id INT,
    name VARCHAR(20),
    cardId INT
);

CREATE TABLE card (
    id INT,
    name VARCHAR(20)
);

INSERT INTO card VALUES (1, '饭卡'), (2, '建行卡'), (3, '农行卡'), (4, '工商卡'), (5, '邮政卡');
INSERT INTO person VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 6);

mysql> select * from card;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 饭卡   |
|    2 | 建行卡 |
|    3 | 农行卡 |
|    4 | 工商卡 |
|    5 | 邮政卡 |
+------+--------+
5 rows in set (0.00 sec)

mysql> select * from person;
+------+------+--------+
| id   | name | cardId |
+------+------+--------+
|    1 | 张三 |      1 |
|    2 | 李四 |      3 |
|    3 | 王五 |      6 |
+------+------+--------+
3 rows in set (0.00 sec)

分析两张表发现,person 表并没有为 cardId 字段设置一个在 card 表中对应的 id 外键。如果设置了的话,person 中 cardId 字段值为 6 的行就插不进去,因为该 cardId 值在 card 表中并没有。

  1. inner join 查询(内连接)
mysql> select * from person inner join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
+------+------+--------+------+--------+
2 rows in set (0.00 sec)
-- inner 可以省略

内联查询,其实就是两张表中的数据,通过某个字段相对,查询出相关记录数据。

  1. left join(左外连接)
mysql> select * from person left join card on person.cardId=card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
|    3 | 王五 |      6 | NULL | NULL   |
+------+------+--------+------+--------+
3 rows in set (0.01 sec)

左外连接,会把左边表里面的所有数据取出来,而右边表中的数据,如果有相等的,就显示出来,如果没有就会补 NULL

  1. right join(右外连接)
mysql> select * from person right join card on person.cardID= card.id;
+------+------+--------+------+--------+
| id   | name | cardId | id   | name   |
+------+------+--------+------+--------+
|    1 | 张三 |      1 |    1 | 饭卡   |
|    2 | 李四 |      3 |    3 | 农行卡 |
| NULL | NULL |   NULL |    2 | 建行卡 |
| NULL | NULL |   NULL |    4 | 工商卡 |
| NULL | NULL |   NULL |    5 | 邮政卡 |
+------+------+--------+------+--------+
5 rows in set (0.00 sec)

右外连接,会把右边表里面的所有数据取出来,而左边表中的数据,如果有相等的,就显示出来,如果没有就会补 NULL

  1. 全外链接

完整显示两张表的全部数据。

-- MySQL 不支持这种语法的全外连接
-- SELECT * FROM person FULL JOIN card on person.cardId = card.id;
-- 出现错误:
-- ERROR 1054 (42S22): Unknown column 'person.cardId' in 'on clause'

-- MySQL全连接语法,使用 UNION 将两张表合并在一起。
SELECT * FROM person LEFT JOIN card on person.cardId = card.id
UNION
SELECT * FROM person RIGHT JOIN card on person.cardId = card.id;
+------+--------+--------+------+-----------+
| id   | name   | cardId | id   | name      |
+------+--------+--------+------+-----------+
|    1 | 张三   |      1 |    1 | 饭卡      |
|    2 | 李四   |      3 |    3 | 农行卡    |
|    3 | 王五   |      6 | NULL | NULL      |
| NULL | NULL   |   NULL |    2 | 建行卡    |
| NULL | NULL   |   NULL |    4 | 工商卡    |
| NULL | NULL   |   NULL |    5 | 邮政卡    |
+------+--------+--------+------+-----------+

五、mysql 事务

在 MySQL 中,事务其实是一个最小的不可分割的工作单元。事务能够保证一个业务的完整性

比如我们的银行转账:

-- a -> -100
UPDATE user set money = money - 100 WHERE name = 'a';

-- b -> +100
UPDATE user set money = money + 100 WHERE name = 'b';

在实际项目中,假设只有一条 SQL 语句执行成功,而另外一条执行失败了,就会出现数据前后不一致。

因此,在执行多条有关联 SQL 语句时,事务可能会要求这些 SQL 语句要么同时执行成功,要么就都执行失败。

1. 如何控制事务 - COMMIT / ROLLBACK

在 MySQL 中,事务的自动提交状态默认是开启的。

-- 查询事务的自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            1 |
+--------------+

自动提交的作用:当我们执行一条 SQL 语句的时候,其产生的效果就会立即体现出来,且不能回滚

什么是回滚?举个例子:

CREATE DATABASE bank;

USE bank;

CREATE TABLE user (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    money INT
);

INSERT INTO user VALUES (1, 'a', 1000);

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

可以看到,在执行插入语句后数据立刻生效,原因是 MySQL 中的事务自动将它提交到了数据库中。那么所谓回滚的意思就是,撤销执行过的所有 SQL 语句,使其回滚到最后一次提交数据时的状态。

在 MySQL 中使用 ROLLBACK 执行回滚:

-- 回滚到最后一次提交
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

由于所有执行过的 SQL 语句都已经被提交过了,所以数据并没有发生回滚。那如何让数据可以发生回滚?

-- 关闭自动提交
SET AUTOCOMMIT = 0;

-- 查询自动提交状态
SELECT @@AUTOCOMMIT;
+--------------+
| @@AUTOCOMMIT |
+--------------+
|            0 |
+--------------+

将自动提交关闭后,测试数据回滚:

INSERT INTO user VALUES (2, 'b', 1000);

-- 关闭 AUTOCOMMIT 后,数据的变化是在一张虚拟的临时数据表中展示,
-- 发生变化的数据并没有真正插入到数据表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

-- 数据表中的真实数据其实还是:
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

-- 由于数据还没有真正提交,可以使用回滚
ROLLBACK;

-- 再次查询
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
+----+------+-------+

那如何将虚拟的数据真正提交到数据库中?使用 COMMIT :

INSERT INTO user VALUES (2, 'b', 1000);
-- 手动提交数据(持久性),
-- 将数据真正提交到数据库中,执行后不能再回滚提交过的数据。
COMMIT;

-- 提交后测试回滚
ROLLBACK;

-- 再次查询(回滚无效了)
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

总结

  1. 自动提交
  • 查看自动提交状态:SELECT @@AUTOCOMMIT

  • 设置自动提交状态:SET AUTOCOMMIT = 0

  1. 手动提交

@@AUTOCOMMIT = 0 时,使用 COMMIT 命令提交事务。

  1. 事务回滚

@@AUTOCOMMIT = 0 时,使用 ROLLBACK 命令回滚事务。

事务的实际应用,让我们再回到银行转账项目:

-- 转账
UPDATE user set money = money - 100 WHERE name = 'a';

-- 到账
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

这时假设在转账时发生了意外,就可以使用 ROLLBACK 回滚到最后一次提交的状态:

-- 假设转账发生了意外,需要回滚。
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

这时我们又回到了发生意外之前的状态,也就是说,事务给我们提供了一个可以反悔的机会。假设数据没有发生意外,这时可以手动将数据真正提交到数据表中:COMMIT

2. 手动开启事务 - BEGIN / START TRANSACTION

事务的默认提交被开启 ( @@AUTOCOMMIT = 1 ) 后,此时就不能使用事务回滚了。但是我们还可以手动开启一个事务处理事件,使其可以发生回滚:

-- 使用 BEGIN 或者 START TRANSACTION 手动开启一个事务
-- START TRANSACTION;
BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

-- 由于手动开启的事务没有开启自动提交,
-- 此时发生变化的数据仍然是被保存在一张临时表中。
SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 测试回滚
ROLLBACK;

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |  1000 |
|  2 | b    |  1000 |
+----+------+-------+

仍然使用 COMMIT 提交数据,提交后无法再发生本次事务的回滚。

BEGIN;
UPDATE user set money = money - 100 WHERE name = 'a';
UPDATE user set money = money + 100 WHERE name = 'b';

SELECT * FROM user;
+----+------+-------+
| id | name | money |
+----+------+-------+
|  1 | a    |   900 |
|  2 | b    |  1100 |
+----+------+-------+

-- 提交数据
COMMIT;

-- 测试回滚(无效,因为表的数据已经被提交)
ROLLBACK;

3. 事务的 ACID 特征与使用

事务的四大特征:

  • A 原子性:事务是最小的单位,不可以再分割;
  • C 一致性:要求同一事务中的 SQL 语句,必须保证同时成功或者失败;
  • I 隔离性:事务 1 和 事务 2 之间是具有隔离性的;
  • D 持久性:事务一旦结束 ( COMMIT ) ,就不可以再返回了 ( ROLLBACK ) 。

4. 事务的隔离性

事务的隔离性可分为四种 (性能从低到高)

  1. READ UNCOMMITTED (读取未提交)

如果有多个事务,那么任意事务都可以看见其他事务的未提交数据

  1. READ COMMITTED (读取已提交)

只能读取到其他事务已经提交的数据

  1. REPEATABLE READ (可被重复读)

如果有多个连接都开启了事务,那么事务之间不能共享数据记录,否则只能共享已提交的记录。

  1. SERIALIZABLE (串行化)

所有的事务都会按照固定顺序执行,执行完一个事务后再继续执行下一个事务的写入操作

查看当前数据库的默认隔离级别:

-- MySQL 8.x, GLOBAL 表示系统级别,不加表示会话级别。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
SELECT @@TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                | -- MySQL的默认隔离级别,可以重复读。
+--------------------------------+

-- MySQL 5.x
SELECT @@GLOBAL.TX_ISOLATION;
SELECT @@TX_ISOLATION;

修改隔离级别:

-- 设置系统隔离级别,LEVEL 后面表示要设置的隔离级别 (READ UNCOMMITTED)。
SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

-- 查询系统隔离级别,发现已经被修改。
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-UNCOMMITTED               |
+--------------------------------+

1. 脏读

测试 READ UNCOMMITTED (读取未提交) 的隔离性:

INSERT INTO user VALUES (3, '小明', 1000);
INSERT INTO user VALUES (4, '淘宝店', 1000);

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

-- 开启一个事务操作数据
-- 假设小明在淘宝店买了一双800块钱的鞋子:
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 然后淘宝店在另一方查询结果,发现钱已到账。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

由于小明的转账是在新开启的事务上进行操作的,而该操作的结果是可以被其他事务(另一方的淘宝店)看见的,因此淘宝店的查询结果是正确的,淘宝店确认到账。但就在这时,如果小明在它所处的事务上又执行了 ROLLBACK 命令,会发生什么?

-- 小明所处的事务
ROLLBACK;

-- 此时无论对方是谁,如果再去查询结果就会发现:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

这就是所谓的脏读,一个事务读取到另外一个事务还未提交的数据。这在实际开发中是不允许出现的。

2. 不可重复读

把隔离级别设置为 READ COMMITTED

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| READ-COMMITTED                 |
+--------------------------------+

这样,再有新的事务连接进来时,它们就只能查询到已经提交过的事务数据了。但是对于当前事务来说,它们看到的还是未提交的数据,例如:

-- 正在操作数据事务(当前事务)
START TRANSACTION;
UPDATE user SET money = money - 800 WHERE name = '小明';
UPDATE user SET money = money + 800 WHERE name = '淘宝店';

-- 虽然隔离级别被设置为了 READ COMMITTED,但在当前事务中,
-- 它看到的仍然是数据表中临时改变数据,而不是真正提交过的数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+


-- 假设此时在远程开启了一个新事务,连接到数据库。
$ mysql -u root -p12345612

-- 此时远程连接查询到的数据只能是已经提交过的
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
+----+-----------+-------+

但是这样还有问题,那就是假设一个事务在操作数据时,其他事务干扰了这个事务的数据。例如:

-- 小张在查询数据的时候发现:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |   200 |
|  4 | 淘宝店    |  1800 |
+----+-----------+-------+

-- 在小张求表的 money 平均值之前,小王做了一个操作:
START TRANSACTION;
INSERT INTO user VALUES (5, 'c', 100);
COMMIT;

-- 此时表的真实数据是:
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

-- 这时小张再求平均值的时候,就会出现计算不相符合的情况:
SELECT AVG(money) FROM user;
+------------+
| AVG(money) |
+------------+
|  820.0000  |
+------------+

虽然 READ COMMITTED 让我们只能读取到其他事务已经提交的数据,但还是会出现问题,就是在读取同一个表的数据时,可能会发生前后不一致的情况。这被称为不可重复读现象 (READ COMMITTED)

3. 幻读

将隔离级别设置为 REPEATABLE READ (可被重复读取) :

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| REPEATABLE-READ                |
+--------------------------------+

测试 REPEATABLE READ ,假设在两个不同的连接上分别执行 START TRANSACTION :

-- 小张 - 成都
START TRANSACTION;
INSERT INTO user VALUES (6, 'd', 1000);

-- 小王 - 北京
START TRANSACTION;

-- 小张 - 成都
COMMIT;

当前事务开启后,没提交之前,查询不到,提交后可以被查询到。但是,在提交之前其他事务被开启了,那么在这条事务线上,就不会查询到当前有操作事务的连接。相当于开辟出一条单独的线程。

无论小张是否执行过 COMMIT ,在小王这边,都不会查询到小张的事务记录,而是只会查询到自己所处事务的记录:

SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
+----+-----------+-------+

这是因为小王在此之前开启了一个新的事务 ( START TRANSACTION ) ,那么在他的这条新事务的线上,跟其他事务是没有联系的,也就是说,此时如果其他事务正在操作数据,它是不知道的。

然而事实是,在真实的数据表中,小张已经插入了一条数据。但是小王此时并不知道,也插入了同一条数据,会发生什么呢?

INSERT INTO user VALUES (6, 'd', 1000);
-- ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'

报错了,操作被告知已存在主键为 6 的字段。这种现象也被称为幻读,一个事务提交的数据,不能被其他事务读取到

4. 串行化

顾名思义,就是所有事务的写入操作全都是串行化的。什么意思?把隔离级别修改成 SERIALIZABLE :

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.TRANSACTION_ISOLATION;
+--------------------------------+
| @@GLOBAL.TRANSACTION_ISOLATION |
+--------------------------------+
| SERIALIZABLE                   |
+--------------------------------+

还是拿小张和小王来举例:

-- 小张 - 成都
START TRANSACTION;

-- 小王 - 北京
START TRANSACTION;

-- 开启事务之前先查询表,准备操作数据。
SELECT * FROM user;
+----+-----------+-------+
| id | name      | money |
+----+-----------+-------+
|  1 | a         |   900 |
|  2 | b         |  1100 |
|  3 | 小明      |  1000 |
|  4 | 淘宝店    |  1000 |
|  5 | c         |   100 |
|  6 | d         |  1000 |
+----+-----------+-------+

-- 发现没有 7 号王小花,于是插入一条数据:
INSERT INTO user VALUES (7, '王小花', 1000);

此时会发生什么呢?由于现在的隔离级别是 SERIALIZABLE (串行化) ,串行化的意思就是:假设把所有的事务都放在一个串行的队列中,那么所有的事务都会按照固定顺序执行,执行完一个事务后再继续执行下一个事务的写入操作 ( 这意味着队列中同时只能执行一个事务的写入操作 ) 。

根据这个解释,小王在插入数据时,会出现等待状态,直到小张执行 COMMIT 结束它所处的事务,或者出现等待超时。

串行话问题是,性能特差!

READ UNCOMMITTED > READ COMMITTED > REPEATABLE READ > SERIALIZABLE

隔离级别越高,性能越差

mysql 默认隔离级别是 REPEATABLE READ