数据库实验九---各类语句

最新推荐文章于 2023-06-13 22:28:35 发布

ALAN_CF

最新推荐文章于 2023-06-13 22:28:35 发布

阅读量1.1k

点赞数 3

分类专栏： MYSQL数据库文章标签：数据库 database

本文链接：https://blog.csdn.net/caoxiaobao1207/article/details/121822463

版权

MYSQL数据库专栏收录该内容

16 篇文章 15 订阅

订阅专栏

数据库实验九---各类语句

1、查询（有关宿舍楼活动人数的查询）

1.1 根据宿舍楼编号：查询该宿舍楼当前时刻宿舍楼活动人数

SELECT dormkey AS '宿舍楼编号',dorm_stunum AS '宿舍楼入住总人数',living_stunum AS '宿舍楼当前活动人数' FROM dormitory WHERE dormkey=21;

在这里插入图片描述

1.2 根据年级编号：查询该大四（18）年级当前时刻在宿舍楼活动的人数

SELECT SUBSTRING(stukey,1,2) AS '年级', COUNT(*) AS '宿舍活动人数'
 FROM stu_dormitory
  WHERE status=1 && SUBSTRING(stukey,1,2)=18;

在这里插入图片描述

1.3 根据专业编号：查询计算机科学与技术专业当前时刻在宿舍楼的人

SELECT  stukey AS '学生编号', dormkey AS '宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间'
 FROM stu_dormitory
  WHERE status=1 && stukey
   IN (SELECT stukey
        FROM student
         WHERE SUBSTRING(classkey,5,2)
          IN (SELECT majorkey
               FROM major
                WHERE majorname='计算机科学与技术'));

在这里插入图片描述

1.4 根据学院编号：查询智能学院当前时刻在宿舍楼活动的人

SELECT  stukey AS '学生编号', dormkey AS '宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间'
 FROM stu_dormitory
  WHERE status=1 && stukey
   IN (SELECT stukey
        FROM student
         WHERE SUBSTRING(classkey,3,2)
          IN (SELECT collegekey
               FROM college
                WHERE collegename='人工智能与数据科学学院'));

在这里插入图片描述

1.5 根据班级编号：查询该班级当前时刻在宿舍楼活动的人

SELECT  stukey AS '学生编号', dormkey AS '宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间'
 FROM stu_dormitory
  WHERE status=1 && stukey
   IN (SELECT stukey
        FROM student
         WHERE classkey = 18083201);

在这里插入图片描述

1.6 根据学生编号：查询此人当前时刻是否在宿舍楼，以及进出时间

SELECT  stukey AS '学生编号', dormkey AS '宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间'
 FROM stu_dormitory
  WHERE stukey = 182556;

在这里插入图片描述

1.7 给出学生学号，查询该学生的年级、学院、专业、班级，班级总人数、姓名、所在校区，所居住宿舍楼，所居住房间，他是否在宿舍楼，进/出宿舍楼时间

SELECT SUBSTRING(student.stukey,1,2) AS '年级', grade.gradename AS '年级名称', college.collegename AS '学院名称',
 major.majorname AS '专业名称',class.classname AS '班级名称', class.class_stunum AS '班级总人数',
 student.stuname AS '学生姓名',student.stukey AS '学生学号', campus.campusname AS '校区名称',
 dormitory.dormname AS '宿舍楼名称',student.roomkey AS '宿舍房间编号', stu_dormitory.status AS '1在宿舍0不在宿舍',
 stu_dormitory.goORget_time AS '进/出宿舍时间'
  FROM campus,grade,college,major,class,dormitory,student,stu_dormitory
   WHERE student.stukey=182556 && SUBSTRING(student.stukey,1,2)=grade.gradekey
    && SUBSTRING(student.classkey,3,2)=college.collegekey 
    && SUBSTRING(student.classkey,5,2)=major.majorkey  
    && student.classkey=class.classkey
    && student.dormkey=dormitory.dormkey
    && SUBSTRING(student.dormkey,1,1)=campus.campuskey
    && student.stukey=stu_dormitory.stukey
  \G;

在这里插入图片描述

2、插入信息

2.1 非法插入新班级信息（年级/学院/专业不合法时）验证触发器拒绝插入

INSERT class(classkey,classname,class_stunum) VALUES(17010101,'错误班级001',0);
INSERT class(classkey,classname,class_stunum) VALUES(18650101,'错误班级002',0);
INSERT class(classkey,classname,class_stunum) VALUES(19019001,'错误班级003',0);

在这里插入图片描述

2.2 非法插入新学生信息（年级/学院/专业/宿舍号/房间号都合法，但是给此新学生分配的宿舍房间人数已满时）验证触发器拒绝插入

SELECT room_living_stunum AS '宿舍已入住人数' FROM room WHERE dormkey=21 && roomkey=401;
SELECT stukey AS '学号', stuname AS '学生姓名', dormkey AS '宿舍楼编号', roomkey AS '宿舍房间编号'
 FROM student WHERE dormkey=21 && roomkey=401;

INSERT student (stukey,stuname,classkey,dormkey,roomkey) VALUES(189999,'我能分配到宿舍房间吗',18052001,21,401);

在这里插入图片描述

2.3 正确插入新学生信息后，验证触发器：检查班级人数更新/检查宿舍房间人数更新/检查宿舍楼总人数更新/检查宿舍进出信息表是否按照此新学号插入一条新的学生进出信息

SELECT classkey AS '班级编号', class_stunum AS '插入新学生信息前班级人数' FROM class WHERE classkey=18052001;
SELECT dormkey AS '宿舍楼编号', dorm_stunum AS '插入新学生信息前所在宿舍楼总人数' FROM dormitory WHERE dormkey=21; 
SELECT dormkey AS '宿舍楼编号', roomkey AS '房间编号', room_living_stunum AS '插入新学生信息前宿舍已入住人数' FROM room WHERE dormkey=21 && roomkey=410; 
SELECT stukey AS '学号', stuname AS '学生姓名', dormkey AS '宿舍楼编号', roomkey AS '宿舍房间编号'
 FROM student WHERE dormkey=21 && roomkey=410;

INSERT student (stukey,stuname,classkey,dormkey,roomkey) VALUES(189999,'我能分配到宿舍房间吗',18052001,21,410);

SELECT classkey AS '班级编号', class_stunum AS '插入新学生信息后班级人数' FROM class WHERE classkey=18052001; 
SELECT dormkey AS '宿舍楼编号', dorm_stunum AS '插入新学生信息后所在宿舍楼总人数' FROM dormitory WHERE dormkey=21; 
SELECT dormkey AS '宿舍楼编号', roomkey AS '房间编号', room_living_stunum AS '插入新学生信息后宿舍已入住人数' FROM room WHERE dormkey=21 && roomkey=410;
SELECT stukey AS '学号', stuname AS '学生姓名', dormkey AS '宿舍楼编号', roomkey AS '宿舍房间编号'
 FROM student WHERE dormkey=21 && roomkey=410;
#检查是否在学生信息插入新行的时候，触发器给学生进出信息表也插入了对应的一行
SELECT stukey AS '学生编号', dormkey AS '宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间' FROM stu_dormitory WHERE stukey=189999;

插入之前：
在这里插入图片描述
插入之后：

3、更新信息

3.1 更改某个学生的班级编号/宿舍编号/宿舍房间编号之后，验证触发器：检查新/旧班级人数是否加一/减一，新/旧宿舍楼入住总人数是否加一/减一，新/旧宿舍楼的新/旧房间入住总人数是否加一/减一

#更新之前旧班级/宿舍
SELECT classkey AS '班级编号', class_stunum AS '更新学生信息之前旧班级人数' FROM class WHERE classkey=18052001; 
SELECT dormkey AS '宿舍楼编号', dorm_stunum AS '更新学生信息之前所在旧宿舍楼总人数' FROM dormitory WHERE dormkey=21; 
SELECT dormkey AS '宿舍楼编号', roomkey AS '房间编号', room_living_stunum AS '更新学生信息之前旧宿舍已入住人数' FROM room WHERE dormkey=21 && roomkey=410;
SELECT stukey AS '学号', stuname AS '学生姓名', dormkey AS '旧宿舍楼编号', roomkey AS '宿舍房间编号'
 FROM student WHERE dormkey=21 && roomkey=410;
 
#更新之前新班级/宿舍
SELECT classkey AS '班级编号', class_stunum AS '更新学生信息之前新班级人数' FROM class WHERE classkey=18052002; 
SELECT dormkey AS '宿舍楼编号', dorm_stunum AS '更新学生信息之前新宿舍楼总人数' FROM dormitory WHERE dormkey=12; 
SELECT dormkey AS '宿舍楼编号', roomkey AS '房间编号', room_living_stunum AS '更新学生信息之前新宿舍已入住人数' FROM room WHERE dormkey=12 && roomkey=310;
SELECT stukey AS '学号', stuname AS '学生姓名', dormkey AS '新宿舍楼编号', roomkey AS '宿舍房间编号'
 FROM student WHERE dormkey=12 && roomkey=310;
 
#更新之前学生进出信息
SELECT stukey AS '学生编号', dormkey AS '更新之前宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间' FROM stu_dormitory WHERE stukey=189999;

#更新班级和宿舍
UPDATE student SET classkey=18052002,dormkey=12,roomkey=310 WHERE stukey=189999;

#更新之后旧班级/宿舍
SELECT classkey AS '班级编号', class_stunum AS '更新学生信息之后旧班级人数' FROM class WHERE classkey=18052001; 
SELECT dormkey AS '宿舍楼编号', dorm_stunum AS '更新学生信息之后所在旧宿舍楼总人数' FROM dormitory WHERE dormkey=21; 
SELECT dormkey AS '宿舍楼编号', roomkey AS '房间编号', room_living_stunum AS '更新学生信息之后旧宿舍已入住人数' FROM room WHERE dormkey=21 && roomkey=410;
SELECT stukey AS '学号', stuname AS '学生姓名', dormkey AS '旧宿舍楼编号', roomkey AS '更新之后旧宿舍房间编号'
 FROM student WHERE dormkey=21 && roomkey=410;
 
#更新之后新班级/宿舍
SELECT classkey AS '班级编号', class_stunum AS '更新学生信息之后新班级人数' FROM class WHERE classkey=18052002; 
SELECT dormkey AS '宿舍楼编号', dorm_stunum AS '更新学生信息之后新宿舍楼总人数' FROM dormitory WHERE dormkey=12; 
SELECT dormkey AS '宿舍楼编号', roomkey AS '房间编号', room_living_stunum AS '更新学生信息之后新宿舍已入住人数' FROM room WHERE dormkey=12 && roomkey=310;
SELECT stukey AS '学号', stuname AS '学生姓名', dormkey AS '新宿舍楼编号', roomkey AS '宿舍房间编号'
 FROM student WHERE dormkey=12 && roomkey=310;

#更新之后学生进出信息
SELECT stukey AS '学生编号', dormkey AS '更新之后宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间' FROM stu_dormitory WHERE stukey=189999;

更新之前旧班级/宿舍：
在这里插入图片描述

更新之前新班级/宿舍：
在这里插入图片描述
更新之前学生进出信息：

在这里插入图片描述

更新学生班级和宿舍：
在这里插入图片描述

更新之后旧班级/宿舍：

在这里插入图片描述

更新之后新班级/宿舍：
在这里插入图片描述

更新之后进出学生信息：
在这里插入图片描述

3.2 更新一个学生的进出状态之后，检查其更新前后学生进出信息表的时间/状态变化/检查其宿舍楼活动人数变化

SELECT stukey AS '学生编号', dormkey AS '更新之前宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间' FROM stu_dormitory WHERE stukey=189999;
SELECT dormkey AS '宿舍楼编号', living_stunum AS '更新学生进出状态位之前所在宿舍楼活动人数' FROM dormitory WHERE dormkey=12;

UPDATE stu_dormitory SET status=1 WHERE stukey=189999;

SELECT stukey AS '学生编号', dormkey AS '更新之后宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间' FROM stu_dormitory WHERE stukey=189999;
SELECT dormkey AS '宿舍楼编号', living_stunum AS '更新学生进出状态位之后所在宿舍楼活动人数' FROM dormitory WHERE dormkey=12;

在这里插入图片描述

4、删除信息

4.1 删除一条学生信息之后,所在宿舍房间人数减一，所在班级人数减一，所在宿舍楼总人数减一，学生进出信息表对应删除一条（引发它的删除触发器：检查宿舍进出状态位是否是1，如果是1，该宿舍活动人数减一）

SELECT classkey AS '班级编号', class_stunum AS '删除学生信息之前班级人数' FROM class WHERE classkey=18052001; 
SELECT dormkey AS '宿舍楼编号', dorm_stunum AS '删除学生信息之前所在宿舍楼总人数',living_stunum AS '删除学生信息之前所在宿舍楼活动人数' FROM dormitory WHERE dormkey=12; 
SELECT dormkey AS '宿舍楼编号', roomkey AS '房间编号', room_living_stunum AS '更新学生信息之前宿舍房间已入住人数' FROM room WHERE dormkey=12 && roomkey=310;
SELECT stukey AS '学号', stuname AS '学生姓名', dormkey AS '删除学生信息之前对应的进出宿舍表的宿舍楼编号', roomkey AS '宿舍房间编号'
 FROM student WHERE dormkey=12 && roomkey=310;
SELECT stukey AS '学生编号', dormkey AS '删除之前宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间' FROM stu_dormitory WHERE stukey=189999;


DELETE FROM student WHERE stukey=189999;
 

SELECT classkey AS '班级编号', class_stunum AS '删除学生信息之后班级人数' FROM class WHERE classkey=18052001; 
SELECT dormkey AS '宿舍楼编号', dorm_stunum AS '删除学生信息之后所在宿舍楼总人数',living_stunum AS '删除学生信息之后所在宿舍楼活动人数' FROM dormitory WHERE dormkey=12; 
SELECT dormkey AS '宿舍楼编号', roomkey AS '房间编号', room_living_stunum AS '更新学生信息之后宿舍房间已入住人数' FROM room WHERE dormkey=12 && roomkey=310;
SELECT stukey AS '学号', stuname AS '学生姓名', dormkey AS '删除学生信息之后的宿舍楼编号', roomkey AS '宿舍房间编号'
 FROM student WHERE dormkey=12 && roomkey=310;
SELECT stukey AS '学生编号', dormkey AS '删除之后宿舍楼编号', status AS '1在宿舍0不在宿舍', goORget_time AS '进/出宿舍时间' FROM stu_dormitory WHERE stukey=189999;

在这里插入图片描述

5、创建视图

5.1 创建视图Viewpart1 ，要求列出宿舍楼编号为12的宿舍楼的学生学号、姓名、宿舍楼号、房间号、班级号、进出状态、进/出时间

5.* datetime 类型判不空为：！= TRUE

在这里插入图片描述

CREATE VIEW Viewpart1 (stukey,stuname,dormkey,roomkey,classkey,status,goORget_time)
 AS SELECT      stu_dormitory.stukey,student.stuname,stu_dormitory.dormkey,student.roomkey,student.classkey,stu_dormitory.status,stu_dormitory.goORget_time
      FROM student,stu_dormitory 
       WHERE stu_dormitory.dormkey=12 && stu_dormitory.stukey=student.stukey;
 

SELECT stukey AS '学号' , stuname AS '姓名' , dormkey AS '宿舍楼编号' , roomkey AS '房间编号' , classkey AS '班级编号', status AS ' 1在宿舍0不在宿舍 ', goORget_time AS '进/出时间'  
 FROM Viewpart1;

SELECT stukey AS '学号' , stuname AS '姓名' , dormkey AS '宿舍楼编号' , roomkey AS '房间编号' , classkey AS '班级编号', status AS ' 1在宿舍0不在宿舍 ', goORget_time AS '进/出时间'  
 FROM Viewpart1 WHERE goORget_time != TRUE;

在这里插入图片描述

6、创建用户

6.1 创建用户给它查询视图Viewpart1的权限（第12宿舍楼的宿舍管理员）

CREATE USER 'Twelve'@'localhost' IDENTIFIED BY '12';
GRANT SELECT ON test_new_nine.Viewpart1 TO 'Twelve'@'localhost';

mysql> quit;
Bye

C:\Users\15328>mysql -u Twelve -p
Enter password: **
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 187
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

use test_new_nine
select * from Viewpart1 where goORget_time != true;

在这里插入图片描述

7、创建存储过程

7.1 导入数据的时候先导入的班级表，其中的班级总人数是随意给的30+的一个数字，现在创建一个存储过程根据实际班级人数更新班级表的班级总人数（输入参数：年级编号）更新整个年级的所有班级总人数

执行存储过程之前
在这里插入图片描述

SELECT class_stunum FROM class WHERE SUBSTRING(classkey,1,2)=18;
DELIMITER $$
CREATE PROCEDURE proc1(INOUT updategradekey INT)
BEGIN
 DECLARE true_stunum INT;
 DECLARE done1 INT DEFAULT 0;
 DECLARE updateclasskey INT;
 
 DECLARE classkey_x CURSOR FOR SELECT classkey FROM class;
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1=1;
 OPEN classkey_x;
 FETCH classkey_x INTO updateclasskey;
 WHILE done1=0 DO
 IF SUBSTRING(updateclasskey,1,2)=updategradekey THEN
 SELECT COUNT(*) INTO true_stunum FROM student WHERE classkey=updateclasskey;
 UPDATE class SET class_stunum=true_stunum WHERE classkey=updateclasskey;
 END IF;
 FETCH classkey_x INTO updateclasskey;
 END WHILE;
CLOSE classkey_x;
 END
DELIMITER ;
SET @test = 18;
CALL proc1(@test);
SELECT class_stunum FROM class WHERE SUBSTRING(classkey,1,2)=18;

执行存储过程

在这里插入图片描述

验证更新结果=

在这里插入图片描述

ALAN_CF

关注

3
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
数据库实验九---各类语句

数据库实验九---各类语句查询根据宿舍楼编号：查询该宿舍楼当前时刻宿舍楼活动人数根据专业编号：查询该专业当前时刻在宿舍楼的人根据学院编号：查询该学院当前时刻在宿舍楼活动的人根据班级编号：查询该班级当前时刻在宿舍楼活动的人根据学生编号：查询此人当前时刻是否在宿舍楼，以及进出时间查询根据宿舍楼编号：查询该宿舍楼当前时刻宿舍楼活动人数SELECT dormkey AS '宿舍楼编号',dorm_stunum AS '宿舍楼入住总人数',living_stunum AS '宿舍楼当前活动人数' FROM do
复制链接

扫一扫