尚学堂-马士兵Oracle笔记———吐血整理

1、 第一课：口令超级管理员：sys/change_on_install 普通管理员：system/manager 普通用户：scott/tiger 大数据用户：sh/sh1.sys/change_on_install 2.alter user scott account unlock;(解锁) 第二课：SQL语言Sql语言是在数据库地下进行操作的专门的语言，sql语言本身是一种标准语言，它是一个国际标准，它定义了套标准SQL1922， SQL1999，SQL在大多数数据库上通用，或许有轻微的改变包含四大语句：1. 查询语句 查询语句只有一种就是select语句2. DML语句 DML语句包含In

2、sert,Update,Delete等常用语句3. DDL语句 DDL语句包含Create, Alter， Drop等常用语句4. 事务控制语句 包含Commit， Rollback等常用语句还有一大类语句：叫DCL语句（Data Control Language），主要用于权限的分配与回收，由于与开发关系不是十分密切，不做重点讲解Select语句就是从表里把数据选出来首先熟悉试验中的数据第一条语句：desc emp; desc 表名; 列出 表头 = 字段 = 列Varchar2 可变字符串支持国际化 NUMBER(7,2) 7位的数字，2位的小数每行显示的宽度setlinesize 200

3、; 显示的页数 setpagesize 30;第三课：table_structures 1.desc 表名 /描述表 2.select * from 表名 /查看表数据第四课：select语句 1.含有别名的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; 如果想让别名中有空格也使用双引号 2.desc dual，

4、select 2*3 from dual，dual是系统自带的一张空表，计算数据时可以使用该表 3.任何含有空值的算术表达式的计算结果是空值 4.select ename|sal from emp; 字符串连接符：| 5.select ename | 'abcdefg' from emp; 6.select ename | 'abcd''efg' from emp;当字符串中含有单引号时，可用两个单引号代表一个单引号 7.set linesize 200;-用于设定每行显示的宽度 8.set pagesize 30;-设置显示的页数 9.sele

5、ct syadate from dual;/显示系统时间第五课：distinct 1.select distinct deptno from emp; /把deptno值重复的不显示出来 2.select distinct deptno,job from emp; /把deptno,job都重复的都不显示出来；第六课：where 1.select * from emp where empno = 10; /显示部门编号为10的所有员工的详细信息 2.select * from emp where empno <> 10;/<>是不等于 3.select * from e

6、mp where ename = 'KING' /显示名字为KING的员工的详细信息, 判断字符串是否相等时，字符串要用单引号引起来，单引号中字符串内容是要区分大小写的；比较的按字母顺序4.select * from emp where comm is (not) null; /为空值的表项查询 5. AND， OR， NOT例1：Select ename, deptno, sal from emp wheredeptno = 10 and sal > 1000; 查询部门标号为10 并且薪水>1000 的员工例2：Select ename, deptno, job

7、 from emp where deptno = 10 or job = CLEARK; 查询部门编号为10或者工作岗位为CLERK 的员工例3：select * from emp where sal (not) between 800 and 1500; /取出薪水位于800和1500(包含800和1500)之间的雇员名字和薪水6.In语句：用于塞选某一个值select * from emp where ename (not) in ('smith','philip','jay'); /把名字为SMITH, ALLEN, KING 的雇员取出来

8、Select ename, sal from emp where sal in(800, 1250, 1500, 2000); /把薪水是800，1250， 1500， 2000 的雇员信息取出来7.select * from emp where hiredate > '20-2 月 1820'； /20-2 月 1820后的时间, 如果想用自己定义的日期格式，可以使用to_char or to_date函数; Oracle默认的日期格式为：DD-MON-RR8.模糊查询 使用like关键字，和通配符 % 表示0个或多个字符，_ 表示1个字符, "?"

9、为0个或1个select * from emp where ename like '_A%' / 查询第二字母中含有 A 的雇员 select * from emp where ename like '_%a%' /系统默认转义符是,可以自己指定转义符 select * from emp where ename like '_$%a%' escape '$' 第七课：order by 使用order by对数据进行排序 1.select * from dept; 2.select * from dept order by dept

10、 desc; /(默认为asc) desc为降序排列，asc为升序 3.select ename, sal, deptno from emp where sal > 2000 order by deptno asc,ename desc; 第八课：sql_function 1.select ename,sal*12 annual_sal from emp where ename not like '_A%' and sal > 1500 order by sal desc; 2.select lower(ename) from emp; / Lower()函数 将字

11、符串全部转换成小写,把ename 转换为小写值 3.select ename from emp where lower(ename) like '_a%' /把ename的值第二个字母为A或a输出 4.select substr(ename,2,3) from emp; /从第二字符截，一共截三个字符。 5.select chr(65) from dual; /求一个与某个ASCII码值对应的字符,结果为A 6.select ascii('a') from dual; /求一个字符的ASCII码值,结果为65 7.round()函数 对参数值进行四舍五入的操作例

12、子1： Select round(23.652)from dual; /对23.652进行四舍五入操作 24例子2：Select round(23.652,2) from dual; /对23.652四舍五入到小数点后2位 23.65例子3：Select round(23.652,-1) from dual; /用负数指定小数点前面几位,对23.652四舍五入到小数点前1位 20*8.to_char() 用于将数字或日期转换成特定的字符串，To_char()有两个参数： 第一个参数：需要进行转换的日期或数字 第二个参数：特定的转换格式，对于数字有一下几个格式可以指定：9 代表数字，若果该位没有

13、数字则不进行显示，但对于小数点后面的部分仍会强制显示0 代表一位数字，如果该位没有数字则强制显示0 select to_char(sal,'$99,999.9999') from emp; /to_char函数主要用于对日期和数字格式化 $16,000.0000 select to_char(sal,'$0000,000.0000') from emp; /结果$0016,000.0000 select to_char(sal,'L99,999.9999') from emp; 人民币符号，L代表本地符号。 select birthdate fr

14、om emp; 显示为：BIRTHDATE - 22-3月-87 改为：select to_char(birthdate,'YYYY-MM-DD HH24:MI:SS') from emp; /24制的 没有24为12制的格式控制符 含义YYYY、YY - 代表4位，2位数字的年份MM - 用数字表示的月份MON - 月份的缩写对中文月份来说就是全称DD - 数字表示的日DY - 星期的缩写，对中文的星期来说就是全称HH24、HH12 -12小时或者24小时进制下的时间MI - 分钟数SS - 秒数*9.to_data函数将特定的字符串转换成日期格式，这个函数有两个参数:第一参

15、数：自定义的日期字符串 第二参数：指定这个字符串的格式 select ename,birthdate from emp where birthdate > to_date('1987-3-22 12:00:00','YYYY-MM-DD HH24:MI:SS'); /不能直接写birthdate>'1987-2-22 11:22:33'会出现格式不匹配，因为表中格式为DD-MM月YY, 10. Select * from emp where sal > to_number($1,200.00,$9,999.99); /求薪水大于

16、1200的员工信息*11.nvl()函数 用来处理空值，这个函数有两个参数：第一参数：字段名或表达式，如果这个参数值为null，就返回第二参数值，否则返回第一参数值select ename, sal*12+nvl(comm,0) from emp /这样防止comm为空时，sal*12相加也为空的情况 第九课：Group function 组函数 1.select max(sal) from emp; /最大值 2.select min(sal) from emp; /最小值 3.select to_char(avg(sal), '$999,999,999.99') from

17、emp; /平均值 4.select round(sum(sal),2) from emp; /求和 5.select count(*) from emp where sal > 1500; /求记录数 6.select count(comm) from emp; /Count()可以对单独字段使用，得到的是所有非空记录的数量 7.select count(distinct deptno) from emp; /Count()可以和distinct一起使用，得到所有唯一值记录的数量第十课：Group by语句 1.select avg(sal) from emp group by dep

18、tno; /首先将现有数据按照部门进行分组，然后再计算每个组员工的平均薪水 2.select deptno,avg(sal) from emp group by deptno; 3.select deptno,job,max(sal) from emp group by deptno,job; /按照部门，和职位的最大薪水进行分组 4.求薪水值最高的人的名称select ename,max(sal) from emp; /出错，因为max只能有一个值，但是等于max的值可能有好几个，不能匹配。 可以写成select ename from emp where sal=(select max(sa

19、l) from emp); Group by 语句应注意，出现在select中的字段，如果没有出现在组函数中，必须出现在Group by语句中。 第十一课：Having对分组结果筛选 1.where是对单条记录进行筛选,Having是对分组结果进行筛选 select avg(sal),deptno from emp group by deptno having avg(sal) > 2000; 2.查询工资大于2000的雇员,按照部门编号进行分组,分组后平均薪水大于1500,按工资倒序排列 select deptno,avg(sal) from emp where sal > 20

20、00 group by deptno having avg(sal) >1500 order by avg(sal) desc; /select-where-group by- having-order by/第十二课:子查询 1.select 语句中嵌套select 语句,求哪些人工资在平均工资之上. select ename,sal from emp where sal > (select avg(sal) from emp); 2.查找每个部门挣钱最多的那个人的名字. select ename, deptno from emp where sal in (select max

21、(sal) from emp group by deptno) 查询会多值.正确写法是: 应把select max(sal),deptno from emp group by deptno当成一个表,语句如下: select ename,sal from emp join (select max(sal) max_sal,deptno from emp group by deptno) t on (emp.sal = t.max_sal and emp.deptno = t.deptno); 第十三课:self_table_connection 自连接：为同一张表起不同的别名，然后当成两张表来

22、用 select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno; /求这个人的名字和他经理人的名字(自连接) 第十四课:SQL1999_table_connection 1.select ename,dname,grade from emp e,dept d,sqlgrade s where e.deptno=d.deptno and e.sal between s.losal and s.hisal and job <> 'CLERK' 这种写法没有把过滤条件和连接条件分开来,由于这个原因

23、,sql1999标准推出来了. 2.select ename,dname from emp,dept;(旧标准) select ename,dname from emp cross join dept;(1999标准) 3.select ename,dname from emp,dept where emp.deptno = dept.deptno;(旧) select ename,dname from emp join dept on (emp.deptno = dept.deptno) 4.select ename,grade from emp e join salgrade s on(s

24、.sal between s.losal and s.hisal); 5.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%' 6.select e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.deptno); 7.select e1.ename,e2.enam

25、e from emp e1 left (outer) join emp e2 on (e1.mgr = e2.empno); 8.select ename,dname from emp e right outer join dept d on (e.deptno = d.deptno); 9.select ename,dname from emp e full join dept d on (e.deptno = d.deptno); 第十六至二十三课:求部门平均薪水等级 1.A.求部门平均薪水的等级 select deptno,avg_sal,grade from (select deptn

26、o,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) 2.求部门平均的薪水等级 select deptno,avg(grade) from (select deptno,ename,grade from emp e join salgrade s on (emp.sal between s.losal and s.hisal) t group by deptno; 3.哪些人是经理 select ename2 from (select e

27、1.ename ,e1.mgr mgr1,e2.ename ename2 from emp e1 join emp e2 on (e1.mgr = e2.empno) t join emp on (t.mgr1 = emp.empno) select ename from emp where empno in (select distinct mgr from emp) 4.不准用组函数,求薪水的最高值 select distinct sal from emp where sal not in( select distinct e1.sal from emp e1 join emp e2 on

28、 (e1.sal < e2.sal) ) 5.求平均薪水最高的部门编号 select deptno,avg_sal from ( select deptno,avg(sal) avg_sal from dept group by deptno ) where avg_sal = ( select max(avg_sal) from ( select deptno,avg(sal) avg_sal from dept group by deptno ) ) select deptno,avg_sal from (select deptno,avg(sal) avg_sal from dep

29、t group by deptno) where avg_sal = (select max(avg(sal) from dept group by deptno) 6.求平均薪水最高的部门名称 select dname from dept where deptno = ( select deptno from (select deptno,avg(sal) avg_sal from dept group by deptno) where avg_sal = ( select max(avg_sal) from (select deptno,avg(sal) avg_sal from dept

30、 group by deptno) ) ) 7.求平均薪水等级最低的部门的部门名称 错误写法: select min(avg_grade),deptno from ( select deptno,avg(grade) avg_grade ( select ename,deptno,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal) ) group by deptno ) group by deptno 正确写法: select dname,t1.deptno,grade,avg_sal from ( s

31、elect deptno,avg_sal,grade 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) ) t1 join dept on (t1.deptno = dept.deptno) where t1.grade = ( select min(grade) from ( select deptno,avg_sal,grade from (select deptno,avg(sal) avg_

32、sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) ) ) 8.视图:view就是一张虚表,一个子查询 conn sys/change_on_install as sysdba; grant create table,create view to scott; conn scott/tiger; 创建视图 create view v$_dept_avg_sal_info as select deptno,avg_sal,grade from (select dept

33、no,avg(sal) avg_sal from emp group by deptno) t join salgrade s on (t.avg_sal between s.losal and s.hisal) 使用视图后可以简写 select dname,t1.deptno,grade,avg_sal_info from v$_dept_avg_sal t1 join dept on (t1.deptno = dept.deptno) where t1.grade = ( select min(grade) from v$_dept_avg_sal_info ) 9.求比普通员工最高薪水还

34、要高的经理人的名称 先求普通员工的最高薪水 select max(sal) from emp where empno not in (select distinct mgr from emp where mgr is not null); select ename from emp where empno in (select distinct mgr from emp where mgr is not null) and sal > ( select max(sal) from emp where empno not in (select distinct mgr from emp w

35、here mgr is not null); ) 第二十四课 备份与恢复 -backup scott;exp;import the date; DOS环境下:exp导出,imp导入 -drop user yugang cascade; /删除YUGANG 用户 -cd -cd temp =-delete *.* -exp -create user yugang indentified by yugang default tablespace users quota 10M on users /创建用户 -grant create session,create table,create view

36、 to yugang /分配权限 -imp the data 第一次输入的用户名密码为:yugang/yugang 第二次输入的用户名密码为:导出数据的用户的用户名和密码 insert into dept valus(50,'fghfgh','gj');/插入数据 -rollback /取消刚才的操作 create table emp2 as select * from emp; /复制emp第二十五课:rownum rownum是在Oracle中在表的后面加的一个尾字段,并且只能使用诸如rownum<n或rownum<=n这样的形式,不能与>

37、或>=相连接在 Oracle里面有一个尾字段叫->rownum 它是按照1，2，3.进行排列的，它不显示 1.求薪水值最高的前5条记录. select ename,sal from emp order by sal desc where sal <= 5; 2.求薪水值最高的第610条记录. select ename,sal from ( select ename,sal,rownum r from (select ename,sal from emp order by sal desc) ) where r >=6 and r <= 10; 第二十六课:home

38、work_dml_transaction SQL面试题 有三张表S、C、SC S(SNO、SNAME)代表（学号、姓名） C(CNO、CNAME、CTEACHER)代表（课号、课名、老师） SC(SNO、CNO、SCGRADE)代表（学号、课号、成绩） 1.求出没选过郭富城老师的所有学生姓名 2.列出2门以上（含2门）不及格的学生姓名及平均成绩 3.既学过1号课程又学过2号课程的所有学生姓名 1.select sname from s join sc on (s.sno = sc.sno) join c on (o = o) where c.cteacher <> '郭富城

39、' 2.select sname from s where sno in ( select sno from sc where scgrade < 60 group by sno having count(*) >= 2 ) 3.select sname from s where sno in ( select sno from sc where cno=1 and sno in (select distinct sno from sc where cno=2) ) update 更新表中的数据 把emp2这张表里，编号为10的，所有人的薪水提1倍 update emp2

40、set sal = sal*2, ename = ename|'-' where depton = 10/修改数据 delete from dept2 where depton = 10;四条语句讲完了：背过语法格式 DDL- 数据定义语言，建表，建视图，建其他一些东西。 1.创建表- create table create table t (a varchar2(10); 2.删除表 drop table drop table xx; 跳过此阶段，先讲事务控制语句 事务transaction代表一组不可分割的操作，要么全部执行，要么全部不执行， transaction起始于一

41、条DML语句，结束于commit;语句，或者是DCL、DDL语句，在事务未提交前 可以通过rollback回滚事务，正常退出事务会自动提交，非正常退出事务会自动回滚。 第二十七课：create table 为什么有变长字符串Varchar2还要有定长字符串Char呢? 因为存在效率问题，用Char效率高，但浪费空间，这就是拿空间换时间Hashtable也是拿空间换时,创建一张表，并往里插入一条数据 create table student ( id number(6), name varchar2(20), sex number(1), age number(3), sdate date, g

42、rade number(2) default 1, class number(4), email varchar2(50) ); 第二十八三十课 主键：PRIMARY KEY 叫做：可以唯一标识整条记录的这样的一个东西 从语法上来说主键的约束可以理解为：非空 ，唯一 两个的组合主键约束在逻辑意义上代表着单独的，每一条记录，看到主键就知道你是一条单独的不同的记录，是唯一的代表的记录 我们可以用两个字段的组合作为主键 外键(最麻烦的约束)：建立于一张表的两个字段，或者两张表的两个字段 constraint stu_class_fk foreign key (class)references cla

43、ss (id)外键关系：是建立在两个字段上，某一个字段会参考另外一个字段里面的值，如果另外一个字段里面没有这个值，你不能够把这个字段里面的值，设置成为其他的值。被参考的字段必须是主键，被其他人参考的字段不能作为删除条件主键约束和外键约束非常重要，老牢牢掌握其概念1.唯一约束->在这个字段里面所有的记录不能取重复的值，每个值必须是唯一的 唯一约束后面 + unique ，当然也可以用constraint + 名字 字段级的约束：把约束条件+在字段名的后面 表级的约束：+在所有字段名的最后面constraint stu_name_email_uni unique(email,name) 表示

44、的是：这两个字段的组合不能重复2.constraint/非空约束约束条件本身也是一个对象，本身约束条件你个可以为它起一个名字 用constraint stu_name_nn not null ->为约束条件not null起名字为：stu_name_nn如果不给约束条件起名字，系统会默认的给约束条件起一个名字 not null,unique(当某字段有unique约束时，可以插入空值，空值之间不重复)、主键、外键、 check约束 create table student ( id number(6), name varchar2(20) constraint stu_name_nn n

45、ot null, （字段级约束） sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50) unique /unique唯一约束 都为空可以 ) / 行级约束（放在字段后面）与表级约束（加在表后面）： create table student ( id number(6), name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate

46、 date, grade number(2) default 1, class number(4), email varchar2(50), constraint stu_email_name_uni unique(name,email) /name,email的组合唯一约束（表级约束） ) / 主键约束 create table student ( id number(6) primary key, name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date, grad

47、e number(2) default 1, class number(4), email varchar2(50), constraint stu_email_name_uni unique(name,email) ) / create table student ( id number(6), name varchar2(20) constraint stu_name_nn not null, sex number(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar

48、2(50), constraint stu_id_pk primary key(id), constraint stu_email_name_uni unique(name,email) ) / 外键约束 外键约束被参考的字段必须是主键。 create table class ( id number(4) primary key, name varchar2(20) not null ) / create table student ( id number(6), name varchar2(20) constraint stu_name_nn not null, sex number(1),

49、 age number(3), sdate date, grade number(2) default 1, class number(4) references class(id), email varchar2(50), constraint stu_id_pk primary key(id), constraint stu_email_name_uni unique(name,email) ) / create table student ( id number(6), name varchar2(20) constraint stu_name_nn not null, sex numb

50、er(1), age number(3), sdate date, grade number(2) default 1, class number(4), email varchar2(50), constraint stu_id_pk primary key(id), constraint stu_class_fk foreign key (class) references class(id), constraint stu_email_name_uni unique(name,email) ) /第三十一课：alter table drop table /修改表结构； alter table student add(addr varchar2(100); /添加结构 alter table student drop(addr); /删除结构 alter table student modify(addr varchar2(50); /修改 若addr字段中有的记录长度大于50，则修改不成功。 alter table studen