9. 添加、修改和删除数据记录
|
本章主题 l 添加数据记录 INSERT l 修改数据记录 UPDATE l 删除数据记录 DELETE
SQl语言必学部分！这一章内容非常重要！！★★★ |
与我的学生共勉：比金钱重要的是时间，比时间重要的是时机，比时机重要的是你要抓住时机的决心，动力和准备！
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命令只删除数据记录，并不将表的结构及相关对象删除，那么若想将表整个删除，用什么命令？
与我的学生共勉：大多数人想要改造这个世界，但却罕有人想改造自己。
温一军
|
第9章 结束 |
