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
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
| -- 创建数据库 --所用的数据库复制到navicat上点击运行可直接创建实验所用的数据库
create database kk;
use kk;
-- 创建课程表c
CREATE TABLE c (
cno char(10) NOT NULL COMMENT '课程号',
cn varchar(45) NOT NULL COMMENT '课程名',
ct int NOT NULL COMMENT '课时',
PRIMARY KEY (cno)
);
-- 创建学生表s
CREATE TABLE s (
sno char(10) NOT NULL COMMENT '学号',
sn varchar(45) NOT NULL COMMENT '姓名',
sex enum('男','女') NOT NULL DEFAULT '男' COMMENT '性别',
age int NOT NULL COMMENT '年龄',
maj varchar(45) NOT NULL COMMENT '专业',
dept varchar(45) NOT NULL COMMENT '院系',
PRIMARY KEY (sno)
) ;
-- 创建选课表
CREATE TABLE sc (
sno char(10) NOT NULL COMMENT '学号',
cno char(10) NOT NULL COMMENT '课程号',
score decimal(5,2) DEFAULT NULL COMMENT '成绩',
PRIMARY KEY (sno,cno),
FOREIGN KEY (cno) REFERENCES c (cno),
FOREIGN KEY (sno) REFERENCES s (sno)
);
-- 创建教师表t
CREATE TABLE t (
tno char(10) NOT NULL COMMENT '教师号',
tn varchar(45) DEFAULT NULL COMMENT '姓名',
sex enum('男','女') NOT NULL DEFAULT '男' COMMENT '性别',
age int NOT NULL COMMENT '年龄',
prof varchar(10) NOT NULL COMMENT '职称',
sal decimal(6,2) NOT NULL COMMENT '工资',
maj varchar(45) NOT NULL COMMENT '专业',
dept varchar(45) NOT NULL COMMENT '院系',
PRIMARY KEY (tno)
);
-- 创建授课表tc
CREATE TABLE tc (
tno char(10) NOT NULL COMMENT '教师号',
cno char(10) NOT NULL COMMENT '课程号',
tcdate date NOT NULL COMMENT '开课日期',
PRIMARY KEY (tno,cno),
FOREIGN KEY (cno) REFERENCES c (cno),
FOREIGN KEY (tno) REFERENCES t (tno)
) ;
-- 分别在以上表中插入数据，注意主表和从表
INSERT INTO c VALUES
('c1','Java程序设计',40),
('c8','控制理论',32),
('c5','数据库系统',56),
('c6','数据挖掘',32),
('c4','数据结构',64),
('c2','程序设计基础',48),
('c3','线性代数',48),
('c7','高等数学',60);
INSERT INTO s VALUES
('s1','王彤','女',18,'计算机','信息学院'),
('s10','韩义','男',19,'计算机','信息学院'),
('s11','杨青','男',19,'计算机','工学院'),
('s2','苏乐','女',20,'信息','信息学院'),
('s3','林欣怡','男',19,'信息','信息学院'),
('s4','陶然','女',18,'自动化','工学院'),
('s5','魏立','男',17,'数学','理学院'),
('s6','何欣荣','女',21,'计算机','信息学院'),
('s7','赵琳琳','女',19,'数学','理学院'),
('s8','李轩','男',19,'自动化','工学院');
INSERT INTO sc VALUES
('s1','c1',90.50),
('s1','c2',85.00),
('s2','c4',70.00),
('s2','c5',57.00),
('s2','c6',81.50),
('s2','c7',NULL),
('s3','c1',75.00),
('s3','c2',70.50),
('s3','c4',85.00),
('s4','c1',93.00),
('s4','c2',85.00),
('s4','c3',83.00),
('s4','c6',NULL),
('s5','c2',89.00),
('s5','c7',60.00),
('s7','c2',62.00),
('s7','c5',80.00),
('s7','c7',100.00),
('s8','c3',96.00),
('s8','c7',78.50);
INSERT INTO t VALUES
('t1','刘杨','男',40,'教授',3610.50,'计算机','信息学院'),
('t2','石丽','女',26,'讲师',2923.30,'信息','信息学院'),
('t3','顾伟','男',32,'副教授',3145.00,'计算机','信息学院'),
('t4','赵礼','女',50,'教授',4267.90,'自动化','工学院'),
('t5','赵希希','女',36,'副教授',3332.67,'数学','理学院'),
('t6','张刚','男',30,'讲师',3012.00,'自动化','工学院');
INSERT INTO tc VALUES
('t1','c1','2021-09-03'),
('t1','c2','2021-09-04'),
('t2','c5','2021-09-06'),
('t2','c6','2021-09-10'),
('t3','c2','2021-03-08'),
('t3','c4','2021-03-06'),
('t4','c3','2020-03-10'),
('t5','c7','2020-03-09'),
('t5','c8','2021-09-10');
-- 开始操作
--使用这个数据库
use kk;
--创建信息学院学生视图 s_view
create view s_view
as select * from s
where dept='信息学院';
--创建学生选课情况视图 s_sc_c_view，视图字段列表为学号 sno、姓名 sname、课程名 cname 及成绩 score
CREATE VIEW s_sc_c_view(sn,sname,cname,score)
AS SELECT s.sno,sn,cn,score
FROM s,c,sc
WHERE s.sno=sc.sno AND sc.cno=c.cno
--在信息学院的学生视图 s_view 基础上，创建计算机专业学生视图 s_maj_view
CREATE VIEW s_maj_view
AS SELECT * FROM s_view
WHERE maj='计算机';
--查看视图 s_view 的结构信息
DESCRIBE s_view;
--查看视图 s_view 的状态信息
SHOW TABLE STATUS LIKE 's_view';
--查看视图 s_view 的创建信息
SHOW CREATE VIEW s_view;
--查询视图 s_view 的元信息
select * from information_schema.VIEWS
where table_name='s_view';
--删除视图 s_view
DROP VIEW s_view;
--构建用于更新数据的学生视图 s_update_view，提供学生表中的所有信息
CREATE view s_update_view
AS SELECT * FROM s;
--使用 s_update_view 视图向学生表 s 中插入一条学生记录，学生记录中各字段信息为
（s10，韩义，男，19，计算机，信息学院）
INSERT INTO s_update_view
VALUES('s12','韩义','男','19','计算机','信息学院');
--使用 s_update_view 视图，将学号为 s10 的学生年龄修改为 20 岁
UPDATE s_update_view
SET age=20
WHERE sno='s10';
--删除视图 s_update_view 中学号为 s10 的数据
DELETE FROM s_update_view
WHERE sno='s10';
--构建一个提供工学院学生信息的 s_check_view 视图
并指定WITH CHECK OPTION 参数(条件检查参数，看是否满足创建语句中select语句的条件)
CREATE VIEW s_check_view
AS SELECT * FROM s
WHERE dept='工学院'
WITH CHECK OPTION;
--为学生表 s 的姓名字段（sn）建立普通索引 s_name_index，索引针对 sn 的前 6个字节且以降序方式排列
CREATE INDEX s_name_index
ON s(sn(6) DESC);
--为课程表 c 的课程名（cn）和学时（ct）字段建立复合唯一索引 c_cn_ct_index
CREATE UNIQUE INDEX c_cn_ct_index
ON c(cn,ct);
--假设已经在学生表 s 中增加了 TEXT 类型的学生基本信息列（info），为 info 列创建全文索引 s_info_index
CREATE FULLTEXT INDEX s_info_index
ON s(info);
--创建教室表 classroom，要求包含自增主键 cid、教室编号 crno（非空字符串）、教室教学楼名称 cbn（非空字符串）
创建 classroom 表时，附加由教室编（crno）和教室名称（cbn）构成的普通唯一索引 cn_cb_index
CREATE TABLE classroom(
cid INT AUTO_INCREMENT,
crno VARCHAR(10) NOT NULL,
cbn VARCHAR(10) NOT NULL,
PRIMARY KEY(cid),
UNIQUE INDEX cn_cb_index(crno,cbn)
);
--为教师表 t 中的教师姓名（tn）添加索引 tn_index，索引长度为 6 且使用降序排列
ALTER TABLE t
ADD INDEX tn_index(tn(6) DESC);
--查看当前数据库下学生表 s 的索引信息
SHOW index from s;
--删除当前数据库学生表 s 上的索引 s_name_index
ALTER TABLE s
DROP INDEX s_name_index;
|
