SQL基础操作_4_表的插⼊、更新、删除、合并操作
⽬录
7.4 表的插⼊、更新、删除、合并操作
注：数据集和表结构见
7.4.1 插⼊新的记录
需求：向dept表中插⼊部门编号为50，部门名称为Production，部门位置为Shanghai的数据.
解决⽅法：这⾥通过INSERT INTO TableName VALUES (…)
Mysql、Sql server、Oracle:
INSERT INTO dept VALUES (50,'Production','Shanghai');
注：这⾥表dept的定义没有⾃增字段，如果有请详见下⾯的解决⽅案.
7.4.2 插⼊含⾃增列的记录
需求：向dept表中插⼊部门编号为50，部门名称为Production，部门位置为Shanghai的数据.
解决⽅法：这⾥通过INSERT INTO TableName VALUES (…)，这⾥表dept的deptno字段定义的是⾃增.
MySql:
INSERT INTO dept VALUES (50,'Production','Shanghai');
Mysql虽然在字段定义时约束了⾃增，但是在插⼊⾃增数据时依然可以显⽰的插⼊，只要⾃增字段的值不和已有的数据重复即可.
如果插⼊的重复的⾃增字段数据，则会有类似如下的报错:
Duplicate entry '50' for key'PRIMARY'
SQL Server:
INSERT INTO dept VALUES (50,'Production','Shanghai');
消息 8101，级别 16，状态 1，第 2 ⾏
仅当使⽤了列列表并且 IDENTITY_INSERT 为 ON 时，才能为表'dept'中的标识列指定显式值。
问题原因:
dept表的deptno字段设置了⾃增模式,⽽默认默认情况下对⾃增字段的插⼊是数据库⾃⼰维护的，所以当⽤户⼿动指定时则会抛出该异常.
create table dept(
deptno int IDENTITY(1,1) NOT NULL,
dname varchar(15),
loc varchar(50),
primary key(deptno)
);
解决该问题有3种办法:
1) 修改表结构的定义，去掉表的⾃增属性。不建议。
2) 只插⼊除⾃增外的其他字段，让数据库⾃⾏维护⾃增字段。建议。
3) 通过打开表的IDENTITY_INSERT开关，显⽰插⼊指定的⾃增字段。建议.
其中第三种⽅法的实现代码见下:
SET IDENTITY_INSERT dept ON;
GO
INSERT INTO dept(deptno,dname,loc) VALUES (50,'Production','Shanghai')
SET IDENTITY_INSERT dept OFF;
GO
Oracle:
Oracle⾥对于⾃增字段的维护⿇烦点，因为它没有对应的关键字.不过我们可以通过内置的数据库对象sequence来实现.具体实现见下:
create sequence dept_autoinc
minvalue 50
maxvalue 9999999999999999999999999999
startwith 50
incrementby 10
nocache;
INSERT INTO dept VALUES (val,'Production','Shanghai');
DEPTNO DNAME LOC
10ACCOUNTING NEW
YORK
20RESEARCH DALLAS
30SALES CHICAGO
40OPERATIONS BOSTON
50Production Shanghai
7.4.3 插⼊新的多条记录
需求：向dept表中插⼊部门编号为50，部门名称为Production，部门位置为Shanghai和部门编号为60，部门名称为Programming，部门位置为Beijing的数据.
解决⽅法：这⾥通过INSERT INTO TableName VALUES (…),(…),(…)
Sql server 、Mysql:
INSERT INTO dept VALUES (50,'Production','Shanghai'), (60,'Programming','Beijing');
Oracle:
INSERT ALL INTO dept VALUES (50,'Production','Shanghai')
INTO dept VALUES (60,'Programming','Beijing')
select 1 from dual;
7.4.4 同时往多个表插⼊记录
需求：从dept表⾥插⼊数据到3张表，当loc是NEW YORK和BOSTON时向dept_east表中插⼊，当当loc是CHICAGO时向dept_mid表中插⼊，其它情况往dept_west表中插⼊.
解决⽅法：这⾥通过INSERT ALL WHEN Condition THEN INTO TABLENAME VALUES (…)的⽅式.
Oracle:
CREATE TABLE dept_east
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
CREATE TABLE dept_mid
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
CREATE TABLE dept_west
(DEPTNO NUMBER(2),
DNAME VARCHAR2(14),
LOC VARCHAR2(13));
INSERT ALL
WHEN loc IN ('NEW YORK','BOSTON') THEN
INTO dept_east(deptno,dname,loc) VALUES (deptno,dname,loc)
WHEN loc IN ('CHICAGO') THEN
INTO dept_mid(deptno,dname,loc) VALUES (deptno,dname,loc)
ELSE
INTO dept_west (deptno,dname,loc) VALUES (deptno,dname,loc)
SELECT deptno,dname,loc FROM dept
我们可以通过如下的SQL，清晰的看到分散到3个表的数据:
SELECT A.*,'dept_east' AS TableSource FROM dept_east A
UNION ALL
SELECT A.*,'dept_mid' AS TableSource FROM dept_mid A
UNION ALL
SELECT A.*,'dept_west' AS TableSource FROM dept_west A;
DEPTNO DNAME LOC TABLESOURCE
10ACCOUNTING NEW
dept_east
YORK
40OPERATIONS BOSTON dept_east
30SALES CHICAGO dept_mid
20RESEARCH DALLAS dept_west
注: 截⽌⽬前，仅oracle⽀持该语法.
7.4.5 通过其它表插⼊
需求：向dept表中插⼊部门编号为50，部门名称为Production，部门位置为Shanghai的数据.这⾥需要指定通过SELECT其它表的⽅式插⼊
解决⽅法：这⾥通过INSERT INTO TableName SELECT ColumnName FROM TableName …
如果我们想将⼀个表或则多个表的数据插⼊到另外⼀张新的表，也可以通过INSERT INTO TableName SELECT的⽅式.
Sql server 、Mysql:
CREATE TABLE temp(
deptno varchar(50) NULL,
deptname varchar(50) NULL,
loc varchar(50) NULL,
empno varchar(50) NULL,
ename varchar(50) NULL,
sal int NULL
);
Oracle:
CREATE TABLE temp(
deptno varchar2(50) NULL,
deptname varchar2(50) NULL,
loc varchar2(50) NULL,
empno varchar2(50) NULL,
ename varchar2(50) NULL,
sal int NULL
);
Sql server 、Mysql、Oracle:
INSERT INTO temp(deptno,deptname,loc)
SELECT deptno,dname,loc
FROM dept
WHERE dname in ('SALES','OPERATIONS')
sql中union多表合并
7.4.6 通过多表关联插⼊
需求：通过dept和emp表向temp表中部门名称为RESEARCH何ACCOUNTING0的数据.这⾥temp表的字段来⾃dept表和emp表.解决⽅法：这⾥通过INSERT INTO TableName SELECT ColumnName FROM TableA JOIN TableB …
Sql server 、Oracle、Mysql:
INSERT INTO temp(deptno,deptname,loc,empno,ename)
SELECT A.deptno,A.dname,A.loc,B.ame
FROM dept A
JOIN emp B
ON A.DEPTNO = B.Deptno
WHERE A.dname in ('RESEARCH','ACCOUNTING')
ORDER BY A.deptno;
--或者
INSERT INTO temp(deptno,deptname,loc,empno,ename)
SELECT A.deptno,A.dname,A.loc,B.ame
FROM dept A,emp B
WHERE A.DEPTNO = B.Deptno
AND A.dname IN ('RESEARCH','ACCOUNTING')
ORDER BY A.deptno;
7.4.7 通过视图插⼊
需求：向dept表中插⼊部门编号为60，部门名称为Testing，部门位置为Guangzhou的数据.这⾥需要指定通过借助视图的⽅式插⼊.解决⽅法：这⾥需要先建⽴⼀张视图，然后往视图⾥插⼊数据.
Sql server、Mysql、Oracle:
CREATE VIEW v_deptAS
SELECT deptno,deptname,loc FROM temp;
INSERT INTOv_dept VALUES (60,'Testing','Guangzhou');
SELECT * FROM temp;
执⾏结果:
deptno deptname loc empno ename
50Production Nanjing7369SMITH
60Testing Guangzhou NULL NULL
7.4.8 插⼊⼿⼯数据
需求：向dept表中插⼊部门编号为50，部门名称为Production，部门位置为Shanghai和部门编号为60，部门名称为Programming，部门位置为Beijing的数据.
解决⽅法：这⾥通过INSERT INTO TableName SELECT value1,value2 UNION ALL SELECT … 的⽅式来事项该功能.
Mysql、Sql server:
INSERT INTO dept(deptno,dname,loc)
SELECT 50,'Production','Shanghai'
UNION ALL
SELECT 60,'Programming','Beijing';
注：这⾥假设SQL Server⾥的dept表已经开启SET IDENTITY_INSERT dept ON或者deptno不是⾃增字段.
Oracle:
INSERT INTO dept(deptno,dname,loc)
SELECT 50,'Production','Shanghai' FROM DUAL
UNION ALL
SELECT 60,'Programming','Beijing' FROM DUAL;
7.4.9 插⼊默认值
需求：指定dept表loc字段的默认值是Beijing，并向该表中插⼊部门编号为50，部门名称为Production的数据.
解决⽅法：这⾥需要DDL的⾥知识，即对表dept在loc这列新增个默认值的约束.当我们不去插⼊loc这列时数据库会⾃动补充默认约束⾥定义的值.
SQL Server:
ALTER TABLE dept add CONSTRAINT DF_dept_loc DEFAULT 'Beijing'FOR loc;
SET IDENTITY_INSERT dept ON;
GO
INSERT INTO dept(deptno,dname) VALUES (50,'Production');
SET IDENTITY_INSERT dept OFF;
GO
SELECT * FROM dept WHERE deptno=50;
执⾏结果:
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论