数据库上机1

发表于 | 分类于 ， ， |
字数统计: | 阅读时长 ≈

数据库上机1

代码如下

创建表

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
-- 11.0
-- 创建学生表
CREATE TABLE Student(
	 Sno char(7) not null UNIQUE,
	 Sname varchar(20) not null,
	 Ssex char(2) not null,
	 Sage SMALLINT,
	 Clno Char(5)
);

-- 创建课程表
CREATE TABLE Course(
		Cno char(1) not null,
		Cname varchar(20) not null,
		Credit Smallint
);

-- 创建班级表

CREATE TABLE Class(
		Clno Char(5) not null UNIQUE,
		Speciality VARCHAR(20) not null,
		Inyear char(4) not null,
		Number INTEGER,
		Monitor Char(7)
);

-- 创建成绩表
CREATE table Grade(
	Sno Char(7) not null,
	Cno char(1) not null,
	Gmark Numeric(4,1)
);


-- 11.1
alter table Student add Nation VARCHAR(20);
SELECT * from Student;

-- 11.2
alter table Student drop Nation;
select * from Student;

-- 11.3
INSERT into Grade VALUES('2001110','3',80);
select * from Grade;

-- 11.4
update Grade set Gmark = 70 where Sno = '2001110';
select * from Grade;

-- 11.5
delete from Grade where Sno = '2001110';
select * from Grade;

-- 11.6
create index IX_Class on Student(Clno ASC);

-- 11.7
drop index IX_Class on Student;

插入书本中的数据

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
44
45
46
47
48
49
50
51
52
53
-- 插入Class数据
insert into class values
('00311','计算机软件','2000',120,'2000101'),
('00312','计算机应用','2000',140,'2000103'),
('01311','计算机软件','2001',220,'2001103');
SELECT * from Class;


-- 插入Course数据
insert into Course values
('1','数据库',4),
('2','离散数学',3),
('3','管理信息系统',2),
('4','操作系统',4),
('5','数据结构',4),
('6','数据处理',2),
('7','C语言',4);
SELECT * from Course;


-- 插入Grade数据
insert into Grade values
('2000101','1',92),
('2000101','3',NULL),
('2000101','5',86),
('2000102','1',78),
('2000102','6',55),
('2000103','3',65),
('2000103','6',78),
('2000103','5',66),
('2000104','1',54),
('2000104','6',83),
('2001101','2',70),
('2001101','4',65),
('2001102','2',80),
('2001102','4',NULL),
('2000103','1',83),
('2000103','2',76),
('2000103','4',56),
('2000103','7',88);
SELECT * from Grade; 

-- 插入student数据
insert into Student values
('2000101','李勇','男',20,'00311'),
('2000102','刘诗晨','女',19,'00311'),
('2000103','王一鸣','男',20,'00312'),
('2000104','张婷婷','女',21,'00312'),
('2001101','李勇敏','女',19,'01311'),
('2001102','贾向东','男',22,'01311'),
('2001103','陈宝玉','男',20,'01311'),
('2001104','张逸凡','男',21,'01311');
select * from Student;