MySQL学习总结
概述
- MySQL属于数据库的一种，他被称为关系型数据库
- MySQL数据是一种C/S模型，即客户端和服务端模型。客户端通过账号，密码连接服务器，连接成功后才可以进行数据操作（增删改查CRUD）
- MySQL的服务端采用的是 IO复用+可伸缩的线程池，实现了网络高并发的经典模型
常用名词
- 实体：现实世界中客观存在并可以被区别的事物，这里所说的“事物”不仅仅是看得见摸得着的“东西”，它也可以是虚拟的，例如“老师与学校的关系”
- 属性：“实体所具有的某一特性”，属性一开始是个逻辑概念，在关系数据库中，属性又是个物理概念，属性可以看作是“表的一列”
- 元组：表中的一行就是一个元组
- 分量：元组的某个属性值
- 码（键）：表中可以唯一确定一个元组的某个属性（或者属性组）
- 候选码（候选键）: 能唯一标识关系中元组的一个属性或者属性集，称为候选码（候选键）
- 主码（主键）：若一个关系中有多个候选码，从候选码中选择一个作为CRUD元组的操作变量，这个候选码称为主码（主键）
- 全码：如果一个码包含了所有的属性，这个码就是全码
- 主属性：包含在主码中的各个属性就是主属性
- 非主属性：没有在任何候选码中出现过，这个属性就是非主属性
- 外码（外键）：一个属性（或属性组），它不是当前表的主码，是另一个表的码，对当前表来说就是外码
数据库范式
- 范式：在关系模式的分解中，函数依赖有很重要的作用，分解这些依赖后，衡量的标准就是范式（NF）
- 好处：减少数据冗余、消除异常、让数据组织的更加和谐
- 作用：进行数据库设计时字段、库表划分的依据
- 分类
- 第一范式 1NF：数据库中的每一列都是不可分割的基本数据项，同一列中不能有多个值，即实体中的某个属性不能有多个值或者不能有重复的属性，如果出现重复的属性则需要重新构建实体，新的实体由重复的属性构成，不满足第一范式不能称之为关系型数据库
- 第二范式 2NF：数据库的每个实例或行必须可以被唯一的区分，即表中要有一列属性可以将实体完全区分，这个属性就是主键，即每一个属性完全依赖于主键
- 完全依赖概念：即非主属性不能依赖于主键的部分属性，必须依赖于主键的所有属性
- 第三范式 3NF：数据库表中不包含已在其他表中已包含的非主关键字信息
- 满足第三范式必须先满足第二范式，满足第二范式必须先满足第一范式
注释
- 单行注释 : – 注释内容(注释与符号后需要一个空格)
- 多行注释 : /* 注释内容 */
- MySQL注释 : #注释内容
分类
- DDL 数据定义语言 ------- 操作库与表
- DML 数据操作语言 ------- 数据增删改操作
- DQL 数据查询语言 ------- 数据查询
- DCL 数据控制语言 ------- 操作权限
- TCL 事务控制语句 ------- 操作事务
与非关系型数据库区别
- 存储方式不同
①关系型数据库:采用关系模型来管理、存储数据的数据库,以便于用户理解的行与列组成数据表
②非关系型数据库:解决大规模集合数据的多重数据种类,常以key-value形式存储 - 扩展性:关系<非关系
- 稳定性:关系>非关系
- 对大数据处理:非关系型数据库更好
DDL
操作库
- 创建数据库
- CREATE DATABASE [IF NOT EXISTS] 数据库名;
- 设置数据库编码集
- CREATE DATABASE 数据库名 CHARACTER SET 编码集;
- 修改数据库的字符集
- ALTER DATABASE 数据库名 CHARACTER SET 编码集;
- 删除数据库(操作前需先备份)
- DROP DATABASE [IF EXISTS] 数据库名;
- 显示所有数据库
- SHOW DATABASES;
- 显示指定数据库的详细信息
- SHOW CREATE DATABASE 数据库名;
- 使用指定数据库
- USE 数据库名;
- 查询正在使用的数据库
- SELECT DATABASE();
操作表
- 创建表
- CREATE TABLE 表名{
列名1 数据类型,
列名1 数据类型,
…
};
- CREATE TABLE 表名{
- 添加新的字段(列)
- ALTER TABLE 表名 ADD COLUMN 列名 数据类型;
- 修改表的编码集
- ALTER TABLE 表名 CHERACTER SET 编码集;
- 修改表名
- ALTER TABLE 表名 RENAME TO 新表名;
- 修改列名,数据类型
- ALTER TABLE 表名 CHANGE COLUMN 原列名 新列名 新类型;
- 修改数据类型
- ALTER TABLE 表名 MODIFY COLUMN 列名 新类型;
- 查询当前数据库所有表
- SHOW TABLES;
- 查询表结构
- DESC 表名;
- 查询表创建的具体语句
- SHOW CREATE TABLE 表名;
- 删除表
- DROP TABLE [IF EXISTS] 表名;
- 删除列
- ALTER TABLE 表名 DROP COLUMN 列名;
- 复制表结构
- CREATE TABLE 表名 LIKE 原表名;
- 复制表
- CREATE TABLE 表名 SELECT 查询列表 FROM 原表名 WHERE 筛选条件;
常见的数据类型
- 数值型
- 整形
- Tinyint 一个字节
- Int 四个字节
- 默认有符号,UNSIGNED设置无符号
- int(4) ZEROFILL 设置长度为4不足在前补0
- 浮点型
- float(M,D) 四个字节
- double(M,D) 八个字节
- M表示位数,D表小数占位数
- 定点型
- DECIMAL(M,D)/DEC(M,D) m+2个字节
- 默认(10,0)
- 整形
- 字符型
- char(M)
- M表最大字符数,默认为1,可省略
- 固定长度字符
- varchar(M)
- M表最大字符数,默认为1,不可省略
- 可变长字符
- text/blob
- 存储较长文本
- 扩展:LONGTEXT>MEDIUMTEXT>TEXT
- ENUM(A,B,…)
- 表只可存储特定值
- Set(A,B,…)
- 可存储特定值,一次可存储多个值
- char(M)
- 日期值
- date 四个字节,只保存日期
- time 三个字节,只保存时间
- year 一个字节,只保存年份
- datatime 八个字节,保存日期与时间,范围1000-9999
- timestamp 时间戳,四个字节,保存日期与时间,范围1970-2038,默认系统时间
常见约束
- PRIMARY KEY : 主键
- 用于保持该字段唯一且非空
- 联合主键:PRIMARY KEY(字段1,字段2…)
- FOREIGN KEY : 外键
- 用于限制两个表的关系,保证该字段必须来自主表的关联列的值
- 外键名定义格式: fk_主表名_从表名_字段名
- 级联操作:主从表同时操作
开启级联删除 ON DELETE CASCADE [SET NULL]
开启级联更新 ON UPDATE CASCADE
MySQL8开始支持SET NULL表示删除后值为null,若插入外键表的字段主键表中不存在则插入失败 - 注:删除表的时候必须先删除外键表,再删除主键表
- NOT NULL : 非空
- 用于保持该字段不能为空
- UNIQUE : 唯一
- 用于保持该字段唯一
- DEFAULT : 默认
- 用于保证该字段有默认值
- CHECK(检查值) : 检查
- MySQL8开始支持
- 格式:
①列名 类型 CHECK(列名 值限定)
②[CONSTRAINT 约束名] CHECK(列名 值限定)
- 分类
- 列级约束:
- 包含在列定义中,放在该列的其他定义之后,用空格分隔,不必指定列名,是对某个特定列的约束
- 支持主键,唯一,默认,非空,外键,检查
- 表级约束:
- 不包含在列定义中,与列定义相互独立,与定义用’,'分隔,通常用于对多个列进行约束
- 支持主键,唯一,外键,检查
- 列级约束:
- 添加
- 添加列级约束:ALTER TABLE 表名 MODIFY COLUMN 列名 类型 新约束;
- 添加表级约束:ALTER TABLE 表名 ADD [CONSTRAINT 约束名] 约束类型(字段名) [REFERENCES 主表(主表字段)//外键的引用];
- 删除列级约束
- 删除主键:ALTER TABLE 表名 DROP PRIMARY KEY;
- 删除唯一键:ALTER TABLE 表名 DROP INDEX 列名;
- 删除外键:ALTER TABLE 表名 DROP FOREIGN KEY(外键字段);
- 删除非空约束:
①ALTER TABLE 表名 MODIFY COLUMN 列名 类型;
②ALTER TABLE 表名 MODIFY COLUMN 列名 类型 NULL; - 删除默认约束:
①ALTER TABLE 表名 MODIFY COLUMN 列名 类型;
②ALTER TABLE 表名 ALTER COLUMN 列名 DROP DEFAULT;
-- 通用写法:
CREATE TABLE IF EXISTS 表名(
id INT PRIMARY KEY,
stuname VARCHAR(20) NOT NULL,
sex CHAR(1),
age INT DEFAULT 18,
seat INT UNIQUE,
majorid INT,
CONSTRAINT 外键名 FOREIGN KEY(从表字段) REFERENCES 主表(主表字段)
)
- 标识列(自增长列)
- 在字段后添加 AUTO_INCREMENT 关键字
- 与主键或唯一键搭配
- 只能有一列为标识列且为数值型
- SET AUTO_INCREMENT_INCREMENT 数值;//修改步幅
- 设置标识列
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 约束 AUTO_INCREMENT; - 删除标识列
ALTER TABLE 表名 MODIFY COLUMN 列名 新类型 约束;
DML
插入语句
- 方式一
- 向表中插入一条数据
- INSERT INTO 表名 VALUES (字段值1,字段值2,…);
- 向表中指定字段插入数据
- INSERT INTO 表名(字段名1,字段名2,…) VALUES (字段值1,字段值2,…);
- 向表中指定字段插入多条数据
- INSERT INTO 表名(字段名1,字段名2,…)
VALUES (字段值1,字段值2,…),(字段值1,字段值2,…),…;
- INSERT INTO 表名(字段名1,字段名2,…)
- 向表中插入一条数据
- 方式二
- 向表中插入一条数据
- INSERT INTO 表名 SET 列名1=字段值1,列名2=字段值2,…;
- 向表中插入一条数据
- 方式三
- 向表中插入多条数据(表二查出的字段顺序要与表一相同)
- INSERT INTO 表名(字段名1,字段名2,…) SELECT * FROM 表名2
- 向表中插入多条数据(表二查出的字段顺序要与表一相同)
修改语句
- 修改单表中指定列的值
- UPDATE 表名 SET 列名1=值,列名2=值,…;
- 根据条件修改单表中指定列的值
- UPDATE 表名 SET 列名1=值,列名2=值,… WHERE 条件;
- 修改多表中指定列的值
- sql92
- UPDATE 表1 别名,表2 别名
SET 列名1=值,列名2=值,…
WHERE 连接条件 AND 筛选条件;
- UPDATE 表1 别名,表2 别名
- sql99
- UPDATE 表1 别名
INNER| LEFT|RIGHT JOIN 表2 别名
ON 连接条件
SET 列名1=值,列名2=值,…
WHERE 筛选条件;
- UPDATE 表1 别名
- sql92
删除语句
- 删除表中所有数据(保留表结构)
- DELETE FROM 表名 [WHERE 条件];
- 清空表中数据
- TRUNCATE TABLE 表名;
- 删除多表
- sql92
- DELETE 别名1,别名2
FROM 表1 别名,表2 别名
WHERE 连接条件 AND 筛选条件;
- DELETE 别名1,别名2
- sql99
- DELETE 别名1,别名2
FROM 表1 别名
INNER| LEFT|RIGHT JOIN 表2 别名
ON 连接条件
WHERE 筛选条件;
- DELETE 别名1,别名2
- sql92
DQL
基础查询
- 查询指定表中所有字段和数据信息
- SELECT * FROM 表名;
- 查询指定字段的数据
- SELECT 字段1,字段2… FROM 表名;
- 查询指定字段的不重复数据
- SELECT DISTINCT 字段 FROM 表名;
条件查询
- 单个条件查询
- SELECT 字段 FROM 表名 WHERE 条件;
- 多个条件查询
- SELECT 字段 FROM 表名 WHERE 条件 逻辑运算符 条件;
- 补充:
①起别名两种方式:(别名中有特殊符号要给别名加双引号)
1.原列名 AS 别名
2.原列名 别名
②字段可为常量,表达式,函数(字段是关键字时使用~转换为普通字符[例: ~ YEAR ~ ])
③数据库中的运算符
1.算数运算符 :+、-、*、/(若操作数一方为字符型,则进行转换,不成功字符转换为0,若操作数一方为NULL,则结果必为NULL)
2.关系运算符:=、>、>=、<、<=、<> 不等于、!= 不等于
3.逻辑运算符:
①BETWEEN…AND…(两个相同字段的数值型条件且包含临界值)
②…AND…(两个及以上的任意字段)
③…OR…(不同字段)
④IN(X,Y…)或NOT IN(X,Y…) (相同字段):等于列表中的任意一个
⑤ANY(X,Y…)或SOME(X,Y…):比较列表中的某一个
⑥ALL(X,Y…):比较列表中的所有值
⑦… IS NULL或… IS NOT NULL(判断是否为null)
⑧NOT:表非
4.安全等于:<=>(既可以判断null又可以判断普通数值)
模糊查询
- 使用关键字LIKE
- SELECT * FROM 表名 WHERE 字段 LIKE ‘通配符’;
- 通配符
- _ : 代表单个字符
- % : 代表任意多个字符
- 通配符变普通字符
- 在通配符前加转义字符\
- 自定义转义字符 ESCAPE ‘字符’
排序查询
- 语法
- SELECT * FROM 表名 [WHERE 筛选条件]
ORDER BY 排序列表 [asc|desc]; - 默认升序asc 即从小到大排列
- SELECT * FROM 表名 [WHERE 筛选条件]
- ORDER BY子句可支持单个字段,多个字段,表达式,函数,别名
常见函数
- 字符函数
- 获取参数值的字节个数
- LENGTH(‘字符’)
- 拼接字符串
- CONCAT(参数一,参数二,…)
- 大小写变换
- UPPER(‘字符’) LOWER(‘字符’)
- 截取指定字符长度的字符串,sql索引从1开始
- SUBSTR(‘字符’,起始索引)
- SUBSTR(‘字符’,开始索引,结束索引)
- 获取子串第一次出现的索引,若无则返回0
- INSTR(‘字符’,‘子字符’)
- 去除字符前后指定字符
- TRIM(’ 字符 ') : 去前后空格
- TRIM(‘子字符’ FROM ‘字符’) : 去除子字符
- 用指定字符左填充指定长度
- LPAD(‘原字符’,返回字符的长度,‘指定字符’)
- 返回字符长度若小于原字符则截断
- 用指定字符右填充指定长度
- RPAD(‘原字符’,返回字符的长度,‘指定字符’)
- 返回字符长度若小于原字符则截断
- 指定字符替换
- REPLACE(‘原字符’,‘替换字符’,‘指定字符’)
- 获取参数值的字节个数
- 数学函数
- 四舍五入函数
- ROUND(数值型)
- ROUND(数值型,保留位数)
- 注:若(数值型+0.5),表示只入不舍,若(数值型-0.5),表示只舍不入
- 向上取整,获取>=该数值的最小整数
- CEIL(数值型)
- 向下取整,获取<=该数值的最大整数
- FLOOR(数值型)
- 截断
- TRUNCATE(小数,小数点后保留位数)
- 取余
- MOD(被除数,除数)
- 例MOD(a,b) : a-a/b*b
- 四舍五入函数
- 日期函数
- 获取当前系统日期与时间
- NEW()/SYSDATE()
- 获取当前系统日期
- CURDATE()
- 获取当前系统时间
- CURTIME()
- 获取年份,月份,日期等
- YEAR(‘日期字段’)
- MONTH(‘日期字段’)
- DAY(‘日期字段’)
- HOUR(‘日期字段’)
- MINUTE(‘日期字段’)
- SECOND(‘日期字段’)
- MONTHNAME(‘日期字段’)月份英文名
- 增加时间值
- DATE_ADD(‘日期字段’,INTERVAL 数值型 TYPE)
- 数值型为正整数表示增加时间,负整数表示减少时间,小数会自动转为整数
- TYPE为YEAR等日期关键字
- 将日期格式的字符串转换成日期时间类型
- STR_TO_DATE(‘str’,‘format’)
- 格式说明 : %Y 四位的年份、%y 两位的年份、%m 补0的月份、%c 不补0月份、%d 日期、%H 24小时制、%h 12小时制、%i 分钟、%s 秒
- 将日期类型转换成指定格式的日期时间类型
- DATE_FORMAT(‘date’,‘format’)
- 获取当前系统日期与时间
- 其他函数
- 版本号 : VERSION()
- 当前数据库 : DATABASE()
- 当前用户 : USER()
- 替换null值 : IFNULL(字段,替换值)
- 分组函数
- SUM()求和、AVG()平均值、MAX()最大值、MIN()最小值、COUNT()总数
- SUM与AVG一般用于处理数值型
- MAX MIN COUNT可处理任何类型
- 以上分组函数都忽略null值
- 可以与DISTINCT搭配去重后计算
- 解决忽略null值:①使用*号等特殊符号代替指定字段、②使用IFNULL()处理、③使用常量值代替指定字段(相当于在每行加一个常量在统计常量个数)
- 与分组查询搭配使用才有意义
分组查询
- 语法
- SELECT 列(要求出现在group by后),分组函数
FROM 表
[WHERE 分组前筛选条件]
GROUP BY 分组的列
[HAVING 分组后筛选条件]
- SELECT 列(要求出现在group by后),分组函数
- 特点
- 以分组函数做条件肯定放在having子句中
- 能用分组前筛选的,就优先考虑分组
- GROUP BY子句可支持单个字段,多个字段,表达式,函数,别名
连接查询
- 等值连接(sql92标准)
- SELECT 查询列表
FROM 主表名,复表名
WHERE 主表列名 = 复表列名
[AND 其他连接条件或筛选条件]
[其他子句] - 一般需为表名起别名,后查询的字段就不能使用原来的表名
- 连接结果为多表的交集部分
- 多表顺序无关
- SELECT 查询列表
- 非等值连接(sql92标准)
- SELECT 查询列表
FROM 主表名,复表名
WHERE 主表列名 非等于号的其他关系或逻辑运算符 复表列名
[AND 其他连接条件或筛选条件]
[其他子句]
- SELECT 查询列表
- 自连接(sql92标准)
- SELECT 查询列表
FROM 表名
WHERE 表列名 = 表列名
[AND 其他连接条件或筛选条件]
[其他子句]
- SELECT 查询列表
- 自然连接(sql99标准)
- 一种特殊的等值连接,两个表中相同的列(名,含义,类型)进行等值匹配,并且在结果中消除重复的属性列
- SELECT 查询列表
FROM 表1名
NATURAL JOIN 表1名
- 内连接(sql99标准)
- SELECT 查询列表
FROM 表1名 别名
INNER JOIN 表2名 别名 ON 连接条件(可多个)
[WHERE 筛选条件]
[其他子句] - INNER 可省略
- 连接条件中使用’主表列名 = 复表列名’则与等值连接(sql92标准)效果一致
- 连接条件中使用非等于号的其他关系或逻辑运算符则与非等值连接(sql92标准)效果一致
- 连接条件中使用’表列名 = 表列名’则与自连接(sql92标准)效果一致
- SELECT 查询列表
- 外连接(sql99标准)
- 特点 : 连接结果为主表的所有记录
- 左外连接 : LEFT [OUTER] JOIN(左边的表为主表)
- 右外连接 : RIGHT [OUTER] JOIN(右边的表为主表)
- 全外连接 : FULL [OUTER] JOIN(左右表都为主表,MySQL目前不支持)
- 交叉连接(sql99标准)
- CROSS JOIN(笛卡尔乘积)
- 相当于SELECT 查询列表 FROM 表名1,…
子查询(查询嵌套)
- 特点:①子查询放在小括号里
②子查询一般放在条件的右侧
③标量子查询一般搭配条件运算符,列子查询一般搭配逻辑运算符
④优先主查询执行 - SELECT后放
- 标量子查询(结果集只有一行一列)
- FROM后放
- 表子查询(结果集一般为多行多列)
- WHERE或HAVING后放
- 标量子查询(结果集只有一行一列)
- 列子查询(结果集只有一列多行)
- 行子查询(结果集有多列)
- EXISTS后(相关子查询)
- 表子查询(结果集一般为多行多列)
- 返回结果0或1
- 可与IN替换
分页查询
- 前提:数据过多,分页提交sql请求
- 语法:
SELECT 查询列表
FROM 表
[JOIN 表2名 别名 ON 连接条件
WHERE 筛选条件
GROUP BY 分组字段
HAVING 分组后筛选条件
ORDER BY 排序字段
LIMIT [起始索引,]条目个数]; - LIMIT放在查询语句最后,只MySQL单独支持,执行顺序FROM→JOIN→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT
- 公式 : 起始索引=(page-1)*条数
联合查询
- 使用union关键字将多个查询语句的结果合并成一个结果
- 语法:查询语句1 UNION 查询语句2 UNION …
- 特点:多条查询语句的列数一致
每列的类型和顺序保持一致
默认去重(UNION ALL不去重)
DCL数据控制语言
- 查看当前用户权限
- SELECT * FROM user;
- 创建用户
- CREATE USER ‘用户名’@‘主机名’ IDENTIFIED BY ‘密码’;
- 删除用户
- DROP USER ‘用户名’@‘主机名’;
- 修改密码
- UPDATE USER SET PASSWORD = PASSWORD(‘新密码’) WHERE USER = ‘用户名’;//普通方式
- SET PASSWORD FOR ‘用户名’@‘主机名’ = PASSWORD(‘新密码’);//特有的简化方式
- 查询权限
- SHOW GRANTS FOR ‘用户名’@‘主机名’;
- 授予权限
- GRANT 权限列表 ON 数据库名.表名 TO ‘用户名’@‘主机名’;
- GRANT ALL ON . TO ‘用户名’@‘主机名’; //使用通配符给用户授予全部权限
- 撤销权限
- REVOKE 权限列表 ON 数据库名.表名 FROM ‘用户名’@‘主机号’;
- GRANT ALL ON . FROM ‘用户名’@‘主机号’;//撤销用户对数据库所有权限
TCL事务控制语言
- 事务特性ACID: 原子性,一致性,隔离性,持久性
- 事务使用步骤:
- 1.开启事务SET autocommit = 0;(MySQL5开启事务START TRANSCATION)
2.编写一组事务语句(设置保存点savepoint 名;)
3.提交事务commit;(回滚事务rollback 名;回滚到保存点)
- 1.开启事务SET autocommit = 0;(MySQL5开启事务START TRANSCATION)
- 并发运行多个事务问题
- 脏读: T1读取了已经被T2更新但还没有被提交的字段,若T2回滚,T1读取的内容就是临时且无效的
- 不可重复读: T1读取了一个字段,然后T2更新了该字段,T1再次读取该字段,值不相同了
- 幻读: T1读取了一个字段,T2在表中插入或删除一行,T1再次读取同一个表,就多出或少了几行
- 事务隔离级别:
- READ UNCOMMITTED(读未提交数据),
READ COMMITED(读已提交数据)—避免脏读,
REPEATABLE READ(可重复读)默认—避免脏读 不可重复读,
SERIALIZABLE(串行化)—避免脏读 不可重复读 幻读 性能较差
- READ UNCOMMITTED(读未提交数据),
- 查看当前隔离级别: SELECT @@tx_isolation;
- 设置当前连接隔离级别: set transaction isolation level 级别;
- 设置全局的隔离级别: set global transaction isolation level 级别;
视图
- 含义:虚拟表,是通过表动态生成的数据
- 优点:重用sql语句,简化复杂的sql操作,保护数据提高安全性
- 创建:
- CREATE VIEW 视图名 AS 查询语句
- 修改:
- ①CREATE OR REPLACE VIEW 视图名 AS 查询语句
②ALTER VIEW 视图名 AS 查询语句
- ①CREATE OR REPLACE VIEW 视图名 AS 查询语句
- 删除:
- DROP VIEW 视图名,…;
- 查看:
- ①DESC 视图名;
②SHOW CREATE VIEW 视图名;
- ①DESC 视图名;
- 查询:
- SELECT * FROM 视图名;
- 视图不建议更新(增删改)
- 包含以下关键字不允许更新:分组函数,distinct,group by,having,union,union all
- 增删改语句与操作表相同
索引
- 分类
- 主键索引(PRIMARY KEY),唯一索引(UNIQUE),普通索引(缺省),组合索引,全文索引(FULLTEXT),空间索引(SPATIAL)
- 创建
- CREATE [索引类型] INDEX 索引名 ON 表名(字段1,字段2…);
- 索引名:idx_字段名_索引类型
- 删除
- DROP INDEX 索引名 ON 表名;
- 显示索引信息
- EXPLAIN 查询语句;
- 查询结果id(查询编号),select_type(查询类型),table(查询表名),type(扫描类型),possible_keys(可能使用的索引),key(真正使用的索引),key_len(索引长度),ref(参考类型),rows(扫描影响的行数),extra(额外的)
- 查看表的索引
- SHOW INDEX FROM 表名;
- 为表添加索引
- ALTER TABLE 表名 ADD INDEX 索引名(索引长度);
- 为表删除索引
- ALTER TABLE 表名 DROP INDEX 索引名;
- 创建表时添加索引
- [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY 索引名(字段名[(长度)]);
- 索引在创建前后使用相同查询语句速度会明显提升,如果一张表小于32列可根据实际创建索引列,若大于等于32列则最大为32列建立索引
分区
-
分库:将一个数据库分割成多个子数据库
-
分表:水平方向(横向)①根据安全级别分离表②根据字段类型分离表
垂直方向(纵向):根据指定的字段或内容分离 -
RANGE分区:按照分区关键字的范围进行分区
- 格式:
PARTITION BY RANGE [COLUMNS] (字段)(
PARTITION 分区名 VALUES LESS THAN(值),//小于等于
…//MAXVALUE最大值
); - 补充:使用COLUMNS对分区关键字限定,要求分区关键字必须是表中完整的列,当插入不符合分区要求的数据时插入失败
从所有分区删除所有的数据使用TRUNCATE TABLE命令
改变表的分区而又不丢失数据使用ALTER TABLE … REORGANIZE PARTITION语句
- 格式:
-
LIST分区:按照列表数据项进行分区
- 格式:PARTITION BY LIST(字段)(
PARTITION 分区名 VALUES IN (列表参数),
…
);
- 格式:PARTITION BY LIST(字段)(
-
HASH分区:按照分区字段对分区个数取余进行分区
- 格式:PARTITION BY HASH (字段) PARTITIONS 个数;
-
KEY分区:对HASH分区的优化
- 格式:PARTITION BY LINEAR KEY (字段) PARTITIONS 个数;
-
分区管理
- RANGE分区管理
- 增加分区:ALTER TABLE 表名 ADD PARTITION (PARTITION 分区名 VALUES LESS THAN (值));
- 删除分区:ALTER TABLE 表名 DROP PARTITION 分区名;//删除分区会将该分区中的数据同时删除
- 重新分区:ALTER TABLE 表名 REORGANIZE PARTITION 分区名1[,…] INTO (
PARTITION 分区名 VALUES LESS THAN (值),
…
);
- LIST分区管理
- 增加分区:ALTER TABLE 表名 ADD PARTITION (PARTITION 分区名 VALUES IN (列表参数));
- 删除分区:ALTER TABLE 表名 DROP PARTITION 分区名;//删除分区会将该分区中的数据同时删除
- 重新分区:ALTER TABLE 表名 REORGANIZE PARTITION 分区名1[,…] INTO (
PARTITION 分区名 VALUES IN (列表参数),
…
);
- HASH分区与KEY分区管理
- 扩充:在原有值的基础上加个数
ALTER TABLE 表名 ADD PARTITION PARTITIONS 个数; - 缩减:在原有值的基础上减个数
ALTER TABLE 表名 COALESCE PARTITION 个数;
- 扩充:在原有值的基础上加个数
- 其他分区管理
- 重建分区:ALTER TABLE 表名 REBUILD PARTITION (重建分区名1,重建分区名2…);
- 优化分区:ALTER TABLE 表名 OPTIMIZE PARTITION (优化分区名1,优化分区名2…);
- 分析分区:ALTER TABLE 表名 ANALYZE PARTITION (分区名);
- 维护分区:ALTER TABLE 表名 REPAIR PARTITION (维护分区名1,维护分区名2…);
- 检查分区:ALTER TABLE 表名 CHECK PARTITION (分区名);
- RANGE分区管理
-
显示每个分区数据量
- SELECT PARTITION_NAME,TABLE_ROWS
FROM information_schema.PARTITION
WHERE TABLE_NAME = ‘表名’;
- SELECT PARTITION_NAME,TABLE_ROWS
存储过程与函数
- 变量
- 系统变量
1.由系统提供,不是用户定义,分为全局变量(GLOBAL)和会话变量(SESSION默认),对会话变量操作只对当前会话有效
2.查看系统变量:
SHOW [GLOBAL|SESSION] VARIABLES [LIKE ‘%char%’];
3.查看指定的某个系统变量的值:
SELECT @@[GLOBAL/SESSION].系统变量名;
4.为某个系统变量赋值:
SET [GLOBAL/SESSION] 系统变量名 = 值;
SET @@[GLOBAL/SESSION].系统变量名 = 值; - 自定义变量
由用户自定义,分为用户变量和局部变量- 用户变量(只对当前会话有效)
- 声明并初始化
SET @用户变量名=值;
[SET|SELECT] @用户变量名:=值; - 赋值
SET @用户变量名=值;
[SET|SELECT] @用户变量名:=值;
SELECT 字段 INTO 用户变量名 FROM 表; - 查看
SELECT @用户变量名;
- 局部变量(begin end中有效)
- 声明并初始化
DECLARE 变量名 类型 DEFAULT 值; - 赋值
SET 局部变量名[=|:=]值;
SELECT @局部变量名:=值;
SELECT 字段 INTO 局部变量名 FROM 表; - 查看
SELECT @用户变量名;
- 系统变量
- 存储过程
含义:一组预先编译好的SQL语句的集合- 创建
- CREATE PROCEDURE 存储过程名(参数列表)
BEGIN
存储过程体
END - 参数列表定义(参数模式 参数名 参数类型)
参数模式:
IN:该参数需调用方传值(默认)
OUT:该参数可以作为返回值
INOUT:该参数既需要传入值,又可以作为返回值 - 存储过程体只有一句话时BEGIN END可省略,每条SQL语句结尾必须加分号,可用DELIMITER(语法:DELIMITER 结尾标记)重新设置存储过程的结尾标记来结束存储过程,MySQL8缺陷见到;就结束
- CREATE PROCEDURE 存储过程名(参数列表)
- 删除
DROP PROCEDURE 存储过程名;(不支持多个同时删除) - 查看
SHOW CREATE PROCEDURE 存储过程名; - 调用
CALL 存储过程名(实参列表);
- 创建
- 函数
特点:有且只有一个返回值,适合做处理数据后返回一个结果的操作,而存储过程适合做批量操作- 创建
- CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
BEGIN
函数体
END - 参数列表:参数名 参数类型
- 要有return语句(return 值;)
- 函数体只有一句话BEGIN END可省略,每条SQL语句结尾必须加分号,可用DELIMITER(语法:DELIMITER 结尾标记)语句设置结束标记
- CREATE FUNCTION 函数名(参数列表) RETURNS 返回类型
- 删除
DROP FUNCTION 函数名;(不支持多个同时删除) - 查看
SHOW CREATE FUNCTION 函数名; - 调用
SELECT 函数名(参数列表) 结尾标记
- 创建
触发器
- 概述:触发器由一个事件来启动运行,即当某个事件发生时自动的隐式运行,触发器不能接收函数
- 一张表最多有六个触发器
INSERT BEFORE/AFTER
UPDATE BEFORE/AFTER
DELETE BEFORE/AFTER - DML触发器格式:
CREATE [OR REPLACE] TRIGGER 触发器名
BEFORE|AFTER //事件前触发还是事件后触发
INSERT|DELETE|UPDATE [OF column [,column…]] //什么事件触发
ON 表名 //作用在哪个表
[FOR EACH ROW] //触发器为行级触发器默认为语句级
[WHEN 触发条件]
BEGIN
触发体;
END
流程控制结构
- 分支结构
- IF( 条件 , 成功执行 , 失败执行)
- IF 条件1 THEN 语句1;
ELSEIF 条件2 THEN 语句2;
…
END IF;
注:应用在begin end中 - 类似于JAVA中switch语句
CASE 变量|字段|表达式
WHEN 常量1 THEN 要显示的值或[语句;]
WHEN 常量2 THEN 要显示的值或[语句;]
…
ELSE 要显示的值或[语句;]
END [CASE];
注:若ELSE省略WHEN条件都不满足,则返回null - 类似于JAVA中多重if语句
CASE
WHEN 条件1 THEN 要显示的值或[语句;]
WHEN 条件2 THEN 要显示的值或[语句;]
…
ELSE 要显示的值或[语句;]
END [CASE];
注:若ELSE省略WHEN条件都不满足,则返回null
- 循环结构(应用在begin end中)
- 先判断后执行(类似于JAVA中while循环语句)
[别名:]WHILE 循环条件
DO 循环体
END WHILE [别名]; - 先执行后判断(类似于JAVA中do…while循环语句)
[别名:]REPEAT 循环体
UNTIL 结束条件
END REPEAT [别名]; - 没有条件的死循环(类似于JAVA中for循环语句)
[别名:]LOOP
循环体
END LOOP [别名]; - 循环控制
ITERATE (类似于JAVA中continue)
LEAVE (类似于JAVA中break)
- 先判断后执行(类似于JAVA中while循环语句)
215
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
