建表语句
课程表
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 '周%';