Transcript 设计和创建学生成绩管理系统数据库表及数据编辑

《数据库应用技术》
—— 省级精品课程
http://172.16.38.204/index.asp
学习情境3
设计和创建学生成绩管理系统数据库的数据表及数据
编辑
主讲：余建军
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
技能目标：
能够使用SQL sERVER创建数据库表（包括
完整性的定义）
 能够使用SQL sERVER修改数据库表
 能够使用SQL sERVER进行数据编辑

知识目标：


掌握表、完整性等基本概念
掌握数据库设计的基本概念
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务1：学生成绩管理系统表设计（逻辑结构设计）
数据（1）：教师所需要数据
存放教师名及
口令
班级表
学生表
 教师所上班级
和课程
 学生成绩表（学
号、课程、成….）
课程号、课程名
和课程密码
 学生成绩临时表
教师表（教师编号、教师名、口令、二级学院编号）
教师授课表（教师编号、课程编号、班级号）
课程表（课程编号、课程名、课程密码、学分,开设学期，二级学
院编号）
班级表（班级号、班级名，二级学院编号）
学生表（学号、姓名、班级号、性别）
成绩表（学号、课程号、成绩）
成绩临时表（学号、课程号、成绩）
衢州职业技术学院
每张表
简单，
单一
网络数据库技术
http://172.16.38.204/index.asp
任务1：学生成绩管理系统表设计（逻辑结构设计）
数据（2）：学生所需要数据
存放学号及口令
（学生表增加口
令列）
 学生成绩表（学
号、课程、成….）
学生表、成绩表、课程表
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务1：学生成绩管理系统表设计（逻辑结构设计）
数据（3）：教学秘书需要数据
存放用户名
及口令
 学生表
课程表
成绩表
班级表（增加二级
学院列）
班级平均分、最好分、
最低分、及格率？ 学生
成绩总评及排名？
秘书表、班级表、学生表、课程表、成绩表、
二级学院表
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务1：学生成绩管理系统表设计（逻辑结构设计）
数据（4）：系统管理员需要数据：全部+
用户名+口令
---------用数据库管理员的帐号
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务1：学生成绩管理系统表设计（逻辑结构设计）
教师表（教师编号、教师名、口令，二级学院编号）
教师授课表（教师编号、课程编号、班级号）
班级表（班级号、班级名，二级学院编号）
学生表（学号、姓名、班级号、性别 、出生年月、口令）
课程表（课程编号、课程名、学分、课程密码、开设学期，二级学院编号）
成绩表（学号、课程号、成绩）
口令：
成绩临时表（学号、课程号、成绩）
涉及信息安全和用户
秘书表（用户名，口令）
二级学院表（编号，名称）
权限控制方法
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务1：学生成绩管理系统表设计（逻辑结构设计）
字段名
结构：字段（列）
数据  数据库 表
类型
长度
数据：记录
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务1：学生成绩管理系统表设计（逻辑结构设计）
1. 教师表（教师编号【字符，10】、教师名【字符，8】、口令【字符，8】，
二级学院编号）
“马文龙”， 1996000183，，12
2. 班级表（班级号【字符，8】、班级名【字符，20】，二级学院编号【字符，
2】）
31012331，“10网络1”，12
3.
学生表（学号【字符 10】、姓名【字符8】、班级号、性别【字符2】、
出生年月【日期型】、口令【字符，8】）
“郭晓菲”， 3101233113，“女”
4. 课程表（课程编号【字符，8】、课程名【字符，20】、学分【小整数】、课
程密码【字符：8】,开设学期【极小整数】，二级学院编号）
“网络数据库技术”，12312050，3学分,123456,3，12
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务1：学生成绩管理系统表设计（逻辑结构设计）
5. 教师授课表（教师编号【字符，10】、课程编号【字符，8】、班级号【字
符 ，8】）
6. 成绩表（学号【字符，10】、课程号【字符，8】、成绩【小整数】）
7. 成绩临时表（学号、课程号、成绩）
8. 秘书表（用户名【字符，10】，口令【字符：8】）
9.二级学院表（编号【字符：2】，名称【字符，16】）
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务2：学生成绩管理系统表创建
系统表：复制Model库
表：存储数据
用户表： 用户创建
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务2.1：学生成绩管理系统表创建(教师表)
1. 使用SQL-EM
教师表（教师编号【字符，10】、教师名【字符，
8】、口令【字符，8】，二级学院编号）
T （tno, tname ,tpd，dno）
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
使用SQL-EM
SQL-EM→数据库→表→右键→新建表
定义字段： 字段名、类型、长度
主键完整性
创建表
定义数据完整性
衢州职业技术学院
唯一完整性
外键完整性
非空完整性
默认完整性
检查完整性
网络数据库技术
http://172.16.38.204/index.asp
任务2.2：学生成绩管理系统表创建（学生表、课程
表、成绩表）
使用T-SQL
学生表（学号【字符 10】、姓名【字符8】、班级
号【字符8】 、性别【字符2】，出生年月【日期
型】、口令【字符，8】）
S （sno, sname , classno, ssex, sbirthday, spd）
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
create table S
(sno char(10) primary key,
sname char(8),
classno char(8),
ssex char(2),
sbirthday datetime,
spd char(8)
)
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识2.1]CREATE TABLE语句
语句格式：
CREATE TABLE [<数据库名>.]<表名>
(<列名> <数据类型> [<列级完整性约束>][,…n]
[<表级完整性约束>]）
主键(primary)
外键(foreign)
数据完整性约束
非空(not null)
默认(default)
检查完整性约束(check)
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识2.2]数据类型和主键
数值型：
bigint、int、smallint、tinyint
数据类型 字符型：char
时间日期型：datetime
数据完整性约束： 主键
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
课程表（课程编号【字符，8】、课程名【字符，20】、学
分【小整数】、课程密码【字符：8】，二级学院编号）
create table C
(cno char(8) primary key,
cname char(20),
credit tinyint,
Cpd char(8),
Semester tinyint，
Dno char(2))
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
成绩表（学号【字符，10】、课程号【字符，8】、
成绩【小整数】）
create table SC
(sno char(10),
cno char(8) ,
score smallint,
primary key(sno,cno))
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务2.3: 学生成绩管理系统表完整性定义—修改表
1. 学生表
姓名，出生年月，性别 不允许为空，密码
默认为12345678
2. 成绩表
成绩在0-100之间
学号、课程号 必须在学生表和课程表出现
3.课程表
学分1-20之间，密码默认12345678
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
学生表完整性定义：使用SQL-EM
非空：sname, ssex ,sbirthday
密码：spd值为“12345678”
练习：增加email字段且唯一
外键：sno (关系图，管理关系)
修改表名：指定表→右键→重命名
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
成绩表完整性定义：使用T-SQL
成绩0-100之间， 学号、课程号 必须在学生表和课
程表出现
1. ALTER TABLE sc WITH NOCHECK
ADD CONSTRAINT score_check
CHECK(score>=0 and score<=100)
2. ALTER TABLE sc
ADD CONSTRAINT fk_sc_s FOREIGN
KEY(sno) REFERENCES s(sno)
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识2.3]ALTER TABLE语句
语句格式：
ALTER TABLE [<数据库名>.]<表名>
{[ALTER <列名> <数据类型> [<列级完整性约束>][,…n]]
|ADD <列名> <数据类型> [<列级完整性约束>][,…n]
|DROP <列名> [,…n]
如： ALTER TABLE s
ADD postcode char(6) NULL
ALTER TABLE s drop column postcode
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[相关知识2.4]外键
外键：外键字段的值只能取另一表的主键字段的值
实例
s(sno,class,sname,sex,birthday,address,telephone,email)
c(cno,cname,credit)
参照表
sc(sno,cno,score)
外键表
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[拓展技能1]删除表
1. 使用SQL-EM
SQL-EM→数据库→表→右键→删除
2. 使用SQL语句
语句格式：
DROP TABLE <表名>
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
[拓展技能2]生成表脚本文件
表脚本文件：以文件方式保存的表结构的SQL语句，
扩展名.sql。
SQL-EM→数据库→右键→所有任务→生成SQL脚本
实例 生成数据库student表s、c、sc对应脚本文件
注意
选中“编写主键、外键、默认值和检查约束脚本”
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
任务3：在学生成绩管理数据库中输入和编辑数
据
结构： 字段
表
数据： 记录
插入
删除
修改
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
输入学生、课程、临时成绩表、成绩表
外部数据导入--》修改表结构--》数据检查--》
导入目标表（SELECT）--》修改、删除、插入数据
【要求】
输入学生信息
输入课程信息
输入和编辑成绩表--使用SQL语句
工具：EM 和查询分析器
【相关技能】插入、删除、修改（EM）
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
【相关知识3.1】输入和编辑成绩表--使用SQL语句
插入： INSERT
编辑记录
删除： DELETE
修改： UPDATE
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
输入成绩表----SQL
格式一：INSERT [INTO] <表名>[(<列名表>)]
VALUES(<值列表>)
注意：值列表必须与列名表一一对应
插入：学生表数据
格式二：INSERT [INTO] <目标表>[(<列名表>)]
SELECT <列名表> FROM <源表> WHERE <条件>
临时成绩表-----》成绩表（按课程号）
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
DELETE语句
语句格式：
DELETE [FROM] <表名> [WHERE <条件>]
删除临时成绩表数据（以课程号）
衢州职业技术学院
网络数据库技术
http://172.16.38.204/index.asp
UPDATE语句
格式一：UPDATE <表名>
SET <列名>=<表达式>[,…n] [WHERE <条件>]
实例 将项宇同学的密码改为？？？？。
格式二：UPDATE <目标表名>
SET <列名>=<表达式>[,…n]
FROM <源表名> [WHERE <条件>]
实例 将网络数据库技术课程学生的成绩加5分
衢州职业技术学院