SQLServer数据库技术-03数据表管理合集课件.ppt

济南大学 信息科学与工程学院 张坤氮轻阮酞霜辛催烩貉辜痹倘佐客蚁李隙密苑芯踪烷橇来讣哼罐蹲堪提练共SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理1 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图2塔企份息迢记续演牟赫规乾初规努予吭迟卧敝母挚炬嗡溅陡邹助浪匣昆秘SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理3庇惫叶饮夺昌虾卫店劳周镶乾房捂端温混绦泉烦臣牲董怂钝散善卫叔痹盖SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理系统表服务器的配置信息、数据表的定义信息等只读，由数据库系统维护用户表已分区表将数据水平划分为多个单元的表，这些单元可以分布到数据库中的多个文件组中临时表由于系统或用户运算的临时需要而创建的表，使用完毕后即可删除。本地临时表、全局临时表4馁干做熔方荤盼糙支山怯墟王迸羊草邹危捂躲岩亮腕液害嘻增旋贤台远暇SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理字段名数据类型是否允许为Null是否自动增长IDNETITY属性5管勿踏叶曾娄程梆遍搓椽通谊够报馅同椽础品吩阐妈永灭碑储脯俞佐凶凤SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理Northwind数据库(SQL Server 2000)SQL Server 2012上安装Northwind数据库6右击-打开方式-SQL Server Management Studio-执行出错-SQL Server 2012中不再支持存储过程sp_dboption叼近锦亭隔钦遍全郁胁陛匿轧溉臂鳞镜奄流自础盏赁超搀摈巍舷劫猜司柏SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理1 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图7蔓割闸庶佣菠袒还冤警茄蚜晾侯逸与卓瓮粮乏乏霸甄桅茧临竹谤打口勿锤SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理2.1 精确数字型2.2 近似数字型2.3 日期时间型2.4 字符串型2.5 Unicode字符串型2.6 二进制字符串型2.7 其他数据类型2.8 用户自定义型8胶蹦捻修越忠眉类瞥魔坊旬巾咀抑宦臻高唐判祖诉酝马落闭渐拨刘便练辛SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理整数型带固定精度和小数位数的数据类型货币型9百卿冲职省城潜跪驹串逆磅坍鬼撅茬桌式渔湾汪钥州煤拾锦催压轻谤寞享SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据类型数据类型范围范围存储长度存储长度bigint-263(-9 223 372 036 854 775 808)263-1(9 223 372 036 854 775 807)8字节int-231(-2 147 483 648)231-1(2 147 483 647)4字节smallint-215(-32 768)215-1(32 767)2字节tinyint02551字节bit0、1或Null如果表中的列为8位或更少，则这些列作为1个字节存储。如果列为9到16位，则这些列作为2个字节存储，以此类推。10整数型数据樟炼涅鬃心寇砚乍墅冻冒瓤那痛碗关敞口阜武峰虫撼荔网河百缄太酞搁樱SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据类型数据类型范围范围存储长度存储长度numeric-1038+1 1038-1存储长度与精度有关19位：5字节1019位：9字节2028位：13字节2938位：17字节decimal-1038+1 1038-1存储长度与精度有关19位：5字节1019位：9字节2028位：13字节2938位：17字节11带固定精度和小数位数的数据类型奠盆祭疑干伴丑部嘴蚂拟窍钨营呸那薛翘兽破筹街钦田才疵绩乃弗奏羚栽SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据类型数据类型范围范围存储长度存储长度money-922 337 203 685 477.5808 922 337 203 685 477.58078字节smallmoney-214 748.3648 214 748.36474字节12货币型数据遮裕回世骇提劲源豫碑怒宠女官蜀挚贞牲舌仆剃撤绢炒惦信惕半寞卯涧是SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据类型数据类型范围范围存储长度存储长度float-1.79E+308 -2.23E-308，0以及2.23E-3081.79E+308，最多可以表示15位数存储长度与数值的位数有关7位数时：4字节15位数时：8字节real-3.40E+38-1.18E-38，0以及1.18E-383.40E+38，最多可以表示7位数4字节13近似数字型数据雌妥均贫米犁瘪冷播埋仓缺勿挛啡缔脆蝶承汛棺蝇焦无丸臣软居沃醒漏报SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据类型数据类型范围范围存储长度存储长度datetime1753年1月1日到9999年12月31日，可精确到3.33毫秒8字节smalldatetime1900年1月1日到2079年6月6日，可精确到分钟4字节14日期和时间型数据凌擅淌稍父商市蜒踌蜕斤步假费司彤祈素扰战从陈孵液徒肋矩乌塘鲍弓阴SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据类型数据类型范围范围存储长度存储长度char18000个字符1个字符占1个字节，为固定长度，如果插入的数据不够定义的长度，系统会自动补上空格varcharvarchar(n):18000个字符varchar(max):1231-1个字符varchar(n):1个字符占1个字节varchar(max):输入数据的实际长度加2个字节Text1231-1个字符1个字符占1个字节，存储多少个字符即占多少空间，最大可存储2GB数据15字符串型数据奈择逊鱼胰茧吴慈枉闻缨消钵雍筋昌瞬砚住列楞哮畸摇管究咎咨塌翔桔兢SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据类型数据类型范围范围存储长度存储长度nchar14000个字符1个字符占2个字节，为固定长度，如果插入的数据不够定义的长度，系统会自动补上空格narcharvarchar(n):14000个字符varchar(max):1231-1个字符varchar(n):1个字符占2个字节varchar(max):输入数据的实际长度的两倍再加2个字节ntext1230-1个字符1个字符占2个字节，存储多少个字符即占多少空间，最大可存储2GB数据16Unicode字符串型数据纠粗胰伶煤妆厚霜隔便洛傻纺祝羔蛾借迄壹奠杠雪庙佛衡盘沁擒挑疚榔撵SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据类型数据类型范围范围存储长度存储长度binary18000个字节为固定长度，如果插入的数据不够定义的长度，系统会自动补上0 x00varbinaryvarbinary(n):18000个字节varbinary(max):1231-1个字节varbinary(n):可变长度，输入数据的实际长度varbinary(max):输入数据的实际长度加2个字节image1231-1个字节可变长度，输入数据的实际长度17二进制字符串型数据创研绘棍头念佑湍矾窝滇愤恩裹暂快搐趾粥埔鳞塘惰捅叠脊伞嗜氨矫暮蔼SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理cursorsql_varianttimestampuniqueidentifiertableXML18雁甜仆佬榷惭竹那阀仰浆东驹弘讲氨熔盟蝉冗请势寨炉树肥俩湾刨芒港度SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理sql_variant可用来存储除了text、ntext、image、timestamp、sql_variant之外的所有的SQL Server 2008支持的数据类型，其主要用于列、参数、变量和用户定义函数的返回值中。当某个字段需要存储不同类型的数据时，可以将其设置为sql_variant。XML可以在列或变量中存储XML文档和片段XML片段是缺少单个顶级元素的XML实例存储空间不能超过2GB19隔勇茎净掳颊妈兜唱慈自脱议蕴蕾曼煞伐邯羡砌坠纬洪拦垣逆累赌秘掉荫SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理timestamp8字节的16进制数据在数据库范围内提供唯一值在数据库中更新或插入数据行时，此数据类型定义的列的值会自动更新，一个计算值被自动添加到列中，而且此值在整个数据库中是唯一的每个数据表中只能有一个是timestamp类型的字段uniqueidentifier与timestamp类似16字节的16进制数据提供的是全球范围内的唯一值20塘远骗帧枷药翘袜漠悲肆迈兄芋瘸绵爆迫氦讣荐息永扁身楚意访樱馈龚揩SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理用户可以根据需要自定义数据类型，并可以用此数据类型来声明变量或字段如可以将经常使用的char(100)数据类型自定义为一个数据类型，如char100。22塑跪两俘盔垦铃挠揩搞威盔诵沮豹鸥尉赖耳奏臀术肝槽俘监窄般箍幅兆旨SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理1 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图23喇苇技可葱追较檀络昆橡价尖纂违席哪莽枉货求粕惰挎屈貌恩恼循眼荚狮SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理SQL Server Management Studio定义数据表的字段和主键主键：一个字段或多个字段的组合设置默认值设置标识列自动增长。标识种子、标识增量建立表与表之间的关系INSERT和UPDATE规范：不执行任何操作、级联、设置Null、设置默认值创建索引创建约束主键约束、外键约束、Unique约束、Check约束、默认值、允许空设置数据表所在文件组24填旺稗掠烈瘤惑掠挤闺曝字峪官赤钎竿汤比往您蛙仑蝴始姬俭默熏厂莉麻SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理smalldatetime-getdate()25张祟冬孽媒毁衣祷屎针狙屠重啮竭能柠台邮性掀恰趋何诅喇者廊擅抑荣祁SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理提示说不允许保存修改工具-选项-设计器-表设计器和数据库设计器-阻止保存要求重新创建表的更改26限呆丧康屡包真亭饥栽好孙苹劣嵌圆宴锅信样啦嗽噬闽阮嘿刷魄止矫锐煞SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理CREATE TABLE database_name.schema_name .|schema_name.table_name -设置表名 (-设置列属性|-设置计算列 ,.n )-设置表约束 ON partition_scheme_name(partition_column_name)|filegroup|default -指定存放表数据的分区架构或文件组 TEXTIMAGE_ON -指定存放Text及Image类型字段数据的分区架构或文件组 filegroup|default 27骂挡念给伏胆睡翻弦早捉捻讽终餐秸派筹唤离染棱酉添巢屁蒸寒伎扳疫鱼SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理创建新表CREATE TABLE Northwind.dbo.例一表(编号 int,姓名 nvarchar(50)CREATE TABLE Northwind.例一表(编号 int,姓名 nvarchar(50)USE NorthwindCREATE TABLE 例一表(编号 int,姓名 nvarchar(50)28芍殴擦研淡颠造这朝抿愿奢幅狞汇怔零簿痔玩溅朱呜剐烤倾贰衅蒸抄畜溉SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理创建标识字段CREATE TABLE 例二表(编号 int IDENTITY,姓名 nvarchar(50)创建带标识种子和标识增量的标识字段CREATE TABLE 例三表(编号 int IDENTITY(1,2)PRIMARY KEY,姓名 nvarchar(50)创建非NULL字段CREATE TABLE 例四表(编号 int IDENTITY(1,1)PRIMARY KEY,姓名 nvarchar(50)NOT NULL)29凡共污蛙梨酿活幼刷驱炊紊赞睛口蕴矣戎茎舞汽皇铭超淄领欢键离哗亨红SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理设置字段的默认值CREATE TABLE 例五表(编号 int IDENTITY(1,1)PRIMARY KEY,姓名 nvarchar(50)NOT NULL,性别 bit DEFAULT 1)30笑卒箍篙喉喇湛怔品搀帆钝管吨无孽涛壬掷舆浑磐邪暴依泞饶摄用咆掇于SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理创建新表并将其放在文件组中CREATE TABLE 例六表(编号 int IDENTITY(1,1)PRIMARY KEY,姓名 nvarchar(50)NOT NULL,性别 bit DEFAULT 1)ON 第二文件组31夹了萄状狡扰氢效圈躺聪豢姻刨薪蜜憎毒讨坷骄蚀戏闯仆撰帐妙斧请酌昆SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理创建新表并使用Check约束CREATE TABLE 例七表(编号 int IDENTITY(1,1)PRIMARY KEY,姓名 nvarchar(50)NOT NULL,性别 bit DEFAULT 1,年纪 tinyint CONSTRAINT CK_年纪 CHECK(年纪0 AND 年纪0 AND 年纪0),合计 as 单价*数量)37蠕享所猜讥盗禄叙弘湾优阉型啡谣替黔疾舵粮陷亡佬曝瘫硷讫亩恿壮洼霉SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理设置数据表的约束CREATE TABLE 例十三_用户注册表(编号 int IDENTITY(1,1)PRIMARY KEY,用户名 nvarchar(50)Not null,密码 varchar(16)Not null,电话 varchar(50),地址 nvarchar(200),CHECK(电话 is not null or 地址 is not null)38脚涂棕搜鞠辗束苯在提腕汾权碱偷沫低疲味权渺娄焙迄坚画造盘腑条捡委SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理1 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图39阻巢峻赔宗拂垃抢拿磨彰噶斋雄你屋笨撤纺蔷蒂祷融博颇岿藉岗晴悍拂都SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理SQL Server Management Studio修改数据表名插入、移动和删除数据数据表的字段修改字段数据类型及属性数据表有记录时不能随意修改，只能修改成兼容的数据类型。Varchar(5)Varchar(10),Varchar(10)-varchar(5)截断Bit-decimal(18,0),decimal(18,0)-bit不一定修改主键、外键、索引和约束修改数据表的属性40以萤崩董铣钥锅涂插伯碱唤惩缮戳档鲤括帖钡腹动絮窟傍肥窑芝关自虽吧SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理ALTER TABLE database_name.schema_name .|schema_name.table_name ALTER COLUMN column_name -要修改的字段名 type_schema_name.type_name (precision ,scale|max|xml_schema_collection )-修改后的数据类型 NULL|NOT NULL -设置是否为NULL COLLATE collation_name 设置排序规则|ADD|DROP ROWGUIDCOL|PERSISTED -添加或删除ROWGUIDCOL属性|WITH CHECK|NOCHECK ADD -添加字段 -设置字段属性|-设置计算列|-设置表约束 ,.n|DROP -删除 CONSTRAINT constraint_name -删除约束 WITH(,.n )-设置聚集约束选项|COLUMN column_name 删除字段 ,.n|WITH CHECK|NOCHECK CHECK|NOCHECK CONSTRAINT -启用或禁用约束 ALL|constraint_name ,.n|ENABLE|DISABLE TRIGGER -启用或禁用触发器 ALL|trigger_name ,.n|SWITCH PARTITION source_partition_number_expression -切换数据块 TO schema_name.target_table PARTITION target_partition_number_expression 41册耘霓泉笋驭石贸除莱垛九颐循资男烘抄座的建牺戴咯措娩柬郝佳寞型吹SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理更改表字段的属性ALTER TABLE 例一表ALTER COLUMN 姓名 nvarchar(20)COLLATE Chinese_PRC_CI_AS not null添加字段ALTER TABLE 例一表ADD 性别 nvarchar(2)添加字段并设置字段属性ALTER TABLE 例一表ADD 密码 varchar(16)NOT Null42饺续猜釜绚舀彰畸租辈阻飞洛型摸岂钟殊此辩悄雍植历拐定柳褂姑驴沛娠SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理删除字段ALTER TABLE 例一表DROP COLUMN 密码添加约束ALTER TABLE 例一表ADD CONSTRAINT CK_性别 CHECK(性别=男 OR 性别=女)默认情况下，添加Check约束时会检查数据表里的数据是否符合Check约束，如果有数据不符合Check约束，添加Check约束会失败。如果希望添加约束时不检查数据库里的数据是否符合Check约束，参考如下：ALTER TABLE 例一表WITH NOCHECK ADD CONSTRAINT CK_性别 CHECK(性别=男 OR 性别=女)43谩辞套乙毯融憎没捡棍淹溃棘骆奏革庭嚣并罐梯堪稼删颖班你啊签池传琵SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理禁用约束ALTER TABLE 例一表NOCHECK CONSTRAINT CK_性别一次禁用多个约束ALTER TABLE 例一表NOCHECK CONSTRAINT CK_1,CK_2,CK_3禁用所有约束ALTER TABLE 例一表NOCHECK CONSTRAINT ALL44碴因涤菇戎覆撤题茂杨逃讥标禁暂咆雌从丸妨这铲妙月颗雏恒挥骂崖饭奎SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理启用约束ALTER TABLE 例一表CHECK CONSTRAINT CK_性别一次启用多个约束ALTER TABLE 例一表CHECK CONSTRAINT CK_1,CK_2,CK_3启用所有约束ALTER TABLE 例一表CHECK CONSTRAINT ALL45避测当宙窒印履瞥隐且抛记粗橱灶夺绩择蔚赋撩族泻波很贸黍浪歼侵搐挟SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理删除约束ALTER TABLE 例一表DROP CK_性别设置主键ALTER TABLE 例一表ALTER COLUMN 编号 int not nullGOALTER TABLE 例一表ADD CONSTRAINT PK_主键 PRIMARY KEY(编号)GO46匠伺勉凛吓巢窃丘宁驶禁八仕农奢雌硼鳞七日佛阑畏诣虫勃坑心犯停堵碍SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理修改字段名和数据表名Alter Table没有直接修改数据表名和字段名的功能。使用存储过程sp_rename(SQL Server 2008)exec sp_rename 例一表.姓名,名称,COLUMNexec sp_rename 例一表,例二十三表sp_rename 原对象名称,新对象名称 ,对象类型47烩社贷火咯诚棋噬顷磅渠啊饱量兆匡鸦专乾色碌碳辆阉墙僚将偶塑抬您宇SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理1 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图48烁屎拓淹钓聊诲其庚洼耽匿豌历乐切剪浅收听蔓股拾柞昌延腹枢硒牛敷沧SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理SQL Server Management Studio当数据表有外键依赖时是不能被删除的查看表的依赖关系一次删除多个数据表摘要窗格中选择多个数据表49践睬得策鸿恫围敝趟尤胎沪各铃贬胸舆存醋秀圆核澄葬欲煎踪液伞复绽勺SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理DROP TABLE database_name.schema_name .|schema_name.table_name ,.n ;DROP TABLE 例二表如果要删除的数据表是外键指向的数据表，那么用Drop Table语句无法将其删除，必须先删除表与表之间的关联才能进行删除。50肿华志颅杀挠橡皱拌学鲸裙酗倡口巫裴眯缝讨貌稠悼渺蓖繁米阁祝滑孤危SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理1 数据表简介2 数据类型3 数据表创建4 数据表修改5 数据表删除6 关系图51厉砖教诺焙撇诅低净初蕾雏论虐馁疆璃来脚抡正刀埃映灶矽获褥眩慑彰躺SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据库关系图以图形的方式显示数据库结构和数据表之间的联系。使用数据库关系图可以创建和修改数据表、字段、关系、键、索引和约束。52丈析亏蝶醒稚埋质汤亨哑易沦揪捂蓉恒甲糊旋遂磋颖烬膊字长且撇灵蓬辅SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理创建数据库关系图并添加数据表添加相关联的数据表更改数据表的显示模式标准、列、键、仅表名、自定义在关系图中数据数据表建立和删除表与表之间的关联保存部分修改过的数据表在关系图中创建数据表在关系图中修改字段属性在关系图中设置或修改表的外键与约束53用惰颇舵幕祝猾思号怪廷梅撮癌钮榴趣乙柏泪肮愁敝顾寞寞擒襟峪申京竿SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理将现有的数据表结构生成一个T-SQL代码脚本，利用该脚本可以创建或更新数据表开发代码，或维护、备份数据表脚本。54旬练函秽衷盼者赣闹悄偶蝴她扫蛰进辟罪耽嘎宇筏瞅盏瞳弘娘赵犹藕拇涂SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理临时表是一种因为暂时需要所产生的数据表，它存放在tempdb数据库中。本地临时表本地临时表是以”#”开头命名的表，只有创建它的用户可以使用它，当用户断开连接后，SQL Server会自动删除它。如果是在存储过程或触发器中创建的本地临时表，当存储过程或触发器结束后，该本地临时表也会被自动删除。全局临时表全局临时表是以”#”开头命名的表，在创建后，任何用户和连接都可以使用它，只有当所有使用该临时表的用户都与SQL Server实例断开连接后，SQL Server才将其删除。临时表也可以用Drop Table语句删除CREATE TABLE#数据表名（字段1，字段2）CREATE TABLE#数据表名（字段1，字段2）55岗晾丝啃径勉炊绎鄙绢恳链适搬馒疫绸型邻徘顽粱蕉赁倾恭吟这返史笺鄙SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理数据类型数据表创建数据表修改数据表删除关系图56绢筛榔郎愈耻钞疑奥抽岁邪袁淮占常烃孕炔共检扶丫扦椒果群拓疤浇床觅SQLServer数据库技术-03数据表管理SQLServer数据库技术-03数据表管理