数据库技术-MySQL基础篇

⁡⁢⁡布莱克先生

已于 2022-10-13 13:35:43 修改

阅读量486

点赞数 1

分类专栏：计算机基础文章标签：数据库 mysql

于 2022-10-06 14:17:09 首次发布

本文链接：https://blog.csdn.net/MrBlake/article/details/125039736

版权

计算机基础专栏收录该内容

26 篇文章 1 订阅

订阅专栏

参考资料：MySQL数据库入门、JavaGuide、数据库系统概念

文章目录

前言
正文

前言

（1）一些基本概念

MySQL是一种关系型数据库,建立在关系模型的基础上，而关系模型则表明了所存储的数据之间的联系。关系型数据库中的数据常存储在表中，表中的每一行就存放着一条数据，比如一个用户的所有信息。
元组：表中的每一行（即数据库中的每条记录）通常被称为元组。
码：对应表中的每一列。
候选码：指能够唯一标识一个元组的属性或者属性组(若是属性组，则其还需满足任何子集都无法标识一个元组），举例来说，在姓名不重复的前提下。对于关系 ——学生（姓名、学号、班级）来说，{学号}和{姓名，班级}都是候选码，但是{姓名，学号，班级}就不是候选码，因为存在他的某子集仍然可以唯一标识一个元组。一个实体集中可以有多个候选码。
主属性和非主属性：候选码中出现过的属性都被称为主属性。而候选码中没有出现过的属性都被称为非主属性。
主码：又被称为主键，是从候选码中选出来的，一个实体集只能有一个主码。
外码：如果一个关系中的一个属性是另一个关系中的主码，则这个属性被称为外码。
注1：主键用来唯一标识一个元组，不允许为空。外键用来建立和其他表之间的联系，可以重复，可以有多个外键，也可以为空。
注2：对于外键和级联的使用，应当根据实际的开发情况，一般不推荐使用。虽然外键保证了数据库数据的一致性和完整性，且级联操作方便，减少了代码量。但是，以学生成绩关系为例，学生表中的student_id为主键，成绩表中的student_id为外键，更新学生表中的student_id触发成绩表中student_id的更新即为级联更新，其只适用于单机低并发，不适用于分布式高并发集群。级联更新是强阻塞，具有数据库更新风暴的风险，且外键会影响数据库的插入速度。
常见的关系型数据库：
MySQL、PostgreSQL、SQLserver、SQLite(微信用来存储本地聊天记录用的就是SQLite）

（2）事务

事务概念的产生，是为了保证数据的一致性，简单来说就是逻辑上的一组操作，事务内的SQL语句都执行成功则提交事务，只要有一个SQL语句执行失败则回滚事务。即要么执行整个事务，要么属于该事务的操作一个都不执行。而这种全或无的概念就是我们所说的原子性。
关系数据库都有ACID特性
1）一致性（Consistency）单一事务内应当保持一致性，即某些数据在单一事务执行前后应当是保持不变的。比如A向B转账，那么A和B账面的金额总数应当是不变的。
2）原子性（Atomicity)某个事务的所有动作，要么全部都反应，要么全部都不反应。
注：事务在磁盘中存储其旧值。此信息记录在日志中，若事务没有完成他的执行，那么则从日志中恢复其信息，使得事务仿佛没有执行。
3）隔离性（Isolation）并发访问数据库的时候，一个用户的事务不被其他事务干扰。即每个事务执行的时候都感觉不到系统中有其它事务并发执行。
4）持久性（Durability）只要一个事务被成功提交，那么他对数据的改变是持久的。即使在事务完成后系统发生故障。

START TRANSACTION;
SQL1,SQL2···
COMMIT;

多个事务的并发运行经常出现多个用户对同一个数据进行操作。从而可能引发下列问题。
1）脏读（dirty read）
两个事务并发进行。当一个事务正在访问并修改数据，但是事务还没有被提交，修改后的数据还没有被上传。另一个事务在这时候访问并使用了同一个数据（修改前的数据）。即为脏读。解决脏读问题，我们可以设立权限，不允许读取尚未提交变更的数据。
2）丢失修改（Lost to modify）
两个并发事务访问并修改同一个数据会导致的另一个问题是丢失修改，因为他们读取的都是原数据，并在其基础上进行操作，那么，后提交的事务对此数据的操作就会覆盖掉先提交的事务对数据的操作。
3）不可重复读（Unrepeatable read）一个事务内多次读取同一数据，在几次读取之间，另一事务读取并修改了同一数据，导致其几次读取的数据不一样，称为不可重复读。
4）幻读（Phantom read），与不可重复读类似，不可重复读发生的原因是多次读取中进行了数据修改。而幻读，则是多次读取中插入了新增或删除记录的操作。

（3）SQL定义的事务隔离级别及实现方式

在并发执行的时候，所执行的所有调度的效果应当和没有并发执行时的调度效果一致，也就是说，调度应当在某种程度上等价于一个串行调度，这种调度被称为可串行化调度。
注：假设只考虑read和write操作。那么不难想，对于两条连续的指令，如果引用不同的数据项，交换两条指令不会影响调度中任何指令的结果。如果引用相同的数据项，那么只有两个都是read指令时，执行顺序才是无关紧要的。而通过一系列非冲突指令的顺序交换，某调度可以转换成一个串行调度，那么就称这个调度是可串行化的。
读取未提交（READ-UNCOMMITTED）：允许读取未提交的数据。是最低的隔离级别。
读取已提交(READ-COMMITED)：只允许读取已提交的数据。可防止脏读和丢失修改。
可重复读(REPEATABLE-READ)：在一个事务内对同一字段的多次读取应当保持一致，除非是在本事务内被修改。可防止不可重复读
可串行化(SERIALIZABLE)：所有事务依次执行。是最高的隔离级别。
注：MySQL的隔离级别是基于锁和MVCC机制共同实现的。可串行化是通过锁来实现的，其他隔离级别都是基于MVCC机制来实现，但也可能使用到锁机制。比如可重复读需要加锁才能避免幻读问题。
表级锁：MySQL中锁定粒度最大的一种锁。针对非索引字段，触发锁的概率最高，并发度最低。
行级锁：MySQL中锁定粒度最小的一种锁。针对索引字段。并发度高，但加锁开销大，且会出现死锁。
注：当我们执行UPDATE\DELETE语句时，如果WHERE条件中字段没有命中唯一索引，或者索引失效的话。就会导致扫描全表，对表中的所有记录进行加锁。

正文

1.数据库基本操作

1.1创建数据库

CREATE DATABASE 数据库名称;

就是在数据库系统中划分出一块存储数据的空间。

1.2查看已经存在的数据库

SHOW DATABASES;

1.3 查看某个已创建的数据库的信息

SHOW CREATE DATABASE 数据库名称;

1.4修改数据库编码方式

ALTER DATABASE 数据库名称 DEFAULT CHARACTER SET 编码方式 COLLATE 编码方式_bin

注：（1）DEFAULT CHARACTER SET 默认字符集
（2）COLLATE 校对集，即用来排序的规则
_bin: binary 使用二进制比较，可以认为是区分大小写的
_cs:case sensitive 大小写敏感
_ci:case insensitive 大小写不敏感

1.5删除数据库

删除数据库是将数据库系统中已经存在的数据库删除，删除后数据库的所有数据都会被清楚，创建时所分配的内存空间也将被回收。

DROP DATABASE 数据库名称;

2.数据类型

2.1整数类型

数据类型	字节数	无符号数的取值范围
TINYINT	1	0~255
SMALLINT	2	0~65535
MEDIUMINT	3	0~16777215
INT	4	0~4294967295
BIGINT	8	0~18446744073709551615

3.数据表的操作

3.1创建数据表

在使用“USE 数据库名”指定数据库后，可以在指定数据库中建立新表。如下是创建数据表的基本语法格式。

CREATE TABLE 表名
    字段名 1，数据类型[完整性约束条件]，
    字段名 2，数据类型[完整性约束条件],
    ……
    字段名 n，数据类型[完整性约束条件],

注：完整性约束条件-为了防止数据表中插入错误的数据。而字段名则是数据表的列名，如姓名、性别、身份证号。

3.2 查看数据表

3.2.1 SHOW CREATE TABLE 表名

查看创建表时的定义语句以及表的字符编码。

3.2.2 DESCRIBE 表名（简写：DESC 表名）

可以查看表的字段信息，包括字段名和字段类型。
（1）NULL 表示该列是否可以存储NULL值
（2）Key 表示该列是否已经编制索引
（3）Default 表示该列是否有默认值
（4）Extra 表示获取到的与给定列相关的附加信息
注：SHOW CREATE TABLE 表示创建表时的具体语句，DESC 语句以表格形式显示表的字段信息

3.3 修改数据表

修改表名，字段名，字段类型等。

3.3.1 修改表名

ALTER TABLE 旧表名 RENAME TO 新表名;

其中，关键字TO是可选的

3.3.2 修改字段名

ALTER TABLE 表名 CHANGE 旧表名 新表名 新数据类型;

注：新数据类型不能为空，即使新字段和旧字段的数据类型相同。

3.3.3 修改字段的数据类型

ALTER TABLE 表名 MODIFY 字段名 数据类型;

3.3.4添加字段

ALTER TABLE 表名 ADD 字段名 数据类型 [约束条件][FIRST|AFTER 已存在的字段名];

注：FIRST和AFTER 已存在的字段名都是可选参数，FIRST表示将新添加的字段设置为表的第一个字段，AFTER 则是将新添加的字段添加到指定的字段之后。

3.3.5 删除字段

ALTER TABLE 表名 DROP 字段名；

3.3.6 修改字段的排列位置

ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST|AFTER 字段名2；

3.4 删除数据表

DROP TABLE 表名；

4.表的约束

约束条件	说明
PRIMARY KEY	主键约束用于唯一标识对应的记录
FOREIGN KET	外键约束
NOT NULL	非空约束
UNIQUE	唯一性约束
DEFAULT	默认值约束，用于设置字段的默认值

4.1主键约束

为了快速的查找表中的某条信息，可以通过将一个或者一些字段设置成主键来完成。举个栗子，疫情感染者信息表，为了快速查找某一患者的相关信息，我们可以将字段身份证号设置为主键字段。

4.1.1 单字段主键

字段名 数据类型 PRIMARY KEY

4.1.2 多字段主键

PRIMARY(字段名1，字段名2，···，字段名n)

4.2 非空约束

非空约束指的是字段的值不能为NULL，在同一个数据表中可以定义多个非空字段。

字段名 数据类型 NOT NULL

4.3 唯一约束

唯一约束用于保证字段值的唯一性，即不能重复出现。

字段名 数据类型 UNIQUE

4.4 默认约束

用于给数据表中的字段指定默认值，即若没有给字段赋值，那么数据库会为这个字段插入默认值。

字段名 数据类型 DEFAULT 默认值

5.设置表的字段值自动增加

字段名 数据类型 AUTO_INCREMENT

AUTO_INCREMENT 约束的字段可以是任何整数类型。默认情况下，字段的值是从1开始自增。

6.索引

6.1 索引的概念

如果数据库没有索引，那么数据库查找特定数据的方式就是从第一条记录开始遍历，这种查找数据的方式效率是比较低的。而索引就是通过数据库表中的一个或者多个字段值对记录进行某种方式的排序，从而提高查询数据的效率。简单来说，索引的作用类似于书的目录。
注：由于索引本身占据的空间比较大，因此一般存储在磁盘上的文件中，可能单独存储在磁盘文件中，也可能和数据一起存放在数据文件当中。
常见的索引结构：B树、B+树、哈希。

6.1.1 普通索引

是MySQL中的基本索引类型，可以创建在任何数据类型中。其值是否唯一和非空都由字段本身的约束条件来决定。
KEY|INDEX

6.1.2 唯一性索引

该索引所在的字段的值必须是唯一的。这就区别于普通索引了。
UNIQUE

6.1.3 全文索引

只能创建在CHAR\VARCHAR\TEXT类型的字段上，且只有MyISAM存储引擎支持全文索引。
FULLTEXT

6.1.4 单列索引

指在表中的单个字段上创建索引。

6.1.5 多列索引

在表中的多个字段上创建索引。但只有查询条件中使用了这些字段中的第一个字段的时候，该索引才会被引用。其实也就是遵循最左前缀原则。如（name,id,sex）,如果name缺失，则无法继续匹配下一个结点。

6.1.6 空间索引

只能创建在空间数据类型的字段上，MySQL中的空间数据类型有四种：GEOMETRY\POINT\LINESTRING\POLYGON。
注：创建空间索引的字段，必须将其声明为NOT NULL。且空间索引只能在存储引擎为MyISAM的表中创建。

6.2 创建索引

6.2.1 在创建表的时候创建索引

6.2.2 使用CREATE INDEX 在已经存在的表上创建索引

6.3 删除索引

索引会占用一定的磁盘空间，因此，为了避免影响数据库的性能，应当及时删除不再使用的索引。而删除索引的主要方式有两种：

6.3.1 使用ALTER TABLE 删除索引

ALTER TABLE 表名 DROP INDEX 索引名;

6.3.2使用DROP INDEX删除索引

DROP INDEX 索引名 ON 表名;

7.添加、更新、删除数据

7.1添加数据

7.1.1 为表中的所有字段添加数据

（1）INSERTQ语句指定所有的字段名
一般来说，添加数据值得是为表中的所有字段添加一条记录。

INSERT INTO 表名(字段1，字段2，···)
       VALUES(值1，值2···);

注：字段的顺序可以与表中定义的顺序不一致，只需与值相对应即可。
（2）INSERT语句不指定字段名

INSERT INTO 表名 VALUES(值1，值2···)

因为不指定字段名，所以值要与定义时的字段顺序一致。

7.1.2为表的指定字段添加值

只为指定字段添加值，而其他未指定的字段则被赋定义时的默认值。
注：需要额外注意的一点是，如果某个字段在定义的时候添加了非空约束，且没有添加default约束，那么就必须为该字段赋值，否则数据库系统就会提示错误。
注：INSERT语句的另一种写法

INSERT INTO 表名
SET 字段名1=值1，字段名2=值2,···

7.1.3 同时添加多条数据

INSERT INTO 表名[(字段1,字段2,···)]
	VALUES(值1,值2，···),(值1,值2，···),···;

注：表名是可选的，当没有指定字段列表的时候，只需要让值和定义时的字段顺序相对应即可。

7.2 更新数据

UPDATE 表名
	SET 字段名1=值1，字段名2=值2
	WHERE 条件表达式;

注：如果没有使用where进行指定，那么所有记录中SET指定的字段都会进行更新。

7.3 删除数据

DELETE from 表名
	WHERE 条件表达式;

8.单表查询

SELECT [DISTINCT] *|{字段1，字段2，字段3···}
	FROM 表名
	[WHERE 条件表达式1]
	[GROUP BY 字段名 [HAVING 条件表达式2]]
	[ORDER BY 字段名 [ASC|DESC]]
	[LIMIT [OFFSET] 记录数]

注：可选字段DISTINCT用于剔除查询结果中的重复数据。
GROUP BY 字段名 [HAVING 条件表达式]用于将查询结果按照指定字段进行排序，其中HAVING用于对分组后的结果进行过滤。
ORDER BY则是对查询结果进行排序。ASC为升序，DESC为降序。不指定则默认为升序排列。
OFFSET为偏移量

而在条件表达式中，需要特殊介绍几个会使用到的关键字。
（1）IN

	WHERE 字段名 [NOT] IN (元素1，元素2···)

用于判断字段值是否在某个集合中
（2）BETWEEN AND

	WHERE 字段名 [NOT] BETWEEN 值1 AND 值2

（3）LIKE
使用关系运算符=可以判断两个字符串是否相等，然而实际应用场景有时需要我们进行模糊查询，LIKE关键字就是用来判断两个字符串是否匹配。

WHERE 字段名 [NOT] LIKE "匹配字符串"

%通配符：以某个字符开始，匹配任意长度的字符串
_通配符：匹配单个字符串
注：因为%和_具有特殊含义，因此当需要匹配他们的时候需要进行转义。需要在前面加
（4）AND OR
AND和OR用于连接多个查询条件。
但是需要注意的一点是。当AND和OR关键字一起使用的时候。AND优先级要高于OR。即先运算AND两边的条件表达式，再运算OR两边的条件表达式。
（5）DISTINCT

SELECT DISTINCT 字段名 FROM 表名

过滤重复项

在实际开发的过程当中，经常需要对某些数据进行统计。为此，MySQL提供了一些函数来完成这些功能。
用于对一组值进行统计，并返回唯一值，被称为聚合函数。

（1）COUNT()
用于统计记录的条数

SELECT COUNT(*) FROM 表名

（2）SUM()

SELECT SUM(字段名) FROM 表名

（3）AVG()

SELECT AVG(字段名) FROM 表名

（4）MAX()

SELECT MAX(字段名) FROM 表名

（5）MIN()

SELECT MIN(字段名) FROM 表名

查询结果排序

SELECT 字段名1，字段名2···
FROM 表名
ORDER BY 字段名1 [ASC|DESC] 字段名2 [ASC|DESC]···

分组查询：在对表中的数据进行统计的时候，有可能需要按照某个或者多个字段中的值进行分组。

SELECT 字段名1，字段名2···
FROM 表名
GROUP BY 字段名1，字段名2··· [HAVING 条件表达式]

注：HAING和WHERE后面都跟条件表达式，但是HAVING后面能跟聚合函数，而WHERE后面不能。
注：聚合函数也可以在SELECT后面使用，例如：

SELECT COUNT(*),gender
FROM student
GROUP BY gender

LIMIT限制查询结果的数量：

SELECT 字段名1，字段名2···
FROM 表名
LIMIT [OFFSET,]记录数

注：OFFSET为偏移量，默认为0，如1则表示从第二个数据开始。记录数则表示返回查询结果的条数。

为表和字段取别名：

SELECT *
FROM 表名 AS 别名

SELECT 字段名1 AS 别名,字段名2 AS 别名
FROM 表名

9.多表操作

9.1外键

例：一个班级表，包括name,id，一个学生表，包括sid,sname,gid。其中gid引用了班级表中的主键id。

CREATE TABLE chapter09;
USE chapter09;
CREATE TABLE grade(
	id int(4) NOT NULL PRIMARY KEY,
	name varchar(36)
);/*班级表*/

CREATE TABLE student(
	sid int(4) NOT NULL PRIMARY KEY,
	sname varchar(36),
	gid int(4) NOT NULL
);/*学生表*/

alter table student add constraint FK_ID foreign key(gid) REFERENCES grade(id);/*为学生表添加主键约束*/

注：外键指引用另一个表中的一列或多列，被引用的列应当具有主键约束或者唯一性约束。被引用的表为主表，引用外键的表是从表。外键列只能插入参照列存在的值，而参照列被参照的值不能被删除。
为表添加外键约束：

alter table 表名 add constraint FK_ID foreign key (外键字段名) REFERENCES 外表表名(主键字段名);

删除外键约束：

alter table 表名 drop foreign key 外键名;

补：建立外键时添加 ON DELETE 或ON UPDATE子句来告诉数据库如何避免垃圾数据的产生。

[ON DELETE {CASCADE|SET NULL|NO ACTION|RESTRICT}]
[ON UPDATE {CASCADE|SET NULL|NO ACTION|RESTRICT}]

注：CASCADE 删除包含与已删除键值有参照关系的所有记录
SET NULL 修改包含与已删除键值有参照关系的所有记录，用NULL替换
NO ACTION 不进行任何操作
RESTRICT 拒绝主表删除或者修改外键关键列。为默认设置

9.2 操作关联表

对于关联表的操作包括添加数据和删除数据。还是以班级和学生表为例。在添加完主键后。此时学生表和班级表就是多对一的关系。
因为外键列只能插入参照列存在的值，所以如果要为两个表添加数据，则需要先为主表grade添加数据。

INSERT INTO grade(id,name) VALUES(1,'IOTgrade1');
INSERT INTO grade(id,name) VALUES(2,'IOTgrade2');

而删除数据时，上文已经提到，参照列被参照的值是不能被删除的。故而首先应当删除从表中的数据。例如，IOT一班取消了，则应当首先删除IOT一班的学生，再删除IOT一班班级。

delete from student where gid=1;
delete from grade where id=1;

9.3 连接查询

交叉连接：交叉连接返回的结果，是被连接的两个表中所有满足条件的数据行的笛卡尔积。

SELECT * from 表1 CROSS JOIN 表2；

内连接：

SELECT 查询字段 from 表1 [INNER] JOIN 表2 ON 表1.关系字段=表2.关系字段;

外连接：
复合条件查询连接：

9.4 子查询

子查询是指一个查询语句嵌套在另一个查询语句的内部，在执行查询语句时，首先会执行子查询中的语句，然后将返回的结果作为外层查询的过滤条件。
IN关键字的子查询：
EXISTS关键字的子查询：
子查询只返回一个布尔值，作为外层查询是否执行的依据。

SELECT * 
FROM department WHERE EXISTS(select did from employee where age>21);

ANY关键字的子查询:

SELECT *
FROM department WHERE did>any(select did from employee);

注：只需满足内层查询中的任意一个比较条件
ALL关键字的子查询:

SELECT *
FROM department WHERE did>all(select did from employee);

注：需要满足所有内存查询条件