oracle学习笔记
第一集：
#目前主流数据库 微软 sqlsever and access mysql ad2 sybase informix oracle #小型数据库：access：负载量小 100人，安全性要求不高 #中型数据库：mysql，sqlsever ：负载在5000到15000 #大型数据库：sybase，oracle，db2：负载可以可以处理海量的 sybase《oracle《db2 #用什么数据库？ 1.项目规模：a、负载量多大（用户量） 2.成本 3.安全性 oracle认证： 第二集 oracle安装 数据库标识 myOracle
#默认产生两个用户 a.sys------->change_on_install (超级用户，具有最高权限，sysdba角色，create database的权限) b.system---->manager(管理操作员，权限很大。具有sysoper角色，createdatebase权限)
#一般来说对数据库维护，使用system用户就可以了
第三集，oracle的启动、配置 #Oracle 的数据库是 多户 ---->到一个实例数据库 ①不同用户对表有不同的权限； #sqlserver 一用户----->多个数据库 #启动 ①OracleServer+数据库名； ②启动oracleOraHome90TNSListener
#卸载 ① #oracle管路工具的介绍
自带的工具软件，主要于执行sql语句，p1\sql快。 步骤： 1）开始-->oracle orachome90-->appliacation development --.sql*plus 2）在运行栏输入sqlpusw 即可 3)程序-->Oracle orahome 90-->appplication development -->sql*plus worksheet
oracle企业
主机字符串：即数据库名称；
#https://localhost:5500/em 可以查看数据库运行状态，进行新建表空间和用户配置
#show user;
#plSqlDeveloper
#scott/tiger #连接命令 1)conn 用法：conn 用户名/密码@网络服务器名 当特权用户身份连接时，必须带上 as sysdba 或是 as sysoper conn system/manager #断开连接命令 ：disc #修改密码命令：passw # 退出命令：exit #文件操作命令 1）start和@ //运行脚本 start d：\aa.sql； 2）edit ：编辑指定文件 3）spool：将sql*plus屏幕上的内容输出到指定文件中去。 spool d：\b.sql 最后输入 spool off
#设置显示行的宽度：set linesize 120; 分页设置：set pagesize 5;（设置每页的行数，默认为14）
第三节oracle 用户管理
#创建一个新的用户使用create user语句，一般是具有dba的权限才能使用 create user xiaoming identified by m123；
#给用户修改密码 需要有dba的权限或者是拥有alter user系统权限； password xiaoming；
#删除用户 一般以dba的身份删除某用户，不能自杀；如果其他用户去删除用户需要drop user的权限。
*在删除用户时，如果删除的用户已经创建了表，那么就需要带一个参数。（cascade）。 列入：drop user 用户名 【cascade】
#创建的新用户是没有任何权限的，甚至连登陆数据库的权限都没有，需要为其指定相应的权限，给用户赋予权限使用grant命令，回收权限使用revoke。 *grant connect to xiaoming #权限： 系统权限（用户对数据库访问的权限）和 对象权限（用户对其他用户的数据对象操作的权限select、insert、update） #数据对象：用户创建的表、视图、索引、触发器。。。。。 #角色：自定义角色和预定义角色 dba角色； connect角色； resource角色（可以在任何表空间建表）；
#创建表 *grant resource to xiaoming（授予创建表的全权限） create table test（userId vachar2（30），userName vachar2（30））；
#希望用户去查询scott的emp表； *grant select on emp to xiaoming #grant select on emp xiaoming//授权给小明
#select *form scott.emp；
#收回权限：revoke select on emp from xiaoming
#权限的传递（权限的维护）： 小明用户可以查询scott的emp表，还希望小明可以把这个权限继续传给其他用户。
*如果是对象权限，就加入with grant option *grant select on emp to xiaoming with grant option； 如果是系统权限，就加 grant connect to xiaoming with admin option；
#使用profile 管理用户口令：profile是口令限制，资源限制的命令集合，当建立数据库时，oracle就会自动建立名称为default的profile 。当建立用户没有指定profile选项，oracle就会将default分配给用户。 1）账户锁定：指用户登录时最多可以输入密码的次数，也可以指用户锁定的时间，一般用dba的身份去执行该命令。
//①列子：创建profile文件 create profile lock_account limit//lock——account代表名称 failed_logion_attempts 3 password_lock_time 2; //②指定用户 alter user xiaoming profile lock_account;
2）给账户解锁 *alter user xiaming account unlock；
3）终止 为了让用户定期修改密码，可以使用终止口令来执行；用dba身份来操作 *create profile myprofile limit password_life_time 10 password_garce_time 2;//要求用户每隔10天修改自己的登录面膜。宽限2天。
4）口令历史 希望用户在修改密码时，不能使用以前用过的密码，可以使用口令历史。 *create profile password_history limit password_life_time 10 password_grace_time2password_reuse_time 10;
5)删除profile *drop profile password_history[cascade];
第06讲 oracle 表的管理
#表名和列的命名规则 1）必须以字母开头 20不能超过30字符 3）不能使用oracle保留字
#oracle的数据类型 1）char 定长 最长2000字符 2)varchar2 变长 最大4000字符 3）clob 字符型大对象 最大4g
4）数字类型 number 范围-10的-38~10的38次方，可以表示整数，也可以表示小数
5）日期类型 date 包含年月日和分秒 timestamp是对date数据类型的扩充
6）图片（包括声音、视频） blob 二进制数据
#怎样创建表 ---学生表 create table student（ StNumber number（4）， StName varchar2（20）， sex char（2）， birthday date， sal number（7,2） ）； #删除表 drop table students；
---班级表 CREATE table classes( classId number(2), cname varchar2(40));
#添加字段 alter table student add （classId number（2））；
#查看表 desc student
#修改字段的长度 alter table student modify （xm varchar2（30））;
#删除一个字段//尽量不使用 alter table student drop column sal;
#修改表名 rename student to stu;
#添加数据 所有字段都添加 insert into student values （‘A001’，‘张三’，‘男’，‘11-12月-1997’，’222.2‘，21） 日期的格式：oracle默认的日期格式‘DD-MON-YY’改日期的默认格式： alter session set nls_date_format=‘yyyy-mm-dd’； #插入部分字段 insert into student （xh，xm，sex）value （‘’，‘’，）
#插入空值 insert into student （xh，xm，sex）value （‘’，‘’，null）
#查询没有生日的人 select *from student where birthday is null；//is not null;
#修改一个字段 update student set sex=‘女’ where=‘A001’;
#修改多个字段 update student set sex=‘女’， birthday=‘1980-04-01’ where=‘A001’; 修改字段加逗号就可以。
#修改含有null用is修改。
#删除数据 delete from student；//删除了表里面的内容，但是表的结构还在；可以通过日志恢复；
#在删除之前添加回滚点（设置保存点） savepoint a； #回滚命令 rollback to a；
#删除表的结构和数据 drop table student；
#删除一条记录 delete from student where xh=‘A001’；
#删除表中的所有记录，表结构还在，不写日志，无法找回删除记录，熟读快 truncate table student；
#第八讲 oracle表查询
#查看表的结构 desc emp
#查询指定列 select ename ，sal,job,deptno from emp;
#set timing on；打开操作时间；
#insert into users (userid,username,userpassword) select *from users;自动增长数据；
#如何取消重复行 select distinct deptno，job from emp；
#如何smith 的薪水，工作，所在部门 select deptno,job,sal from emp where ename=‘SMITH‘;
#使用算术表达式
select sal*13，ename from emp；
#给列取别名 select sal*13+nvl（comm,0)*13 ”年工资“，ename from emp；
#如何处理null值：使用nvl函数来处理
#显示工资高于3000的员工 *select ename，sal from emp where sal>=3000;
#显示在1982后入职的职员 *select ename,hiredate from emp where hiredate>‘1-1月-1982‘
#显示工资在2000~2500的员工 *select ename，sal from emp where sal>=2000&sal在<=2500;
#如何使用like操作符 %表示任意0到多个字符； _表示任意单个字符； #如何显示首字符为S的员工姓名和工资 *select ename，sal from emp where ename like ‘S%’；
#如何显示第三个字符为S的员工姓名和工资 *select ename，sal from emp where ename like ‘__o%’；
#在where条件中使用in *select *from emp where empno in（123,213,324）；
#如何显示没有上级的员工 *select *from emp where mgr is null；
#第09讲
#使用order by字句
#如何按照工资从低到高显示员工信息 *select *from emp order by sal #从高到底 *select *from emp order by sal desc；
#按照部门号升序而雇员的工资降序； *select *from emp order by deptno ，sal desc；
#使用列的别名排序 *select enamel，sal*12 ”年薪“ from emp order by “年薪”；
#oracle表复杂查询 #数据分组----max，min，avg，count
#如何显示所有员工最高最低工资 *select max（sal），min（sal） from emp；
#如何显示所有员工最高最低工资 *select ename,sal from emp where sal=(select min(sal) from emp);
#显示工资高于平均工资的员工信息 *select *from emp where sal>(select avg(sal) from emp);
#group by 和group by group by用于对查询的结果分组统计 group by用于限制分组显示结果
#显示每个部门的平均工资和最高工资 *select avg(sal),max(sal),deptno from emp gruop by deptno;
#显示每个部门的每种岗位的平均工资和最低工资 *select avg(sal),max(sal),deptno,job from emp group by deptno,job;
#显示平均工资低于2000的部门 *select avg(sal),max(sal),deptno from emp gruop by deptno having avg(sal)<2000 #注意 1）分组只能出现在选择列里面，或者出现在having 。orderby里面； 2）先出现order by再有having；
#显示平均工资高于2000的部门，再按avg排序 *select avg(sal),max(sal),deptno from emp group by deptno having avg(sal)>2000 order by avg(sal);
#多表查询：两个两个以上的表或是视图的查询。
#显示雇员工资所在部门的名字 *select a1.ename,a2.dname from emp a1,dept a2 where a1.deptno=a2.deptno;
#规定：多表查询的条件是 至少不能少于 表的个数-1；
#第10讲多表查询 #显示部门号为10的部门名、员工名和工资 *select a1.dname,a2.ename,a2.sal from dept a1,emp a2 where a1.deptno=a2.deptno and a1.deptno=10;
#显示各员工的姓名，工资，及其工资级别 #select a1.ename,a1.sal,a2.grade from emp a1,salgrade a2 where a1.sal between a2.LOSAL and a2.HISAL;
#自连接：在同一张表的连接查询；
#显示某员工的上级领导的姓名//把一张表看作两张 *select woker.ename,?boss.ename from emp worker,emp boss where wokker.mgr=boss.empno and worker.ename=KING;
#子查询(嵌套查询)：嵌入在其他sql中的select语句；
#单行子查询：只返回一行数据的查询 显示SMITN同一部门的员工
*查询SIMITH的部门号 select deptno from emp where ename=‘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 deptn=10 );
注意：where job 后面写=还是in 取决于是单行还是多行，单用=；
#在多行子查询中使用all 操作符
#如何显示工资比部门30的所有员工的工资高的员工的姓名。工资和部门 *select ename,sal,dept from emp where sal> all(select sal from emp wherr deptno=30); *方法2 select *from emp where sal>(select max(sal) from emp where deptno=30);
#在多行子查询中使用any 操作符
显示工资比部门30的任意一个员工的工资的姓名、 *select ename,sal,dept from emp where sal> any(select sal from emp wherr deptno=30); *方法2 select *from emp where sal>(select min(sal) from emp where deptno=30);
#多列子查询
#如何查询与simth 部门和岗位相同的员工 //查询他的部门 select deptno,job from emp where ename=‘SMITH‘; //显示 select *from emp where (deptno,job)=(select deptno,job from emp where ename=‘SMITH‘);
#在from子句中使用子查询 #显示高于自己部门平均工资的员工的信息 //1、查询部门的平均工资和部门号 select deptno,avg(sal) mysal from emp group by deptno; //把上面的查询看作是一张子表 select a2.ename,a2.sal,a2.deptno,a1.mysal from emp a2,(select deptno,avg(sal) mysal from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sal>a1.mysal;
#分页查询 *分页一共有三种方式 1）rownum分页 select *from emp 2）显示rownum select a1.*,rownum rn from (select *from emp) a1 3）
取6~10行 select a1.*,rownum rn from (select *from emp) a1;//rn行号 步骤 1.select a1.*,rownum rn from (select *from emp) a1 where rownum<10; 2.select *from（select a1.*,rownum rn from (select *from emp) a1 where rownum<=10） where rn>=6;
注意： a。若指定查询列，只需修改最里层的查询 select *from（select a1.*,rownum rn from (select ename，sal from emp) a1 where rownum<=10） where rn>=6; b。排序 只需修改最里层的查询 select *from（select a1.*,rownum rn from (select ename，sal from emp order by sal ) a1 where rownum<=10） where rn>=6;
#用结果创建新表 这个命令是一个快捷的建标的方法 create table my table (id,name,sal,deptno) as select empno,ename,sal,job,deptno from emp; *比如create table Myemp1 (id,name,sal) as select empno,ename,sal from emp;
#合并查询：操作符号 union ，union all，intersect，minus 1）union 该操作用于取得两结果集的并集。会自动去掉结果集中重复行。 *select ename,sal,job from emp where sal >2500 union select ename,sak ,job from emp where job=‘manager‘; 2)union all:不会取消重复行 3）intersect 操作用于取得两结果集的交集 4）minus 操作用于取得两结果集的差集
#创建新的数据库 1）工具向导：adca 2）手工创建
-----------第12讲java操作oracle--------
#java连接oracle？？？？？ #
#使用特定格式插入日期 *使用to_date函数：用户指定添加日期的格式 *insert into emp values(9998,‘小红‘,‘MANAGER‘,‘7782‘,to_date(1998-12-12,‘yyyy-mm-dd‘),78.9,55.33,10);
#当用values子句时一次能插入多值 *create table kkk(myid number(4),myname varchar2(50),mydept number(5)); insert into kkk(myid,muname,mydept)select empno,ename,deptno from emp where deptno=10;
#用子查询更新数据 *希望员工scott的岗位、工资、补助与smith一样； *update emp set (job,sal,comm)=(select job,sal,comm from emp where ename=‘SMITh‘) where ename=‘SCOTT‘;
--------oracle事务处理------
#事物用于保证数据的一致性，它由一组相关的dml语句组成，该组的dml语句要么全部成功，要么全部失败；
#事物和锁：当执行一事务操作时（dml语句），oracle会在被作用的表上加锁，防止其他用户该表的结构。 #锁：文件锁。。。
#提交事务 *当使用commit语句可以提交事务，当执行commit语句后，会确认事务的变化。结束事务。删除保存点、释放锁。当使用commit语句结束事务之后，其他会话将卡哇伊查看到事务变化后的新数据
#回退事务：保存点是事务中的一点，用于取消部分事务，当结束事务时，会自动删除该事物所定义的所有保存点。当执行rollback时，通过保存点可以回退到指定的点。
#savapoint a1; #delete from emp where empno=9996; #savapoint a2; #delete from emp where empno=9999; #rollback to a2;
#第14讲 oracle的函数
#只读事务：只允许执行查询的操作，而不允许执行任何其他dml操作事务，使用只读事务可以确保用户只能取得某时间点的数据。其他会话可以提交新事务，但是只读事务将不会取得最新数据变化 #设置只读事务：set transaction read only;
#sql函数的使用 1）字符函数 lower(char)：将字符串转换为小写格式； upper(char)：将字符串转换为大写格式； length(char):返回字符串的长度； substr(char,m,n):取字符串的子串； replace(char1,search_string,replace_string) instr(char1,char2,[,n[,m]])取子串在字符串的位置
*将员工的名字按小写的方式显示 select lower(ename) from emp;
*将员工的名字按大写的方式显示 select upper(ename) from emp;
*显示员工名字正好为5个的字符的员工； select *from emp length(ename)=5;
*显示所有员工姓名的前三个字符； select substr(ename,1,3) from emp;
*以首字母大写的方式显示所有员工的姓名； select upper(substr(ename,1,1)) from epm;//首大写 select lower(substr(ename,1,length(ename)-1)) from epm;//小写 select upper(substr(ename,1,1)) || lower(substr(ename,1,length(ename)-1)) from emp;合并
#替换 select replace(ename,‘A‘,‘我是老虎‘)；
--------数学函数-------- #包括：cos cosh,exp,il,log,sin,round,trunc,mod,floor,ceil #round(n,[m]) 该函数用于执行 四舍五入； #trunc(n,[m])该函数用于截取数字；如果省掉m，就截取小数部分，如果m是正数就截取到小数点的m位后，如果m是负数就截取到小数点的前m位， #mod取模； #floor(返回小于或等于n的最大整数)；向上取整 #ceil(返回大于或等于n的最小整数)；
*显示在一个月为30天的情况所有员工的日薪，忽略余数；
#SQL 函数的使用--日期函数 #日期函数用于处理date类型的数据。默认情况下日期的格式是dd-mon-yy. 1)sysdate:该函数返回系统时间； *select sysdate from dual; 2）add_months(d,n):加月份 *查找八个月多的员工 select *from emp where sysdate>add_months(hiredate,800) 3)last_day(d):返回指定日期所在月份额度最后一天 显示员工加入公司的天数 select sysdate-hiredate "入职天数",ename from emp; 4)找出各月倒数第3天受雇的所有员工 select hiredate, ename from emp where last_day(hiredate)-2=hiredate;
#转换函数
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
