oracle学习笔记 oracle安装、查询等SQL语法介绍

1,oracle用户解锁
1)sqlplus 帐号/密码 yb yb123
2)alter user scott account unlock;

2,浏览器登录isqlplus http://127.0.0.1:5560/isqlplus/
http://v.youku.com/v_show/id_XMzc3Njg0NjQ=.html
3,描述desc
desc emp....dept


4,select * from XXX;
*为表达式什么的.

5,dual是一个空表

6,sysdate关键字,用于查看系统时间;
select sysdate from dual;


7,select ename, sal*12 年薪 from emp;
为sal*12起一个别名nianxin
select ename, sal*12 "nianxin": from emp;可以保持格式小写

8,select ename||sal from emp;
相当于字符串连接 ' ' 单引号里面是一个字符串;如果字符串里面就有单引号 那么两个单引号代表一个引号

9,select distinct deptno, job from emp;
去掉depno和job里面重复的.
select distinct deptno from emp; 去掉重复的;distinct//清楚的明显的....

10,select * from emp 回车完了后面可以接着打
2 where ename = '222'; 选出名字为222的人;
<> <-不等于符号

select ename, sal from emp where sal between 800 and 1000; // 800 到1000
select ename, sal from emp where sal is not null;
is null代表控制 is not null.......

select ename, sal from emp where sal in('SI', 'SDA', 'SDFAS');取出in里面的东西..


11,select ename, sal from emp where sal>1000 or hiredate = 10;
//or 是或者 and是和 not in 是取反
select ename, sal from emp where not in sal>1000 or hiredate = 10;

12,模糊查询
select ename from emp where ename like '%ALL%';
//%是通配付一个或多个 _A% _代表一个字母
select ename from emp where ename like '%&%%' escape '&';
escape申明'&'是转义字符;转义字符默认是\;

13, select *from dept order by deptno desc;按降序排序关键字order by asc是升序默认是升序


14,1)sql 函数 lower 转换为小写 upper你懂的
select lower(ename) from emp;

2)select substr(ename, 1, 3) from emp;
从第一个字符开始截 一共截取3个字符

3)select chr(65) from dual;
select ascii('A') from dual;转换ASC...

4)select round(23.554) from dual;四舍五入输出24
select round(23.554, 2) from dual;输出23.55四舍五入到小数点后两位

5)比较重要
select to_char(sal, '$99,99') from emp;
转换格式必须填9
select to_char(sal, 'L99,99') from emp;
L代表本地货币符号
select to_char(sal, 'L00,00') from emp;
0表示该位上没有数字也显示为0
select to_char(hiredate, 'YYYY MM DD HH:MI:SS') from emp;
排日期 还有HH24 ..就24小时进制咯

6)select ename, hiredate from emp where hiredate > to_date('1981-2-20 12::45::44', 'YYYY-MM-DD HH24::MI::SS');
to_date把字符串按一定格式转换为日期

7)select sal from emp where sal > to_number('$1.254.00','$9.999.99');
to_number转化为数字

8)select ename ,sal*12 + nvl(comm,0) from emp;
nvl(comm,0)如果comm是空值,那么就用0 来替代他


16,组函数 重点就这个5个
select min(sal) from emp;输出最小值的薪水
select max(sal) from emp;
select avg(sal) from emp;平均
select sum(sal) from emp;
select count(*) from emp;求出有多少条记录
select count(comm) from emp;说明有多个有津贴 distinct

17,group by
select avg(sal), deptno from emp group by deptno;
分组函数by按照部门进行分组,要一一对应.
select avg(sal), ename from emo group by deptno;
这条是错的,ename 和 deptno不能对应起来



eg.
每个部门的平均薪水
select avg(sal), deptno from emp group by deptno;


18,使用having 对分组进行限制
select avg(sal) avg(sal), deptno from emp group by deptno having avg(sal) > 2000;
having是对分组进行限制, where是对单条进行限制;

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

20,ed回车可以用记事本来写语句


21,子查询 select 里面套select 语句
select ename, sal from emp where sal >(select avg(sal) from emp);



每个部门平均sal的薪水等级
select ename ,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
select ename ,grade from emp e, salgrade s where (e.sal between s.losal and s.hisal);

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)


22,1999标准 join no

select ename, dname from emp join dept on (emp.deptno = dept.deptno);
连接emp 和 dept两张表
= select ename, dname from emp , dept where (emp.deptno = dept.deptno);
= select ename ,dname from emp join dept using (deptno);

select ename , dname from emp cross join dept;
交叉连接ename dname找出每种组合


23,外连接
select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);

会把左边那张表没产生连接的也打印出来.

右外连接同理
全外连接
select ename, dname from emp e full join dept d on (e.deptno = d.deptno);



24,
求部门中哪些人的薪水最高
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 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 ename, sal from emp where sal = (select max(sal) from emp);

工资位于平均工资之上


哪些人是经理人
select ename from emp where empno in(select distinct mgr from emp);

不用组函数求薪水的最高值
select ename, sal from emp where ename not in( select distinct e1.ename fro
m emp e1 join emp e2 on e1.sal < e2.sal);


平均薪水最高的部门编号
改天

平均薪水的等级最低的部门名称
select dname, t1.deptno, grade, avg_sal from
(
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) e
join salgrade s on (e.avg_sal between s.losal and hisal)
) t1
join dept on (t1.deptno = dept.deptno)
where t1.grade =
(
select min(grade) from
(
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) e
join salgrade s on (e.avg_sal between s.losal and hisal)
)
)
/

比普通员工的最高薪水还要高的经理人
1)先选出哪些不是经理人的最高薪水 select max(sal) from emp
where empno not in (select mgr from emp where mgr is not null)
2)
select ename, sal from emp
where sal >
(
select max(sal) from emp
where empno not in (select mgr from emp where mgr is not null)
)
and empno in (select distinct mgr from emp)


25,创建视图view 需要权限
简化用creat view v$dept_avg_sal_info as
select deptno, avg_sal, grade from
(select deptno, avg(sal) avg_sal from emp group by deptno) e
join salgrade s on (e.avg_sal between s.losal and hisal)
授权
conn sys/orcl as sysbda;
grant create table, creat view to scott;

conn scott/tiger

26,
建立新的用户
登录超级管理员 conn sys/orcl as sysdba;

1)删除用户drop user scott cascade;
2)创建新用户
1--backup scott
exp 导出
2--create user
create user username用户名 identified by password密码 default tablespace users默认空间 quota 10M on users分配10M的空间
3--赋权限
grant create session登录, create table建表, create view to username;
3--import data
imp
需要输入用户名的地方输入scott也就是你要导的那个用户名

ORA-01950错误分析 对表空间无权限2009-02-12 21:10ORA-01950错误分析
1.用户没有resource权限。

2.在修改了用户的表空间后，在用户中创建表时会出现以下的错误：ORA-01950: 表空间'HDNHG'中无权限

这个时候就要给修改了表空间的用户重新分配权限如：grant connect,resource to username;

再切换到该用户下创建表OK了。。

3.总的来说这个错误是由于对表空间操作的权限不足造成的,所以这个时候就可以检查出错之前对于所操作的表空间有哪些权限可能被revoke了(或者说原来就没有grant),然后重新赋予相应的权限。


撤销权限revoke
更多权限管理http://www.javaeye.com/topic/431907


27,
create table emp2 as select * from emp;//相当于emp2是emp的备份
create v iew xxx as xxx;
insert into dept values(50,'game','sdf');//插入
insert into dept (deptno)values(60);//只插deptno这个字段
insert into dept select * from dept;

update
update emp2 set sal = sal * 2,ename = ename || 'sb';


delete from emp2 where ename = 'xx';

drop table xxx;


28,rownum
select empno, ename from emp where rownum <= 5;
只能是<= 或者<不能是= >
解决方法用一个子查询
select empno,ename from(select rownum r, ename, empno from emp) where r > 5;

求薪水最高的前5名雇员
select ename, sal, rownum r from
(
select ename, sal from emp order by sal desc
)
where rownum <= 5

mysql 下可以用limit

29,create
创建表
create table t (a varchar2(10));

varchar2不定长
char定长 区别只是效率和空间的问题

number(8,2) 8位,两个小数点
long用来存大的文章之类
date;

create table stu
(
id number(9) default 1,//设置一个默认值
name varchar2(20) not null//设置为非空,必须输入名字.

);
insert into stu(id) values(1);报错

30,transaction 事务

commit;提交事物完成 rollback回不去了;

create table grand exit之类 事务也提交一样rollback不回去;
非正常断开连接自动回滚;


31,constraint

1)非空约束
create table stu
(
id number(9) default 1,//设置一个默认值
name varchar2(20) constraint stu_name_nn not null
);
//设置为非空,必须输入名字.
使用constraint 关键字为限制取一个名字




2)唯一约束,例如学号必须唯一
create table stu
(
id number(9) unique,//设置一个默认值
name varchar2(20),
date date;
);
学号重复则报错

create table stu
(
id number(9) unique,//设置一个默认值
name varchar2(20),
date date,
email varchar2(50),
constraint stu_name_email unique(name, email)
);
组合重复约束,如果名字和邮件地址都重复才重复



3)主键约束
create table stu
(
id number(9) unique,
name varchar2(20),
sdate date,
email varchar2(50),
constraint saaa primary key (id)
);
唯一且不能为空

4)外键约束 设计到两张表或者一张表的两个字段
例如dept这张表里的deptno里面没有50这个部门,那么在emp里面插入数据就不能插入50这个部门编号

用法
create table class
(
id number(1) primary key

)
create table stu
(
id number(9) unique,
class number(1) references class(id)//class是表的名字
)

另一种写法,表限制
create table stu
(
id number(9) unique,
class number(1),
contraint sss foreign key(class) references class(id)
)

重要:被references的值必须是主键
如果另一张表有值在参考class 则不能删除class里的这条记录

5)check
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)
Id_P>0咯不解释




32,
函数返回表中的记录数：

SELECT COUNT(*) FROM table_name;
select count(xxx)from tab;--返回制定列的值的数目


33,alter
修改表结构,不是很重要
增加不解释
alter table stu add(aaa varchar2(22));
删除
alter table stu drop(aaa);
修改
alter table stu modify(aaa varchar2(33));

删除添加表的约束条件
删除
alter table stu drop constraint xxx;
添加
alter table stu add constraint xxx foreign key(class) references class(id);
alter table stu add check(id>0);
之类

34,查看数据字典表

有一张默认的表 user_tables
select table_name from user_tables;
select view_name from user_views;
select constraint_name from user_constraints;
select index_name from user_indexes;
存放数据字典表的表 dictionary


35,indexs
建立索引,读数据的时候更快,修改的时候会更慢一点.一般不轻易建立索引.
create index idx_stu_email on stu (email);



36,视图
就是一个虚表,可以看做是一个子查询.可以简化查询和保护数据
create view v$_stu_info as select id, name, class from stu;SS

37,sequence序列 orcle only
创建一个独一无二自动递增的数字
create sequence seq;
select seq.nextval from dual;
会发现每次运行都产生不一样的数字,默认已经包含同步了



38,三范式
1有主键 列不可分
2当一张表里有多个主键存在时,不能依赖于单独的主键
不能部分依赖,要依赖于组合的主键.不然会存在数据的冗余
3不存在传递依赖

一般设计良好的数据库SQL语句写起来就会麻烦一些



39,PL_SQL
1-第一个简单的hello world程序
BEGIN
DBMS_OUTPUT.PUT_lINE('HELLO WORLD');
END;

要显示需要设置默认显示set serveroutput on;默认是关闭的

2-声明变量declare

declare
v_name varchar2(20);--约定的v_开头的变量名
begin
v_name := 'name';
dbms_output.put_line('name' || v_name);
end;


3-exception

declare
v_name number := 0;
begin
v_num := 2/v_num;
dbms_output.put_line(v_num);
exception
when others then--相当于catch excption了
dbms_output.put_line('error');
end;

3-变量声明
每行只能声明一个,不能与数据库或表重名
变量类型
简单变量
a number(1);
a binary_integer := 0;--主要用来计数
a number(7,2) := 4000.00;
a date := sysdate;--sysdate系统当前时间
a constant number(3,2) := 3.14;
a boolean := false;--可以取空值,不赋值默认为空.不能打印
a varchar2(20) not null := '12345';

%type属性
declare
v_empno emp.empno%type;
v_empno v_empno%type;


复合变量
table 相当于数组
declare
type type_table_emp_empno is table of emp.empno%type index by binary_integer;--相当于声明一个数组类型
v_empnos type_table_emo_empno;
begin
v_empnos(0) := 4444;
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%typ
);
v_test type_record_dept;
begin
v_test.deptno := 50;
v_test.dname := '11';
v_test.loc := 'cd';
dbms_output.put_line(v_test.deptno || v_test.dname || v_test.loc);
end;



4-PL_SQL语句的不同
----执行SQL语句必须且只返回一条记录.有且只有
select ename,sal into v_name,v_sal from emp where empno = 7369;--必须有into,赋值
dbms_output.put_line....


---excute immediate 关键字

begin
execute immediate 'create table T(te/
st varchar2(20) default ''aa'')';
end;
执行DDL语句的时候要用execute immediate'';单引号内加语句


----IF关键字
declare
v_sal emp.sal%type;
begin
select sal into v_sal from emp where empno = 7369;
if (v_sal < 1200) then
dbms_output.put_line('low');
elsif(v_sal < 2000) then---eslif不是else if
dbms_output.put_line('high');
else
dbms_output.put_line('high very');
end if;
end;


----循环
loop

declare
i binary_integer := 1;
begin
loop
dbms_output.put_line(i);
i := i ++;
exit when(i > = 10);
end loop;
end;


for

begin
for k in 1..10 loop
dbms_output.put_line(k);
end loop;
for k in reverse 1..10 loop--逆序
dbms_output.put_line(k);
end loop;
end;


5---错误处理
declare
v_temp number(4);
begin
select empno into v_temp from emp where deptno = 10;
exception
when too_many_rows then
dbms_output.putline('aaa');
when others then
dbms_output.putline('error');
end;



6---游标cursor,重点 相当于迭代器
declare
cursor c is
select * from emp;
v_emp c%rowtype;--rowtype相当与C这是一表的类型
begin
open c;
fetch c into v_emp;
dbms_output.put_line(v_emp.ename);
close c;
end;
拿出第一条记录


declare
cursor c is
select * from emp;
v_emp c%rowtype;--rowtype相当与C这是一表的类型
begin
open c;
loop
fetch c into v_emp;
exit when(c%notfound)
dbms_output.put_line(v_emp.ename);
endloop;
close c;
end;
循环拿出每一条记录


游标的4个属性
c%isopen是不是打开了;
c%notfound最近的一次fetch如果没有找到就返回true;
c%found 找到就返回true;
c%rouwcount 当前已经fetch 到了多少条记录

for循环遍历,有些不一样.不用open fetch什么的
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;


待参数的游标
declare
cursor c(v_deptno emp.deptno%type, v_job emp.job%type)--和形参差不多
is
select ename, sal from emp where deptno = v_deptno and job = v_job;
begin
for v_temp in c(30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;


可更新的游标
declare
cursor c
is
select * from emp for update;
begin
for v_temp in c loop
if(v_temp.sal < 2000) then
update emp set sal = sal * 2 where current of c;---where current of c就是更新当前这个游标
elsif (v_temp.sal = 5000) then
delete from emp where current of c;
end if;
end loop;
--commit;
end;


创建存储过程
create or replace procedure p
is
begin
dbms_ouput.put_line('222');
end;

执行后会提示过程已经完成,但是他没有执行
执行方法
1)exec p;
2)
begin
p;
end;
这样的话可以方便的多次执行


带参数的存储过程
create or replace procedure p
(v_a in number, v_b number, v_ret out number, v_temp in out number)-- 传入传出,默认是in
is
---变量声明 无
begin
if(v_a > v_b) then
v_ret := v_a;
else
v_ret := v_b;
end if;
v_temp := v_temp + 1;
end;



执行
declare
v_a number := 3;
v_b number := 4;
v_ret number;---因为他是out number
v_temp number := 5;
begin
p(v_a, v_b, v_ret, v_temp);
dbms_output.put_line(v_ret);
dbms_output.put_line(v_temp);
end;


show error;


函数function
create or replace function sal_tex
(v_sal number)--形参
return number
is
begin
if(v_sal < 2000) then
return 0.10;
else
return 0.20;
end if;
end;

这个不是很重要


触发器
create table emp2_log
(
uname varchar2(20),
ation varchar2(10),
atime date
);

创建一个触发器
create or replace trigger trig
after insert or delete or update on emp2 for each row---或者把after改成俄海关before 如果加上for each row 会产生多条记录.每更新一条记录就触发一次,而不是每次操作触发一次
begin
if inserting then
insert into emp2_log values(USER, 'i', sysdate);--USER关键字当前的用户
elsif updating then
insert into emp2_log valuse(USER, 'u', sysdate);
elsif deleting then
insert into emp2_log valuse(USER, 'd', sysdate);
end if;
end;


触发器的另类使用
create or replace trigger trig
after update on dept
for each row
begin
update emp2 set deptno = :NEW.deptno where deptno = :OLD.deptno;
end;

这样的话就可以修改emp2里面被参考的值了



树状结构的展示recursion

create table article
(
id number primary key,
cont varchar2(4000),
pid number,--pried
isleaf number(1),---0代表非叶子节点, 1代表叶子节点
alevel number(2)---级别
);
id l al
insert into article values (1, '大象大战蚂蚁', 0, 0, 0);
insert into article values (2, '大象被打趴下了', 1, 0, 1);---pid就是他的父ID
insert into article values (3, '蚂蚁也不好过', 2, 1, 2);---说明他是最后一个了
insert into article values (4, '瞎说', 2, 0, 2);
insert into article values (5, '没有瞎说', 4, 1, 3);
insert into article values (6, '怎么可能', 1, 0, 1);
insert into article values (7, '怎么没有可能', 6, 1, 2);
insert into article values (7, '可能性是很大的', 6, 1, 2);
insert into article values (8, '大象进医院了', 2, 0, 2);
insert into article values (9, '护士是蚂蚁', 9, 1, 3);


蚂蚁大战大象
大象被打趴下了
蚂蚁也不好过
瞎说


只需要在Oracle官方网站下载一个叫Instant Client Package的软件就可以了，这个软件不需要安装，只要解压就可以用了，很方便，就算重装了系统还是可以用的。

下载地址：
http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html
解压到一个目录中即可，例如c:\oracleclient
2. 配置操作系统环境变量
NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
TNS_ADMIN=C:\oracleclient\NETWORK\ADMIN
3. 配置连接Oracle的配置。tnsnames.ora文件到目录C:\oracleclient。可以从oracle服务器拷贝一个过来。
4. 安装PLSQL Developer
5. 配置PLSQL Developer（第一次可以不用登录直接进到PLSQL Developer）
Tools --> Preferences--> Connections,
Oracle Home内容为 C:\oracleclient
OCI library内容为 C:\oracleclient\oci.dll
重新启动PLSQLDeveloper
完毕

触发器 自动递增


001.创建唯一索引控制 ID ACCOUNT什么的两个唯一


创建主键：

alter table T add primary key (V)

T是表名，V是列名



创建索引：

create index F2009100000NMINFOSYS_XIANG on f2009100000nminfo( SYS_XIANG );
创建一般索引，索引名为表名+列名



create unique index F2009100000NMINFOSYS_ZDM on f2009100000nminfo( SYS_ZDM );

创建唯一索引



create BITMAP index F2009100000NMINFOSYS_XIANG on f2009100000nminfo( SYS_XIANG );
创建位图索引



完整语法如下：

CREATE (UNIQUE|BITMAP) INDEX [用户名.]索引名 ON [用户名.]表名 (列名 [ ASC | DESC], [列名 [ ASC | DESC]]...)

[ TABLESPACE 表空间名 ]

[ PCTFREE 正整型数 ]

[ INITRANS 正整型数 ]

[ MAXTRANS 正整型数 ]

[ 存储子句 ]

[ LOGGING | NOLOGGING ]

[ NOSORT ]