SQL语法基础及注意事项

        SQL语法基础及注意事项

        抽空再次读了《SQL必知必会》，书很薄，全读下来也不过半天时间，但是里面很多知识点还是值得我们关注的。现在，把这些容易引起遗漏的地方再次整理出来，供自己及各位网友参考。

        1.SELECT连接符
          把列与列，列与字符连接在一起，用 ‘||’表示。可以用来‘合成’列。例如：

        SELECT mio_cust_name||'-'||mio_cust_no as newname         FROM plnmio_rec        WHERE rownum<10;

        2.DISTINCT的用法
          DISTINCT的作用为去掉重复行，需要注意其可指定多个字段，但是最好放在最后，否则容易造成重复。例如：

        SELECT DISTINCT cntr_type,mio_item_code,a.*         FROM plnmio_rec a;//选出所有字段不同的记录

       3.DESCRIBE
          DESCRIBE+表名可以显示表结构（限在SQLPLUS中）。例如：DESCRIBE plnmio_rec;
        4.函数
          注意在使用DBMS的函数时一定要考虑移植性的问题，各DBMS的函数不尽相同，未来若要迁移数据库会比较麻烦。以下是ORACLE常用的函数：
          1）字符函数
                大小写转换：LOWER(),UPPER(),INITCAP()//第一个字母变大写;
                字符控制：CONCAT(str1,str2)//两个字符串连接
                SUBSTR(str1,n,m)//截取子串，从第n位，截取m位（从1计数）
                LENGTH(str)//获取字符串长度
                INSTR(str,substr)//获取子串在源串中的第几位（从1计数）
                LPAD|RPAD(str,n,str1)//从左|右将str用str1补足成n位，如果n小于str长度既从左|右截取n位
                TRIM(str)//去掉左右空格
                NVL(str1,repstr)//如果str1为NULL，则NVL函数返回repstr的值，否则返回str1的值，如果都为NULL，返回NULL
          2）日期函数
                MONTHS_BETWEEN：两个日期相差的月数
                ADD_MONTHS：向指定日期中加上若干月数
                NEXT_DAY(date1,m)：指定date1日期的下m天，从后天开始算
                LAST_DAY：本月的最后一天
          3）类型转换
                TO_DATE，TO_NUMBER，TO_CHAR：货币TO_CHAR(salary, '$99,999.00')；日期TO_CHAR(sysdate,'YYYY-MM-DD HH24:MI:SS')
          4）分组函数
                分组函数作用于一组数据，并对一组数据返回一个值，组函数忽略空值。组函数包括：AVG、COUNT、MAX、MIN、STDDEV标准偏差、SUM；
                COUNT(DISTINCT expr) 返回 expr非空且不重复的记录总数；
                NVL函数使分组函数无法忽略空值。SELECT AVG(NVL(commission_pct, 0))。
                以上函数可使用DISTINCT去掉重复的值，AVG(DISTINCT price)，ORACLE不支持TOP函数，可以使用ROWNUM代替。
                所用包含于SELECT 列表中，而未包含于组函数中的列都必须包含于 GROUP BY 子句中。
                不能在WHERE子句中使用组函数，但可以在HAVING子句中使用组函数。因为WHERE过滤行，在分组钱进行过滤；HAVING过滤分组。
         5.多表连接
            1）内连接：（等值/不等值/自然）查询操作列出与连接条件匹配的数据行，它使用比较运算符比较被连接列的列值；
            2）自然连接：找到A和B中关联字段都存在的全部属性并删除重复的（等值内连接并删除重复的记录）；nature join一般与using(两表相同的属性名)同时；
            3）外连接：返回到查询结果集合中的不仅包含符合连接条件的行，而且还包括左表(左外连接或左连接))、右表(右外连接或右连接)或两个边接表(全外连接)中的所有数据行。（1）left join返回包括左表中的所有记录和右表中联结字段相等的记录；（2）right join返回包括右表中的所有记录和左表中联结字段相等的记录；Oracle中使用“(+)”在where子句中作为外连接标示；
            4）交叉连接：不带WHERE子句，它返回被连接的两个表所有数据行的笛卡尔积（由没有连结条件的表关系返回的结果为笛卡尔积），返回到结果集合中的数据行数等于第一个表中符合查询条件的数据行数乘以第二个表中符合查询条件的数据行数。例，titles表中有6类图书，而publishers表中有8家出版社，则下列交叉连接检索到的记录数将等于6*8=48行。
         6.组合查询
            UNION自动去掉重复的行，UNION ALL则列出所有的行。
         7.LIKE的使用
           1）尽量不要使用 like '%..%'，开头和结尾都是%，很难优化。
           2）对于 like '..%..' (不以%开头)，Oracle可以应用colunm上的index。例如，常量开头的，利用index：

            SELECT *             FROM test_like             WHERE object_name like AS%';

           3）对于 like '%...' 的 (不以 % 结尾)，可以利用reverse + function index 的形式，变化成like '..%'。例如，以常量结束，直接写的时候是不能应用index的：

            SELECT *             FROM test_like             WHERE object_name LIKE '%S';

                 但是，加个reverse函数，又可以用上index了：

            SELECT *             FROM test_like             WHERE reverse(object_name) LIKE reverse('%AS');

        8.插入数据
            INSERT INTO 表(字段列表) VALUES(值列表)；
            INSERT INTO表1(字段列表) SELECT 字段列表 FROM 表2；字段列表的名称可不同，关键在于位置的匹配，既选出的第一列插入表1的第一列中。可简化为

           SELECT *            INTO new_table_name [IN externaldatabase]            FROM old_tablename

        9.更新删除
            在写出UPDATE和DELETE语句前，先写SELECT语句进行数据的检查；
            在写出UPDATE和DELETE语句时，先写WHERE语句，以免操作错误；
        10.视图
            好处很多：重用SQL，保护数据，简化复杂的SQL操作等；但是，因为视图不包含物理数据，所以要关注视图引发的效率问题；另外，视图不允许有索引，触发器和默认值。一般情况下，视图作为查询数据使用，很少用作更新及删除数据使用，这一操作也依赖于DBMS，有些DBMS完全禁止了改操作。