当前位置:首页 >> 计算机软件及应用 >>

第3章 sql 语言基础及数据定义功能


第3章 SQL语言基础及数据定义功能

?3.1 ?3.2 ?3.3 ?3.4

SQL语言概述 数据类型 创建数据库 创建与维护关系表

概述
?SQL(Structured Query Language)是 用户操作关系数据库的通用语言。 ?包含数据定义、数据查询、数据操作和 数据控制等与数据库有关的全部功能。 ?已成为关系数据库的标准语言。

?所有的关系数据库管理系统都支持SQL。

3.1 SQL语言概述
?3.1.1 SQL语言的发展 ?3.1.2 SQL语言的特点 ?3.1.3 SQL语言功能概述

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

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

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

命令动词

3.2 数据类型 ?数值类型 ?字符串类型 ?日期时间类型 ?货币类型

精确数值类型
精确数值 类型
bigint int smallint tinyint bit 说明 存 储 从 –263 (–9,223,372,036,854,775,808) (9,223,372,036,854,775,807) 范围的整数 到 263-1 存储空间 8字节

存储从–231 (–2,147,483,648 ) 到 231-1 ( 2,147,483,647 ) 范围的整 4字节 数。 存储从–215 (–32,768 ) 到 215-1 (32,767 ) 范围的整数 2字节
存储从 0 到 255 之间的整数。 1字节

存储1或0。如果一个表中有不多于8个的bit列,则这些列公用 1字节 一个字节存储 定点精度和小数位数。使用最大精度时,有效值从 –1038 +1 到 1038 -1。其中,s为精度,指定小数点左边和右边可以存储的十 进制数字的最大个数。精度必须是从1到最大精度之间的值。 最多17字节 最大精度为38。s为小数位数,指定小数点右边可以存储的十进 制数字的最大个数,0 <= s <= p。s的默认值为0

numeric(p,s) 或 decimal(p,s)

近似数值数类型
近似数值 类型

说明

存储空间

float[(n)]

存储从-1.79E + 308至-2.23E –308、0以及2.23E– 308至1.79E + 308范围的浮点数。n有两个值, 如果指定的n在1~24之间,则使用24,占用4字 4 字 节 或 8 字 节 节空间;如果指定的n在25~53之间,则使用53, 占用8字节空间。若省略(n),则默认为53
存储从–3.40E + 38到3.40E + 38范围的浮点型数 4字节

real

字符串类型 ?普通编码字符串类型 ?统一字符编码字符串类型 ?二进制字符串类型

普通编码字符串类型
普通编码 字符串类型 char(n)
说明 存储空间

固定长度的普通编码字符串类 n个字节。当实际字符串 型,n表示字符串的最大长度, 所需空间小于n时,系统 取值范围为1~8000 自动在后边补空格 可变长度的字符串类型,n表 示字符串的最大长度,取值范 字符数+2字节额外开销 围为1~8000 最 多 可 存 储 231-1 每个字符1个字节 (2,147,483,647) 个字符 字符数+2字节额外开销

varchar(n)
text

varchar(max) 最多可存储231-1个字符

说明:如果在使用char(n)或varchar(n)类型时未指定n,则默认长度 为1。如果在使用CAST和CONVERT函数时未指定n,则默认长度为30。

统一字符编码字符串类型
统一编码 字符串类型 nchar(n) 说明 存储空间

nvarchar(n)

2n字节。当实际字符 固定长度的统一编码字符串 串所需空间小于2n时, 类型,n表示字符串的最大长 系统自动在后边补空 度,取值范围为1~4000 格 可变长度的统一编码字符串 2*字符数+2字节额外 类型,n表示字符串的最大长 开销 度,取值范围为1~4000

最 多 可 存 储 230-1 ntext (1,073,741,823) 个 统 一 字 每个字符2个字节 符编码的字符 最多可存储230-1个统一字符 2*字符数+2字节额外 nvarchar(max) 编码的字符 开销

二进制字符串类型
二进制 字符串类型 binary(n) 说明 存储空间

varbinary(n)
image

固定长度的二进制数据,n的 n字节 取值范围为1~8000 可变长度的二进制数据,n的 字 符 数 + 2 字 节 额 取值范围为1~8000。 外开销
可变长度的二进制数据,最多 为 231-1 (2,147,483,647) 个 每个字符1个字节 十六进制数字 可变长度的二进制数据,最多 字符数+2字节额 为 231-1 (2,147,483,647) 个 外开销 十六进制数字

varbinary(max)

日期时间类型
日期时间 类型
说明

存储空 间

date

time[(n)]

定义一个日期,范围为 到 。字符长度10位,默认格式 为 : YYYY-MM-DD 。 YYYY 表 示 4 位 年 份 数 字 , 范 围 从 0001到9999;MM表示2位月份数字,范围从01到12; 3字节 DD表示2位日的数字,范围从 01 到 31(最大值取决于 具体月份) 定义一天中的某个时间,该时间基于24小时制。默认格 式为:hh:mm:ss[.nnnnnnn],范围为00:00:00.0000000到 23:59:59.9999999。精确到100纳秒。 3~5字节 n为秒的小数位数,取值范围是0到7的整数。默认秒的 小数位数是7(100ns) 定义一个采用24小时制并带有秒的小数部分的日期和时 间,范围为到-,时间范围是00:00:00到23:59:59.997。默 认格式为:YYYY-MM-DD hh:mm:ss.nnn,n为数字, 8字节 表示秒的小数部分(精确到0.00333 秒)

datetime

日期时间类型(续)
日期时间 类型 smalldatetime 说明 存储空间 定义一个采用24小时制并且秒始终为零(:00)的日期和时间,范围 4字节 为到。默认格式为:YYYY-MM-DD hh:mm:00。精确到1分钟 定 义 一 个 结 合 了 24 小 时 制 时 间 的 日 期 。 可 将 该 类 型 看 成 是 datetime类型的扩展,其数据范围更大,默认的小数精度更高, 并 具 有 可 选 的 用 户 定 义 的 精 度 。 默 认 格 式 是 : YYYY-MM-DD hh:mm:ss[.nnnnnnn],n为数字,表示秒的小数位数(最多精确 6~8字节 到100 纳秒),默认精度是7位小数。该类型的字符串长度最少 19 位 ( YYYY-MM-DD hh:mm:ss ) , 最 多 27 位 (YYYY-MM-DD hh:mm:ss.0000000)

datetime2

定义一个与采用 24 小时制并与可识别时区的一日内时间相组合 的日期,该数据类型使用户存储的日期和时间(24小时制)是时 区一致的。语法格式为:datetimeoffset [(n)],n为秒的精度,最 大为7。默认格式为:YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|datetimeoffset }hh1:mm1],其中hh1的取值范围为-14 到 +14,mm1的取值范围 8~10字节 为00 到 59。该类型的日期范围为到,时间范围为00:00:00 到 23:59:59.9999999。时区偏移量范围为-14:00 到+14:00。该类型的 字符串长度为:最少26位(YYYY-MM-DD hh:mm:ss {+|-}hh:mm), 最多34位 (YYYY-MM-DD hh:mm:ss.nnnnnnn {+|-}hh:mm)

货币类型

货币类型

说明 存 储 –922,337,203,685,477.5808 到 922,337,203,685,477.5807范围的数值,精确到 小数点后4位

存储空间

money

8字节

smallmoney

存储-214,748.3648 到214,748.3647范围的数值, 精确到小数点后4位 4字节

3.3 创建数据库
?3.3.1 ?3.3.2 ?3.3.3 ?3.3.4 SQL Server数据库分类 数据库基本概念 用图形化方法创建数据库 用T-SQL语句创建数据库

3.3.1 SQL Server数据库分类
?两大类:
? 系统数据库:存放维护系统正常运行的 信息。 ? 用户数据库:存放用户自己的信息。

系统数据库
?Master:最重要数据库,用于记录所有系统 级信息。 ?Msdb:保存关于报警、作业、操作员等信息 ?Model:包含所有用户数据库的共享信息。 当用户创建数据库时,系统自动将model数据 库中的内容复制到新建数据库中。 ?Tempdb:临时数据库,用于存储用户创建的 临时表、用户声明的变量以及用户定义的游 标数据等。

3.3.2 数据库基本概念
?1.SQL Server数据库的组成 ?2.关于数据的存储分配 ?3.数据库文件的属性

1.SQL Server数据库的组成
?SQL Server将数据库映射为一组操作系 统文件,这些文件被划分为两类:数据 文件和日志文件。 ?数据文件包含数据和对象,例如表、索 引、存储过程和视图等。 ?日志文件包含恢复数据库中的所有事务 需要的信息。 ?数据和日志信息不混合在同一个文件中 ,一个文件只由一个数据库使用。

数据文件
?用于存放数据库数据。数据文件又分为: ?主要数据文件:
? ? ? ? ? 推荐扩展名是.mdf, 包含数据库的系统信息,也可存放用户数据。 每个数据库都有且只能有一个主要数据文件。 是为数据库创建的第一个数据文件。 不能小于3MB。

?次要数据文件:
? 推荐扩展名是.ndf。 ? 一个数据库可以包含0—n个次要数据文件。 ? 可与主要数据文件存放在不同地方。

?所有文件的使用对用户也是透明的。

日志文件
?推荐扩展名为.ldf。 ?用于存放恢复数据库的所有日志信息。 ?每个数据库有1—n个日志文件。

2.关于数据的存储分配
?数据库中数据的存储分配单位是数据页。 一页是一块8KB(其中用8060个字节存放数 据,另外132个字节存放系统信息)的连续 磁盘空间。 ?页是存储数据的最小空间分配单位。 ?不允许表中的一行数据存储在不同页上( varchar(max)、nvarchar(max)、text、 ntext、varbinary(max)和image数据类型 除外),即行不能跨页存储。

示例
?假设某数据表有10000行数据,每行3000 字节,估算其需要的空间: (10000/2) * 8KB = 40MB ?其中,每页中有6000字节用于存储数据 ,有2060个字节是浪费的。
数据页 数据页 数据页 3000字节

3000字节

...

2060字节

3.数据库文件的属性
?文件名及其位置:每个文件都有一个逻 辑文件名和物理文件名及物理存储位置 ?初始大小:可以指定每个文件的初始大 小。 ?增长方式:可以指定文件是否自动增长 ?最大大小:可以指定文件增长的最大空 间限制

3.3.3 用图形化方法创建数据库
?在SSMS的“对象 资源管理器”中 ,在“数据库” 节点上右击鼠标 ,在弹出的菜单 中选择“新建数 据库”命令; ?在“新建数据库 ”窗口中进行相 应设置。

3.3.4 用T-SQL语句创建数据库
CREATE DATABASE database_name [ ON [ PRIMARY ] [ <filespec> [ ,...n ] [ LOG ON { <filespec> [ ,...n ] } ] ] ]
<filespec> ::= { ( NAME = logical_file_name , FILENAME = { 'os_file_name' | 'filestream_path' } [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE ={max_size[KB|MB|GB|TB]| UNLIMITED } ] [ , FILEGROWTH =growth_increment[KB|MB|GB|TB|% ] ] ) [ ,...n ] }

示例1
?例1.创建一个名为“学生管理数据库” 的数据库,其他选项均采用默认设置。 CREATE DATABASE 学生管理数据库

示例2
CREATE DATABASE RShDB ON ( NAME = RShDB, FILENAME = 'D:\RShDB_Data\RShDB.mdf ', SIZE = 10, MAXSIZE = 30, FILEGROWTH = 5 ) LOG ON ( NAME = RShDB_log, FILENAME = 'D:\RShDB_Data\RShDB_log.ldf ', SIZE = 3, MAXSIZE = 12, FILEGROWTH = 2 )

示例3
CREATE DATABASE students ON PRIMARY ( NAME = students, FILENAME = 'F:\Data\students.mdf', SIZE = 3MB, MAXSIZE = UNLIMITED ), ( NAME = students_data1, FILENAME = 'D:\Data\students_data1.ndf', SIZE = 5MB, MAXSIZE = 10MB, FILEGROWTH = 1MB ) LOG ON ( NAME = students_log, FILENAME = 'F:\Data\students_log.ldf', SIZE = 2MB, MAXSIZE = 6MB, FILEGROWTH = 10% )

3.4 创建与维护关系表
?3.4.1 用T-SQL语句实现 ?3.4.2 用SSMS工具实现

3.4.1 用T-SQL语句实现
?1 ?2 ?3 ?4 创建表 定义完整性约束 修改表结构 删除表

1 创建表
CREATE TABLE <表名>(

<列名> <数据类型> [列级完整性约束定义] {, <列名> <数据类型> [列级完整性约束定义] … } [, 表级完整性约束定义 ] )

2 定义完整性约束
?NOT NULL:限制列取值非空。 ?DEFAULT:给定列的默认值。 ?UNIQUE:限制列取值不重。 ?CHECK:限制列的取值范围。 ?PRIMARY KEY:定义主码。 ?FOREIGN KEY:定义外码。 ?说明:
? NOT NULL和DEFAULT只能定义在列级完整性约束处 ? 多列的CHECK约束只能定义在表级完整性约束处

主码约束
?语法格式: PRIMARY KEY [(<列名> [, … n] )] ?如果是在列级完整性约束处定义单列的 主码,可省略方括号部分。

外码约束
?语法格式为: [FOREIGN KEY (<列名>)] REFERENCES <外表名>(<外表列名>)

?如果是在列级完整性约束处定义单列的 外码,可省略方括号部分。

UNIQUE约束
?用于限制在一个列中不能有重复的值。 ?语法格式为:
<列名> 数据类型 UNIQUE [(<列名> [, … n] )]

?如果是在列级完整性约束处定义单列的 UNIQUE约束,则可以省略括号部分。

DEFAULT约束
?用于提供列的默认值。 ?一个列只能有一个默认值约束,而且一 个默认值约束只能用在一个列上。 ?只能定义在列级完整性约束处。 ?语法格式: <列名> DEFAULT 默认值

CHECK约束
?用于限制列的输入值在指定的范围内。 ?语法格式: CHECK (逻辑表达式)
?说明:逻辑表达式中不能包含来自多个 表的列。

Student表结构
列名 Sno Sname 含义 学号 姓名 数据类型 CHAR(7) NCHAR(5) 主码 非空 约束

SID
Ssex Sage Sdept

身份证号
性别 年龄 所在系

CHAR(18)
NCHAR(1) TINYINT NVARCHAR(20)

取值不重
默认值为“男” 取值范围为15~45

定义Student表
CREATE TABLE Student ( Sno CHAR(7) PRIMARY KEY, Sname NCHAR(5) NOT NULL, SID CHAR(18) UNIQUE, Ssex NCHAR(1) DEFAULT '男', Sage TINYINT CHECK(Sage>=15 AND Sage<=45), Sdept NVARCHAR(20) )

Course表结构
列名 Cno 含义 课程号 数据类型 CHAR(6) 约束 主码

Cname Credit Semester

课程名 学分 学期

NVARCHAR(20) NUMERIC(3,1) TINYINT

非空 大于0

定义Course表
CREATE TABLE Course ( Cno CHAR(6) PRIMARY KEY, Cname NVARCHAR(20) NOT NULL, Credit NUMERIC(3,1) CHECK(Credit>0), Semester TINYINT )

SC表结构

列名 Sno

含义 学号

数据类型 CHAR(7)

约束 主码列,引用 Student的外码 主码列,引用 Course的外码

Cno
Grade

课程名
成绩

CHAR(6)
TINYINT

定义SC表
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) )

3 修改表结构
?在定义基本表之后,如果需求有变化, 比如添加列、删除列或修改列定义, 可以使用ALTER TABLE语句实现。 ?ALTER TABLE语句可以对表添加列、删 除列、修改列的定义,也可以添加和 删除约束。

ALTER TABLE语句语法
ALTER TABLE <表名> [ ALTER COLUMN <列名> <新数据类型>] | [ADD <列名> <数据类型> <约束>] | [DROP COLUMN <列名>] | [ADD 约束定义] | [DROP <约束名>]

示例
?例2.为SC表添加“修课类别”列,此列 的定义为:Type NCHAR(1),允许空。 ALTER TABLE SC ADD Type NCHAR(1) NULL ?例3.将新添加的Type列的数据类型改为 NCHAR(2)。 ALTER TABLE SC ALTER COLUMN Type NCHAR(2)

示例
?例4.为Type列添加限定取值范围为{必修 、选修}的约束。 ALTER TABLE SC
ADD CHECK(Type IN ('必修', '选修') )

?例5.删除SC表的“Type”列。
ALTER TABLE SC DROP COLUMN Type

4 删除表
? 当不再需要某个表时,可以将其删除。 ? 删除表的语句格式为: DROP TABLE <表名> { [, <表名> ] … } ? 例:删除test表: DROP TABLE test

3.4.2 用SSMS工具实现
?在数据库的“表 ”节点上右击鼠 标,在弹出的单 中选择“新建表 ”命令。 ?在表设计器窗格 中进行相应的设 置。


相关文章:
第三章关系数据库标准语言SQL--T-sql数据定义
第三章关系数据库标准语言SQL--T-sql数据定义_计算机软件及应用_IT/计算机_专业资料。数据库基础教程 关系数据库标准语言 SQL Transact-SQL 语言 ---T-SQL 简介...
第3章 SQL语言
第3章 sql 语言基础 67页 2财富值 第3章 SQL语言 111页 8财富值 第3章 ...(87 页) 答:SQL数据定义功能包括定义表、定义视图和定义索引 3.用 SQL ...
第3章 关系数据库标准语言SQL答案
(5)语言简捷,易学易用。 2 .试述 sQL 的定义功能。 sQL数据定义功能包括定义表、定义视图和定义索引。 SQL 语言使用 cREATE TABLE 语句建立基本表, ALTER ...
第3章_SQL语言
第3章_T-SQL语言基础 26页 1财富值 第3章 SQL 语言简介 16页 免费 第3章...SQL 语言主要有四大功能: (1) 数据定义语言(Data Definition Language,简称 DDL...
第三章 SQL语言练习题和答案
第三章 SQL 语言 一、选择题 1. SQL 语言是(B )的语言,容易学习 。 A....SQL 语言集数据查询、 数据操纵、 数据定义和数据控制功能于一体, 其中, CREATE...
第三章 关系数据库标准语言SQL
第三章 关系数据库标准语言 SQL SQL (Structured Query Language)语言是 1974 ...因此 SQL数据定义功能包括定义表、定义视图和定索引, 如表 3.2 所示。 ...
《数据库原理及应用》考试大纲 (1)
第三章基本概念与知识: SQL 语言基础及数据定义功能 SQL 语言概述(SQL 语言的发展、SQL 语言的特点、语言功能概述)、数据类 型(数值类型★、字符串类型★、日期...
第三章关系数据库标准语言SQL
如要投诉违规内容,请到百度文库投诉中心;如要提出功能问题或意见建议,请点击此处进行反馈。 第三章关系数据库标准语言SQL 简单实用的SQL语言课程简单实用的SQL语言...
第3章 关系数据库标准语言SQL(习题集)
第三章 习题集 二、选择题 1、SQL 语言是( B )的语言,容易学习 。 A....SQL 语言集数据查询、数据操纵、数据定义和数据控制功能于一体,其中,CREATE、 ...
第3章 关系数据库标准语言SQL
的语法,熟悉 SQL 数据定义语言(DDL)语句,掌握 SQL 语言中数据查询、数据操纵语言的详细语法,并能深刻理解、综合应用,以便为今后深层 次的学习打下更加坚实的基础...
更多相关标签: