概述
数据库：存储数据的仓库，数据是有组织地进行存储 DB
数据库管理系统：操纵和管理数据库的大型软件 DBMS
SQL：操作关系型数据库的编程语言，定义了一套操作关系型数据库的统一标准
MySQL启动：输入net start mysql80 停止用stop
客户端连接：
1 打开cmd 输入mysql -u root -p
2 打开MySQL 8.0 Command Line Client - Unicode
数据模型：一个数据库服务器中可以创建多个数据库，一个数据库可创建多张表，表才是最终存储数据的
关系型数据库：1 使用表存储数据 2 使用SQL语言操作
SQL
SQL通用语法
SQL语句可以单行或者多行书写，以分号结尾
可以用空格或者缩进增强语句的可读性
不区分大小写，关键字建议用大写
单行注释:--来注释内容 或#注释内容
多行注释：/*注释内容*/
SQL分类
DDL
数据库的查询
查询所有数据库 show databases ;
查询当前数据库 select database（）；
创建数据库
create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则]；
删除数据库 drop database[if exists] 数据库名 ；
使用数据库 use 数据库名 ；
表的查询
查询当前数据库所有表 show tables ;
查询表结构 desc 表名；
查询指定表的建表语句 show create table 表名 ；
//在查询当前数据库的所有表时应该先use该数据库
表的创建
//该过程使用的符号全部为英文符号
// ）前面的字段没有逗号
mysql> use w ;
Database changed
mysql> create table user(
-> id int,
-> name varchar(50),
-> age int,
-> gender varchar(1)
-> )comment '用户表';
Query OK, 0 rows affected (0.04 sec)
mysql> show tables ;
+-------------+
| Tables_in_w |
+-------------+
| user |
+-------------+
1 row in set (0.00 sec)
mysql> desc user ;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| gender | varchar(1) | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (0.01 sec)
mysql> show create table user ;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`age` int DEFAULT NULL,
`gender` varchar(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='用户表' |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
数据类型
数值类型，字符串类型，日期时间类型
//有符号指允许出现负数
//123.66 精度5（整个数的长度）标度2（小数点后的位数）
//使用后三种在使用时需要确定精度和标度 例如 score double （4，1）
//char(10)即使存储一个字符也会占用十个字符的空间,其他空间用空格补位
//varchar（10）存储几个字符就占用几个字符的空间 性能差(需要根据内容计算所需空间)
表的修改
添加字段 alter table 表名 add 字段名 字段类型(长度)；
修改数据类型 alter table 表名 modify 字段名 新数据类型(长度)；
修改字段名和字段类型 alter table 表名 change 旧字段名 新字段名 类型(长度);
删除字段 alter table 表名 drop 字段名；
指定表名 alter table 表名 rename to 新表名；
删除表 drop table [if exists] 表名；
删除指定表，并重新创建该表 truncate table 表名；
DML
对数据库表中的数据进行增删改
给指定字段添加数据 insert into 表名（字段名1，字段名2，，，）values（值1，值2，，，）；
给全部字段添加数据 insert into 表名 values （值1，值2，，，，）；
批量添加数据 insert into 表名（字段名1，字段名2，，，）values（值1，值2，，，），（值1，值2，，，）（值1，值2，，，）；
insert into 表名 values （值1，值2，，，，）（值1，值2，，，），（值1，值2，，，）；
注意 1 插入数据时指定的字符顺序需要与值的顺序一一对应
2 字符串与日期型数据应包含在引号中
3 插入数据的大小应在字段的规定范围内
修改表的数据 update 表名 set 字段名1=值1，字段名2=值2，，，，[where 条件]；
修改条件语句如果没有就会修改整张表的数据
删除表的数据 delete from 表名 [where条件]；
该语句不能用于删除某个字段的值，若要删除某个字段用updata null
DQL
查询数据库表中的记录
基本查询
1 查询多个字段 select 字段1，字段2，字段3，，，from 表名；
查询所有字段 select * from 表名；
2 设置别名 select 字段1[as 别名1]，字段2[as 别名2]，字段3[as 别名3]，，，from 表名；
as可以省略 [ ]内部也可直接省略
3 去除重复记录 select distinct 字段列表 from 表名；
条件查询
__表示占的位置有几个 %指不管前面是什么，只匹配%后面的内容
聚合函数
分组查询
排序查询
分页查询
limit n m 从第n条开始查询m条数据
DCL
管理数据库用户，控制数据库访问权限
函数
指的是一段可以直接被另一段程序调用的程序或代码
字符串函数
索引从1开始 空格算一个索引
数值函数
返回的模就是取余的结果
日期函数
delect 函数；
流程函数
约束
用于限制存储在表中的数据 保证数据库中的数据正确，有效，完整
外键约束
让两张表的数据之间建立连接
添加外键
1 创建表时添加
2 表创建好后添加
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名)；
删除外键
alter table 表名 drop foreign key 外键名称；
多表查询
多表关系
一对多：在多的一方建立外键，指向一的一方的主键
多对多：建立第三张中间表，中间表至少包含两个外键，分别联系两方主键
一对一：用于单表拆分 在任意一方加入外键，关联另一方的主键，并且设置外键是唯一的(unique)
多表查询概述
从多张表中查询数据
笛卡尔积：A集合和B集合组合的所有情况(多表查询时，需要消除无效的笛卡尔积)
连接查询
内连接：查询A,B交集部分的数据
1 隐式内连接 select 字段列表 from 表1，表2 where 条件，，，；
2 显式内连接 select 字段列表 from 表一[inner] join 表二 on 连接条件；
外连接：1 左外连接：查询左表所有数据，以及交集部分的数据
select 字段列表 from 表1 left [outer] join 表2 on条件；
2 右外连接：查询右表所有数据，以及交集部分的数据
select 字段列表 from 表1 right [outer] join 表2 on条件；
3 自连接：当前表与自身的连接查询，自连接必须用表别名
select 字段列表 from 表A 别名A join 表A 别名B on 条件，，，；
自连接查询可以是内连接查询也可以是外连接查询
联合查询
union[all]查询：把多次查询结果合并起来，形成一个新的查询结果集
1 union是对查询的结果去重后进行拼接，union all是直接拼接
2 联合查询的多张表列数必须一致，字段类型也必须一致
这样字段不一致就会报错
子查询
标量子查询
子查询返回的结果是单个值(数字，字符串，日期等)，最简单的形式
常用操作符：= <> > <
将两步需要完成的转换成一步完成的，中间用=连接
列子查询
子查询返回的结果是一列(可以是多行)
常用操作符：in ,not in, any, some, all
all可以换成any
行子查询
子查询返回的结果是一行(可以是多列)
常用操作符：= <> in ，not in
子查询的结果如图
两个条件都必须满足
表子查询
子查询返回的结果是多行多列
常用操作符：in
强调满足任意一个条件都可以
多表查询练习
1
注意在on后面的条件限制关键字是where
2
去重用distinct
3
两张表之间没有外键关联，唯一的联系就是员工的薪资介于薪资等级表的最大值和最小值之间
采用的是隐式内连接
另一种写法，连接条件用between...and...
4
先分析表，表与表之间的连接条件，查询条件跟在最后面
有n张表连接条件就有n-1个
依旧用隐式内连接（要查询的内容在三张表上）
5
注意avg( )的用法
6
对同一个表进行查询多次查询，返回不同的结果，可以将表起别名
7
子查询
事务
是一组操作的集合，会把所有操作作为一个整体一起向系统提交或撤销请求(即操作要么同时成功，要么同时失败)
当执行一条DML语句，MySQL会立即隐式的提交事务
事务操作
第一种：
查看/设置事务提交方式
select @@autocommit;
set @@autocommit = 0; //0手动提交 //1自动提交
提交事务
commit;
回滚事务(如果事务执行过程中出现异常，就会回滚事务，保证数据库里的数据是正确的)
rollback；
第二种：
开启事务
start transaction 或 begin;
提交事务
commit;
回滚事务
rollback;
事物的四大特性 ACID
原子性：食物是不可分割的最小操作单元，要么全成功，要么全失败
一致性：事务完成时，务必使所有数据都保持一致
隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
永久性：事务一旦提交或者回滚，对数据库中数据的改变是永久的
并发事务问题
事务隔离级别
从上到下级别依次升高 隔离级别越高数据越安全，但是性能越低
查看事务隔离级别：select @@transaction_isolation;
设置事务隔离级别：set [session/global] transaction isolation level read uncommitted/resd committed/repeatable read/serializable;
存储引擎
简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式
存储引擎是基于表的，而不是基于库的，所以存储引擎也可被称为表类型
默认存储引擎：InnoDB
查询建表语句：
show creste table 表名 ；
查询当前数据库支持的存储引擎：
show engines;
建表时，指定存储引擎
存储引擎特点
InnoDB
是一种兼顾高可靠性和高性能的通用存储引擎
特点： 1 DML操作遵循ACID模型，支持事务
2 行级锁，提高并发访问性能
3 支持外键foreign key约束，保证数据的完整形和正确性
文件：
逻辑存储结构：
表空间 段 区（1M） 页（16K） 行
一个区包含64个页
MyISAM
是MySQL早期的默认存储引擎
特点：1 不支持事务，不支持外键
2 支持表锁，不支持行锁
3 访问速度快
Memory
它引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用
特点：1 内存存放
2 hash索引（默认）
InnoDB与MyISAM的区别：
InnoDB支持事务 行锁 支持外键
MyISAM不支持事务 表锁 不支持外键
存储引擎的选择
索引
索引是帮助数据库高效获取数据的数据结构（有序）
索引的优缺点
优点：提高查询效率，降低数据库IO成本
通过索引对数据进行排序，降低数据排序成本，降低CPU消耗
缺点：索引会占用存储空间
索引大大提高了查询效率，同时也降低了insert、update、delete的效率
索引结构
Mysql的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要包含下面几种：
B+Tree (多路平衡查找树)
注意：5阶 4个值 4个指针
1 每个节点可以存储多个key(有n个key,就有n+1个指针)
2 所有的数据都存储在叶子节点，非叶子节点仅用于索引数据
3 所有的叶子节点形成了一个单向链表，便于数据的排序及区间范围查询
B+Tree与B-Tree的区别：
B+Tree:
1 所有的数据都会出现在叶子节点
2 叶子节点形成一个单向链表
Mysql中的B+Tree在原来的基础上进行优化，增加了一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能
Hash
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中
如果两个（或多个）键值，映射到一个相同的槽位上，他们就产生了hash冲突（也称为hash碰撞），可以通过链表来解决
特点：
1 只等用于对等比较（=，in）,不支持范围查询（between,<,>...）
2 无法利用索引完成排序操作
3 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引
索引分类
第一种
第二种
在InnoDB存储引擎中，根据索引的存储形式，又分为聚集索引和二级索引
聚集索引：将数据存储与索引放到了一块 必须有，而且只有一个
聚集索引的叶子节点下面挂的row是几 对应的就是第几行的数据
聚集索引选取规则：
如果存在主键，主键索引就是聚集索引
如果不存在主键，第一个UNIQUE索引作为聚集索引
如果表没有主键，或没有合适的唯一索引，则nnoDB会自动生成一个rowid作为隐藏的聚集索引
二级索引：将数据与索引分开存储 可以存在多个
二级索引的叶子节点下面挂的是主键值
回表查询
先根据二级索引查到对应的主键，再根据聚集索引通过拿到的主键 找到要查找的这一行的数据
索引语法
创建索引
CREAE [UNIQUE][FELLTEXT] INDEX 索引名称 ON 表名 （表中的字段名，可以是联合索引，字段之间用逗号隔开）；
查看索引
SHOW INDEX FROM 表名；
删除索引
DROP INDEX 索引名 ON 表名；
SQL性能分析
查询当前数据库增删改查的访问频次
SHOW GLOBAL STATUS LIKE 'Com_____';
慢查询日志
该日志记录了所有执行时间超过指定参数（默认10秒）的所有SQL语句的日常
profile详情
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了
profile操作：
select @@profiling ;
打开profiling操作：
set profiling = 1 ;
查看每一条SQL的耗时基本情况
show profiles;
查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
explain
explain或者desc命令获取mysql如何执行select语句的信息，包括在select语句执行过程中表如何连接和连接的顺序
直接在select语句之前加上关键字
explain select 字段列表 from 表名 where 条件；
explain执行计划各字段含义：
id：select查询的序列号
表示查询过程中执行select字句或者是操作表的顺序
id相同，执行顺序从上到下；id不同，值越大，越先执行
此处因为id都是1，所以执行顺序是：表s 表sc 表c
select_type:
type:访问类型
性能由好到差的连接类型：NULL、system、const、eq_ref、ref、range、index、all
使用主键或唯一索引查询时会出现const
使用非唯一性索引查询时会出现ref
全表扫描会出现all
possible_key:
显示可能应用在这张表上的索引，一个或多个
key:
实际用到的索引，如果为null，则没有使用索引
key_len:
表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度
在不损失精确度的前提下，长度越短越好
rows:
MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的
filtered:
表示返回结果的行数占需读取行数的百分比，filtered的值越大越好
Extra:
额外的信息，在前面的字段中都没有展示出来的信息
索引使用规则
最左前缀法则
如果索引了多列（联合索引），要遵循最左前缀法则。
最左前缀法则指的是查询时最左侧的字段必须存在（最左侧字段的位置在哪里不影响走索引），并且不跳过索引中的列。
如果跳跃某一列，索引将部分失效（后面的字段索引失效）
profession age status这三个字段使用的是联合索引
1
该条查询语句跳过了最左侧，所以会进行全表查询，索引失效
2
该条查询语句跳过了中间的age字段，所以索引部分失效，因此status上的索引失效
范围查询
联合索引中，出现范围查询（>,<），范围查询右侧的索引失效
profession age status这三个字段使用的是联合索引
此时status的查询将不走索引
用>= 此时可以避免索引失效
索引列运算
如果在索引列上进行运算操作，索引将失效
substring(phone,10,2)意思是从phone字段的第10个位置截取2位，这两位是15，并进行查询
该操作是函数运算，因此走的是全表查询，索引失效
字符串不加引号
字符串类型字段使用时不加索引，索引将失效
模糊查询
如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效
索引不失效
索引失效
or连接的条件
用or分割开的条件，如果有一个没有索引，那么涉及到的索引都不会被用到
数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引
SQL提示
是优化数据库的一个重要手段，就是在SQL语句中加入一些人为的提示来达到优化操作的目的
use index:（建议它用，用不用是它的选择）
ignore index：
force index：（强制它用这个索引）
覆盖索引
尽量使用覆盖索引（查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到），减少使用select *（它很容易走回表查询）
注意：
覆盖索引不需要回表
回表查询的效率不高
前缀索引
当字段类型是字符串时，有时候需要索引很长的字符串，会让索引变得很大，查询时会浪费大量的磁盘IO，影响效率。此时可以只将字符串的一部分前缀，建立索引。
语法：
create index 索引名 on 表名（字段名（前缀长度））
单列索引与联合索引
单列索引：即一个索引值包含单个列
联合索引：即一个索引包含了多了列
在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引
此时没有用name上的索引，而是用了phone上的索引并进行了回表查询
索引设计原则
SQL优化
插入数据
insert优化
批量插入（一次最好不超过1000条）
手动提交事务（自动提交的话，会插入一条数据提交一次）
主键顺序插入
大批量插入数据
一次性大批量插入数据，使用insert性能较低，此时可以用MySQL数据库提供的load指令进行插入
主键顺序插入性能高于乱序插入
主键优化
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表（IOT）
页分裂
页可以为空，也可以填充一半，也可以填充100% 每个页包含了2-N行数据（如果一行数据过大，会溢出），根据主键排列
主键顺序插入：第一页写满了写第二页，依次往后
主键乱序插入：
要把50插入到页中
会开辟一个新的页，将后面两个数据移到新的一页，再把50插入新的页
移动页的顺序 创建新的链表
页合并
当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记为删除并且它的空间变得允许被其他记录声明使用
示例如下：
此时2号会和3号合并
合并后再插入数据20，就会进入到3号数据页
主键设计原则
order by优化
Using filesort:通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作 所有不是通过索引直接返回排序结果的排序都叫FileSort排序
Using index:通过有序索引顺序扫描直接返回有序数据，不需要额外排序，操作效率高
group by优化
在分组时，通过创建联合索引来提高效率
分组操作时，索引的使用也满足最左前缀法则
limit优化
通过创建覆盖索引加子查询的方式进行优化
count优化
count的几种用法：
update优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁
锁
锁是计算机协调多个进程或线程并发访问某一资源的机制
全局锁
锁定数据库中的所有表
全局锁就是对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的DML、DDL语句，已经更新操作的事务提交语句都将被阻塞
应用场景：做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性
加上全局锁
flush tables with read lock ;
进行数据备份
mysqldump -uroot -p123456 要备份的数据库名 > xxx.sql（要把备份的数据存到哪个sql文件中）在windows命令行中执行
解锁
unlock tables ;
数据库中加全局锁是一个比较重的操作，存在的问题：
在InnoDB引擎中，我们可以在备份时加上参数 --single-transaction参数来完成不加锁的一致性数据备份
musqldump --single-transaction -uroot -p123456 要备份的数据库名 > xxx.sql（要把备份的数据存到哪个sql文件中）
表级锁
每次操作锁住整张表。锁定粒度大，发生锁冲突的概率最高，并发性最低。
主要分为：1 表锁
2 元数据锁（meta data lock,MDL）
3 意向锁
表锁
1 表共享读锁（read lock）
2 表独占写锁 (write lock)
语法：1 加锁 lock tables 表名 read/write
2 释放锁 unlock tables / 客户端断开连接
读锁不会阻塞其他客户端的读，但是会阻塞写；
写锁既会阻塞其他客户端的读，又会阻塞其他客户端的写
元数据锁（MDL）
MDL加锁过程是系统自动控制，无需显式使用，在访问一张表的时候会自动加上。DML锁主要作用是维护表元数据统一性，在表上有活动事务时，不可对元数据进行写入操作。
为了避免DML与DDL冲突，保证读写的准确性
查看元数据锁
意向锁
为了避免DML在执行时，表锁与行锁的冲突，在InnoDB中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查
意向共享锁(IS):与表锁共享锁(read)兼容，与表锁排它锁(write)互斥
意向排它锁(IX): 与表锁共享锁以及排它锁都互斥。意向锁之间不会互斥
行级锁
每次操作锁住对应的行数据。锁定粒度最小，发生锁冲突的概率最低，并发度最高。
InnoDB的数据是基于索引组织的，行锁是通过对索引上的索引项加锁来实现的，而不是对记录加的锁。对于行级锁，主要分为下面三类：
行锁
共享锁：允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁
排他锁：允许获得排它锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排它锁
注意：
1 针对唯一索引进行检索时，对已存在的数据进行等值匹配时，将会自动优化为行锁
2 InnoDB的行锁是针对于索引加的锁，如果要检索的数据没有索引，那么InnoDB将会对表中所有记录加锁，此时就会升级为表锁
间隙锁 临键锁
间隙锁锁的是间隙，不包含对应的数据记录
临界锁既会锁数据记录又会锁数据记录之前的间隙
间隙锁唯一目的是防止其他事务插入间隙。间隙锁可以共存，一个事务采用的间隙不会阻止另一个事务在同一间隙上采用间隙锁
终于完结了，撒花~~~~~~
扫一扫
7万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
