初见

SQL基础--万字笔记纯干货

2020-06-17  本文已影响0人  文知道

SQL

数据库操作类型

  1. DDL，英文叫做 Data Definition Language，也就是数据定义语言，它用来定义我们的数据库对象，包括数据库、数据表和列。通过使用 DDL，我们可以创建，删除和修改数据库和表结构。

  2. DML，英文叫做 Data Manipulation Language，数据操作语言，我们用它操作和数据库相关的记录，比如增加、删除、修改数据表中的记录。

  3. DCL，英文叫做 Data Control Language，数据控制语言，我们用它来定义访问权限和安全级别。

  4. DQL，英文叫做 Data Query Language，数据查询语言，我们用它查询想要的记录，它是 SQL 语言的重中之重。在实际的业务中，我们绝大多数情况下都是在和查询打交道，因此学会编写正确且高效的查询语句，是学习的重点。

DB、DBS 和 DBMS 的区别是什么

数据库类型

关系型
非关系型

Oracle中SQL执行过程

从上面这张图中可以看出，SQL 语句在 Oracle 中经历了以
下的几个步骤。

  1. 语法检查：检查 SQL 拼写是否正确，如果不正确，
    Oracle 会报语法错误。
  2. 语义检查：检查 SQL 中的访问对象是否存在。比如我们
    在写 SELECT 语句的时候，列名写错了，系统就会提示错
    误。语法检查和语义检查的作用是保证 SQL 语句没有错
    误。
  3. 权限检查：看用户是否具备访问该数据的权限。
  4. 共享池检查：共享池（Shared Pool）是一块内存池，最
    主要的作用是缓存 SQL 语句和该语句的执行计划。
    Oracle 通过检查共享池是否存在 SQL 语句的执行计划，
    来判断进行软解析，还是硬解析。那软解析和硬解析又该
    怎么理解呢？
    在共享池中，Oracle 首先对 SQL 语句进行 Hash 运算，
    然后根据 Hash 值在库缓存（Library Cache）中查找，
    如果存在 SQL 语句的执行计划，就直接拿来执行，直接
    进入“执行器”的环节，这就是软解析。
    如果没有找到 SQL 语句和执行计划，Oracle 就需要创建
    解析树进行解析，生成执行计划，进入“优化器”这个步
    骤，这就是硬解析。
  5. 优化器：优化器中就是要进行硬解析，也就是决定怎么
    做，比如创建解析树，生成执行计划。
  6. 执行器：当有了解析树和执行计划之后，就知道了 SQL
    该怎么被执行，这样就可以在执行器中执行语句了。
    共享池是 Oracle 中的术语，包括了库缓存，数据字典缓冲
    区等。我们上面已经讲到了库缓存区，它主要缓存 SQL 语
    句和执行计划。而数据字典缓冲区存储的是 Oracle 中的对
    象定义，比如表、视图、索引等对象。当对 SQL 语句进行
    解析的时候，如果需要相关的数据，会从数据字典缓冲区中
    提取。
    库缓存这一个步骤，决定了 SQL 语句是否需要进行硬解
    析。为了提升 SQL 的执行效率，我们应该尽量避免硬解
    析，因为在 SQL 的执行过程中，创建解析树，生成执行计
    划是很消耗资源的。
    你可能会问，如何避免硬解析，尽量使用软解析呢？在
    Oracle 中，绑定变量是它的一大特色。绑定变量就是在
    SQL 语句中使用变量，通过不同的变量取值来改变 SQL 的
    执行结果。这样做的好处是能提升软解析的可能性，不足之
    处在于可能会导致生成的执行计划不够优化，因此是否需要
    绑定变量还需要视情况而定。
    举个例子，我们可以使用下面的查询语句：
SQL> select * from player where player_id = 10001;

你也可以使用绑定变量，如：

SQL> select * from player where player_id = :player_id;

这两个查询语句的效率在 Oracle 中是完全不同的。如果你
在查询 player_id = 10001 之后，还会查询 10002、
10003 之类的数据，那么每一次查询都会创建一个新的查询解析。而第二种方式使用了绑定变量，那么在第一次查询之后，在共享池中就会存在这类查询的执行计划，也就是软解析。

因此我们可以通过使用绑定变量来减少硬解析，减少
Oracle 的解析工作量。但是这种方式也有缺点，使用动态
SQL 的方式，因为参数不同，会导致 SQL 的执行效率不
同，同时 SQL 优化也会比较困难。

MySQL执行过程

  1. 连接层：客户端和服务器端建立连接，客户端发送 SQL
    至服务器端；
  2. SQL 层：对 SQL 语句进行查询处理；
  3. 存储引擎层：与数据库文件打交道，负责数据的存储和读取。
  1. 查询缓存：Server 如果在查询缓存中发现了这条 SQL 语句，就会直接将结果返回给客户端；如果没有，就进入到解析器阶段。需要说明的是，因为查询缓存往往效率不高，所以在 MySQL8.0 之后就抛弃了这个功能。
  2. 解析器：在解析器中对 SQL 语句进行语法分析、语义分析。
  3. 优化器：在优化器中会确定 SQL 语句的执行路径，比如是根据全表检索，还是根据索引来检索等。
  4. 执行器：在执行之前需要判断该用户是否具备权限，如果具备权限就执行 SQL 查询并返回结果。在 MySQL8.0 以下的版本，如果设置了查询缓存，这时会将查询结果进行缓存。
  1. InnoDB 存储引擎：它是 MySQL 5.5 版本之后默认的存
    储引擎，最大的特点是支持事务、行级锁定、外键约束
    等。
  2. MyISAM 存储引擎：在 MySQL 5.5 版本之前是默认的存储引擎，不支持事务，也不支持外键，最大的特点是速度快，占用资源少。
  3. Memory 存储引擎：使用系统内存作为存储介质，以便
    得到更快的响应速度。不过如果 mysqld 进程崩溃，则会
    导致所有的数据丢失，因此我们只有当数据是临时的情况
    下才使用 Memory 存储引擎。
  4. NDB 存储引擎：也叫做 NDB Cluster 存储引擎，主要用于 MySQL Cluster 分布式集群环境，类似于 Oracle 的
    RAC 集群。
  5. Archive 存储引擎：它有很好的压缩机制，用于文件归
    档，在请求写入时会进行压缩，所以也经常用来做仓库。
    需要注意的是，数据库的设计在于表的设计，而在 MySQL
    中每个表的设计都可以采用不同的存储引擎，我们可以根据实际的数据处理需要来选择存储引擎，这也是 MySQL 的强大之处。

MySQL字符集

数据库设计 三少一多原则

  1. 数据表的个数越少越好RDBMS 的核心在于对实体和联系的定义，也就是 E-R 图（Entity Relationship Diagram），数据表越少，证明实体和联系设计得越简洁，既方便理解又方便操作。
  2. 数据表中的字段个数越少越好字段个数越多，数据冗余的可能性越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段的取值可以由其他字段计算出来。当然字段个数少是相对的，我们通常会在数据冗余和检索效率中进行平衡。
  3. 数据表中联合主键的字段个数越少越好设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式（也就是用多个字段来定义一个主键）。联合主键中的字段越多，占用的索引空间越大，不仅会加大理解难度，还会增加运行时间和索引空间，因此联合主键的字段个数越少越好。
  4. 使用主键和外键越多越好数据库的设计实际上就是定义各种表，以及各种字段之间的关系。这些关系越多，证明这些实体之间的冗余度越低，利用度越高。这样做的好处在于不仅保证了数据表之间的独立性，还能提升相互之间的关联使用率。你应该能看出来“三少一多”原则的核心就是简单可复用。简单指的是用更少的表、更少的字段、更少的联合主键字段来完成数据表的设计。可复用则是通过主键、外键的使用来增强数据表之间的复用率。因为一个主键可以理解是一张表的代表。键设计得越多，证明它们之间的利用率越高。

SELECT 的执行顺序

查询是 RDBMS 中最频繁的操作。我们在理解 SELECT 语法的时候，还需要了解 SELECT 执行时的底层原理。只有这样，才能让我们对 SQL 有更深刻的认识。
其中你需要记住 SELECT 查询时的两个顺序：

  1. 关键字的顺序是不能颠倒的：
SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...
  1. SELECT 语句的执行顺序（在 MySQL 和 Oracle 中，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
SQL 的执行原理。
比较运算符
逻辑运算符

and的优先级高于or

通配符 % _

SQL函数

算术函数
SELECT ABS(-2)，运行结果为 2。
SELECT MOD(101,3)，运行结果 2。
SELECT ROUND(37.25,1)，运行结果 37.3。
字符串函数
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。
日期函数
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。

这里需要注意的是，DATE 日期格式必须是 yyyy-mm-dd 的形式。如果要进行日期比较，就要使用 DATE 函数，不要直接使用日期与字符串进行比较，我会在后面的例子中讲具体的原因。

转换函数
SELECT CAST(123.123 AS INT)，运行结果会报错。
SELECT CAST(123.123 AS DECIMAL(8,2))，运行结果为 123.12。
SELECT COALESCE(null,1,2)，运行结果为 1。
聚类函数
SQL：SELECT MIN(CONVERT(name USING gbk)), MAX(CONVERT(name USING gbk)) FROM heros

HAVING

SQL: SELECT COUNT(*) as num, role_main, role_assist FROM heros GROUP BY role_main, role_assist HAVING num > 5 ORDER BY num DESC

子查询

关联子查询
非关联子查询
EXISTS 子查询
集合比较子查询
SQL: SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team

在 SQL92 中是如何使用连接的

笛卡尔积
等值连接
SQL: SELECT player_id, player.team_id, player_name, height, team_name FROM player, team WHERE player.team_id = team.team_id
非等值连接
SQL：SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest
外连接
自连接

SQL99 标准中的连接查询

交叉连接
SQL: SELECT * FROM player CROSS JOIN team
自然连接
SELECT player_id, team_id, player_name, height, team_name FROM player NATURAL JOIN team

视图

创建视图：CREATE VIEW
CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

数据库调优

目的
如何确定优化的目标
第一步，选择适合的 DBMS
第二步，优化表设计
第三步，优化逻辑查询
第四步，优化物理查询
  1. 如果数据重复度高，就不需要创建索引。通常在重复度超过 10% 的情况下，可以不创建这个字段的索引。比如性别这个字段（取值为男和女）。
  2. 要注意索引列的位置对索引使用的影响。比如我们在 WHERE 子句中对索引字段进行了表达式的计算，会造成这个字段的索引失效。
  3. 要注意联合索引对索引使用的影响。我们在创建联合索引的时候会对多个字段创建索引，这时索引的顺序就很重要了。比如我们对字段 x, y, z 创建了索引，那么顺序是 (x,y,z) 还是 (z,y,x)，在执行的时候就会存在差别。
  4. 要注意多个索引对索引使用的影响。索引不是越多越好，因为每个索引都需要存储空间，索引多也就意味着需要更多的存储空间。此外，过多的索引也会导致优化器在进行评估的时候增加了筛选出索引的计算时间，影响评估的效率。
第五步，使用 Redis 或 Memcached 作为缓存
第六步，库级优化
上一篇 下一篇

猜你喜欢

热点阅读