Orcale职业培训笔记.doc

Day011. s_emp、s_dept表的字段含义first_name名last_name姓title职位dept_id部门号commission_pct提成（有空值）2. 列出所有人的年薪select first_name,salary*12 from s_emp;3. 给列起别名select first_name,salary*12 Ann_Sal from s_emp;select first_name,salary*12 Ann Sal from s_emp;select first_name,salary*12 as Ann Sal from s_emp;4. 处理空值的函数nvl(p1,p2)nullOracle当做无穷大来处理空值不等于0空值不等于空格算数表达式中为空值，返回空值select first_name , salary*12*(1+nvl(commission_pct,0)/100) from s_emp;5. SQLPLUS命令：a) L列出上一次敲入的命令b) clear scr或者 ！clear清屏6. 字段（列名）拼接|字符串拼接Oracle中字符和字符串用单引号表示双引号用于表示别名select first_name| |last_name employee from s_emp;select first_name| is int department |dept_id|. from s_emp;7. 去除重复值 distinct#该公司有哪些职位？select distinct title from s_emp;#各个部门有哪些不同的职位？#distinct的功能：部门号单独重复，职位单独重复，部门号和职位联合不重复select distinct dept_id,title from s_emp;#会报错，因为distinct只能出现在select后面，否则会造成逻辑不通select dept_id,distinct title from s_emp; (X)8. 列出表中所有字段#注意：写*会降低效率，公司中一般会禁止写*;select * from s_emp;9. Oracle中写SQL大小写区别在功能上无影响，性能上有影响#注意：写SQL，一般公司都有规范10. where控制子句#年薪大于1.2w的员工的年薪?#如果salary字段上建了索引，第一种写法，索引用不上，所以慢select first_name,salary*12 a_sal from s_empwhere salary*12 12000;#如果salary字段上建了索引，第二种写法，效率高些select first_name,salary*12 a_sal from s_empwhere salary1000;#会报错，where子句后面不可以跟“列别名”,where子句执行在select语句之前select first_name,salary*12 a_sal from s_empwhere a_sal 12000;#EX.不会报错，order by子句可以使用“别名”select first_name,salary*12 a_sal from s_emporder by a_sal;11. 注意：单引号中大小写敏感#列出Carmen的年薪是多少？Select first_name,salary*12 a_salFrom s_empWhere first_name = Carmen;12. 大小写转换函数lower() upper()#列出Carmen的年薪是多少？select first_name , salary*12 a_salfrom s_empwhere lower(first_name) = carmen;13. where salary between 1000 and 1500;AND和between and连接符#找出员工工资在1000与1500之间select first_name , salaryfrom s_empwhere salary=1000 and salary=1500;#between and就表示了如上含义select first_name , salaryfrom s_emp14. OR连接符 IN（）表述形式 =ANY()#找出31、41、43部门员工的姓名和部门号？select first_name , dept_idfrom s_empwhere dept_id=31 or dept_id=41 or dept_id=43;#简单的表述形式in（）select first_name , dept_idfrom s_empwhere dept_id in (31 , 41 , 43);#另一种表述形式 in()相当于=any()select first_name , dept_idfrom s_empwhere dept_id = any(31, 41, 43);#从连续区间中取值使用Between-And，从离散数值中取值用IN（）15. LIKE运算符SUBSTR（）函数Length（）函数（通配符：%表示0或多个字符；_表示任意单个字符）#效率高些where last_name like M%#结果等同如上where substr(last_name , 1 , 1) = M;#列出名字的最后两个字母select first_name , substr(first_name , -2 ,2) from s_emp;#列出名字的最后两个字母 length(）函数select substr(first_name , length(first_name)-1 ,2) from s_emp; 16. escape关键字（表示后边的符号不是通配符）select talble_name from user_tableswhere talbe_name like S_% escape ;17. IS NULL判断字段是否为空IS NOT NULLselect first_name , commission_pct from s_emp where commission_pct is null;18. NOT BETWEEN ANDNOT IN()NOT LIKEIS NOT NULL#除了31、41、43部门的部门员工的情况select first_name , dept_id from s_emp where dept_id not in(31, 41 , 43); #等价写法select first_name , dept_id from s_emp where dept_id != 31 and dept_id!=41 and dept_id!=43;#等价写法all(31,41,43)select first_name , dept_id from s_emp where dept_id all (31,41,43);#任何数据与NULL比较，都返回false，#使用not in()时，如果集合中有null值，则查不出任何记录，对in()没影响select first_name , dept_id from s_emp where dept_id not in(31, 41 , 43,null); 19. 注意下两句SQL的区别，理解OR和AND# 找出部门号为44，工资大于1000的员工或者部门号为42的所有员工?select last_name , salary , dept_idfrom s_empwhere salary =1000 and dept_id=44 or dept_id=42;#找出部门号为44或者42的，并且工资大于1000的员工select last_name , salary , dept_idfrom s_empwhere salary =1000 and (dept_id=44 or dept_id=42);20. 隐式数据类型转换#如下式相同的结果，系统做了隐式数据类型转换，均为：字符转数值select first_name , salary from s_emp where salary = 1450;select first_name , salary from s_emp where salary = 1450;#相当于select first_name , salary from s_emp where to_number(salary) = 1450;#做严格的数据类型匹配相当重要select first_name , salary from s_emp where salary = 1450;21. 显式数据类型转换to_char()函数#输出所有员工的manager_id，如果没有manager_id，则用BOSS填充select first_name , nvl(to_char(manager_id) , Boss) from s_emp;Day021. 表和表之间的关系s_emp员工表s_dept部门表s_region部门所在地区表salgrade工资等级表emp员工表dept部门表2. 等值连接#查询Carmen所在部门的地区？（Canmen在哪个地区上班？）#中间表“部门表”#用几张表就JOIN几次#等值连接（内连接的一种）：父表的主键=子表的外键select e.first_name , from s_emp e join s_dept don e.dept_id = d.idand e.first_name = Carmen -为什么 where 不行？3. And在外连接之前做过滤，where在外连接之后做过滤join s_region r on d.region_id = r.id;#亚洲地区有哪些员工？select e.first_name , from s_emp e join s_dept don e.dept_id = d.idjoin s_region r on d.region_id = r.idand = Asia; -为什么where 能代替 and4. 非等值连接#列出员工的工资以及对应的工资级别？select e.ename , e.sal , s.gradefrom emp e join salgrade son e.sal between s.losal and s.hisal;#SMITH的工资级别？select e.ename , e.sal , s.gradefrom emp e join salgrade son e.sal between s.losal and s.hisaland e.ename = SMITH;#3, 5级有哪些员工（哪些员工属于3,5级）？select e.ename , e.sal , s.gradefrom emp ejoin salgrade son e.sal between s.losal and s.hisaland s.grade in(3,5); 5. 自连接#列出员工名和领导名的对应关系#结果为24个，少一个manager_id为空的人（BOSS丢了）select e.id,e.first_name emplayee ,m.id, m.first_name managerfrom s_emp e join s_emp mon e.manager_id = m.id;#列出哪些人是领导？select distinct m.first_namefrom s_emp e join s_emp mon m.id = e.manager_id;6. outer join外连接# 内连接from t1 join t2 on t1.id = t2.id#from t1 left outer join t2 on t1.id = t2.id左边的表做驱动表#from t1 right outer join t2 on t1.id = t2.id右边的表做驱动表#外连接解决的问题：驱动表中的记录在结果集中“一个都不少”#列出员工名和领导名的对应关系？select e.first_name employee , nvl(m.first_name,Boss) managerfrom s_emp eleft outer join s_emp mon e.manager_id = m.id;#如何写外连接：#先写出内连接，再确定哪张表当驱动表就可以#哪个部门没有员工？14条记录，少1条select e.ename , e.deptnofrom emp e join dept don e.deptno = d.deptno;#哪个部门没有员工？15条记录select e.ename , e.deptno , d.deptno ,d.dnamefrom emp e right join dept don e.deptno = d.deptno;#哪个部门没有员工？15条记录select d.deptno ,d.dname , e.ename , e.deptnofrom emp e right join dept don e.deptno = d.deptnowhere e.empno is null;#使用外连接解决了两类问题：1. 把所有结果列出到结果集2. 解决否定问题（不是，没有，不包含）#那些人是员工？(即：那些人不是领导？)#思路：#先解决那些人是领导#能匹配的是领导 #把匹配不上的挑出来#select e.first_name , m.first_namefrom s_emp e right join s_emp mon e.manager_id = m.id;# 加条件select e.first_name , m.first_namefrom s_emp e right join s_emp mon e.manager_id = m.idwhere e.id is null;-为什么用and 会 出现结果错误？、/-# 最后列出m.first_name即可select m.first_namefrom s_emp e right join s_emp mon e.manager_id = m.idwhere e.id is null; 用 and 和where 会得到不同的结果 什么时候用and 什么时候用where7. And在外连接之前做过滤，where在外连接之后做过滤8. Where 在得出结果以后过滤 很重要别把# select e.ename , d.dnamefrom emp e right join dept don e.deptno = d.deptnoand e .ename = SMITH;#驱动表的过滤全部写在where之后select e.ename eename , d.dname denamefrom emp e right join dept don e.deptno = d.deptnoand e.ename = SMITH;where e.empno is null;#选择left jon 或者right join不重要，重要的是选择哪张表做驱动表9. full out join用的比较少10. 组函数#组函数：一堆数据返回的结果#max()#avg()#min()#avg()#求所有人的平均工资？#求所有人的平均提成？select avg(nvl(commission_pct,0) from s_emp;#count()处理的结果如果全为空值，结果返回0#计算有多少条记录select count(id) from s_emp;#求按提成分组，计算人数？select commission_pct , count(id)from s_empgroup by commission_pct;#count()函数中可以加入关键字select count(title) from s_emp;#等同于select count(all title) from s_emp; #把重复值去掉，再做统计select count(distinct title) from s_emp;#列出42号部门的平均工资#若有groupby子句，select后面可跟group by后面跟的表达式以及组函数，其他会报错。关于 group by 选择 having 还是 whereWhere 在分组之前过滤写在 group之前 效率高Having 写在group by 之后在分组之后做过滤；select dept_id , avg(salary)from s_emp where dept_id=42group by dept_id;#若没有group by子句，select 后面有一个组函数，其他都必须是组函数select max(dept_id) , avg(salary)from s_emp where dept_id=42;作业：insert into salgrade values (6,10000,15000);#列出每个工资级别有多少员工？#列出3,5级有多少员工#列出每个工资级别有多少员工（若该级别没有员工，也要列出）Day031. 子查询#先执行子查询；子查询只执行一遍#若子查询返回值为多个，Oracle会去掉重复值之后，将结果返回主查询#谁是受老板剥削工资最低的人？select first_name , salaryfrom s_empwhere salary = (select min(salary) from s_emp);#谁跟SMITH的职位是一样的？select last_name , title from s_emp where title = (select title from s_emp where last_name=Smith)and last_name != Smith;#如果表中有重复值，如两个Smith，会报错：#single-row subquery returns more than one row单行子查询返回多行#修改为： in可以不？ 可以select last_name , title from s_emp where title = any (select title from s_emp where last_name=Smith)and last_name != Smith;#哪些部门的平均工资比32部门的工资高？select dept_id , avg(salary)from s_empgroup by dept_idhaving avg(salary) (select avg(salary) from s_emp where dept_id = 32);#那些人是领导？子查询select first_namefrom s_empwhere id in (select manager_id from s_emp);#那些人是领导？表连接select distinct m.first_namefrom s_emp mjoin s_emp eon e.manager_id = m.id;#Ben的领导是谁？子查询select first_namefrom s_empwhere id =(select manager_id from s_emp where first_name=Ben);#Ben领导谁？子查询select first_namefrom s_emp where manager_id =(select id from s_emp where first_name=Ben);#Ben的领导是谁？ 表连接select m.first_namefrom s_emp mJoin s_emp eon e.first_name = Ben and e.manager_id = m.id;#Ben领导谁？表连接select e.first_namefrom s_emp ejoin s_emp mon m.first_name=Ben and e.manager_id = m.id;#select first_namefrom s_empwhere id in (select manager_id from s_emp);#演示代码#对not in来说，结果集中如果有null，则整个结果集为null#结论：对not in来说，子查询结果集中是不能有null的select first_namefrom s_empwhere id not in (select manager_id from s_emp);#查询那些人是员工？select first_namefrom s_empwhere id not in (select manager_id from s_emp where manager_id is not null);#not in尽量不用2. 子查询与空值#哪些部门的员工工资等于本部门员工平均工资？# 多列select first_name , dept_id , salaryfrom s_empwhere (dept_id , salary) in (select dept_id , avg(salary) from s_emp group by dept_id);3. 关联子查询4. 同表中一列相等 一列比大小用关联子查询#哪些员工的工资比本部门的平均工资高？select first_name , dept_id , salaryfrom s_emp outerwhere salary (select avg(salary) from s_emp innerwhere outer.dept_id = inner.dept_id);5. 常用的关联子查询：EXISTS NOT EXISTS#找到即返回#哪些部门有员工？select dname from dept owhere exists(select 1 from emp iwhere o.deptno = i.deptno);#那些人是员工？select first_name from s_emp awhere not exists(select 1 from s_emp b where a.id =b.manager_id);总结：子查询：非关联in / not in(不建议)关联exists (比inner join优势)/ not exist(即outer join + is null) 6. IN和EXISTS的比较（非关联和关联子查询的比较）7. 标量子查询#列出员工名和领导名？select first_name employee , (select first_name from s_emp i where o.manager_id = i.id) Managerfrom s_emp o;8. CASE WHEN表达式#实现31部门，32部门工资分别涨1.1倍和1.2倍？ #如果没有else返回空值select first_name , salary,case when dept_id = 31 then salary*1.1when dept_id = 32 then salary*1.2elsesalaryendala_salfrom s_emp;#工资1000涨300块，1000工资1500涨500，其他人不动select first_name , salary,case when salary1000 and salary1000); 2. primary key约束(主键约束)第一种形式：create table test(c number primary key ); 第二种形式：create table test(c number , primary key(c) ) ; 表级约束create table test( c1 number constraints pkc1 primary key ); 此约束有名字: pkc13. foregin key (fk) 外键约束:(先定义父表，再定义子表）carete table parent(c1 number primary key );create table child (c number primary key , c2 number references parent(c1);或表级约束定义:create table child( c number primary key , c2 number , foreign key(c2) references parent(c1);create table test(c1 number primary key); 设置主键create table test(c1 number constraints test_c1 primary key); 定义约束名,默认约束名为SYS_ 在列后面定义约束称为列级约束create table test(c1 number primary key(c1); 所有列定义完后再定义约束称为表级约束(能定义联合主键)cretae table test(c1 number,c2 number,priary key(c1,c2); 定义联合主键create table child(c1 number primary key); 先要定义父表create table child(c1 number primary key, c2 number references parent(c1); 然后定义子表 references parent定义外键create table child(c1 number primary key, c2 number references parent(c1) on delete cascate); on delete cascate为级联删除create table child(c1 number primary key, c2 number references parent(c1) on delete set null); on delete set null删除后将外键置空create table child (c1 number primary key, c2 number,foreignkey(c2) references parent(c1); 4. 两表没有任何关联时会产生迪卡尔乘积：select first_name , name from s_emp , s_dept;5. insert操作，插入记录(DML操作 )insert into student value(1,xxx,xxx);insert into student(id,name,address) value(1,xxx,xxx);注意：有空值的话: 隐式插入 INSERT INTOs_dept (id, name) VALUES(12, MIS); 不往想为空的字段中插数据，系统默认为NULL 显示插入 INSERT INTOs_dept VALUES(13, Administration, NULL); select * from s_emp where 1=2; 这样选不出纪录，方便察看表结构6. update修改操作update table 表名 set 字段名1=数据1或表达式1， 字段名2=数据2或表达式2 where .=.;update shenfenzhen set num=99 where sid=2; 7. delete删除操作delete from 表名 where .=.;用delete操作删除的记录可以通过 rollback命令回滚操作，会恢复delete操作删除的数据。delete操作不会释放表所占用的空间，delete不适合删除记录多的大表。delete操作会占用大量的系统资源。8. alter table命令alter table 命令用于修改表的结构(这些命令不会经常用)：增加字段：alter table 表名add(字段字，字段类型)删除字段：alter tbale 表名 drop column 字段; (8i 以后才支持)给列改名:9.2.0才支持alter table 表名 rename column 旧字段名 to 新字段名;修改字段alter table 表名 modify( 字段，类型)(此时应注意的问题，更改时要看具体值情况之间的转达换，改为字符类型时，必须要为空)not null约束是使用alter table . modify (.,not null)，来加上的。增加约束：alter table 表名 add constraint 约束名 约束(字段);只能够增加表级约束。解除约束：(删除约束)alter table 表名 drop 约束;（对于主键约束可以直接用此方法，因为一张表中只有一个主键约束名, 注意如果主键此时还有其它表引用时删除主键时会出错）alter table father drop primary key cascade; (如果有子表引用主键时，要用此语法来删除主键,这时子表还存在只是子表中的外键约束被及联删除了）alter table 表名 drop constraint 约束名;(怎样取一个约束名：a、人为的违反约束规定根据错误信息获取!b、查询视图获取约束名!)使约束失效或者生效alter table 表名 disable from primary key; (相当于把一个表的主键禁用)alter table 表名 enable primary key;（enable 时会自动去检查表的记录是不是符合要求,如果有脏数据时必须要先删除脏数据才可以 enable）更改表名rename 旧表名 to 新表名;删除表：trucate table 表名;(表结构还在，数据全部删除，释放表所占的空间，不支持回退,常用删除大表)Day05 今天的note不全，请自己补充1. 哪些列更适合做索引#表大，结果集小经常出现在where子句的列经常用于表连接的列主键列PK、唯一键列UKcreate table test(create unique index test_c1_idx on test(c1);2. 新的建表语句create table test_01as select * from test;3. 一些概念建索引的目的就是为了加快查询速度。索引就相于一本的书的目录。索引点系统空间，属于表的附属物。删除一个表时，相对应的索引也会删除。索引是会进行排序。4. 查看表的rowid#每条记录都有自己的rowidselect rowid,first_name from s_emp;5. 创建视图creating views 视图名;视图就相当于一条select 语句,定义了一个视图就是定义了一个sql语句,视图不占空间,使用view 不会提高性能，但是能简单化sql语句（扩展知识： oracle 8i 以后的新视图）MV 物化视图(占存储空间，把select 结果存在一个空间，会提高查询视图,增强实时性，但是存在刷新问题，物化视图中的数据存在延迟问题，主要应用在数据仓库中用要用于聚合表)使用视图的好处：控制数据访问权限。如何创建一个视图的例子：create or replace views test_vi as select * from test1 where c1=1;or replace的意义，如果view存在就覆盖，不存在才创建。force|no force ，基表存在是使用，不存在是则创建该表。此时往表test1（base table 基表）中插入数据时：表中没能变化，视图中的数据发生改变从视图中插数据时相对应的表会发生改变:往视图中插数据时，会直接插进基表中，查看视图中的数据时，相当于就是执行创建时的select语句。限制对数据库的访问，简化查询。简单视图：来自于单表，且select语句中不能包括函数，能进行DML操作。复杂视图：来源于多张表，不能执行DML操作。6. 删除视图 drop views 视图名7. 行列转置问题8. 子查询还可以跟在from后面select e.first_name ,e.salary , a.asalfrom s_emp e join(select dept_id , avg(salary) asal from s_emp group by dept_id) aon e.dept_id = a.dept_idand e.salary a.asal;9. Rownum#出现两条记录select first_name , rownum from s_emp where rownum =2;#无记录，因为rownum的特点是必须从1条记录开始找select first_name , rownum from s_emp where rownum between 4 and 9;#排名问题：找出前N条记录#找出工资最高的前10个人？select rownum , first_name , salary from (select first_name , salary from s_emp order by salary desc)where rownum=10;#分页问题#找出第11条到第20条的记录（先找出前20条记录，再过滤掉前10条）#关键点：给rownum起别名#越往后走越慢，这种做法效率较低select rn , first_name , salaryfrom (select rownum rn , first_name , salaryfrom s_empwhere rownum =20)where rn between 11 and 20;10. (集合运算)Union 和Union ALL拼结果集#去重select deptno from deptunionselect deptno from emp;#不去重select deptno from deptunion allselect deptno from emp;11. (集合运算)intersect去重后的交集select deptno from deptintersectselect deptno from emp;12. (集合运算)minus找出不包含的select deptno from deptminusselect deptno from emp;1