Oracle笔记

1 三版Oracle(共五天) day1 （陆晓伟） 一．lesson 1（视频课件35.1和35.2） Oracle版本：------7i—>8—>8i—>9i—>10g(i：Internet g:网格) 1.关系数据库三要素： 1).实体（表）————class 2).属性（列）————property属性/field 3).关系（pk和fk）————对应Java中的关联关系，继承关系 pk:无序不重复，唯一，not null。（每个实体只有一个主键） fk:可以重复，可以为null ，必须指向pk, fk的值必须在pk里取 2. SQL（结构化查询语言）：所有数据库的通用语言。(各别地方，不同DB有区别！) PLSQL：Oracle公司对SQL的扩展，Oracle数据库的特有语言。 SQLPLUS：Oracle公司提供的工具，用来写/发送SQL语句的。 注 SQLPLUS语句和SQL之间的比较： 相同：不区分大小写 区别：1.SQLPLUS语句可以简写，而SQL不可以； 2.SQLPLUS语句发送后不能存入Buffer，而SQL或PLSQL语句可以； 3.SQLPLUS语句后的；号不要求，而SQL必须以分号结尾。 3.创建普通用户并授权 SQL>create user 用户名 identified by 2 default tablespace users 3 temporary tablespace temp; User created. SQL>grant resource,connect to 用户名; Grant succeeded. SQL> alter user 用户名 identified by 新密码; 4.三种登陆sqlplus工具的方式： 1）SQL>sqlplus 回车 username: password: 2）SQL>sqlplus username回车 password： 3）SQL>sqlplus username/password 回车 5.SQLPLUS命令： SQL>describe(desc)+表名: 查看表的结构，可看到：columnname,是否not null,type Oracle中的几种数据类型：number(数字类型),date,varchar2(字符串类型,变长),varchar 。 SQL>list(L):显示buffer的内容，buffer属于当前的SQLPLUS，里面只能存放一条SQL命令 SQL> /<====>run(r): 运行buffer里的命令 SQL> column（col）------SQLPLUS命令 1.col[umn] 列名 heading ‘别名’ ：对列进行预定义，----->此为用SQLPLUS命令为列起别名。 2.col[umn] 列名 format an或 L或$999,999.00 :对某列数据的 显示字长或格式 进行预定义。 注：an：仅适用于字符型或日期型的列，用于控制每行数据显示长度 $999,999.00：仅适用于number类型的列，控制每行数据的显示格式 L：表示根据你的数据库方言去选择货币符号，这里如果用L，则显示的为￥. 如：col last_name format a10 ：表示使last_name列的数据占10个字符长度来显示。 col name heading 'na' format a10 2

注 用SQLPLUS命令col为列起别名 与 用sql命令起别名的区别： col预定义: 作用的时间长,且只要不退出sqlplus，对所有表中叫这个列名的列都生效。 sql:当次生效，且仅对当前sql的当前表中的这个列生效。 SQL> change(c):改变缓冲区中命令 1. SQL> c/old串/new串 2. SQL> 1 select * (当命令得某一行中需要改动较多时，使用该命令来更改某一具体行) 3. SQL> edit(ed)------>vi：unix系统 ed------>记事本：windows SQL> save ：buffer----->file 将buffer里的内容保存出去： 1.new ------->save filename ：保存并新建 2.override---->save filename replace(rep) ：保存并覆盖 SQL> get filename：file------>buffer 把文件的内容导回缓冲区 SQL> start(@) filename ======get + run(/) 把文件内容导入缓冲区并且执行。 SQL> spool filename------>spool off：抓屏，将抓到的所有内容都保存到filename文件中 二．lesson 2 1.order by子句: 排序 select id,last_name,dept_id from s_emp 1)order by dept_id; order by title; //注：可以不按照要查询的列进行排序，只要表中有的列就可以。 2)order by last_name,dept_id;先按名字升序排,名字相同再按dept_id升序排。不指明时默认按照升序(ASC)排列 3)order by last_name desc; order by dept_id,last_name desc; //dept_id升序,last_name降序 4)null //注：null在oracle里是最大的值。 5)order by 2; <===> order by last_name //注：这里的2，表示select里面的第二列。 6)按列的别名排序 select id,last_name name from s_emp order by NAME; 2.where子句：条件判断 select id,last_name,dept_id,start_date from s_emp 1)where dept_id=41; where last_name='Smith'; ---->严格区分大小写 where start_date='08-mar-90';---->必须这么写，dd-mon-yy是Oracle数据库中默认的日期格式。 2)where salary>1000; 3)关键字：between --- and--- where dept_id between 41 and 45; ---包括边界值 where dept_id between 45 and 41; ---无语法错误，但查不出结果！注:必须值小的在前,反了,将查不出记录。 4)where dept_id in(41,43,45); ----枚举，用in 5)where commission_pct=null;----查不出记录，错误！因为表达式中出现null，结果一定为null！ where commission_pct is null;-----正确！！ where commission_pct is not null; 6)where last_name like 'S%';----模糊查询 unix中通配符: *（表0到多个） ?（有且仅有一个） oracle中通配符: %（表0到多个） _（有且仅有一个） 例：查出last_name是以'S_'开始的： where last_name like 'S_%'; ----> error！！！ where last_name like 'S\_%' escape '\'; ---->right！！！ 注：oracle中没定义固定的转义字符，需要用转义字符时，须用escape指明谁是转义字符！！！ 3

7)非: != <> not between…and not in not like is not null 8)执行优先级：括号>and > or && > ||

Oracle day2 一．lesson 3（day2 上午 视频课件36.1） 单行函数（Single Row Function）:应用于每一个值，并且产生每一个结果。 1.针对varchar2型的:（LOWER、upper、initcap单词首字母大写、concat、substr、length、nvl） 1) 处理空值函数 nvl('hello','world') --等价---> 'hello' (由于hello串永远都不为null，所以等价于‟hello‟) nvl(null,'world') --等价---> 'world' 2) select id,last_name,dept_id from s_emp where lower(last_name)='smith'; ---LOWER应用很多！ 3) select id,concat(last_name,first_name),dept_id from s_emp; concat(last_name,first_name)--->result1; concat(result1,title); ----等价---> concat(concat(last_name,first_name),title); 注：||连接多个列的情况较常用，因为concat连接多个串时需要函数的嵌套。 4）select substr('String',-4,3) from dual; //显示结果：rin ---SUBSTR应用很多！ 注：当所查的列不在任何表中或不清楚它能从哪个表中查到时，就可以用dual（dummy table）盲(哑)表,它的存在是为了维护select的完整性 例：substr可辅助进行模糊查询 select id,last_name,dept_id from s_emp where last_name like 'S%'; ---等价于--> where substr(last_name,1,1)='S'; 2.针对number型的: round：四舍五入的截取数字值 Round(45.923,2)-->45.92 Round(45.923,0)-->46 Round(45.923,-1)-->50 trunc：直接截取数字值 trunc(45.923,2)-->45.92 trunc(45.923,0)-->45 trunc(45.923,-1)-->40 3.针对date型的: months_between(‟01-SEP-95‟,‟11-jan-94‟) ------>19.774194 :两个日期之间的月份数 add_months (‟11-JAN-94‟,6)----->‟11-JUL-94‟:Add calendar months to date. next_day(‟19-jun-08‟,Friday) : 接下来第一个星期五的日期（从当前天往后查，找第一个符合的） last_day(‟01-sep-95‟)---->‟30-sep-95‟ :本月的最后一天的日期。 Round:四舍五入的截取日期 round(‟25-MAY-95‟,‟MONTH‟)---->01-JUN-95 round(‟25-MAY-95‟,‟YEAR‟) ---->01-JAN-95 Trunc :直接截取日期 trunc(‟25-MAY-95‟,‟MONTH‟)---->01-MAY-95 trunc(‟25-MAY-95‟,‟YEAR‟) ---->01-JAN-95 1） SQL> select sysdate from dual; 查看当前系统时间 SYSDATE -------------- 22-7月 -09 2）SQL> select to_char(sysdate,'dd-mon-yyyy,hh24:mi:ss pm') sdate from dual;//设置显示格式 SDATE ---------------------------------------- 22-7月 -2009,09:58:28 上午 注：Oracle中默认的日期显示格式是DD-MON-YY。 3）标准的日期格式: 年: yy 08 yyyy 2008