第6次课表的创建与修改,完整性约束

1、表的创建与修改表的创建与修改, ,完整性约束完整性约束信息工程学院信息工程学院SQLSQL的产生和发展的产生和发展 最早的版本有IBM提出Structured Query Language 最新的版本是SQL-99 主流关系数据库产品采用的是在SQL-92标准的基础上自己扩展而来 SQL SERVER的语句叫Transact SQLTransact SQL Oracle的语句叫PL/SQL MySQL的SQL语句与其他的也有所不同SQLSQL的特点的特点 一体化一体化 集DDL、DML、DCL功能于一体。 高度非过程化高度非过程化 只需“做什么”，无须“怎么做”。 简洁简洁 接近自然语言 创建

2、表：CREATE TABLE 以多种方式使用以多种方式使用 命令式 嵌入到程序设计语言中SQLSQL的组成部分的组成部分 数据定义（数据定义（DDLDDL） 定义、删除、修改数据库（Database） 定义、删除、修改表(Table)、视图（View） 定义、删除、修改索引（Index） 数据操纵（数据操纵（DMLDML） 数据查询 数据增、删、改 数据控制（数据控制（DCLDCL） 用户访问权限的授予、收回 数据定义数据定义数据库定义数据库定义 创建数据库 CREATE DATABASE CREATE DATABASE ;CREATE DATABASECREATE DATABASE XSCJ

3、; 打开数据库 USEUSE ;USEUSE XSCJ; 删除数据库 DROP DATABASE DROP DATABASE ;DROP DATABASE DROP DATABASE XSCJ; 一个数据库在打开的时候是不能删除的SQLSQL语句创建数据库语句创建数据库 以下语句创建了一个名为TestDB的数据库。创建数据库的相关信息大家都可以从代码中读出来。CREATE DATABASE TestDB ON (NAME = TestDB, FILENAME = D:TestDB.mdf, SIZE = 20 MB, FILEGROWTH = 0)LOG ON (NAME = TestDB_L

4、og, FILENAME = D:TestDB_Log.ldf, SIZE = 5 MB, FILEGROWTH = 0)SQL语句大小写不敏感，用大写还是小写都能识别！语句大小写不敏感，用大写还是小写都能识别！建表示例：学生信息表建表示例：学生信息表创建这张表的步骤创建这张表的步骤确定每一列的数据类型确定每一列的数据类型创建无数据的空表创建无数据的空表添加数据添加数据学号姓名性别高考分数班级身份证号照片指纹9912101 李荣浩 男 388.513软工11234567890123456781312102 上官刘晨 男 44213信管11000000000000000020512103 陆盈盈

5、女 631.513电商1设我们现在要在数据库中创建下面的学生信息表，步骤是怎样的呢？假设我们现在要在数据库中创建下面的学生信息表，步骤是怎样的呢？数据库的数据类型数据库的数据类型 数值型 字符型 日期型 货币型 二进制型 数据类型在不同数据库产品中有差异，这里是指数据类型在不同数据库产品中有差异，这里是指SQL SQL SERVERSERVER中的数据类型中的数据类型确定数据类型确定数据类型学生信息表列名列名数据类型数据类型学号姓名性别高考分数班级身份证号照片指纹学号姓名性别高考分数班级身份证号照片指纹9912101 李荣浩 男 388.599软工112

6、34567890123456781312102 上官刘晨 男 44213信管11000000000000000020512103 陆盈盈女 631.507电商1定数据类型确定数据类型学号姓名性别高考分数班级身份证号照片指纹9912101 李荣浩 男 388.599软工11234567890123456781312102 上官刘晨 男 44213信管11000000000000000020512103 陆盈盈女 631.507电商1名列名数据类型数据类型理由理由学号Char(7)第一个数字可能是0，且数据的长度固定不

7、变姓名nvarchar(8) 可以有多种选择性别Nchar(2)固定长高考分数Decimal(5,1) 使用Numeric(5,1)是一模一样的班级Nvarchar(10) Unicode型所有字符都是2个字节身份证号Char(18)照片Varchar(50) 一般保存图片在服务器中的路径和文件名指纹image数值型数值型准确型准确型整数Int：4字节 Smallint：2字节 tinyint ：1字节小数 （建议使用准确小数型）（建议使用准确小数型）Numeric（p,q）或Decimal（p,q)近似型近似型（不建议使用）（不建议使用） Float：8字节 Real：4字节年龄选择什么类型

8、? Tinyint够不够？ 价格？ 3687.88 99.7 131452.99这组数据，什么类型合适？超过bigint的定义范围的整数该怎么解决？详细介绍见书上相关表格，这里只介绍常用的、需注意的字符型字符型 CHAR, VARCHAR, TEXT nCHAR, nVARCHAR, nTEXT1、姓名 CHAR( 10 ) 柴静 李荣浩 姓名2 varchar（10） 柴静 李荣浩 姓名 和 姓名2 这两列有什么区别？2、微博内容，需要记录日文，英文，中文，应该用什么数据 类型？3、中国大陆的手机号码，用什么类型？ 字符串类型的数据两端用单引号括起来，如how are you,1234等都是

9、字符串的数据 请注意数据库中字符和字符串都是用单引号 12313，2.343是数值型数据 数字型字符与数值用单引号来区别字符型字符型 日期时间型日期时间型 Datetime：8字节，年月日时分秒毫秒(例：2001/08/03 10:30:00.000 ) SmallDateTime：4字节，年月日时分 (例： 2001/08/03 10:30:00 ) 日期、时间的输入格式试列出实际生活中需要精确到毫秒和不需要精确到毫秒的时间数据。日期时间类型日期时间类型 l SQL Server的日期时间数据类型是将日期和时间合起来存储。l 日期的格式： Oct 25 2001 2001-10-25 200

10、1/10/25 20011025注：日期型数据使用单引号括起来注：日期型数据使用单引号括起来货币类型货币类型 货币数据类型表示货币值。货币数据存储的精确度固定为四位小数。 SQL92或SQL99没有对应的货币类型，Oracle等数据库产品中也没有货币类型。 建议使用decimal类型代替二进制类型二进制类型 Binary(n)：固定长度，n = 8000 Varbinary(n)：可变长度，n 8000 注：n为二进制数据的字节数 image：大容量、可变长二进制字符数据，可用于存储文件。网站中上传的图片一般情况下是如何保存的呢？学生信息表学生信息表创建这张表的步骤创建这张表的步骤1.1.确定

11、每一列的数据类型确定每一列的数据类型2.2.创建无数据的空表创建无数据的空表3.3.添加数据添加数据学号姓名性别高考分数班级身份证号照片指纹9912101 李荣浩 男 388.513软工11234567890123456781312102 上官刘晨 男 44213信管11000000000000000020512103 陆盈盈女 631.513电商1QLSQL语句创建表语句创建表CREATE TABLE CREATE TABLE ( null | not null , )；CREATE TABLE CREATE TABLE students( (sno

12、char(7) not null, , sname nvarchar(8) not null, , ssex nchar(2) not null, , score decimal(4,1) , ,class nvarchar(10) not null , ,sfzh char(18) not null , ,tp varchar(50) , ,zw image) )学生表结构学生表结构列名列名数据类型数据类型是否允许空是否允许空学号Char(7)不允许空姓名nvarchar(8)不允许空性别Nchar(2)不允许空高考分数Decimal(4,1)班级Nvarchar(10)不允许空身份证号Ch

13、ar(18)不允许空照片Varchar(50)指纹image默认默认null，可以不写，可以不写课堂练习课堂练习1 1：创建读者表：创建读者表参考答案：CREATE TABLE Reader (Reader_ID CHAR(6) not null,name NVARCHAR(8) not null,sex NVARCHAR(2) not null,birthdate SMALLDATETIME)reader列名列名数据类型数据类型是否允许空是否允许空描述描述reader_IDreader_ID Char(6)否读者编号rnamernameNvarchar(8)否读者姓名sexsexNvarch

14、ar(2)否性别birthdatebirthdate smalldatetime出生日期设置自动编号设置自动编号创建借阅表CREATE TABLE Borrow(Xh int not null identity(1,1),bID CHAR(10) not null,RID CHAR(6) not null,Borrowdate DATETIME not null)列名列名数据类型数据类型是否允许空是否允许空描述描述xhintintNot null序号，要求自动增长编号bIDChar(10)否图书编号rIDChar(6)否读者编号borrowdatedatetime否借阅日期borrowSSMS

15、SSMS图形工具表设计器建表创建数据表图形工具表设计器建表创建数据表选中要建表的数据库右键新建表创建Books表修改表也是同样的两种方式修改表也是同样的两种方式 用SSMS图形工具的表设计器直接修改，方法同刚刚讲的建表(同学们自己动手试试，此处不再讲解)修改表时可能系统不让你修改，思考下为什么会有这样的设置？修改表时可能系统不让你修改，思考下为什么会有这样的设置？ 用SQL 语句中的ALTER TABLE语句修改表此处默认是打钩的，也就此处默认是打钩的，也就是不让你修改表的结构，是不让你修改表的结构，要把勾去掉才能修改表要把勾去掉才能修改表基本表的修改基本表的修改假设我们刚刚创建的假设我们刚刚

16、创建的studentsstudents表要进行如下修改：表要进行如下修改： 增加一列：联系电话；增加一列：联系电话； 学号长度需要调整，学号长度需要调整，7 7位改为位改为1010位；位； 删除指纹列。删除指纹列。SQL语言使用ALTER TABLE命令可以实现： 增加新列：ADD 修改原有的列定义： ALTER COLUMN 删除列： DROP COLUMN 基本表的修改的语法基本表的修改的语法语法格式为： ALTER TABLE ADD ; -添加新列 ALTER TABLE ALTER COLUMN ; -修改已有列定义 ALTER TABLE DROP COLUMN ； -删除列基本表

17、的修改基本表的修改 - - - -学生表中增加一列学生表中增加一列“手机号码手机号码”ALTER TABLE 学生表ADD 手机号码 char(11);- - -学生表中学生表中“学号学号”列长度改为列长度改为1010位位ALTER TABLE 学生表Alter column 学号 char(10);- - -学生表中删除学生表中删除“指纹指纹”列列ALTER TABLE 学生表DROP COLUMN 指纹;课堂练习课堂练习2 2：修改表：修改表 Reader表中添加两列：职业profession、联系电话phone ALTER TABLE Reader ADD profession CHAR

18、(20), phone char(11) 将Reader表中的profession列修改为char(30)，联系电话改为char(13) ALTER TABLE Reader ALTER COLUMN profession CHAR(30) ALTER TABLE Reader ALTER COLUMN phone CHAR(13) 删除Reader表中的profession列和phone列 ALTER TABLE Reader DROP COLUMN profession ,phone15:06关于修改表的思考？关于修改表的思考？ Students表原先“学号”的数据类型为Char(10)，

19、现在改为char(7)是否可以？如何验证？ 若students表中已有数据，以下语句能否成功执行？ ALTER TABLE students ADD 手机号码 char(11) not null;注意：表中有数据时，表中有数据时， 增加的列不能增加的列不能not nullnot null 修改的长度不能小于原数据的长度修改的长度不能小于原数据的长度 数据类型不能转换的不允许修改数据类型不能转换的不允许修改删除表删除表 DROP TABLE , 删除students表（学生信息表） DROP TABLE students 删除book、reader、borrow表(一次删除多个表) drop t

20、able book,reader,borrow数据完整性数据完整性学生表学生表学号学号姓名姓名年龄年龄身份证身份证系别系别13011301nullNull李荣浩李荣浩刘强东刘强东马云马云陆盈盈陆盈盈18null1718330227199303174026330105199308300017330105199306061324nullX01X02X03X04系别号系别号系名系名X01计算机系计算机系X02信管系信管系X03软件系软件系通过设置约束约束，保证保证数据库中的数据是完整、一完整、一致、正确致、正确的系别信息表系别信息表关系完整性约束关系完整性约束 实体完整性实体完整性 主键（非空且不重

21、复非空且不重复） 参照完整性参照完整性 外键（表示表间列值的参照关系表示表间列值的参照关系） 用户自定义完整性用户自定义完整性 非空：不允许空值不允许空值 唯一：不可重复，允许空值不可重复，允许空值 检查：设置该列值的范围设置该列值的范围 缺省值（默认）：当新输入的数据没指定值时，当新输入的数据没指定值时，就将默认值存储在数据表中就将默认值存储在数据表中主键约束主键约束（PRIMARY KEY） 1.1.主键（主键（PRIMARY KEYPRIMARY KEY）约束）约束 PRIMARY KEY约束用于定义基本表的主键，它是惟一确定表中每一条记录的标识符，又称主码。其值不能为NULL，也不能重

22、复，以此来保证实体的完整性。 下表中（学号）为主码，则学号不能取空值，亦不得重复。学号学号姓名姓名性别性别身份证身份证系别系别13011301nullNull李荣浩李荣浩刘强东刘强东马云马云陆盈盈陆盈盈男男男男女女女女330227199303174026330105199308300017330105199306061324nullX01X02X03X04学号学号姓名姓名性别性别身份证身份证系别系别13011301nullNull李荣浩李荣浩刘强东刘强东马云马云陆盈盈陆盈盈男男男男女女女女33022719930317402633010519930830001733010519930606132

23、4nullX01X02X03X04方法 （1） SQL Server管理平台操创建选择多个字段共同做为主键需要选择多个字段共同做为主键需要同时选中这几个字段，如图所示同时选中这几个字段，如图所示创建主键创建主键方法二：使用方法二：使用SQLSQL语句创建主键语句创建主键(1)建表时在字段上创建create table students(sno char（7） primary key,)(2)建表时在末尾非标准创建create table students( (sno char(7) not null , zw image，primary key (sno) )(3)建表时在末尾标准创建语法：C

24、ONSTRAINT 主键名 PRIMARY KEY （列名）上面的最后一句改成： CONSTRAINT PK_Students primary key (sno) 关于主键名，可以由创建者自己取，一般系统习惯取名为PK_表名此时设为主键的字段可不必再写此时设为主键的字段可不必再写not null，因为隐含了不为空的信息，因为隐含了不为空的信息constraint knstreint n.强制, 约束组合主键只能用后面两种方法定义组合主键只能用后面两种方法定义唯一性约束唯一性约束(unique)(unique) 惟一性约束用于指定一个或者多个列的组合值具有惟一性，以防止在列中输入重复的值 唯一性

25、约束与主键约束的区别：主键约束一个表只能有一个，而唯一性约束可创建多个、主键不能为空而唯一性约束可以。 学生表中，学号是主键，身份证号就可以定义为惟一性约束学号学号姓名姓名性别性别身份证身份证系别系别1301130213041303李荣浩李荣浩刘强东刘强东马云马云陆盈盈陆盈盈男男男男F女女330227199303174026330227199303174026330105199306061324nullX01X02X03null学号学号姓名姓名性别性别身份证身份证系别系别1301130213041303李荣浩李荣浩刘强东刘强东马云马云陆盈盈陆盈盈男男男男F女女33022719930317402

26、6330227199303174026330105199306061324nullX01X02X03null创建唯一性约束创建唯一性约束方法 （1） SQL Server管理平台操创建选中某个表-右键设计选择列- 索引/键如果创建了主如果创建了主键，在这里也键，在这里也可以看到可以看到方法二：使用方法二：使用SQLSQL语句创建唯一性约束语句创建唯一性约束(1)建表时在字段上创建create table students(sfzh char（18） unique,)(2)建表时在末尾非标准创建create table students( (zw image，primary key (sno)，

27、unique (sfzh) )(3)建表时在末尾标准创建语法：CONSTRAINT 唯一性约束名 UNIQUE （列名）上面的最后一句改成： CONSTRAINT uq_Students unique (sfzh) 关于唯一性约束名，可以由创建者自己取，一般系统习惯取名为IX_表名（因为跟创建索引是在一个地方）此时设为唯一性约束的字段如果不可为空，一定要指明此时设为唯一性约束的字段如果不可为空，一定要指明not null检查约束（检查约束（checkcheck） 检查约束对输入列或者整个表中的值设置检查条件，以限制输入值，保证数据库数据的完整性。 比如：要求学生年龄的约束为15-30岁的话，一

28、旦你设置好后， 输入8或65就输不进去了！如果已经输入了的话，它就会显示错误。再比如：学生信息表中设置了性别的检查约束只能是“男”或者“女”，就不能输入其他的信息。学号学号姓名姓名性别性别身份证身份证系别系别1301130213041303李荣浩李荣浩刘强东刘强东马云马云陆盈盈陆盈盈男男男男F女女330227199303174026330227199303174026330105199306061324nullX01X02X03null学号学号姓名姓名性别性别身份证身份证系别系别1301130213041303李荣浩李荣浩刘强东刘强东马云马云陆盈盈陆盈盈男男男男F女女3302271993031

29、74026330227199303174026330105199306061324nullX01X02X03null创建检查约束创建检查约束方法 （1） SQL Server管理平台操创建选中某列-右键-CHECK约束方法二：使用方法二：使用SQLSQL语句创建检查约束语句创建检查约束(1)建表时在字段上创建create table students(ssex nchar（2） check(ssex=男or ssex=女),)(2)建表时在末尾非标准创建create table students( (zw image，primary key (sno)，unique (sfzh)，check

30、(ssex=男or ssex=女) )(3)建表时在末尾标准创建语法：CONSTRAINT 检查约束名 CHECK （逻辑表达式）上面的最后一句改成： CONSTRAINT CK_Students check (ssex=男or ssex=女)关于唯一性约束名，可以由创建者自己取，一般系统习惯取名为CK_表名课堂练习课堂练习3 3 使用SQL语句建立一个学生选课信息SC表，表中有学号sno，课程号cno，成绩score。请为该表设置主键，并定义SCORE 的取值范围为0到100之间。 建立一个学生表stu（学生编号sno，姓名sname，年龄sage，家庭地址address，联系电话tel），

31、定义年龄的取值范围为0到30之间,学生编号为主键,姓名为唯一性约束.默认约束默认约束 （DEFAULTDEFAULT） 默认约束指定在插入操作中如果没有提供输入值，则系统自动按默认约束的设置指定值。默认约束可以包括常量、函数、不带变元的内建函数或者空值。 使用默认约束时，应该注意以下几点：(1)每个字段只能定义一个默认约束；(2)如果定义的默认值长于其对应字段的允许长度， 那么输入到表中的默认值将被截断；(3)不能在设置为IDENTITY属性或者数据类型为timestamp(时间戳)的字段上创建默认约束；(4)如果某字段设置为not null，则设置默认约束没有任何意义创建默认约束创建默认约束

32、 方法 （1） SQL Server管理平台操创建图5-10 创建默认约束对话框 方法二：使用方法二：使用SQLSQL语句创建默认约束语句创建默认约束其语法形式如下： CONSTRAINT 默认约束名称 DEFAULT 默认约束表达式 FOR 列名(放在表的末尾)例1：为ssex字段创建默认约束“男”。constraint DF_students default 计算机 for dept例2：为 dept字段创建默认约束“计算机”。constraint DF_dept default 计算机 for dept 外键（外码）约束 student dept学号学号姓名姓名性别性别系别系别1301李

33、荣浩李荣浩女女X011302刘强东刘强东男男X011303马云马云男男X041304陆盈盈陆盈盈女女X02系别号系别号系名系名X01计算机系计算机系X02信管系信管系X03软工系软工系参照被参照表被参照表（主表）（主表）参照表参照表（从表）（从表）主码外码Null值值可以吗？可以吗？外键 参照关系和和被参照关系可以是同一张表学号姓名性别年龄专业号班长学号801张三女1901802802李四男2001null803王五男2001802804赵六女2002805805钱七男1902null外码主码创建外键约束创建外键约束 第一种方法：在SQL Server管理平台中添加外部键约束图5-12 创建外

34、键约束对话框 方法二：使用方法二：使用SQLSQL语句创建外键约束语句创建外键约束使用SQL语句设置外部键约束 ，其语法形式如下： CONSTRAINT 外键约束名 FOREIGN KEY （外键约束的列名） REFERENCES 主键表 （主键表中的主键列名）与前面创建主键类似，也可在建表时用3种方式创建创建借阅表创建借阅表Borrow(P34)Borrow(P34)CREATE TABLE Borrow(book_ID CHAR(10) PRIMARY KEY,Reader_ID CHAR(10) PRIMARY KEY,Borrowdate DATETIME default getdat

35、e(),FOREIGN KEY(book_ID) REFERENCES Book(book_ID), FOREIGN KEY(Reader_ID) REFERENCES Reader(Reader_ID) )列名数据类型约束reader_IDchar(10) 主键，外键，参照reader表中reader_idbook_IDchar(10) 主键，外键，参照book表中book_idborrowdate datetime 默认值为当前的系统时间表级完整性约束（表级完整性约束（外外键键），建在表的末尾处），建在表的末尾处创建借阅表创建借阅表Borrow(P34)Borrow(P34)CREATE

36、TABLE Borrow(book_ID CHAR(10) REFERENCES Book(book_ID),Reader_ID CHAR(10) REFERENCES Reader(Reader_ID),Borrowdate DATETIME default getdate(),PRIMARY KEY(book_ID,Reader_ID)列名数据类型约束reader_IDchar(10)主键，外键，参照reader表中reader_idbook_IDchar(10)主键，外键，参照book表中book_idborrowdatedatetime默认值为当前的系统时间列级完整性约束（列级完整性约

37、束（外外键键），建在字段后面），建在字段后面表级完整性约束表级完整性约束（组合组合主键主键）创建借阅表创建借阅表Borrow(P34)Borrow(P34)CREATE TABLE Borrow(book_ID CHAR(10) ,Reader_ID CHAR(10),Borrowdate DATETIME default getdate(),constraint PK_Borrow PRIMARY KEY(book_ID,Reader_ID),constraint FK_B_B Foreign key(book_id) REFERENCES Book(book_ID),constraint

38、FK_B_R Foreign key(reader_id) REFERENCES Reader(Reader_ID)列名数据类型约束reader_IDchar(10)主键，外键，参照reader表中reader_idbook_IDchar(10)主键，外键，参照book表中book_idborrowdatedatetime默认值为当前的系统时间列级完整性约束表级完整性约束在已有表中添加和删除约束在已有表中添加和删除约束语法格式：ALTER TABLE ADD CONSTRAINT 约束定义; -添加约束ALTER TABLE DROP CONSTRAINT； -删除约束添加主键添加主键 语法：

39、语法：ALTER TABLE ADD CONSTRAINT Primary Key(列名,n); 在student表的sno列上添加主码ALTER TABLE student ADD PRIMARY KEY(sno); 添加Sc表的主键ALTER TABLE sc ADD constraint pk_sc PRIMARY KEY(sno,cno);由系统分配由系统分配约束名约束名自己定义约束名列名列名约束约束sno主码cno主码grade添加外键添加外键 语法：ALTER TABLE ADD CONSTRAINT Foreign Key(列名,n) references 主表表名(主表的列名); 添加Sc表的外键ALTER TABLE sc ADD FOREIGN KEY(sno) REFERENCES student(sno);ALTER TABLE sc ADD constraint fk_sc_cno FOREIGN KEY(cno) REFERENCES course(cno);由系统分配由系统分配约束名约束名自己定义约束名列名列名约束约束sno外码，引用student的snocno外码，引用course的cnograde级联更新、删除级联更新、删除ALTER TABLE ADD CONSTRAINT Foreign Ke