(一)简单查询、带条件查询
请按照以下要求查询表goods中的数据。
goods表结构如下:
字段名
类型
描述
id
INT
商品编号
name
VARCHAR(20)
商品名称
expiry_date
DATE
生产日期
price
FLOAT
商品价格
插入数据:
insert into goods values
(1,'洗衣粉','2016-03-01',null),
(2,'肥皂','2015-12-22',2),
(3,'毛巾','2015-01-20',9.9),
(4,'清洗剂','2016-02-19',8.6),
(5,'卫生纸','2015-11-06',null),
(6,'牙刷','2014-11-14',4.5),
(7,'牙膏','2016-07-05',13),
(8,'洗面奶','2016-03-29',43.8),
(9,'香水','2015-09-16',null),
(10,'口罩','2015-01-30',5);
1)查询出goods表中所有字段
select * from goods;
2)查询出goods表中生产日期在一年前的商品
select name,expiry_date from goods where expiry_date between
'2016-05-15' and '2017-05-25';
3)查询出goods表中商品名称中带“洗”字的商品
select name from goods where name like
"%洗%";
4)查询出goods表中商品编号为2,4,6,8的商品
select id,name from goods where id in(2,4,6,8);
5)查询出goods表中price字段值为NULL的商品
select name,price from goods where price is null;
6)查询出goods表中price字段不为NULL并且商品编号在4到10范围内的商品
select id,name,price from
goods where price is not null AND id between 4 and 10;