2021-10-1数据库练习

1.名人代表作sql

-- 查询名人表中性别是男的作者
select sname,ssex from celebrity where ssex='男';

 -- 查询静夜思的作者
select c.sname,w.wwork from summary as s inner join celebrity as c on s.sid=c.sid inner join works as w on s.wid=w.wid where w.wwork='静夜思';

 -- 查询李白和杜甫的年纪
select sname,sage from celebrity where sname in('李白','杜甫');

 -- 查询名人表中的男女人数
select celebrity.ssex as 性别,count(*) as 个数 from celebrity group by ssex;

 -- 查询白居易写的作品名称和对应的名句
select c.sname as 作者,w.wwork as 作品,s.sassess as 名句 from summary as s inner join celebrity as c on s.sid=c.sid inner join works as w on s.wid=w.wid where c.sname='白居易';

 -- 查询年纪在25到30岁之间的个数
select count(*) as 个数 from celebrity where sage between 25 and 30;

 -- 查询名人表中年纪最小两条的数据信息
select * from celebrity order by sage limit 0,2;

-- 查询李白的作品名称，名句和年纪
select c.sname,c.sage,w.wwork,s.sassess from summary as s inner join celebrity as c on s.sid=c.sid inner join works as w on s.wid=w.wid where c.sname='李白';

 

  -- 将作品为望岳的朝代修改为北宋
update works set wdynasty='北宋' where wwork='望岳';

-- 新增名人表中一个作者王维，年纪25，性别男
insert into celebrity values(7,'王维',25,'男');

 2.教师学生sql

 -- 查询sc表中对应何昊老师所授课程的女生的信息
select s.*,c.cteacher as 老师 from studentcourse as sc inner join student as s on sc.sno=s.sno inner join course as c on sc.cno=c.cno where c.cteacher='何昊' and s.sex='女';

-- 找出没有选修过何老师的课程的所有学生的姓名
select s.sname,c.cteacher from studentcourse as sc inner join student as s on sc.sno=s.sno inner join course as c on sc.cno=c.cno where c.cteacher!='何昊';

 3.用户商品sql

 -- 查询孙姓用户的个数
select count(*) from user where uname like '孙%';

 -- 查询年纪最大的2个用户
select * from user order by uage desc limit 0,2

-- 查询李三买的商品名
select p.pname as 商品名 from `order` as o inner join user as u on o.ouid=u.uid inner join product as p on o.opid=p.pid where u.uname='李三';

-- 查询购买过电脑的用户名
select u.uname as 用户名 from `order` as o inner join user as u on o.ouid=u.uid inner join product as p on o.opid=p.pid where p.pname='电脑';

-- 查询商品表中的最高价格的前两条数据
select * from product order by pprice desc limit 0,2