oracle学习笔记

1、连接数据库方式
1)、cmd> sqlplus   cmd>scott cmd > trigger
2)、http://127.0.0.1:5560/isqlplus
3)、自带sqlplus的快捷菜单连接到
2、超级管理员登录
CMD>sqlplus sys/密码 as sysdba
更改用户解锁
alter user scott account unlock

3、查询语句
desc(描述) emp(表面)的结构
4、desc dual(空表)就一个字段专门用于计算各种表达式的表
5、当前系统时间 sysdate
select sysdate from dual;
5、select enma||'haohaho' from emp; (字符连接)
6、select sal from emp where sql between 900 and 1900;
取出控制 select ename from  where ename is null;
7、select sysdate from emp where sysdate>'100-2月-01';
8、select ename, sal*12 annal_sal from emp where ename not like '_a%' and sal>800 order by sal desc;

单行函数：

9 、全部转成小写lower ，
10、截取字符串 sustr(str,1,3);从第1个开始截取3个字符
11、chr(65) 把ascii转成字符 ,  ascii（q）  转换成ascii
12 、四舍五入 round (num,n);
13、to_char (字段,'格式')；转换为何种格式；
14、对日期转换 ，select to_char(hiredate,'yyyy-mm-dd hh:mi:ss') from emp;
15、to_date('1922-10-11 12:33:33','yyyy-mm-dd hh24:mi:ss')转换成日前格式
16、转换成数字 to_number('$1233.00','$9999')
17、select ename ,sal*12 + nvl(comm,0) from emp;  nvl处理空值的

多行函数：
18、max(); min(); avg();平均 ，sum();
 select to_char(avg(sal),'999999.99') from emp;
 select count(*) from emp;取出多少条记录
 select count(distinct(去掉重复的) deptno) from
19、group by deptno,job  以多个条件分组
20、 select ename from emp where sal = (select max(sal) from emp)
21、每个部门的最高工资
出现在select列表中的字段必须出现在组函数中或者group by中
  select deptno max(sal) from emp group by deptno;
22、select avg(sal),deptno from emp group by deptno having avg(sal) >2000;
having 是对分组进行限制，where是对单条进行限制
23 完整的select语句
select * from where sal> 2000 group by deptno having .. order by ..
24、select deptno, avg(sal) from emp where sal>1200 group by deptno having avg(sal)>1500 order by avg(sal) desc;

子查询
25、select ename ,sal from emp where sal > (select avg(sal) from emp);\ 
26、select ename ,sal from emp join (select max(sal) max_sal ,deptno from emp group by  deoptno )t
  on (emp,sal = t.max_sal and emp.deptno = t.deptno);
27、select e1.ename, e2.ename from emp e1, emp e2 where e1,mgr = e2.empno;
29 select ename ,dname from emp cross join dept; 交叉连接
30 select ename, dname from emp join dept on(emp.deptno = dept.deptno);
31、left join on () 左外连接，right join on () 右外连接  full join on ()全外连接
求部门平均薪水等级
32 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);

33 求各部门的薪水平均等级
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;

34 组函数嵌套只能是2层
max(avg(sal))

35 create view v$dept_avg_sal as
36、conn sys/密码 as  sysdba
 grant  create table，create view to scott;授权给其权限

创建新用户
conn sys/mima as sysdba 连接登陆
conn scott/tigger 登陆
drop user lide; 删除用户

backup scott 备份用户信息
exp ..导出数据
create user lide identified(认证密码) by lide default tablespace users quota(配额空间) 10M on users
grant create session, create table, create view to lide;授权
imp ...导入数据

rollback 回滚回复

create table emp2 as select * from emp;备份表
insert into dept2 select * from dept;插入正表

为字段 rownum 只能用< 或者 < =
取得工资最高的前5个
select ename, sal from emp (select ename, sal from emp order by sal desc) where rownum<=5;

建表：
char 定长8位字符串存储效率高
varchar2  最长 4096个字节
number （8,3），数值类型
long 长字0符
字段尽量不要和关键字相同 
constraint 约束
unique 唯一（约束  ）
主键约束primary key (非空唯一) 自动加为索引
 外键约束（完整约束条件）references (参考)
create table stu{
 id number(6);
 name varchar2(20) constraint stu_name_no not null,
 sex number(2),
 age number(3).
 sdate date,
 grade number(2) default 1,
 class number(4) ,（参考字段）
 email varchar2(50),
 constraint stu_class_fk foreign key references class(id),
 constraint stu_id pk primary key (id),
 constraint stu_name_email unique(email,name)
}

create table class{
 id number(4) primary key,(被参考的字段必须为主键 )
 name varchar2(20) not null
}

修改表结构
alter table stu add(adds varchar2(1000));
alter table stu drop (adds);删除字段
alter table stu modify (adds varchar2(150)); 修改字段

删除增加修改约束条件
alter table stu drop constraint stu_class_fk;
alter table stu add constraint stu_class_fk foreign key(class) references;

select table_name from user_tables;当前用户下的表
select view_name from user_views;当前用户下的视图
select constraint_name from user_constraints(oracle中的数据字典表);查出现有用户的约束条件
dictionary（数据字典表的表）
  
索引：
create index idx_stu_email on stu (email);
drop index idx_stu_email;

视图
create view v$_stu as select id, name age from stu;

序列
create sequence sq;(oracle 中特有的自动递增序列)

三范式（设计数据库的规则）
1、不存在冗余数据（重复的）设计任何表都要有主键，列不可分
2、不能存在部分依赖
3、不能存在传递依赖