Mysql

学习目标：

学完mysql和安装完。

---

学习内容：

初识 SQL

2.1 概念介绍

SQL 语句可以分为以下三类.：

1. DDL（Data Definition Language，数据定义语言）：
   CREATE ： 创建数据库和表等对象
   DROP ： 删除数据库和表等对象
   ALTER ： 修改数据库和表等对象的结构
   DML
2. DML（Data Manipulation Language，数据操纵语言） 用来查询或者变更表中的记录。DML 包含以下几种指令。
   SELECT ：查询表中的数据
   INSERT ：向表中插入新数据
   UPDATE ：更新表中的数据
   DELETE ：删除表中的数据
   DCL
3. DCL（Data Control Language，数据控制语言） 用来确认或者取消对数据库中的数据进行的变更。除此之外，还可以对 RDBMS 的用户是否有权限操作数据库中的对象（数据库表等）进行设定。DCL 包含以下几种指令。
   COMMIT ： 确认对数据库中的数据进行的变更
   ROLLBACK ： 取消对数据库中的数据进行的变更
   GRANT ： 赋予用户操作权限
   REVOKE ： 取消用户的操作权限

2.2 SQL的基本书写规则
2.3 数据库的创建（ CREATE DATABASE 语句）
CREATE DATABASE shop;
show databases; 显示数据表
2.4 表的创建（ CREATE TABLE 语句）
use+数据库名称：使用此数据库
CREATE TABLE product(
product_id CHAR(4) NOT NULL,
product_name VARCHAR(100) NOT NULL,
product_type VARCHAR(32) NOT NULL,
sale_price INTEGER,
purchase_price INTEGER,
regist_date DATE,
PRIMARY KEY(product_id)：主键
) ;

2.5 命名规则
只能使用半角英文字母、数字、下划线（_）作为数据库、表和列的名称
名称必须以半角英文字母开头

2.6 数据类型的指定
INTEGER 型
CHAR 型
VARCHAR 型
DATE 型

2.7 约束的设置
NOT NULL是非空约束，即该列必须输入数据。
PRIMARY KEY是主键约束，代表该列是唯一值，可以通过该列取出特定的行的数据。

2.8 表的删除和更新

1. 删除表的语法：DROP TABLE < 表名 > ;删除就无法恢复
2. 添加列的 ALTER TABLE 语句：ALTER TABLE < 表名 > ADD COLUMN < 列的定义 >:ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);
3. 删除列的 ALTER TABLE 语句
   ALTER TABLE < 表名 > DROP COLUMN < 列名 >:ALTER TABLE product DROP COLUMN product_name_pinyin;
4. 清空表内容
   TRUNCATE TABLE TABLE_NAME;
   优点：相比drop/delete，truncate用来清除数据时，速度最快。
5. 数据的更新
   
   使用 update 时要注意添加 where 条件，否则将会将所有的行按照语句修改。
   使用 UPDATE 也可以将列更新为 NULL（该更新俗称为NULL清空）。此时只需要将赋值表达式右边的值直接写为 NULL 即可。
   
   
   SET 子句中的列不仅可以是两列，还可以是三列或者更多。

2.9 向 product 表中插入数据



原则上，执行一次 INSERT 语句会插入一行数据。插入多行时，通常需要循环执行相应次数的 INSERT 语句。其实很多 RDBMS 都支持一次插入多行数据。

INSERT 语句中想给某一列赋予 NULL 值时，可以直接在 VALUES子句的值清单中写入 NULL。想要插入 NULL 的列一定不能设置 NOT NULL 约束。

还可以向表中插入默认值（初始值）。可以通过在创建表的CREATE TABLE 语句中设置DEFAULT约束来设定默认值。

可以使用INSERT … SELECT 语句从其他表复制数据。

本课程用表插入数据sql如下：

Drop和delete的区别：前者删整个表，后者只删除数据。

SQL基础查询与排序

一、SELECT语句基础
1.1 从表中选取数据
SELECT语句：
1.2从表中选取符合条件的数据
WHERE语句


1.3 相关法则
星号**（）代表全部列的意思。
SQL中可以随意使用换行符，不影响语句执行（但不可插入空行）。
设定汉语别名时需要使用双引号（"）括起来。
在SELECT语句中使用DISTINCT可以删除重复行**。
注释是SQL语句中用来标识说明或者注意事项的部分。分为1行注释"-- “和多行注释两种”/ */"。

用AS设置别名时，不修改原文件。

二、算术运算符和比较运算符
2.1 算术运算符

2.2 比较运算符


2.3 常用法则
SELECT子句中可以使用常数或者表达式。
使用比较运算符时一定要注意不等号和等号的位置。
字符串类型的数据原则上按照字典顺序进行排序，不能与数字的大小顺序混淆。
希望选取NULL记录时，需要在条件表达式中使用IS NULL运算符。希望选取不是NULL的记录时，需要在条件表达式中使用IS NOT NULL运算符。

三、逻辑运算符
3.1 NOT运算符

想要表示“不是……”时，除了前文的<>运算符外，还存在另外一个表示否定、使用范围更广的运算符：NOT。
NOT不能单独使用：

3.2 AND运算符和OR运算符

当希望同时使用多个查询条件时，可以使用AND或者OR运算符。
AND 相当于“并且”，类似数学中的取交集；
OR 相当于“或者”，类似数学中的取并集。


3.3 通过括号优先处理
AND 运算符优先于 OR 运算符，想要优先执行OR运算，可以使用括号。

3.4 真值表

• AND 运算符**：**两侧的真值都为真时返回真，除此之外都返回假。
• OR 运算符**：**两侧的真值只要有一个不为假就返回真，只有当其两侧的真值都为假时才返回假。
• NOT运算符**：**只是单纯的将真转换为假，将假转换为真。

查询条件为P AND（Q OR R）的真值表

3.5 含有NULL时的真值
NULL的真值结果既不为真，也不为假，因为并不知道这样一个值。
这时真值是除真假之外的第三种值——不确定（UNKNOWN）。

WHERE purchase_price = NULL;WHERE purchase_price <> NULL; WHERE product_name > NULL：一条记录也取不出来，因为不能对null使用比较运算符，只能使用is null和is not null。

四、对表进行聚合查询
4.1 聚合函数
COUNT：计算表中的记录数（行数）
SUM：计算表中数值列中数据的合计值
AVG：计算表中数值列中数据的平均值
MAX：求出表中任意列中数据的最大值
MIN：求出表中任意列中数据的最小值

4.2 使用聚合函数删除重复值

4.3 常用法则
COUNT函数的结果根据参数的不同而不同。
TIPS：COUNT(*)会得到包含NULL的数据行数。
COUNT(<列名>)会得到NULL之外的数据行数。
聚合函数会将NULL排除在外，但COUNT(*)例外，并不会排除NULL。
MAX/MIN函数几乎适用于所有数据类型的列。SUM/AVG函数只适用于数值类型的列。
想要计算值的种类时，可以在COUNT函数的参数中使用DISTINCT。
在聚合函数的参数中使用DISTINCT，可以删除重复数据。

五、对表进行分组

5.1 GROUP BY语句


5.2 聚合键中包含NULL时

将进货单价（purchase_price）作为聚合键举例：

此时会将NULL作为一组特殊数据进行处理。

5.3 GROUP BY书写位置

1. **.**SELECT → 2. FROM → 3. WHERE → 4. GROUP BY

5.4 在WHERE子句中使用GROUP BY

5.5 常见错误

1. 在聚合函数的SELECT子句中写了聚合健以外的列 使用COUNT等聚合函数时，SELECT子句中如果出现列名，只能是GROUP BY子句中指定的列名（也就是聚合键）。
2. 在GROUP BY子句中使用列的别名 SELECT子句中可以通过AS来指定别名，但在GROUP BY中不能使用别名。因为在DBMS中 ,SELECT子句在GROUP BY子句后执行。
3. 在WHERE中使用聚合函数 原因是聚合函数的使用前提是结果集已经确定，而WHERE还处于确定结果集的过程中，所以相互矛盾会引发错误。 如果想指定条件，可以在SELECT，HAVING（下面马上会讲）以及ORDER BY子句中使用聚合函数。

六、为聚合结果指定条件
6.1 用HAVING得到特定分组

可以在GROUP BY后使用HAVING子句。

6.2 HAVING特点

七、对查询结果进行排序
7.1 ORDER BY

默认为升序排列，降序排列为DESC


当用于排序的列名中含有NULL时，NULL会在开头或末尾进行汇总。

7.2 ORDER BY中列名可使用别名
ORDER BY 子句中却可以使用别名：这是因为SQL在使用 HAVING 子句时 SELECT 语句的执行顺序为：
FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY。
ORDER BY中可以使用别名，但是在GROUP BY中不能使用别名。

注意：select-> from -> where -> group by -> having ;因为是文字所以用count。


having里可以考虑降序和升序排序哦！

复杂查询方法-视图、子查询、函数等-天池龙珠计划SQL训练营

3.1 视图
3.1.1 什么是视图
视图是一个虚拟的表。
视图它是虚拟的表。
视图view是动态的。

3.1.2 视图与表有什么区别

3.1.3 为什么会存在视图
主要有以下几点原因：
通过定义视图可以将频繁使用的SELECT语句保存以提高效率。
通过定义视图可以使用户看到的数据更加清晰。
通过定义视图可以不对外公开数据表全部字段，增强数据的保密性。
通过定义视图可以降低数据的冗余。

3.1.4 如何创建视图
创建视图的基本语法如下：


需要注意的是在一般的DBMS中定义视图时不能使用ORDER BY语句！！！数据行都是没有顺序的。

• 基于单表的视图
  

• 基于多表的视图
  
  3.1.5 如何修改视图结构
  ALTER VIEW <视图名> AS <SELECT语句>

• 修改视图
  
  3.1.6 如何更新视图内容
  对于一个视图来说，如果包含以下结构的任意一种都是不可以被更新的：
  聚合函数 SUM()、MIN()、MAX()、COUNT() 等。
  DISTINCT 关键字。
  GROUP BY 子句。
  HAVING 子句。
  UNION 或 UNION ALL 运算符。
  FROM 子句中包含多个表
  视图归根结底还是从表派生出来的，因此，如果原表可以更新，那么 视图中的数据也可以更新。反之亦然，如果视图发生了改变，而原表没有进行相应更新的话，就无法保证数据的一致性了。

• 更新视图
  
  刚才修改视图的时候是设置product_type='办公用品’的商品的sale_price=5000，为什么原表的数据只有一条做了修改呢？
  还是因为视图的定义，视图只是原表的一个窗口，所以它修改也只能修改透过窗口能看到的内容。
  注意：这里虽然修改成功了，但是并不推荐这种使用方式。而且我们在创建视图时也尽量使用限制不允许通过视图来修改表

3.1.7 如何删除视图
DROP VIEW <视图名1> [ , <视图名2> …]

• 删除视图：DROP VIEW productSum;

3.2 子查询
3.2.1 什么是子查询
嵌套查询。
3.2.2 子查询和视图的关系
一次性的，会消失。
3.2.3 嵌套子查询

虽然嵌套子查询可以查询出结果，但是随着子查询嵌套的层数的叠加，SQL语句不仅会难以理解而且执行效率也会很差，所以要尽量避免这样的使用。

3.2.4 标量子查询
所谓单一就是要求我们执行的SQL语句只能返回一个值，也就是要返回表中具体的某一行的某一列

3.2.5 标量子查询有什么用

3.2.6 关联子查询

• 什么是关联子查询
  
• 关联子查询与子查询的联系
  
  敲重点：
  
  
  3.3 各种各样的函数
  算术函数 （用来进行数值计算的函数）
  字符串函数 （用来进行字符串操作的函数）
  日期函数 （用来进行日期操作的函数）
  转换函数 （用来转换数据类型和值的函数）
  聚合函数 （用来进行数据聚合的函数）

3.3.1 算数函数

• • • • /四则运算在之前的章节介绍过
• ABS – 绝对值
  ABS( 数值 )：当 ABS 函数的参数为NULL时，返回值也是NULL。
• MOD – 求余数
  注意：主流的 DBMS 都支持 MOD 函数，只有SQL Server 不支持该函数，其使用%符号来计算余数。
• ROUND – 四舍五入
  语法：ROUND( 对象数值，保留小数的位数 )
  注意：当参数 保留小数的位数 为变量时，可能会遇到错误，请谨慎使用变量。
  
  3.3.2 字符串函数
  
• CONCAT – 拼接
  语法：CONCAT(str1, str2, str3)
• LENGTH – 字符串长度
  语法：LENGTH( 字符串 )
• LOWER – 小写转换
  LOWER 函数只能针对英文字母使用；UPPER 函数用于大写转换。
• REPLACE – 字符串的替换
  语法：REPLACE( 对象字符串，替换前的字符串，替换后的字符串 )
• SUBSTRING – 字符串的截取
  语法：SUBSTRING （对象字符串 FROM 截取的起始位置 FOR 截取的字符数），索引值起始为1。
• （扩展内容）SUBSTRING_INDEX – 字符串按索引截取
  语法：SUBSTRING_INDEX (原始字符串， 分隔符，n)

3.3.3 日期函数

• CURRENT_DATE – 获取当前日期
  
• CURRENT_TIME – 当前时间
  
• CURRENT_TIMESTAMP – 当前日期和时间
  
• EXTRACT – 截取日期元素
  语法：EXTRACT(日期元素 FROM 日期)
  
  3.3.4 转换函数
• CAST – 类型转换
  语法：CAST（转换前的值 AS 想要转换的数据类型）
  
• COALESCE – 将NULL转换为其他值
  语法：COALESCE(数据1，数据2，数据3……)
  COALESCE 是 SQL 特有的函数。该函数会返回可变参数 A 中左侧开始第 1个不是NULL的值。参数个数是可变的，因此可以根据需要无限增加。
  在 SQL 语句中将 NULL 转换为其他值时就会用到转换函数。
  
  3.4 谓词
  3.4.1 什么是谓词
  谓词就是返回值为真值的函数。包括TRUE / FALSE / UNKNOWN。
  谓词主要有以下几个：
  LIKE
  BETWEEN
  IS NULL、IS NOT NULL
  IN
  EXISTS

3.4.2 LIKE谓词 – 用于字符串的部分一致查询

• 前方一致：选取出“dddabc”
  
  其中的%是代表“零个或多个任意字符串”的特殊符号，本例中代表“以ddd开头的所有字符串”。
• 中间一致：选取出“abcddd”,“dddabc”,“abdddc”
  
• 后方一致：选取出“abcddd“
  
• _下划线匹配任意 1 个字符
  
  3.4.3 BETWEEN谓词 – 用于范围查询
  
  BETWEEN 的特点就是结果中会包含 100 和 1000 这两个临界值，也就是闭区间。如果不想让结果中包含临界值，那就必须使用 < 和 >。
  
  3.4.4 IS NULL、 IS NOT NULL – 用于判断是否为NULL
  为了选取出某些值为 NULL 的列的数据，不能使用 =，而只能使用特定的谓词IS NULL。
  
  与此相反，想要选取 NULL 以外的数据时，需要使用IS NOT NULL。
  
  3.4.5 IN谓词 – OR的简便用法
  多个查询条件取并集时可以选择使用or语句。
  
  这时， 我们就可以使用IN 谓词
  `IN(值1, 值2, 值3, …)来替换上述 SQL 语句。
  
  
  需要注意的是，在使用IN 和 NOT IN 时是无法选取出NULL数据的。

3.4.6 使用子查询作为IN谓词的参数

• IN和子查询
  
  
  
• NOT IN和子查询
  NOT IN 同样支持子查询作为参数，用法和 in 完全一样。
  
  3.4.7 EXIST 谓词
  EXIST谓词的使用方法：谓词的作用就是 “判断是否存在满足某种条件的记录”。
  如果存在这样的记录就返回真（TRUE），如果不存在就返回假（FALSE）。
  
• EXIST的参数
  所以，EXIST 只需要在右侧书写 1 个参数，该参数通常都会是一个子查询。
• 子查询中的SELECT *
  由于 EXIST 只关心记录是否存在，因此返回哪些列都没有关系。 EXIST 只会判断是否存在满足子查询，因此，使用下面的查询语句，查询结果也不会发生变化。
  
• 使用NOT EXIST替换NOT IN
  就像 EXIST 可以用来替换 IN 一样， NOT IN 也可以用NOT EXIST来替换。
  
  3.5 CASE 表达式
  3.5.1 什么是 CASE 表达式？
  CASE 表达式是函数的一种。是 SQL 中数一数二的重要功能，有必要好好学习一下。
  CASE 表达式是在区分情况时使用的，这种情况的区分在编程中通常称为（条件）分支。
  CASE表达式的语法分为简单CASE表达式和搜索CASE表达式两种。由于搜索CASE表达式包含简单CASE表达式的全部功能。本课程将重点介绍搜索CASE表达式。
  
  依次判断 when 表达式是否为真值，是则执行 THEN 后的语句，如果所有的 when 表达式均为假，则执行 ELSE 后的语句。

3.5.2 CASE表达式的使用方法




总结：
当待转换列为数字时，可以使用SUM AVG MAX MIN等聚合函数。
当待转换列为文本时，可以使用MAX MIN等聚合函数。

tips:不能加入空数。

集合运算-表的加减法和join等

4.1表的加减法
4.1.1 什么是集合运算
在数学领域表示“各种各样的事物的总和”, 在数据库领域表示记录的集合. 具体来说,表、视图和查询的执行结果都是记录的集合, 其中的元素为表或者查询结果中的每一行。

在标准 SQL 中, 分别对检索结果使用 UNION, INTERSECT, EXCEPT 来将检索结果进行并,交和差运算, 像UNION,INTERSECT, EXCEPT这种用来进行集合运算的运算符称为集合运算符。

4.1.2 表的加法–UNION
4.1.2.1 UNION(并集）


UNION 等集合运算符通常都会除去重复的记录.

4.1.2.2UNION 与 OR 谓词
你会发现, 使用 UNION 对两个查询结果取并集, 和在一个查询中使用 WHERE 子句, 然后使用 OR 谓词连接两个查询条件, 能够得到相同的结果。

4.1.2.3 包含重复行的集合运算 UNION ALL
在 UNION 的结果中保留重复行的语法其实非常简单,只需要在 UNION 后面添加 ALL 关键字就可以了.

【4.1.2.4[扩展阅读]bag 模型与 set 模型】
1.该元素是否至少在一个 bag 里出现过,
2.该元素在两个 bag 中的最大出现次数
Bag 是和 set 类似的一种数学结构, 不一样的地方在于: bag 里面允许存在重复元素,

4.1.2.5隐式类型转换

4.1.3 MySQL 8.0 不支持交运算INTERSECT

4.1.3.1[扩展阅读]bag 的交运算
1.该元素是否同时属于两个 bag,
2.该元素在两个 bag 中的最小出现次数

4.1.4 差集,补集与表的减法

4.1.4.1 MySQL 8.0 还不支持 EXCEPT 运算

4.1.4.2 EXCEPT 与 NOT 谓词

4.1.4.3EXCEPT ALL 与bag 的差
类似于UNION ALL, EXCEPT ALL 也是按出现次数进行减法, 也是使用bag模型进行运算
1.该元素是否属于作为被减数的 bag,
2.该元素在两个 bag 中的出现次数

4.1.4.4INTERSECT 与 AND 谓词

4.1.5对称差
提示: 使用 NOT IN 实现两个表的差集.

4.1.5.1借助并集和差集迂回实现交集运算 INTERSECT
两个集合的交可以看作是两个集合的并去掉两个集合的对称差

4.2连结(JOIN)

4.2.1 内连结(INNER JOIN)

4.2.1.1 使用内连结从两个表获取信息

要点一: 进行连结时需要在 FROM 子句中使用多张表.

要点二:必须使用 ON 子句来指定连结条件.
要点三: SELECT 子句中的列最好按照 表名.列名 的格式来使用.

4.2.1.2 结合 WHERE 子句使用内连结


4.2.1.3结合 GROUP BY 子句使用内连结
结合 GROUP BY 子句使用内连结, 需要根据分组列位于哪个表区别对待.

4.2.1.4自连结(SELF JOIN)
4.2.1.5内连结与关联子查询

4.2.1.6自然连结(NATURAL JOIN)

试写出与上述自然连结等价的内连结.

使用自然连结还可以求出两张表或子查询的公共部分


4.2.1.7使用连结求交集

4.2.2 外连结(OUTER JOIN)
内连结会丢弃两张表中不满足 ON 条件的行,和内连结相对的就是外连结. 外连结会根据外连结的种类有选择地保留无法匹配到的行.

4.2.2.1 左连结与右连接
4.2.2.2 使用左连结从两个表获取信息
●外连结要点 1: 选取出单张表中全部的信息
●外连结要点 2:使用 LEFT、RIGHT 来指定主表.

4.2.2.3结合 WHERE 子句使用左连结

4.2.2.4在 MySQL 中实现全外连结
MySQL8.0 目前还不支持全外连结, 不过我们可以对左连结和右连结的结果进行 UNION 来实现全外连结。

4.2.3多表连结
4.2.3.1 多表进行内连结

4.2.3.2多表进行外连结

4.2.4ON 子句进阶–非等值连结
4.2.4.1非等值自左连结(SELF JOIN)

4.2.5 交叉连结—— CROSS JOIN(笛卡尔积)

4.2.5.1[扩展阅读]连结与笛卡儿积的关系

然后对上述笛卡尔乘积增加筛选条件 SP.product_id=P.product_id, 就得到了和内连结一致的结果:

4.2.6 连结的特定语法和过时语法

内连接：inter join on:自然连接要求是同名属性列的比较，而内连接则不要求两属性列同名。
sql语句：Select …… from 表1 inner join 表 2 on 表1.A=表2.E

左外连接（left outer join)：左外连接是在两表进行自然连接，只把左表要舍弃的保留在结果集中，右表对应的列上填null。
sql语句：Select …… from 表1 left outer join 表2 on 表1.C=表2.C

右外连接(rignt outer join)
右外连接是在两表进行自然连接，只把右表要舍弃的保留在结果集中，左表对应的列上填null。
Select …… from 表1 rignt outer join 表2 on 表1.C=表2.C

全外连接(full join)：
全外连接是在两表进行自然连接，只把左表和右表要舍弃的都保留在结果集中，相对应的列上填null。
Select …… from 表1 full join 表2 on 表1.C=表2.C

自然连接（natural join）:自然连接是一种特殊的等值连接，他要求两个关系表中进行比较的必须是相同的属性列，无须添加连接条件，并且在结果中消除重复的属性列。
sql语句：Select …… from 表1 natural join 表2

非等值连接:SELF JOIN：on 里面的变动

交叉连接：cross join+where =内连接

窗口函数等

5.1窗口函数
5.1.1窗口函数概念及基本的使用方法
窗口函数也称为OLAP函数。OLAP 是OnLine AnalyticalProcessing 的简称，意思是对数据库数据进行实时分析处理。
窗口函数的通用形式：

PARTITON BY是用来分组
ORDER BY是用来排序


5.2窗口函数种类
一是 将SUM、MAX、MIN等聚合函数用在窗口函数中
二是 RANK、DENSE_RANK等排序用的专用窗口函数

5.2.1专用窗口函数
RANK函数**（英式排序）:如果存在相同位次的记录，则会跳过之后的位次。
DENSE_RANK函数（中式排序）**:存在相同位次的记录，也不会跳过之后的位次。
ROW_NUMBER函数:赋予唯一的连续位次。

5.2.2聚合函数在窗口函数上的使用
聚合函数在开窗函数中的使用方法和之前的专用窗口函数一样，只是出来的结果是一个累计的聚合函数值。

5.3窗口函数的的应用 - 计算移动平均


5.3.1窗口函数适用范围和注意事项
原则上，窗口函数只能在SELECT子句中使用。
窗口函数OVER 中的ORDER BY 子句并不会影响最终结果的排序。其只是用来决定窗口函数按何种顺序计算。

5.4GROUPING运算符
5.4.1ROLLUP - 计算合计及小计

---

学习时间：

提示：这里可以添加计划学习的时间
例如：
1、 2021/5/12-2021/5/13

---

学习产出：

提示：这里统计学习计划的总量
例如：
1、 技术笔记 1 遍