DML 数据表操作

INSERT 向表中插入数据

insert into 表名(列名1, 列名2, ...) values(值1, 值2, ...);

普通数据
SQL> insert into departments(department_id, department_name, manager_id, location_id) values(280, 'Teaching', 180, 2000);

插入null数据
SQL> insert into departments(department_id, department_name, Manager_Id, location_id) values(302, 'A', NULL, NULL);

插入日期为系统当前日期(sysdate)
SQL> insert into employees(employee_id, last_name, first_name, email, hire_date, job_id) values(301, 'tom','ding','301@com.com',sysdate,'IT_PROG');

插入日期为系统默认格式
SQL> insert into employees(employee_id, last_name, first_name, email, hire_date, job_id) values(302, 'tom','ding','302@com.com','25-1月-2019','IT_PROG');

插入日期为经过格式函数转换后的日期
SQL> insert into employees(employee_id, last_name, first_name, email, hire_date, job_id) values(303, 'tom','ding','303@com.com',to_date('2019-12-13','yyyy-mm-dd'),'IT_PROG');

使用查询结果，向另一个表中批量复制数据，指定列名
SQL> insert into emp(ID, Name, salary, comissions) select e.employee_id, e.last_name, e.salary, e.commission_pct from employees e where job_id='IT_PROG';

使用查询结果，向另一个表中批量复制数据，不指定列名
SQL> insert into emp select e.employee_id, e.last_name, e.salary, e.commission_pct from employees e where job_id='IT_PROG';

使用列中默认值(default)插入到数据表
SQL> insert into emp(id, name, salary) values(301, 'lixi', default);

UPDATE 修改数据

update 表名 set 列名1=值1, 列名2=值2, ... where 过滤条件;
如果没有过滤条件，将修改整个表中的数据。

SQL> update emp set id=302, name='wangsan' where name is null;
1 row updated

通过查询修改数据
SQL> update emp set salary=(select salary from employees where employee_id='180') where id=302;
1 row updated

DELETE 删除数据

delete [from] 表名 where 过滤条件;

删除指定数据
SQL> delete emp where id < 200;

根据查询结果删除数据
SQL> delete emp where salary in (select salary from employees where job_id='IT_PROG');

整表删除
SQL> delete from emp;

SQL> delete emp;