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 salary>1000;#会报错，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 , r.namefrom 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 , r.namefrom s_emp e join s_dept don e.dept_id = d.idjoin s_region r on d.region_id = r.idand r.name = '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 salary<1000 then salary+300when salary>1000 and salary<1500 then salary+500elsesalaryendala_salfrom s_emp;9. DECODE函数(等同于Case when)#decode(参数1，参数2，参数3，参数11，参数12，参数13.)#表示如果参数1的值为参数2则参数3；参数11的值为参数12则参数13.select first_name, salary,decode(dept_id , 31 , salary*1.1,32,salary*1.2,33,salary*1.3,salary) aft_salfrom s_emp;select first_name from s_emp outerwhere not exists (select 1 from s_emp inner where inner.id=outer.manager_id);#列出所有员工select first_namefrom s_emp outerwhere not exists(select 1 from s_emp inner where outer.id =inner.manager_id);Day041. 约束not null（非空约束）这是一个列级约束。在建表时,在数据类型的后面加上 not null ，也就是在插入时不允许插入空值。 create table student(id number primary key,name varchar2(32) not null,address varchar2(32);primary key （主键约束 PK）保证记录的主键唯一且非空，并且每一个表中只能有一个主键。foreign key （外建约束 FK）被引用的表，叫做parent table（父表），引用方的表叫做child table（子表），要想创建子表，就要先创建父表，后创建子表，记录的插入也是如此，先父表后子表，删除记录，要先删除子表记录，后删除父表记录，要修改记录，如果要修改父表的记录要保证没有被子表引用。要删表时，要先删子表，后删除父表。unique key（唯一键），值为唯一的, 如果创建一个uk，系统自动建一个唯一索引唯一约束，是会忽略空值的，唯一约束，要求插入的记录中的值是为一的。create table student(id number，name varchar2(32),address varchar2(32),primary key (id),unique (address); check约束检查约束，可以按照指定条件，检查记录的插入。check中不能使用尾列，不能使用函数，不能引用其他字段。create table sal (a1 number , check(a1>1000); 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(