Oracle学习笔记总结

小鱼儿 2022-09-26 13:53 219阅读 0赞

**用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字句上创建索引，加快判断速度