Oracle数据库：sql语言结构，数据查询语言DQL，select * from table；算术，别名，连接，去重等操作
2023-09-11 14:15:38 时间
Oracle数据库：sql语言结构，数据查询语言DQL，select * from table;算术，别名，连接，去重等操作
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数据库：表的关系：一对多，一对一，多对多，一一继承和修改的关系，表不是重点，重点是数据和约束关系
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) Y Country name
REGION_ID NUMBER Y Region 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_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
27 rows selected
SQL>
你会发现这个表格，数字靠右侧贴近
文字靠左贴近显示
可以学会美化sql语句
关键字不可分开
反正很直白
select 列，from 表
【这是考网警最基础的骚操作，你要会的】
如果你只想看DEPARTMENT_NAME，则这么搞
SQL> select department_name from departments;
DEPARTMENT_NAME
------------------------------
Administration
Marketing
Purchasing
Human Resources
Shipping
IT
Public Relations
Sales
Executive
Finance
Accounting
Treasury
Corporate Tax
Control And Credit
Shareholder Services
Benefits
Manufacturing
Construction
Contracting
Operations
DEPARTMENT_NAME
------------------------------
IT Support
NOC
IT Helpdesk
Government Sales
Retail Sales
Recruiting
Payroll
27 rows selected
如何，就只有部门名字了吧，美滋滋
select的算术表达式
这些知识点，跟常规的程序语言一模一样
所以你程序界，一通百通
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
里面有薪水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 Steven King 24000.00 288100
101 Neena Kochhar 17000.00 204100
102 Lex De Haan 17000.00 204100
103 Alexander Hunold 9000.00 108100
104 Bruce Ernst 6000.00 72100
105 David Austin 4800.00 57700
106 Valli Pataballa 4800.00 57700
107 Diana Lorentz 4200.00 50500
108 Nancy Greenberg 12008.00 144196
109 Daniel Faviet 9000.00 108100
110 John Chen 8200.00 98500
111 Ismael Sciarra 7700.00 92500
112 Jose Manuel Urman 7800.00 93700
113 Luis Popp 6900.00 82900
114 Den Raphaely 11000.00 132100
115 Alexander Khoo 3100.00 37300
116 Shelli Baida 2900.00 34900
117 Sigal Tobias 2800.00 33700
118 Guy Himuro 2600.00 31300
119 Karen Colmenares 2500.00 30100
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100
----------- -------------------- ------------------------- ---------- -------------
120 Matthew Weiss 8000.00 96100
121 Adam Fripp 8200.00 98500
122 Payam Kaufling 7900.00 94900
123 Shanta Vollman 6500.00 78100
124 Kevin Mourgos 5800.00 69700
125 Julia Nayer 3200.00 38500
126 Irene Mikkilineni 2700.00 32500
127 James Landry 2400.00 28900
128 Steven Markle 2200.00 26500
129 Laura Bissot 3300.00 39700
130 Mozhe Atkinson 2800.00 33700
131 James Marlow 2500.00 30100
132 TJ Olson 2100.00 25300
133 Jason Mallin 3300.00 39700
134 Michael Rogers 2900.00 34900
135 Ki Gee 2400.00 28900
136 Hazel Philtanker 2200.00 26500
137 Renske Ladwig 3600.00 43300
138 Stephen Stiles 3200.00 38500
139 John Seo 2700.00 32500
140 Joshua Patel 2500.00 30100
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100
----------- -------------------- ------------------------- ---------- -------------
141 Trenna Rajs 3500.00 42100
142 Curtis Davies 3100.00 37300
143 Randall Matos 2600.00 31300
144 Peter Vargas 2500.00 30100
145 John Russell 14000.00 168100
146 Karen Partners 13500.00 162100
147 Alberto Errazuriz 12000.00 144100
148 Gerald Cambrault 11000.00 132100
149 Eleni Zlotkey 10500.00 126100
150 Peter Tucker 10000.00 120100
151 David Bernstein 9500.00 114100
152 Peter Hall 9000.00 108100
153 Christopher Olsen 8000.00 96100
154 Nanette Cambrault 7500.00 90100
155 Oliver Tuvault 7000.00 84100
156 Janette King 10000.00 120100
157 Patrick Sully 9500.00 114100
158 Allan McEwen 9000.00 108100
159 Lindsey Smith 8000.00 96100
160 Louise Doran 7500.00 90100
161 Sarath Sewall 7000.00 84100
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100
----------- -------------------- ------------------------- ---------- -------------
162 Clara Vishney 10500.00 126100
163 Danielle Greene 9500.00 114100
164 Mattea Marvins 7200.00 86500
165 David Lee 6800.00 81700
166 Sundar Ande 6400.00 76900
167 Amit Banda 6200.00 74500
168 Lisa Ozer 11500.00 138100
169 Harrison Bloom 10000.00 120100
170 Tayler Fox 9600.00 115300
171 William Smith 7400.00 88900
172 Elizabeth Bates 7300.00 87700
173 Sundita Kumar 6100.00 73300
174 Ellen Abel 11000.00 132100
175 Alyssa Hutton 8800.00 105700
176 Jonathon Taylor 8600.00 103300
177 Jack Livingston 8400.00 100900
178 Kimberely Grant 7000.00 84100
179 Charles Johnson 6200.00 74500
180 Winston Taylor 3200.00 38500
181 Jean Fleaur 3100.00 37300
182 Martha Sullivan 2500.00 30100
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100
----------- -------------------- ------------------------- ---------- -------------
183 Girard Geoni 2800.00 33700
184 Nandita Sarchand 4200.00 50500
185 Alexis Bull 4100.00 49300
186 Julia Dellinger 3400.00 40900
187 Anthony Cabrio 3000.00 36100
188 Kelly Chung 3800.00 45700
189 Jennifer Dilly 3600.00 43300
190 Timothy Gates 2900.00 34900
191 Randall Perkins 2500.00 30100
192 Sarah Bell 4000.00 48100
193 Britney Everett 3900.00 46900
194 Samuel McCain 3200.00 38500
195 Vance Jones 2800.00 33700
196 Alana Walsh 3100.00 37300
197 Kevin Feeney 3000.00 36100
198 Donald OConnell 2600.00 31300
199 Douglas Grant 2600.00 31300
200 Jennifer Whalen 4400.00 52900
201 Michael Hartstein 13000.00 156100
202 Pat Fay 6000.00 72100
203 Susan Mavris 6500.00 78100
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY SALARY*12+100
----------- -------------------- ------------------------- ---------- -------------
204 Hermann Baer 10000.00 120100
205 Shelley Higgins 12008.00 144196
206 William Gietz 8300.00 99700
107 rows selected
我们选择了employee_id,first_name,last_name,salary,salary*12+100列
你会发现，展示了这些列
而且薪水那有10000的
12个月就是120000
再加100，就是120100
那如果是月薪+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 Steven King 24000.00 289200
101 Neena Kochhar 17000.00 205200
102 Lex De Haan 17000.00 205200
103 Alexander Hunold 9000.00 109200
104 Bruce Ernst 6000.00 73200
105 David Austin 4800.00 58800
106 Valli Pataballa 4800.00 58800
107 Diana Lorentz 4200.00 51600
108 Nancy Greenberg 12008.00 145296
109 Daniel Faviet 9000.00 109200
110 John Chen 8200.00 99600
111 Ismael Sciarra 7700.00 93600
112 Jose Manuel Urman 7800.00 94800
113 Luis Popp 6900.00 84000
114 Den Raphaely 11000.00 133200
115 Alexander Khoo 3100.00 38400
116 Shelli Baida 2900.00 36000
117 Sigal Tobias 2800.00 34800
118 Guy Himuro 2600.00 32400
119 Karen Colmenares 2500.00 31200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12
----------- -------------------- ------------------------- ---------- ---------------
120 Matthew Weiss 8000.00 97200
121 Adam Fripp 8200.00 99600
122 Payam Kaufling 7900.00 96000
123 Shanta Vollman 6500.00 79200
124 Kevin Mourgos 5800.00 70800
125 Julia Nayer 3200.00 39600
126 Irene Mikkilineni 2700.00 33600
127 James Landry 2400.00 30000
128 Steven Markle 2200.00 27600
129 Laura Bissot 3300.00 40800
130 Mozhe Atkinson 2800.00 34800
131 James Marlow 2500.00 31200
132 TJ Olson 2100.00 26400
133 Jason Mallin 3300.00 40800
134 Michael Rogers 2900.00 36000
135 Ki Gee 2400.00 30000
136 Hazel Philtanker 2200.00 27600
137 Renske Ladwig 3600.00 44400
138 Stephen Stiles 3200.00 39600
139 John Seo 2700.00 33600
140 Joshua Patel 2500.00 31200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12
----------- -------------------- ------------------------- ---------- ---------------
141 Trenna Rajs 3500.00 43200
142 Curtis Davies 3100.00 38400
143 Randall Matos 2600.00 32400
144 Peter Vargas 2500.00 31200
145 John Russell 14000.00 169200
146 Karen Partners 13500.00 163200
147 Alberto Errazuriz 12000.00 145200
148 Gerald Cambrault 11000.00 133200
149 Eleni Zlotkey 10500.00 127200
150 Peter Tucker 10000.00 121200
151 David Bernstein 9500.00 115200
152 Peter Hall 9000.00 109200
153 Christopher Olsen 8000.00 97200
154 Nanette Cambrault 7500.00 91200
155 Oliver Tuvault 7000.00 85200
156 Janette King 10000.00 121200
157 Patrick Sully 9500.00 115200
158 Allan McEwen 9000.00 109200
159 Lindsey Smith 8000.00 97200
160 Louise Doran 7500.00 91200
161 Sarath Sewall 7000.00 85200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12
----------- -------------------- ------------------------- ---------- ---------------
162 Clara Vishney 10500.00 127200
163 Danielle Greene 9500.00 115200
164 Mattea Marvins 7200.00 87600
165 David Lee 6800.00 82800
166 Sundar Ande 6400.00 78000
167 Amit Banda 6200.00 75600
168 Lisa Ozer 11500.00 139200
169 Harrison Bloom 10000.00 121200
170 Tayler Fox 9600.00 116400
171 William Smith 7400.00 90000
172 Elizabeth Bates 7300.00 88800
173 Sundita Kumar 6100.00 74400
174 Ellen Abel 11000.00 133200
175 Alyssa Hutton 8800.00 106800
176 Jonathon Taylor 8600.00 104400
177 Jack Livingston 8400.00 102000
178 Kimberely Grant 7000.00 85200
179 Charles Johnson 6200.00 75600
180 Winston Taylor 3200.00 39600
181 Jean Fleaur 3100.00 38400
182 Martha Sullivan 2500.00 31200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12
----------- -------------------- ------------------------- ---------- ---------------
183 Girard Geoni 2800.00 34800
184 Nandita Sarchand 4200.00 51600
185 Alexis Bull 4100.00 50400
186 Julia Dellinger 3400.00 42000
187 Anthony Cabrio 3000.00 37200
188 Kelly Chung 3800.00 46800
189 Jennifer Dilly 3600.00 44400
190 Timothy Gates 2900.00 36000
191 Randall Perkins 2500.00 31200
192 Sarah Bell 4000.00 49200
193 Britney Everett 3900.00 48000
194 Samuel McCain 3200.00 39600
195 Vance Jones 2800.00 34800
196 Alana Walsh 3100.00 38400
197 Kevin Feeney 3000.00 37200
198 Donald OConnell 2600.00 32400
199 Douglas Grant 2600.00 32400
200 Jennifer Whalen 4400.00 54000
201 Michael Hartstein 13000.00 157200
202 Pat Fay 6000.00 73200
203 Susan Mavris 6500.00 79200
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY (SALARY+100)*12
----------- -------------------- ------------------------- ---------- ---------------
204 Hermann Baer 10000.00 121200
205 Shelley Higgins 12008.00 145296
206 William Gietz 8300.00 100800
107 rows selected
对于工资10000的
加100
10100
那乘12个月
就是121200
简单吧
算术运算就OK了
null既不是0，也不是空格，因为0是数字，而空格是字符，null是虚无
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
你看那个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
Hunold
Ernst
Austin
Pataballa
Lorentz
Greenberg
Faviet
Chen
Sciarra
Urman
Popp
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
LAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT
------------------------- -------------- ------------------------
Weiss
Fripp
Kaufling
Vollman
Mourgos
Nayer
Mikkilineni
Landry
Markle
Bissot
Atkinson
Marlow
Olson
Mallin
Rogers
Gee
Philtanker
Ladwig
Stiles
Seo
Patel
LAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT
------------------------- -------------- ------------------------
Rajs
Davies
Matos
Vargas
Russell 0.40 168000.4
Partners 0.30 162000.3
Errazuriz 0.30 144000.3
Cambrault 0.30 132000.3
Zlotkey 0.20 126000.2
Tucker 0.30 120000.3
Bernstein 0.25 114000.25
Hall 0.25 108000.25
Olsen 0.20 96000.2
Cambrault 0.20 90000.2
Tuvault 0.15 84000.15
King 0.35 120000.35
Sully 0.35 114000.35
McEwen 0.35 108000.35
Smith 0.30 96000.3
Doran 0.30 90000.3
Sewall 0.25 84000.25
LAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT
------------------------- -------------- ------------------------
Vishney 0.25 126000.25
Greene 0.15 114000.15
Marvins 0.10 86400.1
Lee 0.10 81600.1
Ande 0.10 76800.1
Banda 0.10 74400.1
Ozer 0.25 138000.25
Bloom 0.20 120000.2
Fox 0.20 115200.2
Smith 0.15 88800.15
Bates 0.15 87600.15
Kumar 0.10 73200.1
Abel 0.30 132000.3
Hutton 0.25 105600.25
Taylor 0.20 103200.2
Livingston 0.20 100800.2
Grant 0.15 84000.15
Johnson 0.10 74400.1
Taylor
Fleaur
Sullivan
LAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT
------------------------- -------------- ------------------------
Geoni
Sarchand
Bull
Dellinger
Cabrio
Chung
Dilly
Gates
Perkins
Bell
Everett
McCain
Jones
Walsh
Feeney
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
LAST_NAME COMMISSION_PCT 12*SALARY+COMMISSION_PCT
------------------------- -------------- ------------------------
Baer
Higgins
Gietz
107 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
Hunold
Ernst
Austin
Pataballa
Lorentz
Greenberg
Faviet
Chen
Sciarra
Urman
Popp
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
LAST_NAME COMM annual salary
------------------------- ---- -------------
Weiss
Fripp
Kaufling
Vollman
Mourgos
Nayer
Mikkilineni
Landry
Markle
Bissot
Atkinson
Marlow
Olson
Mallin
Rogers
Gee
Philtanker
Ladwig
Stiles
Seo
Patel
LAST_NAME COMM annual salary
------------------------- ---- -------------
Rajs
Davies
Matos
Vargas
Russell 0.40 168000.4
Partners 0.30 162000.3
Errazuriz 0.30 144000.3
Cambrault 0.30 132000.3
Zlotkey 0.20 126000.2
Tucker 0.30 120000.3
Bernstein 0.25 114000.25
Hall 0.25 108000.25
Olsen 0.20 96000.2
Cambrault 0.20 90000.2
Tuvault 0.15 84000.15
King 0.35 120000.35
Sully 0.35 114000.35
McEwen 0.35 108000.35
Smith 0.30 96000.3
Doran 0.30 90000.3
Sewall 0.25 84000.25
LAST_NAME COMM annual salary
------------------------- ---- -------------
Vishney 0.25 126000.25
Greene 0.15 114000.15
Marvins 0.10 86400.1
Lee 0.10 81600.1
Ande 0.10 76800.1
Banda 0.10 74400.1
Ozer 0.25 138000.25
Bloom 0.20 120000.2
Fox 0.20 115200.2
Smith 0.15 88800.15
Bates 0.15 87600.15
Kumar 0.10 73200.1
Abel 0.30 132000.3
Hutton 0.25 105600.25
Taylor 0.20 103200.2
Livingston 0.20 100800.2
Grant 0.15 84000.15
Johnson 0.10 74400.1
Taylor
Fleaur
Sullivan
LAST_NAME COMM annual salary
------------------------- ---- -------------
Geoni
Sarchand
Bull
Dellinger
Cabrio
Chung
Dilly
Gates
Perkins
Bell
Everett
McCain
Jones
Walsh
Feeney
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
LAST_NAME COMM annual salary
------------------------- ---- -------------
Baer
Higgins
Gietz
107 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
Hunold
Ernst
Austin
Pataballa
Lorentz
Greenberg
Faviet
Chen
Sciarra
Urman
Popp
Raphaely
Khoo
Baida
Tobias
Himuro
Colmenares
LAST_NAME COMM annual salary
------------------------- ---- -------------
Weiss
Fripp
Kaufling
Vollman
Mourgos
Nayer
Mikkilineni
Landry
Markle
Bissot
Atkinson
Marlow
Olson
Mallin
Rogers
Gee
Philtanker
Ladwig
Stiles
Seo
Patel
LAST_NAME COMM annual salary
------------------------- ---- -------------
Rajs
Davies
Matos
Vargas
Russell 0.40 168000.4
Partners 0.30 162000.3
Errazuriz 0.30 144000.3
Cambrault 0.30 132000.3
Zlotkey 0.20 126000.2
Tucker 0.30 120000.3
Bernstein 0.25 114000.25
Hall 0.25 108000.25
Olsen 0.20 96000.2
Cambrault 0.20 90000.2
Tuvault 0.15 84000.15
King 0.35 120000.35
Sully 0.35 114000.35
McEwen 0.35 108000.35
Smith 0.30 96000.3
Doran 0.30 90000.3
Sewall 0.25 84000.25
LAST_NAME COMM annual salary
------------------------- ---- -------------
Vishney 0.25 126000.25
Greene 0.15 114000.15
Marvins 0.10 86400.1
Lee 0.10 81600.1
Ande 0.10 76800.1
Banda 0.10 74400.1
Ozer 0.25 138000.25
Bloom 0.20 120000.2
Fox 0.20 115200.2
Smith 0.15 88800.15
Bates 0.15 87600.15
Kumar 0.10 73200.1
Abel 0.30 132000.3
Hutton 0.25 105600.25
Taylor 0.20 103200.2
Livingston 0.20 100800.2
Grant 0.15 84000.15
Johnson 0.10 74400.1
Taylor
Fleaur
Sullivan
LAST_NAME COMM annual salary
------------------------- ---- -------------
Geoni
Sarchand
Bull
Dellinger
Cabrio
Chung
Dilly
Gates
Perkins
Bell
Everett
McCain
Jones
Walsh
Feeney
OConnell
Grant
Whalen
Hartstein
Fay
Mavris
LAST_NAME COMM annual salary
------------------------- ---- -------------
Baer
Higgins
Gietz
107 rows selected
咋样，as省略了，规则不变就行了，好说的这
连字运算符，||，连接字符
我们先把名字连接瞅瞅
SQL> select last_name||first_name as name from employees;
NAME
---------------------------------------------
AbelEllen
AndeSundar
AtkinsonMozhe
AustinDavid
BaerHermann
BaidaShelli
BandaAmit
BatesElizabeth
BellSarah
BernsteinDavid
BissotLaura
BloomHarrison
BullAlexis
CabrioAnthony
CambraultGerald
CambraultNanette
ChenJohn
ChungKelly
ColmenaresKaren
DaviesCurtis
NAME
---------------------------------------------
De HaanLex
DellingerJulia
DillyJennifer
DoranLouise
ErnstBruce
ErrazurizAlberto
EverettBritney
FavietDaniel
FayPat
FeeneyKevin
FleaurJean
FoxTayler
FrippAdam
GatesTimothy
GeeKi
GeoniGirard
GietzWilliam
GrantDouglas
GrantKimberely
GreenbergNancy
GreeneDanielle
NAME
---------------------------------------------
HallPeter
HartsteinMichael
HigginsShelley
HimuroGuy
HunoldAlexander
HuttonAlyssa
JohnsonCharles
JonesVance
KauflingPayam
KhooAlexander
KingJanette
KingSteven
KochharNeena
KumarSundita
LadwigRenske
LandryJames
LeeDavid
LivingstonJack
LorentzDiana
MallinJason
MarkleSteven
NAME
---------------------------------------------
MarlowJames
MarvinsMattea
MatosRandall
MavrisSusan
McCainSamuel
McEwenAllan
MikkilineniIrene
MourgosKevin
NayerJulia
OConnellDonald
OlsenChristopher
OlsonTJ
OzerLisa
PartnersKaren
PataballaValli
PatelJoshua
PerkinsRandall
PhiltankerHazel
PoppLuis
RajsTrenna
RaphaelyDen
NAME
---------------------------------------------
RogersMichael
RussellJohn
SarchandNandita
SciarraIsmael
SeoJohn
SewallSarath
SmithLindsey
SmithWilliam
StilesStephen
SullivanMartha
SullyPatrick
TaylorJonathon
TaylorWinston
TobiasSigal
TuckerPeter
TuvaultOliver
UrmanJose Manuel
VargasPeter
VishneyClara
VollmanShanta
WalshAlana
NAME
---------------------------------------------
WeissMatthew
WhalenJennifer
ZlotkeyEleni
107 rows selected
然后我们把薪水和名字挂起来
年薪也可以挂，然后起个别名，叫月薪和年薪
SQL> select last_name||salary as month,last_name||12*salary as annual from employees;
MONTH ANNUAL
----------------------------------------------------------------- -----------------------------------------------------------------
King24000 King288000
Kochhar17000 Kochhar204000
De Haan17000 De Haan204000
Hunold9000 Hunold108000
Ernst6000 Ernst72000
Austin4800 Austin57600
Pataballa4800 Pataballa57600
Lorentz4200 Lorentz50400
Greenberg12008 Greenberg144096
Faviet9000 Faviet108000
Chen8200 Chen98400
Sciarra7700 Sciarra92400
Urman7800 Urman93600
Popp6900 Popp82800
Raphaely11000 Raphaely132000
Khoo3100 Khoo37200
Baida2900 Baida34800
Tobias2800 Tobias33600
Himuro2600 Himuro31200
Colmenares2500 Colmenares30000
MONTH ANNUAL
----------------------------------------------------------------- -----------------------------------------------------------------
Weiss8000 Weiss96000
Fripp8200 Fripp98400
Kaufling7900 Kaufling94800
Vollman6500 Vollman78000
Mourgos5800 Mourgos69600
Nayer3200 Nayer38400
Mikkilineni2700 Mikkilineni32400
Landry2400 Landry28800
Markle2200 Markle26400
Bissot3300 Bissot39600
Atkinson2800 Atkinson33600
Marlow2500 Marlow30000
Olson2100 Olson25200
Mallin3300 Mallin39600
Rogers2900 Rogers34800
Gee2400 Gee28800
Philtanker2200 Philtanker26400
Ladwig3600 Ladwig43200
Stiles3200 Stiles38400
Seo2700 Seo32400
Patel2500 Patel30000
MONTH ANNUAL
----------------------------------------------------------------- -----------------------------------------------------------------
Rajs3500 Rajs42000
Davies3100 Davies37200
Matos2600 Matos31200
Vargas2500 Vargas30000
Russell14000 Russell168000
Partners13500 Partners162000
Errazuriz12000 Errazuriz144000
Cambrault11000 Cambrault132000
Zlotkey10500 Zlotkey126000
Tucker10000 Tucker120000
Bernstein9500 Bernstein114000
Hall9000 Hall108000
Olsen8000 Olsen96000
Cambrault7500 Cambrault90000
Tuvault7000 Tuvault84000
King10000 King120000
Sully9500 Sully114000
McEwen9000 McEwen108000
Smith8000 Smith96000
Doran7500 Doran90000
Sewall7000 Sewall84000
MONTH ANNUAL
----------------------------------------------------------------- -----------------------------------------------------------------
Vishney10500 Vishney126000
Greene9500 Greene114000
Marvins7200 Marvins86400
Lee6800 Lee81600
Ande6400 Ande76800
Banda6200 Banda74400
Ozer11500 Ozer138000
Bloom10000 Bloom120000
Fox9600 Fox115200
Smith7400 Smith88800
Bates7300 Bates87600
Kumar6100 Kumar73200
Abel11000 Abel132000
Hutton8800 Hutton105600
Taylor8600 Taylor103200
Livingston8400 Livingston100800
Grant7000 Grant84000
Johnson6200 Johnson74400
Taylor3200 Taylor38400
Fleaur3100 Fleaur37200
Sullivan2500 Sullivan30000
MONTH ANNUAL
----------------------------------------------------------------- -----------------------------------------------------------------
Geoni2800 Geoni33600
Sarchand4200 Sarchand50400
Bull4100 Bull49200
Dellinger3400 Dellinger40800
Cabrio3000 Cabrio36000
Chung3800 Chung45600
Dilly3600 Dilly43200
Gates2900 Gates34800
Perkins2500 Perkins30000
Bell4000 Bell48000
Everett3900 Everett46800
McCain3200 McCain38400
Jones2800 Jones33600
Walsh3100 Walsh37200
Feeney3000 Feeney36000
OConnell2600 OConnell31200
Grant2600 Grant31200
Whalen4400 Whalen52800
Hartstein13000 Hartstein156000
Fay6000 Fay72000
Mavris6500 Mavris78000
MONTH ANNUAL
----------------------------------------------------------------- -----------------------------------------------------------------
Baer10000 Baer120000
Higgins12008 Higgins144096
Gietz8300 Gietz99600
107 rows selected
好玩吧
我们再给名字和jobid连接
SQL> select last_name||job_id as employee from employees;
EMPLOYEE
-----------------------------------
AbelSA_REP
AndeSA_REP
AtkinsonST_CLERK
AustinIT_PROG
BaerPR_REP
BaidaPU_CLERK
BandaSA_REP
BatesSA_REP
BellSH_CLERK
BernsteinSA_REP
BissotST_CLERK
BloomSA_REP
BullSH_CLERK
CabrioSH_CLERK
CambraultSA_MAN
CambraultSA_REP
ChenFI_ACCOUNT
ChungSH_CLERK
ColmenaresPU_CLERK
DaviesST_CLERK
EMPLOYEE
-----------------------------------
De HaanAD_VP
DellingerSH_CLERK
DillySH_CLERK
DoranSA_REP
ErnstIT_PROG
ErrazurizSA_MAN
EverettSH_CLERK
FavietFI_ACCOUNT
FayMK_REP
FeeneySH_CLERK
FleaurSH_CLERK
FoxSA_REP
FrippST_MAN
GatesSH_CLERK
GeeST_CLERK
GeoniSH_CLERK
GietzAC_ACCOUNT
GrantSH_CLERK
GrantSA_REP
GreenbergFI_MGR
GreeneSA_REP
EMPLOYEE
-----------------------------------
HallSA_REP
HartsteinMK_MAN
HigginsAC_MGR
HimuroPU_CLERK
HunoldIT_PROG
HuttonSA_REP
JohnsonSA_REP
JonesSH_CLERK
KauflingST_MAN
KhooPU_CLERK
KingSA_REP
KingAD_PRES
KochharAD_VP
KumarSA_REP
LadwigST_CLERK
LandryST_CLERK
LeeSA_REP
LivingstonSA_REP
LorentzIT_PROG
MallinST_CLERK
MarkleST_CLERK
EMPLOYEE
-----------------------------------
MarlowST_CLERK
MarvinsSA_REP
MatosST_CLERK
MavrisHR_REP
McCainSH_CLERK
McEwenSA_REP
MikkilineniST_CLERK
MourgosST_MAN
NayerST_CLERK
OConnellSH_CLERK
OlsenSA_REP
OlsonST_CLERK
OzerSA_REP
PartnersSA_MAN
PataballaIT_PROG
PatelST_CLERK
PerkinsSH_CLERK
PhiltankerST_CLERK
PoppFI_ACCOUNT
RajsST_CLERK
RaphaelyPU_MAN
EMPLOYEE
-----------------------------------
RogersST_CLERK
RussellSA_MAN
SarchandSH_CLERK
SciarraFI_ACCOUNT
SeoST_CLERK
SewallSA_REP
SmithSA_REP
SmithSA_REP
StilesST_CLERK
SullivanSH_CLERK
SullySA_REP
TaylorSA_REP
TaylorSH_CLERK
TobiasPU_CLERK
TuckerSA_REP
TuvaultSA_REP
UrmanFI_ACCOUNT
VargasST_CLERK
VishneySA_REP
VollmanST_MAN
WalshSH_CLERK
EMPLOYEE
-----------------------------------
WeissST_MAN
WhalenAD_ASST
ZlotkeySA_MAN
107 rows selected
都很简单的
这些考网警的，最基础的查询数据库的操作，你是必须要会的
不然你去做网络警察抓罪犯怎么搞？
数据库你都不会查，请问你怎么搞？？？
文字字符串
就是外加别的字符串
同时可以展示日期，数字
字符串和日期需要加单引号
看例子
SQL> select last_name||' is '||job_id as "employee details" from employees;
employee details
---------------------------------------
Abel is SA_REP
Ande is SA_REP
Atkinson is ST_CLERK
Austin is IT_PROG
Baer is PR_REP
Baida is PU_CLERK
Banda is SA_REP
Bates is SA_REP
Bell is SH_CLERK
Bernstein is SA_REP
Bissot is ST_CLERK
Bloom is SA_REP
Bull is SH_CLERK
Cabrio is SH_CLERK
Cambrault is SA_MAN
Cambrault is SA_REP
Chen is FI_ACCOUNT
Chung is SH_CLERK
Colmenares is PU_CLERK
Davies is ST_CLERK
employee details
---------------------------------------
De Haan is AD_VP
Dellinger is SH_CLERK
Dilly is SH_CLERK
Doran is SA_REP
Ernst is IT_PROG
Errazuriz is SA_MAN
Everett is SH_CLERK
Faviet is FI_ACCOUNT
Fay is MK_REP
Feeney is SH_CLERK
Fleaur is SH_CLERK
Fox is SA_REP
Fripp is ST_MAN
Gates is SH_CLERK
Gee is ST_CLERK
Geoni is SH_CLERK
Gietz is AC_ACCOUNT
Grant is SH_CLERK
Grant is SA_REP
Greenberg is FI_MGR
Greene is SA_REP
employee details
---------------------------------------
Hall is SA_REP
Hartstein is MK_MAN
Higgins is AC_MGR
Himuro is PU_CLERK
Hunold is IT_PROG
Hutton is SA_REP
Johnson is SA_REP
Jones is SH_CLERK
Kaufling is ST_MAN
Khoo is PU_CLERK
King is SA_REP
King is AD_PRES
Kochhar is AD_VP
Kumar is SA_REP
Ladwig is ST_CLERK
Landry is ST_CLERK
Lee is SA_REP
Livingston is SA_REP
Lorentz is IT_PROG
Mallin is ST_CLERK
Markle is ST_CLERK
employee details
---------------------------------------
Marlow is ST_CLERK
Marvins is SA_REP
Matos is ST_CLERK
Mavris is HR_REP
McCain is SH_CLERK
McEwen is SA_REP
Mikkilineni is ST_CLERK
Mourgos is ST_MAN
Nayer is ST_CLERK
OConnell is SH_CLERK
Olsen is SA_REP
Olson is ST_CLERK
Ozer is SA_REP
Partners is SA_MAN
Pataballa is IT_PROG
Patel is ST_CLERK
Perkins is SH_CLERK
Philtanker is ST_CLERK
Popp is FI_ACCOUNT
Rajs is ST_CLERK
Raphaely is PU_MAN
employee details
---------------------------------------
Rogers is ST_CLERK
Russell is SA_MAN
Sarchand is SH_CLERK
Sciarra is FI_ACCOUNT
Seo is ST_CLERK
Sewall is SA_REP
Smith is SA_REP
Smith is SA_REP
Stiles is ST_CLERK
Sullivan is SH_CLERK
Sully is SA_REP
Taylor is SA_REP
Taylor is SH_CLERK
Tobias is PU_CLERK
Tucker is SA_REP
Tuvault is SA_REP
Urman is FI_ACCOUNT
Vargas is ST_CLERK
Vishney is SA_REP
Vollman is ST_MAN
Walsh is SH_CLERK
employee details
---------------------------------------
Weiss is ST_MAN
Whalen is AD_ASST
Zlotkey is SA_MAN
107 rows selected
瞅见了吗
中间我再额外写一个别的字符就很舒服，看起来易读
同时改名字也看起来舒服
SQL> select last_name||55||job_id as "employee details" from employees;
employee details
-------------------------------------
Abel55SA_REP
Ande55SA_REP
Atkinson55ST_CLERK
纯数字中间拼就不用单引号
但是日期是要的
SQL> select last_name||'2022年10月23日'||job_id as "employee details" from employees;
employee details
----------------------------------------------------
Abel2022年10月23日SA_REP
Ande2022年10月23日SA_REP
去除重复行DISTINCT
distinct就是独有的意思
部门id
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
正常情况下，部门id很多重复的
SQL> select department_id from employees;
DEPARTMENT_ID
-------------
90
90
90
60
60
60
60
60
100
100
100
100
100
100
30
30
30
30
30
30
DEPARTMENT_ID
-------------
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
DEPARTMENT_ID
-------------
50
50
50
50
80
80
80
80
80
80
80
80
80
80
80
80
80
80
80
80
80
DEPARTMENT_ID
-------------
80
80
80
80
80
80
80
80
80
80
80
80
80
80
80
80
80
50
50
50
DEPARTMENT_ID
-------------
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
50
10
20
20
40
DEPARTMENT_ID
-------------
70
110
110
107 rows selected
去重重复的部门id，加distinct
出现在select之后紧跟distinct
然后紧跟列名
SQL> select distinct department_id from employees;
DEPARTMENT_ID
-------------
100
30
90
20
70
110
50
80
40
60
10
12 rows selected
你瞅瞅，实际上公司就12个部门，重复的就去掉了
SQL> select distinct department_id,last_name from employees;
DEPARTMENT_ID LAST_NAME
------------- -------------------------
50 Seo
80 Cambrault
80 McEwen
80 Livingston
50 Dellinger
110 Gietz
60 Austin
60 Pataballa
100 Popp
30 Baida
50 Weiss
50 Fripp
50 Mikkilineni
50 Atkinson
50 Olson
50 Philtanker
50 Stiles
80 Bates
80 Kumar
50 Taylor
DEPARTMENT_ID LAST_NAME
------------- -------------------------
50 Sullivan
50 Sarchand
50 Bell
60 Ernst
100 Sciarra
30 Himuro
50 Kaufling
50 Nayer
50 Landry
80 Russell
80 Errazuriz
80 Doran
80 Greene
80 Banda
Grant
50 Everett
50 Feeney
70 Baer
110 Higgins
60 Hunold
100 Faviet
DEPARTMENT_ID LAST_NAME
------------- -------------------------
30 Colmenares
50 Mourgos
50 Patel
80 Partners
80 Smith
50 Fleaur
50 Dilly
50 Walsh
50 Grant
90 King
30 Khoo
50 Bissot
50 Rogers
50 Gee
50 Matos
80 Tucker
80 Olsen
80 King
50 Cabrio
50 McCain
20 Hartstein
DEPARTMENT_ID LAST_NAME
------------- -------------------------
90 Kochhar
90 De Haan
100 Greenberg
50 Ladwig
80 Zlotkey
80 Hall
80 Sewall
80 Vishney
80 Ande
80 Taylor
80 Johnson
50 Geoni
40 Mavris
100 Chen
30 Raphaely
50 Vollman
50 Marlow
50 Mallin
50 Rajs
80 Bernstein
80 Tuvault
DEPARTMENT_ID LAST_NAME
------------- -------------------------
80 Sully
80 Marvins
80 Lee
80 Bloom
80 Fox
80 Hutton
50 Perkins
50 Jones
60 Lorentz
100 Urman
30 Tobias
50 Markle
50 Davies
50 Vargas
80 Ozer
80 Abel
50 Bull
50 Chung
50 Gates
50 OConnell
10 Whalen
DEPARTMENT_ID LAST_NAME
------------- -------------------------
20 Fay
105 rows selected
你加上last_name
你会发现，部门又有重复了
啥意思？？？
其实distinct是针对的，部门id和名字，都重复的话，再去重
其实distinct是针对的，部门id和名字，都重复的话，再去重
其实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 288000
101 Kochhar 204000
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, 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> 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
27 rows selected
来源于表格
这个是employees表
我们去查信息
SQL> select employee_id,last_name,job_id,hire_date as startdate from employees;
EMPLOYEE_ID LAST_NAME JOB_ID STARTDATE
----------- ------------------------- ---------- -----------
100 King AD_PRES 2003/6/17
101 Kochhar AD_VP 2005/9/21
102 De Haan AD_VP 2001/1/13
这都好说的
这个文章的好处，就是带你一步步，学习数据库，sql语言，完整地搞定数据库
SQL> select distinct job_id from employees;
JOB_ID
----------
AC_ACCOUNT
AC_MGR
AD_ASST
AD_PRES
AD_VP
FI_ACCOUNT
FI_MGR
HR_REP
IT_PROG
MK_MAN
MK_REP
PR_REP
PU_CLERK
PU_MAN
SA_MAN
SA_REP
SH_CLERK
ST_CLERK
ST_MAN
19 rows selected
job分级，19级，这不就是华为的offer等级吗
jobid越高，工资越高
SQL> select last_name||', '||job_id from employees;
LAST_NAME||','||JOB_ID
-------------------------------------
Abel, SA_REP
Ande, SA_REP
Atkinson, ST_CLERK
链接字符串，好说
数据操作语言DML
除查询语言之外的操作
【本节，下一篇文章继续讲】
事物处理语言TCL
事物是很重要的概念，现在简单介绍
除了查询之外，针对DML语句，他们会受到一个事物的控制，预留返回的操作
后续再说细节【本节，下一篇文章继续讲】
数据控制语言DCL
权限的授予和撤销
【本节，下一篇文章继续讲】
数据定义语言：DDL
帮助创建数据库对象
创建表啥的
【本节，下一篇文章继续讲】
总结
提示：重要经验：
1）数据查询语句，选择列，选择表，其余操作都好说
2）学好oracle，即使经济寒冬，整个测开offer绝对不是问题！同时也是你考公网络警察的必经之路。
3）笔试求AC，可以不考虑空间复杂度，但是面试既要考虑时间复杂度最优，也要考虑空间复杂度最优。
相关文章
- 使用navicat premium将数据库从Oracle迁移到SQL Server，或从Oracle迁移到MySQL
- 【Oracle】oracle的LAG和LEAD分析函数
- Linux 下安装oracle 数据库的准备
- Oracle VM VirtualBox - 启用 VT-x/AMD-V 支持
- struts+hibernate+oracle+easyui实现lazyout组件的简单案例——OpSessionview实现
- Oracle数据库基本概念理解（3）
- 《oracle每天一练》Oracle冷备份与数据恢复
- 8个DBA最常用的监控Oracle数据库的常用shell脚本
- cx_Oracle连接oracle数据库
- 2013年7月14日-Java连接Oracle数据库
- 【Teradata SQL】数据库中查询 一个字段包含另一个字段sql
- cx_Oracle连接oracle数据库
- PLSQL连Oracle数据库Could not load "……binoci.dll"
- 连接ORACLE数据库，是不是必须要安装oracle客户端的运行时
- Oracle数据库冷备份与恢复（救命稻草）
- Atitit oracle新特性5 6 7 8 9 10 11 12 18 19 20 attilax总结 目录 1.1. :ora 20c1 1.2. Oracle Database 19c 的
- Atitit.mysql oracle with as模式临时表模式 CTE 语句的使用,减少子查询的结构性 mssql sql server..
- atitit.sql server2008导出导入数据库大的表格文件... oracle mysql
- 在Oracle 11.2的数据库中建表时遇到 RESULT_CACHE (MODE DEFAULT) ORA-00922: missing or invalid option
- Oracle PL/SQL中的循环处理(sql for循环)
- Oracle数据库之表空间（tablespace）---面试再也不害怕系列之一
- Linux centos7新建Oracle数据库，在进度条百分之六十八的时候报错ins_ctx.mk编译错误的解决方法
- sql的介绍——SQL Server数据库管理系统
- Oracle数据库问题必知，最全的错误码（BUG）和解决方式汇总
- 解密 Oracle 数据库 SQL 执行历史：掌握多种实现方式，轻松实现 SQL 监控与性能优化
- Oracle的学习心得和知识总结（十）|Oracle数据库PL/SQL语言循环控制语句之LOOP语句技术详解
- Oracle的学习心得和知识总结（九）|Oracle数据库PL/SQL语言条件选择语句之IF和CASE语句技术详解
- Oracle的学习心得和知识总结（八）|Oracle数据库PL/SQL语言顺序控制语句之GOTO和NULL语句技术详解