ORACLE学习笔记

1、ORACLE结构化查询语言（Structured Query Language）数据定义语言ddl表 table是数据库中存储数据的基本单位column (data type width constraint)create table alter table drop table1.数据操作语言DMLDML table datarowinsert update delete事务控制语言 Transaction Control Language TCL transactioncommit rollback数据查询语言DQLselect数据控制语言DCLgrant授予权限revoke 回收权限DB

2、A database adminstrator数据库管理员desc s_emp'desc' means 'describe'desc s_deptdesj s_region给列起别名能够改变一个列，表达式的标识适合计算字段在原名和别名之间可以使用as 关键字别名中包含空格，特殊字符或希望大小写敏感用双引号将起括起来空值处理nvl （p1,p2 ） ->if p1 is null thenreturn p2;elsereturn p1;end if两条之间拼接用加空格用 （空格）distinct去重投影操作select distinct title from

3、 s_emp;distinct应用到 select后面所有列，所有列联合起来唯一如果 select后面跟 distinct关键字，一定是直接跟在select后面，前面没有任何内容。Oracle home: oracle产品的安装目录ORACLE_SID: 数据库所对应的实例（instance ）名字（一个实例唯一的对应一个数据库，实例确定了，数据库就确定了）telnet 192.168.0.26sqlplus username/passwordWhere子句的功能Where跟在 from 子句之后Where子句后跟条件表达式select last_name,dept_id,salaryfrom

4、 s_empwhere dept_id=42;select from wherewhere 后面不能用列别名where 后面尽量不用算术表达式表达式里字符串用单引号表示表达式与或 and orbetween and 表示一个范围，是闭区间。含义大于等于并且小于等于in （ , ,. ）表示一个集合，是离散值。含义为等于集合中任意一个在字符串比较中，可用like和通配服进行查找s' %'s 打头的字' _'s 打头的两个字母的字查找_%时 在where句末用escape定义一个转义字符然后加在_ %前面is null判断是否为空数据库中的表分为系统表：储存数据库的

5、信息用户表：存储用户信息逻辑比较运算符<> != =SQL比较运算符not between andnot in <=> <>allnot likeis 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语句分析语句搜索是否有相同语

6、句检查语法，表名。权限在分析过程中给对象加锁生成执行计划绑定变量 给变量赋值执行语句获取数据 将数据返回给用户进程单行函数字符函数（ character）LOWER小写函数UPPER大写函数INITCAP 首字符大写函数CONCAT拼接函数 ('good','string')SUBSTR求字串函数 substr('string',1,3) /*第三位数字表示取字串 的位数 */（第二位表示字符串下标正数表示左边第N个 负数表示右边第N个）LENGTH求长度LPAD 右对齐函数，位数不够的，左边补指定字符LPAD（STRING ，10，* ）RPA

7、D 右对齐函数，位数不够的，左边补指定字符数值类型（ number）日期类型（ date ）转换函数多行函数（组函数）round四舍五入函数round( 小数，小数点后保留位数) 不写位数默认保留到个位负数表示小数点前trunc截取函数用法类似 round非保留位无论多大都省略TO_NUMBER将字符串转换成数值TO_CHAr将数值转化为数字字符串隐式类型转换通常会导致索引无法使用NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK' EXPORT NLS_LANGNLS_LANG+'AMERICAN_AMERICA.US7ASII&#

8、39;￥ 99， 999$00,000 格式说明 9 代表数字位0 定义宽度大于连接操作cross join全连（inner ） join on内连接匹配问题自连接通过给一张表起不同的别名来将一张表的列间关系转化成不同表之间的关系内连接用 and外连接用 where外连接 outer join匹配不上记录from ti left join t2左边的表必须作驱动表on t1.c1 = t2.c2and 连接前过滤where 连接后过滤外连接的结果集内连接的结果集t1 表中匹配不上的记录和t2 表中的一条 NULL记录from ti right join t2on t1.c1 = t2.c2外连

9、接的结果集内连接的结果集 t2 表中匹配不上的记录和 t1 表中的一条 NULL记录组函数操作在一组行记录上AVG（DISTINCTALL n） 平均值SUM （DISTINCTALLn）求和COUNT(DISTINCT|ALL|EXPR|*)计数MAX(DISTINCT|ALL|expr)最大值MIN(DISTINCT|ALL|expr)最小值主函数不处理空值当所有值都为空返回 0count （* ） 表示表中记录数GROUP By指定的表达式，叫要处理的数据（若有WHERE子句即为通过条件过滤后的数据）分为若干组，每组有唯一的组标识，组内有若干条记录，根据 SELECT后面的组函数对每组的

10、记录进行计算，每组对应一个返回值group by XXall/dinstinctselect后面若有组函数则其他都必为组函数分组后标识含义改变若有 GROUP By子句， SELECT后面可跟 GROUP By后面跟的表达式以及组函数，其他会报错GROUP BY子句中包含多列，分组的粒度更细 group by dept_id,title;HAVING子句行被分组将 HAVING子句的条件应用到每个分组上只有符合 HAVING条件的组被保留，在应用 SELECT后面的组函数对每组的数据进行处理HAVING后不能接组函数也不能接列别名where 过滤的是行（记录）HAVING子句过滤的是分组（组标

11、识，每组数据的聚合结果）where 子句包含单行函数having 子句只能包含 group by 后面的表达式和组函数 where 子句执行在前， having 子句执行在后 where 子句和 having 子句都不允许用列别名子查询：1，非关联子查询selectfromwhere expr eperator(selectfrom)先执行子查询，子查询的返回结果作为主查询的条件，再执行主查询。子查询之执行一遍若子查询的返回结果为多个值， ORACLE会去掉重复值之后，再将 结果返回给主查询是单行运算符集合时应该用inwhere（X，Y）多列子查询关联子查询采用的是循环（loop ）的方式，执

12、行步骤如下：1 外部查询得到一条记录exists存在采用的是循环（ loop ）方式select t1from outerwhere exists（select x from t2 where s1.x1 = s2.x2）exist是用循环方式（ loop ），由 outer表的记录数决定标量子查询内连接select t1.c1,t2.c2from t1,t2where t1.c1 = t2.c2select t1.c1,t2.c2,t3.c3from t1,t2,t3where t1.c1 =t2.c2and t2.c4 = t3.c3外连接select t1.c1,t2.c2from t1

13、,t2where t1.c1 = t2.c2(+)where t2.c2(+) = t1.c1加号侧是匹配表and t2.c1 is null（+）连接前过滤E-R 图基于数据库的开发是一个自顶向下的系统开发方法将业务ER图 实体关系图实体有共同属性的一类对象的集合属性通过属性描述实体，区分实体关系主键值 (primary key) PK联合主键：多列联合唯一，任意一列都可以重复，每一列都不能为NULLchild table fk (dept_id)-> parent table(id) pk/uk(id)先 create parent table,pk.uk,再建 child tabl

14、e2先 insert into parent table,再 insert into child table3先 delete child table,再 delete parent table4先 drop child table,再 drop parent table范式；第一范式 表中不会有重复的记录，即有主属性：每个属性值不再可分第二范式 每个非主属性必须完全依赖于主属性第三范式每个非主属性不能依赖于另一个非主属性CREATE TABLE必须有建表的权限命名规则首字母必须是字母名字为 130 个字符长只能包含 A Z， az，09，_,$ 和#同一个命名空间里的对象不能重名不能用 Or

15、acle 的保留字数据类型：VARCHAR2（size ），变长字符类型建表CREATE TABLE TEST（C1 VARCHAR2（） 10，C2 CHAR（10）；表中增加一列：alter table test add c3 number;INSERT INTO tableVALUES (valueinsert into test(c1) values ('abc');varchar2对空格敏感number(p,s)p 是精度，数值中所有数字为的个数，最大精度36s 是刻度范围， s 为正数，表示小数点右边的数字位的个数，为负数，表示小数点开始向左进行计算数字位的个数。刻

16、度范围从84 到 127c1 numberc2 number(6)c3 number(4,2)数字可输入4位小数点后 2位 1.234567 1.235c4 number(3,-3),数字 3位小数点前三位不计999000c5 number(2,4) 0.0099oracle用 7 个字节来存储日期和时间信息世纪，年，月，日，时，分，秒to_date()如果需要日期值to_char()如果要处理一个日期值yyyymmddhh24h12missd: 用数字表达的一周内的第几天（周日：0）daymonthselect start_date from s_empwhere to_char(start

17、_date,'fammonth')='month'select start_date from s_empwhere 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

18、')与该日期在同一月份的最后一天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 paren

19、t_c1_pk parmary key)create table child(2 c1 number (3) constraints child_c1_pk primary key,3 c2 number(4) constraints child_c2_fk4references 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 forei

20、gn key (c2)4references parent(c1);(引用父表)表级约束delete fromwhere( 不写 where 则删除表中所有记录)删除时先删子表再删父表cascade constraints解除约束create table child(c1 number constraints child_c1_pk primary key,c2 number constraints child_c2_fkreferences parent(c1) on delete cascade);级联删除create table child(c1 number constraints c

21、hild_c1_pk primary key,c2 number constraints child_c2_fkreferences parent(c1) on delete set null);update childset c2 = 1where c2 is nullupdate childset c2 = nullwhere c2 = 1唯一性约束若唯一键列上没建索引，系统会自动创建唯一性索引create table test(c1 number constraints test_c1_pk primary key,c2 number constraints test_c2_uk uni

22、pue,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_41asselect id,first_name,title,salary,dept_idfrom s_empwhere dept_id = 4

23、1;追加约束add constraints emp_41_id_pk primary key(id);数据操作语句update更改数据update s_empset dept_id = 60where dept_id = 10( 把部门 10 里的员工调到部门60 号去）default(10)设置缺省值 10事务的结束从哦秘密他r ollback事务的特性： ACID原子性（ atomic ）：一个事务要么完全发生，、要不完全不发生一致性（ conststen ）：事务把数据库从一个一致状态装边到另一个状态隔离性事务的隔离级别数据库开发的关键挑战排他锁（ X 锁）如果一个对象上加了X 锁，在这

24、个锁被采用commitcommit 提交一个事务rollback回滚一个事务truncate tableddl 语句无法用rollback截取回滚是 DDL语句表扫描的方式：全表扫描 FTS（Full Table Scan）高水位线：曾经包含数据的最右边的块rowid表示一条记录的物理位置select id,first_name,rowid from s_emp包含如下信息：该记录是属于那张表的（哪个数据库对象）： object_id 该记录在哪个数据文件里 file_id该记录在数据文件的第几个数据块里：block_id该记录在数据块里是第几条记录：row_id索引为什么要使用索引Oracl

25、e 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); 那些列

26、适合建索引经常出现在WHERE子句的列经常用于表连接的列该列是高基数数列（高基数数据列是指有很多不同的值）该列包含许多NULL值表很大，查询的结果集小主键（ PK）列，唯一键（ uk）列外键（ FK）列经常需要排序（ ORDER BY）和分组（ GROUP by）的列索引不是万能的不适合建索引的列列很少出现在WHERE子句查询的结果集也大该列被反复更新唯一性索引（unique ）等价于唯一性约束非唯一性索引用于提高查询效率单列索引索引建在一列上联合索引索引建在多列上create index emp_id_idx on test(c1,c2);哪些写法会导致索引用不了函数导致索引用不了where

27、 upper(first_name) = 'CARMEN'表达式导致索引用不了where salary*12 = 12000部分隐式数据类型导致索引用不了where c1= 2 (C1为 varchar2类型 )Like 和 substrwhere first_name like 'CA%'( 可用索引 )where substr (first_name ,1 , 2)= 'CA'否定形式导致索引用不了where first_name <> 'CARMEN'where salary not between 1000 a

28、nd 2000where dept_id not in (32,34,42)is null导致索引用不了where commission_pct is null基于函数的索引create index s_emp_last_name_idxFROM后面跟子查询from(select)rownum必须从第一条记录找起select rownum ,first_name from s_emp;rownum 与 rowid区别rowid 是数据存放的物理位置rownum是排序问题分页问题分别处理：部门 313233 工资上调 10 20 30select first_name,dept_id，case when dept_id = 31 then salary*1.1when dept_id = 32 then salary*1.2when dept_id = 33 then salary*1.3else（不加 else则 whe