目录
表的插入、更新、删除、合并操作
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;GOINSERT INTO dept(deptno,dname,loc) VALUES (50,'Production','Shanghai')SET IDENTITY_INSERT dept OFF;GO
Oracle:
Oracle里对于自增字段的维护麻烦点,因为它没有对应的关键字.不过我们可以通过内置的数据库对象sequence来实现.具体实现见下:
create sequence dept_autoincminvalue 50maxvalue 9999999999999999999999999999startwith 50incrementby 10nocache;INSERT INTO dept VALUES (dept_autoinc.nextval,'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 ALLWHEN loc IN ('NEW YORK','BOSTON') THENINTO dept_east(deptno,dname,loc) VALUES (deptno,dname,loc)WHEN loc IN ('CHICAGO') THENINTO dept_mid(deptno,dname,loc) VALUES (deptno,dname,loc)ELSEINTO 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 AUNION ALLSELECT A.*,'dept_mid' AS TableSource FROM dept_mid AUNION ALLSELECT A.*,'dept_west' AS TableSource FROM dept_west A;
注:截止目前,仅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,locFROM deptWHERE 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.enameFROM dept AJOIN emp BON A.DEPTNO = B.DeptnoWHERE 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.enameFROM dept A,emp BWHERE A.DEPTNO = B.DeptnoAND A.dname IN ('RESEARCH','ACCOUNTING')ORDER BY A.deptno;
7.4.7 通过视图插入
需求：向dept表中插入部门编号为60,部门名称为Testing,部门位置为Guangzhou的数据.这里需要指定通过借助视图的方式插入.
解决方法:这里需要先建立一张视图,然后往视图里插入数据.
Sql server、Mysql、Oracle:
CREATE VIEW v_deptASSELECT deptno,deptname,loc FROM temp;INSERT INTOv_dept VALUES (60,'Testing','Guangzhou');SELECT * FROM temp;
执行结果:
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 ALLSELECT 60,'Programming','Beijing';
注:这里假设SQL Server里的dept表已经开启SET IDENTITY_INSERT dept ON或者deptno不是自增字段.
Oracle:
INSERT INTO dept(deptno,dname,loc)SELECT 50,'Production','Shanghai' FROM DUALUNION ALLSELECT 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;GOINSERT INTO dept(deptno,dname) VALUES (50,'Production');SET IDENTITY_INSERT dept OFF;GOSELECT * FROM dept WHERE deptno=50;
执行结果:
注:
1) 上述的insert语句,loc这列并没有显示维护,是数据库里的默认约束自动插入的.
2) 如果你的表里每一列都直接或间接定义了默认值,那么可以通过如下语句插入一个默认值:
INSERT INTO TableNameDEFAULTVALUES;
完整例子见下:
IF OBJECT_ID('dbo.T1','U') IS NOT NULLDROP TABLE dbo.T1;GOCREATE 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);GOINSERT INTO T1 DEFAULT VALUES;
执行结果:
Mysql:
alter table dept alter loc set default 'Beijing';INSERT INTO dept(deptno,dname) VALUES(50,'Production');SELECT * FROM dept WHERE deptno=50;
Oracle:
ALTER TABLE DEPT MODIFY LOC DEFAULT 'Beijing';INSERT INTO dept(deptno,dname) VALUES (50,'Production');SELECT * FROM dept WHERE deptno=50;
执行结果:
7.4.10 仅复制表结构
需求:快速新建一个和dept结构一样的表dept_new,仅需要字段定义一致,不需要数据.
解决方法:这里通过SELECT * FROM TableName WHERE 1=0的方式仅拷贝到表结构,而没有数据.
Oracle、Mysql:
CREATE TABLE dept_new ASSELECT * FROM deptWHERE 1= 2;
Sql server:
SELECT * INTO dept_new FROM dept WHERE 1=0;
注:如果想既复制表又复制数据,则不需要加WHERE1=0的限制条件即可.
7.4.11 更新表的记录
需求:更新dept表里deptno为50的记录对应的loc字段为Nanjing
解决方法:这里通过UPDATE TableName SET ColName=.. WHERE …的方式来更新数据.
Oracle、Mysql、Sql server:
UPDATE deptSET 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',1000BEGIN TRANSELECT empno,ename,sal FROM emp WHERE empno=7369;UPDATE ASET A.sal= B.sal*1.5FROM emp AJOIN temp BON A.empno= B.empnoSELECT empno,ename,sal FROM emp WHERE empno=7369;ROLLBACK TRAN
执行更新前:
执行更新后:
注:
这里的join方式也可以写成如下的形式:
UPDATE ASET A.sal= B.sal*1.5FROM emp A,temp BWHERE A.empno= B.empno
Mysql:
TRUNCATE TABLE temp;INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)SELECT 50,'Production','Nanjing',7369,'SMITH',1000UPDATE emp AINNER JOIN fridge.temp BON A.empno= B.empnoSET A.sal= B.sal*1.5
或者:
UPDATE emp,tempSET emp.sal=temp.sal*1.5WHERE 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 aset sal=(select b.sal*1.5from temp b whereb.empno=a.empno)where exists (select1from temp bwhere 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 aset (sal,ename)=(select b.sal*1.5,b.enamefrom temp bwhereb.empno=a.empno)where exists (select1from temp bwhere b.empno=a.empno)SELECT empno,ename,sal FROM emp WHERE empno=7369;
执行更新前:
SELECT empno,ename,sal FROM emp WHERE empno=7369;
执行更新后:
Sql Server:
TRUNCATE TABLE temp;INSERT INTO temp(deptno,deptname,loc,empno,ename,sal)SELECT 50,'Production','Nanjing',7369,'SMITH2',1000BEGIN TRANSELECT empno,ename,sal FROM emp WHERE empno=7369;UPDATE empSET emp.sal=b.sal*1.5,emp.ename=b.ename from emp a,temp bWHERE a.empno= b.empnoUPDATE empSET emp.sal=b.sal*1.5,emp.ename=b.ename from emp aJOIN temp bON a.empno= b.empnoSELECT empno,ename,sal FROM emp WHERE empno=7369;ROLLBACK
执行更新前:
执行更新后:
Mysql:
BEGIN;SELECT empno,ename,sal FROM emp WHERE empno=7369;UPDATE emp a,temp bSET a.sal=b.sal*1.5 ,a.ename=b.enameWHERE a.empno= b.empno;UPDATE emp aINNER JOIN temp bON a.empno= b.empnoSET 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',1000BEGIN;SELECT empno,ename,sal,'From_emp' as Table_Name FROM emp WHERE empno=7369UNIONSELECT empno,ename,sal,'From_temp' as Table_Name FROM temp WHERE empno=7369;UPDATE emp a,temp bSET a.sal=b.sal*1.5 ,b.ename=a.enameWHERE a.empno= b.empno;UPDATE emp aINNERJOIN temp bON a.empno= b.empnoSET a.sal=b.sal*1.5 ,b.ename=a.ename;SELECT empno,ename,sal,'From_emp' as Table_Name FROM emp WHERE empno=7369UNIONSELECT empno,ename,sal,'From_temp' as Table_Name FROM temp WHERE empno=7369;ROLLBACK;
执行更新前:
执行更新后:
注:Oracle和SQL Server里暂未发现该语法.
7.4.15 按照默认值更新表
需求:更新deptno对应是50的dept表的loc字段成默认值.
解决方法:这里通过UPDATE TableName SET ColName=DEFAULT …的方式来更新数据.
SQL Server:
BEGIN TRANSELECT * FROM dept WHERE deptno= 50;UPDATE deptSET loc = DEFAULTWHERE deptno= 50;SELECT * FROM dept WHERE deptno= 50;ROLLBACK TRAN
MySQL:
BEGIN;SELECT * FROM dept WHERE deptno=40;UPDATE deptSET loc=defaultWHERE deptno=40;SELECT * FROM dept WHERE deptno=40;ROLLBACK;
Oracle:
SELECT * FROM dept WHERE deptno=40;UPDATE deptSET loc=defaultWHERE deptno=40;SELECT * FROM dept WHERE deptno=40;ROLLBACK;
执行更新前:
执行更新后:
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 TRANSELECT * FROM dept;DELETE FROM deptFROM dept AJOIN temp BON A.deptno= B.deptnoSELECT * FROM dept;ROLLBACK TRAN
Mysql:
BEGIN;SELECT * FROM dept A;-- JOIN temp B-- ON A.deptno = B.deptnoDELETE A FROM dept AINNER JOIN temp BON A.deptno= B.deptno AND B.deptno=40;--或者见下DELETE dept FROM dept,tempWHERE dept.deptno= temp.deptno AND temp.deptno=40;SELECT * FROM dept;ROLLBACK;
不难发现,这里dept表里deptno=40的记录被删除了.
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)
执行结果:
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=50UNION ALLSELECT deptno,deptname,loc,'tab_temp' as Table_Name FROM temp WHERE deptno=50;DELETE A,B FROM dept AINNER JOIN temp BON A.deptno= B.deptno AND B.deptno=50;SELECT deptno,dname,loc,'tab_dept' as Table_Name FROM dept WHERE deptno=50UNION ALLSELECT deptno,deptname,loc,'tab_temp' as Table_Name FROM temp WHERE deptno=50;ROLLBACK;
执行之前:
执行之后, temp和dept这两张表里deptno为50的记录均被删除:
注: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 ASSELECT deptno,empno,ename,comm FROM empWHERE empno IN (7782,7839,7934);SELECT et.empno,et.ename,et.deptno,m,emp.salFROM emp_temp etJOIN empON et.empno = emp.empno;MERGE INTO emp_tempetUSING(SELECTempno,ename,deptno,comm,SAL FROM emp) empON(et.empno = emp.empno)WHEN MATCHED THENUPDATE SET m=1000DELETE WHERE (SAL<2000)WHEN NOTMATCHED THENINSERT(et.empno,et.ename,et.deptno,m)VALUES(emp.empno,emp.ename,emp.deptno,m);SELECT et.empno,et.ename,et.deptno,m,emp.salFROM emp_tempetJOIN empON et.empno= emp.empno;
执行前:
执行后,emp_temp表里员工编号为7782和7839的comm被更新成了1000,而7934这条记录被删除了,其它情况的数据从emp表里插入了过来.
SqlServer:
CREATE TABLE emp_temp(deptno int NULL,empno int NOT NULL,ename varchar(15)NULL,comm decimal(7, 2)NULL)INSERT INTO emp_tempSELECT deptno,empno,ename,comm FROM empWHERE empno IN (7782,7839,7934);BEGIN TRANSELECT et.empno,et.ename,et.deptno,m,emp.salFROM emp_temp etJOIN empON et.empno= emp.empno;MERGE INTO emp_temp etUSING(SELECT empno,ename,deptno,comm,SAL FROM emp) empON (et.empno= emp.empno)WHEN MATCHEDAND SAL< 2000THENDELETE WHEN MATCHED THENUPDATE SET m = 1000WHEN NOTMATCHED THENINSERT VALUES(emp.deptno,emp.empno,emp.ename,m);SELECT et.empno,et.ename,et.deptno,m,emp.salFROM emp_temp etJOIN empON et.empno= emp.empno;ROLLBACK TRAN
执行前:
执行Merge into之后:
注:
1) 这里SQL Server和Oracle的mergeinto语法还是有差异的.SQL Server里如下代码:
WHEN MATCHEDAND SAL< 2000THEN
DELETE
要写在前面,如果和UPDATE互换了位置,则会报如下错:
消息 5324，级别 16，状态 1，第 11 行
在 MERGE 语句中，带搜索条件的 'WHEN MATCHED' 子句不能出现在不带搜索条件的 'WHENMATCHED' 子句后。
2) 目前Mysql并没有有merge into的语法.
如果觉得《SQL基础操作_4_表的插入 更新 删除 合并操作》对你有帮助，请点赞、收藏，并留下你的观点哦！
