oracle（2）oracle的基础入门

1.sys与system

system系统管理用户，sys超级用户，sys账号比system账户的权限更多，在oracle11以后没有了scott用户

 

2.表的导入导出

imp 用户名/密码@服务名 file=数据库表.dmp存放的地址   ignore=y  full=y //导入

exp 用户名/密码@服务名 tables=表名 file=存入本地dmp目标地址 log=存入本地log目标地址 owner=要导出的用户名//导出

PL/SQL Developer工具导出： 菜单栏---->Tools---->Export Tables//导出

PL/SQL Developer工具导出： 菜单栏---->Tools---->import Tables //导入

 

3.oracle对大小写以及大于等于

sql 语言大小写不敏感（例如关键字，字段名，表名（mysql这个默认是敏感的）），sql 可以写在一行或者多行，关键字不能被缩写也不能分行，sql只有<=没有>=,只有>，但是对例如字段内容敏感（mysql默认是不敏感的）

 

4.null代表的含义（与mysql一样）

空值是无效的，空值不是空格或者0，任何一个值与空运算结果都是null

 

 5.‘’与""

字符，日期只能是单引号，别名对于特殊（字段名，关键字等）需要使用双引号。

 

6.||

||符类似concat或者java中的+

 

7.escape

escape转义字符，类似java与mysql中的\；

 

8.时间函数

next_day（date，week）  //指定日期的下一个星期对应的日期，日期默认格式是dd-mm -yyyy

months_between（date1，date2） // 两个日期相差的月数

last_day（date）  //本月的最后一天

add_months（date，month）//向指定日期中加上若干月数

to_char(date, 'format_model')  //转换时间： yyyy年， mm月，dd日，day星期 ，hh小时，mi分钟 ，ss 秒；转换成数字：9数字//存在则取代，不存在则没有 ，0零//不存在用0取代，$美元符，L本地货币符号，.小数点，,千位符

trunc (sysdate ,'year') //日期截断，

to_date (char, 'format_model')//格式需要满足一致yyyy年， mm月，dd日，day星期 ，hh小时，mi分钟 ，ss 秒

select  sysdate  from  dual;//sysdate 当前系统时间，dual伪表

备注：日期上加上或减去一个数字结果仍为日期，两个日期相减返回日期之间相差的天数。两个日期之间不能做加法。

 

9.其他一些函数

lpad(数值,10,字符)  //不足10位左边补字符，10自定义

rpad(数值, 10, 字符) //不足10位右边补字符，10自定义

trim('h' from'helloworld') //去除头和尾的字符h

replace（‘abcd’,’b’,’m’) //将所有的b替换成m

trunc(45.926, 2) //保留两位小数，取整

round(45.926, 2) //保留两位小数，四舍五入

to_number(char, 'format_model')//格式需要满足一致，$,L可做加法运算

nvl (expr1, expr2)  //如果不为空那么是其本身expr1，为空那么是expr2，注意oracle中需要expr1和expr2的字段类型一致，否则需要转型，类似mysql的ifnull

nvl2 (expr1, expr2, expr3)//满足条件expr1则为expr2，否则为expr3，类似mysql的if

nullif (expr1, expr2)// expr1与expr2相等返回null，不等返回expr1

coalesce (expr1, expr2, ..., exprn) //coalesce表示expr1,为buul则expr12依次下去

 

10.条件循环

条件表达式同mysql；

case when  then  同mysql；

while 条件 loop  循环体 end；//循环

 

11.连表查询基本同mysql，cross join  交叉连接同where  e，t；using 子句指定等值连接中需要用到的列（相当于where e.deptno=d.deptno）；

 

12.创建表和修改表

1）表名和列名:命名规则

必须以字母开头

必须在 1–30 个字符之间

必须只能包含 a–z, a–z, 0–9, _, $, 和 #

必须不能和用户定义的其他对象重名

必须不能是oracle 的保留字

数据类型number为取代mysql的int，double等

varchar2类似于mysql的varchar，也有char

blob 二进制最大4g

clob 字符数据最大4g

long 可变长字符数据，最大2g

date  日期

 

2）重命名数据表的列

modify，add同mysql

alter  table 表名 drop column 列名

alter  table  表名 rename  column 旧列名 to 新列名

truncate table 表名 //清空数据，不能回滚，delete能回滚

rename 表名 to 新表名;

 

13.事务

commit开始 或 rollback回滚语句，回滚点为最近的一次commit

savepoint  回滚点名  ，rollback  to  回滚点，可以回到相应的回滚位置

备注：

ddl语句（自动提交），不会回滚，回滚是对于dml语句而言，添加事务之后dml所涉及的行被锁定，类似mysql的innodb，在没有commit之前其他用户不能见到自己的修改，自己能见到是读己之所写，类似mysql，oracle默认不会自动提交dml语句，需要使用使用commit（mysql默认自动提交）

select * from 表名  for update；//查询更新后的表，假如这个查询结果中有被锁，那么会被处于阻塞状态

 

14.视图

视图的创建同mysql，修改没有的列会产生错误与mysql不同（mysql无效，但不会报错）

组函数,group by子句,distinct 关键字,rownum 伪列,列的定义为表达式不能使用update与insert。添加with read only 表示该视图只读。

 

15.序列

类似auto_increment，但又不同，auto_increment必须位于索引之上，且每张表唯一，作用于联合索引之上排序后相同则自增左1字段，序列， 唯一的数值，可以充当主键值，共享对象，装入内存可以提高访问效率，首次默认不存在，需要使用nextval之后才能使用currval，不可以修改序列值，需要删除后重建，可以修改序列的增量, 最大值, 最小值, 循环选项, 或是否装入内存，序列的裂缝可能出现在多个表使用同一个序列，回滚（回滚对序列无效），系统崩溃。

语法：

create sequence sequence；

       [iincrement by  n]  --每次增长的数值

       [start with n]    --从哪个值开始

       [{maxvalue n | nomaxvalue e}]

       [{minvalue  n | nominvalu e }]

       [{cycle| nocycle}]     --是否需要循环

       [{cache  | nocache}];  --是否缓存登录

 

16.索引

删除一个表时,所有基于该表的索引会自动被删除，可以通过指针加速 Oracle 服务器的查询速度，减少磁盘 I/O，索引的创建删除同mysql

 

17.同义词

create  synonym  别名 for 表名;//创建，类似给表起别名

drop synonym  别名；//删除同义词

 

18.权限管理

1）创建，修改用户以及更改密码

create user 用户名 identified by   密码;//添加用户

alter  user 用户名 identified by   新密码；//修改密码

grant 权限    to 用户名;//赋予权限

一般包括如下权限

create session（创建会话）

create table（创建表）

create sequence（创建序列）

create view（创建视图）

create procedure（创建存储过程）

备注：需要创建表的权限，除了需要创建表权限，还需要使用alter user 用户名 quota unlimited   on users

 

2）角色的创建，修改

create role 角色名；//创建角色

grant 权限    to 角色名;//赋予权限

grant 角色 to 用户名...;//赋予角色给用户  

grant      对象权限 on 对象 to 用户/角色；//创建者具有所有权限，system也有权限，拥有者给其他人分配权限，角色用户为public表示对所有人具有权限

grant      对象权限 on 对象 to 用户/角色with grant option；//使用户同样具有分配权限的权利

 

3）取消权限

revoke 权限 on 对象 from  用户/角色；//with grant option的权限同样被收回

revoke 权限 on 对象 from  用户/角色cascade constraints;//去除级联权限，a赋予b，b赋予c，a使用cascade constraints去除b的权限，那么b赋予geic的权限同样会清除

 

备注：权限分配情况可查询的表，可以在删除相应的用户与角色

role_sys_privs  角色拥有的系统权限

role_tab_privs 角色拥有的对象权限

user_role_privs      用户拥有的角色

user_tab_privs_made   用户分配的关于表对象权限

user_tab_privs_recd     用户拥有的关于表对象权限

user_col_privs_made    用户分配的关于列的对象权限

user_col_privs_recd      用户拥有的关于列的对象权限

user_sys_privs 用户拥有的系统权限

 

19.set操作符

union/union all //union可以去重，union all不会去重，intersect  //取交集，minus //减，用法都同union

 

20.pl/sql语法

1）一般用法

declare

声明变量 //a  int（10）或者a  表名.字段名%type；表名.字段名%type表示与表名的字段名的类型一致，表字段的类型改变，该处也随之改变

type 变量类 is  record（//类似java中的类与属性

b 表名.字段名%type；

）

变量类实例 变量类

cursor 游标名称 is select..//定义游标，将select单行的查询结果放入游标中，

begin

select  ... into 变量类名实例  from ... //select后的字段名顺序与数量需要与变量类名保持一致，后续使用可以使用类名.变量读取

fetch 游标名称 into 变量；//提取游标值，游标名%found类似于iterate的hasnext，可以用循环提取每次游标指向的值

close 游标名//关闭游标

exception  //没有书写异常，那么这里不写，when  异常的类型 then 输出打印；

end

 

2）函数

Create  or  replace function 函数名（参数）return 返回值类型

is  声明变量类似declare；

begin；

return；

exception

end 函数名；

 

3）存储过程//在函数的基础上去除return

create or replace procedure 存储过程名（参数） 

as  //声明   

begin // 执行 

exception//存储过程异常 

end;

备注：参数类型有3中 in 可以是值，out必须是变量，inout 必须是变量 //create   procedure 过程名 （in  形参名称 数据类型 ，out   形参名称 数据类型，inout  形参名称 数据类型），蒸锅过程是滞后的，只有在内部完成之后，赋值才给传入给外部的全局变量。

 

4）触发器

Create or  replace trigger 触发器..//后面的同mysql

备注：要使用输出需要开启  set  serveroutput on；

 

21.分页

oracle没有limit关键字，但是又rownum，rowid伪列关键字

rownum：总是从1开始，找到一个数后加1，所以rownum>10永远找不到数，rownum遇到不满足的条件就会停止。

SELECT * FROM  ( 

    SELECT A.*, ROWNUM RN 
    
    FROM (SELECT * FROM TABLE_NAME) A 

    WHERE ROWNUM <= 100

) WHERE RN >= 51

例如：

select *  from  emps  where rownum<10;//相当于select *  from  emps  where rownum!=10,相当于select *  from  emps  where rownum!=10 and rownum!=15 ,相当于select *  from  emps  where rownum  between 0 and 10,相当于select *  from  emps  where rownum  between 1 and 10

rowid代表了一行数据的存储地址

例如：

delete from stu where rowid not in (select max(rowid) from stu t group by t.no, t.name, t.sex );//根据no，name，sex分组，然后得出每组最大的rowid，不在里面的删除

 待续。。。