文章目录

数据准备面试题参考链接

数据准备

建表 建立如下四张表：

添加/删除主键

# 建表时添加 create table tableName( id int primary key ); 或： create table tableName( id int, primary key (id) ); # 单独添加 alter table tableName add primary key(id) # 删除主键 alter table tableName drop primary key;

1、学生表

# 建表语句 CREATE TABLE Student ( SID VARCHAR (10), Sname nvarchar (10), Sage datetime, Ssex nvarchar (10) ) #注：char，varchar，nvarchar都用来存储字符串，方式不同。 #频繁改变的字段应该用char，因为每次修改varchar都会重新计算长度，而这些char不用。 #大数据量（多行）提取时varchar的磁盘I/O消耗更低，意味着varchar在综合查询性能上比char更好。 #建议纯英文和数字用char/varchar，有中文使用nvarchar。 # 插入测试数据 INSERT INTO Student VALUES('01' , N'赵雷' , '1990-01-01' , N'男') INSERT INTO Student VALUES('02' , N'钱电' , '1990-12-21' , N'男') INSERT INTO Student VALUES('03' , N'孙风' , '1990-05-20' , N'男') INSERT INTO Student VALUES('04' , N'李云' , '1990-08-06' , N'男') INSERT INTO Student VALUES('05' , N'周梅' , '1991-12-01' , N'女') INSERT INTO Student VALUES('06' , N'吴兰' , '1992-03-01' , N'女') INSERT INTO Student VALUES('07' , N'郑竹' , '1989-07-01' , N'女') INSERT INTO Student VALUES('08' , N'王菊' , '1990-01-20' , N'女')

2、课程表

# 建表语句 CREATE TABLE Course ( CID VARCHAR (10), Cname nvarchar (10), TID VARCHAR (10) ) # 插入测试数据 INSERT INTO Course VALUES('01' , N'语文' , '02') INSERT INTO Course VALUES('02' , N'数学' , '01') INSERT INTO Course VALUES('03' , N'英语' , '03')

3、教师表

# 建表语句 CREATE TABLE Teacher ( TID VARCHAR (10), Tname nvarchar (10) ) # 插入测试数据 INSERT INTO Teacher VALUES('01' , N'张三') INSERT INTO Teacher VALUES('02' , N'李四') INSERT INTO Teacher VALUES('03' , N'王五')

4、成绩表

# 建表语句 CREATE TABLE SC ( SID VARCHAR (10), CID VARCHAR (10), score DECIMAL (18, 1) ) #注：DECIMAL数据类型用于在数据库中存储精确的数值。语法：DECIMAL(P, D) #P是表示有效数字数的精度。P范围为1〜65，默认是10。D是表示小数点后的位数。D的范围是0~30。 #MySQL要求D小于或等于(<=)P。 #eg. amount DECIMAL(6,2); #在此示例中，amount列最多可以存储6位数字，小数位数为2位; 因此，amount列的范围是从-9999.99到9999.99。 # 插入测试数据 INSERT INTO SC VALUES('01' , '01' , 80) INSERT INTO SC VALUES('01' , '02' , 90) INSERT INTO SC VALUES('01' , '03' , 99) INSERT INTO SC VALUES('02' , '01' , 70) INSERT INTO SC VALUES('02' , '02' , 60) INSERT INTO SC VALUES('02' , '03' , 80) INSERT INTO SC VALUES('03' , '01' , 80) INSERT INTO SC VALUES('03' , '02' , 80) INSERT INTO SC VALUES('03' , '03' , 80) INSERT INTO SC VALUES('04' , '01' , 50) INSERT INTO SC VALUES('04' , '02' , 30) INSERT INTO SC VALUES('04' , '03' , 20) INSERT INTO SC VALUES('05' , '01' , 76) INSERT INTO SC VALUES('05' , '02' , 87) INSERT INTO SC VALUES('06' , '01' , 31) INSERT INTO SC VALUES('06' , '03' , 34) INSERT INTO SC VALUES('07' , '02' , 89) INSERT INTO SC VALUES('07' , '03' , 98)

建表完毕，结果如下： 学生表： 课程表： 教师表： 成绩表：

面试题

查询" 01 “课程比” 02"课程成绩高的学生的信息及课程分数 # 法1 SELECT a.*, b.score FROM student a JOIN sc b ON b.Sid = a.Sid JOIN sc c ON c.Sid = a.Sid WHERE b.Cid = '01' AND c.Cid = '02' AND b.score > c.score; # 法2 SELECT a.*, b.score FROM student a JOIN (SELECT * FROM sc WHERE Cid = '01') b ON b.Sid = a.Sid JOIN (SELECT * FROM sc WHERE Cid = '02') c ON c.Sid = a.Sid WHERE b.score > c.score; 查询同时存在课程“01”和课程“02”的学生的信息 # 法1 mysql> SELECT a.* -> FROM student a -> JOIN sc b ON b.Sid = a.Sid -> JOIN sc c ON c.Sid = a.Sid -> WHERE b.Cid = '01' -> AND c.Cid = '02'; # 法2 mysql> select a.* -> from student a -> join (select * from sc where Cid = '01') b on b.Sid = a.Sid -> join (select * from sc where Cid = '02') c on c.Sid = a.Sid; 查询存在" 01 "课程但可能不存在"02 "课程的学生成绩情况(不存在时显示为 null ) mysql> select * -> from -> (select * from sc where Cid = '01') a -> left join -> (select * from sc where Cid = '02') b -> on a.Sid = b.Sid; 查询不存在" 01 “课程但存在” 02 "课程的学生成绩情况 mysql> select * -> from sc -> where Cid = '02' -> and Sid not in -> (select Sid from sc where Cid = '01'); 查询平均成绩大于等于 60 分的同学的学生编号，学生姓名和平均成绩 mysql> select -> a.Sid, b.Sname, a.dc -> from( -> select Sid, avg(score) dc -> from sc group by Sid -> having avg(score)>=60 -> ) a -> join student b on a.Sid = b.Sid; 查询在 SC 表存在成绩的学生信息 mysql> select * -> from student -> where -> Sid in (select distinct Sid from sc); 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null ) mysql> select a.Sid, a.Sname, b.Cnt, b.total -> from student a -> left join( -> select Sid, count(Cid) Cnt, sum(score) total -> from sc -> group by Sid -> ) b on a.Sid = b.Sid; 查询有成绩的学生的信息，包括学生编号、学生姓名、选课总数、所有课程的总成绩 mysql> select a.Sid, a.Sname, b.Cnt, b.total -> from student a -> right join( -> select Sid, count(Cid) Cnt, sum(score) total -> from sc -> group by Sid -> ) b on a.Sid = b.Sid; 查询「李」姓老师的数量 mysql> select count(*) -> from teacher -> where Tname like '李%'; 查询学过「张三」老师授课的同学的信息 mysql> select * from student -> where Sid in( -> select distinct Sid from sc a -> join course b on a.Cid = b.Cid -> join teacher c on b.Tid = c.Tid -> where c.Tname = '张三'); 查询没有学全所有课程的同学的信息 mysql> select * from student -> where Sid in( -> select Sid from sc -> group by Sid -> having count(Cid)<3 -> ); 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 mysql> select * from student -> where Sid in ( -> select distinct Sid from sc -> where Cid in -> (select Cid from sc where Sid = '01') -> ); 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 mysql> select * from student -> where Sid in ( -> select Sid from sc where Cid in -> (select distinct Cid from sc where Sid = '01') -> and Sid <> '01' -> group by Sid -> having count(Cid)=3); 查询没学过"张三"老师讲授的任一门课程的学生姓名 mysql> select Sname from student -> where Sid not in( -> select distinct Sid from sc a -> join course b on a.Cid = b.Cid -> join teacher c on b.Tid = c.Tid -> where c.Tname = '张三'); 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 mysql> select a.Sid, a.Sname, b.平均成绩 -> from student a -> right join( -> select Sid, avg(score) 平均成绩 from sc -> where score<60 -> group by Sid -> having count(score)>=2 -> ) b on a.Sid = b.Sid; 检索" 01 "课程分数小于 60，按分数降序排列的学生信息 mysql> select a.*, b.score from student a -> join sc b on a.Sid = b.Sid -> where Cid = '01' and score < 60 -> order by score desc; 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩 mysql> select Sid, -> max(case Cid when '01' then score else 0 end) '01', -> max(case Cid when '02' then score else 0 end) '02', -> max(case Cid when '03' then score else 0 end) '03', -> avg(score) 平均分 from sc -> group by Sid order by 平均分 desc; 查询每门课程被选修的学生数 mysql> select Cid, count(Sid) 学生数 from sc group by Cid; 查询出只选修两门课程的学生学号和姓名 mysql> select Sid, Sname from student -> where Sid in ( -> select Sid from -> (select Sid, count(Cid) 课程数 from sc group by Sid) a -> where a.课程数 = 2); 查询男生、女生人数 mysql> select Ssex, count(Ssex) 人数 -> from student group by Ssex; 查询名字中含有「风」字的学生信息 mysql> select * from student -> where Sname like '%风%'; 查询同名同性学生名单，并统计这些人数 mysql> select a.*, b.人数 -> from student a -> left join ( -> select Sname, Ssex, count(*) 人数 -> from student group by Sname, Ssex -> ) b on a.Sname = b.Sname and a.Ssex = b.Ssex -> where b.人数 > 1; 查询 1990 年出生的学生名单 mysql> select * from student where year(Sage)=1990; 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列 mysql> select Cid, avg(score) 平均成绩 from sc -> group by Cid order by 平均成绩 desc, Cid; 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 mysql> select a.Sid, a.Sname, b.平均成绩 from student a -> left join ( -> select Sid, avg(score) 平均成绩 -> from sc group by Sid -> ) b on a.Sid = b.Sid -> where b.平均成绩 > 85; 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 mysql> select a.Sname, b.score from ( -> select * from sc where score < 60 and Cid = -> (select Cid from course where Cname = '数学') -> ) b -> left join student a on a.Sid = b.Sid; 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况） mysql> select a.Sid, b.Cid, b.score from student a -> left join sc b on a.Sid = b.Sid; 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数 mysql> select a.Sname, b.Cname, b.score from ( -> select c.*, d.Cname from -> (select * from sc where score > 70) c -> left join course d on c.Cid = d.Cid ) b -> left join student a on b.Sid = a.Sid; 查询不及格的课程学生姓名，课程名及分数 mysql> select a.Sname, b.Cname, c.score from sc c -> join course b on c.Cid = b.Cid -> join student a on c.Sid = a.Sid -> where c.score < 60; 查询课程编号为01且课程成绩在80分以上的学生的学号和姓名 mysql> select a.Sid, b.Sname from ( -> select * from sc where score > 80 and Cid = '01' -> ) a left join student b on a.Sid = b.Sid; 求每门课程的学生人数（假设每个学生都有参加考试且有成绩） mysql> select Cid, count(*) 学生人数 -> from sc group by Cid; 统计每门课程的学生选修人数（超过5人的课程才统计），要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 mysql> select Cid, count(Sid) 选修人数 -> from sc -> group by Cid -> having count(Sid)>5 -> order by 选修人数 desc, Cid; 检索至少选修两门课程的学生学号 mysql> select Sid from sc -> group by Sid -> having count(Cid)>=2; 查询选修了全部课程的学生信息 mysql> select Sid from sc -> group by Sid -> having count(Cid) = -> (select distinct count(1) a from course);

参考链接

MySQL decimal类型 mysql中char、varchar、nvarchar区别 MySQL添加/删除主键、外键、唯一键、索引、自增 MySQL 连接的使用