《SQL初学者指南》读书笔记

Awey
1,547 阅读31分钟

关系型数据库和SQL

  • SQL语言的三个部分
    • DML：Data Manipulation Language，数据操纵语言，检索、修改、增加、删除数据库（表）中的数据
    • DDL：Data Definition Language，数据定义语言，创建和修改数据库（表）本身
    • DCL：Data Control Language，维护数据库安全
  • 关系型数据库基本术语
    • relational（关系）：表示各表彼此关联
    • record（记录）：表中的行（row）
    • field（字段）：表中的列（column）
    • primary key：表的主键，通常为自增型（auto-increment），本身没有特殊含义，只用于保证每一行都有一个唯一的值
    • foreign key：表的外键，确保这一列有一个有效的值，通常会把某个其他表的共同列，通常是主键作为外键，比如订单表中的客户列
    • column（field）的数据类型（更详细的数据类型介绍参见SQL数据类型）
      • 数字
      • bit：位，只允许0和1
      • integer：整数，不含小数位
      • decimal：浮点数，含小数位
      • real number：实数
    • 字符：string/character string类型，在SQL语句中需要用引号括起来
    • 日期/时间：用于表示日期和时间，在SQL语句中需要用引号括起来，允许对所涉及的日期进行特殊运算
    • NULL：空值，非数据类型，而是在允许包含空值的列中表示空置

基本数据检索

  • SQL语句不区分大小写，且可以写成任意多行
  • 可以把重要关键字作为单独一行凸显语义
  • 从表中选择所有：SELECT * FROM tablename
  • 指定列：SELECT columnname FROM tablename
  • 指定多个列：SELECT column1, column2 FROM tablename
  • 带空格的列名：Microsoft SQL Server使用方括号[]，MySQL使用重音符`，Oracle使用双引号

计算字段和别名

  • 计算字段
    • 直接量：与表中数据没有任何关系的值叫做literal value（直接量），字符串直接量需要加引号，数字直接量不需要
    • 算数运算：允许使用列数据与直接量或其它列数据进行加减乘除运算，比如SELECT QuantityPurchased * PricePerItem FROM Orders
    • 连接字段：把字符数据连接或组合到一起，Microsoft SQL Server使用加号+，MySQL使用CONCAT()函数连接字符串，Oracle使用双竖线||
  • 别名
    • 列的别名：用于修改列（表头）标题或为计算字段提供列（表头）标题，比如SELECT f_n AS 'first_name' from customers
    • 表的别名：通常有三种情况需要修改表名称，a.不好理解或复杂的表名，b.从多个表中进行选择，c.使用子查询；SELECE something from someshittablename AS another_table_name

使用函数

  • Scalar function：标量函数，针对单行中的数据执行
  • Aggregate function：聚合函数，针对较大的数据集合进行操作
  • 字符函数
    • LEFT/RIGHT (string, numberOfCharactors)：从左/右取字符串数据的指定位数，在Oracle中以SUBSTR替代
    • SUBSTRING (string, start, end)：取得字符串数据的子字符串，在Oracle中以SUBSTR替代
    • LTRIM/RTRIM (string)：删除字符串数据左侧/右侧的空格
    • CONCAT (string1, string2, string3 ...)：拼接多个字符串，Oracle中只允许拼接两个字符串
    • UPPER/LOWER (string)：返回字符串的大/小写
  • 复合函数：函数的嵌套使用被称为复合函数，比如RIGHT(RTRIM(something)) AS 'something'
  • 日期时间函数
    • GETDATE/NOW/CURRENT_DATE ()：三个函数都用于获取当前时间，对应Microsoft SQL Server/MySql/Oracle三家数据库的实现
    • DATEPART (date_part, date_value)：单独返回某个部分的时间，date_part为需要返回的时间部分，date_value为原始时间，MySQL的实现为DATE_FORMAT(date_value, date_format),date_value为原始时间，date_format为类似于%d这样的格式用于告诉函数需要返回哪部分时间，date_part的有效值为：year/quarter/month/dayofyear/day/month/hour/minute/second
    • DATEDIFF (date_part, start_date, end_date)：用于计算任意两个不同日期间相差的时间，在MySQL中该函数之允许计算天数差异，所以date_part参数不可用，仅需要传入两个日期即可
  • 数值函数（数学函数）
    • ROUND (NumbericValue, DecimalPlaces)：对任意数进行四舍五入，NumbericValue表示要四舍五入的数，DecimalPlaces表示从第几位开始四舍五入（即需要保留到第几位），以十分位为0，向左为负数，向右为正数
    • RAND ([seed])：产生一个随机数 ，可选的seed参数为一个整数，用于每次返回相同的值
    • PI ()：返回数学运算中的pi值
  • 转换函数
    • CAST (expression AS data_type)：将数据从一种类型转换为另一种类型，expression表示数据或函数表达式，data_type表示需要转换到的数据类型，一般情况下SQL在做计算时会进行自动的类型转换，所以很少用到这个函数，它的典型使用场景是当一个日期被保存成了字符串，需要转换为真正的日期数据：CAST('2017-5-1', AS DATETIME)，Oracle中该函数的参数形式会不一样
    • ISNULL/IFNULL/NVL (column_data_maybe_null, if_null_will_use_this_data )：将NULL值转换为一个有意义的值，对应Microsoft SQL Server/MySql/Oracle三家数据库的实现

排序数据

排序数据的语法如下：

SELECT
column1,
column2
FROM table1, table2
ORDER BY column3, column2 DESC
  • ORDER BY 句子总是在FROM子句之后，FROM子句总是在SELECT关键字之后
  • SELECT和ORDER BY后面指定的列，可能是完全不同的一些列
  • 使用关键字ASC和DESC来升序/降序排列
  • ORDER BY后指定了多列，则首先按照第一列排序，如遇相同则相同的行按第二列排序，以此类推
  • 根据计算字段排序时，如果计算字段已经出现在SELECT关键字后，则只需要在ORDER BY子句中指定计算字段的别名即可（经测试在MySQL中如果计算字段别名带有空格则暂时无法在此引用，因为不能使用引号），如果计算字段没有出现在SELECT关键字后，则可直接在ORDER BY子句中指定一个计算字段，例如：
    SELECT
title,
rental_duration,
rental_rate
FROM film
ORDER BY rental_duration * rental_rate DESC
  • 按升序排列时，大部分的SQL数据库都会是按NULL（Oracle中排在最后，可使用NULLS FIRST关键字来强制最先）-数字-字符（字符中首先显示数字字符，再显示普通字符，除Oracle外不区分大小写）来进行排序，反之亦然。

基于列的逻辑

  • 基于列的逻辑主要用于根据逻辑条件改变想要展现给用户的输出
  • 简单格式：判断某列的值为某个具体值后将其转换为一个其它值
SELECT column1, column2
CASE column3
WHEN value1 THEN result1
WHEN value2 THEN value2
(repeat WHEN-THEN any number of times)
[ELSE defaul_result]
END
column4
FROM tablename
  • 查询格式：判断一列或多列中的某个值是否符合某个条件而将其转换为一个其它值并显示在一列中
SELECT
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN  result2
(repeat WHEN-THEN any number of times)
[ELSE defaul_result]
END AS custom_column_name,
FROM tablename

# 最新版的MySQL语法与书中的语法有细微差别：
# ELSE子句最后不需要逗号

SELECT
title,
CASE
WHEN rental_duration = 3 THEN 'Three Day Left'
WHEN rental_rate = 0.99 THEN 'Cheapest'
ELSE 'Normal'
END AS 'Rental Status'
FROM film

基于行的逻辑

  • 基于行的逻辑主要用于获取满足特定条件的数据
  • 应用查询条件
    • SQL中的查询条件从WHERE子句开始
    • WHERE子句总是在FROM和ORDER BY 子句之间，实际上任何“子句”都必须按照这个顺序来
      SELECT columnList
FROM tableList
WHERE condition
ORDER BY columnList
  • WHERE子句操作符，以下这些操作符都可以在基于列的逻辑CASE WHEN condition语句中使用
    • 等于：=
    • 不等于：<>
    • 大于：>
    • 小于：<
    • 大于等于：>=
    • 小于等于：<=
    SELECT
first_name,
last_name
FROM actor
WHERE age > 18
  • 限制行
    • 使用TOP/LIMIT/ROWNUM（对应Microsoft SQL Server、MySQL和Oracle）限制行数（关键字TOP返回的行，并不是真的随机样本，而是根据数据库中的物理存储方式限定了前几行数据而已）
      # Microsoft SQL Server
SELECT
TOP number
columnList
FROM table

# MySQL
SELECT
columnList
FROM table
LIMIT number

# Oracle
SELECT
columnList
FROM table
WHERE ROWNUM <= number
    • 结合SORT BY子句做“Top N”查询（基于特定分类，得到带有最大/小值的一定数量的行）
      # 本月卖得最好的莎士比亚的三本书
# MySQL
SELECT
title AS 'Book Title',
current_month_sale AS 'Quantity Sold'
FROM books
WHERE author = 'Shakespear'
LIMIT 3
ORDER BY current_month_sale DESC

# Oracle中的TOP N查询需要用到子查询，后文会细讲
SELECT * FROM
(SELECT
title AS 'Book Title',
current_month_sale AS 'Quantity Sold'
FROM books
ORDER BY current_month_sale)
WHERE ROWNUM <= 3

布尔逻辑（更复杂的基于行的逻辑）

  • 使用与AND、或OR、非NOT三个关键字在WHERE子句中表示布尔逻辑。与其它语言的计算顺序一样，AND的优先级最高，OR其次，NOT优先级最低，也可以使用()来改变三者的计算顺序
    # 这个例子仅为展示布尔逻辑，实际使用不应该这么绕
SELECT
first_name,
last_name,
age
FROM actors
WHERE
NOT(
(age < 18 OR age > 60)
AND last_name = 'Jhon'
)
  • 还有两个表示范围的操作符BETWEEN和IN，用于替代column >= range_bottom AND column <= range_top和column = value1 OR column = value2这样的特例，简化SQL语句的编写
    # BETWEEN，等价于 age >= 18 AND age <= 60
SELECT
first_name,
last_name,
age
FROM actors
WHERE
age BETWEEN 18 AND 60

# IN，等价于 state = 'IL' AND state = 'NY'
SELECT
customer_name,
state
FROM orders
WHERE state IN ('IL', 'NY')
  • 使用IS NULL在WHERE子句中判断一个列是否为空，也可以与函数ISNULL(column, value)结合使用
    # 选取重量为0或者没有填写重量的产品
SELECT
product_description,
weight
FROM products
WHERE weight = 0
OR weight IS NULL

# 使用ISNULL等价的写法
SELECE
product_description,
weight
FROM products
WHERE ISNULL(weight, 0) = 0

# IS NULL和ISNULL
SELECT
product_description,
ISNULL(weight, 0) AS 'weight'
FROM products
WHERE weight = 0
OR weight IS NULL

模糊匹配

  • WHERE子句可以使用LIKE操作符来查找针对列值的某部分匹配
    • 包含某关键字：
      SELECT 
  title
FROM
  film
WHERE
  title LIKE '%love%'
    • 以某关键字开头：
      SELECT
  title
FROM
  film
WHERE
  title LIKE 'love%'
    • 以某关键字结尾
      SELECT
  title
FROM
  film
WHERE
  title LIKE '%love'
    • 包含某关键字但不以其开头也不以其结尾（未能在MySQL 4中验证，只验证通过了单独的不以某字符串开头，或者不以某字符串结尾两种情况）
      SELECT
  title
FROM
  film
WHERE
  title LIKE '% love %'
  • 通配符
    符号 含义
    % 任意个任意字符
    _ 一个任意字符
    [characterlist] 一个指定字符列表中的字符（在MySQL和Oracle中没有）
    [^charactorlist] 一个非指定字符列表中的字符（在MySQL和Oracle中没有）
  • NOT操作符可以和LIKE操作符组合使用，例如
    SELECT
first_name,
last_name
FROM actor
WHERE first_name LIKE '%ARY%'
AND last_name NOT LIKE '[MG]ARY'
  • 按照读音匹配（不常用也不好用）
    • SOUNDEX函数：能够输出一个表示字符读音的四字代码（以首字母开头，然后删去剩余字符中所有元音和字母y，最后转换为一个三位数的数字用于表示读音，最后输出类似S530）
    • DIFFERENCE函数：可以和SOUNDEX函数一起使用（仅Microsoft SQL Server支持），检查两个字符的SOUNDEX相似度并返回一个表示读音相近度的数字，（两个字符的SOUNDEX值共有四个字符，每有一个位置的字符相等，则结果自增1，所以DIFFERENCE函数的返回值只有0到4五个可能的数字，越大越相近，越小越不同

汇总数据

  • 消除重复：使用DISTINCT关键字来删除输出重复的行
    # 查看所有艺术家（没有显示相同艺术家的行）
SELECT
DISTINCT
artist
FROM songs
ORDER BY artist

# 查看所有艺术家和专辑的唯一组合（没有显示同一艺术家和同一专辑的行，每一行中艺术家和专辑的组合是唯一的）
SELECT
DISTINCT
artist,
album
FROM songs
ORDER BY artist, album
  • 聚合函数：标量函数只能针对单个的数字或值进行计算，而聚合函数则可以用于分组数据
    函数 解释
    SUM 合计、加总
    AVG 平均值
    MIN 最小值
    MAX 最大值
    COUNT 数量 
    # 总值、均值、最大值、最小值
SELECT
SUN(fee) AS 'Total Gym Fees'
AVG(grade) AS 'Average Quiz Score'
MIN(grade) AS 'Minimum Quiz Score'
MAX(grade) AS 'Maximum Quiz Score'
FROM grades
WHERE grade_type = 'quiz'

# 返回所有选中行的数目
SELECT
COUNT(*) AS 'Count of Homework Rows'
FROM grades
WHERE grade_type = 'homework'

# 返回指定列中存在值的行的数目
SELECT
COUNT(grade) AS 'Count of Homework Rows'
FROM grades
WHERE grade_type = 'homework'

# 与DISTINCT配合返回指定列中唯一值的行数
SELECT
COUNT(DISTINCT fee_type) AS 'Number of Fee Types'
FROM Fees
  • 分组数据：以指定列为依据对所有选中行进行分组，重新划分了行的显示规则
    • 单列分组
      # 统计每个分级下的电影数量
SELECT 
rating, COUNT(rating) AS 'Rating Count'
FROM
film
GROUP BY rating
    • columnlist中的所有列，要么是GROUP BY子句中的列，要么是在聚合函数中使用的列，因为所有内容都在组中出现，不在组中的内容没有办法处理，这种情况下MySQL与其它两种数据库不同，它只会得出错误的结果，而不会报错
    • 多列分组：组的概念可以扩展，从而根据多列进行分组
      # 统计不同租金率下的不同分级电影的数量
SELECT 
rating, 
rental_rate,
COUNT(rating) AS 'Rating Count'
FROM film
GROUP BY rating, rental_rate
    • 在没有分组的情况下，聚合函数（SUM、AVG、MIN、MAX、COUNT）统计的是所有行的数据，在有分组的情况下，这些聚合函数则仅会统计组内的数据，当然实际上也是最终显示的表的每一行的聚合
    • GROUP BY子句中的columnlist顺序没有意义，但ORDER BY子句中的顺序是有意义的，一般按照排序的优先顺序来列出这些列会很有帮助（也即SELECT中的columnlist与ORDER BY中的columnlist保持一致）
  • 基于分组应用查询条件：WHERE子句中的查询条件是针对单独的行来应用的，如果存在GROUP BY分组，就需要使用HAVING关键字了
    # 查看分级中所有电影平均时长大于100分钟的分级中电影的数量
SELECT 
  rating AS '分级',
  COUNT(title) AS '电影数量',
  AVG(length) AS '平均时长'
FROM
  film
GROUP BY rating
HAVING AVG(length) > 100
ORDER BY 电影数量 DESC
  • 至此，SELECT语句的一般格式如下：
    SELECT
  columnlist
FROM
  tablelist
WHERE
  condition
GROUP BY
  columnlist
HAVING
  condition
ORDER BY
  COLUMNLIST

用内连接来组合表

  • 关系型数据库最重要的成就是能够把数据组织到任意多个相互关联的表中，但同时这些又是彼此独立的；人们可以分析业务实体然后进行适当的数据库设计，这样就可以具有最大的灵活性；关系型数据库可以以任何你想要的方式把代表业务实体的表连接到一起，从而实现“关系”
  • 类似“客户”和“订单”这样两个独立的实体信息，至少应该要拆分到两个表中（订单表很有可能需要继续拆分成多个表），可以使用实体关系图（entity-relationship diagram）来表示可视化地表示两个表以及他们之间存在的隐性关系，实体（entity）指的是表，关系（relationship）指的是这些表中数据元素之间所画的线
    实体关系图
  • 内连接
    • 使用关键字INNER JOIN来指定想要连接的第二个表，使用ON来指定两个表的共同列由于共同列名称是一样的，所以需要在列名前面使用表名作为命名空间来区分两个表中独立的列
      # 简单地依据customer_id将顾客表和订单表拼接到一个表中
SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id
    • 内连接只会返回关联的两个表之间相匹配的数据，表在FROM和INNER JOIN之间的顺序仅会影响哪个表的列先显示，不会影响行的顺序
    • SQL不是过程式语言，不会指定任务的先后顺序，而只是指定需要的逻辑并让数据库内部机制去决定如何执行任务。
    • 仅使用FROM和WHERE也可以指定表的内连接，这是内连接的另一种范式，但因其没有显示地表示出连接的逻辑，所以不推荐使用（所以其实INNER JOIN ON的唯一作用仅仅是表达语义而已）
      SELECT *
FROM customers, orders
WHERE customers.customer_id = orders.customer_id
    • 可以通过显式地指定表的别名和列的别名（注意Oracle中表的别名与其他两个库的区别，前文有提及），来去除内连接后的重复列或者只显示需要的列，这是推荐的做法：
      SELECT
c.customer_id AS 'Customer Id',
c.first_name AS 'First Name',
c.last_name AS 'Last Name',
o.order_id AS 'Order Id',
o.quantity AS 'Quantity',
o.price_per_item AS 'Price'
FROM customers AS 'c',
INNER JOIN

用外连接来组合表

  • SQL中表连接的默认类型就是内连接，所以可以只使用JOIN来指定一个内连接
  • 外连接有三种类型：左连接LEFT OUTER JOIN，右连接RIGHT OUTER JOIN，全连接FULL OUTER JOIN，其中关键字OUTER并不是必须的。
  • 所以总结起来有4种类型的连接：
    连接类型 全称 简写 用途
    内连接 INNER JOIN JOIN 两个表都是主表（primary table），共同列中所有的行都必须同时在这两个表中才会被选中
    左连接 LEFT OUTER JOIN LEFT JOIN 左表为主表，右表为从表（secondary table），选中共同列中所有在主表中的行，不管它是否出现在从表
    右连接 RIGHT OUTER JOIN RIGHT JOIN 左表为从表，右表为主表，规则同左连接
    全连接 FULL OUTER JOIN FULL JOIN 两个表都是从表，共同列中的行只要出现在任意一个表中都会被选中
  • 在实体关系图中，单向箭头表示表之间的连接是单向的，箭头终点的表中有一列所有行都能在箭头起点的表中找到，但反过来则不一定，比如，不是所有的客户都有订单，且一个客户可能有多个订单，但所有的订单都会有客户信息（甚至可以说所有的订单有且只有一个客户信息），退货信息与订单的关系类似
  • 当连接主表和从表时，我们需要主表中所有的行，即使在从表中的共同列没有任何行与之匹配
  • 使用IS NOT NULL和IS NULL来过滤空行或显示空行
    # 过滤了没有订单的客户和有退货的订单
SELECT
customers.first_name AS 'First Name',
customers.last_name AS 'Last Name',
orders.order_date AS 'Order Date',
orders.order_amount AS 'Order Amt'
FROM customers
LEFT JOIN orders
ON orders.customer_id = customers.customre_id
LEFT JOIN refunds
ON orders.order_id = refunds.order_id
WHERE orders.order_id IS NOT NULL
AND refunds.refund_id IS NULL
ORDER BY customers.customer_id, orders.order_id
  • 右连接与左连接唯一的不同就是主从表在关键字前后的位置，所以基本上没有必要使用右连接，建议只使用左连接，因为人直觉上往往认为先列出的表更为重要
  • 当设计有多个表的复杂FROM子句时，建议仅使用关键字LEFT JOIN并且避免使用圆括号
  • 全连接会显示所有行，即使没有在其他表中找到任何一个匹配。但在实际工作中很少会用到全连接，因为表之间的这种类型的关系是非常少见的。

自连接和视图

  • 自连接：处理那些本质上是自引用的表（表中的一列指向自己的另一列，比如员工表中的manager列指向自己的employee_id，员工的经理也是员工），为其创建多个视图
    • 可以使用四种表连接中的任意一种来实现自连接，唯二的区别就是ON子句中，非自连接的共同列来自两个表，自连接的共同列来自同一个表，所以这时候需要在FROM关键字和JOIN关键字后为该表各自创建一个别名用以在ON子句中进行区分
    # 列出personnel表中所有员工的经理名字
SELECT
employees.employee_name AS 'Employee Name',
managers.employee_name AS 'Maneger Name'
FROM personnel AS 'employees'
LEFT JOIN personnel AS 'managers'
ON employees.manager_id = managers._employee_id
ORDER BY employee.employee_id
  • 视图
    • 视图只是保存在数据库中的SELECT语句，它不包含任何数据。
    • 随着时间的流逝，访问数据的需求会有所变化，但有时很难去重新组织数据库中的数据以满足新的需求。视图允许为数据库中已经存在的数据创建新的虚拟视图（或理解为虚拟的表）而无需重新组织数据，这为我们增加了始终能保持数据库设计不断更新的能力。
    • 因为视图没有保存物理数据，所以在视图中不能包含ORDER BY子句
  • 创建视图
    # 创建视图的语法：
CREATE VIEW view_name AS
select_statement

# 一个创建视图的例子，注意不能有ORDER BY子句
CREATE VIEW customers_orders_refunds AS
SELECT
customers.first_name AS 'First Name',
customers.last_name AS 'Last Name',
orders.order_date AS 'Order Date',
orders.order_amount AS 'Order Amt'
FROM customers
LEFT JOIN orders
ON orders.customer_id = customers.customre_id
LEFT JOIN refunds
ON orders.order_id = refunds.order_id
WHERE orders.order_id IS NOT NULL
AND refunds.refund_id IS NULL
  • 引用视图
    # 创建视图
CREATE VIEW view_name AS
select_statement

# 引用视图
SELECT * from view_name
    • 当引用视图中的列的时候，需要指定列的别名，而列的别名是在创建视图时指定的
      # 创建视图
CREATE VIEW customers_view AS
SELECT
first_name AS 'First Name',
last_name AS 'Last Name'
FROM customers

# 引用视图中的列
SELECT
`First Name`,
`Last Name`,
FROM customers_view
WHERE `Last Name` = 'Lopez'
  • 视图的优点
    • 视图可以减少复杂度：将复杂的SELECT语句封装为一个视图
    • 视图可以增加复用性：封装那些总是相连的表
    • 视图可以正确地格式化数据：如果一个表中的某些数据总是需要格式化，可以将其封装到视图中
    • 视图可以创建计算的列：如果需要一个含有大量的计算字段的表，也可将其封装到视图中
    • 视图可以用来重新命名列的名称：如果一个表中的列名总是需要重命名，可以将其封装到视图中
    • 视图可以创建数据子集：如果总是只需要看到某个表的某些子集，可以将它们封装到不同的视图
    • 视图可以用来加强安全性限制：如果一个表中的某些数据希望对某些用户做访问限制，可以使用视图将它们挑出来然后仅将视图的权限给那些用户而不是整个表的权限
  • 修改视图：使用ALTER关键字修改一个已经创建的视图，重新指定被封装到其中的SELECT语句
    # 整个被封装到视图的select语句都需要重新指定
ALTER VIEW view_name AS
new_select_statement

# 与Microsoft SQL Server和MySQL不同，Oracle在修改视图之前，需要使用DROP VIEW view_name先删除视图
    • 同样，修改视图与创建视图一样，只是修改了视图的定义，它本身不会返回任何数据
  • 删除视图：使用DROP VIEW view_name来删除视图

子查询

  • 包含在其他查询中的查询叫做子查询，子查询可以用在SELECT、INSERT、UPDATE、DELETE语句
  • 在SELECT语句中子查询可以有三种用法：
    • 一个一般的SELECT语句格式如下：
      SELECT column_list
FROM table_list
WHERE condition
GROUP BY column_list
HAVING condition
ORDER BY column_list
    • 当子查询是table_list的一部分时，它指定了一个数据源
    • 当子查询时condition的一部分时，它成为查询条件的一部分
    • 当子查询是column_list的一部分时，它创建了一个单个的计算的列
  • 使用子查询指定数据源：把一个子查询指定为FROM子句的一部分时，它立即创建了一个新的数据源，并被当做一个独立的表或视图来引用，与视图的区别是视图是永久保存在数据库中的，而子查询只是临时的
    # 使用子查询指定数据源的一般格式
SELECT column_list
FROM [table_list]
[JOIN] subquery
AS custom_subquery_name

# 从address表，city表和country表中列出五个地址对应的城市和国家
SELECT 
  address AS 'Address',
  city AS 'City',
  country AS 'Country'
FROM address
LEFT JOIN(
  SELECT 
      city.city,
      city.city_id,
  	country.country,
      country.country_id
  FROM city
  LEFT JOIN country
  ON city.country_id = country.country_id
) AS city_and_country ON address.city_id = city_and_country.city_id
ORDER BY address
LIMIT 5
  • 使用子查询指定查询条件：把一个子查询指定为WHERE子句中IN操作符的右值，可以以更复杂的逻辑来为IN操作符创建一个可选列表;注意，当子查询用于指定查询条件时，仅能返回单一的列
    # 使用子查询指定查询条件的一般格式
SELECT column_list
FROM table_list
WHERE column IN subquery

SELECT column_list
FROM table_list
WHERE subquery match_some_comdition

# 列出所有使用现金支付的客户名称
SEELCT customer_name AS 'Customer Name'
FROM costomers
WHERE customer_id IN
(
  SELECT customer_id
  FROM orders
  WHERE order_type = 'cash'
)

# 列出订单金额少于20美元的客户列表
SELECT customer_name AS 'Customer Name'
FROM customers
WHERE
(
  SELECT SUM(orderAmount)
  FROM orders
  WHERE customers.customer_id = orders.customer_id
) < 20
  • 使用子查询作为计算列：把一个子查询作为column_list中的一项，将其用作一个计算的列
    # 使用子查询作为计算列的一般格式
SELECT column_list,
subquery_result AS 'Result Alia'
FROM table_list

# 查询客户及其订单数量
SELECT
customer_name AS 'Customer Name',
(
  SELECT COUNT(order_id)
  FROM orders
  WHERE customers.customer_id = orders.customer_id
) AS 'Number of Orders'
FROM customers
ORDER BY customers.customer_id
  • 关联子查询：无法独立运行的子查询为关联子查询，可以独立运行的子查询为非关联子查询。非关联子查询完全独立与外围查询语句，只会计算和执行一次，而关联子查询需要针对返回的每一行逐行计算，且每次执行子查询的时候得到的结果可能都不一样，上文中查询客户及其订单数量中的子查询即为关联子查询，它使用了外围查询的数据来源customers表
  • EXISTS操作符：用于确定一个关联子查询中是否存在数据
    # 查询下过订单的用户
SELECT
customer_name AS 'Customer'
FROM customers
WHERE EXISTS
(
  SELECT * FROM orders
  WHERE customers.customer_id = orders.customer_id
)

集合逻辑

在前文中，连接JOIN可以将来自两个表的列组合到一个表中，子查询则是将一条SELECT语句的结果提供给第另一条SELECT语句使用。然而有时候我们希望将来自两个表的行组合到一个表中，这时候就需要使用SQL中的集合逻辑UNION，来做合并查询。

  • UNION-合并两条SELECT语句，选取在A或B中的数据，如果同时存在在A或B中，仅显示一条
    SELECT
order_date AS 'Date',
'order' AS 'Type',
order_amount AS 'amount'
FROM orders
WHERE custom_id = 2

UNION

SELECT
return_date AS 'Date',
'return' AS 'type',
return_amount AS 'amount'
FROM returns
WHERE custom_id = 2

ORDER BY date
    使用UNION需要遵守3个规则（实际就一条规则：相同列）：
    • 两个SELECT语句中的列的数量必须相等
    • 两个SELECT语句中的列排列顺序必须相同
    • 两个SELECT语句中的列数据类型必须相同
  • UNION ALL-合并两条SELECT语句，选取在A或B中的数据，即使同时存在在A或B中，都将显示在结果中
    SELECT
DISTINCT
order_date AS 'Date'
FROM orders

UNION ALL

SELECT
DISTINCT
return_date AS 'Date'
FROM returns
ORDER BY Date

# UNION 确保来自两个表的行没有重复数据，但 UNION ALL 允许来自两个表的行可以有相同数据
# DISTINCT 确保来自同一个表（或者说同一个SELECT语句）的行没有重复数据
# 所以上面的语句选取的数据可能会存在重复数据，但重复的数据并不来自两个表而是来自同一个表，并且仅会重复一次
  • INTERSECT-合并两条SELECT语句，选取同时出现在A和B中的行（MySql不支持该操作符）
    SELECT order_date AS 'Date'
FROM orders

INTERSECT

SELECT return_date As 'Date'
FROM returns

ORDER BY Date
  • EXCEPT-合并两条SELECT语句，选取仅出现在A或仅出现在B中的的数据（MySql和Oracle不支持该操作符，但Oracle提供了等价的MINUS操作符）
    SELECT order_date AS 'Date' FROM orders

EXCEPT

SELECT return_date AS 'Date' FROM returns

ORDER BY Date

存储过程和参数

到目前为止，前文所有的SQL语句都是单独使用，然而很多时候，你会希望SQL语句能够像函数一样，定义一次，然后重复调用，并且可以使用参数来增加灵活性。这时，你就可以使用存储过程来实现这一目的。

  • 创建存储过程：创建存储过程不会执行任何内容，只是直接创建了一个过程，以便后续执行它。与表和视图一样，创建好的存储过程在管理工具中是可以查看的
    -- Microsoft SQL Server
CREATE PROCEDURE ProcedureName (OptionalPrameterDeclarations)
AS
BEGIN
SQLStatements
END

-- MySQL
DELIMITER ? -- 规定END语句使用的分隔符，默认为分号
CREATE PROCEDURE ProcedureName (OptionalPrameterDeclarations)
BEGIN
SQLStatements; -- 每一条SQL语句都必须使用分号分隔，即使只有一条
END?
DELIMITER ; -- 将分隔符改回分号
  • 存储过程的参数：例如存储一个选取特定用户的SQL过程，可以使用参数来指定用户的ID
    -- Microsoft SQL Server
CREATE PROCUDURE CustomerProcudure (@custId INT)
AS
BEGIN
  SELECT * FROM customers
  WHERE customer_id = @custId
END

-- MySQL
DELIMITER ?
CREATE PROCEDURE CustomerProcudure (custId INT)
BEGIN
  SELECT * FROM customers
  WHERE CUSTOMER_ID = custId;
END
DELEMITER ;
  • 执行存储过程
    -- Microsoft SQL Server
EXEC CustomerProcudure @custId = 2

-- MySQL
CALL CustomerProcudure (2)
  • 修改和删除存储过程：在Microsoft SQL Server中，修改过程和创建过程几乎一样，只需要把CREATE关键字替换为ALTER关键字即可；然而在MySQL中，虽然也存在ALTER命令，但它的功能十分简单，所以一般我们选择先删除存储过程然后再重新创建
    -- 删除存储过程
DROP PROCEDURE ProcedureName
  • 函数与存储过程的两点区别
    • 存储过程可以有任意数目的输出，而函数只有单一的返回值
    • 只能通过调用程序来执行存储过程，而函数可以在SQL语句中使用

修改数据

  • 修改策略：使用“软删除（使用表中特定的列来标记该行数据是否有效）”技术替代真正的删除；插入新行时在特定列中标记准确的插入日期和时间以便出错时对其进行删除；使用单独的表来保存事务所要更新的数据通常是明智的选择。请永远记住，SQL中没有撤销命令。
  • 插入数据：使用INSERT命令来插入指定数据，注意不需要为自增型的列指定数据，数据库会自动处理它；另外，Oracle不允许一次插入多行数据，需要分开书写
    • 插入INSERT语句中指定的具体数据
      -- 向customer表插入两条新数据
INSERT INTO customers
(first_name, last_name, state) -- 只要列名是正确的，它们的顺序无所谓
-- 当这里的列名顺序与数据库中的物理顺序一致时可省略它们，但强烈不建议这么做
VALUES
('Virginia', 'Jones', 'OH'), -- VALUES关键字后的数据列，要与INSERT INTO后的列相对应
('Clark', 'Woodland', 'CA')
    • 插入用一条SELECT语句指定的数据
      -- 将customer_transaction中的RI州的用户插入到customer表中
INSERT INTO customer
(first_name, last_name, state)
SELECT
fn, ln, state -- 这里选中列的顺序需要与INSERT INTO 语句中的顺序一致
FROM customer_transactions
WHERE state = 'RI'
  • 删除数据：使用DELETE命令来删除一条数据，通常是一整行（删除某行中的列没有意义，那属于修改数据的范畴）
    -- 删除数据的一般写法
DELETE
FROM table_name
WHERE conditions

-- 可以使用SELECT语句来验证删除结果
SELECT
COUNT (*) -- 使用聚合函数COUNT来统计被删除数据的数量以确认是否全部都被删除了
FROM table_name
WHERE conditions

-- 清空一个表中的所有数据，可以使用TRUNCATE TABLE语句
TRUNCATE TABLE customers
-- 上面的语句与下面的DELETE语句效果基本相同
DELETE FROM customers
-- 唯一不同在于，TRUNCATE TABLE语句重置了自增列，而DELETE语句没有
  • 更新（修改）数据：删除数据只需要指定删除的行即可，但更新数据是针对具体行中的具体列，所以需要首先指定更新哪些列，然后指定更新这些列中的哪些行
    • 使用指定的具体数据更新数据
      -- 更新数据的一般格式
UPDATE table
SET
  column1 = expression1,
  column2 = expression2
  -- repeat any number of time
WHERE conditions -- 如果没有指定行，该句会把所有行的指定列都更新一次
    • 使用子查询中的数据修改数据（使用一个表中的数据来更新另一个表中的数据）
      -- 一般格式
UPDATE table -- 指定要更新的表
SET table.column_1 = -- 指定需要更新的列1
  (
    SELECT another_table.column_1 -- 子查询从另一表中获取数据，并通过主键（也可是其它）来进行匹配
    FROM another_table
    WHERE another_table.auto_increment_primary_key = table.auto_increment_primary_key
  )
SET table.column_2 = -- 指定需要更新的列2
  (
    SELECT another_table.column_2
    FROM another_table
    WHERE another_table.auto_increment_primary_key = auto_increment_primary_key.column_2
  )
WHERE EXISTS -- 指定需要更新的行，使用子查询指定只更新table中存在于another_table中的行
  (
    SELECT *
    FROM another_table
    WHERE another_table.auto_increment_primary_key = table.auto_increment_primary_key
  )

维护表

  • 回顾SQL语言的三种组成部分：数据操纵语言（Data Manipulation Language，DML，对数据库中或者更详细地说是表中的数据进行增删改查操作）、数据定义语言（Data Definition Language，DDL，对数据库中的表及其索引进行增删改查）、和数据控制语言（Data Control Language，DCL，维护数据库安全）。本章主要讲述DDL，但前文也已经用到过DDL，视图VIEW、过程PROCEDURE需要用到的都是DDL
  • 添加或修改表和索引的SQL语句很复杂，但是我们无需了解细节，数据库软件通常提供了图形化的工具来修改表的结构，而不一定需要使用SQL语句
  • 表属性：表（Table）是数据库中最重要的对象，数据库中所有数据物理地存储在表中，没有了表数据库中的一切也就没有意义了。前文已经介绍过一些表的属性，主键、外键、数据类型、自增型列等等
    • 表的列
      • 列名：表中的每个列都必须有唯一的列名
      • 数据类型：决定列可以包含什么样的数据
      • 是否自增型：表中每增加一行，该列会以升序序列自动赋值（术语auto-increment是MySQL中的的特定用法，Oracle没有自增型属性）
      • 默认值
  • 主键和索引
    • 主键：只能指定一个列作为主键，目的是保证这个列包含唯一值（所以不允许它们包含NULL值）；实际上主键可以跨越多个列，叫做复合主键（当希望使用电影名称列来作为主键时可能会存在重复名称， 这时可以使用电影名称+年份两个列作为复合主键来唯一地定义每部电影）
    • 索引：索引是一种物理结构，目的是当SQL语句中包含该列的时候，可以加速数据检索，缺点是需要更多的磁盘空间，并且会减慢更新数据时的速度
  • 外键：
    • 外键定义：外键是从一个表中的一个列到另一个不同的表中的列的直接引用，含有外键的表为“子表”，被外键引用的表被称为“父表”
    • 外键级联（Cascade）：当父表有更新或删除时，会自动更新或删除子表中的关联行
    • Set Null：当父表有更新或删除时，如果影响到子表，是否把子表中关联行的外键设置为NULL
  • 创建表：使用CREATE TABLE语句来创建表及其属性（列），不同数据库之间存在差异：
    -- Microsoft SQL Server
CREATE TABLE my_table
(
  column_1 INT IDENTITY (1, 1) PRIMARY KEY NOT NULL, -- 列名column_1，INT类型，自增型，主键，不能为NULL
  column_2 NOT NULL REFERENCES related_table (first_column), -- 列名column_2，INT类型，不能为NULL，外键，关联到related_table表的first_column列
  column_3 VARCHAR (25) NULL, -- 列名column_3，VARCHART类型，可以是NULL
  column_4 FLOAT NULL DEFAULT (10) -- 列名column_4，FLOAT类型，可以是NULL
)


-- My SQL
CREATE TABLE my_table(
  column_1 INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
  column_2 INT NOT NULL,
  column_3 VARCHAR (25) NULL,
  column_4 FLOAT NULL DEFAULT 10 NULL,
  CONSTRAINT FOREIGN KEY (column_2) REFERENCE 'related_table' (first_column) -- 指定外键
)
  
-- Oracle
CREATE TABLE my_table
(
  column_1 INT PRIMARY KEY NOT NULL, -- Oracle不允许有自增型的列
  column_2 INT NOT NULL,
  column_3 VARCHAR2 (25) null,
  column_4 FLOAT DEFAULT 10 NULL
  CONSTRAINT "foreign_key" FOREIGN KEY (column_2) REFERENCES related_table (first_column)
)
    使用ALTER TABLE语句修改表的具体属性，该语句的复杂性及数据库差异巨大，这里不再展开；使用DROP TABLE table_name语句来删除一个表
    -- 修改表
ALTER TABLE my_table
DROP COLUMN column_3

-- 删除表
DROP TABLE my_table
  • 创建索引：使用CREATE INDEX语句，用来在创建表之后创建索引，使用ALTER INDEX语句来添加和修改索引
    -- 创建索引
CREATE INDEX index_2
ON my_table (column_4)

-- 删除索引
DROP INDEX index_2
ON my_table

数据库设计原理与显示数据的策略（略）

  • 关系型数据库是一个数据集合，数据库中的表以某些方式相互关联。
  • SQL语句仅仅是使用数据库的工具，数据库设计则是另外一个更为重要的话题。《SQL初学者指南》中对这个话题进行了简单的概括：规范化及其替代方法，这里将不再展开。
  • 关于这个话题建议阅读另外的一些书籍：《SQL必知必会》、《高性能MySQL》
avatar
文章
阅读
粉丝