ai训练营sql task1 代码笔记

CREATE DATABASE shop;

CREATE TABLE product(

     product_id CHAR(4) NOT NULL,

     product_name VARCHAR(100) NOT NULL,

     product_type VARCHAR(32) NOT NULL,

     sale_price INTEGER,

     purchase_price INTEGER,

     regist_date DATE,

     PRIMARY KEY(product_id)

 )  ;

DROP TABLE product;

ALTER TABLE product ADD COLUMN product_name_pinyin VARCHAR(100);

ALTER TABLE product DROP COLUMN product_name_pinyin;

-- 修改所有的注册时间

UPDATE product

   SET regist_date = '2009-10-10'; 

-- 仅修改部分商品的单价

UPDATE product

   SET sale_price = sale_price * 10

 WHERE product_type = '厨房用具'; 

UPDATE product

   SET sale_price = sale_price * 10,

       purchase_price = purchase_price / 2

 WHERE product_type = '厨房用具'; 

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 INTEGER ,

regist_date    DATE ,

PRIMARY KEY (product_id));

-- 包含列清单

INSERT INTO productins (product_id, product_name, product_type,

sale_price, purchase_price, regist_date) VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');

-- 省略列清单

INSERT INTO productins

VALUES ('0005', '高压锅', '厨房用具', 6800, 5000, '2009-01-15');