第一部分、SQL Server基础-建立数据库和数据表
1.1 建立数据库和数据表
本节介绍如何使用SQL Server Management Studio及查新分析器建立数据库和数据表。首先需要在自己的台式机或者笔记本电脑上安装SQL Server 数据库引擎和SQL Server Management Studio两个组件。这两个组件有些安装包是放在一起的，有些是分开的，选择安装包时注意区分。本书使用的是SQL Server 2012版本，下载地址为：https://www.microsoft.com/zh-cn/download/details.aspx?id=29062，在下载页面有下载包的详细说明，大家可以根据自己机器的情况安装32位或者64位的版本，一般安装Express版本就可以了，如果需要学习数据仓库技术，可以安装Advanced版本。安装的过程很简单，一般都是跟着安装的界面点击“下一步”，需要注意的是在安装过程中需要选择“Windows和SQL Server”的两种用户验证方式，并设置数据库管理员sa的密码。SQL Server的身份认证方式在后续的Web开发中会使用到。
1.1.1 图形化方式建立数据库和数据表
1、新建数据库
安装SQL Server 数据库引擎和SQL Server Management Studio成功后，启动SQL Server Management Studio，出现如图1.1所示的界面：
图1.1 登录SQL Server
选择“Windows 身份认证”或者“SQL Server身份认证”，“Windows 身份认证”不需要输入密码，因为该认证方式是以Windows操作系统的管理员的身份登录数据库引擎，操作系统管理员拥有对其所管理的软件的所有权限。“SQL Server身份认证”需要输入在安装数据库引擎时，自定义的密码。登录数据库引擎成功后，出现SQL Server Management Studio的操作主界面，如图1.2所示：
图1.2 SQL Server Management Studio操作主界面
在上图的“数据库”处，点击鼠标右键，在弹出的快捷菜单中选择“新建数据库”，出现新建数据库的界面，如图1.3所示：
图1.3 新建数据库
在“数据库名称”处，输入“StdMng2020”作为数据库的名称，确定数据库名称后，数据库的mdf文件和log文件的名称会自动出现，mdf文件和log文件均存储在“D:\Data”目录下。将数据库的主文件（mdf文件）和日志文件（log文件）存放到指定而不是默认的目录是一个好的习惯，可以避免数据库的安装升级导致的数据库丢失的情况。点击“确定”按钮确认数据库的创建。
2、建立数据表
在SQL Server Management Studio的左侧数据库列表中找到刚刚新建的数据库“StdMng2020”，点击“+”号，展开数据库，在“表”处点击鼠标的右键，在出现的快捷菜单中选择“新建表”，出现如图1.4所示的界面：
图1.4 新建数据表
在定义表结构处，输入如表1.1所示的内容，定义t_Student表的结构：
表1.1 t_Student表结构
|
列名 |
数据类型 |
允许Null值 |
说明 |
|
Sno |
varchar(50) |
|
学生的学号，可变字符类型。主码。和t_SC表中的Sno形成外键关系，为被参考对象。 |
|
Sname |
varchar(50) |
|
学生的姓名，可变字符类型。 |
|
SGender |
varchar(2) |
|
学生的性别，可变字符类型。 |
|
SBirth |
datetime |
|
学生的出生日期，日期时间型。 |
|
Sdept |
varchar(50) |
√ |
学生所在的系别，可变字符类型。外键。和t_Sdept表中的SdeptId字段形成外键关系，为参考对象。表示学生只能属于一个已经存在的系别，或者为Null，表示学生不属于任何系别。 |
|
SImage |
varbinary(MAX) |
√ |
学生的登记照，blob（二进制大对象类型）。 |
|
Sage |
|
√ |
学生的年龄，计算列，由SBirth导出。计算公式为：(datediff(year,sbirth,getdate()))。 |
计算Sage的公式定义位置如图1.5所示：
图1.5 Sage计算列公式定义
datediff和getdate()均为SQL Server数据库引擎的内置函数，getdate()表示当前的日期，datediff(year,sbirth,getdate())表示取当前日期和sbirth值的差值，以年为单位。这样求得的学生年龄可能不太准确。比如说，如果今天是2020年4月3日，那么只要是1998年出生的，不管那一天，都是22岁，大家可以考虑计算学生年龄更加准确的公式。设置Sno字段为主码的操作如图1.6所示：
图1.6 设置Sno为主码
设置主码完成后，点击“保存”按钮，以“t_Student”为名称保存学生表。
按如表1.2 所示的内容定义系别表结构，以SdeptID为主码，以t_Sdept为名称保存。
表1.2 t_Sdept表结构
|
列名 |
数据类型 |
允许Null值 |
说明 |
|
SdeptID |
varchar(50) |
|
系别编号，可变字符类型。主码。和t_Student表中的Sdept形成外键关系，为被参考对象。 |
|
SdeptName |
varchar(50) |
|
系别的名称，可变字符类型 |
按如表1.3 所示的内容定义课程表结构，以Cno为主码，以t_Course为名称保存。
表1.3 t_Course表结构
|
列名 |
数据类型 |
允许Null值 |
说明 |
|
Cno |
varchar(50) |
|
课程的编号，可变字符类型。主码。和t_SC表中的Cno形成外键关系，为被参考对象。和自身的CPno形成外键关系，为被参考对象。 |
|
CName |
varchar(50) |
|
课程的名称，可变字符类型。 |
|
CPno |
varchar(50) |
√ |
课程的先修课，可变字符类型。外键。和自身的Cno形成外键关系，为参考对象。表示课程的先修课必须是一门已经存在的课程，或者为NULL，表示该课程没有先修课。 |
|
CCredit |
smallint |
|
课程的学分，16位短整型。 |
按如表1.4 所示的内容定义学生选课表结构，以（Sno、Cno）为主码，以t_SC为名称保存。同时选中Sno、Cno的方法是按住“ctrl”键不放，然后鼠标依次点击Sno和Cno，保证同时选中后，然后点击设置主键的按钮。
表1.4 学生选课表
|
列名 |
数据类型 |
允许Null值 |
说明 |
|
Sno |
varchar(50) |
|
学生的学号，可变字符类型。主属性。外键。和t_Student表中的Sno形成外键关系，为参考对象。表示只能是一个已经存在的学生才能选择课程，并且有考试成绩，由于为主属性，故不能为NULL。 |
|
Cno |
varchar(50) |
|
课程的编号，可变字符类型。主属性。外键。和t_Course表中的Cno形成外键关系，为参考对象。表示只能是一门已经存在的课程才能被学生选择，并且有考试成绩，由于为主属性，故不能为NULL。 |
|
Grade |
smallint |
|
学生所选课程的考试成绩，16位短整型。 |
3、建立表之间的关系。
1）、设置系别和学生之间的一对多关系，外键为t_Student表中的Sdept，被t_Sdept表中的SdeptID约束。选中“t_Student”表后，点击鼠标右键，在出现的快捷菜单中选择“设计”，出现如图1.7所示的界面：
图1.7 t_Student表设计界面
点击关系按钮后，在出现的关系设置界面，设置系别和学生的关系，如图1.8所示：
图1.8 设置系别和学生之间的关系
点击保存按钮，保存所设置的关系。
2）、设置先修课和课程之间的一对多关系，外键为t_Course表中的CPno，被t_Couese表中的Cno约束。设置过程如图1.9所示：
图1.9设置先修课和课程之间的关系
3）、学生选课表和学生及课程之间的多对多关系。外键为t_SC表中的Sno，被t_Student表中的Sno约束；外键为t_SC表中的Cno，被t_Course表中的Cno约束。设置过程如图1.10所示：
图1.10 学生选课表和学生及课程之间的关系
保存完成后，鼠标右键点击“数据库关系图”，在出现的快捷菜单中选择“新建数据库关系图”，在出现的“添加表”对话框中，依次选择“t_Student”、“t_Sdept”、“t_Course”、“t_SC”四个表，点击确定，出现如图1.11所示的数据表之间的关系图：
图1.11 数据表之间的关系图
若图形如1.11所示，表示数据表之间的关系建立是正确的。
4）、为学生表建立以下三个约束
学生姓名的唯一键约束，右键点击t_Student表，在出现的快捷菜单中选择“设计”，出现如图1.12所示的界面，在工具栏上点击“管理键和索引”，出现如图1.13所示的界面，按图1.13所示，建立学生姓名的唯一键约束。
图1.12 学生表设计界面
图1.13 设置学生姓名为唯一键约束
注意唯一键是约束，不是主码，不要和主键的概念混淆。唯一键是只不允许有重名的学生，是数据完整性的一种保证，注意唯一键是可以允许整个表的学生姓名中，有一个可以为NULL的。学生姓名的唯一键设置完成后，关闭对话框。在t_Student表的设计界面，点击工具栏上的“管理Check约束 ”按钮，按图1.14所示，分别为学生表的性别字段及出生日期字段设置CHECK约束，从而保证性别字段只能输入“男”或者“女”，出生日期在“1990-01-01”和“2000-01-01”之间。
图1.14 Check约束设置
Check约束是保证用户自定义完整性的一个重要手段，是数据库中数据有效性的重要保证。
1.1.2 SQL语句方式建立数据库和数据表
本节演示使用SQL语句建立数据库及数据表，关于create database和create table的具体语法本节不做深入的讨论，大家可以查阅官方文档，获得更详细的帮助（最好在安装数据库引擎时，选择安装SQL Server 2012 联机丛书，这样在SQL Server Management Studio的帮助菜单中，就可以获得SQL语句的详尽帮助）。
如果已经按上节的图形化方式建立了StdMng2020数据，请将其删除，再进行本节的演练。
1、建立数据库
在工具栏点击“新建查询”，打开一个空的查询分析器，输入以下SQL语句，建立StdMng2020数据库。
--创建数据库 create database StdMng2020 on (name = StdMng2020, FILENAME ='d:\Data\StdMng2020.mdf') LOG ON (name = StdMng2020_Log , FILENAME ='d:\Data\StdMng2020.ldf'); GO
注意SQL语句在SQL Server中的大小写是不区分的，按F5键，或者ALT+X，执行SQL语句。执行成功后，刷新数据库，确认StdMng2020数据库建立成功。
2、创建数据表
在同一个查询分析器中，将上述的建立数据库的SQL语句注释掉（CTRL+K+C，取消注释为CTRL+K+U）。初学者在此经常犯错，在没有注释前面语句的情况下，继续执行后面的SQL语句，会导致前面的SQL语句重复执行，出现一些不必要的错误。在同一个查询分析器中执行多个SQL语句块的一个好习惯就是将与本次执行无关的SQL语句块删除，或者注释掉。在查询分析器中，输入以下的SQL语句，依次建立“t_Sdept”、“t_Student”、 “t_Course”、“t_SC”4个数据表：
-保证当前数据库为StdMng2020 use StdMng2020 --建立系别表 create table t_Sdept ( SdeptID varchar(50) primary key, SdeptName varchar(50) not null ) --建立学生表 create table t_Student ( sno varchar(50) primary key, --定义姓名为唯一键约束 sname varchar(50) unique, SGender varchar(2) not null --check约束，性别只能是‘男’或者‘女’ check(SGender in('男','女')), SBirth datetime not null --check约束，出生日期在'1990-01-01' 和'2000-01-01'之间 check (SBirth between '1990-01-01' and '2000-01-01') , Sdept varchar(50) null, SImage varbinary(max) null --定义外键 foreign key (sdept) references t_sdept(SDeptID) ) --添加Sage为计算列 alter table t_Student add Sage as datediff(year,sbirth,getdate()) --建立课程表 create table t_Course ( Cno varchar(50) primary key, CName varchar(50) not null, CPno varchar(50) null, CCredit smallint not null, --定义外键 foreign key (CPno) references t_Course(Cno) ) --建立学生选课表 create table t_SC ( Sno varchar(50), Cno varchar(50), Grade smallint , --定义主键 primary key (Sno,Cno), --定义外键 foreign key (Sno) references t_Student(Sno), foreign key (Cno) references t_Course(Cno) )
执行这段语句请注意以下几个问题：
- 建立表的语句次序不要错，按“t_Sdept”、“t_Student”、 “t_Course”、“t_SC”的次序，否则外键引用可能会出错。
- 每建立一个表，就执行建立该表的语句，可以利用SQL Server 查询分析器的特点，将某段SQL语句块选中（鼠标拖放操作），然后执行，这时，不论其它语句块是否处于注释状态，所执行的语句只有选中的语句块。
- 养成写注释的好习惯，对SQL语句块中的关键语句或者执行逻辑写注释，一个是便于阅读，第二也是一个整理思路的过程。团队开发中，自己写的代码往往也需要给团队中的其它成员阅读，所以清晰的注释有助于大家的交流。不要认为代码是你自己写的，只给自己看，就不用写注释，人的大脑是会遗忘的，如果没有注释，2个月后，再来看自己写的代码，保证已经不认为这个代码是自己写的，或者非常疑惑，当时自己为什么要这些写。
建立4个表的语句全部执行完成后，再次建立数据库关系图，如果得到的关系图和图1.XX一致，则说明建立数据表是成功的。
1.1.3 插入测试数据
为方便后续章节的SQL语句的学习，需要在数据表中插入一些记录，在同一个查询分析器中，继续输入以下的语句，然后执行，保证以下数据全部插入到对应的数据表中。
--插入系别表 insert t_Sdept(SdeptID,SdeptName) values('cs','计算机系') insert t_Sdept(SdeptID,SdeptName) values('IS','信息技术系') insert t_Sdept(SdeptID,SdeptName) values('MA','数学系') insert t_Sdept(SdeptID,SdeptName) values('CHS','化学系') --插入课程表，注意插入课程的次序 insert t_Course(Cno,CName,Cpno,Ccredit) values('2','数学',null,2) insert t_Course(Cno,CName,Cpno,Ccredit) values('6','数据处理',null,2) insert t_Course(Cno,CName,Cpno,Ccredit) values('4','操作系统',6,3) insert t_Course(Cno,CName,Cpno,Ccredit) values('7','PASCAL语言',6,4) insert t_Course(Cno,CName,Cpno,Ccredit) values('5','数据结构',7,4) insert t_Course(Cno,CName,Cpno,Ccredit) values('1','数据库',5,4) insert t_Course(Cno,CName,Cpno,Ccredit) values('3','信息系统',1,4) --插入学生记录 insert t_Student (Sno,sname,sGender,sBirth,sdept,sImage) values ('200215121','李勇','男','1994-04-13','CS',null) insert t_Student (Sno,sname,sGender,sBirth,sdept,sImage) values ('200215122','刘晨','女','1995-07-16','CS',null) insert t_Student(Sno,sname,sGender,sBirth,sdept,sImage) values('200215123','王敏','女','1996-02-14','MA',null) insert t_Student (Sno,sname,sGender,sBirth,sdept,sImage) values('200215125','张立','男','1995-12-14','IS',null) --插入选课记录 insert t_sc values('200215121','1',92) insert t_sc values('200215121','2',85) insert t_sc values('200215121','3',88) insert t_sc values('200215122','2',90) insert t_sc values('200215122','3',80)
可以在查询分析器中输入”select * from t_Student”检查学生表的数是否插入正确，以同样的语句检查其它表中的数据插入情况。
点击“保存”按钮，将整个查询分析器中的SQL语句保存为一个以.sql为扩展名的文件到磁盘上。然后删除StdMng2020数据库。在SQL Server Management Studio中打开刚刚保存的文件，直接执行整个文件的内容，执行成功后，检查数据库、数据表、数据表之间的关系及各个表中的数据是否正确。
比较图形化方式和SQL语句方式建立数据库和表，各有优势。图形化方式简单直观，不需要理解和记忆建立数据库和表的SQL语句和语法，直接在图形界面上完成一些输入和设置，就可以完成数据库的设计。SQL语句方式灵活可控，使用sql脚本文件，就可以轻松的在不同的数据库引擎上，完成数据库结构的还原。实际应用过程中，两种方式经常交替使用，各取所长。作为计算机类专业的学生，SQL语句方式建立数据库和表的方式是必须掌握的。
浙公网安备 33010602011771号