Oracle数据库：约束行限制where语句，判断条件，比较条件，字符串日期格式，in,like,escape,null语句
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；算术，别名，连接，去重等操作
oracle约束和排序数据
行选择
where放在from table之后，限制行
之前from之前的是限制列
整一个例子就知道了
好说1=1条件满足，全部执行
SQL> select * from employees where 1=1;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 2003/6/17 AD_PRES 24000.00 90
101 Neena Kochhar NKOCHHAR 515.123.4568 2005/9/21 AD_VP 17000.00 100 90
102 Lex De Haan LDEHAAN 515.123.4569 2001/1/13 AD_VP 17000.00 100 90
比较对象就是常量、列名，或者别的列表
限定那些部门id是90的行，其他行不要
SQL> select * from employees where department_id = 90;
EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
----------- -------------------- ------------------------- ------------------------- -------------------- ----------- ---------- ---------- -------------- ---------- -------------
100 Steven King SKING 515.123.4567 2003/6/17 AD_PRES 24000.00 90
101 Neena Kochhar NKOCHHAR 515.123.4568 2005/9/21 AD_VP 17000.00 100 90
102 Lex De Haan LDEHAAN 515.123.4569 2001/1/13 AD_VP 17000.00 100 90
你瞅瞅，这里面部门id是90的也就三行
这就是限制行的的例子，本质是用列名满足某些条件来控制
SQL> select last_name,job_id,department_id from employees where department_id = 90;
LAST_NAME JOB_ID DEPARTMENT_ID
------------------------- ---------- -------------
King AD_PRES 90
Kochhar AD_VP 90
De Haan AD_VP 90
只要某些列，就限制在from前面
只要某些航，就限制在from后面
美滋滋
oracle字符串和日期格式
SQL> select last_name,job_id from employees where last_name='King';
LAST_NAME JOB_ID
------------------------- ----------
King SA_REP
King AD_PRES
查的是jobid
条件是名字为king
区分大小写哦
查姓名，部门，iobid
查2006年1月24日入职的条件
日月年
我这是中文版的月
直接写1月
SQL> select last_name,job_id,department_id from employees where hire_date='24-1月-06';
LAST_NAME JOB_ID DEPARTMENT_ID
------------------------- ---------- -------------
Fox SA_REP 80
Taylor SH_CLERK 50
20世纪也能写上去
SQL> select last_name,job_id,department_id from employees where hire_date='24-1月-2006';
LAST_NAME JOB_ID DEPARTMENT_ID
------------------------- ---------- -------------
Fox SA_REP 80
Taylor SH_CLERK 50
比较条件
SQL> select last_name,salary from employees where salary<=3000;
LAST_NAME SALARY
------------------------- ----------
Baida 2900.00
Tobias 2800.00
Himuro 2600.00
Colmenares 2500.00
Mikkilineni 2700.00
Landry 2400.00
Markle 2200.00
Atkinson 2800.00
Marlow 2500.00
Olson 2100.00
Rogers 2900.00
Gee 2400.00
Philtanker 2200.00
Seo 2700.00
Patel 2500.00
Matos 2600.00
Vargas 2500.00
Sullivan 2500.00
Geoni 2800.00
Cabrio 3000.00
LAST_NAME SALARY
------------------------- ----------
Gates 2900.00
Perkins 2500.00
Jones 2800.00
Feeney 3000.00
OConnell 2600.00
Grant 2600.00
26 rows selected
这很好说
其他比较条件
[a,b]闭区间关系
in匹配任意值
like模板，模糊通配查询
null是否为null
SQL> select last_name,salary from employees where salary between 2500 and 3000;
LAST_NAME SALARY
------------------------- ----------
Baida 2900.00
Tobias 2800.00
Himuro 2600.00
Colmenares 2500.00
Mikkilineni 2700.00
Atkinson 2800.00
Marlow 2500.00
Rogers 2900.00
Seo 2700.00
Patel 2500.00
Matos 2600.00
Vargas 2500.00
Sullivan 2500.00
Geoni 2800.00
Cabrio 3000.00
Gates 2900.00
Perkins 2500.00
Jones 2800.00
Feeney 3000.00
OConnell 2600.00
LAST_NAME SALARY
------------------------- ----------
Grant 2600.00
21 rows selected
会英语就好说，系统底层自动转
等价于
SQL> select last_name,salary from employees where salary>=2500 and salary<=3000;
LAST_NAME SALARY
------------------------- ----------
Baida 2900.00
Tobias 2800.00
Himuro 2600.00
Colmenares 2500.00
Mikkilineni 2700.00
Atkinson 2800.00
Marlow 2500.00
Rogers 2900.00
Seo 2700.00
Patel 2500.00
Matos 2600.00
Vargas 2500.00
Sullivan 2500.00
Geoni 2800.00
Cabrio 3000.00
Gates 2900.00
Perkins 2500.00
Jones 2800.00
Feeney 3000.00
OConnell 2600.00
LAST_NAME SALARY
------------------------- ----------
Grant 2600.00
21 rows selected
666
看看x是否在set中
or条件
只能做相等的判断
多条件转化为或关系
上面是and关系
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> select last_name,salary from employees where manager_id=100 or manager_id=101 or manager_id=201;
LAST_NAME SALARY
------------------------- ----------
Kochhar 17000.00
De Haan 17000.00
Raphaely 11000.00
Weiss 8000.00
Fripp 8200.00
Kaufling 7900.00
Vollman 6500.00
Mourgos 5800.00
Russell 14000.00
Partners 13500.00
Errazuriz 12000.00
Cambrault 11000.00
Zlotkey 10500.00
Hartstein 13000.00
Greenberg 12008.00
Whalen 4400.00
Mavris 6500.00
Baer 10000.00
Higgins 12008.00
Fay 6000.00
20 rows selected
or太多
为了简化，搞成这样
SQL> select last_name,salary from employees where manager_id in(100,101,201);
LAST_NAME SALARY
------------------------- ----------
Kochhar 17000.00
De Haan 17000.00
Raphaely 11000.00
Weiss 8000.00
Fripp 8200.00
Kaufling 7900.00
Vollman 6500.00
Mourgos 5800.00
Russell 14000.00
Partners 13500.00
Errazuriz 12000.00
Cambrault 11000.00
Zlotkey 10500.00
Hartstein 13000.00
Greenberg 12008.00
Whalen 4400.00
Mavris 6500.00
Baer 10000.00
Higgins 12008.00
Fay 6000.00
20 rows selected
in(a,b,c……)
in只能做相等的或条件判断
数据结构与算法里面的题目
%当时是*
就是匹配0或者多个字符
而_是一个占位符，表示一个字符
SQL> select last_name from employees where last_name like 'S%';
LAST_NAME
-------------------------
Sarchand
Sciarra
Seo
Sewall
Smith
Smith
Stiles
Sullivan
Sully
9 rows selected
当时我们学了a*代表有0个a或者多个a
同理
这里S%就是代表0个S或者多个S匹配
S后面是啥不重要
_怎么用
查：第二个字母为a的名字
那第一个字符是啥不重要，可以一个_占位
后面%通配啥都行
SQL> select last_name from employees where last_name like '_a%';
LAST_NAME
-------------------------
Baer
Baida
Banda
Bates
Cabrio
Cambrault
Cambrault
Davies
Faviet
Fay
Gates
Hall
Hartstein
Kaufling
Ladwig
Landry
Mallin
Markle
Marlow
Marvins
LAST_NAME
-------------------------
Matos
Mavris
Nayer
Partners
Pataballa
Patel
Rajs
Raphaely
Sarchand
Taylor
Taylor
Vargas
Walsh
33 rows selected
我自己玩一个
比如里面含有字母x的
其他前后字符随意
SQL> select last_name from employees where last_name like '%x%';
LAST_NAME
-------------------------
Fox
%就是代表0个或者多个字符，然后跟x，然后后面随意0个或者多个字符
like可以做模型的日期查询
默认的格式太狠
20世纪不要了，就是05年，前面是啥日月都行的
SQL> select last_name,hire_date from employees where hire_date like '%05';
LAST_NAME HIRE_DATE
------------------------- -----------
Kochhar 2005/9/21
Austin 2005/6/25
Chen 2005/9/28
Sciarra 2005/9/30
Baida 2005/12/24
Tobias 2005/7/24
Fripp 2005/4/10
Vollman 2005/10/10
Nayer 2005/7/16
Bissot 2005/8/20
Atkinson 2005/10/30
Marlow 2005/2/16
Stiles 2005/10/26
Davies 2005/1/29
Partners 2005/1/5
Errazuriz 2005/3/10
Tucker 2005/1/30
Bernstein 2005/3/24
Hall 2005/8/20
Smith 2005/3/10
LAST_NAME HIRE_DATE
------------------------- -----------
Doran 2005/12/15
Vishney 2005/11/11
Ozer 2005/3/11
Hutton 2005/3/19
Bull 2005/2/20
Chung 2005/6/14
Dilly 2005/8/13
Everett 2005/3/3
Fay 2005/8/17
29 rows selected
escape语句，转义，用任意字符转义，一般就是反斜杠\
如果你就要搜%和_呢
那就要转义处理
你要不写\，那_就是占位符
SQL> select last_name,job_id from employees where last_name like 'Sa_%';
LAST_NAME JOB_ID
------------------------- ----------
Sarchand SH_CLERK
后面加escape ‘’
这样就是告诉你反斜杠是转义字符
SQL> select last_name,job_id from employees where job_id like 'SA\_%' escape '\';
LAST_NAME JOB_ID
------------------------- ----------
Abel SA_REP
Ande SA_REP
Banda SA_REP
Bates SA_REP
Bernstein SA_REP
Bloom SA_REP
Cambrault SA_MAN
Cambrault SA_REP
Doran SA_REP
Errazuriz SA_MAN
Fox SA_REP
Grant SA_REP
Greene SA_REP
Hall SA_REP
Hutton SA_REP
Johnson SA_REP
King SA_REP
Kumar SA_REP
Lee SA_REP
Livingston SA_REP
LAST_NAME JOB_ID
------------------------- ----------
Marvins SA_REP
McEwen SA_REP
Olsen SA_REP
Ozer SA_REP
Partners SA_MAN
Russell SA_MAN
Sewall SA_REP
Smith SA_REP
Smith SA_REP
Sully SA_REP
Taylor SA_REP
Tucker SA_REP
Tuvault SA_REP
Vishney SA_REP
Zlotkey SA_MAN
35 rows selected
jobid，不要搞成名字了哦
你看看我写的命令
这样的话，就可以把反斜杠转义了
这java和Python也是这么搞的
任何编程语言都有这个东西
你换别的符号也行@#都可以，随你——考试要注意这里
SQL> select last_name,job_id from employees where job_id like 'SA@_%' escape '@';
LAST_NAME JOB_ID
------------------------- ----------
Abel SA_REP
Ande SA_REP
Banda SA_REP
Bates SA_REP
Bernstein SA_REP
Bloom SA_REP
Cambrault SA_MAN
Cambrault SA_REP
Doran SA_REP
Errazuriz SA_MAN
Fox SA_REP
Grant SA_REP
Greene SA_REP
Hall SA_REP
Hutton SA_REP
Johnson SA_REP
King SA_REP
Kumar SA_REP
Lee SA_REP
Livingston SA_REP
LAST_NAME JOB_ID
------------------------- ----------
Marvins SA_REP
McEwen SA_REP
Olsen SA_REP
Ozer SA_REP
Partners SA_MAN
Russell SA_MAN
Sewall SA_REP
Smith SA_REP
Smith SA_REP
Sully SA_REP
Taylor SA_REP
Tucker SA_REP
Tuvault SA_REP
Vishney SA_REP
Zlotkey SA_MAN
35 rows selected
null判空
null不是任何值
不能用=
SQL> select last_name,job_id,commission_pct from employees where commission_pct is null;
LAST_NAME JOB_ID COMMISSION_PCT
------------------------- ---------- --------------
King AD_PRES
Kochhar AD_VP
De Haan AD_VP
Hunold IT_PROG
Ernst IT_PROG
Austin IT_PROG
Pataballa IT_PROG
Lorentz IT_PROG
Greenberg FI_MGR
Faviet FI_ACCOUNT
Chen FI_ACCOUNT
Sciarra FI_ACCOUNT
Urman FI_ACCOUNT
Popp FI_ACCOUNT
Raphaely PU_MAN
Khoo PU_CLERK
Baida PU_CLERK
Tobias PU_CLERK
Himuro PU_CLERK
Colmenares PU_CLERK
LAST_NAME JOB_ID COMMISSION_PCT
------------------------- ---------- --------------
Weiss ST_MAN
Fripp ST_MAN
Kaufling ST_MAN
Vollman ST_MAN
Mourgos ST_MAN
Nayer ST_CLERK
Mikkilineni ST_CLERK
Landry ST_CLERK
Markle ST_CLERK
Bissot ST_CLERK
Atkinson ST_CLERK
Marlow ST_CLERK
Olson ST_CLERK
Mallin ST_CLERK
Rogers ST_CLERK
Gee ST_CLERK
Philtanker ST_CLERK
Ladwig ST_CLERK
Stiles ST_CLERK
Seo ST_CLERK
Patel ST_CLERK
LAST_NAME JOB_ID COMMISSION_PCT
------------------------- ---------- --------------
Rajs ST_CLERK
Davies ST_CLERK
Matos ST_CLERK
Vargas ST_CLERK
Taylor SH_CLERK
Fleaur SH_CLERK
Sullivan SH_CLERK
Geoni SH_CLERK
Sarchand SH_CLERK
Bull SH_CLERK
Dellinger SH_CLERK
Cabrio SH_CLERK
Chung SH_CLERK
Dilly SH_CLERK
Gates SH_CLERK
Perkins SH_CLERK
Bell SH_CLERK
Everett SH_CLERK
McCain SH_CLERK
Jones SH_CLERK
Walsh SH_CLERK
LAST_NAME JOB_ID COMMISSION_PCT
------------------------- ---------- --------------
Feeney SH_CLERK
OConnell SH_CLERK
Grant SH_CLERK
Whalen AD_ASST
Hartstein MK_MAN
Fay MK_REP
Mavris HR_REP
Baer PR_REP
Higgins AC_MGR
Gietz AC_ACCOUNT
72 rows selected
71个人没有佣金
有佣金的就是反过来查
SQL> select last_name,job_id,commission_pct from employees where commission_pct is not null;
LAST_NAME JOB_ID COMMISSION_PCT
------------------------- ---------- --------------
Russell SA_MAN 0.40
Partners SA_MAN 0.30
Errazuriz SA_MAN 0.30
Cambrault SA_MAN 0.30
Zlotkey SA_MAN 0.20
Tucker SA_REP 0.30
Bernstein SA_REP 0.25
Hall SA_REP 0.25
Olsen SA_REP 0.20
Cambrault SA_REP 0.20
Tuvault SA_REP 0.15
King SA_REP 0.35
Sully SA_REP 0.35
McEwen SA_REP 0.35
Smith SA_REP 0.30
Doran SA_REP 0.30
Sewall SA_REP 0.25
Vishney SA_REP 0.25
Greene SA_REP 0.15
Marvins SA_REP 0.10
LAST_NAME JOB_ID COMMISSION_PCT
------------------------- ---------- --------------
Lee SA_REP 0.10
Ande SA_REP 0.10
Banda SA_REP 0.10
Ozer SA_REP 0.25
Bloom SA_REP 0.20
Fox SA_REP 0.20
Smith SA_REP 0.15
Bates SA_REP 0.15
Kumar SA_REP 0.10
Abel SA_REP 0.30
Hutton SA_REP 0.25
Taylor SA_REP 0.20
Livingston SA_REP 0.20
Grant SA_REP 0.15
Johnson SA_REP 0.10
35 rows selected
只有35人有佣金
这很好办
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
685
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
