P1 数据库概述
声明：
本课程笔记依据B站课程MySQL，网址为：https://www.bilibili.com/video/BV1Vt411z7wy?t=3
1、笔记根据老师课程进行，全手打，SQL语言和注释。
2、中间部分重复课程内容过于简单，略去。
3、一开始是采用命令提示符（cmd）写的，过于脑瘫，后改为MySQL front编译器编写，建议大家用编译器编写。
4、如有雷同，那可能咱俩看的是一个课程。
5、全干货，以后学习进阶，会持续更新。
数据库
数据库是以一定方式储存在一起、能与多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合，可视为电子化的文件柜——存储电子文件的处所，用户可以对文件中的数据进行新增、查询、更新、删除等操作。
关系数据库
关系型数据库，存储的格式可以直观地反映实体间的关系。关系型数据库和常见的表格比较相似，关系型数据库中表与表之间是有很多复杂的关联关系的。 常见的关系型数据库有Mysql，SqlServer等。
非关系型数据库（NoSQL）
随着近些年技术方向的不断拓展，大量的NoSql数据库如MongoDB、Redis、Memcache出于简化数据库结构、避免冗余、影响性能的表连接、摒弃复杂分布式的目的被设计。
P2 mysql学习笔记：关系型数据库
如何使用终端操作数据库
（一）常用操作
1、如何登陆数据库服务器
C:\Users\Administrator>mysql -u root -p
2、mysql创建数据库和表
//创建新的数据库
mysql> CREATE DATABASE IF NOT EXISTS admin DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.03 sec)
//简单方法
mysql> create database test;
Query OK, 1 row affected (0.02 sec)
//切换到新建数据库
mysql> use test
Database changed
mysql> show tables;
Empty set (0.00 sec)
//创建一个新表语法
mysql> create table pet(
-> name VARCHAR(20),
-> owner VARCHAR(20),
-> species VARCHAR(20),
-> sex CHAR(1),
-> birth DATE);
Query OK, 0 rows affected (0.28 sec)
//查看是否创建成
mysql> show tables
-> ;
+---------------+
| Tables_in_pet |
+---------------+
| pet |
+---------------+
1 row in set (0.00 sec)
//查看表的结构
mysql> desc 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 | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
3、如何查询数据库服务器中所有的数据库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
4、查看表中的记录
mysql> select * from pet;
Empty set (0.00 sec)
5、插入内容
mysql> insert into pet
-> values('Puffball','Diane','hamster','f','1999-03-30');
Query OK, 1 row affected (0.06 sec)
6、增删改查操作
//先增
mysql> INSERT INTO pet VALUES('kk1','cc1','dog1','1','1998-1-2');
Query OK, 1 row affected (0.07 sec)
mysql> INSERT INTO pet VALUES('kk2','cc2','dog2','2','1998-2-2');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO pet VALUES('kk3','cc3','dog3','1','1998-3-2');
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO pet VALUES('kk4','cc4','dog4','2','1998-4-2');
Query OK, 1 row affected (0.04 sec)
//删除数据
mysql> delete from pet where name='kk1';
Query OK, 1 row affected (0.06 sec)
//修改数据
mysql> update pet set name='kk10' where owner='cc2';
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
7、总结
数据库常用操作
增加 insert into
删除 delete from
查询 select
修改 update
（二）mysql常用数据类型
MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。
| 类型 | 大小 | 范围（有符号） | 范围（无符号） | 用途 |
|---|---|---|---|---|
| TINYINT | 1 byte | (-128，127) | (0，255) | 小整数值 |
| SMALLINT | 2 bytes | (-32 768，32 767) | (0，65 535) | 大整数值 |
| MEDIUMINT | 3 bytes | (-8 388 608，8 388 607) | (0，16 777 215) | 大整数值 |
| INT或INTEGER | 4 bytes | (-2 147 483 648，2 147 483 647) | (0，4 294 967 295) | 大整数值 |
| BIGINT | 8 bytes | (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) | (0，18 446 744 073 709 551 615) | 极大整数值 |
| FLOAT | 4 bytes | (-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) | 0，(1.175 494 351 E-38，3.402 823 466 E+38) | 单精度 浮点数值 |
| DOUBLE | 8 bytes | (-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) | 双精度 浮点数值 |
| DECIMAL | 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 | 依赖于M和D的值 | 依赖于M和D的值 | 小数值 |
| 类型 | 大小 ( bytes) | 范围 | 格式 | 用途 |
|---|---|---|---|---|
| DATE | 3 | 1000-01-01/9999-12-31 | YYYY-MM-DD | 日期值 |
| TIME | 3 | ‘-838:59:59’/‘838:59:59’ | HH:MM:SS | 时间值或持续时间 |
| YEAR | 1 | 1901/2155 | YYYY | 年份值 |
| DATETIME | 8 | 1000-01-01 00:00:00/9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
| TIMESTAMP | 4 | 1970-01-01 00:00:00/2038结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07 | YYYYMMDD HHMMSS | 混合日期和时间值，时间戳 |
| CHAR | 0-255 bytes | 定长字符串 |
|---|---|---|
| VARCHAR | 0-65535 bytes | 变长字符串 |
| TINYBLOB | 0-255 bytes | 不超过 255 个字符的二进制字符串 |
| TINYTEXT | 0-255 bytes | 短文本字符串 |
| BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
| TEXT | 0-65 535 bytes | 长文本数据 |
| MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
| MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
| LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
| LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
（三）mysql建表约束
1、主键约束+自增约束
主键约束能够唯一确定一张表中的一条记录，也就是通过给某个字段添加约束就可以使得该字段不重复且不为空。
mysql> create table user(
-> id int primary key,//主键约束
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.29 sec)
mysql> insert into user values(1,'xiaoming');
Query OK, 1 row affected (0.05 sec)
mysql> insert into user values(2,'xiaoming');
Query OK, 1 row affected (0.07 sec)
mysql> select * from user
-> ;
+----+----------+
| id | name |
+----+----------+
| 1 | xiaoming |//id不能为空且不可重复
| 2 | xiaoming |
+----+----------+
2 rows in set (0.00 sec)
//联合主键：所联合的主键加起来不重复即可，且联合主键中任何元素不能为NULL
mysql> create table user2(
-> id int,
-> name varchar(20),
-> password varchar(20),
-> primary key(id,name)
-> );
//自增约束与主键约束共用自动分配id值
mysql> create table user3(
-> id int primary key auto_increment,
-> name varchar(20)
-> );
Query OK, 0 rows affected (0.22 sec)
mysql> insert user3 (name) values('zhangsan');
Query OK, 1 row affected (0.04 sec)
mysql> select * from user3;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
+----+----------+
1 row in set (0.00 sec)
如果在建表过程中忘记创建主键约束，可以采用：
alter table user4 add primary key(id,name);
删除主键约束：
alter table user4 drop primary key;
修改主键约束：
alter table user4 modify id int primary key;
2、外键约束
涉及到两个表：父表、子表或者说主表、副表
班级表例子：
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,'class_1');
insert into classes values(2,'class_2');
insert into classes values(3,'class_3');
insert into classes values(4,'class_4');
insert into students values(1001,'zhangsan',1);
insert into students values(1002,'zhangsan',2);
insert into students values(1003,'zhangsan',3);
insert into students values(1004,'zhangsan',4);
insert into students values(1005,'lisi',1);
总结：
1、父表classes中没有的数据值，在子表中无法使用
2、父表中的记录被子表引用时不可以删除
3、唯一约束
修饰的字段不可以重复
create table user5(
id int,
name varchar(20)
);
alter TABLE user5 add UNIQUE(name);//添加约束
方法2
create table user6(id int,name varchar(20),unique(name));
方法3
create table user6(id int,name varchar(20) unique);
方法4
alter table user7 modify name varchar(20) unique;
删除唯一约束
mysql> alter table user5 drop index name;
Query OK, 0 rows affected (0.13 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 | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
总结：
1、建表时添加约束
2、alter。。。add。。。
3、alter。。。modify。。。
4、删除使用alter。。。drop。。。
4、非空约束
修饰的字段不能为空
mysql> create table user7(
-> id int,
-> name varchar(20) not null
-> );
Query OK, 0 rows affected (0.21 sec)
5、默认约束
当我们插入字段值时，如果没有传值，就会使用默认值
mysql> create table user8(
-> id int,
-> name varchar(20),
-> age int default 10
-> );
Query OK, 0 rows affected (0.20 sec)
mysql> insert into user8(id,name) values(1,'zhangsan');
Query OK, 1 row affected (0.04 sec)
mysql> select * from user8;
+------+----------+------+
| id | name | age |
+------+----------+------+
| 1 | zhangsan | 10 |
+------+----------+------+
1 row in set (0.00 sec)
（四）数据库的三大设计范式.sql
1、第一范式
-- 1、第一范式
-- 1NF
-- 数据表中所有的字段都是不可分割的原子值
create TABLE student2(
id int PRIMARY key,
name varchar(20),
address varchar(30)
);
-- 变更数据库编码
alter database test CHARACTER set utf8;
-- 变更表的编码
alter table student2 CHARACTER set utf8;
-- 变更所需使用中文的列的编码
alter TABLE student2 change name name varchar(256) character set utf8;
alter TABLE student2 change address address varchar(256) character set utf8;
insert into student2 values(1,'张三','河南省周口市郸城县');
insert into student2 values(2,'李四','河南省项城市');
insert into student2 values(3,'王五','河南省郑州市');
-- 上述这种字段值可以拆分的，不满足第一范式
create TABLE student3(
id int PRIMARY key,
name varchar(20),
country varchar(30),
province varchar(30),
city varchar(30)
);
-- 变更表的编码
alter table student3 CHARACTER set utf8;
-- 变更所需使用中文的列的编码
alter TABLE student3 change name name varchar(256) character set utf8;
alter TABLE student3 change country country varchar(256) character set utf8;
alter TABLE student3 change province province varchar(256) character set utf8;
alter TABLE student3 change city city varchar(256) character set utf8;
--第一范式
insert into student3 values(1,'张三','中国','河南省','周口市');
insert into student3 values(2,'李四','中国','河南省','项城市');
insert into student3 values(3,'王五','中国','江苏省','南京市');
– 范式：设计越详细，对某些实际操作可能更好，但也可能增加复杂度
– 根据实际情况设计范式
2、第二范式
– 必须满足第一范式前提下，要求除主键外的每一列都必须完全依赖主键，如果出现不完全依赖，只可能发生联合主键的情况
-- 订单表
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 int primary key,
product_id int,
customer_id int
);
CREATE table product(
id int PRIMARY key,
name varchar(20)
);
CREATE table customer(
id int PRIMARY key,
name varchar(20)
);
3、第三范式
必须满足第二范式，除开主键列的其他列不能有传递依赖的关系，不做外键
-- 第三范式
create table myorder(
order_id int primary key,
product_id int,
customer_id int
);
-- 不把对customer id有依赖的键放入上面的表中
create table customer(
id int primary key,
name varchar(20),
phone varchar(15)
);
（五）mysql查询练习
mysql查询练习
1、插入数据
-- 学生表:学号、姓名、性别、生日、班级
create table student(
snum varchar(20) PRIMARY key,
sname varchar(20) not null,
ssex varchar(10) not null,
sbirthday datetime,
class varchar(20)
);
show tables;
desc student;
-- 教师表：教师编号、姓名、性别、生日、支撑、部门
create table teacher(
tnum varchar(20) primary key,
tname varchar(20) not null,
tsex varchar(10) not null,
tbiethday datetime,
prof varchar(20) not null,
depart varchar(20) not null
);
-- 课程表:课程号、课程名称、任课老师编号
create table course(
cnum varchar(20) primary key,
cname varchar(20) not null,
tnum varchar(20) not null,
foreign key(tnum) references teacher(tnum)
);
-- 成绩表：学号、课程号、成绩
CREATE TABLE score(
snum varchar(20) not null,
cnum varchar(20) not null,
degree decimal,
foreign key(snum) REFERENCES student(snum),
foreign key(cnum) REFERENCES course(cnum),
primary key(snum,cnum)
);
-- 添加数据
#添加学生信息
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-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');
select * from course;
2、查询student表的所有记录
select * from student;
3、查询student表中所记录的sname、ssex、class
select sname,ssex,class from student;
4、查询教师所有的单位即不重复的depart列
select distinct depart from teacher;
5、查询score在60-80之间
方法1
select * from score where degree between 60 and 80;
方法2（运算符比较）
select * from score where degree>60 and degree<80;
6、查询score中成绩为85、86、88的记录
表示关系的查询
select * from score where degree in(85,86,88);
7、查询student表中“95031”班或者性别为“女”的同学记录
SELECT * from student where class='95031' or ssex='女';
8、以class降序查询student表的所有记录。升序、降序
desc表示降序，默认升序，asc也表示升序
select * from student order by class desc;
9、snum升序、degree降序查询score表中的所有记录
select * from score order by snum asc,degree desc;
10、查询“95031”班级人数
SELECT count(*) from student where class='95031';
11、查询score表中最高分的学生学号和课程号
select snum,cnum from score where degree= (SELECT max(degree) from score);
上述方法其中可以分为两步
排序做法：
select snum,cnum,degree from score order by degree desc limit 0,1;
12、查询每门课的平均成绩
-- 查询每门课的平均成绩
select * from course;
select avg(degree) from score where cnum='3-105';
--一次计算所有的group by
select avg(degree) from score group by cnum;
13、查询score中至少有两名学生选修的并以3开头的课程的平均分数
-- 查询score中至少有两名学生选修的并以3开头的课程的平均分数
select cnum from score group by cnum having count(cnum)>=2 and cnum like '3%';
select cnum,avg(degree) from score group by cnum having count(cnum)>=2 and cnum like '3%';
14、查询分数大于70小于90的snum列
select snum,degree from score where degree>70 and degree<90;
select snum,degree from score where degree between 70 and 90;
15、查询所有学生的sname、cnum的degree列，涉及多表查询
select snum,sname from student;
select snum,cnum,degree from score;
select sname,cnum,degree from student,score where student.snum=score.snum;
16、查询所有学生的sname，cname，degreee
-- student-->sname
-- course-->cname
-- score-->degree
select sname, cname, degree from student,course,score where student.snum = score.snum and course.cnum=score.cnum;
17、查询95031班学生每门课的平均分,where,in表示或,group
select cnum,avg(degree) from score where snum in (select snum from student where class='95031') group by cnum;
18、查询选修3-105课程的成绩高于109号同学的3-105成绩的所有同学的记录
select snum,cnum,degree from score where cnum='3-105' and degree>(select degree from score where snum='109' and cnum='3-105');
19、查询成绩高于109号同学的3-105成绩的所有同学的记录
select * from score where degree>(select degree from score where snum='109' and cnum='3-105');
20、查询学号为108、101的同学同年出生的是所有学生的snum、sname、sbirthday
select year(sbirthday) from student where snum in (108,101);
select snum,sname,sbirthday from student where year(sbirthday) in (select year(sbirthday) from student where snum in (108,101));
21、查询张旭老师任课的学生成绩
select tnum from teacher where tname='张旭';
select cnum from course where tnum in (select tnum from teacher where tname='张旭');
select snum,degree from score where cnum=(select cnum from course where tnum in (select tnum from teacher where tname='张旭'));
22、查询选修某课程的同学人数多于5人的教师姓名，需要插入数据
select * from score;
INSERT INTO score VALUES('101','3-105','90');
INSERT INTO score VALUES('102','3-105','91');
INSERT INTO score VALUES('104','3-105','92');
insert into student values('110','张飞','男','1974-06-03 00:00:00','95038');
select cnum from score having count(cnum)>5;
select tnum from course where cnum=(select cnum from score having count(cnum)>5);
select tname from teacher where tnum=(select tnum from course where cnum=(select cnum from score having count(cnum)>5));
23、查询95033和95031班全体学生记录
select * from student where class in ('95033','95031');
24、查询计算机系和电子工程系不同职称的教师的tname和prof
select * 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='电子工程系');
25、查询选修编号为3-105且成绩至少高于3-245的同学的cnum，snum和degree，并按照degree排序
select * from score where cnum='3-105' and degree>any(select degree from score where cnum='3-245') order by degree desc;
26、查询选修编号为3-105且成绩高于3-245的同学的cnum，snum和degree，并按照degree排序
select * from score
where cnum='3-105'
and degree>all(select degree from score where cnum='3-245')
order by degree desc;
27、查询所有学生和教师的name、sex、birthday
select tname as name,tsex as sex,tbiethday as birthday from teacher
union
select sname,ssex,sbirthday from student;
28、查询成绩比该课程成绩低的同学的成绩表，这个表中需要复制数据，暂时无法操作，但是可以新建一张表与原数据相同进行操作
select cnum,avg(degree) from score group by cnum;
select * from score as a where a.degree<(select avg(degree) from score as b where a.cnum=b.cnum);
29、子查询：查询所有任课教师的tname和depart
select tname,depart from teacher;
select * from teacher where tnum in (select tnum from course);
30、查询至少有两名男生的编号
select * from student where ssex='男' group by class having count(*)>1;
31、查询student表中不姓王的同学
select * from student where sname not like '王%';
32、查询student表中每个学生的姓名和年龄
select year(now());
select year(sbirthday) from student;
select sname,year(now())-year(sbirthday) as '年龄' from student;
33、查询student表中最大最小的sbirtgday的日期
select sbirthday from student order by sbirthday;
select max(sbirthday) as 'max',min(sbirthday) as 'min' from student;
34、以班号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by class desc,sbirthday;
35、查询男教师及其所上的课程
select * from teacher where tsex='男';
select * from course where tnum in (select tnum from teacher where tsex='男');
36、查询最高分同学的snum，cnum，degree
select max(degree) from score;
select * from score where degree=(select max(degree) from score);
37、假设使用下面的表创建了一个grade表：
create table grade(
low int(3),
up 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;
-- 先查询所有同学的snum、cnum、grade
select snum,cnum,grade from score,grade where degree between low and up;
38、SQL的四种连接查询
-- person table:id,name,cardid
-- card table:id,name
create database testJoin;
use testJoin;
create table person(
id int,
name varchar(20),
cardid int
);
create table card(
id int,
name varchar(20)
);
show tables;
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,'邮政卡');
insert into person values(1,'张三',1);
insert into person values(2,'李四',3);
insert into person values(3,'王五',6);
内连接inner join或者join
-- 王五的卡id为6，没有对应的卡，没有创建外键，也创建不了，因为超出范围
-- 内联查询，两张表中的数据通过某个字段相对应，查询出相关记录
select * from person inner join card on person.cardid=card.id;
外连接：
1、左连接left join或者left outer join
-- 左连接：会把左边的表中所有数据选取，如有相等则显示，不存在也显示为null
select * from person left join card on person.cardid=card.id;
2、右链接right join或right outer join：与左连接相反
select * from person right join card on person.cardid=card.id;
3、完全外连接full join或者full outer join：mysql不支持full join
左连接+右连接
（六）事务
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默认开启事务与事务回滚
select @@autocommit;
--默认事务开启作用是：当我们执行一个sql语句的时候，效果会立即体现出来，且不能回馈。
create database testTranscation;
use testTranscation;
create table user(
id int primary key,
name varchar(20),
money int
);
insert into user values(1,'a',1000);
-- 事务回滚：撤销sql语句执行效果，但在这条语句中执行无效
rollback;
-- 设置mysql自动提交为false
set autocommit=0;
select @@autocommit;
-- 上述操作关闭了mysql自动提交，可以事务回滚
insert into user values(2,'b',1000);
rollback;
-- 事务回滚成功，过程中我们看到的插入的数据实际是在虚拟的表格中，并未真正提交
-- 如要提交，应做以下操作
insert into user values(2,'b',1000);
commit;
-- 此时提交成功，不可以再回滚
– 自动提交：@@autocommit=1
– 手动提交：commit;
– 事务回滚：rollback；
2、手动开启事务
设置自动提交
set autocommit=1;
select @@autocommit;
-- begin;或者start transaction都可以帮我们手动开启一个事物
select * from user;
update user set money=money-100 where name='a';
update user set money=money+100 where name='b';
select * from user;
rollback;
select * from user;
-- 上述rollback失败
-- 手动开启回滚1
begin;
update user set money=money-100 where name='a';
update user set money=money+100 where name='b';
select * from user;
rollback;
select * from user;
-- 手动开启回滚2
start transaction;
update user set money=money-100 where name='a';
update user set money=money+100 where name='b';
select * from user;
rollback;
select * from user;
3、事物的四大特征：ACID
A：原子性：事务是最小的单位，不可以再分割。
C：一致性：事务要求，同一事务中的sql语句必须同时成功或者同时失败。
I：隔离性：事务1和事务2之间具有隔离性。
D：持久性：事务一旦结束，就不可以回滚。
事务开启：
1、修改默认提交：set aotocommit=0;
2、begin;
3、start transaction;
事务手动提交：
commit；
事务手动回滚：
rollback；
4、事务的隔离性
1、read uncommitted; 读未提交的，这个成为脏读，读取到未提交的数据
如果有事务a和事务b，a事务对数据进行操作，在操作过程中，事务没有被提交，但是b可以看见a操作的结果
insert into user values(3,'小明',1000);
insert into user values(4,'淘宝店',1000);
-- 需要查看数据库的隔离级别
select version();
select @@tx_isolation;
-- 如何修改隔离级别
set global transaction isolation level read uncommitted;
select @@global.tx_isolation;
-- 转账，小明在淘宝店买鞋子800块钱
-- 小明-->成都 ATM
-- 淘宝店-->广州 ATM
start transaction;
update user set money=money-800 where name='小明';
update user set money=money+800 where name='淘宝店';
rollback;
2、read committed; 读已经提交的，不可重复读
-- 修改隔离事务级别
set global transaction isolation level read committed;
select @@global.tx_isolation;
-- 小张：银行的会计
start transaction;
select * from user;
-- 小张出去上厕所
-- 小王：
start transaction;
insert into user values(5,'c',100);
commit;
-- 小张回来
select avg(money) from user;
-- 此时虽然只能读到一个事务提交的数据，但还是会出现问题，发现读取同一个表的数据，发现不可以重复读
-- 这种情况叫做：不可重复读现象
3、repeatable read; 可以重复读，这种现象叫做幻读，查找不到，但插入时显示重复
-- 修改隔离事务级别
set global transaction isolation level repeatable read;
select @@global.tx_isolation;
-- 在这种隔离级别下，又会出现什么问题？
select * from user;
-- 张全蛋-成都
start trasaction;
-- 王尼玛-北京
start trasaction;
-- 张全蛋-成都
insert into user values(6,'d',1000);
-- 王尼玛-北京
insert into user values(6,'d',1000);
4、serializable; 串行化
-- 修改隔离事务级别
set global transaction isolation level serializable;
select @@global.tx_isolation;
select * from user;
-- 张全蛋-成都
start trasaction;
-- 王尼玛-北京
start trasaction;
-- 张全蛋-成都
insert into user values(7,'赵铁柱',1000);
-- 王尼玛-北京
select * from user;
-- 张全蛋-成都
insert into user values(8,'王小花',1000);
当user表被一个事务操作室，其他事务里的写作不可以进行
其他是物理的写作进入排队状态，串行化，直到另一个事务结束，这边操作才会继续执行，超时报错。
一旦另一事物commit，这边即可马上执行。
串行化带来的问题就是：性能极差。一般来说1》2》3》4
即隔离级别越高，性能越差。
mysql默认隔离级别是3、repeatable read;
210
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
