1、oracle7天学习经典笔记-*-2010.9.26 oracle第2天2课-建表语句create table student( id number(4), name varchar(20), sex char(2), birthday date, sal number(7,2);-修改表的信息-(1)添加字段alter table student add (score number);-(2)修改字段长度alter table student modify (score number(3,1);-(3)删除一个字段alter table student drop column score;-
2、查看表的结构(注意,此命令只有用在命令窗口才能够显示表的结构信息)desc student-打开显示时间的操作开关(注意,此命令只有用在命令窗口才能够显示表的结构信息,-此命会将后面的查询操作执行时间显示出来)set timing on-查询select *from student-如果查询某个字段为空的信息使用 is关键字select *from student where birthday is null-插入(注意:时间的插入写法)insert into student values(1,yoyo1,男,1-2月-1988,123.11)-转换时间插入的格式输入法,为中国人习惯型的输入格
3、式alter session set nls_date_format=yyyy-mm-dd-转换时间输入格式后的插入写法insert into student values(1,yoyo1,男,1988-3-2,123.11)-修改update 表名 set 字段名1=值1,字段名2=值2. where 条件-设置一个保存点savepoint a-删除表信息delete from student-回滚rollback-回滚后可以查询刚刚删除的信息select *from student-删除-(1)删除表的内容,不会删除自动增长列已经增长到的某个值的记录delete from student
4、-(2)删除表结构drop table student-(3)删除表的内容,速度快,删除后没有日志记录,自动增长列会从1重新开始记录truncate table student-*-2010.9.26 oracle第2天3课select *from empselect *from dept-查询速度的区别-使用 * 的速度比指明 字段名 的速度 慢-查询smith的工作,薪水和部门(注意:SMITH 内容的大小写,关键字不区分大小写)select deptno,job,sal from emp where ename=SMITH-查询职工一年的工资(sal*13)-查询结果一样,问题(如果查询
5、字段中有一个为null值,那么该字段跟其他字段合起来查询就为null值)select (sal*13)+(comm*13) 年工资 from emp select sal*13+comm*13 年工资 from emp -解决( nvl(comm,0)意思:如果comm字段为空,那么该字段值就为0,如果不为空那么就以该字段值为准进行下一步运算 )select sal*13+nvl(comm,0)*13 年工资 from emp -如何显示工资高于3000的员工select ename,sal from emp where sal3000-如何显示工资在800到1600的员工(注意:使用betw
6、een 800 and 1600,查询出的结果包含800,和1600的值的)select ename,sal from emp where sal between 800 and 1600-如何查找1982.1.1后入职的员工(注意: 时间输入 及使用 大于 符合)select ename,hiredate from emp where hiredate1-1月-1982-或者alter session set nls_date_format=yyyy-mm-ddselect ename,hiredate from emp where hiredate1982-1-1-如何使用like操作符-
7、 %表示:任意0-多个字符,_表示:任意单个字符,?表示:所有select ename,sal from emp where ename like S%-查询员工名字第3个字符是O的员工select ename,sal from emp where ename like _O%-此处是2条_(下划线)-where条件中使用in-查询员工号为:7369,7499,7521select ename,empno from emp where empno in(7369,7499,7521)-查询没有上级的员工select *from emp where mgr is null-查询工资大于500或者
8、职位为MANAGER,并且名字是以S开头的员工select *from emp where (sal500 or job=MANAGER) and ename like S%-order by (默认asc:低到高,高到底:desc)select *from emp sal order by sal desc-按照部门号升序而雇员的工资降序排列select *from emp order by empno asc , sal desc-按照部门号升序而雇员的入职降序排列select *from emp order by empno asc , hiredate desc-使用列的别名排序,使用
9、年薪排名select ename,sal*12 年薪 from emp order by 年薪 ascselect ename,sal*12+nvl(comm,0)*12 年薪 from emp order by 年薪 asc-分页查询-复杂查询 -如何显示所有员工的最高,最低工资 select MAX(sal) from emp select MIN(sal) from emp -查询最高工资,最低工资员工的详细信息(注意:使用子查询-把查询出的结果看做一个结果集,来筛选)select *from emp where sal=(select max(sal) from emp)-查询工资高于
10、平均工资的员工信息select *from emp where sal(select avg(sal) from emp)-grop by 和 having子句-如何显示每个部门的最高工资,最低工资,并按照部门编号降序排列select avg(sal),max(sal),deptno from emp group by deptno order by deptno desc-显示每个部门的每种岗位的平均工资和最低工资select avg(sal),min(sal),deptno,job from emp group by deptno,job-显示评价工资低于2000的部门号和它的平均工资se
11、lect avg(sal),max(sal),deptno from emp group by deptno having avg(sal)2000-分组统计的规律-分组函数只能出现在选择列表、having、order by子句中-顺序:group by,having,order by-笛卡尔集:多表查询的条件是,至少不能少于表的个数减1-查询员工的姓名,工资,工资级别select *from salgradeselect *from empselect *from deptselect a.ename,a.sal,b.grade from emp a,salgrade b where a.s
12、al between b.losal and b.hisal-显示雇员名,工资,及所在部门名字,并按部门排序select a.ename,a.sal,b.dname from emp a,dept b where a.deptno=b.deptno order by b.deptno-自连接(单独一个表多次利用自己)-如何显示某个员工的上级领导的名字(难点! 把一张表当2张表使用)select a.ename,b.ename from emp a,emp b where a.mgr=b.empno -子查询又称嵌套查询(一个查询语句里面出现多个select关键字,也就是一个以查询结果为条件的语
13、句)-如何显示与smith同一部门的所有员工-数据库在执行sql语句时,是从左到右扫描执行,而写sql语句又是从右往左写的-写sql语句时,如果条件是多个,那么把第一次查询出来结果最多的那个放在左边,提高查询性能。最好从多的开始查询再到少的查询select ename 名字,deptno 同一部门编号 from emp where deptno=(select deptno from emp where ename=SMITH)-多行子查询-如何查询和部门10相同的雇员的名字、岗位、工资、部门号-返回的是多行的语句,那么条件使用in关键字,而不用 = select *from emp wher
14、e job in (select job from emp where deptno=10)-使用all关键字-如何查询工资比部门30的所有员工的工资高的员工的姓名、工资、部门号-方法一select ename,sal,deptno from emp where sal all (select sal from emp where deptno=30)-方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)select ename,sal,deptno from emp where sal all (select max(sal) from emp where deptno=30)
15、-any关键字的使用-如何显示工资比部门30号的任意一个员工工资高的员工姓名,工资,部门号-方法一select ename,sal,deptno from emp where sal any (select sal from emp where deptno=30)-方法二(查询快点:原因是在第二次筛选结果集中他的筛选的范围要小店)select ename,sal,deptno from emp where sal any (select max(sal) from emp where deptno=30)-多列子查询(如果根据查询的结果集里面包含2列,而不是1列)-查询与smith的部门和岗
16、位完全相同的所有雇员-注意:列匹配顺序要一样select *from emp where (deptno,job)=(select deptno,job from emp where ename=SMITH)-如何显示高于自己部门平均工资的员工的信息(-难点 -)-1.首先查询各个部门的平均工资和部门号(其中 a 代表平均工资的 别名)select deptno,avg(sal) a from emp group by deptno-2.把上面的查询结果看做是一张 子表 ,并给他取个别名(其中 a1 代表上面所查询的结果的表的别名)-给表取别名不能加as,给列取别名可以加asselect en
17、ame,a2.sal,a2.deptno,a1.a from emp a2,(select deptno,avg(sal) a from emp group by deptno) a1 where a2.deptno=a1.deptno and a2.sala1.a order by deptno-分页查询(oracle分页一共有3种方式)-按雇员的id号升序查询-方法一:rownum分页select a1.*,rownum rn from (select *from emp) a1-问题:如何显示第6条到10条的记录?使用分页显示-1.rownum分页( select *from emp )
18、-2.显示 rownumoracle分配的 行号( select a1.*,rownum rn from (select *from emp) a1 )-3.显示某一部分的信息(rownum只能用一次,不能用多次,使用多次试不会查询出数据结果的)select a1.*,rownum rn from (select *from emp) a1 where rownum=10-不能写成如下这样:select a1.*,rownum rn from (select *from emp) a1 where rownum=6-4.完成分页效果机制select *from (select a1.*,row
19、num rn from (select *from emp) a1 where rownum=6-5.几个查询变化-a.如果指定查询列,只需修改最底层的查询:select *from empselect ename,sal from emp -b.如果是排序,只需修改最底层的查询:select *from empselect ename,sal from emp order by sal-方法二:(最快)-根据ROWID来分select *from t_xiaoxi where rowid in (select rid from (select rownum rn,rid from (sele
20、ct rowid rid,cid from t_xiaoxi order by cid desc) where rownum0080) order by cid desc-方法三:按分析函数来分(最慢)select *from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk9980-查询总共有多少记录:使用count()函数-用查询结果创建表(常用技巧)create table myemp2 (id,name,sal) as select empno,ename,sal from emp
21、-查看表的结构:desc myemp2select *from myemp2-复杂查询,合并查询,使用关键字:union ,union all,intersect,minus-(1)union 该操作符用于取得2个结果集的并集,使用时会自动去掉结果集中的重复行-集合查询速度比and,between等快select ename,sal,job from emp where sal2500 union select ename,sal,job from emp where job=MANAGER-(2)union all 会出现重复,取并集select ename,sal,job from emp
22、 where sal2500 union all select ename,sal,job from emp where job=MANAGER-(3)intersect 取交集-(4)minus 取差集-创建数据库有2种方法-(1)使用工具;-dbca【数据库创建配置助手】-(2)。*-2010年10月1日-第3章 事务与加锁-时间转换函数to_date(1988-12-1,yyyy-mm-dd)-设置回滚点:注意(只有在事务没有提交前管用)savepoint a1;rollback to a1;-在java程序中如何使用事务-加入事务处理(在java代码中写入)-conn.setAutoC
23、ommit(false);/设置不能提交点 -只读事务:指只允许执行查询的操作,而不允许执行dml(增,删,改)操作的事务-使用只读事务可以确保用户只能取得某时点的数据set transaction read only-1.sql函数的使用lower(char):将字符串转换为小写upper():-大写length(): substr(char,m,n):截取字符,m 代表从第几的个字符开始截取 ,n 代表取3个-以首字母大写方式显示所有员工的信息-第一步:取首写字母大写select upper(substr(ename,1,1) from emp-第二步:取后面小写部分select lowe
24、r(substr(ename,2,length(ename)-1) from emp-合并:使用 | 符合select upper(substr(ename,1,1) | lower(substr(ename,2,length(ename)-1) from emp-2.sql函数的使用replace(char1,search_string,replace_string)-替换字符串instr(char1,char2,n,m)-找函数的位置-3.sql(数学)函数的使用:n 代表字字符round(n,m):四舍五入trunc(n,m):截取数字mod(m,n):取模floor(n):返回=n的最
25、大整数-表dual是用来做测试用的表select mod(10,3) from dual-显示在一个月为30天的情况所有员工的日新金,忽略余数使用floor,trunc.-日期函数-默认情况:dd-mm-yy(即12-10月-2010)sysdate:获取系统时间add_months(时间或者字段,所要加的月份数)last_day():返回指定日期所在月份的最后一天-dual表位测试表select sysdate from dual-查询当前时间-查询入职超过300个月的员工select *from emp where sysdateadd_months(hiredate,300)-查询员工入
26、职的天数select sysdate-hiredate 入职天数,ename from emp-去掉小数select trunc(sysdate-hiredate) 入职天数,ename from emp-找出各月倒数第3天受雇的所有员工select hiredate,last_day(hiredate) from empselect hiredate,ename from emp where last_day(hiredate)-2=hiredate-转换函数(隐形转换)to_char(要转换的时间字符,转换成什么格式):select ename,to_char(hiredate,yyyy-
27、mm-dd hh24:mi:ss) from emp-在插入时间数据时,如果插入需要精确到分钟,秒,那么显示的时候就需要用该函数转换一下,才会显示到时、分、秒-to_char()用于人民币的格式转换select to_char(sal,L99,999.99) from empselect to_char(sal,$99,999.99) from emp-显示1980年,12月份入职的员工select *from emp where to_char(hiredate,yyyy)=1980select *from emp where to_char(hiredate,mm)=12-to_date(
28、)函数.-系统函数sys_context():-(1)terminal:当前会话客户所对应的终端的标识符select sys_context(USERENV,db_name) from dual-查询当前使用的数据库-(2):languageselect sys_context(USERENV,language) from dual-(3):session_user 当前用户select sys_context(USERENV,session_user) from dual-(4):current_schema 当前方案名(方案名与用户名一样)用户和方案的关系-用户和方案的关系select s
29、ys_context(USERENV,current_schema) from dual-数据库管理员sys(高于后者)与system用户的区别:(1)存储的数据的重要性不同(2)权限不同- sys用户必需以as sysdba或者as sysoper身份登录- system用户可以以normal登录,但是就是一个普通用户-注意:system可以以sysdba登录,但是如果以这样登录的话,那么就是一个sys用户了,具有他的所有权限sysdba(高于后者)与sysoper的区别:sysoperdba(普通用户)前者可以打开、关闭数据库,创建和删除,后者不行,前者数据恢复可以是部分,可以是全部,而后者是全部恢复-shutdown(关闭数据库)-startup(启动数据库)-管理初始化的参数-show parameter(该行没有运行出来,有点问题)数据库表的逻辑备份与恢复-逻辑备份和物理备份前者:是将数据库的对象导出到一个磁盘里面存放(备份) 将磁盘的信息导入到数据库(恢复)-数据库在运行状态才
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1