教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net 数据库技术 教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net.

Presentation on theme: "教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net 数据库技术 教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net."— Presentation transcript:

1 教 师：曾晓东 电 话：13679007201 E_mail：zengxiaodong@263.net
数据库技术 教 师：曾晓东 电 话：

2 第3章 SQL语言基础及数据定义功能 3.1 基本概念 3.2 SQL的数据类型 3.3 数据定义功能

3 3.1 基本概念 3.1.1 SQL语言的发展 3.1.2 SQL语言的特点 3.1.3 SQL语言功能概述

4 3.1.1 SQL语言的发展 1986年10月由美国ANSI 公布最早的SQL标准。
1989年4月，ISO提出了具备完整性特征的SQL，称为SQL-89 。 1992年11月，ISO又公布了新的SQL标准，称为SQL-92（以上均为关系形式）。 1999年颁布SQL-99，是SQL92的扩展。

5 3.1.2 SQL语言的特点 1. 一体化 2. 高度非过程化 3. 简洁 4. 使用方式多样

6 3.1.3 SQL语言功能概述 四部分：数据定义功能、数据控制功能、数据查询功能和数据操纵功能。 SQL功能 命令动词 数据查询
SELECT 数据定义 CREATE、DROP、ALTER 数据操纵 INSERT、UPDATE、DELETE 数据控制 GRANT、REVOKE

7 3.2 SQL的数据类型 一、 系统数据类型 1. 整型数据类型 6. 货币数据类型 2. 浮点数据类型 7. 位数据类型
3. 字符数据类型 4. 日期和时间数据类型 5. 文本和图形数据类型 6. 货币数据类型 7. 位数据类型 8. 二进制数据类型 9. 特殊数据类型 10. 新增数据类型 数据类型就是定义每一列所能存放的数据值和数据格式。

8 1. 整型数据类型 整型数据类型是最常用的数据类型之一，它主要用来存储数值，可以直接进行数据运算，而不必使用函数转换。
int（integer）：占用4字节，可以存储从-231 ~ （ ~ ）范围之间的所有正负整数。 Smallint：占用2字节，可以存储从-213 ~ （-32768~32767）范围之间的所有正负整数 。 Tinyint：占用1字节，可以存储从0到255范围之间的所有正整数。 Bigint：占用8字节，存放从-263到263-1范围内的整型数据。

9 2. 浮点数据类型 浮点数据包括按二进制技术系统所能提供的最大精度保留的数据。浮点数值的数据在SQL Server中采用只入不舍的方式进行存储 。 Real：精确到第7位精确位数，范围从-3.40E-38~ 3.40E+38 ，占用4个字节的存储空间。 Float：可以精确到第13位小数，其范围从-1.79E-308到1.79E+308，占用8个字节的存储空间。 语法： float[n] ,n 为数据精度1~7定义real,8~15为float

10 小数数据类型 Decimal和numeric：Decimal数据类型和numeric数据类型完全相同，它们可以提供小数所需要的实际存储空间，但也有一定的限制，可以用2到17个字节来存储从 到 之间的数值。 存储数值 比存储5.20需要更多字节。 声明格式 ： numeric(p,[s]) | decimal(p,[s]) 其中p为精度，s为小数位数，s的默认值为0. 例：decimal(6,3) 若向该列赋值 则实际存储的是

11 3. 字符数据类型 SQL Server提供了3类字符数据类型，分别是Char、Text 和Varchar。最常用的Char和Varchar两类。字符数据类型可以用来存储各种字母、数字符号和特殊符号。 利用Char数据类型存储数据时，每个字符占用一个字节的存储空间。Char数据类型使用固定长度来存储字符，最长可以容纳8000个字符。 格式：char[(n)] n表示所有字符所占的空间，默认值为1。 如果实际数据的字符长度短于给定的最大长度，则多余的字节会用空格填充。 如果实际数据的字符长度超过了给定的最大长度，则超过的字符将会被截断。 在使用字符型常量为字符数据类型赋值时，必须使用单引号（‘’）将字符型常量括起来。

12 Varchar Varchar数据类型来存储最长可以达到8000字符的变长字符。与Char 数据类型不同，Varchar数据类型的存储空间随存储在表列中的每一个数据的字符数的不同而变化。 格式：Varchar[(n)] n表示所有字符所占的空间，1~8000。 例:定义表列为Varchar(20)，那么存储在该列的数据最多可以长达20个字节。但是在数据没有达到20个字节时并不会在多余的字节上填充空格。 当存储在列中的数据的值大小经常变化时，使用Varchar数据类型可以有效地节省空间。 但是char比Varchar的处理速度快。

13 Text 当要存储的字符型数据非常庞大以至于8000字节完全不够用时，Char和Varchar数据类型都失去了作用。这时应该选择Text数据类型。 Text：用于存储大量文本数据，其容量理论上为1到231-1（21，4748，3647）个字节，但实际应用时要根据硬盘的存储空间而定。 Ntext：与text数据类型类似，存储在其中的数据通常是直接能输出到显示设备上的字符，显示设备可以是显示器、窗口或者打印机。

14 4. 日期和时间数据类型 4. 日期和时间数据类型 代表日期和一天内的时间的日期和时间数据类型。
Datetime：用于存储日期和时间的结合体 。从 1753 年 1 月 1 日到 9999 年 12 月 31 日的日期和时间数据，精确度为百分之三秒（等于 3.33 毫秒或 秒）。 Smalldatetime：从 1900 年 1 月 1 日到 2079 年 6 月 6 日的日期和时间数据精确到分钟。 SQL Server在用户没有指定小时以上精度的数据时，会自动设置Datetime和Smalldatetime数据的时间为00:00:00。

15 输入日期和时间数据 常用的日期表示格式： 月/日/年： 5/1//2003、05/01/03
月/日/年： /1//2003、05/01/03 月-日-年： 、 月.日.年： 、 常用的时间表示格式： 时：分 ：05 时：分：秒 ：05：25 时：分 ：秒：毫秒 ：05：25：23 时：分 AM | PM ：05 AM

16 5. 文本和图像数据类型 文本和图形数据类型是用于存储大量字符以及二进制数据的可变长度数据类型，包括text型、ntext型和image型。
text型用于存储大量非Unicode文本数据，其容量理论上为 231-1个字节。在实际应用时需要视硬盘的存储空间而定。 ntext型用于存储大量Unicode文本数据，其理论容量为230-1个字节。ntext型的其他用法与text型基本一样。 image型用于存储大量二进制数据，其理论容量为231-1 个字节。Image型数据的存储模式与text型数据相同，通常用来存储图形等OLE对象。在输入数据时，与输入二进制数据一样，必须在数据前加上起始符号“0X”作为二进制标识。

17 6. 货币数据类型 Money：用于存储货币值，存储在money数据类型中的数值以一个正数部分和一个小数部分存储在两个4字节的整型值中，存储范围为 到 ，精度为货币单位的万分之一。 Smallmoney：与money数据类型类似，但其存储的货币值范围比money数据类型小，其存储范围为 到 。

18 7. 位数据类型 Bit数据相当于其他语言的逻辑型数据。他存储0和1,长度为1字节。

19 8. 二进制数据类型 binary [ ( n ) ]：固定长度的 n 个字节二进制数据。N 必须从 1 到 8,000。存储空间大小为 n+4 字节。 varbinary [ ( n ) ]：n 个字节变长二进制数据。n 必须从 1 到 8,000。存储空间大小为实际输入数据长度 +4 个字节，而不是 n 个字节。输入的数据长度可能为 0 字节。

20 8. 二进制数据类型 注： （1）如果在数据定义或变量声明语句中没有指定 n，默认长度为 1。
（2）当列数据项大小一致时应使用 binary。 当列数据项大小不一致时应使用 varbinary。 （3）二进制数据由十六进制数表示。例如，十进制数 245 等于十六进制数 F5。 输入二进制时，在数据全面要加0x，可以用数据符号为0—9，A—F。因此，二进制数据有时也被称为十六进制数据。

21 9. 特殊数据类型 Timestamp：亦称时间戳数据类型，数据库范围的唯一数字，每次更新行时也进行更新。timestamp 这种数据类型表现自动生成的二进制数，确保这些数在数据库中是唯一的。存储大小为 8 字节。 一个表只能有一个 timestamp 列。每次插入或更新包含 timestamp 列的行时，timestamp 列中的值均会更新。

22 10. 新增数据类型 Bigint：用于存储从-263（-9，223，372，036，834，773，807）到263-1（9，223，372，036，834，773，807）之间的所有正负整数。 sql_variant：用于存储除文本、图形数据和timestamp类型数据外的其它任何合法的SQL Server数据。sql_variant 类型的列可以包含不同数据类型的行。例如，定义为 sql_variant 的列可以存储 int、binary 和 char 值。不能使用 sql_variant 存储的值类型只有 text、ntext、image、timestamp 和 sql_variant。 sql_variant 的最大长度可达 8016 字节。 table：用于存储对表或者视图处理后的结果集。这种新的数据类型使得变量可以存储一个表，从而使函数或过程返回查询结果更加方便、快捷。

23 3.3 数据定义功能 3.3.1 表的概念 3.3.2 基本表的定义与删除 3.3.3 修改表结构 3.3.4 数据完整性

24 3.3.1 表的概念 表是数据存储的地方，在SQL Server中所有的数据存储都基于表，表是数据库中最重要的部分。表由行和列组成。表中的一行称为一条记录，表中的一列称为一个字段。 每个SQL Server数据库可容纳多达20亿个表，每个表中至多可以有1024列，每一行最多允许有8086个字节。行数和表的大小只受服务器中可用存储空间的限制。

25 3.3.1 表的概念 SQL Server 2000的数据表可分为用户表和系统表两种类型。用户表是数据库用户根据自己的设计创建的表，用于存放用户的数据，系统表则是SQL Server 2000为实现数据库维护而创建的表，任何用户都不应该直接修改系统表，否则会导致数据库无法使用。 数据表分类： 永久表：在创建后，除非用户删除，否则将一直存储在数据文件中； 临时表：会在用户退出或者进行系统修复的时候被删除。

26 3.3.2 基本表的定义与删除 1．定义基本表 使用SQL语言中的CREATE TABLE语句实现，其格式为：
CREATE TABLE table_name ( { < column_definition > /*列的定义*/ | column_name AS computed_column_expression /*定义计算列*/ | < table_constraint > } /*指定表的约束*/ [ ON { filegroup | DEFAULT } ] /*指定存储表的文件组*/ [ TEXTIMAGE_ON { filegroup | DEFAULT }] /*指定存储text,ntext和image类型数据的文件组*/

27 说明： < column_definition > ::=
{ column_name data_type } /*指定列明、类型*/ [[DEFAULT constant_expression ] /*指定默认值*/ |[IDENTITY [(seed,increment) NOT FOR REPLICATION ] ] ] ] /*指定默认值*/ [ < column_constraint > ] [ ...n ] /*指定列的约束*/

28 各参数说明： database_name：用于指定在其中创建表的数据库名称。 owner：用于指定新建表的所有者的用户名 。
table_name：用于指定新建的表的名称。 column_name：用于指定新建表的列的名称。 computed_column_expression：用于指定计算列的列值的表达式。 ON {filegroup | DEFAULT}：用于指定存储表的文件组名。 TEXTIMAGE_ON：用于指定 text、ntext 和 image 列的数据存储的文件组。 data_type：用于指定列的数据类型 。

29 各参数说明 DEFAULT：用于指定列的缺省值。 constant_expression：用于指定列的缺省值的常量表达式 。
IDENTITY：用于指定列为标识列。 Seed：用于指定标识列的初始值。 Increment：用于指定标识列的增量值。 NOT FOR REPLICATION：用于指定列的IDENTITY属性在把从其它表中复制的数据插入到表中时不发生作用，即不足的生成列值，使得复制的数据行保持原来的列值。 column_constraint和table_constraint：用于指定列约束和表约束。

30 例3.1 创建名称为“product”的产品信息表，该表结构如下表所示。 product表 字段名称 数据类型 预计长度 特殊限制 产品ID
int 默认 主键 产品名 不能为空 类别ID 单价 money 库存量

31 例3.1 SQL语句 CREATE TABLE product( 产品ID int primary key，
产品名 varchar(10) not null， 类别ID int， 单价 money, 库存量 int )

32 例3.2 创建名为“p_order”的订单表，名为“employee”的雇员表，名为“customer”的客户表和名为“category”的类别表。 订单表:

33 订单表 CREATE TABLE p_order( 订单ID int primary key, 产品ID int, 数量 int,
订货日期 datetime ) GO

34 例3.2 雇员表

35 雇员表 CREATE TABLE employee( 雇员ID int NOT NULL, 姓名 varchar(8),
出生日期 datetime, 雇佣日期 datetime, 特长 varchar(20), 薪水 money ) GO

36 例3.2 客户表

37 客户表 CREATE TABLE customer ( 客户ID int NOT NULL, 公司名称 varchar(30) ,
GO

38 例3.2 类别表

39 类别表 CREATE TABLE category ( /*创建类别表category*/ 类别ID int primary key,
类别名 varchar(10) , 说明 varchar(16) ) GO

40 在列级完整性约束定义处可以定义的约束 NOT NULL：限制列取值非空。 DEFAULT：给定列的默认值。 UNIQUE：限制列取值不重。
CHECK：限制列的取值范围。 PRIMARY KEY：指定本列为主码。 FOREIGN KEY：定义本列为引用其他表的外码。使用形式为： ［FOREIGN KEY（<外码列名>）］ REFERENCES <外表名>（<外表列名>）

41 几点说明 NOT NULL和DEFAULT只能是列级完整性约束； 其他约束均可在表级完整性约束处定义。 注意以下几点：
第一，如果CHECK约束是定义多列之间的取值约束，则只能在表级完整性约束处定义； 第二，如果表的主码由多个列组成，则也只能在表级完整性约束处定义，并将主码列用括号括起来，即：PRIMARY KEY（列1｛[，列2 ] …｝）； 第三，如果在表级完整性约束处定义外码，则“FOREIGN KEY (<外码列名>)”部分不能省。

42 ① 列取值非空约束 约束定义 <列名> <类型> NOT NULL
例：sname char(10) NOT NULL

43 约束定义（续） ② 表主码约束 在定义列时定义主码（仅用于单列主码） 列定义 PRIMARY KEY
例： SNO char(7) PRIMARY KEY 在定义完列时定义主码（用于单列或多列主码） PRIMARY KEY （<列名序列>） 例： PRIMARY KEY(SNO) PRIMARY KEY(SNO，CNO)

44 ③外码引用约束 约束定义（续） 指明本表外码列引用的表及表中的主码列。 [ FOREIGN KEY （<本表列名>）]
REFERENCES <外表名>（<外表主码列名>） 例： FOREIGN KEY （sno） REFERENCES 学生表（sno）

45 例3.3 创建如下三张表：学生(Student)表、课程(Course)表和学生选修(SC)表，这三张表的结构如下表所示：
列名 含义 数据类型 约束 Sno 学号 字符串，长度为7 主码 Sname 姓名 字符串，长度为10 非空 Ssex 性别 字符串，长度为2 Sage 年龄 微整型 Sdept 系别 字符串，长度为20

46 创建学生表 CREATE TABLE Student ( Sno char ( 7 ) PRIMARY KEY,
Sname char ( 10 ) NOT NULL, Ssex char (2), Sage tinyint , Sdept char (20 ) )

47 例3.3 Course表结构 列名 含义 数据类型 约束 Cno 课程号 字符串，长度为10 主码 Cname 课程名 字符串，长度为20
非空 Ccredit 学分 微整型 Semester 学期

48 创建课程表 CREATE TABLE Course ( Cno char(10) NOT NULL,
Cname char(20) NOT NULL, Ccredit tinyint , Semester tinyint, PRIMARY KEY(Cno) )

49 例3.3 列名 含义 数据类型 约束 Sno 学号 字符串，长度为7 Cno 课程号 字符串，长度为10 Grade 成绩 小整型 XKLB
SC表结构 列名 含义 数据类型 约束 Sno 学号 字符串，长度为7 主码，引用Student的外码 Cno 课程号 字符串，长度为10 主码，引用Course的外码 Grade 成绩 小整型 XKLB 修课类别 字符串，长度为4

50 创建SC表 CREATE TABLE SC ( Sno char(7) NOT NULL, Cno char(10) NOT NULL,
Grade tinyint, XKLB char(4) , PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) )

51 2. 删除表 DROP TABLE test 当确信不再需要某个表时，可以将其删除 删除表时会将与表有关的所有对象一起删掉，包括表中的数据。
删除表的语句格式为： DROP TABLE <表名> { [, <表名> ] … } 例3.4：删除test表的语句为： DROP TABLE test

52 3.3.3 修改表结构 在定义完表之后，如果需求有变化，比如添加列、删除列或修改列定义，可以使用ALTER TABLE语句实现。

53 修改表结构语法 ALTER TABLE <表名>
[ ALTER COLUMN <列名> <新数据类型>] | [ ADD [COLUMN] <列名> <数据类型> | [ DROP COLUMN <列名> ] | [ADD PRIMARY KEY（列名 [，… n ] ）] | [ADD FOREIGN KEY（列名） REFERNECES 表名（列名）]

54 示例 ALTER TABLE SC ADD XKLB char(4) NULL
例3.5．为SC表添加“修课类别”列，此列的定义为：XKLB char(4) ALTER TABLE SC ADD XKLB char(4) NULL

55 ALTER COLUMN XKLB char(6)
示例 例3.6．将新添加的XKLB的类型改为 char(6)。 ALTER TABLE SC ALTER COLUMN XKLB char(6)

56 示例 例3.7 删除Course表的Period列。 ALTER TABLE Course DROP COLUMN Period

57 3.3.4 数据完整性 完整性约束条件的作用对象 实现数据完整性

58 完整性约束条件的作用对象 完整性检查是围绕完整性约束条件进行的，因此，完整性约束条件是完整性控制机制的核心。完整性约束条件的作用对象可以是表、元组和列。 列级约束 元组约束 关系约束

59 列级约束 列级约束主要是对列的类型、取值范围、精度等的约束，具体包括： 对数据类型的约束：包括数据类型、长度、精度等。
对数据格式的约束：如规定学号的前两位表示学生的入学年份，第三位表示系的编号，第四位表示专业编号，第五位代表班的编号等等。 对取值范围的约束：如学生的成绩取值范围为0～100。 对空值的约束。

60 元组约束 元组的约束是元组中各个字段之间的联系的约束， 如： 开始日期小于结束日期， 职工的最低工资不能低于规定的最低保障金。

61 关系约束 学号的取值不能重复也不能取空值， 学生修课表中的学号的取值受学生表中的学号取值的约束 指若干元组之间、关系之间的联系的约束。
比如: 学号的取值不能重复也不能取空值， 学生修课表中的学号的取值受学生表中的学号取值的约束

62 实现数据完整性 声明完整性 过程完整性 在表定义时声明 使用约束、缺省值(DEFAULT)等 由SQL Server自动加以保证
在客户端或服务器端用编程语言或工具实现 在Server端用触发器（trigger）来实现

63 实现约束 1. PRIMARY KEY 约束 2. UNIQUE 约束 3. FOREIGN KEY 约束 4. DEFAULT 约束
5. CHECK 约束

64 1. PRIMARY KEY 约束 保证实体完整性 每个表有且只有一个PRIMARY KEY 约束 格式： ALTER TABLE 表名
ADD [ CONSTAINT 约束名 ] PRIMARY KEY ( 列名 [, … n ] )

65 示例 例3.8：对雇员表和客户表分别添加主码约束。 ALTER TABLE employee ADD CONSTRAINT PK_EMP
PRIMARY KEY （雇员ID） ALTER TABLE customer ADD CONSTRAINT PK_CUS PRIMARY KEY （客户ID）

66 UNIQUE 约束 ALTER TABLE 表名 ADD [ CONSTRAINT 约束名 ]
确保在非主键列中不输入重复值。 应用在客观具有唯一性质的列上，如身份证号、社会保险号等。 格式： ALTER TABLE 表名 ADD [ CONSTRAINT 约束名 ] UNIQUE（<列名> [, … n] ）

67 示例 ALTER TABLE employee ADD CONSTRAINT UK_SID UNIQUE（姓名）

68 FOREIGN KEY约束 用于建立和加强两个表数据之间的连接的一列或多列 格式： ALTER TABLE 表名
ADD [ CONSTRAINT 约束名 ] FOREIGN KEY（<列名>）REFERENCES 引用表名（<列名>）

69 示例 例3.10 为订单表的产品ID添加外码引用约束，此列引用产品表的产品ID列。 ALTER TABLE P_oder
ADD CONSTRAINT FK_order_productid FOREIGN KEY （产品ID） REFERENCES producet （产品ID）

70 DEFAULT约束 ALTER TABLE 表名 ADD [ CONSTRAINT 约束名 ] DEFAULT 默认值 FOR 列名
格式： ALTER TABLE 表名 ADD [ CONSTRAINT 约束名 ] DEFAULT 默认值 FOR 列名

71 示例 ALTER TABLE employee ADD CONSTRAINT DF_SALARY DEFAULT 1000 FOR 薪水``
例3.11 定义雇员表的薪水的默认值为1000。 ALTER TABLE employee ADD CONSTRAINT DF_SALARY DEFAULT FOR 薪水``

72 CHECK约束 通过限制输入到列中的值来强制域的完整性。 可定义同表多列之间的约束关系 格式： ALTER TABLE 表名
ADD [ CONSTRAINT 约束名 ] CHECK （逻辑表达式）

73 示例 例3.12 在雇员表中，添加限制雇员的薪水必须大于等于500的约束。 ALTER TABLE employee
ADD CONSTRAINT CHK_Salary CHECK ( 薪水 >= 500 )

74 综合起来 CREATE TABLE employee( 雇员ID int primary key,
姓名 varchar(8) not null unique, 性别 char(2), 出生日期 datetime, 雇佣日期 datetime, 特长 varchar(20), 薪水 money default 1000 check(薪水>=500) )

75 作业 P