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
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
image.png

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.用新的列布局创建一个新表;

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

删除表

//执行这条语句将永久删除该表。
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
image.png
image.png

推荐阅读更多精彩内容