MySQL（一）创建大学数据库以及单表查询

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)
);



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中，创建出大学数据库和相关数据表

下面是单表查询操作，一共有二十个题目我将我写出来的答案放在上面，有优化或是其他思路的可以在文章下留言，私信我也可

SELECt studentNo,sname,YEAR(NOW()) - YEAR(birthdate) FROM student WHERe YEAR(birthdate) < '2003';


SELECt COUNT(*) '女生人数'
FROM student
WHERe sex = '女';


SELECt teacherno,tname,major FROM teacher;


SELECt teacherno,tname FROM teacher WHERe ISNULL(prof);


SELECt studentno,final FROM score WHERe courseno = 'c05103' and daily > 84;


SELECt sex,AVG(YEAR(NOW()) - YEAR(birthdate)) AS '平均年龄' FROM student GROUP BY sex;

SELECt studentno,count(courseno) 
FROM score
GROUP BY studentno 
HAVINg count(courseno) >= 3;


SELECt studentno, ROUND(avg(daily*0.2 + final*0.8), 1)'总评平均成绩'
FROM score
GROUP BY studentno;


SELECt * FROM student
WHERe birthdate = (SELECt min(birthdate) FROM student WHERe sex = '男');



SELECt sname,birthdate,email FROM student WHERe sname LIKE '王%';


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;

SELECt studentno, sum(final) AS '总成绩' FROM score
WHERe final > 85
GROUP BY studentno
HAVINg COUNT(studentno) > 2
ORDER BY '总成绩'DESC;

SELECt studentno 学号
FROM score
GROUP BY studentno
ORDER BY AVG(final) DESC
LIMIT 3;


SELECt studentno, SUM(final) 期末总成绩, AVG(final) 期末平均成绩
FROM score
GROUP BY studentno
HAVINg SUM(final) > 250;


SELECt max(final), min(final),max(final) - min(final) FROM score
WHERe courseno = 'c05103';

SELECt *
FROM course
WHERe cname LIKE 'UML_%O__';


SELECt COUNT(*) '三个汉字的学生数量'
FROM student
WHERe LENGTH(sname) = 6


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
);

SELECt teacherno, courseno, count(courseno) *2 AS'上课时数'
FROM teach_course
GROUP BY teacherno, courseno;

把部分查询出来的情况给你们看看吧

第十题就先留给你们解决，我过一阵子在多表查询上把答案展示出来（也是这个大学数据库）