MySQL 学习笔记1-基础篇

2023-09-25 19 0

目录

      • 1,关于 SQL
        • 1.1,SQL 的两个标准
        • 1.2,SQL 命名规范
        • 1.3,SQL 语句大小写问题
      • 2,一条 SQL 的执行步骤
      • 3,MySQL 存储引擎
      • 4,数据库基本操作
        • 4.1,创建与删除数据库
        • 4.2,创建表结构
        • 4.3,修改表结构
      • 5,关于自增主键
        • 5.1,查看自增值
        • 5.2,自增值是如何保存的
        • 5.3,自增值的生成算法
        • 5.4,自增值不能保证是连续的
        • 5.5,自增 id 可能被用完
      • 6,SELECT 语句顺序
      • 7,WHERE 子句
        • 7.1,比较运算符
        • 7.2,逻辑运算符
        • 7.3,like 语句
      • 8,DISTINCT 去重
      • 9,关于 COUNT(*) 操作
      • 10,MYSQL 函数
      • 11,GROUP BY 数据分组
      • 12,子查询(嵌套查询)
      • 13,JOIN 连接查询
      • 14,VIEW 视图
      • 15,存储过程
      • 16,临时表
      • 17,MySQL 权限管理
        • 17.1,全局权限
        • 17.2,库级权限
        • 17.3,表权限和列权限
      • 18,Python 操作 MySQL 的库

这 5 篇文章是我在学习 MySQL 的过程中,总结的笔记:

  • 第一篇 MySQL 学习笔记1-基础篇
    • 1,关于 SQL
    • 2,一条 SQL 的执行步骤
    • 3,MySQL 存储引擎
    • 4,数据库的基本操作
    • 5,关于自增主键
    • 6,SELECT 语句顺序
    • 7,WHERE 子句
    • 8,DISTINCT 去重
    • 9,关于 COUNT(*) 操作
    • 10,MYSQL 函数
    • 11,GROUP BY 数据分组
    • 12,子查询(嵌套查询)
    • 13,JOIN 连接查询
    • 14,VIEW 视图
    • 15,存储过程
    • 16,临时表
    • 17,MySQL 权限管理
    • 18,Python 操作 MySQL 的库
  • 第二篇 MySQL 学习笔记2-进阶篇-上
    • 19,MySQL 的基础架构
    • 20,数据库缓冲池
    • 21,数据库中的存储结构
    • 22,InnoDB 中表数据的存储
  • 第三篇 MySQL 学习笔记3-进阶篇-中
    • 23,事务处理
    • 24,事务的隔离级别
    • 25,MySQL 中的锁
    • 26,MVCC 多版本并发控制
    • 27,MySQL 传输数据的原理
  • 第四篇 MySQL 学习笔记4-进阶篇-下
    • 28,Join 语句的原理
    • 29,MySQL 如何进行排序
    • 30,MySQL 中 kill 命令的原理
    • 31,MySQL 中的 mysqldump 命令
    • 32,MySQL 主从同步
    • 33,MySQL 主备原理
  • 第五篇 MySQL 学习笔记5-调优篇
    • 34,关于 MySQL 索引
    • 35,定位数据库 SQL 性能问题

1,关于 SQL

1.1,SQL 的两个标准

SQL 有两个重要的标准,分别是 SQL92SQL99,它们分别代表了 92 年和 99 年颁布的 SQL 标准,今天的 SQL 语言依然遵循这些标准。

1.2,SQL 命名规范

表名、表别名、字段名、字段别名等都小写;SQL 保留字、函数名、绑定变量等都大写

比如:

SELECT name, hp_max FROM heros WHERE role_main = '战士';
1.3,SQL 语句大小写问题

比如:

SELECT * FROM heros WHERE name = 'guanyu';
SELECT * FROM heros WHERE name = 'GUANYU';

上面两个语句,在 Oracle 中是不同的查询,而在 MySQL 中是相同的查询。

同时,可以通过修改系统参数来配置,比如在 MySQL 中可以通过参数 lower_case_table_names 来配置数据库和数据表的大小写敏感性。

2,一条 SQL 的执行步骤

一条 SQL 语句在数据库中的执行步骤如下:

在这里插入图片描述

注意在 MySQL 8.0 之后,查询缓存功能被取消。

3,MySQL 存储引擎

MySQL 的存储引擎是插件式的,在使用时可以选择不同的存储引擎。

引擎名 特点 其它
InnoDB 支持事务,表锁,行锁,外键约束等 MySQL 5.5 之后的默认引擎
MyISAM 不支持事务,不支持行锁,不支持外键等,速度快,占用资源少 MySQL 5.5 之前的默认引擎
Memory 不支持行锁,只支持表锁;数据存储在内存中, 速度快,数据不能持久化 -
NDB 主要用于 MySQL Cluster 分布式集群 -
Archive 有很好的压缩机制,可用于用于文件归档 -

在 MySQL 中查看可用的存储引擎:

show engines;

结果如下:

在这里插入图片描述

InnoDB 与 Memory 的区别:

  • InnoDB 表的数据是有序存放的,Memory 表的数据是按照写入顺序存放的
  • 当数据文件有空洞的时候,InnoDB 表在插入新数据的时候,为了保证数据有序性,只能在固定的位置写入新值,而 Memory 表找到空位就可以插入新值
  • 数据位置发生变化的时候,InnoDB 表只需要修改主键索引,而 Memory 表需要修改所有索引
  • InnoDB 支持变长数据类型,不同记录的长度可能不同;内存表不支持 Blob 和 Text 字段,并且即使定义了 varchar(N),实际也当作 char(N),因此 Memory 表的每行数据长度相同

4,数据库基本操作

4.1,创建与删除数据库
CREATE DATABASE nba; -- 创建一个名为 nba 的数据库
DROP DATABASE nba;   -- 删除一个名为 nba 的数据库
4.2,创建表结构

MySQL 官方文档 建议我们尽量将数据表的字段设置为 NOT NULL 约束,这样做的好处是可以更好地使用索引,节省空间,甚至加速 SQL 的运行。

创建表的语法如下:

CREATE TABLE [table_name](字段名 数据类型,......)

注意:如果创建的表没有主键,或者把一个表的主键删掉了,那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 来作为主键。

示例:

CREATE TABLE player  (player_id int(11) NOT NULL AUTO_INCREMENT,player_name varchar(255) NOT NULL
);

其中:

  • NOT NULL PRIMARY KEY AUTO_INCREMENT 表示自增主键,NOT NULL AUTO_INCREMENT 只表示自增而非主键。
    • 自增主键,在插入新记录的时候可以不指定 ID 的值,系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。
  • int(11) 代表整数类型,显示长度为 11 位,括号中的参数 11 代表的是最大有效显示长度,与类型包含的数值范围大小无关。
    • int 默认表示有符号整数,长度未定义的话,默认是 11;
    • 无符号整数表示方法:int unsighed,长度未定义的话,默认是 10。
    • 如果插入的数值大小超出了所能表示的范围,将默认插入一个临界值。
  • varchar(255) 代表的是最大长度为 255 个字符数的可变长字符串。
    • 注意单位是字符数,一个汉字与一个英文字母都是一个字符。
    • 对于 varchar(M),M 的范围是 0 ~ 25535
    • 在定义时,M 不可省略。
    • 如果超出了长度,超出的部分会被截断。
  • char(M):表示固定长字符串,M 的范围是 0 ~ 255
    • 在定义时,M 可省略,默认为 1。
    • 如果超出了长度,超出的部分会被截断。
    • 固定长度的意思是,不管实际存储的数据是多大,都会占用固定的空间;容易造成空间浪费,但是检索性能比可变长的要好一些
    • 可变长度的意思是,会根据实际的数据的大小,去占用相应的空间,不会造成空间浪费。
  • float(M, N):单精度浮点数
    • N 表示小数位的位数,小数位如果超出N,则四舍五入;如果不够N 则用 0 补齐。
    • M 表示整数位的位数与小数位的位数之和,如果超出 M,则存储一个边界值。
    • M 和 N 都可省略,表示不限制范围。
  • double(M, N):双精度浮点数
  • DATETIME:占 8 字节
    • 表示的时间范围 1000-01-01 00:00:00/9999-12-31 23:59:59
  • TIMESTAMP:占 4 字节
    • 表示的时间范围 1970-01-01 00:00:00/2038
  • DECIMAL:常用于表示金额
  • CHECK 约束:用来检查特定字段取值范围的有效性CHECK 约束的结果不能为 FALSE
    • 比如可以对身高 height 的数值进行 CHECK 约束,必须≥0,且<3
    • CHECK(height>=0 AND height<3)

对于 int 类型的显示长度的使用,要搭配 zerofill 关键字来使用,如果不与 zerofill 一起使用,其实 int(n) 中的 n,并没有实际意义,n 是几都一样(如果数值不够 n 位,也不会用 0 补齐)。

zerofill 的使用方法:

create table t1 {t1 int(7) zerofill,t2 int(7) zerofill
};

此时如果插入的数值,不够 7 位数,则前边用 0 补齐;比如插入了 123,则显示如下:

在这里插入图片描述

从 Navicat 中导出的 SQL 语句,示例:

DROP TABLE IF EXISTS `player`;
CREATE TABLE `player`  (`player_id` int(11) NOT NULL AUTO_INCREMENT,`team_id` int(11) NOT NULL,`player_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`height` float(3, 2) NULL DEFAULT 0.00,PRIMARY KEY (`player_id`) USING BTREE,UNIQUE INDEX `player_name`(`player_name`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

其中:

  • SET = utf8 代表字符编码
  • COLLATE = utf8_general_ci 代表排序规则
    • utf8_general_ci 代表对大小写不敏感
    • 如果设置为 utf8_bin,代表对大小写敏感
    • 还有许多其他排序规则
  • PRIMARY KEY 代表主键
    • 主键起的作用是唯一标识一条记录,不能重复,不能为空,即 UNIQUE+NOT NULL
    • 一个数据表的主键只能有一个
    • 主键可以是一个字段,也可以由多个字段复合组成
  • USING BTREE 表示底层使用 BTREE 作为索引结构
    • 其它的索引结构还有 HASH
  • UNIQUE INDEX 代表唯一索引,可以设置为其他索引方式,比如 NORMAL INDEX(普通索引)。
    • 唯一索引普通索引的区别在于,它对字段进行了唯一性的约束,索引方式使用了 BTREE

其它建表方式

-- A 的结构是 B 的精简版,A 中没有索引等信息
-- A 中的数据与 B 中的数据完全相同 
create table A as select * from B;-- A 的结构与 B 完全相同,包括索引等其它信息
-- A 是空表,没有任何数据
create table A like B;
-- 可以使用下面语句将 B 中的数据插入 A
insert into A select * from B;
4.3,修改表结构

添加字段,在 player 表中添加 age 字段:

ALTER TABLE player ADD (age int(11));

修改字段名,将 player 表中的 age 字段名改为 player_age:

ALTER TABLE player RENAME COLUMN age to player_age

修改字段数据类型,将 player 表中的 player_age 字段的数据类型改为 float(3,1)

ALTER TABLE player MODIFY (player_age float(3,1));

删除字段,将 player 表中的 player_age 字段删除:

ALTER TABLE player DROP COLUMN player_age;

5,关于自增主键

在 MySQL 里面,如果字段被定义为 AUTO_INCREMENT,则表示该字段自增。

5.1,查看自增值

命令 show create table table_name 可以看到下一个可用的自增值,如下:

在这里插入图片描述

5.2,自增值是如何保存的

不同的引擎对于自增值的保存策略不同:

  • MyISAM 引擎的自增值保存在数据文件中
  • InnoDB 引擎的自增值:
    • 在 MySQL 5.7 及之前的版本,自增值保存在内存里,并没有持久化。每次重启后,第一次打开表的时候,都会去找自增值的最大值 max(id),然后将 max(id)+1 作为这个表当前的自增值
    • 在 MySQL 8.0 版本,将自增值的变更记录在了 redo log 中,重启的时候依靠 redo log 恢复重启之前的值
5.3,自增值的生成算法

自增值的特性如下:

  • 如果插入数据时 id 字段指定为 0NULL未指定值,那么就把这个表当前的 AUTO_INCREMENT 值填到自增字段
  • 如果插入数据时 id 字段指定了具体的值,就直接使用语句里指定的值。假设,某次要插入的值是 X,当前的自增值是 Y:
    • 如果 X<Y,那么这个表的自增值不变;
    • 如果 X≥Y,就需要把当前自增值修改为新的自增值。

新的自增值生成算法是:以参数 auto_increment_offset 为初始值,以 auto_increment_increment 为步长,持续叠加,直到找到第一个大于 X 的值,作为新的自增值。

这两个参数的默认值都是 1:

  • 当准备插入的值 >= 当前自增值,新的自增值就是“准备插入的值 +1”
  • 否则,自增值不变

双 M 的主备架构里要求双写的时候,会设置成 auto_increment_increment=2,让一个库的自增 id 都是奇数,另一个库的自增 id 都是偶数,避免两个库生成的主键发生冲突

5.4,自增值不能保证是连续的

在这两个参数都设置为 1 的时候,自增主键 id 不能保证是连续的,有以下情况:

  • 唯一键冲突是导致自增主键 id 不连续的第一种原因
  • 事务回滚也会产生类似的现象,这就是第二种原因
5.5,自增 id 可能被用完

MySQL 中无符号整型 (unsigned int) 是 4 个字节,上限就是 2^32-1

表定义的自增值达到上限后的逻辑是:再申请下一个 id 时,得到的值保持不变。这就导致,当自增 id 达到上限后,如果再插入新的值,会导致自增id 重复的错误,因此就无法再插入新的值

因此,在建表的时候,你需要考察你的表是否有可能达到这个上限,如果有可能,就应该创建成 8 个字节的 bigint unsigned

InnoDB 系统自增 row_id

如果创建的 InnoDB 表没有指定主键,那么 InnoDB 会创建一个不可见的,长度为 6 个字节的 row_id。

row_id 是从 0 开始到 2^48-1。达到上限后,下一个值就是 0,然后继续循环。这就会导致,当达到上限后,新的数据会覆盖旧的数据

6,SELECT 语句顺序

关键字顺序不能颠倒:

SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY ...

执行顺序:

FROM > WHERE > GROUP BY > HAVING > SELECT 的字段 > DISTINCT > ORDER BY > LIMIT

7,WHERE 子句

7.1,比较运算符
比较运算符 含义
= 等于
<> 或 != 不等于
> 大于
< 小于
>= 大于等于
<= 小于等于
BETWEEN … AND … 在两个值之间
IS NULL 为空值

其中 BETWEEN ... AND ... 可以取到两个值的边界

另外,在 MySQL 中空字符串 ""NULL 是不同的:

  • 空字符串代表有值,但是空字符;可以使用比较运算符进行比较。
  • NULL 代表无值,什么都没有,未定义;只能使用 IS NULL 来筛选。
7.2,逻辑运算符

逻辑运算符用于连接多个 where 子句:

逻辑运算符 含义
AND 并且
OR 或者
IN 在条件范围内
NOT
7.3,like 语句

like 语句用于模糊查询,like 根据 % 位置的不同,对索引有不同的影响:

  • like '%abc%'like '%abc' 都无法使用索引,是全表扫描
  • like 'abc%' 可以使用索引

8,DISTINCT 去重

在 MySQL 中使用 distinct 去重时,distinct 的必须写在所有列的前面,也就是紧跟 select 关键字之后:

-- 对 a 列进行去重
select distinct a from table_name;

也可以对多列进行去重:

select distinct a, b, c from table_name;

此时 MySQL 会将 a,b,c 作为一个联合字段进行统一去重,而不是分别对三个字段进行了去重。

9,关于 COUNT(*) 操作

对于 count(*),不同的 MySQL 引擎,有不同的实现方式:

  • MyISAM 引擎:把一个表的总行数存在了磁盘上,执行 count(*) 的时候会直接返回这个数,效率很高;
    • 如果带了 where 过滤条件,就没有这么快了
    • 不支持事务
  • InnoDB 引擎:它执行 count(*) 的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数。
    • 支持事务

为什么 InnoDB 不向 MyISAM 一样将行数直接记录下来呢?

这是因为,即使是在同一个时刻的多个查询,由于多版本并发控制(MVCC) 的原因,InnoDB 表“应该返回多少行”也是不确定的

这和 InnoDB 的事务有关,可重复读是它默认的隔离级别,这是用 MVCC 来实现的。每一行记录都要判断自己是否对这个会话可见,因此对于 count(*) 请求,InnoDB 只能把数据一行一行地读出依次判断,可见的行才能够用于计算“基于这个查询”的表的总行数。

使用命令 show table status like 'tbname' 得到的结果中也有一个行数(rows),注意这个行数是不准确的,这是一个采样估算值,官方文档说误差可能达到 40%50%

几种不同的 count 用法

  • count(*):总行数
  • count(1):总行数
  • count(主键id):总行数
  • count(字段名):字段部位 NULL 的行数

性能差异:

# count(*) 是经过优化的,性能最好
count(字段名) < count(主键id) < count(1)count(*)

10,MYSQL 函数

MYSQL 函数包括内置函数自定义函数

内置函数可分为 5 大类:

  • 算术函数
    • ABS(n):取绝对值
    • MOD(m, n):取余,m % n
    • ROUND(字段名称,n):四舍五入 n 个小数位
  • 字符串函数
    • CONCAT():字符串连接
    • LENGTH():字符长度(一个汉字3 个字符长)
    • CHAR_LENGTH():字符串长度,汉字、数字、字母都算一个字符
    • LOWER():字符串小写
    • UPPER():字符串大写
    • REPLACE():字符串替换
    • SUBSTRING():字符串截取
  • 日期函数
    • CURRENT_DATE():当前日期
    • CURRENT_TIME():当前时间
    • CURRENT_TIMESTAMP():当前日期 + 时间
    • DATE():时间的日期部分
    • YEAR():年份
    • MONTH():月份
    • DAY():天
    • HOUR():小时
    • MINUTE():分钟
    • SECOND():秒
  • 转换函数
  • 聚集函数
    • COUNT():总行数
    • MAX():最大值,会忽略 NULL
    • MIN():最小值,会忽略 NULL
    • SUM():求和,会忽略 NULL
    • AVG():平均值,会忽略 NULL

COUNT(*)COUNT(字段名)的区别:

-- 统计所有 a 大于 100 的行
select count(*) from table_name where a > 100;-- 统计所有 a 大于 100 且 b 不为 NULL 的行
select count(b) from table_name where a > 100;

11,GROUP BY 数据分组

数据分组使用 GROUP BY 子句,需要注意 NULL 值也会被分成一组。

示例:

-- 用字段 a 进行分组,并统计每个不同值的数量
select count(*), a from table_name group by a;

也可以对多个字段进行分组:

-- 会把 a,b 两个字段的所有取值情况都进行分组
select count(*) as num, a, b from table_name group by a, b order by num;

对分组过滤 having

WHERE 子句作用于数据行,having 子句作用域分组。

-- 筛选出 num 大于 5 的分组
select count(*) as num, a, b from table_name group by a, b having num > 5 order by num;

这里如果将 having 换成 where,则会出错。

-- 这个子句更加复杂
-- 会先筛选出所有 a > 10 的行,然后再进行分组,过滤,排序
select count(*) as num, a, b from table_name where a > 10 group by a, b having num > 5 order by num;

12,子查询(嵌套查询)

会用到下面几个关键字:

  • EXISTS:判断是否存在
  • NOT EXISTS
  • IN:判断是否在集合中
  • NOT IN
  • SOME / ANY:与子查询返回的任何值做比较,需要与比较运算符一起使用
  • ALL:与子查询返回的所有值做比较,需要与比较运算符一起使用

子查询分为关联子查询与非关联子查询:

  • 关联子查询:查询子句会执行多次。
  • 非关联子查询:查询子句只执行一次。

举例:

-- 非关联子查询
SELECT player_name, height
FROM player
WHERE height = (SELECT MAX(height)FROM player
);-- 关联子查询
-- 查找每个球队中大于平均身高的球员有哪些,并显示他们的球员姓名、身高以及所在球队 ID。
SELECT player_name, height, team_id
FROM player a
WHERE height > (SELECT AVG(height)FROM player bWHERE a.team_id = b.team_id
);

EXIST 与 IN 的使用模式:

-- 下面两种含义一样
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)                 -- B 表小时,用 IN 效率高
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc) -- A 表小时,用 Exist 效率高

ANY 与 ALL 子句,必须与一个比较操作符一起使用:

SELECT player_id, player_name, height
FROM player
WHERE height > ANY (SELECT heightFROM playerWHERE team_id = 1002
);SELECT player_id, player_name, height
FROM player
WHERE height > ALL (SELECT heightFROM playerWHERE team_id = 1002
);

13,JOIN 连接查询

根据不同的SQL 标准,连接查询是不同的,主要有 SQL92SQL99 两种。

SQL 中的 5 中连接方式:

  • 笛卡尔积
  • 等值连接:连接多个表的条件是等号
  • 非等值连接:连接多个表的条件不是等号
  • 外连接
    • 左外连接:LEFT JOIN … ON …
    • 右外连接:RIGHT JOIN … ON …
    • 全外连接:FULL JOIN … ON … (MYSQL 不支持)
    • 在这里插入图片描述
  • 自连接

AS 为表名重命名是 SQL92 语法,SQL99 中不需要用 AS,使用空格就行。
LEFT JOIN … ON 是 SQL99 语法,SQL92 中没有该语法。
LEFT 与 RIGHT 表示主表在哪边。

SQL 举例:

-- 笛卡尔积 SQL92 语法
SELECT * FROM player, team;
-- 笛卡尔积 SQL99 语法
SELECT * FROM player CROSS JOIN team;
SELECT * FROM t1 CROSS JOIN t2 CROSS JOIN t3; -- 三张表笛卡尔积-- 等值连接 SQL92 语法
SELECT player_id, player.team_id, player_name, height, team_name
FROM player, team
WHERE player.team_id = team.team_id;
-- 等值连接 SQL99 语法
SELECT player_id, team_id, player_name, height, team_name
FROM playerNATURAL JOIN team-- 非等值连接 SQL92 语法
SELECT p.player_name, p.height, h.height_level
FROM player AS p, height_grades AS h
WHERE p.height BETWEEN h.height_lowest AND h.height_highest;
-- 非等值连接 SQL99 语法
SELECT p.player_name, p.height, h.height_level
FROM player as p JOIN height_grades as h
ON height BETWEEN h.height_lowest AND h.height_highest-- 外连接 SQL99 语法
SELECT * 
FROM player 
LEFT JOIN team on player.team_id = team.team_id;-- SQL99 USING 连接,USING 用于指定两张表中的同名字段
SELECT player_id, team_id, player_name, height, team_name
FROM playerJOIN team USING (team_id)
-- 这两个 SQL 含义相同
SELECT player_id, player.team_id, player_name, height, team_name
FROM playerJOIN team ON player.team_id = team.team_id

14,VIEW 视图

在 MySQL 里,有两个“视图”的概念:

  • 一个是 view。它是一个用查询语句定义的虚拟表,是对 select 语句的封装,本身不具有数据;在调用的时候执行查询语句并生成结果。
    • 创建视图的语法是 create view …,而它的查询方法与表一样。
  • 另一个是 InnoDB 在实现 MVCC 时用到的一致性读视图,即 consistent read view,用于支持读提交可重复读隔离级别的实现。
    • 它没有物理结构,作用是事务执行期间用来定义“我能看到什么数据”。

创建视图:

CREATE VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

当视图创建之后,它就相当于一个虚拟表,可以直接使用:

SELECT * FROM view_name;

更新视图(更改一个已有的视图):

ALTER VIEW view_name AS
SELECT column1, column2
FROM table
WHERE condition

删除视图:

DROP VIEW view_name

15,存储过程

存储过程是对 SQL 语句的封装,是程序化的SQL,也就是将 SQL 写成函数(过程)的形式。存储过程由 SQL 语句和流控制语句共同组成。

定义一个存储过程:

-- 创建存储过程
CREATE PROCEDURE 存储过程名称([参数列表])
BEGIN需要执行的语句
END    -- 修改存储过程
ALTER PROCEDURE-- 删除存储过程
DROP PROCEDURE

例如:

DELIMITER //
CREATE PROCEDURE `add_num`(IN n INT)
BEGINDECLARE i INT;DECLARE sum INT;SET i = 1;SET sum = 0;WHILE i <= n DOSET sum = sum + i;SET i = i +1;END WHILE;SELECT sum;
END //
DELIMITER ;-- 调用存储过程
CALL add_num(50);

其中 DELIMITER // 用于定义结束符,在存储过程结束后,要还原 DELIMITER ;

SET 用于对变量进行赋值。

注意在使用 Navicat 时,不需要使用 DELIMITER。

参数修饰符,IN OUT INOUT 的含义:

在这里插入图片描述

示例:

CREATE PROCEDURE `get_hero_scores`(OUT max_max_hp FLOAT,OUT min_max_mp FLOAT,OUT avg_max_attack FLOAT,  s VARCHAR(255))
BEGINSELECT MAX(hp_max), MIN(mp_max), AVG(attack_max) FROM heros WHERE role_main = s INTO max_max_hp, min_max_mp, avg_max_attack;
END-- 调用存储过程
CALL get_hero_scores(@max_max_hp, @min_max_mp, @avg_max_attack, '战士');
SELECT @max_max_hp, @min_max_mp, @avg_max_attack;

其中 SELECT ... INTO 是将查询结果为变量赋值。

存储过程的缺点

  • 可移植性差
  • 调试困难,不易维护

16,临时表

临时表不同于视图(虚拟表),临时表示真实存在的数据表,只是不会长期存在,它只为当前连接存在,连接关闭后,临时表就释放了。

临时表与内存表的区别:

  • 内存表:特指用 Memory 引擎的表
    • 建表语法是 create table … engine=memory
    • 这种表的数据都保存在内存里,系统重启的时候会被清空,但是表结构还在
  • 临时表:可以使用各种引擎类型
    • 如果使用 InnoDB / MyISAM 引擎,写数据的时候是写到磁盘上的
    • 临时表也可以使用 Memory 引擎
    • 建表语法是 create temporary table …

一个创建临时表的事例:

在这里插入图片描述

临时表的特征:

  • 一个临时表只能被创建它的 session 访问,对其他线程不可见。(所以,图中 session A 创建的临时表 t,对于 session B 就是不可见的
    • 由于临时表只能被创建它的 session 访问,所以在这个 session 结束的时候,会自动删除临时表
  • 临时表可以与普通表同名
  • session A 内有同名的临时表和普通表的时候,show create语句,以及增删改查语句访问的是临时表
  • show tables 命令不显示临时表

在实际应用中,临时表一般用于处理比较复杂的计算逻辑。

上面介绍到的临时表都是用户自己创建的,也称为用户临时表

17,MySQL 权限管理

创建用户命令:

create user 'ua'@'%' identified by 'pa';

这条语句的逻辑是,创建一个用户 'ua'@'%',密码是 pa

在 MySQL 里面,用户名 (user)+ 地址 (host) 才表示一个用户,因此 ua@ip1ua@ip2 代表的是两个不同的用户。

这条命令做了两个动作:

  • 磁盘上:往 mysql.user 表里插入一行,由于没有指定权限,所以这行数据上所有表示权限的字段的值都是 N;
  • 内存里:往数组 acl_users 里插入一个 acl_user 对象,这个对象的 access 字段值为 0。

下图是此时用户 ua 在 user 表中的状态:

在这里插入图片描述

在 MySQL 中,用户权限是有不同的范围的:

  • 全局权限
  • db 权限
  • 表权限和列权限
17.1,全局权限

全局权限作用于整个 MySQL 实例,这些权限信息保存在 mysql 库的 user 表里。

要给用户 ua 赋一个最高权限的话,语句是这么写的:

grant all privileges on *.* to 'ua'@'%' with grant option;

这个 grant 命令做了两个动作:

  • 磁盘上:将 mysql.user 表里,用户 ’ua’@’%' 这一行的所有表示权限的字段的值都修改为‘Y’;
  • 内存里:从数组 acl_users 中找到这个用户对应的对象,将 access 值(权限位)修改为二进制的“全 1”。

注意:一般在生产环境上要合理控制用户权限的范围。如果一个用户有所有权限,一般就不应该设置为所有 IP 地址都可以访问。

revoke 命令用于回收权限:

revoke all privileges on *.* from 'ua'@'%';

revoke 命令做了如下两个动作:

  • 磁盘上:将 mysql.user 表里,用户’ua’@’%'这一行的所有表示权限的字段的值都修改为“N”;
  • 内存里:从数组 acl_users 中找到这个用户对应的对象,将 access 的值修改为 0。
17.2,库级权限

如果要让用户 ua 拥有库 db1 的所有权限,可以执行下面这条命令:

grant all privileges on db1.* to 'ua'@'%' with grant option;

基于库的权限记录保存在 mysql.db 表中,在内存里则保存在数组 acl_dbs 中。

这条 grant 命令做了如下两个动作:

  • 磁盘上:往 mysql.db 表中插入了一行记录,所有权限位字段设置为“Y”;
  • 内存里:增加一个对象到数组 acl_dbs 中,这个对象的权限位为“全 1”。

下图是此时用户 ua 在 db 表中的状态:

在这里插入图片描述

每次需要判断一个用户对一个数据库读写权限的时候,都需要遍历一次 acl_dbs 数组,根据 user、host 和 db 找到匹配的对象,然后根据对象的权限位来判断。

17.3,表权限和列权限

MySQL 支持更细粒度的表权限和列权限:

  • 表权限定义存放在表 mysql.tables_priv
  • 列权限定义存放在表 mysql.columns_priv
  • 这两类权限,组合起来存放在内存的 hash 结构 column_priv_hash

示例:

# 创建一个表
create table db1.t1(id int, a int);# 赋予表权限
grant all privileges on db1.t1 to 'ua'@'%' with grant option;# 赋予列权限
GRANT SELECT(id), INSERT (id,a) ON mydb.mytbl TO 'ua'@'%' with grant option;

这两个权限每次 grant 的时候都会修改数据表,也会同步修改内存中的 hash 结构。

18,Python 操作 MySQL 的库

  • mysql-connector
  • MySQLdb
  • mysqlclient
  • PyMySQL
  • peewee:一个轻量级的 ORM 框架
  • SQLAIchemy:一个 ORM 框架
代码编程
赞赏

相关文章

SEO外链方法之论坛外链心得
seo论坛外链怎么带链接(做论坛外链怎么带链接才不会被删帖)
多仓库手机端erp进销存pc/h5开源版开发
java基于springboot的进销存管理信息系统
毕业设计之基于PHP+uniapp的进销存系统
【原创】基于SSM的超市进销存管理系统(超市管理系统毕业设计)