mysql的一点东西

增删改查–数据记录常见操作

–如何在数据库服务器中创建数据库

mysql> Create database test;
Query OK, 1 row affected (0.02 sec)
 
mysql> use test
Database changed
 

–如何查看某个数据库中所有的数据表

mysql> show tables;
Empty set (0.01 sec)
 

–如何创建一个数据表

CREATE TABLE pet (
name VARCHAR(20),
owner VARCHAR(20),
specials VARCHAR(20),
sex VARCHAR(1),
birth DATE,
death DATE);
Query OK, 0 rows affected (0.08 sec)
 

–查看数据表是否创建成功

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

–查看创建好的数据表的结构

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

–查看表中的记录

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

–如何往数据表中添加数据记录

mysql> INSERT INTO pet
  -> VALUES('Puffball','Diane','hamster','f','1999-03-30',NULL);
Query OK, 1 row affected (0.02 sec)
 

–再一次查询

mysql> select * from pet;
+----------+-------+----------+------+------------+-------+
| name   | owner | specials | sex | birth   | death |
+----------+-------+----------+------+------------+-------+
| Puffball | Diane | hamster | f  | 1999-03-30 | NULL |
+----------+-------+----------+------+------------+-------+
1 row in set (0.00 sec)
 
INSERT INTO pet
VALUES('旺财','周星驰','狗','公','1990-01-01',NULL);
mysql> select * from pet;
+----------+-----------+----------+------+------------+-------+
| name   | owner   | specials | sex | birth   | death |
+----------+-----------+----------+------+------------+-------+
| Puffball | Diane   | hamster | f  | 1999-03-30 | NULL |
| 旺财   | 周星驰   | 狗    | 公  | 1990-01-01 | NULL |
+----------+-----------+----------+------+------------+-------+
2 rows in set (0.00 sec)
 

–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.08 sec)
 
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| 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.02 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字节	极大文本数据

–如何往数据库里插入数据

INSERT INTO pet VALUES('Fluffy','Harold','cat','f','1993-02-04',NULL);
INSERT INTO pet VALUES('Claws','Gwen','cat','m','1994-03-17',NULL);
INSERT INTO pet VALUES('Buffy','Harold','dog','f','1989-05-13',NULL);
INSERT INTO pet VALUES('Fang','Benny','dog','m','1990-08-27',NULL);
INSERT INTO pet VALUES('Bowser','Diane','dog','m','1979-08-31','1995-07-29');
INSERT INTO pet VALUES('Chirpy','Gwen','bird','f','1998-09-11',NULL);
INSERT INTO pet VALUES('Whistler','Gwen','bird',NULL,'1997-12-09',NULL) ;
INSERT INTO pet VALUES('Slim','Benny','snake','m','1996-04-29',NULL);
INSERT INTO pet VALUES('Puffball','Diane','hamster','f','1999-03-30',NULL);
 
mysql> select * from pet;
+----------+-----------+----------+------+------------+------------+
| name   | owner   | specials | sex | birth   | death   |
+----------+-----------+----------+------+------------+------------+
| Puffball | Diane   | hamster | f  | 1999-03-30 | NULL    |
| 旺财   | 周星驰   | 狗    | 公  | 1990-01-01 | NULL    |
| Fluffy  | Harold  | cat   | f  | 1993-02-04 | NULL    |
| Claws  | Gwen   | cat   | m  | 1994-03-17 | NULL    |
| Buffy  | Harold  | dog   | f  | 1989-05-13 | NULL    |
| Fang   | Benny   | dog   | m  | 1990-08-27 | NULL    |
| Bowser  | Diane   | dog   | m  | 1979-08-31 | 1995-07-29 |
| Chirpy  | Gwen   | bird   | f  | 1998-09-11 | NULL    |
| Whistler | Gwen   | bird   | NULL | 1997-12-09 | NULL    |
| Slim   | Benny   | snake  | m  | 1996-04-29 | NULL    |
| Puffball | Diane   | hamster | f  | 1999-03-30 | NULL    |
+----------+-----------+----------+------+------------+------------+
11 rows in set (0.00 sec)

–如何删除数据

mysql> delete from pet where name = 'Fluffy';
Query OK, 1 row affected (0.02 sec)

–如何修改数据

mysql> update pet set name='旺旺财' where owner ='周星驰';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 

–数据记录常见操作

--增加
INSERT
--删除
DELETE
--修改
UPDATE
--查询
SELECT

建表约束

–主键约束

它能够唯一确定一张表中的一条记录，也就我们通过某个字段添加约束，可以使得字段不重复且不为空。

create table user(
id int primary key,
name varchar(20)
);
 
mysql> insert into user values(1,'张三');
Query OK, 1 row affected (0.02 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.01 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

–联合主键

–只要联合的主键值（均不为空）加起来不重复就OK

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

mysql> insert into user2 values(1,'张三','123');
Query OK, 1 row affected (0.02 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.01 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)

–自增约束

–auto_increment

create table user3(
id int primary key auto_increment,
name varchar(20)
);

mysql> insert into user3 (name) values('zhangsan');
Query OK, 1 row affected (0.02 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.07 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.14 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.14 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.12 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> create table user5(
  ->   id int,
  ->   name varchar(20)
  ->   );
Query OK, 0 rows affected (0.07 sec)
 
mysql> alter table user5 add unique(name);
Query OK, 0 rows affected (0.04 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.01 sec)
 
mysql> insert into user5 values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry 'zhangsan' for key 'name'
mysql> insert into user5 values(1,'lisi');
Query OK, 1 row affected (0.01 sec)

create table user6(
id int,
name varchar(20),
unique(name)
);
 
create table user7(
id int,
name varchar(20) unique
);
 
 
create table user8(
id int,
name varchar(20),
unique(id,name)
);

–两个键在一起不重复就OK

Query OK, 0 rows affected (0.08 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> insert into user8 values(1,'zhangsan');
Query OK, 1 row affected (0.02 sec)
 
mysql> insert into user8 values(2,'zhangsan');
Query OK, 1 row affected (0.02 sec)

–如何删除唯一约束

alter table user7 drop index name;
Query OK, 0 rows affected (0.03 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添加

alter table user7 modify name varchar(20) unique;
Query OK, 0 rows affected (0.04 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

–非空约束

–修饰的字段不能为空 NULL

create table user9(
id int,
name varchar(20) not null
);
Query OK, 0 rows affected (0.07 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)
 
insert into user9 (id) values(1);
ERROR 1364 (HY000): Field 'name' doesn't have a default value
 
insert into user9 values(1,'张三');
Query OK, 1 row affected (0.01 sec)
mysql> select * from user9;
+------+--------+
| id  | name  |
+------+--------+
|  1 | 张三  |
+------+--------+
1 row in set (0.00 sec)
 
insert into user9 (name) values('lisi');
mysql> insert into user9 (name) values('lisi');
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from user9;
+------+--------+
| id  | name  |
+------+--------+
|  1 | 张三  |
| NULL | lisi  |
+------+--------+
2 rows in set (0.00 sec)

–默认约束

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

create table user10(
id int,
name varchar(20),
age int default 10
);
Query OK, 0 rows affected (0.07 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)
 
insert into user10 (id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from user10;
+------+----------+------+
| id  | name   | age |
+------+----------+------+
|  1 | zhangsan |  10 |
+------+----------+------+
1 row in set (0.00 sec)

–传了值就不会使用默认值

insert into user10 values(1,'zhangsan',19);
Query OK, 1 row affected (0.02 sec)
 
mysql> select * from user10;
+------+----------+------+
| id  | name   | age |
+------+----------+------+
|  1 | zhangsan |  10 |
|  1 | zhangsan |  19 |
+------+----------+------+
2 rows in set (0.00 sec)
 

–外键约束

–牵扯到两个表：父表、子表

–主表、副表

–班级

create table classes(

id int primary key,

name varchar(20)

);

–学生

create table students(
id int primary key,
name varchar(20),
class_id int,
foreign key(class_id) references classes(id)
);
 
insert into classes values(1,'一班');
insert into classes values(2,'二班');
insert into classes values(3,'三班');
insert into classes values(4,'四班');
mysql> select * from classes;
+----+--------+
| id | name  |
+----+--------+
| 1 | 一班  |
| 2 | 二班  |
| 3 | 三班  |
| 4 | 四班  |
+----+--------+
4 rows in set (0.00 sec)
 
insert into students values(1001,'张三',1);
insert into students values(1002,'张三',2);
insert into students values(1003,'张三',3);
insert into students values(1004,'张三',4);
 
insert into students values(1005,'张三',5);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))
 
--1 主表classes中没有的数据值，在副表中是不可以使用的
--2 主表中的记录被副标使用，是不可以删除的
 
 
delete from classes where id=4;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`test`.`students`, CONSTRAINT `students_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `classes` (`id`))

三大范式

–1.第一范式

–1NF

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

create table student2(
id int primary key,
name varchar(20),
address varchar(30)
);
 
insert into student2 values(1,'张三','中国山东省东营市东营区济南路100号');
insert into student2 values(2,'李四','中国山东省东营市东营区东二路200号');
insert into student2 values(3,'王五','中国山东省东营市广饶县天府路300号');
mysql> select * from student2;
+----+--------+--------------------------------------------------+
| id | name  | address                     |
+----+--------+--------------------------------------------------+
| 1 | 张三  | 中国山东省东营市东营区济南路100号        |
| 2 | 李四  | 中国山东省东营市东营区东二路200号        |
| 3 | 王五  | 中国山东省东营市广饶县天府路300号        |
+----+--------+--------------------------------------------------+
3 rows in set (0.00 sec)
 

–字段值还可以继续拆分的，不满足第一范式

 
create table student3(
id int primary key,
name varchar(20),
country varchar(30),
privence varchar(30),
city varchar(30),
details varchar(30)
);
 
insert into student3 values(1,'张三','中国','山东省','东营市','东营区济南路100号');
insert into student3 values(2,'李四','中国','山东省','东营市','东营区东二路200号');
insert into student3 values(3,'王五','中国','山东省','东营市','广饶县天府路300号');
mysql> select * from student3;
+----+--------+---------+-----------+-----------+--------------------------+
| id | name  | country | privence | city   | details         |
+----+--------+---------+-----------+-----------+--------------------------+
| 1 | 张三  | 中国  | 山东省  | 东营市  | 东营区济南路100号    |
| 2 | 李四  | 中国  | 山东省  | 东营市  | 东营区东二路200号    |
| 3 | 王五  | 中国  | 山东省  | 东营市  | 广饶县天府路300号    |
+----+--------+---------+-----------+-----------+--------------------------+
3 rows in set (0.00 sec)
 

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

–第二范式

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

–如果出现不完全依赖，只可能发生在联合主键的情况下

create table myorder(
product_id int,
customer_id int,
product_name varchar(20),
customer_name varchar(20),
primary key(product_id,customer_id) 
);

–问题

–除主键以外的其他列，只依赖于主键的其他部分字段

–拆表

create table myorder(
order_id primary key,
product_id int,
customer_id int
);
 
create table product(
id int,
name varchar(20)
);
 
create table customer(
id int primary key,
name varchar(20)
);
 

–分成三个表之后完全依赖 满足第二范式

–第三范式

–3NF

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

create table myorder(
order_id primary key,
product_id int,
customer_id int
);
 
create table customer(
id int primary key,
name varchar(20)
phone varchar(15)
);

查询练习

1.查询student表中所有记录
select * from student;
 
2.查询student表中所有记录的sname 、ssex、class、
select sname,ssex,class from student;
 
3.查询教师所有的单位即不重复的depart列
distinct 排除重复
select distinct depart from teacher;
 
4.查询score表中成绩在60到80之间的记录
查询区间 between…and…
select * from score where degree between 60 and 80；
或者
selet * from score where degree > 60 and degree <80;
 
5.查询score表中成绩为85，86或88 的记录
表示或者关系的查询 in
select * from score where degree in (85,86,88);
 
6.查询student表中“95031”班或性别为“女”的同学的记录
or 表示或者
select * from student where class='95031' or ssex='女';
 
7.以class降序查询student表的所有记录
升序asc，降序desc
select * from studnt order by class desc;
默认升序
select * from studnt order by class (asc);
 
8.以cno升序、degree降序查询score表中的所有记录
select * from student score oder by cno asc,degree desc;
 
9.查询“95031”班 的学生人数
统计 count
select count(*) from student where class='95031';
 
10.查询score表中的最高分的学生学号和课程号（子查询或排序）
select sno,cno from score where degree=(select max(degree) from score);
1、找到最高分
select max(degree) from score;
2、找最高分的sno 和cno
select sno,cno from score where degree=(select max(degree) from score);
排序的做法
select sno,cno from score oder by degree;
select sno,cno from score oder by degree desc limit 0,1;
limit a,b
a=从第几条开始查
b=查多少条
 
 
11.查询每门课的平均成绩
分开去找
select * from course;
 
avg()
select avg(degree) from score where cno-'3-105';
select degree from score where cno ='3-105';
在一个sql中写
group by 分组
select cno,avg(degree) from score group by cno;
 
12.查询score表中至少有两名学生选修的并以3开头的课程的平均数
select cno,avg(degree) from score 
group by cno
having count(cno)>=2 
and cno like '3%';
 
13.查询分数大于70，小于90的sno列
select sno ,dgree from score
where degree >70 and degree <90;
或者
select sno ,dgree from score
where degree between 70 and 90;
 
 
14.查询所有学生的sname cno 和degree列
select sname from student;
select cno,degree from score;
 
多表查询
select sname,cno,degree from student,score
where student.sno=score.sno;
 
15.查询所有学生的sno cname 和degree 列
select sno,cname,degree from course,score
where course.cno=score.cno;
 
16.查询所有学生的sname cname和degree 列
sname->student
cname->course
degree->score
select sname,cname,degree from student,course,score
where student.sno=score.sno and course.cno=score.cno;
 
17.查询“95031”班学生每门课的平均成绩
分步思路
select * from student where class ='95031';
select sno from student where class='95031';
select * from score where sno in(select * from student where class ='95031');
 
合并
select cno,avg(degree)
from score 
where sno in (select sno from student where class='95031')
group by cno;
 
18.查询选修“3-105”课程的成绩高于“109”号同学“3-105”成绩的所有同学的记录
select degree from score where sno='109' and cno='3-105';
 
select *from score where cno='3-105' and degree>(select degree from score where sno='109' and cno='3-105');
 
19.查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录
select *from score where and degree>(select degree from score where sno='109' and cno='3-105');
 
20.查询和学号为108、101的同学同年出生的所有学生的sno、sname和sbirthday列
select year(sbirthday) from student where sno in (108,101);
 
select * from student where year(sbirthday) in (select year(sbirthday) from student where sno in (108,101));
 
21.查询“张旭”教师任课的学生成绩
select * from teacher where tname='张旭';
select tno from teacher where tname='张旭';
select * from course where tno=(select tno from teacher where tname='张旭');
select cno from course where tno=(select tno from teacher where tname='张旭');
select * from score where cno=(select cno from course where tno=(select tno from teacher where tname='张旭'));
 
22.查询选修某课程的同学人数多于5人的教师姓名
select cno from score group by cno having count(*)>5;
 
select * from teacher;
select tno from course where cno=(select cno from score group by cno having count(*)>5);
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>5)); 
 
23.查询95033班和95031班全体学生的记录
select * from student;
select * from student where calss in ('95031','95033');
 
24.查询存在有85分以上成绩的课程Cno
select cno from score where degree >85;
 
25.查询出“计算机系”教师所教课程的成绩表
select * from teacher where depart='计算机系';
select * from course where tno in(select tno from teacher where depart='计算机系');
select * from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系'));
 
26.查询“计算机系”与“电子工程系”不同职称的教师的tname和prof
union 求并集
select * from teacher ;
select prof from teacher where depart ='电子工程系';
select * from teacher where depart ='计算机系' and prof not in(select prof from teacher where depart ='电子工程系') ;
select * from teacher where depart ='电子工程系' and prof not in(select prof from teacher where depart ='计算机系') ;
 
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 ='计算机系') ;
 
27.查询选修编号为“3-105”课程且成绩至少高于选修编号为“3-245”的同学的cno 、sno和degree，并按照degree从高到低的次序排序
select * from score where cno='3-245';
select * from score where cno='3-105';

至少：大于其中至少一个，**any

select * from score 
where cno='3-105' 
and degree>any(select degree from score where cno='3-245')
order by degree desc;
 
28.查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的cno、sno和degree
select * from score 
where cno='3-105' 
and degree>all(select degree from score where cno='3-245');

all**表示所有的关系

 
29.查询所有教师和同学的name、sex和birthday
别名 as
select tname as name,tsex as sex,tbirthday as birthday from teacher 
union 
select sname,ssex,sbirthday from student;
 
30.查询所有“女”教师和“女”同学的name、sex、birthday
select tname as name,tsex as sex,tbirthday as birthday from teacher 
where tsex='女' 
union 
select sname,ssex,sbirthday from student 
where ssex='女';
 
31.查询成绩比该课程平均成绩低的同学的成绩表
select cno,avg(degree) from score group by cno;
select * from score;
 
select * from score a where degree < (select avg(degree) from score b where a.cno=b.cno);
 
32.查询所有任课教师的tname和depart
select * from teacher;

任课：在课程表中安排了课程

select * from course;
select * from teacher where tno in(select tno from course);
select tname,depart from teacher where tno in(select tno from course);
 
33.查询至少有两名男生的班号
select * from student;
select class from student where ssex ='男' group by class having count(*)>1;
 
34.查询student表中不姓“王”的同学的记录
select * from student;
select * from student where sname not like '王%';
 
35.查询student表中每个学生的姓名和年龄
年龄：当前年份-出生年龄
当前年龄：
select year (now());
select year(sbirthday) from student;
 
select sname,year(now())-year(sbirthday) as '年龄' from student;
 
36.查询student表中最大和最小的sbirthday日期值
select sbirthday from student;
select sbirthday from student order by sbirthday;

max,min 数值大小

select max(sbirthday) as '最大',min(sbirthday) as '最小' from student;
 
37.以班号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by class sedc,sbirthday;
 
38.查询“男”教师及其所上的课程
select * from teacher where tsex='男';
 
select * from course where tno in(select tno from teacher where tsex='男');
 
39.查询最高分同学的sno、cno和degree列
select max(degree) from score;
select * from score where degree=(select max(degree) from score);
 
40.查询和“李军”同性别的所有同学的sname；
select ssex from student where sname='李军';
select sname from student where ssex=(select ssex from student where sname='李军');
 
41.查询和李军同性别并且同班的同学sname
select sname from student 
where ssex=(select ssex from score where degree=(select max(degree) from score))
and class=(select class from student where sname='李军');
 
42.查询所有选修“计算机导论”课程的“男”同学的成绩表
select * from student where ssex='男';
select * from course where cname='计算机导论';
 
select * from score
where cno=(select cno from course where cname='计算机导论')
and where sno in(select sno from student where ssex='男');
 
43.略

连接查询

SQL的四种连接查询

内连接

inner join或 join

外连接

左连接 left join 或者left outer join

右连接right join或者 right outer join

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

创建数据库：

create database testJoin;
Query OK, 1 row affected (0.02 sec)
 
mysql> use testJoin;
Database changed
mysql>

创建两个表：

person 表

id,
name,
cardId
 
create table person(
id int,
name varchar(20),
cardId int
);

card 表

id,
name
 
create table card(
id int,
name varchar(20)
);
 
 
mysql> show tables;
+--------------------+
| Tables_in_testjoin |
+--------------------+
| card        |
| person       |
+--------------------+
2 rows in set (0.02 sec)
 
insert into card values(1,'饭卡');
insert into card values(2,'建行卡');
insert into card values(3,'农行卡');
insert into card values(4,'工商卡');
insert into card values(5,'邮政卡');

mysql> select * from card;
+------+-----------+
| id  | name   |
+------+-----------+
|  1 | 饭卡   |
|  2 | 建行卡  |
|  3 | 农行卡  |
|  4 | 工商卡  |
|  5 | 邮政卡  |
+------+-----------+
5 rows in set (0.00 sec)
 
insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);
mysql> select * from person;
+------+--------+--------+
| id  | name  | cardId |
+------+--------+--------+
|  1 | 张三  |   1 |
|  2 | 李四  |   3 |
|  3 | 王五  |   6 |
+------+--------+--------+
3 rows in set (0.00 sec)
 

1.inner join查询（内连接）

 
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)
 
内联查询，就是两张表中的数据，通过某个字段相对，查询出相关记录数据
 
select * from person 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)
 

2.left join(左外连接)

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.00 sec)
 
左外连接，会把左表里面的所有数据取出来，而右表中的数据，如果有相等的，就显示出来，如果没有，就补NULL
select * from person left outer 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.00 sec)
 

3.right join(右外连接)

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
select * from person right outer 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)
 
4.full join(全外连接)
select * from person full join card on person.cardId=card.id;
 
mysql 不支持full join
 
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 | 邮政卡  |
+------+--------+--------+------+-----------+
6 rows in set (0.01 sec)

事务

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

比如银行转账：

a->-100
update user set money =money-100 where name='a';
 
b->+100
update user set money =money+100 where name='b';
 
多条sql语句，可能会有同时成功的要求，要么就同时失败
 
MySQL中如何控制事务？
 
1.MySQL默认开启事务（自动提交）
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|      1 |
+--------------+
1 row in set (0.00 sec)
 
默认开启事务的作用：
当我们去执行一个sql语句的时候，效果会立即体现出来，且不能回滚
 
create database bank;
 
create table user(
id int primary key,
name varchar(20),
money int
);
insert into user values(1,'a',1000);
 
事务回滚：撤销sql语句执行效果
roolback；
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
 
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
 
设置MySQL自动提交为false
set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|      0 |
+--------------+
1 row in set (0.00 sec)
 
上面的操作，关闭了MySQL的自动提交（autocommit）
 
insert into user values(2,'b',1000);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
| 2 | b  | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
 
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
+----+------+-------+
1 row in set (0.00 sec)
 
再一次插入数据
 
mysql> insert into user values(2,'b',1000);
Query OK, 1 row affected (0.00 sec)
手动提交数据
 
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
再撤销，是不可以撤销的（持久性）
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
| 2 | b  | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
 

字段提交 @@autocommit=1

手动提交 commit

事务回滚 rollback

如果说这个时候转账：

update user set money =money-100 where name='a';
update user set money =money+100 where name='b';
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  900 |
| 2 | b  | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)
 
rollback
 
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
| 2 | b  | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
 
事务提供了一个反悔的机会
 
 
set autocommit=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|      1 |
+--------------+
1 row in set (0.00 sec)
 
begin;
或者
start transaction;
都可以帮助手动开启一个事务
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  | 1000 |
| 2 | b  | 1000 |
+----+------+-------+
2 rows in set (0.00 sec)
update user set money =money-100 where name='a';
update user set money =money+100 where name='b';
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  900 |
| 2 | b  | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)

事务回滚

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

没有被撤销

mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  900 |
| 2 | b  | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)
 

手动开启事务（1）

begin;
update user set money =money-100 where name='a';
update user set money =money+100 where name='b';
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  800 |
| 2 | b  | 1200 |
+----+------+-------+
2 rows in set (0.00 sec)
 
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  900 |
| 2 | b  | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)
 

手动开启事务（2）

start transaction;
update user set money =money-100 where name='a';
update user set money =money+100 where name='b';
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  800 |
| 2 | b  | 1200 |
+----+------+-------+
2 rows in set (0.00 sec)
 
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  900 |
| 2 | b  | 1100 |
+----+------+-------+
2 rows in set (0.00 sec)
 

事务开启之后，一旦提交，回滚（rollback）无效

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
 
mysql> update user set money =money-100 where name='a';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
mysql> update user set money =money+100 where name='b';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
 
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  800 |
| 2 | b  | 1200 |
+----+------+-------+
2 rows in set (0.00 sec)
 
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from user;
+----+------+-------+
| id | name | money |
+----+------+-------+
| 1 | a  |  800 |
| 2 | b  | 1200 |
+----+------+-------+
2 rows in set (0.00 sec)
 
事务的四大特征：
A：原子性：事务是最小的单位，不可以再分割
C：一致性：事务要求，同一事务中的sql语句，必须保证同时成功或者同时失败
I：隔离性：事务1和事务2之间是具有隔离性的
D：持久性：事务一旦结束，就不可以返回
 
事务开启：
1.修改默认提交 set autocommit=0;
2.begin;
3.start transaction;
 
事务手动提交
commit;
 
事务手动回滚
rollback;
 
 
事务的隔离性：
 

1.read uncommitted;	读未提交的
2.read committed;	都已经提交的
3.repeatable read;	可重复读
4.serializable;	串行化

1-read uncommitted

如果有事务a,事务b,
a事务对数据进行操作，在操作的过程中，事务没有被提交，但是b可以看见a操作的结果
 
bank表 user表
insert into user values(3,'小明',1000);
insert into user values(4,'淘宝店',1000);
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)
 
如何查看数据库的隔离级别
MySQL 8.0:

select @@global.transaction_isolation;	系统级别
select @@transaction_isolation;	会话级别

MySQL默认隔离级别 REPEATABLE-READ
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ        |
+--------------------------------+
1 row in set (0.00 sec)
 
MySQL 5.x:

select @@global.tx_isolation;	系统级别
select @@tx_isolation;	会话级别

 
如何修改隔离级别
set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED         |
+--------------------------------+
1 row in set (0.00 sec)
 
mysql> set global transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-UNCOMMITTED        |
+--------------------------------+
1 row in set (0.00 sec)
 
转账：小明在淘宝店买鞋子：800块
小明-》成都 ATM
淘宝店-》广州 ATM
 
start transaction;
update user set money =money-800 where name='小明';
update user set money =money+800 where name='淘宝店';
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   |  200 |
| 4 | 淘宝店  | 1800 |
+----+-----------+-------+
4 rows in set (0.00 sec)
 
告知淘宝店
淘宝店在广州查账
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   |  200 |
| 4 | 淘宝店  | 1800 |
+----+-----------+-------+
4 rows in set (0.00 sec)
 
发货
晚上请女朋友吃饭
花1800
 
小明rollback
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)
淘宝结账的时候发现钱不够
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)
 
如果两个地方都在进行操作，如果事务a开启之后，他的数据可以被其他事务读取到
这样就会出现“脏读”
脏读：一个事务读到了另外一个事务没有提交的数据
实际开发是不允许脏读出现的
 

2-read committed

修改隔离级别为 READ-COMMITTED

set global transaction isolation level read committed;
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| READ-COMMITTED         |
+--------------------------------+
1 row in set (0.00 sec)
 
bank 数据库 user 表
 
小张：银行的会计
start transaction;
select * from user;
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
+----+-----------+-------+
4 rows in set (0.00 sec)
 
小张出去上厕所去。。。
 
小王：
start transaction;
insert into user values(5,'c',100);
commit;
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
+----+-----------+-------+
5 rows in set (0.00 sec)
 
小张回来了
 
select avg(money) from user;
+------------+
| avg(money) |
+------------+
|  820.0000 |
+------------+
1 row in set (0.00 sec)
 
money 的平均值不是1000，变少了。。
虽然只能读到事务提交的数据，但是还是会出现问题，就是读取同一个表的数据，发现前后不一致。
不可重复读现象：read committed
 

3-repeatable read

set global transaction isolation level repeatable read;
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ        |
+--------------------------------+
1 row in set (0.00 sec)
 
在repeatable read 隔离级别下出现什么问题：
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
+----+-----------+-------+
5 rows in set (0.00 sec)
 
张全蛋-成都
start transaction;
 
王尼玛-北京
start transaction;
 
张全蛋-成都
insert into user values(6,'d',1000);

Query OK, 1 row affected (0.00 sec)

 
mysql> commit;
Query OK, 0 rows affected (0.02 sec)
 
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
| 6 | d     | 1000 |
+----+-----------+-------+
6 rows in set (0.00 sec)
 
王尼玛-北京
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
+----+-----------+-------+
5 rows in set (0.00 sec)
 
insert into user values(6,'d',1000);
mysql> insert into user values(6,'d',1000);
ERROR 1062 (23000): Duplicate entry '6' for key 'PRIMARY'
 
这就现象就叫做幻读！
两个事务，事务a和事务b同时操作一张表 事务a提交的数据也不能被事务b读到，可能出现幻读
 

4-serializable

 
修改隔离级别为串行化
set global transaction isolation level serializable;
select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| SERIALIZABLE          |
+--------------------------------+
1 row in set (0.00 sec)
 
 
mysql> select *from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
| 6 | d     | 1000 |
+----+-----------+-------+
6 rows in set (0.00 sec)
 
张全蛋-成都
start transaction;
 
王尼玛-北京
start transaction;
 
张全蛋-成都
insert into user values(7,'赵铁柱',1000);
Query OK, 1 row affected (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.01 sec)
 
mysql> select *from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
| 6 | d     | 1000 |
| 7 | 赵铁柱  | 1000 |
+----+-----------+-------+
7 rows in set (0.00 sec)
 
王尼玛-北京
mysql> select * from user;
+----+-----------+-------+
| id | name   | money |
+----+-----------+-------+
| 1 | a     |  800 |
| 2 | b     | 1200 |
| 3 | 小明   | 1000 |
| 4 | 淘宝店  | 1000 |
| 5 | c     |  100 |
| 6 | d     | 1000 |
| 7 | 赵铁柱  | 1000 |
+----+-----------+-------+
7 rows in set (0.00 sec)
 
张全蛋-成都
start transaction;
insert into user values(8,'王小花',1000);
 
被卡住了
 
当user表被另外一个事务操作的时候，其他事务里面的写操作，是不可以进行的
进入排队状态，直到王尼玛那边事务结束之后，张全蛋的写入才会执行
前提是在没有等待超时的情况下
 
王尼玛-北京
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
张全蛋-成都
Query OK, 1 row affected (0.00 sec)
 
串行化带来的问题：性能很差
 
 
性能：uncommitted>committed>repeatable>serializable;
隔离级别越高性能越差
 
MySQL默认隔离级别是 repeatable