数据库sql语句练习 基础篇2

数据库建表语句

多表查询

– 数据库productorder。
– 1.查询所有iPhone 6s的入库信息(使用等值连接和内连接)

SELECT pro.pname,rec.*
FROM product pro,recruit rec
WHERE pro.pid = rec.pid
AND pro.pname = "iPhone6s"

– 2.使用内连接查询所有iPhone 7 12月01日之后的销售情况

SELECT *
FROM `order` ord,product pro
WHERE ord.pid = pro.pid AND pro.pname = 'iPhone7' AND ord.odate > '2016-12-01 00:00:00'

SELECT *
FROM `order` ord
where ord.odate > '2016-12-01 00:00:00' AND ord.pid = (
	SELECT pro.pid
	FROM product pro
	WHERE pro.pname = 'iPhone7')

– 3.使用左连接查询11月份入库的所有商品名及入库情况

SELECT *
FROM product pro 
LEFT JOIN recruit rec on pro.pid = rec.pid 
WHERE rec.rdate LIKE '2016-11%'

SELECT * FROM product
LEFT JOIN recruit
ON product.pid = recruit.pid
WHERE rdate >= '2016-11-01' AND rdate < '2016-12-01'

– 4.查询提供过“iPhone 7”商品的所有供应商名及邮编 pro 和Ven 连不起来

SELECT vendor.vname, vendor.vzip FROM product
INNER JOIN recruit
ON product.pid = recruit.pid
INNER JOIN vendor
ON recruit.vid = vendor.vid
WHERE product.pname='iPhone7'

SELECT pro.pname,ven.vname,ven.vzip
FROM product pro,vendor ven,recruit rec
WHERE pro.pid = rec.pid AND ven.vid = rec.vid AND  pro.pname = 'iPhone7'

– 5.查询顾客马云的订单数量,显示订单号，顾客姓名，订单数量 cname,

SELECT  oid,cname,ocount FROM `order` INNER JOIN customer
ON `order`.cid=customer.cid
WHERE cname='马云';

SELECT ord.oid,cus.cname,ord.ocount
FROM customer cus,`order` ord
WHERE cus.cid = ord.cid AND cus.cname = '马云'

– 6.查询12月份哪些顾客买了iPhone 6s,显示顾客姓名，订单号，产品名称，购买日期

SELECT cus.cname,ord.oid,pro.pname,ord.oid
FROM customer cus,product pro,`order` ord
WHERE ord.cid = cus.cid AND ord.pid = pro.pid AND pro.pname = 'iPhone6s' AND (odate BETWEEN '2016-12-01' AND '2017-01-01');

SELECT cname,oid,pname,odate FROM customer INNER JOIN `order`
ON customer.cid=`order`.cid
INNER JOIN product 
ON `order`.pid=product.pid
WHERE pname='iPhone6s' AND (odate BETWEEN '2016-12-01' AND '2017-01-01');

– 7.计算供应商雷军的所有产品的平均价格，显示供应商姓名，平均价格

SELECT ven.vname,AVG(rec.rprice) avgPrice
FROM vendor ven,recruit rec
WHERE rec.vid = ven.vid AND ven.vname = '雷军'

SELECT vname, AVG(rprice) AS '平均价格'
FROM recruit INNER JOIN vendor
ON recruit.vid=vendor.vid
WHERE vname='雷军';

– 8.查询小米Note2 12月份共买了多少台。显示产品名称，买的台数

SELECT pro.pname,SUM(ocount) sum
FROM `order` ord,product pro
WHERE ord.pid = pro.pid AND pro.pname = '小米Note2' AND ord.odate LIKE '%12%'

SELECT pname,sum(ocount) FROM `order` INNER JOIN product
ON `order`.pid =product.pid
WHERE pname='小米Note2'  AND (odate BETWEEN '2016-12-01' AND '2017-01-01');

SELECT pro.pname,SUM(ocount) sum
FROM `order` ord,product pro
WHERE ord.pid = pro.pid  AND ord.odate LIKE '%12%'
GROUP BY pro.pname HAVING pro.pname = '小米Note2'

– 9.查询小米Note2共有几次订单，显示产品名称，订单数量

SELECT pname,COUNT(oid) FROM `order` INNER JOIN product
ON `order`.pid=product.pid
WHERE pname='小米Note2';

– 10.查看12月份iPhone 7有多少盈利，显示产品名称，盈利

SELECT pname,oprice*ocount-pprice*ocount FROM  `order`
INNER JOIN product ON `order`.pid=product.pid
WHERE  pname='iPhone7'  AND (odate BETWEEN '2016-12-01' AND '2017-01-01');

子查询

– 1.查询单笔销量最高的订单信息

SELECT * FROM `order`
WHERE ocount =(
	SELECT MAX(ocount) FROM `order`
)

– 2.查询单笔销量最高的商品信息

SELECT * FROM product
WHERE pid = (
	SELECT pid FROM `order`
	WHERE ocount =(
		SELECT MAX(ocount) FROM `order`
	)
)

– 3.查询2016-12-10日（不包括10日）前售出的所有商品的商品信息

SELECT DISTINCT pro.*
FROM product pro,`order` ord
WHERE pro.pid = ord.pid AND ord.odate < '2016-12-10 00:00:00'

SELECT * FROM product
WHERE pid IN(
	SELECT pid FROM `order`
	WHERE odate < '2016-12-10'
)

– 4.查询2016-12-10日（不包括10日）前没有销量的所有商品的商品信息

SELECT * FROM product
WHERE pid NOT IN(
	SELECT pid FROM `order`
	WHERE odate < '2016-12-10'
) 

– 5.使用EXISTS查询2016-12-01日购买过商品的客户信息

SELECT * FROM customer
WHERE EXISTS (
	SELECT * FROM `order`
	WHERE `order`.cid = customer.cid
		AND TO_DAYS(odate) = TO_DAYS('2016-12-01')
)

– 6.查询产品’小米Mix’的供应商信息

SELECT DISTINCT pro.pname,ven.*
FROM product pro,vendor ven,recruit rec
WHERE pro.pid = rec.pid AND ven.vid = rec.vid AND pro.pname = '小米Mix'

SELECT * from vendor WHERE vid in(
SELECT vid FROM recruit WHERE pid=(
SELECT pid FROM product WHERE pname='小米Mix'))

– 7.查询比“小米Mix”价格高的产品信息

SELECT *
FROM product pro
WHERE pro.pprice > 
(
SELECT pro.pprice
FROM product pro
WHERE pro.pname = '小米Mix'
)

– 8.使用IN查询产品价格超过3000元的供应商信息

SELECT DISTINCT ven.*
from vendor ven,recruit rec
WHERE ven.vid = rec.vid AND rec.vid IN
(
SELECT DISTINCT rec.vid
FROM recruit rec
WHERE rec.rprice > 3000
)

SELECT * from vendor WHERE vid in(
SELECT vid FROM recruit WHERE pid in(
SELECT pid FROM product WHERE pprice>3000) GROUP BY vid)