《SQL必知必会》学习笔记

 第一次写博客，内容为最近看的《SQL必知必会》这本书里截取的些许知识。虽难登大雅之堂，但初踏技术之路，开始和大家分享点滴，也算是程序人生的一个起步了。

需要的表结构和数据的下载地址为：

http://download.youkuaiyun.com/download/keven2840/9963811

以下语句适用于Oracle数据库，测试执行通过。

//使用order by排序时，可以将表名用数字当做序号代替

SELECT prod_id, prod_price, prod_nameFROM productsORDER BY 2,3;

//NOT 关键字

SELECT prod_nameFROM productsWHERE NOT vend_id = 'DLL01';

/*通配符*/

--百分号（%）匹配0-n个字符

SELECT prod_id, prod_name FROM productsWHERE prod_name LIKE '%bean bag%';

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

-- 下划线(_)只匹配一个字符

SELECT prod_id, prod_name FROM productsWHERE trim(prod_name) LIKE '__ inch teddy bear';

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

/*使用通配符的技巧:

1、不要过度使用通配符，因为处理时间比其他操作符长

2、尽量不要把通配符用在搜索模式的开始处，这样搜索起来是最慢的

3、注意通配符位置，不要放错地方*/

--拼接两个字段的值 用trim去掉两边空格

SELECT trim(vend_name) || '(' || trim(vend_country) || ')'FROM VendorsORDER BY vend_name;

--字段加别名

SELECT RTRIM(vend_name) || '(' || RTRIM(vend_country) || ')' vend_title FROM vendors ORDER BY vend_name;

--  + - * / 运算符

SELECT prod_id, quantity, item_price,quantity*item_price expended_priceFROM OrderitemsWHERE order_num = 20008;

/*函数*/

/*常用文本处理函数:

      LEFT() 返回字符串左边的字符

      LENGTH() 返回字符串长度

      LOWER() 转换为小写

      UPPER() 转换为大写

      LTRIM() 去掉字符串左边的空格

     RTRIM() 去掉字符串右边的空格

      TRIM() 去掉字符串两边的空格

     soundex() 返回soundex值*/

--soundex是一个将任何文本串转换为描述其语音表示的字母数字模式的算法

--意思就是找发音相近的字符串，语法：

SELECT DISTINCT soundex(prod_id) FROM Orderitems

--使用soundex查找联系名读音类似'Michael Green'的顾客名字(实际为'Michelle Green')

SELECT cust_name,cust_contactFROM customersWHERE Soundex(cust_contact) = soundex('Michael Green');

/*类型转换处理函数*/

-- to_number  to_date  to_char

SELECT order_num FROM Orders WHERE to_number(to_char((order_date), 'YYYY')) = 2012;

--等同于

SELECT order_num FROM orders WHERE order_date BETWEEN to_date('01-01-2012','mm-dd-yyyy')

 AND to_date('12-31-2012','mm-dd-yyyy');

/*数值处理函数*/

--ABS() COS() EXP() PI() SIN() SQRT() TAN()

--AVG()函数返回平均值（忽略列值为NULL的行）

SELECT AVG(prod_price) avg_price FROM Products;

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

--使用sum()得出总的订单金额,只统计某个物品

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

SELECT COUNT(*) num_items,MIN(prod_price) price_min,MAX(prod_price) price_max,AVG(prod_price) price_avgFROM products;

--分组数据

SELECT vend_id, COUNT(*) num_prodFROM ProductsGROUP BY vend_id;

--在group by后使用having过滤分组

--having支持所有where操作符

SELECT vend_id,COUNT(*) num_prodFROM ProductsGROUP BY vend_idHAVING COUNT(*) > 2;

SELECT cust_id,COUNT(*) ordersFROM ordersGROUP BY cust_idHAVING COUNT(*) >= 2;

--where和having的差别：where是在数据分组前进行过滤，having是在分组后过滤

--同时使用where和having的语句

--列出具有两个以上产品且其价格大于或等于4的供应商

SELECT vend_id, COUNT(*) num_prodsFROM products WHERE prod_price >= 4 GROUP BY vend_id HAVING COUNT(*) >= 2;

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

--使用子查询查找订购物品(prod_id)为RGAN01的顾客信息

SELECT cust_id,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 c,orders o,orderitems oi

    WHERE c.cust_id = o.cust_id AND o.order_num = oi.order_num AND oi.prod_id = 'RGAN01';

--使用exists

SELECT cust_name,cust_contact FROM customers c

    WHERE EXISTS(SELECT cust_id FROM orders o WHERE cust_id = c.cust_id

                                    and EXISTS (SELECT order_num FROM Orderitems  WHERE order_num = o.order_num and prod_id = 'RGAN01'));

--多表查询

--对每个顾客下的订单进行计数

SELECT c.cust_id, count(o.order_num) num  FROM customers c,orders owhere c.cust_id = o.cust_id(+) group by c.cust_id;

--查询所有顾客及其订单,包括没有订单的顾客

SELECT t1.cust_id, t2.order_numfrom customers t1 left join orders t2on t1.cust_id = t2.cust_id;

--查询所有顾客以及每个顾客所下的订单数

select customers.cust_id, count(orders.order_num) o_numfrom customers

   left join orderson customers.cust_id = orders.cust_id group by customers.cust_id;

--列出所有产品以及订购数量，包括没有人订购的产品

SELECT products.prod_id,sum(orderitems.quantity) from products left join orderitems on products.prod_id = orderitems.prod_id

     group by products.prod_id;

--组合查询

--需要ILLinois、Indiana和Michigan等美国几个州的所有顾客的报表

--还包括不管位于哪个州的所有的Fun4All

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

--等效于SELECT cust_name,cust_contact,cust_emailFROM customers WHERE cust_state IN ('IL','IN','MI')

      OR cust_name = 'Fun4All';     

--导出表数据到新表

CREATE TABLE custcopy ASSELECT * FROM customers;

--视图

--1、利用视图简化复杂的联结

CREATE VIEW Pro_customers AS SELECT cust_name,cust_contact,prod_id

 FROM Orderitems,customers,orders

 WHERE orders.cust_id = customers.cust_id AND Orderitems.Order_Num = orders.order_num;

--检索订购了产品RGAN01的顾客

SELECT cust_name,cust_contactFROM pro_customersWHERE prod_id = 'RGAN01';

--2、利用视图重新格式化检索出的数据

CREATE VIEW VendorLocation AS SELECT (RTRIM(vend_name) || '(' || RTRIM(vend_country) || ')')  vend_title  FROM vendors;

--查询视图

SELECT * FROM VendorLocation;

--3、用视图过滤不想要的数据

--定义CustomerEMailList视图，过滤没有电子邮件地址的顾客

CREATE VIEW CustomerEMailList AS

SELECT * FROM Customers WHERE cust_email IS NOT NULL;

SELECT * FROM CustomerEMailList;

--4、使用视图简化计算字段

--定义OrderItemsExpanded视图，用于检索某个订单中的物品，计算每种物品的总价格

CREATE VIEW OrderItemsExpanded  AS

 SELECT order_num, prod_id, quantity, item_price, (quantity*item_price) expanded_price FROM ORDERITEMS;

SELECT * FROM OrderItemsExpanded WHERE order_num = 20008;

--事务处理

SET TRANSACTION DELETE Orderitems WHERE order_num = 20005;

DELETE Orders WHERE order_num = 20005;

COMMIT;

--约束

--添加主键

ALTER TABLE vendors ADD CONSTRAINT PK_VendID PRIMARY KEY (vend_id);

--添加检查约束

ALTER TABLE OrderitemsADD CONSTRAINT CK_Quantity CHECK (quantity>0);

INSERT INTO Orderitems VALUES(111,111,'111',-1,111);--报错 ORA-02290 违反检查约束条件

--索引

--索引改善检索操作的性能，但降低数据插入、修改和删除的性能

--索引数据可能占用大量的存储空间

--并非所有数据都适合索引，取值不多的数据不如具有更多可能值的数据

--索引用于数据过滤和数据排序。如果经常以某种特定的顺序排序数据，则该数据可能合适做索引

--可以在索引中定义多个列，但这个索引仅在多个列排序时有用

--给Products表的prod_name列创建索引CREATE INDEX prod_name_indON Products (prod_name);

--索引的效率随表数据的增加或改变而改变，最好定期检查索引，根据需要对索引进行调整

--触发器

--触发器是特殊的存储过程，与单个表上的INSERT、UPDATE、DELETE（或组合）相关联

/*触发器用途：1、保证数据一致。例如在INSERT或UPDATE时将所有数据转换为大写。

            2、基于某个表的变动在其他表上执行活动。例如每当更新或删除时将审计跟踪记录写入某个日志表。

            3、进行额外的验证并根据需要回退数据。例如保证插入的某个数值不超过限定，如果超过则阻塞插入。

            4、计算列的值或更新时间戳*/        

 --创建一个触发器，用于在所有insert或update时，将Customers表中的cust_state列转换为大写

CREATE TRIGGER customer_state

 AFTER INSERT OR UPDATE ON Customers

 FOR EACH ROW

BEGIN

UPDATE Customers SET cust_state = UPPER(cust_state) WHERE Customers.Cust_Id =  :old.cust_id

END;

--最后谈谈数据库安全

--需要保护的操作有：

--1、对数据库管理功能（创建、更改或删除表等）的访问

--2、对特定数据库或表的访问

--3、访问的类型（只读、对特定列的访问等）

--4、仅通过视图或存储过程对表进行访问

--5、创建多层次的安全措施，从而允许多种基于登陆的访问和控制

--6、限制管理用户账号的能力--使用GRANT和REVOKE语句来设置安全