3. SQL - 查询

1.SQL-单表查询

1.1 DQL准备工作

#创建商品表：
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]
* | 列名,列名
from 表
where 条件

1.2 简单查询

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

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

#别名查询.使用的关键字是as（as可以省略的）.表别名:
select * from product as p;

#别名查询.使用的关键字是as（as可以省略的）.列别名:
select pname as pn from product;

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

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

1.3 条件查询

练习：
#查询商品名称为“花花公子”的商品所有信息：
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 NOT(price = 800)

#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price > 60;

#查询商品价格在200到1000之间所有商品
SELECT * FROM product WHERE price >= 200 AND price <=100