项目2创建学生管理数据库和数据表课件

SQL Server 2005 数据库基础 模块 2-1 创建学生管理数据库学习目标工作任务任务一：在Management Studio中创建数据库student【任务分析】根据任务要求，要求使用SQL Server Management Studio的图形化界面创建名为student的数据库，并保存在C盘db文件夹下。数据库的具体属性如下表所示：表 2 1 student的数据文件和事务日志文件逻辑名称文件类型文件组系统文件名初始大小最大容量增长容量student_data数据文件primaryC:/db/student_data.mdf3MB30MB10MBstudent_log事务日志文件C:/db/student_log.ldf1MB10MB10%操作步骤1、打开Management Studio界面操作步骤2、打开“新建数据库”对话框操作步骤3、按要求设置数据库基本属性4、设置数据库的高级属性（排序规则）操作步骤5、创建完毕后，查看数据库。操作步骤知识说明：数据库的结构u逻辑结构 是指数据库由哪些性质的信息组成。SQL Server的数据库是由表、视图、索引、约束等各种不同的对象组成，它们构成了数据库的逻辑结构。u物理结构 也称为存储结构，表示数据库文件是如何在磁盘上存放的。数据库文件在磁盘上以文件的单位存放，由数据库文件和事务日志文件组成。任务2 用CREATE DATABASE语句创建数据库student 【任务分析】要求使用Management Studio创建名为student的数据库，同时设置数据库的相关属性。表 2 1 student的数据文件和事务日志文件逻辑名称文件类型文件组系统文件名初始大小最大容量增长容量student_data数据文件primaryC:/db/student_data.mdf3MB30MB10MBstudent_log事务日志文件C:/db/student_log.ldf1MB10MB10%关键代码 NAME=student_data,FILENAME=c:dbstudent.mdf,SIZE=3MB,MAXSIZE=30MB,FILEGROWTH=10MB 知识说明：CREATE DATABASE语句的基本格式CREATE DATABASE database_name ON PRIMARY（）LOG ON（）练习1在SQL Server中创建数据库，具体要求如下：u数据库名称为Test1。主要数据文件：逻辑文件名为Test1Data1，物理文件名为Test1Data1.mdf，初始容量为1MB，最大容量为10MB，递增量为1MB。u次要数据文件：逻辑文件名为Test1Data2，物理文件名为Test1Data2.ndf，初始容量为1MB，最大容量为10MB，递增量为1MB。u事务日志文件：逻辑文件名为Test1Log1，物理文件名为Test1Log1.ldf，初始容量为1MB，最大容量为5MB，递增量为20%。思考u假如没有对日志文件进行定义，那么日志文件的大小和数据文件有什么差别？u怎样创建有多个数据文件的数据库？模块 2-2建立学生管理系统中的数据表学习目标工作任务任务一：创建数据表student的表结构【任务分析】数据表包括表结构、约束以及记录等三个要件。这个任务我们主要是完成对数据表student表结构的创建，表结构如下表所示：表 2 5 student表的结构字段名类型是否允许为空备注Snochar(10)不允许学生编码snamechar(10)不允许姓名ssexchar(2)允许性别sbirthdaydatetime允许出生日期sscorenumeric(18,0)允许入学成绩classnochar(8)不允许班级编码任务一：创建数据表student的表结构【任务分析】可以使用两种方法来解决这个问题：1.利用Management Studio工具，在图形化的界面中创建student表结构。2.使用CREATE TABLE语句创建表结构。方法一：操作界面（操作步骤）1、展开需要创建表的数据库“student”并右击“表”，在弹出的快捷菜单中选择“新建表”命令。方法一：操作界面（操作步骤）2、在打开的表设计器对话框中，按照任务要求设置表student各列的列名、数据类型、允许空（非空约束）方法二：SQL 代码3、单击“SQL Server Management Studio”工具栏上的【新建查询】按钮 方法二：SQL 代码关键代码CREATE TABLE student(sno char(10)NOT NULL,sname char(10)NOT NULL,ssex char(2)NULL,sbirthday datetime NULL,sscore numeric(18,0)NULL,classno char(8)NOT NULL)知识说明：数据类型数据类型具体种类数值型整型tinyint、smallint、int、bigint 定点型decimal和numeric 浮点型float、real字符型普通字符型char、varchar、text Unicode字符型nchar、nvarchar、ntext日期时间型datatime、smalldatetime货币型money、smallmoney 二进制字符型binary、varbinary、image其他数据类型bit、cursor、timestamp、sql_variant、uniqueidentifier、table、xml知识说明：数据表创建CREATE TABLE table_name (|PRIMARY KEY|UNIQUE ,.n ):=column_name data_type COLLATE DEFAULT constant_expression|IDENTITY (seed,increment)NOT FOR REPLICATION .n 任务二 为student表添加数据【任务分析】本任务将要实现为任务1中创建的表student添加数据，student表中的部分数据如下表所示。其中每行代表表中的一条记录，而每列代表表中的一个字段。表 2 6 student表中的部分数据snosnamessexsbirthdaysscoreclassno0701011101孙晓龙男1988-6-04 479070101110701011102杜学艳女1987-6-10 475070101110701011103王迎男1989-2-11474070101110701011104安专林男1988-2-16 473070101110701011105赵飞男1987-1-23 45207010111方法一：在SQL Server Management Studio图形化界面中添加数据1、在打开的“对象资源管理器”窗口中，右击表student，在弹出的快捷菜单中选择“打开表”命令。方法一：在SQL Server Management Studio图形化界面中添加数据2、在打开的表内容标签页中，按照任务要求逐条输入student表中的每条记录。其中sbirthday字段是日期时间型（Datetime）数据，录入时可以使用斜杠（/）、连字符（-）或句号（.）作为年月日的分隔 方法二：使用INSERT INTO语句为数据表student添加数据3、单击“SQL Server Management Studio”工具栏上的“新建查询”按钮 关键代码INSERT INTO student (sno,sname,ssex,sbirthday,sscore,classno)VALUES (0701011101,孙晓龙,男,1988-6-4,479,07010111)方法二：使用INSERT INTO语句为数据表student添加数据知识说明 关系模型 关系模型是现在广泛采用的数据模型，它与先前曾使用的层次模型、网状模型相比具有显著的特点。它主要采用二维表格的方式来表示实体之间的关系，一个表就代表一个实体，表由行和列组成，一行代表一个对象，一列代表实体的一个属性。关系模型数据库也称为关系数据库。模块 2-3 设置学生管理系统 中的数据表的完整性 学习目标工作任务任务1 创建数据表student的约束u任务1.1：将数据表的sno字段设置为主键。u任务1.2：将数据表的ssex字段默认值设置为“男”。u任务1.3：将数据表的sscore字段设置在306到650的范围之间。任务1.1：将数据表的sno字段设置为主键【任务分析】主键约束是最重要的约束类型，它是每条记录的标识符，即可能是该记录与其它记录得以区别开来的唯一不同。如student表中有这样两条记录（0701011101，孙晓龙，男，1988-6-4，479，07010111），（0701011111，孙晓龙，男，1988-6-4，479，07010111）。它们之所以被视为两条不同的记录，就因为sno字段不一样。而sname字段由于现实世界中存在同名的人而可能不唯一，不能作为表的主键字段。每张数据表只能设置一个主键，在表中定义的主键列不能有重复的值。任务1.1：将数据表的sno字段设置为主键1、右击“对象资源浏览器”窗口中要创建主键的表名student，在弹出的快捷菜单上单击“修改”命令。任务1.1：将数据表的sno字段设置为主键2、在打开的【表-dbo.student】标签页上右击要设置为主键的列名sno，在弹出的快捷菜单中选择【设置主键】命令。任务1.2 将数据表的ssex字段默认值设置为“男”【任务分析】用户在插入某条记录时，如果没有为某个字段输入相应的值，该列的值就为空。如果该列设置了默认约束，如为student的ssex字段设置过默认值“男”，那么即使该字段没有输入任何值，记录输入完成后也会获得该字段的默认值“男”。u在ssex列属性的“常规”选项区中的“默认值或绑定”选项中输入默认值“男”。任务1.2 将数据表的ssex字段默认值设置为“男”任务1.3 将数据表的sscore字段设置在306到650的范围之间【任务分析】该校学生的入学成绩的最高分是650，最低分是306分，即在306到650的范围之间，该任务就要为sscore设置检查约束，如果输入的入学成绩超出这个范围，系统会认为输入的信息有误拒绝接收数据，从而保证了数据的完整性。u“CHECK约束表达式”对话框 任务1.3 将数据表的sscore字段设置在306到650的范围之间知识说明u数据完整性 数据完整性是指数据库中数据的正确性和一致性，它是衡量数据库设计好坏的一项重要指标。u分类实体完整性域完整性参照完整性用户自定义完整性任务2 创建数据表class的约束【任务分析】该任务是为数据表class建立各类约束、规则以实现数 据的完整性。要完成上述任务，则分别要为相关字段设置主键约束 和唯一约束，建立并绑定数据库的规则。该任务的实现，主要采用编写程序代码的方式。任务2 创建数据表class的约束【任务分析】具体还可以分为以下几个子任务：任务2.1：将数据表的classno字段设置为主键。任务2.2：将数据表的classname字段的值设成唯一。任务2.3：创建规则zydm_rule，并将它绑定在字段 pno 上，用来保证输入的专业代码只能是数字字符。任务2.1 将数据表的classno字段设置为主键【程序代码】USE studentGOALTER TABLE classADD CONSTRAINT pk_bhPRIMARY KEY CLUSTERED(classno)GOu【执行效果】任务2.1 将数据表的classno字段设置为主键任务2.2 将数据表的classname字段的值设成唯一【程序代码】USE studentGOALTER TABLE classADD CONSTRAINT uk_bjmcUNIQUE NONCLUSTERED(classname)GOu【执行效果】任务2.2 将数据表的classname字段的值设成唯一任务2.3 创建规则zydm_rule，并将它绑定在字段pno上，用来保证输入的专业代码只能是数字字符【程序代码】USE studentGOCREATE RULE zydm_ruleASch like 0-90-90-90-9GOEXEC sp_bindrule zydm_rule,class.pnoGO任务2.3 创建规则zydm_rule，并将它绑定在字段pno上，用来保证输入的专业代码只能是数字字符u【执行效果】知识说明：约束的类型约束类型说明约束对象关键字非空约束定义某列不接受空值列NOT NULL默认约束为表中某列建立默认值列DEFAULT检查约束为表中某列能接受的值进行限定列CHECK主键约束在表中定义主键来唯一标识每行记录行PRIMARY KEY唯一约束限制表的非主键列不允许输入重复值行UNIQUE外键约束可以为两个相互关联的表建立关系表与表之间FOREIGN KEY任务3 创建数据表student和class之间的关系，并建立数据库关系图显示两者关系【任务分析】SQL Server是关系数据库，它的基本对象之一是数据表，而数据表并不是彼此孤立的，而是存在着内在的关系。比如根据student表中的一条记录（0701011101，孙晓龙，男，1988-6-4，479，07010111）和class表中的记录（07010111，计应0711，0101），我们很容易得出孙晓龙是计应0711班的学生，就是由于这两张表是存在关系的，它们存在着共同的字段。为表student和表class创建关系就是通过class表中的classno字段和student表中的classno字段，建立两表间的连接。其中class.classno是class表中的主键，student.classno是表中的外键；本任务实质也就是为表student创建外键约束。关系图的建立可以使表间的关系以图形的方式加以显示，更加清晰的表现出两张表之间的关联。这样这个任务可以进一步分解为两个子任务：u任务3.1：创建数据表class和student之间的关系u任务3.2：建立数据库关系图显示表间关系任务3 创建数据表student和class之间的关系，并建立数据库关系图显示两者关系任务3.1：创建数据表class和student之间的关系1、展开“对象资源管理器”窗口中的节点表【dbo.student】，右击其子节点【键】，弹出快捷菜单。任务3.1：创建数据表class和student之间的关系2、单击【新建外键】命令，打开“外键关系”对话框。单击列表框中【表和列规范】左侧的“+”，展开子项目。子项目中为缺省定义。任务3.1：创建数据表class和student之间的关系3、单击【表和列规范】右边的按钮，打开“表和列”对话框。在“主键表”下拉列表框中选择“class”，选择列为“classno”；选择“外键表”“student”的列为“classno”，会自动生成关系名FK_student_class。任务3.1：创建数据表class和student之间的关系4、单击【确定】按钮，返回“外键关系”对话框。单击【关闭】按钮，返回“Management Studio”窗口。单击【保存】按钮，提示保存表之间的关系。任务3.2 建立数据库关系图显示表间关系1、鼠标右击“对象资源管理器”窗口的节点【student】的子节点【数据库关系图】，弹出快捷菜单。任务3.2 建立数据库关系图显示表间关系2、单击【新建数据库关系图】命令，在“Management Studio”中打开一个数据库关系图的标签页，并且激活了“添加表”对话框，在【表】列表框中选择表student和表class。任务3.2 建立数据库关系图显示表间关系3、单击【添加】按钮，并单击【关闭】按钮，关闭“添加表”对话框。在【关系图】的标签页中显示已添加的表student和class。任务3.2 建立数据库关系图显示表间关系4、单击【保存】按钮，会弹出“选择名称”对话框，输入关系图的名称，可以将数据库关系图保存在数据库中。知识说明 外键约束 外键约束可以保证表和表之间数据一致性，它通过主键和外键建立起表与表之间的关联。其中包含主键的表称为父表，包含外键的表称为子表。当父表中的数据发生改变时，子表中的数据也会发生相应的变化，以此来保证参照的完整性。用ALTER TABLE 语句也可以创建外键约束。模块 2-4 综合应用学习目标工作任务任务1 创建数据表student和class的基本结构【任务分析】该任务是为数据表student和class创建表结构，并建立相关约束来实现数据的完整性。这里与前一模块不同的是，数据表的约束在创建表时就在程序代码中一起定义了，而不是后续添加的。其中两张数据表的结构在项目1中已经设计完毕。任务1 创建数据表student和class的基本结构表 2 19 学生表的结构字段名类型约束备注snochar(10)主键学生编号snamechar(10)非空姓名ssexchar(2)只取男、女性别sbirthdaydatetime 出生日期sscoreNumeric(8)入学成绩classnochar(8)与班级表中classno外键关联班级编码表2 20 班级表的结构字段名类型约束备注classnochar(8)主键班级编号classnamechar(16)非空班级名称pnochar(4)与专业表中pno外键关联专业编号任务1 创建数据表student和class的基本结构【程序代码】CREATE TABLE class(classno char(8)CONSTRAINT pk_bjbh PRIMARY KEY,classname char(16)NOT NULL,pno char(4)NOT NULL,)任务1 创建数据表student和class的基本结构【程序代码】CREATE TABLE student(sno char(10)CONSTRAINT pk_xh PRIMARY KEY,sname char(10)NOT NULL,ssex char(2)CONSTRAINT uk_xb CHECK(ssex=男 OR ssex=女),sbirthday datetime NULL,sscore numeric(18,0)NULL,classno char(8)CONSTRAINT fk_bh REFERENCES class(classno)任务2 为数据表student和class添加记录【任务分析】该任务是使用INSERT语句为数据表student和class添加相关数据。表 2 21 学生表的部分记录snosnamessexsbirthdaysscoreclassno0701011101孙晓龙男1988-6-4 479070101110701011102杜学艳女1987-6-10 475070101110701011103王迎男1989-2-11474070101110701011104安专林男1988-2-16 473070101110701011105赵飞男1987-1-23 45207010111任务2 为数据表student和class添加记录表 2 22 班级表的部分记录classnoclassnamepno06010111计应0611 010106020111物流0611 020106020211会计0611 020207010111计应0711 010107010211网络0711 0102任务2 为数据表student和class添加记录【程序代码】USE studentGOINSERT INTO studentVALUES (0701011101,孙晓龙,男,1988-6-4,479,07010111)INSERT INTO studentVALUES (0701011102,杜学艳,女,1987-6-10,475,07010111)INSERT INTO studentVALUES (0701011103,王迎,男,1989-2-11,474,07010111)INSERT INTO studentVALUES (0701011104,安专林,男,1988-2-16,473,07010111)INSERT INTO studentVALUES(0701011105,赵飞,男,1987-1-23,452,07010111)任务2 为数据表student和class添加记录INSERT INTO classVALUES (06010111,计应0611,0101)INSERT INTO classVALUES (06020111,物流0611,0201)INSERT INTO classVALUES (06020211,会计0611,0202)INSERT INTO classVALUES (07010111,计应0711,0101)INSERT INTO classVALUES(07010211,网络0711,0102)知识说明省略VALUES清单的INSERT语句INSERT INTO table_name(column_name1,column_name2,column_namen)SELECT column_namenFROM table_nameWHERE search_conditions