2021-10-1 数据库练习

科技前沿 • 2025-02-15 21:44 • 阅读 38

大家好，我是讯享网，很高兴认识大家。

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.查询订单是对应的用户名和商品名

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=;

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,'男');

2021-10-1 数据库练习

第二题

相关推荐