SQL面试练习(MySql)

创建测试数据库：

/*如果已经存在此数据库，先删除*/
drop database if exists sqltest  ;
/*创建并设置编码为UTF-8*/
create database sqltest default character set utf8 ;

一、单表查询

id	sno	name	cno	course	score
2	20170101001	张三	1001	语文	86
3	20170101001	张三	1002	数学	56
4	20170101001	张三	1003	英语	48
5	20170101001	张三	1004	化学	90
6	20170101001	张三	1005	物理	57
7	20170101002	李四	1001	语文	90
8	20170101002	李四	1002	数学	68
9	20170101002	李四	1003	英语	87
10	20170101002	李四	1004	化学	68
11	20170101002	李四	1005	物理	45
12	20170101003	王五	1001	语文	86
13	20170101003	王五	1002	数学	60
14	20170101003	王五	1003	英语	48
15	20170101003	王五	1004	化学	90
16	20170101003	王五	1005	物理	70
17	20170101004	赵六	1001	语文	82
18	20170101004	赵六	1002	数学	80
19	20170101004	赵六	1003	英语	30
20	20170101004	赵六	1004	化学	70
21	20170101004	赵六	1005	物理	88

/*切换至测试数据库*/
use sqltest;

/*如果表存在，则删除*/
drop table if exists tb_score  ;

/*创建学生表ST*/
CREATE TABLE `tb_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Sno` varchar(50) not null ,
  `name` varchar(50) DEFAULT NULL,
  `Cno` varchar(50) not null ,
  `course` varchar(50) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

/*插入数据*/
insert into tb_score (sno,name,cno,course,score) value('20170101001','张三','1001','语文','86') ;
insert into tb_score (sno,name,cno,course,score) value('20170101001','张三','1002','数学','56') ;
insert into tb_score (sno,name,cno,course,score) value('20170101001','张三','1003','英语','48') ;
insert into tb_score (sno,name,cno,course,score) value('20170101001','张三','1004','化学','90') ;
insert into tb_score (sno,name,cno,course,score) value('20170101001','张三','1005','物理','57') ;

insert into tb_score (sno,name,cno,course,score) value('20170101002','李四','1001','语文','90') ;
insert into tb_score (sno,name,cno,course,score) value('20170101002','李四','1002','数学','68') ;
insert into tb_score (sno,name,cno,course,score) value('20170101002','李四','1003','英语','87') ;
insert into tb_score (sno,name,cno,course,score) value('20170101002','李四','1004','化学','68') ;
insert into tb_score (sno,name,cno,course,score) value('20170101002','李四','1005','物理','45') ;

insert into tb_score (sno,name,cno,course,score) value('20170101003','王五','1001','语文','86') ;
insert into tb_score (sno,name,cno,course,score) value('20170101003','王五','1002','数学','60') ;
insert into tb_score (sno,name,cno,course,score) value('20170101003','王五','1003','英语','48') ;
insert into tb_score (sno,name,cno,course,score) value('20170101003','王五','1004','化学','90') ;
insert into tb_score (sno,name,cno,course,score) value('20170101003','王五','1005','物理','70') ;

insert into tb_score (sno,name,cno,course,score) value('20170101004','赵六','1001','语文','82') ;
insert into tb_score (sno,name,cno,course,score) value('20170101004','赵六','1002','数学','80') ;
insert into tb_score (sno,name,cno,course,score) value('20170101004','赵六','1003','英语','30') ;
insert into tb_score (sno,name,cno,course,score) value('20170101004','赵六','1004','化学','70') ;
insert into tb_score (sno,name,cno,course,score) value('20170101004','赵六','1005','物理','88') ;

1.1查询不及格科目数大于等于2的学生学号、姓名和不及格科目数量：

/*不加having条件筛选*/
select sno as '学号' , name as '姓名' , count(score) as '不及格科目数' from tb_score where score < 60 group by sno ;

select sno as '学号' , name as '姓名' , count(score) as '不及格科目数' from tb_score where score < 60 group by sno having count(score) > 2 ;

1.2查询不及格科目数大于等于2的学生学号、姓名：

select sno as '学号' , name as '姓名' from tb_score where score < 60 group by sno having count(score) > 2 ;

1.3、查询不及格科目数大于等于2的学生学号、学生姓名、科目号、科目名称和分数，并按学号降序、科目号升序排序：

 中间步骤探索

select * from tb_score where score < 60 group by sno having count(score) >= 2 order by sno desc , cno asc ;

 最终结果

select * from tb_score where score < 60 and sno in (
    select sno from tb_score where score < 60 group by sno having count(score) >= 2 
)
order by sno desc , cno asc ;

二、多表查询

/*选择数据库*/
use sqltest ;

/*删除已有的学生表*/
drop table if exists tb_student ;

/*创建学生表*/
CREATE TABLE `tb_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stuId` varchar(45) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  `clsId` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*插入学生的数据*/
insert into tb_student (stuId,name,clsId) value ('1001','张三','201') ;
insert into tb_student (stuId,name,clsId) value ('1002','李四','201') ;
insert into tb_student (stuId,name,clsId) value ('1003','王五','201') ;
insert into tb_student (stuId,name,clsId) value ('1004','赵六','202') ;
insert into tb_student (stuId,name,clsId) value ('1005','巩发财','202') ;
insert into tb_student (stuId,name,clsId) value ('1006','柏拉图','202') ;
insert into tb_student (stuId,name,clsId) value ('1007','钱老虎','203') ;
insert into tb_student (stuId,name,clsId) value ('1008','杨伟','203') ;
insert into tb_student (stuId,name,clsId) value ('1009','陈海','203') ;

/*删除已有的班级表*/
drop table if exists tb_class ;

/*创建班级表*/
CREATE TABLE `tb_class` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `clsId` varchar(45) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*插入班级的数据*/
insert into tb_class (clsId,name) value ('201','高二(1)班') ;
insert into tb_class (clsId,name) value ('202','高二(2)班') ;
insert into tb_class (clsId,name) value ('203','高二(3)班') ;

/*删除已有的课程表*/
drop table if exists tb_course ;

/*创建课程表*/
CREATE TABLE `tb_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `couId` varchar(45) DEFAULT NULL,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*插入课程数据*/
insert into tb_course (couId,name) value ('A01','语文') ;
insert into tb_course (couId,name) value ('A02','数学') ;
insert into tb_course (couId,name) value ('A03','英语') ;

/*删除已有的分数表*/
drop table if exists tb_score ;

/*创建分数表*/
CREATE TABLE `tb_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stuId` varchar(45) DEFAULT NULL,
  `couId` varchar(45) DEFAULT NULL,
  `score` float DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*插入分数数据*/
insert into tb_score (stuId,couId,score) value ('1001','A01',81) ;
insert into tb_score (stuId,couId,score) value ('1001','A02',73) ;
insert into tb_score (stuId,couId,score) value ('1001','A03',79) ;

insert into tb_score (stuId,couId,score) value ('1002','A01',79) ;
insert into tb_score (stuId,couId,score) value ('1002','A02',83) ;
insert into tb_score (stuId,couId,score) value ('1002','A03',87) ;

insert into tb_score (stuId,couId,score) value ('1003','A01',65) ;
insert into tb_score (stuId,couId,score) value ('1003','A02',97) ;
insert into tb_score (stuId,couId,score) value ('1003','A03',65) ;

insert into tb_score (stuId,couId,score) value ('1004','A01',78) ;
insert into tb_score (stuId,couId,score) value ('1004','A02',86) ;
insert into tb_score (stuId,couId,score) value ('1004','A03',78) ;

insert into tb_score (stuId,couId,score) value ('1005','A01',67) ;
insert into tb_score (stuId,couId,score) value ('1005','A02',89) ;
insert into tb_score (stuId,couId,score) value ('1005','A03',88) ;

insert into tb_score (stuId,couId,score) value ('1006','A01',98) ;
insert into tb_score (stuId,couId,score) value ('1006','A02',90) ;
insert into tb_score (stuId,couId,score) value ('1006','A03',92) ;

insert into tb_score (stuId,couId,score) value ('1007','A01',85) ;
insert into tb_score (stuId,couId,score) value ('1007','A02',78) ;
insert into tb_score (stuId,couId,score) value ('1007','A03',72) ;

insert into tb_score (stuId,couId,score) value ('1008','A01',78) ;
insert into tb_score (stuId,couId,score) value ('1008','A02',85) ;
insert into tb_score (stuId,couId,score) value ('1008','A03',77) ;

insert into tb_score (stuId,couId,score) value ('1009','A01',68) ;
insert into tb_score (stuId,couId,score) value ('1009','A02',91) ;
insert into tb_score (stuId,couId,score) value ('1009','A03',94) ;

学生表	分数表
班级表
课程表

2.1查询各班各科最高分数：

SELECT 
    cls.name AS '班级',
    cou.name AS '课程',
    MAX(temp.score) AS '最高分' 
FROM
    (SELECT 
        st.clsId, sc.couId, sc.score
    FROM
        tb_score AS sc
    INNER JOIN tb_student AS st ON sc.stuId = st.stuId) as temp
INNER JOIN tb_class AS cls ON temp.clsId = cls.clsId
INNER JOIN tb_course AS cou ON temp.couId = cou.couId
GROUP BY temp.clsId , temp.couId