MySQL0902作业（待巩固）
1）每件商品的单件利润是多少？
select name 商品名称,( outprice - inprice ) 商品单件利润
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 between 10
and 20
7）查询商品售价为空的商品信息
select name
from
tb1_commodity
where
outprice is null
8）查询商品进价为10或20或30的商品信息
select
name,
inprice
from
tb1_commodity
where
inprice in (
10,
20,
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(*) from tb1_customer where gender=1
18）查询出一共有多少女性客户
select count(*) 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
a.name
from
tb1_commodity as a
inner join (
select
b.cid as id
from
tb1_order as b
inner join tb1_customer as c on b.cuid = c.id
where
c.name = '刘德华'
) as d on d.id = a.id;
学生管理
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(*) from tb1_teacher
9）查询每个老师的授课数目
select t.tname,count(c.cid)
from tb1_teacher as t,tb1_course as c
where t.tid=c.tid
group by t.tname;
10）查询每个老师有多少学生
select
t.tname,
count( sc.sid )
from
tb1_teacher as t,
tb1_course as c,
tb1_sc as sc
where
t.tid = c.tid
and c.cid = sc.cid
group by
t.tname
11）根据科目名称查询科目信息
select * from tb1_course where cname='python'
12）查询每个科目对应的老师名字
select
c.cid,
c.cname,
t.tname
from
tb1_course as c,
tb1_teacher as t
where
c.tid = t.tid
13）查询每个科目有多少学生在学
select
c.cid,
count( sc.sid )
from
tb1_course as c,
tb1_sc as sc
where
c.cid = sc.cid
group by
c.cid
14）查询科目名称中带有’s’关键字的科目信息
select * from tb1_course where cname like '%s%'
15）查询不止1个老师教的科目
select cname from tb1_course where cid in(select cid from tb1_course having count(cname>1))
16）查询“c001”课程比“c002”课程成绩高的所有学生的学号
select a.sid from
(select sid,score from tb1_sc where cid='c001') as a,
(select sid,score from tb1_sc where cid='c002') 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 sid, sname from tb1_student where sid not in ( select sid from tb1_sc where score > 60)
20）查询不同老师所教不同课程平均分从高到低显示
select
tb1_teacher.tid,
tb1_course.cid,
avg( tb1_sc.score )
from
tb1_teacher,
tb1_course,
tb1_sc
where
tb1_teacher.tid = tb1_course.tid
and tb1_course.cid = tb1_sc.cid
group by
tb1_teacher.tid,
tb1_course.cid
order by
avg( tb1_sc.score ) desc;
