Oracle数据库：约束行限制where语句，判断条件，比较条件，字符串日期格式，in,like,escape,null语句
找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开
测开的话，你就得学数据库，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语句，判断条件，比较条件，字符串日期格式，in,like,escape,null语句@[TOC](文章目录)oracle约束和排序数据oracle字符串和日期格式比较条件其他比较条件escape语句，转义，用任意字符转义，一般就是反斜杠\null判空总结
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 StevenKing SKING 515.123.4567 /6/17 AD_PRES24000.00 90101 NeenaKochhar NKOCHHAR 515.123.4568 /9/21 AD_VP 17000.00 100 90102 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 StevenKing SKING 515.123.4567 /6/17 AD_PRES24000.00 90101 NeenaKochhar NKOCHHAR 515.123.4568 /9/21 AD_VP 17000.00 100 90102 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_IDDEPARTMENT_ID------------------------- ---------- -------------King AD_PRES90Kochhar AD_VP 90De 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_REPKing AD_PRES
查的是jobid
条件是名字为king
区分大小写哦
查姓名，部门，iobid
查1月24日入职的条件
日月年
我这是中文版的月
直接写1月
SQL> select last_name,job_id,department_id from employees where hire_date='24-1月-06';LAST_NAME JOB_IDDEPARTMENT_ID------------------------- ---------- -------------Fox SA_REP80TaylorSH_CLERK 50
20世纪也能写上去
SQL> select last_name,job_id,department_id from employees where hire_date='24-1月-';LAST_NAME JOB_IDDEPARTMENT_ID------------------------- ---------- -------------Fox SA_REP80TaylorSH_CLERK 50
比较条件
SQL> select last_name,salary from employees where salary<=3000;LAST_NAME SALARY------------------------- ----------Baida 2900.00Tobias 2800.00Himuro 2600.00Colmenares 2500.00Mikkilineni 2700.00Landry 2400.00Markle 2200.00Atkinson 2800.00Marlow 2500.00Olson 2100.00Rogers 2900.00Gee2400.00Philtanker 2200.00Seo2700.00Patel 2500.00Matos 2600.00Vargas 2500.00Sullivan 2500.00Geoni 2800.00Cabrio 3000.00LAST_NAME SALARY------------------------- ----------Gates 2900.00Perkins 2500.00Jones 2800.00Feeney 3000.00OConnell 2600.00Grant 2600.0026 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.00Tobias 2800.00Himuro 2600.00Colmenares 2500.00Mikkilineni 2700.00Atkinson 2800.00Marlow 2500.00Rogers 2900.00Seo2700.00Patel 2500.00Matos 2600.00Vargas 2500.00Sullivan 2500.00Geoni 2800.00Cabrio 3000.00Gates 2900.00Perkins 2500.00Jones 2800.00Feeney 3000.00OConnell 2600.00LAST_NAME SALARY------------------------- ----------Grant 2600.0021 rows selected
会英语就好说，系统底层自动转
等价于
SQL> select last_name,salary from employees where salary>=2500 and salary<=3000;LAST_NAME SALARY------------------------- ----------Baida 2900.00Tobias 2800.00Himuro 2600.00Colmenares 2500.00Mikkilineni 2700.00Atkinson 2800.00Marlow 2500.00Rogers 2900.00Seo2700.00Patel 2500.00Matos 2600.00Vargas 2500.00Sullivan 2500.00Geoni 2800.00Cabrio 3000.00Gates 2900.00Perkins 2500.00Jones 2800.00Feeney 3000.00OConnell 2600.00LAST_NAME SALARY------------------------- ----------Grant 2600.0021 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_NAMEVARCHAR2(20) YFirst name of the employee. A not null column.LAST_NAMEVARCHAR2(25) Last name of the employee. A not null column. EMAILVARCHAR2(25) Email id of the employee PHONE_NUMBER VARCHAR2(20) YPhone number of the employee; includes country code and area code HIRE_DATEDATEDate 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 thejobs table. A not null column. SALARY NUMBER(8,2) YMonthly salary of the employee. Must be greaterthan zero (enforced by constraint emp_salary_min)COMMISSION_PCT NUMBER(2,2) YCommission percentage of the employee; Only employees in salesdepartment elgible for commission percentageMANAGER_IDNUMBER(6) YManager id of the employee; has same domain as manager_id indepartments table. Foreign key to employee_id column of employees table.(useful for reflexive joins and CONNECT BY query) DEPARTMENT_ID NUMBER(4) YDepartment id where employee works; foreign key to department_idcolumn 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.00De Haan 17000.00Raphaely11000.00Weiss 8000.00Fripp 8200.00Kaufling 7900.00Vollman 6500.00Mourgos 5800.00Russell 14000.00Partners13500.00Errazuriz 12000.00Cambrault 11000.00Zlotkey 10500.00Hartstein 13000.00Greenberg 1.00Whalen 4400.00Mavris 6500.00Baer 10000.00Higgins 1.00Fay6000.0020 rows selected
or太多
为了简化，搞成这样
SQL> select last_name,salary from employees where manager_id in(100,101,201);LAST_NAME SALARY------------------------- ----------Kochhar 17000.00De Haan 17000.00Raphaely11000.00Weiss 8000.00Fripp 8200.00Kaufling 7900.00Vollman 6500.00Mourgos 5800.00Russell 14000.00Partners13500.00Errazuriz 12000.00Cambrault 11000.00Zlotkey 10500.00Hartstein 13000.00Greenberg 1.00Whalen 4400.00Mavris 6500.00Baer 10000.00Higgins 1.00Fay6000.0020 rows selected
in(a,b,c……)
in只能做相等的或条件判断
数据结构与算法里面的题目
%当时是*
就是匹配0或者多个字符
而_是一个占位符，表示一个字符
SQL> select last_name from employees where last_name like 'S%';LAST_NAME-------------------------SarchandSciarraSeoSewallSmithSmithStilesSullivanSully9 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-------------------------BaerBaidaBandaBatesCabrioCambraultCambraultDaviesFavietFayGatesHallHartsteinKauflingLadwigLandryMallinMarkleMarlowMarvinsLAST_NAME-------------------------MatosMavrisNayerPartnersPataballaPatelRajsRaphaelySarchandTaylorTaylorVargasWalsh33 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 /9/21Austin/6/25Chen /9/28Sciarra /9/30Baida /12/24Tobias/7/24Fripp /4/10Vollman /10/10Nayer /7/16Bissot/8/20Atkinson /10/30Marlow/2/16Stiles/10/26Davies/1/29Partners /1/5Errazuriz /3/10Tucker/1/30Bernstein /3/24Hall /8/20Smith /3/10LAST_NAME HIRE_DATE------------------------- -----------Doran /12/15Vishney /11/11Ozer /3/11Hutton/3/19Bull /2/20Chung /6/14Dilly /8/13Everett /3/3Fay /8/1729 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_REPAnde SA_REPBanda SA_REPBates SA_REPBernstein SA_REPBloom SA_REPCambrault SA_MANCambrault SA_REPDoran SA_REPErrazuriz SA_MANFox SA_REPGrant SA_REPGreeneSA_REPHall SA_REPHuttonSA_REPJohnson SA_REPKing SA_REPKumar SA_REPLee SA_REPLivingstonSA_REPLAST_NAME JOB_ID------------------------- ----------Marvins SA_REPMcEwenSA_REPOlsen SA_REPOzer SA_REPPartners SA_MANRussell SA_MANSewallSA_REPSmith SA_REPSmith SA_REPSully SA_REPTaylorSA_REPTuckerSA_REPTuvault SA_REPVishney SA_REPZlotkey SA_MAN35 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_REPAnde SA_REPBanda SA_REPBates SA_REPBernstein SA_REPBloom SA_REPCambrault SA_MANCambrault SA_REPDoran SA_REPErrazuriz SA_MANFox SA_REPGrant SA_REPGreeneSA_REPHall SA_REPHuttonSA_REPJohnson SA_REPKing SA_REPKumar SA_REPLee SA_REPLivingstonSA_REPLAST_NAME JOB_ID------------------------- ----------Marvins SA_REPMcEwenSA_REPOlsen SA_REPOzer SA_REPPartners SA_MANRussell SA_MANSewallSA_REPSmith SA_REPSmith SA_REPSully SA_REPTaylorSA_REPTuckerSA_REPTuvault SA_REPVishney SA_REPZlotkey SA_MAN35 rows selected
null判空
null不是任何值
不能用=
SQL> select last_name,job_id,commission_pct from employees where commission_pct is null;LAST_NAME JOB_IDCOMMISSION_PCT------------------------- ---------- --------------King AD_PRES Kochhar AD_VPDe Haan AD_VPHunoldIT_PROG Ernst IT_PROG AustinIT_PROG Pataballa IT_PROG Lorentz IT_PROG Greenberg FI_MGRFavietFI_ACCOUNT Chen FI_ACCOUNT Sciarra FI_ACCOUNT Urman FI_ACCOUNT Popp FI_ACCOUNT Raphaely PU_MANKhoo PU_CLERK Baida PU_CLERK TobiasPU_CLERK HimuroPU_CLERK ColmenaresPU_CLERK LAST_NAME JOB_IDCOMMISSION_PCT------------------------- ---------- --------------Weiss ST_MANFripp ST_MANKaufling ST_MANVollman ST_MANMourgos ST_MANNayer ST_CLERK MikkilineniST_CLERK LandryST_CLERK MarkleST_CLERK BissotST_CLERK Atkinson ST_CLERK MarlowST_CLERK Olson ST_CLERK MallinST_CLERK RogersST_CLERK Gee ST_CLERK PhiltankerST_CLERK LadwigST_CLERK StilesST_CLERK Seo ST_CLERK Patel ST_CLERK LAST_NAME JOB_IDCOMMISSION_PCT------------------------- ---------- --------------Rajs ST_CLERK DaviesST_CLERK Matos ST_CLERK VargasST_CLERK TaylorSH_CLERK FleaurSH_CLERK Sullivan SH_CLERK Geoni SH_CLERK Sarchand SH_CLERK Bull SH_CLERK Dellinger SH_CLERK CabrioSH_CLERK Chung SH_CLERK Dilly SH_CLERK Gates SH_CLERK Perkins SH_CLERK Bell SH_CLERK Everett SH_CLERK McCainSH_CLERK Jones SH_CLERK Walsh SH_CLERK LAST_NAME JOB_IDCOMMISSION_PCT------------------------- ---------- --------------FeeneySH_CLERK OConnell SH_CLERK Grant SH_CLERK WhalenAD_ASST Hartstein MK_MANFay MK_REPMavrisHR_REPBaer PR_REPHiggins AC_MGRGietz 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_IDCOMMISSION_PCT------------------------- ---------- --------------Russell SA_MAN0.40Partners SA_MAN0.30Errazuriz SA_MAN0.30Cambrault SA_MAN0.30Zlotkey SA_MAN0.20TuckerSA_REP0.30Bernstein SA_REP0.25Hall SA_REP0.25Olsen SA_REP0.20Cambrault SA_REP0.20Tuvault SA_REP0.15King SA_REP0.35Sully SA_REP0.35McEwenSA_REP0.35Smith SA_REP0.30Doran SA_REP0.30SewallSA_REP0.25Vishney SA_REP0.25GreeneSA_REP0.15Marvins SA_REP0.10LAST_NAME JOB_IDCOMMISSION_PCT------------------------- ---------- --------------Lee SA_REP0.10Ande SA_REP0.10Banda SA_REP0.10Ozer SA_REP0.25Bloom SA_REP0.20Fox SA_REP0.20Smith SA_REP0.15Bates SA_REP0.15Kumar SA_REP0.10Abel SA_REP0.30HuttonSA_REP0.25TaylorSA_REP0.20LivingstonSA_REP0.20Grant SA_REP0.15Johnson SA_REP0.1035 rows selected
只有35人有佣金
这很好办
总结
提示：重要经验：
1）
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
如果觉得《Oracle数据库：约束行限制where语句 判断条件 比较条件 字符串日期格式 in like escape null语句》对你有帮助，请点赞、收藏，并留下你的观点哦！
