Oracle学习笔记1

1. DBMS:数据库管理系统（database management system）操纵和管理数据库的软件，用户建立使用维护数据库
www.oracle.com
  -- oracle服务
OracleServieXXX -- 数据库服务：这个服务会自动启动和停止数据库
OracleHOME_NAMETNSListener  --  监听器服务：只有在数据库需要远程访问时才需要
OracleDBConsoleXXX  -- 基于web方式的oracle企业管理器eom控制台服务
OracleJobSchedulerXXX -- oracle作业调度程序服务
2. 用户（比如小明），角色（比如班长），权限。-- 用户：角色：权限-1：n：n
oracle系统默认用户
system：数据库内置的一个普通管理员
sys：数据库的超级用户，必须以sysdba身份登陆
scott：简单的样例账户
sysman：企业管理器账户
。。。


3.SQL分类
  -- DCL：控制，grant，revoke，commit，rollback；
  -- DDL：定义，create，drop，alter；
  -- DML：操作，insert，update，delete；
  -- DQL：查询，select；



4. 用户操作
-- 创建/删除用户，
create user test01 identified by 123456; -- 创建test01用户，密码123456
drop user test01;  -- 删除用户
-- 锁定/解锁用户，
alter user test01 account lock/unlock;
-- 修改用户密码，
alter user test01 identified by abc123;
-- 赋予用户角色，
grant connect to test01; -- 赋予test01用户connect角色（登陆系统的权限）
-- 回收用户角色，
revoke connect from test01; -- 收回test01用户connect角色
-- 赋予用户权限，
grant create session to test01;
-- 回收用户权限
revoke create session from test01;
5. 常见角色
CONNECT角色：获取数据库连接，创建回话
RESOURCE角色：创建自己的表，序列，过程等
DBA角色：拥有所有的系统权限，包括无限制的空间限额和给予其他用户授予各种权限的能力

6. 角色操作
-- 创建/删除角色
create role myrole; -- 创建myrole角色
-- 赋予角色操作权限
grant create session to myrole;  -- 赋予myrole角色登陆数据库的权限
-- 驳回角色操作权限
revoke create session from myrole;
7. 权限
创建会话 -- crate session
。。。
8. ---sqlplus-------------------------------
-- system身份
show users; -- 显示当前登陆用户
connect test01/123456; -- 连接test01用户/密码
connect / as sysdba; -- 以超级管理员身份登陆
connect system/XXXXXX@服务器名 -- 以system身份登陆
desc students; -- 查看students表的所有字段及类型
-- 用sysdba身份操作
alter user test01 account lock;   -- 将test01用户锁定
alter user test01 account unlock;   -- 将test01用户解锁
shutdown immediate; -- 关闭数据库
startup; -- 开启数据库
exit; -- 退出数据库
  ---cmd----------------------------------------
net start/stop OracleServiceyangxing -- 启动/关闭数据库服务
lsnrctl start/stop -- 启动/关闭监听器服务
    --------------------

9. 设计数据表方法
1-分析有哪些实体对象
2-分析实体与实体之间的关系（1:1，1:n，n:1，n:m）
3-分析实体有哪些属性
4-画出E-R图
5-创建表
6-数据完整性约束
    -1-非空（not null）约束 （可以是空格）
    -2-唯一（unique）约束（可以为空）
    -3-主键（primary key）约束（特点：唯一，非空）
    -4-外键（foreign key）约束（特点：参照的主表主键存在，允许为空）
-- constraint fk_stu_cla foreign key(stu_class) references classes2(cla_no)
    -5-条件（check）约束
-- check(stu_sex='M' or stu_sex='M')


10. --- 修改表字段 约束条件
（格式：alter table [表] [修改方式] constraint [条件名] [条件]）
alter table tb_dep add constraint pk_dep primary key (dep_id); -- 添加主键
alter table tb_dep drop constraint pk_dep; -- 删除约束
commit; -- 提交事务


    --- 修改表字段
rename qq_users to tb_user;  -- 修改表名
alter table qq_users add    qq_name varchar(20); -- 表 添加字段
alter table qq_users drop   column qq_age; -- 表 删除字段
alter table qq_users modify (qq_no varchar2(30)); -- 修改表 字段类型
alter table qq_users rename name to StuName; -- 修改表 字段名称


   -- 删除表字段
delete from stu where stu_no = '123';  -- 在提交前，回滚事务，可撤销删除；
truncate from stu; -- 不记录单个行删除操作,不可撤销,；

　　
11. 常用数据类型
-字符
  定长  char(N)，N（表示一个正整数，表示一个字节或字符），不足N位空格补齐（utf8汉字字节3）；
char(N CHAR) , 接受任意字符（例：一个汉字表示为一个字符）
nchar(N)，本地化字符（gbk汉字字节2）
  不定长  varchar2(10)
  long , 用于存储较长的文本，最大2GB
-数字
  number(p,s)， p:精度位，表示整数位和小数位的有效数位，取值范围38，负号不占有效位；s:小数位
-日期和时间
  date，7个字节（格式：世纪+100 年 月 日 小时+1 分+1 秒+1）
  timesstamp(p)，11个字节,n表示小数秒的精确度(0-9，默认6)（1秒=1000*1000*1000纳秒）
-大数据型
  blob, 二进制大对象，适用于存储非文本字节流数据（如程序、图像、影音等）
  clob， 字符型大对象，与字符集相关，适用于存储文本型数据（如小说，档案等）


12. 数据库运算符优先级
1- 算数运算符
2- 连接运算符
3- 比较运算符
4- is [not] null、like、[not] in
5- [not] between
6- 不等于
7- not 逻辑运算符
8- and 逻辑运算符
9- or 逻辑运算符
可以使用括号更改优先级规则


13. 函数（test03_5）（格式：select [函数] from dual; dual为临时表）
    ----函数使用方式：
1- 了解函数功能； 2- 明确函数参数个数以及数据类型，有些参数可选； 
3- 明确函数返回的数据类型；


    1-数字函数 
ABS(x):求一个数的绝对值；
round(x,y)：四舍五入，x为被执行的参数，y为小数点后保留的位数（可选,可为负,默认为0,为小数时截取整数）；
trunc(x,y)：数据截取，用法与round相同；
mod(x,y)：取余（取模），x为被除数；
power(x,y)：求x的y次方；
sign(x)：判断一个数的符号（正为1，负为-1，0为0）；
sqrt(x)：求x的平方根（不可为负）；
ceil(x)：向上取整（求大于或等于给定值的最小整数，注意负数）；
floor(x)：向下取整；
    2-字符函数
lower('bc')：字符大写转小写；
upper('AC')：字符小写转大写；
length('afs !a123')：获取字符串长度（包括汉字和空格）；
concat('he','llo')：字符串拼接（参数个数2）；
trim(' he llo ')：去字符串首尾的半角空格（ltrim去左边空格，rtrim去右边空格）；
substr(x,i,y)：字符串截取(x被截取的字符串，i起始位置，y截取的个数（可选，默认到结束）)；
replace('wwww.baidu.com','ww','*')：替换
------------------------
函数嵌套
select replace(lower('WWWabcwww1234Ww'),'w','*') from dual;
    3-日期函数
sysdate：获取当前数据库系统的日期和时间；
last_day(sysdate)：获取给定日期月的最后一天（返回值日期格式）；
next_day(x,y)：当前数据库下指定日期的下一个星期的第几天（x为日期，y为一个星期的第几天）；
add_months(x,y)：向x日期后推y个月（y可为负）；
months_between(x,y)：两个日期之间的月数(x减y，返回值可为小数和负数)；
extract(year from e.birthday)：获取年；
--  select * from employee e where extract(year from e.birthday) = 1993;
  


    4-转换函数
to_number()；字符 -> 数字；
to_char()：数字/日期 -> 字符（to_char(x,'yyyy-mm-dd hh24:mi:ss d ddd day')）； -- day:星期二
to_date(x,y)：字符 -> 日期：（x字符与y格式匹配，字符默认格式:13-3月 2018）

14. 查询
    1- 单表查询
-- 去除重复行
select distinct(s.stu_class) 班级 from tb_students s;
    2- 条件查询
-- where 子句；
-- 使用 =、<=、>、!=、between、in、like和null条件的关系运算符比较条件；
    where s.stu_class <> 'test'；
    where s.stu_age between x and y; ( >=x and <=y )；
    where s.stu_age in (20,22,24,26);
    where s.stu_name like ('赵%'); (%表述0到多个字符可用下划线)；                                                                                                
-- 使用and、or和not运算符的逻辑条件；
    where not s.stu_class = 'test';
    where (s.stu_age >= 24 or s.stu_age = 21) and s.stu_sex = '男';


15. 排序(默认升序)   -- 升序 asc,   -- 降序 desc 
    -- 1 按年龄排序
select * from students s order by s.age asc; 
    -- 2 根据多个字段排序（年龄相同时，再根据id排序）
select * from tb_students s order by s.stu_age, s.stu_id;
    -- 3 分组查询（查询字段必须包括在 GROUP BY子句中）
select  count(*),s.stu_class,max(s.stu_age) 最大年龄,avg(s.stu_age) 平均年龄 from tb_students s group by s.stu_class;
    ---- 统计函数 ---- 
-- 统计行数：count(*);
-- 求最大值：max(); 
-- 求最小值：min();
-- 求平均值：avg();  avg()=sum()/count();
-- 求和：sum();
    ---- 其他函数 ----
-- nvl(x,y):如果x为空，则显示y;
   /* 查询学生表姓名，班级（如果该学生班级为空，显示"未知班级"）。*/
           select s.stu_name,nvl(s.stu_class,'未知班级') 班级 from tb_students s;
-- nvl2(x,y,z)：如果x为空，显示y，不为空，显示z；


-- nullif； 如果两个表达式不想等，返回第一个值，相等返回null;
/* 数据与显示值的替换 */
-- decode(e.sex,1,'男',2,'女','未知') :sex为1:显示"男"，2:显示"女"，其他:显示"未知"；
-- case:  select e.emp_name,
     case e.sex
            when 1 then '男'
            when 2 then '女'
            else '未知'
               end as sex
      from employee e ;
     
  /** 分组查询注意点：
     1. 分组查询的数据属于小组，而不属于某一条记录；
     2. 分组查询在select与from之间允许出现：统计函数，group by后面的字段；
  */


   -- 4 分组筛选（having）;
select s.stu_class,count(*) from tb_students s group by s.stu_class having count(*) > 3;
   -- 5 按小组再分组查询
select rank()over(partition by s.cno order by s.score desc) as rd, s.* from t_sc s;--(各科成绩降序)



16. 多表查询
    --1 笛卡尔积（交叉连接）
select * from tb_s s , tb_c c;
sleect * from tb_s cross join tb_c;  -- 不常用；
    --2 等值连接
select * from tb_s s , tb_c c where s.cid = c.cid;
    --3 自然连接
select * from tb_s s natural join tb_c c; --（1找出两张表相同名称的字段，2匹配相同名称字段的值）；
    --4 内连接
select * from tb_s s inner join tb_c c on s.stu_class = c.cla_id; -- inner可选，on语句不可少；
    --5 外连接（[left|right|full] outer join -- outer可选）；
select count(sid) from tb_s s left join tb_c c on s.cno = c.cno group by cname;  -- on语句不可少；
    --6 自连接
select s1.* from tb_s s1, tb_s s2 where s2.sname='孙三' and s1.sage>s2.sage;(年龄大于孙三的同学)；


17. 子查询
    -- 1.查询年龄小于平均年龄（所有学生）的学生信息
       select * from tb_students s1
       where s1.stu_age < (select avg(s.stu_age) avg_age from tb_students s);


    -- 2查询年龄小于本班平均年龄的学生信息，显示：学生姓名，年龄，本班平均年龄；
    /**
     * 1.查出每个班级的平均年龄；
     * 2.查询学生信息，条件：年龄>本班平均年龄；
     */
      select * from tb_students s2 inner join
       (select s.stu_class,avg(s.stu_age) avg_age from tb_students s group by s.stu_class) s1
      on s2.stu_class = s1.stu_class where s2.stu_age < s1.avg_age;


18. 集合运算符  -- (查询字段列相匹配)；
    -- union运算符（合并A查询和B查询的结果集,去除重复行）
select * from tb_s union select * from tb_s; 
    -- union all（合并A查询和B查询的结果集,不去除重复行）
select * from tb_s union all select * from tb_s;
    -- intersect（查询A查询和B查询的交集）；
select * from tb_s intersect select * from tb_s s where s.class='web';
    -- minus（显示A查询，删除B查询包含的记录集）；
select * from tb_s s1 where s1.class in ('java','web') 
minus select * from tb_s s2 where s2.class in ('java','test');


19. 其他函数
  -- 1.判断字段是否为空
     select * from tb_t t where t.cla is null;
     select * from tb_t t where t.cla is not null;
  -- 2.伪列（rownum）
     select rownum,t.* from tb_t t; -- 给每条记录加个序号；
  -- 3.取前三
     select rownum,s1.sage from (select distinct(s.sage) from t_stu s order by s.sage desc) s1 where rownum<=3
  -- 4.表达式 in，any，all
     -- in（在范围内出现）
     select * from tb_stu s where s.age in (20,11,32); -- s.age=20 or s.age=11 or s.age=32;
     -- any（与范围内任意做比较）
     select * from tb_stu s where s.age > any(30,22,23); -- s.age>30 or s.age>22 or s.age>23;
     -- all（与范围内所有做比较）
     select * from tb_stu s where s.age > all(20,22,23); -- s.age>20 and s.age>22 and s.age>23;
  -- 5.存在（exists()子查询）
     select * from t_cla c where exists(select * from t_stu s where s.age=20 and s.cid=c.cid); 


20. DML语句：数据操作语句
  -- 1. 添加数据 insert
     insert into t_stu(sid,sname) values('1','zs'); 
     commit; -- 提交事务；
     rollback; -- 取消事务；
  -- 2. 修改数据 update
     update t_stu s set s.age=22 where s.sid=3;
  -- 3. 删除数据 delete
     delete from t_stu s where s.age>20;


21. 表的关联删除，添加
   /**
    *  删除：
    *  当两张表有关联关系，即主表与子表的关系（在子表中创建外键约束）；
    *  当删除主表中的记录时，如果子表中含有参照或引用的记录，出现违反外键约束；
    *  方法：1.先删除子表中记录；2.再删除主表中的记录；
    *  
    *  添加：1。先添加主表中的记录；2.再添加子表中的记录；
    */


22. 视图（用于复杂的查询）


  -- 1. 创建和修改视图
  create or replace [force] view v_stu(sid,sname,sex,sage)  -- force无论基表是否存在，强制创建视图(不用)；
  as 
    select * from t_stu
  [with read only];  -- 设置视图为只读；


  /* create or replace view v_stu(sid,sname,sex,sage) -- 别名要与所查询的数据字段相匹配；*/


  -- 2. 查询视图的数据
  select s.sno from v_stu s;


  -- 3. 删除视图
  drop view v_stu;


  -- 4.可通过修改视图表达来修改基表
  update v_stu set sage=22 where sid='s003';


23. 序列


  -- 1. 定义一个可以自动生成的序列：crate sequence；
     create sequence seq_stuid
[increment by n]      -- 每次增长的值
[start with n]       -- 起始值
[{maxvalue n | nomaxvalue}]  -- 最大值
[{minvalue n | nominvalue}]  -- 最小值
[{cycle | nocycle}]      -- 是否循环
[{cache n | cache n}]      -- 
;
  -- 2.删除序列
     drop sequence seq_stuid;


  -- 3.使用序列
     -- 3.1 获取（查看）序列下一个值
  select seq_stuid.nextval from dual;
     -- 3.2 获取（查看）序列当前值
  select seq_stuid.currval from dual;
     -- 3.3 将序列所产生的值作为表中的主键值
insert into t_students(stu_id,sno,sname) values(seq_stuid.nextval,'J1801','zs');
commit;


24. 索引（加快查询速度）


  -- 创建索引
    create index 索引名 on 表名(字段1, 字段2, ...);  -- 在哪个字段上创建索引(主键和唯一约束默认已索引)；
  -- 删除索引
    drop index 索引名;


--------------------------------------------------------------------------------------------------


25. PL/SQL


----1. PL/SQL 块结构


  declare
-- 变量声明（包括orcale标准变量，自定义变量，异常等）； 
cou_s number(38);
x number(38) := 0; -- 定义初值
  begin
-- 块代码（包括变量赋值，循环结构，条件结构等）； 
select count(*) into cou_s from tb_s;
dbms_output.out_line('学生总数为：'||cou_s);


-- 给变量x赋值
x:=20;
  exception
-- 异常处理部分（包括oracle预定义的异常，自定义的异常）；
  end;
  /   -- 结束符；


-----------------------------------------
SQL> set serveroutput on;  -- 打开输出服务；
SQL> clear; -- 清楚当前页的代码
----------------------------------------
----2. 条件和序列控制


  -- if --
  if--then--end if;
  if--then--else--end if;
  if--then--elsif--then--else--end if;


  -- case --
  case 表达式
    when 结果1
    then 陈述1
       ...
    when 结果n
    then 陈述n
  else
    。。。
  end case;


 -- loop循环 格式：--
  declare 
  begin
    loop
     ...
     if 结束循环条件
then exit;
   end loop;
  end;/  


 -- for...loop循环 格式：--
  for i in [reverse] 1...100  -- reverse倒序；
    loop
      if mod(i,2)=0 -- 步长为2；
        then 
  ...
      end if;  
    end loop;


 -- while...loop循环 格式：--
  while (i<=100)  -- 控制结束条件
  loop
    语句块； 
  end loop;


---- 3. 自定义函数 格式：----
  create [or replace] function 函数名
  [{argment[{in|out|in out}]} 参数1类型,
   {argment[{in|out|in out}]} 参数2类型...]
  return 返回类型 -- 不定义参数和返回值的精度；
  {is|as} 
<类型，变量说明>
  begin
     函数体；
  exception
    其他；
  return 要返回的变量;
  end; 


27. 存储过程
-----------------------------------------------------------
/* 1.in传入，out输出；参数不带精度 */
create or replace procedure pro_t5_1(param1 in varchar,param2 in varchar) 
as
  /* 2.变量定义，带精度 */
  sno1 varchar(20);
  sno2 varchar(20);
begin
  /* 3.用count(*) 判断是否存在记录 */
  select count(*) into c1 from t_student s where s.sno = param1; 
  if sno1 > 0
    then ;  /* 4.用select...into...给变量赋值 */
  else
    raise e1; /* 5.抛出异常；raise+异常名 */ 
exception /* 异常 */
  when others then 
    rollback;

end pro_t5_1;