SQL必知必会要点记录（5/6）

SQL必知必会要点记录（5/6）

*创建表CREATE TABLE:后接表名，若不指定NOT NULL，默认为NULL
CREATE TABLE PRODUCTS
(
    prod_id    CHAR(10)        NOT NULL,
    vend_id    CHAR(10)        NOT NULL,
    prod_name    CHAR(254)    NOT NULL,
    prod_price    DECIMAL(8,2)    NOT NULL,
    prod_desc    VARCHAR(1000)    NULL
);

*指定默认值：DEFAULT
CREATE TABLE OrderItems
(
    order_name    INTEGER        NOT NULL,
    order_item    INTEGER        NOT NULL,
    prod_id    CHAR(10)        NOT NULL,
    quantity    INTEGER        NOT NULL    DEFAULT 1,
    item_price    DECIMAL(8,2)    NOT NULL
);

*表的更新：ALTER TABLE
1.为Venders新增一个名为vend_phone的列
ALTER TABLE Venders
ADD vend_phone CHAR(20);

2.删除列
ALTER TABLE Venders
DROP COLUMN vend_phone;

*表的删除：永久性
DROP TABLE CustCopy;

*创建视图：CREATE VIEW：便于以后重复使用，无需每次都写一遍多行代码
注：试图的删除延用DROP VIEW veiwname;
1-创建一个ProductCustomers试图
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;

2-检索订购了产品“RGAN01”的顾客
SELECT cust_name , cust_contact
FROM ProductCustomers
WHERE prod_id = 'RGAN01';

*试图的格式化数据功能
1-检索单个组合计算列，返回供应商名字和位置
SELECT RTRIM(vend_name) + '('+RTRIM(vend_country)+')'
            AS vend_title
FROM Vendors
ORDER BY vend_name;

2-假设经常需要这个格式的结果，注：+和 || 等价使用
CREATE VIEW ProductCustomers AS
SELECT RTRIM(vend_name) + '('+RTRIM(vend_country)+')'
            AS vend_title
FROM Vendors
SELECT *
FROM ProductCustomers;

*视图可以过滤掉不想要的数据
CREATE VIEW CustomerEMailList AS
SELECT cust_id , cust_name , cust_email
FROM Customers
WHERE cust_email IS NOT NULL;

SELECT *
FROM CustomerEMailList;

*视图与计算字段结合
CREATE VIEW OrderItemsExpanded AS
SELECT order_num ,
             prod_id,
             quantity,
             item_price,
             quantity*item_price AS expanded_price
FROM OrderItems;

SELECT *
FROM OrderItemsExpanded
WHERE order_num = 20008;

*存储过程的执行EXECUTE
EXECUTE AddNewProduct('JTS01',
                   'Stuffed Eiffel Tower' ,
                    6.49 ,
                       'Plush stuffed toy with the text La Tour Eiffel in red white and blue');

*创建储存过程CREATE PROCEDURE
CREATE PROCEDURE MailingListCount(
    ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
        SELECT COUNT(*) INTO v_rows
        FROM Customers
        WHERE NOT cust_email IS NULL;
        ListCount :=v_rows;
END;

*变量存储值
var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

*事务处理--通常，COMMIT用于保存更改，ROLLBACK用于撤销
SET TRANSACTION
...

*撤销ROLLBACK--例：执行delete操作后用rollback撤销
DELETE FROM Orders;
ROLLBACK;

*保存更改COMMIT
SET TRANSACTION
DELETE OrderItems WHERE order_num = 12345;
DELETE ORDERS WHERE order_num = 12345;
COMMIT;

*使用保留点，也即占位符
ROLLBACK TO delete1;

*游标