《数据库应用与安全管理》期末大课业关键信息总结

数据库结构

• 数据库名称：schooldb+72021810135

  • 包含4个核心表：

    • 教师表(teacher)

    • 学生表(student)

    • 课程表(course)

    • 选课表(cs)

表结构详情

教师表(teacher)：

• 教师编号(teacherno，INT，NOT NULL，主键，默认值无)、

• 姓名(tname，CHAR(8)，NOT NULL，默认值无)、

• 性别(gender，CHAR(2)，NOT NULL，默认值男)、

• 职称(title，CHAR(12)，NULL，默认值无)、

• 出生年月(birth，DATE，NOT NULL，默认值无)、

• 基本工资(sal，DECIMAL(7,2)，NULL，默认值无)

   

学生表(student)：

• 学号(studentno，INT，NOT NULL，主键，默认值无)、

• 姓名(sname，CHAR(8)，NOT NULL，默认值无)、

• 性别(gender，CHAR（2），NOT NULL，默认值女)、

• 出生年月(birth，DATE，NOT NULL，默认值无)、

• 总学分(tc，TINYINT，NULL，默认值无)

课程表(course)：

• 课程号(courseno，INT，NOT NULL，主键，默认值无)、

• 课程名(cname，CHAR（16），NOT NULL，默认值无)、

• 学分(credit，INTYINT，NULL，默认值无)

选课表(cs)：

• 选课编号(csno，INT，NOT NULL，默认值无)、

• 学号(studentno，INT，NOT NULL，默认值无)、

• 课程号(courseno，INT，NOT NULL，默认值无)、

• 得分(credit，DECIMAL，NULL，默认值无)

作业任务

创建与使用数据库：创建schooldb+72021810135数据库

x
-- 创建数据库
CREATE DATABASE schooldb72021810135;
-- 使用数据库
USE schooldb72021810135;

创建数据表：按照上方要求创建4个表，

• teacher+72021810135

  xxxxxxxxxx
  -- 教师表
  CREATE TABLE teacher72021810135 (
      teacherno INT NOT NULL PRIMARY KEY,
      tname CHAR(8) NOT NULL,
      gender CHAR(2) NOT NULL DEFAULT '男',
      title CHAR(12) NULL,
      birth DATE NOT NULL,
      sal DECIMAL(7,2) NULL
  );
  

• student+72021810135

  x
  -- 学生表
  CREATE TABLE student72021810135 (
      studentno INT NOT NULL PRIMARY KEY,
      sname CHAR(8) NOT NULL,
      gender CHAR(2) NOT NULL DEFAULT '女',
      birth DATE NOT NULL,
      tc TINYINT NULL
  );
  

• course+72021810135

  xxxxxxxxxx
  -- 课程表
  CREATE TABLE course72021810135 (
      courseno INT NOT NULL PRIMARY KEY,
      cname CHAR(16) NOT NULL,
      credit TINYINT NULL
  );
  

• cs+72021810135

  xxxxxxxxxx
  -- 选课表（先创建基础结构，后加约束）
  CREATE TABLE cs72021810135 (
      csno INT NOT NULL,
      studentno INT NOT NULL,
      courseno INT NOT NULL,
      credit DECIMAL(5,2) NULL
  );
  

添加表约束：

• 为cs表的csno字段添加主键约束和自动增长

  x
  -- 为cs表的csno字段添加主键约束和自动增长
  ALTER TABLE cs72021810135
  MODIFY csno INT AUTO_INCREMENT PRIMARY KEY;
  

• 为cs表的studentno和courseno字段添加外键约束

  xxxxxxxxxx
  -- 为 cs 表的 studentno 和 courseno 字段添加外键约束
  ALTER TABLE cs72021810135 
  ADD CONSTRAINT fk_studentno 
  FOREIGN KEY (studentno) 
  REFERENCES student72021810135(studentno);
  
  ALTER TABLE cs72021810135 
  ADD CONSTRAINT fk_courseno 
  FOREIGN KEY (courseno) 
  REFERENCES course72021810135(courseno);
  

数据插入：按示例插入教师、学生、课程和选课数据

• Teacher表插入

  • INSERT INTO teacher72021810135 VALUES

  • (1001,'王小明','男','教授','1976-1-2',9000),

  • (1002,'李明','男','讲师','1982-8-22',5500),

  • (1003,'王丹','女','讲师','1980-7-12',5000),

  • (1004,'王红','女','助教','1993-12-2',NULL),

  • (1005,'张贺','男','讲师','1991-1-8',6400),

  • (1006,'韩芳','女','教授','1977-9-10',9000),

  • (1007,'刘阳','男','讲师','1985-9-3',5800)

    xxxxxxxxxx
    -- 插入教师数据
    INSERT INTO teacher72021810135 VALUES
    (1001,'王小明','男','教授','1976-1-2',9000),
    (1002,'李明','男','讲师','1982-8-22',5500),
    (1003,'王丹','女','讲师','1980-7-12',5000),
    (1004,'王红','女','助教','1993-12-2',NULL),
    (1005,'张贺','男','讲师','1991-1-8',6400),
    (1006,'韩芳','女','教授','1977-9-10',9000),
    (1007,'刘阳','男','讲师','1985-9-3',5800);
    

• Student表插入

  • INSERT INTO student72021810135 VALUES

  • (2001,'赵一','男','2002-1-2',64),

  • (2002,'钱二','男','2002-8-22',90),

  • (2003,'孙三','女','2001-7-12',64),

  • (2004,'李四','女','2003-12-2',72),

  • (2005,'周五','男','2003-1-5',32),

  • (2006,'吴六','女','2002-3-15',96),

  • (2007,'郑琦','女','2001-4-20',32)

    xxxxxxxxxx
    -- 插入学生数据
    INSERT INTO student72021810135 VALUES
    (2001,'赵一','男','2002-1-2',64),
    (2002,'钱二','男','2002-8-22',90),
    (2003,'孙三','女','2001-7-12',64),
    (2004,'李四','女','2003-12-2',72),
    (2005,'周五','男','2003-1-5',32),
    (2006,'吴六','女','2002-3-15',96),
    (2007,'郑琦','女','2001-4-20',32); 
    

• Course表插入

  • INSERT INTO course72021810135 VALUES

  • (3001,'数据库',4),

  • (3002,'信息安全',2),

  • (3003,'网络技术',4),

  • (3004,'密码学',2),

  • (3005,'C语言',4)

    xxxxxxxxxx
    -- 插入课程数据
    INSERT INTO course72021810135 VALUES
    (3001,'数据库',4),
    (3002,'信息安全',2),
    (3003,'网络技术',4),
    (3004,'密码学',2),
    (3005,'C语言',4);
    

• Cs表插入

  • INSERT INTO cs72021810135(studentno,courseno,credit) VALUES

  • (2001,3001,90),(2001,3002,78),(2002,3001,66),(2002,3002,56),

  • (2003,3003,55),(2003,3004,88),(2004,3005,99),(2004,3001,88),

  • (2005,3002,87),(2005,3003,67),(2006,3004,65),(2006,3005,76),

  • (2007,3001,54),(2007,3002,88)

    xxxxxxxxxx
    -- 插入选课数据
    INSERT INTO cs72021810135(studentno,courseno,credit) VALUES
    (2001,3001,90),(2001,3002,78),(2002,3001,66),(2002,3002,56),
    (2003,3003,55),(2003,3004,88),(2004,3005,99),(2004,3001,88),
    (2005,3002,87),(2005,3003,67),(2006,3004,65),(2006,3005,76),
    (2007,3001,54),(2007,3002,88);
    

数据修改与删除：

• 添加个人数据

  1. (2008,韩天麒,男,2006-8-5,108)

  2. Studentno:2008,courseno:3002,credit:100

     xxxxxxxxxx
     -- 添加个人数据
     INSERT INTO student72021810135 VALUES
     (2008,'韩天麒','男','2006-8-5',108);
     
     -- 添加个人选课数据
     INSERT INTO cs72021810135(studentno,courseno,credit) VALUES
     (2008,3002,100);
     

• 修改个人姓名为"张三"

  1. 韩天麒改为张三

     xxxxxxxxxx
     -- 修改个人姓名
     UPDATE student72021810135 
     SET sname = '张三' 
     WHERE studentno = 2008;
     

• 删除个人数据

  1. 删除韩天麒在student表和cs表添加的数据

     xxxxxxxxxx
     -- 删除个人数据
     DELETE FROM cs72021810135 
     WHERE studentno = 2008;
     
     DELETE FROM student72021810135 
     WHERE studentno = 2008;
     

数据查询（11个查询任务）：

• 查询学生的姓名、性别和出生日期；

  x
  -- 1. 查询学生姓名、性别、出生日期
  SELECT sname, gender, birth
  FROM student72021810135;
  

• 查询基本工资小于等于5000元的教师的姓名、职称和基本工资；

  x
  -- 2. 查询基本工资<=5000的教师信息
  SELECT tname, title, sal 
  FROM teacher72021810135 
  WHERE sal <= 5000;
  

• 查询出生日期在2002-01-01之前女学生的信息；

  xxxxxxxxxx
  -- 3. 查询2002-01-01前出生的女学生
  SELECT * 
  FROM student72021810135 
  WHERE birth < '2002-01-01' AND gender = '女';
  

• 查询基本工资为空值的教师信息；

  xxxxxxxxxx
  -- 4. 查询基本工资为空的教师
  SELECT * 
  FROM teacher72021810135 
  WHERE sal IS NULL;
  

• 查询出生日期在1980-01-01到1990-01-01的教师的信息；

  xxxxxxxxxx
  -- 5. 查询1980-1990年出生的教师
  SELECT * 
  FROM teacher72021810135 
  WHERE birth BETWEEN '1980-01-01' AND '1990-01-01';
  

• 查询姓王的教师信息；

  xxxxxxxxxx
  -- 6. 查询姓王的教师
  SELECT * 
  FROM teacher72021810135 
  WHERE tname LIKE '王%';
  

• 查询学生的最高学分和最低学分；

  xxxxxxxxxx
  -- 7. 查询学生最高/最低学分
  SELECT MAX(tc) AS 最高学分, MIN(tc) AS 最低学分 
  FROM student72021810135;
  

• 查询各职称的工资总额和工资平均值；

  xxxxxxxxxx
  -- 8. 查询各职称的工资总额和平均值
  SELECT title, SUM(sal) AS 工资总额, AVG(sal) AS 平均工资 
  FROM teacher72021810135 
  GROUP BY title;
  

• 查询学生的信息，按照总学分降序排列；

  xxxxxxxxxx
  -- 9. 学生信息按总学分降序排列
  SELECT * 
  FROM student72021810135 
  ORDER BY tc DESC;
  

• 查询所有学生各科成绩；

  xxxxxxxxxx
  -- 10. 所有学生各科成绩（多表连接）
  SELECT 
      s.studentno, 
      s.sname, 
      c.courseno, 
      c.cname, 
      cs.credit AS 成绩
  FROM student72021810135 s
  JOIN cs72021810135 cs ON s.studentno = cs.studentno
  JOIN course72021810135 c ON cs.courseno = c.courseno
  ORDER BY s.studentno, c.courseno;
  

• 使用子查询，查询出数据库课程的所有学生的成绩；

  xxxxxxxxxx
  -- 11. 查询数据库课程的学生成绩（子查询）
  SELECT 
      s.studentno, 
      s.sname, 
      cs.credit AS 成绩
  FROM student72021810135 s
  JOIN cs72021810135 cs ON s.studentno = cs.studentno
  WHERE cs.courseno = (
      SELECT courseno 
      FROM course72021810135 
      WHERE cname = '数据库'
  );
  

索引与视图：

• 为学生表的sname创建索引、教师表的tname创建唯一性约束；

  xxxxxxxxxx
  -- 为sname创建索引
  CREATE INDEX idx_sname 
  ON student72021810135(sname);
  
  -- 为tname创建唯一性约束
  ALTER TABLE teacher72021810135 
  ADD CONSTRAINT uk_tname UNIQUE(tname);
  

• 使用show index查看索引

  x
  -- 查看索引
  SHOW INDEX FROM student72021810135;
  SHOW INDEX FROM teacher72021810135;
  

• 删除sname和tname的索引；

  xxxxxxxxxx
  -- 删除索引
  DROP INDEX idx_sname ON student72021810135;
  ALTER TABLE teacher72021810135 DROP INDEX uk_tname;
  

• 给学生表创建视图；

  x
  -- 创建学生信息视图
  CREATE VIEW v_student72021810135 AS
  SELECT * FROM student72021810135;
  

• 通过视图查看学生的信息；

  x
  -- 查询视图
  SELECT * FROM v_student72021810135;
  

• 给学生表、课程表和选课表创建视图，视图中包含学生学号、学生姓名、课程号、课程名和成绩；

  xxxxxxxxxx
  -- 创建多表视图（学生、课程和成绩）
  CREATE VIEW v_student_course_score72021810135 AS
  SELECT 
      s.studentno,
      s.sname,
      c.courseno,
      c.cname,
      cs.credit AS score
  FROM student72021810135 s
  JOIN cs72021810135 cs ON s.studentno = cs.studentno
  JOIN course72021810135 c ON cs.courseno = c.courseno;
  

• 通过视图查看学生的各科成绩。

  xxxxxxxxxx
  -- 查询视图
  SELECT * FROM v_student_course_score72021810135;