SQL基础操作_4_表的插入、更新、删除、合并操作

最新推荐文章于 2022-01-18 20:01:27 发布
最新推荐文章于 2022-01-18 20:01:27 发布
阅读量1.5k 收藏 4
点赞数
分类专栏： # SQL案例 文章标签： oracle sql server mysql SQL 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shenliang1985/article/details/79109651
版权

目录

 表的插入、更新、删除、合并操作

7.4.1 插入新的记录

7.4.2 插入含自增列的记录

7.4.3 插入新的多条记录

7.4.4 同时往多个表插入记录

7.4.5 通过其它表插入

7.4.6 通过多表关联插入

7.4.7 通过视图插入

7.4.8 插入手工数据

7.4.9 插入默认值

7.4.10 仅复制表结构

7.4.11 更新表的记录

7.4.12 通过表关联更新表

7.4.13 通过表关联更新多个字段

7.4.14 通过表关联更新多个表多个字段

7.4.15 按照默认值更新表

7.4.16 删除表中所有记录

7.4.17 按照条件删除表中记录

7.4.18 清空表中记录

7.4.19 通过表关联删除记录

7.4.20 通过表关联删除多张表

7.4.21 合并表中数据

7.4 表的插入、更新、删除、合并操作

注:数据集和表结构见 SQL基础操作_1_检索数据

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 (dept_autoinc.nextval,'Production','Shanghai');

DEPTNO

DNAME

LOC

10

ACCOUNTING

NEW YORK

20

RESEARCH

DALLAS

30

SALES

CHICAGO

40

OPERATIONS

BOSTON

50

Production

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

10

ACCOUNTING

NEW YORK

dept_east

40

OPERATIONS

BOSTON

dept_east

30

SALES

CHICAGO

dept_mid

20

RESEARCH

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')

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.EMPNO,B.ename
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.EMPNO,B.ename
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

50

Production

Nanjing

7369

SMITH

60

Testing

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;

    执行结果:

deptno

dname

loc

50

Production

Beijing


注:

1)  上述的insert语句,loc这列并没有显示维护,是数据库里的默认约束自动插入的.

2)  如果你的表里每一列都直接或间接定义了默认值,那么可以通过如下语句插入一个默认值:

INSERT INTO TableName DEFAULT VALUES;

完整例子见下:

IF OBJECT_ID('dbo.T1','U') IS NOT NULL
    DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(column_1 AS 'Computedcolumn ' + column_2,
   column_2 varchar(30)
   CONSTRAINT default_name DEFAULT ('my column default'),
   column_3 rowversion,
   column_4 varchar(40)NULL
);
GO
INSERT INTO T1 DEFAULT VALUES;

执行结果:

column_1

column_2

column_3

column_4

Computed column my column default

my column default

0x00000000000007D5

NULL


Mysql:

alter table dept alter loc set default 'Beijing';
INSERT INTO dept(deptno,dname) VALUES(50,'Production');
SELECT * FROM dept WHERE deptno=50;

deptno

dname

loc

50

Production

Beijing


Oracle:

ALTER TABLE DEPT MODIFY LOC DEFAULT 'Beijing';
INSERT INTO dept(deptno,dname) VALUES (50,'Production');
SELECT * FROM dept WHERE deptno=50;

    执行结果:

deptno

dname

loc

50

Production

Beijing


7.4.10 仅复制表结构

需求:快速新建一个和dept结构一样的表dept_new,仅需要字段定义一致,不需要数据.

解决方法:这里通过SELECT * FROM TableName WHERE 1=0的方式仅拷贝到表结构,而没有数据.

Oracle、Mysql:

CREATE TABLE dept_new AS
SELECT * FROM dept
WHERE 1= 2;

Sql server:

SELECT * INTO dept_new FROM dept WHERE 1=0;

注: 如果想既复制表又复制数据,则不需要加WHERE 1=0的限制条件即可.

7.4.11 更新表的记录

需求:更新dept表里deptno为50的记录对应的loc字段为Nanjing

解决方法:这里通过UPDATE TableName SET ColName=.. WHERE …的方式来更新数据.

Oracle、Mysql、Sql server:

UPDATE dept
SET loc='Nanjing'
WHERE deptno=50;

注:

1) 如果想更新表中某个或某几个字段的所有记录,只要不加WHERE条件过滤即可.

2) 如果想更新多个字段,直接在SET后面跟字段等于相应的值即可.

7.4.12 通过表关联更新表

需求:更新emp表里的sal字段的值为temp表里的sal乘以1.5,这些员工号同时出现在在temp表中.

解决方法:这里通过UPDATE TableName SET ColName=..FROM TABLENAME1 A JOIN TABLENAME2 B … WHERE …的方式来更新数据.

Sql server:

TUNCATE TABLE temp;
INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)
SELECT 50,'Production','Nanjing',7369,'SMITH',1000

BEGIN TRAN
SELECT empno,ename,sal FROM emp WHERE empno=7369;

UPDATE A
SET A.sal= B.sal*1.5
FROM emp A
JOIN temp B
ON A.empno= B.empno
 
SELECT empno,ename,sal FROM emp WHERE empno=7369;
ROLLBACK TRAN

执行更新前:

empno

ename

sal

7369

SMITH

800.00


执行更新后:

empno

ename

sal

7369

SMITH

1500.00


注:

这里的join方式也可以写成如下的形式:

UPDATE A
SET A.sal= B.sal*1.5
FROM emp A,temp B
WHERE A.empno= B.empno

Mysql:

TRUNCATE TABLE temp;
INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)
SELECT 50,'Production','Nanjing',7369,'SMITH',1000

UPDATE emp A
INNER JOIN fridge.temp B
ON A.empno= B.empno
SET A.sal= B.sal*1.5

或者:

UPDATE emp,temp
SET emp.sal=temp.sal*1.5
WHERE emp.empno=temp.empno;

Oracle:

TRUNCATE TABLE temp;
INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)
SELECT 50,'Production','Nanjing',7369,'SMITH',1000 FROM DUAL;

update emp a
set sal=(select b.sal*1.5from temp b whereb.empno=a.empno)
where exists (select1
from temp b
where b.empno=a.empno
)
SELECT empno,ename,sal FROM emp WHERE empno=7369;

结果跟上述相同.

7.4.13 通过表关联更新多个字段

需求:更新emp表里的sal字段的值为temp表里的sal乘以1.5,同时更新ename为temp表里的值, 匹配条件是两个表的deptno..

解决方法:这里通过UPDATE TableName SET ColName=..FROM TABLENAME1 A JOIN TABLENAME2 B … WHERE …的方式来更新数据.

Oracle:

TRUNCATE TABLE temp;

INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)

SELECT 50,'Production','Nanjing',7369,'SMITH2',1000 FROM DUAL;

update emp a
set (sal,ename)=(select b.sal*1.5,b.enamefrom temp bwhereb.empno=a.empno)
where exists (select1
from temp b
where b.empno=a.empno
)
SELECT empno,ename,sal FROM emp WHERE empno=7369;

执行更新前:

Empno

ename

sal

7369

SMITH

800.00

SELECT empno,ename,sal FROM emp WHERE empno=7369;

执行更新后:

empno

ename

sal

7369

SMITH2

1500.00


Sql Server:

TRUNCATE TABLE temp;
INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)
SELECT 50,'Production','Nanjing',7369,'SMITH2',1000
BEGIN TRAN
SELECT empno,ename,sal FROM emp WHERE empno=7369;

UPDATE emp
SET emp.sal=b.sal*1.5,emp.ename=b.ename from emp a,temp b
WHERE a.empno= b.empno
 
UPDATE emp
SET emp.sal=b.sal*1.5,emp.ename=b.ename from emp a
JOIN temp b
ON a.empno= b.empno

SELECT empno,ename,sal FROM emp WHERE empno=7369;

ROLLBACK

执行更新前:

Empno

ename

sal

7369

SMITH

800.00


执行更新后:

empno

ename

sal

7369

SMITH2

1500.00


Mysql:

BEGIN;
SELECT empno,ename,sal FROM emp WHERE empno=7369;
UPDATE emp a,temp b
SET a.sal=b.sal*1.5 ,a.ename=b.ename
WHERE a.empno= b.empno;
 
UPDATE emp a
INNER JOIN temp b
ON a.empno= b.empno
SET a.sal=b.sal*1.5 ,a.ename=b.ename;

SELECT empno,ename,sal FROM emp WHERE empno=7369;
ROLLBACK;

执行结果相同.

举一反三:这里多表关联时用到的是join(inner join),当然你也可以通过left join、right join以及不等值连接。

7.4.14 通过表关联更新多个表多个字段

更新emp表里的sal字段的值为temp表里的sal乘以1.5,同时更新temp表里ename为emp表里的值,匹配条件是两个表的deptno.

Mysql:

TRUNCATE TABLE temp;
INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)
SELECT50,'Production','Nanjing',7369,'SMITH2',1000

BEGIN;
SELECT empno,ename,sal,'From_emp' as Table_Name FROM emp WHERE empno=7369
UNION
SELECT empno,ename,sal,'From_temp' as Table_Name FROM temp WHERE empno=7369;
UPDATE emp a,temp b
SET a.sal=b.sal*1.5 ,b.ename=a.ename
WHERE a.empno= b.empno;

UPDATE emp a
INNERJOIN temp b
ON a.empno= b.empno
SET a.sal=b.sal*1.5 ,b.ename=a.ename;

SELECT empno,ename,sal,'From_emp' as Table_Name FROM emp WHERE empno=7369
UNION
SELECT empno,ename,sal,'From_temp' as Table_Name FROM temp WHERE empno=7369;

ROLLBACK;

执行更新前:

empno

ename

sal

Table_Name

7369

SMITH

800.00

From_emp

7369

SMITH2

1000.00

From_temp


执行更新后:

empno

ename

sal

Table_Name

7369

SMITH

1500.00

From_emp

7369

SMITH

1000.00

From_temp


注:Oracle和SQL Server里暂未发现该语法.

7.4.15 按照默认值更新表

需求:更新deptno对应是50的dept表的loc字段成默认值.

解决方法:这里通过UPDATE TableName SET ColName=DEFAULT …的方式来更新数据.

SQL Server:

BEGIN TRAN
SELECT * FROM dept WHERE deptno= 50;

UPDATE dept
SET loc = DEFAULT
WHERE deptno= 50;

SELECT * FROM dept WHERE deptno= 50;
ROLLBACK TRAN

MySQL:

BEGIN;
SELECT * FROM dept WHERE deptno=40;
UPDATE dept
SET loc=default
WHERE deptno=40;

SELECT * FROM dept WHERE deptno=40;
ROLLBACK;

Oracle:

SELECT * FROM dept WHERE deptno=40;

UPDATE dept
SET loc=default
WHERE deptno=40;
SELECT * FROM dept WHERE deptno=40;
ROLLBACK;

执行更新前:

deptno

dname

loc

50

Production

Nanjing


执行更新后:

deptno

dname

loc

50

Production

Beijing


 

7.4.16 删除表中所有记录

需求:删除dept表里的所有记录,但后期可通过数据库日志恢复.

解决方法:这里通过DELETE FROM TableName的方式来删除数据.

Oracle、Mysql、Sql server:

DELETE FROM dept;
DELETE * FROM dept;

7.4.17 按照条件删除表中记录

需求:删除dept表里deptno为50的记录,但后期可通过数据库日志恢复.

解决方法:这里通过DELETE FROM TableName WHERE Columname= …的方式来删除数据.

Oracle、Mysql、Sql server:

DELETE FROM dept WHERE deptno=50;
DELETE FROM dept WHERE deptno IN (50);

7.4.18 清空表中记录

需求:删除dept表里deptno为50的记录,但数据不可再恢复.

解决方法:这里通过TRUNCATE TABLE TableName方式来删除数据.

Oracle、Mysql、Sql server:

TRUNCATE TABLE dept;

7.4.19 通过表关联删除记录

需求:删除dept表里deptno和temp相同的记录,但数据可再恢复.

解决方法:这里通过DELETE FROM tableNAME A JOIN tableName B …方式来删除数据.

Sql server:

BEGIN TRAN
 SELECT * FROM dept;
 DELETE FROM dept
 FROM dept A
 JOIN temp B
 ON A.deptno= B.deptno
 SELECT * FROM dept;
ROLLBACK TRAN

Mysql:

BEGIN;

SELECT * FROM dept A;
-- JOIN temp B
-- ON A.deptno = B.deptno
 
DELETE A FROM  dept A
INNER JOIN temp B
ON A.deptno= B.deptno AND B.deptno=40;
--或者见下
DELETE dept FROM  dept,temp
WHERE dept.deptno= temp.deptno AND temp.deptno=40;
SELECT * FROM dept;

ROLLBACK;

不难发现,这里dept表里deptno=40的记录被删除了.

deptno

dname

loc

10

ACCOUNTING

NEW YORK

20

RESEARCH

DALLAS

30

SALES

CHICAGO

50

Production

Beijing


Oracle:

INSERT INTO dept VALUES(50,'Production','Nanjing');
INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)
SELECT 50,'Production','Nanjing',7369,'SMITH2',1000 FROM DUAL;
DELETE FROM dept WHERE EXISTS (SELECT 1 FROM TEMP WHERE dept.deptno = temp.deptno)

执行结果:

DEPTNO

DNAME

LOC

10

ACCOUNTING

NEW YORK

20

RESEARCH

DALLAS

30

SALES

CHICAGO

40

OPERATIONS

BOSTON


7.4.20 通过表关联删除多张表

需求:删除dept表里deptno和temp相同的记录,但数据不可再恢复.

解决方法:这里通过DELETE FROM A,B FROM tableNAME A JOIN tableName B ON …方式来删除数据.

INSERT INTO dept VALUES(50,'Production','Nanjing');
INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)
SELECT 50,'Production','Nanjing',7369,'SMITH2',1000;
BEGIN;
SELECT deptno,dname,loc,'tab_dept' as Table_Name FROM dept WHERE deptno=50
UNION ALL
SELECT deptno,deptname,loc,'tab_temp' as Table_Name FROM temp WHERE deptno=50;
 
DELETE A,B FROM  dept A
INNER JOIN temp B
ON A.deptno= B.deptno AND B.deptno=50;
 
SELECT deptno,dname,loc,'tab_dept' as Table_Name FROM dept WHERE deptno=50
UNION ALL
SELECT deptno,deptname,loc,'tab_temp' as Table_Name FROM temp WHERE deptno=50;
ROLLBACK;

执行之前:

deptno

dname

loc

Table_Name

50

Production

Nanjing

tab_dept

50

Production

Nanjing

tab_temp


执行之后, temp和dept这两张表里deptno为50的记录均被删除:

deptno

dname

loc

Table_Name


注: SQL Server、Oracle目前未见相关语法。

7.4.21 合并表中数据

需求:如果emp_temp表中的员工存在于emp中,则将他们的提成(comm字段)更新为1000;

对于提成已经更新为1000的员工,如果他们的工资(SAL字段)少于2000,则从emp_temp表里删除他们;

其它情况从EMP表里取员工编号(EMPNO字段)、员工名称(ENAME字段)、部门号(DEPTNO字段)插入到emp_temp中.

Oracle:

CREATE TABLE emp_temp AS
 SELECT deptno,empno,ename,comm FROM emp
 WHERE empno IN (7782,7839,7934);

SELECT et.empno,et.ename,et.deptno,et.comm,emp.sal
FROM emp_temp et
JOIN emp
ON et.empno = emp.empno;

MERGE INTO emp_tempet
 USING(SELECTempno,ename,deptno,comm,SAL FROM emp) emp
 ON(et.empno = emp.empno)
 WHEN MATCHED THEN
   UPDATE SET et.comm=1000
   DELETE WHERE (SAL<2000)
 WHEN NOTMATCHED THEN
   INSERT(et.empno,et.ename,et.deptno,et.comm)
   VALUES(emp.empno,emp.ename,emp.deptno,emp.comm);
 
SELECT et.empno,et.ename,et.deptno,et.comm,emp.sal
 FROM emp_tempet
 JOIN emp
 ON et.empno= emp.empno;

执行前:

EMPNO

ENAME

DEPTNO

COMM

SAL

7782

CLARK

10

2450.00

7839

KING

10

5000.00

7934

MILLER

10

1300.00


执行后,emp_temp表里员工编号为7782和7839的comm被更新成了1000,而7934这条记录被删除了,其它情况的数据从emp表里插入了过来.

EMPNO

ENAME

DEPTNO

COMM

SAL

7369

SMITH

20

1500.00

7499

ALLEN

30

300.00

1600.00

7521

WARD

30

500.00

1250.00

7566

JONES

20

2975.00

7654

MARTIN

30

1400.00

1250.00

7698

BLAKE

30

2850.00

7782

CLARK

10

1000.00

2450.00

7788

SCOTT

20

3000.00

7839

KING

10

1000.00

5000.00

7844

TURNER

30

0.00

1500.00

7876

ADAMS

20

1100.00

7900

JAMES

30

950.00

7902

FORD

20

3000.00


SqlServer:

CREATE TABLE emp_temp(
   deptno int NULL,
   empno int NOT NULL,
   ename varchar(15)NULL,
   comm decimal(7, 2)NULL
)
INSERT INTO emp_temp
SELECT deptno,empno,ename,comm FROM emp
WHERE empno IN (7782,7839,7934);

BEGIN TRAN
SELECT et.empno,et.ename,et.deptno,et.comm,emp.sal
FROM emp_temp et
JOIN emp
ON et.empno= emp.empno;

MERGE INTO emp_temp et
 USING(SELECT empno,ename,deptno,comm,SAL FROM emp) emp
 ON (et.empno= emp.empno)
 WHEN MATCHEDAND SAL< 2000THEN
   DELETE WHEN MATCHED THEN
   UPDATE SET et.comm = 1000
 WHEN NOTMATCHED THEN

INSERT VALUES(emp.deptno,emp.empno,emp.ename,emp.comm);
SELECT et.empno,et.ename,et.deptno,et.comm,emp.sal
FROM emp_temp et
JOIN emp
ON et.empno= emp.empno;
ROLLBACK TRAN

执行前:

empno

ename

deptno

comm

sal

7782

CLARK

10

NULL

2450.00

7839

KING

10

NULL

5000.00

7934

MILLER

10

NULL

1300.0


执行Merge into之后:

empno

ename

deptno

comm

sal

7782

CLARK

10

1000.00

2450.00

7839

KING

10

1000.00

5000.00

7369

SMITH

20

NULL

800.00

7499

ALLEN

30

300.00

1600.00

7521

WARD

30

500.00

1250.00

7566

JONES

20

NULL

2975.00

7654

MARTIN

30

1400.00

1250.00

7698

BLAKE

30

NULL

2850.00

7788

SCOTT

20

NULL

3000.00

7844

TURNER

30

0.00

1500.00

7876

ADAMS

20

NULL

1100.00

7900

JAMES

30

NULL

950.00

7902

FORD

20

NULL

3000.00


注:

1)  这里SQL Server和Oracle的mergeinto语法还是有差异的.SQL Server里如下代码:

WHEN MATCHEDAND SAL< 2000 THEN

   DELETE

要写在前面,如果和UPDATE互换了位置,则会报如下错:

消息 5324，级别 16，状态 1，第 11 行

在 MERGE 语句中，带搜索条件的 'WHEN MATCHED' 子句不能出现在不带搜索条件的 'WHENMATCHED' 子句后。

2)  目前Mysql并没有有merge into的语法.

ShenLiang2025
关注
专栏目录
批量操作：C＃SQL批量操作用于SQL Server，SQL Azure，SQL Compact，MySQL和SQLite的高性能C＃批量插入，更新，删除和合并
02-05
提高SQL的插入，更新，删除和合并性能，并克服SqlBulkCopy的限制 稽核 区分大小写 实体数据源/ Lambda映射 产值 和更多... 可扩展 SQL Server-基准 运作方式 1,000行 10,000行 100,000行 1,000,000行 插 6毫秒 25毫秒 200毫秒 2,000毫秒 更新资料 50毫秒 80毫秒 575毫秒 6,500毫秒 删除 45毫秒 70毫秒 625毫秒 6,800毫秒 合并 65毫秒 160毫秒 1,200毫秒 12,000毫秒 插入速度与SqlBulkCopy一样快，但功能更多 可扩展的 支持多个SQL提供程序： SQL Server 2
oracle结构试题,Oracle练习题集
weixin_39623050的博客
04-03 343
试题要求：考生将所用SQL PLUS命令及正确显示结果存入以考生本人姓名命名的记事本文件。一、以SYSTEM身份登录，建立部门表和员工表并录入数据：1.dept部门表结构如下：(1)提示：部门号，部门名，地址)(2)要求DEPTNO为主键，DNAME取值唯一；名称 是否为空？ 类型DEPTNO NOT NULL NUMBER(2)DNAME...
Oracle练习题集
weixin_34346099的博客
03-05 255
试题要求：考生将所用SQL PLUS命令及正确显示结果存入以考生本人姓名命名的记事本文件。一、以SYSTEM身份登录，建立部门表和员工表并录入数据：1.dept部门表结构如下：（1）提示：部门号，部门名，地址）（2）要求DEPTNO为主键，DNAME取值唯一； 名称 是否为空？ 类型DEPTNO NOT NULL NUM...
Oracle基础知识（6-10）
weixin_44560813的博客
07-18 1145
开发工具与关键技术：Oracle sql*plus PLSQL Developer 作者：罗小龙 撰写时间：2019年03月26日 ORACLE知识总结 第六章——子查询 与 第十章——视图 例：查询和SMITH同部门的员工的信息，但是不包括SMITH select * from employees where last_name <> ‘SMITH’ and department...
oracle建表查询试题,Oracle练习题集
weixin_39542742的博客
04-05 267
名称 是否为空？ 类型DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(13)记录值如下：DEPTNO DNAME LOC------- -------------- -...
SQL.zip_sql_sql 语句_sql语言
最新发布
09-22
1. JOIN操作：用于连接两个或多个表，根据关联条件合并数据，如INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL JOIN。 2. GROUP BY与聚合函数：用于对数据进行分组并计算摘要信息，如COUNT、SUM、AVG、MAX和MIN。 3. 子...
SQL.rar_sql_sql 语法
09-22
它被广泛应用于数据查询、更新、插入和删除等操作。在数据库管理系统（如MySQL、Oracle、SQL Server等）中，SQL是不可或缺的一部分。以下是对"SQL.rar_sql_sql 语法"这个压缩包文件中可能包含的关键知识点的详细解释...
SQL.rar_sql_sql 语句
09-14
2. **数据操作语言（DML）**：处理数据库中的数据，包括插入、更新和删除。`INSERT INTO`用于插入新记录，`UPDATE`用于修改现有记录，`DELETE FROM`则是删除记录。 3. **数据查询语言（DQL）**：查询数据库中的数据...
SQL.rar_sql_sql 习题
09-14
总结来说，这个“SQL.sql”习题集是学习和巩固SQL技能的宝贵资源，涵盖了SQL的各个方面，从基础操作到高级特性和实践应用，对于任何想要提升SQL能力的人来说都是不可或缺的工具。通过深入学习和反复练习，你将能够...
sqlserver中操作主从关系表的sql语句
01-19
典型的方法就是对于主表的各数据字段进行更新或新增，对于从表一般都会先删除相关的所有记录，然后再进行插入。也就是说每次保存时，从表的数据都要重新执行一遍删除再插入的过程。这是一个不错的方法，在使用数据库...
创建一个过程，向dept表中添加一个新记录
12-14
1.创建一个过程，向dept表中添加一个新记录。（in参数） 创建过程: create or replace procedure Insert_dept ( num_dept in number ,v_ename in varchar3)is begin insert into dept values(num_dept,v_ename); commit; end insert_dept; 调用: begin insert_dept(66,'人事'); end; 2.从scott.emp表中查询给定职工（提示：使用&来输入员工编号）的职工姓名和工资。（要求：利用out模式的参数将值传
第11章 数据操作与事务控制
weixin_40476233的博客
04-04 2792
1、插入语句 insert into 表名（列名）values （列的值）2、修改数据update语句3、删除 语句delete   SELECT * FROM emp--插入数据,insert语句--写出要给值的列名，INSERT INTO dept(deptno,dname,loc) VALUES(50,'HHHD','GUANGZHOU');--省略列名，默认所有列都要有值INSERT IN...
java oracle 插入_oracle 连接java 并且用java向数据库数插入数据------在dept表格中 插入 新的部门编号 部门名称 部门位置...
weixin_35129495的博客
02-20 301
package oracledemo;import java.sql.*;import java.util.Scanner;import javax.sql.*;public class moxie0926 {/*** @param args*/public static void main(String[] args) {Scanner input=new Scanner(System.in);...
Mybatis 批量操作（批量插入、批量更新、批量删除）总结
ddxshf的博客
10-28 3305
文章目录一、批量插入二、批量更新三、批量删除 一、批量插入 <insert id="insertBatch" parameterType="java.util.List"> INSERT INTO business_database (id, person_id, name, id_card, cman, ctime) VALUES <foreach collection="list" index="index" s
实验八：存储过程应用
qq_45823118的博客
05-24 1783
一、实验目的 熟悉掌握存储过程的创建和调用，以及存储过程中不同参数类型（IN参数、OUT参数）的使用。 二、实验内容 1、存储过程IN参数的应用。练习在SCOTT模式中创建存储过程，并使用过程访问其中的数据表。将所有执行正确的PL/SQL语句记录下来。 （1）创建用于向DEPT表中插入记录的过程（过程名自拟） （2）请使用两种方法调用该过程。 2.存储过程OUT参数的应用。练习在SCOTT模式中创建存储过程，并使用过程访问其中的数据表。将所有执行正确的PL/SQL语句记录下来。 （1）创建存储过程
表的插入、更新、删除、合并操作_3_插入新的多条记录
数据科学汇集
04-03 409
插入新的多条记录 需求描述 需求：向dept表中插入部门编号为50,部门名称为Production,部门位置为Shanghai和部门编号为60,部门名称为Programming,部门位置为Beijing的数据. 解决方法:这里通过INSERT INTO TableName VALUES (…),(…),(…) 注: 数据库数据集SQL脚本详见如下链接地址 员工表结构和数据初始化SQL脚本 SQL代码 -- Sql server 、My...
表的插入、更新、删除、合并操作_9_插入默认值
数据科学汇集
04-03 235
插入默认值 需求描述 需求：指定dept表loc字段的默认值是Beijing,并向该表中插入部门编号为50,部门名称为Production的数据. 解决方法:这里需要DDL的里知识,即对表dept在loc这列新增个默认值的约束.当我们不去插入loc这列时数据库会自动补充默认约束里定义的值. 注: 数据库数据集SQL脚本详见如下链接地址 员工表结构和数据初始化SQL脚本 SQL代码 -- SQL Server: ALTER TABLE ...
oracle学习笔记之七 数据操作
luyangever的专栏
12-13 1158
 1.追加数据  INSERT INTO table[(column[,column...])]  VALUES      (value[,value...]);     例1：INSERT INTO dept(deptno,dname,loc)       VALUES      (50,DEVELOPMENT,DETROIT);  例2：INSERT INTO dept      
SQLServer -＞ 使用SQL语句将数据库中的两个表合并成一张表
热门推荐
caoyanchao1的博客
01-18 3万+
select * into 新表名 from (select * from T1 union all select * from T2) 这个语句可以实现将合并的数据追加到一个新表中。 不合并重复数据 select * from T1 union all select * from T2 合并重复数据 select * from T1 union select * from T2 两个表，表1 表2 如果要将 表1的数据并入表2用以下语句即可 insert into 表2(字段1,字段
SQL基础操作全解析：查询、插入、删除与聚合
本文档涵盖了SQL基础语句的全面概述，主要介绍了SQL（Structured ...本文档详细介绍了SQL的基础操作，从基本的查询和数据操作到更复杂的联接和分组，以及数据库对象的管理和备份，对于学习和掌握SQL语言非常有帮助。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

ShenLiang2025

您的鼓励是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值