言成言成啊 | Kit Chen's Blog

数据库学习笔记

以下是个人牢骚。

有个场景,很可笑。

某上课场景

学生:老师,我的xx按你说的运行不了啊。

老师:你安装得不对,卸载了重新安。

其实是学生安装的是最新版,老师非要让学生下载老古董。更气人的是,某些老古董,都已经找不到完整的原程序了。

某面试场景

面试官:你用过什么数据库?

面试者:我们老师教过小海豚标志的数据库,我用得很熟练。

教我们的某个老师,自己就搞不懂,mysql是啥,sqlyog又是啥。教的时候,就把sqlyog当做mysql来操作了。

mysql是数据库,可以通过命令行来操作;sqlyog是数据库的界面可视化工具

大部分老师,是不懂得更新自己知识的,毕竟职业已经是铁饭碗,谁还去想,教的东西有没有用,学生到底学到了什么?

好老师有吗?当然有。一些刚入职教师行业的老师,还很理想化。就拿我某个老师来说,他一直说,教书育人是件很神圣的事,上课也要有仪式感。我们也都觉得这个老师好,但是他自己呢?工资低、同时也没有副业。还有的老师,兢兢业业备课,认认真真教书。就连期末学生给他送钱,求个及格,老师都拒绝了。过得清廉,然而他也只是个老师而已。

某些老师,教学混日子,上课出去抽根烟,出去撒泡尿,半节课就过去了。但是他自己有很多职称,有很多副业,有很多关系,有很多专利。你即使去问问题,他也不鸟你。学生确实没学到东西,但是他自己钱包赚得满满的。

当然,还有些老师,教学认真教,各种关系处得也很好,自己手头也有很多项目。这种老师要看自己机缘了。

本文以MySQL8.0+为例,仅做入门参考。具体内容,参照MySQL官方文档

一、数据库概念

英文:DataBase 简称DB

概念:用于存储和管理数据的仓库

数据库的特点:

  1. 持久化存储数据。本质是一个文件系统
  2. 方便存储和管理数据
  3. 使用了统一的方式(SQL)操作数据库

常见的数据库:

全球数据库排名DB-Engines Ranking

  • Oracle:收费的,大型关系数据库,Oracle公司的产品。Oracle收购Sun公司,收购MySQL
  • MySQL:开源免费的数据库,小型关系数据库。被Oracle收购之后,开始收费
    • 免费: 免费版本的具有开源协议,在免费版本上进行的任何修改所发布的软件,也必须是开源的(开源牛逼!)
    • 收费: 如果你想将修改后的软件出售获利,就必须得购买收费版本才可以 。 此外,收费版本卖的不是软件,而是服务,当数据出现问题的时候,购买的服务可以帮助处理发生问题的数据。
  • SQLServer:MicroSoft公司收费的中型关系型数据库。C#,.Net等语言常使用。
  • DB2:IBM公司的大型关系数据库产品,收费的。常用在银行系统中
  • SQLite:嵌入式的小型关系型数据库,内置到软件或系统中去,比方说浏览器。应用在Android手机端
  • SyBase:已经淡出历史舞台。提供了一个非常专业的数据建模工具PowerDesigner

二、SQL概念

SQL:结构化查询语言(Structured Query Language ),其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式存在不一样的地方,就类似于“方言”。

SQL通用语法:

  1. SQL语句可以单行或者多行书写,以分号结尾
  2. 使用空格和缩进来提高语句可读性
  3. SQL语句不区分大小写,关键字建议使用大写
  4. 注释
    • 单行注释:– 注释内容 或者 # 注释内容(MySQL特有)
    • 多行注释:/* 注释 */
1
2
3
-- 单行注释
# 单行注释,MySQL特有
/* 注释 */

SQL分类:

  1. DDL(Data Definition Language)数据定义语言。用来定义数据库对象:数据库,表,列等。关键字:create, drop,alter 等
  2. DML(Data Manipulation Language)数据操作语言。用来对数据库中表的数据进行增删改。关键字:insert, delete, update 等
  3. DQL(Data Query Language)数据查询语言。用来查询数据库中表的记录(数据)。关键字:select, where 等
  4. DCL(Data Control Language)数据控制语言。用来定义数据库的访问权限和安全级别,及创建用户。关键字:GRANT, REVOKE 等

三、MySQL使用

数据库可视化操作工具:

  1. Navicat Premium(目前为止,感觉最好用的一款)
  2. SQLyog(最新版的界面依旧是上古风格,不过还是挺好用的,推荐)
  3. DBeaver(别人推荐,我还没用过)

MySQL下载:地址

推荐解压版,拒绝傻瓜式。身为开发者,动手能力必须要强。我某同学,至今还以为mysql是个软件。我曾经给人安装了解压版,然后人家跟我说,这不是mysql,有海豚标志的才是mysql(可视化工具sqlyog的图标)。我就回了一句,哦,我还不太会安,你找别人吧。

常用命令行(进入到mysql的bin目录下):

  • mysql -u 用户名 -p 用户名 或mysql -u 用户名 -p; - - 连接数据库

  • show databases; - - 展示全部数据库

  • create database name; - - 创建name数据库

  • use name; - - 使用name数据库

  • show tables; - - 展示其中的表

  • create table name (id int,name varchar(20),age int); - - 创建name表,其中有3列,int型id列,varchar型长度为20的name列,int型age列

  • desc name; - - 查看name表的结构

  • source sql.sql; - - 执行本地的sql.sql文件

  • drop table name; - - 删除name表

  • drop database name; - - 删除name数据库

  • exit或quit; - - 退出数据库终端

windows下用命令行操作,是件极爽的事情,最进下载了一套数学视频,文件名都被打了小广告,cmd一键批处理,爽地不要不要的

本来想着写一篇cmd命令学习笔记,但奈何内容太庞大了,等哪天有足够的时间,来系统学习的话,我会写一篇的。

四、SQL操作

以下例子都是基于MySQL

4.1 DDL:操作数据库、表

操作数据库:CRUD

也就是所谓的增删改查

  1. C(Create):创建
    创建beauty数据库,判断是否存在,并设置字符集为UTF-8:create database if not exists beauty character set utf8;

  2. R(Retrieve):读取
    查询所有数据库:show databases;

  3. U(Update):更新

    查看数据库的字符集:show create database beauty;
    修改数据库的字符集:alter database beauty character set gbk;

  4. D(Delete):删除
    删除数据库:drop database if exists beauty;

  5. 使用数据库:use beauty;

查看当前使用的数据库:select database();

操作表:CRUD

  1. C(Create):创建
    语法:create table 表名 (列名1 数据类型1,列名2 数据类型2,…,列名n 数据类型n);

    创建表:create table girls(id int,name varchar(20),score double(4,1),birthday date,insert_time timestamp);

    复制表:create table 表名 like 被复制的表名;

  2. R(Retrieve):读取
    查询所有表:show tables;
    查询表结构:desc 表名

  3. U(Update):更新
    修改表名:alter table 表名 rename to 新表名;
    查看表的字符集:show create table 表名;
    修改表的字符集:alter table 表名 character set utf8;
    添加一列:alter table 表名 add 列名 数据类型;
    修改列的类型:alter table 表名 modify 列名 新类型;
    修改列名称 类型: alter table 表名 change 列名 新列名 新类型;
    删除列:alter table 表名 drop 列名;

  4. D(Delete):删除
    删除表:drop table if exists 表名;

数据类型的注意点:

double(4,1)表示总长度为4的数,其中小数有1位

date表示日期,只包含年月日,yyyy/MM/dd。我在开发starry的时候,发现在ios的safari中,只识别yyyy/MM/dd,而不识别yyyy-MM-dd,使用的时候注意。

datetime表示日期,包含年月日时分秒,yyyy/MM/dd HH:mm:ss

timestamp表示时间戳,包含年月日时分秒,yyyy/MM/dd HH:mm:ss。有种说法,“如果将来不给这个字段赋值,或者赋值为null,则默认使用系统当前时间,来自动赋值。”,经测验无效。

4.2 DML:增删改表中数据

添加数据

语法:insert into 表名(列名1,列名2,…,列名n) values(值1,值2,…,值n);

1
INSERT INTO fairy(id,NAME,score) VALUES(1,'胡列娜',80.1);

注意:

  1. 列名和值,要一一对应。
  2. 如果表名后,不定义列名,则默认给所有列添加值。insert into 表名 values(值1,值2,…,值n);
  3. 除了数字类型,其他类型都需要使用引号(单双引号都可以)引起来

删除数据

语法:delete from 表名 where 条件;

1
DELETE FROM fairy WHERE NAME='水冰儿';

注意:

  1. 如果不加条件,则会删除表中所有记录。
  2. 如果要删除所有记录。
    • delete from 表名; 不推荐使用,有多少条记录,就会执行多少次删除操作。效率比较低。
    • truncate table 表名; 推荐使用,先删除表,然后再创建一张一模一样的表。效率比较高。

修改数据

语法:update 表名 set 列名1=值1,列名2=值2,…,列名n=值n where 条件;

1
UPDATE fairy SET NAME='水月儿' WHERE id=2;

注意:

  1. 如果不加任何条件,则会将表中所有记录全部修改

4.3 DQL:查询表中记录

导入数据:

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

/*Table structure for table `fairy` */

DROP TABLE IF EXISTS `fairy`;

CREATE TABLE `fairy` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) NOT NULL,
`gender` varchar(2) NOT NULL,
`age` int(11) NOT NULL,
`grade` int(11) NOT NULL,
`school` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
`position` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;

/*Data for the table `fairy` */

insert into `fairy`(`id`,`name`,`gender`,`age`,`grade`,`school`,`position`) values (1,'胡列娜','女',22,52,'武魂殿学院','武魂殿'),(2,'邱若水','女',18,37,'天水学院','天斗帝国'),(3,'水冰儿','女',17,43,'天水学院','天斗帝国'),(4,'水月儿','女',17,36,'天水学院','天斗帝国'),(5,'邪月','男',22,53,'武魂殿学院','武魂殿'),(6,'唐三','男',16,44,'史莱克','天斗帝国'),(7,'戴沐白','男',21,48,'史莱克学院','天斗帝国'),(8,'胡列娜','女',22,52,'武魂殿学院','武魂殿');

语法:select 字段列表 from 表名列表 where 条件列表 group by 分组字段 having 分组之后的条件限定 order by 排序规则 limit 分页限定

基础查询

  1. 多个字段的查询
    select 字段名1,字段名2,… from 表名;
    如果查询所有字段,可以使用 * 来替代字段列表
  2. 去除重复
    DISTINCE关键字
  3. 计算列
    一般可以使用四则运算,来计算列的值
    IFNULL(表达式1,表达式2) 表达式1是指需要判断为NULL的字段,表达式2是指NULL的替换值。
  4. 起别名
    AS关键字,也可以省略
1
2
3
4
5
6
7
8
9
10
11
-- 多个字段查询
SELECT NAME,school FROM fairy;

-- 去除重复
SELECT DISTINCT school FROM fairy;
SELECT DISTINCT NAME,school FROM fairy;

-- 计算age跟grade之和
SELECT NAME,age,grade,age+grade FROM fairy;
-- 如果有NULL参与运算,将NULL换位0
SELECT NAME,age,grade,IFNULL(age,0)+IFNULL(grade,0) AS '年龄+等级' FROM fairy;

条件查询

  1. where子句后跟条件

  2. 运算符

运算符含义
>、<、<=、>=、=、<><>在 SQL 中表示不等于,在 mysql 中也可以使用!=,没有==这个运算符
BETWEEN…AND…在一个范围之内,如:between 100 and 200 相当于条件在 100 到 200 之间,包头又包尾
IN(集合)集合表示多个值,使用逗号分隔
LIKE ‘张%’ 或 LIKE ‘张_’模糊查询。%指多个占位符,_指单个占位符
IS NULL 或 IS NOT NULL查询某一列为 NULL 的值,注:不能写=NULL
AND 或 &&与,SQL 中建议使用前者,后者并不通用。
OR 或 ||
NOT 或 !

代码

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
-- 查询年龄大于等于20岁的
SELECT NAME,age FROM fairy WHERE age>=20;

-- 查询年龄等于22岁的
SELECT NAME,age FROM fairy WHERE age=22;

-- 查询年龄不等于22岁的
SELECT NAME,age FROM fairy WHERE age!=22;
SELECT NAME,age FROM fairy WHERE age<>22;

-- 查询年龄大于等于18岁,小于等于22岁
SELECT NAME,age FROM fairy WHERE age>=18 AND age<=22;
SELECT NAME,age FROM fairy WHERE age>=18 && age<=22;
SELECT NAME,age FROM fairy WHERE age BETWEEN 18 AND 22;

-- 查询所有22岁,18岁,性别为女的人
SELECT * FROM fairy WHERE age=22 OR age=18;
SELECT * FROM fairy WHERE age IN (22,18);

-- 查询等级不为NULL的人
SELECT * FROM fairy WHERE grade IS NOT NULL;

-- 查询姓水的人
SELECT * FROM fairy WHERE NAME LIKE '水%';

-- 查询姓名中含有水字的人
SELECT * FROM fairy WHERE NAME LIKE '%水%';

排序查询

语法:order by 排序字段1 排序方式1,….;

排序方式:

  1. ASC:升序排。默认值
  2. DESC:降序排。

注意:

  1. 如果有多个排序条件,则当前面的值一样时,才会去判断第二条件

代码:

1
2
3
4
5
6
7
8
-- 按age升序排
SELECT * FROM fairy ORDER BY age;

-- 按age降序排
SELECT * FROM fairy ORDER BY age DESC;

-- 按照年龄排序,如果年龄一样,则按照等级排序
SELECT * FROM fairy ORDER BY age ASC,grade ASC;

聚合函数

将一列数据作为一个整体,进行纵向计算。

  1. count:计算个数
    一般选择非空的列:主键
    可以通过count(*),但实际开发中,少用这个
  2. max:计算最大值
  3. min:计算最小值
  4. sum:计算和
  5. avg:计算平均值

注意:聚合函数的计算,排除NULL值

解决方案:

  1. 选择不包含非空的列进行计算
  2. IFNULL(grade,0)

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- 显示总数
SELECT COUNT(NAME) FROM fairy;

-- 如果grade为NULL的时候,聚合函数就不会统计,可以这样处理
SELECT COUNT(IFNULL(grade,0)) FROM fairy;

-- 获取最大值与最小值
SELECT MAX(age) FROM fairy;
SELECT MIN(age) FROM fairy;

-- 获取等级之和
SELECT SUM(grade) FROM fairy;

-- 获取平均等级
SELECT AVG(grade) FROM fairy;

分组函数

语法:group by 分组字段;

注意:

  1. 分组之后查询的字段:分组字段或者聚合函数;
  2. where和having的区别?
    • where在分组之前进行限定,如果不满足条件,则不参与分组;having在分组之后进行限定,如果不满足条件,不会被查询出来
    • where后跟不可跟聚合函数;having可以进行聚合函数的判断
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
-- 按照性别分组,求总人数,并且求不同组的平均等级
SELECT
gender,
AVG(grade) '平均等级',
COUNT(NAME) '人数'
FROM
fairy
GROUP BY gender ;

-- 按照性别分组,求总人数,并且求不同组的平均等级。
-- 要求:等级低于44的,不参与分组
SELECT
gender,
AVG(grade) '平均等级',
COUNT(NAME) '人数'
FROM
fairy
WHERE grade >= 44
GROUP BY gender ;

-- 按照性别分组,求总人数,并且求不同组的平均等级。
-- 要求:等级低于44的,不参与分组。分组之后,人数要大于2人
SELECT
gender,
AVG(grade) '平均等级',
COUNT(NAME) '人数'
FROM
fairy
WHERE grade >= 44
GROUP BY gender
HAVING COUNT(NAME) > 2 ;

SELECT
gender,
AVG(grade) '平均等级',
COUNT(NAME) '人数'
FROM
fairy
WHERE grade >= 44
GROUP BY gender
HAVING 人数 > 2 ;

分页查询

语法:limit 开始的索引,每页查询的条数;

公式:开始的索引=(当前的页码-1)x每页显示的条数

注意:limit是MySQL的一个“方言”。不同的数据库分页操作是不同的

1
2
3
4
5
-- 从第1条数据开始,显示3条
SELECT * FROM fairy LIMIT 0,3;

-- 从第4条数据开始,显示3条
SELECT * FROM fairy LIMIT 3,3;

多表查询

笛卡尔积:A、B两个集合,取这两个集合的所有组成情况。要完成多表查询,要消除无用的数据。

内连接

使用内连接的要求:

  1. 从哪些表中查询数据
  2. 条件是啥
  3. 查询哪些字段

隐式内连接

语法:select 字段列表 from 表名列表 where …;

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
-- 查询两张表所有数据
SELECT
*
FROM
memeber,
position1
WHERE memeber.`p_id` = position1.`id` ;

-- 查询第一张表的name,grade和第二张表的school,position
SELECT
NAME,
grade,
school,
POSITION
FROM
memeber,
position1
WHERE memeber.`p_id` = position1.`id` ;

-- 用表的别名进行优化,先将表命名为别名,再来写select后的别名.xx
SELECT
t1.`name`,
t1.`grade`,
t2.`school`,
t2.`position`
FROM
memeber t1,
position1 t2
WHERE t1.`p_id` = t2.`id` ;

显式内连接

语法:select 字段列表 from 表1 [inner] join 表2 on 条件;

[]这个符号表示可以省略的意思

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
-- 查询两张表所有数据
SELECT
*
FROM
memeber
JOIN position1
ON memeber.`p_id` = position1.`id` ;

-- 查询第一张表的name,grade和第二张表的school,position
SELECT
NAME,
grade,
school,
POSITION
FROM
memeber
JOIN position1
ON memeber.`p_id` = position1.`id` ;

-- 用表的别名进行优化。注意写法,先将表命名为别名,再来写select后的别名.xx
SELECT
t1.name,
t1.`grade`,
t2.`school`,
t2.`position`
FROM
memeber t1
JOIN position1 t2
ON t1.`p_id` = t2.`id` ;

外连接

左外连接

语法:select 字段列表 from 表1 left [outer] join 表2 on 条件;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- 查询所有人信息,如果有位置,则查询位置信息;如果没有位置,则不显示
SELECT
t1.*,
t2.school
FROM
memeber t1,
position1 t2
WHERE t1.`p_id` = t2.`id` ;

-- 此时,如果有数据的外键是空的,就不会查出来了
-- 为解决这个问题,我们需要用到外连接
SELECT
t1.*,
t2.school
FROM
memeber t1
LEFT JOIN position1 t2
ON t1.`p_id` = t2.`id` ;

左外连接:

查询的是左边表的所有数据,以及连接表的交集部分

右外连接

语法:select 字段列表 from 表1 right [outer] join 表2 on 条件;

1
2
3
4
5
6
7
8
-- 右外连接,跟上面左外连接的结果一样,写法的区别就是表的顺序,左换右,右换左了
SELECT
t1.*,
t2.school
FROM
position1 t2
RIGHT JOIN memeber t1
ON t1.`p_id` = t2.`id` ;

右外连接:

查询的是右边表的所有数据,以及连接表的交集部分

子查询

概念:查询中嵌套查询,称嵌套的这个查询,为子查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 查询等级最高的人
-- 1.查询最高等级
SELECT
MAX(grade)
FROM
memeber ;

-- 2.查询等级等于最高的人
SELECT
*
FROM
memeber
WHERE grade = 54 ;

-- 使用子查询
SELECT
*
FROM
memeber
WHERE grade =
(SELECT
MAX(grade)
FROM
memeber) ;

子查询的不同情况

  1. 子查询的结果是单行单列的
    子查询可以作为条件,使用运算符去判断。>,>=, <, <=, =
  2. 子查询的结果是多行单列的
    子查询可以作为条件,使用运算符in来判断
  3. 子查询的结果是多行多列的
    子查询可以作为一张虚拟表参与查询
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
-- 子查询结果是单行单列的
-- 查询等级最高的人
SELECT
*
FROM
memeber
WHERE grade =
(SELECT
MAX(grade)
FROM
memeber) ;

-- 子查询结果是多行单列的
-- 查询武魂殿和天斗帝国的所有成员信息
SELECT
*
FROM
memeber
WHERE p_id IN
(SELECT
id
FROM
position1
WHERE POSITION= "武魂殿"
OR POSITION= "天斗帝国") ;

-- 子查询结果是多行多列的
-- 查询成员年龄为18以上的成员信息跟位置信息
SELECT
*
FROM
position1 t1,
(SELECT
*
FROM
memeber
WHERE age > 18) t2
WHERE t1.`id` = t2.p_id ;

-- 可以使用普通内连接,会更好理解
SELECT
*
FROM
memeber t1,
position1 t2
WHERE t1.`p_id` = t2.`id`
AND t1.`age` > 18 ;

练习

数据库的架构如图

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
76
77
78
79
80
81
82
83
84
85
86
87
88
89
-- 需求:
-- 1.查询所有员工信息。查询员工编号,员工姓名,工资,职务名称,职务描述
SELECT
t1.id,
ename,
salary,
jname,
description
FROM
emp t1,
job t2
WHERE t1.`job_id` = t2.`id` ;

-- 2.查询员工编号,员工姓名,工资,职务名称,职务描述,部门名称,部门位置
SELECT
t1.id,
ename,
salary,
jname,
description,
dname,
loc
FROM
emp t1,
job t2,
dept t3
WHERE t1.`job_id` = t2.`id`
AND t1.`dept_id` = t3.`id` ;


-- 3.查询员工姓名,工资,工资等级
SELECT
t1.`ename`,
t1.`salary`,
t2.`grade`
FROM
emp t1,
salarygrade t2
WHERE t1.`salary` BETWEEN t2.`losalary`
AND t2.`hisalary` ;

-- 4.查询员工姓名,工资,职务名称,职务描述,部门名称,部门位置,工资等级
SELECT
t1.`ename`,
t1.`salary`,
t2.`jname`,
t2.`description`,
t3.`dname`,
t3.`loc`,
t4.`grade`
FROM
emp t1,
job t2,
dept t3,
salarygrade t4
WHERE t1.`dept_id` = t3.`id`
AND t2.`id` = t1.`job_id`
AND t1.`salary` BETWEEN t4.`losalary`
AND t4.`hisalary` ;

-- 5.查询出部门编号、部门名称、部门位置、部门人数
SELECT
t1.`id`,
t1.`dname`,
t1.`loc`,
t2.`总人数`
FROM
dept t1,
(SELECT
dept_id,
COUNT(id) 总人数
FROM
emp
GROUP BY emp.`dept_id`) t2
WHERE t1.`id` = t2.dept_id ;

-- 6.查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询
SELECT
t1.`id`,
t1.`ename`,
t2.ename
FROM
emp t1
LEFT JOIN
(SELECT
id,ename
FROM
emp) t2
ON t1.`mgr` = t2.id ;

4.4 DCL:管理用户与权限

管理用户

查询用户

SELECT * FROM USER;
注意:通配符%表示可以在任意主机使用用户登录

1
2
3
4
5
6
-- 查询用户
-- 1.切换到MySQL数据库
USE mysql;
-- 2.查询user表
SELECT * FROM USER;
-- 通配符%表示可以在任意主机使用用户登录

添加用户

create user ‘用户名‘@’主机名’ identified by ‘密码’;

1
2
3
-- 创建用户
create user '用户名'@'主机名' identified by '密码';
create user 'huliena'@'localhost' identified by '123456';

删除用户

DROP USER ‘用户名‘@’主机名’;

1
2
3
-- 删除用户
DROP USER '用户名'@'主机名';
DROP USER 'huliena'@'localhost';

修改用户密码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- 修改密码
-- mysql5.7版本
UPDATE USER SET PASSWORD=PASSWORD('新密码') WHERE USER='用户名';
SET PASSWORD FOR 'huliena'@'localhost' ='123456';
-- mysql8.0+版本
ALTER USER '用户名'@'主机名' IDENTIFIED BY '密码';
ALTER USER 'huliena'@'localhost' IDENTIFIED BY '654321';
-- DCL特有的方式,高低版本都支持
SET PASSWORD FOR '用户名'@'主机名'='密码';

-- MySQL忘记了root用户的密码,管理员方式打开cmd
-- 1.停止mysql服务
net STOP mysql
-- 2.使用无验证方式启动mysql服务
mysqld --skip-GRANT-TABLES;
-- 3.另开一个窗口登录mysql,直接输入mysql即可
mysql
-- 4.修改密码后,关闭所有命令窗口,并去任务管理器里面关闭mysqld.exe
-- 5.启动mysql服务
net START mysql

管理权限

查询权限

1
2
3
4
-- 查询权限
SHOW GRANTS FOR '用户名'@'主机名';
SHOW GRANTS FOR 'huliena'@'localhost';
SHOW GRANTS FOR 'root'@'localhost';

授予权限

1
2
3
4
5
-- 授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
GRANT SELECT,UPDATE,DELETE ON * TO 'huliena'@'localhost';
-- 授予所有权限
GRANT ALL ON *.* TO 'huliena'@'localhost';

撤销权限

1
2
3
-- 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
revoke all on *.* from 'huliena'@'localhost';

刷新权限

1
2
-- 刷新权限
FLUSH PRIVILEGES;

4.5 约束

概念:对表中的数据进行限定,从而保证数据的正确性、有效性和完整性

约束:

  1. 主键约束:primary key
  2. 非空约束:not null
  3. 唯一约束:unique
  4. 外键约束:foreign key

非空约束

NOT NULL,值不能为NULL

  1. 创建表时,添加非空约束
  2. 已创建表,添加非空约束
  3. 删除非空约束

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建表时添加非空约束
create table hero (
id INT,
NAME VARCHAR(20) NOT NULL
);

desc hero;

-- 删除name的非空约束
alter table hero modify name varchar(20);

-- 已经创建表后,添加约束
alter table hero modify name varchar(20) not null;

唯一约束

UNIQUE,值不能重复(但是允许存在多个NULL)

  1. 创建表时,添加唯一约束
  2. 已创建表,添加唯一约束
  3. 删除唯一约束

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 创建表时,添加唯一约束
CREATE TABLE hero (
id INT,
phone_number VARCHAR(11) UNIQUE
);
-- 注意mysql中,唯一约束限定的列的值可以有多个NULL

-- 删除唯一约束,index索引,唯一约束也叫做唯一索引
ALTER TABLE hero DROP INDEX phone_number;

-- 在创建表之后,添加唯一约束
ALTER TABLE hero MODIFY phone_number VARCHAR(11) UNIQUE;

DESC hero;

主键约束

含义:表示非空且唯一

一张表只能有一个字段表示主键。主键就是表中记录的唯一标识。

主键约束:

  1. 创建表时,添加主键约束
  2. 已创建表,添加主键约束
  3. 删除主键约束

代码

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建表时,添加主键约束
CREATE TABLE hero(
id INT PRIMARY KEY,
NAME VARCHAR(20)
);

-- 删除主键
ALTER TABLE hero DROP PRIMARY KEY;

-- 已创建表,添加主键约束
ALTER TABLE hero MODIFY id INT PRIMARY KEY;

DESC hero;

自动增长

概念:如果某一列是数值类型的,使用auto_increment 可以完成自动增长

自动增长:

  1. 创建表时,添加主键约束,并完成自动增长
  2. 已创建表,添加自动增长
  3. 删除自动增长
1
2
3
4
5
6
7
8
9
10
11
12
13
-- 创建表时,添加自动增长
CREATE TABLE hero(
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);

-- 删除自动增长
ALTER TABLE hero MODIFY id INT;

-- 已创建表,添加自动增长
ALTER TABLE hero MODIFY id INT AUTO_INCREMENT;

DESC hero;

注意:

自动增长是根据上一条数据的大小来增加的。比防说上一条是9,如果不传值,下一条就会是10

外键约束

一张表的数据,会过于冗余,我们可以将其进行拆分,然后再将两张表,通过外键关联起来。

外键约束:

  1. 创建表时,添加外键约束

    1
    2
    3
    4
    5
    create table 表名(
    ....
    外键列
    constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
    );
  2. 已创建表,添加外键约束

  3. 删除外键约束

  4. 级联操作(级联更新和级联删除)

    1
    2
    3
    4
    5
    -- 添加级联操作
    alter table 表名 add constraint 外键名称 foreign key (外键列名称) referencs 主表名称(主表列名称) on update cascade on delete cascade

    --也可以只添加其中某一个,也可以都添加,
    --实现的功能,就是在删除外键表的内容的时候,主键表与其相关的内容也会一起被删掉

代码:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- 创建表时,添加外键约束
CREATE TABLE memeber1 (
id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20) NOT NULL,
gender VARCHAR(2) NOT NULL,
age INT(11) NOT NULL,
grade INT(11) NOT NULL,
p_id INT(11) NOT NULL,
CONSTRAINT m_p_fk FOREIGN KEY (p_id) REFERENCES position1(id)
);

-- 已创建表,添加外键约束
ALTER TABLE memeber1 ADD CONSTRAINT m_p_fk FOREIGN KEY (p_id) REFERENCES position1(id);

-- 删除外键约束
ALTER TABLE memeber1 DROP FOREIGN KEY m_p_fk;

-- 添加级联更新和级联删除
ALTER TABLE memeber ADD CONSTRAINT m_p_fk FOREIGN KEY (p_id) REFERENCES position1(id) ON UPDATE CASCADE ON DELETE CASCADE;

五、数据库的设计

5.1 多表之间的关系

分类

  • 一对一关系:如人和身份证号。一个人只有一个身份证号,一个身份证号对应一个人。开发中很少使用
  • 一对多(多对一)关系:如部门和员工。一个部门对应多个员工,一个员工对应一个部门。
  • 多对多关系: 如学生跟课程。一个课程对应多个学生,一个学生对应多个课程。

这三种关系可以表示所有的事务之间的关系。

实现关系

  • 一对一(一般情况合成一张表)
    一对一关系实现,可以在任意一方添加唯一外键指向另一方的主键。
  • 一对多(多对一)
    在多的一方建立外键,指向一的一方的主键。
  • 多对多
    多对多关系实现需要借助第三张中间表。中间表至少包含两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键

案例

代码

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
-- 创建旅游线路分类表 tab_category
-- cid 旅游线路分类主键,自动增长
-- cname 旅游线路分类名称非空,唯一,字符串 100
CREATE TABLE tab_category (
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(100) NOT NULL UNIQUE
);
-- 创建旅游线路表 tab_route
/*
rid 旅游线路主键,自动增长
rname 旅游线路名称非空,唯一,字符串 100
price 价格
rdate 上架时间,日期类型
cid 外键,所属分类
*/
CREATE TABLE tab_route(
rid INT PRIMARY KEY AUTO_INCREMENT,
rname VARCHAR(100) NOT NULL UNIQUE,
price DOUBLE,
rdate DATE,
cid INT,
FOREIGN KEY (cid) REFERENCES tab_category(cid)
);
/*创建用户表 tab_user
uid 用户主键,自增长
username 用户名长度 100,唯一,非空
password 密码长度 30,非空
name 真实姓名长度 100
birthday 生日
sex 性别,定长字符串 1
telephone 手机号,字符串 11
email 邮箱,字符串长度 100
*/
CREATE TABLE tab_user (
uid INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(100) UNIQUE NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
NAME VARCHAR(100),
birthday DATE,
sex CHAR(1) DEFAULT '男',
telephone VARCHAR(11),
email VARCHAR(100)
);
/*
创建收藏表 tab_favorite
rid 旅游线路 id,外键
date 收藏时间
uid 用户 id,外键
rid 和 uid 不能重复,设置复合主键,同一个用户不能收藏同一个线路两次
*/
CREATE TABLE tab_favorite (
rid INT, -- 线路id
DATE DATETIME,
uid INT, -- 用户id
-- 创建复合主键
PRIMARY KEY(rid,uid), -- 联合主键
FOREIGN KEY (rid) REFERENCES tab_route(rid),
FOREIGN KEY(uid) REFERENCES tab_user(uid)
);

打开架构设计器,就会自动生成如下关系图

5.2 数据库设计的范式

概念:设计数据库时,需要遵循的一些规范。要遵循后边的范式要求,必须要遵循前边所有的范式要求

分类:

  • 第一范式
  • 第二范式
  • 第三范式
  • 其他范式

第一范式

第一范式理解:每一列都是不可分割的原子数据项。

像这样的一张表,就满足第一范式了,但是也存在问题

  1. 存在非常严重的数据冗余(重复):姓名、系名、系主任
  2. 数据添加存在问题:添加新开设的系和系主任时,数据不合法
  3. 数据删除存在问题:如果有人毕业了,删除信息时,会将系一起删掉。

第二范式

第二范式理解:在1NF的基础上,非码属性必须完全依赖于码(在1NF基础上消除非主属性对主码的部分函数依赖)

函数依赖:A–>B,如果A属性(属性组)的值,可以唯一确定B属性的值。则成B依赖于A。例如,学号–>姓名;(学号,课程名称)–>分数

完全函数依赖:A–>B,如果A是一个属性组,则B属性值的确定需要依赖于A属性组中的所有的属性值。例如,(学号,课程名称)–>分数

部分函数依赖:A–>B,如果A是一个属性组,则B属性值的确定只需要依赖于A属性组中的某一些值即可。例如,(学号,课程名称)–>姓名

传递函数依赖:A–>B,B–>C,如果通过A属性(属性组)的值,可以确定唯一B属性的值,再通过B属性(属性组)的值可以确定唯一C属性的值,则称C传递函数依赖于A。例如,学号–>系名,系名–>系主任

码:如果在一张表中,一个属性或者属性组,被其他所有属性所依赖,则称这个属性(属性组)为该表的码。例如,在上表中的码,就是(学号,课程名称)。

  • 主属性:码属性组中的所有属性
  • 非主属性:除码属性组的属性

详细了解,请移步这里

现在根据第二范式的理解,在1NF基础上基础上消除非主属性对主码的部分函数依赖

第三范式

第三范式理解:在2NF基础上,任何非主属性不依赖于其它非主属性(在2NF基础上消除传递依赖)

其他范式

巴德斯科范式(BCNF)、第四范式(4NF)和第五范式(5NF)

六、数据库的备份与还原

数据库的备份和还原目的:防止出现问题,导致数据丢失的情况。

6.1 命令行

备份语法:mysqldmp -u用户名 -p密码 数据库名称 > 保存的路径

还原语法:

  1. 登录数据库
  2. 创建数据库
  3. 使用数据库
  4. 执行文件:source 文件路径

6.2 可视化工具

不同的可视化工具,具体操作不同,不多赘述

七、事务

7.1 事务的了解

概念

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

举个例子,支付宝转钱。A给B转5块钱,成功的话,A账户金额-5,B账户金额+5。如果在A账户减少金额,或者在B账户增加金额的过程中出错,则所有事件回滚。

事务的操作

具体操作

  1. 开启事务
  2. 回滚
  3. 提交

开启事务:start transaction;

回滚:rollback;

提交:commit;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- zs给ls转账500
-- 0 开启事务
start transaction;

-- 1 zs账户-500
update account set balance=balance-500 where name='zs';

-- 1.5 模拟中间异常
-- 我来打断,嘿咻嘿咻!

-- 2 ls账户+500
update account set balance=balance+500 where name='ls';

-- 2.5 模拟中间状态,此时可以通过查询看看中间结果
-- select * from account;
-- 会发现数据已经变了

-- 3 发现执行没有问题,提交事务,执行成功
commit;

-- 3 有问题的话,就回滚事务,执行失败
rollback;

事务提交的两种方式:

  1. 自动提交
    MySQL就是自动提交的,然而Oracle默认是手动提交的。
    一条DML(增删改)语句会自动提交一次事务。
  2. 手动提交
    需要先开启事务,再提交

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

  1. 查看事务的默认提交方式:SELECT @@autocommit; – 1代表自动提交 0代表手动提交
  2. 修改默认提交方式:set @@autocommit=0;

7.2 事务的四大特征

特征:

  1. 原子性:是不可分割的最小操作单位,要么同时成功,要么同时失败。
  2. 持久性:当事务提交或者回滚后,数据库会持久化地保存数据。
  3. 隔离性:多个事务之间,相互独立。但是,真实的情况下,事务之间是相互影响的,所以,我们要了解事务的隔离级别
  4. 一致性:事务操作前后,数据总量不变。

7.3 事务的隔离级别

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

存在问题:

  1. 脏读:一个事务,读取到另一个事务中没有提交的数据。
  2. 不可重复读(虚读):在同一个事务中,两次读取到的事务不一样。
  3. 幻读:一个事务操作(DML)数据表中所有记录,A事务添加了一条数据,B事务查询不到修改。

数据库并发事务存在的问题

隔离级别:

  1. read uncommited
    含义:读未提交
    产生的问题:脏读、不可重复读、幻读
  2. read commited(Oracle中默认隔离级别)
    含义:读已提交
    产生的问题:不可重复读、幻读
  3. repeatable read(MySQL中默认隔离级别)
    含义:可重复读
    产生问题:幻读
  4. serializable
    含义:串行化(相当于把表锁上,A事物不提交,B事物是没法查询得到的)
    可以解决任何问题

注意:

隔离级别从小到大,安全性越来越高,但是效率越来越低。我们要选择合适级别,既保证相对比较安全,又能保证效率较高

修改数据库的默认隔离级别:

  1. 查询隔离级别:
    mysql5.6-5.7:select @@tx_isolation;
    mysql8.0+:select @@transaction_isolation;
  2. 设置隔离级别:set global transaction isolation level 级别字符串;

MySQL8.0+系统变量文档

MySQL5.6系统变量文档

修改隔离级别之后,查询,如果没有改变。需要断开连接,重新连接之后,才会查出来。

最后修改:2020-05-18 09:40:14
原文链接:https://meethigher.top/blog/2020/sql/
付款码 捐助 分享
翻墙之后才能评论哦
阅读量