MYSQL——综合练习

目录

一、基础配置

1.建库、建表

2. 插入数据

注：插入数据时可能出现的错误

二、查询选修了全部课程的学生信息

三、查询各学生的年龄(实岁)

四、查询本周过生日的学生

五、检索至少选修两门课程的学生学号

六、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

---

一、基础配置

1.建库、建表

创建数据库

create database mysql_exampleTest;

use mysql_exampleTest;

 创建学生表

 CREATE TABLE Student(
s_id VARCHAR(20),
s_name VARCHAR(20) NOT NULL DEFAULT '',
s_birth VARCHAR(20) NOT NULL DEFAULT '',
s_sex VARCHAR(10) NOT NULL DEFAULT '',
PRIMARY KEY(s_id)
);

 创建课程表

 CREATE TABLE Course(

c_id VARCHAR(20),

c_name VARCHAR(20) NOT NULL DEFAULT '',

t_id VARCHAR(20) NOT NULL,

PRIMARY KEY(c_id)

);

 创建教师表

CREATE TABLE Teacher(

t_id VARCHAR(20),

t_name VARCHAR(20) NOT NULL DEFAULT '',

PRIMARY KEY(t_id)

);

创建成绩表

CREATE TABLE Score(

s_id VARCHAR(20),

c_id VARCHAR(20),

s_Score INT(3),

PRIMARY KEY(s_id,c_id)

);

2. 插入数据

插入学生表测试数据

insert into Student values('01' , '赵雷' , '1990-01-01' , '男');

insert into Student values('02' , '钱电' , '1990-12-21' , '男');

insert into Student values('03' , '孙风' , '1990-05-20' , '男');

insert into Student values('04' , '李云' , '1990-08-06' , '男');

insert into Student values('05' , '周梅' , '1991-12-01' , '女');

insert into Student values('06' , '吴兰' , '1992-03-01' , '女');

insert into Student values('07' , '郑竹' , '1989-07-01' , '女');

insert into Student values('08' , '王菊' , '1990-01-20' , '女');

 select *from Student;

 插入课程表测试数据

insert into Course values('01' , '语文' , '02');

insert into Course values('02' , '数学' , '01');

insert into Course values('03' , '英语' , '03');

 select *from Course;

 插入教师表测试数据

insert into Teacher values('01' , '张三');

insert into Teacher values('02' , '李四');

insert into Teacher values('03' , '王五'); 

 select *from Teacher;

 插入成绩表测试数据

insert into Score values('01' , '01' , 80);

insert into Score values('01' , '02' , 90);

insert into Score values('01' , '03' , 99);

insert into Score values('02' , '01' , 70);

insert into Score values('02' , '02' , 60);

insert into Score values('02' , '03' , 80);

insert into Score values('03' , '01' , 80);

insert into Score values('03' , '02' , 80);

insert into Score values('03' , '03' , 80);

insert into Score values('04' , '01' , 50);

insert into Score values('04' , '02' , 30);

insert into Score values('04' , '03' , 20);

insert into Score values('05' , '01' , 76);

insert into Score values('05' , '02' , 87);

insert into Score values('06' , '01' , 31);

insert into Score values('06' , '03' , 34);

insert into Score values('07' , '02' , 89);

insert into Score values('07' , '03' , 98);

 select *from Score;

注：插入数据时可能出现的错误

 我第一次插入数据时出现了这个错误提示

 检查数据库的默认字符集时，发现使用的是对汉字不兼容的Latin1。Latin1是一个单字节字符集，支持大部分西欧语言，包括英语、德语、法语等，但对包含中文、日文、韩文等在内的多字节字符集支持不足。所以要将修改默认字符集修改为通用性广泛的utf8。

show variables like 'character_set_database' ;

 ALTER DATABASE mysql_exampleTest CHARACTER SET = utf8 COLLATE = utf8_general_ci; ----对MySQL5.7及以下有效

 show variables like 'character_set_database';

 将默认字符集修改为utf-8之后，需要将原来的表删除，再重新创建表，后即可插入数据。

 DROP TABLE IF EXISTS Student,Course,Teacher,Score;

二、查询选修了全部课程的学生信息

select st.*from Student st left join Score s on st.s_id=s.s_id group by st.s_id having count(s.c_id)=(select count(*) from Course);

三、查询各学生的年龄(实岁)

 select st.*,timestampdiff(year,st.s_birth,now()) from Student st;

四、查询本周过生日的学生

select st.* from Student st where week(now())=week(date_format(st.s_birth,'%Y%m%d')); 

五、检索至少选修两门课程的学生学号

select st.s_id from Student st

left join Score sc on sc.s_id=st.s_id

group by st.s_id having count(1)>=2;

六、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

select c.c_id, count(s.s_id)

from Course c left join Score s on c.c_id=s.c_id

group by c.c_id

having count(s.s_id)>5

order by count(s.s_id) desc, c.c_id;