ocp培训第三天

本文详细介绍了在Oracle环境下进行数据库管理和SQL操作的步骤,包括启动和关闭数据库实例、使用SQL*PLUS进行查询、编辑及排序数据。讲解了SQL的基础语法,如SELECT语句、子查询、连接查询、条件过滤以及数据插入、更新和删除操作。同时,还探讨了内外连接、子查询在WHERE和FROM子句中的应用,以及如何进行Top-N查询。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

课前准备

解压 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

http://ip:5560/isqlplus

 

登录名: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,ConversionDate

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 AM15:45:32 PM

Add character strings by enclosing them in double quotation marks:

DD "of" MONTH12 of OCTOBER

Number suffixes spell out numbers:

ddspthfourteenth

--------------------------------------
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;

*******************************************************************************


 


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值