【SQL基础教程】【四至七章】总结归纳与思维导图

SQL基础教程

第四章 数据更新

4.1 数据的插入INSERT

• 4.1.1 基本语法

  • （1）INSERT INTO <表名> （列名1，列名2，……） VALUES （值1，值2，……）;
  • （2）插入多行时可以省略之后的VALUES标识，直接括号打过去

• 4.1.2 列清单的省略

  • 全列INSERT时直接省略列名清单

• 4.1.3 插入NULL与默认值

  • （1）NULL直接写入清单，注意不能插入NOT NULL列
  • （2）默认值通过DEFAULT写入，或在清单中省略对应列

• 4.1.4 从其他表中复制数据

  • （1）注意是从其他表中复制数据，不能贪求将复制数据与插入数据一行语句
  • （2）INSERT INTO <表名> （列名1，列名2，……） SELECT（值1，值2，……） FROM <表名> （WHERE 子类的限制） ;

4.2 数据的删除DELETE

• 4.2.1 DROP与DELETE区别

  • （1）DROP TABLE <表名> 为完全删除表
  • （2）DELETE FROM <表名> 为清空表的数据

• 4.2.2 条件删除

  • DELETE FROM <表名> WHERE <条件>；

• 4.2.3 清空数据

  • TRUNCATE <表名>；

4.3 数据的更新UPDATE

• 4.3.1 基本语法

  • （1）全列更新：UPADATE <表名> SET <列名> = <表达式>；
  • （2）指定更新：UPADATE <表名> SET <列名> = <表达式> WHERE <条件>；
  • （3）表达式可以为NULL，但是注意列格式是否为NOT NULL

• 4.3.2 多列更新

  • （1）多条合并1：UPADATE <表名> SET <列名1> = <表达式1> , <列名2> = <表达式2>，…… WHERE <条件>；
  • （2）多条合并2：UPADATE <表名> SET （<列名1> ，<列名2>，…… ） =（ <表达式1> , <表达式2>，…… ）WHERE <条件>；

4.4 事务

• 4.4.1 定义：需要在同一个处理单元中执行的一系列更新处理的集合，针对DML语句（INSERT，UPDATE，DELETE）

• 4.4.2 创建事务

  • （1）START/BEGIN TRANSACTION；
  • （2）开始事务将会停止自动提交COMMIT（自动调用SET AUTOCOMMIT = 0）；

• 4.4.3 提交与回滚

  • （1）COMMIT：提交所有DML语句，使其正式实现
  • （2）ROLLBACK：回滚到START/BEGIN TRANSACTION时的状态

第五章 复杂查询

5.1 视图

• 5.1.1 定义：视图为临时表，其存储的为从表中取出数据的SELECT语句，其中的数据可以支持DML操作

• 5.1.2 创建视图

  • （1）语法：CREATE VIEW 视图名称（<视图列名1>，<视图列名2>，……）AS <SELECT语句>
  • （2）多重视图即为从视图上创建视图，其会降低SQL性能，尽量避免使用
  • （3）创建时无法使用ORDER BY 语句，因为数据行时无序的

• 5.1.3 更新视图

  • （1）视图和表需要同时更新，因此通过汇总（GROUP BY）得到的视图无法进行更新
  • （2）没有通汇总组成的视图，可以根据数据类型随意使用DML语句

• 5.1.4 删除视图

  • 语法：DROP VIEW 视图名称（<视图列名1，视图列名2>，……）

5.2 子查询

• 5.2.1 定义：

  • 一次性的视图，SELECT包含子SELECT。可以理解为对视图进行查询，所以需要AS来提供子查询的名称

• 5.2.2 语法

  • （1）语法：SELECT <列名> FROM （子查询1（子查询2） AS <别名1>）AS <别名2>；（最内层子查询可不用AS，外层必须AS）
  • （2）示例：SELECT cust_id FROM ( SELECT order_num, cust_id FROM orders AS table1) AS table2 WHERE cust_id = 10001;

• 5.2.3 标量子查询

  • （1）定义：必须而且只能返回1行1列的结果，可用于常规使用单一变量的地方
  • （2）其特色在于只返回一个值，既可以当做变量对待，当不小心使用了GROUP BY之类的操作使其变为普通子查询则失效

• 5.2.4 关联子查询

  • （1）定义：关联子查询即在子查询中添加与父查询中相关联的WHERE子句条件，这样就会使得两者的相互调用是多次单行单行的关联查询，并非一次性多行查询
  • （2）理解深度：由于父查询的条件如A.as=B.as在子查询中存在，子查询在执行完一类as时，就会遍历父查询的所有as来进行下一步计算，此时对于父查询来说其接受的参数就是一行数据（即一个标量子查询），所以可以实现正常流程。这说穿了就是两个for循环，其中大for循环只能接受单一的数据，即标量子查询。
  • （3）关联名称与作用域：关联名称存在自己以及向下的作用域中，所以如果有关联条件最好放到最子层的WHERE中

第六章 函数、谓词、CASE表达式

6.1 函数

• 6.1.1 算术函数

  • 加减乘除，ABS（数值）绝对值，MOD（被除数，除数）求余，ROUND（对象数值，保留小数的位数）四舍五入；

• 6.1.2 字符串函数

  • CONTACT（str1，str2，str3）字符串拼接，LENGTH，LOWER，REPLACE，SUBSTRING，UPPER，CURRENT_DATE/TIME/TIMESTAMP，EXTRACPT（日期元素 FROM 日期）

• 6.1.3 转换函数

  • CAST（转换前值 AS 转换后类型），COALESCE（数据1，数据2，……）将NULL转换为其他值便于计算

6.2 谓词

• 6.2.1 LIKE

  • （1）百分号%通配符：表示任何字符出现任意次数
  • （2）下划线_：匹配单个任意字符
  • （3）语法：SELECT <列名> FROM <表名> WHERE <列名> LIKE <表达式>

• 6.2.2 BETWEEN

  • 语法：SELECT <列名> FROM <表名> WHERE <列名> BETWEEN <表达式1> AND <表达式2>，范围为表1到表2

• 6.2.3 IS NULL，IS NOT NULL

  • 语法：SELECT <列名> FROM <表名> WHERE <列名> IS NULL / IS NOT NULL

• 6.2.4 IN

  • IN（A,B,C,D）选择的是ABCD这些数据

• 6.2.5 EXISTS

  • 常使用关联子查询作为其参数，用于确定WHERE条件中某条件是否成立

6.3 CASE表达式

• 6.3.1 搜索CASE

  • CASE WHEN <求值表达式> THEN <表达式>
    WHEN <求值表达式> THEN <表达式>
    ELSE <表达式>
    END

• 6.3.2 简单CASE

  • CASE <表达式>
    WHEN <求值表达式> THEN <表达式>
    WHEN <求值表达式> THEN <表达式>
    ELSE <表达式>
    END

第七章 集合运算

7.1 表的加减法

• 7.1.1 加法UNION

  • （1）语法：SELECT <列名1> FROM <表名> UNION SELECT <列名1> FROM <表名> （自动去除重复的记录）
  • （2）运算的列数应该相同，对应的列类型应该一致，ORDER BY只能在最后的时候使用一次
  • （3）UNION ALL包含重复的集合运算
  • （4）INTERSECT选取表中的公共部分（取并集）

• 7.1.2 减法EXCEPT

  • （1）语法：SELECT <列名1> FROM <表名> EXCEPT SELECT <列名1> FROM <表名> （自动去除重复的记录）
  • （2）A减B，即取A与B的差集。MySQL不支持减法

7.2 联结

• 7.2.1 联结定义

  • JOIN为将其他表的列添加过来，进行“添加列”的运算，UNION为“添加行”操作

• 7.2.2 内联结INNER JOIN

  • （1）定义：以A中的列为桥梁，将B中满足同样条件的列汇集到同一结果之中（即只选取满足联结键的数据）
  • （2）语法：SELECT <带表列名1> <带表列名2> FROM <表1> AS <表1别名> INNER JOIN <表2> AS <表2别名> ON <表1内联列> = <表2内联列> （WHERE子句，必须在联结键之后）（若想重复使用，可创建为视图）
  • （3）关键问题：别名可省略；ON后为联结键；使用联结时SELECT的列尽量使用<表名/别名.列名>方式书写
  • （4）左右理解：INNER JOIN右不分主表分表，但SELECT显示结果根据SELECT语句顺序而定

• 7.2.3 外联结OUTER JOIN

  • （1）定义：选取每张表中的全部信息，即使不满足联结键也选取，但对应值为NULL
  • （2）语法：SELECT <带表列名1> <带表列名2> FROM <表1> AS <表1别名> LEFT/RIGHT OUTER JOIN <表2> AS <表2别名> ON <表1内联列> = <表2内联列> （WHERE子句，必须在联结键之后）（若想重复使用，可创建为视图）
  • （3）主表：LEFT OUTER JOIN 即左侧为主表，RIGHT 即右侧为主表，主表将会被选取全部信息而分表不会
  • （4）多表联结语法：SELECT 中选取全部所需列，FROM后的 <表X>到ON<联结键> 看做一个表，对此表再进行联结

• 7.2.4 交叉联结 CROSS JOIN

  • （1）语法：内联结中的INNER JOIN修改为CROSS JOIN即可，采用联结写法但是不写JOIN类型和ON条件，交叉联结为默认
  • （2）所得结果为笛卡尔积，即联结左侧的行数乘以联结右侧的行数
  • （3）除法过程复杂，可以将被除数理解为笛卡尔积，商和除数分别为所得结果与限制条件

对应思维导图