DDL操作数据库、表

操作数据库

查看数据库

show databases;

查询创建数据库mysql的字符集

show create database mysql;

创建数据库

create database db1; -- 创建db1数据库show create database db1; -- 查看db1数据库的字符集create database if not exists db2 -- 如果不存在就创建db2，判断操作create database db3 character set gbk; -- 将字符集设置成gbk编码
show create database db3create database if not exists db4 character set gbk; -- db4不存在的情况下，创建数据库db4，同时设置编码为gbk

修改数据库

alter database db3 character set utf8; -- 不能写成utf-8

删除数据库

drop database db3;drop database if exists db4;

使用数据库

select database(); -- 查询当前正在使用的数据库名称use db1; -- 使用数据库db1

操作表

查询表（use、show）

use db1;show tables; -- 显示当前数据库的所有表的名称desc db1; -- 查询当前表的结构

数据类型

• int 整数类型
• double(a,b) 浮点数类型，浮点数长a，小数点保留到b为
• date 日期类型，只包含年月日 yyyy-MM-dd
• datetime 日期类型，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
• timestamp 时间戳类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss。如果不给这个字段赋值，或为null，那么默认使用当前系统的时间
• varchar(a) 字符串类型,最大为a个字符长度
• 详细内容：菜鸟教程-Mysql数据类型

创建表（create）

create table student(id int,name varchar(32),age int,score double(4,1), -- 100.0birthday date,insert_time timestamp
);

复制表（like）

create table stu like student; -- 复制student表

删除数据表（drop table）

drop table stu;drop table if exists stu;

修改表（alter）

alter table student rename to stu; -- 将student表重命名show create table stu;
alter table stu character set utf8; -- 修改表的字符集alter table stu add gender varchar(10); -- 添加名为gender的列alter table stu change gender sex varchar(20); -- 修改名为gender的列名为sex，并修改数据类型
alter table stu modify sex varchar(10); -- 只改类型，不改名称alter table stu drop sex; -- 删除表中的sex列

DML 增删改表中数据

添加数据（insert）

insert into stu(id,name,age) values(1,'张无忌',18); -- 将数据插入到表当中INSERT INTO stu VALUES(1,'Tom',17,99.5,NULL,NULL); -- 省略列名的添加方式INSERT INTO stu VALUES(3,'Bob',19,88.5,"1993-11-11",NULL); -- 除了数字类型，其他类型都需要用单引号或者双引号引用起来。

删除数据（delete、truncate）

DELETE FROM stu WHERE id=3; -- 删除列名为id，值为3的一行记录DELETE FROM stu; -- 删除stu表中所有的数据，数据量大的时候开销大，有多少条记录就会执行多少次删除操作。TRUNCATE TABLE stu; -- 直接删除stu表，然后再创建一个不含数据但结构一样的stu表，数据量大的时候开销小

修改数据（update）

UPDATE stu SET age=22 WHERE id=2; -- 修改id=2的那一行的age属性值UPDATE stu SET age=18 ,score=100 WHERE id=3; -- 同时修改两个数据UPDATE stu SET age=20; -- 不加限制条件，则会修改age字段的所有数据都为20

DQL查询表中的记录

基础查询（select、DISTINCT、IFNULL()函数）

select * from stu;SELECT NAME,age FROM stu; --两列SELECT DISTINCT age FROM stu; -- distinct 去除重复的结果集SELECT NAME,math,english,math+english FROM stu; -- 两列数值可以直接相加，如有null参加运算，计算结果都为nullSELECT NAME,IFNULL(math,0),IFNULL(english,0),math+english FROM stu; -- 可以用ifnull 这个函数来进行null值判断，如果为null，那么这个数据就会当成0来计算SELECT NAME,IFNULL(math,0) AS 数学,IFNULL(english,0) AS 英语,math+english AS 总分 FROM stu; -- 使用as 进行起别名

条件查询（> < = ! & |、between and、or、and、in）

-- 比较运算符
SELECT * FROM stu WHERE math>80;
SELECT * FROM stu WHERE math=80;
SELECT * FROM stu WHERE math>=80;
SELECT * FROM stu WHERE math!=80;
SELECT * FROM stu WHERE math<>80; -- 不等号-- 逻辑运算符
SELECT * FROM stu WHERE math<=80 && math>90; -- 不推荐
SELECT * FROM stu WHERE math<=80 and math>90;
SELECT * FROM stu WHERE math BETWEEN 80 AND 90; -- 在80和90之间，包括80和90SELECT * FROM stu WHERE math=80 || math=90;-- 不推荐
SELECT * FROM stu WHERE math=80 OR math=90 OR math=100; -- 或者条件
SELECT * FROM stu WHERE math IN (80,90,100); -- 或者条件的简写

对null操作（is null、is not null）

-- 对null操作
SELECT * FROM stu WHERE birthday=NULL; -- 不正确，null不能使用比较运算符判断，应该用is
SELECT * FROM stu WHERE birthday IS NULL;
SELECT * FROM stu WHERE birthday IS NOT NULL;

模糊查询（%、_）

SELECT * FROM stu WHERE NAME LIKE '张%'; -- 查询名字第一个字是张
SELECT * FROM stu WHERE NAME LIKE "_无%"; -- 查询名字第二字是无的人
SELECT * FROM stu WHERE NAME LIKE "___"; -- 查询名字是三个字的人
SELECT * FROM stu WHERE NAME LIKE "%无忌%"; -- 查询名字包含无忌的人

排序查询（order by ASC\DESC）

SELECT * FROM stu ORDER BY math; -- 默认升序 asc
SELECT * FROM stu ORDER BY math ASC;
SELECT * FROM stu ORDER BY math DESC; -- 降序SELECT * FROM stu ORDER BY math ASC ,english ASC;-- 按照数学成绩排名，数学成绩一样，就按照英语成绩排序，都是升序。当第一个条件值排序一样的时候，才会去判断第二个条件

聚合函数（count()、max()、min()、sum()、avg()）

-- 将一列数据作为一个整体，做纵向计算SELECT COUNT(NAME) FROM stu; -- 计算name列中的属性个数
-- 注意：聚合函数会排除null值，解决方法：
SELECT COUNT(*) FROM stu;  -- 使用*计算
SELECT COUNT(id) FROM stu; -- 使用不为空的主键
SELECT COUNT(IFNULL(birthday,0)) FROM stu; -- 把null值替换成0select max(math),min(math),avg(math) from stu; -- 计算数学成绩的最大值，最小值，以及平均成绩
SELECT SUM(math) FROM stu; -- 计算数学总成绩
SELECT SUM(math,english) FROM stu; -- 报错，不能两个列进行操作

分组查询（group by … having…）

SELECT age,AVG(math) FROM stu GROUP BY age; -- 不同年龄的数学平均分
SELECT age,COUNT(id) FROM stu GROUP BY age; -- 查询不同年龄的人数分别有多少
SELECT age,AVG(math),COUNT(id) FROM stu GROUP BY age;SELECT age,AVG(math) FROM stu  WHERE math<90 GROUP BY age ; -- 查询成绩低于90分的不同年龄段的平均成绩
SELECT age,AVG(math) FROM stu  GROUP BY age HAVING age>18 ; -- 分组之后，年龄要大于18

• where 和 having的区别
  • 位置不同，where 在分组之前限定，如果不满足结果，则不参与分组。having在分组之后确定，如果不满足结果则不显示出来
  • where后面不能跟聚合函数，having后面可以跟聚合函数

分页查询（limit）

SELECT * FROM stu LIMIT 0,2; -- 从0开始查，查三条记录
SELECT * FROM stu LIMIT 2,2; -- 从第3条开始，查两条
-- 【开始的索引】=【当前的页码-1】*【每页显示的条数】
-- 最后一页有多少条显示多少条，不会报错

limit操作是一个MySQL方言，只能在MySQL数据库中用

约束

约束就是对表中数据进行限定，保证数据的正确性，有效性和完整性

分类：

• 主键约束
• 非空约束
• 唯一约束
• 外键约束

非空约束（not null）

某一列的值不能为空

-- 创建表添加非空约束
create table stu(id int,name varchar(20) not null -- name为非空
);

ALTER TABLE stu MODIFY NAME VARCHAR(20); -- 取消（删除）非空约束
ALTER TABLE stu MODIFY NAME VARCHAR(20) NOT NULL; -- 额外添加非空约束

唯一约束（unique）

某一列的值不能重复

CREATE TABLE stu(id INT,phone_number VARCHAR(20) UNIQUE -- 唯一约束-- 手机号不能重复
);

唯一约束可以有null值，但是只有一条记录可以有null

-- 删除唯一约束
ALTER TABLE stu MODIFY phone_number VARCHAR(20); -- 不能删除ALTER TABLE stu DROP INDEX phone_number; -- 正确删除

ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; -- 添加唯一约束，如果此时数据不满足列的unique，就会报错

主键（primary key）

非空且唯一，一张表只能有一个字段为主键

CREATE TABLE stu(id INT PRIMARY KEY, -- 主键NAME VARCHAR(20)
);

-- 删除主键
ALTER TABLE stu MODIFY id INT;  -- 不起作用ALTER TABLE stu DROP PRIMARY KEY; -- 正确删除

自动增长（auto_increment）

-- 自动增长，必须是数字类型
CREATE TABLE  stu(id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加自动增长主键约束NAME VARCHAR(20)
);INSERT INTO stu VALUES(NULL,'sss'); -- 此时会读取上一条id的值，并将此时的id为上一个id+1-- 删除自动增长
ALTER TABLE stu MODIFY id INT; -- 主键并不会被删除
-- 添加自动增长
ALTER TABLE stu MODIFY id INT AUTO_INCREMENT;

外键约束(FOREIGN KEY … REFERENCES …）

看一段数据：

CREATE TABLE emp(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(30),age INT,dep_name VARCHAR(30),dep_location VARCHAR(30)
);
SHOW TABLES;
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('张三',18,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('李四',20,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('王五',21,'研发部','广州');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('宋白',18,'销售部','深圳');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('张成',24,'销售部','深圳');
INSERT INTO emp(NAME,age,dep_name,dep_location) VALUES('孙七',23,'销售部','深圳');id  name       age  dep_name   dep_location  
------  ------  ------  ---------  --------------1  张三          20  研发部        广州        2  李四          20  研发部        广州        3  王五          21  研发部        广州        4  宋白          18  销售部        深圳        5  张成          24  销售部        深圳        6  孙七          23  销售部        深圳       

数据存在冗余，销售部一定在深圳，研发部一定在广州

解决方案，分成两张表，一张写员工，一张写部门

-- 部门表
CREATE TABLE department(id INT PRIMARY KEY AUTO_INCREMENT,dep_name VARCHAR(20),dep_location VARCHAR(20)
);
-- 员工表
CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,dep_id INT
);
SHOW TABLES;
DESC employee;
DESC department;-- 添加两个部门
INSERT INTO department VALUES(NULL,'研发部','广州'),(NULL,'销售部','深圳');INSERT INTO employee(NAME,age,dep_id) VALUE('张三',20,1);
INSERT INTO employee(NAME,age,dep_id) VALUE('李四',20,1);
INSERT INTO employee(NAME,age,dep_id) VALUE('王五',21,1);
INSERT INTO employee(NAME,age,dep_id) VALUE('宋白',18,2);
INSERT INTO employee(NAME,age,dep_id) VALUE('张成',24,2);
INSERT INTO employee(NAME,age,dep_id) VALUE('孙七',23,2);
SELECT * FROM employee;
SELECT * FROM department;id  name       age  dep_id  
------  ------  ------  --------1  张三          20         12  李四          20         13  王五          21         14  宋白          18         25  张成          24         26  孙七          23         2id  dep_name   dep_location  
------  ---------  --------------1  研发部        广州        2  销售部        深圳        

带来问题，两个表不能关联，需要添加外键

-- 部门表
CREATE TABLE department(id INT PRIMARY KEY AUTO_INCREMENT,dep_name VARCHAR(20),dep_location VARCHAR(20)
);
-- 员工表
CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,dep_id INT,CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id) -- 外键，dep_id和department表中的id关联了
);

-- 删除外键
ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk;-- 添加外键
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY (dep_id) REFERENCES department(id);

修改数据库引擎（ENGINE=INNODB）

如果外键不起作用，数据库表的默认引擎为MyISAM,这个引擎不支持外键，需要换成innoDB即可。

解决方法：

1. 修改表的引擎

SHOW TABLE STATUS LIKE 'employee'; -- 查看数据表引擎ALTER TABLE employee ENGINE=INNODB;

2. 新建表时直接给值

CREATE TABLE department(id INT PRIMARY KEY AUTO_INCREMENT,dep_name VARCHAR(20),dep_location VARCHAR(20)
)ENGINE = INNODB; -- 修改引擎
-- 员工表
CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(20),age INT,dep_id INT,CONSTRAINT dep_id_fk FOREIGN KEY(dep_id) REFERENCES department(id) -- 外键，dep_id和department表中的id关联了
)ENGINE = INNODB; -- 修改引擎

级联操作（ON UPDATE CASCADE）

外键的约束要更改相应的值就特别麻烦。

级联操作可以做到只要更改一处，相关联的就会接着更改

-- 添加外键，设置级联更新
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE; -- 级联删除，当把相关依赖删除后，对应的数据也会被删除
ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE ON DELETE CASCADE; 

数据库的设计

多表之间的关系

• 一对一的关系

  比如：人和身份证

  分析：一个人只有一个身份证，一个身份证也就只有一个人

• 一对多的关系

  比如： 部门和员工

  分析： 一个部门有多个员工，一个员工只能由一个部门

• 多对多的关系

  比如： 学生和课程

  分析： 一个学生可以选择很多课程，一个课程也可以被很多学生选择

一对多

员工和部门：

    id  dep_name   dep_location  
------  ---------  --------------1  研发部        广州        2  销售部        深圳        id  NAME       age  dep_id  
------  ------  ------  --------1  张三          20         12  李四          20         13  王五          21         14  宋白          18         25  张成          24         26  孙七          23         2

实现方式：在多的一方的外键指向一的一方的主键。

多对多

多对多的实现需要借助第三张中间表。

中间表至少包含两个字段，这两个字段作为第三张表的外键，分别指向两张表的主键

这两个字段叫联合主键

一对一

一对一的关系实现，可以在任意一方添加唯一的外键指向另一方的主键

实现

旅游网站

• 分类： cid name
• 线路表： rid name price cid
• 用户： uid username password
• 中间表：rid uid

-- 创建旅游分类表
-- cid为旅游路线分类的主键，自动增长
-- cname 为旅游路线分类的名称，非空且唯一
CREATE TABLE tab_category(cid INT PRIMARY KEY AUTO_INCREMENT,cname VARCHAR(100) NOT NULL UNIQUE
)ENGINE = INNODB;
-- 线路表
CREATE TABLE tab_route(rid INT PRIMARY KEY AUTO_INCREMENT,rname VARCHAR(100) NOT NULL UNIQUE,price DOUBLE,cid INT,FOREIGN KEY(cid) REFERENCES tab_category(cid)
)ENGINE = INNODB;-- 用户表
CREATE TABLE tab_user(uid INT PRIMARY KEY AUTO_INCREMENT,username VARCHAR(100) UNIQUE NOT NULL,PASSWORD VARCHAR(30) NOT NULL,sex CHAR(1) DEFAULT '男'
)ENGINE = INNODB;-- 中间表
CREATE TABLE tab_favorite(rid INT,DATE DATETIME,uid INT,-- 创建联合主键PRIMARY KEY(rid,uid),FOREIGN KEY(rid) REFERENCES tab_route(rid),FOREIGN KEY(uid) REFERENCES tab_user(uid)
)ENGINE = INNODB;

数据库范式

第一范式

所谓第一范式（1NF）是指在关系模型中，对于添加的一个规范要求，所有的域都应该是原子性的，即数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。

第二范式

在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖），即消除部分依赖

在一个表中，有学号、姓名、系名、系主任、课程名称、课程成绩

• 函数依赖：A->B ，如果通过A的属性（属性组）的值，可以确定唯一B属性的值，则称B依赖于A
  • 例如：学号–>姓名， （学号，课程名称）–>分数
• 完全函数依赖：A–>B，如果A是一个属性组，则B属性值得确定需要依赖于A属性组中的属性值
  • 例如：（学号，课程名称）–>分数
• 部分函数依赖：A–>B，如果A是一个属性组，则B属性值的确定只需要依赖于A属性组中的某一些值即可
  • 例如：（学号，课程名称）–>姓名
• 传递函数依赖：A–>B，B–>C ，如果通过A的属性（属性组）的值，可以确定唯一B属性的值，再通过A的属性（属性组）的值，可以确定唯一C属性的值
  • 例如：学号–>系名，系名–>系主任
• 码：如果在一张表中，一个属性或者属性组，被其他所有的属性完全依赖，那么就称其为码。
  • 例如：码为（学号，课程名称）
  • 主属性：码这个属性组中的所有属性
  • 非主属性：除过码属性组的属性

第三范式

在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）

数据库备份与还原

命令行

mysqldump -u root -p javaweb > C:/Desktop.sql -- 备份mysql -u root -p
drop database javaweb; -- 删除原来的数据库create database db1;
use db1;
source c:/Desktop.sqlmysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| department    |
| employee      |
| stu           |
| student       |
| tab_category  |
| tab_favorite  |
| tab_route     |
| tab_user      |
+---------------+
8 rows in set (0.00 sec)

图形化工具

有手就行

多表查询

笛卡尔积

SELECT * FROM emp,dept; -- 笛卡尔积：取emp,dept两个集合的组成情况
-- 会存在无用的数据

完成多表查询，需要消除无用的数据

内连接查询（隐式、显式）

• 从哪些表中查询数据
• 条件是声明
• 查询哪些字段

隐式内连接：使用where条件消除无用数据。

-- 查询员工表的名称、性别、部门表的名称
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id`=dept.`id`;-- 起别名
SELECT t1.name,t1.gender,t2.name
FROMemp t1,dept t2 -- 对表起别名，省略了as
WHERE t1.`dept_id`=t2.`id`;

显式内连接

SELECT * FROM emp INNER JOIN dept ON emp.`dept_id`=dept.`id`;
-- inner可以省略

外连接查询（左外，右外，全外）

左外连接：查询的是左表所有的数据以及其并集的部分

右外连接：查询的是右表所有的数据以及其并集的部分

-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
-- 内连接查询，查不到没有部门的人
SELECT t1.* ,t2.`dname`
FROM employee t1,dept t2
WHERE t1.`dep_id`=t2.`id`;id  NAME       age  dep_id  dname      
------  ------  ------  ------  -----------1  张三          20       1  研发部  2  李四          20       1  研发部  3  王五          21       1  研发部  4  宋白          18       2  学工部  5  张成          24       2  学工部  -- 左外连接:查询到了没有部门的人
SELECT t1.* ,t2.`dname`
FROM employee t1
LEFT JOIN dept t2
ON t1.`dep_id`=t2.`id`;id  NAME       age  dep_id  dname      
------  ------  ------  ------  -----------1  张三          20       1  研发部  2  李四          20       1  研发部  3  王五          21       1  研发部  4  宋白          18       2  学工部  5  张成          24       2  学工部  6  孙七          23  (NULL)  (NULL)    -- 右外连接
SELECT t1.* ,t2.`dname`
FROM employee t1
RIGHT JOIN dept t2
ON t1.`dep_id`=t2.`id`;id  NAME       age  dep_id  dname      
------  ------  ------  ------  -----------1  张三          20       1  研发部  2  李四          20       1  研发部  3  王五          21       1  研发部  4  宋白          18       2  学工部  5  张成          24       2  学工部  
(NULL)  (NULL)  (NULL)  (NULL)  销售部  
(NULL)  (NULL)  (NULL)  (NULL)  财务部  -- 全连接
-- MySQL不支持直接实现全连接，可以用union联合查询
SELECT *
FROM employee t1
RIGHT JOIN dept t2
ON t1.`dep_id`=t2.`id`
UNION
SELECT *
FROM employee t1
LEFT JOIN dept t2
ON t1.`dep_id`=t2.`id`;id  NAME       age  dep_id      id  dname      loc     
------  ------  ------  ------  ------  ---------  --------1  张三          20       1       1  研发部        北京  2  李四          20       1       1  研发部        北京  3  王五          21       1       1  研发部        北京  4  宋白          18       2       2  学工部        上海  5  张成          24       2       2  学工部        上海  
(NULL)  (NULL)  (NULL)  (NULL)       3  销售部        广州  
(NULL)  (NULL)  (NULL)  (NULL)       4  财务部        深圳  6  孙七          23  (NULL)  (NULL)  (NULL)     (NULL)  

子查询

概念：查询中嵌套查询，称嵌套查询为子查询

-- 查询工资最高的员工信息
SELECT * FROM employee WHERE employee.`salary`=(SELECT MAX(salary) FROM employee);id  NAME       age  dep_id  salary  
------  ------  ------  ------  --------1  张三          20       1      9000

子查询结果是单行单列的：子查询可以作为条件，使用运算符去判断。运算符 > >= < <= =

-- 查询员工工资小于平均工资的人
SELECT * FROM employee WHERE employee.`salary`<(SELECT AVG(salary) FROM employee);

**子查询结果是多行单列的：**子查询可以作为条件，使用 IN 关键字

-- 查询研发部和学工部所有的员工信息SELECT * FROM employee WHERE dep_id IN(SELECT id FROM dept WHERE dname='研发部' OR dname='学工部');

子查询结果是多行多列的：可以把查询结果是一张虚拟表参与查询

-- 查询员工入职日期是2019年3月23日之后的员工信息和部门信息
SELECT * FROM dept t1,(SELECT * FROM employee WHERE employee.`join_date` > '2019-03-23') t2
WHERE t1.id=t2.dep_id;-- 也可以用笛卡尔积多表查询
SELECT t1.`dname`,t2.*
FROM dept t1,employee t2
WHERE t1.`id`=t2.`dep_id` AND t2.`join_date`>'2019-03-23';

事务

• 事务的基本介绍
• 事务的四大特征
• 事务的隔离级别（了解）

事务的基本介绍

如果一个包含多个步骤的业务操作，被事务管理，那么这些操作要么同时成功，要么同时失败。

比如，张三给李四转账500块：

1. 查询张三账户余额是否大于500
2. 张三账户金额减500
3. 李四账户金额加500

如果2、3部之间出现异常，那么张三的账户减500，李四的账户不会加500.

被事务管理之后，这三个操作变成了一个整体，要么同时成功要么同时失败。

如果出现了异常，就会回滚，没有出现异常，就会提交事务

-- 创建账户表
USE javawebCREATE TABLE account(id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),balance DOUBLE
);
DROP TABLE account;
INSERT INTO account(NAME,balance) VALUES('lisi',232234),('zhangsan',2323423);
SELECT * FROM account;id  name      balance  
------  --------  ---------1  lisi         2322342  zhangsan    2323423

回滚操作：

UPDATE account SET balance=1000;
-- 转账的操作：张三给李四转账500元
-- 0. 开始事务
START TRANSACTION;
-- 1. 查询张三账户余额是否大于500
-- 2. 张三账户金额减500
UPDATE account SET balance=balance-500 WHERE NAME='zhangsan';
-- 3. 李四账户金额加500
SELECT adadfsaf FROM adfadsfa;
UPDATE account SET balance=balance+500 WHERE NAME='lisi';
-- 4.回滚事务
ROLLBACK;
-- 5.提交事务
COMMIT;

事务提交方式

MySQL数据库中会自动提交记录

事务提交的两种方式：

• 自动提交：
  • MySQL当中就是自动提交的
  • 一条dml（增删改查）都会自动提交一次事务
• 手动提交：
  • 需要先开启事务，再提交

修改事务的默认提交方式：

• 查看事务的提交方式：

SELECT @@autocommit; -- 1代表自动提交，0代表手动提交

• 修改默认提交方式

SET @@autocommit=0;
UPDATE account SET balance=30;
COMMIT;

事务的四大特征

1. 原子性：不可分隔的最小操作单位，要么同时成功，要么同时失败
2. 持久性：当事务提交或回滚后，数据库会持久化地保存数据
3. 隔离性：多个事务之间。相互独立。
4. 一致性：事务操作前后数据总量不变。

事务的隔离级别

概念：多个事务之间相互独立的。但是如果多个事务操作同一个批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。

存在问题：

• 脏读：一个事务，读取到另一个事务中没有提交的数据
• 不可重复读：同一个事务中两次读取到的数据不一样
• 幻读：一个事务操作数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。

隔离级别：

1. read uncommitted：读未提交

   脏读、不可重复读、幻读

2. read committed：读已提交

   不可重复读，幻读

3. repeatable read：可重复读

   幻读

4. serializable：串行化

注意：隔离级别从小到大安全性越来越高，但是效率越来越低

数据库查询隔离级别：

select @@tx_isolation;

数据库设置隔离级别：

set global transaction isolation level 级别字符串（比如：read uncommitted）

DCL

• DDL:操作数据库和表
• DML：增删改表中数据
• DQL：查询表中数据
• DCL：管理用户，授权

管理用户

• 创建用户

-- 创建admin用户,只用自己的电脑可以访问
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'admin123';
-- 创建users用户，可以在任意电脑上访问
CREATE USER 'users'@'%' IDENTIFIED BY 'users123';

• 删除用户

-- 删除用户users
DROP USER 'users';

• 查询用户

USE mysql -- 用户信息在mysql中SELECT * FROM USER; -- 可以查询到所有用户的信息

• 修改密码

-- 修改admin密码为admin456 
FLUSH PRIVILEGES; -- 刷新权限
UPDATE USER SET authentication_string = PASSWORD('admin456') WHERE USER='admin';
SET PASSWORD FOR 'admin'@'localhost'=PASSWORD('admin456');

• 忘记密码

-- 管理员运行cmd
net stop mysql
-- 无验证启动MySQL服务
mysqld --skip-grant-tables
-- 更改密码
-- 结束mysqld.exe服务
-- 打开新cmd，进入mysql

权限管理

• 查询权限

SHOW GRANTS FOR 'root'@'localhost';

• 授予权限

-- 授予admin用户查询和修改javaweb数据库中`account`表的权限
GRANT SELECT,ALTER ON javaweb.`account` TO 'admin'@'localhost';-- 授予admin用户查询和修改db1数据库中`employee`表的权限
USE db1;
GRANT SELECT,ALTER ON employee TO 'admin'@'localhost';

• 撤销权限

-- 撤销admin查看db1数据库中employee数据表的权限
use db1;
REVOKE SELECT ON employee FROM 'admin'@'localhost';