MySQL数据库

安装

命令行连接到MySQL

在MySQL服务启动后，输如
mysql -h localhost -P 3306 -u root -proot1234
注意：-h 和 -P不写默认就是主机和3306

三层结构

SQL语句分类

java操作MySQL（体会）

创建数据库

注意： 如果是collect: utf8_bin，则表中的数据区分大小写
默认是不区分大小写的

查看或删除数据库

备份和恢复数据库

例如：
备份： mysqldump -u root -p -B bj_db02 >/Users/bijing/documents/bj.sql
恢复：source /Users/bijing/documents/bj.sql
或者执行这个sql

例如：mysqldump -u root -proot1234 bj_db03 goods >/Users/bijing/documents/bj.sql

创建表

MySQL常用数据类型

整型

bit

小数类型

字符串的基本使用

注意：varchar存放的是字符，和字节编码有关，在gbk中两个字节代表一个字符，在utf8中三个字节代表一个字符，并且在varchar中有3个字节是用来存放size的

字符串使用细节

日期类型的基本使用

修改表

#修改表名test改为emp
RENAME TABLE test TO emp;

#修改表,添加image列,在birthday后面
ALTER TABLE emp
    ADD image varchar(32) NOT NULL DEFAULT '' AFTER birthday;

#修改password,使其长度20个字符
ALTER TABLE emp
    MODIFY password varchar(20) NOT NULL DEFAULT '';

#删除image列
ALTER TABLE emp
    DROP image;

#修改字符集utf8
ALTER TABLE emp
    CHARACTER SET utf8;

#列名id改为userId
ALTER TABLE emp
    CHANGE id userid varchar(10) NOT NULL DEFAULT '';
DESC emp;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

数据库curd语句

insert语句

细节说明

1. 底层会尝试把字符串转成整型，能转就可以插入
2. 建议插入单行数据用value，多行用values，虽然说都可以通用

update语句

delete语句

如：DELETE FROM goods WHERE id = ‘1004’;
删除列：ALTER TABLE goods DROP date ;

select语句

注意：between and 是闭区间

统计函数

order by

count

注意：
– count() 和 count(列)的区别
– count()返回满足条件的总行数,
– count(列)统计满足条件的某列有多少个,但是会排除为空的情况

sum

Max/min

分组统计group by

字符串函数

-- 演示字符串函数的使用

#charset(str) 返回字符串集
SELECT CHARSET(ename)
FROM emp;

#concat(string2 [,..]) 连接字串,将多个列拼接成一列
SELECT CONCAT(ename, ' job is ', job)
FROM emp;

# instr(string,substring) 返回substring在string中出现的位置,第一个位置1,没有返回0
-- dual亚元表,系统表 可以作为一个测试表使用
SELECT INSTR('bijing', 'ng')
FROM dual;

# ucase(string) 转成大写
SELECT UCASE(ename)
FROM emp;

#lcase(string) 转小写
SELECT LCASE(job)
FROM emp;

#left(string2, length) ,从string2中的左边取length个字符
SELECT LEFT('bijing', 5)
FROM dual;
#right(str,length),从str中往右去length个字符

#length(string) ,返回string长度
SELECT LENGTH(ename)
FROM emp;

#replace(str,search_str,replace_str) 在str中用replace_str替换search_str,区分大小写
SELECT REPLACE(ename, 'KING', 'bijing')
FROM emp;

#strcmp(str1,str2) 逐字符比较两个字符串大小
SELECT STRCMP(ename, 'HAN'), ename, 'HAN'
FROM emp;

#substring(str,position [,length]),从str的position开始,从1开始计算
SELECT SUBSTRING(ename, 2, 3)
FROM emp;
SELECT SUBSTRING(ename, 2)-- 不加length就是从position取到尾
FROM emp;

#ltrim(string2) 去除左端空格    rtrim(string2)去除右端空格 trim两边都去
SELECT LTRIM('   我是张无忌 哈哈')
FROM emp;

#首字母小写,显示emp所有员工姓名
SELECT CONCAT(LCASE(SUBSTRING(ename, 1, 1)), SUBSTRING(ename, 2))
FROM emp;

SELECT CONCAT(LCASE(LEFT(ename, 1)), RIGHT(ename, LENGTH(ename) - 1))
FROM emp;

SELECT REPLACE(ename, LEFT(ename, 1), LCASE(LEFT(ename, 1)))
FROM emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

数学相关函数

-- 演示数学相关函数
#abs(num) 绝对值
SELECT ABS(-2)
FROM dual;

#bin(num)十进制转二进制
SELECT BIN(10)
FROM dual;

#ceiling(num) 向上取整,
SELECT CEILING(-2.1)
FROM dual;

#floor(num) 向下取整
SELECT FLOOR(-2.1)
FROM dual;

#CONV(num, from_base, to_base)
SELECT CONV(5, 10, 2)-- 5是十进制的5,转成二进制
FROM dual;
SELECT CONV(16, 16, 10)-- 16进制的16转成10进制
FROM dual;

#format(number,decimal_places)保留小数位数
SELECT FORMAT(3.141592653, 5)
FROM dual;

#hex()转16进制
SELECT HEX(18)
FROM dual;

#least(num1,num2,....)求最小值
SELECT LEAST(3.4, 2.4, -4.2)
FROM dual;

#mod(numerator,denominator)求余
SELECT MOD(10, 3)
FROM dual;

#rand([seed]),返回一个随机数范围0<=v<=1.0,
-- 说明:如果放入了seed,生成一个随机数,下次查询的时候还是这个随机数
SELECT RAND(3)
FROM dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

时间日期相关的函数

补充：last_day（日期），返回日期中的最后一天

-- 日期时间相关的函数

# current_date()当前日期
SELECT CURRENT_DATE()
FROM dual;

# current_time()当前时间
SELECT CURRENT_TIME
FROM dual;

# current_timestamp()当前时间戳,年月日,时分秒
SELECT CURRENT_TIMESTAMP
FROM dual;

#date(datetime)返回datetime的日期部分
SELECT DATE(CURRENT_TIMESTAMP)
FROM dual;

#date_add(date2,interval d_value d_type)在date2中加上时期或者时间
-- 从当前时间开始,加上一个小时
SELECT DATE_ADD(CURRENT_TIMESTAMP, INTERVAL 1 HOUR)
FROM dual;
-- 从发送时间开始,加上10分钟大于现在的时间(即10分钟以内的新闻)
SELECT *
FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();
#date_sub(date2,interval d_value d_type)在date2上减去一个时间
-- 同上
#datediff(date1,date2)两个日期相差多少天,(date1-date2)
SELECT DATEDIFF('2010-1-1', '2010-1-5')
FROM dual;
#timediff(time1,time2)两个时间相差多少小时多少分钟多少秒
SELECT TIMEDIFF('2022-07-15 19:25:08', NOW())
FROM dual;
#now()当前时间

#year|month|date(datetime)
-- 只要年
SELECT YEAR(NOW())
FROM dual;

SELECT MONTH('2022-2-10')
FROM dual;

#unix_timestamp()返回的是1970-1-1 到现在的秒数
SELECT UNIX_TIMESTAMP()
FROM dual;

#from_unixtime() 把一个unix_timestamp秒数转成指定格式的日期
-- 相当于系统时间加上指定的毫秒数,并转为指定的日期格式
-- %Y-%m-%d格式是固定的,小写y,年缩写,大写M英文月,大写D天th ,小写h 12小制
SELECT FROM_UNIXTIME(1657886513, '%Y-%m-%d %H:%i:%s')
FROM dual;

-- mes案例
-- 显示所有的新闻信息,发布日期只显示日期,不用显示时间
SELECT id, content, DATE(send_time)
FROM mes;

-- 查询10分钟内发布的新闻
SELECT *
FROM mes
WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();

-- 2011-11-11和1990-1-1差多少天
SELECT DATEDIFF('2011-11-11', '1990-1-1')
FROM dual;

-- 求自己活了多少天
SELECT DATEDIFF(NOW(), '1996-09-28')
FROM dual;

-- 如果能活80岁,自己还能活多少天
SELECT DATEDIFF(DATE_ADD('1996-09-28 1:34:00', INTERVAL 80 YEAR), NOW())
FROM dual;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75

加密和系统的函数

流程控制函数

注意：ifnull(expr1,expr2)中，如果为空返回第二个表达式

-- 演示流程控制函数

# if(expr1,expr2,expr3) ,如果表达式expr1为true,则返回expr2,否则expr3
SELECT IF(TRUE, '北京', '南京')
FROM dual;

# ifnull(expr1,expr2) 如果expr1不为空,则返回expr1,否则返回expr2
SELECT IFNULL(NULL, '为空')
FROM dual;
SELECT IFNULL('不为空', '为空')
FROM dual;

# SELECT CASE WHEN expr1 THEN expr2 WHEN expr3 THEN expr4 ELSE expr5 END;
-- 如果第一个表达式为true,则返回expr2,如果expr3为true,则返回expr4,否则返回expr5(还能继续expr....)
SELECT CASE WHEN FALSE THEN 'expr1为true' WHEN TRUE THEN 'expr1为false,expr3为true' ELSE 'expr1和expr3都是false' END;

-- 案例
#查询emp表,如果comm是null,则显示0.0
SELECT ename, IFNULL(comm, 0.0)
FROM emp;

SELECT ename, IF(comm IS NULL, 0.0, comm)
FROM emp;
#如果emp表的job是clerk则显示职员,如果是manager则显示经理,如果是salesman则显示销售人员,其他正常显示
SELECT ename,
       CASE
           WHEN job = 'clerk' THEN '职员'
           WHEN job = 'manager' THEN '经理'
           WHEN job = 'salesman' THEN '销售人员'
           ELSE job END
FROM emp;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

select增强

-- 查询加强
#查询1992.1.1后入职的员工
-- 在mysql中日期类型可以直接比较
SELECT *
FROM emp
WHERE hiredate > '1992.1.1';

#模糊查询,
-- %表示0到多个任意字符, _表示单个任意字符
#首字母为S的员工姓名和工资
SELECT ename, sal
FROM emp
WHERE ename LIKE 'S%';
#第三个字母为O的所有员工姓名和工资
SELECT ename, sal
FROM emp
WHERE ename LIKE '__O%';

#显示没有上级的雇员情况
SELECT *
FROM emp
WHERE mgr IS NULL;

#查询表结构 selectinc_sql
DESC emp;

-- 使用order by
#工资从低到高,显示雇员信息
SELECT *
FROM emp
ORDER BY sal;

#部门号升序,雇员工资降序,显示雇员信息
SELECT *
FROM emp
ORDER BY deptno ASC, sal DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

分页查询

-- 演示分页查询

#按雇员id升序取出,每页显示3条,显示第1,2,3页
-- 第一页
SELECT *
FROM emp
ORDER BY empno
LIMIT 0,3;
-- 第二页
SELECT *
FROM emp
ORDER BY empno
LIMIT 3,3;
-- 第三页
SELECT *
FROM emp
ORDER BY empno
LIMIT 6,3;
-- 推导出公式
/*SELECT *
FROM emp
ORDER BY empno LIMIT 每页显示的记录数*(第几页-1),每页显示记录数;*/
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

分组增强

-- 演示分组函数和分组子句group by
#显示每种岗位的雇员总数,平均工资
SELECT job, COUNT(*), AVG(sal)
FROM emp
GROUP BY job;

#显示雇员总数,以及获取补助的雇员人数
SELECT COUNT(*), COUNT(comm)
FROM emp;

#显示管理者总人数
SELECT COUNT(DISTINCT mgr)
FROM emp;

#显示雇员工资的最大差额
SELECT MAX(sal) - MIN(sal)
FROM emp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

增强总结（顺序）

先分组，再排序，最后再分页

#统计各个部门的平均工资,并且是大于1000的,并按平均工资降序,取出前两行
SELECT deptno, AVG(sal) avg_sal
FROM emp
GROUP BY deptno
HAVING avg_sal > 1000
ORDER BY avg_sal DESC
LIMIT 0,2;
1
2
3
4
5
6
7

多表查询

笛卡尔积

注意：条件用where，并且条件不能少于表的个数减一

-- 演示多表查询

# 显示雇员工资及所在部门名字
SELECT ename, sal, dname
FROM emp,
     dept
WHERE emp.deptno = dept.deptno;

#部门号为10的部门名,员工名和工资
SELECT dname, ename, sal
FROM emp,
     dept
WHERE emp.deptno = dept.deptno
  AND emp.deptno = 10;

#显示各个员工的姓名,工资,以及工资级别
SELECT ename, sal, grade
FROM emp,
     sal_grade
WHERE sal BETWEEN sal_grade.losal AND sal_grade.hisal;

#显示雇员名,雇员工资,以及所在部门的名字,并按部门排序
SELECT ename, sal, dname
FROM emp,
     dept
WHERE emp.deptno = dept.deptno
ORDER BY emp.deptno;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

自连接

自连接的特点：

1. 把同一张表当做两张表来使用
2. 要给表取别名
3. 列名不明确，可以指定列的别名

#显示公司员工和他上级的名字
SELECT e1.ename, e2.ename mgr
FROM emp e1,
     emp e2
WHERE e1.mgr = e2.empno;

1
2
3
4
5
6

子查询

临时表

SELECT goods_id, ecs_goods.cat_id, goods_name, shop_price
FROM (SELECT cat_id, MAX(shop_price) max_price FROM ecs_goods GROUP BY cat_id) tmp,
     esc_goods
WHERE esc_goods.cat_id = tmp.cat_id
  AND esc.shop_price = tmp.max_price;
1
2
3
4
5

多行子查询中all和any

多列子查询

子查询练习

-- 子查询联系

#查找每个部门工资高于本部门平均工资的人的资料
SELECT *
FROM emp e1,
     (
         SELECT deptno, AVG(sal) avg_sal
         FROM emp
         GROUP BY deptno) e2
WHERE e1.sal > e2.avg_sal
  AND e1.deptno = e2.deptno;

#查找每个部门工资最高人的详细资料

#多列in
SELECT *
FROM emp
WHERE (deptno, sal) IN (SELECT deptno, MAX(sal)
                        FROM emp
                        GROUP BY deptno);
#临时表
SELECT *
FROM emp,
     (SELECT deptno, MAX(sal) max_sal
      FROM emp
      GROUP BY deptno) tmp
WHERE emp.deptno = tmp.deptno
  AND emp.sal = max_sal;

#查询每个部门的信息,包括(部门名,编号,地址)和人员数量
SELECT dname, dept.deptno, loc, num
FROM dept,
     (SELECT deptno, COUNT(*) num FROM emp GROUP BY deptno) tmp
WHERE dept.deptno = tmp.deptno;

SELECT tmp.*, dname, loc
FROM dept,
     (SELECT COUNT(*) AS per_num, deptno FROM emp GROUP BY deptno) tmp
WHERE tmp.deptno = dept.deptno;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

表复制和去重

-- 演示表的复制
-- 为了对某个SQL语句进行效率测试,我们需要海量数据时,可以通过此法为表创建海量数据

CREATE TABLE my_tab01
(
    id     int,
    name   varchar(32),
    sal    double,
    job    varchar(32),
    deptno int
);


-- 1.先把emp表的记录复制到my_tab01中
INSERT INTO my_tab01 (id, name, job, deptno, sal)
SELECT empno, ename, job, deptno, sal
FROM emp;
-- 2.自我复制
INSERT INTO my_tab01
SELECT *
FROM my_tab01;

SELECT COUNT(*)
FROM my_tab01;

-- 如何删除一张表中的重复记录
#1.先创建一个表my_table01
-- 这个语句把emp表的结构复制过来
CREATE TABLE my_tab02 LIKE emp;

#2.让my_tab02有重复记录
INSERT INTO my_tab02
SELECT *
FROM emp;

#3考虑去重my_tab02中的数据
/*
思路:先创建一张临时表my_tmp,该表的结构和my_tab02一样
    把my_tmp的记录通过distinct关键字处理后,把记录复制到my_tmp
    清除my_tab02记录
    把my_tmp记录复制到my_tab02
    drop临时表
*/
CREATE TABLE my_tmp LIKE my_tab02;
INSERT INTO my_tmp
SELECT DISTINCT *
FROM my_tab02;
DELETE
FROM my_tab02;
INSERT INTO my_tab02
SELECT *
FROM my_tmp;
DROP TABLE my_tmp;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53

合并查询

-- 演示合并查询

# union all 就是将两个查询结果合并,不去重
SELECT ename, sal, job
FROM emp
WHERE sal > 2500
UNION ALL
SELECT ename, sal, job
FROM emp
WHERE job = 'MANAGER';

# union  就是将两个查询结果合并,会去重
SELECT ename, sal, job
FROM emp
WHERE sal > 2500
UNION
SELECT ename, sal, job
FROM emp
WHERE job = 'MANAGER';


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

表的外连接

-- 演示外连接

#列出部门名称和这些部门的员工名称和工作,同时要求显示出那些没有员工的部门
SELECT dname, ename, job
FROM emp
         RIGHT JOIN dept d ON emp.deptno = d.deptno
ORDER BY dname;

#显示所有人成绩,如果没有成绩,也要显示该人的姓名和id
SELECT name, grade
FROM stu
         LEFT JOIN
     exam ON stu.id = exam.id;

#显示所有成绩如果没有名字匹配显示空
SELECT name, grade
FROM stu
         RIGHT JOIN exam ON stu.id = exam.id;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

MySQL约束

主键

-- 演示主键使用

CREATE TABLE t
(
    id    int PRIMARY KEY,
    name  varchar(32),
    email varchar(32)
);


-- 主键列的值是不可以重复的
INSERT INTO t
VALUES (1, 'jack', 'jack@sohu.com');
INSERT INTO t
VALUES (2, 'tom', 'tom@sohu.com');
# INSERT INTO t VALUE (1, 'bj', 'bj@sohu.com')


-- 主键使用的细节
/*
1.主键不能重复并且不能为空
2.一张表最多只有一个主键,但是可以有复合主键
复合主键:
create table ..(
...
primary key(A,B) --这里就是复合主键
)

3.主键的指定方式有两种:
    直接在字段名后面指定:字段名 主键
    在表定义,括号的最后一行中写 主键  (列名)
4.使用desc 表名 可以看到主键
5.实际开发中往往每个表都会设计一个主键
*/


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

非空和唯一

#not null非空

#unique 唯一(如果没有指定字段非空,该字段可以有多个null)

1
2
3
4

外键

-- 外键

# 外键一般是另一张表的主键
# 字段有外键约束时,该字段添加的数据如果再作为主键的表中不存在的话会添加失败
# 字段有外键约束时,在该字段作为主键的表中要删除一行数据必须先删除该字段作为外键的表中的数据

1
2
3
4
5
6

check

-- 演示check约束

CREATE TABLE t2
(
    id   int PRIMARY KEY,
    name varchar(32),
    sex  varchar(6) CHECK ( sex IN ('man', 'woman') ),
    sal  double CHECK ( sal > 1000 AND sal < 2000 )
);

-- 失败
INSERT INTO t2
VALUES (1, 'jack', 'mid_sex', 100);
-- 成功
INSERT INTO t2
VALUES (1, 'john', 'man', 1500);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

商店表设计

自增长

细节

注意：

1. 自增长要是唯一的，primary key或者unique
2. 如果插入values过程中手动赋值如666，下一行数据就是667,668…
   

-- 演示自增长

#AUTO_INCREMENT设置自增长
CREATE TABLE increment
(
    id    int PRIMARY KEY AUTO_INCREMENT,
    email varchar(32) NOT NULL DEFAULT '',
    name  varchar(32) NOT NULL DEFAULT ''
);

# 方式一 values(null,...)
INSERT INTO increment
VALUES (NULL, 'jack@qq.com', 'jack'),
       (NULL, 'john@qq.com', 'john');

# 方式二 和上面类似,值写null
INSERT INTO increment (id, email, name)
VALUES (NULL, 'tom@qq.com', 'tom');

# 方式三 自增的字段不写,值也不写
INSERT INTO increment(email, name)
VALUES ('jack@qq.com', 'jack');


# 修改默认的自增长开始值
ALTER TABLE increment
    AUTO_INCREMENT = 100;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

MySQL的索引

-- 创建索引后只对创建了索引的列有效
CREATE INDEX empno_index ON emp (empno);
1
2

索引的原理

索引的类型

索引使用

-- 演示索引的使用
# 创建索引
CREATE TABLE t3
(
    id   int,
    name varchar(32)
);

#查询是否有索引
SHOW INDEXES FROM t3;

#添加索引
-- 添加唯一索引
CREATE UNIQUE INDEX id_index ON t3 (id);
-- 添加普通索引(id可重复)
CREATE INDEX id_index ON t3 (id);
-- 添加普通索引方式二
ALTER TABLE t3
    ADD INDEX id_index (id);
-- 添加主键索引(可以直接使用primary key)或者
ALTER TABLE t3
    ADD PRIMARY KEY (id);

#删除索引
-- 删除普通索引
DROP INDEX id_index ON t3;
-- 删除主键索引
ALTER TABLE t3
    DROP PRIMARY KEY;

#修改索引,先删除,再添加新的索引

-- 查询索引
SHOW INDEXES IN t3; -- in/from
SHOW INDEX FROM t3;
SHOW KEYS FROM t3;
DESC t3;


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

创建索引规则

MySQL事务

-- 演示事务

-- 1.创建测试表
CREATE TABLE t4
(
    id     int,
    `name` varchar(32)
);
-- 2.开始事务
START TRANSACTION;
-- 3.设置保存点
SAVEPOINT a;
-- 执行dml操作
INSERT INTO t4
VALUES (109, 'tom');

SELECT *
FROM t4;

SAVEPOINT b;
-- 执行dml操作
INSERT INTO t4
VALUES (200, 'jack');

SELECT *
FROM t4;

-- 5.回退到b
ROLLBACK TO b;
-- 回退到a(会删除a后面的所有保存点)
ROLLBACK TO a;

-- 回退全部事务(回退到事务开始的地方)
ROLLBACK;

-- 6.提交事务(会删除保存点)
COMMIT;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

事务细节

MySQL事务隔离级别

脏读：事务b读取了事务a的数据（未提交），事务a又进行了回滚操作，导致事务b读取的数据不正确
不可重复读：事务b读取事务a的数据（已经提交），但是事务a的数据又进行了修改或者删除，导致事务b第一次读出的结果可能无法复现
幻读：事务b读取事务a的数据（已经提交），但是事务a的数据又进行了插入，导致事务b读出的数据可能不一样

-- 演示事务隔离

CREATE DATABASE IF NOT EXISTS transaction;
USE transaction;

-- 1.开了两个mysql的控制台
-- 2.查看当前mysql的事务隔离级别
-- mysql8以前  SELECT @@tx_isolation;
SELECT @@transaction_isolation;
# REPEATABLE-READ默认是可重复的

-- 3.把其中一个事务的隔离级别设置成 Read uncommitted(默认是REPEATABLE READ)
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
# SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
# SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
-- 4.启动事务
START TRANSACTION;
-- 5.创建表
CREATE TABLE account
(
    id    int,
    name  varchar(32),
    money int
);

-- 6.插入数据
INSERT INTO account
VALUES (100, 'tom', 1000);

## 此时控制台2可以读到控制台1未提交的数据,这叫脏读

-- 7.修改数据
UPDATE account
SET money =800
WHERE id = 100;

-- 8.添加数据
INSERT INTO account
VALUES (200, 'jack', 2000);

-- 9.控制台1提交事务(如果控制台2也提交事务,则代表演示结束了)
COMMIT;

## 此时控制台2可以读到控制台1提交后的插入和修改的数据,分别出现了幻读和不可重复读

-- 补充
/*
步骤3中改为 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
则不会出现脏读,会出现不可重复读和幻读

步骤3中改为 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
则不会出现脏读,不可重复读,幻读

步骤3中改为 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
则当控制台1操作事务时,控制台2的事务会卡住,因为控制台1操作事务时加锁了
*/

-- 几个指令
-- 查看当前会话的隔离级别
SELECT @@transaction_isolation;
-- 查看系统当前隔离级别
SELECT @@global.transaction_isolation;
-- 设置当前会话的隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 设置系统的当前隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67

设置隔离级别（几个指令）

注意：

1. mysql8以前 SELECT @@tx_isolation; mysql8以后SELECT @@transaction_isolation;
   

MySQL事务的ACID

MySQL存储引擎

基本介绍

主要的存储引擎特点

细节说明

视图

视图和基本表的关系

视图的基本使用

视图的使用细节

-- 演示视图的使用

-- 创建一个视图emp_view01,只能查询emp表的(empno,ename,job和deptno)信息

-- 创建视图
USE bj_select;
CREATE VIEW emp_view01 AS
SELECT empno, ename, job, deptno
FROM emp;

-- 查看视图
DESC emp_view01;

SELECT *
FROM emp_view01;

-- 查看创建视图的指令
SHOW CREATE VIEW emp_view01;

-- 修改视图
    ALTER VIEW emp_view01 AS SELECT empno, ename
                             FROM emp;

-- 删除视图
DROP VIEW emp_view01;

-- 视图的细节
/*
1.创建视图后,到数据库中去看,对应视图只有一个视图的结构文件(视图.frm)
2.视图的数据变化会影响到基表,基表的数据变化也会影响到视图
3.视图中可以再使用视图,数据来源基表

*/

-- 修改视图内容
UPDATE emp_view01
SET job = 'MANAGER'
WHERE empno = '7369';

CREATE VIEW emp_view02 AS
SELECT empno, ename
FROM emp_view01;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

视图的最佳实践

mysql管理

创建用户和修改密码

-- 演示数据库管理

-- 当我们做开发时可以根据不同的开发人员,赋予它相应的mysql操作权限
-- 所以DBA可以根据需要,创建不同的用户,赋予不同的权限,给不同的人员使用

-- 创建用户,'bijing' @'localhost' 表示用户的完整信息,用户名+登陆地
-- 这边的密码存放是password()加密后的
CREATE USER 'bijing' @'localhost' IDENTIFIED BY 'root1234';
SELECT *
FROM mysql.user;

SELECT host, user, authentication_string
FROM mysql.user;

-- 删除用户
DROP USER 'bijing'@'localhoset';

-- 修改自己的密码(mysql5中可以使用,mysql8会报错)
SET PASSWORD = PASSWORD ('1234');

-- 修改别人的密码,需要权限(mysql5中可以使用,mysql8会报错)
SET PASSWORD FOR 'bijing' @'localhost' = PASSWORD ('123456');

-- mysql8中修改密码
ALTER USER 'bijing' @'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '123456';


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

MySQL中的权限管理

权限列表

给用户授权

-- 演示数据库管理

-- 当我们做开发时可以根据不同的开发人员,赋予它相应的mysql操作权限
-- 所以DBA可以根据需要,创建不同的用户,赋予不同的权限,给不同的人员使用

-- 创建用户,'bijing' @'localhost' 表示用户的完整信息,用户名+登陆地
-- 这边的密码存放是password()加密后的
CREATE USER 'bijing' @'localhost' IDENTIFIED BY 'root1234';
SELECT *
FROM mysql.user;

SELECT host, user, authentication_string
FROM mysql.user;

-- 删除用户
DROP USER 'bijing'@'localhoset';

-- 修改自己的密码(mysql5中可以使用,mysql8会报错)
SET PASSWORD = PASSWORD ('1234');

-- 修改别人的密码,需要权限(mysql5中可以使用,mysql8会报错)
SET PASSWORD FOR 'bijing' @'localhost' = PASSWORD ('123456');

-- mysql8中修改密码
ALTER USER 'bijing' @'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY '123456';

-- 给用户授权
/* grant 权限列表 on 库.对象名 to '用户'@'登录位置' [identified by '密码']
说明:
   1.权限列表,多个权限用逗号隔开
   2.   *.*代表所有数据库中的所有对象
        .*代表某个数据库中的所有对象
   3. identified by可以省略,也可以写出
        (1)如果用户存在就是修改用户的密码
        (2)如果用户不存在就是创建用户
   */

-- 回收用户权限
# revoke 权限列表 on 库.对象名 from '用户名'@'登录位置';

-- 权限生效指令
# FLUSH PRIVILEGES;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

细节

本章作业

注意：两个字段相加的时候，如果有个字段的数据为null，相加的结果是null，