mysql-grammer-summary

former

use <database> : 选择某个数据库
测试: 不加 FROM 字段可以测试 mysql 的各个功能
DELIMITER <character>: 自定义新的分隔符（以替换掉;分隔符）

常用的 SHOW 命令

SHOW DATABASES:
SHOW TABLES:
SHOW COLUMNS FROM <TABLE> 等价于 DESCRIBE <TABLE>
SHOW GRANTS

常用的 SELECT 命令

SELECT <COLUMNS> FROM <TABLE> : 从表中选出某一列（整列）
SELECT DISTINCT <COLUMNS> FROM <TABLE> : 从表中选出某一整列，但剔除掉重复的行
SELECT <COLUMNS> FROM <TABLE> LIMIT <row, lines> : 选出从第 row 行开始的 lines 行
SELECT <TABLES>.<COLUMNS> FROM <DATABASE>.<TABLE> : 限定 scope（相当于 namespace)

排序

只要涉及到排序，必须使用order by，这是保证正确性的唯一途径
SELECT <COLUMN1> FROM <TABLE> ORDER BY <COLUMN2> :
SELECT <COLUMN1> FROM <TABLE> ORDER BY <COLUMN2>, <COLUMN3> :
SELECR <COLUMN1> FROM <TABLE> ORDER BY <COLUMN1> DESC :
SELECT <COLUMN1> FROM <TABLE> ORDER BY <COLUMN2> DESC, <COLUMN3> :
SELECT <COLUMN1> FROM <TABLE> ORDER BY <COLUMN2> DESC LIMIT 1 :
select <column> from <table> order by ... collation <collation-name>: 使用指定的校对规则进行排序（比如区分大小写的校对）

选择

SELECT <COLUMNS> FROM <TABLE> WHERE <STATEMENT> ORDER BY <COLUMN> :
SELECT <COLUMNS> FROM <TABLE> WHERE <COLUMN1> BETWEEN 2 AND 8
SELECT <COLUMN1> FROM <TABLE> WHERE <COLUMN2> IS NULL
<STATEMENT> 包括 : =, !=, >, <, BETWEEN, is NULL

组合 WHERE

AND 以及 OR : 注意，需要使用()以指定计算次序
IN : 使用IN来取代OR
NOT : 只允许对IN, BETWEEN, EXISTS取返

通配符

LIKE
% : 匹配任意个字符，但并不匹配NULL
_ : 只匹配一个字符

不要过度使用通配符，因为会降低效率;
通配符放在越前面，速度会越慢;

正则表达式

REGEXP :
REGEXP 和 LIKE的区别：前者匹配列中的内容；后者匹配整个列的值
BINARY : 区分大小写
|
[]

[32as] : 这几个字符任选一个
[0-9] : 0 至 9 任选一个
[^325534] : 除了这几个字符之外的字符
// : 转义
/// : 转义斜杠本身

计算字段

AS : 给列赋一个别名，方便客户机对于数据的引用
- 如果给表赋别名，则别名不会返回到客户机
拼接：
- Concat()：
- RTrim()：删除值的右侧多余空格, 类似还有LTrim()以及Trim()
加减乘除：支持对数值列进行基本的+-*/

函数

函数没有那么强的通用性，对跨平台支持的不是那么好，因此应该谨慎使用。

Soundex() : 按照发音的相似性进行选择
Upper() 和 Lower()
获取时间
- Now(), CurDate(), CurTime()
- 应该总是使用YYYY-MM-DD的日期格式
- Date() : 只提取Datetime型变量的日期部分
- Time() : 同理
- datediff() : 求两个日期的差值
流程控制
- if(<condition>, <value1>, <value2>): 类似于问号表达式，满足condition，则返回value1，否则返回value2
类型转换
- ascii(): 将表中的值转为 ascii 值
- char(): 强制转换为字符类型
聚集函数：对列中的数据进行汇总
- AVG()
- COUNT() : COUNT(*)全部行, COUNT(COLUMN)非空行
- MAX() 和 MIN()
- SUM()
- 聚合不同的值：使用DISTINCT参数（select COUNT(distinct RESERVED) from KEYWORDS;）
其他：
- round() : 四舍五入，可以填入第二个参数，表示保留小数点后几位mysql 练习-考试分数
- last_insert_id(): 返回表中的最近一个 auto_increment 值

分组

对整个表分为不同的逻辑组，使得聚集函数的执行对象是各个逻辑组，而不是整个表

顺序要求：where ... group by ... order by ...
依赖：where中出现的列必须也要出现在group by中
group by可以选择多个列：这样就是将所有的列都进行分组(N*N*N*…*N 个组)
WHERE 和 HAVING : WHERE过滤行，HAVING过滤分组
order by和group by : order by可对任意列进行操作；group by仅对且必须对select选择的列进行操作

流程控制

case when <condition> then ... else ... end
case <column> when <value> then ... else ... end

顺序

select -> from -> where -> group by -> having -> order by -> limit

子查询

select ... where XXX in (select ...)

需要保证子查询和 where 子句有相同的列
相关子查询：即限定 scope，子查询中的 where 涉及到外部的列

select cust_name,
       (select count(*) from orders
       where orders.cust_id = customers.cust_id) AS order
from customers
order by cust_name;

联结表

设计数据库时尽量保证不出现重复的数据；
把信息分为多个表，一类数据一个表；
一切为性能为主：执行联结操作时能少点就尽量少点
利用外键定义表和表之间的关系；
“联结”并不是实体，它只存在于查询操作之中；

等值联结：where 子句中的各个列为相等关系

利用 where 进行等值联结
- 保证联结时必须有 where，否则将会是笛卡尔积，数据非常多。
使用inner join ... on ...替代等值联结
- 两者实际上等价:
- where 和inner join首选inner join

1
2
3

select vender_name, order_name, order_price
from orderes inner join venders
on orders.vender_id = venders.vender_id;

使用自联结替代子查询

自联结通常会比子查询快很多

# 查询和商品“XXX”的生产商家一样的商品
select p2.prod_id, p2.prod_name # 注意此处为p2
from prods as p1, prods as p2
where p2.vend_id = p1.vend_id AND
      p1.prod_name = 'XXX';

外部联结：包含了在相关表中没有关联行的行

将两个表之间的联结行视为公共属性，公共属性相同的行意味着在笛卡尔坐标中存在交叉点
<table-A> left outer join <table-B> : 检出表 A 的无关联行
<table-A> right outer join <table-B> : 检出表 B 的无关联行
问题举例:

获取所有非 manager 的员工 emp_no
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
思路 1：使用子查询，先查出所有 manager 的 emp_no，然后再找到不在这些 emp_no 中的 employee 即可；
思路 2：使用外部联结，因为要找的为不是 manager 的 employee，因此这些 employee 本身没有和“table dept_manager”关联，故可以使用外部联结，最后再加上where条件筛选一下即可：

select e.emp_no
from employees as e left outer join dept_manager as d
on e.emp_no = d.emp_no
where d.emp_no is NULL; # d.emp_no为空代表e.emp_no无关联

组合查询

组合查询和多个 where哪一种性能更好需要测试
UNION: 组合多条 select 的查询结果；各个 select 的查询返回形式必须一致
- UNION会自动去重（和数学中的并集的语义一致）
- 如果不想去重，则使用UNION ALL
- UNION的作用主要还是用于简化太过复杂的多个 where

select prod_id, prod_name
from prods
where price > 5
UNION // 并操作
select prod_id, prod_name
from prods
where prod_id in (23,24);

全文本搜索

需要使用FULLTEXT关键词以指示系统需要进行全文搜索的列。FULLTEXT (COLUMN1, COLUMN2, ...)
match(COLUMN) against(<PATTERN>)
和like的区别：全文搜索按照匹配度的高低顺序进行返回

数据插入

最简单的使用：insert into <TABLE1> values(...)
- 并不安全，应避免这种用法：如果各个列的顺序未对应上，则是未定义行为
更安全的使用：insert into <TABLE1> (COLUMN1, COLUMN2, ...) VALUES (...)
- 明确了列的顺序，所以即使数据库表的结构发生变化，代码依然有效
- 如果某些列没有值（即 NULL），则还可以省略这些列名
insert LOW_PRIORITY into : 降低插入命令的优先级，以降低其对性能的限制
insert into <TABLE1> select ... : 将 select 语句返回的各行插入到 TABLE1 中

三大范式

第一范式：表中所有字段都是不可拆分的原子值；
- 比如“地址”可以拆分为“省份”，“城市”，“街道”等，因此“地址”不是原子值；
第二范式：表中的每一列都要与主键相关，注意，是主键中的所有键
第三范式：表中每一列都和主键直接相关，不能间接相关
- 第二范式和第三范式的区别就是：第二范式中属性依赖于主键；而第三范式中属性只依赖于主键。

数据更新和删除

1
2
3

update <table1>
set <column1> = 'given-value'
where (...) // important! to limit the range

1 2	delete from <table1> where (...) // important! to limit the range

使用update或delete时一定要加where来限定范围（除非确实是需要更新所有行或者删除所有行）
如果使用了表别名，则delete from需要改为delete <TABLE-NAME> from
delete from是删除表中内容；drop table是删除表本身

创建表

create table <table-name>(
  <column-name>    <variable-type>    <NULL-or-NOT-NULL> <FLAG>,
  ...
  PRIMARY KEY (<column-name>)
)
ENGINE=<engine-name> # 指定引擎
DEFAULT CHARACTER SET <charater-set-name> # 指定默认字符集
COLLATE <collate-name> # 指定校对方式

FLAG包括：
- auto_increment: 插入新行时对于该列自动增量
- default <value>: 指定一个默认值
创建表的注意事项
- 各个列名必须各不相同
- 新表的名字不能和已有表相同(可以使用create table if not exists(...))
- 只能有一个列可以指定为auto_increment，且必须被索引
索引
- 优化查询操作，就是要尽可能降低磁盘 IO 的次数
- B+树的高度直接等价于磁盘 IO 的次数，而实际情况中 3 层的 B+树就可以表示上百万的数据。所以 MySQL 的索引使用 B+树作为底层数据结构
- 如果数据表的数据为 N, 每个磁盘块的数据项的数量为 m，则高度$h=log_{m+1}^{N}$
- 一个磁盘块可以理解为一个页；一个数据项即一个索引字段
- 磁盘块大小固定，故数据项所占空间越小，B+树高度越低
- 最左前缀匹配原则：因为 B+树的特性，只有等值查询才是可以索引的(=, in之类)，故建立索引时，要将等值查询的列放在前面；
- 建立索引时要选在区分度高的列，根据count(distinct <column-name>) / count(*)，越接近 1 越好

MySQL 索引原理及慢查询优化 - 美团技术团队 (meituan.com)

ENGINE包括:
- InnoDB: 事务处理引擎，不支持全文本搜索
- MEMORY: 和 MyISAM 类似，除了将数据存储在内存中，所以速度很快
- MyISAM: 性能极高，支持全文本搜索，不支持事务处理

视图(VIEW)

视图是虚拟的表, 其本身并不包含数据。归根结底，视图是对 select 的封装，使得 SQL 语言可以得到复用，简化了复杂度

用途 1：隐藏复杂的 SQL 语句(复用)

## 使用`create view`创建一个视图，后续的查询都可以直接使用这个视图
## 从而复用语句
create view <view-name> AS (
  select ... # select语句来得到一个临时表
);

select <column1> from <view-name>;
select <column2> from <view-name> where ...;
...

用途 2：格式化检索出的数据(将函数放到视图的内部)

create view tmp_view  AS (
  select Concat(column1, '(', column2, ')')
  from table1
);

select * from tmp_view; # 格式化输出

用途 3：过滤不想要的数据
注意事项：
- 从 VIEW 中执行检索时使用的where和 VIEW 内部的where子句会被自动组合

存储过程

可以简单的理解为批处理文件，但拥有比批处理更强大的功能
创建存储过程和使用存储过程的权限并不一致，大多数时候只能使用，而创建权被限制
存储过程实际上是一种函数
一般来说，存储过程不显然执行结果，而是将结果存储到变量中

调用存储过程

1	call <procedure-name>(@<parameter-name>); # 变量必须以`@`符号开始

删除存储过程

1 2	drop PROCEDURE <procedure-name>; drop PROCEDURE IF EXISTS <procedure-name>;

创建存储过程

注意，一定要添加delimiter，不然的话当declare变量的时候会报错；存储过程的创建语句的尾部也就需要使用$$这个新指定的截断符，但存储过程内部还是,没有改变

# OUT代表输出参数
# IN代表输入参数
# INOUT代表输入输出参数
delimiter $$
create PROCEDURE <procedure-name>(
  <OUT/IN/INOUT> <parameter-name> <parameter-type>
)
BEGIN
  declare <变量名> <类型> <default <默认值>>;
  select
  ... # 具体的select语句
  INTO <parameter-name>;

  IF <condition> THEN
  ...
  END IF;

  while <条件> do
  ...
  end while;
END $$
delimiter ;

其他

使用变量：select @<variable1-name>, @<variable2-name>, @<variable3-name> ...
声明变量：declare <variable-name> <variable-type>
在存储过程中加注释: 以--为行的开头

游标

仅使用于存储过程之中
主要用途是进行查询结果的实时交互
- fetch <column-name> into <variable-name>: 每次处理一行(单行单行的处理)
<state-code>:
- 02000 - 表示循环 fetch 到最后一行结束

create PROCEDURE tmp_procedure()
begin
  --- 声明游标
  declare <cursor-name> CURSOR
  for
  select <...> # 定义该游标的具体用途

  --- 打开游标(使用游标)
  open <cursor-name>;

  --- 关闭游标
  close <cursor-name>;

  --- 声明句柄，当`SQLSTATE`为对应state-code时应该执行的command
  --- 一般情况下是当SQLSTATE变为指定state-code的时候，更改指定的variable
  declare CONTINUE HANDLER <handle-name> SQLSTATE '<state-code>' <command> ;

  --- 重复进行fetch，直至每行都被处理完毕
  repeat
  fetch ... into ...;
  until <variable-name> END REPEAT;
end;

注意事项:
- 声明变量在声明游标之前；
- 定义句柄在声明游标之后；

触发器

在表发生变动(insert, update, delete …)的时候需要自动执行的操作
视图不支持触发器
一个表仅可能存在6 个触发器：(insert, update, delete) * (before, after)

create TRIGGER <trigger-name> <BEFORE/AFTER> <insert/update/delete> on <table-name>
for each row # 对每一个（变动的）行
select ...; # 需要执行的操作

drop TRIGGER <trigger-name>; # 删除触发器

New: 可以看作 insert 的新行构成的一个虚拟表
- 在 update 或者 insert 之前是可以更改的

create trigger tmp_trigger after insert on table1
for each row
select New.name_id; # 每插入一行，则取出该行对应的name_id列

create trigger tmp_trigger2 before insert on table1
begin
  for each row
  set New.name = Upper(New.name); # 每次插入新行之前，先将行中的name变为大写
end;

Old: 可以看作 delete 的旧行构成的一个虚拟表

create trigger tmp_trigger3 before delete on table2
begin
for each row
insert into archive_table2(name_id, name)
values(Old.name_id, Old.name) # 在删除一行之前，先把该行的name_id和name进行存档
end;

update: 更新表的时候，New和Old都能使用

事务处理

用来维护数据库的完整性(要么完整的运行完整组操作，要么完全不执行)
MyISAM 不支持事务处理；InnoDB 支持
事务：一组 SQL 语句
START TRANSACTION: 事务的开始标志
ROLLBACK:
- 回退；只能在单个START TRANSACTION之后回退
- 只能用于管理insert, update, delete; 创建和删除表操作是对 ROLLBACK 无效的
COMMIT:
- 也是和START TRANSACTION配对，保证只在不出错的情况下提交更新
SAVEPOINT:
- 保留点；用于灵活的回退或者提交
执行完ROLLBACK或COMMIT之后，事务自动关闭

begin transaction
  delete from table1 where order_id = '000';
  delete from table2 where order_id = '000';
commit # 保证不会对table1和table2部分删除

begin transaction
  delete from table1 where name_id = '123';
  SAVEPOINT point1; # 定义了保留点point1
  insert into table2(name_id, name_value) VALUE('123', 'XXX');
  ROLLBACK to point1; #当发生错误之后，只回退到point1处，对于table1的行删除操作不会撤销
commit

本地化和全球化

字符集：字母和符号的集合
编码：字符集的内部表示
校对：规定字符之间如何进行比较

安全管理

访问控制：给予用户能够满足需求的最低权限
用户定义：<user-name>@<host-name>，host-name 默认为%，代表本机
创建新用户:

1
2
3

create user <user-name> identified by '<password>';
rename user <old-name> to <new-name>;
drop user <user-name> # 删除指定账户

用户权限：
详细的operation-name请见《mysql 必知必会》202 页

1
2
3

show grants for <user-name>; # 显示指定用户被赋予的权限
grant <operation-name> on <databaseName.tableName> to <user-name>; #  针对指定数据库的指定表的范围内，为指定用户授予指定操作的权限（select, update等等）
revoke <operation-name> on <databaseName.tableName> to <user-name>; # 撤销权限

用户口令（密码）:

# set password在新版本中已经不支持
#set password for <user-name> = Password('<new-password>') # Password()的作用是对数据进行加密
alter user <userName@hostName> identified by '<new-password>' # 修改密码

alter user 'dizzy'@'%' identified by '123456';

改善性能

当需要调整 DBMS 设置项时，使用SHOW VARIABLES和SHOW STATUS来查看当前设置项
当多线程环境中执行效率收到某个任务的影响时，使用SHOW PROCESSLIST查看状态
执行 select 太慢 -》确定 where 中的某几个列，建立索引
UNION 往往比 OR 更快