MySQL概述
-mysql启动
net start mysql80
net stop mysql80-mysql客户端连接
mysql -u root -pSQL
| 分类 | 全称 | 说明 |
| DDL | Data Definition Language | 数据定义语言，用来定义数据库对象 |
| DML | Data ManipulationLanguage | 数据操作语言，用来对数据库表中的数据进行增删改 |
| DQL | Data Query Language | 数据查询语言，用来查询数据库中表的记录 |
| DCL | Data Control Language | 数据控制语言，用来创建数据库用户、控制数据库的访问权限 |
DDL
/*DDL 数据库操作*/
/*查询所有数据库*/
SHOW DATABASES;
/*查询当前数据库*/
SELECT DATABASE();
/*创建*/
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
/*删除*/
DROP DATABASE [IF EXISTS] 数据库名;
/*使用*/
USE 数据库名;/*DDL表操作-查询*/
/*查询当前数据库所有表*/
SHOW TABLES;
/*查询表结构*/
DESE 表名;
/*查询指定表的建表语句*/
SHOW CREATE TABLE 表名;
/*DDL 表操作-创建*/
CREATE TABLE 表名(
字段1 字段1类型 [COMMENT 字段1注释],
字段2 字段2类型 [COMMENT 字段2注释],
......
字段n 字段n类型 [COMMENT 字段n注释]
)[COMMENT 表注释];
/*DDL -表操作-修改*/
/*添加字段*/
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释][约束];
/*修改数据类型*/
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
/*修改字段名和字段类型*/
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
/*删除字段*/
ALTER TABLE 表名 DROP 字段名;
/*修改表名*/
ALTER TABLE 表名 RENAME TO 新表名;/*DDL 表操作-删除*/
/*删除表*/
DROP TABLE [IF EXISTS] 表名;
/*删除指定表，并重新创建该表*/
TRUNCATE TABLE 表名;
DML
/*DML INSERT*/
/*给指定字段添加数据*/
INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...),(...);
/*给全部字段添加数据*/
INSERT INTO 表名 VALUES(值1,值2,...),(...);
注意：
- 插入数据时，指定的字段顺序需要与值的顺序一致
- 字符串和日期类型数据要包含在 ' ' 中
- 插入数据的大小，要在字段的规定范围之类
/*DML 修改数据*/
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2,...[WHERE 条件];
/*DML 删除数据*/
DELETE FROM 表名 [WHERE 条件];
DQL
/*查询语法结构*/
SELECT
字段列表
FROM
表名列表
WHERE
条件列表
GROUP BY
分组字段列表
HAVING
分组后条件列表
ORDER BY
排序字段列表
LIMIT
分页参数
/*-------------------------------------------------------------------------------*/
/*查询所有字段*/
SELECT * FROM 表名;
/*在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。
说明：
增加查询分析器解析成本。
增减字段容易与 resultMap 配置不一致。
无用字段增加网络 消耗，尤其是 text 类型的字段。
*/
/*设置别名*/
SELECT 字段1 [AS 别名1],... FROM 表名;
/*去除重复记录*/
SELECT DISTINCT 字段列表 FROM 表名;
/*---------------------------------------------------------------------------------*/
/*
条件查询
> < = >= <=
BETWEEN...AND... 在某个范围之类，含最小最大值
IN(...) 在in之后的列表中的值，多选一
LIKE 占位符 模糊匹配（_匹配单个字符，%匹配任意多个字符）
IS NULL 是null
AND OR NOT
*/
/*---------------------------------------------------------------------------------*/
SELECT 聚合函数(字段列表) FROM 表名;
/*
聚合函数
count 统计数量
max 最大值
min 最小值
avg 平均值
sum 求和
null值不参与聚合函数运算
*/
/*---------------------------------------------------------------------------------*/
SELECT 字段列表 FROM 表名 GROUP BY 分组字段名 [HAVING 条件];
/*
where与having区别
执行时间：where是分组之前过滤，不满足where条件，不参与分组；而haveing是对分组后的结果过滤。
判断条件：where不能对聚合函数进行判断。
*/
/*---------------------------------------------------------------------------------*/
SELECT FILEDNAME FROM TABLENAME ORDER BY FILED1 ASC/DESC, FILED2 ASC/DESC,...;
/*
如果是多字段排序，当第一个字段值相同时，才会根据第二个字段值排序。
asc：升序（默认，ascend）
desc：降序（descend）
*/
/*---------------------------------------------------------------------------------*/
SELECT FILEDNAME FROM TABLENAME LIMIT BEGIN_INDEX,NUMBER;
/*
分页查询（不同数据库实现方式不同）
起始索引 = （查询页码-1）*每页显示记录数
*/
/*---------------------------------------------------------------------------------*/
DQL执行顺序:
- FROM - WHERE - GRPUP BY - HAVING - SELECT - ORDER BY - LIMIT
DCL
/*DCL - 管理用户*/
/*查询用户*/
USE mysql;
SELECT * FROM user;
/*创建用户*/
CREATE USER 'USERNAME'@'HOSTNAME' IDENTIFIED BY 'PASSWORD';
/*修改用户密码*/
ALTER USER '用户名'@'主机名' IDENTIFIED mysql_native_password WITH BY '新密码';
/*删除用户*/
DROP USER '用户名'@'主机名';/*DCL -权限控制*/
/*
常见权限
ALL,ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据路/表/视图
CREATE 创建数据路/表
*/
/*查询权限*/
SHOW CRANTS FOR 'USERNAME'@'HOSTNAME';
/*授予权限*/
GRANT 权限列表 ON 数据路名.表名 TO '用户名'@'主机名';
/*撤销权限*/
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';数据类型
| TINYINT | 1byte |
| SMALLINT | 2byte |
| MEDIUMINT | 3byte |
| INT/INTEGER | 4byte |
| BIGINT | 8byte |
| FLOAT | 4byte |
| DOUBLE | 8byte |
| DECIMAL |
| CHAR | 0-255 bytes |
| VARCHAR | 0-65535 bytes |
| TINYBLOB | |
| TINYTEXT | |
| BLOB | |
| TEXT | |
| MEDIUMBLOB | |
| MEDIUMTEXT | |
| LONGBLOB | |
| LONGTEXT |
| DATE |
| TIME |
| YEAR |
| DATETIME |
| TIMESTAMP |
函数
字符串函数
| 函数 | 功能 |
| CONCAT(S1,S2,...Sn) | 字符串拼接 |
| LOWER(str) | 将字符串str全部转为小写 |
| UPPER(str) | 将字符串str全部转为大写 |
| LPAD(str,n,pad) | 左填充，用字符串pad对str的左边进行填充，达到n个字符串长度 |
| RPAD(str,n,pad) | 右填充，用字符串pad对str的右边进行填充，达到n个字符串长度 |
| TRIM(str) | 去掉字符串头部和尾部的空格 |
| SUBSTRING(str,start,len) | 返回从字符串start位置起的len个长度的字符串 |
数值函数
| 函数 | 功能 |
| CEIL(X) | 向上取整 |
| FLOOR(X) | 向下取整 |
| MOD(X,Y) | 返回x/y的模 |
| RAND() | 返回0~1内的随机数 |
| ROUND(X,Y) | 求参数x的四舍五入的值，保留y位小数 |
日期函数
| 函数 | 功能 |
| CURDATE() | 返回当前日期 |
| CURTIME() | 返回当前时间 |
| NOW() | 返回当前日期和时间 |
| YEAR(date) | 获取指定date的年份 |
| MONTH(date) | 获取指定date的月份 |
| DAY(date) | 获取指定date的日期 |
| DATE_ADD(date,INTERVAL expr type) | 返回一个日期/时间值加上一个时间间隔expr后的时间值 |
| DATEDIFF(date1,date2) | 返回起始时间date1和结束时间date2之间的天数 |
流程函数
| 函数 | 功能 |
| IF(value,t,f) | 如果value为true，返回t，否则返回f |
| IFNULL(value1,value2) | 如果value1不为空，返回value1，否则返回value2 |
| CASE WHEN [var1] THEN [res1] ...ELSE[default] END | 如果val1为true，返回res1，...否则返回default默认值 |
| CASE [expr] WHEN [val1] THEN [res1] ... ELSE[default] END | 如果expr的值等于val1，返回res1，否则返回default默认值 |
约束
| 约束 | 关键字 | 描述 |
| 非空约束 | NOT NULL | 限制该字段的数据不能为空 |
| 唯一约束 | UNIQUE | 数据都是唯一，不重复的 |
| 主键约束 | PRIMARY KEY | 非空且唯一 |
| 默认约束 | DEFAULT | 保存数据时，如果未指定该字段的值采用默认值 |
| 检查约束 | CHECK | 保证字段值满足给定条件 |
| 外键约束 | FOREIGN KEY | 让两张表的数据之间建立连接 |
AUTO_INCREMENT:主键自增
多表查询
表关系
- 一对多，多张表中新增字段
- 多对多，用一张表维护，包含多张表的主键
- 一对一，多用于单表拆分（基础字段和详情字段分开，以提升操作效率）
/*多表查询*/
select * from table1, table2 where tahle1.id = table2.id;
select * from table1 join table2 on table1.id = table2.id;
多表查询分类
-连接查询
- 内连接
- 外连接：左外连接，右外连接
- 自连接
-子查询
/*内连接*/
select 字段列表 from 表1 [inner] join 表2 on 连接条件...;
/*外连接*/
/*左外连接*/
SELECT FILEDLIST FROM TABLE1 LEFT [OUTER] JOIN TABLE2 ON CONDITION...;
/*右外连接*/
SELECT FILEDLIST FROM TABLE1 RIGHT [OUTER] JOIN TABLE2 ON CONDITION...;
/*自连接*/
SELECT FILEDLIST FROM TABLE1 别名1 JOIN TABLE1 别名2 ON ...;
/*
联合查询 union，union all
把多次查询的结果合并起来，形成一个新的查询结果集*/
SELECT 字段列表 FROM 表a...
UNION [ALL]
SELECT 字段列表 FROM 表b...;
/*
多张表的字段列表需要保持一致
union会对合并后的数据去重
*//*子查询/嵌套查询*/
SELECT * FROM T1 WHERE COLUMN1 = (SELECT COLUMN1 FROM T2);
/*
子查询外部的语句可以是INSERT/UPDATE/DELETE/SELECT中的任意一个
*/
/*行子查询*/
select * from t1 where (c1,c2) = (select c1,c2 from t1 where ...);事务
-事务是一组操作的集合，它是一个不可分割的工作单位。事务会把所有的操作作为一个整体一起向系统提交或撤销。这些操作要么同时成功，要么同时失败。
-事务操作方式一
- 查看/设置事务提交方式：select @@autocommit; set @@autocommit = 0;
- 提交事务：commit;
- 回滚事务：rollbaclk;
-事务操作方式二
- start transaction; 或 begin；
-事务的四大特性ACID
- 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败
- 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态
- 隔离性(Isolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行
- 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的
-并发事务问题
| 问题 | 描述 |
| 脏读 | 一个事务读到另外一个事务还没有提交的数据 |
| 不可重复读 | 一个事务先后读取同一条记录，但两次读取的数据不同 |
| 幻读 | 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在 |
-事务隔离级别
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
| Read uncommitted | T | T | T |
| Read committed | F | T | T |
| Repeatable Read(mysql 默认) | F | F | T |
| Serizlizable | F | F | F |
/*查看事务隔离级别*/
select @@transaction_isolation;
/*设置事务隔离级别*/
set [session|global] transaction isolation leyel {read uncommitted | read committed |...|... }- 事务隔离级别越高，数据越安全，但性能越低。
存储引擎
-简介
- 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎时基于表的，而不是基于库的。
/*创建表时，指定存储引擎*/
create table 表名(
...
) engine = innodb [comment 表注释];
/*查看当前数据库支持的存储引擎*/
show engines;-InnoDB
- 在mysql5.5之后，innodb是默认的mysql存储引擎。
- DML操作遵循ACID模型，支持事务
- 行级锁，提高并发访问性能
- 支持外键foreign key约束，保证数据的完整性和正确性
- 表名.ibd,innoDB引擎的每张表都会对应这样一个表空间文件，存储该表的表结构（frm,sdi）、数据和索引
-innodb逻辑存储结构
Tablespace - Segment - Extent(64个page) - Page - Row
-MyISAM
-mysql早期默认的存储引擎
-特点
- 不支持事务，不支持外键
- 支持表锁，不支持行锁
- 访问速度快
-文件
- xxx.sdi:存储表结构信息
- xxx.MYD:存储数据
- xxx.MYI:存储引擎
-Memory
-memory引擎的表数据存储在内存中，只能将这些表作为临时表，或者缓存使用（硬件问题，断电问题）
-特点
- 内存存放
- hash索引（默认）
-文件
- xxx.sdi：存储表结构信息
-常见存储引擎对比
| 特点 | InnoDB | MyISAM | Memory |
| 存储限制 | 64TB | ||
| 事务安全 | 支持 | ||
| 锁机制 | 行锁 | 表锁 | 表锁 |
| B+树索引 | 支持 | 支持 | 支持 |
| hash索引 | 支持 | ||
| 全文索引 | 支持 | 支持 | |
| 空间使用 | 高 | 低 | |
| 内存使用 | 高 | 低 | 中 |
| 批量插入速度 | 低 | 高 | 高 |
| 支持外键 | 支持 |
-存储引擎选择
- InnoDB:是Mysq的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么innoDB存储引擎是比较合适的选择。
- MyISAM: 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的.不怎么用，有mongodb
- MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。不怎么用，有redis
索引
-索引概述
索引是帮助MySQL高效获取数据的数据结构。
优点：提高数据检索的效率，降低数据库的io成本。通过索引列对数据进行排序，降低数据排序的成本，降低cpu的消耗。
缺点：索引列占用空间。降低了更新表的速度，如insert，update，delete。
-索引结构
MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构
| 索引结构 | 描述 |
| B+Tree | 最常见的索引类型 |
| Hash索引 | 底层数据结构是哈希表，不支持范围查询 |
| R-tree | 空间索引是myisam引擎的一个特殊索引类型，主要用于地理空间数据类型 |
| Full-text | 全文索引，是一种通过建立倒排索引，快速匹配文档的方式 |
| 索引 | InnoDB | MyISAM | Memory |
| B+Tree | 支持 | 支持 | 支持 |
| Hash索引 | 支持 | ||
| R-tree | 支持 | ||
| Full-text | 5.6后支持 | 支持 |
常见索引结构：
B-Tree(多路平衡查找树)：n阶b树（即父节点可以有n个子节点），每个节点最多存储n-1个key、n个指针。添加元素时，向节点中插入元素（有序），当节点中元素数量等于n-1个时，下一个元素插入后，节点分裂，将节点中间的元素作为父节点。
B+Tree：所有的数据都会出现在叶子节点。叶子节点形成一个单向链表。
MySQL对b+tree进行了优化，在原b+tree的基础上，增加了一个指向相邻叶子节点的链表指针。所有叶子节点构成了一个双向循环链表。
Hash索引：采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。
InnoDB存储引擎使用B+tree原因：
- 相对于二叉树，层级更少，搜索效率高
- 对于b-tree，无论是叶子节点，还是非叶子节点，都会存储数据，这样导致一页(16k)中存储的键值减少，指针随之减少。
- hash索引，只支持等值匹配，不支持范围匹配（底层数据存储无序）
-索引分类
| 分类 | 含义 | 特点 | 关键字 |
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 避免同一个表中某数据列中的重复值 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 查找文本中的关键词 | 可以有多个 | FULLTEXT |
| 分类 | 含义 | 特点 |
| 聚集索引 | 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据 | 必须有，只有一个 |
| 二级索引 | 将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则：
- 存在主键，主键索引就是聚集索引
- 不存在主键，使用第一个唯一索引做聚集索引
- 都不符合，则InnoDB自动生成一个rowid作为隐藏的聚集索引
-索引语法
创建索引
CREATE [UNIQUE| FULLTEXT] INDEX index_name ON table_name(index_col_name,...);查看索引
SHOW INDEX FROM table_name;删除索引
DROP INDEX index_name ON table_name;-SQL性能分析
show [session|global] status;
#可以提供服务器状态信息。
#可以查询insert，update，delete,select的访问频次,com后是七个下划线
show global status like 'Com_______';
show global status like 'Com_insert';
#慢查询日志：记录了所有执行时间超过指定参数的所有sql语句的日志。
#配置文件：
#开启MySQL慢查询日志开关
slow_query_log=1
@设置慢查询日志的时间为2秒。
long_query_time=2#profile能够在做sql优化时帮助我们了解时间都耗费到哪里去了。
show profiles;
#查看受否支持profile
SELECT @@have_profiling;
#默认profiling是关闭的
set profiling =1;#explain执行计划，获取MySQL如何执行select语句的信息。
#在select语句前面加上EXPLAIN
EXPLAIN SELECT...;
/*
id
select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)
select type
表示SELECT的类型，常见的取值有SIMPLE (简单表，即不使用表连接或者子查询)、PRIMARY (主查询，即外层的查询)UNION (UNION 中的第二个或者后面的查询语句》、SUBOUERY (SELECT/WHERE之后包含了子查询)等
type
表示连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all
*/-索引使用规则
最左前缀法则
- 如果索引是联合索引，查询中最左边的列必须存在（与位置无关），否则不走索引；如果跳过了某个列，索引将部分失效。
索引失效
范围查询
- 联合索引，查询中出现范围查询（>, <）范围查询右侧的列索引失效;
- 所以业务允许的情况下，尽量使用 >= , <= ;\
索引列运算
- 不要在索引列上进行运算操作，否则索引失效。
- 字符串要加 ' ' ，不然存在隐式类型转换（ '666' 类似于这种）。
模糊查询
- 头部模糊匹配，索引失效。（字符串按首字母生成b+tree）
or连接的条件
- 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都失效。（or的左右两边，只要一边的字段没有索引，那两边都不会用索引）
数据分布影响
- 如果MySQL评估使用索引比全表扫描更慢，则不使用索引。
SQL提示
在sql语句中加入一些认为的提示来达到优化的目的
#use index 建议使用
select * from filed use index(index_name) where ...;
#ignore index 避免使用
select * from filed ignore index(idx_name) where ...;
#force index 强制使用
select * from filed force index(idx_name) where ...;覆盖索引&回表查询
尽量使用覆盖索引（查询中使用了索引，并且需要返回的列在该索引中能够全部找到）。减少使用select*，容易 回表查询。
前缀索引
当字段类型为字符串（varchar，text等）时，有时候需要所有很长的字符串。这时可以将字符串的一部分前缀，建立索引，节约索引空间，提高索引效率。
create index idx_xxx on table_name(column(n));
#column(n)
#n代表前缀长度，其可以根据索引的选择性来决定；
#选择性是指不重复的索引值和数据表的记录总数的比值；
#索引选择性越高，则查询效率越高。
select count(distinct xxx)/count(X) from table_name;
select count(distinct substring(xxx,1,5)/count(*)) from table_name;单列索引与联合索引
如果存在多个查询条件，考虑针对查询字段建立索引时，建议建立联合索引。可以避免回表查询。
-索引设计原则
- 针对于数据量较大，且查询比较频繁的表建立索引
- 针对于常作为查询条件 (where) 、排序 (order by) 、分组 (group by) 操作的字段建立索引,
- 尽量选择区分度高的列作为索引，尽量建立唯一素引，区分度越高，使用索引的效率越高
- 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引
- 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率
- 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
- 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询
SQL优化
插入优化
- 批量插入
- 手动提交事务
- 主键顺序插入
- 如果一次性需要大批量插入，使用insert语句插入性能较低，此时可以使用MySQL提供的load指令。
#客户端连接服务端时，加上参数 --local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
set global local_infile =1;
#执行load指令将准备好的数据，加载到表结构中
load data local infile '/root/sql1.log' into table 'tbName' filds terminated by ',' lines terminated by '/n/;
主键优化
- 满足业务需求的情况下，尽量降低主键的长度
- 插入数据时，尽量选择顺序插入，选择使用auto_increment自增主键（乱序插入可能会造成页分裂，页合并）
- 尽量不要使用uuid做主键或者其他自然主键，如身份证号
- 业务操作时，避免对主键的修改（不然主键的索引也要变化）
order by优化
using filesort: 通过表的索引或全表扫描，读取满足条件的行数据，然后在排序缓冲区sort buffe中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫filesort排序
using index:通过有序索引顺序扫描直接返回有序数据。不需要额外排序，操作效率高。
- 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则.
- 尽量使用覆盖索引
- 多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则 (ASC/DESC)
- 如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_bufer_size(默认256k)。
group by优化
- 通过索引来提高效率
- 索引的使用满足最左前缀法则
limit优化
limit 2000000，10 此时MySQL排序前200010条记录，仅仅返回最后十条记录。其他记录丢弃，查询排序代价非常大。
- 一般分页查询时，通过创建 覆盖索引 能够较好的提高性能，可以通过覆盖索引加子查询进行优化。
select * from tb_name as t, (select id from tb_name order by id limit 2000000,10) as a where t.id = a.id;count优化
select count(*) from tb_name;MyISAM引擎把一个表的总行数存在了磁盘上，因此执行count(*)的时候会直接返回这个数，效率很高
InnoDB引擎，它执行 count(*)的时候，需要把数据一行一行地从引擎里面读出来，然后累积计数
count (主键）
InnoDB 引擎会遍历整张表，把每一行的主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul)
count (字段)
没有not nul 约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为nul，不为null，计数累加、
有notnull约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
count (1)
InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加
count (*）
InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加
update优化
尽量根据主键/索引字段进行数据更新；
InnoDB的行锁 是针对索引加的锁，不是针对记录加的锁。 如果where 条件中的字段没有索引，则加的是表锁，并发度会降低。
视图
视图（View）是一种虚拟表。行和列数据来自基表，使用视图时动态生成的。
视图只保存了查询的sql逻辑，不保存查询结果。
#创建/修改 视图
create or replace view view_name[(列名列表)] as select语句 [with [cascaded |local] check option];
#查询创建视图语句
show create view view_name;
#查询视图
select * from view_name;
#删除
drop view if exists view_name;视图的检查选项
当使用WITH CHECK OPTION子句创建视图时，MySOL会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。MySOL允许基于另一个视图创建视图，它还会检查依赖视图中的规则以保持一致性。为了确定检查的范围，mvsgl提供了两个选项CASCADED和LOCAL，默认值为CASCADED；CASCADED：
v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 cascaded，但是v1视图创建时未指定检查选项。 则在执行检查时，不仅会检查v2，还会级联检查v2的关联视图v1。
LOCAL：
v2视图是基于v1视图的，如果在v2视图创建的时候指定了检查选项为 local ，但是v1视图创 建时未指定检查选项。 则在执行检查时，只会检查v2，不会检查v2的关联视图v1。
视图的更新
视图中的行与基础表中的行之间存在一对一关系，视图才可以更新。若进行了如下操作，则视图不可更新
- 聚合函数
- distinct
- group by
- having
- union/union all
视图作用
- 简单，经常使用的查询可以被定义为视图
- 安全，通过视图开发人员只能查询和修改他们有权限见到的数据
- 数据独立，基表字段名发生变化，视图起别名即可。
存储过程
存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。数据库SQL语言层面的代码封装与重用。
#创建
create procedure p_name([in/out/inout 参数名 参数类型])
begin
sql语句
end;
#调用
call p_name([参数]);
#查看
select * from information_schema.routines where routine_schema='数据库名';
show create procedure p_name;
#删除
drop procedure if exists p_name;变量
#系统变量是mysql服务器提供的，分为全局变量global和会话变量session。
#查看系统变量
show [session|global] variables;
select @@[session|global] 系统变量名;
#设置系统变量
set [session|globall] 系统变量名 = 值;
set @@[session|globall] 系统变量名 = 值;#用户自定义变量，不用提前声明，在使用时直接 @变量名。 作用域为当前连接；
#赋值
set @Var_name = expr;
select 字段名 into @var_name from 表名;
#使用
select @var_name; #局部变量,使用前需要先 declare定义。 在存储过程中定义的变量，作用域是当前存储过程。
#声明
declare 变量名 变量类型 [default 默认值];
#赋值
set 变量名 = 值;
select 字段名 into 变量名 from 表名;参数
| 类型 | 含义 | 备注 |
| IN | 调用时的传入值 | 默认 |
| OUT | 返回值 | |
| INOUT | 即可以是传入值，也可以是返回值 |
IF语句
if 条件1 then
...
elseif 条件2 then
...
else
...
end if;case
# 语法一
case case_value
when value1 then ...
when value2 then ...
else ...
end case;
# 语法二
case
when condition1 then ...
when condition2 then ...
else ...
end case;循环
#while
while condition do
...
end while;
#repeat,满足条件退出
repeat
...
until condition
end repeat;
#loop
#leave label; 退出指定标记的循环体
#iterate label; 直接进入下一次循环
[begin_label:]loop
...
end loop [end_label];
DELIMITER $$
CREATE PROCEDURE msum(IN endindex INT , OUT result INT )
BEGIN
SET result = 0;
sum1:LOOP
IF endindex <=0 THEN
LEAVE sum1;
END IF;
SET result = result + endindex;
SET endindex = endindex -1;
END LOOP sum1;
END$$
DELIMITER ;
CALL msum( 100,@result);
SELECT @result;游标cursor
游标是用来存储查询结果集的数据类型。
#声明游标
declare 游标名称 cursor for 查询语句;
#打开游标
open 游标名称;
#获取游标记录
fetch 游标名称 into 变量[,变量];
#关闭游标
close 游标名称;条件处理程序Handler
可以用来定义在流程控制结构执行过程中遇到问题时相应的处理步骤
declear handler_action handler for condition_value[,condition_value].. statement;
/*
handler_action
continue:继续
exit：终止
condition_value
sqlstate sqlstate_value:状态码
sqlwarning
not found
sqlexception
*/存储函数
存储函数是有返回值类型的存储过程，存储函数的参数只能是in类型。
必须有返回值
create function 函数名称(参数列表)
returns type [characteristic...]
begin
...
return ...;
end;
/*
characteristic说明
deterministic:相同的输入参数总是产生相同的结果
no sql:不包含sql语句
reads sql data:包含读取数据的语句，不包含写入数据的语句
*/触发器
触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的sql语句集合。
确保数据的完整性，日志记录，数据校验
只支持行级触发，不支持语句级触发
使用别名old和new来引用触发器中发生变化的记录内容
#创建
create trigger trigger_name
before/after insert/update/delete
on tb_name for each row --行级触发器
begin
trigger_stmt;
end;
#查看
show triggers;
#删除
drop trigger [schema_name.]trigger_name; --如果没有指定schema_name默认为当前数据库锁
概述
锁是计算机协调多个进程或线程并发访问某一资源的机制。
数据库中，数据也是共享的资源。
| 全局锁 | 锁定数据库中的所有表 |
| 表级锁 | 每次操作锁住整张表 |
| 行级锁 | 每次操作锁住对应的行数据 |
全局锁
对整个数据库实例加锁
使用场景：全库的逻辑备份，对所有表进行锁定，从而获取一致性视图，保证数据的完整性
表级锁
每次操作锁住整张表。
分类
- 表锁（读锁，写锁）
- 元数据锁(meta data lock)
- 意向锁
#表锁
lock tables t_name... read/write;
unlock tables;/*
mdl加锁过程是系统自动控制，无需显示使用，在访问一张表的时候会自动加上。
mdl锁主要作用是维护表元数据的数据一致性，在表上有活动事务的时候，不可以对元数据进行写入操作。
为了避免DML和DDL冲突，保证读写的正确性。
*/行级锁
InnoDB引擎
MySQL管理
未完待续...125/152/195
207
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
