mysql学习第二天作业

题目

要求1：根据上图创建数据库 & 表结构并录入数据
create table class(
cid int not null primary key auto_increment,
caption varchar(20) not null
)default charset=utf8;

desc class;

insert into class
values
(1, ‘三年二班’),
(null, ‘一年三班’),
(null, ‘三年一班’);

select * from class;

create table student(
sid int not null primary key auto_increment,
sname varchar(5) not null,
gender char(1) not null,
class_id int not null
)default charset=utf8;

desc student;

insert into student
values
(1, ‘钢蛋’, ‘女’, 1),
(null, ‘铁锤’, ‘女’, 1),
(null, ‘山炮’, ‘男’, 2);

select * from student;

create table teachar(
tid int not null primary key auto_increment,
tname varchar(5) not null
)default charset=utf8;

desc teachar;

insert into teachar
values
(1, ‘菠萝’),
(null, ‘苍空’),
(null, ‘饭局’);

select * from teachar;

create table course(
cid int not null primary key auto_increment,
cname varchar(5) not null,
teachar_id int not null
)default charset=utf8;

desc course;

insert into course
values
(1, ‘生物’, 1),
(null, ‘体育’, 1),
(null, ‘物理’, 2);

select * from course;

create table score(
sid int not null,
student_id int not null,
course_id int not null,
number int not null
)default charset=utf8;

desc score;

insert into score
values
(1, 1, 1, 60),
(2, 1, 2, 59),
(3, 2, 2, 100);

select * from score;

要求二：创建用户luffy并赋予此数据库的所有权限

要求三：查询姓“李”的老师的个数
select count(*) as count from teachar where tname like ‘李%’;

要求四：查询姓“张”的学生名单
select * from student where sname like ‘张%’;

要求五：查询男、女生的人数
笨方法
select count() as num from student where gender=‘男’;
select count() as num from student where gender=‘女’;

select gender,count(*) as num from student group by gender;

要求六：查询同名同姓学生名单，并统计同名人数
在学生表插入一行数据

有重名意味着分组后大于1
select * from student where sname in
(select sname from student
group by sname having count() > 1);
统计同名人数
select sname, count() as num from student group by sname having count(*) > 1;

要求七：查询“三年二班”的所有学生
select * from student where class_id in (select cid from class where caption = ‘三年二班’);

要求八：查询每个班级的班级名称、班级人数
select caption, count(*)
from
student left join class on student.class_id = class.cid
group by student.class_id;
有一个问题是class表共有三个班，第三个班应该显示0个，却不显示，为什么呀？

要求九：查询成绩小于60分的同学的学号、姓名、成绩、课程名称
select student.sid, sname, number, cname from
score left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
where score.number < 60;

要求十：查询选修了“生物课”的所有学生ID、学生姓名、成绩

select student.sid, sname, number from
score left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
where course.cname = ‘生物’;

要求十一：查询选修了“生物课”且分数低于60的所有学生ID、学生姓名、成绩
select student.sid, sname, number from
score left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
where course.cname = ‘生物’ and score.number < 60;