数据库基本概念
-
数据库”是以一定方式储存在一起、能予多个用户共享、具有尽可能小的冗余度、与应用程序彼此独立的数据集合
-
一个数据库由多个表空间（
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及更高版本支持角色概念，您可以创建和管理角色，并为用户分配角色以简化权限管理。
数据加密
-
传输加密：通过启用
SSL/TLS，可以对MySQL的网络通信进行加密，防止窃听和中间人攻击。 -
存储加密：
MySQL提供各种存储加密选项。您可以使用加密函数来加密敏感数据，或者选择使用加密文件系统或硬件来保护整个数据库的存储。
审计与日志
-
审计日志：
MySQL的审计日志功能可以记录用户的操作、连接和一般的查询活动。配置审计日志以追踪关键活动，并监控潜在的安全问题。 -
错误日志和查询日志：
MySQL还提供错误日志和查询日志，可用于排查问题和分析潜在的安全威胁。
难点与易错点
-
不安全的默认设置：
MySQL在安装后的默认设置可能不是最安全的。您需要评估并进行必要的配置更改，例如禁用匿名用户、删除测试数据库等。 -
SQL注入攻击：使用参数化查询或预处理语句，可以防止SQL注入攻击。避免将用户输入直接拼接到SQL查询中。 -
不当的权限管理：仔细管理用户的权限，并按需分配最小限度的访问权限以减少潜在风险。
技术改进与建议
-
定期更新：及时升级到最新版本的
MySQL，以获得最新的安全修补程序和功能改进。 -
定期检查和修复安全漏洞：保持关注
MySQL社区的安全公告，检查并修复可能出现的安全漏洞。 -
监控和报警：设置监控机制，可以实时检测异常活动并触发警报。
1573
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
