目录
1、检索不同的值(类似于取集合):用DISTINCT关键字
SELECT DISTINCT id
FROM List;
2、限制输出结果的数目:用TOP关键字
SELECT TOP 5 id
FROM List;
如果使用的MySQL,也可以用下面语句(出来单独一列前五数据):LIMIT
SELECT id FROM List LIMIT 5
指定检索起点和行数:OFFSET
SELECT id
FROM List
LIMIT 4 OFFSET 3;
#在MySQL中等同于SELECT id FROM List LIMIT 3,4
第一个4代表检索的行数,第二个5代表从第3行开始(最开始是第0行)。
3、排序检索数据
- 按列排序
取一个或多个列的名字进行排序:ORDER BY
SELECT id
FROM List
ORDER BY id;
注意:ORDER BY的位置一定是最后的子句,否则会报错,另外排序也可以选择非检索数据。
- 按多列排序
SELECT prod_id,prod_price,prod_name
FROM Prod_list
OREDR BY prod_price,prod_name; --先对价格排序,价格相同的在对物品名称排序
- 按列位置排序
SELECT prod_id,prod_price,prod_name
FROM Prod_list
OREDR BY 2,3; --先对检索的第二列(prod_price)排序,然后对检索的第三列(prod_name)进行排序
- 指定排序方向
降序:在ORDER BY后指定关键字DESC
SELECT prod_id,prod_price,prod_name
FROM Prod_list
OREDR BY prod_price DESC;
多个列中按之一进行降序
SELECT prod_id,prod_price,prod_name
FROM Prod_list
OREDR BY prod_price DESC, prod_name; --最贵的排前面,然后产品名还是按字母顺序
4、过滤数据
- 按条件搜索数据:WHERE(位置在表名即FROM子句之后)
SELECT prod_name,prod_price
FROM Prod_list
WHERE prod_price = 3.9;
- 不匹配检查
SELECT prod_id,prod_name
FROM Prod_list
WHERE prod_id<>'DLL01'; --列出所有不是DLL01生产的产品,其中<>和!=可以互换
- 范围值检查:BETWEEN … AND …
SELECT prod_price,prod_name
FROM Prod_list
WHERE prod_price BETWEEN 5 AND 10; --筛选价格位于5至10之间的产品
- 空值检查:WHERE … IS NULL
- 组合WHERE子句,其中操作符优先级:()>AND>OR
- IN操作符
SELECT prod_name,prod_price
FROM Prod_list
WHERE prod_id IN ('DLL01','BRS01') --等价于WHERE prod_id='DLL01' OR prod_id='BRS01'
ORDER BY prod_name;
- NOT操作符:否定其后所跟的任何条件
SELECT prod_name
FROM Prod_list
WHERE NOT prod_id='DLL01' --等价于WHERE prod_id != 'DLL01'
ORDER BY prod_name;
5、用通配符进行过滤
前面所介绍的所有操作符都是基于已知值进行过滤的,下面用通配符就好比正则表达式。
谓词不是操作符:LIKE(用于字符串)
- %通配符:表示任何字符出现任意次数
SELECT prod_id,prod_name
FROM Prod_list
WHERE prod_name LIKE 'Fish%'; --匹配以词Fish起头的产品
- _通配符:只匹配单个字符
SELECT prod_price,prod_name
FROM Prod_list
WHERE prod_price LIKE '__ yuan' --匹配价格为两位数的产品
- []通配符:指定一个字符集,必须匹配指定位置的一个字符(MySQL不支持)
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%' --匹配所有以J或者M开头的联系人
ORDER BY cust_contact;
- 否定符:^
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%' --匹配所有除了J和M开头的联系人,等价于WHERE NOT cust_contact LIKE '[JM]%'
ORDER BY cust_contact;
6、计算字段
与前面表中的列意思基本相同,但其并不实际存在于数据库表中。
- 拼接字段
把两个列拼接起来成为一个新列,在SQL的SELECT语句中一般使用+和||来拼接,但根据DBMS的不同,方式有所不同,如在MySQL中使用特殊函数Concat.
SELECT Concat(prod_name,'(',prod_country,')')
FROM Prod_list
ORDER BY prod_name;
- 去掉字符串中的空格
RTRIM():去掉字符串右边的空格
LTRIM():去掉字符串左边的空格
TRIM():去掉字符串左右两边的空格 - 赋予字段替换名:AS
SELECT Concat(RTRIM(prod_name) ,' (' ,RTRIM(prod_country) , ')')
AS prod_title
FROM Prod_list
ORDER BY prod_name;
- 算数计算字段:对检索数据进行算数计算
SELECT prod_id,quantity,prod_price,
quantity*prod_price AS expanded_price
FROM Prod_list
WHERE order_num=20008;
7、函数
下面函数主要用于MySQL
- 提取字符串的组成部分:SUBSTRING()
- 数据类型转换:CONVERT()
- 取当前日期:CURDATE()
可移植:所编写的代码可以在多个系统上运行。SQL函数不是可移植的。
4. SOUNDEX():将任何文本串转化为描述其语音表示的字母数字模式的算法,这个函数可以用于在匹配发音相似但拼写不准确的人名上
8、汇总数据
- 聚集函数:对某些行运行的函数,计算并返回一个值。
AVG():返回某列的平均值
SELECT AVG(prod_price) AS avg_price
FROM Prod_list
WHERE prod_id='DLL01';
COUNT():确定表中行的数目或符合特定条件的行的数目,其有以下两种使用方式:
COUNT(*):对列中无论NULL值还是非空值均计数。
COUNT(column):对列中具有值的行进行计数,忽略NULL值。其中column为指定的列名。
MAX(),MIN(),SUM():列中最大,列中最小,以及对列求和。SUM()忽略值为NULL的行。
- 聚集不同值:在聚集函数中添加DISTINCT参数,以AVG函数为例
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Prod_list
WHERE prod_id='DLL01';
9、分组数据
- 创建分组:GROUP BY
SELECT prod_id,COUNT(*) AS num_prods
FROM Prod_list
GROUP BY prod_id; --统计各个供货商id的产品数
注意:GROUP BY子句必须出现在WHERE子句之后,ORDER BY子句之前。
- 过滤分组:筛选哪些分组,排除哪些分组,必须基于完整的分组而不是个别的行进行过滤的。
HAVING:类似于WHERE,但是唯一差别是,WHERE过滤行,而HAVING过滤分组。
SELECT cust_id,COUNT(*) AS orders
FROM Orders --一个订单一个条目
GROUP BY cust_id
HAVING COUNT(*) >= 2; --筛选出订单至少为2个的所有顾客,用WHERE无法实现
- GROUP BY 和ORDER BY混合使用:例如对订购物品的数目至少为3个的订单号基于数目排序输出
SELECT order_num,COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING count(*)>=3
ORDER BY items,order_num;
- SELECT子句顺序:SELECT 、FROM 、WHERE 、GROUP BY 、HAVING 、ORDER BY
10、子查询
把第一个查询的结果当作条件去继续查询,查询顺序总是为由内向外查询。
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
FROM OrderItems
WHERE prod_id='RGAN01');
作为计算字段使用子查询
SELECT cust_name,
cust_state,
(SELECT COUNT(*)
FROM Orders
WHERE Orders.cust_id=Customers.cust_id) AS orders
FROM Customers
ORDER BY cust_name;
11、联结表
在数据查询的执行中联结表是SQL最强大的功能之一。
- 关系表
对于一个产品目录的数据库表,其中每一类物品占一行,对于每一种物品,要储存的信息包括产品描述、价格,以及生产该产品的供应商。
因为同一供应商生产的多种物品,那么在何处存储供应商名、地址、联系方式等供应商信息?将这些数据与产品信息分开存储好处:
(1)同一供应商生产的每个产品,其供应商的信息相同,如果对每个产品重复此信息即浪费时间又浪费存储空间。
(2)若供应商信息发生变化,只需要修改一次即可。
(3)如果有重复数据(即每种产品都存储供应商信息),则很难保证每次输入该数据的方式都相同。不一致的数据在报表中就很难利用。
关系数据可以有效地存储,方便处理,因此,关系数据库的可伸缩性远比非关系数据库要好。
可伸缩:能适应不断增加的工作量而不失败。设计良好的数据库或应用程序称为可伸缩性好。 - 创建联结
SELECT vend_name,prod_name,prod_price
FROM Vendors,Products
WHERE Vendors.vend_id=Products.vend_id;
- WHERE子句
笛卡尔积:由没有联结条件的表关系返回的结果。检索出的行的数目将是第一个表中的行数乘以第二个表中的行数。
实例中的笛卡尔积返回的数据用每个供货商匹配每个产品,即使供货商根本没有生产的产品。
所以要保证所有联结都有WHERE子句,否则过滤条件将会返回许多不正确的问题。
叉联结:返回笛卡尔积的联结 - 内联结
等值联结:基于两个表间的相等测试,这种联结也称为内联结。
对于这种联结可以使用稍微不同的语法,明确指定联结的类型,下面的SELECT语句返回与前面例子完全相同的数据:
SELECT vend_name,prod_name,prod_price
FROM Vendors
INNER IOIN Products ON Vendors.vend_id=Products.vend_id;
- 联结多个表
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 --WHERE中前两句是关联联结中的表
AND order_num=20007; --这个是筛选数据的条件
12、创建高级联结
- 使用表别名:SQL除了可以对列名和计算字段使用别名,还允许给表名起别名。
列别名语法:
SELECT RTRIM(vend_name)+'('+RTRIM(vend_country)+')' AS vend_title
FROM Vendors
ORDER BY vend_name;
表别名语法:
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';
注意:Oracle不支持AS关键字,若在Oracle中使用别名,应该是Customers C,而不是Customers AS C。另外,表别名只在查询执行中使用,与列别名不同,表别名不返回到客户端。
- 其他类型联结:自联结、自然联结、外联结
(1)自联结:举个例子,假如要给与Jim Jones同一公司的所有顾客发送一封信件,首先用前面学到的子查询解决:
SELECT cust_id,cust_name,cust_contact
FROM Customers
WHERE cust_name=(SELECT cust_name
FROM Customers
WHERE cust_contact='Jim Jones');
自联结:
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';
注意:此查询需要的两张表实际上是相同的表,因此Customers在FROM子句中出现了两次,在子查询中用的是同一张表,使用表别名是为了避免歧义。
(2)自然联结:无论何时对表进行联结,应该至少有一列不止出现在一个表中(被联结的列)。前面介绍的内联结返回所有数据,相同的列甚至多次出现。自然联结排除多次出现,使每一列只返回一次。自然联结要求只能选择那些唯一的列,一般通过对一个表使用通配符(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';
(3)外联结:许多联结将一个表中的行与另一个表中的行相关联,但有时候需要包含没有关联行的那些行。例如:
- 对每个顾客下的订单进行计数,包括那些迄今为止尚未下订单的顾客
- 列出所有产品以及订购数量,包括没有人订购的产品
- 计算平均销售规模,包括那些至今尚未下订单的顾客。
左外联结:
SELECT Customers.cust_id,Orders.order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id=Order.cust_id; --从FROM子句左边的表中选择所有行
右外联结:
SELECT Customers.cust_id,Orders.order_num
FROM Customers
RIGHT OUTER JOIN Orders ON Customers.cust_id=Order.cust_id; --从FROM子句右边的表中选择所有行
- 带聚集函数的联结
聚集函数用来汇总数据,举个例子,要检索所有顾客及每个顾客所下的订单数,用COUNT()函数实现
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;
13、组合查询
使用UNION,可以给出多条SELECT语句,将他们的结果组合成一个结果集。UNION从查询结果集中自动去除了重复的行,若想不取消重复的行,就用UNION ALL。
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必须由两条及以上的SELECT语句组成,语句用关键字UNION分隔
- UNION中的每个查询必须包含相同的列、表达式或聚集函数(不过,各个列不需要以相同的次序列出)
- 列数据类型必须兼容:类型不必完全相同,但必须是DBMS可以隐含转换的类型(例如,不同的数值类型或不同的日期类型)
- 在对组合查询结果排序时,只能使用一条ORDER BY子句,他必须位于最后一条SELECT语句之后。
14、插入数据
INSERT:将行插入到数据库表中。
- 插入完整的行:要求指定表名和插入到新行中的值。每列提供一个值,否则用NULL代替。
INSERT INTO Customers
VALUES(10000000006,
'Toy Land',
'123 Any Street',
'New York',
'NY',
NULL,
NULL);
注意:这种语法简单,但不安全,依赖于表中列的定义次序,而且无法保证下一次表结构变动后保持完全相同的次序。
因此,更安全的语法(但是更繁琐,这种语法可以省略列,省略的列不提供值)如下:
INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
VALUES(10000000006,
'Toy Land',
'123 Any Street',
'New York',
'NY',
'11111',
'USA',
NULL,
NULL);
- 插入检索出的数据
例子:把另一张表中的顾客列合并到Customers表中,不需要每次读取一行再将它用INSERT插入,可以如下:
INSERT INTO Customers(cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email)
SELECT cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
FROM CustNew; --注意CustNew表中不应该存在Customers中用过的cust_id值,因为此为主键,唯一,否则INSERT操作会失败。
- 将表复制到另一个新表
CREATE SELECT语句:
CREATE TABLE CustCopy AS SELECT * FROM Customers;
如果使用的是SQL Server,语法可以如下:
SELECT * INTO CustCopy FROM Customers; --要想只复制部分列,可以将*换成明确的列名
15、更新或删除数据
- 更新数据
修改数据(UPDATE),注意不要忽略WHERE子句,因为稍不注意,就会更新表中的所有行。
例子:客户10002现在有了电子邮件地址以及联系人,因此其记录需要更新:
UPDATE Customers
SET cust_contact='Sam Roberts',
cust_email='sam@toyland.com'
WHERE cust_id=10002;
删除某列的值,设置值为NULL,注意其与保存空字符串很不同,’'表示的还是一个值,而NULL表示没有值。
UPDATE Customers
SET cust_email=NULL
WHERE cust_id=10002;
- 删除数据
DELETE:从Customers表中删除一行,删除整行而不是列,若想删除指定的列,用UPDATE语句:
DELETE FROM Customers
WHERE cust_id=10002;
外键:联结表中的公用字段,即关联两张表的公共列,存在外键时,DBMS使用它们实施引用完整性。
16、创建和操纵表
CREATE TABLE:创建表常见信息如下:
新表的名字,在关键字CREATE TABLE之后给出;
表列的名字和定义,用逗号分隔;
有的DBMS还要求指定表的位置。
- NULL值设置
CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL, --NOT NULL列表示状态,在插入行时不接受没有列值的行
vend_id CHAR(10) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
prod_desc VARCHAR(1000) NULL --NULL列表示插入行时允许不给出该列的值,默认设置,忽略时就是NULL状态
);
注意:在设置主键时,一定致命状态为NOT NULL,因为允许NULL值的列不能作为唯一标识。
- 指定默认值:DEFAULT
CREATE TABLE Products
(
prod_id CHAR(10) NOT NULL,
vend_id CHAR(10) NOT NULL,
prod_price DECIMAL(8,2) NOT NULL,
quantity INTEGER NOT NULL DEFAULT 1, --插入行如果不给出值,默认为1
prod_desc VARCHAR(1000) NULL
);
默认值经常用于日期或时间戳列,MySQL指定DEFAULT CURRENT_DATE()。
- 更新表:ALTER TABLE
给表增加列
ALTER TABLE Vendors
ADD vend_phone CHAR(20);
注意:使用ALTER TABLE要极为小心,应该在进行改动前做完整的备份(表结构和数据的备份)。数据库表的更改不能撤销,如果增加了不需要的列,也许无法删除他们。
- 删除表:DROP TABLE
删除整张表,而不是其中部分内容,执行该语句将永久删除该表,不能撤销。
DROP TABLE CustCopy;
- 重命名表:RENAME,要求指定旧表名和新表名。
17、视图
视图是虚拟的表,与包含数据的表不一样,视图只包含使用时动态检索数据的查询。
例如从三个表中检索数据:
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';
现在可以把整个查询包装成一个名为ProductCustomers的虚拟表,则可以如下轻松地检索出相同的数据:
SELECT cust_name,cust_contact
FROM ProductCustomers
WHERE prod_id='RGAN01';
- 创建视图:CREATE VIEW,只能用于创建不存在的视图。在覆盖(更新)视图,必须先删除它,然后再重新创建,其语法为DROP VIEW viewname。
CREATE VIEW ProductCustomers AS
SELECT cust_name,cust_contact,prod_id
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id=Orders.cust_id
AND OrderItems.order_num=Orders.order_num;
- 用视图重新格式化检索出的数据
前面我们介绍了用RTRIM或者||语法来格式化检索结果,假设经常需要这个格式的结果,我们不必每次需要执行那种拼接,而是创建一个视图。
CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name)+'('+RTRIM(vend_country)+')'
AS vend_title
FROM Vendors;
检索数据进行:
SELECT * FROM VendorLocations;
- 用视图过滤不想要的数据
创建CustomerEMailList视图,过滤没有电子邮件地址的顾客。
CREATE VIEW CustomerEMailList AS
SELECT cust_id,cust_name,cust_email
FROM Customers
WHERE cust_email IS NOT NULL;
- 使用视图与计算字段
SELECT prod_id,quantity,prod_price,
quantity*prod_price AS expanded_price
FROM Prod_list
WHERE order_num=20008;
将其转化为一个视图:
CREATE VIEW OrderItemsExpanded AS
SELECT prod_id,quantity,prod_price,
quantity*prod_price AS expanded_price
FROM Prod_list
在对创建好的视图进行检索
SELECT *
FROM OrderItemsExpanded
WHERE order_num=20008;
18、存储过程(介绍)
概念:为以后使用而保存的一条或者多条SQL语句。可将其视为批文件。
为什么使用存储过程?简单、安全、高性能
- 执行存储过程:EXECUTE
- 创建存储过程:CREATE PROCEDURE
19、游标
- 游标:是一个存储在DBMS服务器上的数据库查询,它不是一条SELECT语句,而是被该语句检索出来的结果集。
作用:在检索出来的行中前进或者后退一行或多行。存储游标后,应用程序可根据需要滚动或浏览其中的数据。 - 创建游标:DECLARE
例如创建一个游标来检索没有电子邮件地址的所有顾客,帮助操作人员来找空缺的电子邮件地址。
DECLARE CustCursor CURSOR --CustCursor为定义的游标名
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;
- 使用游标:OPEN CURSOR
OPEN CURSOR CustCursor
访问游标数据:FETCH,指出要检索哪些行,从何处检索它们以及将它们放于何处。
- 关闭游标
CLOSE CustCursor
游标在使用完毕后需要关闭,再次使用它时不需要再声明,只用OPEN打开即可。