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

落日映苍穹つ 2022-10-19 04:27 94阅读 0赞

– 查询单列  
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;