利用MySQL进行数据复杂查询
一，某电商网商品库存管理系统数据的查询操作
1,查询每件商品的单件利润
select name as 商品名称,(outprice-inprice) as 商品单件利润 from tb1_commodity
2,查询进价大于等于10的商品信息
SELECT NAME
,
inprice
FROM
tb1_commodity
WHERE
inprice >= 10
3,查询进价小于10的商品信息
SELECT NAME
,
inprice
FROM
tb1_commodity
WHERE
inprice < 10``
4）查询进价小于10并且商品类型为1的商品信息
SELECT NAME
,
inprice,
type
FROM
tb1_commodity
WHERE
inprice < 10
AND type = 1
5）查询进价小于10或商品类型为1的商品信息
SELECT NAME
,
inprice,
type
FROM
tb1_commodity
WHERE
inprice < 10
OR type = 1
6）查询进价在10-20之间
SELECT NAME AS
inprice,
type
FROM
tb1_commodity
WHERE
inprice <= 20 AND inprice >= 10
7）查询商品售价为空的商品信息
SELECT NAME
FROM
tb1_commodity
WHERE
outprice IS NULL
8）查询商品进价为10或20或30的商品信息
SELECT
*
FROM
tb1_commodity
WHERE
inprice = 10
OR inprice = 20
OR inprice = 30
9）商品名称包括\'玩具\'的所有商品
SELECT NAME
FROM
tb1_commodity
WHERE
NAME LIKE \'%玩具%\'
10）根据商品进价从低到高排序输出
SELECT NAME
,
inprice
FROM
tb1_commodity
ORDER BY
inprice ASC
11）根据商品售价从高到低排序输出
SELECT NAME
,
outprice
FROM
tb1_commodity
ORDER BY
outprice DESC
12）商品售价排行榜前5名
SELECT NAME
,
outprice
FROM
tb1_commodity
ORDER BY
outprice DESC
LIMIT 5
13）商品售价排行榜第6-10名
SELECT NAME
,
outprice
FROM
tb1_commodity
ORDER BY
outprice DESC
LIMIT 6,
10
14）计算每种商品的平均进价
SELECT
type,
avg( inprice )
FROM
tb1_commodity
GROUP BY
type
15）根据客户姓名查询客户信息
SELECT
*
FROM
tb1_customer
WHERE
NAME = \'刘德华\'
16）根据客户手机号查询客户信息
SELECT
*
FROM
tb1_customer
WHERE
phone = 123
17）查询出一共有多少男性客户
SELECT
count( NAME )
FROM
tb1_customer
WHERE
gender = 1
18）查询出一共有多少女性客户
SELECT
count( NAME )
FROM
tb1_customer
WHERE
gender = 0
19）使用子查询来获取指定客户买了什么
SELECT NAME
FROM
tb1_commodity
WHERE
id IN (
SELECT
cid
FROM
tb1_order
WHERE
cuid = ( SELECT id FROM tb1_customer WHERE NAME = \'刘德华\' ))
20）使用连接查来获取指定客户买了什么
SELECT
c.NAME
FROM
tb1_order a,
tb1_customer b,
tb1_commodity c
WHERE
b.id = c.id
AND b.id = a.cid;
二，某中学在线考试系统数据的查询操作
1）根据姓名查询学生信息
select * from tb1_student where sname=\'关胜\'
2）查询年龄小于30岁的学生信息
select * from tb1_student where sage<30
3）查询年龄在25至30之间的学生信息
select * from tb1_student where sage BETWEEN 25 and 30
4）按照年龄从大到小顺序输出学生信息
select * from tb1_student order by sage desc
5）查询年龄最小的学生信息
select * from tb1_student order by sage asc limit 1
6）查询姓“刘”“张”的老师的个数
select count(*) from tb1_teacher where tname like \'刘%\' or tname like \'张%\'
7）查询没学过“张学友”老师课的同学的学号、姓名
SELECT
sid,
sname
FROM
tb1_student
WHERE
sid NOT IN (
SELECT
sid
FROM
tb1_sc
WHERE
cid IN (
SELECT
cid
FROM
tb1_course
WHERE
tid =(
SELECT
tid
FROM
tb1_teacher
WHERE
tname = \'张学友\'
)))
8）查询老师的总数
SELECT
count( tid )
FROM
tb1_teacher
ORDER BY
tid
9）查询每个老师的授课数目
SELECT
a.tid,
count( b.tname )
FROM
tb1_teacher b,
tb1_course a
WHERE
a.tid = b.tid
GROUP BY
b.tname
10）查询每个老师有多少学生
SELECT
tname,
COUNT( c.sid )
FROM
tb1_teacher a,
tb1_course b,
tb1_sc c
WHERE
a.tid = b.tid
AND b.cid = c.cid
GROUP BY
c.sid
11）根据科目名称查询科目信息
SELECT
*
FROM
tb1_course
WHERE
cname = \'体育\'
12）查询每个科目对应的老师名字
SELECT
a.cid,
a.cname,
b.tname
FROM
tb1_course a, tb1_teacher b
WHERE
a.tid = b.tid
13）查询每个科目有多少学生在学
SELECT
cname,
COUNT( cname )
FROM
tb1_course a,
tb1_sc b
WHERE
a.cid = b.cid
GROUP BY
cname
14）查询科目名称中带有\'s\'关键字的科目信息
SELECT
*
FROM
tb1_course
WHERE
cname LIKE \'%s%\'
15）查询不止1个老师教的科目
SELECT
a.cid,
a.cname,
b.tname
FROM
tb1_course a,
tb1_teacher b
WHERE
a.tid = b.tid
GROUP BY
cname
16）查询“c001”课程比“c002”课程成绩高的所有学生的学号
SELECT
a.sid
FROM
( SELECT sid, score FROM tb1_sc WHERE cid = 1 ) AS a,
( SELECT sid, score FROM tb1_sc WHERE cid = 2 ) AS b
WHERE
a.sid = b.sid
AND a.score > b.score
17）查询平均成绩大于60分的同学的学号和平均成绩
SELECT
sid,
avg( score )
FROM
tb1_sc
GROUP BY
sid
HAVING
avg( score )> 60
18）查询所有同学的学号、姓名、选课数、总成绩
SELECT
tb1_sc.sid,
tb1_student.sname,
count( \'cid\' ),
sum( score )
FROM
tb1_student,
tb1_sc
WHERE
tb1_student.sid = tb1_sc.sid
GROUP BY
tb1_sc.sid
19）查询所有课程成绩小于60分的同学的学号、姓名
SELECT
a.sid,
a.sname,
b.score
FROM
tb1_student a,
tb1_sc b
WHERE
a.sid = b.sid
AND score < 60
20）查询不同老师所教不同课程平均分从高到低显示
SELECT
a.tid,
b.cid,
avg( c.score )
FROM
tb1_teacher a,
tb1_course b,
tb1_sc c
WHERE
a.tid = b.tid
AND b.cid = c.cid
GROUP BY
a.tid,
b.cid
ORDER BY
avg( c.score ) DESC
