Oracle+总结 (1)

OracleSQL datebase (DB)Structured query language 结构化查询语言DDL(数据定义语言)Date definition languagecreate table 建表alter table 修改表结构drop table 删表column data type width constraints(约束)DML(数据操作语言)Data manipulation languageinsert update deletedata row recordTCL(事物控制语句)Transcation control languagecommit(提交) rollback(回滚) savepointDQL(数据查询语言)Data query languageselectInstall software rdbms 安装软件create database 创建数据库登录数据库sqlplus username/passwordORACLE_SID(环境变量) 数据库对应的实例的名字，该名字决定了连接哪个具体的数据库show user查看当前用户desc tablename查看表结构desc describe的缩写查询员工的姓名，和工资select first_name,salary from s_emp;查询员工的名字和职位select first_name,title from s_emp;edit修改sql语句l 查看/运行select * from s_dept; 列出部门表的所有信息列出每个员工的年薪select first_name,salary*12 from s_emp;列出每个员工的总收入select first_name,salary*12*(1+commission_pct/100) "tol sal" from s_emp;空值会导致算术表达式为空，Oracle认为null为无穷大select first_name,salary*12*(1+nvl(commission_pct,0)/100) tol_sal,Commission_pctfrom s_empnvl(p1,p2)if(p1 is null) then return p2;else return p1;coalesce 和nvl实现的是同样的功能，nvl只能用在Oracle，coalesce可以用在多种数据库,nvl的两个参数的类型必须一致给列起别名在列后直接跟别名，别名有空格，或者大小写敏感，要给别名加“”将姓和名拼接起来''表示字符串，“”表示别名，||表示字符串的拼接select first_name||last_name employee from s_emp;select first_name||' '||last_name employee from s_emp;select first_name||' is in department '||dept_id||'.' from s_emp;列出有哪些部门select distinct name from s_dept;distinct 去重(null值夜只保留一个)，只能放在select后列出公司有哪些不同的职位select distinct title from s_emp;set feed on显示查询返回的记录数各个部门有哪些不同的职位select title,dept_id from s_emp;部门号和职位联合起来唯一列出工资大于1000的员工select first_name,salary from s_emp where salary>1000;年薪大于12000的员工(where语句后的字段最好不要使用表达式，影响效率) select first_name,salary*12 from s_emp where salary>1000;where子句不能跟列的别名列出Carmen的年薪select first_name,salary from s_emp where first_name='Carmen';哪些人的职位是Stock Clerkselect first_name,title from s_emp where title='Stock Clerk';哪些员工的工资在1550-2000之间select first_name,salary from s_emp where salary>=1550 and salary<=2000;select first_name,salary from s_emp where salary between 1550 and 2000;列出部门号为31,41,43的员工的工资select first_name,salary,dept_id from s_emp where dept_id=31 ordept_id=41 or dept_id=43;select first_name,salary,dept_id from s_emp where dept_id in (31,41,43);select first_name,salary,dept_id from s_emp where dept_id =any (31,41,43); %表示0或多个字符_表示任意一个字符select last_name from s_emp where last_name like 'M%';系统表，user_tables 记录数据库中有哪些表查询当前用户下有哪些表select table_name from user_tables;查找用户下所有以“s_”的表名select table_name from user_tables where table_name like 'S\_%' escape '\';escape '\' 表示定义\为转义字符哪些员工没有提成select first_name,commission_pct from s_emp where commission_pct is null; 判断字段的值是否为空is null 而不是=null哪些员工有提成select first_name,commission_pct from s_emp where commission_pct is not null;列出除了31,41,43部门的员工的工资select first_name,dept_id,salary from s_emp where dept_id not in (31,41,43);select first_name,dept_id,salary from s_emp where dept_id <>31 anddept_id <>41 and dept_id <>43;select first_name,dept_id,salary from s_emp where dept_id <>all (31,41,43); 如果集合里面包含null值，not in 一定查不出结果，null与任何值比较都为false列出部门号为32,42，工资大于1500的员工select first_name,dept_id,salary from s_emp where salary>1500 and dept_id in(32,42);列出员工的姓名和工资，并按工资由大到小排序select first_name,salary from s_emp order by salary desc;order by 后的字段要想使用索引，必须保证不能为nullselect first_name,salary from s_emp where salary is not null order by salary desc;列出42部门的员工信息，按年薪降序排列select first_name,dept_id,salary*12 ann_sal from s_emp where dept_id=42 order by salary desc;select first_name,dept_id,salary*12 ann_sal from s_emp where dept_id=42 order by ann_sal desc;select first_name,dept_id,salary*12 ann_sal from s_emp where dept_id=42 order by 3 desc;按部门号升序排列，同一部门按工资的降序排列select first_name,dept_id,salary from s_emp order by dept_id,salary desc;函数单行函数：每一条记录都对应有结果集字符类型lower()将字符串转化为小写select lower('SQL') from dual;upper()将字符串转化为大写initcap()将字符的首字母大写concat(string,string)字符串的拼接substr(string,1,3)字符串的截取,第二个参数表示字符串的起始位置，第三个表示截取的长度length(string) 字符串的长度lpad(string,10,'*')字符串的长度是10，不足的在左边补'*'rpad(string,10,'*')字符串的长度是10，不足的在右边补'*'调文字值和表没有关系的时候使用系统表dual查询Carmen的工资（不知道大小写）select first_name,salary from s_emp where lower(first_name)='carment';找出每个人名字的最后两个字符select first_name,substr(first_name,length(first_name)-1,2) from s_emp;select first_name,substr(first_name,-2,2) from s_emp;负数表示从右向左数值类型round(处理的数字,保留小数点后几位)四舍五入trunc(处理的数字,截取几位)截取日期类型转换函数to_number('1550')转化为数字to_number(字段,'xx')按16进制处理字段值隐式数据类型转换，默认字符转为数字，可能导致索引用不了to_char(字段,'$99,999,99')按格式转化成字符串格式：9代表数字位0代表定义宽度大于实际值宽度，有0补齐L 表示本地货币如果显示位数不足（定义宽度小于实际值宽度），用#代替更改本地语言NLS_LANG='SIMPLIFIED CHINESE_CHINA.ZHS16GBK'export NLS_LANGNLS_LANG=AMERICAN_7ASCII将没有领导的员工的领导位置设为bossselect first_name,nvl(to_char(manger_id),'Boss') from s_emp; 多行函数多表查询列出员工的名字，和部门的名称cross join笛卡尔积，两个表的每条记录都匹配select first_name,dept_id,name from s_emp cross join s_dept;300条select e.first_name,e.dept_id,d.id, from s_emp e cross join s_dept d;300条如果两个表里有字段名相同，必须指出是哪个表的字段inner join 内连接(将满足条件的记录匹配,精确匹配)可省略的写成join select e.first_name,e.dept_id,d.id, from s_emp e inner join s_dept d on e.dept_id=d.id; 25条列出部门号，地区号，地区名称select d.id,,r.id, from s_dept d join s_region r ond.region_id=r.id;内连接等值连接：两张表有描述共同属性的列非等值连接：可以用between and把两张表中的列写成表达式自连接：同一张表的列之间有关系实际反映的是同一张表的行之间有关系，通过给表起别名将同一张表的列之间的关系转换成不同表的列之间的关系所谓表之间的关系，实际指表中的行之间的关系，该关系通过将表中的列写成表达式来体现Carmen在哪个部门上班select e.first_name, from s_emp e join s_dept d on e.dept_id=d.id and e.first_name='Carmen';先做过滤在做连接亚洲地区有哪些部门select d.id,, from s_dept d join s_region r on d.region_id=r.id and ='Asia';Carmen在哪个地区上班select e.first_name, from s_emp e join s_dept d on e.dept_id=d.id and e.first_name='Carmen' join s_region r on d.region_id=r.id;亚洲地区有哪些员工select e.first_name, from s_emp e join s_dept d on e.dept_id=d.id join s_region r on d.region_id=r.id and ='Asia';列出员工的工资，名字，及工资的级别select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal betweens.losal and s.hisal;3级和5级有哪些员工select e.ename,e.sal,s.grade from emp e join salgrade s on e.sal betweens.losal and s.hisal and s.grade in (3,5);列出每个员工的领导select e.first_name,m.first_name from emp e join emp m one.manager_id=m.id;哪些人是领导select distinct m.first_name from emp e join emp m on e.manager_id=m.id;外连接left outer join right outer join outer可以省略from t1 left join t2 on t1.c1=t2.c2 表示t1做驱动表from t1 right join t2 on t1.c1=t2.c2 表示t2做驱动表外连接把驱动表不能匹配的记录也会放在结果集里，没有匹配上的字段设置为null，驱动表里的数据一个都不能少外连接的结果集=内连接的结果集+t1表中匹配不上的记录和一条null记录的组合关键：谁做驱动表列出每个员工的领导，并且打印出bossselect e.first_name,nvl(m.first_name,'Boss') from emp e left join emp m on e.manager_id=m.id;哪个部门没有员工select e.ename,d.deptno from emp e right join dept d on e.deptno=d.deptno where e.empno is null;这里用where不用and，因为and是先过滤在连接哪些人是员工(哪些人不是领导)select e.first_name,m.first_name from s_emp e right join s_emp m one.manager_id=m.id where e.id is null;哪个部门没有叫SMITH的员工select d.deptno,d.dname from emp e right join dept d on e.deptno=d.deptno and e.ename='SMITH' where e.empno is null;哪个部门没有叫SMITH的部门(Oracle)select d.dname from emp e,dept d where e,deptno(+)=d.deptno ande.ename(+)='SMITH' and e.ename is null;第一个+表示dept表为驱动表，第二个+表示在连接之前过滤组函数操作在一组记录上，每组返回一个结果处理的是所有的非空值（count(*)表示记录数）如果所有记录都是null，count返回0，其他返回空avg平均值(数值)sum求和（数值）count计数（字符，数字，日期）max最大值（字符，数字，日期）min最小值（字符，数字，日期）工资的平均值，提成的平均值，和提成的平均值select avg(salary),avg(commission_pct),max(commission_pct) from s_emp;所有人提成的平均值select sum(commission_pct)/count(*) from s_emp;select avg(nvl(commission_pct,0)) from s_emp;group by给记录分组根据group by子句指定的表达式，将要处理的数据（若有where子句即为通过条件过滤后的数据）分成若干组，每组有唯一标识，组内有若干条记录，根据select语句后的组函数处理每个组的记录，每个组返回一直值各个部门的平均工资select avg(salary),dept_id from s_emp group by dept_id;各个职位的平均工资select title avg(salary) from s_emp group by title;每个提成有多少人select commission_pct,count(*) from s_emp group by commission_pct;分组时所有的空值都在同一个组各个部门有多少种不同的职位select dept_id,count(distinct title) from s_emp group by dept_id;各个部门不同职位的平均工资select dept_id,title,avg(salary) from s_emp group by dept_id,title;42部门的平均工资，列出部门号select max(dept_id),round(avg(salary)) from s_emp where dept_id=42;select dept_id,round(avg(salary)) from s_emp where dept_id=42 group by dept_id;若没有group by子句，select后面有一个是组函数，其他都必须是组函数有group by子句，select后面可跟group by后面跟的表达式以及组函数，其他会报错每个工资级别有多少人select s.grade,count(e.empno) from emp e right join salgrade s on e.sal between s.losal and s.hisal group by s.grade order by s.grade;各个部门的平均工资（列出部门名称：部门名称+地区名称）select ,,avg(e.salary) from s_emp e join s_dept d one.dept_id=d.id join s_region r on d.region_id=r.id group by ,r,name;select max(),max(),avg(e.salary) from s_emp e join s_dept d on e.dept_id=d.id join s_region r on d.region_id=r.id group by d.id;having对分组后的结果进行过滤(后面跟组函数)过滤组，而where是过滤记录哪些部门的平均工资比2000高select dept_id,avg(salary) from s_emp group by dept_id havingavg(salary)>2000;32部门和42部门的平均工资select dept_id,avg(salary) from s_emp where dept_id in (32,42) group by dept_id;优化select dept_id,avg(salary) from s_emp group by dept_id having dept_idin(32,42);where和having的区别where子句过滤的是行（记录）having子句过滤的是分组（组标识，每组数据的聚合结果）where子句包含单行函数having子句只能包含group by 后面的表达式和组函数where子句执行在前，having子句执行在后where子句和having子句都不允许用列别名子查询就是在一条SQL语句中嵌入select语句先执行子查询，子查询的返回结果作为主查询的条件，在执行主查询子查询只执行一遍若子查询的返回结果为多个值，Oracle会去掉重复值后，再将结果返回给主查询非关联子查询：主查询和子查询之间没有表连接单列子查询谁的工资最低select first_name,title from s_emp where salary=(select min(salary) froms_emp);谁和SMITH的职位一样select last_name,title from s_emp where title=(select title from s_emp where last_name='Smith') and last_name<>'Smith';哪些人是领导select first_name from s_emp where id in (select manager_id from s_emp);哪些人是员工select first_name from s_emp where id not in (select manager_id from s_emp where manager_id is not null);not in 比外连接的效率低哪个部门的平均工资比32部门的平均工资高select dept_id,avg(salary) from s_emp group by dept_id havingavg(salary)>(select avg(salary) from s_emp where dept_id = 32);多列子查询哪个员工的工资等于本部门的平均工资select first_name,dept_id,salary from s_emp where (dept_id,salary) in (select dept_id ,avg(salary) from s_emp group by dept_id);关联子查询先做主查询，再执行子查询执行顺序1.外部查询得到一条记录（查询先从outer表中读取数据）并将其传入到内部查询2.内部查询基于传入的值执行3.内部查询从其结果中把值传回外部查询，外部查询使用这些值来完成其处理，若符合条件，outer表中得到的那条记录放入结果集中，否则放弃，该记录不符合条件4.重复执行步骤1-3，直到把outer表中的所有记录判断一边哪个员工的工资比本部门的平均工资高select first_name,dept_id,salary from s_emp o where salary>(selectavg(salary) from s_emp i where o.dept_id=i.dept_id);exists运算符（存在）主查询与子查询的条件是否匹配（一旦匹配就返回，不会继续找下去）exists采用的是循环方式，判断outer表中是否存在记录只要在inner表中找到一条匹配的记录即可1.外部查询得到一条记录（查询先从outer表中的去数据）并将其传入到内部查询表2.对inner表中的记录依次扫描，若根据条件存在一条记录与outer表中的记录匹配，立即停止扫描，返回true，将outer表中的记录放入结果集中，若扫描了全部的记录，没有任何一条记录符合匹配条件，返回false，outer表中的该记录被过滤掉3.重复执行步骤1-2，直到把outer表中的所有记录判断一边哪些人是领导select first_name,dept_id from s_emp outer where exists (select 'x' froms_emp where manager_id=outer.id);哪个部门有员工select dname from dept d where exists (select 1 from emp e wheree.deptno=d.deptno);not exists 不存在哪些人是员工select first_name,dept_id from s_emp outer where not exists (select 'x' from s_emp where manager_id=outer.id);in和exists的比较exists是用循环的方式，由outer表的记录数决定循环的次数，对于exists的影响最大，所以，外表的记录数要少in先执行子查询，子查询的返回结果去重之后，在执行主查询，所以，子查询的返回结果越少，越适合用该方式标量子查询列出员工的名字和领导名字的对应关系select first_name,nvl((select first_name from s_emp i whereo.manager_id=i.id),'Boss') from s_emp o;查出每个工资级别的人数select grade,(select count(empno) from emp e where e.sal between s.losal and s.hisal) from salgrade;基于数据库的开发将业务需求转换成可操作的数据库E-R 图E 实体，有共同属性的一类对象的集合属性，描述实体，区分实体R 关系，描述实体和实体的关系虚线表示可选的，可以表示实体 实线表示强制的，必须* 表示强制属性o 表示可选属性# 表示唯一属性实体和实体的关系从实例之间的数量关系的角度分为一对一，一对多，多对多 实体和实体之间的关系从紧密程度上分为必须和可以递归关系：同一实体里的实例之间有关系完整性约束保证数据的一致性通过数据库的特性或应用程序完成数据库约束主键（primary key ），唯一键（unique key ），外键（foreign key ）实体完整性：主键值唯一且非空（PK ）引用完整性：外键值必须是已存在的主键值或为空（FK ）主键主键值要求唯一且非空联合主键：多列联合唯一，任意一列都可以重复，每一列都不能为null 表中只能有一个主键外键外键是定义在子表（child table ）上一列，它的取值要引用父表（parent table ） CUSTOMER #*id * name o phone EMPLOYEE #* id * last name O first name上的主键列或唯一列外键的定义是基于数据值的，是纯逻辑概念外键的取值必须匹配主键值或唯一键值还可以是空值若外键是主键的一部分，它的取值不能为空1.先create parent table（pk，uk），再create child table（fk）2.先insert into parent table，再insert into child table3.先delete from child table，再delete from parent table4.先drop child table，再drop parent tableE-R图向表转换表和列分别对应实体和属性1.将实体映射成表2.将属性映射成列强制属性定义成非空列名不能用保留字3.将唯一标识映射成主键一个表只能有一个主键4.将关系映射成外键一对多：一的那边定义成主键，或唯一键，多的那边定义成外键一对一：合表在外键列上增加唯一约束主键即外键多对多：通过增加中间表将一个多对多关系转换成两个一对多关系三个范式最小化数据冗余减少完整性问题标识丢失的实体，关系，表第一范式：表中不会有重复的记录，即有主属性（pk）；每个属性值不可再分第二范式：每个非主属性必须完全依赖于主属性第三范式：每个非主属性不能依赖于另一个非主属性DDL建表语句命名规则首字母必须是字符长度是1-30只能包含A-Z,a-z,0-9,_,$,#同一个区域不能定义相同的对象不能定义Oracle的保留字create table tablename(字段数据类型默认值约束，……);数据类型字符varchar2后面必须跟宽度，按照实际长度存，最大4000个字节，列取值长度不固定用varchar2，比较时按实际字符长度比，对空格是敏感的char默认一个字符，按照定义长度存，最大2000个字节，取值长度固定用char，比较时，会将短字符串补齐后，在与长字符串比较，对空格不敏感数字number(p,s)可以不定义长度，缺省38位,p表示数值中所有数字位的个数，最大38位，s表示刻度范围，s为正数，表示小数点右边的数字的个数，为负数，表示小数点开始向左进行计算数字位的个数。