18.数据操纵语言

1.向表中插入数据

--values列表中的值的顺序与insert语句列表中顺序一致或与表中顺序一致
--在insert语句列出列是可选的
--字符和日期需要单引号括起来
hr@ORCLPDB01 2023-02-25 18:23:05> r
  1  insert into departments(department_id,department_name,manager_id,location_id)
  2* values(300,'Public Relations',100,1700)

1 row created.

Elapsed: 00:00:00.00

--隐式：在insert列表忽略列
hr@ORCLPDB01 2023-02-25 18:25:06> insert into departments(department_id,department_name)
  2  values(301,'PPublic Relations');

1 row created.

Elapsed: 00:00:00.00

--显示：在values中指定null或''
hr@ORCLPDB01 2023-02-25 18:27:33> insert into departments values (302,'Finance',null,'');

1 row created.

Elapsed: 00:00:00.01
hr@ORCLPDB01 2023-02-25 18:27:47> commit;

Commit complete.

Elapsed: 00:00:00.00

--插入特定的值
hr@ORCLPDB01 2023-02-25 18:35:23> insert into employees （EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (305,'louis','Popp1','LPOPP1','515,124,4567',SYSDATE,'AC_ACCOUNT',6900,nULL,205,110);

1 row created.

Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-25 18:35:33> select * from employees where EMPLOYEE_ID = 305
  2  ;

EMPLOYEE_ID FIRST_NAME		 LAST_NAME		   EMAIL		     PHONE_NUMBER	  HIRE_DATE	      JOB_ID	     SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------
	305 louis		 Popp1			   LPOPP1		     515,124,4567	  2023-02-25 18:35:33 AC_ACCOUNT       6900			  205		110

Elapsed: 00:00:00.00

--时期插入法
hr@ORCLPDB01 2023-02-25 18:38:18> insert into employees （EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) values (306,'louis','Popp1','LPOPP2','515,124,4567',to_date('FEB 3, 2000','MON DD,YYYY'),'AC_ACCOUNT',6900,nULL,205,110);

1 row created.

Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-25 18:40:18> select * from employees where EMPLOYEE_ID = 306;

EMPLOYEE_ID FIRST_NAME		 LAST_NAME		   EMAIL		     PHONE_NUMBER	  HIRE_DATE	      JOB_ID	     SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------
	306 louis		 Popp1			   LPOPP2		     515,124,4567	  2000-02-03 00:00:00 AC_ACCOUNT       6900	      205		110

Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-25 18:40:47> select * from employees where EMPLOYEE_ID = 306;

EMPLOYEE_ID FIRST_NAME		 LAST_NAME		   EMAIL		     PHONE_NUMBER	  HIRE_DATE	      JOB_ID	     SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ------------------- ---------- ---------- -------------- ---------- -------------
	306 louis		 Popp1			   LPOPP2		     515,124,4567	  2000-02-03 00:00:00 AC_ACCOUNT       6900			  205		110

Elapsed: 00:00:00.00

--使用&符号提示用户输入变量值
hr@ORCLPDB01 2023-02-25 18:46:48> insert into departments (department_id,department_name,location_id)
  2  values (&department_id,'&department_name','&location_id');
Enter value for department_id: 303
Enter value for department_name: Human
Enter value for location_id: 1700
old   2: values (&department_id,'&department_name','&location_id')
new   2: values (303,'Human','1700')

1 row created.

Elapsed: 00:00:00.00
hr@ORCLPDB01 2023-02-25 18:47:28> 

-- insert列表中的列个数，类型，顺序与子查询匹配
--子查询返回的所有行被插入到指定表中

2.更新现有数据

--1.修改存在的数据用update
--2.根据需要可更新一条或多条记录
----修改指定行用where，缺少where，更新所有行
hr@ORCLPDB01 2023-02-25 18:54:08> l
  1  update employees
  2  set job_id = (select job_id
  3		   from employees
  4		   where employee_id = 205),
  5	 salary = (select salary
  6		   from employees
  7		   where employee_id = 205)
  8* where employee_id = 113
hr@ORCLPDB01 2023-02-25 18:54:10> r
  1  update employees
  2  set job_id = (select job_id
  3		   from employees
  4		   where employee_id = 205),
  5	 salary = (select salary
  6		   from employees
  7		   where employee_id = 205)
  8* where employee_id = 113

1 row updated.

Elapsed: 00:00:00.03
hr@ORCLPDB01 2023-02-25 18:54:12> select job_id,salary from employees where employee_id in (113,205);

JOB_ID	       SALARY
---------- ----------
AC_MGR		12008
AC_MGR		12008

Elapsed: 00:00:00.01

--udate子查询语句更新的行记录来自于另外一个表
hr@ORCLPDB01 2023-02-25 18:54:10> r
  1  update copy_emp
  2  set job_id = (select job_id
  3		   from employees
  4		   where employee_id = 205),
  5	 salary = (select salary
  6		   from employees
  7		   where employee_id = 205)
  8* where employee_id = 113

1 row updated.

Elapsed: 00:00:00.03
hr@ORCLPDB01 2023-02-25 18:54:12> select job_id,salary from employees where employee_id in (113,205);

JOB_ID	       SALARY
---------- ----------
AC_MGR		12008
AC_MGR		12008

Elapsed: 00:00:00.01

3.从表中删除数据

--删除指定的行用where语句缺少where语句删除所有行
--用子查询返回结果作为值进行删除记录

truncate table copy_emp;

4.一个事务处理是由一系列DML语句逻辑组成的

--数据库事务是由以下某一个产生
--DML语句
--DDL语句
--DCL语句

--执行第一个DML语句就是事务开始
--结束标志符有：
--commit，rollback
--DDL，DCL语句执行（自动结束）
--the system crashes


--隐式事务，
--事务自动提交
----DDL语句
----DCL语句
----正常退出

--事务结束之前数据状态
----数据可以恢复
----当前用户可以看到DML操作的结果
----其他用户不能看到当前操作结果


--事务结束数据库状态
----更改数据保存到数据库
----