oracle笔记2

p>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 identified by tiger(密码) default tablespace users quota 10M on users;
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 的使用