【实战】mysql 表创建及查询

一、前言

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

实战要求如下：

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

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

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

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

二、表创建

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

1、STUDENT

?

1 2 3 4 5 6 7 8

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

?

1 2 3 4 5 6 7 8 9

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

?

1 2 3

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

?

1 2 3 4

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

?

1 2 3 4 5 6 7 8 9 10 11 12

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 2 3 4 5 6 7 8 9 10

#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