利用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