------------------------------------------------------------ 常用函数
1字符函数 1.1转换函数 lower 大写转小写 upper 小写转大写 initcap 首字母转为大写 1.2 字符操作函数 concat 连接字符串 substr 取子串 length 取长度 instr 取得位置值 lpad 将一个字符串按照某中模式来显示 lpad(sal,10,'*') *****50000 2.数值函数
round 返回四舍五入 round(45.926,2) = 45.93 trunc 截取小数 trunc945.926,2) = 45.92 mod 求余
3.日期函数 sysdate 返回日期和时间 DUAL是用来显示日期的名义表
months_between 返回两个日期的月份的差值 add_months 在日期上加上月份数 next_day 下一天的日期 last_day 月份的最后一天 round 四舍五入日期 trunc 截断日期
4.转换函数
隐式转换 from to VARCHAR2 OR CHAR NUMBER VARCHAR2 OR CHAR DATE NUMBER VARCHAR2 DATE VARCHAR2 显示转换 form to 函数 number VARCHAR2 to_char date varchar2 to_char varchar2 number to_number varchar2 date to_date
NVL函数 将NULL值转换成一个实际的值 eg. nvl(comm,0) 当comm的值为NULL时转换成0 DECODE函数 有点类似 switch case eg. decode(job, 'analyst',sal*1.1, 'clerk',sal*1.15, sal) ---------------------------------------------------------------------------------- 数据库锁和表分区手工锁定
1.共享锁 -锁定表 -仅允许其他用户执行查询操作 -不能插入,更新和删除 -多个用户可以同时在同一表中放置此锁Lock table 表名 in share mode ;
2.共享更新锁 -锁定要被更新的行 -允许其他用户同时做其他的所有操作lock table 表名 in share update mode;
3.排他锁 lock table 表名 in exclusive mode;-----------------------------------------------------------------------
oracle数据库存储数据的方式
规则表,分区表,索引组织表,簇.
表分区优点: -增强可用性. 单个分区出现故障,不影响其他分区 -均衡的I/O.不同的分区可以映射到不同的磁盘 -改善性能. 1.范围分区 -这种分区方法是根据表中列的值的范围对表进行分区 -分区时,首先依据列中的值可能的范围进行划分 create table Student ( StudentId integer not null, Studentname varchar2(20) Score integer ) Partition by range(Score) ( Partition p1 values less than(60), Partition p2 values less than(75), Partition p2 values less than(85), Partition p2 values less than(MaxValue) ); 2.散列分区法-提供了一种通过指定分区编号来均匀的分布数据的方法.
-它是通过Hash函数将数据映射到相应的分区 -它可以使得数据均匀的分布到各个分区中. Create table department ( Deptno int, Deptname varchar2(14) ) Partition by hash(deptno) ( Partition p1, Partition p2 ); 3.复合分区 它是先对数据进行范围分区,然后在每个子分区又进行散列分区. Create table salgrade ( grate number,losal number, hisal number) Partition by range(grade) Subpartition by hash(losal,hisal) ( Partition p1 values less than (10), (subpartition sp1,subpartition sp2), Partition p2 values less than(20) (subpartition sp3,subpartition sp4))
4.列表分区 -列表分区允许用户明确的控制行到分区的映射 -eg. Create table customer ( custNo int, custname varchar(20), custState varchar(20) ) Partition by list(custState) ( Partition asia values('中国','韩国','新加坡'); Partition Europe values('英国','法国','德国'); ) 表分区的使用 插入和没有分区是一样的. select * from student partition(p1);---查询单个分区的数据 维护用ALTER语句表分区的相关数据字典
USER_TAB_PARTITIONS USER_IND_PARTITIONS --------------------------------------------------------------------oracle中的数据库对象
1.同义词 = 数据库的替换名称 优点: -简化SQL语句 -隐藏对象的名称和所有者 -为分布式数据库的远程对象提供透明性 -提供对对象的公共访问 同义词类型: -私有 -公有 *CREATE SYNONYM name for oldname 创建同义词 数据字典 User_Synonym 2.序列 它是能够自动产生连续唯一值的数据库共享对象 用于为主键提供值Create sequence sequencename
[increment by n] [start with n] [{maxvalue n| nomaxvalue}] [{minvalue n| nominvalue}] [{cycle | nocycle}] [{cache n | nocache}]; 两个重要的属性 * nextval 返回下一个可用的值 * currval 获取当前的序列中值 对于一新定义的序列必须先用nextval产生值,再可以用CURRVAL返回当前值 Alter Sequence 修改序列 Drop Sequence 删除序列 User_Sequences 序列的数据字典 3.视图 creat view or replace问题: 查询出前几条记录,在sqlserver中有 top关键字.
oracle 中的表都有两个隐藏字段:rowid ,rownum4.索引
*与表关联的可选结构 *加快SQL语句执行 *减少磁盘I/O *CREATE INDEX 语句用于创建索引 *在逻辑上和物理上独立于表中的数据 *Oracle 自动维护索引 4.1 唯一索引 -确保在定义索引的列中没有重复的值 -Oracle自动为主键和唯一键列创建唯一索引 -CREATE UNIQUE INDEX 语句用于创建唯一索引 4.2 组合索引=连接索引 -组合索引中的列可以按任意 -CREATE INDEX 4.3 反向键索引 -反转索引列中的每一个字节 -将数据插入操作分布在整个索引上 -在创建索引时使用REVERSE关键字 实用场景:字段的数据的前几位比较固定,为了加快通过该字段检索.可以在该字段中创建反向键索引. 4.4 位图索引 *为低基数列(重复数据较多的列) *BITMAP INDEX 创建 优点 -减少响应时间 -降低空间占用 4.5索引组织表 *按照索引的顺序来组织和存储数据的表 *organization index; eg. create table indorg ( vencode int primary key ) organization index; 普通表 VS. 索引组织表 rowid唯一标识行 主键唯一的标识行 隐式的ROWID列 没有ROWID列 基于ROWID访问 基于主键访问 顺序扫描 完全索引扫描 可以存储在簇中 不能存储在簇中 支持分发,复制,分区 不支持 4.6基于函数的索引 -基于一个或多个列上的函数或表达式创建的索引 -表达式中不能包含聚集函数(SUM,COUNT,AVG,MIN,MAX) -不能在LOB,REF 或嵌套表列上创建 eg. create index idx_ename on emp(Lower(ename));4.7 键压缩索引
-将索引键拆分为前缀项和后缀项 -在一个索引块中,通过后缀项共享前缀项即可达到压缩的目的 -节省磁盘空间 -在一个索引块中可以存储更多的键 -COMPRESS子句用于创建索引 我的理解是一种组合索引的优化.使得索引占用的空间更小eg. create index idx_emp on emp(job,ename) compress 1;
4.8 分区索引
分区索引的类型
本地索引 本地前缀索引 本地无前缀索引 全局索引 全局前缀索引 全局无前缀索引5 簇
簇: *在公共列的两个或多个表的集合 *簇表中的数据存储在公共数据块中 簇键 *簇中行的唯一标识符 *用于获取行 创建簇: *create cluster 语句创建簇 *首先创建簇,然后创建组成簇的表 优点: * 缺点: *插入效率降低 eg. create cluster class_cluster ( classno varchar2(10) ) tablespace users; //为簇创建索引 create index idxclasscluster on cluster class_cluster; create table stuclass(classno varchar2(10),classname varchar(20)) cluster class_cluster(classno); create table stu(classno varchar2(10),stuname varchar(20),stuid integer) cluster class_cluster(classno); 常用数据字典: USER_TABLES 用户所有表信息 USER_VIEWS USER_CONSTRAINTS USER_DEPENDENCIES USER_TAB_COLUMNS USER_TRIGGERS USER_TYPES USER_SYNONYMS USER_SEQUENCES USER_OBJECTS USER_LOBS USER_SOURCES USER_ERRORS------------------------------------------ 常用命令( sql plus) desc 列出表结构 save 保存到某个文件 get 从文件中读取到缓存区 connect @ / run ed 查看缓存区 exit
------------------------------------------------------------