本文详细介绍了在Oracle环境下进行数据库管理和SQL操作的步骤，包括启动和关闭数据库实例、使用SQL*PLUS进行查询、编辑及排序数据。讲解了SQL的基础语法，如SELECT语句、子查询、连接查询、条件过滤以及数据插入、更新和删除操作。同时，还探讨了内外连接、子查询在WHERE和FROM子句中的应用，以及如何进行Top-N查询。
课前准备
解压 Red Hat Enterprise Linux 5.6&Oracle10g&SQL.rar
VMware 安装解压后的linux （里面有配好的oracle）
SQL*PLUS执行环境
命令存放路径: $ORACLE_HOME/bin
oracle安装过程见：
Oracle10g Install for Redhat5.6.docx
打开新linux，登录oracle
#su - oracle
（oracle切换到root的话，直接用 'su -' 就可以了）
在oracle用户中打开终端，执行如下操作
1) [oracle@localhost ~]$lsnrctl start #启动监听
2) [oracle@localhost ~]$sqlplus / as sysdba #进入sql连接
3) 在sql连接中执行 SQL>startup #启动oracle数据库实例
4） SQL>select sysdate from dual；，若出现当前系统日期，则表示数据库启动成功。
编辑命令：[L]IST , [A]PPEND ,[C]HANGE , [I]NPUT , DEL , / （执行），EDITOR
SQL> DEFINE_EDITOR=vi
SQL> select first_name from employees;
SQL> L (查看缓存中的sql,可用edit命令进行编辑，ctrl+d退出来【注意先设置：DEFINE _EDITOR=vi】)
1* select first_name from employees
SQL> A wheredepartment_id = 90 (在原先的基础上加了个限定条件，显示如下，可用‘/’直接进行执行)
1* select first_name from employees where department_id = 90
SQL> C/90/80/
1* select first_name from employees wheredepartment_id = 80
SQL> I (接着上面的语句添加sql语句)
2 where department_id=90;
SQL> /
SQL> DEL (执行一个del会删除一条语句)
SQL> @/home/oracle/sqldemo/summit1.sql --执行sql文件
SQL> spool info (新建一个info文件保存后续操作)
SQL> spool off 退出
在Oracle主目录下回产生一个文件info.lst
----??
SQL> ^? ^H
stty erase ^? | ^H
-------
SQL> clear screen --清屏
-----------------------------iSQL*PLUS执行环境
[oracle@Redhat ~]$isqlplusctl start
登录名：system
密码：shjdora
注：在外部访问，要关闭Linux操作系统防火墙。（setup进行操作）
-------------------PLSQL Developer开发工具& sqldeveloper开发工具
大家对sql应该都比较熟了，以下是一些例子，ppt里没什么精华，去掉了。。。
-----------------------PPT1:RetrievingData Using the SQLSELECTStatement
*******************************************************************************
select first_name, *
from employees;
ERROR atline 1:
ORA-00936:missing expression
*******************************************************************************
关键字名称一般为大写，其他一般为小写（增强可读性）。
SELECT first_name,last_name
FROM employees;
**********************************************************************
select first_name,salary
from employees
where salary+100< 5000;
注：运算也可以用在WHERE条件上,除了From子句都可以。
*******************************************************************************
select sysdate,sysdate+1, sysdate-1, systimestamp, systimestamp+1, systimestamp-1 from dual;
*******************************************************************************
select salary "Sal($)"
from employees;
*******************************************************************************
select department_name ||
', it's assigned Manager Id: '
|| manager_id
AS "Department and Manager"
from departments;
ERROR:
ORA-01756:quoted string not properly terminated
select department_name ||
q'[, it'sassigned Manager Id: ]'
||manager_id
AS"Department and Manager"
from departments;
注：这里的转义符可以是[ ], { }, ( ), or < >。
等价于
select department_name ||
q'\, it'sassigned Manager Id: \'
||manager_id
AS"Department and Manager"
from departments;
等价于
select department_name ||
', it''sassigned Manager Id: '
||manager_id
AS"Department and Manager"
from departments;
*******************************************************************************
select department_id, job_id
from employees;
比较
select distinct department_id, job_id
from employees;
注：这里会把(department_id,job_id)一起重复的数据过滤。
*******************************************************************************
PPT2:Restricting and Sorting Data
SELECT last_name,job_id, department_id
FROM employees
WHERE last_name = 'King' ;
比较
SELECT last_name,job_id, department_id
FROM employees
WHERE last_name = 'KING' ;
SELECT last_name,job_id, hire_date
FROM employees
WHERE hire_date='17-JUN-87';
比较
SELECT last_name, job_id, hire_date
FROM employees
WHERE hire_date='17-JUN-1987';
比较
select LAST_NAME,HIRE_DATE
from employees
where HIRE_DATE='1997-08-17';
ERROR at line 3:
ORA-01861: literal does not match format string
注：字符是大小写敏感的，日期是格式敏感的。
*******************************************************************************
SELECT last_name,salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500 ;
等价于
SELECT last_name,salary
FROM employees
WHERE salary>= 2500 AND salary <= 3500;
*******************************************************************************
SELECT employee_id, last_name, job_id
FROM employees WHERE job_id LIKE '%SA_%'
比较
SELECTemployee_id, last_name, job_id
FROM employees WHERE job_id LIKE '%SA\_%' ESCAPE '\';
*******************************************************************************
SELECT last_name, manager_id
FROM employees
WHERE manager_id IS NULL ;
比较
SELECT last_name, manager_id
FROM employees
WHERE manager_id = NULL ;
注：IS NULL条件用于空值测试,不能用"="等号测试空值 ，因为null不能等于或不等于任何值。
*******************************************************************************
SELECT last_name, job_id
FROM employees
WHERE job_id NOT IN ('IT_PROG','ST_CLERK', 'SA_REP') ;
等价于
SELECT last_name, job_id
FROM employees
WHERE job_id !='IT_PROG' or job_id != 'ST_CLERK' or job_id != 'SA_REP';
The symbols != and ^= can also represent the not equal to condition.
*******************************************************************************
SELECT last_name, job_id,department_id, hire_date
FROM employees
ORDER BY department_id;
等价于
SELECT last_name, job_id,department_id, hire_date
FROM employees
ORDER BY 3;
*******************************************************************************
SELECT first_name,commission_pct
from employees
order by commission_pct asc;
注：null 这里会认为是无穷大。
*******************************************************************************
SQL> insert into test values (&1,&2); ----要先建个test表
Enter value for 1: 1
Enter value for 2: 1
old 1: insert into test values (&1,&2)
new 1: insert into test values (1,1)
1 row created.
SQL> select *from test;
COL1 COL2
--------------------
1 1
*******************************************************************************
SQL> insert into test values (&&1,&&2);
SQL>/
*******************************************************************************
DEFINE employee_num = 200;
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;--因为前面有deffine employee_number,这里就不需要输入啦
UNDEFINE employee_num;--记得要把原先设定的值去掉
SET VERIFY ON/OFF （display of the substitution variable, both before and after iSQL*Plus replaces substitution variables with values）
显示/不显示
SHOW ALL --显示系统变量类型
*******************************************************************************
Not only can you use the substitution variables in the WHERE clause of a SQL statement, but these variables can also be used to substitute for column names, expressions, or text.
Example
SELECT employee_id, last_name, job_id,&column_name
FROM employees
WHERE &condition
ORDER BY &order_column ;
The slide example displays the employee number, name, job title, and any other column that is specified by the user at run time, from the EMPLOYEES table. For each substitution variable in the SELECT statement, you are prompted to enter a value, and you then click the Continue button to proceed.
If you do not enter a value for the substitution variable, you get an error when you execute the preceding statement.
PPT3:Using Single-Row Functions to Customize Output
*******************************************************************************
select last_name||job_id AS "Employees"
from employees;
等价于
select CONCAT(last_name,job_id) AS "Employees"
from employees;
*******************************************************************************
SQL> select lpad('test',10) from dual;-左侧加10个空
LPAD('TEST
----------
test
SQL> select rpad('test',10) from dual;--右侧加10个空
RPAD('TEST
----------
test
SQL> select lpad('test',2) from dual;
LP
--
Te
SQL> select rpad('test',2) from dual;
RP
--
Te
lpad(string,n,[pad_string])：其中string1是需要粘贴字符的字符串
padded_length是返回的字符串的数量，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成padded_length;
pad_string是个可选参数，这个字符串是要粘贴到string1的左边，如果这个参数未写，lpad函数将会在string1的左边粘贴空格。
*******************************************************************************
SQL> select substr('oracle10g', 4) from dual;
SUBSTR
------
cle10g
*******************************************************************************
SQL> select trim(' 12 3 ') from dual;
TRIM
----
12 3
*******************************************************************************
ROUND(for number)
ROUND函数返回处理后的数值，该函数对指定小数前或后的部分做相应舍入选择处理。
其具体的语法格式如下
ROUND（number[,decimals]）
其中：
number需四舍五入处理的数值
decimals四舍五入 , 小数取几位 ( 预设为 0 )
round(123.456, 0)=123
round(123.456, 1)=123.5
round(123.456, 2)=123.46
round(123.456, 3)=123.456
TRUNC(for number)
TRUNC函数返回处理后的数值，其工作机制与ROUND函数极为类似，只是该函数不对指定小数前或后的部分做相应舍入选择处理，而统统截去。
其具体的语法格式如下
TRUNC（number[,decimals]）
其中：
number 待做截取处理的数值
decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
注意：第二个参数可以为负数，表示为小数点左边指定位数后面的部分截去，即均以0记。
*******************************************************************************
SQL> select mod(2,3) from dual;
MOD(2,3)
----------
2
*******************************************************************************
月的3字母缩写：
JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC
周：
SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
*******************************************************************************
SELECT last_name, hire_date
FROM employees
WHERE hire_date < to_date('01-FEB-2088','DD-MON-YY');
比较
SELECT last_name, hire_date
FROM employees
WHERE hire_date < to_date('01-FEB-1988','DD-MON-YY');
比较
SELECT last_name, hire_date
FROM employees
WHERE hire_date < to_date('01-FEB-88','DD-MON-YY');
*******************************************************************************
select first_name, salary
from employees
where salary='8300';
比较
select first_name, salary
from employees
where salary=8300;
select first_name, hire_date
from employees
where hire_date='07-JUN-94';
比较
select first_name, hire_date
from employees
where hire_date=to_date('07-JUN-1994',DD-MON-YY’);--结果是'07-JUN-94'
*******************************************************************************
Single-row functions：Character，General，Number，Conversion，Date
Character functions: Accept character input and can return both character and number values
Number functions: Accept numeric input and return numeric values
Date functions: Operate on values of the DATE data type (All date functions return a value of DATE data type except the MONTHS_BETWEEN function, which returns a number.)
Conversion functions: Convert a value from one data type to another
General functions:
NVL
NVL2
NULLIF
COALESCE
CASE
DECODE
Character Functions
Single-row character functions accept character data as input and can return both character and numeric values. Character functions can be divided into the following:
Case-manipulation functions （LOWER，UPPER，INITCAP）
Character-manipulation functions （CONCAT，SUBSTR，LENGTH，INSTR，LPAD | RPAD，TRIM，REPLACE）
SELECT 'The job id for '||UPPER(last_name)||' is '
||LOWER(job_id) ,'and name is'||INITCAP(employee_name) AS "EMPLOYEE DETAILS"
FROM employees;
Function Result
CONCAT('Hello', 'World') HelloWorld
SUBSTR('HelloWorld',1,5) Hello
LENGTH('HelloWorld') 10
INSTR('HelloWorld', 'W') 6
LPAD(salary,10,'*') *****24000
RPAD(salary, 10, '*') 24000*****
REPLACE ('JACK and JUE','J','BL') BLACK and BLUE
TRIM('H' FROM 'HelloWorld') elloWorld
Date Functions
Date functions operate on Oracle dates. All date functions return a value of DATE data type except MONTHS_BETWEEN, which returns a numeric value.
MONTHS_BETWEEN(date1, date2): Finds the number of months between date1 and date2. The result can be positive or negative. If date1 is later than date2, the result is positive; if date1 is earlier than date2, the result is negative. The noninteger part of the result represents a portion of the month.
ADD_MONTHS(date, n): Adds n number of calendar months to date. The value of n must be an integer and can be negative.
NEXT_DAY(date, 'char'): Finds the date of the next specified day of the week ('char') following date. The value of char may be a number representing a day or a character string.
LAST_DAY(date): Finds the date of the last day of the month that contains date
ROUND(date[,'fmt']): Returns date rounded to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is rounded to the nearest day.
TRUNC(date[, 'fmt']): Returns date with the time portion of the day truncated to the unit that is specified by the format model fmt. If the format model fmt is omitted, date is truncated to the nearest day.
---------------------------------------------------------------------------
SELECT employee_id, hire_date,
MONTHS_BETWEEN (SYSDATE, hire_date) TENURE,
ADD_MONTHS (hire_date, 6) REVIEW,
NEXT_DAY (hire_date, 'FRIDAY'), LAST_DAY(hire_date)
FROM employees
WHERE MONTHS_BETWEEN (SYSDATE, hire_date) < 36;
SELECT employee_id, hire_date,
ROUND(hire_date, 'MONTH'), TRUNC(hire_date, 'MONTH'),ROUND(hire_date, 'YEAR'), TRUNC(hire_date, 'YEAR')
FROM employees
WHERE hire_date LIKE '%97';
------------------------------------------------------------------------------
Data type conversion
In addition to Oracle data types, columns of tables in an Oracle database can be defined using ANSI, DB2, and SQL/DS data types. However, the Oracle server internally converts such data types to Oracle data types.
In some cases, the Oracle server uses data of one data type where it expects data of a different data type. When this happens, the Oracle server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Oracle server or explicitly by the user.
Implicit data type conversions work according to the rules that are explained in the next two slides.
Explicit data type conversions are done by using the conversion functions. Conversion functions convert a value from one data type to another. Generally, the form of the function names follows the convention data type TO data type. The first data type is the input data type; the second data type is the output.
Note: Although implicit data type conversion is available, it is recommended that you do explicit data type conversion to ensure the reliability of your SQL statements.
Implicit data type conversions
From To
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
NUMBER VARCHAR2
DATE VARCHAR2
Explicit data type conversions
NUMBER --TO_CHAR-->CHARACTER--TO_DATE-->DATE
DATE --TO_CHAR-->CHARACTER--TO_NUMBER-->NUMBER
Elements of the Date Format Model
Element Result
YYYY Full year in numbers
YEAR Year spelled out (in English)
MM Two-digit value for month
MONTH Full name of the month
MON Three-letter abbreviation of the month
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the month
--------------------------------------------------
Select to_char(salary,’$999,999.00’) from emp;
---------------------------------------------------------------------
Elements of the Date Format Model
Time elements format the time portion of the date:
| HH24:MI:SS AM | 15:45:32 PM |
Add character strings by enclosing them in double quotation marks:
| DD "of" MONTH | 12 of OCTOBER |
Number suffixes spell out numbers:
| ddspth | fourteenth |
--------------------------------------
SELECT last_name,
TO_CHAR(hire_date,
'fmDdspth "of" Month YYYY fmHH:MI:SS AM')
HIREDATE
FROM employees;
------------------------------------------
SELECT TO_CHAR(salary, '$99,999.00') SALARY
FROM employees
WHERE last_name = 'Ernst';
--------------------------------------------------------
SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE('May 24, 1999', 'fxMonth DD, YYYY');
-------------------------------------------------------------
RR Date Format Element
SELECT TO_CHAR(NEXT_DAY(ADD_MONTHS
(hire_date, 6), 'FRIDAY'),
'fmDay, Month DDth, YYYY')
"Next 6 Month Review"
FROM employees
ORDER BY hire_date;
-----------------
General Functions
NVL (expr1, expr2) expr1 is the source value or expression that may contain a null,expr2 is the target value for converting the null
NVL2 (expr1, expr2, expr3) expr1 is the source value or expression that may contain null,expr2 is the value that is returned if expr1 is not null,expr3 is the value that is returned if expr1 is null
NULLIF (expr1, expr2) 两值比较，相等返回null，不想等返回expr1
COALESCE (expr1, expr2, ..., exprn) expr1 returns this expression if it is not null,expr2 returns this expression if the first expression is null and this expression is not null,exprn returns this expression if the preceding expressions are null
--------------------------
SELECT last_name,
COALESCE(manager_id,commission_pct, -1) comm
FROM employees
ORDER BY commission_pct;
---------------------
Using the CASE Expression
SELECT last_name,salary,
(CASE WHEN salary<5000 THEN 'Low'
WHEN salary<10000 THEN 'Medium'
WHEN salary<20000 THEN 'Good'
ELSE 'Excellent'
END) qualified_salary
FROM employees;
----------------------------
Using the DECODE Function --相当于if then else
SELECT last_name, job_id, salary,
DECODE(job_id, 'IT_PROG', 1.10*salary,
'ST_CLERK', 1.15*salary,
'SA_REP', 1.20*salary,
salary)
REVISED_SALARY
FROM employees;
---------------------------------
PPT4:ReportingAggregated Data Using the Group Functions
Types of Group Functions
AVG
COUNT
MAX （MAX and MIN cannot be used with LOB or LONG data types）
MIN
STDDEV
SUM
VARIANCE
*******************************************************************************
SELECT department_id, AVG(salary)
FROM employees
WHERE salary > 8000
GROUP BY department_id;
SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
Having salary > 8000;
ERROR at line 4:
ORA-00979: not a GROUP BY expression
*******************************************************************************
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY SUM(salary);
等价于
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY PAYROLL;
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
GROUP BY job_id
HAVING PAYROLL > 13000
ORDER BY PAYROLL;
ERROR at line 5:
ORA-00904: "PAYROLL": invalid identifier
SELECT job_id, SUM(salary) PAYROLL
FROM employees
WHERE job_id NOT LIKE '%REP%'
ORDER BY PAYROLL
GROUP BY job_id
HAVING SUM(salary) > 13000;
ERROR at line 5:
ORA-00933: SQL command not properly ended
*******************************************************************************
--求部门号为90的员工的平均工资，显示部门号，平均工资
select department_id, avg(salary)
from employees
where department_id=90
group by department_id;
等价于
select department_id, avg(salary)
from employees
group by department_id
having department_id=90;
*******************************************************************************
Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句：
如果是GROUP BY ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作。
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY (department_id, job_id) ;
+
SELECT department_id, SUM(salary)
FROM employees
GROUP BY (department_id) ;
+
SELECT SUM(salary)
FROM employees ;
=
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY rollup(department_id, job_id) ;
*******************************************************************************
如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作。
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY (department_id, job_id) ;
+
SELECT department_id, SUM(salary)
FROM employees
GROUP BY (department_id) ;
+
SELECT job_id, SUM(salary)
FROM employees
GROUP BY (job_id) ;
+
SELECT SUM(salary)
FROM employees ;
=
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY cube(department_id, job_id) ;
*******************************************************************************
GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。
SELECT grouping(department_id), department_id dept_id, SUM(salary)
FROM employees
GROUP BY rollup(department_id) ;
SELECT case grouping(department_id)
when 0 then to_char(department_id)
else 'total'
end dept_id, SUM(salary)
FROM employees
GROUP BY rollup(department_id) ;
*******************************************************************************
Lesion 5 Displaying Data from Multiple Tables
*******************************************************************************
Types of Joins
Joins that are compliant with the SQL:1999 standard include the following:
Cross joins
Natural joins
USING clause
Full (or two-sided) outer joins
Arbitrary join conditions for outer joins
-------------------------------------------------------------------
USING Clause
Natural joins use all columns with matching names and data types to join the tables. The USING clause can be used to specify only those columns that should be used for an equijoin. The columns that are referenced in the USING clause should not have a qualifier (table name or alias) anywhere in the SQL statement.
For example, the following statement is valid:
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;
The following statement is invalid because the LOCATION_ID is qualified in the WHERE clause:
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE d.location_id = 1400;
ORA-25154: column part of USING clause cannot have qualifier
The same restriction also applies to NATURAL joins. Therefore, columns that have the same name in both tables must be used without any qualifiers.
-------------------------
SELECT table1.column, table2.column
FROM table1
[NATURAL JOIN table2] | ---基于相同的列名(有相同名字的所有列)连接两个表
[JOIN table2 USING (column_name)] | ---执行一个基于列名的等值连接
[JOIN table2 ON (table1.column_name = table2.column_name)] | ---执行一个基于在ON子句中的条件的等值连接
[LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name = table2.column_name)] | ---左、右、全外连接
[CROSS JOIN table2] ---从两个表中返回笛卡尔乘积;
*******************************************************************************
SELECT department_id, department_name,location_id, city
FROM departments
WHERE department_id IN (20, 50)
NATURAL JOIN locations;
ERROR at line 4:
ORA-00933: SQL command not properly ended
SELECT department_id, department_name, location_id, city
FROM departments d, locations l
WHERE d.location_id = l.location_id and department_id IN (20, 50);
ERROR at line 1:
ORA-00918: column ambiguously defined
SELECT department_id, department_name, d.location_id, city
FROM departments d, locations l
WHERE d.location_id = l.location_id and department_id IN (20, 50);
SELECT d.department_id, d.department_name, l.location_id, l.city
FROM departments d
NATURAL JOIN locations l
WHERE d.department_id IN (20, 50);
ERROR at line 1:
ORA-25155: column used in NATURAL join cannot have qualifier
SELECT d.department_id, d.department_name, location_id, l.city
FROM departments d
NATURAL JOIN locations l
WHERE d.department_id IN (20, 50);
*******************************************************************************
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE location_id = 1400;
SELECT l.city, d.department_name
FROM locations l JOIN departments d USING (location_id)
WHERE d.location_id = 1400;
ERROR at line 3:
ORA-25154: column part of USING clause cannot have qualifier
*******************************************************************************
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
AND e.manager_id = 149 ;
等价于
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE d.manager_id = 149;
*******************************************************************************
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM departments d right OUTER JOIN employees e
ON (e.department_id = d.department_id) ;
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
*******************************************************************************
SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM departments d left OUTER JOIN employees e
ON (e.department_id = d.department_id) ;
等价于
SELECT e.last_name, e.department_id, d.department_name
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
************************注意using和on区别*************************************
SELECT e.employee_id, e.last_name,
d.location_id, department_id
FROM employees e JOIN departments d
USING (department_id) ;
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);注意要加前缀
*******************************************************************************
SELECT e.last_name emp, m.last_name mgr
FROM employees e JOIN employees m
ON (e.manager_id = m.employee_id);
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
WHERE e.manager_id = 149;
SELECT employee_id, city, department_name
FROM employees e
JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON d.location_id = l.location_id;
SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
*******************************************************************************
INNER Versus OUTER Joins
Joining tables with the NATURAL JOIN, USING, or ON clauses results in an inner join. Any unmatched rows are not displayed in the output. To return the unmatched rows, you can use an outer join. An outer join returns all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other table satisfy the join condition.
There are three types of outer joins:
LEFT OUTER
RIGHT OUTER
FULL OUTER
-----------------------
LEFT OUTER JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM employees e LEFT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
RIGHT OUTER JOIN
SELECT e.last_name, e.department_id, d.department_name
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
FULL OUTER JOIN
SELECT e.last_name, d.department_id, d.department_name
FROM employees e FULL OUTER JOIN departments d
ON (e.department_id = d.department_id) ;
Creating Cross Joins
The CROSS JOIN clause produces the cross-product of two tables.
This is also called a Cartesian product between the two tables.
SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;
*******************************************************************************
Lesion 6 Subqueries to Solve Queries
*******************************************************************************
SELECT last_name, salary, department_id
FROM employees
WHERE salary IN (SELECT MIN(salary)
FROM employees
GROUP BY department_id);
比较
SELECT min(last_name), min(salary), department_id
FROM employees
GROUP BY department_id;
比较
SELECT last_name, min(salary), department_id
FROM employees
GROUP BY department_id,last_name;
*******************************************************************************
ALL：必须要符合子查询查询结果的所有值。
ANY：只要符合子查询结果中的一个值即可。
ANY 运算符
<ANY是小于最大值
>ANY是大于最小值
=ANY 等同于 IN
ALL 运算符
>ALL 是大于最大值
<ALL 是小于最小值。
*******************************************************************************
多列子查询：
SELECT first_name,department_id,job_id,salary
FROM employees
WHERE (department_id,job_id)=(SELECT department_id,job_id
FROM employees
WHERE first_name='Neena');
注：父查询的WHERE条件要和子查询的字段保持一致。
*******************************************************************************
EXISTS子查询：
select first_name,department_id
from employees e
where not exists(select ‘X’
from departments d
where e.department_id=d.department_id and d.department_id=90);
注：“外部驱动内部”，即对于外查询的每个候选行，内查询只要有一个返回行被找到，就不必在内查询中继续查找，内查询到底返回什么值并不重要， 为了提高性能，可以让内查询返回一个常量值。
*******************************************************************************
FROM子句中使用子查询：
SELECT first_name,job_id,salary
FROM employees emp,(SELECT department_id,avg(salary) as avgsal
FROM employees
GROUP BY department_id) dept
WHERE emp.department_id=dept.department_id AND salary>dept.avgsal;
*******************************************************************************
With语句
使用WITH AS 语句可以为一个子查询语句块定义一个名称，使用这个子查询名称可以在查询语句中引用这个子查询。
WITH
<alias_one> AS (subquery_sql_statement),
<alias_two> AS (sql_statement_from_alias_one)
……
SELECT <column_name_list>
FROM <alias_one>, <alias_two>
WHERE <join_condition>;
注：
1、在同级select前有多个查询定义的时候，第1个用with，后面的不用with，并且用逗号隔开。
2、最后一个with 子句与下面的查询之间不能有逗号，只通过右括号分割，with 子句的查询必须用括号括起来
3、如果定义了with子句，而在查询中不使用，那么会报ora-32035 错误：未引用在with子句中定义的查询名。
4、前面的with子句定义的查询在后面的with子句中可以使用。但是一个with子句内部不能嵌套with子句。
5、with查询的结果列有别名，引用的时候必须使用别名或*。
WITH
A AS (SELECT * FROM DUAL),
B AS (SELECT * FROM DUAL)
SELECT * FROM A, B;
WITH
DEPT_COSTS AS
(SELECT D.DNAME, SUM(E.SAL) DEPT_TOTAL
FROM DEPT D, EMP E
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME),
AVE_COST AS
(SELECT SUM(DEPT_TOTAL) / COUNT(*) AVG_SUM FROM DEPT_COSTS)
SELECT *
FROM DEPT_COSTS DC
WHERE DC.DEPT_TOTAL > (SELECT AC.AVG_SUM FROM AVE_COST AC)
with
sql1 as (select * from t2),
sql2 as (select * from t3)
select * from sql1
union
select * from sql2
where id in(2,3);
注：该WHERE条件只对对应的那个SELECT子句有效。
*******************************************************************************
Top-N
Top-N 查询是寻找一列中的n个最大或最小值，例如：
–销售最好的前10 位产品是什么?
–销售最差的前10 位产品是什么?
Top-N 的基本语法：
SELECT 列名列表,ROWNUM AS RANK
FROM (SELECT 列名列表 FROM 表名 ORDER BY 被分析的列 [ASC|DESC])
WHERE ROWNUM <= n ;
Top-N 分析查询语句的两个基本构成：
–在子查询中使用order by字句来进行排序
–在主查询中限制结果集能显示的行数(使用 ROWNUM 伪列)
示例：
SELECT rownum as SENIOR,t.last_name, t.hire_date
FROM (SELECT last_name,hire_date
FROM employees
ORDER BY hire_date) t
WHERE rownum <= 4;
查询工资排名在第10位到第20位的员工，显示姓名和工资(即分页查询的另外一种形式)
select t2.name, t2.sal
from (select rownum rw, t1.name name, t1.sal sal
from (select first_name name, salary sal
from s_emp
order by salary desc) t1
where rownum<=20) t2
where t2.rw between 10 and 20;
*******************************************************************************
扫一扫
703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
