创建数据库&&数据插入、更新与删除

创建数据库和创建表

一、实验目的

（1）熟悉和掌握数据库的创建和连接方法；

（2）熟悉和掌握数据库表的建立、修改和删除；

（3）加深对表的实体完整性、参照完整性和用户自定义完整性的理解。

二、实验内容

（1）创建一个教学数据库，数据库的名称为MIS；

（2）创建MIS数据库中的5个关系模式，要求包含完整性约束的定义。

• 系（系编号，系名称）
• 教工（教工号，姓名，性别，职称，工资，系编号）
• 学生（学号，姓名，性别，出生年月，专业，家庭地址，系编号）
• 课程（课程编号，课程名称，学时，系编号）
• 成绩（学号，课程编号，分数）

三、实验平台

操作系统：Windows x64 

DBMS：SQL Server 2016 

四、代码清单

PS：go的命令可以不要，“go”是SQL Server2000批处理的结束标志，不是SQL语句。

/* 注释：实验一中文脚本.sql 创建数据库  */
create  database MIS
go
/*  打开数据库   */
use MIS
/*  创建5个表  */
create table 系
(系编号  smallint not null ,
系名称   char(12),
primary key (系编号))
go
create table 课程(
课程编号 char(8)  not null,
课程名称  char(16),
学时     smallint,
系编号   smallint,
primary key (课程编号),
foreign key  (系编号) references 系(系编号))
go
create table 教工(
教工号 smallint,
姓名 char(8),
性别 char(2),
职称 char(10),
工资 numeric(8,2),
系编号  smallint, 
primary key (教工号),
foreign key (系编号)references 系(系编号))
go
create table 学生(
学号 char(6),
姓名 char(8),
性别 char(2),
出生年月  datetime, 
系编号  smallint, 
primary key (学号),
foreign key (系编号) references 系(系编号))
go
create table 成绩( 
学号 char(6)  not null,   
课程编号 char(8)  not null,   
分数 numeric(4,1), 
primary key (学号 ,课程编号), 
 foreign key (学号) references 学生(学号), 
 foreign key (课程编号) references 课程(课程编号)) 
go

注意：当多个SQL语句执行时，可能出现的逻辑错误，即就单个语句来说，语句没有错误，但多条语句顺序执行时可能出错。例如，上述5个表创建的顺序必须是“先父后子”，而删除的顺序则是“先子后父”。

DROP TABLE  成绩

DROP TABLE  学生

DROP TABLE  教工

DROP TABLE  课程

DROP TABLE  系

数据插入、更新与删除

一、实验目的

（1）熟悉和掌握数据表中数据的插入、修改、删除操作和命令的使用。

（2）加深理解表的定义和数据更新的作用。

 二、实验内容

系

系编号	系名称
101	数学
102	计算机
103	外语
104	经济

课程

课程号	课程名	学时	系编号
C101	数学	68	101
C102	英语	85	103
C103	计算机	102	102
C104	经济学	51	104

教工

教工号	姓  名	性别	职称	工 资	系编号
2101	葛小平	女	教授	3420.00	101
2203	李长江	男	副教授	3190.00	102
2405	姜立伟	男	副教授	3140.00	104
2104	张丽丽	女	讲师	2243.00	101
2302	康立华	女	教授	3740.00	103
2205	王伟平	男	讲师	2130.00	102

成绩

学  号	课程编号	分数
991022	C101	88
991022	C102	67
992124	C101	77
992124	C102	95
992124	C103	45
994021	C104	87

学生

学  号	姓  名	性别	出生年月	系编号	族别
991022	田平平	女	08/05/1980	101	汉
992124	郭黎明	男	03/04/1981	102	汉
994021	何明慧	女	04/12/1982	104	回
991223	姜明明	男	12/05/1980	101	苗
993012	何漓江	男	10/05/1979	103	汉
992104	康纪平	女	03/04/1981	102	汉
994125	康嘉家	男	07/05/1980	104	汉

学生            

三、实验平台

操作系统：Windows x64 

DBMS：SQL Server 2016 

四、程序清单

（1）在5个表中插入数据

insert into 系 values(101,'数学')

insert into 系 values(102,'计算机')

insert into 系 values(103,'外语')

insert into 系 values(104,'经济')

go

insert into 课程 values('c101','数学',68,101)

insert into 课程 values('c102','英语',85,103)

insert into 课程 values('c103','计算机',102,102)

insert into 课程 values('c104','经济学',51,104)

go

insert into 教工 values('2101','葛小平','女','教授',3420.00,101)

insert into 教工 values('2203','李长江','男','副教授',3190.00,102)

insert into 教工 values('2405','姜立伟','男','副教授',3140.00,104)

insert into 教工 values('2104','张丽丽','女','讲师',2243.00,101)

insert into 教工 values('2302','康立华','女','教授',3740.00,103)

insert into 教工 values('2205','王伟平','男','讲师',2130.00,102)

go

insert into 学生 values('991022','田平平','女','08/05/1980',101)

insert into 学生 values('992124 ','郭黎明','男','03/04/1981',102)

insert into 学生 values('994021 ','何明慧','女','04/12/1982',104)

insert into 学生 values('991223 ','姜明明','男','12/05/1980',101)

insert into 学生 values('993012 ','何漓江','男','10/05/1979',103)

insert into 学生 values('992104 ','康纪平','女','03/04/1981',102)

insert into 学生 values('994125 ','康嘉家','男','07/05/1980',104)

insert into 学生 values('991134 ','包立琪','女','03/14/1981',101)

insert into 学生 values('994115 ','王海洋','男','04/13/1982',104)

insert into 学生 values('991354 ','王立平','女','12/05/1981',101)

go

insert into 成绩 values('991022','c101',88)

insert into 成绩 values('991022','c102',67)

insert into 成绩 values('992124','c101',77)

insert into 成绩 values('992124','c102',95)

insert into 成绩 values('992124','c103',45)

insert into 成绩 values('994021','c104',87)

insert into 成绩 values('994021','c102',78)

insert into 成绩 values('994021','c103',67)

insert into 成绩 values('991223','c101',66)

insert into 成绩 values('991223','c102',89)

insert into 成绩 values('993012','c102',93)

insert into 成绩 values('993012','c103',84)

（2） 完成实验操作的SQL语言

1）在系表中插入一行数据{105,’管理’}

insert into 系 values (105,'管理')

2）向教工表中插入一行数据{2001,’葛小平’,’女’,’教授’,3420.00,102}

insert into 教工 values (2001,’葛小平’,’女’,’教授’,3420.00,102)

3）向教工表中插入一个教工号、姓名和工资，数据是{2109,’田新民’,2650}

insert into 教工(教工号,姓名,工资)  values (2109,'田新民',2650)

4）将教工表的姓名和工资拷贝到一个酬金表中

创建一个酬金表

create table 酬金 (教工姓名 char(8), 工资  numeric(8,2))

将教工表中姓名和工资两列数据拷贝到酬金表中

insert into 酬金 (教工姓名,工资)

select 姓名,工资

from 教工

5） 将教工表中职称为“教授”的工资增加15%

update 教工  set 工资=工资*1.15  where  职称='教授'

6） 将教工表中职称不是教授的人，工资增加10%

 update 教工 set 工资=工资*1.1 where 职称 NOT LIKE '教授'

7） 从教工表中删除教工号为2101的教工

delete  from 教工 where 教工号 = '2101'

8） 删除酬金表的所有数据

delete  from 酬金

9） 删除酬金表

drop table  酬金

10）在系表中添加一个电话号码属性，属性类型为char(8)  

alter table 系 add 电话号码 char(8)

11）修改系表中电话号码属性的宽度为char(13) 

alter table 系 alter column 电话号码  char(13)

12）删除系表中电话号码属性

alter table 系 drop column 电话号码

13）在学生表中删除何漓江同学的信息，包括他选课的信息。

提示：先删除子表中的选课信息，再删除主表中的信息。

思考：为什么必须这样做？

delete  from 成绩 where 学号 = '993012' 

delete  from 学生 where 学号 = '993012'

回答：当数据表之间存在外键关联的情况下，假如说直接将父表删除，结果将会显示失败。原因是直接删除的话，将会破坏表的参照完整性。如果必须要删除，可以先删除与它关联的子表，再删除父表，只是这样会同时删除两个表中的数据。