【实战】mysql 表创建及查询

一、前言

看到一道经典的mysql实战题，于是从创库、创表到数据查询全套操作了下来，在此记录下

实战要求如下：

根据以下三个表，回答一下各题

学生表STUDENT(Sno,Sname,Ssex,Sage,Sdep);学号，姓名，性别，年龄，系别

课程表COURSE(Cno,Cname,Cpno,Ccredit);课程编号，课程名称，选修课程编号，学分

学生选课表SC(Sno,Cno,Grade)；学号，课程编号，成绩

二、表创建

先创建数据库，就不再赘述，创建表

1、STUDENT

CREATE TABLE `student` (
  `Sno` int NOT NULL AUTO_INCREMENT COMMENT '学号',
  `Sname` varchar(50) NOT NULL COMMENT '姓名',
  `Ssex` tinyint NOT NULL COMMENT '性别0.男 1.女',
  `Sage` int NOT NULL COMMENT '年龄',
  `Sdep` varchar(50) NOT NULL COMMENT '系别',
  PRIMARY KEY (`Sno`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

2、COURSE

CREATE TABLE `course` (
  `Cno` int NOT NULL AUTO_INCREMENT COMMENT '课程编号',
  `Cname` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '课程名称',
  `Cpno` int NOT NULL COMMENT '选修课程编号',
  `Ccredit` decimal(50,0) NOT NULL COMMENT '学分',
  `datachange_lasttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`Cno`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

3、SC

CREATE TABLE `sc` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `Sno` smallint NOT NULL COMMENT '学号',
  `Cno` smallint NOT NULL COMMENT '课程编号',
  `Grade` float NOT NULL COMMENT '成绩',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `datachange_lasttime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

三、插入数据

1、STUDENT

INSERT INTO `student` SET Sname='张三',Ssex=0,Sage=20,Sdep='信息系'
INSERT INTO `student` SET Sname='韩梅',Ssex=1,Sage=18,Sdep='信息系'
INSERT INTO `student` SET Sname='李四',Ssex=0,Sage=23,Sdep='环艺系'

2、COURSE

INSERT INTO `course` SET Cname='Java编程',Cpno=1,Ccredit =100
INSERT INTO `course` SET Cname='数据库',Cpno=2,Ccredit =100
INSERT INTO `course` SET cname='网页设计',cpno=3,ccredit=100
INSERT INTO `course` SET cname='前端开发',cpno=4,ccredit=100

3、SC

INSERT INTO `sc` SET Sno =1,Cno=2,Grade=90
INSERT INTO `sc` SET Sno =1,Cno=2,Grade=90
INSERT INTO `sc` SET Sno =1,Cno=3,Grade=91
INSERT INTO `sc` SET Sno =1,Cno=4,Grade=94

INSERT INTO `sc` SET Sno =2,Cno=1,Grade=80
INSERT INTO `sc` SET Sno =2,Cno=2,Grade=81 
INSERT INTO `sc` SET Sno =2,Cno=3,Grade=81

INSERT INTO `sc` SET Sno =3,Cno=1,Grade=70
INSERT INTO `sc` SET Sno =3,Cno=2,Grade=71 
INSERT INTO `sc` SET Sno =3,Cno=3,Grade=71

四、数据查询

#1、查询年龄在20~23岁，包含20和23岁的学生的姓名、系别和年龄
SELECT Sname,Sdep,Sage FROM student WHERE Sage >=20 AND Sage<=23
SELECT Sname,Sdep,Sage FROM student WHERE Sage BETWEEN 20 AND 23

#2、统计信息系各学生当前选择的课程数
SELECT SC.`Sno`,s.Sname,COUNT(*) FROM SC AS sc  INNER JOIN student AS s ON sc.`Sno`=s.Sno GROUP BY Sno


#3、桉课程编号排序，累出各课程的参加学生名单
SELECT sc.`Cno`,c.cname,sc.`Sno`,s.Sname FROM (sc INNER JOIN student AS s ON sc.`Sno`=s.Sno) INNER JOIN course AS c ON sc.`Cno`=c.cNO ORDER BY Cno end