注意DATE后面有一個(gè)逗號(hào)

CREATE TABLE ProductIns (
    product_id CHAR (4) NOT NULL,
    product_name VARCHAR (100) NOT NULL,
    product_type VARCHAR (32) NOT NULL,
    sale_price INTEGER DEFAULT 0,
    purchase_price INTEGE
    regist_date DATE,
    PRIMARY KEY (product_id)
);

插入NULL值

INSERT INTO ProductIns (
    product_id,
    product_name,
    product_type,
    sale_price,
    purchase_price,
    regist_date
)
VALUES
    (
        '0006',
        '叉子',
        '廚房用具',
        500,
        NULL,
        '2009-09-20'
    );

插入DEFAULT值

INSERT INTO ProductIns (
    product_id,
    product_name,
    product_type,
    sale_price,
    purchase_price,
    regist_date
)
VALUES
    (
        '0007',
        '擦菜板',
        '廚房用具',
        DEFAULT,
        790,
        '2009-04-28'
    );

注意DELETE配合FROM的

/* TRUNCATE product和下面這句話作用一樣
但是TRUNCATE只能用于刪除表中全部數(shù)據(jù)
DELETE則可以配合WHERE刪除部分?jǐn)?shù)據(jù)
TRUNCATE因?yàn)椴荒芫唧w的控制刪除對(duì)象
所以速度比DELETE快不少
*/
DELETE
FROM
    product;

UPDATE可以一次更新多列

-- 將regist_date一列全部更新
UPDATE Product
SET regist_date = '2009-10-10';

-- 一條UPDATE語(yǔ)句只更新一列 UPDATE Product
SET sale_price = sale_price * 10
WHERE
    product_type = '廚房用具';
UPDATE Product
SET purchase_price = purchase_price / 2
WHERE
    product_type = '廚房用具';

-- 使用逗號(hào)對(duì)列進(jìn)行分隔排列 UPDATE Product
SET sale_price = sale_price * 10,
 purchase_price = purchase_price / 2
WHERE
    product_type = '廚房用具';

-- 將列用()括起來(lái)的清單形式 UPDATE Product
SET (sale_price, purchase_price) = (sale_price * 10, purchase_price / 2)
WHERE
    product_type = '廚房用具';