oracle马士兵笔记.docx

1、oracle马士兵笔记Mysql中创建一张表：create table userinfo ( id int primary key identity,-identity每次自动加1 name char(20), age int check(age10), sex char(2) )-插入insert into userinfo(name,age,sex) values(张三,24,男);第一课：客户端 1. Sql Plus(客户端），命令行直接输入：sqlplus，然后按提示输入用户名，密码。 2. 从开始程序运行:sqlplus，是图形版的sqlplus. 3. http:/localho

2、st:5560/isqlplus Toad：管理， PlSql Developer:第二课：更改用户 1. sqlplus sys/bjsxt as sysdba 2. alter user scott account unlock;(解锁)第三课：table structure 1. 描述某一张表：desc 表名 2. select * from 表名第四课：select 语句：从表中选出数据 1、desc emp ：描述表emp（雇员）； desc dept ：描述表dept（部门）；desc salgrade：描述salgrade（薪水）； .select * from salgrade

3、;标的内容全都取出来 .select ename sal*12 annual_sal from emp;计算年薪，并命名为annual_sal。 .select distinct deptno from emp; deptno部门编号。 删除重复的，修饰一个字段 .select distinct deptno,job from emp;删除重复的组合，修饰多个字段等值判断：where过滤条件，会过滤一些相关记录。 .select * from emp where deptno = 10; 取出部门编号为10的 .select * from emp where ename = CLARK;取出姓

4、名为CLARK的记录。 .select ename,sal from emp where sal 1500;取出薪水值大于1500的记录。 .select ename,sal from emp where deptno 10;取出部门编号不等于10的记录。.select ename,sal, deptno from emp where deptno 10;过滤掉部门编号等于10的.select ename,sal from emp where ename CBA;取出姓名大于CBA的记录。.select ename,sal from emp where sal between 800 and

5、1500;.select ename,sal from emp where sal = 800 and sal 20-2月-81;81年2月20号之后.select ename,sal from emp where deptno = 10 and sal 1000;.select ename,sal from emp where deptno = 10 or sal 1000;.select ename,sal from emp where sal not in (800 ,1500);.select sysdate from emp;取出日期.select ename from emp wh

6、ere ename like %ALL%;.select ename from emp where ename like _A%;取出第二个字母是A的姓名。.select ename from emp where ename like %$% escape $; $是转义字符。数据排序：升序可以不写，默认的是升序；降序要写明。.select * from dept ;按建表时的顺序排.select * from dept order by deptno desc;按降序排.select empno,ename from emp;排列无序.select empno,ename from emp

7、order by empno asc;按升序排.select empno,ename from emp where deptno 10 order by empno asc;过滤之后升序.select ename,sal,deptno from emp order by deptno asc;按deptno生序排列。 .select ename,sal,deptno from emp order by deptno asc,ename desc; deptno升序，deptno相同的情况下再按照enamel降序排列。 1.计算数据可以用空表：比如：.select 2*3 from dual 2

8、.select ename,sal*12 annual_sal from emp;与select ename,sal*12 annual sal from emp;区别，加双引号保持原大小写。不加全变大写。 3. select ename | abcd 如果连接字符串中含有单引号，用两个单引号代替一个单引号。第五课：distinct select deptno from emp; select distinct deptno from emp; select distinct deptno from emp; select distinct deptno ,job from emp 去掉dep

9、tno,job两者组合的重复。更多的项，就是这么多项的组合的不重复组合。第六课：Where select * from emp where deptno =10; select * from emp where deptno 10;不等于10 select * from emp where ename =bike; select ename,sal from emp where sal between 800 and 1500 (=800 and 800 order by sal desc; select lower(ename) from emp; select ename from emp

10、 where lower(ename) like _a%;等同于 select ename from emp where ename like _a% or ename like _A%; select substr(ename,2,3) from emp;从第二字符截,一共截三个字符. select chr(65) from dual; 把数字转换成A select ascii(A) from dual； 结果为:65 round四舍五入： select round(23.652) from dual; 24select round(23.652,2) from dual; 23.65 se

11、lect round(23.652,1) from dual; 结果为: 23.7 select round(23.652,-1) from dual; 20 to_char把数字或者日期转换成某种格式 select to_char(sal,$99_999_999) from emp; select to_char(sal,L99_999_999) from emp;人民币符号,L:代表本地符号 select to_char(sal, L00000.0000)from emp;如果某一位上没有数字，则强制转换为0 这个需要掌握牢:转换日期格式 .select hiredate from emp

12、; .select to_char(hiredate , YYYY-MM-DD HH:MI:SS) from emp; select birthdate from emp; 显示为: BIRTHDATE - 17-12月-80 - 改为: select to_char(birthdate,YYYY-MM-DD HH:MI:SS) from emp; 显示: BIRTHDATE - 1980-12-17 12:00:00 - .sysdate当前时间 select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) from dual; /也可以改为:HH12 TO_C

13、HAR(SYSDATE,YY - 2007-02-25 14:46:14 select to_char(sysdate,YYYY-MM-DD HH12:MI:SS) from dual; TO_CHAR(SYSDATE,YY - 2007-02-25 02:46:14 to_date函数: select ename,birthdate from emp where birthdate to_date(1981-2-20 12:34:56,YYYY-MM-DD HH24:MI:SS); 如果直接写 birthdate1981-2-20 12:34:56会出现格式不匹配,因为表中的格式为: DD-

14、MM月-YY. select sal from emp where sal888.88; 无错.但 select sal from emp where sal$1,250,00; 会出现无效字符错误. 改为: select sal from emp where salto_number($1.250.00,$9,999,99); 把空值改为0 select ename,sal*12+nvl(comm,0) from emp; nvl(comm.,0)如果comm是空值改为0。 这样可以防止comm为空时,sal*12相加也为空的情况.第九课: Group function 组函数 重点重点 m

15、ax,min,avg ,count,sum函数 select max(sal) from emp; select min(sal) from emp; select avg(sal) from emp;平均值 select sum(sal) from emp;总和 select to_char(avg(sal),99999999,99) from emp; select round(avg(sal),2) from emp; 结果:2073.21 .select count(*) from emp;求出emp中一共有多少条记录 select count(*) from emp where de

16、ptno=10;求出部门10里有多少人 select count(ename) from emp where deptno=10; count某个字段,如果这个字段不为空就算一个。.select count(deptno) from emp; select count(distinct deptno) from emp;一共多少个部门编号 select sum(sal) from emp;第十课: Group by语句：Group by按照什么分组 : 需求:现在想求,求每个部门的平均薪水. select avg(sal) from emp group by deptno; select de

17、ptno avg(sal) from emp group by deptno; select max(sal) from emp group by deptno,job; select deptno,job,max(sal) from emp group by deptno,job;按照两个或者多个字段分组 求薪水值最高的人的名字. select ename,max(sal) from emp;出错,因为max只有一个值,但等于max值的人可能好几个,不能匹配. 应如下求: select ename from emp where sal=(select max(sal) from emp);

18、.select deptno,max(sal) from emp group by deptno; Group by语句应注意, 出现在select中的字段,如果没出现在组函数中,必须出现在Group by语句中. 第十一课: Having 对分组结果筛选 Where是对单条纪录进行筛选,Having是对分组结果进行筛选. select avg(sal),deptno from emp group by deptno;分组 select avg(sal),deptno from emp group by deptno having avg(sal)2000;分组之后筛选。 查询工资大于1200

19、雇员,按部门编号进行分组,分组后平均薪水大于1500,按工薪倒充排列. select * from emp where sal1200 group by deptno having avg(sal)1500 order by avg(sal) desc; .select avg(sal) .from emp .where sal 1200 .group by deptno .having avg(sal) 1500 .order by avg(sal) desc; 第十二课:字查询 谁挣的钱最多(谁:这个人的名字, 钱最多) select 语句中嵌套select 语句,可以在where,fro

20、m后. select ename,sal from emp where sal = (select max(sal) from emp); 问那些人工资,在平均工资之上. select ename,sal from emp where sal (select avg(sal) from emp); 查找每个部门挣钱最多的那个人的名字. select ename ,deptno from emp where sal in(select max(sal) from ename group by deptno);出错了 单行子查询返回多个行 查询会多值.应该如下: select max(sal),d

21、eptno from emp group by deptno;当成一个表.面试题：求部门中哪些人的薪水最高：select ename, sal from empjoin (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno); 求部门平均薪水的等级：.select deptno,avg_sal,grade from (select deptno,avg(sal) avg_sal from emp group by deptno)

22、t.join salgrade s on (t.avg_sal between s.losal and s.hisal);求部门平均的薪水等级：.select deptno,avg(grade) from(select deptno,ename,grade from emp join salgrade s on (emp.sal between s.losal and s.hisal) t.group by deptno; 雇员中有哪些人是经理人：.select ename from emp where empno in (select distinct mgr from emp); 不准用组

23、函数，求薪水的最高值（面试题）：.select distinct sal from emp where sal not in(select distict e1.sal from emp e1 join emp e2 on ( e1.sal e2.sal ); 求平均薪水最高的部门的部门编号:.select deptno, avg_sal from (select avg(sal) avg_sal , deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_s

24、al,deptno from emp group by deptno) );等价写法（组函数嵌套）：最多嵌套2层.select deptno, avg_sal from (select avg(sal) avg_sal , deptno from emp group by deptno) where avg_sal = (select max(avg(sal) from emp group by deptno) ); 求平均薪水最高的部门的部门名称：.select dname from dept where deptno =(select deptno, avg_sal from (selec

25、t avg(sal) avg_sal , deptno from emp group by deptno) where avg_sal = (select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp group by deptno) ) ); 求平均薪水的等级最低的部门的部门名称：.select dname , t1.deptno , grade , avg_sal from( select deptno ,grade ,avg_sal from (select deptno , avg(sal) avg_sal fro

26、m emp group by deptno) t .join salgrade s on (t.avg_sal between s.losal and s.hisal) ) t1.join dept on (t1.deptno = dept.deptno).where t1.grade =( .select min(grade) from (.select deptno ,grade ,avg_sal from(select deptno , avg(sal) avg_sal from emp group by deptno) t .join salgrade s on (t.avg_sal

27、between s.losal and s.hisal) 创建视图： .conn sys/bjsxt as sysdba授权： .grant create table ,create view to scott; 授权成功。 .create view v$_dept_avg_sal_info as .select deptno ,grade ,avg_sal from(select deptno , avg(sal) avg_sal from emp group by deptno) t .join salgrade s on (t.avg_sal between s.losal and s.hisal);视图已创建。从视图里取数据：.select * from v$_dept_avg_sal_info;视图本身就是一张表或者说是一个子查询，视图这张表叫虚表，实际数据依然存放在实际表。用视图解决上题：（先建一张虚表，再在虚表中取数据）.select dname , t1.deptno , grade , avg_sal from v$_dept_avg_sal_info t1.join dept on (t1.de