Java心得29

最新推荐文章于 2023-09-15 08:11:52 发布

C_yaya

最新推荐文章于 2023-09-15 08:11:52 发布

阅读量260

点赞数

分类专栏：心得

本文链接：https://blog.csdn.net/C_yaya/article/details/48326531

版权

心得专栏收录该内容

34 篇文章 1 订阅

订阅专栏

今天学习了数据库：

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

INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('大蒜',5.55,'四川南充','2015-04-15',30,'日杂类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('龙虾',50.00,'山东青岛','2015-09-10',7,'海鲜类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('鸡蛋',8.50,'山东济南','2015-08-05',15,'禽蛋类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('老陈醋',4.00,'山西太原','2015-07-25',365,'日杂类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('花鲢',5.00,'四川简阳','2015-09-08',3,'鱼类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('大闸蟹',45.50,'四川成都','2015-09-09',7,'海鲜类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('乌鸡',10.00,'山东烟台','2015-09-10',15,'禽蛋类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('豆瓣',7.50,'四川郫县','2015-08-01',180,'日杂类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('白鲢',4.50,'四川温江','2015-09-08',5,'鱼类');
INSERT INTO t_product(productName,price,factory,createDate,keepDate,producttype) VALUES('白糖',3.00,'四川乐山','2014-04-15',365,'日杂类');

SELECT * FROM t_product;
-- 删除id为3的记录
DELETE FROM t_product WHERE id=3;
-- 修改商品’大蒜’价格为9元
UPDATE t_product SET price=9 WHERE productName='大蒜';
-- 所有海鲜类商品打8折
UPDATE t_product SET price=price*0.8 WHERE producttype='海鲜类';
-- 所有禽蛋类商品价格上浮2%
UPDATE t_product SET price=price*(1+0.2) WHERE producttype='禽蛋类';
-- 查询所有鱼类商品
SELECT * FROM t_product WHERE producttype='鱼类';
-- 查询所有保质期3个月以上的商品
SELECT * FROM t_product WHERE keepDate>90;
-- 查询所有山东省产的禽蛋类商品(产地模糊查询)
SELECT * FROM t_product WHERE factory LIKE '山东%' AND producttype='禽蛋类';
-- 查询所有2015年9月份所产的商品
SELECT * FROM t_product WHERE createDate>='2015-09-01' AND createDate<'2015-10-01';