MySQL与Oracle的查询举例

（1）在商品信息表products查询唯一的供应商ID：

SQL语句：

select distinct vend_id from products;

结果：

（2）在商品信息表products查询唯一的供应商ID和商品价格：

SQL语句：

select distinct vend_id,prod_price from products;

结果：

（3）查询所有商品名称，并按名称升序排序：

SQL语句：

select prod_name from products order by prod_name;

结果：

（4）按照价格降序，也就是从高到低，，查询商品ID，价格，名称：

SQL语句：

select prod_id,prod_price, prod_name from products order by prod_price desc;

结果：

（5）查询商品id，价格，名称并按照价格降序、名称升序

SQL语句：

select prod_id,prod_price ,prod_name from products order by prod_price desc,prod_name;

结果：

（6）查询名称为King doll的商品名称和价格:

SQL语句：在Mysql中：商品名称可以为king doll 也可以为King doll ，在oracle中查询某字符需要区分大小写，商品名称必须为King doll。

select prod_name ,prod_price from products where prod_name = 'King doll';

结果：

（7）查询不是供应商DLL01制造的商品：

SQL语句：

select vend_id ,prod_name from products where vend_id != 'DLL01';

结果：

（8）查询价格在5到10美元的商品

SQL语句：

select prod_name,prod_price from products where prod_price between 5 and 10;

结果：

（9）查询邮箱地址为NULL的顾客：

SQL语句：

select cust_name ,cust_email from customers where cust_email is null;

结果：

（10）查询邮箱地址不为NULL的顾客：

SQL语句：

select cust_name ,cust_email from customers where cust_email is not null;

结果：

（11）取前五个商品(不一样)

SQL语句：

-- MySQL:
select * from products limit 5;

-- Oracle:
     SELECT * FROM PRODUCTS WHERE ROWNUM <= 5;

结果(MySQL的结果)：

（12）根据商品价格，从高到低取4-6名商品(不一样)：

SQL语句：

-- MySQL:
    select prod_name,prod_price from products order by prod_price desc limit 3,3;
    
-- Oracle:
    SELECT prod_name,prod_price FROM (
	    SELECT ROWNUM AS rn,a.* FROM 
		    (SELECT prod_name,prod_price FROM PRODUCTS 
			    ORDER BY prod_price DESC) a
	    WHERE ROWNUM <= 6 ) b
WHERE b.rn > 3 ;

结果：

（13）列出价格为10美元及以上，且由DLL01或BRS01制造的产品：

SQL语句：

select * from products where prod_price >= 10 and (vend_id = 'DLL01' or vend_id = 'BRS01');

结果：

（14）查询由供应商DLL01和BRS01制造的产品，按照名称排序：

SQL语句：

select prod_name,vend_id from products where vend_id in('DLL01','BRS01') order by prod_name;
或
select prod_name,vend_id from products where vend_id = 'DLL01' OR VEND_ID = 'BRS01' order by prod_name;

结果：

（15）查询不是由供应商DLL01或BRS01制造的产品，按照名称排序：

SQL语句：

select prod_name,vend_id from products where vend_id not in('DLL01','BRS01') order by prod_name;
或
select prod_name,vend_id from products where vend_id != 'DLL01' AND VEND_ID != 'BRS01' order by prod_name;

结果：

（16）查询商品名称是以Fish开头的商品：

SQL语句：

select prod_id,prod_name from products where prod_name like 'Fish%';

结果：

（17）查询商品名称是包含teddy的商品：

SQL语句：

select prod_id,prod_name from products where prod_name like '%teddy%';

结果：

（18）拼接供应商名和供应商国家（不一样）：

SQL语句：

-- MySQL:
    select concat(vend_name,'(',vend_country,')' ) from vendors order by vend_name;
    
-- Oracle:
    SELECT VEND_NAME || '(' || VEND_COUNTRY || ')' FROM VENDORS  ORDER BY VEND_NAME;

结果：

（19）统计Customers表中的顾客总数：

SQL语句：

SELECT COUNT(*) FROM CUSTOMERS;

结果：

（20）只对具有电子邮件地址的客户计数：

SQL语句：

select count(cust_email ) from customers;

结果：

（21）返回最贵的商品价格：

SQL语句：

select max(prod_price ) from products;

结果：

（22）查看每个供应商提供的最贵的商品价格：

SQL语句：

select vend_id ,max(prod_price ) from products group by vend_id;

结果：

（23）查询商品数大于等于3个的订单号，并且订单商品数与订单号升序：

SQL语句：

select order_num,count(*) as items from orderitems group by order_num having count(*) >= 3 order by items,order_num;

结果：

（24）查询所有订购物品RGAN01的顾客ID：

SQL语句：

select cust_id from orders where order_num 
	in (select order_num from orderitems where prod_id = 'RGAN01');

结果：

（25）查询所有订购物品RGAN01的顾客信息：

SQL语句：

select * from customers c where c.cust_id in 
 (select cust_id from orders where order_num 
	in (select order_num from orderitems where prod_id = 'RGAN01'));

结果：

（26）显示Customers表中每个顾客的订单总数：

SQL语句：

select cust_name ,(select count(*) from orders o where o.cust_id = c.cust_id) as orders from customers c;

结果：

（27）查询各供应商的商品及价格：

SQL语句：

select vend_name,prod_name,prod_price from vendors v join products p on v.vend_id = p.vend_id;

结果：

（28）检索包括没有订单顾客在内的所有顾客：

SQL语句：

select c.cust_id,c.cust_name,o.order_num from customers c left join orders o on c.cust_id  =  o.cust_id ;

结果：

（29）检索存在订单的所有顾客：

SQL语句：

select c.cust_id,c.cust_name,o.order_num from customers c inner join orders o on c.cust_id  =  o.cust_id ;

结果：

（30）查询illinois、Indiana、Michigan等州的顾客,还想包括名叫Fun4ALL客户的信息（用union）：

SQL语句：

select cust_id,cust_name ,cust_state  from customers where cust_state in ('IL','IN','MI')
union 
select cust_id,cust_name ,cust_state  from customers where cust_name = 'Fun4All';

结果：

（31）查询illinois、Indiana、Michigan等州的顾客,还想包括名叫Fun4ALL客户的信息(用union all)：

SQL语句：

select cust_id,cust_name ,cust_state  from customers where cust_state in ('IL','IN','MI')
union all
select cust_id,cust_name ,cust_state  from customers where cust_name = 'Fun4All';

结果：

3.查询在2012年一月份内订单的顾客名称(不一样）。

SQL语句：

-- MySQL:
select distinct c.cust_name from customers c join orders o on c.cust_id = o.cust_id and year(o.order_date) = 2012 and month(o.order_date ) = 1;

-- Oracle:
select distinct c.cust_name from customers c join orders o on c.cust_id = o.cust_id and EXTRACT (YEAR FROM o.ORDER_DATE ) = 2012 and EXTRACT (month FROM o.ORDER_DATE ) = 1;

结果：

4.查询订单总额超过100美元的订单号。

SQL语句：

select order_num, sum(quantity * item_price ) as sumMoney from orderitems group by order_num having summoney > 100;

结果：

5.根据顾客所有订单总额降序输出客户名称（不一样）。

SQL语句：

-- MySQL:
    select a.custId,a.custName,sum(a.sum_Money)as sumMoney from (
	select c.cust_id as custId,c.cust_name as custName,o.order_num,sum(o2.quantity * o2.item_price ) as sum_Money 
		from customers c 
		left join orders o on c.cust_id = o.cust_id 
		left join orderitems o2 on o2.order_num  = o.order_num 
		group by o.order_num) a 
	group by a.custId
	order by sumMoney desc;
	
-- Oracle:
   SELECT C1.CUST_ID,C1.CUST_NAME,SUM(C2.SUM_MONEY) AS SUMMONEY FROM CUSTOMERS C1 
LEFT JOIN( SELECT A.CUST_ID AS CUSTID,A.ORDER_NUM ,B.sum_money AS SUM_MONEY FROM ORDERS A 
JOIN (SELECT ORDER_NUM,sum(QUANTITY * ITEM_PRICE ) AS sum_money  FROM ORDERITEMS GROUP BY ORDER_NUM) B
	      ON A.ORDER_NUM = B.ORDER_NUM) C2
	ON C1.CUST_ID = C2.CUSTID 
	GROUP BY C1.CUST_ID,C1.CUST_NAME 
	ORDER BY SUMMONEY DESC;

结果：