达内-ORACLE学习笔记.doc

一、Oracle数据库中常用的数据类型4二、数据库查询41）SELECT语句43)ORDER BY子句4三、SQL常用的命令分类及例子51.数据定义语言DDL举例：52.数据操纵语言DML举例：53.事务控制语言举例：6四、Oracle数据库函数61.字符函数62.数值函数63.日期函数64.不同数据类型间转换函数7五、表连接（关联查询）71.等值连接72.非等值连接7自连接8外连接8六、组函数9七、子查询91 单行子查询92 多行子查询103 多列子查询104其它子查询10八、约束101 primary key：102 foreign key113 not null124 unique125 check12九、数据字典 DD12十、事务 Transaction13十一、Oracle中的伪列13十二、序列(sequence)13十三、 视图(View)14十四、索引（index）15预讲笔记：杨建华15第一讲 Oracle SQL 2007.07.05151） sql基本类型152）上机环境（sqlplus）163) Managing Null Values17第二讲 Oracle SQL 2007.07.0618第三讲 Oracle SQL 2007.07.0921笔记：231、查询表结构 describe -desc232、修改错误命令243、创建表244 修改表245 SQLPLUS File Commands246 set pause command247 select 语句258 insert 语句259 fuction2510 伪表 dual2511 查看Oracle运行一条sql语句的时间：2512 ORACL 特性2613子查询的次序2614 伪列rowid and rownum261)rownum262)rowid2615 select 综合查询27练习一*27练习二*2716 Data Modeling and Database Design281. 多对多表的设计282.293.2916 数据导入导出29oracl 注意事项100：301. Oracle安装完成后的初始口令？302. ORACLE9IAS WEB CACHE的初始默认用户和密码？303. oracle 8.0.5怎么创建数据库？304. oracle 8.1.7怎么创建数据库？305. oracle 9i 怎么创建数据库？316. oracle中的裸设备指的是什么？317. oracle如何区分 64-bit/32bit 版本？318. SVRMGR什么意思？329. 请问如何分辨某个用户是从哪台机器登陆ORACLE的？3210. 用什么语句查询字段呢？3211. 怎样得到触发器、过程、函数的创建脚本？3212. 怎样计算一个表占用的空间的大小？3313. 如何查看最大会话数？3314. 如何查看系统被锁的事务时间？3415. 如何以archivelog的方式运行oracle？3416. 怎么获取有哪些用户在使用数据库？3417. 数据表中的字段最大数是多少？3418. 怎样查得数据库的SID ？3419. 如何在Oracle服务器上通过SQLPLUS查看本机IP地址 ？3520. unix 下怎么调整数据库的时间？3521. 在ORACLE TABLE中如何抓取MEMO类型栏位为空的资料记录？3522. 如何用BBB表的资料去更新AAA表的资料(有关联的字段)？3523. P4电脑安装方法3624. 何查询SERVER是不是OPS？3625. 何查询每个用户的权限？3627. 如何将索引移动表空间？3631. SQLPLUS下如何修改编辑器？3732. ORACLE产生随机函数是？3751. 如何将小表放入keep池中？4057. SQL Reference是个什么东西？4159. 请问如何修改一张表的主键？4166. 怎样把“&”放入一条记录中？4267. EXP如何加参数？4271. 内核参数的应用？4373. 如何单独备份一个或多个表？4579. 如何改变一个字段初始定义的Check范围？4681. 内连接INNER JOIN？4683. 如何执行脚本SQL文件？4687. 如何测试SQL语句执行所用的时间？4788. CHR()的反函数是？4789. 字符串的连接4790. 怎么把select出来的结果导到一个文本文件中？4791. 怎样估算SQL执行的I/O数？4793. 如何查询某天的数据？4895. 如果修改表名？48一、 Oracle数据库中常用的数据类型varchar2(长度)可变长字符串char(长度) 定长number()表示整数或者浮点数clob 字符的大对象blob 二进制的大对象二、数据库查询1）SELECT语句从表中提取查询数据.语法为SELECT DISTINCT column1,column2, FROM tablename WHERE conditions GROUP BY conditions ORDER BY expressions ASC/DESC; 说明：SELECT子句用于指定检索数据库的中哪些列，FROM子句用于指定从哪一个表或视图中检索数据。2）WHERE子句WHERE子句用来选择符合条件的的记录.like . 通配查询between . and . ,表示结果在这之间，between and是一个闭区间。!=，=，这三个都可以表示不等于。in (va1,val2,.) 判断结果是否在这个集合中存在 。like . 字符串通配查询，%表示0或多个字符， _表示一个字符。. and . 表示只有两个条件同时满足. or . 表示条件只要满足其中之一就可以all . 是要求都满足条件。not .，则是可以与以上的条件产生反效果。. is null 用来判断值是不是空。3)ORDER BY子句ORDER BY 子句使得SQL在显示查询结果时将各返回行按顺序排列，返回行的排列顺序由ORDER BY 子句指定的表达式的值确定。 ASC（默认，升序） DESC（降序）ascentdingdescentdingorder by 目标列名（别名） 排序顺序（不写排序顺序，默认为升序排序）例：select first_name from s_emp order by first_name;select first_name from s_emp order by first_name desc;三、SQL常用的命令分类及例子DDL数据定义语言：create（创建）、alter（更改）和drop（删除）命令。truncateDML数据操纵语言：insert（插入）、select（选择）、delete（删除）和update（更新）命令。DCL事务控制语言：commit（提交）、savepoint（保存点）和rollback（回滚）命令。数据控制语言：grant（授予）和revoke（回收）。1.数据定义语言DDL举例： SQL create table myTab(no number(4),name varchar2(20); 创建一个名为myTab的表，包含两列no和name；create table empa as select * from emp where 1=2;SQL alter table myTab modify (name varchar2(25); 修改myTab中的name列，使此列能容纳25个字符；SQL alter table myTab add (tel_no varchar2(20); 给表myTab增加一列tel_no；SQL alter table myTab drop column tel_no; 删除表myTab的tel_no列;SQL drop table myTab; 删除表myTab；SQL truncate table myTab; 删除表myTab中的所有行（截断表）,注意:此操作不可以rollback。create type * as Array(5) fo varchar2(20)MySqlcreate table t_user (id int(4) primary key auto_increment,username varchar(20);mysql desc t_user;+-+-+-+-+-+-+| Field | Type | Null | Key | Default | Extra |+-+-+-+-+-+-+| id | int(4) | NO | PRI | NULL | auto_increment | username | varchar(20) | YES | | NULL | |+-+-+-+-+-+-+2.数据操纵语言DML举例：Data Manipulation LanguageSQL insert into myTab values(001,John);向表myTab中插入一行数据；SQL select distinct salary “薪水” from s_emp where salary1500 order by sal desc;选择表中salary大于1500的数据，以别名“薪水”显示并按照salary的降序进行排列输出；SQL create table empa as select empno,ename,job,sal from emp;从emp表中选择“empno,ename,job,sal”四列的数据建立新表empa;SQL create table empa as select * from emp where 1=2;使用一个假条件根据现有表emp创建一个只包含结构的空表empa； SQL delete from empa where sal update empa set sal=1500 where sal commit; 用于提交并结束事务处理；SQL savepoint mark1; 保存点类似于标记，用来标记事务中可以应用回滚的点；SQL rollback to savepoint mark1; 回滚到保存点mark1。四、Oracle数据库函数注意:dual表(虚表)是专门用于函数测试和运算的.1.字符函数 字符是大小写敏感的 转小写 lower(字段名) 转大写 upper(字段名) 首字母大写 initcap(字段名) 字符串拼接 concat(字段1, 字段2) 截取子串 substr(字段名, 起始位置，取字符个数)例: select first_name,substr(first_name,2,2) sub from s_emp;（从名字的第二个字符开始取两个字符）select first_name,substr(first_name,-2,2) sub from s_emp;（从名字的倒数第二个字符开始取两个字符）2.数值函数 四舍五入函数 round(数据,保留到小数点后几位) 1表示保留到小数点后一位，-1表示保留到小数点前一位。 例：select round(15.36,1) from dual; 截取数值函数 trunc(数据，保留到小数点后几位) 例：select trunc(123.456,1) from dual;截取到小数点后一位,注意:与round函数不同,不会四舍五入。3.日期函数 缺省日期格式，日-月-年 dd-mon-rr 修改当前会话的日期格式，会按照指定的格式输出日期 alter session set nls_date_format=yyyy mm dd hh24:mi:ss; 返回当前日期 sysdate 例：select sysdate from dual;round( )trunc( )4.不同数据类型间转换函数 将日期转成字符串 tochar(date,日期格式) 日期格式要用有效格式，格式大小写敏感 yyyy mm dd hh24:mi:ss(标准日期格式),year(年的全拼),month(月的全拼)，day(星期的全拼)，ddspth (日期的全拼) 例：select to_char(sysdate,yyyy mm dd hh24:mi:ss)from dual;select to_char(sysdate,year month day ddspth)from dual; 将字符串转成日期 to_date(.,日期格式) 例：select to_char(to_date(2006 11 03,yyyy mm dd),dd-month-yy) from dual；五、表连接（关联查询）1.等值连接 select table1.column1，table2.column2 from table1 t1，table2 t2 where t1.column3=t2.column4; 表连接时，当表与表之间有同名字段时，可以加上表名或表的别名，加以区分，使用时要用表名.字段名或表别名.字段名（列名）。当表的字段名是唯一时，可以不用加上表名或表的别名。注意：当为表起了别名，就不能再使用表名.字段名了。例如：select e.first_name | | e.last_name name, dept_namefrom s_emp e, s_dept dwhere e.dept_id=d.id;2.非等值连接 select 表别名1.字段名1，表别名2.字段名2,. from 表1 表别名1 ，表2 表别名2 where 表别名1.字段名3 . 表别名2.字段名4 .可以使比较运算符，也可以使其他的除了=的运算符例：select first_name, salaryfrom s_emp where salary between 1000 and 2000;自连接 把一个表的两个字段关系转换成两个表字段之间的关系. select 表别名1.字段名1，表别名2.字段名2,. from 表1 表别名1 ，表1 表别名2 where 表别名1.字段名3=表别名2.字段名4;例：select a.first_name ename,b.first_name cname from s_emp a,s_emp b where a.manager_id=b.id;外连接 使用一张表中的所有记录去和另一张表中的记录按条件匹配(空值也会匹配)这个表中的所有记录都会显示。/要记录一个都不能少，则在对方的那一边加(+)(+):表示对所在位置的表添加一个满足左边的任何一个条件的记录。1. LEFT OUTER JOIN：左外连接SELECT e.last_name, e.dept_id, FROM s_emp e LEFT OUTER JOIN s_dept d ON (e.dept_id = d.id);等价于 SELECT e.last_name, e.dept_id, FROM s_emp e, s_dept d WHERE e.dept_id=d.id(+);结果为：所有员工及对应部门的记录，包括没有对应部门编号dept_id的员工记录。 2. RIGHT OUTER JOIN：右外连接 SELECT e.last_name, FROM s_emp e RIGHT OUTER JOIN s_dept d ON (e.dept_id = d.id);等价于 SELECT e.last_name, FROM s_emp e, s_dept d WHERE e.dept_id(+)=d.id;结果为：所有员工及对应部门的记录，包括没有任何员工的部门记录。 3. FULL OUTER JOIN：全外关联 SELECT e.dept_id,d.id FROM s_emp e FULL OUTER JOIN s_dept d ON (e.dept_id = d.id);结果为：所有员工及对应部门的记录，包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。 六、组函数 group by把 select 的结果集分成几个小组，这个group by 子句可以跟在 select 语句后面或是 having前面。group by子句也会触发排序操作，会按分组字段排序。select 组函数或分组的字段名. from 表名 group by 字段名1,字段名2,.；例：select avg(salary) from s_emp group by dept_id;注意：组函数会忽略空值，但是count(*)除外，他会把空记录也统计在内。avg和sum这两个函数的参数只能是number型的。以下所提到的函数可以使用任意类型做参数。max(.),min(.)求最大值和最小值，count(*)统计表中记录数。例：select max(),avg(a.salary), max() from s_emp a,s_dept b,s_region c where a.dept_id=b.id and b.region_id=c.id group by b.id;注意：只要写了group by子句，select后就只能用group by之后的字段或者是组函数。having子句可以过滤组函数结果或是分组的信息，并且写在group by子句后。七、子查询可以嵌在sql语句中的select语句。在select语句中嵌套子查询时，会先执行子查询。一般的会将子查询放在运算符的右边。注意：在使用子查询时，要注意这个运算符是单行的（也就是只能是单值），还是多行运算符（范围，多值）。配合使用子查询返回的结果必须符合运算符的用法。例:select first_name| |last_name namefrom s_empwhere title in (select title from s_emp where dept_id=42);查询和42部门员工职位相同的所有员工的姓名1 单行子查询返回一行数据的子查询语句( 单列单行)2 多行子查询返回多行数据的子查询语句（where 字句中使用多行子查询，必须使用多行比较符：IN , AND , ANY）（单列多行）3 多列子查询返回多个列数据的子查询1) 返回单行数据 where字句中使用单行比较符( =, , = , ,in not)2) 返回多行数据 where字句中使用多行比较符（IN, ANY, ALL）比较方式：1) 成对比较2) 非成对比较4其它子查询八、约束针对表中的字段进行定义的。primary key（主键约束 PK）保证实体的完整性，保证记录的唯一主键约束，唯一且非空，并且每一个表中只能有一个主键，有两个字段联合作为主键时，将两个字段组合在一起唯一标识记录，叫做联合主键。1 primary key：第一种定义形式：create table test(c number primary key ); 列级约束create table test(c1 number constraints pk_c1 primary key ); 此约束有名字: pk_c1第二种定义形式：create table test(c number , primary key(c) ) ; 表级约束create table test(c number , c2 number , primary key (c ,c1) ) ; 用表级约束可以实现联合主键create table gao_boolean (user_id number(3), sername varchar2(12), constraint gao_pk_user_id primary key(user_id), constraint gao_pk_username unique(username)删除约束：alter table gao_booleandrop constraint gao_pk_user_id添加约束：修改表结构alter table gao_booleanadd constraint gao_pkuser_id primary key(user_id);2 foreign key（外键约束 FK）保证引用的完整性，外键约束，外键的取值是受另外一张表中的主键或唯一值的约束，不能够取其他值（或者只能为null），只能够引用主键或唯一键的值，被引用的表，叫做parent table（父表），引用方的表叫做child table（子表），要想创建子表，就要先创建父表；记录的插入也是如此，先父表后子表；删除记录，要先删除子表记录，后删除父表记录；要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。(可以通过使用cascade constraints 选项来删除父表)carete table parent(c1 number primary key );create table child (c number primary key , c2 number references parent(c1);或表级约束定义:create table child( c number primary key , c2 number , foreign key(c2) references parent(c1);.on delete cascade级联删除，当删除了父表中的记录时，子表中相关联的记录也删除。on delete set null当删除父表中的记录时， 子表中相应的记录赋值位null父表：create table gao_dept( dept_id number(3) primary key, dept_name varchar2(12)子表：SQL get constaintondeletecascade 1 create table gao_emp(emp_id number(3), 2 emp_name varchar2(12), 3 dept_id number(3), 4 constraint gao_pk_emp_id primary key(emp_id), 5 constraint gao_fk_dept_id foreign key(dept_id) 6* references gao_dept(dept_id) on delete cascade)删除约束：alter table gao_empdrop constraint gao_fk_dept_id3 not null非空约束（not null）这是一个列级约束,在建表时,在数据类型的后面加上 not null ，也就是在插入时不允许插入空值。例：create table student(id number primary key,name varchar2(32) not null,address varchar2(32);4 uniqueunique 唯一约束唯一约束，允许为空，要求插入的记录中的值是唯一的。例：create table student(id number，name varchar2(32),address varchar2(32),primary key (id),unique (address);5 checkcheck约束检查约束，可以按照指定条件，检查记录的插入。check中不能使用伪列，不能使用函数，不能引用其他字段。例：create table sal (a1 number , check(a11000);九、数据字典 DD数据字典是由系统维护的，包含数据库的信息数据字典视图user_XXXXX 用户视图all_XXXXX 所有视图dba_XXXXX 数据库中所有视图v$_XXXXX 动态性能视图dict或 dictionary 表示数据字典的数据字典。user_constraints 用户的表中管理约束的表其中有constraints_name字段存放的是约束名，constraint_type字段存放的是约束的类型, r_constraints_name字段表示外键引用自何处.user_cons_column 表，是用户的列级约束表, column_name字段存放的是约束字段的名字, position字段存放的是约束在联合键中的位置DD. USER_TABLESuser_constraintsuser_view十、事务 Transaction原子操作，也就是不可分割的操作，必须一起成功一起失败。事务的结束动作就是commit，DDL, DCL语句执行会自动提交commit。sqlplus正常退出是会做提交动作的commit;，当系统异常退出时，会执行回滚操作rollback;。一个没有结束的事务，叫做活动的事务 (active transaction),活动的事务中修改的数据时，只有本会话(session)才能看见。十一、Oracle中的伪列伪列就像Oracle中的一个表列，但实际上它并未存储在表中。伪列可以从表中查询，但是不能插入、更新或删除它们的值。常用的伪列：rowid和rownum。rowid：数据库中的每一行都有一个行地址，rowid伪列返回该行地址。可以使用rowid值来定位表中的一行。通常情况下，rowid值可以唯一地标识数据库中的一行。rowid伪列有以下重要用途：1）能以最快的方式访问表中的一行；2）能显示表的行是如何存储的。3）可以作为表中行的唯一标识。如：SQL select rowid,ename from emp;rownum：对于一个查询返回的每一行，rownum伪列返回一个数值代表的次序。rownum伪列特点：1） 有个特点要么等于1 要么小于某个值， 不能直接等于某个值, 不能大于某个值。2）常用于分页显示。返回的第一行的rownum值为1，第二行的rownum值为2，依此类推。通过使用rownum伪列，用户可以限制查询返回的行数。如：SQLselect * from emp where rownum10with check option;Rules for Performing DML Operations on a viewYou cant remove a row if the view contains:a) Group functionb) A GROUP BY clausec) The DISTINCT commandYou cant modify data in a view if it contains1. Any of the above conditions2. Columns defined by expressions3. the ROWNUM pseudocolumnyou cant add data if the view contains1. Any of the above conditions2. Any NOT NULL columns not selected by the view.赋权：grant creat view to 用户名十四、索引（index）也是一个记录，该记录只包含关键字和指向指向表中正真记录的指针。建立索引的目的就是为了加快查询速度,建立索引后会使DML操作效率慢，但是对用户查询会提高效率。删除一个表时，相对应的索引也会删除。另外,索引是会进行排序的。创建一个索引：create index 索引名 on 表名 (字段名);create index test_index on test(c1);删除索引:drop index test_index;注意：创建索引就是为了减少物理读，索引会减少磁盘block搜索扫描的时间。在经常要用到where的子句的字段，应该使用索引，另外还要看所查询的数据与全部数据的百分比，表越大，查询的记录越少，索引的效率就越高。4. 系统自动创建。创建主键，或有唯一性的列，5. 手工创建。可以指定位某列创建EXPLAIN plan forselect id from gao_index where username=gao 讲执行过程存放到plan_talbe中。预讲笔记：杨建华第一讲 Oracle SQL 2007.07.05 BooleanLab% sqlplus boolean/boolean/user:soft/adampsd:boolean/adam1） sql基本类型查询语句，数据操作语句，数据定义语句，事物控制语句1. 查询语句：select,where,qroup by, order by . ,desc(表的结构)2. 数据操作语句：DML -insert(往数据库插入语句)-delete(删除数据)-update(修改数据)3. 数据定义语句：create,drop(建表，删表)4. 事务控制语句：rollback, commit;操作实例：SQL desc test Name Null? Type - - - ID NUMBER NAME VARCHAR2(12)insert into adam values(1,gaojz);/暂时保存在当前的缓存SQL select * from 2 adam; ID USERNAME- - 2 shizhen 1 adam 1 gaojz2）上机环境（sqlplus）! :切换到unix环境! * ：在SQL中执行unix命令exit :回到SQL环境clear buffer -cl buff ：清除缓存中的指令l-list :缓存中的指令/ ：执行缓存中的指令change -c/*/*save filename :把存放在缓寸中的SQL指令，保存到文件系统get filename ：把文件系统中的sql指令调入内存start filename ：执行该文件的sql指令filename ：同start filenameedit filename :用vi编辑脚本spool filename ：把console的内容录入到文件a /append :当前指令添加命令 exit ：退出当前系统unix commands:catSQL spool on 开启录象 spool *.*/文件名。sq。 spool offcat *.*/查看 SQL ed insert.sql/编辑脚本文件SQL insert.sql /运行 *。sql文件declareid number;begin for i in 1.100 loopinsert into adam values(i, hello :|i);end loop;endset pause off/on 分屏显示show pause 查看设置Arithmetic ExcepressionsSQL select 员工: |username |的念心是：|salary *12 员工信息 2 from adam_emp;员工信息-员工: adam的念心是：12000 3) Managing Null Valuesnvl( , )第一个参数是列名第二个参数是替换null值的。如果是Null 的话SQL select nvl(username,no body) 2 from adam_emp;添加列名：alter table adam_empadd gender varchar2(8);显示打印信息 1 select 员工:|nvl(username,Narcy)|的性别是|decode(gender, female,male,null,性别待定)员工信息 from adam_empdecode(列名，para2,para3 )select decode(gender, female,male,null,性别待定) from my_emp;delete from mytabel 删除记录select distinct id from adam_emp; 消除重复记录 insert into adam_emp values(2,tom,1534,male);col format format heading text()jus tify align(left,center,right)col name null col username col username null 名字为空; col username clearalter table gao add gender number;SQL select * from my_emp where nvl(username,null,a)=a;SQL select * from my_emp where username is null;asc/升序desc/降序greatest返回一个表达式中最大值，即比较字符的编码大小select greatest(aa,abac)from dual;dual 是个伪表，不是我们自己建的。是数据库自己生成的。我们可以从这里取出我们想要的值，如日期，数值等。least返回一组表达式中的最小值。SQLselect least(啊,安,天)from dual;指定转意字符 escape .用户自己定义like a_% escape字符串和日期类型要用单引号！SQL alter session set nls_date_format = dd-mm-yy;/改变当前会话的日期格式SQL select rownum,id,username from(select rownum row_id, id, username from adam) where row_id between 3 and 8;/(select rownum row_id, id, username from adam)子查询/rownum 伪列，从数据库中查询出来的结果表（在内存中）中又生成了一个伪列。/rownum row_id 给列取一个别名 让伪列变真列。/对内存中的表再执行select第二讲 Oracle SQL 2007.07.06 1. single row functionscharacterlowerupperinitcapconcatsubstrlengthnvlinstr2. multiple row functionsgroup3. Number functionsroundtrunc(truncates values.)mod4. Date FunctionsMonths_between(para1,para2)Add_month(date,1) /月份的累加next_day(