大数据入门与实战-SQL基础教程

CREATE DATABASE shop;

USE shop;
CREATE TABLE shoin(
product_id  CHAR(4) NOT NULL COMMENT "商品id",
product_name VARCHAR(100) NOT NULL COMMENT "商品名称",
product_cate VARCHAR(32) NOT NULL COMMENT "商品分类",
product_price INTEGER COMMENT "销售价格",
product_cost INTEGER COMMENT "进货单价",
register_date DATE COMMENT "登记日期",
PRIMARY KEY (product_id)
);

DROP shop;

ALTER TABLE shoin ADD COLUMN product_nickname VARCHAR(50) COMMENT "商品别称";

ALTER TABLE shoin DROP COLUMN product_nickname;

-- DML:插入数据
INSERT INTO shoin VALUES ('0001','T恤衫','衣服',1000,500,'2009-09-20');
INSERT INTO shoin VALUES ('0002','打孔器','办公用品',500,320,'2009-09-11');
INSERT INTO shoin VALUES ('0003','运动T恤衫','衣服',4000,2800,NULL);
INSERT INTO shoin VALUES ('0004','菜刀','厨房用具',3000,2800,'2009-09-20');
INSERT INTO shoin VALUES ('0005','高压锅','厨房用具',6800,5000,'2009-01-15');
INSERT INTO shoin VALUES ('0006','叉子','厨房用具',500,NULL,'2009-09-20');
INSERT INTO shoin VALUES ('0007','擦菜板','厨房用具',880,790,'2009-04-28');
INSERT INTO shoin VALUES ('0008','圆珠笔','办公用品',100,NULL,'2009-11-11');
COMMIT;

SELECT product_id,product_name,product_cost FROM shoin;

SELECT * FROM shoin;

SELECT product_id as id,
             product_name as name,
             product_cost as cost
    FROM shoin;

SELECT DISTINCT product_cate 
    FROM shoin;

SELECT product_name,product_cate
    FROM shoin
 WHERE product_cate = '衣服';