创建练习数据库

说明

1. 下面是创建数据库和数据表的代码。
2. 代码中如果有不明白的，可参考 MySQL常用指令
3. 后期的文章中会以这个库为准做练习，建议按照下面的步骤，在自己的电脑中把数据库创建出来，方便以后的练习。

创建数据库

create database xscj default character set ="utf8";

进入数据库

use xscj;

创建 xs 表

create table xs
(
    学号 char(6)  not null primary key,
    姓名 char(8) not null,
    专业名 char(10),
    性别 char(2) not null,
    出生日期 date,
    总学分 tinyint(2),
    备注 varchar(50)
);

创建 kc 表

create table KC
(
    课程号 char(3) not null primary key,
    课程名 char(16) not null,
    开课学期 tinyint(1) not null,
    学时 tinyint(1) not null,
    学分 tinyint(2) not null
);

创建 xs_kc 表

create table XS_KC
(
    学号 char(6) not null,
    课程号 char(3) not null,
    成绩 tinyint(1),
    PRIMARY KEY  (学号,课程号)
);

在 xs 表里插入数据

insert into xs values
    ('081101','王林','计算机','男','1990-02-10','50',null),
    ('081102','程明','计算机','男','1991-02-01','50',null),
    ('081103','王燕','计算机','女','1989-10-06','50',null),
    ('081104','韦严平','计算机','男','1990-08-26','50',null),
    ('081105','李方方','计算机','男','1989-08-05','50',null),
    ('081106','李明','计算机','男','1990-05-01','54','修完数据结构'),
    ('081107','林一帆','计算机','男','1989-08-05','52','修完一门课程'),
    ('081108','张强民','计算机','男','1989-08-11','50',null),
    ('081110','张蔚','计算机','女','1991-07-22','50','三好生'),
    ('081111','赵琳','计算机','女','1990-03-18','50',null),
    ('081113','严红','计算机','女','1989-08-11','48','有一门课不及格'),
    ('081201','王敏','通信工程','女','1989-06-10','42',null),
    ('081204','马琳琳','通信工程','女','1989-02-10','42',null),
    ('081206','李计','通信工程','男','1989-09-20','42',null),
    ('081211','李红庆','通信工程','女','1989-05-01','44','已提前修完一门课'),
    ('081216','孙祥欣','通信工程','男','1989-03-09','42',null),
    ('081218','孙研','通信工程','男','1990-10-09','42',null),
    ('081220','吴薇华','通信工程','女','1990-03-18','42',null),
    ('081221','刘燕敏','通信工程','女','1989-11-12','42',null),
    ('081241','罗林琳','通信工程','女','1990-01-30','50','转专业学习');

在 kc 表里插入数据

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

在 xs_kc 表里插入数据

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