select p.pname
from user u
inner join order01 o on o.ouid = u.uid
inner join product p on o.opid = p.pid
where u.uname = '李三'
1.查询名人表性别为男的作者
select *
from celebrity c
where c.ssex= '男'
2.查询静夜思的作者
select
c.sname
from
celebrity c
inner join summary s on c.sid=s.sid
inner join works w on w.wid = s.wid
where 1=1
and w.wwork = '静夜思'
3.查询李白和杜甫的年纪
select
c.sname,
c.sage
from
celebrity c
where 1=1
and c.sname = '李白'
or c.sname = '杜甫'
![在这里插入图片描述](https://img-blog.csdnimg.cn/edbf51836a7044ed88c38ddc032b460b.png)
4.查询名人表中的男女个数
select
w.wwork,
s.sassess
from
celebrity c
inner join summary s on c.sid=s.sid
inner join works w on w.wid = s.wid
where 1=1
and c.sname = '白居易'
5.查询白居易写的作品名称和对应的名句
select
w.wwork,
s.sassess
from
celebrity c
inner join summary s on c.sid=s.sid
inner join works w on w.wid = s.wid
where 1=1
and c.sname = '白居易'
6.查询年纪在25到30之间的个数
select count(1)
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
celebrity c
inner join summary s on c.sid=s.sid
inner join works w on w.wid = s.wid
where 1=1
and c.sname = '李白'
9.将作品为望月的朝代修改为北宋
update works set wdynasty='北宋' where wwork='望岳'
10.新增名人表中的一个作者,王伟,年纪25,性别男
insert into values (7,'王伟',25,'男')
1.查询性孙的用户个数
select *
from user
where name like '孙%'
2.查询年纪最大的2个用户名
select uname
from user
order by uage desc
limit 2
3.查询李三买的商品名
select p.pname
from user u
inner join order01 o on o.ouid = u.uid
inner join product p on o.opid = p.pid
where u.uname = '李三'
4.查询购买过电脑的用户名
select u.uname
from user u
inner join order01 o on o.ouid = u.uid
inner join product p on o.opid = p.pid
where
p.pname = '电脑'
5.查询订单是909090对应的用户名和商品
select u.uname,p.pname
from user u
inner join order01 o on o.ouid = u.uid
inner join product p on o.opid = p.pid
where
o.onumber='909090'
6.查询商品表中的最高价格的前两条数据
select * from
product order by price desc limit 0,2
1.查询sc表中对应的何昊老师所授课的女生的信息
SELECT s.sname,s.age as '年龄', s.sex FROM
student s
join sc on s.sno = sc.sno
join c on sc.cno = c.cno
where 1=1
and c.cteacher = '何昊'
and s.sex = '女'
2.找出所有没有选修何昊老师的所有学生的名字
SELECT DISTINCT(s.sname) FROM
student s
join sc on s.sno = sc.sno
join c on sc.cno = c.cno
where 1=1
and c.cteacher != '何昊'
3.列出所有不及格的学生姓名
select DISTINCT(s.sname)
from student s
join sc on s.sno = sc.sno
join c on sc.cno = c.cno
where 1=1
and sc.scgrade < 60