*创建表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;
*游标