MySQL习题练习

一、基础练习
1、student(学生信息表)

create table student(
	sno bigint primary key not null comment '学号',
    sname varchar(20) not null comment '姓名',
    sex varchar(20) not null comment '性别',
    birthday date not null comment '出生日期',
    class bigint not null comment '所在班级'
)engine=innodb default charset utf8;

insert into student values
(108,'曾华','男','1977-09-01',95033),
(105,'匡明','男','1975-10-02',95031),
(107,'王丽','女','1977-01-23',95033),
(101,'李军','男','1976-02-20',95033),
(109,'王芳','女','1975-02-10',95031),
(103,'陆军','男','1974-06-03',95031);

2、teacher(老师信息表)

create table teacher(
	tno bigint primary key not null comment '教师号',
    tname varchar(20) comment '姓名',
    sex varchar(20) not null comment '性别',
    birthday date not null comment '出生日期',
    prof varchar(20) not null comment '职称',
    depart varchar(32) not null comment '所在部门'
)engine=innodb default charset utf8;

insert into teacher values
(804,'李诚','男','1958-12-02','副教授','计算机系'),
(856,'李旭','男','1969-03-12','讲师','电子工程系'),
(825,'王萍','女','1972-05-05','助教','计算机系'),
(831,'刘冰','女','1977-08-14','助教','电子工程系');

3、course(课程表)

create table course(
	cno varchar(20) primary key not null comment '课程号',
    cname varchar(32) not null comment '课程名',
    tno bigint not null comment '老师号'
)engine=innodb default charset utf8;

insert into course values
('3-105','计算机导论',825),
('3-245','操作系统',804),
('6-166','数字电路',856),
('9-888','高等数学',825);

4、score(成绩表)

create table score(
	sno bigint not null comment '学号',
    cno varchar(20) not null comment '课程号',
    degree int comment '成绩'
)engine=innodb default charset utf8;

insert into score values
(103,'3-245',86),
(109,'3-245',68),
(105,'3-245',75),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'3-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-166',79),
(108,'6-166',81);

5、列出student表中所有记录的sname、sex和class列。

select sname,sex,class from student;

6、显示教师所有的单位即不重复的depart

select distinct depart from teacher;

7、显示学生表的所有记录

select * from student;

8、显示score表中成绩在60到80之间的所有记录

select * from score where degree>60 and degree<80;

9、显示score表中成绩为85,86或88的记录

select * from score where degree=85 or degree=86 or degree=88;

10、显示student表中“95031”班或性别为“女”的同学记录

select * from student where class='95031' or sex='女';

11、以class降序显示student表中的所有记录。

select * from student order by class desc;

12、以cno升序，degree降序显示score表中的所有记录

select * from score order by degree desc,cno asc;

13、显示“98031”班的学生人数

select count(*) from student where class='95031';

14、显示score表中的最高分的学生学号和课程号

select sno,cno from score where degree=(select max(degree) from score);

15、显示“3-105”号课程的平均分

select avg(degree) from score where cno='3-105';

16、显示score表中至少有5名学生选修的并以3开头的课程号的平均数**

select avg(degree) from score where cno like '3%' group by  cno having count(*)>=5;

17、显示最低分大于70，最高分小于90的sno列。

select sno from score group by sno having min(degree)>70 and max(degree)<90;

18、显示所有学生的sname、cno和degree列。

select sname,cno,degree from student join score on student.sno=score.sno;

19、显示所有学生的sname、cname和degree列。

select sname,cname,degree from (score join course on course.cno=score.cno) join student on student.sno=score.sno;

20、列出“95033”班所选课程的平均分

select avg(degree) from score where sno in(select sno from student where class='95033'); 

21、显示选修“3-105”课程的成绩高于“109”号同学的所有同学的记录。

select * from score where degree>(select degree from score where sno='109' and cno='3-105') and cno='3-105';

22、显示score中选修多门课程的同学分数为非最高分成绩的记录。

select sno from score group by sno having count(*)>1); -- 选修了多门课程
select max(degree) from score; -- 有最高分
select cno from score where sno in(select sno from score group by sno having count(*)>1 ) and degree not in(select max(degree) from score where sno in(select sno from score group by sno having count(*)>1));

23、显示成绩高于“109”、课程号为“3-105”的成绩的所有记录。

select * from score where degree>(select degree from score where sno='109' and cno='3-105') and cno='3-105';

24、显示出和学号为“108”的同学同年出生的所有学生的sno、Sname、和birthday列。

select year(birthday) from student where sno='108'; -- 获取108学生的出生年份
select sno,sname,birthday from student where year(birthday)=(select year(birthday) from student where sno='108');

25、显示“李旭”老师任课的学生的成绩。

select tno from teacher where tname='李旭'; -- 获取李旭老师的编号
select cno from course where tno=(select tno from teacher where tname='李旭'); -- 获取李旭老师带的课
select degree from score where cno=(select cno from course where tno=(select tno from teacher where tname='李旭'));

26、显示选修某课程的同学人数多余5人的老师姓名。

select cno from score group by cno having count(*)>=5; -- 获取选修课程大于等于5人的课程号
select tno from course where cno=(select cno from score group by cno having count(*)>=5);  
select tname from teacher where tno=(select tno from course where cno=(select cno from score group by cno having count(*)>=5));

27、显示“95033”班和“95031”班全体学生的记录。

select * from student where class='95033' or class='95031';

28、显示存在有85分以上成绩的课程cno。

select distinct cno from score where degree>85;

29、显示“计算机系”老师所教课程的成绩表。

select tno from teacher where depart='计算机系';
select cno from course where tno in(select tno from teacher where depart='计算机系');
select * from score where cno in(select cno from course where tno in(select tno from teacher where depart='计算机系'));

30、显示“计算机系”和“电子工程系”不同职称的老师的tname和prof。题解

select tname,prof from teacher where prof in(select prof from teacher where depart in('计算机系','电子工程系') group by prof having count(distinct depart)=1);

31、显示选修编号为“3-105”课程且成绩至少高于“3-245”课程的同学的cno、sno和degree，并按degree从
高到低次序排列。

select max(degree) from score where cno='3-245';
select cno,sno,degree from score where cno='3-105' and degree>(select min(degree) from score where cno='3-245') order by degree desc;

32、显示选修编号为“3-105”课程且成绩高于“3-245”课程的同学的cno、sno和degree。

select cno,sno,degree from score where cno='3-105' and degree>(select max(degree) from score where cno='3-245');

33、列出所有任课老师的tname和depart。

select tname,depart from teacher;

34、列出所有未讲课老师的tname和depart。

select tname,depart from teacher where tno not in(select tno from course);

35、列出所有老师和同学的姓名、性别和生日。

select tname,sex,birthday from teacher union select sname,sex,birthday from student; 

36、检索所学课程包含学生“103”所学课程的学生学号。

select sno from score where cno in(select distinct cno from score where sno='103');

37、检索选修所有课程的学生姓名。

select sname from student where sno in( select sno from score group by sno having count(*)>(select count(*) from course));

二、
1、建立三个表 员工表（员工号[主键]、姓名、性别、出生日期）； 销售表（流水号、产品编号、销售单价、销售时间、销售数量）； 产品表（编号[由字母和数字合成]、产品单价）。

create table employe(
	Eno int primary key auto_increment comment '员工号',
	Ename varchar(20) default null comment '姓名',
	Sex boolean default 1 comment '性别',
	Birth date default null comment '出生日期'
)engine=innodb default charset utf8;
    
create table sales(
	Sid int not null comment '流水号',
	id int not null comment '产品编号',
	price numeric not null comment '销售价格',
	salestime date not null comment '销售时间',
	count int not null comment '销售数量'
)engine=innodb default charset utf8;
    
create table production(
	id varchar(20) comment '产品编号',
	price numeric not null comment '产品单价'
)engine=innodb default charset utf8;

2、 修改员工表，用姓名字段建立一个索引。

create index name_index on employe(Ename);

3、 修改员工表将姓名列改为非空列。

alter table employe modify Ename varchar(20) not null;

4、修改员工表为姓名列创建一个唯一性约束。

alter table employe add constraint Ename_unique unique(Ename);

5、 为员工表添加一条约束，要求是所有员工必须是1983以前出生的，并且该字段不能为空，而且输入 格式必须为年-月-日。

alter table employe add constraint check_Birth check(Birth<1983-01-01);

6、用Insert语句测试一下以上的修改是否成功。

insert into employe values(1001,'张三',1,'1980-04-22');

7、用函数将当天的日期和时间显示出来；显示本月的最后一天。

select now();   select last_day(curdate());

8、向产品表中按要求加入几条记录。

insert into production values('小米1-s',2000.00);
insert into production values('小米2-s',2100.01);
insert into production values('小米3-s',2200.02);
insert into production values('小米4-s',2300.03);
insert into production values('小米5-s',2400.04);
insert into production values('小米6-s',2500.05);

9、将产品表中的产品名称的前三个字母用函数取出来。

select left(id,3) from production;

10、用函数找出产品表中产品名超过15的所有产品信息。

select * from production where length(id)>15;

11、向销售表插入两条当天的销售记录，两条4天以前的销售记录，（全部用日期函数插入）。

insert into sales values(1,12301,1999.9,now(),5);
insert into sales values(2,12302,2999.9,now(),6);
insert into sales values(4,12304,2888.8,'2022-04-25',2);
insert into sales values(5,12305,3888,'2022-04-24',10);

12、将销售表中的同一天的销售额算出来，（保留小数点后2位，四舍五入）用函数完成。

select sum(price * count) from sales;

13、将各种产品的单价以整数形式显示出来（不要求四舍五入）。

select price from production;

14、 下一个周五的日期

select date_sub(curdate(),interval weekday(curdate()) - 11 day);

15、两个月前的今天的日期

select date_add(now(),interval-2 month);