day07 作业

第1题:

1.创建表celebrity , works , summsry

create table celebrity(
sid int primary key,
sname varchar(50),
sage int,
ssex varchar(5)
)

create table works(
wid int primary key,
wwork varchar(50),
wdynasty varchar(20)
)

create table summary(
sid int,
wid int,
sassess varchar(255)
)

2.向表中添加数据

insert into celebrity values
(1,'李白',34,'男'),
(2,'杜甫',24,'男'),
(3,'白居易',31,'男'),
(4,'李商隐',40,'女'),
(5,'苏轼',26,'男'),
(6,'辛弃疾',22,'男')


insert into works values
(1,'将进酒','唐代'),
(2,'蜀道难','唐代'),
(3,'夜雨寄北','唐代'),
(4,'静夜思','唐代'),
(5,'望岳','唐代'),
(6,'钱塘湖春行','唐代'),
(7,'念奴娇赤壁怀古','唐代'),
(8,'蜀水调歌头','唐代')


insert into summary values 
(1,1,'天生我材必有用'),
(1,2,'危乎高哉!'),
(6,8,'明月几时有'),
(3,6,'乱花渐欲迷人眼'),
(4,3,'却话巴山夜雨时'),
(5,7,'大江东去'),
(2,5,'一览众山小'),
(1,4,'举头望明月')

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

select sname from celebrity where ssex='男'

 

(2)查询静夜思的作者

select celebrity.sname from summary  inner join celebrity on summary.sid=celebrity.sid inner join works  on summary.wid=works.wid where works.wwork='静夜思'

(3)查询李白和杜甫的年纪

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

 

(4)查询名人表中的男女人数

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

 

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

select works.wwork,summary.sassess from summary  inner join celebrity on summary.sid=celebrity.sid inner join works  on summary.wid=works.wid where celebrity.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 works.wwork,summary.sassess,celebrity.sage from summary  inner join celebrity on summary.sid=celebrity.sid inner join works  on summary.wid=works.wid where celebrity.sname='李白'

(9)将作品为望岳的朝代改为北宋

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

 

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

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

第2题:

1.创建表product , user , orders 

create table product(
pid int primary key,
pname varchar(50),
pprice int
)
create table user(
uid int primary key,
uname varchar(50),
uage int,
usex char(5)
)
create table orders(
opid int,
ouid int,
onumber int
)

2.添加数据

insert into product values
(1,'手机',2300),
(2,'电脑',5600),
(3,'照相机',1200),
(4,'投影仪',2500)
insert into user values
(1,'李三',20,'男'),
(2,'张四',23,'男'),
(3,'赵五',25,'男'),
(4,'孙六',18,'女'),
(5,'孙悟空',24,'男')
insert into orders values
(1,1,123123),
(1,2,112233),
(2,1,234567),
(2,5,787878),
(3,4,343421),
(3,5,909090),
(4,2,212112),
(4,1,343421)

3.(1)查询孙姓用户的用户 

select uname from user where uname like '孙%'

  

 (2)查询年纪最大的2个用户

select uname from user order by uage desc limit 0,2

 (3)查询李三买的商品

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

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

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

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

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

  

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

select * from product order by pprice desc limit 0,2

第3题

1.创建学生信息表S--student , 选课信息表SC--studentcourse , 课程信息表C--course

create table student(
sno int primary key,
sname varchar(50),
age int,
sex varchar(5)
)
create table course(
cno int primary key,
cname varchar(50),
cteacher varchar(50)
)
create table studentcourse(
sno int,
cno int,
scgrade int
)

2.添加数据

insert into student values
(1,'张三',18,'男'),
(2,'李四',20,'女'),
(3,'王五',23,'男'),
(4,'赵六',22,'女')
insert into course values
(1,'java','何昊'),
(2,'php','李美军'),
(3,'android','王超')
insert into studentcourse values
(1,1,50),
(2,1,66),
(3,1,90),
(1,2,55),
(2,2,68),
(3,2,61),
(4,2,44),
(1,3,90),
(2,3,78),
(3,3,22),
(4,3,55)

3.(1)查询SC表中对应何昊老师所授课程的女生的信息

select * from studentcourse sc inner join student on sc.sno=student.sno 
inner join course on sc.cno=course.cno where course.cteacher='何昊' and student.sex='女'

 

(2)找出没有选修过何老师的课程的所有学生的信息

select * from studentcourse sc inner join student on sc.sno=student.sno 
inner join course on sc.cno=course.cno where not course.cteacher='何昊'

(3)列出有不及格课程(成绩小于60) 的学生的姓名

select student.sname from studentcourse sc inner join student on sc.sno=student.sno 
inner join course on sc.cno=course.cno where sc.scgrade<60 

 

第4题

1.创建学生表student , 老师表teacher

create table student(
id int primary key,
name varchar(20),
score int
)
create table teacher(
id int primary key,
name varchar(30),
class varchar(20),
classroom varchar(20),
student_id int
)

2.添加数据

insert into student values
(1,'张三',80),
(2,'李四',56),
(3,'王五',72),
(4,'赵六',30),
(5,'刘七',66)
insert into teacher values
(1,'刘良誉','测试','2102A',1),
(2,'刘良誉','测试','2011A',2),
(3,'樊庆晓','android','2012A',3),
(4,'孙文龙','PHP','2101A',4),
(5,'许腾升','JAVA','2013B',5)

3.(1)查询刘良誉老师带的学生的姓名和对应学科

select student.name,teacher.class from teacher inner join student on teacher.student_id=student.id where teacher.name='刘良誉'

(2)查询刘良誉老师带的学生,成绩及格的学生姓名

select student.name from teacher inner join student on teacher.student_id=student.id where teacher.name='刘良誉' and student.score>=60

(3)查询赵六老师的老师姓名和对应的学科名称

select teacher.name,teacher.class from teacher inner join student on teacher.student_id=student.id where student.name='赵六'