Oracle数据库笔记

Oracle的安装：

在安装Oracle数据库的时候，请以管理员的身份运行。

导入数据文件：

@文件路径

如：@C:/Users/Administrator/Desktop/a/userinfo.sql;

注意要修改文件的分级符号

通过PL/SQL 切换登录用户：connect   scott/tiger;

SQL语言大小写不敏感（是指关键字）。

select 8*8 from dual;--dual为伪表

在数据库中，空值不等于0，包含空值的数学表达式都为空。

当给字段起别名时，字段的别名如果有空格，则需要使用双引号引起来（这是Oracle唯一使用到双引号的地方），使用双引号后，是严格区分大小写的。

如：select employee_id,last_name Name ,salary ,salary*12*(1+commission_pct) "annual salary" from employees;--该语句输出后Name字段是首字母大写的。

select last_name||'`s job_id is'|| job_id from employees;--使用连接符进行连接。

select distinct department_id from employees;--去除重复行，使用distinct

select employee_id||','||last_name||','||salary output_put from employees;--显示所有列，各个列之间用逗号隔开

 select employee_id,last_name from employees where lower(last_name)='grant';

--查询last_name 为Grant的员工，最好使用lower()函数。

select *from employees where hire_date='7-2月-1999';--查询1999/2/7入职的员工信息。注意：字符和日期需要使用单引号。

或者使用：select *from employees where to_char(hire_date,'yyyy-mm-dd')='1999-02-07';

或者使用：select *from employees where hire_date=to_date('1999-02-07','yyyy-mm-dd');

select department_id,last_name ,salary from employees where salary between 6000 and 7000;

--查询工资大于6000小于7000 的员工（是包含边界的）  使用between...and...  注：不在范围内 not between...and...

 select *from employees where department_id in (40,50,60,70);--使用in关键字

select employee_id,last_name salary from employees where last_name like '%z%';

--查询last_name中含有字符“z”的员工信息。——使用like关键字，百分号表0到n个字符。

select employee_id,last_name salary from employees where last_name like '_z%';

--查询last_name中第二个字符是“z” 的员工信息。——使用like关键字和“_”相结合

select employee_id,last_name,salary from employees where last_name like '_\_%' escape '\';

--查询last_name中第二个字符是“_”的员工信息。——使用like关键字结合escape关键字。

select employee_id,last_name,salary from employees where last_name like '_t_t_t_t_%' escape 't';

--查询第二到第四个字符为“_”的员工信息。

 select*from employees where commission_pct is null;--查询奖金率为空的员工。非空使用is not null。

select employee_id,last_name ,salary from employees where department_id=80 order by  salary desc;

--查询第80号部门的员工的信息，按薪资从高到低排。——使用order by...  默认的是升序。

select employee_id,last_name ,department_id,salary from employees  order by  salary desc, department_id desc,last_name ;

--按薪资降序，部门降序，last_name升序来排。

select last_name,job_id ,hire_date from employees where hire_date between '01-2月-1998' and '01-5月-1998';

查询两个时间段之间入职的员工信息。

 select last_name from employees where last_name like '%a%e%' or last_name like '%e%a%';

--查询员工姓名中含有a和e（注意不是a或e） 的员工信息。

单行函数：一条记录返回一个结果。

多行函数：多条记录返回一个结果。分组函数就是典型的多行函数。

lower(args)、upper(args)、initcap(args)   initcap()函数是首字母大写

字符控制函数：

concat('Hello','World')     --HelloWorld

substr('HelloWorld',1,5) --Hello 注意：select substr('123456789',3,7) from dual; 结果为3456789。下标是从1开始的

length('HelloWorld')  --10

instr('HelloWorld','W') --6

lpad(salary ,10,'*')   --*****24000

right(salary,10,'*') --24000*****

trim('H'  from  'HelloWorld')  --elloWorld  注意：只去除前后的匹配字符，不会去除中间的匹配字符。

replace('abcd','b','m')  --amcd

数字函数：

round(45926,2) --45.93   

--保留小数点后2位（默认是保留整数）   round(45926,-1) 结果为 45930  为保留小数点前一位进行四舍五入。

trunc(45.926,2) --45.92  截断  默认是截取整数。

mod(1600,300) --100  求余

months_between(sysdate,hire_date)  --返回两个日期间的月份，包含小数点

add_months(sysdate,2)  

next_day(sysdate,'星期四')    --求下个星期* 的具体日期。

last_day(hire_date) --求该日期的最后一天 

select last_name, hire_date ,salary from employees where last_day(hire_date)-1=hire_date;

--求每个月倒数第二天进公司的员工。

round() 和trunc()  :

select round(sysdate ,'mm') ,round(sysdate,'month') ,trunc(sysdate,'hh') ,trunc(sysdate,'year') from dual;

--2017/9/1 星期五     2017/9/1 星期五        2017/8/17 星期四 下午 11 2017/1/1 星期日

转换函数：

--若字符串中没有特殊字符，可以进行隐式转化。 如：select '123.45'+100 from dual;

to_char(hire_date,'yyyy-mm-dd')--日期转字符串

to_date('1994-06-07','yyyy-mm-dd') --字符串转日期

select to_char(1234567.89,'999,999,999.99') from dual;--1,234,567.89

select to_char(1234567.89,'000,000,999.99') from dual;--001,234,567.89

select to_char(1234567.89,'L999,999,999.99') from dual;--￥1,234,567.89  注：L代表本地符号。

select to_char(1234567.89,'$999,999,999.99') from dual;--$1,234,567.89

select to_number('￥1,234,567.89','L999,999,999.99') from dual;  --字符串转数字

通用函数：

--这些函数适用于任何数据类型，同时也适用于空值。

nvl(expr1,expr2)  :若expr1的值为null ,则整个表达式的值为expr2 。否则，为expr1。

select last_name,nvl(to_char(department_id),'没有部门') from employees ;    

--输出last_name,department_id,当department_id为null时，显示“没有部门”。

nvl2(expr1,expr2,expr3)：expr1不为空时，返回expr2,为空时，返回expr3

nullif(expr1,expr2)  --注：不常用

coalesce--注：不常用

条件表达式：

case...

when...then...

when...then...

else...

end

例：给员工加工资，当部门是10时，乘1.1倍，20时，乘1.2倍，30时，乘1.3倍。

select employee_id,last_name,department_id ,
case department_id 
  when 10 then salary*1.1 
    when 20 then salary*1.2
      else salary*1.3
        end salary 
        from employees 
        where department_id in (10,20,30)

decode(表达式...)--实际上是case表达式的简化版。

select employee_id,last_name,department_id ,
decode( department_id,
        10,salary*1.1,
        20,salary*1.2,
        salary*1.3) salary 
        from employees 
        where department_id in (10,20,30)  --括号后面的salary是别名

select employee_id ,department_name from employees e ,departments d where e.department_id=d.department_id;

--等值连接，因为过滤条件是相等的。

select employee_id,last_name,grade_level  from employees e,job_grades g where e.salary>=lowest_sal and e.salary<=highest_sal;

 --或者select employee_id,last_name,grade_level  from employees e,job_grades g where e.salary between g.lowest_sal and highest_sal;

--非等值连接 。求出员工的工资等级。

内连接：合并具有同一列的两个以上的表的行，结果集中不包含一个表与另一个表不匹配的行。

select* from employees e inner join departments d on e.department_id=d.department_id

（相当于select* from employees e inner join departments d where  e.department_id=d.department_id）

外连接：两个表在连接过程中除了返回满足连接条件的行以外还返回左（或右）表中不满足连接条件的行，这种连接称为左（右）连接。没有匹配的行时，结果表中相应的列为空（null）。外连接的Where子句条件类似于内部连接，但连接条件中没有匹配的表的列后面要加外连接运算符，即用圆括号括起来的加号（+）。除了左外连接和右外连接外，还有全连接。全连接可以看成是左外连接跟右外连接的并集。

左外连接：(可以理解为以左表为准，右表在需要的时候补充空行来满足连接条件的查询)。

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

select employee_id,last_name ,department_name from employees e left outer join departments d on e.department_id =d.department_id;   

--查询全部员工的employee_id,last_name,department_name。没有部门的员工也需要显示。

右外连接：(可以理解为以右表为准，左表表在需要的时候补充空行来满足连接条件的查询)。

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

 select employee_id,last_name ,department_name from employees e right outer join departments d on e.department_id=d.department_id;

--查询全部员工的employee_id,last_name,department_name。没有员工的部门也需要显示。

满外连接（或者全外连接）：

select employee_id,last_name ,department_name from employees e full outer join departments d on e.department_id=d.department_id;

--查询全部员工的employee_id,last_name,department_name。没有部门的员工和没有员工的部门名称都需要显示出来

--全外连接无法使用（+），只能使用99语法提供的方法。

使用join...on 进行多表连接：

select employee_id,d.department_id,department_name,city 
from employees e 
join departments d on e.department_id=d.department_id
join locations l on d.location_id=l.location_id

select e2.employee_id, e2.last_name from employees e1,employees e2 where e1.manager_id=e2.employee_id and e1.last_name='Chen'

查询公司中员工‘Chen’的manager的信息。

select last_name,department_name,l.location_id,city 
from employees e left outer join departments d on e.department_id=d.department_id
left outer join locations l on d.location_id=l.location_id 
where e.commission_pct is not null

--选择所有有奖金的员工的last_name、department_name、location_id、city

分组函数：avg、count、max、min、sum、group by、having

注意：所有的组函数都是计算列的值不为null 的数据的值。

max()和min()函数，可以使用数字、字符、日期作为字符串（适用于任何数据类型）。

如：max(last_name)   max(hire_date)   max(salary)

avg()和sum()函数，只能使用number作为参数。

count()函数，适用于任何数据类型。count(1)=count(salary)

avg(args)=sum(args)/count(args)  --args为某一个表达式

select sum(commission_pct)/count(nvl(commission_pct,0)) from employees;

--计算公司所有员工的平均奖金率。

count(distinct expr)  :返回expr非空且不重复的记录总数。

1、where子句必须紧跟在from子句后面

2、使用group by时，出现在select 子句中的非分组函数的列必须出现在group by子句中。反过来，出现在group by子句中的列可以不必出现在select 子句中。

3、不能在where子句中使用分组函数，需要使用的时候需要使用having关键字。

4、having子句和group by子句可以互相调换位置。

5、order by子句必须放在分组函数最末。

select j.job_title,avg(salary)  from employees e ,jobs j where  e.job_id=j.job_id having avg(salary)>6000  group by j.job_title

--查询平均工资高于6000的job_title 有哪些

where子句和having子句的区别：

 1.用的地方不一样

   where可以用于select、update、delete和insert...into语句中。

   having只能用于select语句中

 2.执行的顺序不一样

   where的搜索条件是在执行语句进行分组之前应用（所以多表连接条件不能写在having里面）

   having的搜索条件是在分组条件后执行的

   即如果where和having一起用时，where会先执行，having后执行

 3.子句有区别

   where子句中的大部分条件表达式having都可以跟，而having子句中的有些表达式where不可以跟；having子句可以用集合函数（sum、count、avg、max和min），而where子句不可以。

select d.department_name ,location_id,count(employee_id),avg(salary) 
from employees e right outer join departments d on e.department_id=d.department_id
group by d.department_name,location_id

--查询所有部门的名字，location_id,员工数量和工资平均值。

--注：如果count(employee_id)改为count(*)或count(1)，则没有员工的部门，其员工数量会显示1，不符合实际情---况。

select count(*) total,
count(decode(to_char(hire_date,'yyyy'),'1995',1,null)) "1995",
count(decode(to_char(hire_date,'yyyy'),'1996',1,null)) "1996",
count(decode(to_char(hire_date,'yyyy'),'1997',1,null)) "1997",
count(decode(to_char(hire_date,'yyyy'),'1998',1,null)) "1998"
from employees where to_char(hire_date,'yyyy') between 1995 and 1998

查询公司在1995-1998年之间，每年雇用的人数，结果类似下面的格式。

     TOTAL       1995       1996       1997       1998
---------- ---------- ---------- ---------- ----------
        65          4         10         28         23

select * from employees where employee_id=(select manager_id from employees where last_name='Chen')

查询Chen的manager的信息

子查询（内查询）在主出现之前一次执行完成

子查询的结果被主查询（外查询）使用

单行子查询：子查询返回一个结果的语句

多行子查询：子查询返回多个结果的语句。多行子查询需要使用多行子查询关键字（in、any、all）

查询最低工资大于50号部门最低工资的部门id和其最低工资。

select department_id,min(salary) 
from employees 
having min(salary)>(select min(salary) from employees where department_id=50)
group by department_id

返回其它部门中比job_id为‘IT_PROG’部门任一工资低的员工的员工号、姓名、job_id、以及salary

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'

查询平均工资最低的部门id

select department_id 
from employees 
having avg(salary)=(select min(avg(salary)) from employees group by department_id)
group by department_id

查询平均工资最低的部门信息和该部门的平均工资

方法一：

select d.*,d2.av
from departments d,

(select department_id,avg(salary) av from employees having avg(salary)=(select min(avg(salary)) from   employees group by department_id) group by department_id) d2 where d.department_id=d2.department_id

方法二：

select d.*,(select avg(salary) from employees where department_id=d.department_id)
from departments d where department_id=(select department_id from employees 

                                        having avg(salary)=(select min(avg(salary)) from employeesgroup by department_id)

                                        group by department_id)

--查询平均工资最高的job 信息
select * from jobs  where 
job_id in (select job_id 
           from employees 
           having avg(salary)=(select max(avg(salary))
                               from employees 
                               group by job_id)
           group by job_id)

--查询公司中所有manager的详细信息。
select * from employees 
where employee_id in (select distinct manager_id from employees)

--查询部门中最高工资中最低的那个部门的最低工资是多少
select min(salary) from employees 
where department_id in (select department_id from employees 
      having max(salary)=(select min(d.mx) from (select max(salary) mx from employees 
      group by department_id) d)
      group by department_id)
      group by department_id

--查询平均工资最高的部门的manager的详细信息：
select last_name,department_id,email,salary
from employees 
where employee_id in (
      select manager_id from employees where department_id in (
             select department_id from employees  having avg(salary)=(
                    select max(avg(salary)) from employees group by department_id
             ) group by department_id
      )
)

--查询1999年来公司的员工中的最高工资的那个员工的信息。
select  employee_id ,last_name ,salary 
from employees 
where salary=(select max(salary) from employees where to_char(hire_date,'yyyy')=1999)
and to_char(hire_date,'yyyy')=1999

--查询各部门中工资比本部门平均工资高的员工的员工号，姓名和工资
select employee_id,last_name,salary 
from employees e
where salary>(select avg(salary) from employees where department_id=e.department_id group by department_id)

--查询和姓名中包含字母u的员工在相同部门的员工的员工号和姓名
select employee_id ,last_name 
from employees 
where department_id in (
      select department_id from employees where last_name like '%u%'
) and last_name not like '%u%'

DML语句：insert、update、delete、select （行级的：增删改查）

DCL语句：create table、alter table、drop table、create index、drop index（表级的：创建和管理表）

DCL语句：grant、revoke、commit、rollback、savepoint、lock（数据库级的：控制对数据库的访问）

常见的数据库对象：表、视图、序列、索引、同义词

--查询当前用户创建的有哪些表

select*from user_tables;

--查询创建的数据库对象：表、视图、同义词、序列

select*from user_catalog;

创建表：

方式一：

create table emp1(
id number(10),
name varchar2(20),
salary number(10,2),
hire_date date
)

方式二：

create table emp2 as select * from employees;

create table emp3 as select * from employees where 1=2;--创建一张空表

错误提示：
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
ora-00119【ora-00119 invalid specification for system parameter】
ora-00132【syntax error or unresolved network name 'LISTENER_ORCL'】
造成原因：数据库异常关闭，造成数据库启动文件异常。
①将F:\app\Administrator\product\11.2.0\dbhome_1\NETWORK\ADMIN路径下的listener.ora文件打开，
复制(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))；
②打开F:\app\Administrator\admin\orcl\pfile，将以上复制的内容粘贴到init.ora.7282016191252文件的local_listener中
即local_listener="(ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))"
③命令行输入sqlplus/nolog
④conn/as sysdba
⑤startup pfile=‘F:\app\Administrator\admin\orcl\pfile\init.ora.7282016191252’
⑥create spfile from pfile=‘F:\app\Administrator\admin\orcl\pfile\init.ora.7282016191252’
待提升“数据库装载完毕、数据库已经打开”即可。


设置监听程序的时候，记得需要以“系统管理员的身份打开”。
在Net Manager中，监听位置的主机可以写localhost，监听程序的数据库服务中Oracle主目录可以不用设置（即为空）
设置Net Manager后，记得重启服务和监听，再打开Net Configaration Manager进行网络服务名的测试或者其他操作。

ALTER TABLE语句：

使用ALTER TABLE 语句可以：

①追加新的列

  alter table emp1 
add (email varchar2(20),department_id number(2))

②修改现有的列

③为新追加的列定义默认值

alter table emp1 
modify (department_id number(4) default 80)

④删除一个列

alter table emp1
drop  column email

⑤重命名表的一个列名

alter table emp1 
rename column department_id to departmentid

⑥设置列不可用

alter table emp

set unused column test_column

删除（ alter table emp drop unused columns;）

只有增删改才能进行回滚

删除表：drop table emp1;

清空表：truncate table emp1;(不可回滚）

删除数据：delete from emp1；

重命名表：rename emp2 to employees2;

把employees表中80号部门的所有数据复制到myemp1中：

insert into myemp1 select department_id,last_name,salary,email from employees where department_id=80;

create table emp1 as select employee_id,last_name,hire_date,salary from employees;

insert into emp1(employee_id,last_name,hire_date) 

values(1002,'陈晓镇',to_date('1999.4.23','YYYY.mm.dd'));

insert into emp1 values(&id,'&name','&hiredate',&salary);--从屏幕获取值

update emp1 set salary=10000 where employee_id=179;

--从emp1表中删除dept1不卖名称中含有Public字符的部门id
delete   from emp2 e where department_id=(select department_id from departments where department_name like '%Public%')

commit rollback  savepont

rollback to savepoint A;

其他用户不能看到当前用户所做的改变，直到当前用户结束事物。

DML语句所涉及的行被锁定，其他用户不能操作。

select*from scott.employees for update;

--更改108号员工的信息：使其工资变为所在部门中的最高工资，job变为公司中平均工资最低的job.
update employees 
set salary=(select max(salary) from employees where department_id=(select department_id from employees where employee_id=108)),
    job_id=(select job_id from employees having avg(salary) =(select min(avg(salary)) from employees group by job_id) group by job_id)
where employee_id=108

--删除108号员工所在部门中工资最低的那个员工。
 delete from employees where salary = (select min(salary) from employees where department_id=(select department_id from employees where 
 employee_id=108
 )) and department_id=(select department_id from employees where employee_id=108)

优化：

 delete from employees e 
 where salary = (select min(salary) 
                  from employees 
                  where department_id=(e.department_id)) 
 and department_id=(select department_id 
                    from employees 
                    where employee_id=108)

列级约束有六种：主键Primary key、外键foreign key 、唯一 unique、检查 checck 、默认default 、非空/空值 not null/ null
表级约束有四种：主键、外键、唯一、检查

constraint：约束

添加非空约束

create table emp2(
id number(10) constraint emp2_id_nn not null,
name varchar2(20) not null,
salary number(10,2)
)

create table emp3(
--列级约束
id number(10) constraint emp3_id_uk unique,
name varchar2(20) constraint emp3_name_nn not null,
email varchar2(20) ,
salary number(10,2),
--表级约束
constraint emp3_email_uk unique(email)
)

primary key 是not null和unique的联合体

create table emp6(
--列级约束
id number(6),
name varchar2(20) constraint emp6_name_nn not null,
email varchar2(20),
salary number(10,2) check(salary>1500 and salary<30000),
department_id number(10),
--表级约束
constraint emp6_email_uk unique(email),
constraint emp6_id_pk primary key(id),
constraint emp6_dept_id_fk foreign key(department_id) references departments(department_id)
)

on delete cascade(级联删除):当父表中的列被删除时，字表中相对应的列也被删除

on delete set null(级联置空):当父表中的列被删除时，字表中相应的类置空

create table myemployees(
id number(10) primary key,
name varchar2(20) not null,
email varchar2(30) unique,
job_id number(10)  references myjobs(job_id) on delete cascade,
department_id number(10) references mydepartments on delete set null
)

可以添加或者删除约束，但是不能修改约束

有效化或无效化约束

添加not null约束要使用modify 语句

alter table emp5 modify(salary number(10,2) not null);

alter table emp5 drop constraint SYS_C0016392;--删除约束

alter table emp5 add constraint emp5_name_uk unique(name)--添加约束

alter table emp5 disable constraint emp5_name_uk;--无效化约束

alter table emp5 enable constraint emp5_name_uk;--有效化约束（有效化之前需要删除重复的数据）

约束需要注意的 地方：

1）非空约束（not null）只能定义在列级

2）唯一约束（unique）的列值可以为空

3）外键（foreign key）引用的列起码要有一个唯一约束

alter table myemp add constraint emp_id_uk unique(job_id);

alter table myemployees add constraint myemployees_job_id_fk foreign key(job_id) references myemp(job_id); --若被引用的myemp(job_id)没有唯一性约束或者主键约束，会提示“ORA-02270: 此列列表的唯一关键字或主键不匹配”

--向表emp2中添加列dept_id,并在其中定义foreign key约束。与之相关联的列是dept2表中的id列。

alter table emp2 add (dept_id number(10,2) constraint emp2_dept_id_fk   references dept2(id));

表：基本的数据存储集合，由行和列组成。

视图view：从表中抽出的逻辑上相关的数据集合。

序列sequence：提供有规律的数值。

索引index：提高查询的效率

同义词synonym：给对象起别名

视图的作用：

1）控制数据访问

2）简化查询

3）避免重复访问相同的数据

创建视图：

createor replace view empview 
as 
select employee_id,last_name,salary
from employees 
where department_id=80

基于多表创建视图（此时仍然可以通过修改视图来达到修改基表的目的）：

create or replace view empdeptview 
as select employee_id,salary,department_name
from employees e ,departments d
where e.department_id=d.department_id and d.department_id=80 with read only

使用了分组函数的视图就是复杂视图

create or replace view empview3
as select department_name, sum(salary) sumsal
from employees e,departments d 
where e.department_id=d.department_id
group by department_name

当视图定义中包含以下元素之一时不能使用DML操作:

1)组函数

2）group by子句

3)distinct 关键字

4）rownum 伪列

5）列的定义为表达式

Top-N分析

查询公司中工资前10的员工的工资

select rownum,employee_id,last_name,salary
from (select employee_id,last_name,salary 
from employees 
order by salary desc)
where rownum<=10

对rownum只能使用 < 或 <= ，而用 = ，> , >= 都将不能返回任何数据。

select no ,employee_id,last_name,salary
from (select rownum no,employee_id,last_name,salary
from (select employee_id,last_name,salary 
from employees 
order by salary desc))
where no>40 and no<=50

默认情况下，rownum按主索引来排序，若没有注索引则自然排序。

定义序列：

create sequence sequence

[increment by n] --每次增长的数值

[start with n]  --从哪个值开始

[maxvalue n] --最大值

[minvalue n] --最小值

[cycle|nocycle] --是否循环

[cache n|no cache] --是否缓存登录

如：

create sequence empseq
start with 10
increment by 10
maxvalue 100
cycle 
nocache

select empseq.nextval from dual;

select empseq.currval from dual;

应用：

insert into emp01 
values(empseq.nextval,'xiaohong',856)

修改：alter sequence empseq increment by 1 nocache;

改变序列的初始值只能通过删除序列之后重建序列的方法实现。

序列在下来情况下出现裂缝：

1）回滚

2）系统异常

3）多个表同时使用同一序列

删除序列：drop sequence empseq;

创建索引

create index emp01_id_ix on emp01(employee_id);

什么时候创建索引
1）列中数据值分别范围很广

2）列经常在WHERE子句或连接条件中出现

3）表经常被访问而且数据量很大，访问的数据大概占数据总量的2%到4%。

同义词：synonym

create synonym e for employees;

select*from e;

同义词的作用：

1）方便访问其它用户的对象

2）缩短对象名字的长度

创建用户：

 create user atguigu1 identified by atguigu1;

解锁用户：

alter user scott account unlock

创建用户的同时给用户赋权限：

grant connect ,resource to atguigu2 identified by atguigu2;

grant create table to atguigu2;

赋予用户使用users表空间的权限

alter user atguigu01 quota unlimited on users

alter user atguigu01 quota 1k on users;

创建角色 

create role manager；

为角色赋予权限

grant create table，create view to manager;

将角色赋予用户

grant manager to kim,hang;

grant select,update on scott.employees to atguigu01;

使用with grant option是用户同样具有分配权限的权利

向数据库中所有用户分配权限

grant select on scott.departmentsto public 

回收权限

 revoke select on employees from atguigu;

SET运算符

UNION/UNION ALL  

INTERSECT 交集

MINUS  差集

select employee_id,department_id from employees01 union select employee_id,department_id from employees02;

set运算符左右两边的列数和运算符都必须保持一致。

给结果集起的列起别名

select employee_id emp_id,department_id dept_id from employees01 union select employee_id,department_id from employees02;

求差集

select employee_id,department_id from employees01 minus select employee_id,department_id from employees02;

select employee_id,department_id ,to_char(null) 
from employees01
union all
select to_number(null),department_id,department_name
from departments

select 'study at' as "My Dream",2
from dual
union
select 'I want to', 1
from dual
union 
select 'atguigu.com',3
from dual order by 2

查询10,50,20号部门的job_id,department_id,并且department_id按10,50,20的顺序排列。

column a_dummy noprint;不打印 被a_dummy标记的列

--查询10,50,20号部门的job_id,department_id,并且department_id按10,50,20的顺序排列。

column hide noprint;
select job_id,department_id,1 hide
from employees where 
department_id=10
union 
select job_id,department_id,2
from employees where 
department_id=50
union
select job_id,department_id,3
from employees where 
department_id=10
order by 3

高级子查询

1、多列子查询

--查询与141号或174号员工的manager_id和department_id相同的其他员工的employee_id,manager_id,department_id.

select employee_id,last_name,manager_id,department_id from employees 
where (manager_id,department_id) in(select manager_id,department_id from employees where 
employee_id=174 or employee_id=141) and employee_id not in (141,174)

2、在from子句中使用子查询

--返回比本部门平均工资高的员工的last_name,department_id,salary及平均工资。

select last_name,e1.department_id,salary ,av from 
employees e1,(select department_id ,avg(salary) av from employees group by department_id) e2
where e1.department_id=e2.department_id and e1.salary>e2.av

3、单列子查询

--显示员工的employee_id,last_name和location.

--其中，若员工department_id与location_id 为1800的department_id相同，则location为‘Canada’，其余则为‘USA’。

select employee_id,last_name,
decode(department_id,(select department_id from departments where location_id=1800),'Canada','USA') location
from employees 

4、相关子查询

   相关子查询按照一行接一行的顺序执行，主查询的每一行都执行一次子查询。

  --查询员工中工资大于本部门平均工资的员工的last_name,salary和其department_id
select last_name,salary,department_id
from employees o
where salary>(select avg(salary) from employees  where department_id=o.department_id)

--若employees表中employee_id与job_history表中employee_id相同的数目不小于2，

--输出这些相同id员工的employee_id，last_name和其job_id.

select employee_id,last_name,job_id 
from employees e
where (select count(*) from job_history where employee_id=e.employee_id)>=2 

exists  和 not exists

--查询公司管理者的employee_id,last_name,job_id,department_id 信息

方法一：
select  employee_id,last_name,job_id,department_id 
from employees e1
where e1.employee_id in (select distinct manager_id from employees)

方法二：

select  employee_id,last_name,job_id,department_id 
from employees e1
where e1.employee_id in (select  manager_id from employees e2 where e1.employee_id=e2.manager_id)

方法三：

select  distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_id 
from employees e1,employees e2
where e1.employee_id=e2.manager_id--注意，这里如果写成where e1.manager_id=e2.employee_id的不同

5、exists和not exists

方法四：

select  distinct e1.employee_id,e1.last_name,e1.job_id,e1.department_id 
from employees e1
where exists (select 1 from employees e2 where e1.employee_id=e2.manager_id)

--查询departments 表中，不存在于employees表中的部门的department_id 和 department_name
select department_id,department_name 
from departments d
where not exists (select 1 from employees e where d.department_id=e.department_id)

6、相关更新

create table employee011 as select*from employees ;

alter table employee011 add(department_name varchar(20));

--更新语句

update employee011 e set department_name=(select department_name from departments where department_id=e.department_id);

7、使用with子句

使用with子句，可以避免在select语句中重复书写相同的语句块

with子句将该子句中的语句块执行一次并存储到用户的临时表空间中

使用with子句可以提高查询效率

--查询公司中工资比Abel高的员工的信息
select employee_id,last_name 
from employees 
where salary>(select salary from employees where last_name='Abel')

--改为with语句

with Abel_salary as ( select salary from employees where last_name='Abel')--使用with创建一个临时表

select employee_id,last_name 
from employees 
where salary>(select salary from Abel_salary)

--两个语句必须同时执行，同时创建两个临时表时，从第二个临时表开始，with需要去掉。

--查询公司中各部门的总工资大于公司中各部门的平均总工资的部门信息
select department_id ,department_name 
from departments 
where department_id in (select department_id from employees 
having sum(salary)>(select sum(salary)/count(distinct department_id) from employees ) group by department_id)

--查询员工的last_name,department_id,salary.其中员工的salary,department_id 与有奖金的任何一个员工的salary，department_id相同即可。
select last_name,department_id,salary 
from employees 
where (salary,department_id) in
(select salary,department_id from employees where commission_pct is not null)

--选择所有没有管理者的员工的last_name
方法一：
select last_name 
from employees e
where not exists (select 1 from employees where employee_id=e.manager_id)
方法二：
select last_name from employees where manager_id is null

PL/SQL

PL/SQL是ORACLE系统的核心语言，现在ORACLE的许多部件都是由PL/SQL写成。在PL/SQL中可以使用的SQL语句有：

INSERT,UPDATE,DELETE,SELECT...INTO,COMMIT,ROLLBACK,SAVEPOINT

提示：在PL/SQL中只能用SQL语句中的DML部分，不能用DDL部分，如果要在PL/SQL中使用DDL（如create table等）的话，只能以动态的方式来使用。

PL/SQL程序由三个块组成，即声明部分、执行部分、异常处理部分。其中，执行部分是必须的（end也是必须的）。

在使用PL/SQL 前，需要设置 set serveroutput on才能进行输出

PL/SQL块格式：

declare 
--声明的变量、类型、游标
begin
  --程序执行部分
exception
  --针对begin块中出现的异常，提供处理的机制
  --when...then...  
  --when...then... 
end; 

PL/SQL建议的命名方法：

程序变量：V_name

程序常量：C_Name

异常标识：E_name

游标变量：Name_cursor

记录类型：Name_record

declare

--声明变量

  v_sal varchar(20);
begin

--sql语句的操作
  select salary into v_sal from employees where employee_id=100;
  dbms_output.put_line(v_sal);
end;

declare 
  v_sal number(10,2);--若定义的空间太小，会提示字符串缓冲区不足，可以定义为"employees.salary%type";
  v_email varchar2(20);
  v_hire_date date;
begin
  select salary,email,hire_date into v_sal,v_email,v_hire_date from employees where employee_id=100;
  dbms_output.put_line(v_sal);
  dbms_output.put_line(v_email||','||v_hire_date);
end;    

记录类型：记录类型是把逻辑相关的数据作为一个单元存储起来，称作PL/SQL RECORD的域（FIELD），其作用是存放不相同但逻辑相关的信息。

declare 

 --声明一个记录类型
  type emp_record is record(
  v_sal employees.salary%type,
  v_email employees.email%type,
  v_hire_date employees.hire_date%type);

--定义一个记录类型的成员变量
  v_emp_record emp_record;
begin
  select salary,email,hire_date into v_emp_record from employees where employee_id=100;
  dbms_output.put_line(v_emp_record.v_sal);
  dbms_output.put_line(v_emp_record.v_email);
  dbms_output.put_line(v_emp_record.v_hire_date);
end;

PL/SQL的学习内容：

1、PL/SQL基本的语法格式

2、记录类型：type...is record(,,,);

3、流程控制：

3.1条件判断（两种）

     方式一：if...then... elsif ...then...else...end if;

     方式二：case...when...then...end;

3.2 循环结构（三种）

     方式一：loop...exit when...end loop;

     方式二：while...loop...end loop;

     方式三：for i in ....loop ...end loop;

3.3 goto、exit

4、游标的使用（类似于Java中的Iterator）

5、异常的处理（三种方式）

6、会写一个存储函数（有返回值）、存储过程（没有返回值）

7、会写一个触发器（第六跟第七是学习PL/SQL的主要目标）

declare 
  type emp_record is record(
       v_name varchar2(20),
       v_salary number(10,2)
  );
  v_emp_record emp_record;
begin
  v_emp_record.v_name:='刘德华';
  v_emp_record.v_salary:=120000;
  dbms_output.put_line( v_emp_record.v_name||'的工资是：'||v_emp_record.v_salary);
end;  

declare 
  v_emp_record employees%rowtype;
begin
  select * into v_emp_record from employees where employee_id=123;
  dbms_output.put_line(v_emp_record.last_name);
  dbms_output.put_line(v_emp_record.salary);
end;  

使用%rowtype特性的优点在于：

1、所引用的数据库中列的个数和数据类型可以不必知道；

2、所引用的数据库中列的个数和数据类型可以实时改变。

执行更新操作：
declare
  v_emp_employee_id number(10);
begin
  v_emp_employee_id:=204;
  update employees set salary=12000 where employee_id=v_emp_employee_id;
end; 


条件表达式
一个判断条件：
if <布尔表达式> then SQL或PL/SQL表达式;
end if;


两个判断条件：
if<布尔表达式> then SQL或PL/SQL表达式;
else 其它语句;
end if;


两个以上判断条件：
if<布尔表达式> then SQL或PL/SQL表达式;
elsif<其它布尔表达式> then 其它语句;
elsif<其它布尔表达式> then 其它语句;
else 其它语句;
end if;


--查询出150号员工的工资，若其工资大于或等于10000则打印‘salary>=10000';
--若在5000到10000之间，则打印'5000<=salary<10000',否则打印’salary<5000'
declare 
  v_emp_salary employees.salary%type;
begin
  select salary into v_emp_salary from employees  where employee_id=150;
  if v_emp_salary>=10000 then dbms_output.put_line('salary>=10000');
    elsif v_emp_salary>=5000  then dbms_output.put_line('5000<=salary<10000');
    else dbms_output.put_line('salary<5000');
    end if;
end;


case 值
when 比较值1 then 结果1
when 比较值2 then 结果2
when 比较值3 then 结果3
【else 结果4】
end;


--查询出150号员工的工资，若其工资大于或等于10000则打印‘salary>=10000';
--若在5000到10000之间，则打印'5000<=salary<10000',否则打印’salary<5000'
declare 
  v_emp_salary employees.salary%type;
  v_temp varchar2(50);
begin
  select salary into v_emp_salary from employees where employee_id=150;
  v_temp:=
  case trunc(v_emp_salary/5000)
    when 0 then 'salary<5000'
    when 1 then '5000<=salary<10000'
    else 'salary>=10000'
    end;
    dbms_output.put_line(v_temp);
end;    
--注意，case 条件语句中的when...then... 语句不允许出现任何表达式并且then 语句后面不会跟分号 


--使用循环语句打印1-100
方式一：
declare 
  v_i number(5):=1;
begin
  loop 
    dbms_output.put_line(v_i);
    exit when v_i>=100;
    v_i:=v_i+1;  
  end loop;
end;  
区别：
declare 
  v_i number(5):=1;
begin
  loop
    dbms_output.put_line(v_i);
    v_i:=v_i+1;
    exit when v_i>=100;  
  end loop;
end;   


方式二：
declare 
  v_i number(5):=1;
begin
  while v_i<=100 loop
    dbms_output.put_line(v_i);
    v_i:=v_i+1;
  end loop;  
end;  


方式三：
declare 
begin
  for c in 1..100 loop
    dbms_output.put_line(c);
  end loop;
end;      
打印100到1：
declare 
begin
  for c  in reverse 1..100 loop
    dbms_output.put_line(c);
  end loop;
end;      


输出2-100之间的质数
declare 
  v_i number(3);
  v_j number(3);
  v_k number(2):=1;
begin
  for c in 2..100 loop
    v_i:=c;
    v_j:=sqrt(c);
    for c2 in reverse 2..v_j loop
      if c mod(c2)=0 then v_k:=0;
      end if;
    end loop;
    if v_k=1  then dbms_output.put_line(c);
    end if;
    v_k:=1;
  end loop;  
end; 
--注意，Java中的(int) Math.sqrt(i) 是没有进行四舍五入的，但PL/SQL 中，若v_j定义为整形，并将v_j:=sqrt(c)则v_j是四舍


五入后的结果。


declare 
  v_flag number(1):=1;
begin
  for v_i in 2..100 loop
    for v_j in 2..sqrt(v_i) loop
      if v_i mod v_j=0 then v_flag:=0;
      goto label;
      end if;
    end loop;
    <<label>>
    if v_flag=1 then dbms_output.put_line(v_i);
    end if;
    v_flag:=1;
  end loop;  
end;    


以下情况下是不会有输出的 in后面的第一个参数需要小于第二个参数：
declare 
begin
   for c in 10..1 loop 
     dbms_output.put_line(c);
   end loop;
end;


找错误，该代码为什么无法输出质数（重写初始化的重要性）：
declare 
  v_i number(3):=2;
  v_j number(3):=2;
  v_flag number(1):=1;
begin
  while v_i<=100 loop
    while v_j <=sqrt(v_i) loop
      if v_i mod v_j=0  then v_flag:=0;
      end if;
      v_j:=v_j+1;
    end loop;
    if v_flag=1 then dbms_output.put_line(v_i);
    end if;
    v_i:=v_i+1;
    v_flag:=1;
   end loop;
end;   




--打印1到100 的自然数，当打印到50时，跳出循环，输出“打印结束"
declare 
begin
  for i in 1..100 loop
    dbms_output.put_line(i);
    if i=50 then dbms_output.put_line('打印结束');
    exit;
    end if;
  end loop;
end;


游标：①显示游标； ②隐式游标
在指定游标数据类型时，不能使用长度约束。 如number(4) 、char(10) 等都是错误的。
PL/SQL 程序不能用OPEN语句重复打开一个游标。


--打印80 号部门所有员工的工资。
declare 
       v_salary employees.salary%type;
       --①定义游标
       cursor emp_salary_cursor is select salary from employees where department_id=80;
begin
  --②打开游标
  open emp_salary_cursor;
  --③提取游标
  fetch emp_salary_cursor into v_salary;
  while emp_salary_cursor%found loop
    dbms_output.put_line(v_salary);
  fetch emp_salary_cursor into v_salary;
  end loop;
  --④关闭游标
  close emp_salary_cursor;
end;


--打印80 号部门所有员工的员工号和工资。
declare
       v_employee_id employees.employee_id%type;
       v_salary employees.salary%type;
       cursor emp_sal_cursor is select employee_id,salary from employees where department_id=80;
begin
  open emp_sal_cursor;
  fetch emp_sal_cursor into v_employee_id,v_salary ;
  while emp_sal_cursor%found loop 
    dbms_output.put_line(v_employee_id||':'||v_salary);
    fetch emp_sal_cursor into v_employee_id,v_salary;
   end loop;
end; 

--打印80 号部门所有员工的员工号、姓名和工资（使用记录类型和游标）。
declare 
--声明一个记录类型
       type emp_record is record(
            v_employees_id employees.employee_id%type,
            v_employees_name employees.last_name%type,
            v_employees_salary employees.salary%type
            );
            --声明一个记录类型的变量
            v_emp_record  emp_record;
            --定义游标
        cursor v_emp_cursor is select employee_id,last_name,salary from employees where department_id=80;
begin
  --打开游标
  open v_emp_cursor;
  --提取游标
       fetch v_emp_cursor into v_emp_record;
        while v_emp_cursor%found loop
              dbms_output.put_line(v_emp_record.v_employees_id||'--'||v_emp_record.v_employees_name||'--'||v_emp_record.v_employees_salary);
              fetch v_emp_cursor into v_emp_record;  
        end loop;
       --关闭游标
        close v_emp_cursor;
end;  

游标的for循环

PL /SQL语言提供了游标的for循环语句，自动执行游标的open、fetch、close语句和循环语句的功能。

--打印80号部门所有员工中员工号大于170的员工的employee_id,salary
declare 
  cursor v_emp_cursor is select employee_id,salary from employees where department_id=80;
begin
  for c in v_emp_cursor loop 
    if c.employee_id>170 
      then dbms_output.put_line(c.employee_id||'--'||c.salary);
      end if;
  end loop;
end; 

/*
利用游标，调整公司中员工的工资：
工资范围  调整基数
 0-5000  5%   5000-10000 3%  10000-15000  2%  15000-  1%
*/
/*
利用游标，调整公司中员工的工资：
工资范围  调整基数
 0-5000  5%   5000-10000 3%  10000-15000  2%  15000-  1%
*/
--方法一：
declare 
 cursor v_emp_cursor is select employee_id,salary from employees ;
 v_emp_id employees.employee_id%type;
 v_emp_salary employees.salary%type;
 v_temp number(4,2);
begin 
  open v_emp_cursor;
  fetch v_emp_cursor into v_emp_id,v_emp_salary;
  while v_emp_cursor%found loop
    if v_emp_salary<5000 then v_temp:=0.05;
    elsif v_emp_salary<10000 then v_temp:=0.03;
    elsif v_emp_salary<15000 then v_temp:=0.02;
    else v_temp:=0.01;
    end if;
    update employees set salary=salary*(1+v_temp) where employee_id=v_emp_id;
    --PL/SQL里面,赋值都是冒号等号,:=相当于java中的=号,而PL/SQL中的=号相当于Java中的==号。
     fetch v_emp_cursor into v_emp_id,v_emp_salary;
  end loop;
  close v_emp_cursor;
end;  

--方法二：

declare 
 cursor v_emp_cursor is select employee_id,salary from employees ;
 v_temp number(4,2);
begin
  for c in v_emp_cursor loop
    if c.salary<5000 then v_temp:=0.05;
    elsif c.salary<10000 then v_temp:=0.03;
    elsif c.salary<15000 then v_temp:=0.02;
    else v_temp:=0.01;
    end if;
    update employees set salary=salary*(1+v_temp) where employee_id=c.employee_id;
  end loop;
end; 
方法三：

update employees set salary=salary*(decode(salary/5000,0,1.05,1,1.03,2,1.02,1.01))

带参数游标：略

--隐式游标：更新101号员工的salary（涨10000），如果该员工没有找到，则打印“查无此人"信息
begin
  update employees set salary=salary+10000 where employee_id=101;
  if sql%notfound then dbms_output.put_line('查无此人');
  end if;
end;  

oracle有三种类型的异常错误：

1、预定义（Predefind）错误

  Oracle预定义的异常情况大约有24个。对这种异常情况的处理，无需在程序中定义，有Oracle自动将其引发。

2、非预定义（non Predefind）错误

即其他标准的Oracle错误。对这种异常情况的处理，需要用户在程序中定义，然后由Oracle自动将其引发。

3、用户定义（User_define）错误

程序执行过程中，出现程序员认为的非正常情况。对这种异常情况的处理，需要用户在程序中定义，然后显示地在程序中将其引发。

异常处理：

Exception 

When...then...

when...then...

when others then...

--预定义异常

declare 
 v_emp_salary employees.salary%type;
begin
  select salary into v_emp_salary from employees where employee_id>100;
  dbms_output.put_line(v_emp_salary);
exception
  when too_many_rows then dbms_output.put_line('输出的行数太多'); 

  when others then dbms_output.put_line('出现其他类型的异常了');
end;  

--非预定义异常

declare 
  e_deleteid_exception exception;
  pragma exception_init(e_deleteid_exception,-2292);--pragma exception_init(错误名称,错误代码);
begin
  delete from employees where employee_id=100;
exception
  when e_deleteid_exception then dbms_output.put_line('违反完整性约束条件，故不可删除此用户');
end;  

--将预定义变成非预定义

declare 
 v_emp_salary employees.salary%type;
 too_many_rows exception;
 pragma exception_init(too_many_rows,-1422);
begin
  select salary into v_emp_salary from employees where employee_id>100;
  dbms_output.put_line(v_emp_salary);
exception
  when too_many_rows then dbms_output.put_line('返回的行数太多');
end;  

用户自定义异常：用户定义的异常错误是通过显示使用raise语句来触发。当引发一个异常错误时，控制就转向exception块异常错误部分，执行错误处理代码。

对应这类异常情况的处理，步骤如下：

1、在PL/SQL块的定义部分定义异常情况：

<异常情况> Exception;

2、raise<异常情况>;

3、在PL/SQL块的异常情况处理部分对异常情况做出相应的处理。

declare
  e_too_high_sal exception;
  v_sal employees.salary%type;
begin
  select salary into v_sal from employees where employee_id=100;
  if v_sal>10000
    then raise e_too_high_sal;
  end if;
exception
  when e_too_high_sal then dbms_output.put_line('工资太高');
end;   

--通过select...into... 查询某人的工资，若没有查询到，则输入“未找到数据"
--考虑需要使用哪一种异常类型（预定义异常、非预定义异常还是自定义异常），若无法决定使用哪一种，可以先运行后再做决定。
--no_data_found 和 too_many_rows 是两种常见的预定义异常
declare 
   v_sal employees.salary%type;
begin
  select salary into v_sal from employees where employee_id=1000;
exception
  when no_data_found then dbms_output.put_line('没找到数据');
end; 

存储过程和函数

过程和函数的唯一区别是函数总向调用者返回数据，而过程则不返回数据。

--存储函数
create or replace function func_name(dept_id number,salary number)
return number
is
       --函数使用过程中，需要声明的变量、记录类型、cursor
begin
  --函数执行体
exception
  
end; 

创建函数

create or replace function hello_world
return varchar2
is
begin
  return 'helloworld';
end;   

调用函数

方法一：

begin

dbms_output.put_line(hello_world);

end;

方法二：

select hello_world from dual;

--创建一个存储函数，返回当前的系统时间
create or replace function get_sysdate
return date
is 
       v_date date;
begin
  v_date:=sysdate;
  return v_date;
end;

定义带参数的函数：两个数相加。

create or replace function add_num(num1 number , num2 number)
return number
is 
begin
  return num1+num2;
end;  

--定义一个函数：获取给定部门的工资总和，要求：部门号定义为参数，工资总和定义为返回值。
create or replace function get_sum(dept_id number)
return number
is 
v_sum_sal number(10,2);
begin
  select sum(salary) into v_sum_sal from employees where department_id=dept_id;
  return v_sum_sal;
exception
  when no_data_found then dbms_output.put_line('没有找到该部门数据');
end;  

关于out型的参数：因为函数只能右一个返回值，PL/SQL程序可以通过OUT型的参数实现有多个返回值。

--定义一个函数，获取给定部门的工资总和，和该部门的员工总数。要求：部门号定义为参数，工资总额定义为返回值。员工总数定义为out型参数.
create or replace function get_sal(dept_id number,sum_id out number)
return number
is
sum_salary number(10,2);
begin 
  select sum(salary) into sum_salary from employees where department_id=dept_id;
  select count(employee_id) into sum_id from employees where department_id=dept_id;
  return sum_salary;
end;

调用：

declare 
  sum_id number(3);
begin
  dbms_output.put_line(get_sal(80,sum_id));
  dbms_output.put_line(sum_id);
end;

存储过程

--定义一个存储该过程，获取给定部门的工资总和（通过out参数）。
create or replace procedure get_sal2(dept_id number,sum_sal out number)
is
begin
  select sum(salary) into sum_sal from employees where department_id=dept_id;
end;  

调用：

declare
  v_sum number(10,2);
begin
  get_sal2(80,v_sum);
  dbms_output.put_line(v_sum);
end;  

--对给定部门（作为输入参数）的员工进行加薪操作，若其到公司的时间在（？，95）期间， 5%（为其加薪）
--                                                                  （95,98）期间 ， 3%
--                                                                  （98，？）期间， 1%
--得到以下结果：为此次加薪公司每月需要额外付出多少成本（定义一个out型的输出参数）。

create or replace function add_sal(dept_id number)
return number
is
       cursor employees_cursor is select employee_id,hire_date,salary from employees where department_id=dept_id;
       percentage number(4,2);
       create_sal number(10,2):=0;
begin
         for c in employees_cursor loop
           if c.hire_date<to_date('1995-01-01','yyyy-MM-dd') then percentage:=0.05;
           elsif c.hire_date<to_date('1998-01-01','yyyy-MM-dd')  then percentage:=0.03;
           else percentage:=0.01;--只要是在函数（或者存储过程中）定义的的变量，在赋值的时候都需要使用:=
           end if;
           update employees set salary=salary*(1+percentage) where employee_id=c.employee_id;
           create_sal:=create_sal+c.salary*percentage;
         end loop;
         return create_sal;
end;

--使用show errors；可以显示所有的错误。

1、DML 触发器

Oracle可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。

2、替代触发器

由于在Oracle里，不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。

3、系统触发器

它可以在Oracle数据库系统的事件中进行触发，如Oracle系统的启动与关闭等。

create or replace trigger emp_update_trigger
after update on emp

for each row;--语句级
begin
  dbms_output.put_line('对employees进行了删除操作。');
end;  

 create or replace trigger emp_update_trigger 
 after update on employees
 for each row --NEW 或 OLD 引用不允许在表级触发器中
 begin
   dbms_output.put_line('old_salary:'||:old.salary||'   new_salary:'||:new.salary);
 end;

------------------------------------------------华育国际-----------------------------------------------------

事物特有的临时表
只在事务生命周期中存在
Create global temporary table classes
(
class_id number(5),
class_name varchar2(8),
class_memo varchar2(200)
)
On commit delete rows;jj


会话特有的临时表
用户退出，自动清除临时表中数据
Create global temporary table student
(
stu_id number(5),
class_id number(5),
stu_name varchar2(8),
stu_meno varchar2(200)
)
on commit preserve rows;


重命名表
1、Rename old_name to new_name;
2、Rename employee to emp;
3、Alter table employee rename to emp;


修改列
1、修改单列
Alter table employee modify empname varchar2(40);
2、修改多列
Alter table department modify(
Deptno char(6),
Note varchar2(80)
);
  --注意，当modify后面的字段数少于原来的字段数时，没被修改的字段数还是会保留，即修改后的字段数大于等于原来的字段数。
 
增加字段
 alter table person add banji number(2);


删除一个约束
ALTER TABLE employee DROP CONSTRAINT sex_ck;


删除一列
ALTER TABLE employee DROP COLUMN  sex;
--注意，当表中的该列仍有数据的时候，依然可以执行删除的操作。


--有的时候数据正在使用，并发时，可以先把字段设置为unused，等没有人使用的时候再进行删除。
Alter table employee 
Set unused(zip);
Alter table employee 
Drop unused columns;


清空表
Delete from emp;
Truncate table emp;
truncate 与delete的区别
TRUNCATE在各种表上无论是大的还是小的都非常快。如果有ROLLBACK命令，DELETE将被撤销，而TRUNCATE则不会被撤销
TRUNCATE 是一个DDL语言，象其他所有的DDL语言一样，它将被隐形提交，不能对TRUNCATE使用ROLLBACK命令
TRUNCATE将重新设置所有的索引。在对整个表和索引进行完全浏览时，经过TRUNCATE操作后的表比DELETE操作后的表要快得多。
TRUNCATE不能触发任何DELETE触发器
当表被清空后，表和表的索引将重新设置成初始大小，而DELETE则不能


SQL分类
DDL :  用于定义,修改,删除数据库模式对象,进行权限管理等
DML:  用于查询,生成,修改,删除数据库中的数据. 
TC:  用于把一组DML语句组合起来形成一个事务并进行事务控制.
SC: 会话控制,用于控制一个会话(session,指从与数据库连接开始到断开之间的时间过程)的属性.
系统控制,用于管理数据库的属性.只有一条语句,即alter system.


date数据类型
DATE数据类型存储标准格式的日期
TIMESTAMP类型是DATE数据类型的扩展，精确到秒后面六位小数，并分为上午、下午
insert into aa(a,d,e)  values('3',to_date('2000.1.1','yyyy.mm.dd'),to_date('2000.1.1','yyyy.mm.dd')); select * from aa;


like
   LIKE                                          模式匹配(或模糊查询) 在搜索字符串中使用通配符 “%”和”_” ,“%”号代表0个或多个字符,而”_”则表示单个字符.如果搜索字符串中需要查询实际的”%”和”_”,那么就需要在搜索字符串中使用一个转义字符(ESCAPE character),如果查一个叫以”刘%” 开头的名字
如: select name from table where name   like  ‘刘\%%’ escape ‘\’


计算部门员工数量
select deptno ,count(*) from emp group by deptno ;


求出部门员工人数在3人以上的部门
select deptno,count(*)from emp group by deptno having count(*)>3;


计算30号部门的平均工资（包括奖金在内）
 select avg(sal+nvl(comm,0)) from emp where deptno=30;
--在写sql语句时，应当时刻注意含有null元素的字段
--除了count(*)之外,其他处理函数都会忽略NULL行


不算重复工资的平均工资（分母同时也相应地减少）
select avg(distinct sal) from emp where deptno=30;


修改列名称
select job as 工作 ,sal as 工资 from emp;
select ename 姓名,sal 工资 from emp;
算出每个工种的总工资
select job ,sum(sal) from emp group by job;
算出有人数是3个人的部门
select deptno ,count(*)  from emp group by deptno having count(*)=3;


组处理函数
1、MAX              返回列或表达式的最大值(数据最大,时间最晚)
2、MIN               返回列或表达式的最小值(数据最小,时间最早)
3、AVG              返回列或表达式的平均值
4、SUM              返回列或表达式的总和
5、COUNT          返回非NULL行的行数


组处理函数应注意以下几点:
1、组处理函数只能出现在选择列表,ORDER BY子句,HAVING子句中,而不能出现在WHERE子句和GROUP BY子句中
2、除了count(*)之外,其他处理函数都会忽略NULL行
3、如果选择列表同时包含列,表达式和组处理函数,则这些列,表达式都必须出现在GROUP BY字句中.
4、在组处理函数中可以指定ALL和DISTINCT选项.其中ALL是默认选项,表示统计所有的行(包括重复的行),而DISTINCT只会统计不同的行
算出各个部门的最高工资、最低工资和平均工资


select deptno ,max(sal),min(sal),avg(sal) from emp group by deptno;
select deptno ,max(sal),min(sal),avg(sal) from emp group by deptno order by max(sal) desc;
select avg(distinct sal) ,avg(sal) from emp where deptno=30;--前面算出的平均工资是不加人重复工资的5个人的平均工资，后面是加入重复工资的6个人的平均工资。


分组
--使用ROLLUP和CUBE限定词生成报表
--显示每个部门、每种岗位的平均工资和最高工资及其横向统计结果------------------------------------------------------
Select deptno,job,avg(sal),max(sal) from emp group by rollup(deptno,job);
--显示每个部门、每种岗位的平均工资和最高工资及其纵向统计结果
Select deptno,job,avg(sal),max(sal) from emp group by cube(deptno,job);


Having 字句
显示平均工资高于2000元的部门编号、平均工资和最高工资
select deptno,avg(sal),max(sal) from emp group by deptno having avg(sal)>2000;
--注意分组函数不能出现在where字句中
--错误的语句：select deptno,avg(sal),max(sal) from emp  where avg(sal)>2000 group by deptno;


单行数学函数
1、ABS(n)   返回n的绝对值
2、CEIL(n)  返回大于等于n的最小整数
3、FLOOR(n)   返回小于等于n的最大整数
4、MOD(m,n) 返回m 除以n 之后的余数,如果n为0,则返回m
5、POWER(m,n) 返回m 为底的n次幂.m和n可以为任意数字,但如果m为负数,则n必须为正数
6、ROUND(n,[m]) 执行四舍五入运算.如果省略m,则四舍五入到整数位;如果m是负数,则到小数点前m位;如果m 是正数,则到小数点后m位
7、SIGN(n) 检测n的正负.如果n小于0,则返回-1;如果n 等于0,则返回0;如果n大于0,则返回1
8、SQRT(n) 返回n的平方根,n 必须大于0
9、TRUNC(n,[m]) 执行截取数字.如果省略m,则将n的小数部分截取;如果m是负数,则截取到小数点前m位;如果m是正数,则截取到小数点后m位


单行字符函数
1、ASCII(c) 返回c的首字符在ASCII码中对应的十进制数 
2、CHR(n)  返回十进制ASCII码n对应的字符
3、CONCAT(c1,c2) 返回将c2添加到c1后面而形成的字符串.如果c1是NULL,那么返回c2;如果c2是NULL,那么返回c1;如果c1,c2都是NULL,那么返回NULL
4、INITCAP(c) 返回将c的每个首字符都大写,其他字符都小写的字符串.单词之间以空格,控制字符和标点符号分界
5、INSTR(c1,c2[,n[,m]]) 在c1中从n开始搜索c2第m次出现的位置,并返回该位置数字.如果n是负数,则搜索从右向左进行,但位置数字仍然从左向右计算.n和m默认都是1
6、LOWER(c) 返回将c全部字符都小写的字符串
7、LENGTH(c) 返回c的长度,包括所有的后缀空格.如果c是NULL,则返回NULL
8、LPAD(c1,n[,c2]) 在c1的左边填充c2,直到字符串的总长度到达n.c2的默认值为空格.如果c1的长度大于n,则返回c1左边的n个字符
9、LTRIM(c1[,c2]) 去掉c1左边所包含的c2中的任何字符,当遇到不是c2中的字符时结束,然后返回剩余的字符串.c2默认为空格
10、RPAD(c1,n[,c2]) 在c1的右边填充c2,直到字符串的总长度到达n.c2的默认值为空格.如果c1的长度大于n,则返回c1右边的n个字符
11、RTRIM(c1 [,c2]) 去掉c1右边所包含的c2中的任何字符,当遇到不是c2中的字符时结束,然后返回剩余的字符串.c2默认为空格
12、REPLACE(c1,c2 [,c3]) 把c1中出现的c2都替换成c3,然后返回剩余的字符串.c3默认为NULL.如果c3为NULL,那么所有出现c2的字符都被删除;如果c2为NULL,则将返回c1;如果c1为NULL,则将返回NULL
13、SUBSTR(c,m[,n]) 返回c的子串,其中m是子串开始的位置,n是子串的长度.如果m为0,则从c的首字符开始;如果m是负数,则从c的结尾开始.
14、TRANSLATE(c1,c2,c3) 把所有在c2中出现的字符,用对应在c3中出现的字符代替,然后返回被替代之后的c1字符串.如果c1,c2,c3中有NULL,则返回NULL;如果c3中字符少于c2,则将在c2中不匹配的字符从c1中删除;如果c2中的字符少于c3,则将在c3中不匹配的字符仍然保留在c1中
15、TRIM([c1] c2 FROM c3)   c1是保留字,可以取如下字符串:LEADING,TRAILING,BOTH.从c3字符串的c1处开始,删除c2字符,然后返回剩余的c3字符串.c1默认为BOTH,c2默认为空格.如果c1,c2,c3中有任何一个为NULL,则返回NULL
16、UPPER(c) 返回将c全部字符串都大写的字符串
--注意：ltrim、rtrim和translate 是比较特殊的字符函数
select concat('ab','cd'),concat('ab',null)from dual;
select 'ab'||'cd' from dual;
select INSTR('mississippi','i',3,3) s1,INSTR('mississippi','i',1,3) s2,INSTR('mississippi','i',-2,3) s3 
from dual
select translate('abcdefgabcd','abc','AB'),translate('abcdefgabcd', 'ab','ABC'),translate('abcdefgabcd','ab',' ')from  dual;


单行日期时间函数
1、MONTHS_BETWEEN(d1,d2) 返回日期d1和d2之间相差的月数.如果d1小于d2,则返回负数;如果d1和d2的天数相同或都是月底,则返回整数;否则Oracle以每月31天为准来计算结果的小数部分
2、NEXT_DAY(d,c) 返回日期d后的下一个c.c是一个字符串,表示用当前会话语言的一周的某一天的全称(如,星期一,星期二等)
3、ROUND(d[,fmt]) 返回日期时间d的四舍五入结果。如果fmt是YEAR,则以7月1日为分界线；如果fmt是MONTH,则以16日为分界线；如果fmt是DAY,则以中午12:00为分界线
4、SYSDATE 返回当前数据库的日期时间
5、TRUNC(d [, fmt]) 返回根据fmt指定的精度截断后的日期d.其中,d是一个日期,fmt是一个指定日期精度的字符串.如果fmt为YEAR,则为本年的1月1日;如果fmt为MONTH,则为本月的1日;如果fmt为DAY,则天的个位为0(只有个位时不变)


 select extract(year from sysdate)年,extract(month from sysdate)月,extract(day from sysdate) 日 from dual;


单行转换函数
1、TO_CHAR(x [ ,fmt]) 返回将x按fmt格式转换后的字符串.x是一个日期或者数字,fmt是一个规定了x采用何种格式转换的格式字符串
2、TO_DATE(c [ , fmt]) 将符合fmt指定的特定日期格式的字符串c转换成DATE类型的数据
3、BIN_TO_NUM(n1,n2,n3) 将每位由n1,n2,n3等组成的二进制数转变为十进制数


在当前的时间上加上一年、一月、一日、一时、一分等
select sysdate,sysdate+numtodsinterval(-2,'hour'),sysdate+numtodsinterval(30,'minute')from dual;
 select sysdate+numtoyminterval(2,'year'),sysdate+numtoyminterval(6,'month') from dual;
当前时间加上7天12小时30分15秒之后的时间
select sysdate+interval'7 12:30:15' day to second from dual;
当前时间加上1年2个月后的时间
select sysdate+interval'1-2' year to month from dual;
将二进制的1101转换成十进制的数
select bin_to_num(1,1)from dual;


--日期to_char的使用
一年中的第几天
select to_char(sysdate,'DDD') from dual;
一个月中的第几天
select to_char(sysdate,'DD') from dual;
星期中的第几天
select to_char(sysdate,'d')from dual;
一个月中的第几周
select to_char(sysdate,'W') from dual;
一年中的第几周
select to_char(sysdate,'WW') from dual;
今天是星期几
select to_char(sysdate,'DAY') from dual;
select to_char(sysdate,'DY') from dual;--dy是day 的缩写
格式化时间
select to_char(sysdate,'yyyy-mm-dd***hh-mi-ss*(*)') from dual;
一年中的第几个月
select to_char(sysdate,'DY') from dual;
select to_char(sysdate,'MONTH') from dual;
这样显示2,015年
select to_char(sysdate,'y,yyy')from dual;


--nvl2的用法
select ename,sal,comm,nvl2(comm,sal+comm,sal) from emp


自我连接
将一个表复制一个结果集进行连接
如：
--查询雇员对应的管理员的名字
Select e.empno,e.ename ,m.ename 管理员
From emp e,emp m
Where m.empno=e.mgr;
--显示工资最高的雇员信息
Select ename,deptno,sal from emp
Where sal=(select max(sal) from emp);


多行子查询
--显示与部门编号为20的岗位相同的雇员信息
Select ename,deptno,sal,job from emp
Where job in (select distinct job from emp where deptno=20);
--显示不与部门编号为20的岗位相同的雇员信息
Select ename,deptno,sal,job from emp where job not in (select distinct job from emp where deptno=20);
--显示高于部门编号为20的所有雇员的工资的雇员信息
select ename,deptno,sal ,job from emp
where sal>all(select sal from emp where deptno=20);
--显示高于部门编号为20的任何雇员的工资的雇员信息
select ename,deptno,sal ,job from emp
where sal>any(select sal from emp where deptno=20);
--显示工作在NEW YORK的雇员信息
select ename,deptno,sal,job from emp
where exists (select 'x' from dept where dept.deptno=emp.deptno and dept.loc='NEW YORK');


--显示与SMITH部门和岗位完全相同的所有雇员信息，以说明成对比较的意思
select ename,deptno,sal,job from emp
where (deptno,job) = (select deptno,job from emp where ename='SMITH');




create table dept1(deptno,dname,loc) as 
select deptno,dname,loc from dept;
--创建所有列
create table emp1 as select * from emp;


--先查询于WARD岗位相同的雇员的工资、补助。然后将与WARD岗位相同的雇员的工资、补助更新为与WARD完全相同
select ename,job,sal,comm from emp1
where job=(select job from emp1 where ename='WARD');
update emp1 set (sal,comm)=(select sal,comm from emp1 where ename='WARD') where job=(select job from emp1 where ename='WARD');


向表中引入外表信息的时候，不需要写【values】
 insert into emp1   select*from emp where deptno=(select deptno from dept where dname='ACCOUNTING');


--将EMP表中ACCOUNTING部门的雇员信息插入到EMP1表中
insert into emp1 select * from emp
where deptno=(select deptno from dept where dname='ACCOUNTING');
select ename,job,deptno from emp1
where deptno=(select deptno from dept1 where dname='ACCOUNTING');
insert into emp1(empno,ename)
select empno,ename from emp
where deptno=(select deptno from dept where dname='ACCOUNTING');
--注意一定包含主键列和非空列，否则会报约束错误。


查询出平均工资最高的部门
select deptno,avg(sal) from emp group by deptno having avg(sal) = (select max(a) from(select avg(sal) a from emnt sp1;
        insert into emp(empno,ename,job,hiredate) values(5678,'lucy','clerk','04-4月-95');
        rollback to sp1;
        rollback;




高级查询
1、内连接
2、外连接
   左外连接
   右外连接
   完全外连接
3、自然连接


内连接
     select   e.ename, d.dname from  emp e  inner  join  dept don  e.deptno = d.deptno;
等价于：
select   e.ename, d.dname from  emp e , dept d where  e.deptno = d.deptno


左外连接
     返回满足连接条件的所有行，而且还会返回不满足连接条件的连接操作符的左边表的其他行
如：
     Select d.dname, e.ename from dept d left  join  emp e on  d.deptno = e.deptno and d.deptno = 10;


select emp.*,dept.deptno from emp,dept where dept.deptno=emp.deptno(+);
select emp.*,dept.deptno from emp,dept where dept.deptno(+)=emp.deptno;


右外连接
      返回满足连接条件的所有行，而且还会返回不满足连接条件的连接操作符的右边表的其他行
      Select  d.dname, e.ename from dept  d  right  join  emp e on  d.deptno = e.deptno and  d.deptno = 10;
左连接是全部显示【left join】左边表的信息，右连接是全部显示【right join】右边表的信息


完全外连接
      不仅返回满足连接条件的所有行，而且还会返回不满足连接条件的所有行
      Select d.dname, e.ename from dept d full join emp e on d.deptno=e.deptno and d.deptno = 10;


自然连接
      同一张表之间的连接查询
      主要用于在参照表上显示上下级关系或层次关系


自然连接
      SELECT M.ENAME FROM EMP M INNER JOIN EMP W ON M.EMPNO = W.MGR AND W.ENAME='BLAKE';


查询emp 表中的管理人员
      SELECT distinct M.ENAME FROM EMP M INNER JOIN EMP W ON M.EMPNO = W.MGR ;
统计每个部门经理下有多少个员工
      select a.empno,a.ename,count(*) from emp a,emp b where a.empno=b.mgr group by a.empno,a.ename;
      等同于
      select a.empno,count(*)d from emp a,emp b where a.empno=b.mgr group by a.empno ;
      --count(*)是组函数，所以a.empno,a.ename必须前后一致才不会出错


--更新数字列
update emp set deptno=50,sal=1200 where empno=1234 or empno=5678;


事务的概念
事务的性质
   原子性(atomicity)  
   一致性(consistency)
   隔离性(isolation)
   持久性(durability)
基本语句及功能
   设置事务(set transaction)
   提交事务(commit)
   设置保留点(savepoint)
   回退全部事务(rollback)
   回退部分事务(rollback)


原子性：一个不可分割的逻辑单位。


一致性：事务操作的结果必须使数据库中的数据处于一致性的状态


隔离性：事务的执行，不受其他事务的干扰


持续性：一个事务一旦提交成功，它对数据库的数据所作的修改永久性的保留了下来


求出津贴不为空的所有员工
        select ename,job, comm from emp where comm is not null;--注意是【is not null】！


保留点
        insert into emp(empno,ename,job,hiredate) values(1234,'jack','clerk','29-4月-63');
        savepoint sp1;
        insert into emp(empno,ename,job,hiredate) values(5678,'lucy','clerk','04-4月-95');
        rollback to sp1;
        rollback;




求哪个经理的工资最低
select ename,job,sal from emp where sal=(select min(a) from (select sal a from emp where job='MANAGER'));
求哪个部门的平均工资最低
select deptno ,avg(sal)from emp group by deptno having avg(sal)=(select min(a) from(select avg(sal)a from emp group by deptno));
查询出部门编号为30的部门里面哪种工作的平均工资最高
select job,avg(sal) from (select*from emp where deptno=30) group by job having avg(sal)=(select max(a) from ( select avg(sal)a from (select*from emp where deptno=30) group by job));
统计出各个部门的各个工作岗位的平均工资,结果按部门排序
select deptno,job,avg(sal) from emp group by deptno,job order by deptno;


查询工资高于部门平均工资的所有员工
select*from emp a where sal>(select avg(sal) from emp where deptno=a.deptno);


查询emp表的1~3行：
 select*from emp where rownum>=1 and rownum<=3;
 查询emp表的4~6行：
 select *from (select rownum num,e.*from emp e where rownum <=6)where num>=4;
 使用rownum来查询工资最高的员工的信息
 select*from(select*from emp order by sal desc) where rownum<=1;
 
 
用decode、sign函数算个人所得税(大数据59页)： 
 
decode(sign(sal-2000-a1),1,(),-1,(0),0,(c1))


decode(sign(sal-2000-a2),1,(),-1,((sal-2000-a1)*b1+c1),0,(c2))


decode(sign(sal-2000-a3),1,(),-1,((sal-2000-a2)*b2+c2),0,(c3))


decode(sign(sal-2000-a4),1,(),-1,((sal-2000-a3)*b3+c3),0,(c4))


decode(sign(sal-2000-a5),1,(),-1,((sal-2000-a4)*b4+c4),0,(c5))


先where再group byr


查询部门的名称以及该部门的人数，要求没有员工的部门也要显示
select dname,count(empno) from emp e,dept d where e.deptno(+)=d.deptno group by dname;
select dname,count(empno) from emp e right join dept d on e.deptno=d.deptno group by dname;
在使用（+）号时，要注意当连接是自连接的时候，返回的结果是（+）号一边的表的全部结果跟另一表的部分结果


查询员工姓名及其直接上级的姓名
select 员工.ename,经理.ename from emp 员工，emp 经理 where 员工.mgr=经理.empno(+);--注意后面加（+）号的区别


显示员工信息中的第3行到第6行的信息
select*from (select rownum num,a.*from emp a where rownum<=6)where num>=3;


列出10号部门中有而20号部门中没有的工作
select job from emp where deptno=10 minus select job from emp where deptno=20;
集合运算：1、并集（union/union all）union 会消除查询结果中的重复记录，union all则不会
          2、交集（intersect）
          3、差集（minus）




查询KING直接领导的员工的工资总额
select sum(sal) from emp where mgr=(select empno from emp where ename='KING');
查询KING带领的团队（包括下属的下属）的工资总额
 select sum(sal) from emp connect by prior empno=mgr start with ename='KING';--注意：empno=mgr不能写反


查询每个部门中拥有最高工资的员工的信息
select*from emp e where (select count(*) from emp where deptno=e.deptno and sal>e.sal)=0;-----------------------------------------------


列出所有员工的姓名及其直接上级的姓名
select a.ename,b.ename from emp a,emp b where a.mgr=b.empno(+);
列出受雇日期早于其直接上级的所有员工
select *from emp a where a.hiredate<(select hiredate from emp b where a.mgr=b.empno);
列出所有“CLERK”的姓名及其部门名称
select e.ename,d.dname from emp e,dept d where e.job='CLERK' and e.deptno=d.deptno;
列出最低薪金大于1500的各种工作
select job,m from (select job,min(sal) m from emp group by job) where m>1500;
select job,min(sal) from emp group by job having min(sal)>1500;
列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号
select ename from emp where deptno=(select deptno from dept where dname='SALES');
列出在每个部门工作的员工数量、平均工资和平均服务期限
select count(empno),avg(sal),avg(mb)服务期限 from (select months_between(sysdate,hiredate) mb,e.* from emp e) group by deptno;
列出所有部门的详细信息和部门人数
select deptno,dname,loc,count(empno) from ( select d.*,e.empno from dept d ,emp e where d.deptno=e.deptno(+)) group by deptno,dname,loc;
select a.*,(select count(deptno) from emp b where b.deptno=a.deptno) as deptcount from dept a;
 
查询选所有课程的学生信息
select *from student where studentid in ( select s from (select studentid s,count(subjectid) c from grade group by studentid)f where c=3);
列变行
select 
f.姓名,
sum(case when f.科目='C++' then 分数 else null end) as "C++",
sum(case when f.科目='JAVA' then 分数 else null end) as "JAVA",
sum(case when f.科目='ASP' then 分数 else null end) as "ASP", 
sum(f.分数) ttscore
from (select sd.studentname 姓名,sj.subjectname 科目,mark 分数 from student sd,
subject sj,grade g where sd.studentid=g.studentid and sj.subjectid=g.subjectid)f group by 姓名;
注意：取有符号的别名的时候用双引号括起来，用有符号的别名的时候用单引号括起来。


select 
姓名,
sum(decode(科目,'C++',分数,null))"C++",
sum(decode(科目,'JAVA',分数,null))JAVA,
sum(decode(科目,'ASP',分数,null))ASP, 
sum(分数) ttscore
from (select sd.studentname 姓名,sj.subjectname 科目,mark 分数 from student sd,
subject sj,grade g where sd.studentid=g.studentid and sj.subjectid=g.subjectid)f group by 姓名;


书写SQL语句一定要注意如下的一些基本规则：
1、不带where条件的SQL语句一定不能使用索引；
2、在where条件中有索引的字段是不能使用函数的；
3、在where条件中有索引的字段不能参与运算；
4、尽量少用not in、not exists、like‘%’ 等关键字；
5、书写查询语句时，应首先考虑关联查询、其次考虑集合查询、最后才考虑写子查询；
6、where条件中限制性强的条件要写在where子句的末尾；
7、多表查询的时候，驱动表应是返回数据少的表；
8、对于联合索引来说，当where中含有联合索引的第一个字段时该索引有效。


查询oracle数据库中所有的用户
select username from dba_users;
查询scott用户下所有的表
select table_name from user_tables;
查询Scott用户有权察看的表
select table_name,owner from all_tables;


oracle的SQL语句分类
1、DCL（Data Control Language数据控制语言）是指授予权限和回收权限语句，如：grant select on emp to user1和revoke select on emp from user1
2、DDL（Data Definition Language数据定义语言）是指4种SQL语句：create、drop、alter、truncate
3、DML（Data Manipulation Language数据操纵语言）是指增删改查——insert、update、delete、select
4、TCL（Transaction Control Language事务处理语言）是指提交、回滚和保留点，即：commit、rollback和savepoint


求同时买佳洁士和夏士莲的顾客--93
select aa.*from customer aa where not exists(select productid from purcase intersect select productid from product where productname='佳洁士' or productname='夏士莲'
minus select a.productid from purcase a ,customer b where a.customerid=b.customerid and b.name=aa.name and b.name<>'Dennis');

---------------------------------------

p

一、oracle数据库系统环境搭建
安装：
1、解压到同一文件夹下
2、仅安装数据库软件
3、单实例数据库安装
4、企业版


创建数据库
1、开始菜单里面选择【配置和移植工具】--》【database configuration Assistant】
2、填写全局数据库名和SID
3、不选【配置Enterprise Manager】
4、选中【示例方案】
5、选中【口令管理】
6、填写Scott和HR的口令


配置监听器
1、开始--》【配置和移植工具】--》【Net Configuration Assistant】（网络配置助手）
2、一直下一步就可以了


Oracle数据库常用工具使用
1、Oracle Universal Installer  Oracle全局安装器--》OUI 在开始菜单的【Oracle 安装产品】里面，卸载Oracle的时候要用到
2、Database Configuration Assistant 数据库配置助手
3、Net Configuration Assistant 网络配置助手
4、SQL Plus
5、SQL Developer
6、PL/SQL Developer
7、TOAD


二、Oracle数据查询（一）

DBMS  数据库管理系统 通常指的是一种软件
1、表、关系、二维表
2、行、记录、元祖
3、列、字段、属性
4、数据项、数据单元


数据定义语言DDL
    包括六个语句：create、drop、alter、truncate、rename、comment
    主要是操作数据库对象的
数据控制语言DCL
    grant、revoke


where字句的格式及作用
    不等于：<>,!=,^=
    特殊运算符：[not]between...and,[not]like,[not]in,is[not]null
    多个条件可以使用not，and，or连接
字符值与日期值需要使用单引号界定
查询1982年1月1号之后入职的员工
select*from emp where hiredate>'1-1月-1982';
order by 中默认时null最大
查询员工的姓名，工资，部门和入职时间，按查询的第二个属（即sal）性进行排序
select ename,sal,deptno,hiredate from emp order by 2 desc;


单行函数：接受一个数据，输出一个数据
多行函数：聚合函数，分组函数。接受一组数据，输出一个数据。


左去除、右去除
select trim('h' from 'hellohh'),ltrim('hfd hh','h'),rtrim('hhjkjhhh','h') from dual;--注意trim跟ltrim/rtrim的格式是完全不同的
左补齐、右补齐
lpad、rpad
连接字符串
concat('s1','s2')


extract:获取给定日期的年、月、日。
select extract(month from hiredate) from emp;


空值函数
nvl、nvl2、
select ename,comm,nvl2(comm,0,sal) from emp;


decode函数:
select ename,deptno,sal,decode(deptno,10,sal+100,20,sal+200,30,sal+300,5000) from emp;


case表达式:
select ename,deptno,sal ，case deptno 
when 10 then sal+100
when 20 then sal+200
when 30 then sal+300
else 5000 end salary from emp;--注意不要忘了加end，同时，when后面可以加条件，这是case之所以强调的原因。decode是一个函数，case是一个表达式


常用聚合函数
max、min：可以用在字符、数值和日期类型数据上
sum、avg：可以应用在数值类型数据上
count：统计表达式值不为空的行数


聚合函数与数据分组:select distinct... from 表名 [where 条件][group by 列，...][having 分组条件][order by 列|列别名|表达式|序号[asc|desc]];
having :用来限定分组后的数据 后面的字句必须是group by之后结果里面有的，比如
select deptno,max(sal) ,min(sal) from emp group by deptno having deptno=20;
select deptno,max(sal) ,min(sal) from emp group by deptno having min(sal)>1000;


在select字句中出现的非分组列，必须出现在group 不要字句中
执行顺序：from，where，group by,having,select,order by


三、Oracle数据查询（二）




相等连接
select ename,deptno,dname from emp natural join dept;
select ename,deptno,dname from emp join dept using(deptno);
select ename,emp.deptno,dname from emp join dept on emp.deptno=dept.deptno;
注意：对于join...on...必须使用表名或表别名进行限定，对于natural join和join...using...必须不可以使用表别名进行限定，这是Oracle数据库的规定
不等连接
查看员工的工资等级
select ename,sal,grade from emp join salgrade on sal between losal and hisal;


自连接：将一张表作为两张表进行的连接


外链接：使用空行匹配更多的行


单行子查询：查询结果为一行的查询
多行子查询：查询结果为多行的查询
            使用的比较运算符是：any  all in
select*from emp where sal>all(select avg(sal) from emp group by deptno);
select*from emp where sal>any(select avg(sal) from emp group by deptno);


相关子查询概念及执行流程
1、子查询中需要使用主查询中行的查询


2、首先从主查询中获取一行
3、利用主查询中获取行执行子查询
4、执行主查询


使用[not]exists进行相关子查询
select ename,sal,deptno from emp e where exists(select 1 from emp where mgr=e.empno);


特别要注意：当在PL/SQL 里面对数据进行操作但还没进行提交的时候，jdbc对Oracle的操作会一直处于阻塞状态。




四、Oracle数据操作与事务


delete [from] table [where...] --from关键字在Oracle中是可选的，在其他数据库中是必须的
无条件多表查询
insert all（这里all改为first之后，插入cemp1表的数据不会插入到cemp2） into cemp1 values(empno,ename,sal,comm) into cemp2 --values(empno,hiredate,deptno) into cemp3(empno,ename,job,deptno) select*from emp;




将工资更新为所在部门的平均工资
update emp e set sal=(select avg(sal)from emp where deptno=e.deptno);


事务提交：发布commit、正常退出系统环境、遇到DCL或DDL
事务回滚：发布rollback、非正常退出系统环境


事务的概念：
由一组DML组成的逻辑工作单元
可以包含一组DML，同时包含一个DDL或者DCL




五、Oracle表的基础
create table ddept(deptno number(2,0)); 
--表的小数点后是0位，如果插入30.6，则只显示30
复制一张表的结构：
create table ddept as select*From dept where 1=0;


error:未找到父项关键字 --是因为引用外键，外表没有该数据
删除列：alter table emp drop(hiredate,comm);
添加列：alter table emp add hiredate date default sysdate;
添加非空约束：alter table emp modify ename constraint emp_ename_n not null;
截断表：truncate table emp;(truncate属于DDL即数据定义语言，所以是没法回滚的)
启用表的只读状态便于备份或导出表
alter table talbe_name read only;
启用表的读写状态便于表的正常使用
alter table table_name read write;
error:不允许对表 "SCOTT"."EMP" 进行更新操作--表被修改为只读状态




六、Oracle中其他数据库对象的管理
问题：视图可以插入数据吗？
答案：视图分简单视图和复杂视图，简单视图：不可以包含分组、不可以包含函数和表达式、子查询只允许为一张表、允许DML操作，并且DML同时会作用在基表上（这一点应当特别注意）。复杂视图：可以包含分组、可以包含函数和表达式、允许多表查询、不一定允许DML操作
普通的Scott是没有创建view、sequence和syＤＭＬ的权限的
授权：grant create view,create sequence ,create synonym to scott;
create or replace view vu20(员工号,姓名,工资,部门号,补贴) as select empno,ename,sal,deptno,comm from emp where deptno=20;
创建视图的时候指定只读模式：
create or replace view deptvu as select*From dept with read only;
序列
create sequence seq_name
start with n
increment by n
[maxvalue n|no maxvalue] --是否指定最大值
[minvalue n|no minvalue] --是否指定最小值
[cycle|nocycle]   --是否循环分配序列
[cache n|nocache];--是否缓存序列的值
序列的两个伪列：currval、nextval
--在使用currval之前，必须至少执行一次nextval

同义词:
create [public] synonym syn_name for object;

七、Oracle中的用户、权限与角色
创建用户
create user name
identified by password
[account lock|unlock] 
[password expire]--用户密码是否立刻过期


create user test identified by test account lock password expire;
grant create session to test;
系统权限：能否在数据库上完成某个动作
例如：创建回话、创建表、查询任意表等
对象权限：能否在某个数据库对象上完成某种操作
例如：查询emp 表，修改dept表等
系统权限的授予与回收
授予：
grant priv
to user|role
[with admin option];--系统权限是否可以级联授予
回收：
revoke priv from user|role;
对象权限的授予与回收
授予：
grant priv[(col,...)]
on object
to user|role
[with grant option]
回收：
revoke priv[(col,...)]
on object
from user|role;
例：grant select,update(sal) on scott.emp to test with grant option;
--应当特别注意：对象权限的回收是级联的，系统权限的回收是非级联的


角色：本质上是权限的集合，目的是为了简化权限管理
创建角色
create role role_name;
grant role to user;
当角色删除时，拥有角色相应权限的用户将不再拥有这些权限




八、PL/SQL控制结构
declare 
month int;
day int;
begin
  month:=&inputmonth;
  case
    when month in(1,3,5,7,8,10,12) then day:=31;
    when month in(4,6,9,11) then day:=30;--注意then后面给变量赋值要用  :=
    when month=2 then day:=28;   --when后面比较的时候用  =
    else day:=0;   
  end case; --记住要写end case;
  dbms_output.put_line(day);
end;


输出1到10
begin
  for x in 1..10
  loop
   dbms_output.put_line(x);
  end loop;
end;


continue[label][when condition]
不带label的continue
begin
  for x in 0..9
  loop
    continue when x mod 2!=0;--当X除以2不等于0的时候，就跳过输出语句
    dbms_output.put_line(x);
  end loop;
end;


带label的continue
begin
  <<outer>>
  for x in 0..9
  loop
    for y in 0..9
    loop
      continue outer when (x+y) mod 2!=0;
      dbms_output.put_line(x||' '||y);
    end loop;
  end loop;
end;


九、PL/SQL 基础知识
PL/SQL变量分为：
标量：保存单一值
组合量：保存多个值
参照量：类似于指针
大对象：blob,clob,nclob,...
 
参考类型：
%type
%rowtype:组合量
用case表达式赋值
declare 
x int;
y int;
begin
  x:=2;
  y:=case x
     when 1 then 2--注意，用case表达式赋值的时候，后面并没有符号
     when 2 then 4
     when 3 then 6
     end;
  dbms_output.put_line(y);
end;




十、PL/SQL 的应用
过程化语言：if、case、loop、while、for
declare
  type myrecord is record(name emp.ename%type,sal emp.sal%type);
  v_var myrecord;
begin
  select ename,sal into v_var from emp where empno=7900;
  dbms_output.put_line(v_var.name||' sal is:'||v_var.sal);
end;


DDL、DCL等语句无法在PL/SQL块中直接执行，只能使用动态SQL
使用动态SQL有两种方式：DBMS_SQL包和本地动态SQL


declare
  v_id number:=12;
  v_name varchar2(12):='aa';
  v_sql varchar2(64);
  v_outid number;
begin
  v_sql:='insert into test2 values(:1,:2) returning id into :3';
  execute immediate v_sql using v_id,v_name returning into v_outid;
  dbms_output.put_line(v_outid);
end;




十一、PL/SQL的异常
预定义异常：Oracle服务器定义好的异常
自定义异常：自己定义的异常
NO_date_found:执行查询无结果
too_many_rows:使用select...into时，返回结果超出1条
zero_divide:除以0
timeout_on_resource:等到资源超时
cursor_already_open:打开一个已经打开的游标
invalid_cursor:引用一个无效游标


sqlcode：返回错误码，表示错误的一个负数
sqlerrm：返回错误码和异常信息
这两个函数只能在exception中使用
自定义异常：
declare
  v_sal number;
  saltoohigh exception;
begin
  select sal into v_sal from emp where empno=7698;
  if v_sal>2500 then
     raise saltoohigh;
  end if;
exception
  when saltoohigh then
    dbms_output.put_line('salary is too high');
end;


要将异常从发生者传给调用者，必须用raise语句触发异常；




十二、PL/SQL游标的使用
隐式游标：由PL/SQL 系统定义好的游标
begin
  delete from emp;
  dbms_output.put_line(sql%rowcount||' affected');
  rollback;
end;
隐式游标的属性：
SQL%FOUND:FETCH:fetch语句是否获得了结果
SQL%ISOPEN:游标是否处于打开的状态
SQL%NOTFOUND:fetch语句是否没有获取到结果
SQL%ROWCOUNT:受语句影响的行数（游标记录的行数）
显示游标：由自己定义的游标，属性更隐式游标一样
参数游标：可以实现输入部门号来输出部门员工信息




十三、PL/SQL中的存储过程与函数
十四、PL/SQL触发器
一般情况下，非系统触发器是不能直接调用DDL、DCL和TCL的
DDL触发器：创建、修改和删除数据库对象时被触发
常见的DDL事件：alter/create/drop/grant/revoke/truncate
create or replace trigger ddltrigger
before create on schema
begin
  dbms_output.put_line('ddddddddddddddddddddddd');--这里的'ddddddddddddddddddddddd'并没有出现在输出台上，而是当第一次查表的时候出现在表结构里面
end; 


Instead-of 触发器
1、拦截insert、update、delete操作病使用代替之
2、通常用来管理对不可更新视图的操作
create or replace trigger insteadtrig instead of delete on empview
begin
  dbms_output.put_line('delete on empview');
end;
--当对empview表进行删除操作时，删除操作被替换为begin里面的动作
--empview:
create view empview as select*From emp;


十五、PL/SQL中的包
函数和存储过程也可以重载
在包头定义的变量在包体中的任意一个存储过程或函数都可以使用
包体也可以包含初始化程序和私有函数或存储过程--私有的函数或存储过程必须放在最前面，并且私有的东西是没办法在外部调用的