一次小测--（由于数据库问题有些为空值）

7. 1某电商网商品库存管理系统数据的查询操作

7.1.1商品管理

1）每件商品的单件利润是多少？

SELECT NAME AS 商品名称 , outprice - inprice as 商品单件利润 
FROM tbl_commodity

2）查询进价大于等于10的商品信息

SELECT name,inprice FROM tbl_commodity WHERE inprice>=10

3）查询进价小于10的商品信息

SELECT name,inprice FROM tbl_commodity WHERE inprice<10

4）查询进价小于10并且商品类型为1的商品信息

INSERT INTO tbl_commodity VALUES(7,'小白花','中国',1,8,30,20)
SELECT name,inprice,type FROM tbl_commodity WHERE inprice<10 AND type=1

5）查询进价大于10或商品类型为1的商品信息

SELECT name,inprice,type FROM tbl_commodity WHERE inprice>=10 AND type=1

6）查询进价在10-20之间

SELECT name,inprice FROM tbl_commodity WHERE inprice BETWEEN 10 AND 20

7）查询商品售价为空的商品信息

在这里插入代码片

8）查询商品售价为空的商品信息

SELECT *FROM tbl_commodity WHERE outprice is null

9）商品名称包括’玩具’的所有商品

INSERT INTO tbl_commodity VALUES(9,'小白杨玩具','中国',1,9,30,20)
SELECT *FROM tbl_commodity WHERE name like '%玩具%'

10）根据商品进价从低到高排序输出

select*FROM tbl_commodity ORDER BY inprice 

11）根据商品售价从高到低排序输出

select*FROM tbl_commodity ORDER BY inprice DESC

12）商品售价排行榜前5名

SELECT name,outprice from tbl_commodity ORDER BY outprice DESC LIMIT  5

13）商品售价排行榜第6-10名

SELECT name,outprice from tbl_commodity ORDER BY outprice DESC  LIMIT 5,4

14）计算每种商品的平均进价

select type,AVG(inprice) FROM tbl_commodity GROUP BY type

7.1.2客户管理

15）根据客户姓名查询客户信息

SELECT *from tbl_customer WHERE NAME='刘德华'

16）根据客户手机号查询客户信息

SELECT *from tbl_customer WHERE phone='d24c465f85c24bd39af89ea84de9cc72'

17）查询出一共有多少男性客户

SELECT COUNT(gender),
CASE gender
WHEN 1 THEN '男'
WHEN 0 THEN '女'
end 
 from tbl_customer WHERE gender=1 

18）查询出一共有多少女性客户

SELECT COUNT(gender),
CASE gender
WHEN 1 THEN '男'
WHEN 0 THEN '女'
end 
 from tbl_customer WHERE gender=10

7.1.3订单管理

19）使用子查询来获取指定客户买了什么

SELECT NAME FROM tbl_commodity WHERE id IN(
SELECT cid FROM tbl_order WHERE cuid=(
SELECT id FROM tbl_customer WHERE NAME='刘德华'));

20）使用连接查来获取指定客户买了什么

SELECT c.NAME FROM tbl_commodity AS c INNER JOIN(
SELECT o. cid AS id FROM tbl_order AS o INNER JOIN
tbl_customer AS cu ON o.cuid=cu.id
where cu.`name`='刘德华')AS a
ON a.id = c.id; 

7.2某中学在线考试系统数据的查询操作

7.2.1学生管理

1）根据姓名查询学生信息

SELECT *FROM tbl_student where sname ='jack'

2）查询年龄小于30岁的学生信息

SELECT*FROM tbl_student WHERE sage<30

3）查询年龄在25至30之间的学生信息

SELECT*FROM tbl_student WHERE sage BETWEEN 25 AND 30

4）按照年龄从大到小顺序输出学生信息

SELECT*FROM tbl_student  ORDER BY sage DESC

5）查询年龄最小的学生信息

SELECT*FROM tbl_student  ORDER BY sage LIMIT 1

7.2.2老师管理

6）查询姓“刘”“张”的老师的个数

SELECT COUNT(*)FROM tbl_teacher WHERE tname LIKE '刘%'or tname LIKE '张%'

7）查询没学过“张学友”老师课的同学的学号、姓名

SELECT sid, sname FROM tbl_student WHERE sid NOT IN(
SELECT sid FROM tbl_sc WHERE cid IN(
SELECT cid FROM tbl_course WHERE tid=(
SELECT tid FROM tbl_teacher WHERE tname='张学友')));

8）查询老师的总数

SELECT COUNT(*)FROM tbl_teacher;

9）查询每个老师的授课数目

SELECT t.tname,COUNT(c.cid)
FROM tbl_teacher AS t,tbl_course AS c
WHERE t.tid=c.tid
GROUP BY t.tname

10）查询每个老师有多少学生

SELECT t.tname ,COUNT(sc.sid)
FROM tbl_teacher AS t , tbl_course AS c , tbl_sc  AS sc 
WHERE t.tid=c.tid AND c.cid =sc .cid
GROUP BY t.tname

7.2.3科目管理

11）根据科目名称查询科目信息

SELECT *FROM tbl_course WHERE cname ='体育'

12）查询每个科目对应的老师名字

SELECT c.cid ,c.cname ,t.tname
FROM tbl_course AS c, tbl_teacher AS t
WHERE c.tid =t.tid

13）查询每个科目有多少学生在学

SELECT c.cid ,COUNT(sc .sid)
FROM tbl_course AS c ,tbl_sc AS sc 
WHERE c.cid=sc.cid
GROUP BY c.cid 

14）查询科目名称中带有’s’关键字的科目信息

SELECT *FROM tbl_course WHERE cname LIKE '%s%'

15）查询不止1个老师教的科目

SELECT cname FROM tbl_course  WHERE cid IN(
SELECT cid FROM tbl_course  HAVING COUNT(cname)>1);

7.2.4成绩管理

16）查询“c001”课程比“c002”课程成绩高的所有学生的学号

SELECT a.sid FROM 
(SELECT sid, score FROM tbl_sc WHERE cid='1')AS a,
(SELECT sid, score FROM tbl_sc WHERE cid='2')AS b
WHERE a.sid =b.sid AND a.score >b.score;

17）查询平均成绩大于60分的同学的学号和平均成绩

SELECT sid ,AVG(score)
FROM tbl_sc
GROUP BY sid
HAVING AVG( score)>60;

**18）查询所有同学的学号、姓名、选课数、总成绩 **

SELECT tbl_sc.sid, tbl_student .sname ,COUNT( cid),SUM(score)
FROM tbl_student ,tbl_sc
WHERE tbl_student .sid =tbl_sc.sid
GROUP BY tbl_sc .sid

19）查询所有课程成绩小于60分的同学的学号、姓名

SELECT sid ,sname 
FROM tbl_student WHERE sid NOT IN(
SELECT sid FROM tbl_sc WHERE score >60);

20）查询不同老师所教不同课程平均分从高到低显示

SELECT tbl_teacher .tid,tbl_course.cid ,AVG(tbl_sc.score)
FROM tbl_teacher,tbl_course,tbl_sc
WHERE tbl_teacher.tid=tbl_course.tid AND
tbl_course.cid=tbl_sc.cid
GROUP BY tbl_teacher.tid,tbl_course.cid
ORDER BY AVG(tbl_sc.score) DESC