SQL 笔记（二）

1. 联结表
	联结由DBMS根据需要建立，它存在于查询执行当中。

2. select vend_name, prod_name, prod_price from vendors, products where vendors.vend_id = products.vend_id;
	
3. 笛卡尔积 由没有联结条件的表关系返回的结果为笛卡尔积。检索出的行的数目将是
	第一个表中的行数乘以第二个表中的行数。
	
4. 内部联结（等值联结）（跟 WHERE 联结是等价的）
	select vend_name, prod_name, prod_price from vendors inner join products on 
    -> vendors.vend_id = products.vend_id;

5. 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;
	+---------------------+-----------------+------------+----------+
	| prod_name           | vend_name       | prod_price | quantity |
	+---------------------+-----------------+------------+----------+
	| 18 inch teddy bear  | Bears R Us      | 11.99      |       50 |
	| Fish bean bag toy   | Doll House Inc. | 3.49       |      100 |
	| Bird bean bag toy   | Doll House Inc. | 3.49       |      100 |
	| Rabbit bean bag toy | Doll House Inc. | 3.49       |      100 |
	| Raggedy Ann         | Doll House Inc. | 4.99       |       50 |
	+---------------------+-----------------+------------+----------+

6. 创建高级联结
	自联结（基于子查询）
	select cust_id, cust_name, cust_contact from 
    -> customers where cust_name = (select cust_name from customers where cust_contact='Jim Jones');
	+------------+-----------+--------------------+
	| cust_id    | cust_name | cust_contact       |
	+------------+-----------+--------------------+
	| 1000000003 | Fun4All   | Jim Jones          |
	| 1000000004 | Fun4All   | Denise L. Stephens |
	+------------+-----------+--------------------+

	自联结 通常作为外部语句用来代替从相同表中检索数据的使用子查询语句。
	虽然结果是一样的，但是DBMS处理联结比子查询快得多。
	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';
	+------------+-----------+--------------------+
	| cust_id    | cust_name | cust_contact       |
	+------------+-----------+--------------------+
	| 1000000003 | Fun4All   | Jim Jones          |
	| 1000000004 | Fun4All   | Denise L. Stephens |
	+------------+-----------+--------------------+
	
	自然联结 排除多次出现，使每个列只返回一次。
	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';
	
	外部联结 许多联结将一个表中的行与另一个表中的行相关联。但有时候需要包含没有关联行
	的那些行。
	// 检索所有客户及其订单
	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;
	+------------+-----------+
	| cust_id    | order_num |
	+------------+-----------+
	| 1000000001 |     20005 |
	| 1000000001 |     20009 |
	| 1000000002 | NULL      |
	| 1000000003 |     20006 |
	| 1000000004 |     20007 |
	| 1000000005 |     20008 |
	+------------+-----------+
	
	使用带聚集函数的联结
	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;

7. 组合查询 SQL语句允许执行多个查询，并将结果作为单个查询结果集返回。
	有两种基本情况，需要用到组合查询
	（1）在单个查询中从不同的表类似返回结构数据
	（2）对单个表执行多个查询，按单个查询返回数据（与用OR 联结的 WHERE相同）
	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';
	+---------------+--------------------+-----------------------+
	| cust_name     | cust_contact       | cust_email            |
	+---------------+--------------------+-----------------------+
	| Village Toys  | John Smith         | [e-mail] |
	| Fun4All       | Jim Jones          | [e-mail]    |
	| The Toy Store | Kim Howard         | NULL                  |
	| Fun4All       | Denise L. Stephens | [e-mail] |
	+---------------+--------------------+-----------------------+

	UNION 的每个查询必须包含相同的列、表达式或聚集函数。
	UNION 自动去除重复的行。
	UNION ALL 去除所有重复的行。
	
8. 数据插入
	插入的集中方式
	（1）插入完整的行
	（2）插入行的一部分
	（3）插入某些查询的结果
	
	不指定列名的插入，需要为每个字段提供一个值。顺序按在数据库表中的顺序。
	insert into customers values('100000006', 'Toy Land', '123 Any Street', 'New York', 'NY', '1111', 
	'USA', NULL, NULL);
	
	插入时，省略的列必须满足一下某个条件：
	（1）该列定允许NULL值（无值或空值）
	（2）在表定义中给出默认值。这表示如果不给出值，将使用默认值。

	插入检索出的数据（INSERT SELECT）
	insert into customers(cust_id, cust_contact, cust_email, cust_name, cust_address, 
	cust_city, cust_state, cust_zip, cust_country)
	select cust_id, cust_contact, cust_email, cust_name, cust_address, 
	cust_city, cust_state, cust_zip, cust_country from custnew;
	
	INSERT SELECT 中的列名 为简单起见，这个列子在insert 和select 语句中使用的相同的列名。
	但是，不一定要求列名匹配。事实上，DBMS按位置匹配。
	
	
	从一个表负值到另一个表（SELECT INTO）与（INSERT INTO）最大的区别是前者导入表，后者
	导入数据。
	select * into custcopy from customers;（ORACLE 的语法）
	create table custcopy as select * from customers;（MySQL 语法）
	
9. 更新和删除数据
	update customers set cust_email = '[e-mail]' where cust_id = '100000005';
	
	为了删除某个列的值，可以设置为NULL
	update customers set cust_email = null where cust_id = '100000005';

	删除
	delete from customers where cust_id = '100000006';
	
	默认的时间时间戳
	MySQL 	current_date();
	Oralce sysdate
	
10. 更新表
	alter table vendors add vend_phone char(20)
	alter table vendors drop column vend_phone
	
	删除表
	drop table custcopy 
	
	重命名表
	rename

11. 使用视图
	视图的作用
	（1）重用SQL语句
	（2）简化复杂的SQL操作。
	（3）使用表的组成部分而不是整个表
	（4）保护数据
	（5）更改数据格式和表示
	
	视图的规则和研制
	视图不能索引，也不能有关联的触发器或默认值
	
	创建视图
	create view 
	 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 * from productcustomers where prod_id = 'RGAN01';
	
	删除视图
	drop view viewname
	
	视图为虚拟的表。它们包含的不是数据而是根据需要检索数据的查询。视图提供了一种封装
	SELECT 语句的层次，可以用来监护数据处理以及重新格式化基础数据或保护基础数据。
	
12. 存储过程
	为以后的使用而保存的一条或多条SQL语句的集合。可以将其视为批文件，虽然
	他们的作用不仅限于批处理。
	create procedure
	
	执行
	execute 接受存储过程名和要传递给他的任何参数。
	
13. 事物处理
	用来维护数据库的完整性，它保证成批的SQL操作要么完全执行，要么完全不执行。
	
	事物处理用来管理 insert,update, delete 语句。
	
14. 游标
	需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因。
	
15. 高级的SQL特性
	约束
	主键
	alter table vendors add constraint primary key (vend_id)
	
	外键 是表中的一个列，其值必须在另一表的主键中列出。
	create table orders_temp(
    -> order_num integer not null primary key,
    -> order_date datetime not null,
    -> cust_id char(10) not null references customers(cust_id)
    -> );
	
	alter table customers add constraint foreign key (cust_id) 
	references customers (cust_id)
	
	有的DBMS支持称为级联删除的特性。
	
	唯一约束
	不同于其他，但是可以为NULL。
	
	检查约束
	（1）检查最大值最小值
	（2）制定范围
	（3）只允许特定的值
	
	create table orderitems(
		order_num integer not null,
		order_item integer not null,
		prod_id char(10) not null,
		quantity integer not null check(quantity > 0),
		item_price money not null);
		
	索引
	索引用来排序数据以加快搜索和排序操作的速度。主键总是排序的，因此按主键检索特定行
	总是一种快速有效的操作。
	
	注意事项：
	（1）索引改善操作的性能，但降低数据插入、修改和删除的性能。
	（2）索引可能占据大量的存储空间。
	（3）索引用于数据过滤盒排序。
	（4）可以再索引中定义多个列。
	
	create index pro_name_ind on products (prod_name);
	
	触发器
	触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、
	update、delete操作相关联。
	
	触发器与单个表相关联。
	
16. 数据库安全
	grant revoke