概论
公共表表达式（临时视图） WITH 子句
视图
存储过程
事务
- 锁
  - 类型
  - 避免死锁
触发器
范式
索引
all
except
UNION
NULL
选择语句
like、RLIKE和正则
IN
函数
group by 和count的联合使用问题
时间格式
参考

概论

基本概念

数据：数据库中存储对象的基本对象。描述事物的符号记录称为数据。数据的含义称为数据的语义，数据与其语义是不可分的。
数据库：存放数据的仓库。数据库是长期存储在计算机内、有组织的、可共享的大量数据的集合。数据库中的数据按一定的数据模型组织、描述和存储。具有较小的冗余度、较高的独立性和易拓展性，并可为各种用户共享。
数据库管理系统：用户与操作系统之间的一层数据管理软件。功能：

数据定义DDL
数据管理、存储和组织
数据操纵
数据库的事务管理和运行管理
数据库的建立和维护
其它

数据库系统：由数据库、数据库管理系统（及其开发工具）、应用程序和数据库管理员组成的存储、管理、处理和维护数据的系统。

数据管理三个阶段

文件系统到数据系统标志着数据管理技术的飞跃

数据模型

数据模型是对现实世界数据特征的抽象，用来迈速数据、组织数据和对数据进行操作的。
模型分为两大类：

概念模型(信息模型)：按照用户观点对数据和信息建模，主要用于数据库设计
逻辑模型和物理模型
- 逻辑模型：按计算机系统的观点对数据建模，主要用于数据库管理系统的实现。
  - 层次模型
  - 网状模型
  - 关系模型
  - 面向对象模型
  - 对象关系数据模型
  - 半结构化数据模型
- 物理模型:对数据地产的抽象，描述在系统内部的表示方式和存取方法。
  概念模型
  实体：客观存在可相互区别的事物
  属性：实体所具有的的某一特征
  码：表示实体的属性集
  实体型：用实体名及其属性名集合来抽象和刻画同类实体
  实体集：同一类型实体的集合
  联系：实体之间的关联
一对一
一对多
多对多

ER（实体-联系方法）图

数据模型的组成要素
数据结构
数据操作
完整性约束
公共表表达式（临时视图） WITH 子句
相当于一个临时视图 ```sql with payroll (deptno.totalpay) as ( select deptno,sum(salary)+ sum(bonus) from emp group by depton )

<a name="eWUr3"></a>
# 递归查询
举一个递归实现树形查询的例子，还是拿 emp 表来说吧。我想知道 emp 表中每个员工的和 boss 之间的层级关系，以及员工所在的层级，使用递归就可以这么做：
先获取到 boss 的信息；<br />然后根据上下级关系不断去迭代，直到找到所有没有下级的员工的信息。
```sql
WITH RECURSIVE cte (empno, ename, LEVEL, tree) AS 
(SELECT 
  empno,
  ename,
  0 AS LEVEL,
  CAST(ename AS CHAR(120)) AS tree 
FROM
  emp 
WHERE mgr IS NULL 
UNION ALL 
SELECT 
  e.empno,
  e.ename,
  c.level + 1,
  CONCAT_WS('-->', e.ename, c.tree) 
FROM
  cte c 
  INNER JOIN emp e 
    ON e.mgr = c.empno) 
SELECT 
  * 
FROM
  cteWITH RECURSIVE cte (empno, ename, LEVEL, tree) AS
(SELECT
 empno,
 ename,
 0 AS LEVEL,
 CAST(ename AS CHAR(120)) AS tree
 FROM
 emp
 WHERE mgr IS NULL
 UNION ALL
 SELECT
 e.empno,
 e.ename,
 c.level + 1,
 CONCAT_WS('-->', e.ename, c.tree)
 FROM
 cte c
 INNER JOIN emp e
 ON e.mgr = c.empno)
 SELECT
 *
 FROM
cte

这条 SQL 需要注意一个地方，我在递归子查询里面的第一个 SELECT 语句中指定了 tree 字段的长度。如果没有指定 tree 字段的长度，它将使用 ename 字段的实际长度作为 tree 字段的长度，在第二个 SELECT 子句中放入超过 tree 字段长度的内容将会被截断。

上面 SQL 执行的结果：

empno  ename    level  tree
7839  KING         0  KING
7566  JONES        1  JONES-->KING
7698  BLAKE        1  BLAKE-->KING
7782  CLARK        1  CLARK-->KING
7499  ALLEN        2  ALLEN-->BLAKE-->KING
7521  WARD         2  WARD-->BLAKE-->KING
7654  MARTIN       2  MARTIN-->BLAKE-->KING
7788  SCOTT        2  SCOTT-->JONES-->KING
7844  TURNER       2  TURNER-->BLAKE-->KING
7900  JAMES        2  JAMES-->BLAKE-->KING
7902  FORD         2  FORD-->JONES-->KING
7934  MILLER       2  MILLER-->CLARK-->KING
7369  SMITH        3  SMITH-->FORD-->JONES-->KING
7876  ADAMS        3  ADAMS-->SCOTT-->JONES-->KING

视图

由基表数据映射而成的虚表

CREATE VIEW CurrentProductList AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No

存储过程

用来改善用户的开发体验，经常使用的一组语句。
优点：

改进系统性能，数据库系统会对存储过程的sql进行优化
修改这个功能时只需要更改这个存储过程

拓展DBMS的功能

-- 创建： 无参
delimiter $
    create procedure myp1()
  begin
   insert into admin(username,`password`) 
  values('join',0001),
  values('jack',0002),
  values('zhangsan',0003),
  values('zhaoliu',0004),
  values('wangwu',0005);
end $
--  调用
call myp1()$
-- 删除
drop  procedure myp1;

事务

ACID特性：

原子性
保持一致性
隔离性
持久性

锁

类型
S锁：共享锁。又称读锁，若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。
这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
X锁：排它锁。又称写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。
这保证了其他事务在T释放A上的锁之前不能再读取和修改A。
- U锁，X锁的一种，更新锁。U锁首先加到要更新的数据上(即使目标上面有S锁，U锁还是可以加上)，等目标可以被更新时，U锁即转变为X锁。
  避免死锁
事务重试：为每个事务安排一个时间戳

触发器

范式

第一范式（1NF）：所关系中每个属性必须是原子的，即不能表中套表
第二范式：满足一范式，且不存在属性对主键的部分函数依赖
第三范式：满足二范式，且不存在属性对主键的传递依赖
BNCF（和第三范式在大多数情况下等价）：属性依赖的决定值都是主键
第四范式：满足三范式，且不存在消去属性的多值依赖
第五范式：满足四范式，且不存在消去属性的连接依赖

不满足范式可能的问题：

插入异常
删除异常
更新异常：很难维护数据的一致性，存在数据冗余

索引
单例索引
联合索引
空间索引
唯一索引

all
加all：保留重复元组
不加all：去除重复元组

except
集合差

UNION
合并两个或多个 SELECT 语句的结果

NULL
MySQL 使用三值逻辑 —— TRUE, FALSE 和 UNKNOWN。任何与 NULL 值进行的比较都会与第三种值 UNKNOWN 做比较。这个“任何值”包括 NULL 本身！这就是为什么 MySQL 提供 IS NULL 和 IS NOT NULL 两种操作来对 NULL 特殊判断。

选择语句

Case when
对工资进行分级
```
SELECT
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END salary_class,
COUNT(*)
FROM    Table_A
GROUP BY
CASE WHEN salary <= 500 THEN '1'
WHEN salary > 500 AND salary <= 600  THEN '2'
WHEN salary > 600 AND salary <= 800  THEN '3'
WHEN salary > 800 AND salary <= 1000 THEN '4'
ELSE NULL END;
```
编写一个 SQL 查询来交换所有的 ‘f’ 和 ‘m’ （即，将所有 ‘f’ 变为 ‘m’ ，反之亦然），仅使用单个 update 语句，且不产生中间临时表。
```
UPDATE salary
SET
  sex = CASE sex
      WHEN 'm' THEN 'f'
      ELSE 'm'
  END;
```
If
IF( expr1 , expr2 , expr3 )
expr1 的值为TRUE，则返回值为 expr2
expr1 的值为FALSE，则返回值为 expr3

查找出售价为 50 的书，如果是 java 书的话，就要标注为已售完

select *,if(book_name='java','已卖完','有货') as product_status from book where price =50

IFNULL

IFNULL( expr1 , expr2 )

在 expr1 的值不为 NULL 的情况下都返回 expr1
否则返回 expr2

如下：

SELECT IFNULL(NULL,"11"); -> 11 SELECT IFNULL("00","11"); #00

like、RLIKE和正则

REGEXP和LIKE默认是不匹配大小的，要加BINARY来限制
REGEXP
- 用|表示条件之一
- 在开头用’^DIAB1’匹配
- 在其他位置用’\sDIAB1’匹配，’\s’表示空格
LIKE
- 在开头用’DIAB1%’匹配
- 在其他位置用’% DIAB1%’匹配
RLIKE
- LIKE 的内容不是正则，而是通配符
- RLIKE 的内容可以是正则或者通配符
- 可以用于字段的匹配，like只能用于字符串的匹配

写一条 SQL 语句，查询患有 I 类糖尿病的患者 ID （patient_id）、患者姓名（patient_name）以及其患有的所有疾病代码（conditions）。I 类糖尿病的代码总是包含前缀 DIAB1 。

select 
    * 
from 
    patients 
where 
    conditions rlike '^DIAB1|.*\\sDIAB1';

IN

in后面的列表不能为空，否则会报错

函数

group_concat

将同一个字段不同记录以逗号分隔以一个记录方式显示
通过使用DISTINCT可以排除重复值

SELECT id,GROUP_CONCAT(DISTINCT score) FROM testgroup GROUP BY id

find_in_set(str,strlist)

第一个参数可以是字段中的一个数据
第二个参数时字段集合也可以是作为逗号分隔的字符串
查询结果为str在strlist的结果集

SELECT * FROM category WHERE FIND_IN_SET(id,'1,3,5,7')

concat 和 concat_ws 的区别

concat：只能拼接字符
concat_ws：可以拼接字段串和字符串

举个例子🌰：
查询所有顶级分类课程，使用CONCAT_WS得到正确的结果

SELECT
 c.classification_id_list,
 ci.id,
 ci.name,
 ci.*
FROM
    class c,
    class_ification ci
WHERE
    c.is_delete = 0
    AND c.classification_id_list LIKE CONCAT_WS('%',ci.id,"")
    AND ci.parent_id=0

查询所有顶级分类课程，使用CONCAT_WS得到错误的结果

SELECT
 c.classification_id_list,
 ci.id,
 ci.name,
 ci.*
FROM
    class c,
    class_ification ci
WHERE
    c.is_delete = 0
    AND c.classification_id_list LIKE CONCAT('%',ci.id,"")
    AND ci.parent_id=0

CAST

将某种数据类型转换为另一种数据类型

# 语法
select CAST (类型1 AS 类型2)
from 表

# 例子
select *,cast(updated_time as signed) as date_time
FROM ceshi1

但是可以转换的类型只可以是下面的其中一个：

二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
时间: TIME
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED

无符号整数 : UNSIGNED

group by 和count的联合使用问题

一下查询出来的结构是错误的，统计的是每个分组的数据个数，所以都是1。

SELECT
  COUNT( t1.id ) AS count 
FROM
  course AS t1
  LEFT JOIN ( SELECT * FROM lesson WHERE is_delete = 0 ) AS t2 ON t1.id = t2.class_id
  LEFT JOIN ( SELECT * FROM class WHERE is_delete = 0 AND corp_id = "ding8e9eaf0f82120a5735c2f4657eb6378f" ) AS t3 ON t2.lesson_id = t3.id 
WHERE
  ( t1.is_delete = 0 ) 
  AND ( t2.id IS NULL ) 
  AND ( t1.corp_id = "ding8e9eaf0f82120a5735c2f4657eb6378f" ) 
GROUP BY
  t1.id 
ORDER BY
  t1.create_time DESC

更改后的代码

SELECT COUNT(list.id) as count FROM (
  SELECT t1.id from
      course AS t1 
  LEFT JOIN 
      ( SELECT * FROM lesson WHERE is_delete = 0 ) AS t2 ON t1.id = t2.class_id
  LEFT JOIN 
      (SELECT * FROM class WHERE is_delete = 0 AND corp_id = "${corp_id}") AS t3 ON t2.lesson_id = t3.id 
  WHERE 
      ${text}
  GROUP BY t1.id
) AS list

时间格式

参考

【1】LeetCode
【2】sql中的 IF 条件语句的用法
【3】SQL函数Group_concat用法
【4】MySQL数据库FIND_IN_SET函数的使用
【5】数据库系统概论-第四版-王珊
【6】《数据库系统概论》第五版 +学习笔记总目录
【7】数据库原理及应用东南大学徐立臻（视频）

CS-Base-Knowledge

SQL基础

概论

基本概念

数据管理三个阶段

数据模型

数据模型

概念模型

ER（实体-联系方法）图

数据模型的组成要素

公共表表达式（临时视图） WITH 子句

视图

存储过程

事务

锁

类型

避免死锁

触发器

范式

索引

all

except

UNION

NULL

选择语句

Case when

If

IFNULL

like、RLIKE和正则

IN

函数

group_concat

find_in_set(str,strlist)

concat 和 concat_ws 的区别

CAST

group by 和count的联合使用问题

时间格式

参考