MySQL

存储引擎

MySQL在v5.1之前的默认存储引擎使MyISAM，在此之后的默认存储引擎是InnoDB

表的类型

1. 永久表

• 创建以后用于长期保存数据的表

2. 临时表

• 只保存临时数据
• 能够长久存在

3. 虚表/视图

永久表

临时表

内存临时表

• 使用的是MEMORY存储引擎

磁盘临时表

视图

索引

索引是存储在一张表中特定列上的数据结构

• 索引是在列上创建的
• 索引是一种数据结构

全局索引

FULLTEXT

• 目前只有MyISAM引擎支持全局索引

哈希索引

HASH

• 是MySQL中用到的唯一键值对（key-value）的数据结构
• 哈希索引适合应用于查找单个键的情况
• 在范围查找中，哈希索引的性能很低

B-Tree索引

平衡树索引

• 存在很多变种
• B+Tree
• ……

R-Tree索引

• 在MySQL中较少使用
• 仅支持geometry数据类型
• 支持该类型的存储引擎有：
• MyISAM
• BDb
• InnoDb
• NDb
• Archive
• 相对于B-Tree来说，R-Tree的优势在于范围查找

MySQL自带数据库

MySQL自带三个数据库：

• information_schema
• performance_schema
• mysql

information_schema

提供访问数据库元数据的方式；可称information_schema是一个元数据库。

元数据（meta data）：关于数据的数据（data about data）。也可称为“数据词典”、“系统目录”

• 数据库名
• 表名
• 列的数据类型
• 访问权限
• ……

常见的表：

表	表的含义	表中字段	字段含义
schemata	提供数据库信息 相关命令：SHOW DATABASES;	schema_name	数据库名
		default_character_set_name	字符集
		default_collation_name	排序规则
tables	提供表的信息 相关命令：SHOW TABLES FROM schema_name;	table_schema	数据库名
		table_name	表名
		table_type	表的类型 base table, view, system view
		engine	存储引擎
		create_time	建表时间
columns	提供表中字段信息 相关命令：SHOW COLUMNS; 或 DESC TCTEST.EMP	schema_schema	数据库名
		table_name	表名
		column_name	字段名
		column_type	字段类型
statistics	索引信息 相关命令：SHOW INDEX;	schema_schema	数据库名
		table_name	表名
		index_schema	数据库名
		index_name	索引名
		column_name	字段名
		index_type	索引类型 一般是BTREE
table_constraints	约束信息	constraint_schema	数据库名
		constraint_name	约束名
		table_schema	数据库名
		table_name	表名
		constraint_type	约束类型 UNIQUE, PRIMARY KEY, FOREIGN KEY
key_column_usage	外键的参考信息	constraint_schema	数据库名
		constraint_name	约束名
		table_schema	数据库名
		table_name	表名
		column_name	字段名
		referenced_table_schema	参考的数据库
		referenced_table_name	参考的表
		referenced_column_name	参考的列
routines	函数和存储过程	specific_name	程序名
		routine_schema	数据库名
		routine_name	程序名
		routine_type	程序类型 PROCEDURE或FUNCTION
		routine_body	函数体
		routine_definition	具体的程序语句
views	视图	table_schema	数据库名
		table_name	表名
		view_definition	视图定义语句
triggers	触发器 相关命令：SHOW TRIGGERS FROM table_name;	trigger_schema	数据库名
		trigger_name	触发器名
		event_object_schema	触发的数据库
		event_object_table	触发的表
		action_statement	触发的语句
		action_timing	触发的时机 BEFORE或AFTER
engines	当前数据库对InnoDB、Memory、MyISAM等存储引擎的支持情况 相关命令：SHOW ENGINES;
global_variables	服务器变量设置，一些开关和设置 相关命令：SHOW GLOBAL VARIABLES;
plugins	插件列表 相关命令：SHOW PLUGINS;
processlist	正在运行的线程 相关命令：SHOW FULL PROCESSLIST;

SELECT REPEAT('* ', @NUMBER := @NUMBER - 1)
FROM information_schema.tables, 
(SELECT @NUMBER:= 13) t LIMIT 12; 
-- 输出：
* * * * * * * * * * * * 
* * * * * * * * * * * 
* * * * * * * * * * 
* * * * * * * * * 
* * * * * * * * 
* * * * * * * 
* * * * * * 
* * * * * 
* * * * 
* * * 
* * 
* 
-- from: https://www.hackerrank.com/challenges/draw-the-triangle-1/problem?h_r=profile

-- 输出1000以内的所有素数
SELECT GROUP_CONCAT(NUMB SEPARATOR '&')
FROM (
    SELECT @nun:=@num+1 AS NUMB FROM
    information_schema.tables t1,
    information_schema.tables t2,
    (SELECT @num:=1) tmp
) tempNum  -- 所有的可能素数
WHERE NUMB <= 1000 
AND NOT EXISTS (
    SELECT * FROM 
    (
        SELECT @nu:=@nu+1 AS NUMA FROM 
        information_schema.tables t1,
        information_schema.tables t2,
        (SELECT @nu:=1) tmp1
        LIMIT 1000
    ) t  -- NUMA是divisor
    WHERE FLOOR(NUMB/NUMA) = (NUMB/NUMA)
    AND NUMA < NUMB 
    AND NUMA > 1
);

-- 或
SET @potential_prime = 1;
SET @divisor = 1;

SELECT GROUP_CONCAT(POTENTIAL_PRIME SEPARATOR '&') FROM
(
    SELECT @potential_prime := @potential_prime + 1 AS POTENTIAL_PRIME 
    FROM 
        information_schema.tables t1,
        information_schema.tables t2
    LIMIT 1000
) list_of_potential_primes
WHERE NOT EXISTS(
    SELECT * FROM
    (
        SELECT @divisor := @divisor + 1 AS DIVISOR 
        FROM 
            information_schema.tables t3,
            information_schema.tables t4
        LIMIT 1000
    ) list_of_divisors
    WHERE MOD(POTENTIAL_PRIME, DIVISOR) = 0
    AND POTENTIAL_PRIME <> DIVISOR
    );
-- 参考：https://www.hackerrank.com/challenges/print-prime-numbers/forum

触发器

允许用户定义一组操作，这些操作通过对指定的表进行删除、更新等命令来执行或激活

时间

• 时间不能写为<='2020-07-12'的形式
• <'2020-07-12'是指2020年07月12日之前，不包括2020年07月12日
• <='2020-07-12'包含了2020年07月12日00时00分00秒，但不包含2020年07月12日一整天
• 如果要包含2020年07月12日这一天，则应写为<'2020-07-13'

运算

不等于

• 以不等于排除某些记录时，也会排除该字段为空的数据

SELECT * FROM student
WHERE Sname != '王五';
-- 该查询语句的返回结果不包含包含Sname为空的记录

-- 严谨的写法：
SELECT * FROM student
WHERE Sname != '王五' OR Sname IS NULL;
-- 返回结果不包括Sname为“王五”的记录，但包括Sname为空的记录

字符串

REPLACE()

• LEFT, RIGHT, SUBSTRING应习惯性与REPLACE()嵌套使用；因为有些数据中间可能含有意料之外的空格，导致字符串切片出错。

SUBSTRING(REPLACE(字段, ' ', ''), 7, 4)

SUBTRING()

提取字符串的子串

SUBSTRING(S, 3, 5)  -- 提取字符串S的从左数第3个字母起（包括第3个）的5个字母
SUBSTRING(S, -3)  -- 提取字符串S的从右倒数第3个字母起（包括第3个）、向右的所有的字母

相关在线练习：

• HackerRank1
• HackerRank2
• [HackerRank3]((https://www.hackerrank.com/challenges/weather-observation-station-8/problem)
• HackerRank4
• HackerRank5
• HackerRank6

其他

SET

SET @变量名称=变量值：可以快速替换重复的代码

SET @DT='2020-07-12';
SELECT * FROM table_1 WHERE date>@DT;

@

SELECT (@i:=@i+1) AS num 生成一列递增序列

延时注入

MySQL中使用延时注入常用的语句：

• sleep(5)
• BENCHMARK(10000000, MDG(1))：第一个参数是执行的次数，第二个参数是要执行的函数/表达式

但是使用BENCHMARK() (M)会让MySQL停一下，会大量消耗web服务器资源

其他注意事项

• 注意字段的格式，数字和字符串不会隐式转换
• 以 MySQL 5.7 或更低版本为准的数据库中，如何正确选择和使用合适的数据类：
  • 更小通常更好
  • 尽量用最简单的数据类型
  • 尽量不适用NULL作为字段值

参考资料

• 9 道 MySQL 面试题
• 那些SQL里面踩过的坑
• MySQL的information_schema库
• MySQL元数据库——information_schema
• MySQL information_schema 详解