Oracle常用语练习

首先，以超级管理员的身份登录oracle sqlplus sys/bjsxt as sysdba 然后，解除对scott用户的锁 alter user scot

首先，以超级管理员的身份登录Oracle  
    sqlplus sys/bjsxt as sysdba  
 
然后，解除对scott用户的锁  
    alter user scott account unlock;  
那么这个用户名就能使用了。  
(默认全局数据库名orcl)  
 
1、select ename, sal * 12 from emp; //计算年薪  
2、select 2*3 from dual;  //计算一个比较纯的数据用dual表  
3、select sysdate from dual;  //查看当前的系统时间  
4、select ename, sal*12 anuual_sal from emp; //给搜索字段更改名称（双引号 keepFormat 别名有特殊字符，要加双引号）。  
5、任何含有空值的数学表达式，最后的计算结果都是空值。  
6、select ename||sal from emp;  //(将sal的查询结果转化为字符串，与ename连接到一起，，相当于Java中的字符串连接)  
7、select ename||'afasjkj' from emp;   //字符串的连接  
8、select distinct deptno from emp;   //消除deptno字段重复的值  
9、select distinct deptno , job from emp; //将与这两个字段都重复的值去掉  
10、select * from emp where deptno=10;   //(条件过滤查询)  
11、select * from emp where empno > 10;  //大于 过滤判断  
12、select * from emp where empno 10  //不等于  过滤判断  
13、select * from emp where ename > 'cba';  //字符串比较，实际上比较的是每个字符的AscII值，与在Java中字符串的比较是一样的  
14、select ename, sal from emp where sal between 800 and 1500;  //(between and过滤，包含800 1500)  
15、select ename, sal, comm from emp where comm is null;  //(选择comm字段为null的数据)  
16、select ename, sal, comm from emp where comm is not null;  //(选择comm字段不为null的数据)  
17、select ename, sal, comm from emp where sal in (800, 1500,2000);  //(in 表范围)  
18、select ename, sal, hiredate from emp where hiredate > '02-2月-1981'; //(只能按照规定的格式写)  
19、select ename, sal from emp where deptno =10 or sal >1000;  
20、select ename, sal from emp where deptno =10 and sal >1000;  
21、select ename, sal, comm from emp where sal not in (800, 1500,2000);  //(可以对in指定的条件进行取反)  
22、select ename from emp where ename like '%ALL%';   //(模糊查询)  
23、select ename from emp where ename like '_A%';    //(取第二个字母是A的所有字段)  
24、select ename from emp where ename like '%/%%';   //(用转义字符/查询字段中本身就带%字段的)  
25、select ename from emp where ename like '%$%%' escape '$';   //(用转义字符/查询字段中本身就带%字段的)  
26、select * from dept order by deptno desc; (使用order by  desc字段 对数据进行降序排列 默认为升序asc);  
27、select * from dept where deptno 10 order by deptno asc;   //(我们可以将过滤以后的数据再进行排序)    
28、select ename, sal, deptno from emp order by deptno asc, ename desc;   //(按照多个字段排序 首先按照deptno升序排列,当detpno相同时，内部再按照ename的降序排列)  
29、select lower(ename) from emp;  //(函数lower() 将ename搜索出来后全部转化为小写)；  
30、select ename from emp where lower(ename) like '_a%';  //(首先将所搜索字段转化为小写，然后判断第二个字母是不是a)  
31、select substr(ename, 2, 3) from emp;    //(使用函数substr() 将搜素出来的ename字段从第二个字母开始截，一共截3个字符)  
32、select chr(65) from dual;  //(函数chr() 将数字转化为AscII中相对应的字符)   
33、select ascii('A') from dual;  //(函数ascii()与32中的chr()函数是相反的 将相应的字符转化为相应的Ascii编码）                                                                                                                                                                                                                                                                                                                                             )  
34、select round(23.232) from dual;  //(函数round() 进行四舍五入操作)  
35、select round(23.232, 2) from dual;  //(四舍五入后保留的小数位数 0 个位 -1 十位)  
36、select to_char(sal, '$99,999.9999')from emp;  //(加$符号加入千位分隔符，保留四位小数，没有的补零)  
37、select to_char(sal, 'L99,999.9999')from emp;  //(L 将货币转化为本地币种此处将显示￥人民币)  
38、select to_char(sal, 'L00,000.0000')from emp;  //(补零位数不一样，可到数据库执行查看)  
39、select to_char(hiredate, 'yyyy-MM-DD HH:MI:SS') from emp;  //(改变日期默认的显示格式)  
40、select to_char(sysdate, 'yyyy-MM-DD HH:MI:SS') from dual;  //(用12小时制显示当前的系统时间)  
41、select to_char(sysdate, 'yyyy-MM-DD HH24:MI:SS') from dual;  //(用24小时制显示当前的系统时间)  
42、select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:24:45','YYYY-MM-DD HH24:MI:SS');   //(函数to-date 查询公司在所给时间以后入职的人员)  
43、select sal from emp where sal > to_number('$1,250.00', '$9,999.99');   //(函数to_number()求出这种薪水里带有特殊符号的)  
44、select ename, sal*12 +  nvl(comm,0) from emp;   //(函数nvl() 求出员工的"年薪 + 提成(或奖金)问题")  
45、select max(sal) from emp;  // (函数max() 求出emp表中sal字段的最大值)  
46、select min(sal) from emp;  // (函数max() 求出emp表中sal字段的最小值)  
47、select avg(sal) from emp;  //(avg()求平均薪水);  
48、select to_char(avg(sal), '999999.99') from emp;   //(将求出来的平均薪水只保留2位小数)  
49、select round(avg(sal), 2) from emp;  //(将平均薪水四舍五入到小数点后2位)  
50、select sum(sal) from emp;  //(求出每个月要支付的总薪水)  
 
/////////////////////////组函数(共5个)：将多个条件组合到一起最后只产生一个数据//////min() max() avg() sum() count()/////////////////////////////  
51、select count(*) from emp;  //求出表中一共有多少条记录  
52、select count(*) from emp where deptno=10;  //再要求一共有多少条记录的时候，还可以在后面跟上限定条件  
53、select count(distinct deptno) from emp;   //统计部门编号前提是去掉重复的值  
////////////////////////聚组函数group by() //////////////////////////////////////  
54、select deptno, avg(sal) from emp group by deptno;  //按照deptno分组，查看每个部门的平均工资  
55、select max(sal) from emp group by deptno, job; //分组的时候，还可以按照多个字段进行分组，两个字段不相同的为一组  
56、select ename from emp where sal = (select max(sal) from emp); //求出  
57、select deptno, max(sal) from emp group by deptno; //搜素这个部门中薪水最高的的值  
//////////////////////////////////////////////////having函数对于group by函数的过滤 不能用where//////////////////////////////////////  
58、select deptno, avg(sal) from emp group by deptno having avg(sal) >2000; （order by ）//求出每个部门的平均值，并且要 > 2000  
59、select avg(sal) from emp where sal >1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;//求出sal>1200的平均值按照deptno分组，平均值要>1500最后按照sal的倒序排列  
60、select ename,sal from emp where sal > (select avg(sal) from emp);  //求那些人的薪水是在平均薪水之上的。  
61、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);  //查询每个部门中工资最高的那个人  
///////////////////////////////等值连接//////////////////////////////////////  
62、select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;  //自连接，把一张表当成两张表来用  
63、select ename, dname from emp, dept;  //92年语法 两张表的连接 笛卡尔积。  
64、select ename, dname from emp cross join dept; //99年语法 两张表的连接用cross join  
65、select ename, dname from emp, dept where emp.deptno = dept.deptno; // 92年语法 表连接 + 条件连接  
66、select ename, dname from emp join dept on(emp.deptno = dept.deptno); // 新语法  
67、select ename,dname from emp join dept using(deptno); //与66题的写法是一样的，但是不推荐使用using ： 假设条件太多  
///////////////////////////////////////非等值连接///////////////////////////////////////////  
68、select ename,grade from emp e join salgrade s on(e.sal between s.losal and s.hisal); //两张表的连接 此种写法比用where更清晰  
69、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%';  //三张表的连接  
70、select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr = e2.empno); //自连接第二种写法，同62  
71、select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr = e2.empno); //左外连接 把左边没有满足条件的数据也取出来  
72、select ename, dname from emp e right join dept d on(e.deptno = d.deptno); //右外连接  
73、select deptno, avg_sal, grade 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);//求每个部门平均薪水的等级  
74、select ename from emp where empno in (select mgr from emp); // 在表中搜索那些人是经理  
75、select sal from emp where sal not in(select distinct e1.sal from emp e1 join emp e2 on(e1.sal 76、select deptno, max_sal from  
    (select avg(sal) max_sal,deptno from emp group by deptno)  
        where max_sal =  
        (select max(max_sal) from  
         (select avg(sal) max_sal,deptno from emp group by deptno)  
    );//求平均薪水最高的部门名称和编号。  
77、select t1.deptno, grade, avg_sal from  
      (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)  
      ) t1  
    join dept on (t1.deptno = dept.deptno)  
    where t1.grade =   
      (  
        select min(grade) from  
          (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)  
     )  
   )//求平均薪水等级最低的部门的名称 哈哈 确实比较麻烦  
78、create view 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);  
    //视图的创建，一般以v$开头，但不是固定的