23．高级SQL特性

11．分组数据

• 数据分组

select count(*) as num_prod from Products where vend_id = "DLL01"; 

• 创建分组group by
  位置：在where子句后，order by子句后

select vend_id,count(*) as num_prod from Products group by vend_id; 

• 过滤分组having

select cust_id, count(*) as orders from Orders group by cust_id having count(*)>=2; 

• where在数据分组前过滤，having在数据分组后过滤

select vend_id, count(*) as num_prods  from Products  where prod_price>=4 group by vend_id  having count(*)>=2; 

• 分组与排序
  一般在使用group by子句时，应该也给出order by子句，保证数据正确排序

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 

12．使用子查询

• 利用子查询进行过滤
  作为子查询的select语句只能查询单个列

select order_num from OrderItems where prod_id = "RGAN01"; 

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"); 

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 Orders  　　　　　　　　　　　where order_num in (select order_num  　　　　　　　　　　　　　　　　　　　　　　　from OrderItems  　　　　　　　　　　　　　　　　　　　　　　　where prod_id = "RGAN01")); 

• 作为计算字段使用子查询

select count(*) as orders from Orders where cust_id = "1000000001"; 

select Orders.cust_id, count(*) from Orders, Customers where Orders.cust_id = Customers.cust_id group by Orders.cust_id; 

• 合并计算字段

select cust_name,  　　　　cust_state,  　　　　(select count(*)  　　　　from Orders  　　　　where Orders.cust_id = Customers.cust_id) as orders  from Customers  order by cust_name; 

13．联结表

• 联结join

• 关系表
  各表中只主键重复，其他数据只记录一次
  更有效地存储、更方便地处理、可伸缩性好

• 创建联结

select vend_name, prod_name, prod_price  from Vendors, Products  where Vendors.vend_id = Products.vend_id; 

• 笛卡尔积（叉联结）
  没有where给出的联结条件

select vend_name, prod_name, prod_price from Vendors, Products; 

• 内联结

select vend_name, prod_name, prod_price  from Vendors inner join 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 　and order_num = 20007; 

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

14．创建高级联结

• 使用别名
  对列名和计算字段使用别名

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

-自联结（多次使用相同的表）
使用子查询实现

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

• 自然联结
  排除多次出现，使每一列只返回一次

select C.*, O.order_num, 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; 

• 使用带聚集函数的联结
  聚集＋内联结实现

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; 

15．组合查询

• 使用union
  where子句实现

select cust_name, cust_contact, cust_email from Customers where cust_state in ("IL", "IN", "MI") or 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"; 

• 对组合查询结果排序
  order by子句，对所有结果排序

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; 

16．插入数据

• 插入完整的行

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

• 插入部分行
  省略行允许NULL值，且在表定义中给出了默认值

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"); 

• 插入检索出的数据

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; 

• 从一个表复制到另一个表

create table CustCopy as select * from Customers; 

17．更新和删除数据

• 更新数据

update Customers set cust_contact = "Sam Robert", 　　cust_email = "sam@toyland.com" where cust_id = "1000000006"; 

• 删除某个列的值

update Customers set cust_email = NULL where cust_id = "1000000005"; 

• 删除数据（某行）

delete from Customers where cust_id = "100000006"; 

18．创建和操作表

• 创建表

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  text NULL ); 

• 使用NULL值
  只有不允许NULL值的列可作为主键
• 指定默认值

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

默认值经常用于日期或时间戳列

default current_date() 

• 更新表

alter table Vendors add vend_phone char(20); 

• 删除列

alter table Vendors drop vend_phone; 

• 删除表
  不允许删除与其他表相关联的表

drop table CustCopy; 

• 重命名表

rename 

19．使用视图

• 联结实现

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

• 创建视图

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; 

select cust_name, cust_contact from ProductCustomers where prod_id = "RGAN01"; 

• 用视图重新格式化检索出的数据

create view VendLocation as select concat(vend_name, " (", vend_country, ")") as vend_title  from Vendors; 

select * from VendLocation; 

• 用视图过滤不想要的数据

create view CustomerEmailList as select cust_id, cust_name, cust_email from Customers where cust_email is not null; 

select * from CustomerEmailList; 

• 使用视图与计算字段

create view OrderItemExpanded as select order_num, 　　　　prod_id,     　 quantity,     　 item_price,     　 quantity*item_price as expanded_price from OrderItems; 

select * from OrderItemExpanded where order_num = 20008; 

20．使用存储过程

• 创建存储过程

create procedure productpricing() select avg(prod_price) as priceaverage  from Products; 

• 执行存储过程

call productpricing(); 

• 删除存储过程

drop procedure productpricing; 

21．管理事务处理

• 事务处理
  事务　　一组SQL语句
  回退　　撤销指定SQL语句的过程
  提交　　将未存储的SQL语句结果写入数据库表
  保留点　事务处理中设置的临时占位符，可以对它发布回退

• 控制事务处理
  标识事务的开始语句

start transaction; 

回退语句

delete from Orders; rollback　T0 delete1; 

使用保留点

savepoint detele1; 

22．使用游标

• 创建游标

DECLARE CustCursor CURSOR FOR SELECT * FROM Customers WHERE cust_email IS NULL 

• 使用游标

OPEN CURSOR CustCursor 

23．高级SQL特性

• 约束
  管理如何插入或处理数据库数据的规则
• 主键
  使用PRIMARY KEY定义主键

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

ALTER TABLE Vendors ADD CONSTRAINT PRIMARY KEY (vend_id); 

• 外键
  利用外键可以防止意外删除数据

CREATE TABLE Orders (   order_num  int 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) 

• 检查约束

CREATE TABLE OrderItems (   order_num  int NOT NULL ,   order_item int NOT NULL ,   prod_id    char(10) NOT NULL ,   quantity   int NOT NULL 　CHECK (quantuty > 0),   item_price decimal(8,2) NOT NULL ); 

ADD CONSTRAINT CHECK (gender LIKE "[MF]") 

• 索引

CREATE INDEX prod_name_ind ON Products (prod_name); 

• 触发器

24．补充

• SQL子句书写顺序

SELECT 查询列表 FROM 表１　 【联结类型】　JOIN 表２ 　ON 联结条件 WHERE 筛选条件 GROUP BY 分组列表 HAVING 分组后的筛选条件 ORDER BY 排序字段 LIMIT 起始的条目索引，条目数； 

• 执行顺序

SELECT 查询列表　　　　　　　　　　　　　　　　　　　　　　　　　⑦ FROM 表１　　　　　　　　　　　　　　　　　　　　　　　　　　　　① 【联结类型】　JOIN 表２　　　　　　　　　　　　　　　　　　　　　②　　　　　 　ON 联结条件　　　　　　　　　　　　　　　　　　　　　　　　　　③ WHERE 筛选条件　　　　　　　　　　　　　　　　　　　　　　　　　④ GROUP BY 分组列表　　　　　　　　　　　　　　　　　　　　　　　⑤ HAVING 分组后的筛选条件　　　　　　　　　　　　　　　　　　　　⑥ ORDER BY 排序字段　　　　　　　　　　　　　　　　　　　　　　　⑧ LIMIT 起始的条目索引，条目数；　　　　　　　　　　　　　　　　　⑨