Select专题

Select专题

一、基础

1.基本的Select 查询语句

  语法：

    SELECT *|{[DISTINCT] column|expression [alias],...}

    FROM table;

 （1）查询所有的列：

    select * from dept;

    select dname,deptno,loc from dept;

   *的效率低

 （2） Sql语句的书写规范：

    1.大小写不敏感

    2.可以分行写

    3.关键字不能分行和和缩写

    4.条件一般分行写

     select * FROM EMP;

     SELECT *

     FROM emp;

（3）数学表达式 ：+ - * /

     select ename,sal+300 from emp;

     运算符的优先级：

    *、/  > + -  同级从左到右进行运算，( )优先级最高

     select ename,sal,12*sal+100 from emp;

     select ename,sal,12*(sal+100) from emp;

（4） NULL 是一个不可用的值，不同于0或空行

   

     select ename,sal,comm from emp;

     select ename,sal,12*sal*comm from emp;

（5）定义字段column别名 alias

    可以直接在字段column名后，也可以是column名 as 别名alias

   需要双引号如果包含空格或特殊字符，再者是为了大小写敏感

    select ename name,sal,12*sal*comm from emp;

    select ename name,sal,12*sal*comm as "Annual Salary" from emp;

（6）连接字符 ||

    select ename||job as "Employees" from emp;

    select ename||' is a '||job as "Employees" from emp;

  (7)常量 ：字符， 数字，日期等（在Select中）

   日期和字符必须要单一行单引号''包围起来

     select ename ||' is a'||job as "Employee Details" from emp;

（8）处理重复的行 distinct

     select deptno from dept;

     select distinct deptno from emp;

二、约束和排序数据

  语法：

    SELECT *|{[DISTINCT] column|expression [alias],...}

    FROM table

    [WHERE condition(s)]

    [ORDER BY];

（1） WHERE 条件

   

   日期和字符必须要单一行单引号''包围起来，

  字符串的值是大小写敏感的，日期date的值是格式敏感的（默认：DD-MON-RR）

1. 约束列

    select * from emp where deptno=30;

    select * from emp where ename='KING';

2. 比较：=, >,>=, <,<=,<>

    select * from emp where sal>2000;

3.  between ... and ...,in(set),like, is null

         select * from emp where sal between 2000 and 2500;

         select * from emp where mgr in (7698,7839,7788);

//%>=0  _=1

         select * from emp where ename like 'A%'; 

         select * from emp where ename like '_A%';

        //转义字符 ESCAPE

         SELECT last_name, job_id

         FROM employees

         WHERE job_id LIKE '%SA\_%' ESCAPE '\';

         

         select * from emp where mgr is null;

         

4. 逻辑条件：      and ,or,not

    

         select * from emp where  sal>1000 and job like '%MAN%';

         select * from emp where  sal>1000 or job like '%MAN%';

         select * from emp where job not in ('SALESMAN','CLERK');

5.约束的优先级

     1 Arithmetic operators   算术运算符

2 Concatenation operator  链接运算符

3 Comparison conditions   比较条件

4 IS [NOT] NULL, LIKE, [NOT] IN

5 [NOT] BETWEEN

6 NOT logical condition

7 AND logical condition

8 OR logical condition 

     select * from emp where job='SALESMAN' or job='CLERK'  and sal >=1300;

     select * from emp where (job='SALESMAN' or job='CLERK')  and sal >=1300;

（二） ORDER BY 排序 ，放在SELECT语句的最后

    ASC: ascending order, default 升序

    DESC: descending order 降序

1.   select * from emp order by hiredate;

2.   select * from emp order by hiredate desc;

3. 按照别名排序

     select ename,sal,12*sal annsal from emp order by annsal;

4. 利用多列进行排序

     select ename,deptno,sal annsal from emp order by deptno,sal desc;

三 、函数

（1）字符串函数

      select ename,lower(ename) from emp; //转化为小写

      select upper('Sql course') from dual;//转化为大写

      select initcap('sql course') from dual;//转为驼峰写法

     dual table ：是和数据字典以前生成的一张表，dual是在SYS的Schema中，但能被所有的用户使用

     dual表有一个DUMMY的字段，有一X的值

    

      select * from emp where ename='smith'; //没记录

      select * from emp where lower(ename)='smith';//有记录

      select concat('Hello','World') from dual; // 字符串连接

      select substr('HelloWorld',1,5) from dual;

      select length('HelloWorld') from dual;

      select instr('HelloWorld','W') from dual;

      select lpad(sal,10,'*') from emp;

      select rpad(sal,10,'*') from emp;

      select trim('H' from 'HelloWorld') from dual;

（2）数值函数

   

      select round(45.926,2),round(45.923,0),round(45.923,-1) from dual;

      select trunc(45.926,2),trunc(45.923,0),trunc(45.923,-1) from dual;

      select mod(1600,300) from dual;

（3）日期函数

    可以+ - 一个日期；

    两个日期相减得到两日期间的间隔天数；两日期不能相加

       select sysdate from dual;

       select sysdate+1 from dual;

       select sysdate-1 from dual;

       select ename,hiredate, (sysdate-hiredate)/365 from  emp;

       select sysdate+24/24,sysdate+33/24 from  dual;

      

      months_between(date1,date2)= (date1-date2)

      select months_between(to_date('1999.11.29','yyyy.mm.dd'), to_date('1998.11.

      29','yyyy.mm.dd')) from dual;

      add_months(date,number)=(date.month+number)

      select sysdate as today ,add_months(sysdate,1) as next_month,

       add_months(sysdate,-1) as last_month from dual;

      next_day(date,'day')

       select next_day(sysdate,5) from dual;

       select next_day(sysdate,'星期五') from dual;

      last_day(date)

      select last_day('01-4月-95') from dual;

     round(date,'format')

       select round(sysdate,'month') from dual;

       select round(sysdate,'year') from dual;

       select trunc(sysdate,'month') from dual;

       select trunc(sysdate,'year') from dual;

（4） 转化函数

       a) to_char(date,'format_model')

            format_model: 必须加单引号'' ，大小写敏感

               YYYY(Full year in numbers)

               YEAR(Year spelled out)

               MM(Two-digit value for month)

               MONTH(Full name of the month)

               MON(Three-letter abbreviation of the month)

               DY(Three-letter abbreviation of the day of the week)

               DAY(Full name of the day of the week)

               DD(Numeric day of the month)

               HH or HH12 or HH24

               MI

               SS

               select ename,to_char(hiredate,'fmDD Month YYYY') from emp;

                  

       b) to_char(number,'format_model')

              9 代表数字

              0 

              $ 美元符号

              L 本地货币符号

              . 小数点

              , 千分割符

  

               select ename,to_char(sal,'L999,999.00') from emp;

        c) to_number(char[,'format_model'])

               select to_number('1234') from dual;

               select to_number('1234','9999') from dual;

        d) to_date(char[,'format_model'])

                select to_date('020908','ddmmyyyy') from dual;

（5） 通用函数

       a) nvl(expr1,expr2) ，如果expr1为null，则用expr2代替

               select ename,sal,nvl(comm,0) from emp;

       b) nvl2(expr1,expr2,expr3)，如果expr1不为null，则用expr2代替，否则用expr3代替

               select ename,sal,comm,nvl2(comm,sal+comm,sal) from emp;

       c) nullif(expr1,expr2)

               case when expr1=expr2 then null else expr1 end    

       d) case表达式

语法：

                   case expr when comparison_expr1 then return_expr1

                            [when comparison_expr2 then return_expr2

                            [when comparison_exprn then return_exprn

                             else else_expr]

                   end

                  select ename,job,sal,case job when 'CLERK' then 1.10*sal

                                                when 'MANAGER' then 1.20*sal

                                                else sal end "revised_salary"

                  from emp;

                  select ename,job,sal,case when job='CLERK' then 1.10*sal

                                            when job='MANAGER' then 1.20*sal

                                            else sal end "revised_salary"

                  from emp;

                   

四、多表连接

连接类型： 等值连接，不等值连接，外连接，自连接

      SELECT table1.column, table2.column

      FROM table1, table2

      WHERE table1.column1 = table2.column2;

        a) 等值连接 equijoins

                    select ename,dname

                    from emp,dept

                    where emp.deptno=dept.deptno;

                    select ename,dname

                    from emp,dept

                    where emp.deptno=dept.deptno

                          and emp.deptno=30;

                    select e.ename,d.ename

                    from emp e,dept d

                    where e.deptno=d.deptno;

                    select ename,dname

                    from emp e,dept d,

                    where e.deptno=d.deptno

                Joining More than Two Tables

                   hr>select e.LAST_NAME,d.DEPARTMENT_NAME,l.CITY 

                      from employees e,departments d,locations l

                      where e.department_id=d.department_id and d.location_id=l.location_id

         b) 不等值连接 nonequijoins

                    select ename,sal,grade

                    from emp e,salgrade s

                    where e.sal between s.losal and s.hisal 

         c)外连接  outer joins

      展示两表不满足条件的column,+在那边表示多展示此表不满足的column

              SELECT table1.column, table2.column

              FROM table1, table2

              WHERE table1.column = table2.column(+);

                  select e.ename,e.deptno,d.dname

                  from emp e,dept d

                  where e.deptno(+)=d.deptno;

                  select ename,dname

                  from emp,dept

                  where emp.deptno(+)=dept.deptno

                  and emp.deptno(+)=30;