第二次作业

mysql> create table student_info
    -> (学号 char(4) primary key,
    -> 姓名 char(8),
    -> 性别 char(2) null,
    -> 出生日期 date null,
    -> 家庭住址 varchar(50) null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc student_info;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 学号     | char(4)     | NO   | PRI | NULL    |       |
| 姓名     | char(8)     | YES  |     | NULL    |       |
| 性别     | char(2)     | YES  |     | NULL    |       |
| 出生日期 | date        | YES  |     | NULL    |       |
| 家庭住址 | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.02 sec)

mysql> create table curriculum
    -> (课程编号 char(4) primary key,
    -> 课程名称 varchar(50) null,
    -> 学分 int null
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> desc curriculum;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| 课程编号 | char(4)     | NO   | PRI | NULL    |       |
| 课程名称 | varchar(50) | YES  |     | NULL    |       |
| 学分     | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> create table grade
    -> (学号 char(4),
    -> 课程编号 char(4),
    -> 分数 int null,
    -> primary key(学号,课程编号));
Query OK, 0 rows affected (0.01 sec)

mysql> desc grade;
+----------+---------+------+-----+---------+-------+
| Field    | Type    | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| 学号     | char(4) | NO   | PRI | NULL    |       |
| 课程编号 | char(4) | NO   | PRI | NULL    |       |
| 分数     | int     | YES  |     | NULL    |       |
+----------+---------+------+-----+---------+-------+
3 rows in set (0.02 sec)

mysql> insert into student_info (学号,姓名,性别,出生日期,家庭住址) values
    -> ("0001","张青平","男","2000-10-01","衡阳市东风路77号");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student_info (学号,姓名,性别,出生日期,家庭住址) values
    -> ("0002","刘东阳","男","1998-12-09","东阳市八一北路33号");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student_info (学号,姓名,性别,出生日期,家庭住址) values
    -> ("0003","马晓夏","女","1995-05-12","长岭市五一路763号");
Query OK, 1 row affected (0.00 sec)

mysql> insert into student_info (学号,姓名,性别,出生日期,家庭住址) values
    -> ("0004","钱忠理","男","1994-09-23","滨海市洞庭大道279号");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student_info (学号,姓名,性别,出生日期,家庭住址) values
    -> ("0005","孙海洋","男","1995-04-03","长岛市解放路27号");
Query OK, 1 row affected (0.01 sec)

mysql> insert into student_info (学号,姓名,性别,出生日期,家庭住址) values
    -> ("0006","郭小斌","男","1997-11-10","南山市红旗路113号");
Query OK, 1 row affected (0.02 sec)

mysql> insert into student_info (学号,姓名,性别,出生日期,家庭住址) values
    -> ("0007","肖月玲","女","1996-12-07","东方市南京路11号");
Query OK, 1 row affected (0.02 sec)

mysql> insert into student_info (学号,姓名,性别,出生日期,家庭住址) values
    -> ("0008","张玲珑","女","1997-12-24","滨江市新建路97号");
Query OK, 1 row affected (0.03 sec)

mysql> select * from student_info;
+------+--------+------+------------+---------------------+
| 学号 | 姓名   | 性别 | 出生日期   | 家庭住址            |
+------+--------+------+------------+---------------------+
| 0001 | 张青平 | 男   | 2000-10-01 | 衡阳市东风路77号    |
| 0002 | 刘东阳 | 男   | 1998-12-09 | 东阳市八一北路33号  |
| 0003 | 马晓夏 | 女   | 1995-05-12 | 长岭市五一路763号   |
| 0004 | 钱忠理 | 男   | 1994-09-23 | 滨海市洞庭大道279号 |
| 0005 | 孙海洋 | 男   | 1995-04-03 | 长岛市解放路27号    |
| 0006 | 郭小斌 | 男   | 1997-11-10 | 南山市红旗路113号   |
| 0007 | 肖月玲 | 女   | 1996-12-07 | 东方市南京路11号    |
| 0008 | 张玲珑 | 女   | 1997-12-24 | 滨江市新建路97号    |
+------+--------+------+------------+---------------------+
8 rows in set (0.13 sec)

mysql> insert into curriculum (课程编号,课程名称,学分) values
    -> ("0001","计算机应用基础","2");
Query OK, 1 row affected (0.02 sec)

mysql> insert into curriculum (课程编号,课程名称,学分) values
    -> ("0002","C语言程序设计","2");
Query OK, 1 row affected (0.02 sec)

mysql> insert into curriculum (课程编号,课程名称,学分) values
    -> ("0003","数据库原理及应用","2");
Query OK, 1 row affected (0.02 sec)

mysql> insert into curriculum (课程编号,课程名称,学分) values
    -> ("0004","英语","4");
Query OK, 1 row affected (0.02 sec)

mysql> insert into curriculum (课程编号,课程名称,学分) values
    -> ("0005","高等数学","4");
Query OK, 1 row affected (0.02 sec)

mysql> select * from curriculum;
+----------+------------------+------+
| 课程编号 | 课程名称         | 学分 |
+----------+------------------+------+
| 0001     | 计算机应用基础   |    2 |
| 0002     | C语言程序设计    |    2 |
| 0003     | 数据库原理及应用 |    2 |
| 0004     | 英语             |    4 |
| 0005     | 高等数学         |    4 |
+----------+------------------+------+
5 rows in set (0.10 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0001","0001","80");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0001","0002","91");
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0001","0003","88");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0001","0004","85");
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0001","0005","77");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0002","0001","73");
Query OK, 1 row affected (0.01 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0002","0002","68");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0002","0003","80");
Query OK, 1 row affected (0.00 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0002","0004","79");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0002","0005","73");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0003","0001","84");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0003","0002","92");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0003","0003","81");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0003","0004","82");
Query OK, 1 row affected (0.02 sec)

mysql> insert into grade (学号,课程编号,分数) values
    -> ("0003","0005","75");
Query OK, 1 row affected (0.02 sec)

mysql> select * from grade;
+------+----------+------+
| 学号 | 课程编号 | 分数 |
+------+----------+------+
| 0001 | 0001     |   80 |
| 0001 | 0002     |   91 |
| 0001 | 0003     |   88 |
| 0001 | 0004     |   85 |
| 0001 | 0005     |   77 |
| 0002 | 0001     |   73 |
| 0002 | 0002     |   68 |
| 0002 | 0003     |   80 |
| 0002 | 0004     |   79 |
| 0002 | 0005     |   73 |
| 0003 | 0001     |   84 |
| 0003 | 0002     |   92 |
| 0003 | 0003     |   81 |
| 0003 | 0004     |   82 |
| 0003 | 0005     |   75 |
+------+----------+------+
15 rows in set (0.07 sec)