学生成绩数据库，把学生表、成绩表通过学号建立约束关系

项目背景介绍：随着科学技术的发展，我们校园的各项管理也日趋完善，本次的数据库设计主要涉及到学生信息与课程、成绩信息的相关信息查询与编辑操作。任务概述：

具体关系数据库如下：

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

学生表(班级编号char(6)，学号char(10)，姓名，性别，民族，身份证号，出生日期)

课程表(课程号char(3)，课程名)

成绩表(ID，学号char(10)，课程号char(3)，分数)

用SQL语言实现下列功能的sql语句代码：

(1). 创建:学生成绩数据库，学生表、课程表、成绩表.

(a) 创建数据库[学生成绩数据库]代码。

                create database 学生成绩数据库;

(b) 创建数据表[学生表]代码；

    学生表(班级编号char(6),学号 char(10)，姓名，性别，民族，身份证号，出生日期)

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

                create table 学生表

                (

                班级编号 char(6),

                学号 char(10) primary key,

                姓名 nchar(4) not null,

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

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

                身份证号 char(18) unique,

                出生日期 datetime

                );

            向学生表添加2条数据：

            insert 学生表 values

            ('050101','050101001','赵敏','女','汉','341222200609226221','2006-09-22'),

            ('050102','050102001','刘振','男','NULL','341623200705256022','2007-05-25');

            或者把第7题的2条数据，在这里一起添加进来：

            insert 学生表 values

            ('050201','050201001','王莉欣','女','汉','53010219790625224','1979-06-25'),

            ('050202','050202001','张晶','男','NULL','01020319801224121','NULL');

            在[学生成绩数据库]的[学生表]中查询年龄为20岁或22岁的学生

            需要先添加2条数据年龄20或22

            insert 学生表 values

            ('050101','050101002','柴小虎','男','汉','341222200303218582','2003-03-21'),

            ('050102','050102002','刘维','男','汉','341623200105256022','2001-05-25');

            查询学号为050202003  学生的姓名、课程名、分数。

            需要先添加这条数据

            insert 学生表 values

            ('050202','050202003','葛晓丽','女','汉','341222199807200569','1998-07-20');         

            数据填写完成，如下所示：

(c) 创建数据表[课程表]代码；

    课程表(课程号 char(3)，课程名)

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

                create table 课程表

                (

                课程号 char(3) primary key,

                课程名 nchar(20) not null

                );

            添加课程数据，如下所示：

            insert 课程表 values('101','西班牙语');

            insert 课程表 values('102','专业英语');

(d) 创建数据表[成绩表]代码；

    成绩表(ID,学号char(10)，课程号char(3)，分数)

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

                create table 成绩表

                (

                ID int primary key auto_increment,

                学号 char(10) references 学生表(学号),

                课程号 char(3) references 课程表(课号),

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

                );

                添加几位同学的成绩：

                insert 成绩表 values 

                (1,'050101001','101',86),

                (2,'050101001','102',82),

                (3,'050101002','101',76),

                (4,'050101002','102',89),

                (5,'050102001','101',90),

                (6,'050102001','102',82),

                (7,'050102002','101',96),

                (8,'050102002','102',100),

                (9,'050201001','101',100),

                (10,'050201001','102',100); 

                (11,'050202001','101',99),

                (12,'050202001','102',85),

                (13,'050202003','101',86),

                (14,'050202003','102',88);

(2). 在[学生成绩数据库]的[学生表]中查询年龄为20岁或22岁的学生。

            注意：

            前面添加数据的时候（这两个数据中的 身份证信息中年份 需要根据具体年份进行调整，否则，查询的数据是空，没有符合条件的数据输出）

            在[学生成绩数据库]的[学生表]中查询年龄为20岁或22岁的学生

            需要先添加2条数据年龄20或22

            insert 学生表 values

            ('050101','050101002','柴小虎','男','汉','341222200303218582','2003-03-21'),

            ('050102','050102002','刘维','男','汉','341623200105256022','2001-05-25');

            sql语句编写如下所示：

            select 姓名,性别,TIMESTAMPDIFF(year, 出生日期,CURDATE()) as 年龄

            from 学生表

            where

            (TIMESTAMPDIFF(year,出生日期,CURDATE())=20)

            or

            (TIMESTAMPDIFF(year,出生日期,CURDATE())=22);

(3). 在[学生成绩数据库]中查询每个学生的班级编号、学号、姓名、平均分，结果按平均分降序排列，均分相同者按班级排列。

      select 班级编号,a.学号,姓名,avg(分数) as 平均分

                from 学生表 as a inner join 成绩表 as b

                on a.学号=b.学号 

                group by 班级编号,a.学号,a.姓名

                order by avg(分数) desc,班级编号 asc;

(4). 创建一个视图[教学成绩表视图]显示学生的学号、姓名、课程名、分数。

            create view 教学成绩表视图

            as

            select 学生表.学号,姓名,课程名,分数

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

            where 学生表.学号=成绩表.学号

            and 课程表.课程号=成绩表.课程号;

(5). 查询学号为050202003  学生的姓名、课程名、分数。

            select 姓名,课程名,分数

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

            where 学生表.学号=成绩表.学号

            and 课程表.课程号=成绩表.课程号

            and 学生表.学号='050202003';

(6). 把[学生表]、[成绩表]通过[学号]建立约束关系。

                因为学生表中学号是主键，所以成绩表中的学号可以作为 外键

                alter table 成绩表

                add constraint  约束1 foreign key (学号) references  学生表 (学号)

(7). 在学生表中插入记录：

班级编号  学号       姓名    性别  民族  身份证号            出生日期

050201    050201001  王莉欣  女    汉     53010219790625224  1979-06-25

050202    050202001  张晶    男    NULL  01020319801224121  NULL

把张晶的民族改为“汉”、出生日期改为“1980-12-24”

        update 学生表

        set

        民族='汉' ,

        出生日期='1980-12-24'

        where 姓名='张晶';