《尚学堂马士兵Oracle学习笔记words-副本.docx》由会员分享，可在线阅读，更多相关《尚学堂马士兵Oracle学习笔记words-副本.docx（50页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、尚学堂马士兵Oracle学习笔记-101_sqlplus_introduction.avi三种不同的命令行的客户端Oracle需要了解的第一个：sqlplus，当启动sqlplus需要让你输入，用户名口令字符串Sqlplus是Oracle的一个客户端，Oracle大多数的东西都是在命令行执行，输入用户名：scott 口令：tiger，进入oracle命令行3个界面：图形版界面，命令行界面，网页界面(isqlplus)：在浏览器地址栏输入：http:/127.0.0.1:5560/isqlplus/ 输入用户名：scott 口令：tiger进入有一个著名的oracle客户端叫toadOracle
2、还有一个客户端plsql develement命令行：图形版界面：网页版：02_unlock_user.avi使用超级管理员登录到数据库上：sqlplus sys/bjsxt as sysdba 当成DBA登录到服务器上连上之后更改user ：alter user scott account unlock; 更改用户 解除锁定03_table_structures.avi第二章SQL（structured Query Language）语言Sql语言是在数据库地下进行操作的专门的语言，sql语言本身是一种标准语言，它是一个国际标准，它定义了套标准SQL1922， SQL1999，SQL在大多数
3、数据库上通用，或许有轻微的改变包含四大语句：1. 查询语句查询语句只有一种就是select语句2. DML语句(数据操作/操纵语言)DML语句包含Insert,Update,Delete等常用语句3. DDL语句(数据定义语言)DDL语句包含Create, Alter， Drop等常用语句4. 事务控制语句包含Commit， Rollback等常用语句还有一大类语句：叫DCL语句（Data Control Language），主要用于权限的分配与回收，由于与开发关系不是十分密切，不做重点讲解最重要的就是select语句，任何select语句全部要背过，select语句必考，不会考其它的Sele
4、ct语句就是从表里把数据选出来首先熟悉试验中的数据第一条语句：desc emp; desc 表名; 列出 表头 = 字段 = 列Varchar2 可变字符串支持国际化NUMBER(7,2) 7位的数字，2位的小数每行显示的宽度setlinesize 200;显示的页数 setpagesize 30;emp雇员信息表EMPNO 雇员编号NOT NULL NUMBER(4)ENAME 雇员姓名 VARCHAR2(10)JOB 工作岗位 VARCHAR2(9)MGR 该雇员经理人的编号 NUMBER(4)HIREDATE 入职时间 DATESAL 薪水NUMBER(7,2)COMM 津贴NUMBER
5、(7,2)DEPTNO 雇员所在部门编号 NUMBER(2)Varchar2：可以存放各国的语言，适合国际化；varchar：不能存放各国语言，有限定。表内容：EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7369 SMITH CLERK 7902 17-12月-80 800 207499 ALLEN SALESMAN 7698 20-2月 -81 1600 300 307521 WARD SALESMAN 7698 22-2月 -81 1250 500 307566 JONES MANAGER 7839 02-4月 -8
6、1 2975 207654 MARTIN SALESMAN 7698 28-9月 -81 1250 1400 307698 BLAKE MANAGER 7839 01-5月 -81 2850 307782 CLARK MANAGER 7839 09-6月 -81 2450 107788 SCOTT ANALYST 7566 19-4月 -87 3000 207839 KING PRESIDENT 17-11月-81 5000 107844 TURNER SALESMAN 7698 08-9月 -81 1500 0 307876 ADAMS CLERK 7788 23-5月 -87 1100 2
7、0EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO- - - - - - - -7900 JAMES CLERK 7698 03-12月-81 950 307902 FORD ANALYST 7566 03-12月-81 3000 207934 MILLER CLERK 7782 23-1月 -82 1300 10dept部门信息表DEPTNO 部门编号NOT NULL NUMBER(2)DNAME 部门名字VARCHAR2(14)LOC 部门所在地 VARCHAR2(13)内容：DEPTNO DNAME LOC- - -10 ACCOUNTING NE
8、W YORK20 RESEARCH DALLAS 30 SALES CHICAGO40 OPERATIONS BOSTONsalgrade薪水等级表GRADE 薪水等级NUMBERLOSAL 该等级的最低薪水值 NUMBERHISAL 该等级的最高薪水值 NUMBER内容：GRADE LOSAL HISAL- - -1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999熟悉表里面的数据：第一个select语句：select * from 表名;Select * from emp;04_select_1.avi1.简单select语句：例子1
9、：取出一张表中所有的数据Select * from emp;例子2：取出某些字段的值Select empno, ename, deptno from emp;2.包含算术表达式的sql语句例子1：取出emp中所有人的年薪及名字Select ename , sal*12 from emp;例子2：在emp表中取出2*3的结果Select 2*3 from emp;例子3：取出2*3的一条记录 Select2*3 from dual;-当我们需要显示一个结果的时候就用系统提供的dual虚表例子4：取出当前系统时间Select sysdate from dual;-sysdate在Oracle中表示
10、当前系统时间3.含有别名的sql语句例子1：select ename, sal*12 annual_sal from emp;例子2：如果想让别名强制大小写混合可以使用双引号Select ename, sal*12 “Annual_sal” from emp;例子3：如果想让别名中有空格也使用双引号Select ename, sal*12 “Annual sal” from emp;5.处理含有空值的字符串例子1：算某人一年的年薪含有任何null值的数学表达式最后的结果都为nullSelect ename, sal*12+comm from emp例子2：含有任何null值的字符串表达式中，n
11、ull被当作空字符串处理select empno, ename |and his manager number is | mgrfrom emp;字符串连接符：|Select语句中用单引号表示字符串6.在select中使用单引号例子1：用两个单引号表示一个单引号select empno, ename | s manager is | mgr from emp;05_distinct.avi7.消除重复值例子1:select distinct deptno from emp;-用distinct消除结果集中的重复信息例子2：用distinct修饰多个字段的时候，指的是消除后面所有字段的组合重复结
12、果Select distinct job, deptno from emp;-指job和deptno都相同的删除06_where.avi使用where对数据库设定条件限制，where过滤条件，过滤相关的数据1. 等值判断例子1：显示部门编号为10的所有员工的详细信息select * from emp where deptno = 10;例子2：显示名字为KING的员工的详细信息判断字符串是否相等时，字符串要用单引号引起来，单引号中字符串内容是要区分大小写的Select * from emp where ename = KING;2. 非等值判断（ = = ）例子1：取出薪水大于2000的所有员
13、工的名字和薪水Select ename, sal from emp where sal 2000;例子2：字符串大小比较：是比较字符串ASCII码值的比较，先比较第一字母，依次Select ename, sal from emp where ename DBA;例子3：取出所有部门号不是10的雇员的名字和薪水Select ename, sal from emp where deptno 10;例子4：取出薪水位于800和1500之间的雇员名字和薪水Select ename, salfrom emp where sal = 800 and sal = 20-2月-81;Select ename,
14、 hiredate from emp wherehiredate 20-2月-1981;如果想用自己定义的日期格式，可以使用to_char or to_date函数，3. AND， OR， NOT例子1：查询部门标号为10 并且薪水1000 的员工Select ename, deptno, sal from emp wheredeptno = 10 and sal 1000;例子2：查询部门编号为10或者工作岗位为CLERK 的员工Select ename, deptno, job from emp where deptno = 10 or job = CLEARK;例子3：查询薪水没有位于8
15、00, 1500, 2000 之中的员工Select ename, sal from emp where sal notin (800, 1500, 2000);7模糊查询使用like关键字，和通配符 % 表示0个或多个字符，_ 表示1个字符例子1：查询名字中含有ALL的人员Select ename from emp where ename like %ALL%;例子2：查询第二字母中含有 A 的雇员Select ename from emp where ename like _A%;例子3：查询名字中含有 % 等通配符的数据时，使用转义字符 Escape 自定义转移字符, 系统默认的转义字符
16、是 Select ename from emp where ename like %escape;Select ename from emp where ename like %$%escape$;07_order_by.avi使用它order by 对数据进行排序例子1：按照名字的升序进行排序Select ename, salfrom emp order by ename;Select ename, salfrom emp order by ename asc; 用asc关键字指出按升序排列例子2：按照雇员编号的升序排序Select ename ,deptno from emp order
17、by deptno;Select ename, deptnofrom emp order by deptno asc;例子3：按照名字降序排列Select ename, deptnofrom emp order by ename desc;例子4：按照部门编号降序排列Select ename, deptnofrom emp order by deptno desc;例子5：多个关键字排序，先按照部门编号升序排列，在再内部按照enamel 降序排序08_sql_function_1.avi常用SQL函数1.Lower()函数 将字符串全部转换成小写例子1：将雇员中名字含有A或a的人员全部显示出来
18、Select ename from emp where ename like %A%orename like%a%;Select ename from emp where lower(ename)like %a%;2.Upper()函数将字符串全部转换为大写例子2：将雇员中名字含有A或a的人员全部显示出来Select ename from emp where upper(ename)like %A%;3.substr()函数例子1：从第一字符开始共截3个子串Select substr(Hello, 1, 3) from dual; HelSubstr 可以省略第三个参数例子2：从第二个字符开始
19、，截取到整个字符串结束Select sbustr(ename, 2) from emp;4.Chr()函数例子1：求一个与某个ASCII码值对应的字符Select chr(65) from dual;1. Ascii()函数例子：求一个字符的ASCII码值Select ascii(A) from dual;2. round()函数 对参数值进行四舍五入的操作例子1：对23.652进行四舍五入操作Select round(23.652)from dual; 24可以指定四舍五入到小数点后几位例子2：对23.652四舍五入到小数点后2位Select round(23.652,2) from dua
20、l; 23.65可以用负数指定小数点前面几位例子3：对23.652四舍五入到小数点前1位Select round(23.652,-1) from dual; 20*7.to_char()用于将数字或日期转换成特定的字符串，To_char()有两个参数：第一个参数：需要进行转换的日期或数字第二个参数：特定的转换格式，对于数字有一下几个格式可以指定：9 代表数字，若果该位没有数字则不进行显示，但对于小数点后面的部分仍会强制显示0 代表一位数字，如果该位没有数字则强制显示0$ 显示美元符号L 显示本地货币符号. 显示小数点, 显示千分位符号例子1：select to_char(sal, $99,99
21、9.9999) salary from emp where ename = ALLEN; $1,600.0000例子2：select to_char(sal, $00,000.0000) salary from emp where ename = ALLEN; $01,600.0000对于日期：to_char()可以指定为下面的常用格式：格式控制符含义YYYY、YY -代表4位，2位数字的年份MM -用数字表示的月份MON -月份的缩写对中文月份来说就是全称DD -数字表示的日DY -星期的缩写，对中文的星期来说就是全称HH24、HH12 -12小时或者24小时进制下的时间MI -分钟数SS
22、-秒数有了这些格式，就可以把日期自定义为任何格式例子1：select to_char(sysdate, YYYY-MM-DD-HH24:MI:SS) from dual; 2008-04-041、把字段转换成小写2、截子串 substr(ename,1,3)从第一个开始截，截取3个字符3、把数字转换成相对应的字母3、把字母转换成相对应的ascII码4、四舍五入5、把数字或者日期转化成某种格式1）对数字对齐格式，格式化L：本地货币符号2）对日期时间格式控制HH：12进制HH24:24进制09_sql_function_2.avi*8.to_date()函数将特定的字符串转换成日期格式，这个函数有
23、两个参数第一参数：自定义的日期字符串第二参数：指定这个字符串的格式例子1：将1981年3月2日中午以后入职的雇员信息取出：Select * from empwhere hiredate to_date(1981-03-02 12:00:00, YYYY-MM-DD HH12:MI:SS);*9.to_number()函数讲指定的字符串转换成数字格式，这个函数有两个参数第一参数：自定义的数字字符串第二参数：指定这个字符串的格式例子1：求薪水大于1200的员工信息Select * from empwhere sal to_number($1,200.00,$9,999.99);*10.nvl()函
24、数用来处理空值，这个函数有两个参数：第一参数：字段名或表达式，如果这个参数值为null，就返回第二参数值，否则返回第一参数值例子：求每个员工每年的年收入(12个月的薪水+津贴)因为comm的值为null，想要得到正确的结果，必须讲null值转换为0Select ename,sal*12+nvl(comm, 0) from emp; 10_group_function.avi（重要,牢牢记住，一个都不能忘）*11.组函数包括5个函数：(1).avg():求平均值(2).max()：求最大值(3).min()：求最小值(4).sum()：求总和(5).count()：求记录的数量例子1：求薪水的总
25、和、平均值、最大值和最小值Select sum(sal),avg(sal), max(sal), min(sal) from emp; 例子2：求emp表中记录的数量Select count(*) fromemp;Count()可以对单独字段使用，得到的是所有非空记录的数量例子3：求comm字段中所有非空记录的数量Select count(comm)from emp;Count()可以和distinct一起使用，得到所有唯一值记录的数量例子4：求emp表中deptno唯一的数量Select count(distinct,deptno) from emp;注意：函数名不是在所有数据库中通用10、
26、11_group_by.avi对表中的数据进行分组例子1：计算每个部门的平均工资首先将现有数据按照部门进行分组，然后再计算每个组员工的平均薪水。Select deptno,avg(sal) from emp group by deptno; 例子2：计算每个部门的最大工资Select deptno,max(sal) from emp group by deptno;例子3：按照部门，和职位的最大薪水进行分组Select deptno, job,max(sal) from emp group by deptno, job;使用group by 的规律：出现在select列表中的字段，如果没有出现
27、在组函数中，则必须出现在group by子句中典型错误：select ename, deptno, max(sal) from emp group by deptno;例子4：求出每一个部门里赚钱最多的那个人的名字Select ename,deptno, sal from emp where sal in (Select sal from (Select deptno,max(sal) sal from emp group by deptno)例子4：选出所用部门里工资最高的人的名字Select ename, salfrom emp where sal = (Select max(sal) f
28、rom emp);Ename可能有很多，max（sal）只有一行12_having.avi使用Having对分组进行限制如果我们要从分组数据中把某些特定的剔除去的时候，使用Having关键字例子1：将平均薪水大于1000的组的平均薪水从emp这张表中选出来Select avg(sal),deptno group by deptno having avg(sal) 1000;例子2：求薪水大于1200的雇员，按照部门进行分组，而且这些分分组后组内平均薪水必须大于1500，要查询分组的平均工资Select avg(sal) from emp where sal 1200 group by dept
29、no having avg(sal) 1500Order by avg(sal);13_表连接.avi在where子句中进行多表连接(SQL1992)实际需要在很多表中取数据，例子：把员工姓名及员工所在部门的名字同时显示出来Select ename, dnamefrom emp, dept;第二天上午：13_表连接-21:41为什么启动慢？因为每次启动Oracle的服务都会启动（解决办法，把Oracle服务设置成“手动”启动）scott/tiger - 用户名密码可以直接这样输入把第一天学的内容用一句话总结出来问：从emp这张表里把平均工资和部门编号列出来，并且过滤掉大写是A的名字 ，把剩下的
30、数据按照部门编号进行分组，分组之后的平均薪水必须大于2000，按照部门编号的倒序排列select avg(sal), deptno from emp where ename not like _A%group by deptnohaving avg(sal) 2000order by deptno 这是一个单条的select语句，(只是从一张表里取数据)第一天已经讲完今天讲：多表的连接例子1：请你选出雇员的名字，和雇员在部门的名字？错误写法：select ename, deptno from emp;/这里选的是部门的编号，问题里是让选部门的名字select dname, deptno fro
31、m dept where deptno= 20;/选出了编号是20的这个人所在部门的名字正确写法：select ename, dname from emp, dept whereemp.deptno = dept.deptno;必须明确的指出重复字段是哪个表的例如：select ename, dname,dept.deptno from emp, dept where emp.deptno = dept.deptno;指定哪张表的deptno实际上对它有一个效率上的影响例子2：求每个人的薪水值包括他的名字select ename, salfrom emp;例子3：求每个人的薪水值，名字和他的薪
32、水处于第几个级别(非等值连接)select ename, sal,grade from emp, salgrade where sal between losal and hisal;select ename, sal, gradefrom emp, salgradewhere sal = losal and sal =s.losal and e.sal = s.hisal and job PRESIDENT;13_子查询例子1：求谁挣的钱最多？错误的写法：select ename, max(sal) from emp;正确的写法：select ename, sal from emp wher
33、e sal = (select max(sal)from emp);例子2：求出来有哪些工资位于所有人平均工资之上 selectename, sal from emp where sal (select avg(sal) from emp);例子3：按照部门进行分组之后挣钱最多的那个人的名字，部门编号？select ename, sal, t.deptno from emp join(select max(sal) max_sal, deptno fromemp group by deptno) ton (emp.sal = t.max_sal and emp.deptno =t.deptno
34、);*理解子查询的关键-把它当成一张表例子4：(练习)求每个部门的平均薪水等级是多少？Select t.avg_sal, grade, t.deptno from salgrade sJoin( select avg(sal) avg_sal, deptno fromemp group by deptno) tOn ( t.avg_sal between s.losal and s.hisal);尚学堂马士兵Oracle学习笔记314_self_table_connection.avi例子1：求这个人的名字和他经理人的名字(自连接)select e1.ename, e2.ename from
35、emp e1, emp e2 where e1.mgr = e2.empno;自连接：为同一张表起不同的别名，然后当成两张表来用15_SQL1999_table_connections.aviSQL1992是在where语句里直接写表连接的条件，新的语法是把表连接语句拿出来，where里只写过滤条件。有一个小小的问题：select ename, dname, grade from emp e, deptd, salgrade swhere e.deptno = d.deptno and e.sal betweens.losal and s.hisal and -两个表的连接条件job CLER
36、K; -过滤条件连接条件和过滤条件混在一起让人读起来SQL语句会困难一些，不太容易清楚怎么把连接条件和过滤条件分开来呢？原来的交叉连接：select ename, dname from emp, dept; -笛卡尔乘积56行1999年标准的语法：（写法）select ename, dename from emp cross join dept; cross join - 叫做交叉连接(新语法定义的比较明确,也是56行，笛卡尔乘积)原来的等值连接：select ename, dname from emp, dept where emp.deptno = dept.deptno;新语法的等值连接
37、：select ename, dname from emp join dept on (emp.deptno = dept.deptno);等值连接的简单的写法：select ename, dname from emp join dept using(deptno)using (deptno) - 是说我这个表的deptno等于你这个表的deptno* using 的用法不推荐使用- 两张表中要有相同的字段，类型必须相同非等值连接：例子1：取出雇员名称和薪水等级select ename, grade from emp e join salgrade s on (e.sal between s.
38、losal and s.hisal);三个表连接在一起的：例子2：取出雇员名字，他的部门名称，和薪水等级其中名字第二个字母包含A的不要取出select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno)join salgrade s on (e.sal between s.losal and s.hisal)where ename not like _A%;例子3：自连接新语法求这个人的名字，他经理人的名字select e1.ename, e2.ename from emp e1 joinemp e2 on (
39、e1.mgr = e2.empno);King如果想显示出来该怎么办呢？外连接：左外连接：可以把左边这张表的多余的数据（不能产生连接的数据给拿出来）选出雇员名字和它经理人的名字(可以将没有经理人的那个人取出来)select e1.ename, e2.ename from emp e1 left （outer） join emp e2 on(e1.mgr = e2.empno);例子4：求：每个雇员的名字，他所在部门的名称，全部选出来，并且把多余的部门也选出来？select ename, dname from emp e right outer join dept d on(e.deptno =
40、 d.deptno);全外连接：即把左边的多余的数据拿出来，又把右边的多余的数据拿出来，例：select ename, dname from emp e full join deptd on (e.deptno = d.deptno);16_部门平均薪水的等级.avi1.求部门平均薪水等级？select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) tjoin salgrade s on (t.avg_sal between s.losal and s.hisal);
41、2.求部门中哪些人的薪水最高select ename, sal from emp join (select max(sal) max_sal, deptno fromemp group by deptno) ton (emp.sal = t.max_sal and emp.deptno =t.deptno);17_部门平均的薪水等级.avi例子1:求出每个人的薪水等级，然后再平均求出的就是平均薪水等级select deptno, ename, grade from emp join salgrade s on (emp.sal between s.losaland s.hisal);select deptno, avg(grade) from (select deptno, ename, grade from emp join salgrade s