ORACLE学习笔记

ORACLE 结构化查询语言（Structured Query Language） 数据定义语言 ddl 表table是数据库中存储数据的基本单位 column (data type width constraint) create table alter table drop table 数据操作语言 DML DML table data row insert update delete 事务控制语言 Transaction Control Language TCL transaction commit rollback 数据查询语言 DQL select 数据控制语言 DCL grant授予权限 revoke回收权限 DBA database adminstrator 数据库管理员 desc s_emp 'desc' means 'describe' desc s_dept desj s_region 给列起别名 能够改变一个列，表达式的标识 适合计算字段 在原名和别名之间可以使用as关键字 别名中包含空格，特殊字符或希望大小写敏感用双引号将起括起来 空值处理 nvl（p1,p2）--> if p1 is null then return p2; else return p1; end if 两条之间拼接用 ｜｜ 加空格用‘（空格） ’ distinct 去重 投影操作 select distinct title from s_emp; distinct应用到select后面所有列，所有列联合起来唯一 如果select后面跟distinct关键字，一定是直接跟在select后面，前面没有任何内容。 Oracle home: oracle产品的安装目录 ORACLE_SID: 数据库所对应的实例（instance）名字（一个实例唯一的对应一个数据库，实例确定了，数据库就确定了） telnet 192.168.0.26 sqlplus username/password Where子句的功能 Where跟在from子句之后 Where子句后跟条件表达式 select last_name,dept_id,salary from s_emp where dept_id=42; select from where where后面不能用列别名 where后面尽量不用算术表达式 表达式里字符串用单引号表示 表达式与 或 and or between and表示一个范围，是闭区间。含义大于等于并且小于等于 in（ｐ１,ｐ２,...）表示一个集合，是离散值。含义为等于集合中任意一个 在字符串比较中，可用like和通配服进行查找 ‘s’　'ｓ%'s打头的字 　'ｓ_'s打头的两个字母的字 查找 _　%　时 在where句末用escape定义一个转义字符 然后加在 _ %前面 is null 判断是否为空 数据库中的表分为系统表：储存数据库的信息 用户表：存储用户信息 逻辑比较运算符 <> != ^= SQL比较运算符 not between and not in <==> <>all not like is not null !=31 and !=41 and !=43 用order by子句对查询出来的结果集进行排序 ASC——升序 DESC——降序 ORDER By是SELECT语句中最后一个子句 ORDER By后面可以用别名 可以写 2 desc（表示select第2列） 索引不能有空值 先执行from 然后where然后select最后order by 处理一条SELECT语句 分析语句 搜索是否有相同语句 检查语法，表名。权限 在分析过程中给对象加锁 生成执行 计划 项目进度计划表范例计划下载计划下载计划下载课程教学计划下载 绑定变量—给变量赋值 执行语句 获取数据—将数据返回给用户进程 单行函数 字符函数（character） LOWER 小写函数 UPPER 大写函数 INITCAP 首字符大写函数 CONCAT 拼接函数('good','string') SUBSTR 求字串函数 substr('string',1,3) /*第三位数字表示取字串 的位数*/ （第二位表示字符串下标 正数表示左边第N个 负 数表示右边第N个） LENGTH 求长度 LPAD 右对齐函数，位数不够的，左边补指定字符 LPAD（‘STRING’，10，‘*’） RPAD 右对齐函数，位数不够的，左边补指定字符 数值类型（number） 日期类型（date） 转换函数 多行函数（组函数） round 四舍五入函数 round(小数，小数点后保留位数)不写位 数默认保留到个位 负数表示小数点前 trunc 截取函数 用法类似round 非保留位无论多大都省略 TO_NUMBER将字符串转换成数值 TO_CHAr将数值转化为数字字符串 隐式类型转换通常会导致索引无法使用 NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK' EXPORT NLS_LANG NLS_LANG+'AMERICAN_AMERICA.US7ASII' ‘￥99，999’ ‘$00,000’ 格式说明 9代表数字位 0 定义宽度大于 连接操作 cross join 全连 （inner） join on 内连接 匹配问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 自连接 通过给一张表起不同的别名来将一张表的列间关系转化成不同表之间的关系 内连接用 and 外连接用 where 外连接 outer join 匹配不上记录 from ti left join t2 左边的表必须作驱动表 on t1.c1 = t2.c2 and连接前过滤 where连接后过滤 外连接的结果集＝内连接的结果集＋t1表中匹配不上的记录和t2表中的一条NULL记录 from ti right join t2 on t1.c1 = t2.c2 外连接的结果集＝内连接的结果集＋t2表中匹配不上的记录和t1表中的一条NULL记录 组函数操作在一组行记录上 AVG（DISTINCT｜ALL｜n） 平均值 SUM （DISTINCT｜ALL｜n） 求和 COUNT(DISTINCT|ALL|EXPR|*) 计数 MAX(DISTINCT|ALL|expr) 最大值 MIN(DISTINCT|ALL|expr) 最小值 主函数不处理空值 当所有值都为空 返回0 count（*） 表示表中记录数 GROUP By指定的表达式，叫要处理的数据（若有WHERE子句即为通过条件过滤后的数据）分为若干组，每组有唯一的组标识，组内有若干条记录，根据SELECT后面的组函数对每组的记录进行计算，每组对应一个返回值 group by XX all/dinstinct select 后面若有组函数 则其他都必为组函数 分组后标识含义改变 若有GROUP By子句，SELECT后面可跟GROUP By后面跟的表达式以及组函数，其他会报错 GROUP BY 子句中包含多列，分组的粒度更细 group by dept_id,title; HAVING子句 行被分组 将HAVING子句的条件应用到每个分组上 只有符合HAVING条件的组被保留，在应用SELECT后面的组函数对每组的数据进行处理 HAVING后不能接组函数 也不能接列别名 where过滤的是行（记录） HAVING子句过滤的是分组（组标识，每组数据的聚合结果） where子句包含单行函数 having子句只能包含group by后面的表达式和组函数 where子句执行在前，having子句执行在后 where子句和having子句都不允许用列别名 子查询： 1，非关联子查询 select from where expr eperator (select from) 先执行子查询，子查询的返回结果作为主查询的条件，再执行主查 询。 子查询之执行一遍 若子查询的返回结果为多个值，ORACLE会去掉重复值之后，再将 结果返回给主查询 ＝是单行运算符 集合时应该用in where（X，Y）多列子查询 关联子查询采用的是循环（loop）的方式，执行步骤如下： 1 外部查询得到一条记录 exists 存在 采用的是循环（loop）方式 select t1 from outer where exists （select x from t2 where s1.x1 = s2.x2） exist是用循环方式（loop），由outer表的记录数决定 标量子查询 内连接 select t1.c1,t2.c2 from t1,t2 where t1.c1 = t2.c2 select t1.c1,t2.c2,t3.c3 from t1,t2,t3 where t1.c1 =t2.c2 and t2.c4 = t3.c3 外连接 select t1.c1,t2.c2 from t1,t2 where t1.c1 = t2.c2(+) where t2.c2(+) = t1.c1 加号侧是匹配表 and t2.c1 is null （+）连接前过滤 E-R图 基于数据库的开发 是一个自顶向下的系统开发方法 将业务 E－R图 实体关系图 实体 有共同属性的一类对象的集合 属性 通过属性描述实体，区分实体 关系 主键值 (primary key) PK 联合主键：多列联合唯一，任意一列都可以重复，每一列都不能为NULL child table fk (dept_id)--> parent table(id) pk/uk(id) 先create parent table,pk.uk,再建child table 2 先insert into parent table,再insert into child table 3 先delete child table,再delete parent table 4 先drop child table,再drop parent table 范式； 第一范式 表中不会有重复的记录，即有主属性：每个属性值不再可分 第二范式 每个非主属性必须完全依赖于主属性 第三范式 每个非主属性不能依赖于另一个非主属性 CREATE TABLE 必须有建表的权限 命名规则 首字母必须是字母 名字为1－30个字符长 只能包含A－Z，a－z，0－9，_,$和# 同一个命名空间里的对象不能重名 不能用Oracle的保留字 数据类型： VARCHAR2（size），变长字符类型 建表 CREATE TABLE TEST（ C1 VARCHAR2（）10， C2 CHAR（10））； 表中增加一列： alter table test add c3 number; INSERT INTO table VALUES (value[] insert into test(c1) values ('abc'); varchar2对空格敏感 number(p,s) p是精度，数值中所有数字为的个数，最大精度36 s是刻度范围，s为正数，表示小数点右边的数字位的个数，为负数，表示小数点开始向左进行计算数字位的个数。刻度范围从－84到127 c1 number c2 number(6) c3 number(4,2) 数字可输入4位 小数点后2位 1.234567 1.235 c4 number(3,-3), 数字3位 小数点前三位不计 999000 c5 number(2,4) 0.0099 oracle用7个字节来存储日期和时间信息 世纪，年，月，日，时，分，秒 to_date()如果需要日期值 to_char()如果要处理一个日期值 yyyy mm dd hh24 h12 mi ss d:用数字表达的一周内的第几天（周日：0） day month select start_date from s_emp where to_char(start_date,'fammonth')='month' select start_date from s_emp where rtrim(to_char(start_date,'month'))='month' rtrim 去右边空格 month_between('01-sep-95','11-jan-94') add_months('11_jan-94'.6) next_day('01-sep-95','friday') last_day('01-sep-95') 与该日期在同一月份的最后一天 round(sysdate,'month') 定义主键 若主键列上没建索引，系统会自动创建唯一性索引 create table test( c1 number constraints test_c1_pk primary key, 3 c2 number); 插入 insert into test(c1) values (3); //插入操作格式 定义联合主键： constraints test_c1_c2_pk primary key(c1,c2), 外键约束 create table parnet( c1 number(4) constraints parent_c1_pk parmary key) create table child( 2 c1 number (3) constraints child_c1_pk primary key, 3 c2 number(4) constraints child_c2_fk 4 references parent(c1));(引用父表) references parent(c1)) 列级约束 create table child( 2 c1 number (3) constraints child_c1_pk primary key, 3 c2 number(4) , constraints child_c2_fk foreign key (c2) 4 references parent(c1));(引用父表) 表级约束 delete from where(不写where则删除表中所有记录) 删除时先删子表再删父表 cascade constraints 解除约束 create table child( c1 number constraints child_c1_pk primary key, c2 number constraints child_c2_fk references parent(c1) on delete cascade); 级联删除 create table child( c1 number constraints child_c1_pk primary key, c2 number constraints child_c2_fk references parent(c1) on delete set null)); update child set c2 = 1 where c2 is null update child set c2 = null where c2 = 1 唯一性约束 若唯一键列上没建索引，系统会自动创建唯一性索引 create table test( c1 number constraints test_c1_pk primary key, c2 number constraints test_c2_uk unipue, c3 number constraints test_c3_uk unique); UK允许多个null值 检查约束 1 create table refreee( 2* c1 number constraints test_c1 check (c1>100)) 建表脚本 创建一个脚本文件，内容是create table语句 末尾 commit； 执行脚本 使用 ＠＋脚本名 默认只执行当前目录 运行其他目录下的脚本要注明路径 create table emp_41 as select id,first_name,title,salary,dept_id from s_emp where dept_id = 41; 追加约束 add constraints emp_41_id_pk primary key(id); 数据操作语句 update 更改数据 update s_emp set dept_id = 60 where dept_id = 10 (把部门10里的员工调到部门60号去） default(10) 设置缺省值10 事务的结束 ／从哦秘密他 r ollback 事务的特性：ACID 原子性（atomic）：一个事务要么完全发生，、要不完全不发生 一致性＊（conststen）：事务把数据库从一个一致状态装边到另一个状态 隔离性 事务的隔离级别 数据库开发的关键挑战 排他锁（X锁） 如果一个对象上加了X锁，在这个锁被采用commit commit提交一个事务 rollback 回滚一个事务 truncate table 截取 是DDL语句 ddl语句无法用rollback回滚 表扫描的方式： 全表扫描FTS（Full Table Scan） 高水位线：曾经包含数据的最右边的块 rowid 表示一条记录的物理位置 select id,first_name,rowid from s_emp 包含如下信息： 该记录是属于那张表的（哪个数据库对象）：object_id 该记录在哪个数据文件里 file_id 该记录在数据文件的第几个数据块里：block_id 该记录在数据块里是第几条记录：row_id 索引 为什么要使用索引 Oracle server通过rowid能够快速定位要找的行 通过rowid定位数据能有效降低读取数据块的数量 索引的使用和维护是自动的，一般情况不需要用户干预 索引使用 B*tree结构 B*tree 索引的操作 建立索引： create index test_c2_idx on test(c2); drop index 重建索引 快 用空间换时间 ALTER INDEX s_emp_last_name_idx rebuild; 慢 时间换空间 drop index s_emp_last_name_idx; create index s_emp_last_name_idx on s_emp(last_name); 那些列适合建索引 经常出现在WHERE子句的列 经常用于表连接的列 该列是高基数数列（高基数数据列是指有很多不同的值） 该列包含许多NULL值 表很大，查询的结果集小 主键（PK）列，唯一键（uk）列 外键（FK）列 经常需要排序（ORDER BY）和分组（GROUP by）的列 索引不是万能的 不适合建索引的列 列很少出现在WHERE子句 查询的结果集也大 该列被反复更新 唯一性索引 （unique） 等价于唯一性约束 非唯一性索引 用于提高查询效率 单列索引 索引建在一列上 联合索引 索引建在多列上 create index emp_id_idx on test(c1,c2); 哪些写法会导致索引用不了 函数导致索引用不了 where upper(first_name) = 'CARMEN' 表达式导致索引用不了 where salary*12 = 12000 部分隐式数据类型导致索引用不了 where c1= 2 (C1为varchar2类型) Like和substr where first_name like 'CA%' (可用索引) where substr (first_name ,1 , 2)= 'CA' 否定形式导致索引用不了 where first_name <> 'CARMEN' where salary not between 1000 and 2000 where dept_id not in (32,34,42) is null导致索引用不了 where commission_pct is null 基于函数的索引 create index s_emp_last_name_idx FROM后面跟子查询 from(select) rownum 必须从第一条记录找起 select rownum ,first_name from s_emp; rownum 与 rowid区别 rowid是数据存放的物理位置 rownum是 排序问题 分页问题 分别处理： 部门31／32／33工资上调10％ ／20％／30％ select first_name,dept_id， case when dept_id = 31 then salary*1.1 when dept_id = 32 then salary*1.2 when dept_id = 33 then salary*1.3 else （不加else 则when之外数据都为null） salary end aft_sal from s_emp; （使用CASE 注意逗号） docode select first_name,dept_id,salary, decode(dept_id 31,salary*1.1, 32,salary*1.2, 33,salary*1.3, salary) aft_sal form s_emp; 行列转置 decode（X，Y，Z）如果X＝Y 则返回Z select job,count(decode(deptno,10,10)) dept_10, count(decode(deptno,20,20)) dept_20, count(decode(deptno,30,30)) dept_30, from emp group by job 集合运算 union/union all intersect minus 集合运算要求两个select语句是同构的，即列的个数和数据类型必须一致 slsect dept_id from s_emp intersect select deptno from emp; selelct deptno from dept minus select deptno from emp; 约等于not exists 什么是序列号（sequence） 能自动产生唯一值 是个可共享的数据库对象 主要用于产生主键值 create sequence name [increment by n] [start whth n] [{maxvaluen n|nomacvalue}] [{minvaluen n|nominvalue}] [{}] create sequence s_dept_id increment by 1 start with 51 maxvalue 9999999 nocache 修改sequence alter sequence [increment by n] [{}|] set head off set feed off set echo off spool droptab.sql select 'drop table'|| lower(table_name)||'cascade constraints;' from u set_tables; spool off exit