目录标题
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这样的形式来查看第二条语句具体执行过程分析。
- 查询指定的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.主键约束
- 逐渐约束作用:唯一标识一条记录,不能重复,不能为空,UNIQUE+NOT NULL,且一个数据表有且只能有一个主键。
- 主键可以是一个字段,也可以是多个字段复合组成(联合主键)
- 主键是索引的一种,而且是唯一索引的一种。
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.使用主键和外键越多约好
数据库的设计实际上就是定义各种表,以及各种字段之间的关系。关系越多,证明实体之间的冗余度越低,利用率越高。这样坐的好处在于不仅仅保证数据表之间的独立性,还能提升相互之间的关联使用率。
总体上看设计表的原则:
使用更少的表、更少的字段、更少的联合主键来完成主键的设计、使用主键和外键越多约好,可复用则是通过主键、外键的使用来增强数据表之间的复用率。
关于使用外键的问题:
首先外键可以实现强一致性,如果正确性>性能的话,建议使用外键,它可以在数据库层面保证数据的完整性和一致性
不使用外键,也可以在业务层进行实现,但会让业务逻辑与数据具备一定的耦合性—>业务逻辑和数据必须同时修改,显然在工作中,业务层会经常发生变化
在超大型的数据应用场景,大量的插入、更新和删除在外键的约束下会降低性能,同时数据库在水平拆分和分库的情况下,数据库端也做不到执行外键约束,另外,在高并发的情况下,外键的存在也会造成额外的开销,因为每次更新操作,都要检查另外一张表的数据,容易造成死锁,所以大型项目都是通过业务层实现,取消外键提高效率
数据检索
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;
DISTINCT 需要放在所有列名前
DISINCT其实是对后面所有列名组合进行去重
排序检索数据
使用ORDER BY子句来排序
-
排序的列名:ORDER BY后面可以有一个或多个列名,如果是多个列名进行排序,会拍先后顺序来排序
-
排序的顺序:ORDER BY后面可以注明排序规则,ASC代表递增排序,DESC代表递减排序。没有注明排序规则,默认按照ASC递增排序,
-
非选择排序:ORDER BY 可以使用非选择列排序,所以即使SELECT后面没有这个列名,同样放到ORDER BY后面排序。
-
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查询效率提升
数据量少的情况下,使用SELECT *,是可以的
数据量大的时候,如果不需要把所有列检索出来,还是先指定所需的列名,因为写清列名,可以减少数据表查询的网络传输量。
知道查询的记录数,可以通过约束返回结果的数量,提高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到磁盘中进行排序,效率低。