oracle小知识点

1.oracle 怎样查看实例名：
select instance_name from v$instance;


2.当oracle数据库中有多个实例，怎样连接其中的某个实例：
conn sys/password@orcl as sysdba
或者 conn sys/password@orcl


3，怎样远程登录到某个oracle实例：
conn scott/tiger@192.168.0.101/orcl


4,在往数据库里插入数据的时候报下面的错：
Exception in thread "main" java.lang.AbstractMethodError: 
oracle.jdbc.driver.T4CPreparedStatement.getParameterMetaData()Ljava/sql/ParameterMetaData;
这个可能是数据库驱动问题，驱动版本不对，或者是驱动的jar包忘记加了。

5.五种分类
sql的五大分类:
Data retrieval：数据查询
select
DML：数据操纵语言（行级操作语言）：操作的是表格当中一条一条的数据
insert update delete
DDL：数据定义语言（表级操作语言）：操作的内容为表格（对象）
create alter drop truncate rename
transaction control：事务控制
commit rollback savepoint
DCL：数据控制语言
grant revoke

集合连接
union:将上下结果取并集，去除掉重复的记录（重复的只显示一次）

union all：将上下结果全部显示


minus：取差集 A-B


intersect：取交集


rownum：记录行号
如
id namerownum
100 zs1
200 ls2
300 ww3

oracle 无密码登录

sqlplus "/as sysdba"无密码登陆。

drop user；


alter user **** identified by 密码；修改用户的密码；
sqlplus sys/bjsxt as sysdba用sysdba的身份登录到sqlplus


conn sys/bjsxt as sysdba;
grant create table,create view to scott;




conn scott/tiger; 


alter user scott account unlock;解开账户
查看一个用户下的所有表的名字，：
select table_name from user_tables;

desc 表   ：查看表的信息。
 dual 表只有一个字段，一个记录。
sysdate 查看日期


保持原来的格式用“”；

计算值：selec 2*3 from dual；


select ename,sal*12 anuual from 表；


任何含有空值的数学表达式计算出来的值都是空值




字符串连接：select ename||sal from emp;


select ename||'ni hao'from emp;
字符串表示单引号引起了的一大串字符。


select ename||'ni''de'有一个单引号是写两个单一号，输出时：ni'de

desc 表   ：查看表的信息。
 dual 表只有一个字段，一个记录。
sysdate 查看日期


保持原来的格式用“”；




计算值：selec 2*3 from dual；


select ename,sal*12 anuual from 表；


任何含有空值的数学表达式计算出来的值都是空值




字符串连接：select ename||sal from emp;


select ename||'ni hao'from emp;
字符串表示单引号引起了的一大串字符。


select ename||'ni''de'有一个单引号是写两个单一号，输出时：ni'de






distinct  去重，也可以去除组合一样的
select distinct deptno,job from emp;
当deptno字段和job字段的值都一样是，去掉，否则不去，显示出来 




select * from emp where deptno=10;
where是过滤条件，
select *from emp where ename='clark';
等值判断，也可以用不等值，>< <>不等于


字符串比较：where ename>'cba' 比较阿斯科码


where sal between 800 and 1500;包含
        >=800 and<=1500;是一样的






控制的处理：


select ename,sal,comm from emp where comm is not null;// is null;


where sal in <800,1500,2000>;
where ename in <'ni','hao','ni'>;
对日期的处理：
where hiredate >'20-2月-81';现在只能特定的格式 ，以后会学日期处理函数。


where depton=10 and sal>1000;
where depton=10 or sal<1000;


where sal not in<100,10000>;
模糊查询：
%代表一个多个，_代表一个字母
where ename like '%all%';
where ename like '_A%';
转义字符
默认的是反“\”;
也可以自己指定转义字符：
where ename like '%$%%' escape '$';


select *from dept order by deptno desc;//降序


                order by empno asc;//升序




select * from emp where deptno <> 10 order by empno asc;




order by deptno asc,ename desc;


select ename,sal*1 2 annual_sal from emp where ename not like '_A%' and sal>500 order by sal desc;


常用的sql函数：
 select lower(ename) from emp;小写
 
select ename from emp where lower(ename)like'_a%';


select ename from emp where ename like'_a%'
or ename like '_A%';


select substr(ename,2,3) from emp;//从第二个字符开始截，一个截三个；


select chr(65) from dual;//数字转化为字符


select ascii('A') from dual;




select round(23.45) from dual;  23
select round(16.564,2)from dual;16.56
select round(56.564,-1)from dual;十位


select to_char(sal,'$99,999.9999') from em


L 本地货币


0代表以为数字，没有的话，强制补0；


*****systimestamp 时间戳
select to_char(hiredate,'YYYY-MM-DD HH:MI:SS') from emp;


select to_char(hiredate,'YYYY-MM-DD HH24:MI:SS') from emp;


to_date:::


select ename,hiredate from emp where hiredate > to_date('1981-2-20 12:25:22','YYYY-MM-DD HH24:MI:SS');


where sal < to_number('$1,235.00','$9,999.99');






select ename,sal*12+nvl(comm,0) from emp;//必须是同类型的转换，即字符串对应字符串，整形对应整形。
如果comm 为null 用0替代它；


select max(sal) from emp;


select min(sal)from emp;
select avg(sal)from emp;


select to_char(avg(sal),'9999999,99')from emp;


select round(avg(sal),2) from emp;


select sum(sal)from emp;


select count(*) from emp;


select count(*) from emp where deptno = 10;




group by


select deptno,avg(sal) from emp group by depton;


group by depton,job;




select ename from emp where sal =
(select max(sal)from emp);


select ename,max(sal) from emp group by deptno;//错误的
select 后面的查询的项要不出现在组函数中，就必须出现在group by的后面，否则是错误的




select depton,max(sal) from emp group by depton;//正确的


select ava(sal),deptno from emp group by depton having avg(sal)>2000;
having 是对分组的限制。


select avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 ordedr by avg(sal) desc;


子查询：
select ename,sal from emp where sal = (select max(sal) from emp);


select ename,sal from emp where sal >(select avg(sal) from emp);


select ename ,sal from emp join(select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);
************************************
select last_name ,salary from s_emp 
join (select max(salary) max_salary ,dept_id from s_emp group by dept_id) t on (s_emp.salary = t.max_salary and s_emp.dept_id = t.dept_id)


、、每个部门平均薪水，平均薪水的等级。


select deptno ,avg_sal ,grade from
(select deptno ,avg(sal) sal_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);


自连接：每个员工的名字和其对应经理的名字。
select e1.last_name ,e2.last_name from s_emp e1,s_emp e2
where e1.manager_id = e2.id

cross join 交叉连接。
等值连接
select ename,dname from emp,dept where emp.deptno = dept.deptno;//旧语法
select ename,dname from emp join dept on (emp.deptno = dept.deptno);
select ename ,dname from emp join dept using (deptno);//新语法的两种写法，等值连接。

不等值连接；
select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
三张表的连接
select ename,dname,grade from emp e join dept d on(e.deptno = d.deptno)join salgrade s on(e.sal between s.losal and s.hisal) where ename not like'_A%';
自连接的新语法L：
select e1.name,e2.name from emp e1 join emp e2 on(e1.mgr = e2.empno);
左外连接：
select e1.name,e2.name from emp e1 left join emp e2 on(e1.mgr = e2.empno);
右外连接：
select e1.name,e2.name from emp e1 right join emp e2 on(e1.mgr = e2.empno);
全连接：
select e1.name,e2.name from emp e1 full join emp e2 on(e1.mgr = e2.empno);


求部门中那些人的薪水最高：
select ename ,sal from emp join(select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno);
select last_name,salary from s_emp
, (select max(salary) max_sal,dept_id d from s_emp
group by dept_id) t where (salary=max_sal and dept_id =d)
求部门平均薪水的等级：
select deptno ,avg_sal grade from (select deptno,avg(sal) avg_sal from  emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal);
求部门平均的薪水等级
select deptno ,avg(grade) from (select deptno ,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t group by deptno;
求雇员中有那些人是经理人：
select last_name from s_emp where id in(select distinct manager_id from s_emp)

不准用组函数，求薪水的最高值（面试题）
select e1.salary,e2.salary from s_emp e1 left join s_emp e2 on(e1.salary <e2.salary)
或者：
select distinct sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal<e2.sal));

求平均薪水最高的部门的部门编号：
 
select deptno,avg_sal from (select avg(sal)avg_sal,deptno from emp group by deptno)where avg_sal = (select max(avg_sal) from (select  avg(sal) avg_sal ,deptno  from emp group by deptno));


求平均薪水最高的部门的部门名称：
select dname deptno avg_sal from dept where depton= (
select deptno,avg_sal from (select avg(sal)avg_sal,deptno from emp group by deptno)where avg_sal = (select max(avg_sal) from (select  avg(sal) avg_sal ,deptno  from emp group by deptno)));

求平均薪水等级最低的部门的名称：

select dname ,t1.deptno,grade ,avg_sal from
(
  select deptno,grade,avg_sal from (select    deptno,avg(sal) avg_sal from emp group by    deptno) t join salgrade s on(t.avg_sal     between s.losal and s.hisal))t1
join dept on (t1.deptno = dept.deptno)
where t1.grade = 
(
select min(grade) from (
 select deptno,grade,avg_sal from (select  deptno,avg(sal) avg_sal from emp group by  deptno) t join salgrade s on(t.avg_sal   between s.losal and s.hisal))
)

比普通员工的最高薪水还要高的经理人的名称：
select ename from emp 
where empno in (select distinct mar from emp where mgr is not null) and 
sal>(
select max(sal) from distinct mar emp where mgr in not null);\

rownum 只接受《和《=号’
求薪水最高的前五个人；
select ename,sal from emp where rownum<=5 order by sal desc;
求薪水最高的第六个到第十个人；

select last_name ,salary ,r from
(select last_name,salary ,rownum r from
(select last_name,salary from s_emp order by salary desc)) where r>=6 and r <=10

面试题：比较效率：

select * from  emp where deptno=10 and ename like '%A%';
select * from emp where enam like '%A%' and deptno = 10;
理论上第一个的效率高点，但实际也不一定是这样的，也许数据库对其进行了优化。

创建新用户：
backup scott

exp

create user liuchao identified bu liuchao default tablespace users quota 10M on users

gant create session ,create table ,create view to liuchao;

imp
  
******************************************
DML

insert into dept values(50,'dd','bj');
数据备份：
rollback;
回退

creat table dept2 as select * from dept;
insert into dept2 (deptno,dname) values(60,'games');
update emp2 set sal= sal*2 ,ename =ename||'_' where deptno = 10;

不写where，全部更新。

delete from emp2;

delete from dept2 where deptno=25;

*******************DML结束*******************
rollback;回退
一个事务，起始于一个DML语句，结束于commit；提交了，rollback全部回退。


碰见ddl语句，事务自动提交
当用户自动断开时，事务自动提交
非正常断开的时候，事务自动回退。

数据库常用对象
表：
create table stu
(
id number(6) primary key,
name varchar2(20) not null,
sex number(1),
age number(4),
sdate date,
grade number(2) default 1,
class number(4) references class (id),
email varchar2(50) ,

constraint stu_name_email_uni unique(email,name)//字段的组合必须唯一
)


主键约束：
主键（primary key）：可以唯一标识整条记录的一个属性。
主键不能为空，唯一。。逻辑上代表唯一的一个记录。


可以加到表级上：constraint stu_id_pk primary key(id),
也可以多个字段，组合主键：constraint stu_id_pk primary key(id，name),

外键约束：涉及两个表的两个字段或一个表的两个字段。某一个字段会参照另外一个字段的值。**被参考的字段必须是主键**
 class number (2) references class (id),
也可以加在表级上：
constraint stu_class_pk foreign key (class) references class(id),
修改表结构：
alter table stu (addr varchar2(100));
alter talbe str drop (addr);
alter table stu modify(addr varchar2(25));

修改约束条件：
alter table stu drop constraint sut_class_fk;
alter table stu add constraint stu_class_fk  froeign key (class) references class (id);

删除一张表:
drop table **;

desc user_tables;
当前用户下面有多少张表。

当前用户下有多少长表：
select table_name from user_tables;
当前用户有多少视图：

select view_name from user_views;

当前用户有哪些约束：

select constraint_name from user_constraints;

desc dictionary
*********************************************
索引：
create index idx_stu_email on stu(emali)；
也可以为一个组创建索引。为某个字段建立索引,查的时候效率会高，但插入的时候，修改的时候会慢。

访问某个字段的次数多的时候，可以考虑为这个字段建立一个索引。

视图：就是一个子查询。 能简化我们的查询。
一张虚表，可以保护一些私有的数据。
create view v$_stu as select id,name,age from stu;只能看到某些字段。

*********************
序列:
create sequence s_dept_id
increment by 1
start with 4
maxvalue 999999//nomaxvalue,nominvalue,
nocycle
no cache;// cache n;

******************************
数据库设计三范式：
范式：数据库设计是要遵循的一些规则，姓范的兄弟设计的。所以叫范式。


数据库设计的目标：不存在冗余数据，
一范式：
１．要有主键，
２．列不可分，也不能重复，
第二范式：
不能部分依赖，依赖主键。
第三范式：
1NF：一个table中的列是不可再分的（即列的原子性）


2NF：一个table中的行是可以唯一标示的，（即table中的行是不可以有重复的）


3NF：一个table中列不依赖以另一个table中的非主键的列，还是不通俗！巨寒！！
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊


ＰＬ／ＳＱＬ


set serveroutput on;
begin
dbms_output.put_line('helloworle');
end;


declare
v_name varchar2(200;
begin 
v_name := 'myname';
dbms_output.put_line(v_name);
end;
/




 declare
 v_num number := 0;
 begin
 v_num := 2/v_num;
 dbms_output.put_line(v_num);
 exception
  when others then
 dbms_output.put_line('error');
 end;
/


变量声明的规则：
变量名不能使用保留字，如：from 
第一个字符必须是字母
变量名最多包含30个字符
不要与数据库的表或者列同名
每一行只能声明一个变量


常用的变量的类型：
binary_integer：整数，主要用来计数而不是用来表示字段类型
number：数字类型
char：定长字符串
varchar2：变长字符串
date：日期
long：长字符串，最长2gb
boolean：布尔类型，可以取值为true，false，和null值。

table变量类型

declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;
 v_empnos type_table_emp_empno;

begin
v_empnos(0):=7369;
v_empnos(2):=5897;
v_empnos(-1):= 9999;
dbms_output.put_line(v_empnos(-1));
end;

--Record 变量类型
declare
  type type_record_dept is record
(
deptno dept.deptno%type;
dname dept.dname%type;
loc dept.loc%type;
);
v_temp type_record_dept;

begin 
v_temp.deptno :=50;
v_temp.dname :='aaa';
v_temp.loc :='bj';
dbms_output.put_line(v_temp.deptno ||' '||v_temp.dname);
end;

--使用%rowtype声明record变量


declare
v_temp dept%/rowtype;
begin
v_temp.deptno :=50;
v_temp.dname:='aaa';
v_temp.loc :='vj';
dbms_output_line(v_temp.deptno ||' '|| v_temp.dname);
end;

在pl/sql中select语句中，有且只能有一条语句返回。必须和into一块使用。

declare
v_emp emp%rowtype;
begin
select *  into v_emp form emp where empno = 7369;
dbms_output_line(v_emp.ename);
end;

declare
v_deptno dept.deptno%type :=50;
v_dname dept.dname%type :='aa';
v_loc dept.loc%type :='bj';
begin
insert into dept2 values(v_deptno,v_dname,v_loc);
commit;
end;


游标


存储过程：
执行存储过程：
exec p;

或者：

begin 
p;
end;

DDL：数据定义语言。