例如： contrat('Hello' ,'World') HelloWorld
substr('HelloWorld',1,5) Hello
length('HelloWord') 10
instr('HelloWorld', 'W' ) 6
lpad(salary,10, '*') ****24000
rpad(salary,10 , '*') 24000****
0.如果是删除某个表的所有数据，并且不需要回滚，使用 TRUNCATE 就ok了
SQL> truncate table table_name;
导入csv文件 命令： sqlldr userid=mys/tiger control=d:\test.ctl log=d:\test.log 命令行（cmd下）
test.ctl格式如下
************************************************************
load data
infile 'D:\2000W\2000W\1-200W.csv'
replace into table mys.shenfenzheng
fields terminated by ','
(Name,CardNo,Descriot）
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession1-------
1.在select 字句种使用关键字'distinct' 删除重复行
例如： select distinct department id from employees;
2.使用describe 命名，表示表结构
desc[ribe] tablename
例如： describe employees
lession2-------
3. 比较运算
between ... and ... 在两个值之间（包含边界）
in(set) 等于值列表中的一个(exist效率高一些)
like 模糊查询(%代表一个或多个字符，_代表一个字符)
is null 空值
4. and 并 or 或 not 否 not in ('aa', 'bb' , 'cc' )
5. 运算优先级：算术运费符>连接符>比较符> is [not] null like [not] in >[not] between >not > and >or
6. order by asc/desc
lession3-------
7.大小写空值函数 lower / upper / initcap (第一个字符大写)
例如 lower('SQL Course') sql course
upper('SQL Course') SQL COURSE
initcap('SQL Course') Sql Course
例如： lower(last_name) = 'higgins'
8. 字符控制函数 concat / substr /length /instr / lpad / rpad / trim / replace
trim('H' from 'HelloWorld' ) elloWorld
9. round：四舍五入 trunc：截断 mod ：求余
例如：round(45.926,2) 45.93
trunc(45.926,2) 45.92
mod(1600,300) 100
10. 函数sysdate 返回：日期 时间
months_between 两个日期相差的月数
ADD_MONTHS 向指定日期中加上若干月数
NEXT_DAY 本月的最后一天
LAST_DAY 指定日期的下一个日期
ROUND 日期四舍五入
TRUNC 日期截断
11. to_char(date,'format_model') 对日期转换
to_char(number,'format_model') 对数字转换
to_number(char[, 'format_model'])
to_date(char[, 'format_model'])
12. nvl 将空值转换为一个已知的值 可以使用日期、字符、数字
例如：nvl (commission_pct,0) nvl(hire_date,'01-jan-97') nvl(job_id,'no job yet')
nvl2 (commission_pct , 'SAL+COMM', 'SAL') 第一个表达式为空，返回第三个值，否则返回第二个值
nullif (a,b) 第一个表达式与第二个表达式相同，返回null 否则返回第一个值
COALESCE(COMMISSION_PCT , SALARY , 10 ) 如果COMMISSION_PCT 为空，返回salary的值，如果COMMISSION_PCT 和SALARY 都为空，返回10
13.在需要使用 IF-THEN-ELSE 逻辑时:
CASE expr WHEN comparison_expr1 THEN return_expr1
[WHEN comparison_expr2 THEN return_expr2
WHEN comparison_exprn THEN return_exprn
ELSE else_expr]
END
14.DECODE(col|expression, search1, result1
[, search2, result2,...,]
[, default])
lession4-------
15.使用外连接可以查询不满足连接条件的数据。
外连接的符号是 (+)
16.SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];
17. SELECT last_name, department_name
FROM employees
CROSS JOIN departments ;
18. NATURAL JOIN 子句，会以两个表中具有相同名字的列为条件创建等值连接。
在表中查询满足等值条件的数据。
如果只是列名相同而数据类型不同，则会产生错误。
19. 在NATURAL JOIN 子句创建等值连接时，可以使用 USING 子句指定等值连接中需要用到的列。
使用 USING 可以在有多个列满足条件时进行选择。
不要给选中的列中加上表名前缀或别名。
NATURAL JOIN 和 USING 子句经常同时使用
例如： SELECT e.employee_id, e.last_name, d.location_id
FROM employees e JOIN departments d
USING (department_id) ;
20. 使用ON 子句创建连接：
自然连接中是以具有相同名字的列为连接条件的。
可以使用 ON 子句指定额外的连接条件。
这个连接条件是与其它条件分开的。
ON 子句使语句具有更高的易读性。
例如： 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 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;
21 .LEFT OUTER JOIN 左外连接 RIGHT OUTER JOIN 右外连接 FULL OUTER JOIN 满外联接
lession5-------
22.分组函数 AVG COUNT MAX MIN STDDEV SUM
COUNT(expr) 返回 expr不为空的记录总数。
COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数
组函数忽略空值 SELECT AVG(commission_pct) FROM employees;
NVL函数使分组函数无法忽略空值 SELECT AVG(NVL(commission_pct, 0)) FROM employees;
23. SELECT department_id, AVG(salary) FROM employees GROUP BY department_id ;
在GROUP BY 子句中包含多个列 SELECT department_id dept_id, job_id, SUM(salary) FROM employees GROUP BY department_id, job_id ;
24. 所用包含于SELECT 列表中，而未包含于组函数中的列都 必须包含于 GROUP BY 子句中。
SELECT department_id, COUNT(last_name) FROM employees; GROUP BY 子句中缺少列
不能在 WHERE 子句中使用组函数。 可以在HAVING 子句中使用组函数
25.使用 HAVING 过滤分组:
1. 行已经被分组。
2. 使用了组函数。
3. 满足HAVING 子句中条件的分组将被显示。
例如：
SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id
HAVING MAX(salary)>10000 ;
lession6-------
26.多行子查询 in 等于列表中的任何一个
any 和子查询返回的任意一个值比较
all 和子查询返回的所有值比较
例如：SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(SELECT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
lession7-------
27.Isqlplus
在变量名前加前缀 (&) 使用户输入值。
SELECT employee_id, last_name, salary, department_id
FROM employees
WHERE employee_id = &employee_num ;
在子符和日期两端加单引号。
SELECT last_name, department_id, salary*12
FROM employees
WHERE job_id = '&job_title' ;
lession8-------
28.
INSERT INTO employees ( hire_date) VALUES ( SYSDATE);//插入当前日期
INSERT INTO employees VALUES (114, TO_DATE('FEB 3, 1999', 'MON DD, YYYY'), 'AC_ACCOUNT');
INSERT INTO departments (department_id, department_name, location_id) VALUES (&department_id, '&department_name',&location);
29.
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
不必书写 VALUES 子句。
子查询中的值列表应于 INSERT 子句中的列名对应。
DELETE FROM copy_emp;
如果省略WHERE子句，则表中的全部数据将被删除。
30.
INSERT INTO (SELECT employee_id, last_name, email,
hire_date, job_id, salary
FROM employees
WHERE department_id = 50 WITH CHECK OPTION)
VALUES (99998, 'Smith', 'JSMITH',
TO_DATE('07-JUN-99', 'DD-MON-RR'),
'ST_CLERK', 5000);
使用子查询表示 DML 语句中使用的表
WITH CHECK OPTION 关键字避免修改子查询范围外的数据
31.
在对表COPY_EMP使用merge语句，根据指定的条件从表
EMPLOYEES中插入或更新数据。
MERGE INTO copy_emp c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
...
WHEN NOT MATCHED THEN
INSERT VALUES...;
32.
使用 SAVEPOINT 语句在当前事务中创建保存点。
使用 ROLLBACK TO SAVEPOINT 语句回滚到创建的保存点。
UPDATE...
SAVEPOINT update_done;
INSERT...
ROLLBACK TO update_done;
lession9-------
33.
使用 ADD 子句追加一个新列
ALTER TABLE dept80 ADD (job_id VARCHAR2(9));
ALTER TABLE dept80 MODIFY (last_name VARCHAR2(30));
ALTER TABLE dept80 DROP COLUMN job_id
34.使用 SET UNUSED 使一个或多个列被标记为不可用
使用 DROP UNUSED COLUMNS 选项删除不可用的列
ALTER TABLE table
SET UNUSED (column);
ALTER TABLE table
SET UNUSED COLUMN column;
ALTER TABLE table
DROP UNUSED COLUMNS;
35.执行RENAME语句改变表, 视图, 序列, 或同义词的名称
RENAME dept TO detail_dept;
36.TRUNCATE TABLE 语句:
删除表中所有的数据
释放表的存储空间
TRUNCATE TABLE detail_dept;
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession10-------约束
定义约束
CREATE TABLE employees(
employee_id NUMBER(6),
first_name VARCHAR2(20),
...
job_id VARCHAR2(10) NOT NULL,
CONSTRAINT emp_emp_id_pk
PRIMARY KEY (EMPLOYEE_ID));
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE
CONSTRAINT emp_hire_date_nn
NOT NULL,
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25) ,
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));
PRIMARY KEY 约束
CREATE TABLE departments(
department_id NUMBER(4),
department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
FOREIGN KEY 约束
CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
FOREIGN KEY: 在表级指定子表中的列
REFERENCES: 标示在父表中的列
ON DELETE CASCADE: 当父表中的列被删除是，子表中相对应的列也被删除
ON DELETE SET NULL: 子表中相应的列置空
添加约束
ALTER TABLE employees
ADD CONSTRAINT emp_manager_fk
FOREIGN KEY(manager_id)
REFERENCES employees(employee_id);
从表 EMPLOYEES 中删除约束
ALTER TABLE employees
DROP CONSTRAINT emp_manager_fk;
使用CASCADE选项删除约束
ALTER TABLE departments
DROP PRIMARY KEY CASCADE;
无效化约束
在ALTER TABLE 语句中使用 DISABLE 子句将约束无效化。
使用 CASCADE 选项将相关的约束也无效化
ALTER TABLE employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;
激活约束
ENABLE 子句可将当前无效的约束激活
ALTER TABLE employees
ENABLE CONSTRAINT emp_emp_id_pk;
CASCADE CONSTRAINTS 子句在 DROP COLUMN 子句中使用
在删除表的列时 CASCADE CONSTRAINTS 子句指定将相关的约束一起删除
在删除表的列时 CASCADE CONSTRAINTS 子句同时也删除多列约束
查询约束
查询数据字典视图 USER_CONSTRAINTS
SELECT constraint_name, constraint_type,
search_condition
FROM user_constraints
WHERE table_name = 'EMPLOYEES';
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession11-------视图
创建视图
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id = 80;
描述视图结构
DESCRIBE empvu80
使用CREATE OR REPLACE VIEW 子句修改视图
CREATE OR REPLACE VIEW empvu80
(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' ' || last_name,
salary, department_id
FROM employees
WHERE department_id = 80;
创建复杂视图
CREATE VIEW dept_sum_vu
(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name;
可以在简单视图中执行 DML 操作
当视图定义中包含以下元素之一时不能使用delete:
组函数
GROUP BY 子句
DISTINCT 关键字
ROWNUM 伪列
当视图定义中包含以下元素之一时不能使用update :
组函数
GROUP BY子句
DISTINCT 关键字
ROWNUM 伪列
列的定义为表达式
当视图定义中包含以下元素之一时不能使用insert :
组函数
GROUP BY 子句
DISTINCT 关键字
ROWNUM 伪列
列的定义为表达式
表中非空的列在视图定义中未包括
使用 WITH CHECK OPTION 子句确保DML只能在特定的范围内执行
CREATE OR REPLACE VIEW empvu20
AS SELECT *
FROM employees
WHERE department_id = 20
WITH CHECK OPTION CONSTRAINT empvu20_ck ;
可以使用 WITH READ ONLY 选项屏蔽对视图的DML 操作
任何 DML 操作都会返回一个Oracle server 错误
CREATE OR REPLACE VIEW empvu10
(employee_number, employee_name, job_title)
AS SELECT employee_id, last_name, job_id
FROM employees
WHERE department_id = 10
WITH READ ONLY;
删除视图
DROP VIEW view;
查询工资最高的三名员工:
SELECT ROWNUM as RANK, last_name, salary
FROM (SELECT last_name,salary FROM employees
ORDER BY salary DESC)
WHERE ROWNUM <= 3;
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession12-------序列
创建序列 DEPT_DEPTID_SEQ为表 DEPARTMENTS 提供主键
不使用 CYCLE 选项
CREATE SEQUENCE sequence
[INCREMENT BY n]
[START WITH n]
[{MAXVALUE n | NOMAXVALUE}]
[{MINVALUE n | NOMINVALUE}]
[{CYCLE | NOCYCLE}]
[{CACHE n | NOCACHE}];
/*-------------------------------------------------------------
例子：CREATE SEQUENCE dept_deptid_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
查询数据字典视图 USER_SEQUENCES获取序列定义信息
例子：SELECT sequence_name, min_value, max_value,
increment_by, last_number
FROM user_sequences;
/*-------------------------------------------------------------
NEXTVAL 返回序列中下一个有效的值，任何用户都可以引用
CURRVAL 中存放序列的当前值
NEXTVAL 应在 CURRVAL 之前指定 ，二者应同时有效
例子：
INSERT INTO departments(department_id,
department_name, location_id)
VALUES (dept_deptid_seq.NEXTVAL,
'Support', 2500);
/*-------------------------------------------------------------
序列 DEPT_DEPTID_SEQ 的当前值
SELECT dept_deptid_seq.CURRVAL
FROM dual;
/*-------------------------------------------------------------
修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存
ALTER SEQUENCE dept_deptid_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;
/*-------------------------------------------------------------
使用DROP SEQUENCE 语句删除序列
删除之后，序列不能再次被引用
DROP SEQUENCE dept_deptid_seq;
//*************************************************************//
lession12-------创建索引
自动创建: 在定义 PRIMARY KEY 或 UNIQUE 约束后系统自动在相应的列上创建唯一性索引
手动创建: 用户可以在其它列上创建非唯一的索引，以加速查询
CREATE INDEX emp_last_name_idx
ON employees(last_name);
/*-------------------------------------------------------------
可以使用数据字典视图USER_INDEXES 和 USER_IND_COLUMNS 查看索引的信息
SELECT ic.index_name, ic.column_name,
ic.column_position col_pos,ix.uniqueness
FROM user_indexes ix, user_ind_columns ic
WHERE ic.index_name = ix.index_name
AND ic.table_name = 'EMPLOYEES';
/*-------------------------------------------------------------
基于函数的索引是一个基于表达式的索引
索引表达式由列, 常量, SQL 函数和用户自定义的函数
CREATE INDEX upper_dept_name_idx
ON departments(UPPER(department_name));
Index created.
SELECT *
FROM departments
WHERE UPPER(department_name) = 'SALES';
/*-------------------------------------------------------------
使用DROP INDEX 命令删除索引
DROP INDEX index;
/*-------------------------------------------------------------
使用同义词访问相同的对象:
方便访问其它用户的对象
缩短对象名字的长度
CREATE [PUBLIC] SYNONYM synonym
FOR object;
为视图DEPT_SUM_VU 创建同义词
CREATE SYNONYM d_sum
FOR dept_sum_vu;
删除同义词
DROP SYNONYM d_sum;
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession13-------控制用户权限
DBA 使用 CREATE USER 语句创建用户
CREATE USER scott
IDENTIFIED BY tiger;
以应用程序开发者为例, 一般具有下列系统权限:
CREATE SESSION（创建会话）
CREATE TABLE（创建表）
CREATE SEQUENCE（创建序列）
CREATE VIEW（创建视图）
CREATE PROCEDURE（创建过程）
GRANT privilege [, privilege...]
TO user [, user| role, PUBLIC...];
DBA 可以赋予用户特定的权限
例如：
GRANT create session, create table,
create sequence, create view
TO scott;
创建角色
create role manager;
为角色赋予权限
grant create table,create view to manager
将角色赋予用户
grant manager to dehaan,kohhk;
/*-------------------------------------------------------------
修改密码
DBA 可以创建用户和修改密码
用户本人可以使用ALTER USER 语句修改密码
ALTER USER scott
IDENTIFIED BY lion;
/*-------------------------------------------------------------
对象权限
GRANT object_priv [(columns)]
ON object
TO {user|role|PUBLIC}
[WITH GRANT OPTION];
分配表 EMPLOYEES 的查询权限
GRANT select
ON employees
TO sue, rich;
分配表中各个列的更新权限
GRANT update (department_name, location_id)
ON departments
TO scott, manager
WITH GRANT OPTION 使用户同样具有分配权限的权利
GRANT select, insert
ON departments
TO scott
WITH GRANT OPTION;
向数据库中所有用户分配权限
GRANT select
ON alice.departments
TO PUBLIC;
/*-------------------------------------------------------------
查询权限分配情况
数据字典视图 描述
ROLE_SYS_PRIVS 角色拥有的系统权限
ROLE_TAB_PRIVS 角色拥有的对象权限
USER_ROLE_PRIVS 用户拥有的角色
USER_TAB_PRIVS_MADE 用户分配的关于表对象权限
USER_TAB_PRIVS_RECD 用户拥有的关于表对象权限
USER_COL_PRIVS_MADE 用户分配的关于列的对象权限
USER_COL_PRIVS_RECD 用户拥有的关于列的对象权限
USER_SYS_PRIVS 用户拥有的系统权限
使用 REVOKE 语句收回权限
使用 WITH GRANT OPTION 子句所分配的权限同样被收回
REVOKE {privilege [, privilege...]|ALL}
ON object
FROM {user[, user...]|role|PUBLIC}
[CASCADE CONSTRAINTS];
例如:
REVOKE select, insert
ON departments
FROM scott;
//*************************************************************//
创建数据库联接
CREATE PUBLIC DATABASE LINK hq.acme.com
USING 'sales';
使用SQL 语句访问远程数据库
SELECT *
FROM emp@HQ.ACME.COM;
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession15-------SET 运算符
UNION 操作符返回两个查询的结果集的并集
SELECT employee_id, job_id
FROM employees
UNION
SELECT employee_id, job_id
FROM job_history;
UNION ALL 操作符返回两个查询的结果集的并集以及两个结果集的重复部分（不去重）
SELECT employee_id, job_id, department_id
FROM employees
UNION ALL
SELECT employee_id, job_id, department_id
FROM job_history
ORDER BY employee_id;
INTERSECT 操作符返回两个结果集的交集
SELECT employee_id, job_id
FROM employees
INTERSECT
SELECT employee_id, job_id
FROM job_history;
MINUS 操作符返回两个结果集的补集
SELECT employee_id,job_id
FROM employees
MINUS
SELECT employee_id,job_id
FROM job_history;
ORDER BY 子句:
只能在语句的最后出现
可以使用第一个查询中的列名, 别名或相对位置
使用相对位置排序举例
COLUMN a_dummy NOPRINT
SELECT 'sing' AS "My dream", 3 a_dummy
FROM dual
UNION
SELECT 'I''d like to teach', 1
FROM dual
UNION
SELECT 'the world to', 2
FROM dual
ORDER BY 2;
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession16-------日期函数
通过本章学习，您将可以使用下列日期函数:
TZ_OFFSET
CURRENT_DATE
CURRENT_TIMESTAMP
LOCALTIMESTAMP
DBTIMEZONE
SESSIONTIMEZONE
EXTRACT
FROM_TZ
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
/*-------------------------------------------------------------
Oracle9i中, 可以将时区加入到日期和时间中而且可以将秒进行进一步的精确
日期中加入了三种新的数据类型:
TIMESTAMP（时间撮）
TIMESTAMP WITH TIME ZONE (TSTZ) （带时区的时间撮）
TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) （带有本地时区的时间撮）
/*-------------------------------------------------------------
显示时区 ‘US/Eastern’的时差
SELECT TZ_OFFSET('US/Eastern') FROM DUAL;
按照当前会话的时区显示当前会话的时间
CURRENT_DATE 对会话所在的时区是敏感的
ALTER SESSION
SET NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
ALTER SESSION SET TIME_ZONE = '-8:0';SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;
CURRENT_TIMESTAMP
按照当前会话的时区显示当前会话的时间
ALTER SESSION SET TIME_ZONE = '-5:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP
FROM DUAL;
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP
FROM DUAL;
CURRENT_TIMESTAMP 对会话所在的时区是敏感的
返回值是 TIMESTAMP WITH TIME ZONE 数据类型
LOCALTIMESTAMP
ALTER SESSION SET TIME_ZONE = '-5:0';SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;
ALTER SESSION SET TIME_ZONE = '-8:0';
SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP
FROM DUAL;
LOCALTIMESTAMP对会话所在的时区是敏感的
返回值是 TIMESTAMP 数据类型
DBTIMEZONE 和 SESSIONTIMEZONE
显示数据库所在的时区
SELECT DBTIMEZONE FROM DUAL;
显示会话所在的时区
SELECT SESSIONTIMEZONE FROM DUAL;
EXTRACT从 SYSDATE 中抽出年
SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;
从HIRE_DATE 中抽出月
SELECT last_name, hire_date,
EXTRACT (MONTH FROM HIRE_DATE)
FROM employees
WHERE manager_id = 100;
SELECT FROM_TZ(TIMESTAMP
'2000-03-28 08:00:00','3:00')
FROM DUAL;
SELECT FROM_TZ(TIMESTAMP
'2000-03-28 08:00:00', 'Australia/North')
FROM DUAL;
SELECT TO_TIMESTAMP ('2000-12-01 11:00:00', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
SELECT
TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00', 'YYYY-MM-DD HH:MI:SS TZH:TZM')FROM DUAL;
TO_YMINTERVAL 应用举例
SELECT hire_date,
hire_date + TO_YMINTERVAL('01-02') AS HIRE_DATE_YMININTERVAL FROM EMPLOYEESWHERE department_id = 20;
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession17-------对 GROUP BY 子句的扩展
GROUP BY 子句 组函数处理多行返回一个行
使用 HAVING 对组函数进行限制
对查询进行第二次限制
使用带有ROLLUP 和 CUBE 操作的GROUP BY 子句产生多种分组结果
ROLLUP 产生n + 1种分组结果
CUBE 产生2的n次方种分组结果
SELECT [column,] group_function(column). . .
FROM table
[WHERE condition]
[GROUP BY [ROLLUP] group_by_expression]
[HAVING having_expression];
[ORDER BY column];
ROLLUP 是对 GROUP BY 子句的扩展
ROLLUP 产生n + 1种分组结果，顺序是从右向左
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);
SELECT [column,] group_function(column)...
FROM table
[WHERE condition]
[GROUP BY [CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
CUBE是对 GROUP BY 子句的扩展
CUBE 会产生类似于笛卡尔集的分组结果
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY CUBE (department_id, job_id)
GROUPING 函数
SELECT [column,] group_function(column) . ,
GROUPING(expr)
FROM table
[WHERE condition]
[GROUP BY [ROLLUP][CUBE] group_by_expression]
[HAVING having_expression]
[ORDER BY column];
GROUPING 函数可以和 CUBE 或 ROLLUP 结合使用
使用 GROUPING 函数，可以找到哪些列在该行中参加了分组
使用 GROUPING 函数, 可以区分空值产生的原因
GROUPING 函数返回 0 或 1
SELECT department_id DEPTID, job_id JOB,
SUM(salary),
GROUPING(department_id) GRP_DEPT,
GROUPING(job_id) GRP_JOB
FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);
GROUPING SETS 是对GROUP BY 子句的进一步扩充
使用 GROUPING SETS 在同一个查询中定义多个分组集
Oracle 对 GROUPING SETS 子句指定的分组集进行分组后用 UNION ALL 操作将各分组结果结合起来
Grouping set 的优点:
只进行一次分组即可
不必书写复杂的 UNION 语句
GROUPING SETS 中包含的分组项越多,性能越好
SELECT department_id, job_id,
manager_id,avg(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id,job_id), (job_id,manager_id));
复合列是被作为整体处理的一组列的集合
ROLLUP (a, , d)
使用括号将若干列组成复合列在ROLLUP 或 CUBE 中作为整体进行操作
在ROLLUP 或 CUBE中, 复合列可以避免产生不必要的分组结果
SELECT department_id, job_id, manager_id,
SUM(salary)
FROM employees GROUP BY ROLLUP( department_id,(job_id, manager_id));
连接分组集可以产生有用的对分组项的结合
将各分组集, ROLLUP 和 CUBE 用逗号连接 Oracle 自动在 GROUP BY 子句中将各分组集进行连接
连接的结果是对各分组生成笛卡尔集
GROUP BY GROUPING SETS(a, b), GROUPING SETS(c, d)
SELECT department_id, job_id, manager_id,
SUM(salary)
FROM employeesGROUP BY department_id,
ROLLUP(job_id),
CUBE(manager_id);
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession18-------
成对比较举例
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (178,174))
AND employee_id NOT IN (178,174);
不成对比较举例
SELECT employee_id, manager_id, department_id
FROM employees
WHERE manager_id IN (SELECT manager_id
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN (SELECT department_id
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN(174,141);
--------------------------------------------------------------------------------------------
EXISTS 操作符检查在子查询中是否存在满足条件的行
如果在子查询中存在满足条件的行:
不在子查询中继续查找
条件返回 TRUE
如果在子查询中不存在满足条件的行:
条件返回 FALSE
继续在子查询中查找
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id =
outer.employee_id);
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id
= d.department_id);
--------------------------------------------------------------------------------------
相关删除
DELETE FROM table1 alias1
WHERE column operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
-------------------------------------------------------------------------------------
使用 WITH 子句, 可以避免在 SELECT 语句中重复书写相同的语句块
WITH 子句将该子句中的语句块执行一次 并存储到用户的临时表空间中
使用 WITH 子句可以提高查询效率
WITH dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
-------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession19-------分级查询
SELECT [LEVEL], column, expr...
FROM table
[WHERE condition(s)]
[START WITH condition(s)]
[CONNECT BY PRIOR condition(s)] ;
COLUMN org_chart FORMAT A12
SELECT LPAD(last_name, LENGTH(last_name)+(LEVEL*2)-2,'_')
AS org_chart
FROM employees
START WITH last_name='King'
CONNECT BY PRIOR employee_id=manager_id
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
lession20-------
Oracle9i 提供以下四种多表INSERT 语句类型:
无条件的 INSERT
有条件的 ALL INSERT
有条件的 FIRST INSERT
旋转 INSERT
无条件的 INSERT ALL 应用举例
INSERT ALL INTO sal_history VALUES(EMPID,HIREDATE,SAL) INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
有条件的 INSERT ALL 应用举例
INSERT ALL WHEN SAL > 10000 THEN INTO sal_history VALUES(EMPID,HIREDATE,SAL) WHEN MGR > 200 THEN INTO mgr_history VALUES(EMPID,MGR,SAL)
SELECT employee_id EMPID,hire_date HIREDATE,
salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
有条件的 FIRST INSERT 应用举例
INSERT FIRST WHEN SAL > 25000 THEN INTO special_sal VALUES(DEPTID, SAL) WHEN HIREDATE like ('%00%') THEN INTO hiredate_history_00 VALUES(DEPTID,HIREDATE) WHEN HIREDATE like ('%99%') THEN INTO hiredate_history_99 VALUES(DEPTID, HIREDATE) ELSE INTO hiredate_history VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE FROM employees
GROUP BY department_id;
旋转 INSERT 应用举例
INSERT ALL INTO sales_info VALUES (employee_id,week_id,sales_MON) INTO sales_info VALUES (employee_id,week_id,sales_TUE) INTO sales_info VALUES (employee_id,week_id,sales_WED) INTO sales_info VALUES (employee_id,week_id,sales_THUR) INTO sales_info VALUES (employee_id,week_id, sales_FRI) SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE, sales_WED, sales_THUR,sales_FRI FROM sales_source_data;
--------------------------------------------------------------------------------------
外部表
创建外部表之前应先使用CREATE DIRECTORY语句创建路径
CREATE DIRECTORY emp_dir AS '/flat_files' ;
CREATE TABLE oldemp ( empno NUMBER, empname CHAR(20), birthdate DATE) ORGANIZATION EXTERNAL (TYPE ORACLE_LOADER DEFAULT DIRECTORY emp_dir ACCESS PARAMETERS (RECORDS DELIMITED BY NEWLINE BADFILE 'bad_emp' LOGFILE 'log_emp' FIELDS TERMINATED BY ',' (empno CHAR, empname CHAR, birthdate CHAR date_format date mask "dd-mon-yyyy")) LOCATION ('emp1.txt')) PARALLEL 5 REJECT LIMIT 200;
--------------------------------------------------------------------------------------
创建主键约束同时创建索引举例
CREATE TABLE NEW_EMP
(employee_id NUMBER(6) PRIMARY KEY USING INDEX (CREATE INDEX emp_id_idx ON NEW_EMP(employee_id)),
first_name VARCHAR2(20),last_name VARCHAR2(25));
SELECT INDEX_NAME, TABLE_NAME FROM USER_INDEXESWHERE TABLE_NAME = 'NEW_EMP';
------------------------------------------------------------------------------------------------
************************************************************************************************
------------------------------------------------------------------------------------------------
浙公网安备 33010602011771号