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

9.1. 10 ． 1 添加数据记录

9.1.1. INSERT …VALUES 单行插入一条记录

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

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

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

USE NorthwindSQL

SET DATEFORMAT mdy

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'MyTable')

DROP TABLE MyTable

-- 建立资料表

CREATE TABLE MyTable

姓名 nvarchar(12),

性别 bit,

出生日期 datetime,

联络地址 nvarchar(60),

电话号码 nvarchar(16),

薪资 money

-- 新增资料记录

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

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

VALUES

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

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

INSERT MyTable -- 还是 INSERT INTO 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 * FROM MyTable

DROP TABLE MyTable

1. 关键字 NULL

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

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

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

USE NorthwindSQL

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'MyTable')

DROP TABLE MyTable

-- 建立资料表

CREATE TABLE MyTable

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

-- 新增资料记录

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

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

SELECT * FROM MyTable

DROP TABLE MyTable

2. 关键字 DEFAULT

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

· 如果字段拥有默认值， SQL Server 会将其默认值添加到字段中。

· 如果字段无默认值，而且该字段允许 NULL 值， SQL Server 会将 NULL 值添加到字段中。

· 如果字段无默认值，而且该字段不允许 NULL 值，则 INSERT 失败。

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

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

USE NorthwindSQL

SET DATEFORMAT mdy

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'MyTable')

DROP TABLE MyTable

-- 建立资料表

CREATE TABLE MyTable

( 姓名 nvarchar(12) NULL,

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

-- 新增资料记录

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

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

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

SELECT * FROM MyTable

DROP TABLE MyTable

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

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

· 如果字段拥有默认值， SQL Server 会将其默认值添加到字段中。

· 如果字段无默认值，而且该字段允许 NULL 值， SQL Server 会将 NULL 值添加到字段中。

· 如果字段无默认值，而且该字段不允许 NULL 值，则 INSERT 失败。

4. 自动编号字段

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

9.1.2. INSERT …SELECT 多行 / 已有的表

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

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

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

USE NorthwindSQL

IF EXISTS (SELECT TABLE_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 (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = 'AnnualScore')

DROP TABLE AnnualScore

CREATE TABLE AnnualScore

(grade char(1),class char(2),score decimal(5,2))

INSERT AnnualScore

SELECT grade,class,AVG(chinese+english)/2

FROM stud87

GROUP BY grade,class

ORDER BY 1,3

INSERT AnnualScore

SELECT grade,class,AVG(chinese+english)/2

FROM stud88

GROUP BY grade,class

ORDER BY 1,3

INSERT AnnualScore

SELECT grade,class,AVG(chinese+english)/2

FROM stud89

GROUP BY grade,class

ORDER BY 1,3

SELECT * FROM AnnualScore

ORDER BY 1,3

DROP TABLE AnnualScore

9.1.3. SELECT …INTO 自动创建一张表

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

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

USE NorthwindSQL

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

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

FROM 产品资料

WHERE 单价 >

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

ORDER BY 单价

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

DROP TABLE 高于平均单价的产品

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

9.2. 修改数据记录

UPDATE MyTable SET name=’ 张三 ’;

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

UPDATE MyTable

SET Salary=Salary-10000

WHERE Salary>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

EXEC sp_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 TABLE MyTable1(id1 int PRIMARY KEY, number1 int)

CREATE TABLE MyTable2(id2 int PRIMARY KEY, number2 int,

number3 int)

INSERT INTO MyTable1 VALUES (1, 10)

INSERT INTO MyTable2 VALUES (1, 20, 30)

INSERT INTO MyTable2 VALUES (2, 40, 60)

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

FROM MyTable2

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

WHERE MyTable2.id2 = 1 -- 指明限定条件

SELECT number1 FROM MyTable1

DROP TABLE MyTable1

DROP TABLE MyTable2

9.3. 删除数据记录

9.3.1. DELETE 命令

DELETE MyTable -- 将删除表 MyTable 中的所有记录但是表的结构不变

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

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

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

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

USE NorthwindSQL

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

SET NOCOUNT ON

SELECT * INTO 我的员工

FROM 飞狐工作室

SELECT COUNT(*) FROM 我的员工

DELETE 我的员工

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

SELECT COUNT(*) FROM 我的员工

DROP TABLE 我的员工

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

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

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

USE NorthwindSQL

SET NOCOUNT ON

BEGIN TRANSACTION MyTransaction -- 开始事务

SELECT COUNT(*) FROM 客户

DELETE 客户

WHERE 客户编号 NOT IN

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

SELECT COUNT(*) FROM 客户

ROLLBACK TRANSACTION MyTransaction -- 回滚事务

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

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

USE NorthwindSQL

SET NOCOUNT ON

BEGIN TRANSACTION mytransaction -- 开始事务

SELECT COUNT(*) FROM 飞狐工作室

DELETE 飞狐工作室

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

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

SELECT COUNT(*) FROM 飞狐工作室

ROLLBACK TRANSACTION mytransaction -- 回滚事务

9.3.2. TRUNCATE TABLE 命令

TRUNCATE TABLE 表名称

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

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

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

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