hand_mysql

模型数据库类型

• 关系型数据库是目前主流的模型数据库类型，采用关系模型来组织数据，以行和列的形式存储数据，便于用户理解，技术体系成熟，支持sql语句，适用于数据格式单一、数据量不是特别大的场景，例如mysql、oracle、sql server、达梦.
• 文档型数据库主要用来存储、索引并管理面向文档的数据或者类似的半结构化数据，例如MongoDB.
• 内存型数据库将数据放在内存中直接操作，极大地提高了应用的性能，例如SQLite、Altibase.
• 分布式数据库在逻辑上是一个统一的整体，在物理上则是分别存储在不同的物理节点上。在分布式架构中，分布式数据库逐渐成为解决数据一致性的选择，例如Hbase、OceanBase.

Mysql语法

DML

查询

• 语法模板

SELECT {* | [distinct] <字段列名>} 
FROM <表 1>, <表 2>… WHERE <表达式>
GROUP BY <group by definition>
HAVING <表达式>
ORDER BY <字段列名> [{ ASC | DESC }]
LIMIT[<偏移量>,] <行数>			-- 从0开始

• 执行顺序：from --> on --> where – > group by --> having --> select --> distinct–> order by --> limit

(8) SELECT (9) DISTINCT (11) <TOP_specification> <select_list>
(1) FROM <left_table> 
(3) <join_type> JOIN <right_table>
(2) ON <join_condition> 
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list> 
(6) WITH {CUBE | ROLLUP} 
(7) HAVING <having_condition> 
(10) ORDER BY <order_by_list>

分组

• 分组实例

-- 找出部门员工总数大于等于5，且平均薪水大于$2000的员工数据，显示部门代码、部门员工数、部门平均薪水数据
select DEPARTMENT_ID, COUNT(*) count, AVG(SALARY) avg 
FROM employees 
GROUP BY DEPARTMENT_ID 
HAVING count >= 5 and avg > 2000;

• 小计/总计 （rollup）

1. with rollup 可以为 GROUP BY 运行结果的每一个分组返回一个统计行，并且为所有分组返回一个总的统计行。

2. 加上 ROLLUP 子句后，会在每个分组后面加上一行统计值，其中统计行高位字段显示为 NULL
   test表:
   

select age, COUNT(*) 
FROM test 
GROUP BY age
with rollup

运行结果:

• grouping函数

1. grouping函数用来返回每条记录是否为 ROLLUP 结果，是为 1 否为 0。
2. 通过grouping函数可以判断，哪些null是正常记录，哪些null是rollup的表示。

select IF(grouping(age)=1, '=total=', age) age, IF(grouping(nam)=1, '=total=', nam) name, 
grouping(age), grouping(nam), COUNT(*) 
FROM test 
GROUP BY age, nam
with rollup

运行结果：

3. 在 GROUPING() 函数包含多个参数时，按照以下方式来返回结果：

   GROUPING(r1,r2) 等价于 GROUPING(r2) + GROUPING(r1)

   GROUPING(r1,r2,r3,…) 等价于 GROUPING(r3) + GROUPING(r2)d

   以此类推

连接

test表

test1表

外连接

• 外连接包括左连接、右连接、全连接

• 左连接

select * from test LEFT JOIN test1 ON test.age = test1.agee;

运行结果：

• 右连接同理
• 全连接：左连接与右连接的并集

SELECT * FROM test LEFT JOIN test1 on test.id = test1.id
UNION	-- union 会删除重复行，union all 不去重
SELECT * FROM test RIGHT JOIN test1 on test.id = test1.id

内连接

内连接分为等值连接和不等连接：

1. 等值连接：在连接条件中使用等于号(=)运算符比较被连接列的列值。可用来求交集。

• 其查询结果中，作为等值连接条件的列会被去重，而其他重复列不会被去重。

-- 等值连接示例
select * from test inner JOIN test1 ON test.age = test1.agee;	-- inner join 可以省略inner
-- 或者
select * FROM test, test1 where test.age = test1.agee;

运行结果：

• using()函数

select * from table1 join table2 using(id);
-- 等价于 
select * from table1 join table2 on table1.id = table2.id;

• 自然连接：是一种特殊的等值连接，默认按列名相同字段连接，无须添加连接条件，在连接条件中使用等于(=)运算符比较，并且在结果中消除重复的属性列。

select * from test natural join test1

2. 不等连接：在连接条件使用除等于运算符以外的其它比较运算符比较被连接的列的列值。这些运算符包括>、>=、<、<=、!。

• 连接中，on 与 where 的区别：

on条件是在生成临时表时使用的条件。
where条件是在临时表生成好后，再对临时表进行过滤的条件。

子查询

• 按返回结果，可分为单行子查询和多行子查询；按子查询是否使用主查询条件，可分为相关子查询和不相关子查询。

1. 单行子查询可使用单行比较操作符作为判断条件，如：=、>、<、>=、<=、<>.

-- 找出属于部门代号10或20且薪水大于该部门平均薪水的员工
SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY
FROM employees e1
where DEPARTMENT_ID in (10, 20)
and SALARY > 
(
	SELECT AVG(SALARY) avg
	FROM employees e2
	WHERE e1.DEPARTMENT_ID = e2.DEPARTMENT_ID
	GROUP BY DEPARTMENT_ID
);

-- 使用子查询于Select子句，找出员工数据及其主管名字（主管id和员工id都在employees表）
SELECT EMPLOYEE_ID, 
(
	SELECT CONCAT(FIRST_NAME,LAST_NAME) FROM employees e2
	WHERE e1.MANAGER_ID = e2.EMPLOYEE_ID
) manager_name
FROM employees e1;

2. 多行子查询需要使用多行比较操作符，如：in、all、any ( 同some )。

-- 找出薪资低于部门代码为10中最低薪员工的员工数据
select EMPLOYEE_ID, SALARY 
from employees
WHERE SALARY < ALL		-- 低于最低的，即比所有人都低
(
	SELECT SALARY
	FROM employees
	WHERE DEPARTMENT_ID = 10
);

3. 相关子查询，在查询中可被当作函数使用，传入参数为外部查询的条件。

• select 和 where 子句中可以使用相关子查询，但 from子句中不行。

-- 由employees表格中，找出为主管的数据
SELECT * FROM employees m
WHERE EXISTS	-- 存在一个员工的managerID是该员工的employeeID,则为主管
(
	SELECT * FROM employees
	WHERE m.EMPLOYEE_ID = employees.MANAGER_ID
);

4. 不相关子查询，仅调用一次，可看作固定值。

• exists / not exists

对于exists，子查询结果集有记录为true，无记录为false。

• in 与 exists 效率对比

1. IN是做外表和内表通过Hash连接，先查询子表，再查询主表，不管子查询是否有数据，都对子查询进行全部匹配。
2. EXISTS是外表做loop循环，先主查询，再子查询，然后去子查询中匹配，如果匹配到就退出子查询返回true，将结果放到结果集。
3. 结论：主查询结果集小用EXISTS，子查询结果集越小用IN。

distinct

• 要点

1. 在对字段进行去重的时候，要保证distinct在所有字段的最前面。
2. 如果distinct关键字后面有多个字段时，则会对多个字段进行组合去重，只有多个字段组合起来的值是相等的才会被去重。
3. 常用来返回不重复字段的条数，其原因是distinct只能返回目标字段，而无法返回其他字段。如果要查询不重复字段建议使用 group by。

• 原理：先分组，然后从每组数据中取一条返回给客户端

1. distinct 依赖的字段全部包含索引时，mysql直接通过操作索引对满足条件的数据进行分组。

2. distinct 依赖的字段未全部包含索引时，mysql在临时表中对该部分数据进行分组，在临时表中进行分组的过程中不会对数据进行排序。

like / not like

• 模糊查询

select * from table where name like '张%'

• 通配符

1. ‘%’，匹配0个或多个字符
2. ‘_’，匹配1个字符

• like匹配默认忽略字母大小写，想要区分大小写可以用 like binary

dual

在oracle中，为满足select * from table的语法规范而引入的虚拟表，用来表示select 1 from dual，但是mysql可以直接select 1。

窗口函数

• 窗口函数（开窗函数），也叫OLAP函数，（Online Analytical Processing）实时分析处理函数。

• 适用于对分组统计结果中的每一条记录进行计算的场景，具体有排名问题和 TopN问题。

• 窗口，即运算范围，具体指数据表中参与运算的那些行，也是窗口函数的作用范围。

• 分组操作会把分组的结果聚合成一条记录，而窗口函数是将分组的结果置于每一条数据记录中。

• 聚合函数可以用于窗口函数中。

• 窗口函数和聚合函数的区别：

  • 聚合函数是将多条记录聚合为一条，返回一个值。
  • 窗口函数是每条记录都会执行，有几条记录执行完还是几条，返回多个值。

• 窗口函数可以分为静态窗口函数和动态窗口函数

  • 静态窗口函数的窗口大小是固定的, 不会因为记录的不同而不同;
  • 动态窗口函数的窗口大小会随着记录的不同而变化;

• 窗口函数基本语法：

-- 窗口函数一般放在 select 的子句中
func_name(<parameter>) OVER
(
    [partition by <part_by_condition>] 
	[order by <order_by_list> asc|desc]
)

func_name(<parameter>) OVER win_name
...
window win_name as
(
    [partition by <part_by_condition>] 
	[order by <order_by_list> asc|desc]
    [rows 行范围关键字| between 行范围关键字 and 行范围关键字 ]	-- 不用between and 则取当前行和指定行之间
)

-- over	关键字用于指定函数的窗口范围
-- over后内容为空，则窗口会包含满足WHERE条件的所有记录，然后窗口函数基于这些记录进行计算。
-- partition by + 字段	根据此字段将数据集分组, 然后窗口函数在每个分组中分别执行。
-- order by + 字段	每个窗口的数据依据此字段进行排序，使窗口函数按照排序后的顺序进行编号。

• 常用行范围关键字：

  • CURRENT ROW 边界是当前行，一般和其他范围关键字一起使用
  • UNBOUNDED PRECEDING 边界是分区中的第一行
  • UNBOUNDED FOLLOWING 边界是分区中的最后一行
  • expr PRECEDING 边界是当前行减去expr的值
  • expr FOLLOWING 边界是当前行加上expr的值

• 实例：

  SELECT *,SUM(sales) over(ORDER BY `month`) as 累计利润 
  from chh_baozipu where product='猪肉大葱包子';
  
  SELECT *,SUM(sales) OVER win as '近三个月利润相加'
  FROM chh_baozipu 
  WINDOW win as (PARTITION BY product ORDER BY `month` ROWS 2 PRECEDING);
  
  SELECT *,SUM(sales) OVER win as '前一个月到下一个月利润相加' 
  FROM chh_baozipu 
  WINDOW win as (PARTITION BY product ORDER BY `month` ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING);
  

• 常用窗口函数：

• 排序函数对比：

select age, 
ROW_NUMBER() over win as _row_number,
RANK() over win as _rank,
DENSE_RANK() over win as _dense_rank,
NTILE(3) over win as _ntile
FROM test 
window win as (ORDER BY age);

执行结果：

case 表达式

SELECT last_name, job_id, salary,
CASE job_id
WHEN 'IT_PROG' THEN 1.10*salary
WHEN 'ST_CLERK' THEN 1.15*salary
WHEN 'SA_REP' THEN 1.20*salary
ELSE salary
END
as "REVISED_SALARY"
FROM employees;

增删改

-- insert
INSERT INTO 表名 (列名1,列名2,...) VALUES(值1,值2,...)

INSERT INTO 表名 (select * from table1)

-- update
UPDATE 表名 SET 列 = 新值,列 = 新值,... WHERE 筛选条件

-- 多字段update
UPDATE t_emp e,t_dept d		-- UPDATE 表1,表2 SET 字段1=值1,字段2=值2,...... WHERE 连接条件
SET e.deptno=d.deptno,e.job="ANALYST",d.loc="GUANGZHOU"
WHERE e.ename="ALLEN" AND d.dname="RESEARCH";

-- 工作职称为'CLARK'的员工，薪水修正为所有员工的平均薪水
UPDATE employees set SALARY = 
(
	SELECT * FROM	-- 加一个外层查询，目的是屏蔽employees表
	(
		SELECT AVG(SALARY) FROM employees
	) a
)
WHERE JOB_ID in 
(
	SELECT JOB_ID FROM jobs
	WHERE JOB_TITLE like '%clerk%'
);

-- delete
DELETE FROM 表名 WHERE 筛选条件

DDL

• create / drop

-- 创建表
create table emp
(
	empno DECIMAL, 			-- empno DECIMAL [primary key],   主键约束
	ename VARCHAR(120),
	hiredate datetime DEFAULT NOW() COMMENT '入职日期'
   	-- [, primary key(empno)]	主键约束
);

-- 复制表
CREATE TABLE emp_temp LIKE emp;				-- 参照emp表结构创建一个表
insert into emp_temp SELECT * FROM emp;		 -- 数据拷贝

-- 删除表
drop table emp;

-- 创建视图
CREATE VIEW emp_v1 as SELECT * FROM employees;

-- 删除视图
drop view emp_v1;

• alter

-- 添加字段
alter table emp_temp
add COLUMN gender VARCHAR(2);

-- 修改字段
alter table emp_temp
MODIFY column gender VARCHAR(10) DEFAULT NULL COMMENT '性别';

ALTER TABLE stu_info_copy 
CHANGE old_name new_name varchar(50) DEFAULT '' COMMENT '用户名';	-- 可改列名，可删除自增长

-- 删除字段
alter table emp_temp
DROP COLUMN gender;

-- 表重命名
alter table emp_temp rename emp_temp2;
rename table emp_temp to emp_temp2;

-- 添加主键约束
alter table emp_temp2 add PRIMARY KEY emp_pk(empno);
alter table emp_temp2 add PRIMARY KEY (empno);			-- 主键约束名称可有可无

-- 删除主键约束
alter table emp_temp2 drop PRIMARY key;

-- 添加索引
ALTER TABLE table_name ADD INDEX index_name (Att_name_1);
CREATE INDEX index_name ON table_name (Att_name);

-- 删除索引
alter table table_name drop index index_name;
DROP INDEX index_name ON table_name

DCL

-- 创建用户
create user '用户名'@'IP地址' identified WITH mysql_native_password by '密码';
flush privileges;
-- 删除用户
# 一个用户可能会有多个IP地址
drop user '用户名'@'IP地址';

-- 授权
grant 权限1, 权限2, 权限3,… ,权限n on 数据库名.表名 to 用户名@地址 [WITH GRANT OPTION];
-- 把mysql数据库的user表的（查询，插入，更新，删除）的权限都给alian,并且是任意ip地址都可以操作
grant SELECT, INSERT, UPDATE, DELETE on mysql.user to 'alian'@'%';
flush privileges;
-- 授予数据库db1的所有权限给指定账户
GRANT ALL ON db1.* TO 'user1'@'localhost';
-- 授予角色给指定的账户
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
FLUSH PRIVILEGES;
-- 查看权限
show grants for 'alian'@'%';
-- 收回权限
revoke 权限1, 权限2…权限n on 数据库名.表名 from 用户名@地址;
-- 回收用户的更新和删除mysql（默认的库）数据库的权限
revoke update,delete on mysql.user from 'alian'@'%';

-- 事务提交与回滚
BEGIN TRANSACTION  # 事务开始
SQL1
SQL2
COMMIT   # 事务提交
BEGIN TRANSACTION  # 事务开始
SQL1
SQL2
ROLLBACK # 事务回滚

-- 保存节点
savepoint 结点名;    # 设置保存点，并和rollback结合使用，实现回滚到指定保存点
SQL1
SQL2
rollback to 结点名;  # 回滚到指定点

MySQL常用函数

字符函数

数字函数

日期函数

• 今天开始时间

SELECT str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s');

• 今天结束时间

select DATE_SUB(DATE_ADD(str_to_date(DATE_FORMAT(NOW(),'%Y-%m-%d'),'%Y-%m-%d %H:%i:%s'),INTERVAL 1 DAY),INTERVAL 1 SECOND);

聚合函数

• count

1. count(*)查询的是用户表中的所有记录，不论字段中是否为null,都计算在内;

2. count(列名)查询的是用户表中所有不为null的记录,不包括字段中为Null的数据;

3. count(一个数)查询的是用户表中所有记录,不论字段中是否为null,都计算在内;

4. count(DISTINCT c_id)

其他函数

Mysql存储引擎

• 聚集规则

有主键则定义主键索引为聚集索引；没有主键则选第一个不允许为NULL的唯一索引；还没有就使用innodb的内置rowid为聚集索引。

SQL解析器

SQL解析器主要包含词法分析和语法分析，其中，词法分析主要负责识别和检查关键字，而语法分析会根据mysql的语法规则对SQL进行校验，找出SQL的语法问题。

Mysql存储结构

Mysql索引结构

mysql索引数据结构有hash和b+tree，hash由数组和链表组成。对MyISAM存储引擎，其叶子节点 data 域存放的是数据的物理地址，即索引结构和真正的数据结构其实是分开存储的。对InnoDB存储引擎，数据是存储在主键索引里面的。

Mysql 优化

数据表结构设计方面

• 为了避免联表查询，有时候可以适当的数据冗余，比如 邮箱、姓名这些不容易更改的数据。
• 选择合适的数据类型：如果能够定长尽量定长。
• 使用 ENUM 而不是 VARCHAR，ENUM类型是非常快和紧凑的，在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个 字段来做一些选项列表变得相当的完美 。
• 不要使用无法加索引的类型作为关键字段，比如 text类型。
• 选择合适的表引擎，有时候 MyISAM 适合，有时候 InnoDB适合。
• 为保证查询性能，最好每个表都建立有 auto_increment 字段， 建立合适的数据库索引。
• 最好给每个字段都设定 default 值。

索引方面

• 一般针对数据分散、重复少的关键字建立索引，比如ID、QQ，而对性别、状态值等等建立索引没有意义。
• 字段唯一、最少、不可为null
• 对大数据量表建立聚集索引，避免更新操作带来的碎片。
• 尽量使用短索引，一般对int、char/varchar、date/time 等 类型的字段建立索引。
• 需要的时候建立联合索引，但是要注意查询SQL语句的编写。
• 谨慎建立 unique 类型的索引（唯一索引）。
• 大文本字段不建立为索引，如果要对大文本字段进行检索， 可以考虑全文索引。
• 频繁更新的列不适合建立索引。
• order by 字句中的字段，where 子句中字段，最常用的sql 语句中字段，应建立索引。
• 唯一性约束，系统将默认为该字段建立索引。
• 对于只是做查询用的数据库索引越多越好，但对于在线实时 系统建议控制在5个以内。
• 索引不仅能提高查询SQL性能，同时也可以提高带where字句 的update，Delete SQL性能。
• Decimal 类型字段不要单独建立为索引，但覆盖索引可以包 含这些字段。
• 只有建立索引以后，表内的行才按照特地的顺序存储，按照 需要可以是asc或desc方式。
• 如果索引由多个字段组成，将用来查询过滤的字段放在前面 可能会有更好的性能。

SQL语句方面

• 能够快速缩小结果集的 WHERE 条件写在前面，如果有恒量条件， 也尽量放在前面。
• 尽量避免使用 GROUP BY、DISTINCT 、OR、IN 等语句的使用， 避免使用联表查询和子查询，因为将使执行效率大大下降。
• 能够使用索引的字段尽量进行有效的合理排列，如果使用了 联合索引，请注意提取字段的前后顺序。
• 针对索引字段使用 >, >=, =, <, <=, IF NULL和BETWEEN 将会使用 索引， 如果对某个索引字段进行 LIKE 查询，使用 LIKE ‘%abc%’ 不能使用索引，使用 LIKE ‘abc%’ 将能够使用索引。
• 如果在SQL里使用了MySQL部分自带函数，索引将失效，同时将无法 使用 MySQL 的 Query Cache，比如 LEFT(), SUBSTR(), TO_DAYS() DATE_FORMAT(), 等，如果使用了 OR 或 IN，索引也将失效。
• 使用 Explain 语句来帮助改进我们的SQL语句
• 不要在where 子句中的“=”左边进行算术或表达式运算，否则系统将 可能无法正确使用索引
• 尽量不要在where条件中使用函数，否则将不能使用索引
• 避免使用 select *, 只取需要的字段
• 对于大数据量的查询，尽量避免在SQL语句中使用order by 字句，避免额外的开销，替代为使用ADO.NET 来实现。
• 只关心需要的表和满足条件的数据
• 适当使用临时表或表变量
• 对于连续的数值，使用between代替in
• where 子句中尽量不要使用CASE条件
• 尽量不用触发器，特别是在大数据表上
• 更新触发器如果不是所有情况下都需要触发，应根据业务需要加上必要判断条件
• 使用union all 操作代替OR操作，注意此时需要注意一点查询条件可以使用聚集索引，如果是非聚集索引将起到相反的结果
• 当只要一行数据时使用 LIMIT 1
• 尽可能的使用 NOT NULL填充数据库
• 拆分大的 DELETE 或 INSERT 语句
• 批量提交SQL语句

思考题

• A) BETWEEN AND 对比 <= and >=
  “between and” 对于数字类型始终包含边界，对于日期类型包含左边界而不包含右边界。而"not between and" 不包括边界。“<= and =>” 则始终包含边界。
• B) ORDER BY 执行时机
  sql执行顺序：from --> on --> where – > group by --> having --> select --> distinct–> order by --> limit
  "order by"的执行在分组之后、分页之前。
• C) ORDER BY 多列顺序对结果影响
  以 “order by” 后面字段的顺序为使用优先级顺序，前面字段的值相同时才会使用后面的字段。asc和desc跟在每个字段的后边，以控制升降序，且asc可省略，如:

select * from table order by grade asc, id desc;

• 给用户A添加数据库B中的表C的查询权限，DCL语句该怎么写？

   grant select on database_B.table_C to 'user_A'@'%' 

• 一条SQL查询语句是如何执行的

1）首先客户端发送请求到服务端，建立连接，并进行权限验证。

2）服务端先看下查询缓存是否命中，命中就直接返回，否则继续往下执行。

3）接着来到解析器，进行语法分析，一些系统关键字校验，校验语法是否合规。

4）然后优化器进行SQL优化，比如怎么选择索引之类，然后生成执行计划。

5）最后执行引擎调用存储引擎API查询数据，返回结果。

1、先在where解析这一步把当前的查询语句中的查询条件分解成每一个独立的条件单元

2、mysql会自动将sql拆分重组

3、然后where条件会在B-tree index这部分进行索引匹配，如果命中索引，就会定位到指定的table records位置。如果没有命中，则只能采用全部扫描的方式

4、根据当前查询字段返回对应的数据值

• 如果表 T 中没有字段 k，而你执行了这个语句 select * from T where k=1,
  那肯定是会报“不存在这个列”的错误： “Unknown column ‘k’ in ‘where clause’”
  这个错误是在上面提到的哪个阶段报出来的呢？

答：在SQL解析阶段。

TO DO

1.PPT中涉及的命令，每个都需要去测试一遍（可以命令与可视化工具结合着看一下）
3.仔细阅读和理解（出现的各种索引的用法详解？特性是什么？各个索引的对比[出一个对比表]）
4.Explain执行计划了解
5.MySQL Slow Log 分析工具了解
6.建表、索引的原则、编写高效SQL的注意点