Oracle 复习A

关于这些东西也不记得看了多少遍了，不经常用就会忘掉，还是写下来备忘吧。

1.字符函数：比如大写，小写，首字母大写等

大写：upper（字段名）

小写：lower（字段名）

首字母大写：initcap（字段名）

2.数字函数

 四舍五入：round（操作数，小数位）

取模：mod（操作数1，操作数2）

截取：trunc （操作数，格式或位数）

trunc（123.345,2）
trunc(sysdate,'yyyymmdd')

trunc截取数字，只截取，不四舍五入，截取日志需要指定格式，yyyy为本年第一天，mm为本月第一天，dd为当前日期，d为本星期第一天，hh当前时间，精确到小时，mi当前时间，精确到分钟。

3.nvl函数

  判断是否为空：nvl（table.money，0）代表如果表table中的money为空，则赋值为0，如果不为空，则是本身的值

4.转换函数

  to_date(‘字符串’,'格式yyyymmdd') 将字符串转换为日期，一般用于where 比较日期的条件。

  to_char(字段，' ')，将数字转换为字符串。

5.分组函数

  平均 avg 

  最大 max

  最小 min 

  求和 sum

  计数 count

 分组 group by

 分组过滤 一般和group by一起使用，having 子句

select a.deptno,max(a.sal) from emp a group by a.deptno having max(a.sal) > 3000

6. 多表连接

   内连接：等值连接

   外连接：左连接--返回左表的数据，右连接-返回右表的数据

  例子： 查询大于每个部门平均工资的员工

select * from emp b where b.sal > any
(
select avg(a.sal) from emp a group by a.deptno
)

 

 注意子查询  any 比子查询任何一个都怎么样的意思

 > all  大于最大值

< all  小于最小值

!=all   ---not in

< any  小于最大值

> any  大于最小值
= any   --in

 

DML语句  

增，删，改

首先，对于insert 语句。一般遇到的错误：a 对于有not null 约束的列 记住不要插入空值。 b 有唯一性约束，check约束，要按照约束的规则插入 c 数据类型要匹配  d  值不要超过列的范围。可以通过desc  表名  来查看表的结构，列的属性等

oralce的值类型 number，varchar2 ，date类型

比如： insert into dept values(333,'smith','xxxxx') ;  执行完后一定要提交，这是oracle的机制，和其他数据库不同

创建一张表的快捷方法：空数据的

create table test as select * from emp where 1=2;

其次： update语句

 update table  t set t.deptno = x where t.name=xxxx;

多列更新  update  emp t set (t.job,t.deptno) = (select a.job,a.deptno from emp a where a.empno=xxxx;)

第三： merge语句

 merge into   表1  a  

  using 表2  b

on （a.xx=b.xx）

when  matched then

update set a.xxxx= b.xxxx,a.xxx=b.xxx

when not matched then

insert (xxxx,xxx) values (b.xxxx,b.xxx,b.xx)

当满足的时候更新字段，不匹配不满足的时候插入新数据

数据控制语句  DCL

1.用户登录

  密码验证    sqlplus  scott/triggers  

  OS认证方式：  sqlplus  / as sysdba

   创建用户：create user  damon identified by 密码;

   给用户解锁：alter user damon  identified by 密码  account unlock;  解锁后就可以用密码登录了

   查询  select * from dba_users； 

2.系统权限

    系统权限包括：连接权限 connect  create table  创建表的权限。  create  sequence  创建序列号的权限，create view  创建视图的权限。create procedure 创建存储过程的权限。

   可以通过PL Developer 查看user的树状结构来了解权限有哪些

   那么，新建的用户如何赋予权限呢？

   新建的用户必须赋予权限，否则无法登录连接到数据库，常用的方法是给用户常用的角色，角色是某些权限的结合。

   create role   名称  ：创建角色

   grant  create session to  角色名   ；将创建会话的角色赋予给了某个角色。

   

SQL> create role test; #创建角色

Role created


SQL> grant create session to test; #将权限赋予给角色

Grant succeeded


SQL> grant test to damon  #将角色赋予给用户

  常用的角色：默认角色（保证使用数据库最基本的一些功能）connect，resource，只需要将这2个角色赋予给用户，用户就可以操作数据库了

  

SQL> grant connect,resource to damon;

Grant succeeded

  给用户赋予一些对象权限： grant select on scott.emp to 用户;  #给用户赋予scott用户的emp表的查询权限。

3.密码有效期：

 

正常情况下，用户的profile属性是default ，密码有效期是180天

select * from dba_profiles where resource_name='PASSWORD_LIFE_TIME' and profile='DEFAULT';

将密码有效期设置为无限制

alter profile default limit password_life time unlimited;

DDL 数据定义语句

 表定义的时候不能使用oracle的关键字，尤其是v$reserved_words中 reserved=Y的关键字

必须以字母开头，特殊字符只能用_,$,# 三种

用户要建表，必须要有建表的权限，有存储空间即表空间。创建用户前创建表空间，创建用户时指定表空间tablespace 即可

创建表

create table test
(
deptno number(2) #2位整数,
deptname varchar2(40),
local varchar2(100) default '北京', #default 默认值
) TABLESPACE USERS #指定表空间

数据类型

 1.字符 char 固定长度 1-2000,  varchar2（n） 可变长度，  1<n<4000

 2.数字 number（p,s） p是精度，总有效位数，s是刻度，小数点后位数。这个类型包括零，正数，负数

 3.日期 date和时间值(timestamp)，包括，世纪，年，月，日，小时，分，秒 timestamp(n),时间戳，表示日期和时间，比date更精准，n表示秒向下划分的精度范围，n取值0-9，默认6

select sysdate from dual;

select systimestamp from dual;

4.BLOB CLOB 大对象。

  给表增加一列

alter table  test  add （telephone，number（11））;

  修改列的长度

  

alter table test modify (telephone number(13));

  删除一列

   

alter table test drop column telephone  #生产环境不可用

alter table test SET UNUSED(LOC) #设置列不可用

alter table test drop unused columns;  #删除不可用的列

#顺序，先设置列不可用，然后在删除不可用的列

列重命名

 alter table dept01 rename  源列名 to 新列名

将表改为只读

 alter table dept01 read only 

alter table dept01 read write;

约束：

非空（not null），唯一unique，主键，primary key，外键 foreign key，检查 check

非空

alter table 表名 modify 列名 not null;

视图：限制数据的访问，简化查询，数据的独立性，避免重复访问相同的数据

 1.类别： 静态视图 DBA_   ALL_, USER_ 开头的，动态视图 V$,存在于内存中，用户创建的视图

 2.案例

 2.1 创建简单视图

create or replace view 名字 as
select empno,ename,job from emp

 创建完成后，经常访问的数据就可以直接去查询视图

 

select * from 视图名

就相当于直接查表了，不同的是，视图中定义的特定的字段。

2.2 查看视图sql文本

select * from user_views d where d.view_name='视图名'  #注意视图名大写

查看TEXT就行了

select * from dba_users;
select * from  dba_tables;
select * from dba_tab_cols;

静态视图：DBA_ 数据库有用的所有对象，ALL_用户拥有及可以操作的其他用户的权限，USER_，用户拥有的对象。

动态视图V$：加载在内存中的，比如会话等。

创建外键时基表字段必须是主键

#创建主键
alter table 表 add primary key (列名)

#创建外键
alter table 表名 add constraint 外键名 foreign key (子表列名) references 基表表名(基表字段)

   索引：

  目的是加快oracle查询的速度，数据自动维护

  a  唯一索引   unique index 

# xxx是表名，abc是列名
create unique index  index_abc  xxx(abc);

  b  非唯一索引   index

  c  函数索引  index

  删除索引

drop  index 索引名

序列 sequence 

  可共享，不同的用户可共同调用，如果是number，可以顺序产生。

  创建序列

#创建序列,nocache不缓存，nocycle不循环
 create sequence 序列名  increment by 1 start with 100 maxvalue 9999 nocache  nocycle

查询序列，可以在数据字典user_sequence

#注意一定要大写
select * from user_sequences a where a.sequence_name='TEST_SEQ'

SQL> select * from user_sequences a where a.sequence_name = 'TEST_SEQ';

SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY CYCLE_FLAG ORDER_FLAG CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ ---------- ---------- ---------- -----------
TEST_SEQ 1 99 1 N N 0 88

SQL>

同义词

oracle当中，表名比较长，或者某个用户表经常被访问，简化对象的反问，可以创建同义词

类型：1  public 共有同义词  只要有权限的用户都可以访问 2  私有同义词 只能自己访问

grant create public synonym to 用户;

create public synonym  同义词名 to 表名

  需要赋予给用户权限才可以访问同义词，如果用户不能访问表，那么访问同义词也就无从谈起了

TCL 事务控制

1.属性  (ACID)

  原子性，事务必须都完成或者不完成。

  一致性，查询的结果必须与开始查询的状态一致

  隔离性，做出变更的会话，其他会话看不到未提交的记录

  持久性，事务一旦完成，所有的用户都可立即看到。