MySQL 创建测试数据库和表

MySQL 评论

说明

  1. 下面是创建数据库和数据表的代码。
  2. 代码中如果有不明白的，可参考 MySQL常用指令
  3. 后期的文章中会以这个库为准做练习，建议按照下面的步骤，在自己的电脑中把数据库创建出来，方便以后的练习。
  4. Mac 中 mysql 服务指令
    • 启动服务：brew services start mysql
    • 停止服务：brew services stop mysql
    • 重启服务：brew services restart mysql
    • 进入 mysql 环境：mysql -uroot -p
    • 退出 mysql 环境：e

xscj 库

创建数据库

1
create database xscj default character set ="utf8";

进入数据库

1
use xscj;

创建 student 表

1
2
3
4
5
6
7
8
9
10
create table student
(
    id char(6)  not null primary key,
    name char(8) not null,
    major char(10),
    sex char(2) not null,
    birthday date,
    credits tinyint(2),
    note varchar(50)
);

创建 class 表

1
2
3
4
5
6
7
8
create table class
(
    id char(3) not null primary key,
    name char(16) not null,
    semester tinyint(1) not null,
    hours tinyint(1) not null,
    credits tinyint(2) not null
);

创建 results 表

1
2
3
4
5
6
7
create table results
(
    sid char(6) not null,
    cid char(3) not null,
    results tinyint(1),
    PRIMARY KEY  (sid,cid)
);

在 student 表里插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
insert into student 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','转专业学习');

在 class 表里插入数据

1
2
3
4
5
6
7
8
9
10
insert into class 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');

在 results 表里插入数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
insert into results 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);

gradem 库

创建数据库

1
create database gradem default character set = "utf8";

进入数据库

1
use gradem;

创建 student 表

1
2
3
4
5
6
7
8
9
10
create table student
(
		sno char(10) not null primary key,
		sname varchar(8) null,
		ssex char(2) null,
		sbirthday datetime null,
		saddress varchar(50) null,
		sdept char(16) null,
		speciality varchar(20) null
);

创建 course 表

1
2
3
4
5
create table course
(
		cno char(5) not null primary key,
   	cname varchar(20) not null
);

创建 sc 表

1
2
3
4
5
6
7
create table sc
(
		sno char(10) not null,
		cno char(5) not null,
		degree decimal(4,1) null,
		primary key(sno,cno)
);

创建 teacher 表

1
2
3
4
5
6
7
8
create table teacher
(
		tno char(3) not null primary key,
		tname varchar(8) null,
		tsex char(2) null,
		tbirthday date null,
		tdept char(16) null
);

创建 teaching 表

1
2
3
4
5
6
7
create table teaching
(
		cno char(5) not null,
		tno char(3) not null,
		cterm tinyint(1) null,
		primary key(cno,tno)
);

在 student 表中插入数据

1
2
3
4
5
insert into student values
('20050101','李勇','男','1987-01-12','山东济南','计算机工程系','计算机应用'),
('20050201','刘晨','女','1988-06-04','山东青岛','信息工程系','电子商务'),
('20050301','王敏','女','1989-12-23','江苏苏州','数学系','数学'),
('20050202','张立','男','1988-08-25','河北唐山','信息工程系','电子商务');

在 course 表中插入数据

1
2
3
4
5
insert into course values
('C01','数据库'),
('C02','数学'),
('C03','信息系统'),
('C04','操作系统');

在 sc 表中插入数据

1
2
3
4
5
insert into sc values
('20050101','C01',92),
('20050101','C02',85),
('20050101','C03',88),
('20050201','C03',80);

在 teacher 表中插入数据

1
2
3
4
5
insert into teacher values
('101','李新','男','1977-01-12','计算机工程系'),
('102','钱军','女','1968-06-04','计算机工程系'),
('201','王小花','女','1979-12-23','信息工程系'),
('202','张小青','男','1968-08-25','信息工程系');

在 teaching 表中插入数据

1
2
3
4
5
insert into teaching values
('C01','101',2),
('C02','102',1),
('C03','201',3),
('C04','202',4);

iGmaStation wide Engineer

程码开发