Oracle数据库学习日记实用性最强的Oracle学习总结.docx

上传人:太** 文档编号:66981913 上传时间:2022-12-23 格式:DOCX 页数:50 大小:120.93KB
返回 下载 相关 举报
Oracle数据库学习日记实用性最强的Oracle学习总结.docx_第1页
第1页 / 共50页
Oracle数据库学习日记实用性最强的Oracle学习总结.docx_第2页
第2页 / 共50页
点击查看更多>>
资源描述

《Oracle数据库学习日记实用性最强的Oracle学习总结.docx》由会员分享,可在线阅读,更多相关《Oracle数据库学习日记实用性最强的Oracle学习总结.docx(50页珍藏版)》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、Oracle数据库学习日记实用性最强的Oracle学习总结Oracle数据库学习错误!未定义书签。1 基本使用71. 1 常用命令71. 1Connect/Disconnect 数据库连接命令71. 1. 2PL/SQL连接数据库配置,Oracle客户端的配置文件71. 3酉己置 Oracle 数据库监听 Assistant82. 1. 4Password 修改密码命令81.5Show显示登录用户83. 1. 6Clear 清屏81.7Exit退出命令84. 1.8文件操作命令81.9&交互式命令85. 1. 10显示与设置环境的变量82 用户管理92. 1 用户管理91. 1Create u

2、ser 创建用户92. 1. 2Password 修改密码91. 3Drop user 删除用户93. 1.4给予与收回权限91.5Profile 管理用户口令104. 1.6给账户解锁101.7终止口令105. 1.8口令历史111. 9删除 profile 文件113数据类型113. 1 字符型111. 1Char定长字符113. 1.2Varchar2 变长字符11Grant update on emp to abc ; -Scott用户将emp表的修改权限授予给abc用户Grant all on emp to abc ; Scott用户将emp表的所有权限授予给abc用户3)回收权限命

3、令revoke,谁授予的权限谁收回Revoke select on emp to abc ; -Scott用户将enip表的查询权限从abc用户收回Revoke update on emp to abc ; Scott用户将emp表的修改权限从abc用户收回Revoke all on emp to abc ; -Scoll用户将emp表的所有权限从abc用户收回4)被授予权限用户继续授予该权限给其他用户,假如是对象权限,后面就加入with grant option ;假如是系统权限,后面就加入with adm i n option ;假如收回某用户的权限则该用户授予给其他下级用户该权限将全部收

4、回Grant select on emp to abc wi th grant option ; 一一 Scott 用户将 6mp 表的查询权限授予给 abc用户,同时让abc用户继续给其他用户授权Grant select on Scott, emp to abcl ; abc 用户将 Scott, omp 表的查询权限授予给 abc 1 用户Profile管理用户口令Profile是口令限制,资源限制的命令集合,当建立数据库时,Oracle会自动建立名为default 的profile,当建立用户没有指定profile选项,那Oracle就会将default分配给用户,账户锁定:指定该账户登

5、录时最多能够输入错误密码的次数,也能够指定用户锁定的时间(天), 通常用dba的身份去执行该命令。案例:指定abc账户登录时最多能够输入错误密码3次,锁定的时间2天Sqlcreate profile lock_a limit failed_login_attempts 3 password_lock_time 2;Sqlal ter user abc prof i le lock a;一给用户 abc 指定profile 选项给账户解锁Sqlalter user abc account unlock;-给用户 abc 解锁终止口令为了让用户定期修改密码能够使用终止口令的指令来完成,同样这个命令

6、也需要dba身份来操作。案例:创建一个profile文件,要求该用户每隔10天要修改自家的登录密码,宽限期为2天。Sqlcreale profi1e lock al 1imi t password li10 password graceime 2;SqDalter user abc profile lock_al;口令历史假如希望用户在修改密码时,不能使用往常使用过的密码,可使用口令历史,这样Oracle就会 将口令修改的信息存放到数据字典中,这样当用户修改密码时,Oracle就会对新旧密码进行比较, 当发现新旧密码一样时,就提示用户重新输入密码。案例:建立profile文件,password

7、_reuse_time 指定口令可重用时间,超过该时间可再次使用:Sqlcrcato profile password h limit password life time 10 password grace time 2 password reuse time 10;分配给用户:SqDalter user abc profile password_h;删除profile文件删除profile文件,假如该profi 1 e已经分配给客户需在后面加参数cascade,所有受到该profile 文件限制的用户全部解除该限制。SqDdrop profile password_h cascade;数据

8、类型字符型Char定长字符char(10)定长字符最大2000字符,字符数不足用空格不足,查询速度快,适合定长数据,如 身份证、手机号码等;Varchar2变长字符varchar (20)变长字符 最大4000字符;Clob字符型大对象clob(30000)字符型大对象,最大4G,能够存图片、视频等;数值型Number数值型number (5, 2)能够表示TO的38次方到10的38次方,如number (5)表示5位整数,number2) 表示3位整数2位小数;日期Date通常日期(年、月、日,时、分、秒)包含年月日与时分秒;timestamp精确时间时间能够精确到更小的单位图片类型Blob

9、可存储图片、视频、声音blob二进制数据,最大4G,能够存图片、视频、声音等;表格管理创建表(注意字母大小写,通常为大写)学生表:create table student (,一学生xh number (4), -学号xm varchar2 (20), -姓名xb char (2),一性别birthday date,-出生日期val number (7, 2) 一奖学金);班级表:create table class (一班级classid(4), -斑级编号name氏。)、-班级名称);添加一个字段a 1 ter table student add (classic! number (2);

10、修改字段的长度alter table student modify (xm varchar2(30);删除一个字段alter table student drop column sal ;实际过程中不要轻易删除字段。修改表的名字rename student to stu;一将STUDENTf 修改为STU删除表drop tab I e student; -删除STUDENT表所有字段都插入数据insert into student values (1003,张三,01-5 月-05, 10);日期格式默认为:DD-M0N-YY该日期格式:al ter session set nIs date

11、format =YY期-MM-DD;日期格式能够任意修改,Y年份,M月份,D日;插入部分字段insert into student (xh, xm, sex) values (1004,李四,女);插入空值insert into student (xh, xm, sex, birthday) values (1005,杨慧,女,null);查询空值select * from student where birthday is null ; -查询空值select * from student where birthday is not nul 1 ; 一-查询非空值修改字段update stu

12、dent set sex=男where xh=,1004,; -修改个字段update student set sex=男,birthday=1980-04-01 where xh=1004;一修改多个字段比如:update student set sal=sal*l. 5 where sex=男;-所有男性员工薪水加 1. 5 倍update student set birthday is null where xh=,1004,;一修改为空值删除数据delete from student,-删除所有记录,表结构还在,写日志,能够恢复的(回滚),速度慢 delete from student

13、 where xh=1005; -删除一条记录 drop table student ; 一删除表结构与数据truncate table stxident;一删除表中的所有记录,表结构还在,不写日志,无法找回删除的记录,速度快;savepoint AA ; -创建回滚点AArollback to AA; 一回滚到AA点,需先创建回滚点简单表查询查询表的结构Sqldesc dept ; 一查看dept表的结构查询表的指定列与所有列Select * from emp;-查询全部列Select ename, sal, job from emp ; -查询指定列注意:查询过程中尽量少用查询全部列,以节

14、约查询时间,提高查询效率Distinct取消重复行Select distinct deptno, job from emp ;疯狂复制Insert into users (userid, username, userpass) select * from users;一向 users 表中添力口users 自己表中的数据案例1 :查询SMITH的薪水,工作,所在部门SeIect deptno, job, sal from emp where ename=SMITHJ ;注意:Select语句中的大小写不区分,但是单引号中的内区分大小写Select中使用列的别名Select ename “姓名,

15、sal*12 as “年收入 from emp;一从 emp 表中查询 ename 别名姓名”、 sal*12别名“年收入Select中使用算术表达式注意:在select运算过程中假如其中有一个值为NULL则整个值为NULLSelect ename , sal*12 from emp;一查询年工资Select sal*12+comm*12 年总工资,ename, comm from emp ;一年总工资等于年工资加年奖金能够使用进行运算使用NVL函数处理NULL值Nvl(comm, 0);假如comm值为空(NULL)则返回值为后面的0,假如comm值不为空则返回comm的值Select sa

16、l*12+nvl (comm, 0)*12 “年总工资”,ename, comm f,rom emp ;一年总工资等于年工资加年 奖金字符串连接符Select ename I I 是 job from emp ; 一将查询出的值与字符串连接在一起Where查询条件1948. 78+120子句案例1 :如何显示工资高于3000的员工Se 1 eel ename, sal from emp where sal3000;案例2 :如何查找1982. 1. 1后入职的员工Select ename, hiredate from emp where hiredate IT 月T981;案例3 :如何显示工

17、资在2000到2500的员工情况Select ename, sal from emp where sal=2000 and sal二2500;Like操作符% :表示任意0到多个字符;_ :表示任意单个字符。案例I :如何显示首字符为S的员工姓名与工资Se1ect ename, sal from emp where ename 1ike 500 or job=MANAGER * ) and ename like J%;Order by排序语句Order by 默认升序(asc),降序(desc) o如何按照工资的从低到高的顺序显示雇员的信息Select * from emp order by

18、sal;按照部门号升序而雇员的工资降序排列Select * from emp order by deptno, sal desc;使用列的别名排序注意:别名需要加双引号,英文不用加双引号,给列加别名时中间能够加as。Select ename, sal*12 “年薪” from emp order by 年薪”asc;复杂表查询数据分组 max, min, avg, sum, count显示所有员工中最高工资与最低工资Se1 eel max(sal), min(sal) (Yom emp;显示工资最高员工的名字,工作岗位Select ename, sal from emp where sal二(

19、Select max(sal) from emp)显示所有员工的平均工资与工资总与Select avg(sal), sum(sal) from emp;计算共有多少员工Select count(*) from emp ; 显示工资高于平均工资的员工信息Select * from emp where sal(Select avg(sal) Irom emp);Group by 与 having 子句Group by用于对查询的结果分组统计,分组查询中分组字段务必出现在查询结果中Having子句用于限制分组显示结果显示每个部门的平均工资与最高工资Se1ect avg(sal), max(sal),

20、 deptno f rom emp group by deptno;显示每个部门的每种岗位的平均工资与最低工资Select avg(sal), min(sal), deptno, job from emp group by deptno, job;显示平均工资低于2000的部门号与它的平均工资Select avg(sal), max(sal), deptno from emp group by deptno having avg(sal)2000;数据分组总结1、分组函数只能出现在选择列表、having、order by子句中;2、假如在select语句中同时包含group by, havin

21、g, order by那么他们的顺序是group by, having, order by ;3、在选择列中假如有列、表达式与分组函数,那么这些列与表达式务必有一个出现在group by子句 中,否则就会出错案例1:显示平均工资低于2000的部门号与它的平均工资并按平均工资排序Select avg(sal), max(sal), deptno from emp group by deptno having avg(sal)2000 order by avg(sal);多表查询多表查询,between and语句多表查询是指基于两个与两个以上的表或者是视图的查询,多表查询查询条件不能少于表的个数

22、减1 O案例1:显示雇员名,雇员工资及所在部门的名字;【笛卡尔集】Select al. ename, al. sal, a2. dname from emp al dept a2 where al. deptno=a2. deptno;案例2 :显示部门号为10的部门名、员工名与工资Select al. ename, al. sal, a2. dname from emp al dept a2 where al. deptno=a2. deptno andal.deptno=10;案例3 :显示部门号为10的部门名、员工名与工资Select al. ename, al. sal, a2. gr

23、ade from emp al, salgrade a2 between a2. losal and a2. hisal;案例4:显示雇员名、雇员工资及所在部门的名字,并按部门排序Select al. ename, al. sal, a2. dname from emp al dept a2 where al. deptno=a2. deptno order by al.deptno;自连接自连接是指在同一张表的链接查询显示FORD,上级的姓名批注nl: SavepointSelect al. ename, a2. ename from al. emp, a2. emp where al. m

24、gr=a2. empno and al. ename=, FORD;左连接与右连接左连接与右连接以如下方式来实现:查看如下语句:SELECT emp name, dept_name FORM Employee, Department WHERE Employee. emp_deptid(+)= Department, deptid此SQL使用了右连接,即“(+)”所在位置的另一侧为连接的方向,右连接说明等号右侧的所有记录 均会被显示,不管其在左侧是否得到匹配,也就是说上例中不管会不可能出现某个部门没有一个员工 的情况,这个部门的名字都会在查询结果中出现。反之:SELECT emp_name,

25、dept_name FORM Employee, Department WHERE Employee. emp_deptid = Department, deptid(+)则是左连接,不管这个员工是否具有一个能在Deparlment表中得到匹配的部门号,这个员工的记录 都会被显示 通常的相等连接select * from a, b where a. id = b. id;这个是内连接子查询子查询是指嵌入其他sql语句中的select语句,也叫嵌套查询注意:数据库在执行sql时是从后往前执行,有括号先执行括号里面的语句,包含多个条件时,尽量 将能过滤数据量大的条件放在后面,以提高执行效率单行子查

26、询单行子查询是指子语句只返回一行数据的子查询语句如:显示与SMITH同一部门的所有员工SELECT * from emp where deptno=(Select deptno from emp where ename=SMITH);多行子查询多行子查询指子语句返回多行数据的子查询如:查询与部门10的工作相同雇员的名字、岗位、工资、部门号SELECT * from emp where job in (Select distinct job from emp where deptno=10)显示工资比部门30的所有员工的工资高的员工的姓名、工资与部门号Select ename, sal, dep

27、tno from emp where salall (select sal from emp where deptno=3);Select ename, sal, deptno from emp where sal (select max (sal) Irom emp where deptno=3);一 效率比第一种高使用any操作符查询显示工资比部门30的任意一个员工的工资高的员工的姓名、工资与部门号Select ename, sal, deptno from emp where salany(select sal from emp where deptno=30);Select ename

28、, sal, deptno irom emp where sal (select min (sal) I Yom emp where deptno=30);多列子查询多列子查询是指查询返回多个列数据的子查询语句查询与SMITH的部门与岗位完全相同的所有雇员1.3Clob字符型大对象113.2 数值型112. 1Number 数值型113.3 日期123. 1Date通常日期(年、月、日,时、分、秒)123. 3. 2 timestamp 精确时间123.4图片类型121Blob可存储图片、视频、声音124 表格管理124.1 1创建表(注意字母大小写,通常为大写)124.2 添加一个字段124

29、.3 修改字段的长度124.4 删除一个字段134.5 修改表的名字134.6 删除表134.7 所有字段都插入数据134.8 插入部分字段134.9 插入空值134.10 10查询空值134.11 11修改字段134.12 删除数据13简单表查询145. 1查询表的结构146. 2查询表的指定列与所有列147. 3Distinct 取消重复行148. 4疯狂复制149. 5Select中使用列的别名1410. 6Select中使用算术表达式1411. 使用NVL函数处理NULL值1512. 字符串连接符15Soleci *rom EMP where (deptno, job) = (se 1

30、 eci deptno, job Il-oin emp whereename= SMITH):一注意列名前后顺序对应From子句中使用子查询当在from子句中使用子查询时,该子查询会被作为一个视图来对待,因此叫做内嵌图,当在 from子句中使用子查询时,务必给予查询指定别名。案例1 :显示高于自己部门平均工资员工的信息Select al. ename, al. sal, al. deptno, a2. mysal from emp al, (Select avg(sal) mysal, deptno from emp group by deptno) a2 where al. deptno=a

31、2. deptno and al. sala2. mysal;分页查询Oracle分页方式有三种:1、 rownum第一步:Select al.*, rownum rn from (select * from emp) al where rownuni=10;-找出前 10 彳亍i己录;第二步:Select * from (Select al.*, rownum rn from (select * from emp) al where rownum=6;一找出 6-10 行记录;案例:A.指定查询列,只需修改最里层的子查询Select * from (Select al.*, rownum r

32、n from (select ename, sal from emp) al where rownum=6;B.按顺序查询,只需修改最里层的子查询Select * from (Select al. *, rownum rn from (select * from emp order by sal) al where rownum=6;C .所有查询修改,只需修改最里层的子查询2、 RowidSelect * from t_xiaoxi where rowid in (select rown rn, rid from (select rowid rid , cid from t_xiaoxi o

33、rder by cid desc) whoro rownum9980) ord(r by cid desc;3、按分析函数Select * from (select t. * , row number() over (order by cid desc) rk from t_xiaoxi t) where rk9980;用查询结果创建新表Create table mytable (id , name, sal, job, deptno) as select empno, ename, sal, job, deptno from emp ;创建表的同时把数据打入新表中合并查询集合操作符号 uni

34、on , union all, intersect, minus ,查询比 and、or 速度快。1、 union该操作符用于取得两个结果集的并表,当使用该操作符时,会自动去掉结果集中的重复行。Select ename, sal, job from emp where sal2500 union select ename, sal, job from emp where job=MANAGER;2、union all该操作与union相似,但是它不可能取消重复行,而且不可能排序3、Intersect使用该操作用于取得两个结果集的交集。4、Minus使用该操作符用于取得两个结果集的差集,它只会显

35、示存在第一个集合中,而不存在第二个集合 中的数据,用一个集合减去另一个集合。Java 操作 Oraclejdbe. odbc桥连接,不能远程连接引sql包Import java. sql. *加载驱动Class. forNanie ( sun. jdbe. odbc. jdbcqdbcDrivorM );得到连接Connection ct=DrivcManager. getConnection(n jdbc:odbc:数据源,用户名”,密码);jdbc. Oracle连接,同意远程连接 引sql包Import java. sql. *加载驱动Class. forName( Oracle, jd

36、bc. Driver. OracleDriver);得到连接Connectiol ct=DriveManager. getConnection(w jdbc:oracle:thin:6127,0.0.1:1521 :数据库名称”用户名“,“密码”);事物set transaction read only;一设为只读事物使用子查询插入数据使用子查询插入数据时,一条Insert语句能够插入大量的数据,当处理行迁移或者者装载外部 表的数据到数据库时,能够使用子查询来插入数据。insert into kkk (myid, myname, mydept) select empno, ename, dep

37、tno from emp where deptno=10;使用子查询插入数据使用update语句更新数据时,既能够使用表达式或者者数值直接修改数据,也能够使用子查询修改 数据。案例1 :使员工SCOTT的岗位、工资、补助与SMITH员工一样Update emp set (job, sal, comm) = (select job, sal, comm from emp where ename=J SMITH ) where ename=scott;Oracle中的事物事物用于保证数据的一致性,它由一组有关的dnil语句构成,该组的dml语句要么全部成功,要么全 部失败,dml语句指数据操作语言

38、,即增、删、改语句,没有查询语句。如:网上转账就是典型的要用事物来处理,用以保证数据的一致性。事物的几个重要操作:1、设置储存点,savepoint a2、取消部分事物,rollback to a3、取消全部事物,rollback函数字符函数lower (char):将字符串转化为小写的格式select lower(ename) from cp;-把所有人的名字按小写显示,多个逗号分开upper (char):将字符串转化为大写的格式select upper(ename) from eg;-把所有人的名字按大写显示,多个逗号分开length (char):返回字符串的长度select * fr

39、om emp where length (ename) =5; 一显示正好有5 个字符的名字substr (char, m, n):取字符串的子串select substr(ename, 1, 3) from eep;-显示所有姓名的前三个字符,中间数值指从第几个字符取值, 后面的数值指取几个字符,1个汉字算1个字符;replace (char 1, serch string, replace string)替换字符串Select replace(ename, A我)from emp;一显示所有姓名,用我替换所有Ainstr (char 1, char2, , n, m)取子串在字符串的位置案

40、例:以首个字符为大写显示所有姓名1)首字母大写显示姓名Select upper (substr (ename, 1, 1) from emp;2)从第二个字母小写显示姓名Select 1 ower(substr(ename, 2, 1 ength(ename)-1) from emp;3)两者合并Se 1 ec I lipper (subs t. r (ename, 1, 1) | | 1 ower (subsi r (ename, 2, 1 engt h (ename) -1) from emp以首个字符为大写显示所有姓名数学函数数学函数的输入参数与返回值的数据类型都是数字类型的,数学函数包

41、含cos, cosh, exp, In, log, sin, sinh, sqrt, tan, tanh, acos, asin, atan, round 等 ;round (n, m)四舍五入该函数用于执行四舍五入,假如省掉叫则四舍五入到整数;假如m是正数,则四舍五入到小数 点的m位后;假如m是负数,则四舍五入到小数点的m位前;select round(sal, 1) from emp;trunc(n, m)截取数字该函数用于截取数字,假如省掉叫 就截去小数部分,假如m是正数就截取到小数点的m位后,假如m是负数,则截取到小数点的前m位;select trunc(sal, 1) from en

42、ip;mod (m, n)取摩如mod (10, 2)值为0, mod (10, 3)值为1,可用函数测试表dual测试;floor(n)向下取最大整数返回小于或者是等于n的最大整数,如floor (55. 66M返回55;select floor(sal, 1) from emp;ceil (n)向上取最小整数返回大于或者是等于n的最小整数如ceil(55.66),返回*561select ceil(sal, 1) from emp;案例:显示在一个月为30天的情况所有员工的日薪金,忽略余数。Select trune (sal/30), ename from emp;Select floor

43、(sal/30), ename from emp;其他数学函数:abs(n)返回数字n的绝对值select abs (-13) from dual ; -求T3的绝对值,dual是函数测试表acos (n)返回数字的反余弦值asin(n)返回数字的反正弦值atan(n)返回数字的反正切cos(n)返回数字的余弦值exp(n)返回。的n次嘉lo晨叫n)返回对数值power (m, n)返回m的n次幕日期函数日期函数用于处理date类型的数据。默认情况下日期格式是dd-mon-yy即12-7月-78To_date 函数能够插入任意格式的日期函数。如:按年-月-日格式插入日期To_date( 198

44、8T2T2 yyyy-nim-dd)sysdate该函数返回系统时间;select sysdate from dual ; -显示当前系统时间,函数测试表dualadd months (d, n);select * from emp where sysdateadd months (hiredato, 8);-查找已入职 8 个月多的员工last_day(d)返回指定日期所在月份的最后一天案例: 显示满10年服务年限的员工的姓名与受雇日期so 1ect ename, hiredate f,rom emp where sysdate=add months(hiredate, 12*10); 关于

45、每个员工,显示其加入公司的天数SeI ect trunc (sysdate-hiredate) “入职天数”,ename from emp ; -对查出的天数取别名入职 天数”,trunc函数截取整天数 找出各月倒数第3天受雇的所有员工Select hiredate, ename from emp where last day(hiredate)-2=hiredate;转换函数转换函数用于将数据类型从一种转为另外一种,在某种情况下,Oracle server同意值的数据类 型与实际的不一样,这时Oracle server会隐含的转化数据类型,但是它并不习惯所有的情况,为了 提高程序的可靠性,我

46、们应该使用转换函数进行转换案例1 :Create table tl (id int);一整数 intInsert into tl values( 0,);-这样Orac 1 e 会自动的将10转换为 10案例2 :Create table t2(id varchar2(10);Insert into t2 values(1);-这样Oracle 会自动的将 1 转换为TTo_char转换函数9:显示数字,并忽略前面0yy :两位数字的年份20049040 :显示数字,如位数不足,则用0补齐yyyy :四位数字的年份2004年.:在指定位置显示小数点mm:两位数字的月份8月分08,:在指定位置显示逗号

展开阅读全文
相关资源
相关搜索

当前位置:首页 > 应用文书 > 解决方案

本站为文档C TO C交易模式,本站只提供存储空间、用户上传的文档直接被用户下载,本站只是中间服务平台,本站所有文档下载所得的收益归上传人(含作者)所有。本站仅对用户上传内容的表现方式做保护处理,对上载内容本身不做任何修改或编辑。若文档所含内容侵犯了您的版权或隐私,请立即通知淘文阁网,我们立即给予删除!客服QQ:136780468 微信:18945177775 电话:18904686070

工信部备案号:黑ICP备15003705号© 2020-2023 www.taowenge.com 淘文阁