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 db3
create 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.0
birthday 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参加运算，计算结果都为null
SELECT 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和90
SELECT * 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值替换成0
select 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 1
2 李四 20 1
3 王五 21 1
4 宋白 18 2
5 张成 24 2
6 孙七 23 2
id 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即可。
解决方法：
- 修改表的引擎
SHOW TABLE STATUS LIKE 'employee'; -- 查看数据表引擎
ALTER TABLE employee ENGINE=INNODB;
- 新建表时直接给值
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 1
2 李四 20 1
3 王五 21 1
4 宋白 18 2
5 张成 24 2
6 孙七 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.sql
mysql> 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
FROM
emp 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块：
- 查询张三账户余额是否大于500
- 张三账户金额减500
- 李四账户金额加500
如果2、3部之间出现异常，那么张三的账户减500，李四的账户不会加500.
被事务管理之后，这三个操作变成了一个整体，要么同时成功要么同时失败。
如果出现了异常，就会回滚，没有出现异常，就会提交事务
-- 创建账户表
USE javaweb
CREATE 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 232234
2 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;
事务的四大特征
- 原子性：不可分隔的最小操作单位，要么同时成功，要么同时失败
- 持久性：当事务提交或回滚后，数据库会持久化地保存数据
- 隔离性：多个事务之间。相互独立。
- 一致性：事务操作前后数据总量不变。
事务的隔离级别
概念：多个事务之间相互独立的。但是如果多个事务操作同一个批数据，则会引发一些问题，设置不同的隔离级别就可以解决这些问题。
存在问题：
- 脏读：一个事务，读取到另一个事务中没有提交的数据
- 不可重复读：同一个事务中两次读取到的数据不一样
- 幻读：一个事务操作数据表中的所有记录，另一个事务添加了一条数据，则第一个事务查询不到自己的修改。
隔离级别：
-
read uncommitted：读未提交
脏读、不可重复读、幻读
-
read committed：读已提交
不可重复读，幻读
-
repeatable read：可重复读
幻读
-
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';
939
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
