SQL必知必会总结3-第14到17章

《SQL必知必会》万字精华

本文是《SQL必知必会》一书的精华总结，帮助读者快速入门SQL或者MySQL，主要内容包含：

• 数据库基础知识
• 库表的相关操作
• 检索数据的方法
• …

前面的章节请参考SQL必知必会总结1-第1到7章和SQL必知必会总结2-第8到13章

组合查询union

什么是组合查询

SQL允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回，这些组合通常称为并union或者复合查询；通常两种情况下需要使用组合查询：

• 在一个查询中从不同的表返回结构数据
• 对一个表执行多个不同的查询，按照一个查询返回数据

创建组合查询

可以用UNION操作符来组合数条SQL查询。

-- 语句1
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI');
-- 语句2
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4ALL';

通过组合查询将上面两个查询组合在一起：

-- 组合查询
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION    -- 关键字
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4ALL';

我们也可以使用多个WHERE条件来实现：

-- 语句1
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
AND cust_name = 'Fun4ALL';

UNION使用规则

总结UNION使用规则：

1. UNION必须由两条或者两条以上的SELECT语句组成；语句之间通过UNION关键字隔开
2. UNION中的每个查询必须包含相同的列、表达式或者聚集函数
3. 列数据类型必须兼容：类型不必完全相同
4. UNION从查询结果集中会自动消除重复的行；但是如果想保留所有的行，使用UNION ALL 实现

对组合结果进行排序

SELECT语句的输出用ORDER BY子句排序。

-- 组合查询
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_state IN ('IL','IN','MI')
UNION    -- 关键字
SELECT cust_name, cust_contact,cust_email
FROM Customers
WHERE cust_name = 'Fun4ALL'
ORDER BY cust_name, cust_contact;   -- 组合之后再进行排序

插入数据

插入数据

INSERT用来将行插入（或者添加）到数据库表中，3种插入方式：

• 插入完整的行
• 插入行的一部分
• 插入某些查询的结果

下面通过实际的例子来说明：

1、插入完整的行

INSERT INTO Customers
VALUES('1000000006',
       'Tony',
       '123 Any Street',
       'New York',
       'NY',
       '1111',
       'USA',
       NULL,
       NULL   
)

将上面的数据插入到Customers表中，每列对应一个值。如果值不存在，则用NULL代替。同时插入数据的顺序必须和表中定义的相同。

安全写法：列出每个字段名称

INSERT INTO Customers(cust_id,   -- 明确列出列名
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state,
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email               
)
VALUES('1000000006',   -- 和上面的列名一一对应
       'Tony',
       '123 Any Street',
       'New York',
       'NY',
       '1111',
       'USA',
       NULL,
       NULL   
)

上面列名和下面插入的数据必须一一对应，我们改变插入顺序：

INSERT INTO Customers(cust_id,   -- 明确列出列名
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state                                   
)
VALUES('1000000006',   -- 和上面的列名一一对应
       '1111',
       'USA',
        NULL,
        NULL,   
       'Tony',
       '123 Any Street',
       'New York',
       'NY'
)

2、插入部分数据

上面的例子中我们插入的是全部列名的数据，现在指定部分列名进行插入：

INSERT INTO Customers(cust_id,   -- 明确列出列名
                      cust_zip,
                      cust_country,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state                                   
)
VALUES('1000000006',   -- 和上面的列名一一对应
       '1111',
       'USA',
       'Tony',
       '123 Any Street',
       'New York',
       'NY'
)

3、插入检索出来的数据

INSERT的另一种使用是将SELECT检索出来的结果插入到表中，使用INSERT SELECT语句

INSERT INTO Customers(cust_id,   -- 2、将SELECT检索的结果插进来
                      cust_zip,
                      cust_country,
                      cust_contact,
                      cust_email,
                      cust_name,
                      cust_address,
                      cust_city,
                      cust_state                                   
)
SELECT cust_id,   -- 1、SELECT先检索结果
   		 cust_zip,
   		 cust_country,
  		 cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state   
FROM CustNew;

INSERT SELECT中SELECT语句也是可以包含WHERE子句，以便过滤插入的数据。

从一个表复制到另一个表

还有一种数据插入不需要使用INSERT语句，要将一个表的内容复制到另一个表，可以使用SELECT INSERT语句

SELECT * 
INTO CustCopy
FROM Customers;

需要注意4点：

1. 在复制的过程中，任何SELECT选项和子句都可以使用，包含WHERE 和GROUP BY子句
2. 可以利用联结从多个表插入数据
3. 不管从多少个表中检索数据，数据最终只能插入到一个表中
4. INSERT INTO是插入数据；SELECT INSERT是导出数据

更新和删除数据

更新数据

更新（修改）表中的数据，可以使update语句。常见的有两种update方式：

• 更新表中特定的行
• 更新表中所有的行

update语句的3个组成部分：

1. 要更新的表
2. 列名和它们的新值
3. 确定要更新哪些行的过滤条件

UPDATE Customers  -- 1、待更新的表
SET cust_email = '28173497@gmail.com'  -- 2、需要更细的列名和它的新值
WHERE cust_id = '10000000005';  -- 3、过滤条件

同时更新多个值：

UPDATE Customers  -- 1、待更新的表
SET   -- 2、同时更新多个值
	cust_email = '28173497@gmail.com',
	cust_contact = 'Sam Roberts'
WHERE cust_id = '10000000005';  -- 3、过滤条件

在更新多个列的值时，只需要使用提交SET命令，每个列=值对之间通过逗号隔开，最后一个列不同。

如果想删除某个列的值，可以将其设置成NULL（假如表定义允许NULL值）。

• 空字符串用''表示，是一个值
• NULL是没有值的

UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '100000000005';

删除数据

从表中删除数据使用DELETE语句。有两种删除方式：

• 从表中删除特定的行
• 从表中删除所有的行

DELETE FROM Customers
WHERE cust_id = '011111111116';

DELETE是删除整行而不是删除列。要删除列请使用UPDATE语句

更新和删除的指导原则

• 请一定要带上WHERE子句，否则会修改全部的数据；除非我们的确是需要更新全部记录（少见）
• 要保证每个表都有主键，可以指定各个主键、多个值或者值的范围
• 在UPDATE或者DELETE语句使用WHERE语句之前，先用SELECT进行测试，保证它过滤掉的是正确的记录

创建和操作表

创建表

SQL中一般有两种创建表的方法：

• 多数DBMS都具有交互式创建和管理数据库表的工具
• 表也可以直接使用SQL语句来操控；通过create table来实现

CREATE TABLE Products   -- 创建表
(
  prod_id     CHAR(10)      NOT NULL,
  vend_id     CHAR(10)      NOT NULL,
  prod_name   CHAR(254)     NOT NULL,
  prod_price  DECIMAL(8,2)  NOT NULL,
  prod_desc   VARCHAR(1000) NULL                  
);

上面代码的解释：

1. 表名紧跟CREATE TABLE 关键字
2. 列名在圆括号中，各个列之间通过逗号隔开
3. 每列的定义以列名开始，后紧跟数据类型 ，是否允许控制等
4. 整条语句是以分号结束

使用NULL值

NULL值就是没有值或者缺失值。每个表中的列要么是NULL列，要么是NOT NULL列。

主键是其值唯一标识表中每一行的列。只有不允许NULL值的列可作为主键，允许NULL值的列不能作为唯一标识。

笔记：NULL是默认设置。如果不指定NOT NULL，则认为指定的就是NULL。

注意NULL和空字符串的区别：

1. NULL值没有值，不是空字符串
2. 空字符串值一个有效的值，它不是无值
3. NULL值使用关键字NULL而不是空字符串指定

指定默认值

SQL中创建表的时候可以指定通过关键字DEFAULT来指定：

CREATE TABLE OrderItems
(
  order_num           INTEGER         NOT NULL,
  order_item          INTEGER         NOT NULL,
  prod_id             CHAR(10)        NOT NULL,
  quantity            INTEGER         NOT NULL   DEFAULT 1,    -- 指定默认值
  item_price          DECIMAL(8,2)    NOT NULL
);

默认值一般是用于日期或者时间戳列。

更新表

更新表中的数据使用关键词ALTER TABLE。

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

删除表

删除整个表而不是其内容，使用DROP TABLE。

DROP TABLE CustCopy;   -- 执行这条语句将会永久删除该表

重命名表

通过关键字RENAME来实现

RENAME TABLE old_name TO new_name;

旧表(old_name)必须存在，新表(new_name)必须不存在。 如果新表new_name存在，则该语句将失败。