/*创建大学数据库*/
CREATE DATABASE college default character set utf8 collate utf8_general_ci;
/*使用大学数据库*/
use college;
/*学生表*/
CREATE table student(
studentNo char(12) comment '学号',
sname char(8) comment '姓名',
sex char(2) comment '性别',
birthdate date comment '出生日期',
entrance int comment '入学成绩',
phone char(11) comment '电话号码',
email VARCHAR(20) comment '邮件号码',
departmentname varchar(20) comment '所在系名'
);
/*课程表*/
CREATE table course(
courseno char(6) comment '课程号',
cname varchar(20) comment '课程名',
type char(8) not null comment '课程类型',
cpno char(6) comment '先行课程',
period int(2) not null comment '总学时',
exp int(2) not null comment '实验学时',
term int(2) not null comment '开课学期',
primary key(courseno)
);
/*成绩表*/
CREATE table score(
studentno char(12) comment '学号',
courseno char(6) comment '课程号',
daily float(3,1) default 0 comment '平时成绩',
final float(3,1) default 0 comment '期末成绩',
primary key(studentno,courseno)
)
/*教师表*/
CREATE TABLE teacher(
teacherno char(6) not null comment'教师号',
tname char(8) not null comment '教师姓名',
major char(10) comment '专业',
prof char(10) comment '职称',
department char(16) comment '部门',
primary key(teacherno)
);
/*teach_course表*/
CREATE TABLE teach_course
(teacherno char(6) not null,
courseno char(6) not null,
teachtime datetime,
primary key(teacherno,courseno,teachtime)
);
/*学生表数据插入*/
INSERT into student values
('18122221324','何白露','女','2000/12/4',879,'13786112345','heyy@sina.com','机电院'),
('18125111109','敬横江','男','2000/3/1',789,'15678945623','jing@sina.com','计数院'),
('18125121107','王伟','男','1999/9/12',790,'13786188347','3267888@QQ.com','计数院'),
('18137156732','吴英','女','2000/10/19',888,'13055568618','17865@QQ.com','经管院');
/*课程表数据插入*/
insert into course VALUES
('c05103','电子技术','选修',null,48,16,2),
('c05107','程序设计基础','必修',null,48,16,4),
('c05109','C语言','必修','c05108',48,16,4),
('c05127','Java语言','必修','c05109',64,16,4),
('c05138','软件工程','必修','c05109',48,8,5),
('c05124','经济学','必修',null,64,16,4),
('c05108','数据库原理','必修','c05107',48,16,3),
('c05222','会计实务','必修',null,48,8,2),
('c05223','UML_SOFT','选修','c05224',48,8,2),
('c05224','UML设计','选修','c05138',64,8,2);
/*成绩表数据插入*/
insert into score VALUES
('18122221324','c05103',87.0,92.0),
('18122221324','c05109',85.0,90.0),
('18122221324','c05127',95.0,93.0),
('18125111109','c05127',91.0,88.0),
('18125111109','c05138',80.0,80.0),
('18125111109','c05108',76.0,78.0),
('18125121107','c05103',88.0,72.0),
('18125121107','c05127',81.0,76.0),
('18125121107','c05138',75.0,74.0),
('18137156732','c05124',89.0,88.0),
('18137156732','c05222',81.0,79.0);
/*教师表数据插入*/
insert into teacher values
('t05001','苏超然','软件工程','教授','计数院'),
('t05002','常斌','会计学','助教','经管院'),
('t05003','孙石安','网络安全','教授','计数院'),
('t05011','卢先','软件工程','副教授','计数院'),
('t05013','李明','机械制造','讲师','机电院'),
('t05014','王石','软件工程',NULL,'计数院');
/*教师授课表数据插入*/
insert into teach_course values
('t05001','c05109','2021-03-08 10:00:00'),
('t05001','c05127','2021-03-09 8:00:00'),
('t05001','c05109','2021-03-10 14:00:00'),
('t05003','c05124','2021-03-11 16:00:00'),
('t05011','c05127','2021-03-13 16:00:00'),
('t05011','c05127','2021-03-15 10:00:00');
把上面这些代码全复制在navicat中，创建出大学数据库和相关数据表
下面是单表查询操作，一共有二十个题目我将我写出来的答案放在上面，有优化或是其他思路的可以在文章下留言，私信我也可
/*1、在student表中查询出生日期在2003年以前的学生的学号、姓名和年龄*/
SELECT studentNo,sname,YEAR(NOW()) - YEAR(birthdate) FROM student WHERE YEAR(birthdate) < '2003';
/*2、查询student表中女生的人数，以别名“女生人数”命名*/
SELECT COUNT(*) '女生人数'
FROM student
WHERE sex = '女';
/*3、查询teacher表中每一位教授的教师号，姓名和专业名称*/
SELECT teacherno,tname,major FROM teacher;
/*4、查询teacher表中没有职称的教师的教工号，姓名*/
SELECT teacherno,tname FROM teacher WHERE ISNULL(prof);
/*5、查询选修课程号为c05103的学生的学号和期末成绩，并要求平时成绩在85到100分之间*/
SELECT studentno,final FROM score WHERE courseno = 'c05103' and daily > 84;
/*6、按性别统计student表中学生的平均年龄，并输出各性别下学生的平均年龄的值（注意以平均年龄来命名）*/
SELECT sex,AVG(YEAR(NOW()) - YEAR(birthdate)) AS '平均年龄' FROM student GROUP BY sex;
/*7、统计选课门数超过3门以上的学生的学号及其选课门数*/
SELECT studentno,count(courseno)
FROM score
GROUP BY studentno
HAVING count(courseno) >= 3;
/*8、统计每个学生所选课程总评成绩的平均分，其中总评成绩=daily*0.2+final*0.8，平均分保留小数位1位*/
SELECT studentno, ROUND(avg(daily*0.2 + final*0.8), 1)'总评平均成绩'
FROM score
GROUP BY studentno;
/*9、输出student表中年龄最大的男生的所有信息（嵌套循环）*/
SELECT * FROM student
WHERE birthdate = (SELECT min(birthdate) FROM student WHERE sex = '男');
/*10、创建新表stu_new，该新表中包含学号、课程号和总评成绩。其中总评成绩=daily*0.2+final*0.8,查看该新表的结构*/
/*11、显示所有姓王学生的姓名、出生日期、EMAIL*/
SELECT sname,birthdate,email FROM student WHERE sname LIKE '王%';
/*12、在score表中查询总评成绩大于85分的学生的学号、课程号和总评成绩，并按照课程号的升序，再按照总评成绩的降序排列*/
SELECT studentno, courseno, (daily*0.2 + final*0.8)AS'总评成绩' FROM score
WHERE (daily*0.2 + final*0.8) > 85
ORDER BY courseno ASC, '总评成绩' DESC;
/*13、查询选课在2门以上且各门课程期末成绩均高于85分的学生的学号及其总成绩，查询结果按照总成绩降序列出*/
SELECT studentno, sum(final) AS '总成绩' FROM score
WHERE final > 85
GROUP BY studentno/*为学生分组*/
HAVING COUNT(studentno) > 2
ORDER BY '总成绩'DESC;
/*14、查询所选课程期末平均成绩最高的前三个学生的学号,并按期末平均成绩降序输出*/
SELECT studentno 学号
FROM score
GROUP BY studentno
ORDER BY AVG(final) DESC
LIMIT 3;
/*15、查询score表中学生的期末总成绩大于250分的学生的学号、期末总成绩和期末平均成绩*/
SELECT studentno, SUM(final) 期末总成绩, AVG(final) 期末平均成绩
FROM score
GROUP BY studentno
HAVING SUM(final) > 250;
/*16、查询选修课程号为c05103的学生的期末最高分、最低分及之间相差的分数*/
SELECT max(final), min(final),max(final) - min(final) FROM score
WHERE courseno = 'c05103';
/*17、查询以"UML_"开头，且倒数第3个字符为O的课程的详细情况。*/
SELECT *
FROM course
WHERE cname LIKE 'UML_%O__';
/*18、统计姓名中含有三个汉字的学生的数量（一个汉字相当于两个英文字节）*/
/*
SELECT sname, COUNT(*) '三个汉字的学生数量'
FROM student
WHERE LENGTH(sname) = 6
GROUP BY sname;
*/
SELECT COUNT(*) '三个汉字的学生数量'
FROM student
WHERE LENGTH(sname) = 6
/*19、查询近七日内同一个老师上同一门课程超过两次的所有的老师的授课信息。*/
SELECT * FROM teach_course
WHERE teacherno IN (
SELECT teacherno
FROM teach_course
GROUP BY teacherno, courseno
HAVING DATEDIFF(CURDATE(), teachtime)<7 AND COUNT(courseno)>= 2
);
/*20、查询每个老师所上的课程的总课时，要求输出教师号，课程号以及以上课时数（一次课是2个课时）*/
SELECT teacherno, courseno, count(courseno) *2 AS'上课时数'
FROM teach_course
GROUP BY teacherno, courseno;
把部分查询出来的情况给你们看看吧
第十题就先留给你们解决，我过一阵子在多表查询上把答案展示出来（也是这个大学数据库）
