SQL必知必会（代码）_sql必知必会源码-CSDN博客

本文链接：https://blog.csdn.net/weixin_41876385/article/details/100883345

-----------------------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes
-- http://forta.com/books/0672336073/
-- Example table creation scripts for Microsoft SQL Server.
-----------------------------------------------------------


-------------------------
-- Create Customers table
-------------------------
CREATE TABLE Customers
(
  cust_id      char(10)  NOT NULL ,
  cust_name    char(50)  NOT NULL ,
  cust_address char(50)  NULL ,
  cust_city    char(50)  NULL ,
  cust_state   char(5)   NULL ,
  cust_zip     char(10)  NULL ,
  cust_country char(50)  NULL ,
  cust_contact char(50)  NULL ,
  cust_email   char(255) NULL 
);

--------------------------
-- Create OrderItems table
--------------------------
CREATE TABLE OrderItems
(
  order_num  int          NOT NULL ,
  order_item int          NOT NULL ,
  prod_id    char(10)     NOT NULL ,
  quantity   int          NOT NULL ,
  item_price decimal(8,2) NOT NULL 
);

----------------------
-- Create Orders table
----------------------
CREATE TABLE Orders
(
  order_num  int      NOT NULL ,
  order_date datetime NOT NULL ,
  cust_id    char(10) NOT NULL 
);

------------------------
-- Create Products table
------------------------
CREATE TABLE Products
(
  prod_id    char(10)      NOT NULL ,
  vend_id    char(10)      NOT NULL ,
  prod_name  char(255)     NOT NULL ,
  prod_price decimal(8,2)  NOT NULL ,
  prod_desc  varchar(1000) NULL 
);

-----------------------
-- Create Vendors table
-----------------------
CREATE TABLE Vendors
(
  vend_id      char(10) NOT NULL ,
  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 
);

----------------------
-- Define primary keys
----------------------
ALTER TABLE Customers WITH NOCHECK ADD CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED (cust_id);
ALTER TABLE OrderItems WITH NOCHECK ADD CONSTRAINT PK_OrderItems PRIMARY KEY CLUSTERED (order_num, order_item);
ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT PK_Orders PRIMARY KEY CLUSTERED (order_num);
ALTER TABLE Products WITH NOCHECK ADD CONSTRAINT PK_Products PRIMARY KEY CLUSTERED (prod_id);
ALTER TABLE Vendors WITH NOCHECK ADD CONSTRAINT PK_Vendors PRIMARY KEY CLUSTERED (vend_id);

----------------------
-- Define foreign keys
----------------------
ALTER TABLE OrderItems ADD
CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num),
CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);
ALTER TABLE Orders ADD 
CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);
ALTER TABLE Products ADD
CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);







-------------------------------------------------------------
-- Sams Teach Yourself SQL in 10 Minutes
-- http://forta.com/books/0672336073/
-- Example table population scripts for Microsoft SQL Server.
-------------------------------------------------------------


---------------------------
-- Populate Customers table
---------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');

-------------------------
-- Populate Vendors table
-------------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

--------------------------
-- Populate Products table
--------------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

------------------------
-- Populate Orders table
------------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2012-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2012-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2012-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2012-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2012-02-08', '1000000001');

----------------------------
-- Populate OrderItems table
----------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);

-------------------------
--第2课  检索数据
--2019.6.30
-------------------------

--2.2 检索单个列
SELECT prod_name
FROM Products;

--2.3 检索多个列
SELECT prod_id, prod_name, prod_price
FROM Products;

--2.4 检索所有列
SELECT *
FROM Products;

--2.5 检索不同的值
SELECT vend_id
FROM Products;

SELECT DISTINCT vend_id, prod_price
FROM Products;

--2.6 限制结果
SELECT TOP 5 prod_name
FROM Products;



----------------------------
--第3课  排序检索数据
--2019.6.30
----------------------------

--3.1 排序数据
SELECT prod_name
FROM Products
ORDER BY prod_name;

--3.2 按多个列排序
SELECT prod_id, prod_price,prod_name
FROM Products
ORDER BY prod_price, prod_name;

--3.3 按列位置排序
SELECT prod_id, prod_price,prod_name
FROM Products
ORDER BY 2, 3;

--3.4 指定排序方向
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 2 DESC;

SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name;


-----------------------
--第4课  过滤数据
--2019.6.30
-----------------------

--4.1 过滤数据
SELECT prod_name, prod_price
FROM Products
WHERE prod_price=3.49;

--4.2.1 检查单个值
SELECT prod_name, prod_price
FROM Products
WHERE prod_price<10;

--4.2.2 不匹配检查
SELECT vend_id, prod_name
FROM Products
WHERE vend_id<>'DLL01';

--4.2.3 范围值检查
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10;

--4.2.4 空值检查
SELECT prod_name
FROM Products
WHERE prod_price IS NULL;

SELECT cust_name
FROM Customers
WHERE cust_email  IS NOT NULL;

SELECT *
FROM Customers
WHERE cust_email  IS  NULL;


-----------------------------
--第5课  高级数据过滤
--2019.6.30
-----------------------------

--5.1.1 AND操作符
SELECT prod_id, prod_price, prod_name
FROM Products
WHERE vend_id='DLL01' AND prod_price<=4; 

--5.1.2 OR操作符
SELECT prod_name, prod_price 
FROM Products
WHERE vend_id='DLL01' OR vend_id='BRS01'; 

--5.1.3 求值顺序
SELECT prod_name, prod_price 
FROM Products
WHERE (vend_id='DLL01' OR vend_id='BRS01') AND prod_price>=10; 

--5.2 IN操作符
SELECT prod_name, prod_price 
FROM Products
WHERE vend_id IN ('DLL01', 'BRS01') AND prod_price>=10
ORDER BY prod_name;

--5.3 NOT操作符
SELECT prod_name
FROM Products
WHERE NOT vend_id='DLL01'
ORDER BY prod_name;

SELECT prod_name
FROM Products
WHERE vend_id<>'DLL01'
ORDER BY prod_name;


------------------------------------
--第6课  用通配符进行过滤
--2019.6.30
------------------------------------

--6.1.1 百分号（%）通配符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE 'Fish%';

SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '%bean bag%';

SELECT prod_name
FROM Products
WHERE prod_name LIKE 'F%y'; 

--6.1.2 下划线（_）通配符
SELECT prod_id, prod_name
FROM Products
WHERE prod_name LIKE '__ inch teddy bear';

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

--6.1.3方括号（[]）通配符
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[JM]%'
ORDER BY cust_contact;

--可用前缀字符^(脱字号)来否定
SELECT cust_contact
FROM Customers
WHERE cust_contact LIKE '[^JM]%'
ORDER BY cust_contact;

SELECT cust_contact
FROM Customers
WHERE NOT cust_contact LIKE '[^JM]%'   /*双重否定等于肯定*/
ORDER BY cust_contact;


------------------------------
--第7课  创建计算字段
--2019.6.30
------------------------------

--7.2 拼接字段
SELECT vend_name + ' (' + vend_country + ')'  /*+*/
FROM Vendors
ORDER BY vend_name;

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

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

--7.3执行算术计算
SELECT prod_id, quantity, item_price
FROM OrderItems
WHERE order_num=20008;

SELECT prod_id, quantity, item_price,
				quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num=20008;

SELECT 3*2;
SELECT RTRIM(LTRIM('                        abc                                                    '));


-----------------------------------
--第8课  使用函数处理数据
--2019.7.1
-----------------------------------

--8.2.1 文本处理函数
SELECT vend_name, UPPER(vend_name) AS vend_name_upcase
FROM Vendors
ORDER BY vend_name;
 
SELECT LEFT('AB CD EF ',3);

SELECT cust_name, cust_contact
FROM Customers
WHERE cust_contact='Michelle Green';

 --SOUNDEX()函数
SELECT cust_name, cust_contact
FROM Customers
WHERE SOUNDEX(cust_contact)=SOUNDEX('Michael Green');

 --8.2.2 日期和时间处理函数
SELECT order_num
FROM Orders
WHERE DATEPART(yy, order_date)=2012;

SELECT order_num
FROM Orders
WHERE order_date BETWEEN CONVERT(datetime, '02/01/2012', 110) 
AND CONVERT(datetime, '12-31-2012', 110);

SELECT *
FROM Orders


------------------------
--第9课  汇总数据
--2019.7.1
------------------------

--9.1.1 AVG()函数
SELECT AVG(prod_price) AS avg_price
FROM Products;

SELECT AVG(prod_price) AS avg_price
FROM Products
WHERE vend_id='DLL01';

--9.1.2 COUNT()函数
SELECT COUNT(*) AS num_cust
FROM Customers;

SELECT COUNT(cust_email) AS num_cust
FROM Customers;

--9.1.3 MAX()函数
SELECT MAX(prod_price) AS max_price
FROM Products;

--9.1.4 MIN()函数
SELECT MIN(prod_price) AS min_price
FROM Products;

--9.1.5 SUM()函数
SELECT SUM(quantity) AS items_ordered
FROM OrderItems
WHERE order_num=20005;

SELECT *
FROM OrderItems;

SELECT SUM(item_price*quantity) AS total_price
FROM OrderItems
WHERE order_num=20005;

--9.2 聚集不同值
SELECT AVG(DISTINCT prod_price) AS avg_price
FROM Products
WHERE vend_id='DLL01';

--9.3 组合聚集函数
SELECT COUNT(*) AS num_items,
				MIN(prod_price) AS price_min,
				MAX(prod_price) AS price_max,
				AVG(prod_price) AS price_avg
FROM Products;

SELECT *
FROM Products;


-------------------------
--第10课  分组数据
--2019.7.1
-------------------------

--10.1 数据分许
SELECT COUNT(*) AS num_prods
FROM Products
WHERE vend_id='DLL01';

--10.2 创建分组
SELECT vend_id, COUNT(*) AS num_prods
FROM Products
GROUP BY vend_id;

--10.3 过滤分组
SELECT cust_id, COUNT(*) AS orders
FROM Orders
GROUP BY cust_id
HAVING COUNT(*)>=2;

SELECT vend_id, COUNT(*) AS orders
FROM Products
WHERE prod_price>=4
GROUP BY vend_id
HAVING COUNT(*)>=2;

--10.4 分组和排序
SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*)>=3;

SELECT order_num, COUNT(*) AS items
FROM OrderItems
GROUP BY order_num
HAVING COUNT(*)>=3
ORDER BY items DESC, order_num DESC;


----------------------------
--第11课  使用子查询
--2019.7.1
----------------------------

--11.2 利用子查询进行过滤
SELECT order_num
FROM OrderItems
WHERE prod_id='RGAN01';

SELECT cust_id
FROM Orders
WHERE order_num IN (20007, 20008);

--合并上述两条SELECT语句
SELECT cust_id
FROM Orders
WHERE order_num IN (SELECT order_num
										 FROM OrderItems
										 WHERE prod_id='RGAN01');

SELECT *
FROM Customers
WHERE cust_id IN (1000000004, 1000000005);

--再合并以上两句


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.3 作为计算字段使用子查询
SELECT COUNT(*) AS orders
FROM Orders
WHERE cust_id='1000000001';

SELECT cust_name, cust_state, (SELECT COUNT(*) 
													  FROM Orders
													  WHERE Orders.cust_id=Customers.cust_id) AS orders /*完全限定列名*/
FROM Customers
ORDER BY  cust_name

SELECT cust_name, cust_state, (SELECT COUNT(*)
													  FROM Orders
													  WHERE cust_id=cust_id) AS orders /*没有完全限定列名*/
FROM Customers
ORDER BY cust_name;

SELECT *
FROM Customers;


----------------------
--第12课  联结表
--2019.7.2
----------------------

--12.2 创建联结
SELECT vend_name,  prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id=Products.vend_id;

--12.2.1 WHERE子句的重要性（笛卡尔积）
SELECT vend_name,  prod_name, prod_price
FROM Vendors, Products;

SELECT *
FROM Vendors;

SELECT *
FROM Products;

--12.2.2 内联结（等值联结）
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id=Products.vend_id;

--12.2.3 联结多个表
SELECT prod_name, vend_name, prod_price, quantity
FROM OrderItems, Vendors, Products
WHERE Vendors.vend_id =Products.vend_id
AND OrderItems.prod_id=Products.prod_id
AND order_num='20007';

SELECT *
FROM OrderItems;

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'; 


-------------------------------
--第13课  创建高级联结
--2019.7.3
-------------------------------

--13.1 使用表别名
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 O.order_num=OI.order_num
AND prod_id='RGAN01'; 

--13.2.1 自联结
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 c1.cust_contact=c2.cust_contact;

SELECT *
FROM Customers;

--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, OrderItems AS OI
WHERE C.cust_id=O.cust_id
AND O.order_num=OI.order_num
AND prod_id='RGAN01'; 

--13.2.3 外联结
SELECT Customers.cust_id, Orders.order_num
FROM Customers INNER JOIN orders
ON Customers.cust_id=Orders.cust_id;

--选中左边的所有行，即使在右边行不存在（返回NULL or 0）
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN orders
ON Customers.cust_id=Orders.cust_id;

SELECT *
FROM Orders;

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

--13.3 使用聚集函数的联结
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;

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课  组合查询
--2019.7.3
-------------------------

--14.2.1 使用UNION
--单条语句
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI'); 

SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name='Fun4All';

--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';

--another way
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
OR cust_name='Fun4All';

--14.2.3 包含或取消重复的行
--重复行不取消（ UNION ALL）
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_state IN ('IL', 'IN', 'MI')
UNION ALL
SELECT cust_name, cust_contact, cust_email
FROM Customers
WHERE cust_name='Fun4All';

--14.2.4 对组合查询结果排序
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;


-------------------------
--第15课  插入数据
--2019.7.3
-------------------------

SELECT *
FROM Customers;

--15.1.1 插入完整的行
--不安全的做法
INSERT INTO Customers
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA', NULL, NULL);

--安全的做法1（可以不用按照表的顺序来，但是值要和指定的列名顺序一致）
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);

--安全的做法2（指定次序不同，代码略）

--15.1.2 插入部分行
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
VALUES('1000000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '11111', 'USA');

--15.1.3 插入检索出来的数据（注意主键值不要重复，否则插入失败）----------------------------------------------待完善------------------------------------
--INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country)
--SELECT cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country
--FROM CustNew;

--15.2 从一个表复制到另一个表
--DROP TABLE CustCopy;
SELECT *
INTO CustCopy
FROM Customers;


----------------------------------
--第16课  更新和删除数据
--2019.7.3
----------------------------------

SELECT *
FROM Customers;

--16.1 更新数据
--更新某个列的值（添加信息）
UPDATE Customers
SET cust_email='kim@thetoystore.com'
WHERE cust_id='1000000005';

--要先执行15.1.2的操作
UPDATE Customers
SET cust_contact='Sam Roberts', 
		cust_email='sam@toyland.com'
WHERE cust_id='1000000006';

--更新某个列的值（删除信息）
UPDATE Customers
SET cust_email=NULL
WHERE cust_id='1000000005';

--16.2 删除数据（不要省略WHERE子句）
DELETE FROM Customers
WHERE cust_id='1000000006';
--小结：删除行用DELETE（不需要列名和通配符），删除指定列用UPDATE（更新操作）


-------------------------------
--第17课  创建和操纵表
--2019.7.3
-------------------------------

--17.2 更新表
--为了防止接入新的列之后无法删除，所以在15.2表CustCopy上进行操作，因为17.3会使用DROP TABLE删除它
SELECT *
FROM CustCopy;

--添加列
ALTER TABLE CustCopy
ADD cust_phone CHAR(11);

--删除列
ALTER TABLE CustCopy
DROP COLUMN cust_phone;
--经验证，上述两条语句在SQL中可以正常执行

--17.3 删除表
--要先执行15.2的操作
DROP TABLE CustCopy;

-------------------------
--第18课  使用视图
--2019.7.4
-------------------------

--18.1 视图
--12课的例子
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'; 
--以下代码（18.2.1）将上面这句代码转换成创建视图与使用视图的代码

--18.2.1 利用视图简化复杂的联结
--创建视图，注意使用GO关键字进行分批，不然会报错
GO
CREATE VIEW ProductCustomers AS
SELECT cust_name, cust_contact, prod_id
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id=Orders.cust_id
AND Orders.order_num=OrderItems.order_num;
GO

--使用视图
SELECT cust_name, cust_contact
FROM ProductCustomers
WHERE prod_id='RGAN01'; 

--18.2.2 用视图重新格式化检索出的数据
--第7课代码（+换成||一样的）
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM Vendors
ORDER BY vend_name;

GO
CREATE VIEW VendorLocations AS
SELECT RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title
FROM Vendors;
GO

SELECT *
FROM VendorLocations;

--18.2.3 用视图过滤不想要的数据
GO
CREATE VIEW CustomerEMailList AS
SELECT cust_id, cust_name, cust_email
FROM Customers
WHERE cust_email IS NOT NULL;
GO

SELECT *
FROM CustomerEMailList;

--18.2.4 使用视图与计算字段
--第7课代码
SELECT prod_id, quantity, item_price,
				quantity*item_price AS expanded_price
FROM OrderItems
WHERE order_num=20008;

GO
CREATE VIEW OrderItemsExpanded AS
SELECT order_num, prod_id, quantity, item_price,
				quantity*item_price AS expanded_price
FROM OrderItems
GO

SELECT *
FROM OrderItemsExpanded
WHERE order_num=20008; 


-----------------------------
--第19课  创建存储过程
--2019.7.4
-----------------------------

SELECT *
FROM Customers;

--19.4 创建存储过程
--PROCEDURE 可简写成 PROC
--EXECUTE 可简写成 EXEC
GO
CREATE PROCEDURE MailingListCount
AS
DECLARE @cnt INTEGER
SELECT @cnt=COUNT(*)
FROM Customers
WHERE NOT cust_email IS NULL;
RETURN @cnt;
GO

--调用以上存储过程
DECLARE @ReturnValue INT
EXECUTE @ReturnValue=MailingListCount;
SELECT @ReturnValue;


---------------一些存储过程，不是来自本书-------------------
GO
CREATE PROC usp_helloworld
AS
BEGIN
		PRINT 'Hello World!'
END
GO

EXEC usp_helloworld;
--------------------------------------------------------------------------


-------------------------------
--第20课  管理事务处理
--2019.7.4
-------------------------------

SELECT *
FROM Customers;

SELECT *
FROM Orders;

SELECT *
FROM OrderItems;

--20.2.3 使用保留点
BEGIN TRANSACTION
INSERT INTO Customers(cust_id, cust_name)
VALUES('1000000010', 'Toys Emporium');
SAVE TRANSACTION StartOrder;
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20100, '2001/12/1', '1000000010');
IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 1, 'BR01', '100', '5.49')
IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20100, 2, 'BR03', '100', '10.99')
IF @@ERROR <>0 ROLLBACK TRANSACTION StartOrder;
COMMIT TRANSACTION


--------------------------
--第21课  使用游标
--2019.7.4
--------------------------

--21.2.1 创建游标
DECLARE CustCursor CURSOR
FOR
SELECT * FROM Customers
WHERE cust_email IS NULL;

--21.2.2 使用游标
OPEN CustCursor

--21.2.3 关闭游标
CLOSE CustCursor
DEALLOCATE CustCursor


-------------------------------
--第22课  高级SQL特性
--2019.7.4
-------------------------------

--22.1 约束 CONSTRAINT
--22.1.1 主键 PRIMARY KEY
--22.1.2 外键 FOREIGN KEY    REFERENCES
--22.1.3 唯一约束 UNIQUE    REFERENCES
--22.1.4 检查约束 CHECK

--22.2 索引 INDEX

--22.3 触发器 TRIGGER

--22.4 数据库安全 GRANT    REVORK