oracle笔记2-MG05 學習筆記~

pennate

　　2011-08-24
　　alter user scott account unlock    更改用户解锁
　　conn scott/tiger       重新以上述用户登录
　　select语句：
　　1、desc  emp        描述emp表，列出表的基本信息。
　　2、数据类型：NUMBER(7,2)   7位的数字，2位小数
　　学习例子：
　　三张表分别为：emp  雇员表，dept 部门表，salgrade 工资等级表
　　3、select from dept ;       注意：每个语句以；号结束
　　4、desc dual    空表    DUMMY  空表   空表用途
　　5、select sysdate from dual;
　　6、select ename,sal12 from emp; 计算每个人的年薪
　　select ename,sal12 anuual_sal from emp;
　　select ename,sal12 "anuual sal" from emp;      anuual sal 之间不能有空格，如果有空间需加双引号
　　7、select ename,sal*12 + comm from emp ;   任何空值的数学表达式都为空值
　　8、两字符连接起来表达（两个字段间连接）：select ename||sal from emp ;
　　9、字段与字符连接：select ename || ‘abcde ’from emp;
　　如果出现字符串里有引号的情况，即可以用两个引号表示一个引号，如：select ename || 'abc''de' from emp ;
　　10、select distinct deptno from emp;  distinct(关键字，去除部门字段中重复的部分)
　　11、where 过虑条件，not like‘_A%’第二个字母不能为A；
　　12、desc  描述      降序排列   asc  升序排列（默认）
　　13、 lower() 函数 把大写变成小写   如：select lower(ename) from emp ; 全部转为大写函数：upper() ,用法与lower()相同；
　　14、substr()函数   如：select substr(ename,2,3) from emp;从第二个字符开始取3个字符。
　　15、chr() 把数字转义为字符 如：select chr(65) from dual;
　　16、ascii()把字符转义为数字 如：select ascii('A') from dual;
　　17、select  round(23.652) from dual;  四舍五入
　　select round(23.652,2) from dual; 四舍五入到小数点后两位 ，-1，如果是-1即是四舍五入到个位；
　　18、to_char(sal,'$99,999.9999') 按指定格式输出，如：select to_char(sal,'$99,999.99999') from dual; 9代表一位数字，L代表本地货币，如：to_char(sal,'L99,999.9999')   ，0代表一位数字，数字格式模型。
　　19、转换日期格式：select to_char(hiredate,'yyyy-mm-dd hh:mi:ss') from emp;
　　20、to_date() 函数，把字串转为日期，如
　　select ename,hiredate from emp where hiredate > to_date('1981-8-23 12:23:44', 'yyyy-mm-dd hh:mi:ss');
　　21、 to_number() 函数，把字符串转为数字；如：
　　select ename,sal12 from emp where sal12 > to_number('$123,3456.8997','$999,9999.9999');
　　22、nvl()  处理空值函数;nvl(comm,0)表示如果comm里面的值为空则用0代替，如果不为空，则用原数表示
　　如：select ename,sal*12 +nvl(comm,0) from emp;
　　23、组函数：
　　max()
　　min()
　　avg()
　　sun()
　　count()
　　24、group by 分组函数的重要性，出现在select列表里的字段，如果没有出现在组函数里面，必须出现在group by函数里面，否则会出现错误：
　　如：select deptno,avg(sal) from emp group by deptno ; 求出各部门的平均薪水；
　　select deptno,job,max(sal) from emp group by deptno,job ;
　　以deptno job分组
　　25、子查询应用：select ename,sal from emp where sal = (select max(sal) from emp ); 查找薪水最大的人的姓名；
　　26、having语句 对分组进行限制
　　select avg(sal),deptno from emp group by deptno having avg(sal) > 2000;  求出平均薪水大于2000的部门；
　　27、连接子查询：
　　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 );
　　求出每个部门薪水最高的人的姓名,与薪水
　　28、表的自连接：select e1.ename,e2.ename from emp e1,emp e2 where e1.mgr=e2.empno;
　　sql 99 新语法,where 只作过虑条件使用
　　十字连接：select dname,ename from emp cross join dept ;
　　29、表连接的两个不同方法：where  与 join  ……  on
　　如：select ename,dname from emp,dept where emp.deptno = dept.no;
　　select ename,dname from emp join dept on (emp.deptno = dept.deptno);
　　30、不等于连接
　　如：select ename,grade from emp e, salgrade s where e.sal between s.losal and s.hisal;
　　select ename,grade from emp e join salgrade s on (e.sal between s.losal and s.hisal) ;
　　31、多表连接：
　　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%'
　　order by e.sal desc;
　　32、左外连接：
　　select e1.ename,e2.name from emp e1 left join emp e2 on (e1.mgr =e2.empno);
　　32、右外连接：
　　select ename,dname from emp e right outer join dept d on (e.deptno = d.deptno);
　　33、左右全连接  full
　　select ename,dname from emp e full  join dept d on (e.deptno = d.deptno);
　　34、求部门平均薪水等级：
　　select t.deptno,dname,grade from
　　(select deptno,avg(sal) from emp group by deptno) t
　　join dept d on t.deptno = d.deptno
　　join salgrade s on t.avg_sal between s.losal and s.hisal
　　order by t.avg_sal desc;
　　35、用户授权：
　　conn sys/tiger as sysdba
　　创建视图：
　　create v$_dept_avg_sal_info as
　　select deptno,grade,avg_sal 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)
　　;
　　grant create table ,create view to scott;
　　赋给用户scott  建立表，建立视图权限
　　删除用户：drop user scott cascade;(注：要以管理员身份才可执行)
　　36、conn sys/tiger as sysdba;    以管理员身份登录
　　37、导出用户数据：
　　进入一个空目录下，建立一个空文件夹来存放导出来的数据：
　　c:\>cd temp
　　c:\temp>del .;
　　c:\temp>exp

　　38、创建用户：creat user ming>　　grant create session,create table,create view to ming; 授权
　　39、导入用户数据：
　　c:\temp>imp
　　40、DML 语句：
　　在dept插入一条数据：insert into dept values (50,'game','bj');
　　注:字符串数据要使用单引号
　　insert into dept (deptno,dname) values (60,'game2')
　　insert into dept select * from dept; 把整张表都插入到里面去
　　41、rollback:撤消上一步的操作
　　42、备份一张表：create table emp2 as select * from emp;
　　43、rownumber 的使用