知识体系

2023-05-22 本文已影响0人 Tina_Xu

DBMS

DBMS 的英文全称是 DataBase Management System，数据库管理系统，实际上它可以对多个数据库进行管理，所以你可以理解为 DBMS = 多个数据库（DB） + 管理程序。
DB 的英文是 DataBase，也就是数据库。数据库是存储数据的集合，你可以把它理解为多个数据表。
DBS 的英文是 DataBase System，数据库系统。它是更大的概念，包括了数据库、数据库管理系统以及数据库管理人员 DBA。

关系型数据库（RDBMS）就是建立在关系模型基础上的数据库，SQL 就是关系型数据库的查询语言。
键值型数据库通过 Key-Value 键值的方式来存储数据，其中 Key 和 Value 可以是简单的对象，也可以是复杂的对象。Key 作为唯一的标识符，优点是查找速度快，在这方面明显优于关系型数据库，同时缺点也很明显，它无法像关系型数据库一样自由使用条件过滤（比如 WHERE），如果你不知道去哪里找数据，就要遍历所有的键，这就会消耗大量的计算。键值型数据库典型的使用场景是作为内容缓存。Redis 是最流行的键值型数据库。
搜索引擎也是数据库检索中的重要应用，常见的全文搜索引擎有 Elasticsearch、Splunk 和 Solr。虽然关系型数据库采用了索引提升检索效率，但是针对全文索引效率却较低。搜索引擎的优势在于采用了全文搜索的技术，核心原理是“倒排索引”。
图形数据库，利用了图这种数据结构存储了实体（对象）之间的关系。最典型的例子就是社交网络中人与人的关系，数据模型主要是以节点和边（关系）来实现，特点在于能高效地解决复杂的关系问题。
文档型数据库用来管理文档，在数据库中文档作为处理信息的基本单位，一个文档就相当于一条记录，MongoDB 是最流行的文档型数据库。
列式数据库是相对于行式存储的数据库，Oracle、MySQL、SQL Server 等数据库都是采用的行式存储（Row-based），而列式数据库是将数据按照列存储到数据库中，这样做的好处是可以大量降低系统的 I/O，适合于分布式文件系统，不足在于功能相对有限。

MySQL如何执行

MySQL 由三层组成
首先 MySQL 是典型的 C/S 架构，即 Client/Server 架构，服务器端程序使用的 mysqld。
连接层：客户端和服务器端建立连接，客户端发送 SQL 至服务器端；
SQL 层：对 SQL 语句进行查询处理；
存储引擎层：与数据库文件打交道，负责数据的存储和读取。

mysql分层.png

SQL层执行过程
查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。
执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。

mysql SQL层执行过程.png

存储引擎层
MySQL 的存储引擎采用了插件的形式，每个存储引擎都面向一种特定的数据库应用环境。在 MySQL 中每个表的设计都可以采用不同的存储引擎，我们可以根据实际的数据处理需要来选择存储引擎，这也是 MySQL 的强大之处。

InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存储引擎，最大的特点是支持事务、行级锁定、外键约束等。
MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。

MySQL对一条SQL语句的执行时间进行分析

image.png

DDL数据定义语言

定义数据库

CREATE DATABASE nba; // 创建一个名为nba的数据库
DROP DATABASE nba; // 删除一个名为nba的数据库

定义数据表

创建表

CREATE TABLE player  (
  player_id int(11) NOT NULL AUTO_INCREMENT,
  player_name varchar(255) NOT NULL
);

修改表

ALTER TABLE player ADD (age int(11));
ALTER TABLE player RENAME COLUMN age to player_age
ALTER TABLE player MODIFY (player_age float(3,1));
ALTER TABLE player DROP COLUMN player_age;

删除表

DROP TABLE player;

表的常见约束

主键约束：主键起的作用是唯一标识一条记录，不能重复，不能为空，即 UNIQUE+NOT NULL。一个数据表的主键只能有一个。主键可以是一个字段，也可以由多个字段复合组成。
外键约束：外键确保了表与表之间引用的完整性。一个表中的外键对应另一张表的主键。外键可以是重复的，也可以为空。
唯一性约束：唯一性约束表明了字段在表中的数值是唯一的，即使我们已经有了主键，还可以对其他字段进行唯一性约束。唯一性约束和普通索引（NORMAL INDEX）之间是有区别的。唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性，而普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束。
NOT NULL：字段不应为空，必须有取值。
DEFAULT：插入数据的时候，这个字段没有取值，就设置为默认值。
CHECK：检查特定字段取值范围，CHECK(height>=0 AND height<3)

数据检索

基础语法

#查询多列
SELECT name, hp_max, mp_max, attack_max, defense_max FROM heros
#起别名
SELECT name AS n, hp_max AS hm, mp_max AS mm, attack_max AS am, defense_max AS dm FROM heros
#查询常数
SELECT '王者荣耀' as platform, name FROM heros
#去除重复行
SELECT DISTINCT attack_range FROM heros
#结果排序
SELECT name, hp_max FROM heros ORDER BY hp_max DESC 
#结果数量限制
SELECT name, hp_max FROM heros ORDER BY hp_max DESC LIMIT 5

SELECT 语句的执行顺序：

FROM > WHERE > GROUP BY > HAVING > SELECT的字段 > DISTINCT > ORDER BY > LIMIT

SELECT DISTINCT player_id, player_name, count(*) as num #顺序5
FROM player JOIN team ON player.team_id = team.team_id #顺序1
WHERE height > 1.80 #顺序2
GROUP BY player.team_id #顺序3
HAVING num > 2 #顺序4
ORDER BY num DESC #顺序6
LIMIT 2 #顺序7

在 SELECT 语句执行这些步骤的时候，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。需要注意的是，这些步骤隐含在 SQL 的执行过程中，对于我们来说是不可见的。

数据过滤

比较运算符

image.png

SELECT name, hp_max FROM heros WHERE hp_max BETWEEN 5399 AND 6811;
SELECT name, hp_max FROM heros WHERE hp_max IS NULL;

逻辑运算符

image.png
一般来说 () 优先级最高，其次优先级是 AND，然后是 OR。

SELECT name, hp_max, mp_max FROM heros WHERE (hp_max+mp_max) > 8000 OR hp_max > 6000 AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC;
#对部分条件增加括号
SELECT name, hp_max, mp_max FROM heros WHERE ((hp_max+mp_max) > 8000 OR hp_max > 6000) AND mp_max > 1700 ORDER BY (hp_max+mp_max) DESC;

通配符
通配符就是我们用来匹配值的一部分的特殊字符。这里我们需要使用到 LIKE 操作符。

关于字符串的搜索可能是需要区分大小写的，比如'liu%'就不能匹配上'LIU BEI'。
如果我们想要匹配单个字符，就需要使用下划线（）通配符。（%）和（）的区别在于，（%）代表零个或多个字符，而（_）只代表一个字符。
如果要让索引生效，那么 LIKE 后面就不能以（%）开头，比如使用LIKE '%太%'或LIKE '%太'的时候就会对全表进行扫描。如果使用LIKE '太%'，同时检索的字段进行了索引的时候，则不会进行全表扫描。

SELECT name FROM heros WHERE name LIKE '%太%'
SELECT name FROM heros WHERE name LIKE '_%太%'

SQL函数

算术函数

image.png

SELECT ABS(-2)，运行结果为 2。
SELECT MOD(101,3)，运行结果 2。
SELECT ROUND(37.25,1)，运行结果 37.3。

字符串函数

image.png

SELECT CONCAT('abc', 123)，运行结果为 abc123。
SELECT LENGTH('你好')，运行结果为 6。
SELECT CHAR_LENGTH('你好')，运行结果为 2。
SELECT LOWER('ABC')，运行结果为 abc。
SELECT UPPER('abc')，运行结果 ABC。
SELECT REPLACE('fabcd', 'abc', 123)，运行结果为 f123d。
SELECT SUBSTRING('fabcd', 1,3)，运行结果为 fab。

日期函数

image.png

SELECT CURRENT_DATE()，运行结果为 2019-04-03。
SELECT CURRENT_TIME()，运行结果为 21:26:34。
SELECT CURRENT_TIMESTAMP()，运行结果为 2019-04-03 21:26:34。
SELECT EXTRACT(YEAR FROM '2019-04-03')，运行结果为 2019。
SELECT DATE('2019-04-01 12:00:05')，运行结果为 2019-04-01。

转换函数

image.png

SELECT CAST(123.123 AS DECIMAL(8,2))，运行结果为 123.12。
SELECT COALESCE(null,1,2)，运行结果为 1。

聚集函数

五种聚集函数

image.png

#COUNT(*) 只是统计数据行数，不管某个字段是否为 NULL
SELECT COUNT(*) FROM heros WHERE hp_max > 6000
#COUNT(role_assist)会忽略值为 NULL 的数据行
SELECT COUNT(role_assist) FROM heros WHERE hp_max > 6000
#使用多个聚集函数
SELECT MAX(hp_max) FROM heros WHERE role_main = '射手' or role_assist = '射手'
#MAX 和 MIN 用于字符串类型数据的统计。如果是英文字母，则按照 A—Z 的顺序排列，越往后，数值越大。如果是汉字则按照全拼拼音进行排列。
SELECT MIN(CONVERT(name USING gbk)), MAX(CONVERT(name USING gbk)) FROM heros

GROUP BY分组

SELECT COUNT(*), role_main FROM heros GROUP BY role_main;
#使用多个字段进行分组，这就相当于把这些字段可能出现的所有的取值情况都进行分组
SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist ORDER BY num DESC;

HAVING过滤分组
WHERE 是用于数据行，而 HAVING 则作用于分组。
HAVING 支持所有 WHERE 的操作，因此所有需要 WHERE 子句实现的功能，你都可以使用 HAVING 对分组进行筛选。

SELECT COUNT(*) as num, role_main, role_assist FROM heros WHERE hp_max > 6000 GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC

知识体系

DBMS

MySQL如何执行

DDL数据定义语言

数据检索

数据过滤

SQL函数

聚集函数

猜你喜欢

热点阅读