《SQL必知必会》例子汇总——适合快速复习sql语句
《SQL必知必会》
第一章放狗屁的
第二章
1.搜索表Products中的prod_name
SELECT prod_name FROM Products;
2.检索表Products中的prod_id，prod_name，prod_price
SELECT prod_id, prod_name, prod_price FROM Products;
3.检索全部列
SELECT * FROM Products；
4.检索不同值（把相同的值过滤掉）
SELECT DISTINCT vend_id FROM Products；（注意DINTINCT会作用在所有后面跟着的列）
5.选择某一列的前面5个
SELECT TOP 5 vend_id FROM Products; (SQLServer 和 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、SQLite）
*LIMIT关键字完整用法：
从第六行开始的五行数据
SELECT prod_name FROM Product LIMIT 5 OFFSET 5；（注意第一行的行号是0）
6.注释写法：-- 这是一条注释 也可以用 /*这是一条注释*/ 来写
第三章
1.从Products中选择prod_name，并按照prod_name的字母顺序（由A到Z）排列
SELECT prod_name FROM Products ORDER BY prod_name；
2.从Products中选择prod_id，prod_price，prod_name，并按照prod_price由小到大，prod_name的字母顺序（由A到Z）排列
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；
-- 按照新生成的表的第2,3列输出的意思
*一些提示：①不论怎么order by，最后输出的内容都是按照SELECT的顺序来输出的。
②order by子句一定是最后写的。
3.从Products中选择prod_id, prod_price,prod_name，并按照prod_price递减排序
SELECT prod_id，prod_price，prod_name FROM Products ORDER BY prod_price DESC;
4.从Products中选择prod_id, prod_price,prod_name，并按照prod_price递减排序，prod_name字母顺序排列
SELECT prod_id，prod_price，prod_name FROM Products ORDER BY prod_price DESC，prod_name
*这里可以看出来，DESC(descending的缩写）只对一个列有效
*DESC的相反是ASC（ascending），但order by默认增序排序所以没用
第四章
1.检索prod_price和prod_name，要求prod_price = 3.49
SELECT prod_price，prod_name FROM Products Where prod_price = 3.49
2.列出所有价格小于等于10美元的产品
SELECT prod_name，prod_price FROM Products Where prod_price <= 10；
3.列出供应商不为DLL01的产品
SELECT prod_name，prod_price FROM Products Where vend_name <> DLL01（或者！=DLL01）；
4.列出价格在5到10美元的产品
SELECT prod_name，prod_price FROM Products Where prod_price BETWEEN 5 AND 10;
5.找出没有写产品价格的产品
SELECT prod_name FROM Products Where prod_price IS NULL;
*NULL指的是空值，不是写的0
第五章
1.检索由供应商DLL01制造且价格小于等于4美元的所有产品的名称和价格
SELECT prod_name，prod_price FROM Products Where vend_name = 'DLL01' AND prod_price <= 4;
2.检索由供应商为DLL01或供应商为BRS01制造的所有产品的名字和价格（分别用OR和IN操作符来写），并用产品名字顺序排序
SELECT prod_name,prod_price FROM Products Where vend_id = ‘DLL01’OR vend_id=‘BRS01’ORDER BY prod_name；
SELECT prod_name，prod_price FROM Products Where vend_id IN （‘DLL01’,‘BRS01’）ORDER BY prod_name；
3.检索由供应商为DLL01或供应商为BRS01制造且价格大于10美元的所有产品的名称和价格
SELECT prod_name，prod_price FROM Products WHERE （vend_id = ‘DLL01’OR vend_id=‘BRS01’）AND prod_price > 10；
*注意AND的优先级比OR更高，但更好的方法是不要依赖默认求值而是主动用括号来为他们规定顺序
4.检索供应商不为DLL01的产品名字和价格，按照产品名字来排列（使用NOT关键字）
SELECT prod_name，prod_price FROM Products Where NOT vend_id = ‘DLL01’;
第六章
*通配符：用来匹配值的一部分的字符。比如下面用到的%等
1.找出所有以词Fish起头的产品名字和价格
SELECT prod_name，prod_price FROM Products Where prod_name LIKE 'Fish%';
*注意，匹配是区分大小写的，上述里头fish开头的就不会被找出来。
2.找出所有在中间含有bean bag的产品名字和价格
SELECT prod_name，prod_price FROM Products Where prod_name LIKE '%bean bag%';
*由于%可以代表0个字符，所有如果名字就叫bean bag的也会被找出来。
3.找出所有F开头，y结尾的产品名字和价格
SELECT prod_name，prod_price FROM Products Where prod_name LIKE 'F%y';
*注意，如果结尾带有空格，这样写也不会被找出来。
**不可以用prod_name LIKE ‘%’检索出prod_name = NULL 的情况，这一点要注意。
***在Microsoft Access 中，%用的是*
4.找出开头有一个字符，然后后面跟着 inch taddy bear的产品名字和价格
SELECT prod_name，prod_price FROM Products Where prod_name LIKE '_ inch taddy bear';
5.找出所有以J或M开头的联系人，并按照联系人字母顺序排列
SELECT cust_contact FROM Customers Where cust_contact LIKE ‘[JM]%’ORDER BY cust_contact；
第七章
*字段：和列的意思基本相同，只不过术语上说会和计算字段一起使用
字段是比记录更小的单位，字段集合组成记录，每个字段描述文献的某一特征，即数据项，并有唯一的供计算机识别的字段标识符。
1.获得供应商的名字和供应商的国家，要求格式是“名字（国家）”的形式，将其命名为vend_title，并按照供应商名字排列
SELECT RTRIM(vend_name）+'(' + RTRIM(vend_country） + ')'
AS vend_title
FROM Vendors
ORDER BY vend_name;
*在Access和SQL Server使用+号。DB2、Oracle等使用||号
写出来是SELECT RTRIM（vend_name）|| '(' || RTRIM(vend_country) || ')'
**TRIM 系列函数：
TRIM（）：去掉字符串两边的空格
RTRIM（）：去掉字符串右边的空格
LTRIM（）：去掉字符串左边的空格
2.获得订单号为20008的所有产品的编号、产品数量、产品单价，并汇总物品的价格（单价*数量）
SELECT prod_id，quantity，item_price，quantity * item_price AS expanded_price
FROM OrderItems
WHERE order_num = 20008；
第八章
1.输出供应商名字及其字母全部大写的形式
SELECT vend_name，UPPER（vend_name）AS vend_name_uppercase
FROM vendors
ORDER BY vend_name；
*Microsoft Access用的是UCASE（）函数
***其他的一些函数
表示输出字符串a从左边开始的b个字符 LEFT(a，b)
表示输出字符串a从右边开始的b个字符 RIGHT(a，b）
将字符串a全部转化为小写 LOWER（a）
返回字符串的长度 LENGTH（a）
2.输出联系名字发音类似于Michael Green的顾客名字和联系名字
SELECT cust_name，cust_contact
FROM Customers
WHERE cust_contact = SOUNDEX('Michael Green')；
3.输出订单日期年份为2012年的订单号
SELECT order_num
FROM Orders
WHERE DATEPART（‘yyyy’，order_date）= 2012;（SQL Server版本）
WHERE to_number(to_char（order_date,'YYYY'）) = 2012;(Oracle版本）
WHERE order_date BETWEEN to_date（‘01-01-2012’）AND to_date（'12-31-2012'）；（用between方法）
*DATEPART函数的使用图表
4.数值处理函数
返回绝对值 ABS（）
返回余弦 COS（）
返回正弦 SIN（）
返回正切 TAN（）
返回指数 EXP（）
返回圆周率 PI（）
返回平方根 SQRT（）
第九章
1.返回 Products 表中所有产品的平均价格
SELECT AVG（prod_price）AS avg_price
FROM Products；
2.返回Products表中vend_id为DLL01的产品的平均价格
V
SELECT AVG（prod_price）AS avg_price
FROM Products
WHERE vend_id = ‘DLL01’;
3.返回Customers表中顾客的总数
SELECT COUNT（*）AS num_cust
FROM Customers；
4.返回Customers表中有电子邮件地址的顾客总数
SELECT COUNT（cust_email）AS num_cust
FROM Customers；
*COUNT（）不会把值为NULL的行计入计数中。
5.返回Products表中最贵/便宜物品的价格
SELECT MAX（prod_price）AS max_price
FROM Products；
SELECT MIN（prod_price）AS min_price
FROM Products；
6.返回Products表中所有物品数量的和
SELECT SUM(quanqity) AS items_ordered
FROM Products；
7.返回OrderItm表中订单号为20005的所有物品的总价格
SELECT SUM（quantity*prod_price）AS total_price
FROM OrderItems
WHERE order_num = 20005；
8.返回 Products 表中开发商编号为DLL01的所有产品的平均价格（但要排除相同的价格）
SELECT AVG（DISTINCT prod_price）AS avg_price
FROM Products
WHERE vend_id = ‘DLL01’;
9.返回表Products中所有物品数量，产品价格最高值、最低值、平均值
SELECT COUNT(*) AS prod_items，
MAX(prod_price）AS max_price，
MIN（prod_price）AS min_price，
AVG（prod_price）AS avg_price
FROM Products；
第十章
1.返回每个开发商提供的产品数量
SELECT vend_id, COUNT（*）AS prod_number
FROM Products
GROUP BY vend_id；
*SELECT中用到的别名不可以在GROUP BY 当中用到。
*GROUP BY 子句在WHERE之后，ORDER BY之前。
2.返回产品数量大于2的开发商提供的产品数量
SELECT vend_id，COUNT（*）AS prod_number
FROM Products
GROUP BY vend_id
HAVING COUNT(*) > 2;
*在HAVING中也不可以用到别名，得用一样的表达式
3.返回具有2个及以上的，价格大于等于4的产品的开发商的id和产品数量
SELECT vend_id，COUNT（*）AS prod_number
FROM Products
WHERE prod_price >= 4
GROUP BY vend_id
HAVING COUNT（*）> 2；
*HAVING针对分组之后的情况，而WHERE则针对原来表的每一行的情况。
4.检索包含三个或更多物品的订单号和订购物品的数目，并要求最终结果按照订购物品数目、订单号来排序输出
SELECT order_num，COUNT（*）AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT（*）>= 3
ORDER BY items，order_num；
*ORDER BY是可以使用SELECT中的别名的，同时要注意不要觉得GROUP BY 可以替代ORDER BY 的排序。
第十一~十二章
1.查询订购物品id为RGAN01的所有顾客名和联系方式（分别使用子查询和链接）
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 Orders.order_num = OrderItems.order_num
AND prod_id = 'RGAN01';
2.显示Customers表中每个顾客的订单总数及对应的cust_state
SELECT cust_name，cust_state，
(SELECT count (*) FROM Orders WHERE Customers.cust_id = Orders.cust_id) AS orders
FROM Customers
ORDER BY cust_name；
3.选出每个产品的名字和价格，及其对应的每一位供应商名字（简单语法和规范语法）
简单语法：
SELECT prod_name，prod_price，vend_name
FROM Products，Vendors
WHERE Products.vend_id = Vendors.vend_id；
规范语法：
SELECT prod_name,prod_price,vend_name
FROM Products INNER JOIN Vendors
ON Products.vend_id = Vendors.vend_id;
4.选出订单为20007的的产品名、开发商名字、产品价格和数量
SELECT prod_name，vend_name，prod_price，quantity
FROM Products，Vendors，OrderItems
WHERE OrderItems.prod_id = Products.prod_id
AND Products.vend_id = Vendors.vend_id
AND order_num = 20007;
第十三章：
1.查询和联系人Jim Jones在同一公司的顾客id，名字，联系人（分别使用子查询和链接）
SELECT cust_id，cust_name，cust_contact
FROM Customers
WHERE cust_name = （SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones'）;
SELECT cust_id,cust_name,cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
2.对每个顾客下的订单好进行计数（显示每一个Orders.num），包括那些至今尚未下单的顾客
SELECT Customers.cust_id , Orders.num
FROM Customers LEFT OUTER JOIN Orders
WHERE Customers.cust_id = Orders.cust_id;
有关外连接可以看这个图：
左外连接：（左边的表匹配不到的部分会出来）
右外连接：（右边的表不配不到的地方会出来）
全外连接：（两边没匹配到的地方依旧会给）
3.检索所有顾客id及每个顾客下的订单数
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 cust_id;
第十四章
1.检索来自IL、IN、MI三个洲的所有顾客，还要包括不论哪个州的名为Fun4ALL的顾客订单，将所有结果按照名字和联系人排序
SELECT cust_name，cust_email，cust_contact
FROM Customers
WHERE cust_state IN ('IN','IL','MI')
UNION
SELECT cust_name，cust_email，cust_contact
FROM Customers
WHERE cust_name = 'Fun4ALL'
ORDER BY cust_name，cust_contact；
*区分UNION 和 UNION ALL
UNION会把重复筛选的行删掉，但是UNION ALL不会
*ORDER BY 对所有结果服务。
第十五章
1.向Customers表中插入一行数据
INSERT INTO Customers（cust_id，cust_name，cust_address，cust_city,cust_state,cust_zip,cust_country,cust_contact,cust_email）
VALUES （'12413','Toy Land','123 Street','New York','NY','11111','USA',NULL,NULL）;
*为NULL的部分可以不写对应插入列
2.把表CustNew中的数据插入到表Customers中
INSERT INTO Customers（/*Customers按顺序的列名*/）
SELECT , , , , , , , , --表CustNew的每列名
FROM CustNew；
*注意 ，新表的主键值（这里是cust_id）不应该有和原表相同的部分，否则INSERT指令失效
*两个表的列名可以不相同，只要顺序对的上就行
3.创建一个新表CustCopy，并把Customers表的内容复制到新表中
SELECT *
INTO CustCopy
FROM Customers；（SQLite用法）
CREATE TABLE CustCopy AS SELECT * FROM Customers；（MySQL 用法）
*有的DBMS用SELECT INTO 会覆盖掉原表
第十六章
1.将客户10000005的电子邮件地址更新为kim@thestoystore.com
UPDATE Customers
SET cust_email = 'kim@thestoystore.com'
WHERE cust_id = 10000005；
2.将客户10000006的电子邮件更新为sam@toyland.com,并把顾客联系人改为Sam Roberts
UPDATE Customers
SET cust_email = 'sam@toyland.com',
cust_contact = 'Sam Robers'
WHERE cust_id = '10000006'；
3.将客户10000006的电子邮件数据清空
UPDATE Customers
SET cust_email = NULL
WHERE cust_id = '10000006';
4.将顾客10000006的数据全部清除
DELETE FROM Customers
WHERE cust_id = '10000006';
第十七章
1.对Vendors建表
CREATE TABLE Venders
（
vend_id CHAR(10) NOT NULL,
vend_name CHAR(50) NOT NULL,
vend_address CHAR(50) ,
vend_city CHAR(50) ,
vend_state CHAR(5) ,
vend_zip CHAR(10) ,
vend_country CHAR(50) ,
）;
2.对OrderItems建表，并默认quantity为1
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,
)；
*NOT NULL表示插入数据的时候这一列不可以为NULL
*建表中的数据类型一览：
3.对表Vendors插入一列vend_phone，其数据类型为CHAR
ALTER TABLE Vendors
ADD vend_phone CHAR（20）；
4.将插入的vend_phone列删除
ALTER TABLE Vendors
DROP COLUMN vend_phone;
5.删除表custcopy
DROP TABLE custcopy；
*更新和删除表的操作都要注意准备一下备份。
第十八章
1.创建一个名为ProductsCustomers的视图，要求具有客人名，联系人和产品id；
并从这个视图里筛选出RGAN01号产品的客人名和联系人
CREATE VIEW ProductsCustomers AS
SELECT cust_id,cust_contact,prod_id
FROM Customers,Orders,OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND Orders.order_num = OrderItems.order_num；
SELECT cust_id，cust_contact，prod_id
FROM ProductsCumtomers
WHERE prod_id = 'RGAN01';
2.创建一个名为VendorLocations的视图，要求其表现为开发商名（开发商所在国家）的形式
并从这个视图里筛选出所有的开发商名（国家）
CREATE VIEW VendorLocations AS
SELECT RTRIM（vend_name）+'(' + RTRIM(vend_country) + ')' AS vend_location
FROM Vendors;
SELECT *
FROM VendorLocations；
3.创建一个CustomerEmailList视图，过滤掉Customers中没有email的顾客
CREATE VIEW CustomerEmailList AS
SELECT cust_id,cust_name,cust_email
FROM Customers
WHERE NOT cust_email IS NULL;
4.创建一个OrderItemsExpanded视图，要求其具有订单号，产品单价，数量，对应总价
CREATE VIEW OrderItemsExpanded AS
SELECT order_num，prod_price，quantity，prod_pride * quantity AS expanded_price
FROM OrderItems;
*在视图上的查询不允许使用ORDER BY
第十九章
1.执行存储过程的指令：EXECUTE + 存储过程名字（参数）
2.写对邮件发送清单中具有邮件地址的顾客进行计数的存储，并调试
Oracle版本：
设计：
CREATE PROCUDURE MailingListCount（
ListCount OUT INTEGAR
// IN代表输入的值，OUT代表输出的值，INOUT代表又要输入又要输出的值
）
IS
v_rows INTEGAR;
//存储的局部变量
BEGIN
SELECT COUNT（*）INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL；
//把计数值赋给v_rows
ListCount ：= v_rows；
//声明输出参数的值 = v_rows的值
END；
调试：
var ReturnValue NUMBER //声明变量
EXEC MailingListCount（：ReturnValue）； //执行存储过程
SELECT ReturnValue； //用SELECT显示返回值
MySql版本：
CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt = COUNT（*）
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt；
调试：
DECLARE @ReturnValue INT
EXECUTE @ReturnValue = MailingListCount；
SELECT @ReturnValue；
第二十章
1.创建一个事件，从系统中完全删除订单12345
SQL Server版本：
BEGIN TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT TRANSACTION
ORCALE版本：
SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345
DELETE Orders WHERE order_num = 12345
COMMIT
2.创建一个包括4条INSERT语句的时间，通过检查@@ERROR变量的方式来回滚节点
BEGIN TRANSACTION
INSERT INTO Customers（cust_id,cust_name）
VALUES('10000001,'Toys Emporium')；
SAVE TRANSACTION StartOrder；
INSERT INTO Orders(order_num,order_date,cust_id)
VALUES (……)；
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder；
INSERT INTO OrderItems（/*OrderItems中的东西*/）
VALUES（……）；
IF @@ERROR <> 0 ROLLBACK TRANSACTION StartOrder；
COMMIT TRANSACTION
第二十一章
1.检索Customers表中没有邮件的顾客，并在检索结果中创建游标
DECLARE CustCursor CURSOR
FOR
SELECT cust_id，cust_name
FROM Customers
WHERE cust_email IS NULL ;(MySql版本）
DECLARE CURSOR CustCursor
IS
SELECT cust_id，cust_name
FROM Customers
WHERE cust_id IS NULL；（Oracle版本）
2.打开和关闭游标
OPEN CURSOR CustCursor
CLOSE CustCursor
DEALLOCATE Cursor CustCursor
（有关游标的具体设置没有写，因为用不上）
第二十二章
1.创建表Vendors，要求vend_id作为主键
CREATE TABLE Vendors
（
vend_id CHAR（10） NOT NULL PRIMARY KEY,
……
）；
2.更新表Vendors，将vend_id设置为主键
ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY （vend_id）；
3.创建表Orders，其中order_num为主键，cust_id为对Customers的外键
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）
）；
4.更新表Orders，将cust_id 设置为对Customers 的外键
ALTER TABLE Orders
ADD CONSTRAINT FOREIGN KEY (cust_id) REFERENCES Customers (cust_id）
5.创建表OrderItems，要求quantity为正数
CREATE TABLE OrderItems
（
……
quantity INTEGER NOT NULL CHECK (quantity > 0），
……
）；
6.更新限制，要求OrderItems表中quantity为正数
ALTER TABLE
ADD CONSTRAINT CHECK (quantity > 0);
7.更新限制，要求gender列必须只包含M或者F
ADD CONSTRAINT CHECK （gender LIKE '[MF]'）；
8.对Products表的prod_name设置一个索引
CREATE INDEX prod_name_ind
ON Products （prod_name）；
9.设置一个触发器，对所有INSERT,UPDATE操作，将Customers表中的cust_state设置为大写
SET TRIGGER customers_state
ON
FOR INSERT,UPDATE
AS
UPDATE Customers
SET cust_state = Upper(cust_state)
WHERE Costomers.cust_id = inserted.cust_id;
同程旅行工作强度 19人发布
