失眠网 > Oracle数据库：sql语言结构数据查询语言DQL select * from table；算术别名连接去重等操作

Oracle数据库：sql语言结构数据查询语言DQL select * from table；算术别名连接去重等操作

时间：2019-02-02 03:26:00

Oracle数据库：sql语言结构，数据查询语言DQL，select * from table;算术，别名，连接，去重等操作

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

测开的话，你就得学数据库，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数据库：表的关系：一对多，一对一，多对多，一一继承和修改的关系，表不是重点，重点是数据和约束关系

文章目录

Oracle数据库：sql语言结构，数据查询语言DQL，select * from table;算术，别名，连接，去重等操作@[TOC](文章目录)oracle：SQL语言sql语言结构数据查询语言DQL编写基本的select语句select的算术表达式null既不是0，也不是空格，因为0是数字，而空格是字符，null是虚无列名的别名修改连字运算符，||，连接字符文字字符串去除重复行DISTINCTselcet语句小练习数据操作语言DML事物处理语言TCL数据控制语言DCL数据定义语言：DDL总结

oracle：SQL语言

查询和程序设计语言

操作数据库

只能依赖于sql语言

咱们plsql客户端目的也是帮助生成sql语言

可视化给我们看

不需要学全部，只需要代表性的

我们用的oracle11g支持sql99版【向下兼容】

很多厂商不见得都支持ISO定制的标准

一般oracle会有自己的扩展，无法使用到mysql中

sql语言结构

数据查询语言DQL

咱们要讲这些语言

你不就得建表，查询吗？

但是自己建表很麻烦，oracle数据库有一个默认的用户HR，它里面有很多示例，所以我们可以借此登录成为HR用户

然后查它已经有了的数据，练习上面这个数据查询语言DQL

dba身份登录sys

把user下面的hr用户的密码设置一下，然后解锁

你再登录了HR

然后就可以发现有7个样例表格

国家、部门、员工，工作、历史工作、地址、区域

我们得知道表结构，应该是有一个命令的

右击edit，看columns

这就是我们经常玩的方法

还有就是用cmdWindows

用desc命令【description的简写，看描述】

desc 表名;

SQL> desc countries;Name Type Nullable Default Comments ------------ ------------ -------- ------- ------------------------------------------------------------------------------------ COUNTRY_ID CHAR(2) Primary key of countries table. COUNTRY_NAME VARCHAR2(40) YCountry name REGION_ID NUMBER YRegion ID for the country. Foreign key to region_id column in the departments table.

可以看见表的列明，数据类型，他们的comments注释

region_id是departments的外键

编写基本的select语句

*是所有的列

想要部分列，就给定特定的列名

DISTINCT是去除重复的过多的数据，剔除，保留补不重复的数据

sql语句至少要有select 和from，一个选择列，一个选择表

你写命令可以用小写，sql会自动转大写的

sql写表格名字，列名也是不敏感的

数据里面的大小写是区分的

SQL> select * from departments;DEPARTMENT_ID DEPARTMENT_NAMEMANAGER_ID LOCATION_ID------------- ------------------------------ ---------- -----------10 Administration 200 170020 Marketing 201 180030 Purchasing 114 170040 Human Resources 203 240050 Shipping121 150060 IT103 140070 Public Relations 204 270080 Sales 145 250090 Executive 100 1700100 Finance 108 1700110 Accounting 205 1700120 Treasury 1700130 Corporate Tax1700140 Control And Credit 1700150 Shareholder Services 1700160 Benefits 1700170 Manufacturing1700180 Construction 1700190 Contracting 1700200 Operations 1700DEPARTMENT_ID DEPARTMENT_NAMEMANAGER_ID LOCATION_ID------------- ------------------------------ ---------- -----------210 IT Support 1700220 NOC1700230 IT Helpdesk 1700240 Government Sales 1700250 Retail Sales 1700260 Recruiting 1700270 Payroll 170027 rows selectedSQL>

你会发现这个表格，数字靠右侧贴近

文字靠左贴近显示

可以学会美化sql语句

关键字不可分开

反正很直白

select 列，from 表

【这是考网警最基础的骚操作，你要会的】

如果你只想看DEPARTMENT_NAME，则这么搞

SQL> select department_name from departments;DEPARTMENT_NAME------------------------------AdministrationMarketingPurchasingHuman ResourcesShippingITPublic RelationsSalesExecutiveFinanceAccountingTreasuryCorporate TaxControl And CreditShareholder ServicesBenefitsManufacturingConstructionContractingOperationsDEPARTMENT_NAME------------------------------IT SupportNOCIT HelpdeskGovernment SalesRetail SalesRecruitingPayroll27 rows selected

如何，就只有部门名字了吧，美滋滋

select的算术表达式

这些知识点，跟常规的程序语言一模一样

所以你程序界，一通百通

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

里面有薪水salary

你要全年的薪水的话，需要12*月薪呗

然后加100好说，整体只增加100元，仅此而已

SQL> select employee_id,first_name,last_name,salary,salary*12+100 from employees;EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100----------- -------------------- ------------------------- ---------- -------------100 StevenKing 24000.00 288100101 NeenaKochhar 17000.00 204100102 Lex De Haan 17000.00 204100103 Alexander Hunold 9000.00 108100104 BruceErnst 6000.00 72100105 DavidAustin 4800.00 57700106 ValliPataballa4800.00 57700107 DianaLorentz 4200.00 50500108 NancyGreenberg 1.00 144196109 DanielFaviet 9000.00 108100110 John Chen8200.00 98500111 IsmaelSciarra 7700.00 92500112 Jose ManuelUrman 7800.00 93700113 Luis Popp6900.00 82900114 Den Raphaely11000.00 132100115 Alexander Khoo3100.00 37300116 ShelliBaida 2900.00 34900117 SigalTobias 2800.00 33700118 Guy Himuro 2600.00 31300119 KarenColmenares 2500.00 30100EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100----------- -------------------- ------------------------- ---------- -------------120 Matthew Weiss 8000.00 96100121 Adam Fripp 8200.00 98500122 PayamKaufling 7900.00 94900123 ShantaVollman 6500.00 78100124 KevinMourgos 5800.00 69700125 JuliaNayer 3200.00 38500126 IreneMikkilineni 2700.00 32500127 JamesLandry 2400.00 28900128 StevenMarkle 2200.00 26500129 LauraBissot 3300.00 39700130 MozheAtkinson 2800.00 33700131 JamesMarlow 2500.00 30100132 TJ Olson 2100.00 25300133 JasonMallin 3300.00 39700134 Michael Rogers 2900.00 34900135 Ki Gee2400.00 28900136 HazelPhiltanker 2200.00 26500137 RenskeLadwig 3600.00 43300138 Stephen Stiles 3200.00 38500139 John Seo2700.00 32500140 JoshuaPatel 2500.00 30100EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100----------- -------------------- ------------------------- ---------- -------------141 TrennaRajs3500.00 42100142 CurtisDavies 3100.00 37300143 Randall Matos 2600.00 31300144 PeterVargas 2500.00 30100145 John Russell 14000.00 168100146 KarenPartners13500.00 162100147 Alberto Errazuriz 12000.00 144100148 GeraldCambrault 11000.00 132100149 EleniZlotkey 10500.00 126100150 PeterTucker 10000.00 10151 DavidBernstein9500.00 114100152 PeterHall9000.00 108100153 ChristopherOlsen 8000.00 96100154 Nanette Cambrault7500.00 90100155 OliverTuvault 7000.00 84100156 Janette King 10000.00 10157 Patrick Sully 9500.00 114100158 AllanMcEwen 9000.00 108100159 Lindsey Smith 8000.00 96100160 LouiseDoran 7500.00 90100161 SarathSewall 7000.00 84100EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100----------- -------------------- ------------------------- ---------- -------------162 ClaraVishney 10500.00 126100163 Danielle Greene 9500.00 114100164 MatteaMarvins 7200.00 86500165 DavidLee6800.00 81700166 SundarAnde6400.00 76900167 Amit Banda 6200.00 74500168 Lisa Ozer 11500.00 138100169 Harrison Bloom 10000.00 10170 TaylerFox9600.00 115300171 William Smith 7400.00 88900172 Elizabeth Bates 7300.00 87700173 Sundita Kumar 6100.00 73300174 EllenAbel 11000.00 132100175 AlyssaHutton 8800.00 105700176 Jonathon Taylor 8600.00 103300177 Jack Livingston 8400.00 100900178 Kimberely Grant 7000.00 84100179 Charles Johnson 6200.00 74500180 Winston Taylor 3200.00 38500181 Jean Fleaur 3100.00 37300182 MarthaSullivan 2500.00 30100EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100----------- -------------------- ------------------------- ---------- -------------183 GirardGeoni 2800.00 33700184 Nandita Sarchand 4200.00 50500185 AlexisBull4100.00 49300186 JuliaDellinger3400.00 40900187 Anthony Cabrio 3000.00 36100188 KellyChung 3800.00 45700189 Jennifer Dilly 3600.00 43300190 Timothy Gates 2900.00 34900191 Randall Perkins 2500.00 30100192 SarahBell4000.00 48100193 Britney Everett 3900.00 46900194 SamuelMcCain 3200.00 38500195 VanceJones 2800.00 33700196 AlanaWalsh 3100.00 37300197 KevinFeeney 3000.00 36100198 DonaldOConnell 2600.00 31300199 Douglas Grant 2600.00 31300200 Jennifer Whalen 4400.00 52900201 Michael Hartstein 13000.00 156100202 Pat Fay6000.00 72100203 SusanMavris 6500.00 78100EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100----------- -------------------- ------------------------- ---------- -------------204 Hermann Baer 10000.00 10205 Shelley Higgins 1.00 144196206 William Gietz 8300.00 99700107 rows selected

我们选择了employee_id,first_name,last_name,salary,salary*12+100列

你会发现，展示了这些列

而且薪水那有10000的

12个月就是120000

再加100，就是10

那如果是月薪+100，再计算全年薪水，应该很多，增加了1200元

这就很好说了，先加括号，然后再乘12

SQL> select employee_id,first_name,last_name,salary,(salary+100)*12 from employees;EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12----------- -------------------- ------------------------- ---------- ---------------100 StevenKing 24000.00289200101 NeenaKochhar 17000.00205200102 Lex De Haan 17000.00205200103 Alexander Hunold 9000.00109200104 BruceErnst 6000.00 73200105 DavidAustin 4800.00 58800106 ValliPataballa4800.00 58800107 DianaLorentz 4200.00 51600108 NancyGreenberg 1.00145296109 DanielFaviet 9000.00109200110 John Chen8200.00 99600111 IsmaelSciarra 7700.00 93600112 Jose ManuelUrman 7800.00 94800113 Luis Popp6900.00 84000114 Den Raphaely11000.00133200115 Alexander Khoo3100.00 38400116 ShelliBaida 2900.00 36000117 SigalTobias 2800.00 34800118 Guy Himuro 2600.00 32400119 KarenColmenares 2500.00 31200EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12----------- -------------------- ------------------------- ---------- ---------------120 Matthew Weiss 8000.00 97200121 Adam Fripp 8200.00 99600122 PayamKaufling 7900.00 96000123 ShantaVollman 6500.00 79200124 KevinMourgos 5800.00 70800125 JuliaNayer 3200.00 39600126 IreneMikkilineni 2700.00 33600127 JamesLandry 2400.00 30000128 StevenMarkle 2200.00 27600129 LauraBissot 3300.00 40800130 MozheAtkinson 2800.00 34800131 JamesMarlow 2500.00 31200132 TJ Olson 2100.00 26400133 JasonMallin 3300.00 40800134 Michael Rogers 2900.00 36000135 Ki Gee2400.00 30000136 HazelPhiltanker 2200.00 27600137 RenskeLadwig 3600.00 44400138 Stephen Stiles 3200.00 39600139 John Seo2700.00 33600140 JoshuaPatel 2500.00 31200EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12----------- -------------------- ------------------------- ---------- ---------------141 TrennaRajs3500.00 43200142 CurtisDavies 3100.00 38400143 Randall Matos 2600.00 32400144 PeterVargas 2500.00 31200145 John Russell 14000.00169200146 KarenPartners13500.00163200147 Alberto Errazuriz 12000.00145200148 GeraldCambrault 11000.00133200149 EleniZlotkey 10500.00127200150 PeterTucker 10000.00121200151 DavidBernstein9500.00115200152 PeterHall9000.00109200153 ChristopherOlsen 8000.00 97200154 Nanette Cambrault7500.00 91200155 OliverTuvault 7000.00 85200156 Janette King 10000.00121200157 Patrick Sully 9500.00115200158 AllanMcEwen 9000.00109200159 Lindsey Smith 8000.00 97200160 LouiseDoran 7500.00 91200161 SarathSewall 7000.00 85200EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12----------- -------------------- ------------------------- ---------- ---------------162 ClaraVishney 10500.00127200163 Danielle Greene 9500.00115200164 MatteaMarvins 7200.00 87600165 DavidLee6800.00 82800166 SundarAnde6400.00 78000167 Amit Banda 6200.00 75600168 Lisa Ozer 11500.00139200169 Harrison Bloom 10000.00121200170 TaylerFox9600.00116400171 William Smith 7400.00 90000172 Elizabeth Bates 7300.00 88800173 Sundita Kumar 6100.00 74400174 EllenAbel 11000.00133200175 AlyssaHutton 8800.00106800176 Jonathon Taylor 8600.00104400177 Jack Livingston 8400.00102000178 Kimberely Grant 7000.00 85200179 Charles Johnson 6200.00 75600180 Winston Taylor 3200.00 39600181 Jean Fleaur 3100.00 38400182 MarthaSullivan 2500.00 31200EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12----------- -------------------- ------------------------- ---------- ---------------183 GirardGeoni 2800.00 34800184 Nandita Sarchand 4200.00 51600185 AlexisBull4100.00 50400186 JuliaDellinger3400.00 42000187 Anthony Cabrio 3000.00 37200188 KellyChung 3800.00 46800189 Jennifer Dilly 3600.00 44400190 Timothy Gates 2900.00 36000191 Randall Perkins 2500.00 31200192 SarahBell4000.00 49200193 Britney Everett 3900.00 48000194 SamuelMcCain 3200.00 39600195 VanceJones 2800.00 34800196 AlanaWalsh 3100.00 38400197 KevinFeeney 3000.00 37200198 DonaldOConnell 2600.00 32400199 Douglas Grant 2600.00 32400200 Jennifer Whalen 4400.00 54000201 Michael Hartstein 13000.00157200202 Pat Fay6000.00 73200203 SusanMavris 6500.00 79200EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12----------- -------------------- ------------------------- ---------- ---------------204 Hermann Baer 10000.00121200205 Shelley Higgins 1.00145296206 William Gietz 8300.00100800107 rows selected

对于工资10000的

加100

10100

那乘12个月

就是121200

简单吧

算术运算就OK了

null既不是0，也不是空格，因为0是数字，而空格是字符，null是虚无

你看那个COMMISSION_PCT佣金

不见得人人都有佣金的

你可以查一波

SQL> select last_name,commission_pct,12*salary + commission_pct from employees;LAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT------------------------- -------------- ------------------------King Kochhar De Haan HunoldErnstAustinPataballa Lorentz Greenberg FavietChen Sciarra UrmanPopp Raphaely Khoo BaidaTobiasHimuroColmenares LAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT------------------------- -------------- ------------------------WeissFrippKaufling Vollman Mourgos NayerMikkilineniLandryMarkleBissotAtkinson MarlowOlsonMallinRogersGee Philtanker LadwigStilesSeo PatelLAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT------------------------- -------------- ------------------------Rajs DaviesMatosVargasRussell 0.40 168000.4Partners 0.30 162000.3Errazuriz 0.30 144000.3Cambrault 0.30 132000.3Zlotkey 0.20 126000.2Tucker0.30 120000.3Bernstein 0.25114000.25Hall 0.25108000.25Olsen 0.20 96000.2Cambrault 0.20 90000.2Tuvault 0.15 84000.15King 0.35120000.35Sully 0.35114000.35McEwen0.35108000.35Smith 0.30 96000.3Doran 0.30 90000.3Sewall0.25 84000.25LAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT------------------------- -------------- ------------------------Vishney 0.25126000.25Greene0.15114000.15Marvins 0.10 86400.1Lee 0.10 81600.1Ande 0.10 76800.1Banda 0.10 74400.1Ozer 0.25138000.25Bloom 0.20 120000.2Fox 0.20 115200.2Smith 0.15 88800.15Bates 0.15 87600.15Kumar 0.10 73200.1Abel 0.30 132000.3Hutton0.25105600.25Taylor0.20 103200.2Livingston0.20 100800.2Grant 0.15 84000.15Johnson 0.10 74400.1TaylorFleaurSullivan LAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT------------------------- -------------- ------------------------GeoniSarchand Bull Dellinger CabrioChungDillyGatesPerkins Bell Everett McCainJonesWalshFeeneyOConnell GrantWhalenHartstein Fay MavrisLAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT------------------------- -------------- ------------------------Baer Higgins Gietz107 rows selected

你可以看到很多列是没有数据的，他们就是null

有佣金的才能用薪水加佣金显示出来

这不管是oracle还是sql，都一样

列名的别名修改

有时候列名太复杂，简写一波，或者表达式简写一波

SQL> select last_name,commission_pct as comm,12*salary + commission_pct as "annual salary" from employees;LAST_NAME COMM annual salary------------------------- ---- -------------King Kochhar De Haan HunoldErnstAustinPataballa Lorentz Greenberg FavietChen Sciarra UrmanPopp Raphaely Khoo BaidaTobiasHimuroColmenares LAST_NAME COMM annual salary------------------------- ---- -------------WeissFrippKaufling Vollman Mourgos NayerMikkilineniLandryMarkleBissotAtkinson MarlowOlsonMallinRogersGee Philtanker LadwigStilesSeo PatelLAST_NAME COMM annual salary------------------------- ---- -------------Rajs DaviesMatosVargasRussell 0.40168000.4Partners 0.30162000.3Errazuriz 0.30144000.3Cambrault 0.30132000.3Zlotkey 0.6000.2Tucker0.30120000.3Bernstein 0.25114000.25Hall 0.25108000.25Olsen 0.20 96000.2Cambrault 0.20 90000.2Tuvault 0.1584000.15King 0.35120000.35Sully 0.35114000.35McEwen0.35108000.35Smith 0.30 96000.3Doran 0.30 90000.3Sewall0.2584000.25LAST_NAME COMM annual salary------------------------- ---- -------------Vishney 0.25126000.25Greene0.15114000.15Marvins 0.10 86400.1Lee 0.10 81600.1Ande 0.10 76800.1Banda 0.10 74400.1Ozer 0.25138000.25Bloom 0.0000.2Fox 0.5200.2Smith 0.1588800.15Bates 0.1587600.15Kumar 0.10 73200.1Abel 0.30132000.3Hutton0.25105600.25Taylor0.3200.2Livingston0.0800.2Grant 0.1584000.15Johnson 0.10 74400.1TaylorFleaurSullivan LAST_NAME COMM annual salary------------------------- ---- -------------GeoniSarchand Bull Dellinger CabrioChungDillyGatesPerkins Bell Everett McCainJonesWalshFeeneyOConnell GrantWhalenHartstein Fay MavrisLAST_NAME COMM annual salary------------------------- ---- -------------Baer Higgins Gietz107 rows selected

annual salary中间有空格，所以需要双引号

这些东西你不用死记硬背，就那plsql操作一下，就知道了

as可以写，可以不写

SQL> select last_name,commission_pct comm,12*salary + commission_pct "annual salary" from employees;LAST_NAME COMM annual salary------------------------- ---- -------------King Kochhar De Haan HunoldErnstAustinPataballa Lorentz Greenberg FavietChen Sciarra UrmanPopp Raphaely Khoo BaidaTobiasHimuroColmenares LAST_NAME COMM annual salary------------------------- ---- -------------WeissFrippKaufling Vollman Mourgos NayerMikkilineniLandryMarkleBissotAtkinson MarlowOlsonMallinRogersGee Philtanker LadwigStilesSeo PatelLAST_NAME COMM annual salary------------------------- ---- -------------Rajs DaviesMatosVargasRussell 0.40168000.4Partners 0.30162000.3Errazuriz 0.30144000.3Cambrault 0.30132000.3Zlotkey 0.6000.2Tucker0.30120000.3Bernstein 0.25114000.25Hall 0.25108000.25Olsen 0.20 96000.2Cambrault 0.20 90000.2Tuvault 0.1584000.15King 0.35120000.35Sully 0.35114000.35McEwen0.35108000.35Smith 0.30 96000.3Doran 0.30 90000.3Sewall0.2584000.25LAST_NAME COMM annual salary------------------------- ---- -------------Vishney 0.25126000.25Greene0.15114000.15Marvins 0.10 86400.1Lee 0.10 81600.1Ande 0.10 76800.1Banda 0.10 74400.1Ozer 0.25138000.25Bloom 0.0000.2Fox 0.5200.2Smith 0.1588800.15Bates 0.1587600.15Kumar 0.10 73200.1Abel 0.30132000.3Hutton0.25105600.25Taylor0.3200.2Livingston0.0800.2Grant 0.1584000.15Johnson 0.10 74400.1TaylorFleaurSullivan LAST_NAME COMM annual salary------------------------- ---- -------------GeoniSarchand Bull Dellinger CabrioChungDillyGatesPerkins Bell Everett McCainJonesWalshFeeneyOConnell GrantWhalenHartstein Fay MavrisLAST_NAME COMM annual salary------------------------- ---- -------------Baer Higgins Gietz107 rows selected

咋样，as省略了，规则不变就行了，好说的这

连字运算符，||，连接字符

我们先把名字连接瞅瞅

SQL> select last_name||first_name as name from employees;NAME---------------------------------------------AbelEllenAndeSundarAtkinsonMozheAustinDavidBaerHermannBaidaShelliBandaAmitBatesElizabethBellSarahBernsteinDavidBissotLauraBloomHarrisonBullAlexisCabrioAnthonyCambraultGeraldCambraultNanetteChenJohnChungKellyColmenaresKarenDaviesCurtisNAME---------------------------------------------De HaanLexDellingerJuliaDillyJenniferDoranLouiseErnstBruceErrazurizAlbertoEverettBritneyFavietDanielFayPatFeeneyKevinFleaurJeanFoxTaylerFrippAdamGatesTimothyGeeKiGeoniGirardGietzWilliamGrantDouglasGrantKimberelyGreenbergNancyGreeneDanielleNAME---------------------------------------------HallPeterHartsteinMichaelHigginsShelleyHimuroGuyHunoldAlexanderHuttonAlyssaJohnsonCharlesJonesVanceKauflingPayamKhooAlexanderKingJanetteKingStevenKochharNeenaKumarSunditaLadwigRenskeLandryJamesLeeDavidLivingstonJackLorentzDianaMallinJasonMarkleStevenNAME---------------------------------------------MarlowJamesMarvinsMatteaMatosRandallMavrisSusanMcCainSamuelMcEwenAllanMikkilineniIreneMourgosKevinNayerJuliaOConnellDonaldOlsenChristopherOlsonTJOzerLisaPartnersKarenPataballaValliPatelJoshuaPerkinsRandallPhiltankerHazelPoppLuisRajsTrennaRaphaelyDenNAME---------------------------------------------RogersMichaelRussellJohnSarchandNanditaSciarraIsmaelSeoJohnSewallSarathSmithLindseySmithWilliamStilesStephenSullivanMarthaSullyPatrickTaylorJonathonTaylorWinstonTobiasSigalTuckerPeterTuvaultOliverUrmanJose ManuelVargasPeterVishneyClaraVollmanShantaWalshAlanaNAME---------------------------------------------WeissMatthewWhalenJenniferZlotkeyEleni107 rows selected

然后我们把薪水和名字挂起来

年薪也可以挂，然后起个别名，叫月薪和年薪

SQL> select last_name||salary as month,last_name||12*salary as annual from employees;MONTH ANNUAL----------------------------------------------------------------- -----------------------------------------------------------------King24000 King288000Kochhar17000 Kochhar204000De Haan17000 De Haan204000Hunold9000Hunold108000Ernst6000 Ernst72000Austin4800Austin57600Pataballa4800 Pataballa57600Lorentz4200Lorentz50400Greenberg1 Greenberg144096Faviet9000Faviet108000Chen8200 Chen98400Sciarra7700Sciarra92400Urman7800 Urman93600Popp6900 Popp82800Raphaely11000 Raphaely132000Khoo3100 Khoo37200Baida2900 Baida34800Tobias2800Tobias33600Himuro2600Himuro31200Colmenares2500 Colmenares30000MONTH ANNUAL----------------------------------------------------------------- -----------------------------------------------------------------Weiss8000 Weiss96000Fripp8200 Fripp98400Kaufling7900 Kaufling94800Vollman6500Vollman78000Mourgos5800Mourgos69600Nayer3200 Nayer38400Mikkilineni2700 Mikkilineni32400Landry2400Landry28800Markle2200Markle26400Bissot3300Bissot39600Atkinson2800 Atkinson33600Marlow2500Marlow30000Olson2100 Olson25200Mallin3300Mallin39600Rogers2900Rogers34800Gee2400 Gee28800Philtanker2200 Philtanker26400Ladwig3600Ladwig43200Stiles3200Stiles38400Seo2700 Seo32400Patel2500 Patel30000MONTH ANNUAL----------------------------------------------------------------- -----------------------------------------------------------------Rajs3500 Rajs42000Davies3100Davies37200Matos2600 Matos31200Vargas2500Vargas30000Russell14000 Russell168000Partners13500 Partners162000Errazuriz12000 Errazuriz144000Cambrault11000 Cambrault132000Zlotkey10500 Zlotkey126000Tucker10000Tucker120000Bernstein9500 Bernstein114000Hall9000 Hall108000Olsen8000 Olsen96000Cambrault7500 Cambrault90000Tuvault7000Tuvault84000King10000 King120000Sully9500 Sully114000McEwen9000McEwen108000Smith8000 Smith96000Doran7500 Doran90000Sewall7000Sewall84000MONTH ANNUAL----------------------------------------------------------------- -----------------------------------------------------------------Vishney10500 Vishney126000Greene9500Greene114000Marvins7200Marvins86400Lee6800 Lee81600Ande6400 Ande76800Banda6200 Banda74400Ozer11500 Ozer138000Bloom10000Bloom120000Fox9600 Fox115200Smith7400 Smith88800Bates7300 Bates87600Kumar6100 Kumar73200Abel11000 Abel132000Hutton8800Hutton105600Taylor8600Taylor103200Livingston8400 Livingston100800Grant7000 Grant84000Johnson6200Johnson74400Taylor3200Taylor38400Fleaur3100Fleaur37200Sullivan2500 Sullivan30000MONTH ANNUAL----------------------------------------------------------------- -----------------------------------------------------------------Geoni2800 Geoni33600Sarchand4200 Sarchand50400Bull4100 Bull49200Dellinger3400 Dellinger40800Cabrio3000Cabrio36000Chung3800 Chung45600Dilly3600 Dilly43200Gates2900 Gates34800Perkins2500Perkins30000Bell4000 Bell48000Everett3900Everett46800McCain3200McCain38400Jones2800 Jones33600Walsh3100 Walsh37200Feeney3000Feeney36000OConnell2600 OConnell31200Grant2600 Grant31200Whalen4400Whalen52800Hartstein13000 Hartstein156000Fay6000 Fay72000Mavris6500Mavris78000MONTH ANNUAL----------------------------------------------------------------- -----------------------------------------------------------------Baer10000 Baer120000Higgins1 Higgins144096Gietz8300 Gietz99600107 rows selected

好玩吧

我们再给名字和jobid连接

SQL> select last_name||job_id as employee from employees;EMPLOYEE-----------------------------------AbelSA_REPAndeSA_REPAtkinsonST_CLERKAustinIT_PROGBaerPR_REPBaidaPU_CLERKBandaSA_REPBatesSA_REPBellSH_CLERKBernsteinSA_REPBissotST_CLERKBloomSA_REPBullSH_CLERKCabrioSH_CLERKCambraultSA_MANCambraultSA_REPChenFI_ACCOUNTChungSH_CLERKColmenaresPU_CLERKDaviesST_CLERKEMPLOYEE-----------------------------------De HaanAD_VPDellingerSH_CLERKDillySH_CLERKDoranSA_REPErnstIT_PROGErrazurizSA_MANEverettSH_CLERKFavietFI_ACCOUNTFayMK_REPFeeneySH_CLERKFleaurSH_CLERKFoxSA_REPFrippST_MANGatesSH_CLERKGeeST_CLERKGeoniSH_CLERKGietzAC_ACCOUNTGrantSH_CLERKGrantSA_REPGreenbergFI_MGRGreeneSA_REPEMPLOYEE-----------------------------------HallSA_REPHartsteinMK_MANHigginsAC_MGRHimuroPU_CLERKHunoldIT_PROGHuttonSA_REPJohnsonSA_REPJonesSH_CLERKKauflingST_MANKhooPU_CLERKKingSA_REPKingAD_PRESKochharAD_VPKumarSA_REPLadwigST_CLERKLandryST_CLERKLeeSA_REPLivingstonSA_REPLorentzIT_PROGMallinST_CLERKMarkleST_CLERKEMPLOYEE-----------------------------------MarlowST_CLERKMarvinsSA_REPMatosST_CLERKMavrisHR_REPMcCainSH_CLERKMcEwenSA_REPMikkilineniST_CLERKMourgosST_MANNayerST_CLERKOConnellSH_CLERKOlsenSA_REPOlsonST_CLERKOzerSA_REPPartnersSA_MANPataballaIT_PROGPatelST_CLERKPerkinsSH_CLERKPhiltankerST_CLERKPoppFI_ACCOUNTRajsST_CLERKRaphaelyPU_MANEMPLOYEE-----------------------------------RogersST_CLERKRussellSA_MANSarchandSH_CLERKSciarraFI_ACCOUNTSeoST_CLERKSewallSA_REPSmithSA_REPSmithSA_REPStilesST_CLERKSullivanSH_CLERKSullySA_REPTaylorSA_REPTaylorSH_CLERKTobiasPU_CLERKTuckerSA_REPTuvaultSA_REPUrmanFI_ACCOUNTVargasST_CLERKVishneySA_REPVollmanST_MANWalshSH_CLERKEMPLOYEE-----------------------------------WeissST_MANWhalenAD_ASSTZlotkeySA_MAN107 rows selected

都很简单的

这些考网警的，最基础的查询数据库的操作，你是必须要会的

不然你去做网络警察抓罪犯怎么搞？

数据库你都不会查，请问你怎么搞？？？

文字字符串

就是外加别的字符串

同时可以展示日期，数字

字符串和日期需要加单引号

看例子

SQL> select last_name||' is '||job_id as "employee details" from employees;employee details---------------------------------------Abel is SA_REPAnde is SA_REPAtkinson is ST_CLERKAustin is IT_PROGBaer is PR_REPBaida is PU_CLERKBanda is SA_REPBates is SA_REPBell is SH_CLERKBernstein is SA_REPBissot is ST_CLERKBloom is SA_REPBull is SH_CLERKCabrio is SH_CLERKCambrault is SA_MANCambrault is SA_REPChen is FI_ACCOUNTChung is SH_CLERKColmenares is PU_CLERKDavies is ST_CLERKemployee details---------------------------------------De Haan is AD_VPDellinger is SH_CLERKDilly is SH_CLERKDoran is SA_REPErnst is IT_PROGErrazuriz is SA_MANEverett is SH_CLERKFaviet is FI_ACCOUNTFay is MK_REPFeeney is SH_CLERKFleaur is SH_CLERKFox is SA_REPFripp is ST_MANGates is SH_CLERKGee is ST_CLERKGeoni is SH_CLERKGietz is AC_ACCOUNTGrant is SH_CLERKGrant is SA_REPGreenberg is FI_MGRGreene is SA_REPemployee details---------------------------------------Hall is SA_REPHartstein is MK_MANHiggins is AC_MGRHimuro is PU_CLERKHunold is IT_PROGHutton is SA_REPJohnson is SA_REPJones is SH_CLERKKaufling is ST_MANKhoo is PU_CLERKKing is SA_REPKing is AD_PRESKochhar is AD_VPKumar is SA_REPLadwig is ST_CLERKLandry is ST_CLERKLee is SA_REPLivingston is SA_REPLorentz is IT_PROGMallin is ST_CLERKMarkle is ST_CLERKemployee details---------------------------------------Marlow is ST_CLERKMarvins is SA_REPMatos is ST_CLERKMavris is HR_REPMcCain is SH_CLERKMcEwen is SA_REPMikkilineni is ST_CLERKMourgos is ST_MANNayer is ST_CLERKOConnell is SH_CLERKOlsen is SA_REPOlson is ST_CLERKOzer is SA_REPPartners is SA_MANPataballa is IT_PROGPatel is ST_CLERKPerkins is SH_CLERKPhiltanker is ST_CLERKPopp is FI_ACCOUNTRajs is ST_CLERKRaphaely is PU_MANemployee details---------------------------------------Rogers is ST_CLERKRussell is SA_MANSarchand is SH_CLERKSciarra is FI_ACCOUNTSeo is ST_CLERKSewall is SA_REPSmith is SA_REPSmith is SA_REPStiles is ST_CLERKSullivan is SH_CLERKSully is SA_REPTaylor is SA_REPTaylor is SH_CLERKTobias is PU_CLERKTucker is SA_REPTuvault is SA_REPUrman is FI_ACCOUNTVargas is ST_CLERKVishney is SA_REPVollman is ST_MANWalsh is SH_CLERKemployee details---------------------------------------Weiss is ST_MANWhalen is AD_ASSTZlotkey is SA_MAN107 rows selected

瞅见了吗

中间我再额外写一个别的字符就很舒服，看起来易读

同时改名字也看起来舒服

SQL> select last_name||55||job_id as "employee details" from employees;employee details-------------------------------------Abel55SA_REPAnde55SA_REPAtkinson55ST_CLERK

纯数字中间拼就不用单引号

但是日期是要的

SQL> select last_name||'10月23日'||job_id as "employee details" from employees;employee details----------------------------------------------------Abel10月23日SA_REPAnde10月23日SA_REP

去除重复行DISTINCT

distinct就是独有的意思

部门id

正常情况下，部门id很多重复的

SQL> select department_id from employees;DEPARTMENT_ID-------------9090906060606060100100100100100100303030303030DEPARTMENT_ID-------------505050505050505050505050505050505050505050DEPARTMENT_ID-------------505050508080808080808080808080808080808080DEPARTMENT_ID-------------8080808080808080808080808080808080505050DEPARTMENT_ID-------------50505050505050505050505050505050501040DEPARTMENT_ID-------------70110110107 rows selected

去重重复的部门id，加distinct

出现在select之后紧跟distinct

然后紧跟列名

SQL> select distinct department_id from employees;DEPARTMENT_ID-------------10030902070110508040601012 rows selected

你瞅瞅，实际上公司就12个部门，重复的就去掉了

SQL> select distinct department_id,last_name from employees;DEPARTMENT_ID LAST_NAME------------- -------------------------50 Seo80 Cambrault80 McEwen80 Livingston50 Dellinger110 Gietz60 Austin60 Pataballa100 Popp30 Baida50 Weiss50 Fripp50 Mikkilineni50 Atkinson50 Olson50 Philtanker50 Stiles80 Bates80 Kumar50 TaylorDEPARTMENT_ID LAST_NAME------------- -------------------------50 Sullivan50 Sarchand50 Bell60 Ernst100 Sciarra30 Himuro50 Kaufling50 Nayer50 Landry80 Russell80 Errazuriz80 Doran80 Greene80 BandaGrant50 Everett50 Feeney70 Baer110 Higgins60 Hunold100 FavietDEPARTMENT_ID LAST_NAME------------- -------------------------30 Colmenares50 Mourgos50 Patel80 Partners80 Smith50 Fleaur50 Dilly50 Walsh50 Grant90 King30 Khoo50 Bissot50 Rogers50 Gee50 Matos80 Tucker80 Olsen80 King50 Cabrio50 McCain20 HartsteinDEPARTMENT_ID LAST_NAME------------- -------------------------90 Kochhar90 De Haan100 Greenberg50 Ladwig80 Zlotkey80 Hall80 Sewall80 Vishney80 Ande80 Taylor80 Johnson50 Geoni40 Mavris100 Chen30 Raphaely50 Vollman50 Marlow50 Mallin50 Rajs80 Bernstein80 TuvaultDEPARTMENT_ID LAST_NAME------------- -------------------------80 Sully80 Marvins80 Lee80 Bloom80 Fox80 Hutton50 Perkins50 Jones60 Lorentz100 Urman30 Tobias50 Markle50 Davies50 Vargas80 Ozer80 Abel50 Bull50 Chung50 Gates50 OConnell10 WhalenDEPARTMENT_ID LAST_NAME------------- -------------------------20 Fay105 rows selected

你加上last_name

你会发现，部门又有重复了

啥意思？？？

其实distinct是针对的，部门id和名字，都重复的话，再去重

你会发现好像有2同名的人，同时在一个部门，被去掉了

因为总行数是107行

懂吧？

selcet语句小练习

请你查询这个sql语句的错误之处

咱们多练习，通过业务训练，使得自己熟悉一下这个数据

查的是employees表

第一个：

工资是salary，显然sal没有的，不能简写列名

第二个：

查询各个列之间，必须用逗号，而薪水前面是没有的

第三个：

乘法，用*，而不是×

第四个：

起别名，可以省去as，但是遇到空格和特殊字符，需要加双引号的

正确的格式如下：

SQL> select employee_id,last_name,salary*12 "annual salary" from employees;EMPLOYEE_ID LAST_NAME annual salary----------- ------------------------- -------------100 King 288000101 Kochhar204000

desc就是显示表结构

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, PublicRelations, Sales, Finance, and Accounting. MANAGER_IDNUMBER(6) YManager_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_IDNUMBER(4) YLocation id where a department is located. Foreign key to location_id column of locations table.

然后查询所有行

来源于表格

这个是employees表

我们去查信息

SQL> select employee_id,last_name,job_id,hire_date as startdate from employees;EMPLOYEE_ID LAST_NAME JOB_IDSTARTDATE----------- ------------------------- ---------- -----------100 King AD_PRES /6/17101 Kochhar AD_VP/9/21102 De Haan AD_VP2001/1/13

这都好说的

这个文章的好处，就是带你一步步，学习数据库，sql语言，完整地搞定数据库

SQL> select distinct job_id from employees;JOB_ID----------AC_ACCOUNTAC_MGRAD_ASSTAD_PRESAD_VPFI_ACCOUNTFI_MGRHR_REPIT_PROGMK_MANMK_REPPR_REPPU_CLERKPU_MANSA_MANSA_REPSH_CLERKST_CLERKST_MAN19 rows selected

job分级，19级，这不就是华为的offer等级吗

jobid越高，工资越高

SQL> select last_name||', '||job_id from employees;LAST_NAME||','||JOB_ID-------------------------------------Abel, SA_REPAnde, SA_REPAtkinson, ST_CLERK

链接字符串，好说