Oracle数据库：数据库操纵语言DML，插入insert into where，更新update where，删除delete where
2022找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开
测开的话，你就得学数据库，sql，oracle，尤其sql要学，当然，像很多金融企业、安全机构啥的，他们必须要用oracle数据库
这oracle比sql安全，强大多了，所以你需要学习，最重要的，你要是考网络警察公务员，这玩意你不会就别去报名了，耽误时间！
oracle系列文章：
【1】Oracle数据库：啥是oracle数据库？你为啥要学oracle？
【2】Oracle数据库：oracle 11g安装教程，已安装好的oracle各个文件夹的作用，oracle用户权限怎么样
【3】Oracle数据库：oracle启动，oracle客户端工具plsql安装教程和使用方法
【4】Oracle数据库：创建表空间，创建新用户，给用户分配对象、角色和系统权限，登录新用户建表
【5】Oracle数据库：链接配置，包括sqlnet.ora里面的transnames.ora配置数据库标识符SID，listener暂时简单了解
【6】Oracle数据库：net configureation assistant工具配置监听listener，配置本地网络访问服务器上的数据库
【7】Oracle数据库：oracle字符类型、数字类型、创建表表名的命名规则
【8】Oracle数据库：约束条件：主键约束、唯一约束、检查约束、非空约束、外键约束、默认值填写
【9】Oracle数据库：表的关系：一对多，一对一，多对多，一一继承和修改的关系，表不是重点，重点是数据和约束关系
【10】Oracle数据库：sql语言结构，数据查询语言DQL，select * from table；算术，别名，连接，去重等操作
【11】Oracle数据库：约束行限制where语句，判断条件，比较条件，字符串日期格式，in,like,escape,null语句
【12】Oracle数据库：逻辑运算and，or，not和各种运算的优先级控制
【13】Oracle数据库：排序order by语句，select from where order by的执行先后顺序，各种样例
【14】Oracle数据库：oracle函数，单行函数，多行函数，upper，lower，initcap，字符串函数
【15】Oracle数据库：数字函数，日期函数，round，trunc，mod，months_between,add_months,next_day,last_day,sysdate
【16】Oracle数据库：oracle数据类型转换to_char()日期和数字转字符,to_number()字符转数字,to_date()字符转日期函数
【17】Oracle数据库：oracle函数嵌套，nvl函数，nvl2函数，nullif函数，coalesce合并函数
【18】Oracle数据库：条件表达式case when then else end，decode函数，oracle单行函数练习示例
【19】Oracle数据库：oracle多表查询，等值连接，非等值连接，自连接的sql语句和规则
【20】Oracle数据库：oracle外连接left/right/full outer join on,oracle扩展的左右外连接展示符号(+)
【21】Oracle数据库：自然连接natural join，using语句，注意避免写交叉连接
【22】Oracle数据库：oracle内连接inner join on，多表查询各种自链接、内连接、外连接的练习示例
【23】Oracle数据库：oracle组函数，聚合函数，多行函数，avg，sum，min，max，count，group by，having
【24】Oracle数据库：oracle嵌套分组函数（聚合函数），组函数的练习题，挺复杂的，用好decode函数，很有趣
【25】Oracle数据库：子查询、单行子查询，多行子查询，in，any，all语句，子查询的练习案例
————前面这些都是数据库查询语言的重要知识，一定要牢牢掌握和熟悉
后面的相对简单一点
前面的1-25那几个文章，是SQL语言中的DQL【database query language】语言，复杂，但是用的也多
一定要好好学习
前面的的1-25那几个文章，是SQL语言中的DQL【database query language】语言，复杂，但是用的也多
一定要好好学习
前面的的1-25那几个文章，是SQL语言中的DQL【database query language】语言，复杂，但是用的也多
一定要好好学习
从今天开始，学剩下的几种语言
oracle操纵语言Date manipulate language【DML】
任何数据结构，诞生有几种骚操作
【1】查询语言query（）
【2】插入数据insert
【3】更新upgrade
【4】删除delete
比如我们经常搞的有序表，线段树等等，这些骚操作都是基本要写的东西
添加一行数据insert into value
如果写AB,但是CD有非null约束就不行哦
SQL> insert into departments(department_id,department_name,manager_id,location_id) values(280,'Teaching',180,2000);
1 row inserted
成功了
看看表呢
SQL> select * from departments;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
20 Marketing 201 1800
30 Purchasing 114 1700
40 Human Resources 203 2400
50 Shipping 121 1500
60 IT 103 1400
70 Public Relations 204 2700
80 Sales 145 2500
90 Executive 100 1700
100 Finance 108 1700
110 Accounting 205 1700
120 Treasury 1700
130 Corporate Tax 1700
140 Control And Credit 1700
150 Shareholder Services 1700
160 Benefits 1700
170 Manufacturing 1700
180 Construction 1700
190 Contracting 1700
200 Operations 1700
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
210 IT Support 1700
220 NOC 1700
230 IT Helpdesk 1700
240 Government Sales 1700
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
280 Teaching 180 2000
28 rows selected
最后这个数据就进去了
美滋滋
注意！！！
你需要点会话中的commit，将这个数据永久插入数据库，否则你退出界面就没有数据了
get？
否则它只能在tmp临时表中间
命令也行
commit;
【这个过程称为事务处理】
完全列，所有列添加
SQL> desc departments;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEPARTMENT_ID NUMBER(4) Primary key column of departments table.
DEPARTMENT_NAME VARCHAR2(30) A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting.
MANAGER_ID NUMBER(6) Y Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.
LOCATION_ID NUMBER(4) Y Location id where a department is located. Foreign key to location_id column of locations table.
SQL>
可以把columns去掉
顺序就是顺序
SQL> insert into departments values(280,'Teaching',180,2000);
insert into departments values(280,'Teaching',180,2000)
ORA-00001: 违反唯一约束条件 (HR.DEPT_ID_PK)
看见没，节约了代码
但是180不能再添加了
我们按照要求来
SQL> insert into departments values(290,'Development',149,2000);
1 row inserted
SQL> commit;
Commit complete
要commit提交事务
然后搞定添加完全列
SQL> select * from departments d where d.department_id=290;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
290 Development 149 2000
查到了
很简单吧
刺激
插入带null的行
SQL> desc departments;
Name Type Nullable Default Comments
--------------- ------------ -------- ------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DEPARTMENT_ID NUMBER(4) Primary key column of departments table.
DEPARTMENT_NAME VARCHAR2(30) A not null column that shows name of a department. Administration,
Marketing, Purchasing, Human Resources, Shipping, IT, Executive, Public
Relations, Sales, Finance, and Accounting.
MANAGER_ID NUMBER(6) Y Manager_id of a department. Foreign key to employee_id column of employees table. The manager_id column of the employee table references this column.
LOCATION_ID NUMBER(4) Y Location id where a department is located. Foreign key to location_id column of locations table.
34这俩列可以为null
隐式就不管了
SQL> insert into departments(department_id,department_name) values(292,'DXXX');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from departments d where d.department_id=292;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
292 DXXX
就省略了292的后面俩数据
显示方式就是直接加null
SQL> insert into departments values(293,'DXXX',null,null);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from departments d where d.department_id=293;
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
293 DXXX
没有就放null
反正都OK的
SQL> desc employees;
Name Type Nullable Default Comments
-------------- ------------ -------- ------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
EMPLOYEE_ID NUMBER(6) Primary key of employees table.
FIRST_NAME VARCHAR2(20) Y First name of the employee. A not null column.
LAST_NAME VARCHAR2(25) Last name of the employee. A not null column.
EMAIL VARCHAR2(25) Email id of the employee
PHONE_NUMBER VARCHAR2(20) Y Phone number of the employee; includes country code and area code
HIRE_DATE DATE Date when the employee started on this job. A not null column.
JOB_ID VARCHAR2(10) Current job of the employee; foreign key to job_id column of the
jobs table. A not null column.
SALARY NUMBER(8,2) Y Monthly salary of the employee. Must be greater
than zero (enforced by constraint emp_salary_min)
COMMISSION_PCT NUMBER(2,2) Y Commission percentage of the employee; Only employees in sales
department elgible for commission percentage
MANAGER_ID NUMBER(6) Y Manager id of the employee; has same domain as manager_id in
departments table. Foreign key to employee_id column of employees table.
(useful for reflexive joins and CONNECT BY query)
DEPARTMENT_ID NUMBER(4) Y Department id where employee works; foreign key to department_id
column of the departments table
前三列非空
后面的随意好像
SQL> insert into employees(employee_id,first_name,last_name,email,phone_number,hire_date,job_id,salary,commission_pct,manager_id,department_id) values(300,'Old','Lu','xx@163.com','123454321',sysdate,'IT_PROG',23233,null,204,290);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from employees where employee_id=300;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
300 Old Lu xx@163.com 123454321 2022/10/29 IT_PROG 23233.00 204 290
稳了
换一个方法
SQL> insert into employees values(301,'New','Lu','xx301@163.com','123454321',sysdate,'IT_PROG',23233,null,204,290);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from employees where employee_id=301;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
301 New Lu xx301@163.com 123454321 2022/10/29 IT_PROG 23233.00 204 290
日期不是标准格式，转化标准格式用to_date(日期，‘yyyy-mm-dd’)
这样就是标准格式了
好说
从另一个表中复制行【了解】
dba用户经常搞
但是日常程序员不需要搞这些事
了解
我们可以先创建一个骚表
SQL> create table emp(id number not null,name varchar(50),salary number(8,2),commission number(2,2));
Table created
格式就是列名 类型，其他列 类型
类型要一致，我们才能copy到别的表格里面
现在我们往里面放数据
SQL> select * from emp;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
我们用employees表的数据，复制放入这个emp
子查询
SQL> select * from employees e where e.job_id like '%REP%';
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
150 Peter Tucker PTUCKER 011.44.1344.129268 2005/1/30 SA_REP 10000.00 0.30 145 80
151 David Bernstein DBERNSTE 011.44.1344.345268 2005/3/24 SA_REP 9500.00 0.25 145 80
152 Peter Hall PHALL 011.44.1344.478968 2005/8/20 SA_REP 9000.00 0.25 145 80
153 Christopher Olsen COLSEN 011.44.1344.498718 2006/3/30 SA_REP 8000.00 0.20 145 80
154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 2006/12/9 SA_REP 7500.00 0.20 145 80
155 Oliver Tuvault OTUVAULT 011.44.1344.486508 2007/11/23 SA_REP 7000.00 0.15 145 80
156 Janette King JKING 011.44.1345.429268 2004/1/30 SA_REP 10000.00 0.35 146 80
157 Patrick Sully PSULLY 011.44.1345.929268 2004/3/4 SA_REP 9500.00 0.35 146 80
158 Allan McEwen AMCEWEN 011.44.1345.829268 2004/8/1 SA_REP 9000.00 0.35 146 80
159 Lindsey Smith LSMITH 011.44.1345.729268 2005/3/10 SA_REP 8000.00 0.30 146 80
160 Louise Doran LDORAN 011.44.1345.629268 2005/12/15 SA_REP 7500.00 0.30 146 80
161 Sarath Sewall SSEWALL 011.44.1345.529268 2006/11/3 SA_REP 7000.00 0.25 146 80
162 Clara Vishney CVISHNEY 011.44.1346.129268 2005/11/11 SA_REP 10500.00 0.25 147 80
163 Danielle Greene DGREENE 011.44.1346.229268 2007/3/19 SA_REP 9500.00 0.15 147 80
164 Mattea Marvins MMARVINS 011.44.1346.329268 2008/1/24 SA_REP 7200.00 0.10 147 80
165 David Lee DLEE 011.44.1346.529268 2008/2/23 SA_REP 6800.00 0.10 147 80
166 Sundar Ande SANDE 011.44.1346.629268 2008/3/24 SA_REP 6400.00 0.10 147 80
167 Amit Banda ABANDA 011.44.1346.729268 2008/4/21 SA_REP 6200.00 0.10 147 80
168 Lisa Ozer LOZER 011.44.1343.929268 2005/3/11 SA_REP 11500.00 0.25 148 80
169 Harrison Bloom HBLOOM 011.44.1343.829268 2006/3/23 SA_REP 10000.00 0.20 148 80
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
170 Tayler Fox TFOX 011.44.1343.729268 2006/1/24 SA_REP 9600.00 0.20 148 80
171 William Smith WSMITH 011.44.1343.629268 2007/2/23 SA_REP 7400.00 0.15 148 80
172 Elizabeth Bates EBATES 011.44.1343.529268 2007/3/24 SA_REP 7300.00 0.15 148 80
173 Sundita Kumar SKUMAR 011.44.1343.329268 2008/4/21 SA_REP 6100.00 0.10 148 80
174 Ellen Abel EABEL 011.44.1644.429267 2004/5/11 SA_REP 11000.00 0.30 149 80
175 Alyssa Hutton AHUTTON 011.44.1644.429266 2005/3/19 SA_REP 8800.00 0.25 149 80
176 Jonathon Taylor JTAYLOR 011.44.1644.429265 2006/3/24 SA_REP 8600.00 0.20 149 80
177 Jack Livingston JLIVINGS 011.44.1644.429264 2006/4/23 SA_REP 8400.00 0.20 149 80
178 Kimberely Grant KGRANT 011.44.1644.429263 2007/5/24 SA_REP 7000.00 0.15 149
179 Charles Johnson CJOHNSON 011.44.1644.429262 2008/1/4 SA_REP 6200.00 0.10 149 80
202 Pat Fay PFAY 603.123.6666 2005/8/17 MK_REP 6000.00 201 20
203 Susan Mavris SMAVRIS 515.123.7777 2002/6/7 HR_REP 6500.00 101 40
204 Hermann Baer HBAER 515.123.8888 2002/6/7 PR_REP 10000.00 101 70
33 rows selected
插
但是你values不要了
直接跟子查询
子查询的列，要对应emp的列哦
SQL> insert into emp(id,name,salary,commission) select e.employee_id,e.last_name,e.salary,e.commission_pct from employees e where e.job_id like '%REP%'
2 ;
33 rows inserted
SQL> select * from emp;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
150 Tucker 10000.00 0.30
151 Bernstein 9500.00 0.25
152 Hall 9000.00 0.25
153 Olsen 8000.00 0.20
154 Cambrault 7500.00 0.20
155 Tuvault 7000.00 0.15
156 King 10000.00 0.35
157 Sully 9500.00 0.35
158 McEwen 9000.00 0.35
159 Smith 8000.00 0.30
160 Doran 7500.00 0.30
161 Sewall 7000.00 0.25
162 Vishney 10500.00 0.25
163 Greene 9500.00 0.15
164 Marvins 7200.00 0.10
165 Lee 6800.00 0.10
166 Ande 6400.00 0.10
167 Banda 6200.00 0.10
168 Ozer 11500.00 0.25
169 Bloom 10000.00 0.20
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
170 Fox 9600.00 0.20
171 Smith 7400.00 0.15
172 Bates 7300.00 0.15
173 Kumar 6100.00 0.10
174 Abel 11000.00 0.30
175 Hutton 8800.00 0.25
176 Taylor 8600.00 0.20
177 Livingston 8400.00 0.20
178 Grant 7000.00 0.15
179 Johnson 6200.00 0.10
202 Fay 6000.00
203 Mavris 6500.00
204 Baer 10000.00
33 rows selected
SQL> commit;
Commit complete
另外一种方法
完全列插入，不要columns
SQL> insert into emp select e.employee_id,e.last_name,e.salary,e.commission_pct from employees e where e.job_id like '%REP%';
33 rows inserted
SQL> select * from emp;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
150 Tucker 10000.00 0.30
151 Bernstein 9500.00 0.25
152 Hall 9000.00 0.25
66 rows selected
OK了
好说的这事
插入表中使用默认值
有默认就是默认
没有就是null
default
好说
再插入数据时，不指定薪水
那么就是默认的1000
SQL> insert into emp(id,name,salary,commission) values(300,'Oldlu',default,null);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from emp where id=300;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
300 Oldlu 1000.00
OK吧
SQL> insert into emp(id,name,commission) values(301,'Oldlu',null);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from emp where id=301;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
301 Oldlu 1000.00
或者
SQL> insert into emp(id,name) values(302,'Oldlu');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from emp where id=302;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
302 Oldlu 1000.00
com那个不加，默认null
反正好说
oracle会自动添加默认值
更新表格中的数据update set where
最好有where，控制某一些行的列
id为170
SQL> select * from emp where id = 170;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
170 Fox 9600.00 0.20
170 Fox 9600.00 0.20
SQL>
更新一波
SQL> update emp e set e.name='EEE',e.salary=2000 where id = 170;
2 rows updated
SQL> commit;
Commit complete
SQL> select * from emp where id = 170;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
170 EEE 2000.00 0.20
170 EEE 2000.00 0.20
SQL>
哦了，一定要加where，否则就是整个表的所有人的列修改更新了
很容易吧这
也可以用子查询
SQL> update emp e set e.name='EEE',e.salary=(select distinct em.salary from emp em where em.id=156) where id = 165;
2 rows updated
SQL> commit;
Commit complete
SQL> select * from emp where id = 165;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
165 EEE 10000.00 0.10
165 EEE 10000.00 0.10
当行操作符=
返回多了就不行哦
这就是更新操作里面的子查询，好说
删除表格中的行 delete where
SQL> select * from emp where id=302;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
302 Oldlu 1000.00
SQL> delete emp where id = 302;
1 row deleted
SQL> select * from emp where id=302;
ID NAME SALARY COMMISSION
---------- -------------------------------------------------- ---------- ----------
过于简单了
这些语句都比DQL简单多了
这就是DML
好说
俩表
SQL> select * from employees where job_id='IT_PROG';
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
103 Alexander Hunold AHUNOLD 590.423.4567 2006/1/3 IT_PROG 9000.00 102 60
104 Bruce Ernst BERNST 590.423.4568 2007/5/21 IT_PROG 6000.00 103 60
105 David Austin DAUSTIN 590.423.4569 2005/6/25 IT_PROG 4800.00 103 60
106 Valli Pataballa VPATABAL 590.423.4560 2006/2/5 IT_PROG 4800.00 103 60
107 Diana Lorentz DLORENTZ 590.423.5567 2007/2/7 IT_PROG 4200.00 103 60
300 Old Lu xx@163.com 123454321 2022/10/29 IT_PROG 23233.00 204 290
301 New Lu xx301@163.com 123454321 2022/10/29 IT_PROG 23233.00 204 290
7 rows selected
7种薪水
这个子查询返回多个结果
那就是in
去除重复也行
SQL> delete emp where salary in(select salary from employees where job_id='IT_PROG');
6 rows deleted
in就把所有满足条件的东西都干废了
没有where是很可怕的
……
最好别搞这事
delete emp;
我就写一下，不运行
这个直接给表名里面的行【不是删除表哈】
drop emp;
是删除整个表格
区别一下哦
有外键约束，是不能乱删除的
这就是外键
不要乱用这个命令，否则导致公司gg
甚至可能会坐牢的
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
扫一扫
693
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
