SQL复习

学习来源：　　http://www.cnblogs.com/shenxiaolin/p/5549343.html

1. 聚合函数

-- 1. AVG（）：返回列的平均值
SELECT AVG(product_price) AS avg_price
FROM products
WHERE vend_id = 1003;
-- 注意事项:函数会自动过滤掉所检索列中包含NULL值的行

-- 2. COUNT（）：返回列的行数。
SELECT COUNT(*) AS num_customer_1 , COUNT(customer_email) AS num_customer_2
FROM products;
-- 注意事项：COUNT（*）会统计所有的行，而COUNT（具体的列）则会忽略NULL值所在行

-- 3. SUM（）：返回列的值汇总。
SELECT SUM(quantity) AS items_ordered
FROM orderitems
WHERE order_num = 2005;

-- 4. MAX（）：返回列的最大值。
SELECT MAX(product_price) AS max_price
FROM products;
-- 注意事项：MAX（）必须指定列。

--5. MIN（）：返回列的最小值。
-- 注意事项：MAX和MIN都可应用于文本类型，当数据是按照该列排序，使用MAX会返回最后一行，而MIN则返回第一行。

2. 表的连接

-- 1. 等值连接/内连接
SELECT vend_name, product_name, product_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id
ORDER BY vend_name, product_name;

-- 2. 交叉连接
SELECT vend_name, product_name, product_price
FROM vendors, products
ORDER BY vend_name, products_name;
-- 注意：返回笛卡儿积的连接类型，即为交叉连接

-- Oracle写法：
SELECT vend_name, product_name, product_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.vend_id;

自连接：如字面意思，是对一个表的连接。

自然连接：能连接的两个表，至少有一列是相同的属性，自然连接会去除重复的列（由程序员实现）。

-- 3. 自连接
SELECT p1.product_id, p2.product_name
FROM products p1, products p2
WHERE p1.vend_id = p2.vend_id
    AND p2.product_id = 'DTNTR';

-- Oracle写法
SELECT p1.product_id, p1.product_name
FROM products p1 INNER JOIN p2
ON p1.vend_id = p2.vend_id
WHERE p2.product_id = 'DTNTR';

-- 4. 自然连接
SELECT c.*, o.order_num, o.order_date, oi.product_id
FROM customers c, orders o, orderitems oi
WHERE c.customer_id = o.customer_id
    AND oi.order_num = o.order_num
    AND product_id = 'FB';
-- 注意：去重是由程序员在SELECT语句中，通过指定所需列，来实现的。

外连接：如果在将两个表融合成一个表时（A+B=C），C中的出现这样的行：A或者B的特有属性全为0。而且功能需要包含这些行，那么就涉及到了外连接。

　　　　外连接具体表现为：左外连接和有外连接。

-- 5. 右外连接
SELECT customers.customer_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders 
ON orders.customer_id = customer.customer_id;
-- 注意：
-- 1. 左右是相对的。
-- 2. Oracle不支持 *=或者=*的写法
-- 3. 右外连接就是以左表为基准，可使左表的属性出现空值

具体地，连接可分四种：内连接，左外连接，有外连接，完全连接

完全连接：即交叉连接。

3. 组合查询

-- 关键字 UNION
SELECT vend_id, product_id, product_price
FROM products
WHERE product_price<=5
UNION
SELECT vend_id, product_id, product_price
FROM products
WHERE vend_id IN (1001, 1002);
-- 注意：
-- 1. 只有最后一条SQL需要写分号
-- 2. UNION的作用在于将多条SQL的结果集组合到单个查询结果集中。
-- 3. 用UNION连接的各个查询必须包含相同的列、表达式或者聚合函数（顺序可以不同）。
-- 4. 可对查询结果进行排序，但只允许在最后一个SQL中写ORDER BY。

UNION可以实现WHERE的功能，但 WHERE 无法实现 UNION ALL 的功能：

使用UNION无非就是想让结果集满足“条件一”或者“条件二”，这个功能使用WHERE能实现。

当存在某一条记录同时满足两个条件时，WHERE 无论如何都做不到让该记录在结果集中出现两次。

但是使用 UNION ALL 则能做到，事实上，也只有 UNION ALL 能做到。

4. 存储过程（Stored Procedure）

Oracle中用于编写存储过程的语言是PL/SQL，其中 PL 是过程语言，提供了从 if 语句到循环等一切功能。

-- 1. 简单示例
CREATE OR REPLACE PROCEDURE Hello 
IS
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello World');
END;
-- 注意：
-- 1. 初次创建：使用Create Procedure
-- 2. 更新：当不确定是否已存在该存储过程时，要使用Create OR Replace来创建或者更新存储过程
-- 3. 存储过程体：位于 Begin 和 End 之间的部分
-- 4. 执行：存储过程的执行，使用 Execute Hello

-- 2. 创建存储过程
CREATE OR REPLACE PROCEDURE Greeting
IS
h number;
g char(20);
BEGIN
SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP) INTO h FROM dual;
IF h >= 20 OR h <= 5 THEN
    g:='Good night!';
ELSIF h > 5 AND h <= 12 THEN
    g:='Good morning!';
ELSIF h > 12 AND h<= 17 THEN
    g:='Good afternoon!';
ELSE
    g:='Good evening';
END IF;
DBMS_OUTPUT.PUT_LINE(g);
END;
-- 执行：Execute Greeting

-- 3.1 添加客户
CREATE OR REPLACE PROCEDURE CustomerAdd(
    v_cust_name IN customers.cust_name%TYPE,  
    v_cust_address IN customers.cust_address%TYPE,  
    v_cust_city IN customers.cust_city%TYPE,
    v_cust_state IN customers.cust_state%TYPE,
    v_cust_sex IN customers.cust_sex%TYPE,
    v_cust_email IN customers.cust_email%TYPE)
IS
-- 声明客户ID变量
v_cust_id number;
BEGIN
-- 获取当前最大的客户ID:
SELECT MAX(cust_id) INTO v_cust_id
FROM customers;
-- 最大ID+1
v_cust_id := v_cust_id+1;
-- 插入新客户信息:
INSERT INTO customers(cust_id,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_sex,
    cust_email)
VALUES(v_cust_id,
    v_cust_name,
    v_cust_address,
    v_cust_city,
    v_cust_state,
    v_cust_sex,
    v_cust_email);
　COMMIT;
END

-- 3.2 执行 “添加客户”
EXECUTE Customers('Pep E. LaPew',
    '100 Main Street',
    'Los Angels',
    'CA',
    'Man',
    NULL)
-- 注意：插入的值允许手动设为NULL

-- 3.3 删除存储过程
DROP PROCEDURE CustomerAdd;

5. 游标

-- 游标的使用：

-- 定义变量
DECLARE
    v_vend_id vendors.vend_id%TYPE;
    v_vend_name vendors.vend_name%TYPE;
    v_vend_address vendors.vend_address%TYPE;
    v_vend_country vendors.vend_country%TYPE;
    v_vend_state vendors.vend_state%TYPE;
-- 定义游标（游标中属性的顺序由定义他的SELECT决定）
    CURSOR c_vendors
IS
    SELECT vend_id, vend_name, vend_address, vend_country, vend_state
    FROM vendors;
BEGIN
    -- 开启游标
    OPEN c_vendors;
    -- 对游标进行循环
    LOOP
        -- 获取一行并赋值给一变量（顺序要手动和游标中属性的顺序一致）
        FETCH c_vendors INTO v_vend_id, v_vend_name, v_vend_address, v_vend_country, v_vend_state;
        -- 对该变量进行处理
        IF Trim(v_vend_country) = 'USA' THEN
            v_vend_state := Upper(v_vend_state);
            UPDATE vendors
            SET vend_state = v_vend_state
            WHERE vend_id = v_vend_id;
        END IF;
        -- 循环终止条件
        EXIT WHEN c_vendors%notfound;
    END LOOP;
    -- 关闭游标
    CLOSE c_vendors;
END;

游标的作用：使用简单的SQL无法针对性地对结果集中的某些行进行获取和处理，有了游标，就可以在结果集中对记录进行滚动。

游标的分类：显示游标（手动创建），隐式游标（每次执行SQL时Oracle都会自动在内部创建）。

6. 管理事务处理

-- 6.1 保存点
insert into scott.emp(empno)  values(1011);
SAVEPOINT aa;
insert into scott.emp(empno)  values(1012);
ROLLBACK TO aa;
insert into scott.emp(empno)  values(1013);
commit;
-- 从保存点的创建位置起，到回滚位置间的操作均会被撤销，也就是说，‘1012’没有插入scott.emp中

-- 6.3 回滚
SELECT * FROM orders_log;
DELETE  FROM orders_log;
SELECT * FROM orders_log;
ROLLBACK;
-- 注意：Oracle中的事务无需显式开启，ROLLBACK默认对他上面的所有SQL操作进行回滚

-- 6.4 提交
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;
-- 注意：只有COMMIT上面的所有SQL均执行成功时，数据库相应表中的数据才会更新，只要一个出错，则所有SQL都无法影响到数据库。

注意：Oracle不需要启动事务，他的事务是隐式的（这一点不同于其他大多数DBMS）。

语句级原子性：每一条语句都具有的原子性，一条语句：要么执行成功；要么执行失败，且不会影响其他语句。

过程级原子性：Begin 和 End 之间的过程块，过程块中的代码：要么全部执行成功；要么都执行失败（哪怕只有一处失败），且不会影响过程之外的其他语句。

事务级原子性：Rollback或者Commit上面的所有语句。

笔试题：

1. 事务的ACID特性包括（原子性）、（一致性）、（隔离性）、（持久性）。

2. 在设置事务隔离层时，需要使用关键字（SET TRANSACTION）。

3. 在众多的事务控制语句中，用来撤销事务操作的语句是（ROLLBACK），用来持久化事务对数据库操作的语句是（COMMIT）。

4. 对表执行INSERT命令时系统自动加（RX）锁，执行CREATE命令时系统自动加（S）锁，执行ALTER命令时系统自动加（X）锁。

5. 哪些情况发生后事务将终止？

答：Oracle中的事务终止会发生在：

（1）用户使用COMMIT命令显示提交事务。

（2）用户使用ROLLBACK命令回滚整个事务。

（3）用户执行了一条DDL语句。（如create\drop\alter）

（4）用户正常断开了与Oracle的连接，这时用户当前的事务将被自动提交。

（5）用户进程意外被终止，这时用户当前的事务被回滚。

（6）用户关闭SQL*PLUS会话时，默认使用ROLLBACK回滚事务。

6. 数据库的并发操作会带来哪些问题？

答：数据库的并发操作会带来以下问题：
（1）丢失更新。

（2）错读（脏读）。

（3）不一致的分析（不可重复读）。

（4）幻读。

posted @ 2017-08-02 10:43 zhengmengen 阅读(301) 评论(0) 收藏举报

刷新页面返回顶部

zhengmengen

SQL复习

公告