表结构关系图

1.查询计算机系学生的学号和姓名。

SELECT sNo,sName 
FROM student 
WHERE Sdept='CS'

2.查询选修了数学的学生学号、姓名和成绩。

SELECT student.sNo,student.sName,sc.grade 
FROM student,sc,course 
WHERE student.sNo = sc.sno 
AND sc.cNo = course.Cno 
AND course.Cname = '数学'

3.查询选修1课程的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号升序排列。

SELECT student.sNo,sc.grade
FROM student,sc
WHERE sc.cno = 1 
AND student.sNo = sc.sno
ORDER BY sc.grade DESC,student.sNo ASC

4.查询计算机系的姓刘且单名的学生的信息。

SELECT * 
FROM student 
WHERE Sdept='CS' 
AND  student.sName LIKE '刘_'

5.查询计算机系的姓刘且包含_的学生信息。

SELECT * 
FROM student 
WHERE Sdept='CS' 
AND  student.sName LIKE '刘%\_%'

6.查询至少选修两门课程的学生学号。

SELECT sc.sno
FROM sc
GROUP BY sc.sno HAVING COUNT(*) > 1

7.查询选修“数据库”课程，且成绩在80分以上的学生的学号和成绩。

SELECT sc.sno ,sc.grade
FROM sc,course 
WHERE sc.cNo = course.Cno 
AND course.Cname = '数据库' 
AND sc.grade > 80

8.查询每一门课的间接先修课。

SELECT c1.Cno, c2.Cpno
FROM course c1, course c2
WHERE c1.Cpno = c2.Cno

9.列出每个学生的学号及选修课程号，没有选修的学生的学号也一并列出。

SELECT student.sNo,sc.cNo
FROM student,LEFT OUTER JOIN sc
ON student.sNo = sc.sno

10.列出与“张立”同性别并位于同一个系的所有同学的姓名

SELECT student.sName
FROM student
WHERE student.Ssex = (SELECT student.Ssex FROM student WHERE sName = '张立')
AND student.Sdept = (SELECT student.Sdept FROM student WHERE sName = '张立')

或

SELECT a.sName 
FROM student a 
WHERE EXISTS
(SELECT * FROM student b WHERE b.sName='张立'
AND b.Ssex=a.Ssex AND b.Sdept=a.Sdept) ;

带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值(true)或假值(false)

11.对被两名以上学生所选修的课程统计每门课的选课人数要求输出课程号和选修人数，查询结果按人数降序排列；若人数相同，按课程号升序排列。

SELECT sc.Cno,COUNT(*)
FROM sc
GROUP BY cno HAVING COUNT(*) > 2
ORDER BY  COUNT(*) DESC, sc.cno ASC

或

SELECT cno, COUNT(*) AS '选修人数'
FROM sc
GROUP BY cno
HAVING COUNT(*)>2
ORDER BY '选修人数' DESC, cno ASC

附加：聚集函数

查询选修了课程的人数

-- DISTINCT 去重
SELECT COUNT(DISTINCT sno) 
FROM sc

计算选修一号课程的学生人数

SELECT AVG(grade)
FROM sc
WHERE Cno = 1

查询学生201215121选修课程的总学分

SELECT SUM(Ccredit)
FROM sc,course
WHERE sc.sno = '201215121' AND sc.cNo = course.Cno

WHERE 子句中是不能用聚集函数作为条件表达式，聚集函数只能SELECT子句和GROUP BY中的HAVING子句

sql文件 

/*
 Navicat Premium Data Transfer

 Source Server         : mydb
 Source Server Type    : MySQL
 Source Server Version : 50719
 Source Host           : localhost:3306
 Source Schema         : sql_query

 Target Server Type    : MySQL
 Target Server Version : 50719
 File Encoding         : 65001

 Date: 04/07/2021 13:31:42
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course`  (
  `Cno` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Cname` varchar(40) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `Cpno` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Ccredit` smallint(6) NULL DEFAULT NULL,
  PRIMARY KEY (`Cno`) USING BTREE,
  INDEX `Cpno`(`Cpno`) USING BTREE,
  CONSTRAINT `course_ibfk_1` FOREIGN KEY (`Cpno`) REFERENCES `course` (`Cno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', '数据库', '5', 4);
INSERT INTO `course` VALUES ('2', '数学', NULL, 2);
INSERT INTO `course` VALUES ('3', '信息系统', '1', 4);
INSERT INTO `course` VALUES ('4', '操作系统', '6', 3);
INSERT INTO `course` VALUES ('5', '数据结构', '7', 4);
INSERT INTO `course` VALUES ('6', '数据处理', NULL, 2);
INSERT INTO `course` VALUES ('7', 'PASCAL语言', '6', 4);

-- ----------------------------
-- Table structure for sc
-- ----------------------------
DROP TABLE IF EXISTS `sc`;
CREATE TABLE `sc`  (
  `sno` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `cNo` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `grade` smallint(6) NULL DEFAULT NULL,
  PRIMARY KEY (`sno`, `cNo`) USING BTREE,
  INDEX `cNo`(`cNo`) USING BTREE,
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sNo`) ON DELETE CASCADE ON UPDATE RESTRICT,
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cNo`) REFERENCES `course` (`Cno`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of sc
-- ----------------------------
INSERT INTO `sc` VALUES ('201215121', '1', 88);
INSERT INTO `sc` VALUES ('201215121', '2', 85);
INSERT INTO `sc` VALUES ('201215121', '3', 88);
INSERT INTO `sc` VALUES ('201215122', '2', 90);
INSERT INTO `sc` VALUES ('201215122', '3', 80);
INSERT INTO `sc` VALUES ('201215123', '1', 87);
INSERT INTO `sc` VALUES ('201215125', '1', 92);

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student`  (
  `sNo` varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `sName` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Ssex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `Sage` smallint(6) NULL DEFAULT NULL,
  `Sdept` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`sNo`) USING BTREE,
  UNIQUE INDEX `sName`(`sName`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('201215121', '李勇', '男', 20, 'CS');
INSERT INTO `student` VALUES ('201215122', '刘晨', '女', 19, 'CS');
INSERT INTO `student` VALUES ('201215123', '王敏', '女', 18, 'MA');
INSERT INTO `student` VALUES ('201215124', '刘一一', '女', 19, 'CS');
INSERT INTO `student` VALUES ('201215125', '张立', '男', 19, 'IS');
INSERT INTO `student` VALUES ('201215126', '李天', '男', 18, 'IS');
INSERT INTO `student` VALUES ('201215127', '刘_一', '男', 18, 'CS');

SET FOREIGN_KEY_CHECKS = 1;

最后修改：2021 年 08 月 25 日 11 : 55 AM
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏