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)
原子性，事务必须都完成或者不完成。
一致性，查询的结果必须与开始查询的状态一致
隔离性，做出变更的会话，其他会话看不到未提交的记录
持久性，事务一旦完成，所有的用户都可立即看到。
