首页 > 数据库 > SQL Server >

SQL(Oracle)学习笔记

2016-11-11

解锁scott账户:alter user scott account unlock;查看表结构desc ‘TableName’desc emp; desc dept;desc salgrade;查询语句 select * from,查看表中数据。

解锁scott账户

    alter user scott account unlock;

查看表结构

desc ‘TableName’
    desc emp; 
    desc dept;
    desc salgrade;

查询语句 select * from

查看表中数据

    select * from salgrade;
    select * from dept;
    select * from emp;
    select sal*12 from emp;
    select ename, sal*12 from emp;

虚表dual

    desc dual; 
    select * from dual;
    select 2*3 from dual;
    select sysdate from dual;  -- sysdate - 系统当前时间函数

设置别名

    select ename, sal*12 anuual_sal from emp; 

0和空值(null)是不一样的

    select ename, comm from emp;
有空值(null)的表达式结果仍为空值(null)(无论什么运算)。
    select ename, sal*12+comm from emp;

字符串连接符 “||”

    select ename || sal from emp; 
保持大小写的字符串放在单引号‘’中,其字符串中的单引号“‘”和“’”用2个表示1个。
    select ename || 'abc' from emp;
    select ename || 'abc''de''fg' from emp;

去重 distinct

    select distinct deptno ,job from emp; 

where 条件过滤(针对from表中的数每条据)

等于、大于
    select * from emp where deptno = 10;
    select * from emp where sal > 1500;
    -- 不等于 <>
and 连接2个条件
    select ename, sal, deptno from emp where sal >1000 and deptno = 10;
    select ename, sal, deptno from emp where sal >1000 or deptno = 10;
between A and B &ndash; 介于A和B之间 等价于 >= A and <= B
    select * from emp where sal between 800 and 1500;
    select * from emp where sal >= 800 and sal <= 1500;
null不是= ,而是is
    select ename, sal, comm from emp where comm is null;
    select ename, sal, comm from emp where comm is not null;
in(A,B,C) 只取 A,B,C中的值
    select ename, sal from emp where sal in (800, 1500, 2000);
日期(格式需和oracle的一致)
    select ename, sal, hiredate from emp where hiredate > &#39;20-2月-81&#39;;
模糊查询(&lsquo;正则匹配&rsquo;)
    select ename, sal from emp where ename like &#39;%ALL%&#39;;
    select ename, sal from emp where ename like &#39;_A%&#39;;
    select ename, sal from emp where ename like &#39;%\%%&#39;;

排序 order by

    select * from dept ;
    select * from dept order by deptno desc;
    --默认(不写) - asc
order by 和 where 、like等组合
    select empno, ename, deptno from emp where deptno <>10 order by empno ;
    select empno, ename, deptno from emp order by deptno ;
    select empno, ename, sal*12 anuual_sal from emp where ename not like &#39;_A%&#39; and sal > 800 order by sal desc;

SQL function 1

lower()
    select lower(ename) from emp; 
    select ename from emp where lower(ename) like &#39;_a%&#39;; 
substr()
    select substr(ename,2,3) from emp;
chr()
    select chr(65) from dual; 
    -- 输出:A
ascii()
    select ascii(&#39;A&#39;) from dual; 
    --输出65
round()
    select round(23.654321) from dual;
    select round(23.654321,1) from dual;
    select round(23.654321,-1) from dual;
to_char()
    select to_char(sal,&#39;$9,999.9999&#39;) from emp; --9代表一位数字,没有时不予显示
    select to_char(sal,&#39;L9,999.9999&#39;) from emp; --L代表当地货币符号
    select to_char(sal,&#39;$0,000.0000&#39;) from emp; --0代表一位数字,没有时补0占位
    select to_char(hiredate, &#39;YYYY-MM-DD HH:MI:SS&#39;) mdate from emp;
    select to_char(sysdate, &#39;YYYY-MM-DD HH24:MI:SS&#39;) mdate from dual;
to_date()
    select ename, hiredate from emp where hiredate > to_date(&#39;1981-02-20 12:00:00&#39;,&#39;YYYY-MM-DD HH24:MI:SS&#39;);
to_number()
    select sal from emp where sal > to_number(&#39;$1,250.00&#39;,&#39;$9,999.99&#39;);
nvl() &ndash; null处理 用0替代空值
    select ename, sal * 12 + nvl(comm,0) from emp; 

SQL function 2 &ndash; 组函数

max(), min(), avg(), count(), sum() 多输入,1个输出
    select max(sal) from emp;
    select min(sal) from emp;
    select round(avg(sal),2) from emp;
    select count(*) from emp;
    select count(comm) from emp;
    -- count() 对于null不计数
    select count(distinct deptno) from emp;
    select sum(sal) from emp;
    select avg(sal) from emp;

分组group by

    select deptno, round(avg(sal),2) from emp group by deptno;
    select deptno, job, max(sal) from emp group by deptno,job;
    select deptno, max(sal) from emp group by deptno;
    select ename, sal from emp where sal = (select max(sal) from emp);
    select deptno, round(avg(sal),2) from emp group by deptno;

having 对分组后的数据过滤

    select deptno, round(avg(sal),2) from emp group by deptno having avg(sal) > 2000;

小结

select查询语句顺序

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

子查询

将一个select查询结果作为一张数据表,再进行select查询操作

谁挣的钱最多?
    select ename, sal from emp where sal = (select max(sal) from emp);
工资超过平均工资之上
    select ename, sal from emp where sal > (select avg(sal) from emp);
每个部门工资最多的员工 (join on)
    select ename, sal from emp join (select deptno, max(sal) max_sal from emp group by deptno) t 
    on (emp.sal = t.max_sal and emp.deptno = t.deptno);
每个部门的平均薪水等级
    select deptno,avg_sal, grade from (select deptno, avg(sal) avg_sal from emp group by deptno) t 
    join salgrade on (t.avg_sal between salgrade.losal and Salgrade.Hisal);
求每个人的经理人 (自连接)
    select e.ename ename,  t.ename mgr_name from emp e , emp t where e.mgr = t.empno;
SQL 1999 表连接查询
    select ename, dname from emp join dept on emp.deptno = dept.deptno;
    select ename, dname from emp join dept using(deptno); --不推荐
    select ename, dname, grade from emp join dept on emp.deptno = dept.deptno join salgrade on emp.sal between salgrade.losal and salgrade.hisal where salgrade.grade > 2;
左外连接 左边表不能连接的数据也拿出来
    select e.ename ename,  t.ename mgr_name from emp e left join emp t on e.mgr = t.empno;
右外连接 右边表不能连接的数据也拿出来
    select ename, dname from emp e right join dept d on (e.deptno = d.deptno);
全外连接 左右表不能连接的数据都列出来
    select ename, dname from emp e full join dept d on (e.deptno = d.deptno);

练习!

A.求部门平均薪水的等级。
    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
    );
B.求部门平均的薪水等级
    select deptno ,avg(grade) from
    (
        select empno,deptno,grade from emp e join salgrade s 
        on e.sal between s.losal and s.hisal
    )
    group by deptno;
C.哪些人是经理
    select ename from emp where empno in (select mgr from emp);
    select ename from emp where empno in(select distinct mgr from emp);
D.不准用组函数,求薪水的最高值(面试题)
    select ename, sal from emp where sal not in 
    (select distinct e1.sal from emp e1 join emp e2 on e1.sal < e2.sal);
E.平均薪水最高的部门编号
    select deptno, avg_sal from (select deptno, avg(sal) avg_sal from 
    emp group by deptno) t where t.avg_sal = 
    (
        select max(avg_sal) max_avg_sal from 
        (
            select deptno, avg(sal) avg_sal from emp group by deptno
        )
    );
F.平均薪水最高的部门名称
    select dname from dept where deptno = 
    (
        select deptno from 
        (
            select deptno, avg(sal) avg_sal from emp group by deptno
        ) 
        where avg_sal = 
        (
            select max(avg_sal) max_avg_sal from 
            (
                select deptno, avg(sal) avg_sal from emp group by deptno
            )
        )
    );
G.求平均薪水的等级最低的部门的部门名称
    select p.deptno, p.dname, t.avg_sal, t.grade from dept p join
    (
        select deptno, avg_sal, grade from 
        (
            select d.deptno deptno, avg_sal, s.grade grade from 
            ( 
                select deptno, avg(sal) avg_sal from emp group by deptno 
            ) d join salgrade s on (d.avg_sal between s.losal and s.hisal)

        )
        d where d.grade = 
        (
            select min(grade) min_grade from 
            (
                select d.deptno deptno, s.grade grade from 
                ( 
                    select deptno, avg(sal) avg_sal from emp group by deptno 
                ) d join salgrade s on (d.avg_sal between s.losal and s.hisal)
            )
        )
    )
    t on (p.deptno = t.deptno);
相关文章
最新文章
热点推荐