oracle10g学习笔记之 SQL语言基础

一、语言的分类

1、DDL(Data Definition Language 数据定义语言)
  * CREATE（创建）
      CREATE TABLE、CREATE INDEX
  * ALTER(更改)
      ALTER TABLE、ALTER PROCEDURE
  * DROP(删除)
      DROP TABLE、DROP INDEX

2、DML(Data Manipulation Language 数据操作语言)
  * SELECT(检索数据行)
  * UPDATE(更改表数据)
  * DELETE(删除表中数据行)
  * INSERT(增加表中数据行)

3、DCL(Data Control Language 数据控制语言)
  * GRANT(将权限/角色赋予用户或其他角色)
      将表(TABLE_NAME)的查询权限赋予用户(USER_NAME)
      GRANT SELECT ON TABLE_NAME TO USER_NAME
  * REVOKE(从用户或数据库角色回收权限)
      REVOKE SELECT ON TABLE_NAME FROM USER_NAME

二、常用的系统函数
   在下面会出现DUAL表，这个表是指当没有目标表的时候，通常用在SELECT语句中，它是 Oracle中的一个实际存在的表，任何用户都可以读取
1、字符函数
  * LENGTH()   返回字符长度 包括空格
      SELECT LENGTH('ABCDE') FROM DUAL; 结果：5
  * LENGTHB()  返回字节长度 包括空格
      SELECT LENGTHB('ABCDE汉') FROM DUAL; 结果：7
  * LTRIM()  去掉字符左边空格
      SELECT LTRIM(' ABCDE') FROM DUAL;
      SELECT LENGTH(LTRIM(' ABCDE')) FROM DUAL;
  * RTRIM()  去掉字符右边空格
      SELECT RTRIM('ABCDE ') FROM DUAL;
  * TRIM()  去掉字符左右空格
      SELECT TRIM(' ABCDE ') FROM DUAL;
  * SUBSTR(str,index,len)  截取字符串,从下标index开始，截取长度为len的字符串.
      当下标取0或1的时候，结果一致
      SELECT SUBSTR('ABCD',2,3) FROM DUAL; 结果：BCD
  * SUBSTR(str,index)  和上面的函数差一个参数，但意义完全不同
    当index为正数时 从下标index开始截取，直到字符结尾为止
      SELECT SUBSTR('ABCD',2) FROM DUAL; 结果：BCD
    当index为负数时 从字符结尾开始，向字符起始位置截取index个字符
      SELECT SUBSTR('ABCD',-2) FROM DUAL;结果： CD
  * REPLACE(str,old-str,new-str)  替换字符串中的字符
      SELECT REPLACE('ABCDE','AB','汉字') FROM DUAL; 结果：汉字CDE

2、日期函数
(1)SYSDATE
     SELECT SYSDATE FROM DUAL;
(2)CURRENT_DATE
     SELECT CURRENT_DATE FROM DUAL;
(3)NEXT_DAY(date,week)  返回指定日期date的下一个周几,如果今天为周二，week指定为星期三，那么结果是第二天的日期；如果week指定为周二之前的日期，如周一，那么结果为下周的日期
      SELECT NEXT_DAY(SYSDATE,'星期二') FROM DUAL;

PS:如果想对显示的日期进行格式化，进行下面操作：
   ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY HH:MI:SS';(在oracle中MI表示分钟)
当退出会话，这种格式化会自动消失

3、转换函数
(1)TO_CHAR(expression,format)  将对应的表达式转换成格式化的字符
     SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD') FROM DUAL;
     SELECT TO_CHAR(10,99.99) FROM DUAL;
(2)TO_DATE(str,format)  将字符串转换成格式化的日期
     SELECT TO_DATE('20091211','YYYYMMDD') FROM DUAL;
(3)TO_NUMBER(str,format)  将字符串转换成相应的数字
     SELECT TO_NUMBER('1400','9999.99') FROM DUAL;

4、聚集函数
(1)MAX(expression)  返回最大值
     SELECT MAX(SAL) FROM EMP;//返回EMP表中字段SAL所有值中的最大值
(2)MIN(expression) 返回最小值
     SELECT MIN(SAL) FROM EMP;//返回EMP表中字段SAL所有值中的最小值
(3)AVG(expression) 返回平均值
     SELECT AVG(SAL) FROM EMP;//返回EMP表中字段SAL所有值的平均值
(4)COUNT(expression) 返回个数
     SELECT COUNT(*) FROM EMP;//返回EMP表中的行数
(5)SUM(expression) 返回所有非空数值的综合
    SELECT SUM(SAL) FROM EMP;//返回EMP表中字段SAL中所有值的和

5、其他函数
(1)NVL(expression,replacevalue)  如果表达式为空，则返回replacevalue
     SELECT A1,NVL(A2,'空值') FROM TAB_NAME;
(2)DECODE(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
      含义如下：
    IF 条件=值1 THEN  RETURN(翻译值1)
　　ELSIF 条件=值2 THEN  RETURN(翻译值2)
　　......
　　ELSIF 条件=值n THEN  RETURN(翻译值n)
　　ELSE　RETURN(缺省值)
　　END IF
    /** 判断表DEPT中字段LOC，如果值为NEW YORK,则返回纽约，反之则返回不是纽约，DECODE后跟的是不是纽约是为了显示结果时更加清晰而加的注释    */
    SELECT DECODE(LOC,'NEW YORK','纽约','不是纽约')是不是纽约 FROM DEPT;

三、常用的语句
   
1、GROUP BY 子句
    主要作用是汇总信息，使用分组函数将多组数据集合在一起
    SELECT JOB,SUM(SAL)工资 FROM EMP GROUP BY JOB;
    PS：当使用GROUP BY语句时，SELECT后的字段，必须出现在GROUP BY后面，除非它是以分组函数的形式存在。就像上面语句中SAL字段就是以分组函数的形式出现的。
    举个例子：
    SELECT ENAME,JOB, SUM(SAL)工资 FROM EMP GROUP BY JOB;
    结果：第 1 行出现错误:ORA-00979: 不是 GROUP BY 表达式
    如果想要避免这个错误，有两种方法：
    (1) SELECT COUNT(ENAME),JOB, SUM(SAL)工资 FROM EMP GROUP BY JOB;
    (2) SELECT ENAME,JOB, SUM(SAL)工资 FROM EMP GROUP BY ENAME,JOB;