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';