OracleSQL培训讲义_RYAN1.ppt

1、Oracle SQL 培训讲义一期,ORACLE9i 9.0.1 空间1.76G ORACLE9i 9.0.2 空间2.86G 事务处理 - 增 删除 修改频繁 数据仓库 - 查询 频繁 GBK -多200个字左右(偏僻字) GB2312 SETUP.exe 双击 启动ORACLE数据库 OracleServiceACCP 服务 多启动几次 OracleOraHome90TNSListener 服务 如果启动不了Listener(监听器) 改注册表 ImagePath D:oracleora90BINTNSLSNR 用命令行启动 cmd - tnslsnr 最小化 tNSlsnter 监听器

2、如果无法启动 运行cmd 切换到DOS界面 敲tnslsnr 窗口不要关闭 D:oracleora90networkADMIN listener.ora 文本文件 HOST=localhost或机器名 存盘 用工具 ORACLE SQL*PLUS验证数据库是否启动 用户名system 密码manager 出现 连接到 和 SQL符号 说明数据库已经可用了 默认用户: 超级用户: 用户名system/密码manager 用户名sys/密码change_on_install 普通的用户: 用户名scott/密码tiger 注意的问题:安装的源程序放在什么目录下? 目录中不要出现中文名字的目录 数据

3、库的名字不要是中文,8个字符以内 字符集用默认的ZHS16GBK 删除: 在硬盘上的一个隐含安装的目录Programm Files下的ORACLE,ORACLE安装和删除,ORACLE SQL*PLUS 字符界面 ORACLE自带 sqlplusw.exe windows窗口下的sqlplus sqlplus.exe DOS下的sqlplus 用户名scott 密码tiger 普通用户 system manager 超级用户 sys change_on_install 超级用户 比system权限还要大 SQLPLUS下的常用命令 show user 察看用户 connect 用户名/密码 连

4、接到指定用户 connect scott/tiger select * from tab where tabtype=TABLE; 察看当前用户下的表 select count(*) from dept; 查询表dept中记录的行数 desc dept 察看表dept的结构 quit/exit 退出 clear screen 清除屏幕 set linesize 200 设置一行显示200个字符 set pagesize 20 设置每页显示20行 spool 文件名 (spool c:abc.txt) 作日志文件命令 spool off 修改D:oracleora90sqlplusadmingl

5、ogin.sql文件 可以设置SQLPLUS的环境 set linesize 200 set pagesize 20,操作ORACLE数据库的工具,ORACLE 企业管理器 (OEM) 图形界面 PL/SQL Developer 第三方软件 ORACLE不带的 QUSET 公司 TOAD SQLPLUS 浏览器使用oracle (isqlplus) D:oracleora90ApacheApacheconfhttpd.conf 修改80端口 - 8001 http:/localhost:8001/isqlplus,操作ORACLE数据库的工具1,字符型 char 范围 最大2000个字节 定长

6、 char(10) 张三 后添空格6个把10个字节补满 张三 性别 char(2) 男 varchar2 范围 最大4000个字节 变长 varchar2(10) 张三 在数据库中张三 大对象 字符型大对象 4000字节 最大4G CLOB (Character Large OBject) 数字 number 范围 10的-38次方 到10的38次方 可以表示小数 也可以表示整数 number(4) 最大表示4位整数 -9999 到 9999 number(5,2) 表示5位有效数字 2位小数的 一个小数 -999.99 到 999.99 日期 date 包含年月日和时分秒 7个字节 图片 b

7、lob 二进制大对象 图像/声音 4G,ORACLE中字段的数据类型,学生表student create table student( -学生表 xh number(4), -学号 xm varchar2(10), -姓名 sex char(2), -性别 birthday date, -日期 sal number(7,2) -奖学金 ); 班级class create table class( -班级表 classid number(2), -班级编号 cname varchar2(20) -班级名字 ); 添加字段(学生所在班级classid) alter table student ad

8、d (classid number(2); 修改字段的长度 alter table student modify (xm varchar2(12) ; 修改字段的类型(不能有记录的) alter table student modify (xh varchar2(5); 删除一个字段 alter table student drop column sal; 删除表 drop table student; 表的名字修改 rename student to stu; 字段如何改名字 -先删除 a)alter table student drop column sal; -再添加 b)alter t

9、able student add (salary number(7,2);,如何建表,插入数据 insert语句 所有字段都插入 insert into student values (A001,张三,男,01-5月-05,10); ORACLE中默认的日期格式DD-MON-YY dd 日子(天) mon 月份 yy 2位的年 09-6月-99 1999年6月9号 改日期的默认格式 alter session set nls_date_format = yyyy-mm-dd; insert into student values (A002,MIKE,男,1905-05-06,10); 恢复O

10、RACLE默认格式 alter session set nls_date_format = dd-mon-yy; 察看日期的格式 set linesize 1000 select * from nls_session_parameters where parameter=NLS_DATE_FORMAT; 永久设置日期格式 改注册表oracle/HOME0 加字符串NLS_DATE_FORMAT 值yyyy-mm-dd 部分字段插入 insert into student(xh,xm,sex) values (A003,JOHN,女); 插入空值 insert into student(xh,x

11、m,sex,birthday) values (A004,MARTIN,男,null);,如何插入数据,改一个字段 update student set sex=女 where xh=A001; 改多个字段 update student set sex=男, birthday=1980-04-01 where xh=A001; 改为空值 (修改为空时=null) update student set birthday=null where xh=A001; 把生日为空的人的班级编号改为20(条件中的空是is null / is not null) update student set clas

12、sid=20 where birthday is null; 错误的没有达到要求 update student set classid=20 where birthday=null; 不表示空值 表示xm是null的字符串 update student set classid=20 where xm=null;,修改 update,delete from student; 删除所有记录,表结构还在,写日志,可以恢复的,速度慢 drop table student; 删除表的结构和数据 delete from student where xh=A001; 删除一条记录 truncate tabl

13、e student; 删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快,删除 delete,select * from student; select xh,xm,sex from student; select * from student where xh like A%1; %任意多个字符 select * from student where xh like A_1; _1个字符 select * from student where xh like %A%; select * from student where xh like A%; select * from

14、 student where xh like %A; select * from student where xh = A%;,查询 select 1,select * from student order by birthday ; 升序 (order by birthday asc;) select * from student order by birthday desc; -降序 select * from student order by birthday desc,xh asc; -按birthday 降序 按xh升序(asc/默认) select * from student w

15、here sex=女 or birthday=1999-02-01; select * from student where sex=女 and birthday=1999-02-01; select * from student where salary 20 and xh B002; (!=),查询 select 2,单行函数 返回值只有一个 分组函数 返回值是多条记录 group by sum avg,ORALCE的函数,字符函数 concat 连接 | 显示dname和loc中间用-分隔 select deptno,dname|-|loc from dept; dual哑元表 没有表需

16、要查询的时候 可以用它 select Hello World from dual; select 1+1 from dual; 查询系统时间 select sysdate from dual; initcap 首字母大写 select ename,initcap(ename) from emp; lower 转换为小写字符 select ename,lower(ename) from emp; upper 转换为大写 update dept set loc=lower(loc); update dept set loc=upper(loc); LPAD 左填充 select deptno,lp

17、ad(dname,10, ),loc from dept; RPAD 右填充 LTRIM 去除左边的空格 RTRIM 去除右边的空格 ALLTRIM 去除两边的空格 replace 替换 translate 转换 select ename,replace(ename,S,s) from emp; 用s去替换ename中的S select ename,translate(ename,S,a) from emp;,单行函数1, ASCII 求ASC码 chr asc码变字符 select ascii(A) from dual; select chr(97) from dual; select He

18、llo|chr(9)|World from dual; t ascii码是 9 n ascii码是 10 select Hello|t|World from dual; substr 字符截取函数 select ename,substr(ename,1,3) from emp; 从第1个位置开始 显示3个字符 select ename,substr(ename,4) from emp; 从第4个位置开始显示后面所有的字符 instr 测试字符串出现的位置 select ename,instr(ename,S) from emp; S第1次出现的位置 select ename,instr(ena

19、me,T,1,2) from emp; 从第1个位置开始 测试T第2次出现的位置 length 字符串的长度 select ename,length(ename) from emp;,单行函数2, sysdate 系统时间 select sysdate from dual; select to_char(sysdate,yyyy/mm/dd hh24:mi:ss) from dual; select to_char(sysdate,DDD) from dual select to_char(sysdate,D) from dual select to_char(sysdate,DAY) fro

20、m dual select to_char(sysdate,yyyy-mm-dd) from dual; select to_char(sysdate,yyyy年mm月dd日 hh24:mi:ss) from dual; select from dual; select to_char(sysdate,SSSSS) from dual; -从今天零点以后的秒数,日期和 时间函数, ADD_MONTHS 添加月份 得到一个新的日期 select add_months(sysdate,1) from dual; select add_months(sysdate,-1) from dual; se

21、lect trunc(sysdate)-to_date(20050101,yyyymmdd) from dual; select add_months(sysdate,12) from dual; 一年以后的今天 select add_months(sysdate,-12) from dual; 一年以前的今天 trunc(sysdate) 截取年月日 select sysdate+2 from dual; 数字代表的是天数 两个日期之间的差值代表天数, last_day 某月的最后一天 select last_day(sysdate) from dual; select add_months

22、(last_day(sysdate)+3,-1) from dual; 本月第3天的日期 months_between 两个日期之间的月数 select months_between(sysdate,2005-02-01) from dual; 方向 sysdate - 2005-02-01 select months_between(2005-02-01,sysdate) from dual;,to_char 把日期或数字类型变为字符串 select to_char(sysdate,hh24:mi:ss) from dual; select to_char(sysdate,yyyymmdd

23、hh24:mi:ss) from dual; select sal,to_char(sal,L9,999) from emp; L本地货币 to_number 把字符串变成数字 select to_number(19990801) from dual; to_date 把字符串变成日期 select to_date(19800101,yyyymmdd) from dual; select to_char(to_date(19800101,yyyymmdd), yyyy年mm月dd日) from dual;,转换函数,ceil(x) 不小于x的最小整数 ceil（12.4) 13 ceil(-1

24、2.4) -12 floor(x) 不大于x的最大整数 floor(12.5) 12 floor(-12.4) -13 round(x) 四舍五入 round(12.5) 13 round(12.456,2) 12.46 trunc(x) 舍去尾数 trunc(12.5) 12 trunc(12.456,2) 12.45 舍去日期的小时部分 select to_char(trunc(sysdate),yyyymmdd hh24:mi:ss) from dual; mod(x,n) x除以n以后的余数 mod(5,2) 1 mod(4,2) 0 power(x,y) x的y次方 select p

25、ower(3,3) from dual;,数学函数,求最大值 select greatest(100,90,80,101,01,19) from dual; 求最小值 select least(100,0,-9,10) from dual; 空值转换函数 nvl(comm,0) 字段为空值 那么就返回0 否则返回本身 select comm,nvl(comm,0) from emp; comm 类型和 值的类型是 一致的,混合函数,decode 选择结构 (if . elseif . elesif . else结构) 要求: sal=800 显示低工资 sal=3000 正常工资 sal=50

26、00 高工资 只能做等值比较 select sal,decode(sal,800,低工资,3000,正常工资,5000,高工资,没判断) from emp; 表示如下的if else 结构 if sal=800 then 低工资 else if sal =3000 then 正常工资 else if sal = 5000 then 高工资 else 没判断 end if sal 800 sal -800 0,复杂的函数,判断正负 sign(x) x是正 1 x是负 -1 x是0 0 select sign(-5) from dual; 如何做大于小于的比较？ sal1000 显示低工资 sal

27、-10000 sign(sal-1000) = -1 1000=sal=3000 正常工资 3000sal=5000 高工资 select sal,decode( sign(sal-1000),-1,低工资, decode(sign(sal-3000),-1,正常工资, 0,正常工资,1, decode(sign(sal-5000),-1,高工资,高工资) ) as 工资状态 from emp; 一般的情况 decode(x,y1,z1,y2,z2,z3) if x= y1 then z1 else if x = y2 then z2 else z3 end if,返回值是多条记录 或计算后的

28、结果 group by sum avg 计算记录的条数 count select count(*) from emp; select count(1) from emp; select count(comm) from emp; 字段上count 会忽略空值 comm不为空值的记录的条数 统计emp表中不同工作的个数 ? select count(distinct job) from emp; select distinct job from emp; select distinct job,empno from emp; select job,empno from emp; 得到的效果是一样的,distinct 是消去重复行 不是消去重复的列,分组函数,group by 分组统计 -在没有分组函数的时候 -相当于distinct 的功能 select job from emp group by job; select distinct job from