SQL面试题

最新推荐文章于 2021-03-19 22:57:19 发布

转载最新推荐文章于 2021-03-19 22:57:19 发布 · 464 阅读

数据库专栏收录该内容

11 篇文章

订阅专栏

本文通过具体实例介绍了SQL在数据库查询中的多种实用技巧，包括条件筛选、分组聚合、联表查询等，帮助读者掌握高效的数据检索方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文参考自http://blog.youkuaiyun.com/lifetragedy/article/details/9935699，做了些修改

准备四张表及数据：

表student 学生
表course 课程
表student_course（学生与课程的分数mapping 表）
表teacher 教师

/*
SQLyog Trial v11.01 (32 bit)
MySQL - 5.1.45-community : Database - newtest
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`newtest` /*!40100 DEFAULT CHARACTER SET latin1 */;

USE `newtest`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `course_no` int(11) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(30) DEFAULT NULL,
  `teacher_no` int(11) NOT NULL,
  PRIMARY KEY (`course_no`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Data for the table `course` */

insert  into `course`(`course_no`,`course_name`,`teacher_no`) values (1,'Math',1),(2,'English',1),(3,'Chinese',1);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `student_no` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(30) DEFAULT NULL,
  `student_age` int(11) DEFAULT NULL,
  `student_sex` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`student_no`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `student` */

insert  into `student`(`student_no`,`student_name`,`student_age`,`student_sex`) values (1,'Jim',16,'Male'),(2,'Tom',15,'Male'),(3,'Kity',16,'Female'),(4,'Jerry',15,'Male'),(5,'Cathy',15,'Female'),(6,'Mongo',16,'Male');

/*Table structure for table `student_course` */

DROP TABLE IF EXISTS `student_course`;

CREATE TABLE `student_course` (
  `student_no` int(11) NOT NULL,
  `course_no` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  UNIQUE KEY `s_c_unique` (`student_no`,`course_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `student_course` */

insert  into `student_course`(`student_no`,`course_no`,`score`) values (1,1,71),(1,2,86),(2,1,91),(2,2,81),(3,1,31),(5,2,56),(4,2,59),(2,3,88),(4,3,36),(3,3,66);

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `teacher_no` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`teacher_no`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

/*Data for the table `teacher` */

insert  into `teacher`(`teacher_no`,`teacher_name`) values (1,'Li Lei'),(2,'Li Ming'),(3,'Deng Gong'),(4,'Wu Feng'),(5,'Li Zi'),(6,'Li Ming');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- 1、查询编号“1”课程比“2”课程成绩高的所有学生的学号；

SELECT sc.student_no FROM student_course sc 
INNER JOIN student_course sc2
	ON sc2.student_no = sc.student_no
WHERE sc.course_no = 1 AND sc2.course_no = 2 AND sc.score > sc2.score


SELECT a.student_no FROM (SELECT student_no,score FROM student_course WHERE course_no=1) a, (SELECT student_no,score
  FROM student_course WHERE course_no=2) b
WHERE a.score > b.score AND a.student_no = b.student_no


-- 2、查询平均成绩大于60分的同学的学号和平均成绩；
-- 利用HAVING
SELECT student_no, AVG(score) avgScore FROM student_course
GROUP BY student_no HAVING AVG(score) > 60

-- 利用子句
SELECT a.student_no, a.avgScore FROM (
	SELECT student_no, AVG(score) avgScore FROM student_course
	GROUP BY student_no
) a WHERE a.avgScore > 60

--  3、查询所有同学的学号、姓名、选课数、总成绩
SELECT s.student_no, s.student_name, COUNT(sc.course_no) course_count, SUM(sc.score) total_score
FROM student s LEFT OUTER JOIN student_course sc 
ON s.student_no = sc.student_no GROUP BY s.student_no 

--  4、查询名字不重复，姓“Li”的老师的个数
SELECT COUNT(DISTINCT(teacher_name)) FROM teacher WHERE teacher_name LIKE 'Li%'

--  5、查询没学过“Li Lei”老师课的同学的学号、姓名
SELECT s.student_no, s.student_name FROM student s
WHERE s.student_no NOT IN (
	SELECT DISTINCT(s.student_no) FROM student s
	LEFT JOIN student_course sc ON s.student_no = sc.student_no
	LEFT JOIN course c ON sc.course_no = c.course_no
	LEFT JOIN teacher t ON c.teacher_no = t.teacher_no
	WHERE t.teacher_name = 'Li Lei'
)

SELECT s.student_no, s.student_name FROM student s
WHERE s.student_no NOT IN (
	SELECT DISTINCT(s.student_no) FROM student s, student_course sc, course c, teacher t 
	WHERE s.student_no = sc.student_no 
	AND sc.course_no = c.course_no
	AND c.teacher_no = t.teacher_no
	AND t.teacher_name = 'Li Lei'
)

--  6、查询学过编号“1”并且也学过“2”课程的同学的学号、姓名
SELECT s.student_no, s.student_name, COUNT(s.student_no) FROM student s, student_course sc
WHERE s.student_no = sc.student_no 
AND sc.course_no IN (1, 2) GROUP BY s.student_no HAVING COUNT(s.student_no) = 2

SELECT s.student_no, s.student_name FROM student s, student_course sc 
WHERE s.student_no = sc.student_no
AND sc.course_no = 1 
AND EXISTS( SELECT * FROM student_course sc2 WHERE sc2.student_no = sc.student_no AND sc2.course_no = 2);

--  7、查询学过“Li Lei”老师所教的所有课的同学的学号、姓名
SELECT s.student_no, s.student_name FROM student s, student_course sc 
WHERE s.student_no = sc.student_no
AND sc.course_no IN 
(
	SELECT c.course_no FROM course c, teacher t 
	WHERE c.teacher_no = t.teacher_no
	AND t.teacher_name = 'Li Lei'
)
 GROUP BY s.student_no HAVING COUNT(sc.course_no) 
 = (
 SELECT COUNT(c2.course_no) FROM course c2, teacher t2 
	WHERE c2.teacher_no = t2.teacher_no
	AND t2.teacher_name = 'Li Lei'
    )

--  8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
SELECT s.student_no, s.student_name FROM student s, student_course sc
WHERE s.student_no = sc.student_no AND sc.course_no = 1
AND EXISTS (SELECT * FROM student_course sc2 WHERE sc2.student_no = s.student_no AND sc2.course_no = 2 AND sc.score > sc2.score);

 SELECT student_no,student_name FROM (SELECT s.student_no, s.student_name,sc.score ,
 (SELECT sc2.score FROM student_course sc2 WHERE sc2.student_no = s.student_no AND sc2.course_no = 2) score2
  FROM student s,student_course sc WHERE s.student_no = sc.student_no AND sc.course_no = 1) s2 WHERE score2 < score;


--  9、查询所有课程成绩小于60分的同学的学号、姓名
SELECT s.student_no, s.student_name 
FROM student s
WHERE s.student_no NOT IN 
(SELECT s2.student_no FROM student s2, student_course sc WHERE s2.student_no = sc.student_no AND sc.score >= 60)


SELECT s.student_no, s.student_name 
FROM student s INNER JOIN student_course sc
ON s.student_no = sc.student_no
WHERE NOT EXISTS (SELECT * FROM student_course sc2 WHERE s.student_no = sc2.student_no AND sc2.score >= 60)
GROUP BY sc.student_no

--  10、查询没有学全所有课的同学的学号、姓名
SELECT s.student_no, s.student_name 
FROM student s LEFT JOIN student_course sc
ON s.student_no = sc.student_no
GROUP BY sc.student_no HAVING COUNT(sc.course_no) < (SELECT COUNT(*) FROM course)
ORDER BY s.student_no

--  11、查询至少有一门课与学号为“3”的同学所学相同的同学的学号和姓名；
SELECT DISTINCT s.student_no, s.student_name 
FROM student s
INNER JOIN student_course sc
ON s.student_no = sc.student_no
AND s.student_no != 3
AND sc.course_no IN 
(SELECT sc2.course_no FROM student_course sc2 WHERE sc2.student_no = 3)

-- 存储过程
-- 不带参数
DELIMITER &&  
CREATE PROCEDURE query_test()
BEGIN
 SELECT * FROM student_course sc WHERE sc.course_no = 1;
END &&
DELIMITER ;

CALL query_test();

-- 带输入输出参数
DROP PROCEDURE query_avg_score;
DELIMITER &&
CREATE PROCEDURE query_avg_score(IN s_id INT, OUT avg_score DOUBLE)
BEGIN
  SELECT AVG(score) INTO avg_score FROM student_course 
  WHERE student_no = s_id;
END &&
DELIMITER ;
  
CALL query_avg_score(1, @avg_score);
SELECT @avg_score;

-- 定义局部变量并使用游标
DROP PROCEDURE test3;
DELIMITER &&
CREATE PROCEDURE test3(OUT rs INT)
BEGIN 
 DECLARE o INT;  -- 声明一个局部变量 
 DECLARE cursorTest CURSOR
 FOR
 SELECT course_no FROM course;
 OPEN cursorTest;  -- 打开游标  
 FETCH cursorTest INTO o;
 SELECT o INTO rs;
 CLOSE cursorTest;  -- 关闭游标
END &&
DELIMITER ;

CALL test3(@rs);
SELECT @rs;