第09章添加、修改和删除数据记录

Posted on 2010-07-21 10:19 Jeffrey mao 阅读(117) 评论(0) 收藏举报

9. 添加、修改和删除数据记录

本章主题

l 添加数据记录 INSERT

l 修改数据记录 UPDATE

l 删除数据记录 DELETE

SQl语言必学部分！这一章内容非常重要！！★★★

与我的学生共勉：比金钱重要的是时间，比时间重要的是时机，比时机重要的是你要抓住时机的决心，动力和准备！　　　　　　　　　　　　　　　　温一军

9.1. 10．1 添加数据记录

9.1.1. INSERT …VALUES

注：插入的数据除了必须与数据与数据类型相符合外，还不能违反表的各项约束，否则插入操作将会失败。

范例1：先建立表，然后增加记录，最后显示记录并删除表。

/*档案名称: Demo991.sql*/

USENorthwindSQL

SET DATEFORMAT mdy

IF EXISTS(SELECTTABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'MyTable')

DROP TABLE MyTable

--建立资料表

CREATE TABLEMyTable

(

姓名 nvarchar(12),

性别 bit,

出生日期 datetime,

联络地址 nvarchar(60),

电话号码 nvarchar(16),

薪资 money

)

--新增资料记录

INSERT MyTable --大家喜欢INSERTINTO MyTable 这种写法吗？

(姓名,性别,出生日期,联络地址,电话号码,薪资)

VALUES

(

'章立民',1,'09/26/1967',

'台北市重庆南路 100 号','(02)2345-0988',32000.00

)

INSERT MyTable --还是INSERTINTO MyTable好看些～ J

(姓名,性别,出生日期,联络地址,电话号码,薪资)

VALUES

(

'郭子仪',1,'02/18/1963',

'桃园市中山路 10 号','(04)234-2929',42000.00

)

INSERT MyTable

(姓名,性别,出生日期,联络地址,电话号码,薪资)

VALUES

(

'叶蝉娟',0,'07/28/1969',

'高雄市中正路 120 号','(03)234-9339',38000.00

)

SELECT * FROMMyTable

DROP TABLEMyTable

1. 关键字NULL

如果某个字段允许NULL值，而且你希望将NULL值添加到该字段，请用NULL关键字作为数据值。

范例2：添加记录时，直接添加NULL值。

/*档案名称: Demo992.sql*/

USE NorthwindSQL

IF EXISTS(SELECTTABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'MyTable')

DROP TABLE MyTable

--建立资料表

CREATE TABLEMyTable

(姓名 nvarchar(12),电话号码nvarchar(16) NULL)

--新增资料记录

INSERT MyTable (姓名,电话号码) VALUES ('章立民',NULL)

INSERT MyTable (姓名,电话号码) VALUES ('陈光友',NULL)

SELECT * FROMMyTable

DROP TABLEMyTable

2. 关键字DEFAULT

如果在使用INSERT命令时，使用DEFAULT关键字：

如果字段拥有默认值，SQL Server会将其默认值添加到字段中。
如果字段无默认值，而且该字段允许NULL值，SQLServer会将NULL值添加到字段中。
如果字段无默认值，而且该字段不允许NULL值，则INSERT失败。

范例3：添加记录时，使用DEFAULT关键字添加默认值。

/*档案名称: Demo993.sql*/

USE NorthwindSQL

SET DATEFORMATmdy

IF EXISTS(SELECTTABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'MyTable')

DROP TABLE MyTable

--建立资料表

CREATE TABLEMyTable

(姓名 nvarchar(12) NULL,

出生日期 nvarchar(16) NULL DEFAULT GETDATE())

--新增资料记录

INSERT MyTable (姓名,出生日期) VALUES ('章立民',DEFAULT)

INSERT MyTable (姓名,出生日期) VALUES (DEFAULT,DEFAULT)

INSERT MyTable (姓名,出生日期) VALUES ('陈光友','09/26/1997')

SELECT * FROMMyTable

DROP TABLEMyTable

3. 若仅添加部分字段的数据

如果在使用INSERT命令时，若仅添加部分字段的数据，则其余字段：

如果字段拥有默认值，SQL Server会将其默认值添加到字段中。
如果字段无默认值，而且该字段允许NULL值，SQLServer会将NULL值添加到字段中。
如果字段无默认值，而且该字段不允许NULL值，则INSERT失败。

4. 自动编号字段

对自动编号字段不需要也不能

9.1.2. INSERT …SELECT

最大的特色是将查询结果直接存储到一个现有表中。

范例1：先创建一个“北区客户”表，然后从“客户”表查询所得的数据记录存入“北区客户”表中。

/*档案名称: Demo996.sql*/

USE NorthwindSQL

IF EXISTS(SELECTTABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = '北区客户')

DROP TABLE 北区客户

CREATE TABLE北区客户

(

客户编号 nvarchar(5) NOT NULL ,

公司名称 nvarchar(40) NOT NULL ,

连络人 nvarchar(30) NULL ,

连络人职称 nvarchar(30) NULL ,

地址 nvarchar(60) NULL ,

城市 nvarchar(15) NULL ,

行政区 nvarchar(15) NULL ,

邮递区号 nvarchar(10) NULL ,

国家地区 nvarchar(15) NULL ,

电话 nvarchar(24) NULL ,

传真电话 nvarchar(24) NULL

)

INSERT 北区客户

SELECT * FROM 客户

WHERE 城市IN ('台北市','台北县','宜兰市','新竹市')

ORDER BY 公司名称

SELECT * FROM北区客户

DROP TABLE北区客户

范例2：先将数据计算后再存入另外一个表中。

/*档案名称: Demo997.sql*/

USE NorthwindSQL

IF EXISTS(SELECTTABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'AnnualScore')

DROP TABLE AnnualScore

CREATE TABLEAnnualScore

(grade char(1),class char(2),scoredecimal(5,2))

INSERT AnnualScore

SELECTgrade,class,AVG(chinese+english)/2

FROM stud87

GROUP BYgrade,class

ORDER BY 1,3

INSERT AnnualScore

SELECTgrade,class,AVG(chinese+english)/2

FROM stud88

GROUP BYgrade,class

ORDER BY 1,3

INSERT AnnualScore

SELECTgrade,class,AVG(chinese+english)/2

FROM stud89

GROUP BYgrade,class

ORDER BY 1,3

SELECT * FROMAnnualScore

ORDER BY 1,3

DROP TABLEAnnualScore

9.1.3. SELECT …INTO

最大的特色：自动新建一个表，然后将查询结果存入其中。

/*档案名称: Demo998.sql*/

USENorthwindSQL

EXEC sp_dboption 'NorthwindSQL','selectinto/bulkcopy','TRUE'

SELECT 产品,单价INTO 高于平均单价的产品

FROM 产品资料

WHERE 单价 >

(SELECT AVG(单价) FROM 产品资料)

ORDER BY 单价

SELECT * FROM高于平均单价的产品

DROP TABLE高于平均单价的产品

EXEC sp_dboption 'NorthwindSQL','selectinto/bulkcopy','FALSE'

9.2. 修改数据记录

UPDATE MyTable SET name=’张三’;

注：UPDATE命令默认的更新范围为“所有”记录，除非用WHERE子句指定范围。

如：

UPDATE MyTable

SETSalary=Salary-10000

WHERESalary>80000

范例1：将薪资低于平均薪资员工的薪资调成平均薪资：

/*档案名称: Demo999.sql*/

USE NorthwindSQL

EXEC sp_dboption'NorthwindSQL','select into/bulkcopy','TRUE'

SELECT * INTO我的员工

FROM 飞狐工作室

-- 将薪资低于平均薪资的员工,调薪至平均薪资

UPDATE 我的员工 --看看下面，使用了子查询

SET 目前薪资 = (SELECT AVG(目前薪资) FROM 我的员工)

WHERE 目前薪资 < (SELECT AVG(目前薪资) FROM 我的员工)

SELECT 姓名,目前薪资FROM 我的员工

DROP TABLE我的员工

EXEC sp_dboption'NorthwindSQL','select into/bulkcopy','FALSE'

范例2：找出性别为男性且姓名为“许建仁”的员工，并将其姓名更改为“章立民”，目前薪资则更改成起薪的2.5倍。

/*档案名称: Demo9100.sql*/

USE NorthwindSQL

EXECsp_dboption 'NorthwindSQL','select into/bulkcopy','TRUE'

SELECT * INTO我的员工

FROM 飞狐工作室

UPDATE 我的员工

SET 姓名 = '章立民',

目前薪资 = 起薪 * 2.5

WHERE 姓名 = '许建仁' AND 性别 = 1 --假设１表示男性

DROP TABLE我的员工

EXEC sp_dboption'NorthwindSQL','select into/bulkcopy','FALSE'

范例3：更新某列的表达式中用到了其它表的字段内容。

/*档案名称: Demo9103.sql*/

USE NorthwindSQL

SET NOCOUNT ON

CREATE TABLEMyTable1(id1 int PRIMARY KEY, number1 int)

CREATE TABLEMyTable2(id2 int PRIMARY KEY, number2 int,

number3 int)

INSERT INTOMyTable1 VALUES (1, 10)

INSERT INTOMyTable2 VALUES (1, 20, 30)

INSERT INTOMyTable2 VALUES (2, 40, 60)

UPDATE MyTable1 SET number1 = MyTable2.number2 + MyTable2.number3

FROM MyTable2

--因为number1的值是MyTable2的字段值运算的结果

WHEREMyTable2.id2 = 1 --指明限定条件

SELECT number1 FROM MyTable1

DROP TABLEMyTable1

DROP TABLEMyTable2

9.3. 删除数据记录

9.3.1. DELETE命令

DELETE MyTable --将删除表MyTable中的所有记录

DELETE命令默认的删除范围是“所有”的数据记录，除非用WHERE子句指定范围。

如：

DELETE 飞狐工作室WHERE 目前薪资<2000

范例1：将薪资为倒数十名且年龄在26岁以上的员工数据删除。

/*档案名称: Demo9101.sql*/

USE NorthwindSQL

EXECsp_dboption 'NorthwindSQL','select into/bulkcopy','TRUE'

SET NOCOUNT ON

SELECT * INTO我的员工

FROM 飞狐工作室

SELECT COUNT(*) FROM 我的员工

DELETE 我的员工

WHERE 目前薪资 IN (SELECTTOP 10 目前薪资 FROM 我的员工 ORDER BY 1) AND DATEDIFF(yyyy,出生日期,GETDATE())>= 26

SELECT COUNT(*) FROM 我的员工

DROP TABLE我的员工

EXECsp_dboption 'NorthwindSQL','select into/bulkcopy','FALSE'

范例2：将未下过订单的客户数据删除。

/*档案名称: Demo9105.sql*/

USE NorthwindSQL

SET NOCOUNT ON

BEGIN TRANSACTIONMyTransaction --开始事务

SELECT COUNT(*) FROM 客户

DELETE 客户

WHERE 客户编号 NOT IN

(SELECT DISTINCT 客户编号 FROM 订货主档) --子查询

SELECT COUNT(*) FROM 客户

ROLLBACK TRANSACTIONMyTransaction --回滚事务

范例3：将年龄最大的10位员工数据删除：

/*档案名称: Demo9106.sql*/

USE NorthwindSQL

SET NOCOUNT ON

BEGIN TRANSACTIONmytransaction --开始事务

SELECT COUNT(*) FROM 飞狐工作室

DELETE 飞狐工作室

WHERE 飞狐工作室.身份证字号 IN

(SELECT TOP 10 身份证字号 FROM 飞狐工作室 ORDERBY 出生日期)

SELECT COUNT(*) FROM 飞狐工作室

ROLLBACK TRANSACTIONmytransaction --回滚事务

9.3.2. TRUNCATE TABLE命令

格式：

TRUNCATE TABLE 表名称

将表中的所有数据记录删除，但速度比DELETE命令快得多。

因为DELETE命令是一条一条删除记录，并且会将每一条被删除的记录存入事务日志（Transaction Log）中。而TRUNCATE TABLE命令直接重新配置表的数据页来删除数据，并且不记录入事务日志中。

DELETE和TRUNCATETABLE命令只删除数据记录，并不将表的结构及相关对象删除，那么若想将表整个删除，用什么命令？

与我的学生共勉：大多数人想要改造这个世界，但却罕有人想改造自己。

温一军

第9章　结束

刷新页面返回顶部

知彼者，智也；知己者，大智也

公告

第09章添加、修改和删除数据记录

9. 添加、修改和删除数据记录

9.1. 10．1 添加数据记录

9.1.1. INSERT …VALUES

9.1.2. INSERT …SELECT

9.1.3. SELECT …INTO

9.2. 修改数据记录

9.3. 删除数据记录

9.3.1. DELETE命令

9.3.2. TRUNCATE TABLE命令

知彼者，智也；知己者，大智也

公告

第09章 添加、修改和删除数据记录

9. 添加、修改和删除数据记录

9.1. 10．1 添加数据记录

9.1.1. INSERT …VALUES

9.1.2. INSERT …SELECT

9.1.3. SELECT …INTO

9.2. 修改数据记录

9.3. 删除数据记录

9.3.1. DELETE命令

9.3.2. TRUNCATE TABLE命令

第09章添加、修改和删除数据记录