第4章 SQL语言基础及数据库定义 4.1 基本概念 4.2 SQL Server 提供的主要数据类型 4.3 数据定义.

Presentation on theme: "第4章 SQL语言基础及数据库定义 4.1 基本概念 4.2 SQL Server 提供的主要数据类型 4.3 数据定义."— Presentation transcript:

1 第4章 SQL语言基础及数据库定义 4.1 基本概念 4.2 SQL Server 提供的主要数据类型 4.3 数据定义

2 4.1 基本概念 4.1.1 SQL语言的发展 4.1.2 SQL语言的特点 4.1.3 SQL语言功能概述

3 4.1 基本概念 SQL （Structured Query Language ，结构化查询语言）是关系数据库的标准语言
对关系模型的发展和商用DBMS的研制起着重要的作用。

4 4.1.1 SQL语言的发展 标准 发布日期 SQL/86 1986.10 SQL/89(FIPS 127-1) 1989年
标准 发布日期 SQL/ SQL/89(FIPS 127-1) 年 SQL/ 年 SQL 年 SQL 年

5 4.1.2 SQL语言的特点 1.综合统一 集数据定义语言（DDL），数据操纵语言（DML），数据控制语言（DCL）功能于一体。
可以独立完成数据库生命周期中的全部活动： 定义关系模式，插入数据，建立数据库； 对数据库中的数据进行查询和更新； 数据库重构和维护 数据库安全性、完整性控制等 用户数据库投入运行后，可根据需要随时逐步修改模式，不影响数据的运行。 数据操作符统一

6 4.1.2 SQL语言的特点 2.高度非过程化 非关系数据模型的数据操纵语言“面向过程”，必须制定存取路径

7 4.1.2 SQL语言的特点 3.面向集合的操作方式 非关系数据模型采用面向记录的操作方式，操作对象是一条记录 SQL采用集合操作方式
操作对象、查找结果可以是元组的集合 一次插入、删除、更新操作的对象可以是元组的集合

8 4.1.2 SQL语言的特点 4.语言简洁，易学易用 SQL功能极强，完成核心功能只用了10个动词。 SQL功能 命令动词 数据定义
CREATE、DROP、ALTER 数据查询 SELECT 数据操纵 INSERT、UPDATE、DELETE 数据控制 GRANT、REVOKE、DENY

9 4.1.2 SQL语言的特点 5.以同一种语法结构提供多种使用方式 SQL是独立的语言 能够独立地用于联机交互的使用方式
SQL能够嵌入到高级语言（例如C，C++，Java）程序中，供程序员设计程序时使用

10 4.1.2 SQL语言的特点 6. 支持关系数据库三级模式结构 SQL 视图2 视图1 基本表2 基本表1 基本表3 基本表4 存储文件2
存储文件1 外模式 模 式 内模式

11 SQL支持关系数据库三级模式结构 SQL语言支持数据库的三级模式结构。其中外模式对应于视图和部分基本表，模式对应于基本表，内模式对应于存储文件及索引。 基本表是本身独立存在的表， SQL中一个关系就对应一个基本表，一个(或多个)基本表对应一个存储文件，一个基本表可以带若干索引，存储文件及索引组成了关系数据库的内模式。 视图是从一个或几个基本表导出的表，它本身不独立存储在数据库中，即数据库中只存储视图的定义，不存储对应的数据。因此视图是一个虚表。 用户可以用SQL语言对视图（View）和基本表（Base Table)进行查询等操作，在用户观点里，视图和基本表一样，都是关系。 SQL语言可以定义的模式种类 –基本表，索引，视图

12 4.1.3 SQL语言功能概述 数据定义功能、数据控制功能、数据查询功能和数据操纵功能。 SQL功能 命令动词 数据定义
CREATE、DROP、ALTER 数据查询 SELECT 数据操纵 INSERT、UPDATE、DELETE 数据控制 GRANT、REVOKE、DENY

13 4.2 SQL Server提供的主要数据类型 数值型 字符串型 日期时间型

14 4.2 SQL Server提供的主要数据类型 关系数据库的表结构由列组成 列名指明了要存储的数据的含义
每一列的的数据是同质的，在定义表结构时，要指明每个列的数据类型。 每个数据库产品所支持的数据类型并不完全相同，而且与标准的SQL也有差异 主要介绍Microsoft SQL Server支持的常用数据类型，同时对比标准SQL数据类型。

15 4.2.1 数值型 1.准确型 :准确型数值是指在计算机中能够精确存储的数据，比如整型数、定点小数等都是准确型数据。 SQL Server
数据类型 SQL92或SQL99数据类型 说明 Bigint 8字节，存储从–263 (– ) 到 ( ) 范围的整数。 Int Integer 4字节，存储从–231 (–2,147,483,648 ) 到 ( 2,147,483,647 ) 范围的整数。 Smallint 2字节，存储从–215 (–32,768 ) 到 (32,767 ) 范围的整数。 Tinyint 存储从 0 到 255 之间的整数。 Bit 存储1或0。 numeric（p,q）或decimal（p,q） decimal 定点精度和小数位数。使用最大精度时，有效值从 – 到 。其中，p为精度，指定小数点左边和右边可以存储的十进制数字的最大个数。q为小数位数，指定小数点右边可以存储的十进制数字的最大个数，0 <= q <= p。q的默认值为0。

16 4.2.1 数值型 2.近似型:近似型是用于表示浮点型数据的近似数据类型。浮点数据为近似值，表示在其数据类型范围内的所有数据在计算机中不一定都能精确地表示。 SQL Server 数据类型 SQL92或SQL99数据类型 说明 float 8字节，存储从 –1.79E 到 1.79E 范围的浮点型数。 real 4字节，存储从 –3.40E + 38 到 3.40E + 38 范围的浮点型数。

17 4.2.2 字符串型 字符串数据由汉字、英文字母、数字和各种符号组成。 普通编码字符串类型 统一字符编码字符串类型 二进制字符串类型

18 4.2.2 字符串型 SQL Server 数据类型 SQL92或SQL99数据类型 说明 char（n） character
varchar（n） character varying 可变长度的字符串类型，n表示字符串的最大长度，取值范围为1～8000。 text 可存储231-1 (2,147,483,647) 个字符的大文本。 nchar（n） national character 固定长度的 Unicode 数据，n表示字符串的最大长度，取值范围为1～4000 nvarchar（n） national character varying 可变长度的 Unicode 数据，n表示字符串的最大长度，取值范围为1～4000。 ntext 最多可存储230-1 (1,073,741,823) 个字符的统一字符编码文本。 binary（n） binary 固定长度的二进制字符数据，n表示最大长度，取值范围为1～8000。 varbinary（n） binary varying 可变长度的二进制字符数据，n的取值范围为1～8000。 image 大容量的、可变长度的二进制字符数据，可以存储多种格式的文件，如：Word、Excel、BMP、GIF和JPEG文件数据等。最多可存储231-1 (2,147,483,647) 个字节，约为2GB。

19 普通编码字符串类型 Char(n): 定长存储，n<=8000
Varchar(n)：不定长存储（按实际长度存储），长度最大不超过n , n<=8000 注：n为字符个数 Text：存储大于8000字节的文本

20 统一字符编码字符串类型 nchar(n):定长存储，n<=4000
nvarchar(n)：不定长存储，长度最大不超过n , n<=4000 ntext：存储大于8000字节的文本 特点：每个字符占两个字节

21 二进制字符串类型 Binary(n)：固定长度，n <= 8000。 Varbinary(n)：可变长度，n <=8000 。
image：大容量、可变长二进制字符数据，可用于存储文件。

22 4.2.3 日期时间类型 SQL Server的日期时间数据类型是将日期和时间合起来存储，它没有单独存储的日期和时间类型
SQL92或SQL99是将日期和时间类型分开，日期是Date类型，时间是Time类型。

23 4.2.3 日期时间类型 SQL Server的日期时间类型 SQL Server 说明 数据类型
Datetime 占用8字节空间，存储从1753年1月1日到9999年12月31日的日期和时间数据，精确到百分之三秒（或 3.33 毫秒）。 （例：’2001/08/03 10:30:00.000’ ） Smalldatetime 占用4字节空间，存储从1900年1月1日到2079年6月6日的日期和时间数据，精确到分钟。 （例：’2001/08/03 10:30:00’）

24 4.2.3 日期时间类型 输入日期 :在输入日期部分时可采用英文数字格式、数字加分隔符格式和纯数字格式。采用英文数字格式时，月份可用英文全名或缩写形式，不区分大小写。例如：2001年10月25日可以采用下列几种输入格式： Oct /* 英文数字格式 */ 或 2001/10/25/* 数字加分隔符格式 */ /* 纯数字格式 */ 输入时间:在输入时间部分时可以采用12小时格式或24小时格式。例如，要表示2001年10月25日下午3点28分56秒，可以用如下形式输入： :28:56 PM /* 12小时格式 */ :28: /* 24小时格式 */

25 4.2.4 货币类型 货币数据类型表示货币值。货币数据存储的精确度固定为四位小数，实际上货币类型的数据都是有4位小数的decimal类型的数据。SQL92或SQL99没有对应的货币类型。 SQL Server 数据类型 说明 money 8字节，存储的货币数据值介于–263 (–922,337,203,685, ) 与 (+922,337,203,685, ) 之间，精确到货币单位的千分之十。最多可以包含19位数字。 Smallmoney 4字节，存储的货币数据值介于 –214, 与 +214, 之间，精确到货币单位的千分之十。

26 4.3 数据定义 4.3.1 基本表的定义与删除 4.3.2 修改表结构

27 SQL的数据定义功能: 表定义、视图和索引的定义
. SQL 的数据定义语句 操 作 方 式 对 象 创 建 删 除 修 改 表 CREATE TABLE DROP TABLE ALTER TABLE 视 图 CREATE VIEW DROP VIEW 索 引 CREATE INDEX DROP INDEX

28 4.3.1 基本表的定义与删除 1．定义基本表 使用SQL语言中的CREATE TABLE语句实现，其一般格式为： CREATE TABLE <表名> （<列名> <数据类型>[ <列级完整性约束条件> ] [，<列名> <数据类型>[ <列级完整性约束条件>] ] … [，<表级完整性约束条件> ] ）； 注：如果完整性约束条件涉及到该表的多个属性列，则必须定在表级上，否则既可以定义在列级也可以定义在表级。

29 4.3.1 基本表的定义与删除 在列定义同时定义的约束称为列级完整性约束定义 在表定义的最后定义的完整性约束我们称为表级完整性约束
在列级完整性约束定义处可以定义如下约束： NOT NULL：限制列取值非空。 DEFAULT：给定列的默认值，使用形式为：DEFAULT 常量 UNIQUE：限制列取值不重。 CHECK：限制列的取值范围，使用形式为：CHECK（约束表达式）。 PRIMARY KEY：指定本列为主码。 FOREIGN KEY：定义本列为引用其他表的外码。使用形式为：［FOREIGN KEY］［<列名>］ REFERENCES <外表名>（<外表列名>） 在上述约束中，除了NOT NULL和DEFAULT不能在表级完整性约束处定义之外，其他约束均可在表级完整性约束处定义。

30 示例：定义Student表 列名 数据类型 约束 Sno 普遍编码定长字符串，长度为7 主码 Sname 普遍编码定长字符串，长度为10 非空 Ssex 普遍编码定长字符串，长度为2 Sage 微整型 Sdept 普遍编码定长字符串，长度为20 CREATE TABLE Student ( Sno char(7) PRIMARY KEY, Sname char(10) NOT NULL, Ssex char(2), Sage tinyint, Sdept char(20) )

31 示例：定义Course表 列名 数据类型 约束 Cno 普遍编码定长字符串，长度为6 主码 Cname 普遍编码定长字符串，长度为20 非空 Credit 微整型 Semster CREATE TABLE Course ( Cno char(6) NOT NULL, Cname char(20) NOT NULL, Credit tinyint, Semester tinyint, PRIMARY KEY(Cno) )

32 示例：定义SC表 列名 数据类型 约束 Sno 普遍编码定长字符串，长度为7 主属性，外码（参照Student的主码） Cno 普遍编码定长字符串，长度为6 主属性，外码（参照Course的主码） Grade 微整型 CREATE TABLE SC ( Sno char(7) NOT NULL, Cno char(6) NOT NULL, Grade tinyint, PRIMARY KEY(Sno, Cno), FOREIGN KEY(Sno) REFERENCES Student(Sno), FOREIGN KEY(Cno) REFERENCES Course(Cno) )

33 学生-课程数据库 三张表： 学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
课程表：Course(Cno,Cname,Cpno,Ccredit) 学生选课表：SC(Sno,Cno,Grade)

34 学生-课程数据库 1. Student表结构 列名 说明 数据类型 约束 Sno 学号 字符串，长度为7 主码 Sname 姓名
字符串，长度为10 非空 Ssex 性别 字符串，长度为2 取‘男’或‘女’ Sage 年龄 整数 取值15～45 Sdept 所在系 字符串，长度为20 默认为‘计算机系’

35 学生-课程数据库 Student表数据

36 学生-课程数据库 2. Course表结构 列名 说明 数据类型 约束 Cno 课程号 字符串，长度为6 主码 Cname 课程名
字符串，长度为20 非空 Ccredit 学分 整数 取值大于0 Semster 学期 Period 学时

37 学生-课程数据库 Course表数据

38 学生-课程数据库 3. SC表结构 列名 说明 数据类型 约束 Sno 学号 字符串，长度为7 主属性，外码（参照Student的主码值）
Cno 课程名 字符串，长度为6 主属性，外码（参照Course的主码值） Grade 成绩 整数 取值0～100

39 学生-课程数据库 SC表数据

40 创建学生数据库的三张表 CREATE TABLE Student ( Sno char ( 7 ) PRIMARY KEY,
Sname char ( 10 ) NOT NULL, Ssex char (2) CHECK (Ssex = '男' OR Ssex = '女'), Sage tinyint CHECK (Sage >= 15 AND Sage <=45), Sdept char (20 ) DEFAULT '计算机系' )

41 创建学生数据库的三张表 CREATE TABLE Course ( Cno char(10) NOT NULL, Cname char(20) NOT NULL, Ccredit tinyint CHECK (Ccredit > 0), Semester tinyint CHECK (Semester > 0), Period int CHECK (Period > 0), PRIMARY KEY(Cno) )

42 创建学生数据库的三张表 CREATE TABLE SC ( Sno char(7) NOT NULL, Cno char(10) NOT NULL, Grade tinyint, CHECK (Grade >= 0 and Grade <= 100), PRIMARY KEY ( Sno, Cno ), FOREIGN KEY ( Sno ) REFERENCES Student ( Sno ), FOREIGN KEY ( Cno ) REFERENCES Course ( Cno ) )

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

44 4.3.2 修改表结构（自学） 在定义完表之后，如果需求有变化，比如添加列、删除列或修改列定义，可以使用ALTER TABLE语句实现。
不同的数据库产品的ALTER TABLE语句的格式略有不同，这里给出SQL Server的ALTER TABLE语句的部分格式，对于其他的数据库管理系统，可以参考它们的语言参考手册。

45 注：‘--’为SQL语句的单行注释符。 ALTER TABLE <表名>
[ ALTER COLUMN <列名> <新数据类型>] 修改列定义 | [ ADD [COLUMN] <列名> <数据类型> <约束>] -- 添加新列 | [ DROP COLUMN <列名> ] 删除列 | [ADD 约束定义] --添加约束 | [DROP <约束名>] --删除约束 | [ADD PRIMARY KEY（列名 [，… n ] ）] -- 添加主码约束 | [ADD FOREIGN KEY（列名）REFERNECES 表名（列名）] -- 添加外码约束 注：‘--’为SQL语句的单行注释符。

46 示例 例．为Student表添加“专业”列，此列的定义为：Spec char(10)，允许空。 ALTER TABLE Student
ADD Spec char(8) NULL 例．将新添加的“专业”列的类型改为char(20)。 ALTER COLUMN Spec char(20) 例．删除新添加的“专业”列。 DROP COLUMN Spec

47 例．为SC表添加“修课类别”列，此列的定义为：XKLB char(4) ALTER TABLE SC ADD XKLB char(4) NULL 例．将新添加的XKLB的类型改为char(6)。 ALTER COLUMN XKLB char(6) 例．删除Course表的Period列 ALTER TABLE Course DROP COLUMN Period