Oracle学习笔记总结

用sys登录
用户名：    sys
密码/口令：  change_on_install as sysdba
刚安装完修改一下密码


命令：
1，conn+用户名/密码    连接命令
2，disc                          断开连接
3, password                  修改密码    
4，show user              显示当前用户名
5，exit                    退出同时会退出sql*plus        


Oracle图形化管理工具--sqlDeveloper
路径设置：C:\app\xh\product\11.2.0\dbhome\jdk\bin\java.exe


Oracle数据类型
CHAR VARCHAR 
NUMBER(p,s)：即可以存储浮点数，又可以存储小数
    p表示数字的最大位数（若是小数包括整数部分和小数部分和小数点），s指小数位数


Oracle建表和约束
create table infos{
stuId varchar2(7) not null,
Gender varchar2 not null,
stu varchar2(7) not null,
Age number(2) not null,
Birthday date
}
alter table infos add constraint pk_infos primary key(stuId)
alter table infos add constraint ck_infos check(Gender='男' or gender='女') 
alter table infos add constraint ck_infos check(Age>=0 and Age<=100) 


Oracle分组查询
SELECT AVG(sal),MAX(sal),deptno FROM emp GROUP by deptno
SELECT AVG(sal),MAX(sal),deptno,ename FROM emp GROUP by deptno  -> 语法错误
注: SELECT后面查询的字段必须是聚合函数或者在GROUP by后面出现的字段才能在SELECT后面出现


显示各个员工的姓名，工资以及工资级别
SELECT e.ename,e.sal,s.grade FROM emp e,salgrade s
       WHERE e.sal BETWEEN s.local AND s.hisal


显示雇员名，雇员工资以及所在部门的名字，并按部门排序
SELECT e.ename,e.sal,d.name FROM emp e,dept d
       WHERE e.deptno=d.deptno ORDER BY e.deptno


自连接(显示某个员工的上级领导姓名---领导和员工都在一张表里面)
SELECT worker.ename,boss.ename FROM emp worker,emp boss
WHERE worker.mgr = boss.empno


pl/sql
1,模块化的设计思想（分页，订单，转账）
2,提高应用程序的运行性能
3,减少网络传输量
4，提高安全性
缺点：移植性不好


set serveroutput on //打开输出选项，设置输出到SQL*Plus
DECLARE sname varchar(20) := 'jerry';
 BEGIN
   sname := sname||' and tom'; //  '||'表示将两个串拼接
   dbms_output.put_line(sname);   //打印sname
 END;

declare pi constant number := 3.14;
r number default 2;
area number;
begin
     area := pi*r*r;
     dbms_output.put_line(area);
end;

declare sname varchar2(20) default 'jerry';
begin
    select ename into sname from emp empno=7934;
    dbms_output.put_line(area); 
end;


属性数据类型
Oracle中存在两种数据类型：%TYPE和%ROWTYPE


set serveroutput on
declare 
  v_ename  VARCHAR2(30);
begin
  select ename into v_ename from emp where empno=&no; //'&'表示从控制台输入
  dbms_output.put_line('雇员名:'||v_ename);
--异常处理
exception
  when no_data_found then
  dbms_output.put_line('输入有错误');
end;


--存储过程
create table mvtest{
  name varchar2(30),
  password varchar2(30)
};
create or replace procedure sp_pro is
begin
 insert into mvtest values('张三','aa12');
end;


--执行存储过程
exec sp_pro

--带参数的存储过程
 编写一个过程，可以输入一个雇员名，如果该雇员的
 工资小于2000，就给增加1000
 create or replace procedure sp_pro(spName vachar2) is
 v_sal emp.sal%type;  --定义一个变量v_sal,这个变量的类型是与emp表里的sal的类型一致
 begin
   select sal into v_sal from emp where ename = spName;
   if(v_sal < 2000) then 
    update emp set sal = v_sal + 1000 where ename = spName;
   end if;
 end;
   

--带输出参数的存储过程
 create or replace procedure sp_pro(spName in number,spName out varchar2) is  --in表示输入参数，out表示输出
 begin
    select ename into spName from emp where empno = spon;
 end;


 set serveroutput on --打印输出
 declare spno1 number := 7369;   --定义变量并赋值7369
 spname1 varchar(10); 
 begin
   sp_pro(spno1,spname1);
    dbms_output.put_line(spname1);
 end;          

序列：
    是用来生成连续的整数的数据对象。序列常常用来作为主键中的增长列


例子：
    create table cdpt100{
        id number(6),
name varchar2(30),
constraint pk_id primary key(id)
    };
    --更改数据库的"延迟段创建"特性为false
    ALTER SYSTEM deferred segment creation = FALSE;
    
    create sequence seq_cdpt100
    increment by 1 -- 增长量为1
    start with 1 -- 从1开始
    maxvalue 99999      -- 最大值
    minvalue 1 -- 最小值
    NOCYCLE -- 不循环
    nocache        -- 不缓存


   insert into cdpt100 values(seq_cdpt100.nextval，"NBA")  --序列名.nextval表示每执行一次都取下一个值

Oracle中的伪列
   在Oracle的表的使用过程中，实际表中还有附加的列，称为伪列。伪列就像表中
   的列一样，但是在表中并无存储。伪列只能查询，不能进行增删改工作。 ROWID
   物理地址，ROWNUM表示行号


   select ROWID ename from emp where sal > 2000;
 
   查询emp表中第五条到第十条中的记录
   select * from (select ROWNUM R,ename,job,sal from emp where ROWNUM <= 10) where R > 5;


Oracle中的函数
   SELECT CONCAT('Hello','World') FROM DUAL;   --HelloWorld,DUAL表示伪表,可以查系统的数据
   SELECT  FROM DUAL;

   select sysdate,add_months(sysdate,5) from dual --查询当前的日期并且在加5个月

   select TO_CHAR(SYSDATE,'YYYY'年'MM'月DD'日 HH24:MI:SS) "date" FROM DUAL;


索引
   特点：
      1，可以大大加快数据的检索速度，这也是创建索引的最主要原因
      2，可以加速表与表之间的连接
      3，在使用分组和排序字句进行数据检索时，可以显著减少查询中分组和排序的时间
   不足：
      1,创建索引和维护索引需要时间，这种时间会随着数据量的增加而增加
      2,索引需要占用物理空间 
      3,当对表中的数据进行增删改的时候，索引也要动态的维护，降低数据的维护速度 
   为EMP表的工资列创建索引
   CREATE INDEX idx_sal on emp(sal);


应该建索引列的特点：
1，在经常需要搜索的列上，可加快搜索的速度
2，在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
3，在经常需要进行范围搜索的列上创建索引
4，在经常需要排序的列上创建索引
5，在经常使用的where字句上创建索引，加快判断速度