SQL基础总结

SQL语言基础
1.SQL语言的分类
2.各种语言常见语句写法
3.常用查询语句的使用
4.常用系统函数及其应用
5.分组语句
6.模糊查询
7.表的连接
8.子查询

1.SQL语言的分类
 1.DDL数据定义语言
  CREATE   (创建表、库、类型、函数等等)
  ALTER
  DROP
 例如：
  SQL> CREATE TABLE ABC(A VARCHAR2(20),B CHAR(10));

  表已创建。

  SQL> ALTER TABLE ABC ADD C NUMBER;

  表已更改。

  SQL> ALTER TABLE ABC DROP COLUMN B;

  表已更改。

  SQL> DROP TABLE ABC;

  表已删除。
2.DCL数据控制语言
  GRANT   授权   GRANT  ...   TO USER;
  REVOKE  解除授权 REVOKE ... FROM USER;
  
 例如：创建用户并授权
  SQL> CONNECT SYS/ASCENT AS SYSDBA
  已连接。

  SQL> CREATE USER U1 IDENTIFIED BY ABC;

  用户已创建。

  
  SQL> CONN U1/ABC;
  ERROR:
  ORA-01045: user U1 lacks CREATE SESSION privilege; logon denied

  警告: 您不再连接到 ORACLE。(新建用户u1没有连接数据库的权限-CREATE SESSION)

  

  SQL> conn sys/ascent as sysdba
  已连接。
  SQL> grant create session to u1;

  授权成功。

  SQL> conn u1/abc;
  已连接。

  SQL> create table a(a char(10));
  create table a(a char(10))
  *
  第 1 行出现错误:
  ORA-01031: 权限不足

  SQL> conn sys/ascent as sysdba
  已连接。
  
  SQL> grant connect,resource to u1;

  授权成功。

  SQL> conn u1/abc
  已连接。
  SQL> create table a(a char(10));

  表已创建。

  SQL> select * from a;

  未选定行

  SQL> drop table a;

  表已删除。

  
  管理员登陆状态下：

  SQL> revoke resource from u1;

  撤销成功。

  SQL> conn u1/abc
  已连接。
  SQL> create table a2(name varchar(20));
  create table a2(name varchar(20))
  *
  第 1 行出现错误:
  ORA-01031: 权限不足

  SQL> conn sys/ascent as sysdba
  已连接。
  SQL> grant resource to u1;

  授权成功。

  SQL> conn u1/abc
  已连接。
  SQL> create table a2(name varchar(20));

  表已创建。

  SQL> drop table a;

  表已删除。

  SQL> drop table a2;

  表已删除。
3.DML数据操作语言
  SELECT
  INSERT
  DELETE
  UPDATE

4.常用系统函数及其应用

 <1>、字符
  length（字符的长度）,lengthb（字节的长度）
  ltrim（去除字符串左边空格）,rtrim（去除字符串右边空格）,trim（去除字符串左右边空格）
  substr(str,index,length)

 例子：
 SQL> select length('abcdef') from dual;

 LENGTH('ABCDEF')
 ----------------
                 6

 SQL> select length('abc好ef') from dual;

 LENGTH('ABC好EF')
 -----------------
                 6

 SQL> select lengthb('abc好ef') from dual;

 LENGTHB('ABC好EF')
 ------------------
                  7

 SQL> select ltrim(' abc') from dual;

 LTR
 ---
 abc

 SQL> select rtrim(' abc ') from dual;

 RTRI
 ----
  abc

 SQL> select trim(' abc ') from dual;

 TRI
 ---
 abc

 SQL> select trim(' a b c ') from dual;

 TRIM(
 -----
 a b c

 select substr('abcdefg',2,3) from dual;
 ---------------
 bcd

 考虑：怎么完成左取子字符串和右取子字符串？
 左取3个？
 select substr('abcdefg',1,3) from dual;
 右取3个？
 select substr('abcdefg',('abcdefg').length-3+1,3) from dual;

 <2>、日期
  sysdate(系统时间),current_date(当前时间),next_day
  
  select sysdate from dual;//查询系统时间  22-11月-2008
  
  alter session set nls_date_format ='dd-mm-yyyy hh:mi:ss';//修改时间显示形式
  
  select sysdate from dual;//查询系统时间  22-11-2008 09:42:12
  
  select next_day(sysdate,'星期一') from dual;//从今天开始，下个星期一的日期
  
  
 <3>、转换
  to_char非字符类型转换为字符类型
  to_date非时间类型转换为时间类型
  to_number非数字类型转换为数字类型
  
 SQL> select sysdate from dual;

 SYSDATE
 --------------
 25-11月-10

 SQL> select to_char(sysdate,'yyyy-mm-dd') from dual;

 TO_CHAR(SY
 ----------
 2010-11-25

 
 SQL> select to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dual;

 TO_CHAR(SYSDATE,'YY
 -------------------
 2010-11-25 04:18:18

 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

 TO_CHAR(SYSDATE,'YY
 -------------------
 2010-11-25 16:18:35
 
 
 
 
 
 
 
 
 SQL> select to_date('2010-11-25') from dual;
  select to_date('2010-11-25') from dual
        *
  第 1 行出现错误:
  ORA-01861: 文字与格式字符串不匹配
 
 SQL> select to_date('12-11月-10') from dual;

  TO_DATE('12-11
  --------------
  12-11月-10
  
  
  
  
  
  
 SQL> select to_number('200') from dual;

 TO_NUMBER('200')
 ----------------
     200

 SQL> select to_number('00200') from dual;

 TO_NUMBER('00200')
 ------------------
       200

 SQL> select to_number('00200.09') from dual;

 TO_NUMBER('00200.09')
 ---------------------
       200.09

 SQL> select to_number('s00200.09') from dual;
 select to_number('s00200.09') from dual
      *
 第 1 行出现错误:
 ORA-01722: 无效数字 
  
 
 <4>、聚集函数
  sum,avg,max,min,count
  
  注意：聚集函数不能作为条件直接出现在where的后面,除了直接查询函数的结果使用外，通常使用在分组查询或子查询中。
  
  例如： select * from employee where salary> avg(salary);错误的
  
  注意：大家考虑下面两个语句结果是否一样？
  select count(*) from employee;
  select count(salary) from employee;
  
  
 <5>、其他
  user,decode,nvl

  user 
    select user from dual;//当前登录用户
    show user;//当前登录用户
    
    
  decode(列,x1,y1,y2)
    
    ==>if(列值==x1)
      返回 y1
     else
      返回 y2
  
  
    //查询usr表男生人数   女生人数
    select sum(decode(sex,'男',1,0)) 男生人数,sum(decode(sex,'女',1,0)) 女生人数 from usr;
  
  
  
  
  nvl(列,默认值)  当列值为空，返回默认值
  nvl(sex,'男')  //如果sex值null空， 返回‘男’
  select name,nvl(sex,'男') from usr;

  Oracle中判断某列为空 is null   不为空 is not null  和其他数据库sql一样
  排序 order by   去除重复 distinct   等等一样

5.分组语句
 group by 一般都要使用聚集函数
 

 SQL> select job , sum(sal) from emp group by job;

 JOB         SUM(SAL)
 --------- ----------
 CLERK           4150
 SALESMAN        5600
 PRESIDENT       5000
 MANAGER         8275
 ANALYST         6000

 注意：select 后面出现的查询字段，没有使用函数的一定要在group by的后面出现
 SQL> select job ,ename, sum(sal) from emp group by job;
 select job ,ename, sum(sal) from emp group by job
             *
 第 1 行出现错误:
 ORA-00979: 不是 GROUP BY 表达式

 
 注意：聚集函数是不能出现在where语句的后面作为查询条件，可以使用在having字句的后面
 SQL> select job , sum(sal) from emp where sum(sal)>5000 group by job;
 select job , sum(sal) from emp where sum(sal)>5000 group by job
                                       *
 第 1 行出现错误:
 ORA-00934: 此处不允许使用分组函数
 
 SQL> select job , sum(sal) from emp group by job having sum(sal)>5000;

 JOB         SUM(SAL)
 --------- ----------
 SALESMAN        5600
 MANAGER         8275
 ANALYST         6000
 
 思考：查询哪种工作(job)重复了，返回 重复的工作、重复次数？
 
 SQL> select job,count(job) from emp group by job;

 JOB       COUNT(JOB)
 --------- ----------
 CLERK              4
 SALESMAN           4
 PRESIDENT          1
 MANAGER            3
 ANALYST            2

 SQL> select job,count(job) from emp group by job having count(job)>1;

 JOB       COUNT(JOB)
 --------- ----------
 CLERK              4
 SALESMAN           4
 MANAGER            3
 ANALYST            2

6.模糊查询
 like '%xx%'      like '_xx%'

7.表的连接
 内连接
  select t1.xx,t2.yy from t1,t2 where t1.no=t2.no;
  select t1.xx,t2.yy from t1 join t2 on t1.no = t2.no;
  
 SQL> select empno,ename,dname from dept,emp where dept.deptno = emp.deptno;
 SQL> select empno,ename,dname from dept join emp on dept.deptno = emp.deptno;
  
 外连接
  左外连接
  SQL> select empno,ename,dname
  from dept left join emp on dept.deptno= emp.deptno;

    EMPNO ENAME      DNAME
  ---------- ---------- --------------
     7782 CLARK      ACCOUNTING
     7839 KING       ACCOUNTING
     7934 MILLER     ACCOUNTING
     7566 JONES      RESEARCH
     7902 FORD       RESEARCH
     7876 ADAMS      RESEARCH
     7369 SMITH      RESEARCH
     7788 SCOTT      RESEARCH
     7521 WARD       SALES
     7844 TURNER     SALES
     7499 ALLEN      SALES

    EMPNO ENAME      DNAME
  ---------- ---------- --------------
     7900 JAMES      SALES
     7698 BLAKE      SALES
     7654 MARTIN     SALES
         OPERATIONS
  
   
 等价于==》 
  SQL> select empno,ename,dname
  from dept join emp on dept.deptno= emp.deptno(+);

   
  右外连接
  
  SQL> select empno,ename,dname from emp right join dept  on dept.deptno= emp.deptno;

    EMPNO ENAME      DNAME
  ---------- ---------- --------------
     7782 CLARK      ACCOUNTING
     7839 KING       ACCOUNTING
     7934 MILLER     ACCOUNTING
     7566 JONES      RESEARCH
     7902 FORD       RESEARCH
     7876 ADAMS      RESEARCH
     7369 SMITH      RESEARCH
     7788 SCOTT      RESEARCH
     7521 WARD       SALES
     7844 TURNER     SALES
     7499 ALLEN      SALES

    EMPNO ENAME      DNAME
  ---------- ---------- --------------
     7900 JAMES      SALES
     7698 BLAKE      SALES
     7654 MARTIN     SALES
         OPERATIONS
  
  等价于==》
  SQL> select empno,ename,dname from emp join dept  on dept.deptno= emp.deptno(+);
  
  
  ====》
  SQL> select empno,ename,dname from emp,dept where dept.deptno= emp.deptno(+);
  
  
 总结：
  Oracle的左、右外连接可以使用在外键条件后(+)来简写表示，哪张表后面加(+)，表示另外一张表的
  记录全部返回，有(+)这张表如果没有匹配记录就用空补全。所以使用简写的方式，from后面写表的时候
  无所谓谁在前在后。
  
  
8.子查询
 基本一致
 SQL> select ename from emp ,dept where emp.deptno=dept.deptno;
 
 ==>
 SQL> select ename from emp where deptno in (select deptno from dept);
 
 SQL> select ename from emp where deptno in (select * from dept);  错误的
 
 ==>
 相关子查询（后面子查询的where条件有前面查询的条件）
 SQL> select ename from emp where  exists (select * from dept where dept.deptno = emp.deptno);
 
 相反的
 SQL> select ename from emp where not exists (select * from dept where dept.deptno = emp.deptno);
 
 
 
 
 
查询补充：

合并结果行数据  union

  例如：
  SQL> select empno,ename from emp
  union
  select deptno,dname from dept;

    EMPNO ENAME
  ---------- --------------
    10 ACCOUNTING
    20 RESEARCH
    30 SALES
    40 OPERATIONS
     7369 SMITH
     7499 ALLEN
     7521 WARD
     7566 JONES
     7654 MARTIN
     7698 BLAKE
     7782 CLARK

    EMPNO ENAME
  ---------- --------------
     7788 SCOTT
     7839 KING
     7844 TURNER
     7876 ADAMS
     7900 JAMES
     7902 FORD
     7934 MILLER

  已选择18行。

   
  合并两个查询返回结果行，并且会去除重复行。
  
  例如：
  SQL> select deptno from emp
   union
   select deptno from dept;

   DEPTNO
  ----------
    10
    20
    30
    40

    
    
拷贝表（创建表  将其中一张表的结构及数据拷贝给新表）  参考mysql讲过的语法

插入多行表记录： insert into 表1 (column1,column2) select column1,column2 from 表2;

附加：

==================
基本SQL
==================
1.查询语句语法
 select * | {[distinct] column |expression [alias],...}
 from table;

 语法要求：1.关键词不区分大小写 2.关键字不能简写或换行
2.
 iSQL*Plus:
 --头标题：显示居中
 --标题显示：大写

 SQL*Plus:
 --字符和日期：居左，  数字的居右
 --标题显示：大写

3. 查询时候指定别名 [as] 别名，显示时会默认大写，我们要保持制定的大小写时，
   别名要用双引号引住

 例如： select id  as "user_id" from user;

4.oracle 的查询字符串连接  用||连接   作用：提高结果的可读性

 例如 select name ||'''id is '|| id   from user2;
  NAME||'''IDIS'||ID 
  li'id is 1 
  ww'id is 2 
  ss'id is 3 
  ff'id is 4 

5. 查看表结构

 desc tablename;
6.sql*plus 清空屏幕命令  clea scre

7.逻辑运算符 and 、or 、not优先级从高到底是  not>and>or

8.排序 order by  列名  asc(默认)/desc

 多列排序：   order by  列名1  , 列名2 desc；

 这样首先按照第一个默认升序排列，然后相同的再按照第二列降序排列

 
 
 
 
 
 
9. Single-Row Functions 单行函数
 
 函数：计算一个值
 过程: 完成一个事件

 
   【1】Character Functions  字符函数
  <case>   lower/upper/initcap
  <character> concat/substr/length/instr/lpad|rpad/trim/replace

 
 实例<case>

  Function              Result
    lower('SQL Course')     sql course      //字母小写函数
    upper('SQL Course')     SQL COURSE     //字母大写函数
    initcap('SQL Course')    Sql Course     //首字母大写其他小写函数

 实例<character>

 concat('Hello','World')  ----->  HelloWorld   //字符连接函数
 substr('HelloWorld',1,5) ----->  Hello        //截取字符函数
 length('HelloWorld')  ----->  10           //字符长度函数
 instr('HelloWorld','l')  ----->  3            //返回第一次出现位置索引函数
 lpad(salary,10,'*')      ----->  *****24000   //左边填充到相应长度
 rpad(salary,10,'*')      ----->  24000*****   //右边填充到相应长度
 trim('H' FROM 'HelloWorld')--->  elloWorld    //从一个字符中删除知道字符

 例子：
 select concat(name,salary) from employee t;
 1 zhangsan2000
 2 lisi2000.3
 3 zhangsanfeng4000.3
 4 jiaoxueli4000.3
 5 zhaoliu200.3

 select substr(name,1,4) from employee;
 1 zhan
 2 lisi
 3 zhan
 4 jiao
 5 zhao

 select length(name) from employee where lower(name)='jiaoxueli';
 1 9

 
 select name, instr(name,'i') from employee ;
 1 zhangsan 0
 2 lisi  2
 3 zhangsanfeng 0
 4 jiaoxueli 2
 5 zhaoliu  6

 select salary,lpad(salary,10,'#'),rpad(salary,10,'#')  from employee ;
 1 2000 ######2000 2000######
 2 2000.3 ####2000.3 2000.3####
 3 4000.3 ####4000.3 4000.3####
 4 4000.3 ####4000.3 4000.3####
 5 200.3 #####200.3 200.3#####

 select name , trim('z' from name)  from employee ;
 1 zhangsan hangsan
 2 lisi  lisi
 3 zhangsanfeng hangsanfeng
 4 jiaoxueli jiaoxueli
 5 zhaoliu  haoliu

    【2】Number Functions  数字函数
 
 <1> round(45.926,2) ----> 45.93   保留指定个数小数，并完成四舍五入
 <2> trunc(45.926,2) ----> 45.92   保留指定个数小数，不做四舍五入
 <3> mod(1600,300)   ----> 100     求余函数

 例子
 
  select salary , round(salary,3) , trunc(salary,3) , mod(salary,300) from employee where eid=5;
 
 1 5000.5567 5000.557 5000.556 200.5567

 select round(45.926,2),trunc(45.926,2),mod(1600,300) from dual;
 1 45.93 45.92 100

 select round(45.926,-1)  from dual;  //负数，表示小数点左边第几位取舍，此例小数点左边1位5进一位得50
 1 50

 select round(45.926,-2)  from dual;  //负数，小数点左边第二位4取舍，舍掉得0
 1 0

 select round(55.926,-2)  from dual;  //负数，小数点左边第二位5取舍，进一位得100
 1 100
 
    【3】date Functions   时间类型函数

 <1>  sysdate 系统时间
 <2>  months_between(date1,date2)  两个时间之间的月数
 <3>  add_months(date1,number)     时间增加number个月数   负数就是减
 <4>  next_day(date,'星期五')      指定时间的下一个星期五  ？？？？？？？？
 <5>  last_day(date)    指定时间的最后一天

 <6>  round(date)/round(date,'MONTH')/round(date,'YEAR') 对时间四舍五入  日/月/年
 <7>  trunc(date)/trunc(date,'MONTH')/trunc(date,'YEAR') 不进行入

 ==================================================================================
 **********************************************************************************
 dual这是个虚拟表，查询时永远返回一条记录。
 你在操作大多数Oracle对象的时候都可以使用这个表，比如sequence，sysdate等等。
 比如select sysdate from dual
 select seq1.nextval from dual
 等等，一般也就是用来做这个事情的
 ==================================================================================
 例子：
 
 SQL> select sysdate from dual;

  SYSDATE
  --------------
  08-7月 -08

 SQL> select months_between(sysdate,start_word) from employee;

 MONTHS_BETWEEN(SYSDATE,START_WORD)
 ----------------------------------
                                 0
                                 0
                                 0
                                 0
                                 0

 SQL> update employee set start_word = add_months(start_word,-1) ;

 已更新5行。

 SQL> select months_between(sysdate,start_word) from employee;

 MONTHS_BETWEEN(SYSDATE,START_WORD)
 ----------------------------------
                                 1
                                 1
                                 1
                                 1
                                 1

 
 SQL> select last_day(start_word) from employee;

  LAST_DAY(START
  --------------
  30-6月 -08
  30-6月 -08
  30-6月 -08
  30-6月 -08
  30-6月 -08

 SQL> select round(start_word) ,trunc(start_word) , start_word from employee;

 ROUND(START_WO TRUNC(START_WO START_WORD
 -------------- -------------- --------------
 09-6月 -08     08-6月 -08     08-6月 -08
 09-6月 -08     08-6月 -08     08-6月 -08
 09-6月 -08     08-6月 -08     08-6月 -08
 09-6月 -08     08-6月 -08     08-6月 -08
 09-6月 -08     08-6月 -08     08-6月 -08

 SQL> select round(start_word,'MONTH') , trunc(start_word,'MONTH') ,
  round(start_word,'YEAR'),trunc(start_word,'YEAR') ,
  start_word
  from employee;

 ROUND(START_WO TRUNC(START_WO ROUND(START_WO TRUNC(START_WO START_WORD
 -------------- -------------- -------------- -------------- --------------
 01-6月 -08     01-6月 -08     01-1月 -08     01-1月 -08     08-6月 -08
 01-6月 -08     01-6月 -08     01-1月 -08     01-1月 -08     08-6月 -08
 01-6月 -08     01-6月 -08     01-1月 -08     01-1月 -08     08-6月 -08
 01-6月 -08     01-6月 -08     01-1月 -08     01-1月 -08     08-6月 -08
 01-6月 -08     01-6月 -08     01-1月 -08     01-1月 -08     08-6月 -08

     【4】类型转换

 <1> varchar2 / char----->number          to_number
 <2> varchar2 / char----->date            to_date(
 <3> number         ----->varchar2  to_char(number,'format_model') 
 <4> date           ----->varchar2  to_char(date,'format_model')

 例子

 SQL> select to_char(start_word,'dd-mm-yyyy') from employee;

  TO_CHAR(ST
  ----------
  08-06-2008
  08-06-2008
  08-06-2008
  08-06-2008
  08-06-2008

 SQL> select to_char(start_word,'dd-MONTH-yyyy') from employee;

  TO_CHAR(START_
  --------------
  08-6月 -2008
  08-6月 -2008
  08-6月 -2008
  08-6月 -2008
  08-6月 -2008

 SQL> select to_char(salary,'$9999.99') from employee;   //$前面加$符号  9是占位符，表示数字几位

  TO_CHAR(S
  ---------
   $2000.00
   $2000.30
   $4000.30
   $5000.56
    $200.30

 SQL> select to_char(salary,'l9999.99') from employee;  //l显示本地货币符号

  TO_CHAR(SALARY,'L9
  ------------------
          ￥2000.00
          ￥2000.30
          ￥4000.30
          ￥5000.56
           ￥200.30
 

 SQL> select to_char(salary,'l9,999.00') from employee;  //0和9都是占位符，“，”可以在千位分割

  TO_CHAR(SALARY,'L9,
  -------------------
          ￥2,000.00
          ￥2,000.30
          ￥4,000.30
          ￥5,000.56
            ￥200.30

 SQL> select to_char(salary,'l0,000.00') from employee; //0和9占位符不同的是9位数不够不补充，0补充

  TO_CHAR(SALARY,'L0,
  -------------------
          ￥2,000.00
          ￥2,000.30
          ￥4,000.30
          ￥5,000.56
          ￥0,200.30

    【5】 通用函数

 <1> nvl(expr1,expr2)    判断第一个是否为空，如果为空显示第二个
 <2> nvl2(expr1,expr2,expr3)   判断第一个是否空，不是空选择第二个，是空选择三个
 <3> nullif(expr1,expr2)   判断这两个是否相等，如果相等填充空值，不等显示expr1
 <4> coalesce(expr1,expr2,...,exprn) 判断空，依次往下比较，直到返回第一个不为空的

 例子：

 SQL> update employee set salary=null where eid=6;

 已更新 1 行。

 SQL> select * from employee;

         EID NAME                     SALARY     DEPTID START_WORD
  ---------- -------------------- ---------- ---------- --------------
          2 zhangsan                   2000          1 08-6月 -08
          3 lisi                     2000.3          1 08-6月 -08
          4 zhangsanfeng             4000.3          2 08-6月 -08
          5 jiaoxueli             5000.5567          3 08-6月 -08
          6 zhaoliu                                  4 08-6月 -08

 SQL> select nvl(salary,0.0) from employee;

  NVL(SALARY,0.0)
  ---------------
            2000
          2000.3
          4000.3
        5000.5567
               0

 
 SQL> select eid, nvl2(salary,'有工资','无工资') from employee;

         EID  NVL2(S
  ---------- ------
          2  有工资
          3 有工资
          4  有工资
          5  有工资
          6  无工资

 SQL> select eid,  nullif(eid,3) from employee;

         EID NULLIF(EID,3)
  ---------- -------------
         2             2
          3
          4             4
          5             5
          6             6

     【6】case语句   (if-then-else)
 
 语法： case expr when ... then ...
    [when... then ...
    ...
    else ...]
  end

 例子：

 SQL> select eid , salary,
                case eid when 3 then '3号'
                         when 5 then '5号'
                    else '未知号'
                end
             from employee;

         EID      SALARY   CASEEI
  ---------- ----------   ------
          2        2000   未知号
          3       2000.3   3号
          4       4000.3   未知号
          5    5000.5567  5号
          6               未知号

     【7】decode函数  条件结果函数 和case差不多
 
 语法： decode(col|expression ,search1 ,result1
           [,search2,result2,...,]
           [,default])

 例子：
 
  SQL> select eid ,
                decode(eid,3,'3号',
                                       5,'5号',
                       '未知')
          from employee;

         EID  DECO
  ---------- ----
         2  未知
          3  3号
          4  未知
          5  5号
          6  未知