数据库语法之DQL-数据库查询,基本查询和条件语句

# 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;


# 条件查询

#查询商品名称为“花花公子”的商品所有信息：
SELECT * FROM product WHERE pname='花花公子';

#查询价格为800商品
SELECT * FROM product WHERE price=800;
#查询价格不是800的所有商品
SELECT * FROM product WHERE price != 800;
SELECT * FROM product WHERE NOT price = 800;
#查询商品价格大于60元的所有商品信息
SELECT * FROM product WHERE price >= 60;
SELECT * FROM product WHERE NOT price < 60;

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

SELECT * FROM product WHERE price BETWEEN 200 AND 1000;
#查询商品价格是200或800的所有商品
SELECT * FROM product WHERE price IN (200,800);

SELECT * FROM product WHERE price=200 OR price =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;   -- IS NULL 和 IS NOT NULL 是专门用来判断是否为空的

#查询有分类的商品
SELECT * FROM product WHERE category_id IS NOT NULL;
#查询所有价格大于2000的电脑商品(catetory_id是c001)或者价格大于1000的服装商品(catetory_id是c002)**
SELECT * FROM product WHERE (price>2000 AND category_id='c001'OR price>1000 AND category_id='c002');
SELECT * FROM product WHERE price >1000 AND category_id IN ('c001','c002');