2021-10-1 数据库练习

1.查询孙姓用户的个数

select count(*) from users where uname like '孙%';
 

2.查询年纪最大的两个用户名

select uname,uage from users order by uage desc limit 0,2;
 

 

3.查询李三购买的商品名

select product.pname from orders inner join product on orders.opid=product.pid inner join users on 
orders.ouid=users.uid where users.uname='李三';

 4.查询购买过的电脑的用户名

select product.pname from orders inner join product on orders.opid=product.pid inner join users on 
orders.ouid=users.uid where users.uname='李三';

4.查询购买过的电脑的用户名

select users.uname from orders inner join product on orders.opid=product.pid inner join users on 
orders.ouid=users.uid where product.pname='电脑';
 

 5.查询订单是909090对应的用户名和商品名

select users.uname,product.pname from orders inner join product on orders.opid=product.pid inner join users on orders.ouid=users.uid where orders.onumber=909090;

6.查询商品表中的最高价格的前两条数据

 

 显示出的结果如下

第二题 

 

1.查询名人表中性别是男的作者

select * from celebrity where ssex='男';

2.查询静夜思的作者

select c.sname 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='静夜思';
 

3.查询李白和杜甫的年纪

select sname,sage from celebrity where sname in ('李白','杜甫');
 

4.查询名人表中的男女人数

select count(*),ssex from celebrity group by ssex;
 

5.查询白居易写的作品名称和对应的名句

select c.sname,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='白居易';
 

 6.查询年纪在25-30岁之间的个数

select count(*) from celebrity where sage between 25 and 30;

7.查询名人表中年纪最小的两条的数据信息

select * from celebrity order by sage limit 0,2;
 

8.查询李白的作品名称，名句和年纪

select w.wwork,s.sassess,c.sage from summary as s inner join works as w on s.wid=w.wid inner 
join celebrity as c on s.sid=c.sid where sname='李白';
 

9.将作品为望岳的朝代修改为北宋

update works set wdynasty='北宋' where wwork='望岳';
 

10.新增名人表中的一个作者王维，年纪25，性别男

insert into celebrity values(7,'王维',25,'男');