04修改数据.docx
《04修改数据.docx》由会员分享，可在线阅读，更多相关《04修改数据.docx（15页珍藏版）》请在冰豆网上搜索。
04修改数据
子查询的使用
例：
Northwind公司需要列出每笔交易的交易号，交易时间以及此笔交易中最大的交易单价。
Selectord.ordered,ord.orderdate,（selectmax（orddet.unitprice））
Fromnorthwind.dbo.[orderdetails]asordet
Whereord.ordered=orddet.ordered）asmaxuniprice
Fromnorthwind.db.ordersasord
修改数据
——数据的更新、数据的插入、数据的删除（对表的结构没有影响）
插入数据——加入新的数据
修改数据——对现有的数据
删除数据——对现有数据中不需要的，或作错误的
例：
将新客户的信息存储进Customers表中。
列名
数据
Customerid——公司代号
PECOF
Companyname——公司名称
Pecoscoffeecompany
Contactname——联系人名称
Michaeldunn
Contacttile——联系的方式
Owner
Address
1900oakstreet
City
Vancouver
Region
Bc
Pastalcode
V3f2k1
Country
Canada
Phone
（604）555-3392
Fax
（604）555-7293
一、INSERT语句
——添加到数据库中的最小单位是行；整行的插入
语法：
insert[into]表名列名1values值1……
（简写成：
insert[into]表名字段列表values值列表）
——表名，指定要添加数据的表的名字
——values，指定要插入表中的列所包含的值
——SQLSERVER允许将部分数据插入到表中，这些表的的某列允许为null或具有默认值
——数值的个数必须和表中或列表中的个数相同
——插入的值的次序必须和列表中列出的次序相同
——值的数据类型必须和对应列的数据类型匹配
insertcustomers
（customerid,companyname,contactname,contacttitle,address,city,region,postalcode,country,phone,fax）
values（‘pecof’,‘pecoscoffeecompany’,‘michaeldunn’,’owner’,‘1900oakstreet’,‘vancouver’,‘bc’,‘v3f2k1’,‘canada’,‘（604）555-3392’,‘（604）555-7293’）
例1插入一条交易记录
Orderid
Productid
Unitprice单价
Quantity数量
Discount折扣
10248
14
18.6
10
0
Insert[orderdetails]
（orderid,productid,unitprice,quantity,discount）
values（10248,14,18.6,10,0）
可以简化成：
Insert[orderdetails]
values（10248,14,18.6,10,0）
——只有在：
插入的值的次序、数量、数据类型同表结构中字段的次序、数量、数据类型一致时
例2：
插入一条交易记录
Orderid
Productid
Unitprice单价
Quantity数量
Discount折扣
10249
65
16.8
Insert[orderdetails]
（orderid,productid,unitprice）
values（10249,65,16.8）
——这个时候字段列表是不能省略的
例：
将价格高于20美元的商品的信息导入到一张新表expensiveproducts中。
然后再向表expensiveproducts中添加价格为20美元的商品信息。
——将多条记录一次性导入到另外一张表中
selectinto语句
——将一个表中的内容提取到另外一个新表（未建立的）中
语法：
select字段列表
into新表名
from表名
where条件
——字段列表，指定了新表中要包含的列；
——新表名，指定了要存储数据的新表的名字
——是一个复制粘贴的操作，对原表数据没有影响
——技巧：
先作查询，然后在select和from之间插入into表名
selectproductname,unitprict
intoexpensiveproducts
fromproducts
whereunitprice>20
insertinto语句
——从一个（现成）表向另一个（现成）表添加数据
语法：
insertinto表名1
select字段列表
from表名2
where条件
——表名1，指定了将要插入数据的表的名字，必须是已存在的表。
——字段列表，指定了你需要从现有表复制到新表的列的名字
——表名2，指定了从中复制数据的表
——是一个复制粘贴的操作，对原表数据没有影响
——技巧：
先查询，再写insertinto
insertintoexpensiveprodects
selectproductname,unitprice
fromproducts
whereunitprice=20
例：
将1997年以前的交易记录析取到新表oldorders中。
然后再向oldorders中添加1997年所有的交易记录。
Selectorderid,customorid,orderdate
Intooldorders
Fromorders
Whereorderdate<’1997-1-1’
Insertintooldorders
Selectorderid,customerid,orderdate
Fromorders
Whereorderdatebetween‘1998-1-1’and‘1997-12-1’
例：
将1号产品的价格提高10％。
Uqdate语句
——一行中的一栏是更新的最小单元
语法：
update表名
set列名1＝值1，列名2＝值2，….
[Where条件]
——同一时刻只能对一张表进行更新
——如果一次更新违背了完整性约束，则所有的更新都将被回滚。
也就是说，表没有发生任何变化。
Usenorthwind
Updateproducts
Setunitprice=（unitprice*1.1）
Whereproductid=1
例1：
在pubs数据库中，把state（洲）北加利福尼亚CA更名为pacifica（缩写为PC）；把奥克兰市oakland更名为BayCity。
updateauthors
setstate=’pc’,city=’baycity’
wherestate=’CA’andcity=’oakland’
例：
将所有来自美国供应商的产品的价格提高2美元。
语法2：
update表名
set列名1＝值1，列名2＝值2，….
[FROM表名]
[Where条件]
updateproducts
setunitprice=unitprice+2
fromproductspinnerjoinsupplierss
onp.supplierid=s.supplierid
wheres.country=’USA’
例1：
将newmoonbooks（新月球图书公司）出版的所有书籍的价格加倍
updatetitles
setprice=price*2
fromtitlesinnerjoinpublishers
ontitles.pub_id=publishers.pub_id
wherepub_name=’newmoonbooks
例：
删除其中的au_lname是Mcbadden的所有行
DELETE语句
语法：
delete[from]表名
[from表]
[where条件]
——删除操作的最小单元是行
deletefromauthors
whereau_lname=’mcbadden’
例：
删除所有1998年4月1日的订货的交易的详细信息
delete[orderdetails]
from[orderdetails]odinnerjoinorderso
onod.ordered=o.ordered
whereo.orderdate=’1998-4-14’
例1：
删除商业书籍的所有销售记录
deletesales
fromsalessinnerjointitlest
ons.title_id=t.title_id
wheret.type=’business’
表的管理
——表是存放数据的载体
内容：
数据类型、表的创建、维护表、删除表
例：
创建一张员工表
——确定表名：
employeeinfo
——确定表的属性－字段、数据类型：
employeeid代号、name姓名、city城市
——确定属性是否允许NULL值：
employeeid和name不允许NULL
数据类型：
—基本数据类型：
●数值型：
整数：
intsmallinttinyintbigint——差别在于存储数据所需要的空间、所需要的字节04_整型.giftinyint——非负整数
小数：
numericfloatreal——在于精度上的差别04_小数.gif
●日期、货币04_日期、货币型.gif
日期：
datetimesmalldatetime
货币：
moneysmallmoney——小数点后三位
●字符型04_字符型.gif
charvarcharncharnvarchar
●特殊
text——支持长文本，最大到2G
image
—用户自定义数据类型
execsp_addtypecity,‘varchar（15）’,‘notnull’
city——新数据类型的名称
varchar（15）——对应的系统数据类型
字段的其他属性
aIDENTITY属性——控制字段的值是自动生成，还是手动输入
—用于需要自动生成唯一的值的列
—seed是identity列的起始值或初始值
—increment是生成该列的一个值的步长。
可以为负数
—属于整型
—例如：
identity（100,1）
buniqueidentifier属性
—同样自动生成值，随机生成。
—储存16字节的二进制值，是一个唯一的二进制数字，世界上任何两台计算机都不会生成重复
—没有任何方式可以决定生成uniqueidertifier值的顺序
—使用newwd函数生成
创建表
语法结构：
createtable表名
（
列名1数据类型[identity（seed,increment）][null|notnull],
列名2数据类型[identity（seed,increment）][null|notnull],
）——括号里是描述表的结构
on
（文件组名）
例：
createtableemployeeinfo
（employeeidintidentity（1,1）notnull,
namenvarchar（15）nutnull,
citycitynull
）——没有指定文件组，默认在primary这个文件组上。
查看表的结构
execsp_help表名
为给表插入数据
insertemployeeinfo
values（‘maojie’,‘nanjing’）
——employeeid字段不需要，它是自动生成的。
例1
createtablecustomer
（
custiduniqueidentifiernotnulldefaultnewid（）,
custnamechar（30）notnull
）
execsp_helpcustomer
select*fromcusomer
insertcustomer
（cuntname）
values（‘chinaitlab’）
问题：
往employeeinfo表中加入一列title描述职务
修改表的结构
——增加字段
altertalbe表名
add列名数据类型null|notnull
——删除字段
altertable表名
dropcolumn列名
altertableemployeeinfo
addtitlevarchar（15）null
问题：
你是一位新来到公司的数据库操作员，现在想知道表employeesinfo的结构以及创建过程
——使用企业管理器，选中该表—右击—生成sql脚本
问题：
从数据库中删除表employeeinfo
——使用droptable语句来删除。
——当一个表从数据库中删除，该表的所有数据都被删除。
语法：
droptable表名
使用约束——实现数据的完整性
——确保表中的数据是一致的、可靠的、有效的、完整的。
通过编制规则来实现。
约束的用处：
保证数据的正确、可靠、一致；将数据的有效性检查交给系统去实现，而非人工来实现。
数据完整性：
实体完整性、域完整性、引用完整性
域完整性——确保数据存在一个合法的范围内。
可以通过限制字段数据类型、值的范围和数据格式来实现。
如，价格不能为负数。
实体完整性——确保表中无重复记录
例如，所有员工的工号不能重复；交易的交易号不能重复
引用的完整性——确保数据保持一贯的一致性、正确性和可用性，即便在数据修改之后也是如此。
例如，所有交易对象都必须是我公司的顾客买单上的客户。
域完整性——针对字段，保证表中所有字段的值都是有效、合法的
实体完整性——针对记录的，保证这条记录在表中是独一无二的，不重复的；
引用完整性——针对表之间的关系，本表所存储的数据是否与其他表的数据相符。
问题：
在productinfo表中，如果某个产品的描述不详，就认为是‘unknown‘
productinfo表的结构
pro_id产品编号pro_name产品名称
unitprice产品单价supplierid供应商编号
description产品描述
DEFALUT约束默认值约束
——指定列的默认值；只能在一列上创建一个default约束，且该列不是能是indentity列。
语法：
[CONSTRAINT约束名]DEFAULT（常数表达式|NULL）
例：
altertableemployees
addconstraintdefcitydefault“chicago”forcityChicago—芝加哥
altertalbeproductinfo
addconstraintdf_descriptiondefault‘unknown’fordescription
——addconstraint后面只能跟一个默认值；若想设置多个默认值，则要多条altertalbe、addconstraint语句。
演示：
select*fromproductinfo
没有约束前
insertproductinfo（pro_id,pro_name,unitprice,supplierid）
values（12,‘tofu’,9.8,2）
加约束
altertableproductinfo
addconstraintdf_descriptiondefault‘unknown’for[description]
检验约束是否创建成功
execsp_help表名
问题：
在productinfo表中输入产品价格时，必须大于0。
CHECK约束
——通过限制插入列中的值来实施域完整性。
可以在一列上定义多个CHECK约束。
他们按照定义的次序被实施。
语法
[constraint约束名]check（表达式）
表达式制订了对列进行检查的检查条件。
它可以是任何包含下面这些元素的表达式：
算术运算符、关系运算符或者像in、like和between这样的关键字（类似where后的搜索条件）。
例：
altertalbecustomers
addconstraintdf_citycheck（cityin（‘london’,‘chicago’））
altertableproductinfo
addconstraintck_pricecheck（price>0）
演示
约束前
select*fromproductinfo
插入一个价格为零的产品
insertproductinfo（pro_id,pro_name,uniptrice,supp;ierid）
values（13,‘beer’,0,3）
删除价格0的记录
deletefromproductinfo
wherepro_id=13
加约束
altertalbeproductinfo
addconstraintck_pricecheck（unitprice>0）
execsp_helpproductinfo
check约束同样也对数据的修改进行检查
updateproductinfo
setunitprice=0
wherepro_id=13
问题：
必须保证产品的id号和它的名称在productinfo表中是唯一的。
PRIMARYKEY约束主键约束
——PRIMARYKEY约束定义在一列或一组列上，这些列的值可以在表中唯一确定一行。
这些列称为主关键字列。
该列不能为空、不能重复。
Id号；一个表中只能有一个主键约束。
语法
CONSTRAINT约束名PRIMARYKEY（列名….）
UNIQUE约束唯一性约束
——用于在非主关键字列上实施唯一性。
UNIQUE约束类似于primarykey约束，只是它允许null值，但是表中只有一行可以却NULL值。
一个表中可以创建多个唯一性约束。
语法
CONSTRAINT约束名UNIQUE（列名）
Altertalbeproductinfo
Addconstratintpk_productprimarykey（pro_id）
Altertalbeproductinfo
Addconstraintu_pronameunique（pro_name）
演示
select*fromproductinfo
插入一条id相同的记录
insertproductinfo（pro_id,pro_name,unitprice,supplierid）
values（13,‘beer’,15.6,3）
删除
deletfromproductinfo
wherepro_id＝13
添加约束
alerttalbeproductinfo
addconstraintpk_proidprimarykey（pro_id）
execsp_helpproductinfo
updateproductinfo
setpro_id=13
wherepro_id=12
产品名称的约束
altertalbeproductinfo
addconstraintu_nameunique（pro_name）
问题：
保证公司所有产品的供应商都来自于supplierinfo表。
并且当供应商信息修改时，在productinfo表中的相应信息也自动修改。
FROEIGEKEY约束外键约束
语法
CONSTRAINT约束名FOREIGNKEY（列名）——子表字段名称
REFERENGES表名（列名）——父表（字段名称）
——外键约束建立在子表上
Altertableproductinfo
Addconstraintfk_product_supplierforeign
Key（supplierid）references
Supplereinof（supplierid）
Ondeletecascade
Onupdatecascade
演示
select*fromproductinfo——productinfo的情况
select*fromsupperinfo——supperinfo的情况
创建约束——外键约束建立在子表上
altertableproductinfo
addconstraintfk_pro_supforeignkey（supperid）
referencessupplierinfo（supplierid）
——没有级联操作
插入记录
insertproductinfo（pro_id,pro_name,unitprice,supplierid）
values（14,’cake’,22.1,11）
——无法插入，因为没有11号供应商
非法修改——改子表
updateproducinfo
setsupplierid=11
wherepro_id=14
非法修改——改父表
updatesupperlierid
setsupplierid=11
wheresupplierid=1
添加级联——要先删除原先创建的约束
altertalbeproductinfo
dropconstraintfk_pro_sup
创建约束——带级联操作
altertableproductinfo
addconstraintfk_pro_supforeignkey（supperid）
referencessupplierinfo（supplierid）
Ondeletecascade
Onupdatecascade
select*fromproductinfo——productinfo的情况，有3种中产品是1号供应商提供的
select*fromsupperinfo——supperinfo的情况
修改父表
updatesupperlierid
setsupplierid=11
wheresupplierid=1
从供应商表中删除2号供应商
deletefromsupplierin
升级会员 