ORACLE 10G OCP SQL部分 学习笔记

*********************LESSON 1*********************
as 别名
'' 连接单个字符
"" 连接多个字符

sqlplus hr/hr@myoracle

select * from employees;

desc departments;

select department_id id from departments;

select department_id as id from departments;

select department_id "dp_id" from departments;

select distinct department_id ||'id' as dp_id from departments;

select distinct department_id ||'id' dp_id from departments;

*********************LESSON 2*********************

运算符  =  >  = 

select last_name,salary
from employees
where salary<3000;

select last_name,salary
from employees
where salary between 2000 and 3000;


select last_name,salary
from employees
where salary in(2000,3000,2344);

----    _ 占位符，匹配任意数字，占1个字符
select last_name,salary
from employees
where last_name like '%_a%';

select last_name,salary
from employees
where department_id is null;

select last_name,salary
from employees
where department_id is not null;

select employee_id ,last_name,job_id,salary
from employees
where salary<3000 and job_id like 'ST%';

select employee_id ,last_name,job_id,salary
from employees
where salary>3000 and job_id like '%_MAN%';

select last_name,job_id
from employees
where job_id not in ('IT_PROG','ST%','SA%');


--操作符优先级
算术操作符>>>字符串拼接>>>比较条件>>>IS NULL  LIKE>>>
>>>BETWEEN NOT NETWEEN>>>NOT>>>AND>>>OR    括号优先所有的操作

select last_name name ,job_id id ,salary sa
from employees
where job_id='SA_REP' OR job_id ='AD_PRES' AND salary >15000;


select last_name name ,job_id id ,salary sa
from employees
where (job_id='SA_REP' OR job_id ='AD_PRES') AND salary >15000;

--默认为asc升序
select last_name,job_id ,department_id,hire_date
from employees
order by hire_date asc;

select last_name,job_id ,department_id,hire_date
from employees
order by hire_date desc;

--先按department_id升序，在department_id相同的记录里对salary记录降序
select last_name,job_id ,department_id
from employees
order by department_id asc, salary desc;


--查找多少个表，字符区分大小写
select * from tab ;
select * from tab where tname like '%C%';

select * from departments
where location_id between 1400 and 2200
order by department_id;

select * from departments
where location_id between 1400 and 2200
order by department_id,location_id desc;

select * from departments
where location_id in(1400,2000);

select * from departments
where location_id not in(1400,2000);

select * from departments
where location_id =1700 and department_id=200;

select * from departments
where location_id =1700 or department_id=200;

select * from departments
where location_id =1700 or department_id=200 and manager_id is null;

select * from departments
where location_id =1700 or department_id=200 or manager_id is null;

select * from departments
where location_id =1700 or department_id=200 or manager_id is null
order by manager_id desc;


*********************LESSON 3 函数*********************
Character   

LOWER        UPPER        INITCAP--首字母大写
CONTACT--连接    SUBSTR--截取    LENGTH--长度    INSTR--寻找字符的位置    
LPAD--左填充*    RPAD--右填充    TRIM--去除空格    REPLACE--替换

select employee_id ,last_name,department_id
from employees
where LOWER(last_name)='higgins';
--CONCAT('HELLO','WORLD')
--SUBSTR('HELLOWORLD',1,5)
--LENGTH('HELLOWORLD')
--INSTR('HELLOWORLD','W')  W的位置是6
--LPAD(salary,10,'*')左填充  *****24000
--LPAD(salary,10,'*')右填充  24000*****
--TRIM('H' FROM 'HELLOWORLD')去除H   ELLOWORLD

select employee_id ,concat(first_name,last_name) name ,job_id ,LENGTH(last_name),INSTR(last_name,'a') "Contains 'a' ?"
from employees
where SUBSTR(job_id ,4)='REP';


General    


Number    

--ROUND        ROUND(45.926,2)---&gt45.93    有四舍五入的功能
ROUND(45.923,2)---45.92
ROUND(45.923,0)---46
ROUND(45.923,-1)---50
--TRUNC        TRUNC(45.926,2)---&gt45.92    没有四舍五入的功能
TRUNC(45.923,2)---45.92
TRUNC(45.923)---45
TRUNC(45.923,-2)---0
--MOD        MOD(1600,300)---&gt100        取余

Date     
SYSDATE---系统当前时间  秒为单位
select last_name,(SYSDATE-hire_date)/7 as weeks
from employees
where department_id=90;

MONTHS_BETWEEN    MONTHS_BETWEEN('01-SEP-95','11-JAN-94')---10.6774194
ADD_MONTHS    ADD_MONTHS('11-JAN-94',6)
NEXT_DAY    NEXT_DAY('01-SEP-95','FRIDAY')
LAST_DAY    每月最后一条
ROUND
ROUND(SYSDATE,'MONTH')
ROUND(SYSDATE,'YEAR')
    
TRUNC
TRUNC(SYSDATE,'MONTH')   
TRUNC(SYSDATE,'MONTH')   

Conversion    Implicit隐式    Explicit显式
TO_CHAR('01-02-1995','DD-MM-YYYY')
TO_CHAR('01-02-1995','DAY-MON-YYYY')
TO_CHAR('01-02-1995','DD-MONTH-YEAR')

--日期格式
YYYY,YEAR,MM,MONTH,MON,DY,DAY,DD

--时间格式
HH24:MI:SS AM ----15:45:32 PM
DD "of" MONTH ----12 of OCTOBER


select last_name,TO_CHAR(hire_date,'fmDD MONTH YYYY') as Hiredate
from employees;

select last_name,TO_CHAR(hire_date,'DAY MONTH YYYY') as Hiredate
from employees;

select last_name,TO_CHAR(hire_date,'DD MM YYYY') as Hiredate
from employees;

--格式化输出
select TO_CHAR(salary,'$99,999.00') salary
from employees
where last_name='Ernst';

--RR和当前日期进行一个判断  Date Format

select last_name,TO_CHAR(hire_date,'DD-MM-YYYY')
from employees
where hire_date
函数的复合使用
NVL    NVL2

NVL(exp1,exp2)---如果exp1是null则输出exp2的值
select last_name,NVL(TO_CHAR(manager_id),'No Manager')
from employees
where manager_id is null;

NVL2()---第一个参数不为空输出第二个参数的值，否则输出第三个的值
select last_name,salary,commission_pct,
NVL2(commission_pct,'SAL+COMM','SAL') income
from employees
where department_id in (50,80);

NULLIF---如果两个参数不相等取第一个的值，如二个参数则返回为null
select first_name,LENGTH(first_name) "expr1",last_name,LENGTH(last_name) "expr2",NULLIF(LENGTH(first_name),LENGTH(last_name))
from employees;

COALESCE--第一个参数如果为空，显示第二个，第二个为空则为第三个

select last_name,COALESCE(commission_pct,salary,10) comm
from employees
order by commission_pct;

CASE分支语句

select last_name ,job_id ,salary,
CASE job_id WHEN 'IT_PROG' THEN 1.10*salary
        WHEN 'ST_CLERK' THEN 1.15*salary
        WHEN 'SA_REP' THEN 1.20*salary
    ELSE salary END "REVISED_SALARY"
FROM employees;


DECODE--行列转换

select last_name,salary,DECODE(TRUNC(salary/2000,0),
            0,0.00,
            1,0.09,
            2,0.20,
            3,0.30,
            4,0.40,
            5,0.50) TAX_RATE
from employees
where department_id=80;

*********************LESSON 4 多表查询*********************

Equijoins--------等于连接
prefix--前缀
select employees.employee_id,employees.last_name,
employees.department_id,departments.department_id,departments.location_id
from employees,departments
where employees.department_id=departments.department_id;

--使用别名
select e.employee_id,e.last_name,e.department_id,
d.department_id,d.location_id
from employees e,departments d
where e.department_id=d.department_id;

Non-Equijoins----非等于连接
select e.last_name,e.salary,j.job_title
from employees e,jobs j
where e.salary BETWEEN j.min_salary AND j.max_salary;


Out Joins---外连接  (+)的一端代表为从表
select e.last_name,e.department_id,d.department_name
from employees e,departments d
where e.department_id(+)=d.department_id;


Self Joins--自连接
select worker.last_name || ' works for ' || manager.last_name
from employees worker,employees manager
where worker.manager_id=manager.employee_id;

Cross Joins--交叉连接
select last_name ,department_name
from employees CROSS JOIN departments;

NATURAL JOIN---自然连接

select department_id,department_name,location_id,city
from departments NATURAL JOIN locations;

USING--指定使用那个列进行JOIN
select e.employee_id,e.last_name,d.location_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 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;


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,e.department_id,d.department_name
from employees e
FULL OUTER JOIN departments d
ON (e.department_id=d.department_id);


*********************LESSON 5 分组*********************
AVG    COUNT    MAX    MIN    SUM

使用到这些函数可以在子句上加GROUP BY
select AVG(salary),MAX(salary),MIN(salary),SUM(salary)
from employees
where job_id like '%REP%';

也可以对日期进行操作
select MIN(hire_date),MAX(hire_date)
from employees;

select COUNT(*)
from employees
where department_id=50;

COUNT(字段)返回的数据可能少于COUNT(*),它不统计为null的值
select COUNT(commission_pct)
from employees
where department_id=80;

-----11
select COUNT(DISTINCT department_id)
from employees;
-----106
select DISTINCT COUNT(department_id)
from employees;


select AVG(NVL(commission_pct,0))
from employees;


select department_id,AVG(salary)
from employees
GROUP BY department_id;

select AVG(salary)
from employees
GROUP BY department_id;

多列分组
select department_id dept_id,job_id,SUM(salary)
from employees
GROUP BY department_id,job_id;

---错误，无法确定分组条件
SELECT DEPARTMENT_ID,COUNT(LAST_NAME)
FROM EMPLOYEES;

---错误，AVG()不允许出现在where中
select department_id,AVG(salary)
from employees
having AVG(salary) >8000
GROUP BY department_id;

--使用HAVING 来过滤条件
select department_id,AVG(salary)
from employees
HAVING AVG(salary) >8000
GROUP BY department_id;

select department_id,MAX(salary)
from employees
GROUP BY department_id
HAVING MAX(salary)>8000;


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

---WHERE限定取数据的范围，HAVING限定取完数据后输出条件的范围
select MAX(AVG(salary))
from employees
GROUP BY department_id;


select MIN(employee_id) from employees;

select MAX(salary) from employees;

select distinct job_id from employees;

select COUNT(distinct job_id) from employees;

select MIN(employee_id),department_id
from employees
GROUP BY department_id;

select MIN(employee_id),department_id
from employees
GROUP BY department_id
ORDER BY MIN(employee_id);

select MIN(employee_id),department_id
from employees
GROUP BY department_id
HAVING MIN(employee_id)>150
ORDER BY department_id;


*********************LESSON 6子查询subqueries*********************


子查询结果为单行时用 = > >= < <= <>

select last_name
from employees
where salary>(select salary
        from employees
        where last_name='Abel');

select last_name,job_id,salary
from employees
where job_id =(select job_id
        from employees
        where employee_id=141)
AND salary>(select salary
        from employees
        where employee_id=143);

select last_name,job_id,salary
from employees
where salary=(select MIN(salary)
        from employees);

select department_id,MIN(salary)
from employees
GROUP BY department_id
HAVING MIN(salary)>(select MIN(salary)
        from employees
        where department_id=50);

---错误，子查询返回的结果多于一行
select employee_id,last_name
form. employees
where salary=(select MIN(salary)
        from employees
        GROUP BY department_id);

---子查询返回为空
select last_name,job_id
from employees
where job_id=(select job_id
        from employees
        where last_name='Haas');


子查询结果为集合用
IN在子查询范围内
ANY子查询中任意一个
ALL子查询中所有的

select employee_id,last_name,job_id,salary
from employees
where salary        from employees
        where job_id='IT_PROG')
AND job_id <> 'IT_PROG';


select employee_id,last_name,job_id,salary
from employees
where salary        from employees
        where job_id='IT_PROG')
AND job_id <> 'IT_PROG';


select emp.last_name
from employees emp
where emp.employee_id IN (select mgr.manager_id
            from employees mgr);

select emp.last_name
from employees emp
where emp.employee_id NOT IN (select mgr.manager_id
            from employees mgr);


*********************LESSON 7 iSQL*Plus*********************

select * from employees
where employee_id=&employee_id;

select last_name,department_id,salary*12
from employees
where job_id='&job_title';

select employee_id,last_name,job_id,&column_name
from employees
where &condition
ORDER BY &order_column;

------数据定义
DEFINE job_title=IT_PROG
------数据重定义
UNDEFINE job_title


DEFINE employee_num=200
select employee_id,last_name,salary,department_id
from employees
where employee_id=&employee_num;

----两个&&，不需要每次都输入，全局变量的意思
DEFINE column_name=department_id
select employee_id,last_name,job_id,&&column_name
from employees
ORDER BY &column_name;

----SET VERIFY ON  每次都会鉴别变量，显示本次和上次的变量的值
SET VERIFY ON
select employee-id,last_name,salary,department_id
from employees
where employee_id=&employee_num;

ARRAYSIZE
FEEDBACK
HEADING---SET HEADING OFF,SHOW HEADING
LONG



*********************LESSON 8*********************

insert into departments(department_id,department_name,manager_id,location_id)
values(70,'Public Relations',100,1700);

insert into departments(department_id,department_name)
values(30,'Purchasing');

insert into departments
values (30,'Finance',NULL,NULL);


insert into employees (employee_id,first_name,last_name,email,phone_number,
hire_date,job_id,salary,commission_pct,manager_id,department_id)
values(217,'Louis','Popp','LPOPP','515.124.4567',SYSDATE,'AC_ACCOUNT',6900,NULL,205,100);

insert into employees
values (114,'Den','Raphealy','DRAPHEAL','515.127.4561',TO_DATE('FEB 3,1999','MON DD,YYYY'),'AC-ACCOUNT',11000,NULL,100,30);

-----一次可以插入多条数据
insert into sals_reps(id,name,salary,commission_pct)
select employee_id,last_name,salary,commission_pct
from employees
where job_id Like '%REP%';


update employees
set department_id=70
where employee_id=113;

----所有记录都会被更改
update employees
set department_id=70;

update employees
set     job_id=(select job_id from employees
        where employee_id=205),
    salary=(select salary from employees
        where employee_id=205)
where employee_id=114;

update copy_emp
set department_id =(select department_id from employees
        where employee_id=100)
where job_id=(select hon_id from employees
        where employee_id=200);

-----主键不存在，删除错误
update employees
set department_id=55
where department_id=110;

delete from departments
where department_name='Finance';

------不加条件的话，整张表的数据都会被删除
delete from copy_emp;


delete from employees
where department_id=(select department_id
        from departments
        where department_name like '%Public%');

----直接删除的话，子表找不到主键，会报错
delete from departments
where department_id =60;


------通过子查询插入数据
insert into(
select employee_id,last_name,email,hire_date,job_id,
salary,department_id
from employees where department_id=50)
values (99999,'Taylor','DTAYLOR',TO_DATE('07-JUN-99','DD-MON-RR'),
'ST_CLECK',5000,50);



------WITH CHECK OPTION 插入时，自动对插入的数据和表里每一列进行检验
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_CLECK',5000);

------DEFAULT 插入或更新的时候使用该字段的默认值
insert into departments
(department_id,department_name.manager_id)
values(300,'Engineering',DEFAULT);


update departments
set manager_id=DEFAULT where department_id=10;


MERGE----有一个判断的功能，如果记录在在表中存在，就更新，没有就插入

MERGE INTO copy_emp c
USING employee e
ON(c.employee_id=e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name=e.first_name,
c.last_name=e.last_name,
......
c.department_id=e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id,e.first_name,e.last_name,e.email,e.phone_number,e.hire_date,e.job_id,e.salary,e.commission-pct,e.manager_id,e.department_id);



显示事务处理
consistency------一致性
BEGIN TRANSACTION    COMMIT        ROLLBACK    SAVEPOINT
COMMIT 之前数据不会变化


隐式事务处理
DDL    DCL

delete from employees
where employee_id=99999;
insert into departments
values (290,'Corporate Tax',NULL,1700);
COMMIT;


delete from copy_emp
ROLLBACK;

读一致性
在未COMMIT之前，用户看到的只是修改之前的数据
在更新数据前会在数据块上创建回滚段
其他用户查询的数据是在回滚段的数据
ROLLBACK SEGMENTS 回滚段


数据库锁---控制并发事务
显式锁和隐式锁

两种锁定模式
1.独占锁Exclusive
2.共享锁Share
查询，没有锁
DML，表共享，列锁定
在提交或回滚后锁才可以结束


*********************LESSON   9 表操作*********************

数据块对象
TABLE        表
VIEW        视图
SEQUENCE    序列   
INDEX        索引
SYNONYM        同义词

表的命名规则
1-30个长度
A-Z,a-z，0-9，_,$,#
不能用保留字

create table dept(deptno NUMBER(2) NOT NULL,
            dname VARCHAR2(14),
            loc VARCHAR2(13));

DESCRIBE dept;

表的类型
user table用户表

data dictionary数据字典

查看所有表
select table_name
from user_table;
查看对象
select DISTINCT object_type
from user_object;
查看分类
select *
from user_catalog;

数据类型
VARCHAR2(size)可变长度
CHAR(size)定长
NUMBER(p,s)变长数字类型
DATE
LONG
CLOB大对象字符类型
RAW原始二进制类型
LONG RAW
BLOB二进制大对象类型
BFILE
ROWID

TIMESTAMP精确到微妙

INTERVAL YEAR TO MONTH
INTERVAL DAY TO SECOND

INTERVAL '123-2' YEAR(3) TO MONTH------123年2个月
INTERVAL '123' YEAR(3) TO MONTH---123年0个月
INTERVAL '300' MONTH(3)----300个月

INTERVAL '123' YEAR---报错，不知道精度值

INTERVAL '4 5:12:10.222' DAY TO SECOND(3)---4天5小时12分钟10秒222毫秒
INTERVAL '4 5:12' DAY to minute---4天5小时12分钟
INTERVAL '7' DAY---7天
INTERVAL '180' DAY(3)---180天
INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)---11小时12分钟10.2222222秒


TIMESTAMP(number)----精确到几位
TIMESTAMP(number) WHTH TIME ZONE----根据时区来计时
TIMESTAMP(number) WHTH LOCAL TIME ZONE----根据本地时区来计时


----使用子查询来创建表

create table dept80
as
select employee_id,last_name,salary*12 ANNSAL,hire_date
from employees
where department_id=80;

----修改表add,modify,drop

alter table dept80
add(job_id varchar2(9));

alter table dept80
modify (last_name varchar2(30));

alter table dept80
drop column job_id;


SET UNUSED----设置为不使用

alter table dept80
SET UNUSED COLUMN job_id;

alter table dept80
DROP UNUSED job_id;

----删除表

DROP TABLE dept80；


----重命名表

RENAME dept TO detail_dept;

-----删除表,速度快，不可回滚，DDL语句,DROP是DML,可以回滚
TRUNCATE table detail_dept;

----表的备注
COMMENT ON TABLE employees
IS 'Employee Infomation';


*********************LESSON 10 约束*********************

主要约束
NOT NULL    不为空约束
UNIQUE        唯一约束
PRIMARY KEY    主键约束    =UNIQUE+NOT NULL
FOREIGN KEY    外键约束
CHECK        完整性约束

如果用户创建表时候不创建约束，系统会自动创建约束为SYS_Cn 格式

表创建成功后才可以创建约束

可以在表或列的级别上定义约束

-----主键约束,唯一性约束
create table employees(
        employee_id NUMBER(6),
        first_name VARCHAR2(20),
        last_name VARCHAR(25) NOT NULL,
        hire_date DATE NOT NULL CONSTRAINT emp_hire_date_nn,
        job_id VARCHAR2(10) NOT NULL, CONSTRAINT emp_emp_id_pk PRIMARY KEY(EMPLOYEE ID),
        email VARCHAR2(25) CONSTRAINT emp_email_uk UNIQUE(email));


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


------外键约束

create table employees(
        employee_id NUMBER(6),
        first_name VARCHAR2(20),
        last_name VARCHAR(25) NOT NULL,
        department_id    NUMBER(4),
        CONSTRAINT emp_dept_fk FOREIGN KEY(department_id)
        REFERENCE departments(department_id),
        email VARCHAR2(25) CONSTRAINT emp_email_uk UNIQUE(email));


------级联删除
ON DELETE CASCADE    ON DELETE SET NULL


-----CHECK 约束

CONSTRAINT emp_salary_min CHECK(salary > 0),


-------添加约束

alter table employees
ADD CONSTRAINT emp_manager_fk FORENGN KEY(manager_id)
REFERENCE employees(employee_id);

-------删除约束
alter table employees
DROP CONSTRAINT emp-manager_fk;

------级联删除主键约束
alter table departments
DROP PRIMARY KEY CASCADE;

------禁用约束
alter table employees
DISABLE CONSTRAINT emp_emp_id_pk CASCADE;

------启用约束
alter table employees
ENABLE CONSTRAINT emp_emp_id_pk;

----删除有约束的列，使用CASCADE CONSTRINTS
alter table employees
DROP employee_id CASCADE CONSTRAINTS;

alter table employees
DROP employee_id,job_id CASCADE CONSTRAINTS;


-----查询约束信息
-----表名为字符型，都是大写
select constraint_name,constraint_type,serch_condition
from user_constraints
where table_name='EMPLOYEES';

-----查询约束名和对应的列名
select constraint_name,column_name
from user_cons_columns
where table_name='EMPLOYEES';


*********************LESSON 11 视图*********************

WHTH CHECK OPTION---允许修改
WITH READ ONLY---不允许修改
FORCE---没有实际的表也创建视图
NO FORCE---

create or replace VIEW empvu80
AS
select employee_id,last_name,salary
from employees
where department_id=80;

create VIEW salvu50
AS
select employee_id ID_NUMBER,last_name NAME,salary*12 ANN_SALARY
from employees
where department_id=50;


-------修改视图，就是用or replace语句
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操作
----在有GROUP BY,聚合,DISTINCT,TOP N,列是通过表达式定义的,的视图不能进行DML
----基表中有NOT NULL 的列在视图中没有显示的也无法进行DML操作


----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----只能进行查询
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 删除视图
DROP VIEW empvu80；

----Inline VIEW

----TOP N 分析
select ROWNUM as RANK ,last_name,salary
from (select last_name,salary from employees
    order by salary DESC)
where ROWNUM <= 100;


*********************LESSON 12 序列，索引，同义词*********************

SEQUENCE--序列
INDEX--索引
SYNONYM--同义词

自动生成数据,数字不会重复
同共享的对象，可以为多个表创建序列
一般用来创建主键

create SEQUENCE dept_dept_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCACHE
NOCYCLE;
 
----查看所有序列
select sequence_name,min_value,max_value,increment_by,last_number
from user_sequences;

NEXTVAL---取下个序列值
CURRVAL---查看当前序列值
NEXTVAL必须在CURRVAL使用之前使用一次,才可以使用CURRVAL

insert into departments(department_id,department_name,location_id)
values(dept_dept_seq.NEXTVAL,'Support',2500);

----查看当前序列的值
select dept_dept_seq.CURRVAL
from dual;


---修改序列
alter SEQUENCE dept_dept_seq
INCREMENT BY 20
MAXVALUE 999999
NOCACHE
NOCYCLE;

修改后的序列,之前已经使用的数据不受到影响
修改必须符合表的逻辑

--删除序列

DROP SEQUENCE dept_dept_seq;


------索引

自动创建----主键约束,唯一性约束
手动创建----用户创建

create UNIQUE INDEX emp_last_name_idx
ON employees(last_name);

何时需要建索引
----很多数据
----有连接关系的字段建立索引
----有很多数据，但是每次只取很少的数据,在where的条件上建立索引

----查看所有索引信息
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';

-----函数索引
create INDEX upper_dept_name_idx
ON departments(UPPER(department_name));

select * from departments
where UPPER (department_name)='SALES';

----删除索引
DROP INDEX upper_last_name_idx;


----创建和删除同义词
create SYNONYM d_sum
FOR dept_sum_vu;

DROP SYNOYM d_sum;



*********************LESSON 13 权限*********************

系统安全 SYSTEM PRIVILEGE 针对数据库的权限,作用在DATABASE
数据安全 OBJECT PRIVILEGE 针对数据库中的对象，作用在SCHEMA

----SYSTEM PRIVILEGE   系统权限
create new users
remove users
remove tables
back up tables



----创建用户
create USER scott
IDENTIFIED BY tiger;

-----用户权限分配控制
create session
create table
create sequence
create view
create procedure


GRANT create session,create table,create sequence,create view
TO scott;


----ROLE 角色 用户的一组权限的集合
create ROLE manager;

GRANT create table ,create view,create procedure
TO manager;

GRANT manager TO DEHAAN,KOCHHAR;

---修改用户密码
alter USER scott
IDENTIFIED BY lion;


----OBJECT PRIVILEGE    对象权限
使用必须指明对象

GRANT select ON employees
TO sue,rich;

GRANT update (department_name,location_id)
ON departments
TO scott,manager;

----WITH GRANT OPTION scott可以把权限赋予给其他人
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  权限

REVOKE select,insert
ON departments
FROM scoot;

WITH GRANT OPTION----上级的权限被收回,他赋予的其他用户的权限也会被收回


----DB_LINK 可以从远程数据库获得数据
----创建数据库连接
create PUBLIC DATABASE LINK hq.acme.com
USING 'sales';
----使用数据库连接
select * from emp@HQ_ACME.COM;



*********************LESSON 14 WORKSHOP*********************

1.如何查询数据
2.选择数据
3.单列函数功能
4.从多表获取数据
5.分组查询数据
6.子查询
7.iSQL*Plus
8.操作数据    Manipulate Date
9.创建表
10.约束
11.创建视图
12.其他数据库对象
13.用户权限

sqlplus sys/gull@myoracle as sysdba;

show user;

create user yuanchi identified by yuanchi;

desc dba_users;

select username,created from dba_users where username='YUANCHI';

drop user yuanchi cascade;

conn yuanchi/yuanchi@myoracle;

conn sys/gull@myoracle as sysdba;

grant create session to yuanchi;

create table test (id number,name varchar2(20));--错误

conn sys/gull@myoracle as sysdba;

grant dba to yuanchi;

create table test (id number,name varchar2(20));

desc test;

alter table test add constraint pk_test_id primary key (id);

desc test;

insert intoo test values(1,'shy');

select * from test;

delete from test where id=1;

rollback;

select * from test;

insert into test(id) values (1);

insert into test(id) values (NULL);--错误

commit;

insert into test(id) values (2);
insert into test(id) values (3);
insert into test(id) values (2);--错误


create sequence seq_test start with 1;

select seq_test.currval from seq_test;---错误

select seq_test.nextval from seq_test;

select seq_test.currval from seq_test;

/ ---表示运行上一条语句、

insert into test(id) values(seq_test.nextval);

insert into test(id) select object_id from dba_objects where object_id > 6;

create view test_id_small as select * from test where id <10000;

create view test_id_big as select * from test where id >30000;

create view test_id_mid as select * from test where id <=30000 and id =>10000;

select count(*) from test where id<10000;

grant select on test_id_small to hr;

conn hr/hr@myoracle;

desc test_id_small;---错误,没有前缀

desc yuanchi.test_id_small;

select * from yuanchi.test_id_small where rownum<100;


match 匹配

*********************LESSON 15 集合操作*********************

order by子句只针对第一个select子句的列名和别名起作用
下面的select子句只要数据类型匹配就可以了
除了UNION ALL不排序,其余的都是默认排序的


UNION/UNION ALL---加操作
UNION----重复部分计算一次,可以去除重复
UNION ALL---重复部分计算2次,包含重复,性能较好,加order by排序

select employee_id ,job_id
from employees
UNION
select employee_id ,job_id
from job_history;

--UNION ALL 需要加order by排序
select employee_id ,job_id
from employees
UNION ALL
select employee_id ,job_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;

-----一些操作技巧,可以用0,NULL补全
select department_id,TO_NUMBER(NULL),location,hire_date
from employees
UNION
select department_id,location_id,TO_DATE(NULL)
from departments;

select employee_id,job_id,salary
from employees
UNION
select employee_id,job_id,0
from job_history;

---3个字符串作为数据,用UNION连接,再排序
COLUMN a_dummy NOPRINT
select 'sing' as 'My deeam',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;--根据第二个select子句进行排序


*********************LESSON 16 时间函数*********************

----TZ_OFFSET
select TZ_OFFSET('US/Eastern') from DUAL;
select TZ_OFFSET('Canada/Yukon') from DUAL;
select TZ_OFFSET('Europe/London') from DUAL;

----CURRENT_DATE
--alter SESSION 用来改变时区的操作
--SESSIONTIMEZONE 当前会话时区


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

----返回类型,TIMESTAMP WITH TIME ZONE;

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;

----LOCALTIMESTAMP
----默认不显示时区,不显示-05:00
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;


----DBTIMEZONE---服务器端的时区

select DBTIMEZONE from DUAL;

----SESSIONTIMEZONE---客户端的时区

select SESSIONTIMEZONE from DUAL;


----EXTEACT--从系统日期中抓取一些时间单位

select EXTRACT(YEAR FROM SYSDATE)
from dual;

select last_name,hire_date,EXTRACT(MONTH FROM HIRE_DATE)
from employees
where manager_id=100;

----FROM_TZ---该函数可以对TIMESTAMP的值进行转换

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;


----TO_TIMESTAMP---该函数把一个字符串转换为TIMESTAMP类型
select TO_TIMESTAMP('2000-12-01 11:00:00','YYYY-MM-DD HH:MI:SS')
from DUAL;


----TO_TIMESTAMP_TZ---该函数把一个带时区的字符串转换为TIMESTAMP类型

select TO_TIMESTAMP_TZ('1999-12-01 11:00:00 -8:00','YYYY-MM-DD HH:MI:SS TZH:TZM')
from DUAL;

----TO_YMINTERVAL---该函数可以对日期进行换算,1年零2个月后是什么时间

select hire_date,hire_date+TO_YMINTERVAL('01-02') AS HIRE_DATE_YMINITERVAL
from employees
where department_id=20;


*********************LESSON 17 高级分组查询*********************

STDDEV--偏差值

select AVG(salary),STDDEV(salary),COUNT(commission_pct),MAX(hire_date)
from employees
where job_id like 'SA%';

select department_id,job_id,SUM(salary),COUNT(employee_id)
from employees
GROUP BY department_id,job_id;


----ROLLUP 分类汇总,产生n+1的分组数据
----在GROUP BY子句后加强功能,除了聚合分组外，还会对第一列聚合分组,在忽视统计的列以外的列进行汇总统计

select department_id ,job_id,SUM(salary)
from employees
where department_id<60
GROUP BY ROLLUP (department_id,job_id);


----CUBE---多维分组统计,产生2的n次方的分组数据

select department_id ,job_id,SUM(salary)
from employees
where department_id<60
GROUP BY CUBE (department_id,job_id);


----GROUPING---返回值为0或1,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----简化CUBE和ROLLUP当中的一些组合的情况,在列较多，但是需要统计的不是很多的情况下使用

select department_id,job_id,manager_id,AVG(salary)
from employees
GROUP BY GROUPING SETS((department_id,job_id),(job_id,manager_id));


select department_id,job_id,manager_id,SUM(salary)
from employees
GROUP BY ROLLUP(department_id,(job_id,manager_id));


select department_id,job_id,manager_id,SUM(salary)
from employees
GROUP BY department_id,ROLLUP(job_id),CUBE(manager_id);



*********************LESSON 18 高级子查询*********************

--嵌套子查询,子查询的结果会被外面的主查询引用。
--子查询在主查询之前运行。

select last_name
from employees
where salary >(select salary from employees
        where employee_id=149);

---成对比较 Pairwise Comparisons
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);

---非成对比较 Nonpairwise Comparisons
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);


在FROM查询参数中编写子查询
---子查询作为查询参数
select a.last_name,a.salary,a.department_id,b.salavg
from employees a,(select department_id,AVG(salary) salavg
            from employees
            GROUP BY department_id) b
where a.department_id=b.department_id
AND a.salary > b.salavg;


---CASE语句
select employee_id,last_name,
  (CASE WHEN department_id=
  (select department_id from departments
    where location_id=1800)
  THEN 'Canada' ELSE 'USA' END) location
from employees;

---子查询作为ORDER BY的参数
department_name作为order by的参数

select employee_id,last_name
from employees e
ORDER BY(select department_name
    from departments d
    where e.department_id=d.department_id);

---Correlated Subqueries 级联查询
--外层查询参数作为内层查询的参数
select last_name,salary,department_id
from employees outer
where salary > (select AVG(salary) from employees
        where department_id = outer.department_id);



select e.employee_id,last_name,e.job_id
from employees e
where 2 <= (select COUNT(*) from job_history
        where employee_id = e.employee_id);


---EXISTS   NOT EXISTS ---比IN NOT IN 性能好

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


------Correlated UPDATE 级联更新

alter table employees
add(department_name varchar2(18));

UPDATE employees e
SET department_name = (select department_name
            from departments d
            where e.department_id = d.department_id);


----Correlated DELETE 级联删除

delete from employees E
where employee_id=(select employee_id
            from emp_history
            where employee_id = E.employee_id);


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



*********************LESSON 19 树形查询*********************

----START WITH 起始点---定义根节点
START WITH last_name='Kochhar'


----CONNECT BY PRIOR 连接条件--通过什么条件匹配子节点
CONNECT BY PRIOR employee_id = manager_id



----从底层到顶层
select employee_id,last_name,job_id,manager_id
from employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id;

----从顶层到底层
select last_name ||' reports to '||
PRIOR last_name "Walk Top Down"
from employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;


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;


---不显示Higgins的节点的两种方法
---WHERE last_name != 'Higgins'

---CONNECT BY PRIOR employee_id=manager_id
AND last_name != 'Higgins'



*********************LESSON 20 扩展DDL DML*********************
--普通的插入数据方式
insert into departments(department_id,department_name,
            manager_id,location_id)
values(70,'Public Relations',100,1700);

--普通的更新方式
update employees
set department_id = 70
where employee_id =142;


----INSERT ALL INTO一个查询的结果可以一次插入多个表中

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



---Conditional INSERT ALL 有条件多表插入

INSERT ALL
WHEN SAL > 10000 THEN
    INTO sal_history values(EMPID,HIREDATE,SAL)
WHEN MGR >200 WHEN
    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;



----Conditional FIRST INSERT 如果第一个条件满足,后面的条件不会再判断,不满足的话,才会进入下面的条件判断

INSERT FIRST   
WHEN SAL > 25000 WHEN
    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;



---Pivoting INSERT--把非关系型的数据转化为关系型数据插入到表中
把子查询的数据拆开为5条数据

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;


---External Table --外部表
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 --目录DEFAULT DIRECTORY是必须的。
ACCESS PARAMETER
(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;

--执行这个语句时，就会根据表的信息去外部的文件中查找数据
select * from oldemp

---创建带索引的主键
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_INDEXES
WHERE TABLE_NAME='NEW_EMP';


 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16313359/viewspace-682360/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16313359/viewspace-682360/