第11章 数据库应用实践指导 - 图文

第11章 数据库应用实验指导

)

go

create table course (

course_id char(6) not null, dept_id char(6) not null, course_name char(20) null,

constraint PK_COURSE primary key nonclustered (course_id) ) Go

create table student (

stu_id char(6) not null, dept_id char(6) not null, name char(8) null, sex char(2) null, birthday datetime null, address char(40) null, totalscore int null, nationality char(8) null, grade char(2) null, school char(20) null, class char(16) null, major char(30) null,

constraint PK_STUDENT primary key nonclustered (stu_id) ) go

create table teacher (

teacher_id char(6) not null, dept_id char(6) not null, teacher_name char(8) null, rank char(6) null,

constraint PK_TEACHER primary key nonclustered (teacher_id) ) Go

create table teacher_course (

teacher_id char(6) not null, course_id char(6) not null, term_id char(2) null,

constraint PK_TEACHER_COURSE primary key (teacher_id, course_id) ) Go

create table student_teacher_course (

course_id char(6) not null, stu_id char(6) not null, teacher_id char(6) not null, term char(2) null, score int null,

constraint PK_STUDENT_TEACHER_COURSE primary key (course_id, stu_id, teacher_id) ) Go

alter table course /*修改表结构，添加一个外键*/

add constraint FK_COURSE_DEPARTMENT foreign key (dept_id) references department (dept_id) go

alter table course

add constraint FK_COURSE_DEPARTMEN_DEPARTME foreign key (dept_id) references department (dept_id) go

alter table student

add constraint FK_STUDENT_DEPARTMEN_DEPARTME foreign key (dept_id) references department (dept_id) go

alter table student_teacher_course

add constraint FK_STUDENT__STUDENT_T_COURSE foreign key (course_id) references course (course_id) go

alter table student_teacher_course

数据库原理及应用学习与实践指导 SQL Server 2012

add constraint FK_STUDENT__STUDENT_T_STUDENT foreign key (stu_id) references student (stu_id) go

alter table student_teacher_course

add constraint FK_STUDENT__STUDENT_T_TEACHER foreign key (teacher_id) references teacher (teacher_id) go

alter table teacher

add constraint FK_TEACHER_DEPARTMEN_DEPARTME foreign key (dept_id) references department (dept_id) go

alter table teacher_course

add constraint FK_TEACHER__TEACHER_C_TEACHER foreign key (teacher_id) references teacher (teacher_id) go

alter table teacher_course

add constraint FK_TEACHER__TEACHER_C_COURSE foreign key (course_id) references course (course_id) go

（2）修改表：把表student_teacher_course 中term列删除，并将score的数据类型改为float。实验操作步骤；在“新建查询”窗口中输入下列SQL语句：

USE teachingSystem GO

ALTER TABLE student_teacher_course DROP COLUMN term GO

ALTER TABLE student_teacher_course ALTER COLUMN score float GO

（3）删除表：使用SSMS删除表。在“对象资源管理器”窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要删除的表，选择“删除”命令或按下“DELETE”键。

使用T-SQL语句删除表

在数据库teachingSystem中建一个表Test1，然后删除。

USE teachingSystem GO

DROP TABLE Test1

说明：在删除表的时候可能会出现?删除对象?的对话框，如删除department表。这是因为所删除的表中拥有被其他表设置了外键约束的字段，如果删除了该表，必然对其他表的外键约束造成影响，数据库系统禁止删除被设置了外键的表。如图11-65所示。

图11-65 删除对象对话框

3．插入数据库记录和修改数据库记录

1）使用SSMS和T-SQL添加记录 给系部表（department），课程表（course），学生表（student）和教师表（teacher）,教

第11章 数据库应用实验指导

师开课表（teacher_course）,学生选课表（student_teacher_course）添加适当的记录。注意先后次序。先给无外键约束的表进行添加记录，然后再给有外键的表添加，否则无法添加。

使用SSMS添加记录：在“对象资源管理器”窗口中，展开“数据库”节点，再展开所选择的具体数据库节点，展开“表”节点，右键要插入纪录的表，选择“编辑前200行”命令，即可输入纪录值和修改记录。

使用T-SQL添加记录：如给教务管理系统数据库的表添加适量记录：

USE teachingSystem GO

/*添加课程表记录*/

INSERT course (course_id,dept_id,course_name) VALUES ('100001', '1001 ', '数据库原理')

INSERT course (course_id,dept_id,course_name VALUES (N'100002', N'1001', N'面向对象程序设计') /*添加系部表记录*/

INSERT department(dept_id, dept_name, dept_head, dept_phone, dept_addr) VALUES ('1001', '电子信息学院', '王老师', '1391001011', '图书馆7楼')

INSERT department(dept_id, dept_name, dept_head, dept_phone, dept_addr)VALUES ('1002', '机械学院', '刘老师', '1891020202', '文理大楼2楼')

INSERT department(dept_id, dept_name, dept_head, dept_phone, dept_addr)VALUES ('1003', '电气学院', '张老师', '1893774737', '华宁路2332号') /*添加学生表记录*/

INSERT student(stu_id, dept_id, name,sex, birthday, address, totalscore, nationality, grade, school, class, major) VALUES ('1201', N'1001', '高燕', '女', CAST(0x0000806800000000 AS DateTime),'上海', NULL, '汉族', '1 ', '电子信息', '1001', '计算机')

INSERT student(stu_id, dept_id, name,sex, birthday, address, totalscore, nationality, grade, school, class, major) VALUES ('1202', '1001', '马冰峰', '男', CAST(0x000081F400000000 AS DateTime), '安徽', NULL, '汉族', '1 ', '电子信息', '1001', '计算机') /*添加老师表记录*/

INSERT teacher (teacher_id, dept_id, teacher_name, rank) VALUES ('30101 ', '1001', '沈学东', '副教授')

INSERT teacher (teacher_id, dept_id, teacher_name, rank) VALUES ('30102 ', '1001', '贾铁军', '教授') /*添加老师开课表记录*/

INSERT teacher_course (teacher_id, course_id, term_id) VALUES ('30101 ', '100001', '1') INSERT teacher_course (teacher_id, course_id, term_id) VALUES ('30102 ', '100002', '1') /*添加学生选课表记录*/

INSERT student_teacher_course (course_id, stu_id, teacher_id, score) VALUES ('100001', '1201 ', '30101 ', 90)

INSERT student_teacher_course (course_id, stu_id, teacher_id, score) VALUES ('100002', '1201', '30102 ', 85) GO

2) 编写脚本程序来实现上述数据库及表 操作步骤；在“新建查询”窗口中输入下列SQL语句，并另存为test.sql文件，单击“执行”按钮即自动运行该脚本程序，实现数据库及相关表和记录的创建。

use master go

if exists (select * from sysdatabases where name='teachingSystem') --判断teachingSystem数据库是否存在，如果是就进行删除 drop database teachingSystem go

create database teachingSystem --创建数据库 on primary (

name=' teachingSystem',--主数据文件的逻辑名

fileName='D:\\ teachingSystem.mdf',--主数据文件的物理名 size=10MB,--初始大小 filegrowth=10% --增长率 )

log on (

name= ‘teachingSystem_log',--日志文件的逻辑名 fileName='D:\\ teachingSystem.ldf',--日志文件的物理名 size=1MB,

数据库原理及应用学习与实践指导 SQL Server 2012

maxsize=20MB,--最大大小 filegrowth=10% ) go

use teachingSystem go

if exists (select * from sysobjects where name='department')--判断是否存在此表 drop table department go

create table department

( …… --此处省略，同上； ) Go

if exists (select * from sysobjects where name= ‘course’)--判断是否存在此表 drop table course go

create table course

( …… --此处省略，同上； ) Go

--此处省略另外4个表的创建代码

INSERT course (course_id,dept_id,course_name) VALUES ('100001', '1001 ', '数据库原理') ...... –省略其他数据的创建

INSERT department(dept_id, dept_name, dept_head, dept_phone, dept_addr) VALUES ('1001 ', '电

子信息学院', '王老师', '1391001011', '图书馆7楼')

...... –省略其他数据的创建

INSERT student(stu_id, dept_id, name,sex, birthday, address, totalscore, nationality, grade, school,

class, major) VALUES ('1201', N'1001', '高燕', '女', CAST(0x0000806800000000 AS DateTime),'上海', NULL, '汉族', '1 ', '电子信息', '1001', '计算机')

...... –省略其他数据的创建 GO

4．数据查询方法

从数据库中检索出所需要的数据和实现方法。如何使用SQL的SELECT语句的WHERE子句进行比较，BETWEEN、LIKE关键字的查询，使用ORDER BY子句对SELECT语句检索出来的数据进行排序，并用GROUP BY、HAVING子句和函数进行分组汇总。

在SQL Server Management Studio中执行SELECT查询语句，如图11-66所示。 启动“SQL Server Management Studio”，并在“树”窗格中单击“表”节点，数据库中的所有的表对象将显示在内容窗格中。右击“对象资源管理器”的“数据库”中的某一表，在菜单中选择“打开表”命令 。查询设计器中的四个窗格：

关系图窗格：用于向选择查询中添加表或视图对象以及选择输出字段，并允许相关联的表连接起来。如果看不到该窗口，请单击工具栏上的“显示/隐藏关系图”按钮。

条件窗格：用于设置显示字段、排序结果、搜索以及分组结果的选项。如果看不到该窗口，请单击工具栏上的“显示/隐藏条件窗格”按钮。

SQL窗格：用于输入和编辑所有的SELECT语句。如果看不到该窗口，请单击工具栏上的“显示/隐藏SQL窗格”按钮。