[Orcale] select语句

账号被锁定:
sqlplus / as sysdba;
alter user user_name account unlock;
授权权限: 视图
sqlplus / as sysdba;
grant create table, create view to scott;

--------------------------------------------------------------

 

 

 

varchar2  -- 支持各国语言
number(7,2) -- 七位数 两位是小数

select sysdate from dual; 取得当前日期
select ename, sal*12 anuual_sal from emp;   别名的使用方法, 如果别名中有特殊字符, 别名用""包起来.



|| 连接符:    select ename||sal from emp;  连接两列数据.
        select ename||'***' from emp; 连接 ename 的数据后面加上 '***' 字符串,
            如果'***' 中含有单引号, 用两个单引号代替一个单引号.

select distinct deptno from emp;        显示出emp表中, 没有重复的deptno.
select distinct deptno, job from emp;    显示出emp表中, 没有重复的deptno, job 的组合

------------------------------------   where   --------------------------------------------------
    等于(如果是字符串,要加单引号):    select * from emp where ename = 'CLARK';
    不等于:    where sal <> 1500;
    包含:        where sal between 800 and 2000;  或者用  sal>=800 and sal<=2000;
    空值, 非空值:    is null, is not null;
    等于,不等于(集合):        where sal (not) in(800, 1500, 2000);
    日期处理:    按照原来的格式, where hiredate> '20-2月-81'
    or, and :    where deptno =10 or sal >1000;

------------------------------------   LIKE   --------------------------------------------------       
            where ename like '%ALL%';   
            -- 如果ALL里含有百分号(A%LL), 用转义字符.
                默认是"/":    where ename like '%A/%LL%';
                自己设定转义字符:    where ename like '%A/%LL%' escape '$';

------------------------------------   order by   ---------------------------------------------
    升序: order by empno asc;
    降序: order by empno desc;
    组合:     order by empno asc, ename desc;
   
    综合:
    select ename, sal*12 annual_sal from emp where ename not like '_A%' and sal >800 order by sal desc;

--------------------------------------   upper & lower   ---------------------------------------------------
    转换成小写:    select lower(ename) from emp;
    转换成大写:    select upper(ename) from emp;
   
    综合: 第二个字母不是a或A的
    select ename from emp where lower(ename) like '_a%';

------------------------------------   substr   ---------------------------------------------
    select substr(ename, 2, 3) from emp;   从第二个字符开始截, 截3个字符(比如abcde, 就打印bcd)

----------------------------------   ASC II, char 互相转换   --------------------------
    select char(65) from dual;   打印出A.
    select ascii('A') from dual;  打印A的ascii码.

----------------------------------   round  四舍五入   ------------------------------
    select round(23.652) from dual;        结果24;
    select round(23.652, 2) from dual;    四舍五入到,小数点后面第二位, 结果23.65;
    select round(23.652, -1) from dual;    四舍五入到,小数点前面第二位, 结果20;

---------------------------------   to_char & to_date & to_number   ---------------------------------------------
    select to_char(sal, '$99,999.9999') from emp;  格式化成'$99,999.9999'的样式, 不补零;
    select to_char(sal, 'L00000.9999') from emp;    格式化成'L00000.9999'的样式, 长度不够前面补零, L表示本地币种;
    select sal from emp where sal > to_number('$1,250.00','$9,999.99');
   
    ★★★ 日期格式转换,比较 ★★★
    select to_char(hiredate, 'yyyy-mm-dd hh:mi:ss') from emp;
    select to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss') from dual;    当前时间, 24进制;
    select ename, hiredate from emp where hiredate > to_date('1981-2-20 12:34:56', 'yyyy-mm-dd hh24:mi:ss');
   
    任何含有空值的数学表达式,结果都是空值, 用nvl()函数屏蔽空值;
    select ename, sal*12 + nvl(comm,0) from emp;

-----------------------------  组函数   ---------------------------------------------------
    取sal中最高值
    select max(sal) from emp;
    取sal中最低值
    select max(sal) from emp;
    取sal中平均值
    select avg(sal) from emp;
    求和
    select sum(sal) from emp;
    一共有个记录
    select count(*) from emp;

----------------------------------   group by 分组   ------------------------------
    每个部门的平均薪水:
    select deptno, avg(sal) from emp group by deptno;
    多个字段分组:
    select deptno, job,  avg(sal) from emp group by deptno, job;
        子查询
    select ename, sal, deptno from emp where sal in (select max(sal) from emp group by deptno);
    整组筛选 having:
    select avg(sal), deptno from emp group by deptno having avg(sal) > 2000;

------------------------------------   执行顺序   ---------------------------------
    1.    select * from emp
    2.    where sal> 1000
    3.    group by deptno
    4.    having
    5.    order by

    select deptno,round(avg(sal), 2) from emp where sal>1200 group by deptno having avg(sal) >1500 order by avg(sal) desc;

-----------------------------------   自连接   -------------------------------------------
    select e1.ename, e2.ename from emp e1, emp e2 where e1.mgr = e2.empno;   ---老语法
    select e1.ename, e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);
    如何显示没有匹配的数据:
    left join - 显示左边的,
    right join - 显示右边的,
    full join - 都显示
    select e1.ename, e2.ename from emp e1 left join emp e2 on (e1.mgr = e2.empno);

-------------------------------------   表链接   --------------------------------------------
    等值连接
    select ename, dname from emp join dept on (emp.deptno = dept.deptno);
    select ename ,dename from emp join dept using(deptno)     --- 不推荐
    不等值的
    select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);
    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%';


------------------------------------   子查询  ---------------------------------
    查询薪水大于平均值的员工, 按照薪水降序排列
    select deptno, ename, sal from emp where sal> (select avg(sal) from emp) order by sal desc;
   
    查询每个部门薪水最高的员工:
    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);
   
     ★ 查询薪水最高的第六位到第十位员工(rownum,rownum 只有"<"跟"<=") ★:
    select ename, sal from(
        select ename, sal, rownum r from(
        select ename, sal from emp order by sal desc
        )
        )where r between 6 and 10;

    求部门平均薪水的等级:
    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);
   
    求部门平均的薪水等级:
    select deptno, avg(grade) from
    (select deptno, ename , grade from emp join salgrade s on (emp.sal between s.losal and s.hisal)) t
    group by deptno;
   
     求雇员中那些是经理人
     select ename from emp where empno in (select distinct mgr from emp);
   
    求出薪水最高值    --- 不用组函数
    select distinct sal from emp where sal not in
    (select distinct e1.sal from emp e1 join emp e2 on (e1.sal < e2.sal));
   
    求出平均薪水最高的部门编号
    select deptno , avg_sal from
    (select avg(sal) avg_sal , deptno from emp group by deptno)
    where avg_sal =
    (select max(avg_sal) from
        (select avg(sal) avg_sal, deptno from emp group by deptno));
   
    求出平均薪水最高的部门名称
    select dname from dept where deptno =
    (select deptno from
        (select avg(sal) avg_sal , deptno from emp group by deptno)
    where avg_sal =
        (select max(avg(sal)) from emp group by deptno));
   
    求出平均薪水的等级最低的部门的名称:
    select dname, 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)
    ));
   
    也可以用创建视图方式: 需要开启权限
        1. 开启权限:   
            conn sys / as sysdba;
            grant create table, create view to scott;
        2. 创建视图:
            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);
        3. 查询:
            select dname, t1.deptno , grade, avg_sal from
            v$_dept_avg_sal_info t1
            join dept on (t1.deptno = dept.deptno)
            where t1.grade =
            (
            select min(grade) from v$_dept_avg_sal_info
            );
    部门经理人中平均薪水最低的部门名称
   

    比普通员工最高薪水还要高的经理人名称
    select ename from emp where sal >
    (
    select max(sal) max_sal from emp where empno not in
    (
    select distinct e2.empno from emp e1 join emp e2 on(e1.mgr = e2.empno)
    ));

-----------------------------   SQL面试题   --------------------------------------
http://penghao122.javaeye.com/blog/54810
有3个表S，C，SC
S（SNO，SNAME）代表（学号，姓名）
C（CNO，CNAME，CTEACHER）代表（课号，课名，教师）
SC（SNO，CNO，SCGRADE）代表（学号，课号成绩）
问题：
1，找出没选过“黎明”老师的所有学生姓名。
2，列出2门以上（含2门）不及格学生姓名及平均成绩。
3，即学过1号课程又学过2号课所有学生的姓名。
请用标准SQL语言写出答案，方言也行（请说明是使用什么方言）。