狠狠撸

数据库高级应用第 5 章 SQL Server 表的管理

5.1 SQL Server 表概述 5.2 表的创建与维护 5.3 表中数据的维护返回目录

数据类型 1 ．数值类型 2 ．字符串类型 3 ．二进制类型 4 ．日期时间类型 5 ．货币类型

1 ．数值类型整型数据类型 (1) bigint ：占 8 字节的存储空间，存储数据范围为 -2 63 ~2 63 -1 。 (2) int ：占 4 字节的存储空间，存储数据范围为 - 2 31 ~2 31 -1 。 (3) smallint ：占 2 字节的存储空间，存储数据范围为 -2 15 ~2 15 -1 。 (4) tinyint ：占 1 字节的存储空间，存储数据范围为 0~255 。

1 ．数值类型实型数据类型 (1) decimal[(p[,s])] ：小数类型。其中， p 为数值总长度，包括小数位数，但不包括小数点，范围 1~38 。 s 为小数位数。默认 decimal(18,0) 。 (2) numeric[(p[,s])] ：与 decimal[(p[,s])] 等价。 (3) float[(n)] ：浮点类型，占 8 字节的存储空间。 (4) real ：浮点类型，占 4 字节的存储空间。

2 ．逻辑类型只能存储 0 或 1 。当输入 0 和 1 以外的值时，系统自动转换为 1 。通常存储逻辑量，表示真与假。

3 ．字符串类型 (1) char[(n)] ：定长字符串类型，长度为 n ， n 范围为 1~8000 。默认为 char(10) 。 (2) text ：文本类型，实际也是变长字符串类型，存储长度超过 char(8000) 的字符串，最大长度 2 31 -1 。实际上， text 类型列中仅存储一个指针，数据本身存储在连接表中。 (3) varchar[(n)] ：变长字符串类型，即自动删除字符串尾部空格后存储。长度为 n ， n 范围为 1~8000 。默认为 char(50) 。

4 ．二进制类型 (1) binary[(n)] ：定长二进制类型，占 n+4 字节的存储空间， n 范围为 1~8000 。默认为 binary (50) 。 (2) varbinary[(n)] ：变长二进制类型，占 n+4 字节的存储空间， n 范围为 1~8000 。默认为 binary (50) 。 (3) image ：大量二进制类型，实际也是变长二进制类型，通常用于以位字符串形式存储图形，最大长度为 2 31 -1 ，约 2 GB 。

5 ．日期时间类型 SQL Server 2000 的日期时间类型数据同时包含日期和时间信息，没有单独的日期类型或时间类型。 (1)datetime ：日期时间类型，占 8 字节的存储空间，范围为 1753 年 1 月 1 日 -9999 年 12 月 31 日。 (2) smalldatetime ：日期时间类型，占 4 字节的存储空间，范围为 1900 年 1 月 1 日 -2079 年 12 月 31 日。

5 ．日期时间类型日期时间类型常量两端应加单引号。如果只指定日期，则时间默认为午夜 12 点 0 分 0 秒；如果只指定时间，则日期默认为 1900 年 1 月 1 日。如果省略世纪，当年 >=50 默认为 20 世纪， <50 默认 21 世纪。

6 ．货币类型 (1)money ：货币类型， 4 位小数，占 8 字节的存储空间。 (2)smallmoney ：货币类型， 4 位小数，占 4 字节的存储空间。货币类型常量应以美元符号“ $” 或所定义的金融单位符号作前缀。除了 SQL Server 2000 提供的数据类型外，如果需要，还可以定义新的数据类型，称为“自定义数据类型”。

为“学生选课系统”设计 E-R 模型。

将 E-R 模型转换为关系模型。转换的方法：把 E-R 图中的实体和 M∶N 的联系分别转换成关系模式，同时在实体标识符下加一横线表示模式的主键。联系关系模式的属性为与之联系的实体类型的主键和联系本身的属性，主键为与之联系的实体类型的主键的组合。 sc( sno,cno, score) 选课关系模式 c( cno, cname,credit) 课程关系模式 s( sno, class,sname,sex,birthday,address,telephone,email) 学生关系模式

5.1 SQL Server 表概述空值 (NULL) 空值不同于空白 ( 空字符串 ) 或数值零，通常表示未填写、未知 (Unknown) 、不可用或将在以后添加的数据。

5.1 SQL Server 表概述约束约束定义了对于列中允许值的规则， SQL Server 通过限制列中数据、行中数据和表之间数据来保证数据的完整性。 (1) 非空值约束 (Not Null) (2) 默认约束 (Default) (3) 惟一性约束 (Unique ） (4) 主键约束 (Primary Key ，也称主关键字约束 ) (5) 外键约束 (Foreign Key ，也称为外部关键字约束 )

实现数据组织方式 —建立表结构表的概念表（即关系）是关系数据库中用于存储数据的数据对象，数据只能存储在表中。 SQL Server 2000 有两类表：系统表（在创建数据库时由 Model 库复制得到）和用户表。要用数据库存储数据，首先必须创建用户表。

1. 设计数据库为“学生选课系统”设计名为 student 的数据库，由一个主数据文件和一个事务日志文件组成 , 并将其存放在“ e:\example” 下。 2. 设计表学生关系模式 : studinfo(sno,sname,ssex,birthday,address,telephone,e_mail), 主键 sno 课程关系模式 :course(cno,cname,credit), 主键 cno 选课关系模式 :sc(sno,cno,score), 主键 sno 、 cno, 外键 sno 、 cno 为“学生选课系统”设计名为 student 的数据库

创建表 1. 使用 SQL 语句 2. 使用 SQL-EM

1. 使用 SQL 语句 CREATE TABLE [< 数据库名 >.]< 表名 > (< 列名 > < 数据类型 > [< 列级完整性约束 >][,…n] [< 表级完整性约束 >] ） SQL Server 2000 数据完整性约束包括： (1) 主键完整性约束 (primary) ：保证列值的惟一性，且不允许为 NULL 。 (2) 惟一完整性约束 (unique) ：保证列值的惟一性。 (3) 外键完整性约束 (foreign) ：保证列值只能取参照表主键或惟一键的值或 NULL 。 (4) 非空完整性约束 (not null) ：保证列的值非 NULL 。 (5) 缺省完整性约束 (default) ：指定列的默认值。 (6) 检查完整性约束 (check) ：指定列取值的范围。

例创建本章开头示例中的表 studinfo 、 course 、 sc 。脚本： 1. 使用 SQL 语句

例创建“零件供应”表。脚本： 1. 使用 SQL 语句

2. 使用 SQL-EM (1) 启动 SQL-EM ，展开左侧窗口中的指定数据库，指向“表”结点，单击右键，选择“新建表”命令，打开“新表”窗口。 (2) 依次在“列名”框中输入字段名，在“数据类型”框中选择字段的数据类型，在“长度”框中输入字段长度。 (3) 单击“保存”图标。

例创建表 SC 。 (1) 启动 SQL-EM ，展开左侧窗口 student 数据库，指向“表”结点，单击右键，选择“新建表”命令。 (2) 输入字段名，选择数据类型及长度，指定主键。 (3) 保存并命名。

修改表 1. 使用 SQL 语句 ALTER TABLE [< 数据库名 >.]< 表名 > {[ALTER COLUMN < 列名 > < 数据类型 > [< 列级完整性约束 > ][,…n]] |ADD < 列名 > < 数据类型 > [< 列级完整性约束 >][,…n] |DROP COLUMN < 列名 > [,…n] } 2. 使用 SQL-EM 同创建表。

例在表 studinfo 中增加新的列 postcode 。脚本： ALTER TABLE studinfo ADD postcode char(6) NULL 修改表例删除表 studinfo 中的列 postcode 。脚本： ALTER TABLE studinfo DROP COLUMN postcode

修改表例在表 course 中增加主键约束，将 cno 设置为主键。脚本： ALTER TABLE course ADD CONSTRAINT pk_course PRIMARY KEY(cno) 例删除主键约束。脚本： ALTER TABLE course DROP CONSTRAINT pk_course

修改表例设置表 sc 中的列 sno 为外键。脚本： ALTER TABLE sc ADD CONSTRAINT fk_sc_studinfo FOREIGN KEY(sno) REFERENCES studinfo(sno) 例为表 sc 的列 score 增加检查约束。脚本： ALTER TABLE sc WITH NOCHECK ADD CONSTRAINT score_check CHECK(score>=0 and score<=100)

例对表 studinfo, 定义 sname 非空完整性约束、 sex 缺省完整性约束 ( 值“男” ) 、 e_mail 惟一完整性约束。 (1)SQL-EM->student-> 表-> studinfo -> 单击右键->设计表。 (2) 单击 sname 行“允许空”列，去掉对钩。 (3) 单击 sex 行，在“列”窗口“默认值”框中输入“‘男’”。 (4) 单击工具栏“索引 / 键”->新建，在“列名”框中选择 email ，选中“创建 UNIQUE” 复选框。修改表

例对表 sc, 定义 sno 为外键 , 参照表 studinfo 的 sno; 定义 cno 为外键 , 参照表 course 的 cno 。 (1) SQL-EM->student-> 表-> sc-> 单击右键->设计表->工具栏“管理关系”->新建。 (2) SQL-EM->student-> 关系图->单击右键->新建数据库关系图。修改表

删除表 1. 使用 SQL 语句 DROP TABLE table_name 2. 使用 SQL-EM 在公司管理器中，展开指定的数据库，选中“表”结点，指向要删除的表，单击右键，选择“除去表”命令。不能删除系统表，外键约束的参考表必须在取消外键约束或删除外键所在表之后才能删除。

例删除 student 数据库中的表 sc 。脚本： DROP TABLE sc 删除表

管理数据—编辑数据表由表结构和记录两部分组成，定义表实际上是定义了表的结构，当表结构定义完成后，则可以向表中插入数据、修改数据和删除数据。对数据的插入、修改和删除统称为数据的编辑。

使用 SQL-EM (1) 启动 SQL-EM ，单击左侧窗口要编辑记录的表所在数据库的“表”结点，指向右侧窗口中要编辑记录的表，单击右键，选择“打开表”->“返回所有行”命令。 (2) 如果需要插入数据，可以直接录入；如果需要删除记录，可以单击记录第一个列前的按钮以选中该记录，按 Del 键；如果需要修改数据，可以单击或将光标移至需要修改的位置修改。

使用 SQL 语句 INSERT 语句 DELETE 语句 UPDATE 语句

1. INSERT 语句格式一： INSERT [INTO] < 表名 >[(< 列名表 >)] VALUES(< 值列表 >) 格式二： INSERT [INTO] < 目标表名 >[(< 列名表 >)] SELECT < 列名表 > FROM < 源表名 > WHERE < 条件 >

例在表 studinfo 中插入一条学生记录。 insert into studinfo values(‘0801001',' 黄鹏 ',' 男 ','1981-10-12', ' 江苏省常州市 ','hp@163.com') 例在表 studinfo 中插入另一条学生记录。 insert into studinfo(sno, sname,ssex,birthday) values(‘0802001',' 张宇 ',' 男 ','1984-11-6',)

例将表 studinfo 中的男生记录插入到表 s_bak 中 , 假设表 s_bak 已存在且结构与表 studinfo 相同。 select * into stud_bak from studinfo go insert into stud_bak select * from studinfo where ssex=' 男 '

2. DELETE 语句 DELETE [FROM] < 表名 > [WHERE < 条件 >]

例删除表 stud_bak 中所有男生。脚本： DELETE FROM stud_bak WHERE ssex=' 男 '

3. UPDATE 语句格式一： UPDATE < 表名 > SET < 列名 >=< 表达式 >[,…] [WHERE < 条件 >] 格式二： UPDATE < 目标表名 > SET < 列名 >=< 表达式 >[,…] FROM < 源表名 > [WHERE < 条件 >]

例修改表 studinfo 中学号为“ 2001” 的学生记录。脚本： update studinfo set addr=‘ 北京 ',postcode='100086' where sno=‘0802001'

例将所有选修数据库应用课程的学生成绩加 5 分。脚本： update sc set score=score+5 from course where sc.cno=course.cno and cname=‘C 语言 '

实训数据库技术与应用实践教程实验 3

狠狠撸

第5章 sql server表的管理

Recommended

More Related Content

Viewers also liked (13)

Similar to 第5章 sql server表的管理 (12)

第5章 sql server表的管理