数据库语法总结（4）——增删改查

文章继续更新，感兴趣的小伙伴可以选择关注哦

一、插入数据

1、插入新纪录

根据字段插入数据

insert into T_EMP(EMPID,EMPNAME,EMPAGE)
 values (6,'susu','18');

多行插入数据

insert into T_EMP(EMPID,EMPNAME,EMPAGE)
 values (6,'susu','18'),
        (7,'tiantian','20')

如果是插入表中所有字段，可以省略字段

insert into T_EMP
 values (6,'susu','18'),
        (7,'tiantian','20')

2、插入默认值

我们在定义表时，可以指定某列为默认值

CREATE TABLE T_DEMP(ID INTEGER DEFAULT 0)

 关于表列的增删改查在这提一下

ALTER TABLE T_DEMP ADD (DENAME VARCHAR(100) NULL) 
--(列名，数据类型，是否为空)
--primary key(ID) 设置主键

--1.修改列名
alter table 表名 rename column 旧列名 to 新列名;
--实例
alter table xsb rename column xh to 学号;
 
--2.修改列类型
alter table 表名 modify (列名 varchar(256));
--实例
alter table xsb modify (学号 varchar(256));
 
--3.删除表的一列
alter table 表名 drop column 列名;
--实例
alter table xsb drop column bz;
 
--4.给表增加一列
alter table 表名  add 列名   类型   default 值  null;
--实例
alter table xsb add bz varchar(256) default null;

此处转载于ORACLE修改列名与列类型_Luo_xguan的博客-CSDN博客

言归正传，插入默认值的方法用到关键字default

--大多数的写法
INSERT INTO T_DEMP VALUES (default)
--或者
INSERT INTO T_DEMP (ID) VALUES (default)
--Mysql中如果所有列都有默认值，可以这样
INSERT INTO T_DEMP values()
--PostgreSQL、Sqlserver支持default values子句
INSERT INTO T_DEMP DEFAULT VALUES 

如果还有其他列没有设置默认值，可以在插入数据的时候直接省略插入该字段，数据库会默认插入

3、用null覆盖默认值

在上面创建的T_DEMP表中ID赋予了默认值，但是当我们不想在ID字段插入值时，可以插入null

INSERT INTO T_DEMP (ID) VALUES (null)

4、将一张表中的行复制到另一张表中

我们在插入数据的时候往往想要插入一些其他表中的数据，我们只需要找到对应的字段进行如下操作即可

INSERT INTO T_DEMP (ID,DENAME,DENO)
SELECT EMPID,EMPNAME,EMPAGE FROM T_EMP 
WHERE EMPID=1;

5、复制已存在表的结构

新建一张表只复制T_DEMP的结构，不复制T_DEMP的数据

DB2使用like

create table  T_DEMP_2 like T_DEMP;

Oracle、Mysql、PostgreSQL可以创建一个不返回任何行的数据

create table T_DEMP_2 as select * from T_DEMP where 1=0;

Sqlserver在一个不返回任何行的子查询中使用into子句

select * into T_DEMP_2 from T_DEMP where 1=0;

6、同时将一表数据插入多张表中

现在我想将T_EMP数据同时插入到T_DEMP,T_DEMP_2,T_DEMP_3中有什么方法呢

目前在Mysql、PostgreSQL、Sqlserver这写数据库中暂时没有直接的关键字或函数可以做到，目前提供思路：可以采用事务、存储过程、触发器等方式完成

Oracle中使用insert all或insert first借助when-then-else子句完成，二者语法效果相同，区别在于first在遇到满足的条件之后就结束when-then-else检查；而all会检查所有条件，即便T_EMP数据行重复也会插入，所以可以使用all将相同行插入多张表中。

INSERT ALL
WHEN EMPID IN (1,2) THEN 
 INTO T_DEMP (ID,DENAME,DENO) VALUES (EMPID,EMPNAME,EMPAGE)
WHEN EMPID=3 THEN
 INTO T_DEMP_2 (ID,DENAME,DENO) VALUES (EMPID,EMPNAME,EMPAGE)
ELSE 
  INTO T_DEMP_3 (ID,DENAME,DENO) VALUES (EMPID,EMPNAME,EMPAGE)
 SELECT EMPID,EMPNAME,EMPAGE FROM T_EMP ;

DB2使用union all合并所有的目标表，用来创建出一个内嵌视图，还必须给目标表用check设置约束条件

CREATE TABLE T_DEMP(ID INTEGER,DENAME VARCHAR(100),DENO VARCHAR(100) 
 CHECK (EMPID in (1,2)))
CREATE TABLE T_DEMP_2(ID INTEGER,DENAME VARCHAR(100),DENO VARCHAR(100) 
 CHECK (EMPID=3))
CREATE TABLE T_DEMP_3(ID INTEGER,DENAME VARCHAR(100),DENO VARCHAR(100) 
 CHECK (EMPID=4))

insert into (
 select * from T_DEMP union all 
 select * from T_DEMP_2 union all
 select * from T_DEMP_3 
) select * from T_EMP;

此处的插入有些繁琐，如果约束条件不是唯一的将会造成插入错误

7、禁止在特定列中插入值

例如：T_DEMP表中ID,DENAME,DENO中只允许插入ID,DENAME两个字段DENO不允许插入

我们可以通过创建视图来解决

create view V_DEMP AS
select ID,DENAME from T_DEMP;

这样插入或修改视图本质上就是插入/修改视图下原本的表

insert into V_DEMP (ID,DENAME)  values (7,'meimei')
--实质上相当于转换成表的修改
--insert into T_DEMP (ID,DENAME)  values (7,'meimei')

Oracle中也可以直接插入内嵌视图

insert into (select ID,DENAME from T_DEMP)  values (7,'meimei')

但是关于View视图的操作需要慎重，简单的单表视图或许可以操作，复杂联查构成的视图除了查询最好不要进行增删改操作，而Mysql中简单的视图也不可以操作。

二、修改数据

1、修改表中的记录

说到更新其实很常见啦，基本结构是update table set 修改字段=修改值 where 条件

UPDATE T_DEMP SET DENAME ='susu' WHERE ID =1;

2、两表数据匹配时才可以更新

例如，我们想要更新T_DEMP表ID字段中存在于T_EMP的EMPID数据，如果存在，不修改

两种方式：谓词in；exists进行子查询

UPDATE T_DEMP SET DENAME ='susu' 
 WHERE ID IN (SELECT EMPID FROM T_EMP);

UPDATE T_DEMP SET DENAME ='susu' 
 WHERE EXISTS (SELECT NULL  FROM T_EMP WHERE T_DEMP.ID=T_EMP.EMPID);

在exists子查询中select null的null不会给更新带来影响，反而提高了可读性，因为真正决定更新条件的是子查询中where的关联子句T_DEMP.ID=T_EMP.EMPID，而不是子查询中select的返回值。

3、使用关联表中一张表的数据更新到另一张表中

还是上面说的两张表，上面说的set值是固定的，但是现在我想把T_EMP的EMPNAME，EMPAGE内容赋值给T_DEMP的DENAME，DENO两列。

Mysql中相对简单

UPDATE T_DEMP d,T_EMP e SET d.DENAME =e.EMPNAME,d.DENO =e.EMPAGE 
 WHERE d.ID =e.EMPID ;

DB2需要使用关联子查询来确定更新T_EMP表中哪些行，同时这个方法也适用于Oracle、PostgreSQL、Sqlserver，但是这些数据库还有其他的解决方法。

注意=后面的语句只是找到对应值，限制条件还是要用where+子查询进行限制

UPDATE T_DEMP d SET (d.DENAME,d.DENO)=
(SELECT e.EMPNAME,e.EMPAGE  FROM T_EMP e WHERE d.ID=e.EMPID)
--到这只是找到了对应的值，还需要确定哪些值需要修改，这就用到了where 
WHERE EXISTS (SELECT * FROM T_EMP e WHERE d.ID=e.EMPID)

 Oracle可以使用更新内嵌视图的方法，但是此方法必须要确保关联条件d.ID ，e.EMPID中的一个字段在自己的源表中是唯一值（也就是键保留[key-preservation]，确保有主键），这样才能保证更新成功

UPDATE (
 SELECT d.DENAME AS DENAME,d.DENO AS DENO ,e.EMPNAME AS EMPNAME, e.EMPAGE AS EMPAGE 
 FROM  T_DEMP d,T_EMP e WHERE  d.ID =e.EMPID )
SET DENAME=EMPNAME,DENO=EMPAGE;

PostgreSQL可以在update中直接连接（更为简单方便）

UPDATE T_DEMP d SET d.DENAME =e.EMPNAME,d.DENO =e.EMPAGE 
 FROM T_EMP e WHERE d.ID =e.EMPID; 

Sqlserver的方法与  PostgreSQL用法类似，使用起来类似于查询语句，只不过把select换成update 表名 set ,逗号换成等号（大家按照自己的方法记忆，不要被我误导哈）

UPDATE d set d.DENAME =e.EMPNAME,d.DENO =e.EMPAGE 
 FROM T_DEMP d,T_EMP e WHERE d.ID =e.EMPID;

4、合并记录

还是上述的两张表，为了方便大家知道我在说什么，现在展示一下表中的内容(内容纯属虚构哈)

                                                                            现在我想对 T_DEMP进行多步骤操作，使其与T_EMP内容相同，当  ID相同时更新字段DENAME的值，当ID相同并且DENO字段等于10时删除这一行，最后将 T_EMP中EMPID=3，4的数据插入 

这时我们用到了merge into语句进行操作(此语句不适用于Mysql，Mysql可以借助存储过程实现)     

1.merge into的作用是对表进行更改或插入操作。基本用法如下，先确认两表连接
A表为基础表，B表为关联表
MERGE INTO [target-table] A USING [source-table sql] B ON([conditional expression] and [...]...)
2.满足条件时执行语句
WHEN MATCHED THEN
3.执行语句,更新基础表可以省略表名
[UPDATE sql]
4.当不满足条件时
WHEN NOT MATCHED THEN
5.执行语句，插入基础表可以省略表名
[INSERT sql]

 而要实现我们想要的效果，执行语句如下：     

MERGE INTO T_DEMP d 
 USING (SELECT * FROM T_EMP ) e ON (d.ID =e.EMPID) --注意ON后面加（）
 WHEN MATCHED THEN 
      UPDATE SET d.DENAME =e.EMPNAME 
      DELETE WHERE d.DENO =10
 WHEN NOT MATCHED  THEN 
      INSERT (d.ID,d.DENAME,d.DENO)
      VALUES (e.EMPID,e.EMPNAME,e.EMPAGE)

                                                                                                                                                               

三、删除数据                                                                                                                  

1、删除表中所有记录

两种方式：delete from不加where条件；truncate table

区别是：truncate执行效率高，但是部分数据库不支持回滚

delete from  tablename;
truncate table tablename;

另外多提一下，删除表是drop

drop table tablename;

 

2、删除特定记录

即在delete from的基础上加where条件

delete from tablename where ID=1;

如果想要删除单条记录，则需要确定where后条件的唯一性(或用主键或用多个条件限制)。

3、删除非关联性的行

还是在T_EMP和T_DEMP两表中，我们需要删除T_DEMP中有，但是T_EMP没有的数据，有两种方法：not exists借用关联子查询，not in查询。

DELETE FROM T_DEMP d WHERE NOT EXISTS (SELECT * FROM T_EMP e WHERE e.EMPID=d.ID)

DELETE FROM T_DEMP d WHERE d.ID NOT IN (SELECT EMPID FROM T_EMP)
--额外提一下，下面的方式只适用于两表中有外键关联关系
DELETE FROM (SELECT 1 FROM T_DEMP d INNER JOIN T_EMP e ON e.EMPID=d.ID)

4、删除重复记录

  当T_DEMP表中DENAME字段有很多重复值，我们想要保留任意一条，可以利用子查询完成，此处以保留ID中最小的为例：

delete from T_DEMP where ID not in 
(select min(ID) from T_DEMP group by DENAME);

Mysql中delete不允许引用同一张表两次，所以语法上有一些小小的不同

delete from T_DEMP where ID not in 
(select min(ID) FROM (SELECT ID,DENAME FROM T_DEMP ) tmp group by DENAME);

5、删除在另一张表中引用了的记录

还是上面的表T_EMP，T_DEMP，T_EMP所有数据重复值出现超过2次的数据，我就要对应在T_DEMP表中删除

delete from T_DEMP
 where ID in
 (SELECT EMPID FROM T_EMP GROUP BY EMPID HAVING count(*)>=2);

注意：delete和update的操作执行频率要低于select，而且删除和更新涉及到更新撤销的操作（事务回滚），我们在更新或删除前最好先查询数据，确保执行的准确性！

我这边找了一篇关于更新回滚的操作，有兴趣大家可以看一下update误操作，如何数据回滚（MaydayMayday）_sql执行错了怎么回滚_且听深林细雨的博客-CSDN博客

今天的文章就先到这里了，文章会持续更新，请大家关注哦！