1 了解SQL
数据库是指一个以某种有组织的方式存储的数据集合。
表是一种特定类型数据的结构化清单,通过表名才能标识自己。
表由列组成。列存储表中某部分的信息。每个列有相应的数据类型,限定了可存储在列中的数据种类。
表中的数据按行存储,所保存的每个记录存储在自己的行内。
主键是表中的一列或多列,用于唯一标识表中每一行。主键需要满足如下要求:(1)任何两行都不具有相同的主键值(2)每一行都必须有一个主键值(3)主键列中的值不允许修改或更新(4)主键值不能重用。
SQL是用于与数据库沟通的语言。
2 检索数据
使用SELECT语句从表中检索一个或多个数据列。
2.1 检索单个列
SELECT prod_name FROM Products; 表示从表Products中检索列prod_name
数据库管理系统需要在单条SQL语句后加分号。SQL语句不区分大小写。在处理SQL语句时忽略空格。
2.2 检索多个列
SELECT prod_id,prod_name,prod_price FROM Products; 检索多列加逗号
SQL语句一般返回原始的、无格式的数据
2.3 检索所有列
SELECT * FROM Products; 用*表示返回表中的所有列。
2.4 检索不同的值
SELECT DISTINCT vend_id FROM Products; 只返回所有列中不同的值
2.5 限制结果
SELECT语句默认返回指定表中所有匹配的行,如果只是想返回第一行或一定数量的行,可以通过TOP关键字实现。
SELECT TOP 5 prod_name FROM Products; 只检索前5行(SQL Server和Access)
SELECT prod_name FROM Products FETCH FIRST 5 ROWS ONLY; (DB2)
SELECT prod_name FROM Products WHERE ROWNUM<=5; (Oracle)
SELECT prod_name FROM Products LIMIT 5; (MySQL、MariaDB、PostgreSQL、SQLite)
SELECT prod_name FROM Products LIMIT 4 OFFSET 3;返回从第3行起的4行数据 简化写法:LIMIT 3,4
2.6 使用注释
行内注释 -- 多行注释/* */
3 排序检索数据
ORDER BY
3.1 排序数组
默认检索出的数据顺序是其底层表中出现的顺序显示。
SELECT prod_name FROM Products ORDER BY prod_name; 对prod_name列以字母顺序排序。ORDER BY语句必须是SELECT语句的最后一条子句。
3.2 按多个列排序
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price,prod_name; 只有在有相同的price时才按name排序
3.3 按列位置排序
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY 2,3; 按第2、3列排序
3.4 指定排序方向
默认排序顺序是升序。可以通过关键字DESC实现降序。
SELECT prod_id,prod_price,prod_name FROM Products ORDER BY prod_price DESC,prod_name; DESC只作用于其前面的列名。
默认数据库排序时不区分大小写,即a和A一样。
4 过滤数据
WHERE
4.1 使用WHERE子句
通常只会根据特定的操作或条件提取表数据的子集。WHERE语句在表名之后给出。
SELECT prod_name,prod_price FROM Products WHERE prod_price=3.49; 只返回price等于3.49的行
同时使用ORDER BY和WHERE语句时,ORDER BY应该位于WHERE之后。
4.2 WHERE子句操作符
所有的条件操作符:
操作符 | 说明 | 操作符 | 说明 |
= | 等于 | > | 大于 |
<> | 不等于 | >= | 大于等于 |
!= | 不等于 | !> | 不大于 |
< | 小于 | BETWEEN | 在指定的两个值之间 |
<= | 小于等于 | IS NULL | 为NULL值 |
!< | 不小于 |
SELECT prod_name,prod_price FROM Products WHERE prod_price<10;
SELECT vend_id,prod_name FROM Products WHERE vend_id != 'DLL01'; 列出不是供应商DLL01制造的产品,单引号来限定字符串。
SELECT prod_name,prod_price FROM Products WHERE prod_price BETWEEN 5 AND 10; 检索5美元到10美元之间的值
SELECT prod_name FROM Products WHERE prod_price IS NULL; 返回所有没有价格(空prod_price字段)的产品
5 高级数据过滤
5.1 组合WHERE子句
SQL允许使用多个WHERE子句,以AND子句或OR子句的方式使用。
(1)AND操作符
通过不止一个列进行过滤,可以使用AND操作符给WHERE子句附加条件。AND相当于&&
SELECT prod_id,prod_price,prod_name FROM Products WHERE vend_id = 'DLL01' AND prod_price <=4; 列出由供应商DLL01制造的价格小于等于4美元的产品。
(2)OR操作符
OR操作符与AND操作符正好相反,表示匹配任一条件的行。OR相当于||
SELECT prod_name FROM Products WHERE vend_id = 'DLL01' OR vend_id = 'BRS01'; 列出供应商DLL01或供应商BRS01制造的产品
(3)求值顺序
WHERE子句中可以包含任意数目的AND和OR操作符。但在使用的过程中可能会出现组合问题,因此需要用圆括号明确地分组操作符。
SELECT prod_name,prod_price FROM Products WHERE (vend_id = 'DLL01' OR vend_id = 'BRS01') AND prod_price >= 10;
5.2 IN操作符
IN操作符用来指定条件范围,范围中每个条件可以进行匹配。IN取一组由逗号分隔、括在圆括号中的合法值。
SELECT prod_name,prod_price FROM Products WHERE vend_id IN ('DLL01','BRS01'); 列出供应商DLL01和BRS01制造的产品
可以发现IN完成了和OR操作符一样的功能,其优点如下:在多个合法项时,IN操作符的语法更清楚直观;与其他AND和OR操作符组合使用IN时,求值顺序更容易管理;IN操作符一般比OR快;IN的最大优点就是可以包含其他SELECT语句。
5.3 NOT操作符
NOT用于否定其后条件的关键字。NOT关键字可以用在要过滤的列前,而不仅是在其后。
SELECT prod_name FROM Products WHERE NOT vend_id = 'DLL01';
6 用通配符进行过滤
6.1 LIKE操作符
前面的所有操作符都是针对已知值进行过滤。但要搜索比如包含文本bean的所有产品时,这种方法并不适用。可以通过通配符实现。通配符是用来匹配值的一部分的特殊字符。
LIKE操作符表示后面的搜索模式利用通配符匹配而不是简单的相等匹配进行比较。通配符搜索只能用于文本字段,非文本数据类型字段不能使用通配符搜索。
(1)百分号%通配符(*,在Microsoft Access中)
%表示任何字符出现任意次数,0,1或多次。并不能用来匹配NULL
SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE 'Fish%'; 使用了搜索模式找以Fish开头的记录
SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE '%bean bag%'; 使用了多个通配符,匹配任何位置上出现文本bean bag的值。
SELECT prod_name FROM Products WHERE prod_name LIKE 'F%y'; 找出以F起头、y结尾的所有产品
(2)下划线(_)通配符(?)
与%功能一样,但只匹配单个字符,而不是多个字符。
SELECT prod_id,prod_name FROM Products WHERE prod_name LIKE '_ inch teddy bear'; 匹配单个字符
(3)方括号([])通配符
用来指定一个字符集,必须匹配通配符内的一个字符。
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; 找出除J和M以外起头的联系人
6.2 通配符使用技巧
通配符搜索一般比前面讨论的其他搜索耗时更长,因此有下面技巧:
不要过度使用通配符;尽量不要把通配符放在搜索模式的开始处
7 创建计算字段
7.1 计算字段
有时候需要从数据库中检索出转换、计算或格式化过的数据,不是检索出数据,然后再在客户端重新格式化。通过计算字段实现。字段的概念与列相同。
7.2 拼接字段
创建由两列组成的标题,则需要将这两列拼接到一起构成单个值。操作符为+(Access和SQL server),||(Oracle,SQLite),Concat(MySQL)
SELECT RTRIM(vend_name) + ' (' + vend_country + ')' FROM Vendors ORDER BY vend_name; 将vend_name与vend_country拼接生成一个新字段,并去除vend_name后面多余的空格。
RTRIM()去除字符串右边的空格,LTRIM()去除字符串左边的空格,TRIM()去除字符串左右两边的空格
可以发现上述拼接生成的字段并没有名字,则不能用于客户端应用中,因此需要别名。关键字为AS
SELECT RTRIM(vend_name) + ' (' + vend_country + ')' AS vend_title FROM Vendors ORDER BY vend_name; 将vend_name与vend_country拼接生成一个新字段,取名为vend_title
7.3 执行算术计算
对检索的数据进行算术计算。例如通过单价和数量得到总价格
SELECT prod_id,quantity,item_price,quantity*item_price AS expanded_price FROM OrderItems WHERE order_num = 20008;
8 使用函数处理数据
8.1 函数
SQL可以用函数来处理数据。不同的DBMS有着不同的函数。
函数 | 语法 |
提取字符串的组成部分 | Access使用MID();DB2、Oracle、SQLite使用SUBSTR();MySQL和SQLServer使用SUBSTRING() |
数据类型转换 | Access和Oracle每个类型都有一个转换函数;DB2使用CAST();MariaDB、MySQL和SQL Server使用CONVERT() |
取当前日期 | Access使用NOW();DB2和PostgreSQL使用CURRENT_DATE;MariaDB和MySQL使用CURDATE();Oracle使用SYSDATE;SQL Server使用GETDATE();SQLite使用DATE() |
8.2 使用函数
大多数SQL实现支持以下类型的函数:(1)处理文本字符串(删除或填充值,转换值为大写或小写)的文本函数(2)在数值数据上进行算术操作(返回绝对值、代数运算)的数值函数(3)用于处理日期和时间值并从这些值中提取特定成分(返回两个日期差、检查日期有效性)的日期和时间函数。(4)返回DBMS正使用的特殊信息(如返回用户登录信息)的系统函数。
(1) 文本处理函数
SELECT vend_name,UPPER(vend_name) AS vendd_name_upcase FROM Vendors ORDER BY vend_name; 通过upper()函数来将文本转换为大写。
常见的文本处理函数
函数 | 说明 |
LEFT()(或使用子字符串函数) | 返回字符串左边的字符 |
LENGTH()(DATALENGTH()或LEN()) | 返回字符串的长度 |
LOWER()(LCASE()) | 将字符串转换为小写 |
LTRIM() | 去掉字符串左边的空格 |
RIGHT()(或使用子字符串函数) | 返回字符串右边的字符 |
RTRIM() | 去掉字符串右边的空格 |
SOUNDEX() | 返回字符串的SOUNDEX值 |
UPPER()(UCASE()) | 将字符串转为大写 |
其中SOUNDEX是一个将任意文本串转换为描述其语音表示的字母数字模式的算法,能够对字符串进行发音比较而不是字母比较。
SELECT cust_name,cust_contact FROM Customers WHERE SOUNDEX(cust_contact) = SOUNDEX('Michael Green'); 从而可以找到Michelle Green的记录
(2)日期和时间处理函数
日期和时间采用相应的数据类型存储在表中,在不同数据库中有不同的时间处理函数
以从日期中取年份为例:
SQL Server:SELECT order_num FROM Orders WHRER DATEPART(yy,order_date) = 2012; 寻找年份为2012的订单
Access:SELECT order_num FROM Orders WHRER DATEPART('yyyy',order_date) = 2012;
PostgreSQL:SELECT order_num FROM Orders WHRER DATE_PART('year',order_date) = 2012;
Oracle:SELECT order_num FROM Orders WHRER to_number(to_char(order_date,'YYYY')) = 2012;
MySQL/MariaDB:SELECT order_num FROM Orders WHRER YEAR(order_date) = 2012;
SQLite:SELECT order_num FROM Orders WHRER strftime('%Y',order_date) = 2012;
(3)数值处理函数
用于代数、三角或几何运算。
函数 | 说明 |
ABS() | 返回一个数的绝对值 |
COS() | 返回一个角度的余弦 |
EXP() | 返回一个数的指数 |
PI() | 圆周率 |
SIN() | 返回一个角度的正弦 |
SQRT() | 返回一个数的平方根 |
TAN() | 返回一个角度的正切 |
9 汇总数据
9.1 聚集函数
常常需要汇总数据而不是检索出来。比如以下功能:确定表中行数(或者满足某个条件或包含某个特定值的行数);获得表中某些行的和;找出表列的最大值、最小值、平均值。
函数 | 说明 |
AVG() | 返回某列的平均值 |
COUNT() | 返回某列的行数 |
MAX() | 返回某列的最大值 |
MIN() | 返回某列的最小值 |
SUM() | 返回某列值之和 |
(1)AVG()函数
AVG()可以用来返回所有列的平均值,也可以用来返回特定列或行的平均值
SELECT AVG(prod_price) AS avg_price FROM Products; 求prod_price的平均值
SELECT AVG(prod_price) AS avg_price FROM Products WHERE vend_id = 'DLL01'; 求特定行prod_price的平均值
(2)COUNT()函数
COUNT(*)对表中行的数目进行计数,不管表列中包含的是空值还是非空值。用COUNT(column)对特定列中具有值的行进行计数,忽略NULL值。
SELECT COUNT(*) AS num_cust FROM Customers;
SELECT COUNT(cust_email) AS num_cust FROM Customers;
(3)MAX()函数
返回指定列中的最大值。
(4)MIN函数
返回指定列的最小值。
(5)SUM()函数
返回指定列值的和。
SELECT SUM(item_price*quantity) AS total_price FROM OrderItems WHERE order_num = 20005;
9.2 聚集不同值
指定DISTINCT来包含不同值
9.3 组合聚集函数
一句SELECT语句可以包含多个聚集函数
SELECT COUNT(*) AS num_items MIN(prod_price) AS price_min,MAX(prod_price) AS price_max,AVG(prod_price) AS price_avg FROM OrderItems;
10 分组数据
GROUP BY子句和HAVING子句
10.1 创建分组
使用GROUP BY子句建立。
SELECT vend_id,COUNT(*) AS num_prods FROM Products GROUP BY vend_id; GROUP BY指示DBMS按vend_id排序并分组数据。
规定:GROUP BY子句可以包含任意数目的列,可以对分组进行嵌套,更细致地进行数据分组。如果在GROUP BY中嵌套了分组,数据将在最后指定的分组上进行汇总。子句中列出的每一列必须是检索列或有效的表达式。不允许GROUP BY列带有长度可变的数据类型。NULL将作为一个分组返回。必须出现在WHERE子句之后,ORDER BY子句之前。
10.2 过滤分组
可以规定包括哪些分组,排除哪些分组。必须基于完整分组而不是个别的行进行过滤。HAVING子句支持所有的WHERE操作符
SELECT cust_id,COUNT(*) AS orders FROM Orders GROUP BY cust_id HAVING COUNT(*) >= 2; 过滤出至少有两个订单的顾客
HAVING与GROUP BY一起使用,WHERE用于标准的行级过滤
10.3 分组和排序
ORDER BY和GROUP BY的区别
ORDER BY | GROUP BY |
对产生的输出排序 | 对行分组,但输出可能不是分组的顺序 |
任意列都可以使用(甚至非选择的列也可以使用) | 只能用于选择列或表达式列,必须使用每个选择列表达式 |
不一定需要 | 如果与聚集函数一起使用列,则必须使用 |
SELECT order_num,COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*) >= 3 ORDER BY items,order_num;
11 使用子查询
子查询是指嵌套在其他查询中的查询。
11.1 利用子查询进行过滤
例如要列出订购物品RGAN01的所有顾客:
(1)检索包含物品RGAN01的所有订单的编号(2)检索前一步骤列出的订单编号的所有顾客的ID(3)检索前一步骤所有顾客ID的顾客信息
可以发现上述三步可以把一条SELECT语句返回的结果用于另一条SELECT语句的WHERE子句,可以将3个查询组合起来。
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'));
子查询是从内往外扩散。
11.2 作为计算字段使用子查询
假如需要显示Customers表中每个顾客的订单总数。订单与相应的顾客ID存储在Orders中。操作步骤:
(1)从Customers中检索顾客列表(2)对于检索出的每个顾客,统计其在Orders中的订单数
SELECT cust_name,(SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;
子查询对检索的每个顾客执行一次操作。子查询中的WHERE使用完全限定列名,指定表名和列名:Orders.cust_id = Customers.cust_id,从而避免混淆。(之后可以用JOIN同样解决该问题)
12 联结表
12.1 联结
在SELECT中联结多个表查询。
关系表
例子:一个包含产品目录的数据库表,每类物品占一行,对于每个物品要存储产品描述、价格,以及生产该产品的供应商。分开存储的理由:(1)同一供应商生产的每个产品,其供应商信息都是相同的,对每个产品重复此信息既浪费时间又浪费存储空间(2)如果供应商信息发生变化,只需要修改一次(3)如果有重复数据很难保证每次输入该数据的方式相同。
因此建立两个表:一个存储供应商信息(Vendors),另一个存储产品信息(Products)。通过主键联系。
12.2 创建联结
SELECT vend_name,prod_name,prod_price FROM Vendors,Products WHERE Vendors.vend_id = Products.vend_id; 列出了两个表Vendors和Products,如果不使用WHERE子句会返回两个表的笛卡尔积,检索不出正确的数据。
12.2.1 内联结
之前使用的联结称为等值联结,这种联结也称为内联结。下面的语法与前面的例子作用一样:
SELECT vend_name,prod_name,prod_price FROM Vendors INNER JOIN Products ON Vendors.vend_id = Products.vend_id; 指明为内联结,标准写法,联结条件用特定的ON子句。
12.2.2 联结多个表
SQL一个SELECT语句可以联结多个表,但联结的表越多,性能下降越厉害。例如11.1的三条SELECT语句可以用下面一条代替:
SELECT cust_name,cust_contact FROM Customers,Orders,OrderItemts WHERE Customers.cust_id = Orders.cust_id AND OrderItems.order_num = Orders.order_num AND prod_id = 'RGAN01';
13 创建高级联结
13.1 使用表别名
前面可以对列取别名,并使用列的别名检索。为了缩短SQL语句,允许给表名取别名。例如将上面一条SQL语句转换一下:
SELECT cust_name,cust_contact FROM Customers AS C,Orders AS O,OrderItemts AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';
13.2 使用不同类型的联结
13.2.1 自联结
SELECT c1.cust_id,c1.cust_name,c1.cust_contact FROM Customers AS c1,Customer AS c2 WHERE c1.cust_name=c2.cust_name AND c2.cust_contact = 'Jim Jones';
c1和c2其实为同一个表。自联结通常用来替代从相同表中检索数据的使用子查询语句。
13.2.2 自然联结
无论何时对表进行联结,应该至少有一列不止出现在一个表中(被联结列)。如果标准的联结返回所有的数据,则相同的列会多次出现。自然联结就是用来排除该情况,使得每一列只返回一次。自然联结要求你只能选择那些唯一的列,通过对一个表使用通配符(*),而对其他表的列使用明确的子集来完成。
SELECT C.*,O.order_num,O.order_date,OI.prod_id,OI.quantity,OI.item_price FROM Customers AS C,Orders AS O,OrderItemts AS OI WHERE C.cust_id = O.cust_id AND OI.order_num = O.order_num AND prod_id = 'RGAN01';
13.2.3 外联结
许多联结将一个表中的行与另一个表中的行相关联,但有时候需要包含没有关联行的那些行。例如要检索没有订单顾客在内的所有顾客,使用外联结:
SELECT Customers.cust_id,Orders.order_num FROM Customers LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
上述语句会列出Customers中的所有行,如果有关联行则显示其order_num,如果没有关联则为NULL。
使用OUTER JOIN时,必须使用RIGHT或LEFT关键字指定包括所有行的表。
除了左外联结和右外联结外,还有全外联结,检索两个表的所有行,并关联。
13.3 使用带聚集函数的联结
例如需要完成以下工作:对每个顾客下的订单进行计数,包括哪些至今尚未下订单的顾客
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;
14 组合查询
14.1 组合查询
SQL允许执行多个查询,并将结果作为一个查询结果集返回,这些查询称为并(union)或者复合查询。
主要有两种情况需要使用组合查询:(1)在一个查询中从不同的表返回结构数据(2)对一个表执行多个查询,按一个查询返回数据
14.2 创建组合查询
用UNION操作符来组合数条SQL查询。
14.2.1 使用UNION
例子:假如需要Illinois、Indiana等州的所有顾客的报表,还要得到顾客Fun4All所有的报表。可以通过两条SELECT语句得到。组合这两条语句为:
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'; UNION中每个查询必须包含相同的列、表达式或聚集函数,列数据类型必须兼容。
UNION会自动去重,若要显示不去重信息,用UNION ALL。对结果排序的话将ORDER BY子句放在最后一个SELECT子句的后面。
15 插入数据
INSERT语句
15.1 数据插入
插入的三种方式:插入完整的行;插入行的一部分;插入某些查询的结果。
15.1.1 插入完整的行
需要指定表名和插入到新行中的值。
INSERT INTO Customers VALUES('100006','Toy Land','123','New York',NULL,NULL); 按顺序给每一列赋值,如果该列没有值用NULL表示,该插入方式严格依赖于表的结构顺序,并不安全,采用下面较为繁琐的方法。
INSERT INTO Customers(cust_id,cust_name,cust_address,cust_city,cust_contact,cust_email)
VALUES('100006','Toy Land','123','New York',NULL,NULL); 该插入方法可以不列出所有的列,如cust_contact可以省略,表会给出默认值。
15.1.2 插入检索的数据
可以将SELECT检索的结果插入表中。
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_contact, cust_email) SELECT cust_id, cust_name, cust_address, cust_city,cust_contact,cust_email FROM CustNew;
15.2 从一个表复制到另一个表
将一个表的内容复制到一个全新的表,使用SELECT INTO语句。
CREATE TABLE CustCopy AS
SELECT * INTO CustCopy FROM Customers; 创建一个新表CustCopy,将Customers复制到CustCopy中
16 更新和删除数据
UPDATE和DELETE
16.1 更新数据
两种方式:更新表中的特定行;更新表中的所有行。
UPDATE Customers SET cust_email = 'kim@thetoystore.com' WHERE cust_id = '1000000005';
可以通过将值更新为NULL来删除某一列的值。
16.2 删除数据
两种方式:删除表中的特定行;删除表中的所有行。
DELETE FROM Customers WHERE cust_id = '1000000006';
17 创建和操纵表
17.1 创建表
CREATE TABLE创建表,必须给出下列信息:
1、新表的名字,在关键字CREATE TABLE之后给出
2、表列的名字和定义,用逗号分隔
3、指定表的位置
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
);
NULL值表示没有值或缺值,允许NULL值的列允许在插入行时不给出该列的值。NULL为默认设置。
指定默认值:默认值用关键字DEFAULT指定。
CREATE TABLE OrderItems
(
order_num 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
);