《MySQL数据技术与实验指导》jxgl数据库的创建和插入

本是古典何须时尚 2022-04-22 00:16 64阅读 0赞

## 简述 ##

之前都是在宿舍的一个台式电脑上写数据库的作业，但是最近需要出去外面比赛，路上可能就需要写作业。所以就打算重新写一遍这个作业。顺便就写一下答案。

jxgl这个库之后会需要用到。

## 环境介绍 ##

*  windows 10
 *  mysql 8.0 +
 *  Command Line Client 操作

## 代码以及对应的解释 ##

创建student表  
在性别上，设置了只能选 男或者是女

mysql> create table student (
        -> sno VARCHAR(7) NOT NULL,
        -> sname VARCHAR(16),
        -> ssex CHAR(2) DEFAULT '男' CHECK (ssex='男' or ssex='女'),
        -> sage int,
        -> sdept VARCHAR(2),
        -> PRIMARY KEY(sno)) ENGINE = InnoDb;
    Query OK, 0 rows affected (0.20 sec)

创建course表  
要注意：课程表上，cpno其实是course表本身的cno的外键。

mysql> create table course (
        -> cno VARCHAR(2) NOT NULL,
        -> cname VARCHAR(20),
        -> cpno VARCHAR(2) NULL,
        -> credit int,
        -> PRIMARY KEY(cno),
        -> FOREIGN key(cpno) references course(cno)) ENGINE = InnoDB;
    Query OK, 0 rows affected (0.17 sec)
    
    mysql>

创建sc表

mysql> create table sc (
        -> sno VARCHAR(7) NOT NULL,
        -> cno VARCHAR(7) NOT NULL,
        -> grade int NULL check (grade is NULL or( grade between 0 and 100)),
        -> PRIMARY KEY(sno, cno),
        -> Foreign Key(sno) references student(sno),
        -> Foreign Key(cno) references course(cno))ENGINE=InnoDB;
    Query OK, 0 rows affected (0.18 sec)

插入是student表

mysql> insert into student value('2005001', '钱横', '男', 18, 'Cs');
    Query OK, 1 row affected (0.04 sec)
    
    mysql> insert into student value('2005002', '王林', '女', 19, 'Cs');
    Query OK, 1 row affected (0.06 sec)
    
    mysql> insert into student value('2005003', '李民', '男', 20, 'Is');
    Query OK, 1 row affected (0.06 sec)
    
    mysql> insert into student value('2005004', '赵欣然', '女', 16, 'Ma');
    Query OK, 1 row affected (0.17 sec)

插入课程表course

mysql> insert into course value('6', '数据处理基础', NULL, 4);
    Query OK, 1 row affected (0.09 sec)
    
    mysql> insert into course value('7', 'C语言', 6, 3);
    Query OK, 1 row affected (0.08 sec)
    
    mysql> insert into course value('4', '操作系统原理', 6, 3);
    Query OK, 1 row affected (0.06 sec)
    
    mysql> insert into course value('5', '数据结构', 7, 4);
    Query OK, 1 row affected (0.04 sec)
    
    mysql> insert into course value(1, '数据库基础', 5, 4);
    Query OK, 1 row affected (0.09 sec)
    
    mysql> insert into course value(3, '信息系统导论', 1, 3);
    Query OK, 1 row affected (0.10 sec)
    
    mysql> insert into course value(2, '数学分析', NULL, 2);
    Query OK, 1 row affected (0.10 sec)
    
    mysql>

由于之前设置了cpno到cno的一个外键，所以，需要按特定的顺序完成。

mysql> insert into sc value('2005001', 1, 87);
    Query OK, 1 row affected (0.02 sec)
    
    mysql> insert into sc value('2005001', 2, 67);
    Query OK, 1 row affected (0.02 sec)
    
    mysql> insert into sc value('2005001', 3, 90);
    Query OK, 1 row affected (0.07 sec)
    
    mysql> insert into sc value('2005002', 2, 95);
    Query OK, 1 row affected (0.04 sec)
    
    mysql> insert into sc value('2005003', 3, 88);
    Query OK, 1 row affected (0.06 sec)

**在第五章里面插入的一些数据**

mysql> insert into student values ('2005007', '李涛', '男', 19, 'Is');
    Query OK, 1 row affected (0.18 sec)
    mysql> insert into student values ('2005008', '陈高', '女', 21, 'At'),
        -> ('2005009', '张杰', '男', 17, 'At');
    Query OK, 2 rows affected (0.03 sec)
    Records: 2  Duplicates: 0  Warnings: 0

再插入一些数据，下面是添加一些人

mysql> insert into student values ('2005010', '赵青江', '男', 18, 'Cs');
    Query OK, 1 row affected (0.04 sec)
    
    mysql> insert into student values ('2005011', '张丽萍', '女', 19, 'Ch');
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into student values ('2005012', '陈景欢', '男', 20, 'Is');
    Query OK, 1 row affected (0.04 sec)
    
    mysql> insert into student values ('2005013', '陈婷婷', '女', 16, 'Ph');
    Query OK, 1 row affected (0.09 sec)
    
    mysql> insert into student values ('2005014', '李军', '女', 16, 'Ph');
    Query OK, 1 row affected (0.05 sec)

再添加一些选课的记录

mysql> insert into sc values ('2005010', 1, 87);
    Query OK, 1 row affected (0.06 sec)
    
    mysql> insert into sc values ('2005010', 2, NULL);
    Query OK, 1 row affected (0.07 sec)
    
    mysql> insert into sc values ('2005010', 3, 80);
    Query OK, 1 row affected (0.04 sec)
    
    mysql> insert into sc values ('2005010', 4, 87);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into sc values ('2005010', 6, 85);
    Query OK, 1 row affected (0.02 sec)
    
    mysql> insert into sc values ('2005011', 1, 52);
    Query OK, 1 row affected (0.10 sec)
    
    mysql> insert into sc values ('2005011', 2, 47);
    Query OK, 1 row affected (0.01 sec)
    
    mysql> insert into sc values ('2005011', 3, 53);
    Query OK, 1 row affected (0.04 sec)
    
    mysql> insert into sc values ('2005011', 5, 45);
    Query OK, 1 row affected (0.06 sec)
    
    mysql> insert into sc values ('2005012', 1, 84);
    Query OK, 1 row affected (0.07 sec)
    
    mysql> insert into sc values ('2005012', 3, NULL);
    Query OK, 1 row affected (0.05 sec)
    
    mysql> insert into sc values ('2005012', 4, 67);
    Query OK, 1 row affected (0.02 sec)
    
    mysql> insert into sc values ('2005012', 5, 81);
    Query OK, 1 row affected (0.02 sec)