数据库常见面试题sql

建表语句

课程表

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for cource
-- ----------------------------
DROP TABLE IF EXISTS `cource`;
CREATE TABLE `cource` (
  `cno` int(11) NOT NULL AUTO_INCREMENT,
  `cname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`cno`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='课程表';

-- ----------------------------
-- Records of cource
-- ----------------------------
BEGIN;
INSERT INTO `cource` VALUES (1, '语文');
INSERT INTO `cource` VALUES (2, '数学');
INSERT INTO `cource` VALUES (3, '英语');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

学生成绩表

/*
 Navicat Premium Data Transfer

 Source Server         : mysql5.7
 Source Server Type    : MySQL
 Source Server Version : 50725
 Source Host           : 127.0.0.1:3306
 Source Schema         : test

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

 Date: 02/09/2020 13:58:37
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `sno` int(11) DEFAULT NULL,
  `cno` int(11) DEFAULT NULL COMMENT '课程',
  `score` varchar(255) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生成绩表';

-- ----------------------------
-- Records of score
-- ----------------------------
BEGIN;
INSERT INTO `score` VALUES (1, 1, '60');
INSERT INTO `score` VALUES (1, 2, '61');
INSERT INTO `score` VALUES (2, 1, '80');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

学生表

/*
 Navicat Premium Data Transfer

 Source Server         : mysql5.7
 Source Server Type    : MySQL
 Source Server Version : 50725
 Source Host           : 127.0.0.1:3306
 Source Schema         : test

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

 Date: 02/09/2020 13:59:58
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `sno` int(11) NOT NULL AUTO_INCREMENT,
  `sname` varchar(255) DEFAULT NULL COMMENT '名字',
  `sage` tinyint(255) DEFAULT NULL COMMENT '年龄',
  PRIMARY KEY (`sno`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES (1, '周杰伦', 18);
INSERT INTO `student` VALUES (2, '周润发', 18);
INSERT INTO `student` VALUES (3, '吴孟达', 25);
INSERT INTO `student` VALUES (4, '刘德华', 25);
INSERT INTO `student` VALUES (5, '李连杰', 29);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

1、查询“c001”课程比“c002”课程成绩低的所有学生的学号

select a.* from
(select * from sc a where a.cno=1) a,
(select * from sc b where b.cno=2) b
where a.sno=b.sno and a.score < b.score;

2、查询平均成绩大于60 分的同学的学号和平均成绩

select sno,avg(score) from sc
group by sno
having avg(score) >60;

3、查询所有同学的学号、姓名、选课数、总成绩

select a.sno,a.sname,count(b.cno),sum(b.score) from student a
join score b on a.sno=b.sno
group by sno;

4、查询姓“刘”的老师的个数

select count(*) from student
where sname like '周%';