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.013.从表中删除数据
--删除指定的行用where语句缺少where语句删除所有行
--用子查询返回结果作为值进行删除记录
truncate table copy_emp;
4.一个事务处理是由一系列DML语句逻辑组成的
--数据库事务是由以下某一个产生
--DML语句
--DDL语句
--DCL语句
--执行第一个DML语句就是事务开始
--结束标志符有：
--commit，rollback
--DDL，DCL语句执行（自动结束）
--the system crashes
--隐式事务，
--事务自动提交
----DDL语句
----DCL语句
----正常退出
--事务结束之前数据状态
----数据可以恢复
----当前用户可以看到DML操作的结果
----其他用户不能看到当前操作结果
--事务结束数据库状态
----更改数据保存到数据库
----
