MySQL数据库技术知识总结

yueritme

于 2023-06-28 17:02:23 发布

阅读量844

点赞数 3

分类专栏：数据库技术文章标签： mysql 数据库

本文链接：https://blog.csdn.net/yueritme/article/details/131439944

版权

数据库技术专栏收录该内容

1 篇文章 0 订阅

订阅专栏

本文介绍了数据库的基本概念，包括关系数据库如MySQL和Oracle，非关系型数据库如MongoDB，以及数据库管理系统DBMS。详细阐述了SQL语言的三大类别，数据类型的分类，以及数据表间的关系。此外，讨论了数据库安全，如用户认证、权限管理和数据加密策略。

摘要由CSDN通过智能技术生成

数据库基本概念

数据库”是以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合
一个数据库由多个表空间（Tablespace）构成
数据库就是一个存储结构化数据的仓库
数据库管理系统（Database Management System，简称DBMS）——是管理数据库的软件系统
关系数据库——创建在关系模型基础上的数据库
关系模型——表示现实世界中的各种实体以及实体关系
关系数据库典型代表：Microsoft SQL Server、MySQL、Oracle、Access、PostgreSQL、DB2、MariaDB
非关系型数据库——NoSQL，泛指所有非关系型数据库
非关系型数据库典型代表：
- 键值（Key-Value）存储数据库：Redis、Memcached、MemcacheDB、Berkeley DB
- 列存储（Column-oriented）数据库：Hbase（应用场景：大数据存储）
- 面向文档数据库：MongoDB
元组：Tuple。通常指数据表中的一行数据，一行即为一个元组
属性：Attribute。数据表中的一列即为一个属性，每列的列名，即属性名
码（键）：数据系统中的基本概念。码就是能唯一标识实体的属性，是整个实体集的性质，而非单个实体性质。包括超码、候选码、主码
- 超码：一个或多个属性集合，可以我们在一个实体集中唯一标识一个实体
- 候选码：关系（表）中的某个属性组，可以唯一确定一个元组。若一个关系（表）中有多个候选码，则选定其中一个为主码
- 主码：主键就是候选码中的一个，是人为规定的
- 外码：外键，主要用于描述两个表的关系
- 全码：关系的所有属性都包含在候选码中
- 主属性：包含在任何一个候选码中的属性
- 非主属性：不包含在任何码中的属性
- 域：Domain。属性的取值范围
- 分量：元组中的一个属性值
- 形式化定义
  - R（D 1，D 2， ……，D n）
  - R：Relation，关系名
  - n：关系的目或度（Degree）
  - 关系名（属性1，属性2，……，属性n）
  - 书籍（编号，书名，作者，出版日期，出版社，图书分类）
  - 学生（学号，姓名，性别，班级）
- 候选码和超码
  - 候选码是从超码中选出的，候选码也是一个或多个属性的集合
  - 候选码是最小超码，它的任意真子集都不能成为超码
  - 超码范围太广，很多是我们并不感兴趣，即无用的

数据类型

字符

char：定长字符串。长度不足，以空格补足
varchar：变长字符串。根据长度确定长度

数值

整数：smallint、integer（int）、bigint——数值范围从小到大
浮点型：float、real、double——数值范围从小到大
定点：numeric(长度,精度)、decimal(长度,精度)

正数

bit：只存0或1，存储空间——1字节，一般用来表示Boolean

时间

date：日期值
time：时间值，粒度——100ns（纳秒）
timestamp：时间戳（2023-06-20 21:35:19）

`SQL`语言

数据定义

DDL，Data Definition Language，数据定义。库管理、表管理。create、alter、rename、drop/truncate、show
DML，Data Manipulation Language，数据操作。插入数据、修改数据、删除数据。insert、update、delete
DQL，Data Query Language，数据查询。
DCL，Data Control Language，数据控制。授权、操作数据。Grant、Rollback等等，常见于数据库安全管理，除数据库管理员一般人少用

库管理

建库

语法：create database [if not exists] <数据库名>;
可选内容
- [[DEFAULT] CHARACTER SET <字符集名>]
- [[DEFAULT] COLLATE <校对规则>]
utf8字符集：伪utf-8
uft8mb4：utf-8 more bytes 4，真正意义上的uft-8

查看数据库

查看SQL中的所有数据库——语法：show databases;
查看数据库创建信息——语法：show create database <数据库名>;
变更当前数据库——语法：use <数据库名>;
查看当前数据库——语法：select database();

修改数据库

语法：alter database <数据库名> default character set utf8mb4;

删除数据库

语法：drop database [if exists] <数据库名>;

表管理

建表

语法：

create table [if not exists] <表名>
(
 列名1 数据类型(长度) [default][not null][primary key][unique][auto-increment],
 列名2……,
 [primary key],
 [foreign key(外键) references 主表(主键列|唯一索引列)]
);

将查询结果插入新表

create table [if not exists] <表名>
as
select查询;

查看表

查看数据库中的表——语法：show tables;
查看表创建信息——语法：show create table <表名>;
查看表结构信息——desc <表名>;

修改表

alter table <表名>
 [add 新列名 数据类型(长度)……]
 [modify 列名 数据类型(长度)……]|change 旧列名 新列名 数据类型(类型)
 [drop 列名]

change：需要写2次列名，列名相同不做修改，也不会报错

alter table <旧表名> rename to <新表名>;

删除表

删除表——语法：drop table [if exists] <表名>;
删除表（速度快）——truncate table <表名>;

数据表间关系

关系映射

1对1、1对N、N对1、多对多

约束类型

主键约束：primary key
- 唯一标识，可以通过主键准确定义到一行数据
- 非空、非重复
- 复合/联合主键
默认值约束：default——体现应用场景：插入数据
唯一值约束：unique——不可以有重复数据，可以null
外键约束：foreign key
- 引用完整性
- 外键取值必须是它参照表中存在的值
非空约束：not null

插入数据

指定列插入

语法

insert into 表名(列名列表) values(值列表);

列名列表和值列表无论个数，还是数据类型都必须完全匹配

全表插入

语法
```
insert into 表名 values(值列表);
```
数据表有多少列，就有多少个值；
插入的值和数据表列无论顺序、个数、数据类型都必须一一对应
int自增长的数值可以使用0、null、default，mysql会自动为该字段设置自增值

更新数据

语法

update 表名 set 列名=值,……
[where 条件表达式];

修改表数据请一定注意检查条件，否则将会全表更新

删除数据

语法

delete from 表名
[where 条件表达式];

truncate [table] 表名;

删除表数据请一定注意检查条件，否则将会全表删除
delete是逐行删除数据（一行一行的删）
truncate是直接删除数据表，再新建一个一模一样的表，执行速度比delete快
delete可以指定条件删除数据，truncate不支持where子句
truncate清空表数据，返回0；delete删除数据，返回影响的行数
结论：需要完全删除表时使用drop table，需要清空表数据时使用truncate，删除表中的部分数据时使用delete

查询语句

单表查询

全表查询

语法：select * from 表名;
*：通配符，表示任意、所有的含义
全表扫描，需解析对象更多，索引过滤数据后，再通过聚集索引获取所有列，多一次B+树查询，效率低、速度慢

条件查询

语法

select …… from 表名
where 条件表达式1 [and|or 条件表达式2]
[limit offset, count]
[order by asc|desc];

满足其中一个条件：or
所有条件都必须满足：and
注意使用合适的比较运算符和值，避免错误判断
注意条件之间的逻辑关系和优先级，可以使用圆括号改变优先级
limit：又叫分页查询，限制查询结果集的行数。offset表示偏移量，即从某个指定下标开始（index），它不代表行号；count表示获取的数据行/条数；分页查询时使用limit可以提高效率
order by：排序。默认升序asc，desc为降序。需要考虑排序规则和多列排序情况，使用排序可能导致性能问题，避免对大数据量排序；优先级低，一般在最后

模糊查询

语法

select …… from 表名
where ……  [like|in|not in ……]
[not][between …… and ……];

模糊查询使用的通配符：%（任意字符）、_ （单个任意字符）
like后如果没有使用通配符，效果与“=”同。例如：name like '张三'等效于name= '张三'
between x and y：查找x~y之间的值（含x，y）， score between 60 and 100 等效于 score>=60 and score<=100
not between x and y：查找不在x~y之间的值（含x，y）
注意通配符使用和转义字符的处理
考虑模糊匹配的效率是否会影响查询速度

聚合查询

语法

select count(列名),  avg(列名), max(列名), min(列名)  , sum(列名) 
from  表名  
where  条件表达式;

计算查询结果集中行的总数（count）、平均值（avg）、最大值（max）、最小值（min）、求和（sum）等统计结果
聚合查询时注意group by子句的正确性及使用，避免遗漏分组数据
考虑聚合函数的效率问题

分组查询

语法

select  列1, [聚合查询]  
from  表名  
group  by  列1
[having 分组过滤条件];

分组查询是将数据按照某个或几个列进行分组，列中值相同的为一组
分组查询时，null值会被单独分组
单独使用group by：只分组，不过滤
group by+having：对分组结果进行条件过滤，having只和group by配合使用
group by+聚合函数：根据不同分组进行聚合运算
分组查询时，如果在select语句中使用了表达式，必须在group by子句中指定相同的表达式。

多表查询

内连接

语法

select  ……  from  talbe1  
[inner]  join  table2   on  table1.列名=table2.列名;

内连接时对两个表中交集的组合查询。交集数据是指table1、table2都有的数据
注意连接方式和连接条件

左右连接（left|right join）

语法：

select …… from talbe1 
left|right [outer] join talbe2 on talbe1.列名=talbe2.列名;

左连接：left join……on，外连接的一种，左连接会将table1表中的数据全部显示，table2中只显示与table1中交集数据，如果table2中没有对应关系数据，其值为null
右连接：right join……on，与左连接相反

自连接

自连接是指在一个表中查询满足条件的数据，并根据数据之间的关联关系进行连接操作。

示例：查询员工姓名及其上级经理（manager_id）

SELECT e1.employee_name as 员工姓名, e2.employee_name as 上级经理
FROM emloyees e1
join emloyees e2 on e1.manager_id=e2.employee_id;

Tips：使用自连接注意性能问题。

综合查询应用

子查询

标量子查询

标量子查询（Scalar Subquery）返回一个单一的值。它通常用于主查询中的条件判断或计算

示例

SELECT name, (SELECT AVG(score) FROM scores WHERE student_id = students.id) AS avg_score
FROM students;

-- 子查询 (SELECT AVG(score) FROM scores WHERE student_id = students.id) 返回了每个学生的平均分数，这个值被命名为 `avg_score` 并与学生的姓名一起返回。

行子查询

行子查询返回一组行作为结果。它可以用于主查询的条件判断或过滤。

示例

SELECT name, age
FROM students
WHERE (age, name) IN (SELECT age, name FROM students WHERE gender = 'Male');

-- 子查询 (SELECT age, name FROM students WHERE gender = 'Male') 返回了性别为男性的学生的年龄和姓名。主查询使用 WHERE 子句和行子查询的结果来筛选出匹配条件的学生。

列子查询

列子查询返回一个结果集作为一个或多个列。它通常用于主查询的 SELECT 语句中的列列表中，作为数据源之一

示例

SELECT name, (SELECT GROUP_CONCAT(subject) FROM courses WHERE courses.student_id = students.id) AS subjects
FROM students;

-- 子查询 (SELECT GROUP_CONCAT(subject) FROM courses WHERE courses.student_id = students.id) 返回了每个学生所选修的课程，并使用 GROUP_CONCAT 函数将课程名连接成一个字符串。主查询返回了每个学生的姓名和所选课程的字符串。

合并查询

UNION

UNION操作符用于合并两个或多个查询的结果，并去除重复的行。
查询的列数和数据类型必须相同或兼容。

语法

SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

UNION ALL

UNION ALL操作符也用于合并两个或多个查询的结果，但不去除重复的行。
它返回所有行，包括重复的行。
查询的列数和数据类型必须相同或兼容。

语法

SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;

视图

视图是一个虚拟的表，它基于一个查询的结果集。
视图可以被视为已命名的查询，可以像表一样被引用和使用。
使用视图的好处之一是可以简化复杂的查询，尤其是当经常需要使用特定的查询逻辑时。它还能提供逻辑上的数据层次结构，增加数据访问的安全性和灵活性。
需要注意的是，视图并不存储实际的数据，它只是一个基于查询结果的虚拟表。当对视图进行查询时，实际的数据会被查询和处理。

创建视图

CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition;

修改和删除视图

如果需要修改已存在的视图，可以使用ALTER VIEW语句。例如，添加、删除或更改视图中的列。
删除视图：如果不再需要某个视图，可以使用DROP VIEW语句删除它。删除视图后，将无法再引用该视图。

调用视图

一旦创建了视图，就可以像操作表一样使用它。可以在SELECT语句中引用视图，并对其进行过滤、排序、连接等操作。
```
SELECT *
FROM view_name
WHERE column1 = 'value';
```

函数

MySQL函数是一段封装了特定功能的可重复使用的代码块，它接收输入参数并返回一个值。
MySQL提供了许多内置函数，并支持用户定义的函数。
内置函数：MySQL有许多内置函数，用于处理数据、执行数学运算、字符串操作、日期和时间处理等。一些常用的内置函数包括COUNT、SUM、AVG、MIN、MAX、CONCAT、SUBSTRING、DATE_FORMAT等。可以使用这些函数直接在SQL语句中进行操作。
用户定义函数（UDF）：MySQL允许创建自定义函数以满足特定需求。用户定义函数可以扩展MySQL提供的功能。

创建函数

需要使用CREATE FUNCTION语句，并指定函数名称、参数和函数体等。
function_name是函数的名称
parameter1和parameter2是函数的参数
return_datatype是函数返回值的数据类型
在BEGIN和END之间编写函数体逻辑
使用RETURN语句返回一个值。

语法

CREATE FUNCTION function_name (parameter1 datatype, parameter2 datatype)
RETURNS return_datatype
BEGIN
    -- 函数体逻辑
    RETURN value;
END;

删除函数

如果不再需要某个函数，可以使用DROP FUNCTION语句删除它。删除函数后，将无法再调用该函数。

函数调用

一旦创建了函数，就可以在SQL语句中调用它
调用函数类似于调用内置函数，使用函数名称和参数列表。

语法

SELECT function_name(parameter1, parameter2);

`SQL`编程

流程控制语句IF

IF语句用于根据指定条件执行不同的代码块。
可以使用IF、ELSE和ELSEIF来创建多个条件分支。

语法

IF condition THEN
    -- 执行条件为真时的代码块
ELSEIF condition THEN
    -- 执行另一个条件为真时的代码块
ELSE
    -- 执行以上条件都不满足时的代码块
END IF;

IF表达式：IF(condition, 结果1, 结果2); -- 当condition为真时，返回结果1，否则，返回结果2。

流程控制语句CASE

CASE语句可以根据不同的条件执行相应的代码块。
有两种形式：简单CASE表达式和搜索CASE表达式。

语法

CASE expressionmysql
    WHEN value1 THEN
        -- 执行value1对应的代码块
    WHEN value2 THEN
        -- 执行value2对应的代码块
    ELSE
        -- 执行以上条件都不满足时的代码块
END CASE;

SELECT 
    CASE gender
    WHEN true THEN '男' ELSE '女' END AS gender
FROM your_table;

select 
case isreturned
 when 1 then '已归还'
 when 0 then '借出，未归还'
 else '数据出错'
 end
from tbl_borrowinfo;

循环语句WHILE

WHILE循环在指定条件为真时重复执行一组语句。
在每次迭代之前，会首先检查条件是否满足。

示例

-- 累加和
drop function if exists get_plus;
delimiter $$
create function get_plus(num int)
returns int
begin
DECLARE counter INT DEFAULT 0;

WHILE 0 < num DO
    -- 执行循环体内的代码块
    SET counter = counter + num;
		set num=num-1;
END WHILE;
return counter;
END
$$
delimiter ;

-- 奇数求和
drop function if exists get_odd_plus;
delimiter $$
create function get_odd_plus(num int)
returns int
begin
DECLARE counter INT DEFAULT 0;

WHILE num > 0 DO
    -- 执行循环体内的代码块
		if num % 2 = 1 then set counter = counter + num;
		end if;
		set num=num-1;
END WHILE;
return counter;
END
$$
delimiter ;

循环语句REPEAT

REPEAT循环会先执行一组语句，然后检查指定条件是否满足。
只有在条件为假时才会结束循环。

语法

DECLARE counter INT DEFAULT 0;

REPEAT
    SET counter = counter + 1;
    -- 执行循环体内的代码块
UNTIL counter = 10 END REPEAT;

流程控制语句LOOP

LOOP语句用于创建一个循环块，每次迭代时会执行一组语句。
使用LEAVE和ITERATE语句可以控制循环的终止和继续。

数据安全

MySQL数据安全涉及保护数据库免受未经授权的访问、数据泄露和恶意攻击。

用户认证和访问控制

用户账户：MySQL使用用户名和密码进行用户认证。每个用户都有一个关联的账户，存储在mysql.user表中。创建强密码，并仅为必要的用户授予访问权限。
权限管理：MySQL使用授权机制来管理用户对数据库的访问权限。使用GRANT语句为用户分配适当的权限级别，例如SELECT、INSERT、UPDATE和DELETE等。
角色授权：MySQL 8.0及更高版本支持角色概念，您可以创建和管理角色，并为用户分配角色以简化权限管理。