0.mysql | 01基础

sql基础

sql语言分4个部分

1.DDL,Data Definition Language数据定义语言，用于定义数据库对象（数据库、数据表和列），DDL可以创建、删除、修改数据库与表结构

2.DML，Data Mainipulation Language，数据操作语言，用于操作和数据库相关记录（对于数据表的增加、删除、修改）

3.DCL，Data Control Language，数据控制语言，用于定义访问权限和安全级别

4.DQL，Data Query Language，数据查询语言，用于查询想要的记录

ER图

实体是要管理的对象，属性是标识每个实体的属性，关系则是对象之间的关系。

SQL规范

1.表明、表别名、字段名、字段别名等都小写

2.SQL保留字、函数名、绑定变量都大写

例如: SELECT user_id, user_name, user_age FROM user;

注：MYSQL在Windows下默认不区分大小写，

在Linux下大小写规则

1.数据库与表、表别名明严格区分大小写

2.表列名与咧别名在所有情况下忽略大小写

3.变量名严格区分大小写

Windows下设置对大小写敏感

show variables like 'lower%'	# 查看是否区分大小写
# lower_case_table_names = 0:区分大小写，1:不区分大小写

DB、DBS、DBMS区别

DBMS：DataBase Management System，数据库管理系统–>对多个数据库进行管理，DBMS=多个数据库（DB）+管理程序

DB：DataBase，数据库–>存储数据的集合

DBS：DataBase System，数据库系统–>数据库+数据库管理系统+数据库管理人员DBA

关系型数据库（RDBMS）就是建立在关系数据库模型基础上的数据库，SQL就是关系型数据库的查询语言。

绑定变量

绑定变量可以避免硬解析，通过不同的变量取值，来改变SQL的执行结果。

# 1.普通查询
select * from player where player_id = 10001;
# 2.绑定变量,用不同的变量来改变SQL的执行结果
select * from player where player_id = :player_id;

这两种效率是不同的，如果查询了palyer_id=10001之后，还会查询10002，10003，等重复的SQL语句，可以高效执行大量的重复语句。

绑定变量：

优点：减少硬解析，加快SQL处理过程，减少系统开销。

缺点：参数不同导致执行效率不同，优化比较难做

MYSQL执行流程

MYSQL为C/S架构，既Client/Server架构，服务器程序使用mysqld，整体的MYSQL流程如图:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-hLVyzbag-1626490490517)(/Users/wujiewei/Desktop/wcw/01.01.mysql整体流程.png)]

连接层：客户端与server建立连接，客户端发送SQL至server

SQL层:对SQL语句进行查询处理

存储引擎层：负责数据的存储和读取，是与数据库文件打交道

SQL层与数据库文件的存储方式无关

SQL 层结构如图:

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fn4LJKzs-1626490490518)(/Users/wujiewei/Desktop/wcw/01.02.mysql执行流程.png)]

SQL执行过程

SQL语句–>缓存查询–>解析器–>优化器->执行器

8.0版本之后，MYSQL不再支持缓存的查询，原因为:一旦数据表有更新，缓存都讲清空，因此只有数据表是静态的时候，或者数据表很少发生变化时，使用缓存查询才有价值，否则如果数据表经常更新，反而增加了SQL查询时间。

Mysql常见存储引擎

1.InnoDB存储引擎，支持事务、行级锁定、外键约束等（5.6后支持全文索引,且默认引擎是InnoDB）。（应用场景：事务）

2.MyISAM存储引擎，不支持事务、也不支持外键，最大特点就是速度快、占用内存资源少。（以读为主的数据表）数据库奔溃后恢复困难

3.Memory存储引擎，使用系统内存做为存储介质，得到更快响应速度（mysqld进程崩溃,会导致所有数据丢失，所以只有当数据是临时的情况裁使用Memory存储引擎）

4.NDB存储引擎，用于MYSQL Cluster分布式集群的环境

5.Archive存储引擎，有很好的压缩机制，用于文件归档，在请求写入时进行压缩，所有经常用来做仓库

在MYSQL中，每个表的设计都可以采用不同的存储引擎，根据实际的数据处理，选择合适的存储引擎。

InnoDB和M有ISAM是常用的存储引擎

使用profiling分析SQL语句的性能

mysql 自带profiling性能分析工具，用来查询SQL执行多少时间，System lock和 Table loc花费多少时间，对定位一个语句的I/O消耗和CPU消耗，非常重要。

select @@profiling; 	# 查看profiling是否开启，0标识关闭，1标识开启
set profiling=1;			# 把profiling打开，设置为 1

设置了profiling之后，每一个SQL语句都会被记录分析

使用show profiles;可以查看在打开profiling之后所有被记录的操作

show profiles;			# 查看系统中保存的所有Query的profile概要信息
+----------+------------+------------------------------------------------------------+
| Query_ID | Duration | Query
|
+----------+------------+------------------------------------------------------------+
| 1 | 0.00183100 | show databases
|
| 2 | 0.00007000 | SELECT DATABASE()
|
| 3 | 0.00099300 | desc test
|
| 4 | 0.00048800 | show tables
|
| 5 | 0.00430400 | desc test_profiling
|
| 6 | 1.90115800 | select status,count(*) from test_profiling group by status |
+----------+------------+------------------------------------------------------------+
3 rows in set (0.00 sec)

MYSQL为每一个操作都生成了一个Query_ID

我们可以使用show profile for query 2这样的形式来查看第二条语句具体执行过程分析。

1. 查询指定的Query ID

show profile for query 2;

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000083 |
| checking permissions | 0.000009 |		# 权限检查
| Opening tables       | 0.026007 |		# 打开表
| init                 | 0.000040 |		# 初始化
| System lock          | 0.000008 |		# 锁系统
| optimizing           | 0.000002 |		# 优化查询
| statistics           | 0.000022 |		# 
| preparing            | 0.000302 |		# 准备
| executing            | 0.000005 |		# 执行
| Sending data         | 0.000406 |
| end                  | 0.000004 |
| query end            | 0.000005 |
| closing tables       | 0.000006 |
| freeing items        | 0.000063 |
| cleaning up          | 0.000009 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

针对单个Query获取详细的profile信息，查看CPU和阻塞I/O的情况

show profile cpu,block io for query 2;

查看某一个SQL语句的执行计划

EXPLAIN SELECT * FROM player; # 便可在MYSQL查询优化器中是如何执行SQL语句的

DDL创建数据库&数据表

DDL:Data Definition Language，定义了数据库结构和数据表结构

常用功能:增删改，对应命令为:CREATE、DROP、ALTER，执行DDL时，不需要COMMIT，就可以执行任务。

1.对数据库定义

# 创建数据库 基本语法: CREATE DATABASE 数据库名称;
CREATE DATABASE nba;		//创建数据库
# 删除数据库 基本语法: DROP DATABASE 数据库名称;
DROP DATABASE nba;

2.对数据表定义

CREATE TABLE table_name (字段名 数据类型, ...);
# 创建一个球员表，表明Player,字段player_id,player_name-->int,varchar(255)
CREATE TABLE player(
player_id INT(11) NOT NULL AUTO_INCREMENT,
player_name VARCHAR(255) NOT NULL
);

数据类型中int(11)表示:显示长度为11位，括号内的参数11代表最大有效显示长度，与类型的数值范围无关

修改表结构

1.添加字段

# 给表player添加一个age字段，类型为int(11)
ALTER TABLE 表名 ADD (字段名称 数据类型);
ALTER TABLE player ADD (age int(11));

2.修改字段名

# 字段age改为player_age
# ALTER TABLE 表名 RENAME COLUMN 旧字段名 to 新字段名; 
ALTER TABLE player RENAME COLUMN age to player_age;

3.修改字段的数据类型

# 表player的player_age字段数据类型设置为float(3,1)
# ALTER TABLE 表名 MODIFY (字段名称 数据类型);
ALTER TABLE player MODIFY (player_age float(3,1));

MYSQL浮点型和定点型可以用类型名称后加(M,D)来表示，M表示改值的总长度，D表示小数点后面的长度，M和D又称之为精度和标度

4.删除字段

# 删除表中字段
# ALTER TABLE 表名 DROP COLUMN 字段名;
ALTER TABLE player DROP COLUMN player_age;

数据表常见约束

对字段约束的目的是:保证RDBMS里面的数据的准确性和一致性

1.主键约束

1. 逐渐约束作用:唯一标识一条记录，不能重复，不能为空，UNIQUE+NOT NULL，且一个数据表有且只能有一个主键。
2. 主键可以是一个字段，也可以是多个字段复合组成（联合主键）
3. 主键是索引的一种，而且是唯一索引的一种。

2.外键约束

外键确保了表与表之间的完整性，一个表的外键对应另一个表的主键，外键可以重复，也可以为空

3.唯一性约束

表明了字段在表中的数值是唯一的，

唯一性约束相当于创建了一个约束和普通索引，目的是保证字段的正确性。

普通索引只是提升数据检索的速度，并不对字段的唯一性进行约束

4.NOT NULL约束

对字段定义了NOT NULL，表明字段不应该为空，必须有取值

5.CHECK约束

用来检查特定字段取值范围的有效性

CHECK约束的结果不能为FALSE

约束的设置

表字段约束，有两种方式

添加方式1 建表时添加

CREATE TABLE user_test01(
	user_id INT primary key,
  user_name VARCHAR(20) NOT NULL,
  # user_age INT CHECK (user_age>0 AND user_age<200),
  user_Phone VARCHAR(11) # unique
);

添加方式2 建表后添加

ALTER TABLE user_test01 add unique(user_phone);

设计数据表的原则

1.数据表个数越少约好

RDBMS核心在于对实体和联系之间的定义—>ER图,数据表越少，证明实体和联系设计约简洁，方便理解和操作

2.数据表的字段个数越少约好

字段个数越多，数据冗余的可能越大。设置字段个数少的前提是各个字段相互独立，而不是某个字段由其他字段计算出来得到。当然字段个数少是相对的，通常会在数据冗余和检索效率中进行平衡。

3.数据表中联合主键的字段个数越少约好

设置主键是为了确定唯一性，当一个字段无法确定唯一性的时候，就需要采用联合主键的方式。联合主键中的字段越多，占用的索引空间越大，不仅加大理解难度，还会增加运行时间和索引空间。

4.使用主键和外键越多约好

数据库的设计实际上就是定义各种表，以及各种字段之间的关系。关系越多，证明实体之间的冗余度越低，利用率越高。这样坐的好处在于不仅仅保证数据表之间的独立性，还能提升相互之间的关联使用率。

总体上看设计表的原则:

使用更少的表、更少的字段、更少的联合主键来完成主键的设计、使用主键和外键越多约好，可复用则是通过主键、外键的使用来增强数据表之间的复用率。

关于使用外键的问题:

1. 首先外键可以实现强一致性，如果正确性>性能的话，建议使用外键，它可以在数据库层面保证数据的完整性和一致性

2. 不使用外键，也可以在业务层进行实现，但会让业务逻辑与数据具备一定的耦合性—>业务逻辑和数据必须同时修改，显然在工作中，业务层会经常发生变化

3. 在超大型的数据应用场景，大量的插入、更新和删除在外键的约束下会降低性能，同时数据库在水平拆分和分库的情况下，数据库端也做不到执行外键约束，另外，在高并发的情况下，外键的存在也会造成额外的开销，因为每次更新操作，都要检查另外一张表的数据，容易造成死锁，所以大型项目都是通过业务层实现，取消外键提高效率

数据检索

SELECT 查询基本语法

1.查询列

# 列名之间用逗号(,)分割即可
SELECT name,age FROM user;
# 生产环境中要尽量避免使用SELECT * 
SELECT * FROM user;

2.别名

SELECT name AS 名字,age AS 年龄 FROM user;

3.查询常数

# 在SELECT查询结果中增加一列固定的常数项
SELECT '用户表' AS platform ,name FROM USER;

4.去掉重复行

# 去掉重复的行
SELECT DISTINCT name,age FROM user;

1. DISTINCT 需要放在所有列名前

2. DISINCT其实是对后面所有列名组合进行去重

排序检索数据

使用ORDER BY子句来排序

1. 排序的列名:ORDER BY后面可以有一个或多个列名，如果是多个列名进行排序，会拍先后顺序来排序

2. 排序的顺序:ORDER BY后面可以注明排序规则，ASC代表递增排序，DESC代表递减排序。没有注明排序规则，默认按照ASC递增排序，

3. 非选择排序:ORDER BY 可以使用非选择列排序，所以即使SELECT后面没有这个列名，同样放到ORDER BY后面排序。

4. ORDER BY通常位于SELECT语句的最后一条子句，否则会报错

SELECT name,age FROM user ORDER BY name ASC ,age DESC;

# 单列排序
SELECT * FROM test1 ORDER BY data_time;
# 多列排序
SELECT * FROM test1 ORDER BY `status`, date_time DESC
# 自定义排序
SELECT * FROM test1 ORDER BY FIELD(`status`, 3, 2, 4, 1, 5), date_time DESC
使用"FIELD()"函数，可指定顺序

约束返回结果的数量

使用LIMIT关键字，可以设置返回结果的数量

SELECT name, age FROM user ORDER BY age DESC LIMIT 5;

约束返回结果的数量可以减少数据表的网络传输量

如果指导想要的记录只有一条时，使用LIMIT来约束，可以提升查询效率

SELECT 执行顺序

SELECT … FROM … WHERE … GROUP BY(聚集函数) … HAVING … ORDER BY …

在SELECT语句执行过程中，每个步骤都会产生一个虚拟表，然后将这个虚拟表传入下一个步骤中作为输入。虚拟表，是不可见的。

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执行原理:

步骤一:

​ 1.首先通过CROSS JOIN求笛卡尔积，相当于得到了虚拟表vt1-1

​ 2.通过ON进行筛选，在虚拟表vt1-1基础上进行筛选，得到虚拟表vt1-2

​ 3.添加外部行，如果是左连接、右连接、全连接，就会涉及到外部行，也就是在虚拟表1-2基础上增加外部行，得到虚拟表vt1-3,拿到了数据查询表的原始数据，vt1

步骤二:

​ 在vt1基础上进行where阶段，结果筛选过滤，得到虚拟表vt2

步骤三、四

​ 在虚拟表vt2基础上进行了分组和分组过滤，得到了中间虚拟表vt3和vt4

步骤五:

​ 完成了条件筛选部分之后，开始筛选表中提取的字段，进入SELECT 和DISTINCT阶段，首先SELECT阶段会提取到想要的字段，然后DISTINCT阶段，过滤掉重复的行，分别得到中间的虚拟表vt5-1和vt5-2

步骤六:

​ 完成了提前想要字段的数据之后，按照指定的字段进行排序，ORDER BY阶段，得到虚拟表vt6

步骤七:

​ 在vt6基础上，取出指定行记录，LIMIT阶段，得到最终结果，对于的虚拟表vt7

SELECT * 使用情况，与SELECT查询效率提升

1. 数据量少的情况下，使用SELECT *，是可以的

2. 数据量大的时候，如果不需要把所有列检索出来，还是先指定所需的列名，因为写清列名，可以减少数据表查询的网络传输量。

3. 知道查询的记录数，可以通过约束返回结果的数量，提高SELECT效率

SQL数据过滤

WHERE子句的基本格式是:SELECT …(列名) FROM …(表名) WHERE …(子句条件)

SELECT name, age FROM user WHERE age > 18;
SELECT name, age FROM user WHERE name like '吴_' AND age > 18; 
SELECT name, age FROM user WHERE likes IS NOT NULL;
SELECT name, age FROM user WHERE age > 18 AND money > 1000 ORDER BY (age*money) DESC;
SELECT name, age FROM user WHERE age > 18 AND money > 1000 ORDER BY (age*money) DESC;

WHERE 子句中同时出现，AND和ORDER操作符时候，需要考虑执行的先后顺序，优先级 () 最后，AND其次，OR最后。

非连续性的范围查询用in

SELECT name, age, gender, birthdate FROM USER
WHERE (gender in ('男','女')) 
OR DATE(birthdate) NOT BETWEEN '2010-01-01' AND '2012-01-01'
ORDER BY age DESC;

日期类型数据进行检索是，尽量使用DATE函数，讲字段birthdate转换为日期类型再进行比较。

避免部分日期问题出错

使用通配符进行过滤

通配符用来匹配值的一部分的特殊字符，LIKE操作符

SELECT name, age FROM user WHERE name LIKE '吴%';
SELECT name, age FROM user WHERE name LIKE '%吴'; # 这样会产生全表扫描的情况
# %开头， 索引可能会失效，产生全表扫描的效果

% 匹配零个活多个字符

_ 匹配一个字符

绑定变量的方式，去避免硬解析。

​

Problem

mysql为什么权限检查为什么要放在优化器优化之后，放在语义分析之后，不就可以省去了执行路径的开销？还是说在前台界面业务层面上就规避了权限检查？

表的闭环是什么情况？

ORDER BY非选择排序是什么？

4.分页查询的时候int pageIndex,int pageLength

SELECT * FROM user limit pageIndexpageLength ,pageIndexpageLength+pageLength;

MYSQL两种排序方式:FileSort和Index排序，Index排序效率更高。

Index排序:索引可以保证数据的有序性，因此不需要再进行排序。

FileSort排序:一般再内存中进行排序，占用CPU较多，如果待拍结果较大，会产生临时文件I/O到磁盘中进行排序，效率低。