sql之查询

准备工作

#创建商品表：
create table product(
	pid int primary key,
	pname varchar(20),
	price double,
	category_id varchar(32)
);
INSERT INTO product(pid,pname,price,category_id) VALUES(1,'联想',5000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(2,'海尔',3000,'c001');
INSERT INTO product(pid,pname,price,category_id) VALUES(3,'雷神',5000,'c001');

INSERT INTO product(pid,pname,price,category_id) VALUES(4,'JACK JONES',800,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(5,'真维斯',200,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(6,'花花公子',440,'c002');
INSERT INTO product(pid,pname,price,category_id) VALUES(7,'劲霸',2000,'c002');

INSERT INTO product(pid,pname,price,category_id) VALUES(8,'香奈儿',800,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(9,'相宜本草',200,'c003');
INSERT INTO product(pid,pname,price,category_id) VALUES(10,'面霸',5,'c003');

INSERT INTO product(pid,pname,price,category_id) VALUES(11,'好想你枣',56,'c004');
INSERT INTO product(pid,pname,price,category_id) VALUES(12,'香飘飘奶茶',1,'c005');

INSERT INTO product(pid,pname,price,category_id) VALUES(13,'果9',1,NULL);

语法：

select [distinct] * |列名1，列名2， from 表名 [where 条件];

简单查询

#1.查询所有的商品：
select * from product;

#2.查询商品名称与价格
select pname,price from product;

#3.去除重复值
select distinct price from product;

#4.查询结果是表达式（运算查询）：将所有商品价格+10元进行显示。
select pname,price+10 from product;

#5.别名查询，使用关键字是as(as可以省略)
select * from product as p;
select pname as pn from product;

条件查询 

比较运算符	> <  <=   >=   =  <> !=	大于、小于、大于(小于)等于、不等于
	BETWEEN  ...AND...	显示在某一区间的值(含头含尾)(也可以是日期)
	IN(set)	显示在in列表中的值，例：in(100,200)
	LIKE ‘张%’	模糊查询，Like语句中， %代表零个或多个任意字符， _代表一个字符， 例如：first_name like ‘_a%’;
	IS NULL /IS NOT NULL	判断为空/不为空 where name IS NULL              和name = ‘’
逻辑运算符	and	多个条件同时成立
	or	多个条件任一成立
	not	不成立，例：where not(salary>100);

 

#查询商品名称为花花公子的商品的所有信息：
select * from product where pname='花花公子';

#查询价格为800的商品
select * from product where price>800;

#查询价格不是800的所有商品
select * from product where price !=800;
select * from product where price <>800;
select * from product where price not(price=800);

#查询价格大于60元的所有商品信息
select * from product where price > 60;

#查询商品价格在200到1000之间所有商品
select * from product where price >=200 and price <=1000;
select * from product where  between 200 and 1000;

#查询商品价格200或1000的所有商品；
select * from product where price=200 or price=1000;
select * from product where price in (200,800);


#查询所有带'霸'字的所有商品；
select * from product where  pname like '%霸%';

#查询以'香'开头的的所有商品
select * from product where pname like '香%';

#查询第二个字带'想'的所有商品
select * from product where pname like '_想%';

#查询没有分类的商品；
select * from product where category_id is null;

#查询有分类的商品
select * from product where category_is is not null;