Mysql和Oracle下的select,while,null，DESC和ASC，distinct，-蒲公英云

Mysql和Oracle下的select,while,null，DESC和ASC，distinct，

– 查询单列
select prod_name from products;
– 查询多列
select prod_id,prod_name,prod_price from products
– 查询所有列
select * from products;
– distinct查询唯一的供应商ID(去重)
select distinct vend_id from products;
– 没有筛选之前
select vend_id,prod_price from products;
– distinct作用与所有的列，不仅是跟在其后的那一列
select distinct vend_id,prod_price from products;
– 排序（默认升序）ASC
select prod_name from products order by prod_name;
– 按照价格降序排列DESC
select prod_id,prod_name,prod_name from products order by prod_price desc;
– 多列排序:查询商品id，价格，名称并按照价格、名称升序
select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name;
– 查询商品id，价格，名称并按照价格降序，名称升序
select prod_id,prod_price,prod_name from products order by prod_price desc,prod_name;
– where等值查询
select prod_name,prod_price from products where prod_price=3.49
– 查询名称为King doll的商品名称与价格
select prod_name,prod_price from products where prod_name=‘King doll’;
– where范围查询
select prod_name,prod_price from products where prod_price < 10
– 查询不是供应商DLL01制造的商品
select vend_id,prod_name from products where vend_id != ‘DLL01’
– 查询价格在5到10美元的商品
select vend_id,prod_name,prod_price from products where prod_price between 5 and 10;
– null值判断:查询邮箱地址为null的顾客
select cust_id,cust_name,cust_address from customers where cust_email is null;
– 查询邮箱地址不为null的顾客
select cust_name,cust_email from customers where cust_email is not null;