SQL 基础笔记

本文为 SQL必知必会的读后笔记

SELECT 语句

多条SQL语句必须以分号（；）分隔，建议加上
SQL语句不区分大小写，对SQL关键字使用大写，而对列名和表名使用小写，这样做使代码更易于阅读和调试。
在处理SQL语句时，其中所有空格都被忽略。SQL语句可以写成长长的一行，也可以分写在多行（将SQL语句分成多行更容易阅读和调试）

//用SELECT语句从Products表中检索一个名为prod_name的列，将返回表中的所有行
SELECT prod_name FROM Products;

检索多个列

image.png

在SELECT关键字后给出多个列名，列名之间必须以逗号分隔

//使用SELECT语句从表Products中选择数据。在这个例子中，指定了3个列名
SELECT prod_id, prod_name, prod_price FROM Products;

//检索所有的列,在实际列名的位置使用星号（*）通 配符,但检索不需 要的列通常会降低检索和应用程序的性能。
SELECT * FROM Products;

//DISTINCT关键字，，它指示数据库只返回不同的值。
//只返回不同（具有唯一性）的vend_id行, 指定2列的话，除非2列都相同，否则所有的行都会被检索出来。
SELECT DISTINCT vend_id FROM Products;

//各数据库语句不同，这里使用MySQL 只检索前5行数据
SELECT prod_name FROM Products LIMIT 5

//MySQL 返回从第5行起的5行数据。第一个数字是指从哪儿开始，第二个数字是检索的行数。
SELECT prod_name FROM Products LIMIT 5 OFFSET 5;
##Products表中只有9种产品，所以LIMIT 5 OFFSET 5只返回 了4行数据（因为没有第5行,从第6行开始）。
##第一个被检索的行是第0行，而不是第1行。因此，LIMIT 1 OFFSET 1会检索第2行，而不是第1行


##ORDER BY子句取一个或多个列的名字，据此对输出进行排序，这个子句必须是SELECT语句中的最后一条子句
//件对prod_name列以字母顺序排序数据的ORDER BY
SELECT prod_name FROM Products ORDER BY prod_name; 

//多个列排序 首先按价格，然后按名称排序（仅在多个行具有相同的prod_price值时才 对产品按prod_name进行排序。如果prod_price列中所有的值都是唯一的，则不会按prod_name排序）
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price, prod_name; 
//与上面的查询相同，不同之处在于中指定的是选择列的相对位置而不是列
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY 2, 3;

##升序排序（ASC 从A到Z），这只是默认的排序顺序。还可以使用ORDER BY子句进行降序（DESC 从Z到A）排序
##想在多个列上进行降序排序，必须对每一列指定DESC关键字。
//价格降序来排序产品
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC; 
//如果打算用多个列排序，以降序排序产品（最贵的在最前面），再加上产品名(未指定，默认升序)：
SELECT prod_id, prod_price, prod_name FROM Products ORDER BY prod_price DESC, prod_name;

WHERE子句指定搜索条件

在同时使用ORDER BY和WHERE子句时，应该让ORDER BY位于WHERE之后
单引号用来限定字符串。如果将值与字符串类型的列进行比较，就需要限定引号。用来与数值列进行比较的值不用引号

//只返回prod_price值为3.49的
SELECT prod_name, prod_price FROM Products WHERE prod_price = 3.49; 
//列出所有价格小于10美元的产品
SELECT prod_name, prod_price FROM Products WHERE prod_price < 10; 


//!=和<>通常可以互换。但是，并非所有DBMS都支持这两种不等于操作符
//列出所有不是供应商DLL01制造的产品：
SELECT vend_id, prod_name FROM Products WHERE vend_id != 'DLL01'

//它检索价格在5美元和10美元之间的所有产品：
SELECT prod_name, prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10; 

//确定值是否为NULL，不能简单地检查是否= NULL。SELECT语句有一个特殊的WHERE子句 IS NULL子句
//返回所有没有价格（空prod_price字段，不是价格为0）的产品，由于表中没有这样的行，所以没有返回数据
SELECT prod_name FROM Products WHERE prod_price IS NULL;

组合组合WHERE子句

SQL允许给出多个WHERE子句。这些子句有两种使用方式，即以AND子句或OR子句的方式使用，也称为逻辑操作符

要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件

在处理OR操作符前，优先处理AND操作符。

//检索由供应商DLL01制造且价格小于等于4美元的所有产品的名称和价格
SELECT prod_id, prod_price, prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <= 4;

OR操作符与AND操作符正好相反，它指示DBMS检索匹配任一条件的行

//索由任一个指定供应商制造的所有产品的产品名和价
SELECT prod_name, prod_price FROM Products WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’

//假如需要列出价格为10美元及以上，且由DLL01或BRS01制 造的所有产品。
//注意（）不能丢，and 优先级大于or 
//不加 会理解为 ：由供应商BRS01制造的价格为10 美元以上的所有产品，以及由供应商DLL01制造的所有产品
SELECT prod_name, prod_price FROM Products
WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01' )
AND prod_price >= 10;

IN操作符

IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值。
IN操作符完成了与OR相同的功能，IN的最大优点是可以包含其他SELECT语句

//此SELECT语句检索由供应商DLL01和BRS01制造的所有产品。IN操作符后跟由逗号分隔的合法值，这些值必须括在圆括号中。
SELECT prod_name, prod_price FROM Products WHERE vend_id IN ( 'DLL01', 'BRS01' ) ORDER BY prod_name;

NOT操作符

WHERE子句中的NOT操作符有且只有一个功能，那就是否定其后所跟的任何条件
NOT关键字可以用在要过滤的列前，而不仅是在其后。
这里的这种简单的WHERE子句，使用NOT确实没有什么优势。但在更复杂的子句中，NOT是非常有用的。例如，在与IN操作符联合使用时，NOT可以非常简单地找出与条件列表不匹配的行。

//为了列出除DLL01之外的所有供应商制造的产品，
SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01' ORDER BY prod_name;
//上面的例子也可以使用<>,!=操作符来完成

用通配符进行过滤

即通配符搜索一般比前面讨论的其他搜索要耗费更长的处理时间。
也尽量不要把它们用在搜索模式的开始处。把通配符置于开始处，搜索起来是最慢的。

LIKE操作符

是针对未知值进行过滤的，通配符搜索只能用于文本字段（串），非文本数据类型字段不能使用通配符搜索

1.最常使用的通配符是百分号（%)，%表示任何字符出现任意次数

//为了找出所有以词Fish起头的产品，
//将检索任意以Fish起头的词。%告诉DBMS接受Fish之后的任意字符，不管它有多少字 符。
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE 'Fish%';

通配符可在搜索模式中的任意位置使用，并且可以使用多个通配符。下面的例子使用两个通配符，它们位于模式的两端

%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或多个字符
通配符%看起来像是可以匹配任何东西，但有个例外，这就是NULL。子句WHERE prod_name LIKE '%'不会匹配产品名称为NULL的行

//搜索模式'%bean bag%'表示匹配任何位置上包含文本bean bag的值，不论它之前或之后出现什么字符
SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '%bean bag%'; 

//就是根据邮件地址的一部分来查找电子邮件，例如WHERE email LIKE 。
SELECT prod_name FROM Products WHERE prod_name LIKE 'b%@forta.com';

##许多DBMS都用空格来填补字段的内容
如果某列有50个字符，而存储的文本为Fish bean bag toy（17个字 符），
则为填满该列需要在文本后附加33个空格。这样做一般对数据及其使用没有影响，
但是可能对上述SQL语句有负面影响。子句WHERE prod_name LIKE 'F%y'只匹配以F开头、以y结尾的prod_name。如果值后面跟空格，则不是以y结尾，所以Fish bean bag toy就不会检索出 来。
简单的解决办法是给搜索模式再增加一个%号：'F%y%'还匹配y之后的字符（或空格）。
更好的解决办法是用函数去掉空格

2.通配符下划线（_）。下划线的用途与%一样，但它只匹配单个字符，而不是多个字符

，_总是刚好匹配一个字符，不能多也不能少

SELECT prod_id, prod_name FROM Products WHERE prod_name LIKE '_ inch teddy bear';

3.方括号（[ ]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符

并不是所有DBMS都支持用来创建集合的[]。

//找出所有名字以J或M起头的联系人，可进行如下查询
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[JM]%' ORDER BY cust_contact;
//下面的查询匹配不以J或M起头的任意联系人名
SELECT cust_contact FROM Customers WHERE cust_contact LIKE '[^JM]%' ORDER BY cust_contact;

计算字段

拼接

在SQL中的SELECT语句中，可使用一个特殊的操作符来拼接两个列。，此操作符可用加号（+）或两个竖杠（||）表示。在MySQL和MariaDB中，必须使用特殊的函数。

说明：是说明：是+ +还是还是|| ||？？ Access和SQL Server使用+号。DB2、Oracle、PostgreSQL、SQLite和Open Office Base使用||。详细请参阅具体的DBMS文档

问题：

Vendors表包含供应商名和地址信息。假如要生成一个供应商报表，需要在格式化的名称（位置）中列出供应商的位置。
此报表需要一个值，而表中数据存储在两个列vend_name和vend_country中

RTRIM()函数去掉值右边的所有空格。通过使用RTRIM()，各个列都进行了整理。
大多数DBMS都支持RTRIM()（正如刚才所见，它去掉字符串右边的空格）、LTRIM()（去掉字符串左边的空格）以及TRIM()（去掉字符串左右两边的空格）。

//会有空格
SELECT vend_name + ' (' + vend_country + ')' FROM Vendors ORDER BY vend_name;
SELECT vend_name || ' (' || vend_country || ')' FROM Vendors ORDER BY vend_name; 
//为正确返回格式化的数据，必须去掉这些空格。这可以使用SQL的 RTRIM() 函数来完成
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' FROM Vendors ORDER BY vend_name;

image.png

别名（alias）是一个字段或值的替换名。别名用AS关键字赋予。(为了客户端引用值)

SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'      
  AS vend_title FROM Vendors ORDER BY vend_name;

image.png

下面是MySQL和MariaDB中使用的语句：

SELECT Concat(vend_name, ' (', vend_country, ')')      
 AS vend_title FROM Vendors ORDER BY vend_name;

Orders表包含收到的所有订单，OrderItems表包含每个订单中的各项物品。

//下面的SQL语句检索订单号20008中的所有物品：
SELECT prod_id, quantity, item_price FROM OrderItems WHERE order_num = 20008; 

//item_price列包含订单中每项物品的单价。如下汇总物品的价格（单价乘以订购数量）：
SELECT prod_id,  
quantity, 
item_price, 
quantity*item_price AS expanded_price 
FROM OrderItems 
WHERE order_num = 20008;

image.png

与SQL语句不一样，SQL函数不是可移植的。这表示为特定SQL实现编写的代码在其他实现中可能不正常
为了代码的可移植，许多SQL程序员不赞成使用特定于实现的功能

大多数SQL实现支持以下类型的函数。

用于处理文本字符串（如删除或填充值，转换值为大写或小写）的文本函数。
用于在数值数据上进行算术操作（如返回绝对值，进行代数运算）的数值函数。
用于处理日期和时间值并从这些值中提取特定成分（如返回两个日期之差，检查日期有效性）的日期和时间函数。
返回DBMS正使用的特殊信息（如返回用户登录信息）的系统函数。

image.png

//UPPER()将文本转换为大写，
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase FROM Vendors ORDER BY vend_name;

image.png

日期和时间处理函数

这里仅适用 MySQL

//Orders表中包含的订单都带有订单日期。为在SQL Server中检索2012年的所有订单
SELECT order_num FROM Orders WHERE YEAR(order_date) = 2012;

分组数据分组数据

这涉及两个新SELECT语句子句：GROUP BY子句和HAVING子句

使用HAVING时应该结合GROUP BY子句，而WHERE子句用于标准的行级过滤。

//要返回每个供应商提供的产品数目
SELECT vend_id, COUNT(*) AS num_prods FROM Products GROUP BY vend_id;

image.png

除了能用GROUP BY分组数据外，SQL还允许过滤分组，规定包括哪些分组，排除哪些分组。例如，你可能想要列出至少有两个订单的所有顾客。为此，必须基于完整的分组而不是个别的行进行过滤。

在这个例子中WHERE不能完成任务，因为WHERE过滤指定的是行而不是分组。事实上，WHERE没有分组的概念。
就是HAVING子句。HAVING非常类似于WHERE。事实上，目前为止所学过的所有类型的WHERE子句都可以用HAVING来替代。唯一的差别是，WHERE过滤行，而HAVING过滤分组

//列出至少有两个订单的所有顾 客
//WHERE子句在这里不起作用，因为过滤是基于分组聚集值，而不是特定行的值
SELECT cust_id, COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2;

//同时使用WHERE和HAVING子句的需要呢
//具有两个以上产品且其价格大于等于4的供应商
SELECT vend_id, COUNT(*) AS num_prods FROM Products 
WHERE prod_price >= 4
 GROUP BY vend_id HAVING COUNT(*) >= 2;

//检索包含三个或更多物品的订单号和订购 物品的数目：要按订购物品的数目排序输出
SELECT order_num, COUNT(*) AS items
 FROM OrderItems GROUP BY order_num
 HAVING COUNT(*) >= 3
 ORDER BY items, order_num;

image.png

SELECT子句顺序

image.png

利用子查询进行过滤

订单存储在两个表中。每个订单包含订单编号、客户 ID、订单日期，在Orders表中存储为一行。
各订单的物品存储在相关的OrderItems表中。Orders表不存储顾客信息，只存储顾客ID。顾客的实际信息存储在Customers表中。

现在，假如需要列出订购物品RGAN01的所有顾客，应该怎样检索？下面列出具体的步骤。

1. 检索包含物品RGAN01的所有订单的编号。
1. 检索具有前一步骤列出的订单编号的所有顾客的ID。
1. 检索前一步骤返回的所有顾客ID的顾客信息。

//它对prod_id为RGAN01的所有订单物品，检索其order_num列
SELECT order_num FROM OrderItems WHERE prod_id = 'RGAN01';

image.png

//订单包含要检索的物品，下一步查询与订单20007和20008相关的顾客ID
SELECT cust_id FROM Orders WHERE order_num IN (20007,20008);

##结合这两个查询，把第一个查询（返回订单号的那一个）变为子查询。
SELECT cust_id 
FROM Orders 
WHERE order_num IN (SELECT order_num        
            FROM OrderItems              
      WHERE prod_id = 'RGAN01');

image.png

//现在得到了订购物品RGAN01的所有顾客的ID。下一步是检索这些顾客ID的顾客信息。
SELECT cust_name, cust_contact FROM Customers WHERE cust_id IN ('1000000004','1000000005');

//子查询  三个结合一起 
SELECT cust_name, cust_contact FROM Customers 
WHERE cust_id IN (SELECT cust_id             
     FROM Order         
         WHERE order_num IN (SELECT order_num                 
                     FROM OrderItems                        
              WHERE prod_id = 'RGAN01'));

image.png

对于能嵌套的子查询的数目没有限制，不过在实际使用时由于性能的限制，不能嵌套太多的子查询。

联结表（join)

有一个包含产品目录的数据库表，其中每类物品占一行。对于每一种物品，要存储的信息包括产品描述、价格，以及生产该产品的供应商。
现在有同一供应商生产的多种物品，那么在何处存储供应商名、地址、联系方法等供应商信息呢？

在这个例子中可建立两个表：一个存储供应商信息，另一个存储产品信息。Vendors表包含所有供应商信息，每个供应商占一行，具有唯一的标识。此标识称为主键（primary key），可以是供应商ID或任何其他唯一值。

Products表只存储产品信息，除了存储供应商ID（Vendors表的主键）外，它不存储其他有关供应商的信息。Vendors表的主键将Vendors表与Products表关联，利用供应商ID能从Vendors表中找出相应供应商的详细信息

这条语句的FROM子句列出了两个表：Vendors和Products。它们就是这条SELECT语句联结的两个表的名字。这两个表用WHERE子句正确地联结


#简单格式 
//这里最大的差别是所指定的两列（prod_name和prod_price）在一个表 中，而第三列（vend_name）在另一个表中。
SELECT vend_name, prod_name, prod_price FROM Vendors, Products 
WHERE Vendors.vend_id = Products.vend_id;

#标准格式 （2种都行，这种更规范）
//两个表之间的关系是以INNER JOIN指定的部分FROM子句。在使用这种语 法时，联结条件用特定的ON子句而不是WHERE子句给出。传递给ON的实际条件与传递给WHERE的相同。
SELECT vend_name, prod_name, prod_price
 FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id;

联结多个表

//显示订单20007中的物品
//以处理联结。这种处理可能非常耗费资源，因此应该注意，不要联结不必要的表。联结的表越多，性能 下降越厉害。 
SELECT prod_name, vend_name, prod_price, quantity
 FROM OrderItems, Products, Vendors 
WHERE Products.vend_id = Vendors.vend_id AND OrderItems.prod_id = Products.prod_id AND order_num = 20007;

#子查询 可以替换为 下面的 
SELECT cust_name, cust_contact
 FROM Customers 
WHERE cust_id IN (SELECT cust_id          
               FROM Orders           
       WHERE order_num IN (SELECT order_num                
        FROM OrderItems                   
        WHERE prod_id = 'RGAN01'));

##改进为多表联查  
SELECT cust_name, cust_contact FROM Customers, Orders, OrderItems 
WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod_id = 'RGAN01';

使用表别名

还允许给表名起别名。这样做有两个主要理由：

缩短SQL语句；
允许在一条SELECT语句中多次使用相同的表。
与列别名不一样，表别名不返回到客户端。

//列别名 
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')'      
 AS vend_title FROM Vendors ORDER BY vend_name;

//表别名 
//其实它不仅能用于WHERE子句，还可以用于SELECT的列表、ORDER BY子句以及其他语句 部分。
SELECT cust_name, cust_contact FROM Customers AS C, Orders AS O, OrderItems AS OI
 WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

自联结

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。虽然最终的结果是相同的，但许多DBMS处理联结远比处理子查询快得多

//要给与Jim Jones同一公司的所有顾客发送一封信件。这个查询要求首先找出Jim Jones工作的公司，然后找出在该公司工作的顾客

##第一种解决方案，使用了子查询
SELECT cust_id, cust_name, cust_contact 
FROM Customers 
WHERE cust_name = (SELECT cust_name       
            FROM Customers               
    WHERE cust_contact = 'Jim Jones');

##用联结的相同查询：
//此查询中需要的两个表实际上是相同的表，因此Customers表在FROM子句中出现了两次。
//虽然这是完全合法的，但对Customers的引用具有歧义 性，因为DBMS不知道你引用的是哪个Customers表。解决此问题，需要使用表别名

//WHERE首先联结两个表，然后按第二个表中的cust_contact过滤数据，返回所需的数据

SELECT c1.cust_id, c1.cust_name, c1.cust_contact 
FROM Customers AS c1, Customers AS c2 
WHERE c1.cust_name = c2.cust_name AND c2.cust_contact = 'Jim Jones';

自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。标准的联结（前一课中介绍的内联结）返回所有数据，相同的列甚至多次出现。自然联结排除多次出现，使每一列只返回一次

系统不完成这项工作，由你自己完成它。自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *）

SELECT C.*, O.order_num, O.order_date, 
OI.prod_id, OI.quantity, OI.item_price 
FROM Customers AS C, Orders AS O, OrderItems AS OI
 WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';

外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行
对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
，联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。

//它检索所有顾客及其订单
SELECT Customers.cust_id, Orders.order_num 
FROM Customers INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;

//要检索包括没有订单顾客在内的所有顾客，
SELECT Customers.cust_id, Orders.order_num 
FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

image.png

这条SELECT语句使用了关键字OUTER JOIN来指定联结类型（而不是在WHERE子句中指定）。但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT指出的是OUTER JOIN左边的表）。上面的例子使用LEFT OUTER JOIN从FROM子句左边的表（Customers表）中选择所有行。为了从右边的表中选择所有行，需要使用RIGHT OUTER JOIN

SELECT Customers.cust_id, Orders.order_num 
FROM Customers RIGHT OUTER JOIN Orders ON Orders.cust_id = Customers.cust_id;

总是有两种基本的外联结形式：左外联结和右外联结。它们之间的唯一差别是所关联的表的顺序。换句话说，调整FROM或WHERE子句中表的顺序，左外联结可以转换为右外联结。因此，这两种外联结可以互换使用，哪个方便就用哪个

就是全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行。与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行

// 检索所有顾客及每个顾客所下的订单数
SELECT Customers.cust_id,   COUNT(Orders.order_num) AS num_ord 
FROM Customers INNER JOIN Orders
 ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id;

//用左外部联结来包含所有顾客，甚至包含那些没有任何订单的顾客。结果中也包含了顾客1000000002，他有0个订单。

SELECT Customers.cust_id, COUNT(Orders.order_num) AS num_ord 
FROM Customers LEFT OUTER JOIN Orders 
ON Customers.cust_id = Orders.cust_id GROUP BY Customers.cust_id;

检索所有顾客及每个顾客所下的订单数

左外部联结来包含所有顾客，甚至包含那些没有任何订单的顾客。结果中也包含了顾客1000000002，他有0个订单

组合查询

UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分隔（因此，如果组合四条SELECT语句，将要使用三个UNION关键字）。 UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过，各个列不需要以相同的次序列出）。列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含转换的类型（例如，不同的数值类型或不同的日期类型）。

多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。但是，SQL也允许执行多个查询（多条SELECT语句），并将结果作为一个查询结果集返回。这些组合查询通常称为并并（union）或复合查询复合查询（compound query）。
主要有两种情况需要使用组合查询：

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

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成一个结果集。

//假如需要Illinois、Indiana和Michigan等美国几个州的所有顾客的报表，
//还想包括不管位于哪个州的所有的Fun4All。当然可以利 用WHERE子句来完成此工作，不过这次我们使用UNION

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; //排序 

//用多条WHERE子句 来实现 
SELECT cust_name, cust_contact, cust_email FROM Customers 
WHERE cust_state IN ('IL','IN','MI') OR cust_name = 'Fun4All';

image.png

数据插入

插入有几种方式：

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

把数据插入表中的最简单方法是使用基本的INSERT语法，它要求指定表名和插入到新行中的值

//这个例子将一个新顾客插入到Customers表中
//不安全写法 
INSERT INTO Customers 
VALUES('1000000006',    
   'Toy Land',    
   '123 Any Street',    
   'New York',      
   'NY',       
   '11111',     
   'USA',    
   NULL,    
   NULL); 

//安全写法
//插入部分行 ，还可以省略列，这表示可以只给某些列提供值，给其他列不提供
INSERT INTO Customers(cust_id,  cust_name,cust_address,cust_city,cust_state,
                      cust_zip, cust_country, cust_contact,cust_email) 
VALUES('1000000006',  
      'Toy Land',     
      '123 Any Street',  
      'New York',   
      'NY',      
      '11111',  
      'USA',     
      NULL,  
      NULL);

从一个表复制到另一个表

//这条SELECT语句创建一个名为CustCopy的新表，并把Customers表的整个内容复制到新表中
SELECT * INTO CustCopy FROM Customers;

//MySQL 用法 
CREATE TABLE CustCopy AS SELECT * FROM Customers;

更新数据

在这里，SET子句设置cust_email列为指定的值：
没有WHERE子句，DBMS将会用这个电子邮件地址更新Customers表中的所有行

//客户1000000005现在有了电子邮件地址，因此他的记录需要更新，
UPDATE Customers 
SET cust_email = 'kim@thetoystore.com' WHERE cust_id = '1000000005';

//更新多个列的
UPDATE Customers 
SET cust_contact = 'Sam Roberts', cust_email = 'sam@toyland.com' 
WHERE cust_id = '1000000006';

//要删除某个列的值，可设置它为NULL（假如表定义允许NULL值）。
//空字符串用''表示，是一个值），而NULL表示没有值。
UPDATE Customers SET cust_email = NULL WHERE cust_id = '1000000005'

删除数据

甚至是删除表中所有行。但是，DELETE不删除表本身。
想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句,而速度更快

除非确实打算更新和删除每一行，否则绝对不要使用不带WHERE子句的UPDATE或DELETE语句

//从Customers表中删除一行
DELETE FROM Customers WHERE cust_id = '1000000006';

创建表

利用CREATE TABLE创建表，必须给出下列信息：

新表的名字，在关键字CREATE TABLE之后给出；
表列的名字和定义，用逗号分隔；
有的DBMS还要求指定表的位置

实际的表定义（所有列）括在圆括号之中，各列之间用逗号分

因此指定为NOT NULL。其余五列全都允许NULL值，所以不指定NOT NULL。NULL为默认设置，如果不指定NOT NULL，就认为指定的是NULL

默认值经常用于日期或时间戳列。例如，通过指定引用系统日期的函数或变量，将系统日期用作默认日期。 MySQL用户指定DEFAULT CURRENT_DATE()

//而对于MySQL，varchar必须替换为text
CREATE TABLE Products (   
  prod_id       CHAR(10)         NOT NULL,  
  vend_id       CHAR(10)          NOT NULL,   
   quantity       INTEGER          NOT NULL      DEFAULT 1, //默认值 
  prod_name     CHAR(254)         NOT NULL,  
  prod_price    DECIMAL(8,2)      NOT NULL,   
  prod_desc     VARCHAR(1000)     NULL 
);

更新表

可以使用ALTER TABLE语句

使用ALTER TABLE更改表结构，必须给出下面的信息：

在ALTER TABLE之后给出要更改的表名（该表必须存在，否则将出错）；
列出要做哪些更改。

//给Vendors表增加一个名为vend_phone的列，其数据类型为CHAR
ALTER TABLE Vendors ADD vend_phone CHAR(20);

//更改或删除列、增加约束或增加键

ALTER TABLE Vendors DROP COLUMN vend_phone;

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤

1.用新的列布局创建一个新表；

使用INSERT SELECT语句（关于这条语句的详细介绍，请参阅第15课）从旧表复制数据到新表。有必要的话，可以使用转换函数和计算字段；
检验包含所需数据的新表；
重命名旧表（如果确定，可以删除它）；
用旧表原来的名字重命名新表；
根据需要，重新创建触发器、存储过程、索引和外键。

删除表

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

管理事务处理

使用事务处理（transaction processing），通过确保成批的SQL操作要么完全执行，要么完全不执行，来维护数据库的完整性。

事务处理是一种机制，用来管理必须成批执行的SQL操作，保证数据库不包含不完整的操作结果。利用事务处理，可以保证一组操作不会中途停止，它们要么完全执行，要么完全不执行（除非明确指示）。如果没有错误发生，整组语句提交给（写到）数据库表；如果发生错误，则进行回退（撤销），将数据库恢复到某个已知且安全的状态。

约束

管理如何插入或处理数据库数据的规则

DBMS通过在数据库表上施加约束来实施引用完整性。大多数约束是在表定义中定义的，如第17课所述，用CREATE TABLE或ALTER TABLE语句

主键

主键是一种特殊的约束，用来保证一列（或一组列）中的值是唯一的，而且永不改动。换句话说，表中的一列（或多个列）的值唯一标识表中的每一行

CREATE TABLE Vendors ( 
vend_id         CHAR(10)       NOT NULL PRIMARY KEY,  //主键 
vend_name       CHAR(50)       NOT NULL, 
vend_address    CHAR(50)       NULL, 
vend_city       CHAR(50)       NULL,
vend_state      CHAR(5)        NULL,
vend_zip        CHAR(10)       NULL,
vend_country    CHAR(50)       NULL );

//这里定义相同的列为主键，但使用的是CONSTRAINT语法。此语法也可以用于CREATE TABLE和ALTER TABLE语句
//给表的vend_id列定义添加关键字PRIMARY KEY
ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id);

外键

外键是表中的一列，其值必须列在另一表的主键中。外键是保证引用完整性的极其重要部分。

Orders表将录入到系统的每个订单作为一行包含其中。顾客信息存储在Customers表中。Orders表中的订单通过顾客ID与Customers表中的特定行相关联。顾客ID为Customers表的主键，每个顾客都有唯一的ID。订单号为Orders表的主键，每个订单都有唯一的订单号。

其中的表定义使用了REFERENCES关键字，它表示cust_id中的任何值都必须是Customers表的cust_id中的值。

CREATE TABLE Orders (  
  order_num     INTEGER     NOT NULL PRIMARY KEY,   
  order_date    DATETIME    NOT NULL,  
  cust_id       CHAR(10)    NOT NULL REFERENCES Customers(cust_id) );

//修改表 
ALTER TABLE Orders ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

提示：外键有助防止意外删除
在定义外键后，DBMS不允许删除在另一个表中具有关联行的行。例如，不能删除关联订单的顾客。删除该顾客的唯一方法是首先删除相关的订单（这表示还要删除相关的订单项）。由于需要一系列的删除，因而利用外键可以防止意外删除数据。
有的DBMS支持称为级联删除（cascading delete）的特性。如果启用，该特性在从一个表中删除行时删除所有相关的数据。例如，如果启用级联删除并且从Customers表中删除某个顾客，则任何关联的订单行也会被自动删除。

唯一约束

唯一约束用来保证一列（或一组列）中的数据是唯一的。它们类似于主键，但存在以下重要区别。

表可包含多个唯一约束，但每个表只允许一个主键。
唯一约束列可包含NULL值。
唯一约束列可修改或更新。
唯一约束列的值可重复使用。
与主键不一样，唯一约束不能用来定义外键。

employees表是一个使用约束的例子。每个雇员都有唯一的社会安全号，但我们并不想用它作主键，因为它太长（而且我们也不想使该信息容易利用）。因此，每个雇员除了其社会安全号外还有唯一的雇员ID（主键）。雇员ID是主键，可以确定它是唯一的。你可能还想使DBMS保证每个社会安全号也是唯一的（保证输入错误不会导致使用他人号码）。可以通过在社会安全号列上定义UNIQUE约束做到。

唯一约束既可以用UNIQUE关键字在表定义中定义，也可以用单独的CONSTRAINT定义

检查约束

检查约束用来保证一列（或一组列）中的数据满足一组指定的条件。

//OrderItems表施加了检查约束，它保证所有物品的数量大于0
CREATE TABLE OrderItems (   
 order_num     INTEGER     NOT NULL,  
  order_item    INTEGER     NOT NULL,  
  prod_id       CHAR(10)    NOT NULL,   
  quantity      INTEGER     NOT NULL CHECK (quantity > 0), 
  item_price    MONEY       NOT NULL );

索引

索引用来排序数据以加快搜索和排序操作的速度。想像一本书后的索引（如本书后的索引），可以帮助你理解数据库的索引。

数据库索引的作用也一样。主键数据总是排序的，这是DBMS的工作。因此，按主键检索特定行总是一种快速有效的操作。但是，搜索其他列中的值通常效率不高。例如，如果想搜索住在某个州的客户，怎么办？因为表数据并未按州排序，DBMS必须读出表中所有行（从第一行开始），看其是否匹配。这就像要从没有索引的书中找出词汇一样。

解决方法是使用索引。可以在一个或多个列上定义索引，使DBMS保存其内容的一个排过序的列表。在定义了索引后，DBMS以使用书的索引类似的方法使用它。DBMS搜索排过序的索引，找出匹配的位置，然后检索这些行。

注意：

索引改善检索操作的性能，但降低了数据插入、修改和删除的性能。在执行这些操作时，DBMS必须动态地更新索引。
索引数据可能要占用大量的存储空间。
并非所有数据都适合做索引。取值不多的数据（如州）不如具有更多可能值的数据（如姓或名），能通过索引得到那么多的好处。
索引用于数据过滤和数据排序。如果你经常以某种特定的顺序排序数据，则该数据可能适合做索引。
可以在索引中定义多个列（例如，州加上城市）。
这样的索引仅在以州加城市的顺序排序时有用。如果想按城市排序，则这种索引没有用处。

所用到的表参考

Vendors 供应商表

Products 产品目录表

Customers 客户信息表

Orders 用户订单表

OrderItems表存储每个订单中的实际物品

image.png

字符串数据类型

最常用的数据类型是字符串数据类型。它们存储字符串，如名字、地址、电话号码、邮政编码等。有两种基本的字符串类型，分别为定长字符串和变长字符串

定长字符串接受长度固定的字符串，其长度是在创建表时指定的。例如，名字列可允许30个字符，而社会安全号列允许11个字符（允许的字符数目中包括两个破折号）。定长列不允许多于指定的字符数目。它们分配的存储空间与指定的一样多。因此，如果字符串Ben存储到30个字符的名字字段，则存储的是30个字符，缺少的字符用空格填充，或根据需要补为NULL

变长字符串存储任意长度的文本（其最大长度随不同的数据类型和DBMS而变化）。有些变长数据类型具有最小的定长，而有些则是完全变长的。不管是哪种，只有指定的数据得以保存（额外的数据不保存

既然变长数据类型这样灵活，为什么还要使用定长数据类型？答案是性能。DBMS处理定长列远比处理变长列快得多。此外，许多DBMS不允许对变长列（或一个列的可变部分）进行索引，这也会极大地影响性能

image.png

数值数据类型

image.png

最后编辑于：2018.05.29 18:53:17

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 194,524评论 5赞 460
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 81,869评论 2赞 371
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 141,813评论 0赞 320
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,210评论 1赞 263
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,085评论 4赞 355
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,117评论 1赞 272
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,533评论 3赞 381
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,219评论 0赞 253
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,487评论 1赞 290
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,582评论 2赞 309
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,362评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,218评论 3赞 312
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,589评论 3赞 299
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 28,899评论 0赞 17
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,176评论 1赞 250
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,503评论 2赞 341
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,707评论 2赞 335

SQL 基础笔记

SELECT 语句

检索多个列

WHERE子句指定搜索条件

组合 组合WHERE子句

NOT操作符

用通配符进行过滤

LIKE操作符

1.最常使用的通配符是百分号（%)，%表示任何字符出现任意次数

2.通配符下划线（_）。下划线的用途与%一样，但它只匹配单个字符，而不是多个字符

3.方括号（[ ]）通配符用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符

计算字段

拼接

问题：

大多数SQL实现支持以下类型的函数。

日期和时间 处理函数

分组数据 分组数据

SELECT子句顺序

利用子查询进行过滤

联结表（join)

联结多个表

使用表别名

自联结

自然联结

外联结

组合查询

可用UNION操作符来组合数条SQL查询。利用UNION，可给出多条SELECT语句，将它们的结果组合成一个结果集。

数据插入

从一个表复制到另一个表

更新数据

删除数据

创建表

更新表

删除表

管理事务处理

约束

主键

外键

唯一约束

唯一约束既可以用UNIQUE关键字在表定义中定义，也可以用单独的CONSTRAINT定义

检查约束

索引

注意：

所用到的表参考

字符串数据类型

数值数据类型

推荐阅读更多精彩内容

组合组合WHERE子句

日期和时间处理函数

分组数据分组数据