创建数据表语法格式

Size: px

Start display at page:

Download "创建数据表语法格式"

凭刀邰
7 years ago
Views:

1 第三章 : 关系数据库标准语言 SQL 创建和管理数据库数据表 1

2 创建数据库语法格式数据库名 CREATE DATABASE database_name [ ON 指定数据库的数据文件和文件组指定主文件 [ PRIMARY ] [ <filespec> [,...n ] [, <filegroup> [,...n ] ] [ LOG ON { <filespec> [,...n ] } ] 指定数据库文件的属性定义文件组的属性 ] 指定事务日志文件的属性 [ COLLATE collation_name ] [ WITH <external_access_option> ] [ FOR { ATTACH ATTACH_REBUILD_LOG }] ] 2

3 创建数据库语法格式 <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 ]} 返回 3

4 创建数据库语法格式 <filegroup> ::= 文件组的名称 { FILEGROUP filegroup_name [ CONTAINS FILESTREA [ DEFAULT ] <filespec> [,...n ] } 指定属于该文件组的文件, 文件组中各文件的描述和数据文件描述相同 4

5 例 1: 要求创建一个学生 - 课程数据库 ( 名称为 student) Create database student 例 2: 要求在本地磁盘 D 的 data 文件夹中创建一个学生 - 课程数据库 ( 名称为 student1), 只有一个数据文件和日志文件, 文件名称分别为 stu 和 stu_log, 初始大小都为 3MB, 增长方式分别为 10% 和 1MB, 数据文件最大为 500MB, 日志文件大小不受限制 CREATE DATABASE student1 ON ( NAME = stu, FILENAME = D:\data\stu.mdf', SIZE = 3MB, MAXSIZE = 500MB, FILEGROWTH = 10%) LOG ON ( NAME = stu_log, FILENAME = D:\data\stu_log.ldf, SIZE = 3MB, MAXSIZE = unlimited,filegrowth = 1MB ) 5

6 练习 1 创建一个名为 TEST1 的数据库, 其初始大小为 5MB, 最大大小为 50MB, 允许数据库自动增长, 增长方式是按 10% 比例增长日志文件初始为 2MB, 最大可增长到 5MB, 按 1MB 增长数据文件和日志文件的存放位置为 SQL Server 的数据库目录 create database test1 on ( name='test1_data', filename='c:\program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test1.mdf', size=5mb, maxsize=50mb, filegrowth=10% ) log on ( name='test1_log', filename='c:\program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test1.ldf', size=2mb, maxsize=5mb, filegrowth=1mb ); 6

7 练习 2 创建一个名为 TEST2 的数据库, 它有两个数据文件, 其中, 主数据文件为 20MB, 最大大小不限, 按 10% 增长 1 个辅数据文件为 20MB, 最大大小不限, 按 10% 增长, 有 1 个日志文件, 大小为 50MB, 最大大小为 100MB, 按 10MB 增长 create database test2 on primary ( name='test2_data1', filename='d:\data\test2_data1.mdf', size=20mb, maxsize=unlimited, filegrowth=10% ), ( name='test2_data2', filename='d:\data\test2_data1.ndf', size=20mb, maxsize=unlimited, filegrowth=10% ) log on ( name='test2_log1', filename='d:\data\test2_log1.ldf', size=50mb, maxsize=100mb, filegrowth=10mb ); 7

8 练习 3 创建一个具有 2 个文件组的数据库 TEST3 要求: (1) 主文件组包括文件 TEST3_dat1, 文件初始大小为 20MB, 最大为 60MB, 按 5MB 增长 ;(2) 有 1 个文件组名为 TEST3Group1, 包括文件 TEST1_dat2, 文件初始大小为 10MB, 最大为 30MB, 按 10% 增长 create database test3 on primary ( name='test3_dat1', filename='d:\data\test3_dat1.mdf', size=20mb, maxsize=60mb, filegrowth=5mb ), filegroup test3group1 ( name='test3_dat2', filename='d:\data\test3_dat2.ndf', size=10mb, maxsize=30mb, filegrowth=10% ); 8

9 修改数据库语法格式 Alter database database_name Alter {add file database <filespec>[, n] database_name [to filegroup filegroupname] {add file <filespec>[, n] [to filegroup filegroupname] 在文件组中增加数据文件 add log file <filespec>[, n] add remove log file logical_file_name <filespec>[, n] [with delete] remove modify file logical_file_name <filespec> [with delete] modify file name=new_databasename <filespec> modify add filegroup name=new_databasename filegroup_name add remove filegroup filegroup_name remove modify filegroup filegroup_name modify } filegroup filegroup_name 在文件组中增加数据文件增加日志文件增加日志文件删除数据文件删除数据文件更改文件属性更改文件属性数据库更名数据库更名增加文件组增加文件组删除文件组删除文件组更改文件组属性更改文件组属性 } 9

10 练习 1 修改数据库 TEST1 现有数据文件 TEST1_DATA 的属性, 将主数据文件的最大大小改为 100MB, 增长方式改为按每次 5MB 增长 alter database test1 modify file ( name=test1_data, maxsize=100mb, filegrowth=5mb ) 10

11 练习 2 先为数据库 TEST1 增加数据文件 TEST1BAK 然后删除该数据文件 alter database test1 add file ( name='test1bak', filename='d:\data\test1bak.ndf', size=10mb, maxsize=50mb, filegrowth=5% ) alter database test1 remove file test1bak 11

12 练习 3 为数据库 TEST1 添加文件组 FGROUP, 并为此文件组添加两个大小均为 10MB 的数据文件 alter database test1 add filegroup fgroup go alter database test1 add file ( name='test1_data4', filename='c:\program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test1_data4.ndf', size=10mb ), ( name='test1_data5', filename='c:\program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\test1_data5.ndf', size=10mb ) to filegroup fgroup 12

13 练习 4 将上题中添加的文件组 FGROUP 删除 ( 被删除的文件组中的数据文件必须先删除, 且不能删除主文件组 ) alter database test1 remove file test1_data4 alter database test1 remove file test1_data5 alter database test1 remove filegroup fgroup 13

14 练习 5 为数据库 TEST1 添加一个日志文件 alter database test1 add log file ( name='test1_log2', filename='d:\data\test1_log2.ldf', size=5mb, maxsize=10mb, filegrowth=1mb ) 6 将上题中添加的日志文件删除 ( 不能删除主日志文件 ) alter database test1 remove file test1_log2 14

15 删除数据库语法格式 DROP DATABASE, 数据库名组例 1: 将数据库 student 删除, 可使用下述语句 DROP DATABASE student GO 注 : 使用 DROP DATABASE 语句不会出现确认信息, 要小心使用, 并且不能删除系统数据库, 否则将导致服务器无法使用 15

16 创建及管理数据表建立数据库最重要的一步就是创建其中的数据表, 即决定数据库包括哪些表, 每个表中包含哪些字段, 每个字段的数据类型等创建表的实质就是定义表结构及约束等属性, 在创建数据表之前, 先要设计表, 即确定表的名字所包含的各字段名字段的数据类型长度是否可为空值等等, 这些属性构成表结构 16

17 创建及管理数据表主要内容 : 常见数据类型界面方式下创建及编辑数据表命令方式下创建及编辑数据表 17

18 常见数据类型 18

19 整数型 19

20 精确数型精确数值型数据由整数部分和小数部分构成, 其所有的数字都是有效位, 能够以完整的精度存储十进制数精确数值型包括 decimal 和 numeric 两类, 在 SQL Server2008 中, 这两种数据类型在功能上完全等价数据的存储长度随精度变化而变化, 最少为 5 个字节, 最多为 17 个字节声明精确数值型数据的格式是 numeric decimal(p[,s]), 其中 p 为精度, s 为小数位数,s 的默认值为 0 声明精确数值型数据时, 其小数位数必须小于精度 20

21 浮点型浮点型也称近似数值型, 这种类型丌能提供精确表示数据的精度, 使用这种类型来存储某些数值时, 可能会损失一些精度, 所以它可用于处理取值范围非常大且对精确度要求丌太高的数值量, 如一些统计量有两种浮点型 :float[(n)] 和 real 两者通常使用科学计数法表示数据 21

22 货币型当向表中插入 money 或 smallmoney 类型的值时, 必须在数据前面加上货币表示符号 ($), 并且数据中间丌能有逗号 (,); 若货币值为负数, 则需要在符号 $ 的后面加上负号 (-) 例如 $ $ $680 22

23 字符型 char[(n)] 定长字符数据类型, 其中 n 定义字符型数据的长度,n 在 1 到 8000 之间, 缺省为 1 当表中的列定义为 char(n) 类型时, 若实际要存储的串长度丌足 n 时, 则在串的尾部添加空格以达到长度 n, 所以 char(n) 的长度为 n varchar[(n)] 变长字符数据类型, 其中 n 的规定不定长字符型 char 中 n 完全相同, 但这里 n 表示的是字符串可达到的最大长度 varchar(n) 的长度为输入的字符串的实际字符个数, 而丌一定是 n 当列中的字符数据值长度接近一致时, 如姓名, 可使用 char; 而当列中的数据值长度显著丌同时, 使用 varchar 较为恰当, 可以节省存储空间 23

24 文本型用于存储大量的字符数据, 如较长的备注日志信息等 text: 数据的存储长度为实际字符数字节 ; ntext: 表示 Unicode 字符, 数据的存储长度是实际字符数的两倍 24

25 二进制型二进制数据类型表示的是位数据流, 包括 binary( 固定长度 ) 和 varbinary ( 可变长度 ) binary[(n)]: 固定长度的 n 字节二进制数据数据的存储长度为 n+4 字节 varbinary[(n)]:n 字节变长二进制数据数据的存储长度为实际输入数据长度 +4 字节 25

26 日期时间型用于存储日期和时间信息 date: 表示从公元元年 1 月 1 日至 9999 年 12 月 31 日的日期, 只存储日期数据, 丌存储时间数据, 存储长度为 3 字节 time: 只存储时间数据表示格式为 hh:mm:ss[.nnnnnnn], 存储大小为 5 字节 datetime: 存储日期和时间信息, 存储大小随着微秒数的位数 ( 精度 ) 而改变, 精度小于 3 时为 6 字节, 精度为 4 和 5 时为 7 字节, 所有其他精度则需要 8 字节 datetimeoffset: 存储日期和时间信息, 具有偏移量 26

27 创建数据表语法格式 CREATE TABLE [database_name. [schema_name]. schema_name. ] table_name ( 数据库名新表所属架构名 { < column_definition > /* 列的定义 */ column_name AS computed_column_expression [PERSIRTED [NOT NULL]] 表名 } 列名 /* 定义计算列 */ 计算表达式 ) [<table_constraint>][, n] /* 指定表的约束 */ 分区列名指定存储表的文件组 [ON { partition_scheme_name ( partition_column_name ) filegroup default }] 分区方案名 [{TEXTIMAGE_ON { filegroup default }] /* 指定分区方案和存储表的文件组 */ /* 指定存储 text ntext image 等类型数据的文件组 */ 没有指定或指定为 default, 包含这些类型的列将与表存储在同一文件组中 [ FILESTREAM_ON { partition_scheme_name filegroup default }] [ WITH (< table_option> [, n])] /* 指定表选项 */ /* 指定存储 FILESTREAM 数据的文件组 */ 27

constant_expression ] /* 指定默认值 */ [ IDENTITY [( seed,increment )][NOT FOR REPLICATION ] /* 指定列为标识列 */ [

28 列的定义格式 < column_definition > ::= column_name data_type /* 指定列名类型 */ [ FILESTREAM ] /* 指定 FILESTREAM 属性 */ [ COLLATE collation_name] /* 指定排序规则 */ [ NULL NOT NULL] /* 指定是否为空 */ [ ] [CONSTRAINT constraint_name ] DEFAULT constant_expression ] /* 指定默认值 */ [ IDENTITY [( seed,increment )][NOT FOR REPLICATION ] /* 指定列为标识列 */ [ ROWGUIDCOL ] 排序规则名称表示 PRIMARY KEY UNIQUE FOREIGN KEY 或 CHECK 约束定义的开始 [<column_constration>[ n] /* 指定列为全局标识符列 */ [SPARSE] 28

29 以学生管理管理系统数据库为例, 创建学生表 (XSB) 课程表 (KCB) 和成绩表 (CJB) 列名数据类型长度是否为空默认值说明 Stu_ID char 6 否无学号, 主键 Sname char 8 否无姓名 Ssex bit 默认值是 1 性别 Sdate date 默认值是无出生日期 Major char 12 是无专业 Tcredit int 默认值是 0 总学分 Remark varchar 500 是无备注列名数据类型长度是否为空默认值说明 C_ID char 3 否无课程号, 主键 Cname char 16 否无课程名 Term tinyint 1 是 1 开课学期 Chour tinyint 1 是 0 学时 Credit tinyint 1 否 0 学分列名数据类型长度是否为空默认值说明 Stu_ID char 6 否无学号, 主键 C_ID char 3 否无课程号, 主键 Grade int 默认值是 0 成绩 29

30 create table PXSCJ..XSB ( Stu_ID char(6) not null primary key, Sname char(8) not null, Ssex bit null, Sdate date null, Major char(12) null, Tcredit int null, Remark varchar(500) null ) create table PXSCJ..KCB ( C_ID char(3) not null primary key, Cname char(16) not null, Term tinyint null default 1, Chour tinyint null, Credit tinyint not null ) create table PXSCJ..CJB ( Stu_ID char(6) not null, C_ID char(3) not null, Grade int null primary key(stu_id,c_id) ) 30

31 修改表结构语法格式 ALTER TABLE [ database_name. [ schema_name ]. schema_name. ] table_name { 修改成数值类型时, ALTER COLUMN column_name 指定数值的精度和 /* 修改已有列的属性 */ { 小数位数 type_name [ ( { precision [, scale ] [ COLLATE collation_name ] 排序规则 [ NULL NOT NULL ] {ADD DROP } { ROWGUIDCOL PERSISTED NOT FOR REPLICATION SPARSE } } ADD 列的定义计算列的定义 /* 添加列 */ { <column_definition> <computed_column_definition> 表的完整性约束 <table_constraint> } [,...n ] DROP { [ CONSTRAINT ] constraint_name /* 删除约束 */ [ WITH ( <drop_clustered_constraint_option> [,...n ] ) ] COLUMN column_name /* 删除列 */ } [,...n ] [ WITH { CHECK NOCHECK } ] { CHECK NOCHECK } CONSTRAINT { ALL constraint_name [,...n ] } 31

32 1 在 XSB 中增加 1 个新列奖学金等级 ; alter table PXSCJ..XSB add 奖学金等级 tinyint null; 2 在上题的基础上, 删除奖学金等级列 ; alter table PXSCJ..XSB drop column 奖学金等级 ; 3 修改 XSB 中已有列的属性 : 将名为姓名的列长度由原来的 8 改为 10,; 将名为出生时间的列的数据类型由原来的 date 改为 samlldatetime alter table PXSCJ..XSB alter column Sname char(10); alter table PXSCJ..XSB alter column Sdate smalldatetime; 32

33 删除数据表语法格式 DROP TABLE [ database_name. [ schema_name ]. schema_name. ] table_name [,...n ] [ ; ] 33

$..n ] ] /* 指定临时结果集, 在 SELE INSERT [ TOP ( expression ) [ PERCENT ] ] [ INTO ] { table_name /* 表名 */ } view_name /* 视图名 */ rowset_function_limited [ WITH ( <Table_Hint_Limited> [.$

34 插入表数据语法格式 [ WITH <common_table_expression> [,...n ] ] /* 指定临时结果集, 在 SELE INSERT [ TOP ( expression ) [ PERCENT ] ] [ INTO ] { table_name /* 表名 */ } view_name /* 视图名 */ rowset_function_limited [ WITH ( <Table_Hint_Limited> [...n ] ) ] /* 指定表提示 */ { 新插入数据的各列的名称 [ ( column_list ) ] /* 列列表 */ 执行插入操作时返回插入的行 [ <OUTPUT Clause> ] /*OUTPUT 子句 */ { VALUES ( { DEFAULT NULL expression } [,...n ] ) [,...n ] /* 指定列值的 value 子句 */ derived_table /* 结果集 */ <dml_table_source> 34

35 练习 1 向 XSB 中插入如下一行数据 :081101, 王林,1, , 计算机,50, null insert into XSB values('081101',' 王林 ',1,' ',' 计算机 ',50,NULL); 2 一次向 XSB 表中插入两行数据 :091101, 王海,1, , 软件工程, 50,null;091102, 李娜,0, , 软件工程,52,null insert into XSB values('091101',' 王海 ',1,' ',' 软件工程 ',50,NULL), ('091102',' 李娜 ',0,' ',' 软件工程 ',52,NULL); 35

$..n ] ] /* 指定临时结果集 */ DELETE [ TOP ( expression ) [ PERCENT ] ] [ FROM ] { table_name /* 从表中删除数据 */ view_name /* 从视图删除数据 */ rowset_function_limited [ WITH ( <table_hint_limited> [.$

36 [; ] 删除记录语法格式 [ WITH <common_table_expression> [,...n ] ] /* 指定临时结果集 */ DELETE [ TOP ( expression ) [ PERCENT ] ] [ FROM ] { table_name /* 从表中删除数据 */ view_name /* 从视图删除数据 */ rowset_function_limited [ WITH ( <table_hint_limited> [...n ] ) ] /* 指定表提示 */ } 执行插入操作时返回插入的行 [ <OUTPUT Clause> ] /*OUTPUT 子句 */ [ FROM <table_source> [,...n ] ] /* 从 table_source 删除数据 */ [ WHERE { <search_condition> /* 指定条件 */ { [ CURRENT OF { { [ GLOBAL ] cursor_name } cursor_variable_name}]} } /* 有关游标的说明 */ ] [ OPTION ( <Query Hint> [,...n ] ) ] /* 使用优化程序 */ 36

37 练习 1 将 pxscj 数据库的 xsb 表中总学分大于 52 的行删除 delete from XSB where Tcredit>25; 2 将 pxscj 数据库的 xsb 表中备注为空的行删除 delete from XSB where Remark IS NULL; 37

38 删除指定表中所有的行 TRUNCATE TABLE tb_name 38

39 修改表数据的语法格式 [ WITH <common_table_expression> [...n] ] UPDATE [ TOP ( expression ) [ PERCENT ] ] { table_name /* 修改表数据 */ view_name /* 修改视图数据 */ rowset_function_limited [ WITH ( <Table_Hint_Limited> [...n ] ) ] /* 指定目标表允许的一个或多个表提示 */ column_name } 列中某个部值中的起点分的长度 SET /* 赋予新值 */ { column_name = { expression DEFAULT NULL } /* 为列重新指定值 */ column_name {.WRITE ) } /* 指定修改列的一部分 = expression /* 指定变量的新值 = column = expression /* 指定列和变量的新值 */ column_name { += -= *= /= %= &= ^= = } { += -= *= /= %= &= ^= = } = column { += -= *= /= %= &= ^= = } expression } [,...n ] 复合赋值运算符 [ ; ] [ <OUTPUT Clause> ] /* 返回更新后的数据 */ [ FROM{ <table_source> } [,...n ] ] /* 指定为更新操作提供表 */ [ WHERE { <search_condition> /* 指定条件 */ { [ CURRENT OF /* 有关游标的说明 */ { { [ GLOBAL ] cursor_name } cursor_variable_name } ]} } ] [ OPTION ( <query_hint> [,...n ] ) ] /* 使用优化程序 */ 39

40 将 xsb 表中所有学生的总学分都增加 10 将姓名为罗琳琳的同学的专业改为软件工程, 备注改为提前修完学分, 学号改为 USE PXSCJ GO UPDATE XSB SET Tcredit=Tcredit+10; UPDATE XSB SET Major=' 软件工程 ',Remark=' 提前修完学分 ',Stu_ID='081261' WHERE Sname=' 罗琳琳 '; 试一试 : 将 xsb 表中姓名为张林的学生的出生日期由改为 ( 修改列的一部分 ) 40

41 几个问题修改表结构时, 关于 ALTER COLUMN ADD DROP COLUMN, 能否同时修改多列? 能否同时增加多列? 能否同时删除多列? ALTER TABLE PXSCJ.. XSB ADD 说明 ADD 等级 tinyint null, tinyint null; ALTER TABLE PXSCJ.. XSB DROP COLUMN 说明, DROP COLUMN 等级 ; ALTER TABLE PXSCJ.. XSB ALTER COLUMN ALTER COLUMN Sname char( 10 ), Sdate smalldatetime ; ALTER TABLE PXSCJ.. XSB ADD 说明 tinyint null, DROP COLUMN Ssex, ALTER COLUMN Sname char( 10 ); 41

几个问题插入记录时, VALUSE 中的数值个数是否一定要和 COLUMN_LIST 列列表

('081177',' 王二 ',1,'1990-02-10',' 计算机 ','',' 补考

42 几个问题插入记录时, VALUSE 中的数值个数是否一定要和 COLUMN_LIST 列列表中指定的列名个数一致? 插入空值能否省略? INSERT INTO XSB(Stu_ID,Sname,Ssex) VALUES('081170',' 王二 '); INSERT INTO XSB VALUES ('081177',' 王二 ',1,' ',' 计算机 ','',' 补考 '); INSERT INTO XSB VALUES ('081177',' 王二 ',1,' ',' 计算机 ',NULL,' 补考 '); 42

创建数据表语法格式

创建数据表语法格式创建数据库语法格式数据库名 CREATE DATABASE database_name [ ON 指定数据库的数据文件和文件组指定主文件 [ PRIMARY ] [ [,...n ] [, [,...n ] ] [ LOG ON { [,...n ] } ] 指定数据库文件的属性定义文件组的属性 ] 指定事务日志文件的属性 [