Oracle数据库：排序order by语句 select from where order by的执行先后顺序各种样例

Oracle数据库：排序order by语句，select from where order by的执行先后顺序，各种样例

找工作是学历、能力和运气的超强结合体，遇到寒冬，大厂不招人，可能很多算法学生都得去找开发，测开

测开的话，你就得学数据库，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和各种运算的优先级控制

文章目录

Oracle数据库：排序order by语句，select from where order by的执行先后顺序，各种样例@[TOC](文章目录)oracle排序order byselect语句的执行顺序约束和排序的练习总结

oracle排序order by

跟查询条件没关系

跟选择操作没关系

必须跟在select语句的最后【总是，就是order by一定放在整个语句的最后就行了】

order by后面要指定要排序的列【你看看你要排序哪个列】

asc升序

desc降序

SQL> select last_name from employees order by last_name;LAST_NAME-------------------------AbelAndeAtkinsonAustinBaerBaidaBandaBatesBellBernsteinBissotBloomBullCabrioCambraultCambraultChenChungColmenaresDaviesLAST_NAME-------------------------De HaanDellingerDillyDoranErnstErrazurizEverettFavietFayFeeneyFleaurFoxFrippGatesGeeGeoniGietzGrantGrantGreenbergGreeneLAST_NAME-------------------------HallHartsteinHigginsHimuroHunoldHuttonJohnsonJonesKauflingKhooKingKingKochharKumarLadwigLandryLeeLivingstonLorentzMallinMarkleLAST_NAME-------------------------MarlowMarvinsMatosMavrisMcCainMcEwenMikkilineniMourgosNayerOConnellOlsenOlsonOzerPartnersPataballaPatelPerkinsPhiltankerPoppRajsRaphaelyLAST_NAME-------------------------RogersRussellSarchandSciarraSeoSewallSmithSmithStilesSullivanSullyTaylorTaylorTobiasTuckerTuvaultUrmanVargasVishneyVollmanWalshLAST_NAME-------------------------WeissWhalenZlotkey107 rows selected

你看看默认的就是升序了

排序可以用那个你没有展示的列**，比如按照薪水升序，你可以不展示薪水**

SQL> select last_name from employees order by salary;LAST_NAME-------------------------OlsonMarklePhiltanker

这样的话，名字自然不见得是abcd这样升序了

你看看展示薪水是啥状况

SQL> select last_name,salary from employees order by salary;LAST_NAME SALARY------------------------- ----------Olson 2100.00Markle 2200.00Philtanker 2200.00

你瞅瞅，他工资最低，就这么玩

你要降序的话

屁股加desc

SQL> select last_name,salary from employees order by salary desc;LAST_NAME SALARY------------------------- ----------King 24000.00Kochhar 17000.00

SQL> select last_name,hire_date,salary,commission_pct from employees order by hire_date;LAST_NAME HIRE_DATE SALARY COMMISSION_PCT------------------------- ----------- ---------- --------------De Haan 2001/1/1317000.00 Gietz 2002/6/7 8300.00 Baer 2002/6/710000.00 Mavris2002/6/7 6500.00 Higgins 2002/6/71.00

日期最早的2001年入职

salary我上面展示了

SQL> select last_name,hire_date,salary,commission_pct from employees order by commission_pct;LAST_NAME HIRE_DATE SALARY COMMISSION_PCT------------------------- ----------- ---------- --------------Lee /2/236800.00 0.10Johnson /1/4 6200.00 0.10Marvins /1/247200.00 0.10Banda /4/216200.00 0.10Kumar /4/216100.00 0.10Ande /3/246400.00 0.10Greene/3/199500.00 0.15Grant /5/247000.00 0.15Tuvault /11/237000.00 0.15Bates /3/247300.00 0.15Smith /2/237400.00 0.15Taylor/3/248600.00 0.20Bloom /3/2310000.00 0.20Fox /1/249600.00 0.20Cambrault /12/97500.00 0.20Livingston/4/238400.00 0.20Zlotkey /1/2910500.00 0.20Olsen /3/308000.00 0.20Sewall/11/37000.00 0.25Hall /8/209000.00 0.25LAST_NAME HIRE_DATE SALARY COMMISSION_PCT------------------------- ----------- ---------- --------------Bernstein /3/249500.00 0.25Vishney /11/11 10500.00 0.25Hutton/3/198800.00 0.25Ozer /3/1111500.00 0.25Abel /5/1111000.00 0.30Smith /3/108000.00 0.30Partners /1/513500.00 0.30Errazuriz /3/1012000.00 0.30Tucker/1/3010000.00 0.30Cambrault /10/15 11000.00 0.30Doran /12/157500.00 0.30King /1/3010000.00 0.35Sully /3/4 9500.00 0.35McEwen/8/1 9000.00 0.35Russell /10/114000.00 0.40King /6/1724000.00 Kochhar /9/2117000.00 De Haan 2001/1/1317000.00 Hunold/1/3 9000.00 Ernst /5/216000.00 Austin/6/254800.00 LAST_NAME HIRE_DATE SALARY COMMISSION_PCT

null佣金排在最后的

降序的话，就反过来展示，不掩饰了

1 2 3 4 5列号

不管

为了简单简化，你起别名，也可以用别名，就像变量一样

SQL> select last_name,hire_date,salary,commission_pct as com from employees order by com;LAST_NAME HIRE_DATE SALARY COM------------------------- ----------- ---------- ----Lee /2/236800.00 0.10Johnson /1/4 6200.00 0.10Marvins /1/247200.00 0.10Banda /4/216200.00 0.10Kumar /4/216100.00 0.10Ande /3/246400.00 0.10Greene/3/199500.00 0.15Grant /5/247000.00 0.15Tuvault /11/237000.00 0.15Bates /3/247300.00 0.15Smith /2/237400.00 0.15Taylor/3/248600.00 0.20Bloom /3/2310000.00 0.20Fox /1/249600.00 0.20Cambrault /12/97500.00 0.20Livingston/4/238400.00 0.20Zlotkey /1/2910500.00 0.20Olsen /3/308000.00 0.20Sewall/11/37000.00 0.25Hall /8/209000.00 0.25LAST_NAME HIRE_DATE SALARY COM------------------------- ----------- ---------- ----Bernstein /3/249500.00 0.25Vishney /11/11 10500.00 0.25Hutton/3/198800.00 0.25Ozer /3/1111500.00 0.25Abel /5/1111000.00 0.30Smith /3/108000.00 0.30Partners /1/513500.00 0.30Errazuriz /3/1012000.00 0.30Tucker/1/3010000.00 0.30Cambrault /10/15 11000.00 0.30Doran /12/157500.00 0.30King /1/3010000.00 0.35Sully /3/4 9500.00 0.35McEwen/8/1 9000.00 0.35Russell /10/114000.00 0.40King /6/1724000.00 Kochhar /9/2117000.00 De Haan 2001/1/1317000.00 Hunold/1/3 9000.00 Ernst /5/216000.00 Austin/6/254800.00 LAST_NAME HIRE_DATE SALARY COM

com新名字，好说

多个排序同时执行

各自升降

SQL> select last_name,hire_date,salary,commission_pct from employees order by hire_date,salary;LAST_NAME HIRE_DATE SALARY COMMISSION_PCT------------------------- ----------- ---------- --------------De Haan 2001/1/1317000.00 Mavris2002/6/7 6500.00 Gietz 2002/6/7 8300.00 Baer 2002/6/710000.00 Higgins 2002/6/71.00 Faviet2002/8/169000.00 Greenberg 2002/8/171.00 Raphaely 2002/12/711000.00

你看02年的薪资又是升序的

你看看降序薪资如何

SQL> select last_name,hire_date,salary,commission_pct from employees order by hire_date,salary desc;LAST_NAME HIRE_DATE SALARY COMMISSION_PCT------------------------- ----------- ---------- --------------De Haan 2001/1/1317000.00 Higgins 2002/6/71.00 Baer 2002/6/710000.00 Gietz 2002/6/7 8300.00 Mavris2002/6/7 6500.00 Faviet2002/8/169000.00 Greenberg 2002/8/171.00 Raphaely 2002/12/711000.00

反正列在前面，先看前面的列排序，然后再看后面的事

select语句的执行顺序

先查表，然后看看你要哪些行，再看看你要哪些列，再看看你需要对列们做排序操作吗

这是考试可能会考的哦

约束和排序的练习

表是employees表

条件是收入超过12000

需要哪些列：名字，薪水

SQL> select last_name,salary from employees where salary>12000;LAST_NAME SALARY------------------------- ----------King 24000.00Kochhar 17000.00De Haan 17000.00Greenberg 1.00Russell 14000.00Partners13500.00Hartstein 13000.00Higgins 1.008 rows selected

SQL> select last_name,department_id from employees where employee_id=176;LAST_NAME DEPARTMENT_ID------------------------- -------------Taylor 80

SQL> select last_name,salary from employees where salary not between 5000 and 12000;LAST_NAME SALARY------------------------- ----------King 24000.00Kochhar 17000.00De Haan 17000.00Austin 4800.00Pataballa4800.00Lorentz 4200.00Greenberg 1.00Khoo3100.00Baida 2900.00Tobias 2800.00Himuro 2600.00Colmenares 2500.00Nayer 3200.00Mikkilineni 2700.00Landry 2400.00Markle 2200.00Bissot 3300.00Atkinson 2800.00Marlow 2500.00Olson 2100.00LAST_NAME SALARY------------------------- ----------Mallin 3300.00Rogers 2900.00Gee2400.00Philtanker 2200.00Ladwig 3600.00Stiles 3200.00Seo2700.00Patel 2500.00Rajs3500.00Davies 3100.00Matos 2600.00Vargas 2500.00Russell 14000.00Partners13500.00Taylor 3200.00Fleaur 3100.00Sullivan 2500.00Geoni 2800.00Sarchand 4200.00Bull4100.00Dellinger3400.00LAST_NAME SALARY------------------------- ----------Cabrio 3000.00Chung 3800.00Dilly 3600.00Gates 2900.00Perkins 2500.00Bell4000.00Everett 3900.00McCain 3200.00Jones 2800.00Walsh 3100.00Feeney 3000.00OConnell 2600.00Grant 2600.00Whalen 4400.00Hartstein 13000.00Higgins 1.0057 rows selected

希望网警特招考试就靠这些东西，这样我们就会实操

日月年，要搞清楚格式

SQL> select last_name,job_id,hire_date from employees where hire_date between '22-2月-02' and '01-5月-07' order by hire_date;LAST_NAME JOB_IDHIRE_DATE------------------------- ---------- -----------Higgins AC_MGR2002/6/7Gietz AC_ACCOUNT 2002/6/7MavrisHR_REP2002/6/7Baer PR_REP2002/6/7FavietFI_ACCOUNT 2002/8/16Greenberg FI_MGR2002/8/17Raphaely PU_MAN2002/12/7Kaufling ST_MAN/5/1Khoo PU_CLERK /5/18King AD_PRES /6/17LadwigST_CLERK /7/14WhalenAD_ASST /9/17Rajs ST_CLERK /10/17Sarchand SH_CLERK /1/27King SA_REP/1/30Bell SH_CLERK /2/4Hartstein MK_MAN/2/17Sully SA_REP/3/4Abel SA_REP/5/11MallinST_CLERK /6/14LAST_NAME JOB_IDHIRE_DATE------------------------- ---------- -----------Weiss ST_MAN/7/18McEwenSA_REP/8/1Russell SA_MAN/10/1Partners SA_MAN/1/5DaviesST_CLERK /1/29TuckerSA_REP/1/30MarlowST_CLERK /2/16Bull SH_CLERK /2/20Everett SH_CLERK /3/3Errazuriz SA_MAN/3/10Smith SA_REP/3/10Ozer SA_REP/3/11HuttonSA_REP/3/19Bernstein SA_REP/3/24Fripp ST_MAN/4/10Chung SH_CLERK /6/14AustinIT_PROG /6/25Nayer ST_CLERK /7/16TobiasPU_CLERK /7/24Dilly SH_CLERK /8/13Fay MK_REP/8/17LAST_NAME JOB_IDHIRE_DATE------------------------- ---------- -----------Hall SA_REP/8/20BissotST_CLERK /8/20Kochhar AD_VP/9/21Chen FI_ACCOUNT /9/28Sciarra FI_ACCOUNT /9/30Vollman ST_MAN/10/10StilesST_CLERK /10/26Atkinson ST_CLERK /10/30Vishney SA_REP/11/11Doran SA_REP/12/15Baida PU_CLERK /12/24HunoldIT_PROG /1/3Fox SA_REP/1/24TaylorSH_CLERK /1/24Pataballa IT_PROG /2/5Seo ST_CLERK /2/12FleaurSH_CLERK /2/23Urman FI_ACCOUNT /3/7Matos ST_CLERK /3/15Bloom SA_REP/3/23TaylorSA_REP/3/24LAST_NAME JOB_IDHIRE_DATE------------------------- ---------- -----------Olsen SA_REP/3/30Patel ST_CLERK /4/6LivingstonSA_REP/4/23Walsh SH_CLERK /4/24FeeneySH_CLERK /5/23Dellinger SH_CLERK /6/24McCainSH_CLERK /7/1VargasST_CLERK /7/9Gates SH_CLERK /7/11RogersST_CLERK /8/26MikkilineniST_CLERK /9/28SewallSA_REP/11/3HimuroPU_CLERK /11/15Cambrault SA_REP/12/9LandryST_CLERK /1/14Lorentz IT_PROG /2/7CabrioSH_CLERK /2/7Smith SA_REP/2/23Jones SH_CLERK /3/17GreeneSA_REP/3/19Bates SA_REP/3/24LAST_NAME JOB_IDHIRE_DATE------------------------- ---------- -----------Olson ST_CLERK /4/1084 rows selected

SQL> select last_name,department_id from employees where department_id in(20,50) order by last_name;LAST_NAME DEPARTMENT_ID------------------------- -------------Atkinson 50Bell 50Bissot 50Bull 50Cabrio 50Chung 50Davies 50Dellinger 50Dilly 50Everett50Fay 20Feeney 50Fleaur 50Fripp 50Gates 50Gee 50Geoni 50Grant 50Hartstein 20Jones 50LAST_NAME DEPARTMENT_ID------------------------- -------------Kaufling 50Ladwig 50Landry 50Mallin 50Markle 50Marlow 50Matos 50McCain 50Mikkilineni50Mourgos50Nayer 50OConnell 50Olson 50Patel 50Perkins50Philtanker 50Rajs 50Rogers 50Sarchand 50Seo 50Stiles 50LAST_NAME DEPARTMENT_ID------------------------- -------------Sullivan 50Taylor 50Vargas 50Vollman50Walsh 50Weiss 5047 rows selected

取别名了要“”

SQL> select last_name "Employee",salary "Monthly salary",department_id from employees where department_id in(20,50) and salary between 5000 and 12000;Employee Monthly salary DEPARTMENT_ID------------------------- -------------- -------------Weiss 8000.00 50Fripp 8200.00 50Kaufling7900.00 50Vollman6500.00 50Mourgos5800.00 50Fay6000.00 206 rows selected

SQL> select last_name,hire_date from employees where hire_date like '%04';LAST_NAME HIRE_DATE------------------------- -----------Weiss /7/18Mallin/6/14Russell /10/1King /1/30Sully /3/4McEwen/8/1Abel /5/11Sarchand /1/27Bell /2/4Hartstein /2/1710 rows selected

like模板匹配

04年前面啥月日随意

SQL> select last_name,job_id from employees where manager_id is null;LAST_NAME JOB_ID------------------------- ----------King AD_PRES

SQL> select last_name,salary,commission_pct com from employees where commission_pct is not null order by com desc,salary desc;LAST_NAME SALARY COM------------------------- ---------- ----Russell 14000.00 0.40King 10000.00 0.35Sully 9500.00 0.35McEwen 9000.00 0.35Partners13500.00 0.30Errazuriz 12000.00 0.30Cambrault 11000.00 0.30Abel 11000.00 0.30Tucker 10000.00 0.30Smith 8000.00 0.30Doran 7500.00 0.30Ozer 11500.00 0.25Vishney 10500.00 0.25Bernstein9500.00 0.25Hall9000.00 0.25Hutton 8800.00 0.25Sewall 7000.00 0.25Zlotkey 10500.00 0.20Bloom 10000.00 0.20Fox9600.00 0.20LAST_NAME SALARY COM------------------------- ---------- ----Taylor 8600.00 0.20Livingston 8400.00 0.20Olsen 8000.00 0.20Cambrault7500.00 0.20Greene 9500.00 0.15Smith 7400.00 0.15Bates 7300.00 0.15Tuvault 7000.00 0.15Grant 7000.00 0.15Marvins 7200.00 0.10Lee6800.00 0.10Ande6400.00 0.10Johnson 6200.00 0.10Banda 6200.00 0.10Kumar 6100.00 0.1035 rows selected

你注意，列条件，用com别名来指定判断条件是不行

必须用原列名

排序可以用别名，懂吧

因为执行顺序是先执行from表，控制原名字，筛选出列，再执行选择列，最后排序的

所以你后执行选择列，然后重命名，是不能用别名先执行控制条件的

牛

SQL> select last_name,salary,commission_pct com from employees where com is not null order by com desc,salary desc;select last_name,salary,commission_pct com from employees where com is not null order by com desc,salary descORA-00904: "COM": 标识符无效

这个如果考到了我可以解决

要熟悉

占位符__a%后面随意

SQL> select last_name from employees where last_name like '__a%';LAST_NAME-------------------------GrantGrantWhalen

2个条件且

SQL> select last_name from employees where last_name like '%a%' and last_name like '%e%';LAST_NAME-------------------------BaerBatesColmenaresDaviesDe HaanFavietFleaurGatesHartsteinMarkleNayerPartnersPatelPhiltankerRaphaelySewallWhalen17 rows selected

至于ae在哪无所谓

SQL> select last_name,job_id,salary from employees where job_id in('SA_REP','ST_CLERK') and salary not in(2500,3500,7000);LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Nayer ST_CLERK3200.00MikkilineniST_CLERK2700.00LandryST_CLERK2400.00MarkleST_CLERK2200.00BissotST_CLERK3300.00Atkinson ST_CLERK2800.00Olson ST_CLERK2100.00MallinST_CLERK3300.00RogersST_CLERK2900.00Gee ST_CLERK2400.00PhiltankerST_CLERK2200.00LadwigST_CLERK3600.00StilesST_CLERK3200.00Seo ST_CLERK2700.00DaviesST_CLERK3100.00Matos ST_CLERK2600.00TuckerSA_REP 10000.00Bernstein SA_REP 9500.00Hall SA_REP 9000.00Olsen SA_REP 8000.00LAST_NAME JOB_ID SALARY------------------------- ---------- ----------Cambrault SA_REP 7500.00King SA_REP 10000.00Sully SA_REP 9500.00McEwenSA_REP 9000.00Smith SA_REP 8000.00Doran SA_REP 7500.00Vishney SA_REP 10500.00GreeneSA_REP 9500.00Marvins SA_REP 7200.00Lee SA_REP 6800.00Ande SA_REP 6400.00Banda SA_REP 6200.00Ozer SA_REP 11500.00Bloom SA_REP 10000.00Fox SA_REP 9600.00Smith SA_REP 7400.00Bates SA_REP 7300.00Kumar SA_REP 6100.00Abel SA_REP 11000.00HuttonSA_REP 8800.00TaylorSA_REP 8600.00LAST_NAME JOB_ID SALARY------------------------- ---------- ----------LivingstonSA_REP 8400.00Johnson SA_REP 6200.0043 rows selected

0.2

SQL> select last_name,salary,commission_pct from employees where commission_pct=0.2;SQL> select last_name,salary,commission_pct from employees where commission_pct=.2;SQL> select last_name,salary,commission_pct from employees where commission_pct=20/100;LAST_NAME SALARY COMMISSION_PCT------------------------- ---------- --------------Zlotkey 10500.00 0.20Olsen 8000.00 0.20Cambrault7500.00 0.20Bloom 10000.00 0.20Fox9600.00 0.20Taylor 8600.00 0.20Livingston 8400.00 0.207 rows selected

也可以写（20/100）

咱们说都是oracle里面的HR用户的表案例，前面的文章我说过的哦