SQL常用函数复习

一、组合查询

      UNION

     定义：组合多个SELECT语句的结果，不包含重复的记录。在使用UNION时，注意每个SELECT语句里必须选择相同数量的字段、相同数量的字段表达式、同样的数据类型、同样的次序----长度可以不同。

      UNION ALL

     定义：同UNION区别就是可以返回重复的记录，而UNION不返回。

      INTERSECT

     定义：可以组合两个SELECT的结果，并且返回连个SELECT中相同的数据。用法与UNION相同（mysql5.0不支持，SQLSever 和 Oracle支持）

      EXCEPT

     定义：组合两个SELECT语句，返回第一个SELECT语句里有但第二个SELECT语句里没有的记录。用法同UNION。Oracle中使用MINUS实现相同的功能。

二、性能调优

 创建索引语法

单字段索引：

    CREATE INDEX INDEX_NAME ON TABLE_NAME

 

唯一索引:

    CREATE UNIQUE INDEX INDEX_NAME ON TABLE_NAME (COLUMN_NAME)

组合索引：

    CREATE INDEX INDEX_NAME ON TABLE_NAME (COLUMN1，COLUMN2)

删除索引：

    DROP INDEX INDEX_NAME

    （MySQL）DROP INDEX INDEX_NAME ON TABLE   _NAME

什么时候避免使用索引：

1)  小规模表时，让索引引擎全表索引往往比查询索引的速度来的快。

2)  当作为WHERE条件时会返回表中大量的数据：比如 省名、性别 不应该作为 索引。

3)  对于经常要被加载或批量操作的表来说，可以在批量操作之前去除索引，在完成操作时在重新创建索引。（因为当表里出入数据时，索引也会被更新，从而增加了额外的开销）

4)  经常被操作的字段不应该设为索引。

5)  不应该把包含大量NULL值的字段设置索引。索引对在不同记录中包含不同数据的字段特别有效。字段中过多的NULL值会严重影响索引的运行效率。

6)  字段特别长的关键字创建索引时要谨慎，因为大量的I/O开销会不可避免地降低数据库性能。

三、SQL语句调整（取决于SQL优化器如何读取语句）

       一般SQL语句调整和数据库调整可以提高数据库性能。

      SQL书写格式

如：SELECT COLUMN1 ,

          COLUMN2

           ……

    FROMTAB1，

         TAB2，

           ……..

    WHERE  TAB1.COLUMN = TAB2.COLUMN

           ………

1)  FROM 后面跟的表，把较小的表放在前面，较大的表放在后面可以获得更好的性能。

2)  WHERE 后面的条件，大多数结合使用一个基表链接到具有一个或多个共有字段的其他表。基表就是主表，查询里的大多数或全部表都与它结合。在WHERE里，来自基表的字段一般放到结合操作的右侧，要被结合的表通常按照从小到大的次序排列，就像FROM子句里表的排序一样。

3)  如：

  FORM TABLE1，

        TABLE2,

        TABLE3

  WHERE  TABLE1.COLUMN =TABLE3.COLUMN    结合条件（基表的字段放在右侧）

     AND TABLE2.COLUMN = TABLE3.COLUMN       结合条件

    [AND   CONDITION1]                        最简单条件

[ AND  CONDITION1]             最严格条件（放在最后面可以提高性能）

4)  其他性能考虑：使用LIKE操作符和通配符；

避免使用OR操作符

避免使用HAVING子句

避免大规模排序操作

使用存储过程

5)  在批加载时关闭索引

四、事务

1)  控制事务的三个命令：COMMIT、ROLLBACK、SAVEPOINT

2)  事务控制命令只与DML命令INSERT、UPDATE和DELETE配合使用。

3)  在没有出现COMMIT或ROLLBACK命令，临时存储区域里的回退信息就会不断增长直到没有剩余空间，导致数据库停止全部进程，知道空间被释放。

五、操作符

   IS NULL   不为空

   BETWEEN   【】两个值之间，包含在内。

   IN       与in中的值相同返回TRUE

   LIKE     有两个：%代表一个或多个任意字符；_代表一个占位符

   EXISTS    用于搜索指定表里是否存在满足特定条件的记录。

   ALL      用于把一个值与另一个集合里的全部值进行比较。

   SOME     用于把一个值与另一个列表里任意值进行比较。

   ANY     

   NOT BETWEEN  （）不包含区间的值，左开右开

   NOT IN    不在IN范围内。

   NOT LIKE  与LIKE相反

   IS NOT NULL  不为空

   NOT EXISTS   与EXISTS相反

六、数据排序于分组

    ROLLUP；在完成了基本的分组数据汇总以后，按照从右到左的顺序，每次去掉字段列表中的最后一个字段，在对剩余的字段进行分组统计，并将获得的小计结果插入返回表中。

    CUBE：对分组列表中的所有字段进行排列组合，并根据每一种组合结果，分别进行统计汇总。

七、常用字符函数

字符函数

1)  串接字符：

SQL Sever： SELECT COLUMN +COLUMN

Oracle：SELECT  COLUMN  || COLUMN

MY SQL:SELECT CONCAT ( COLUMN 1,COLUMN2)

 

2)  TRANSLATE 函数

语法：TRANSLATE(CHARACTER SET , VALUE1,VALUE2)

 

例子：下面的SQL语句把字符串里每个I都替换为A，每个N都替换为B每个D都替换为C

    SELECT  TRANSALTE(CITY,’IND’,’ABC’)

    FORM ENPLOYEE_TBL;

   

3)  REPLACE

用于把某个字符或字符串替换为指定的一个字符（或多个字符），其实用类似TRANSLATE函数，只是它是把一个字符或字符床替换到另一个字符或字符串里。

 

如：下面的语句返回全部的名，并且把全部的T替换为B

SELECT REPLACE (FIRST_NAME.’T’,’B’)FROM EMPLOYEE_TBL

 

4)  UPPER,LOWER

UPPER:把字段里的所有的字母转换成大写。

LOWER：把字段里的所有字母转换成小写。

 

5)  SUBSTR(位置从1开始)

Oracle语法：

    SUBSTR(COLUMNNAME ,STARTING POSITION , LENGTH)

SQL Server语法：

    SUBSTRING( COLUMN NAME,STRING POSITION ,LENGTH)

 

6)  INSTR

定义：用于在字符串里寻找指定的字符集，返回其所在的位置。

语法：

    INSTR(COLUMN NAME,’SET’,[ START POSITION [ ,OCCURRENCE ] ]);

如：下面的SQL语句查找字母A在字段PROD_DESC里第一次出现的位置

SELECT PROD_DESR,

        INSTR(PROD_DESC,’A’,1,1)

FROM PRODUCTS_TBL

 

7)  LTRIM,RTRIM

定义：用于从左剪除字符串里的字符,LTRIM会剪除被搜索的字符串在目标字符串里最后一次出现的位置之左的所有字符。

语法：LTRIM ( CHARACTER STRING [ , ‘SET’])
如：下面的SQL语句返回职位以及职位字符串里从左侧剪除SALES之后的结果

SELECT POSITION,LTRIM ( POSITION ,‘SALES’) FROM EMPLOYEE_PLAY_TBL

 

8)  DECODE

定义：目前只用在Oracle中

语法：DECODE ( COLUMN NAME, ‘SEARCH1’.’RETURN1’,[‘SEARCH2’,’RETURN2’,’DEFAULT’VALUE’])

如：下面搜索姓名，如果找到SMITH,就在结果里显示JONES,否则就显示OTHER

SELECTDECODE(LAST_NAME,’SMITH’,’JONES’,’OTHER’)

FROM EMPLOYEE_TBL

 

9)  LENGTH

用于得到字符串、数字、日期或表达式的长度，单位是字节。

10) IFNULL

定义：如果字段值为null，则用一个值替换（必须是相同类型）

IFNULL(‘VALUE’,’SUBSTITUTION’)

11) COALESCE

返回第一个非NULL的值，如果没有找到非NULL值，就返回一个NULL值。

如：返回BONUS、SALARY、PAY_RATE字段里第一个非NULL值。

SELECTEMP_ID,COALESCE(BONUS,SALARY,PAY_RATE)

FROM EMPLOYEE_PAY_RATE_TBL;

12) LPAD/RPAD

定义：用于在字符串左/右侧添加字符或空格。

语法：LPAD(CHARACTER SET)

如：在每个产品描述左侧添加点，使其总长度达到30个字符
SELECT LPAD(PRO_DESC,30,’.’) PRODUCT

FROM PRODUCTS_TBL;

13) ASCII

定义：返回字符串最左侧字符的ASCII

语法：ASCII(CHARACTER SET)

算数函数

1)  绝对值（ABS）

2)  舍入（ROUND）

3)  平方根(SQRT)

4)  符号(SIGN)

5)  幂（POWER）

6)  上限和下限（CEIL,FLOOR）

7)  指数（EXP）

转换函数

1)  TO_NUMBER(ORACLE)    字符串转数字
如：SELECT EMP_ID , TO_NUMBER(EMP_ID)

FROM EMPLOYEE_TBL

2)  TO_CHAR(ORACLE) / STR(SQL Sever)

时间函数

1)  获得当前时间

数据库	语法
SQL Sever	SELECT GETDATE()
My SQL	SELECT NOW()
ORACLE	SELECT SYSDATE FROM DUAL;

 

2)  时间与日期相加

SQL Server

SELECT DATE_HIRE,DATEADD(MONTH,1,DATE_HIRE)

FROM EMPLOYEE_PAY_TBL

 

ORACLE

SELECT DATE_HIRE,ADD_MONTHS(DATE_HIRE，1)

FROM EMPLOYEE_PAY_TBL

 

MYSQL

SELECTDATE_HIRE , DATE_ADD(DATE_HIRE,INTERVAL 1 DAY),DATE_HIRE +1

FROM EMPLOYEE_PAY_TBL

WHERE EMP_ID = ‘311549902’

 

储存过程

http://blog.youkuaiyun.com/fanyun_01/article/details/53319193

1)   写

[cpp] view plain copy

print?

 

    DROP PROCEDURE "PLName" 

    @ 

    CREATE PROCEDURE "PLName"(--存储过程名字 

    IN IN_ID BIGINT ,                    --以下全是输入参数 

    IN IN_ENTNAME VARCHAR(200) , 

    IN IN_REGNO VARCHAR(50), 

    IN IN_PASSWORD VARCHAR(20), 

    IN IN_LEREP VARCHAR(300), 

    IN IN_CERTYPE CHARACTER(1), 

    IN IN_CERNO VARCHAR(50), 

    IN IN_LINKMAN VARCHAR(50), 

    IN IN_SEX CHARACTER(1), 

    IN IN_MOBTEL VARCHAR(30), 

    IN IN_REQDATE TIMESTAMP, 

    IN IN_REMITEM VARCHAR(300), 

    IN IN_STATE CHARACTER(1), 

    IN IN_TIMESTAMP TIMESTAMP 

    ) 

    BEGIN 

         

      declare V_RESULT  BIGINT;     --声明变量 

       DELETE FROM  TableNameA WHERE ID = IN_ID; 

     

       SET V_RESULT = NULL;          --为变量赋值 

      --检查用户输入的信息是否合法 

     

      select b.id INTO V_RESULT  from TableNameB b,TableNameC c where 正常的判断条件   

      if(V_RESULT IS NOT NULL)  then ---如果合法，执行下面的insert语句 

       INSERT INTOTableNameA(ID,ENTNAME,REGNO,PASSWORD,LEREP,CERTYPE,CERNO,LINKMAN,SEX,MOBTEL,REQDATE,REMITEM,STATE,TIMESTAMP) 

      VALUES(IN_ID,IN_ENTNAME,IN_REGNO,IN_PASSWORD,IN_LEREP,IN_CERTYPE,IN_CERNO,IN_LINKMAN,IN_SEX,IN_MOBTEL,IN_REQDATE,IN_REMITEM,IN_STATE,IN_TIMESTAMP); 

      end if; 

        commit; 

    END 

@ 

2)   使用

call PLName(存储过程名字) （IN_ID,IN_ENTNAME,IN_REGNO,IN_PASSWORD,IN_LEREP,IN_CERTYPE,IN_CERNO,IN_LINKMAN,IN_SEX,IN_MOBTEL,IN_REQDATE,IN_REMITEM,IN_STATE,IN_TIMESTAMP对应的值） 

触发器

3)   --插入时触动

 

CREATE TRIGGERadministrator.tri_insert

AFTER INSERT ONadministrator.A

REFERENCING NEW AS N

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO administrator.B(ID,NAME)VALUES(N.ID,N.NAME );

END  

--测试: INSERT INTO  administrator.A VALUES ('3','Name');

 

4)   --删除时触动

CREATE TRIGGERadministrator.tri_delete

AFTER DELETE ON administrator.A

REFERENCING OLD AS O

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO administrator.B(ID)VALUES(O.ID);

END --测试:DELETE FROMadministrator.A WHERE ID = '3'

 

5)   --更新时触动

CREATE OR REPLACE  TRIGGER administrator.tri_update

AFTER UPDATE OF NAME ONadministrator.A

REFERENCING NEW AS N OLD ASO

FOR EACH ROW MODE DB2SQL

BEGIN ATOMIC

    INSERT INTO administrator.B(ID,NAME)VALUES(O.ID,N.NAME);

END 

--测试:UPDATEadministrator.A SET NAME = 'n2_name' 

 

--有资料提示 DB2 9.7以上版本支持 AFTER INSERT OR DELETE OR UPDATE 写法，可是9.7版本并不支持

例子：

  CREATE OR REPLACE TRIGGER HIRED

   AFTER INSERT OR DELETE OR UPDATE OF SALARYON EMPLOYEE

   REFERENCING NEW AS N OLD AS O FOR EACH ROW

   BEGIN

      IF INSERTING THEN

　　UPDATE COMPANY_STATSSET NBEMP = NBEMP + 1;

      ELSEIF

　　DELETING THEN UPDATECOMPANY_STATS SET NBEMP = NBEMP - 1

      ELSEIF (UPDATING AND (N.SALARY > 1.1 *O.SALARY))

         THEN SIGNAL SQLSTATE '75000' SETMESSAGE_TEXT='Salary increase>10%';

      END IF;

   END;