学生信息与课程、成绩信息、创建视图[成绩信息表视图]

项目背景介绍本次的数据库设计主要涉及到学生信息与课程、成绩信息的相关管理：信息查询与用户安全；任务概述：

具体关系数据库如下：

数据库名：学生成绩数据库

学生信息表(学号 char(6)，姓名，性别，民族，身份证号)

课程信息表(课号 char(6)，名称)

成绩信息表(ID,学号，课号，分数)

具体要求：用SQL语言实现下列功能并导出sql语句代码

1. 创建数据库[学生成绩数据库]代码。

        create database 学生成绩数据库;

2. 创建数据表[课程信息表]代码；

    课程信息表(课号 char(6)，名称)

    要求使用：主键(课号)、非空(名称)

        create table 课程信息表

        (

        课号 char(6) primary key,

        名称 nchar(20) not null

        );

3. 创建数据表[学生信息表]代码；

    学生信息表(学号 char(6)，姓名，性别，民族，身份证号)

    要求使用：主键(学号)、默认(民族)、非空(民族，姓名)、唯一(身份证号)、检查(性别)

        create table 学生信息表

        (

        学号 char(6) primary key,

        姓名 nchar(4) not null,

        性别 nchar(1) check(性别 in ('男','女')),

        民族 nchar(8) default '汉族' not null,

        身份证号 char(18) unique

        );

向表中录入数据，如下所示：

insert into 学生信息表 values ('001', '张燕', '女','汉族', '34010220010307783X');

insert into 学生信息表 values ('002', '江笛韵', '男','高山族', '110101200007103891');

insert into 学生信息表 values ('003', '刘锐', '女',' 汉族', '210102200508074056');

4. 创建数据表[成绩信息表]；

    成绩信息表(ID,学号，课号，分数)

    要求使用：外键(学号，课号)、检查(分数),自动编号(ID)

        create table 成绩信息表

        (

        ID int primary key auto_increment,

        学号 char(6) references 学生信息表(学号),

        课号 char(6) references 课程信息表(课号),

        分数 integer check(分数 between 0 and 100)

        );

向表中录入数据，如下所示：

insert into 成绩信息表 values (1,'001', '100101',96);

insert into 成绩信息表 values (2,'001', '100102',100);

insert into 成绩信息表 values (3,'002', '100101',58);

insert into 成绩信息表 values (4,'002', '100102',100);

insert into 成绩信息表 values (5,'003', '100101',98);

insert into 成绩信息表 values (6,'003', '100102',86);

5. 将下列课程信息添加到课程信息表的代码

        课号        名称

        100101    西班牙语

        100102    大学英语

        修改  课号为100102的课程名称：专业英语

        删除  课号为100101的课程信息

        课程信息添加到课程信息表

        insert 课程信息表 values('100101','西班牙语');

        insert 课程信息表 values('100102','大学英语'); 

        修改  课号为100102的课程名称：专业英语

        update 课程信息表 set 名称='专业英语' where 课号='100102';

        删除  课号为100101的课程信息

        delete from 课程信息表 where 课号='100101';

6. 创建视图[成绩信息表视图]的代码；

    成绩信息表视图(学号，姓名，课号，课程名称，分数)

        create view 成绩信息表视图

        as

        select 成绩信息表.学号,姓名,成绩信息表.课号,名称 as 课程名称,分数

        from 成绩信息表,学生信息表,课程信息表

        where 

        成绩信息表.学号=学生信息表.学号 

        and 

        成绩信息表.课号=课程信息表.课号;

7. 从学生信息表中查询姓刘的女同学的情况：姓名、性别、民族。

        select 姓名,性别,民族

        from 学生信息表

        where 姓名 like '刘%' and 性别='女';

8. 查询有一门或一门以上课程成绩小于60分的所有学生的信息，包括学号、姓名。

    select 学号,姓名 

    from 学生信息表 

    where 学号 in

    (select distinct 学号 from 成绩信息表 where 分数<60);

9. 查询“专业英语”课程的最高分、最低分、平均分。 

    select 课程名称,

    max(分数) as 最高分,

    min(分数) as 最低分,

    avg(分数) as 平均分

    from 成绩信息表视图

    where 课程名称='专业英语'