【MySQL】经典查找题目及解答

数据准备

建表
建立如下四张表：

添加/删除主键

# 建表时添加
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)

建表完毕，结果如下：

学生表：

课程表：

教师表：

成绩表：

面试题

1. 查询" 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;

2. 查询同时存在课程“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'