oracle学习分析和总结.docxVIP

解锁 scott 用户 我现在安装的 oracle 是 10g 的，sid:sritsys 统一口令是：srit 1 先用 system 用户登陆进去 Alter user scott account unlock; 2.然后用 scott 默认的密码 tiger 登进，进行密码修改 OK 修改 scott 的登录密码 SQL alter user scott identified by pan; 用户已更改。 ok 了，你再拿 scott 和 pan 来登录就可以了! SQL conn scott/pan 查看所有表： Select * from tab; 查看所有用户 1,得用高级帐号哦。Select * from dba_users; Select * from all_users; Select * from user_users; select table_name from user_tables; //当前用户的表 select table_name from dba_tables; //包括系统表select table_name from all_tables; //所有用户的表 select * from user_indexes // 可以查询出所有的用户表索引 常用 sql 语句 /** *公司工资最高的员工列表子查询 */ select t.ename,t.sal from emp t where t.sal = ( select max(sal) from emp ) /** *查询公司平均薪水的等级 * 注意不能在select。。。from 里面去嵌套 *select g.grade,(selectavg(e.sal) from emp) as asal from salgrade g where losalasalhisal; * 这种写法是错误的。第二 个错误是： losalasalhisal; 必须是 losalasal and losalhisal select g.grade from salgrade g where (select avg(e.sal) from emp e) as asal and losalasal and asal=hisal; 这种写法也是错误的。As 不能作变量使用。 */ select s.grade from salgrade s where (select avg(t.sal) from emp t) between s.losal and s.hisal 或者：最好用上面那种 select g.grade from salgrade g where losal=(select avg(e.sal) from emp e) and (select avg(e.sal) from emp e)=hisal; 3— ---下面group by .. having ..在 mysql 是可以的，在oracle 里面是不能这样用。因为在oracle 里面 as 是不能赋值给变量的 --select e.* ,avg(sal)as asal from emp e group by e.ename having asal2000; A：select e.* from emp e where group by e.ename having (select avg(e.sal) from emp)2000;--这个也是错误的不能用 * B：select e.ename,e.sal from emp e where group by e.ename having (select avg(e.sal) from emp)2000; ---这个也是错误的，e.sal 必须放到 group by 里面 ，前后必对应上 C:select e.ename,e.sal from emp e where group by e.ename,e.sal having (select avg(e.sal) from emp)2000—这个也是错误的，后面的 e.sal 改成 sal，同时 where 去掉 D: select e.ename,e.sal from emp e group by e.ename,e.sal having (select avg(sal) from emp)2000; 这个才是正确的 //oracla 插入时间 insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno)values(7777,l eafage,clerk,7788,to_date(1984-03-29,yyyy-MM-dd),100