MySQL Exercise db_xscjgl

导入数据

/*
 Navicat Premium Data Transfer

 Source Server         : localhost
 Source Server Type    : MySQL
 Source Server Version : 50508
 Source Host           : localhost:3306
 Source Schema         : xscjgl

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

 Date: 12/01/2020 12:20:44
*/
CREATE DATABASE IF NOT EXISTS xscjgl DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

USE xscjgl;

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for kc
-- ----------------------------
DROP TABLE IF EXISTS `kc`;
CREATE TABLE `kc`  (
  `课程号` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `课程名` char(16) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `开课学期` tinyint(1) NOT NULL DEFAULT 1 COMMENT '只能为1-8',
  `学时` tinyint(1) NOT NULL,
  `学分` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`课程号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of kc
-- ----------------------------
INSERT INTO `kc` VALUES ('101', '计算机基础', 1, 80, 5);
INSERT INTO `kc` VALUES ('102', '程序设计与语言', 2, 68, 4);
INSERT INTO `kc` VALUES ('206', '离散数学', 4, 68, 4);
INSERT INTO `kc` VALUES ('208', '数据结构', 5, 68, 4);
INSERT INTO `kc` VALUES ('209', '操作系统', 6, 68, 4);
INSERT INTO `kc` VALUES ('210', '计算机原理', 5, 85, 5);
INSERT INTO `kc` VALUES ('212', '数据库原理', 7, 68, 4);
INSERT INTO `kc` VALUES ('301', '计算机网络', 7, 51, 3);
INSERT INTO `kc` VALUES ('302', '软件工程', 7, 51, 3);

-- ----------------------------
-- Table structure for xs
-- ----------------------------
DROP TABLE IF EXISTS `xs`;
CREATE TABLE `xs`  (
  `学号` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `姓名` char(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `专业名` char(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `性别` tinyint(1) NOT NULL DEFAULT 1 COMMENT '1为男 0为女',
  `出生时间` date NOT NULL,
  `总学分` tinyint(1) NULL DEFAULT NULL,
  `照片` blob NULL,
  `备注` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL,
  PRIMARY KEY (`学号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of xs
-- ----------------------------
INSERT INTO `xs` VALUES ('081101', '王林', '计算机', 1, '1990-02-22', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081102', '程明', '计算机', 1, '1991-02-01', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081103', '王燕', '计算机', 0, '1989-10-06', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081104', '韦严平', '计算机', 1, '1990-08-26', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081106', '李方方', '计算机', 1, '1990-11-20', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081107', '李明', '计算机', 1, '1990-05-01', 54, NULL, '提前修完《数据结构》，并获学分');
INSERT INTO `xs` VALUES ('081108', '林一帆', '计算机', 1, '1989-08-05', 52, NULL, '已提前修完一门课');
INSERT INTO `xs` VALUES ('081109', '张强民', '计算机', 1, '1989-08-11', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081110', '张蔚', '计算机', 0, '1991-07-22', 50, NULL, '三好生');
INSERT INTO `xs` VALUES ('081111', '赵琳', '计算机', 0, '1990-03-18', 50, NULL, NULL);
INSERT INTO `xs` VALUES ('081113', '严红', '计算机', 0, '1989-08-11', 48, NULL, '有一门功课不及格，待补考');
INSERT INTO `xs` VALUES ('081201', '王敏', '通信工程', 1, '1989-06-10', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081202', '王林', '通信工程', 1, '1989-01-29', 40, NULL, '有一门课不及格，待补考');
INSERT INTO `xs` VALUES ('081203', '王玉民', '通信工程', 1, '1990-03-26', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081204', '马琳琳', '通信工程', 0, '1989-02-10', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081206', '李计', '通信工程', 1, '1989-09-20', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081210', '李红庆', '通信工程', 1, '1989-05-01', 44, NULL, '已提前修完一门课，并获得学分');
INSERT INTO `xs` VALUES ('081216', '孙祥欣', '通信工程', 1, '1989-03-09', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081218', '孙研', '通信工程', 1, '1990-10-09', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081220', '吴薇华', '通信工程', 0, '1990-03-18', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081221', '刘燕敏', '通信工程', 0, '1989-11-12', 42, NULL, NULL);
INSERT INTO `xs` VALUES ('081241', '罗林琳', '通信工程', 0, '1990-01-30', 50, NULL, '转专业学习');

-- ----------------------------
-- Table structure for xs_kc
-- ----------------------------
DROP TABLE IF EXISTS `xs_kc`;
CREATE TABLE `xs_kc`  (
  `学号` char(6) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `课程号` char(3) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `成绩` tinyint(1) NULL DEFAULT NULL,
  `学分` tinyint(1) NULL DEFAULT NULL,
  PRIMARY KEY (`学号`, `课程号`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of xs_kc
-- ----------------------------
INSERT INTO `xs_kc` VALUES ('081101', '101', 80, 5);
INSERT INTO `xs_kc` VALUES ('081101', '102', 78, 4);
INSERT INTO `xs_kc` VALUES ('081101', '206', 76, 4);
INSERT INTO `xs_kc` VALUES ('081102', '102', 78, 4);
INSERT INTO `xs_kc` VALUES ('081102', '206', 78, 4);
INSERT INTO `xs_kc` VALUES ('081103', '101', 62, 5);
INSERT INTO `xs_kc` VALUES ('081103', '102', 70, 4);
INSERT INTO `xs_kc` VALUES ('081103', '206', 81, 4);
INSERT INTO `xs_kc` VALUES ('081104', '101', 90, 5);
INSERT INTO `xs_kc` VALUES ('081104', '102', 84, 4);
INSERT INTO `xs_kc` VALUES ('081104', '206', 65, 4);
INSERT INTO `xs_kc` VALUES ('081106', '101', 65, 5);
INSERT INTO `xs_kc` VALUES ('081106', '102', 71, 4);
INSERT INTO `xs_kc` VALUES ('081106', '206', 81, 4);
INSERT INTO `xs_kc` VALUES ('081107', '101', 78, 5);
INSERT INTO `xs_kc` VALUES ('081107', '102', 80, 4);
INSERT INTO `xs_kc` VALUES ('081107', '206', 68, 4);
INSERT INTO `xs_kc` VALUES ('081108', '101', 85, 5);
INSERT INTO `xs_kc` VALUES ('081108', '102', 64, 4);
INSERT INTO `xs_kc` VALUES ('081108', '206', 87, 4);
INSERT INTO `xs_kc` VALUES ('081109', '101', 66, 5);
INSERT INTO `xs_kc` VALUES ('081109', '102', 83, 4);
INSERT INTO `xs_kc` VALUES ('081109', '206', 70, 4);
INSERT INTO `xs_kc` VALUES ('081110', '101', 95, 5);
INSERT INTO `xs_kc` VALUES ('081110', '102', 90, 4);
INSERT INTO `xs_kc` VALUES ('081110', '206', 89, 4);
INSERT INTO `xs_kc` VALUES ('081111', '101', 91, 5);
INSERT INTO `xs_kc` VALUES ('081111', '102', 70, 4);
INSERT INTO `xs_kc` VALUES ('081111', '206', 76, 4);
INSERT INTO `xs_kc` VALUES ('081113', '101', 63, 5);
INSERT INTO `xs_kc` VALUES ('081113', '102', 79, 4);
INSERT INTO `xs_kc` VALUES ('081113', '206', 60, 4);
INSERT INTO `xs_kc` VALUES ('081201', '101', 80, 5);
INSERT INTO `xs_kc` VALUES ('081202', '101', 65, 5);
INSERT INTO `xs_kc` VALUES ('081203', '101', 87, 5);
INSERT INTO `xs_kc` VALUES ('081204', '101', 91, 5);
INSERT INTO `xs_kc` VALUES ('081210', '101', 76, 5);
INSERT INTO `xs_kc` VALUES ('081216', '101', 81, 5);
INSERT INTO `xs_kc` VALUES ('081218', '101', 70, 5);
INSERT INTO `xs_kc` VALUES ('081220', '101', 82, 5);
INSERT INTO `xs_kc` VALUES ('081221', '101', 76, 5);
INSERT INTO `xs_kc` VALUES ('081241', '101', 90, 5);

SET FOREIGN_KEY_CHECKS = 1;

1.增删改查语句

1、利用Navicat将代码文件db_xscjgl.sql文件导入，生成数据库与表。
2、查询xs表中的所有记录。
3、在xs表中插入自己的信息。注意：1为男 0为女，如学号位数不够，可以先修改xs表的设计。
4、删除你刚插入的记录
5、将xs表中通信工程专业修改为电子商务专业。
备注：2-5小题，将代码复制到答案区的形式提交作业。
6、将三个表导出为Excel表，表名为：xscjgl_本人中文姓名.xlsx.(以附件的形式提交）

2.select * from xs;
3.insert into xs values ('49','KiriSoyer','计网',1,'2001-04-25',100,null,null);
4.delete from xs where 学号 = '49';
5.update xs set 专业名 = '电子商务' where 专业名 = '通信工程';*

2.单表查询

1、查询学生的学号、姓名、出生时间。
2、查询1990年以后出生的学生的学号、姓名、出生时间。
3、根据学生的成绩，判断是否合格，不低于60分为合格。
4、按总学分的降序显示学生的学号、姓名，总学分。
5、查询姓王的学生的学号与姓名信息。

1.select 学号,姓名,出生时间 from xs;
2.select 学号,姓名,出生时间 from xs where 出生时间 > '1990-01-01';
3.select * from xs_kc where 成绩 >= 60;
4.select 学号,姓名,总学分 from xs order by 总学分 desc;
5.select 学号,姓名 from xs where 姓名 like '王%';

3.分组与多表查询

1、按专业统计各专业学生的人数。
2、统计学生的成绩总分，并按总分由高到低排名。
3、查询学生的学号，姓名，课程名，成绩信息。
4、统计没有参加任何考试的的学生名单。
5、查询学生成绩总分高于（含）200分的学生信息。
6、显示总分前三名的学生信息。

1.select 专业名,count(专业名) 学生人数 from xs group by 专业名;
2.select 学号,sum(成绩) 总分 from xs_kc group by 学号 order by 总分 desc;
3.select xs.学号,xs.姓名,xs_kc.课程号,xs_kc.成绩 from xs,xs_kc where xs.学号 = xs_kc.学号;
4.select * from xs where 姓名 not in (select a.姓名 from xs a,xs_kc b where a.学号 = b.学号 and 课程号 in (select distinct 课程号 from xs_kc));
5.select * from xs where 学号 in (select 学号 from xs_kc group by 学号 having sum(成绩) > 200);
6.select * from xs where 学号 in (select 学号 from xs_kc group by 学号 having sum(成绩) > 200 and count(学号) order by sum(成绩) desc) limit 0,3;

4.子查询

1、显示总分前三名的学生信息。
2、用子查询统计没有参加考试的学生名单。
3、查询计算机基础课程考试，高于平均分的学生信息。
4、查询程序设计与语言课程最高分的学生学号、姓名信息
5、查询计算机基础、程序设计与语言和离散数学这三门课程最高分学生的学号、姓名信息。

1.select * from xs where 学号 in (select 学号 from xs_kc group by 学号 having sum(成绩) > 200 and count(学号) order by sum(成绩) desc) limit 0,3;
2.select * from xs where 姓名 not in (select a.姓名 from xs a,xs_kc b where a.学号 = b.学号 and 课程号 in (select distinct 课程号 from xs_kc));
3.select * from xs where 学号 in (select 学号 from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '计算机基础') and 成绩 > (select avg(成绩) from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '计算机基础')));
4.select * from xs where 学号 = (select 学号 from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '程序设计与语言') and 成绩 = (select max(成绩) from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '程序设计与语言')));
5.select * from xs where 学号 = (select 学号 from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '程序设计与语言') and 成绩 = (select max(成绩) from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '程序设计与语言')))
union all select * from xs where 学号 = (select 学号 from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '计算机基础') and 成绩 = (select max(成绩) from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '计算机基础')))
union all select * from xs where 学号 = (select 学号 from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '离散数学') and 成绩 = (select max(成绩) from xs_kc where 课程号 = (select 课程号 from kc where 课程名 = '离散数学')));

5.视图

1.创建一个显示学生的学号、姓名、课程名、成绩的视图v1；
2.创建一个显示学生成绩90分以上的学生的学号、课程名、成绩、学分视图v2；
3.创建一个显示“计算机”专业的学生名单的视图v3；
4.创建一个没有被学生选修的的课程名单的视图v4；
5.创建一个没有参加过任何选修课程学生名单的视图v5；
6.创建一个显示学生的学号、姓名、性别、出生时间的视图v6；
7.修改视图v1将显示学生的学号、姓名、课程名、成绩、学分；
8.修改视图v6中学号为“081101”的出生时间为“1990-2-22”；
9.在视图v6中插入学生本人的信息；
10.在视图v6中删除学生本人的信息。

1.create view v1 as select a.学号,a.姓名,b.课程名,c.成绩 from xs a,kc b,xs_kc c where a.学号 = c.学号 and c.课程号 = b.课程号;
2.create view v2 as select a.学号,b.课程名,c.成绩,c.学分 from xs a,kc b,xs_kc c where a.学号 = c.学号 and c.课程号 = b.课程号 having c.成绩 >= 90;
3.create view v3 as select * from xs where 专业名 = '计算机';
4.create view v4 as select * from kc where 课程号 not in (select distinct b.课程号 from kc a,xs_kc b where a.课程号 = b.课程号);
5.create view v5 as select * from xs where 姓名 not in (select a.姓名 from xs a,xs_kc b where a.学号 = b.学号 and 课程号 in (select distinct 课程号 from xs_kc));
6.create view v6 as select 学号,姓名,性别,出生时间 from xs;
7.create or replace view v1 as select a.学号,a.姓名,b.课程名,c.成绩,c.学分 from xs a,kc b,xs_kc c where a.学号 = c.学号 and c.课程号 = b.课程号;
8.update v6 set 出生时间='1990-2-22' where 学号='081101';
9.insert into v6 values ('49','KiriSoyer',1,'2001-04-25');
10.delete from v6 where 学号='49';

6.存储过程

1. 创建一个显示学生的学号、姓名、课程名、成绩的存储过程；
2. 创建一个输入学号、课程号，输出学生成绩的存储过程；
3. 创建一个存储过程，按学号输入参数删除xs和xs_kc表中的学生相关信息，必须采用事务处理。

1.create procedure test1() begin select a.学号,a.姓名,b.课程名,c.成绩 from xs a,kc b,xs_kc c where a.学号 = c.学号 and c.课程号 = b.课程号; end
2.create procedure test2(in id varchar(10),in classId varchar(10),out grade int) begin select 成绩 into grade from xs_kc where 学号 = id and 课程号 = classId; end
3.create procedure test3(in id varchar(10)) begin start transaction; delete from xs where 学号 = id; delete from xs_kc where 学号 = id; commit; end

7.触发器

1.在xs表上创建一个删除触发器del_xs，当删除学生信息时，通过触发器删除xs_kc表中的相应学号的成绩记录；
2.创建触发器ins_cj，实现当向xs_kc表插入一行数据时，根据成绩对xs表的总学分进行修改。如果成绩>=60，总学分加上该课程的学分。否则总学分不变；

1.create trigger del_xs before delete on xs for each row begin delete from xs_kc where 学号 = OLD.学号; end
2.create trigger ins_cj after insert on xs_kc for each row begin update xs set 总学分 = (总学分 + NEW.学分) where NEW.成绩 >= 60 and 学号 = NEW.学号; end