SQL练习题9月9号

CREATE DATABASE myproduct;
USE myproduct;
CREATE TABLE t_product(
id INT  PRIMARY KEY AUTO_INCREMENT,
productName VARCHAR(50),
price DECIMAL(10,2),
factory VARCHAR(20),
createDate DATE,
keepDate INT,
typepro VARCHAR(20)
)CHARSET=utf8;


DROP TABLE t_product;


INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('苹果',5,'三亚','2015-02-04',30,'水果');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('大蒜',3,'山东烟台','2015-06-09',96,'调料');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('鱿鱼',18,'沿海','2014-04-01',60,'海鲜');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('鸡蛋',8,'四川','2015-09-05',30,'禽蛋');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('金枪鱼',20,'沿海','2015-12-21',60,'海鲜');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('鸭子',13.5,'四川','2015-06-01',2,'禽蛋');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('洋葱',3,'山东大陵','2015-04-14',20,'禽蛋');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('香蕉',4,'三亚','2015-09-03',20,'水果');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('鲫鱼',9,'三江','2015-06-04',20,'鱼类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,typepro) VALUE ('海带',10,'四川','2015-04-13',30,'海鲜');


-- 查询所有商品
SELECT * FROM t_product;
-- 删除id为3的记录
DELETE  FROM t_product WHERE id=3;
-- 修改大蒜价格为5
-- t_product （AS）p表示给表取别名 （AS）可以不加
UPDATE t_product p SET p.price=5, p.factory='四川绵阳' WHERE p.productName='大蒜';
-- 海鲜商品打8折
UPDATE t_product SET price=price*0.8 WHERE typepro='海鲜';
-- 所有禽蛋上调百分之二
UPDATE t_product SET price=price*1.02 WHERE typepro='禽蛋';
-- 查询所有鱼类商品
SELECT * FROM t_product WHERE typepro= '鱼类';


-- 查询所有保质期3个月以上的商品
SELECT* FROM t_product WHERE keepDate>90;


-- 查询所有山东省产的禽蛋类商品(产地模糊查询)
SELECT *FROM t_product WHERE factory LIKE '山东%' AND typepro='禽蛋';


-- 查询所有2015年9月份所产的商品
SELECT *FROM t_product WHERE   createDate>='2015-09-01' AND createDate<'2015-10-01';
 
-- 查询所有过期商品
-- now()得到当前时间
-- date_add(date,int)在指定时间上增加或减少相应的值
SELECT * FROM t_product p WHERE DATE_ADD(p.createDate,INTERVAL p.keepDate DAY) <NOW();
-- 查询所有的类别
-- DISTINCT去掉重复记录
SELECT DISTINCT P.typepro FROM t_product p; 


-- 查询id 在（5，8，10），范围的商品
SELECT * FROM t_product WHERE id IN(5,8,10);


INSERT INTO t_product(productName,price,factory) VALUES ('人森',199,'岁的法国');


SELECT * FROM t_product;
-- 查询生产日期为null商品
-- 查询null值使用is null和 is not null
SELECT * FROM t_product WHERE createDate IS NULL;
-- 按照价格排序，默认ASC升序，加上DESC降序
SELECT productName FROM t_product ORDER BY price ASC;
SELECT * FROM t_product ORDER BY price DESC;








CREATE TABLE t_man(
id INT PRIMARY KEY AUTO_INCREMENT,
manName VARCHAR(20)


)CHARSET=utf8;
INSERT INTO T_man(manName) VALUES ('张三');
INSERT INTO T_man(manName) VALUES ('李四');


-- 添加一列
ALTER TABLE t_man ADD phone VARCHAR(20);
SELECT *FROM t_man;
-- 修改列


ALTER TABLE t_man CHANGE phone tel VARCHAR(20);


-- 删除列
ALTER TABLE t_man DROP COLUMN tel
-- UNIQUE表示该列的值不能重复
ALTER TABLE t_man CHANGE manName  manName VARCHAR(20) UNIQUE;


INSERT INTO t_man(manName) VALUES('张三');




CREATE TABLE t_woman(
id INT PRIMARY KEY AUTO_INCREMENT,
womanName VARCHAR(20) NOT NULL, -- not null表示该列必须有值，不能有空
womanStatus VARCHAR(20) DEFAULT '群众' -- default 默认值。如果插入该列时，没有给该列赋值，则该列为默认值


)CHARSET=utf8;
INSERT INTO t_woman(womanName,womanStatus) VALUES('张三','党员');
SELECT * FROM t_woman;
INSERT INTO t_woman(womanName) VALUE('李四');