SQL基本语句总结

执行顺序:
　　SELECT select_list
　　[ INTO new_table ]
　　FROM table_source
　　[ WHERE search_condition ]
　　[ GROUP BY group_by_expression ]
　　[ HAVING search_condition ]
　　[ ORDER BY order_expression [ ASC | DESC ] ]
创建：
    create database dbname；
    create  table tablename (column_name1 type(size), column_name1 type(size));
    束缚：
        NOT NULL - 指示某列不能存储 NULL 值。
        UNIQUE - 保证某列的每行必须有唯一的值。
        PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
        FOREIGN KEY - （外键）保证一个表中的数据匹配另一个表中的值的参照完整性。
        CHECK - 保证列中的值符合指定的条件。
        DEFAULT - 规定没有给列赋值时的默认值。

查询：
            select column_name,column_name from tablename;
            select * from tablename;
            select distinct colunmn_name from tablename;
    (where) select column_name from tablename where column_name='c1';
    (like)  select column_name from tablename where column_name like 'city%';(以city结尾)
            select column_name from tablename where column_name like '％city%';(包含city)
            select column_name from tablename where column_name not like 'city%';(以city结尾)
    (in)    select column_name from tablename where column_name in ('a','b');(column_name为a或者为b)
    (between) select column_name from tablename where column_name between 10 and 20;
              select column_name from tablename where column_name not between a and z;
    通配符：
            %   替代 0 个或多个字符
            _   替代一个字符
            [charlist]  字符列中的任何单一字符
            [^charlist] or [!charlist]  不在字符列中的任何单一字符
    （order by） select column_name from tablename where column_name='q' order by column_name ASC | DESC;
插入：(无from)
    insert into tablename (column_name1,column_name2) values (v1,v2);
    insert into tablename values (v1,v2,v3,);
更新：（无from）
    update tablename set column_name1=c1,column_name=c2 where column_name='c';
删除：
    delete from tablename where column_name='c';
top：
    select top 2 * from tablename;
    select top 50 percent * from tablename;
别名：
    select column_name as c1,column_name as c2 from tablename as t;
连接：(inner join / left join /right join)
    select column_name from tablename1 t1 inner join tablename2 t2 on t1.name=t2.name;
合并:(union）
    select column_name from tablename1 union select column_name from tablename2;
    请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个SELECT 语句中的列的顺序必须相同。 
复制：
    (select into) select *(或者column_name) into tablename from tablename2;
    (insert into select) insert into table2 select * from table1;
索引：
    create index index_name on table_name;
drop:
    drop index table_name.index_name;
    drop database db_name；
    drop tabel table_name;
    truncate table table_name;
alert:
    alert table table_name add column_name type;
    alert table table_name alert column column_name type;
    alert table table_name drop column column_name;
函数：
    AVG() - 返回平均值
    COUNT() - 返回行数
    FIRST() - 返回第一个记录的值
    LAST() - 返回最后一个记录的值
    MAX() - 返回最大值
    MIN() - 返回最小值
    SUM() - 返回总和

    UCASE() - 将某个字段转换为大写
    LCASE() - 将某个字段转换为小写
    MID() - 从某个文本字段提取字符
    LEN() - 返回某个文本字段的长度
    ROUND() - 对某个数值字段进行指定小数位数的四舍五入
    NOW() - 返回当前的系统日期和时间
    FORMAT() - 格式化某个字段的显示方式

快速参考：
http://www.runoob.com/sql/sql-quickref.html

        SQL 语句  语法
        AND / OR    SELECT column_name(s)
        FROM table_name
        WHERE condition
        AND|OR condition
        ALTER TABLE ALTER TABLE table_name 
        ADD column_name datatype
        or
        ALTER TABLE table_name 
        DROP COLUMN column_name
        AS (alias)  SELECT column_name AS column_alias
        FROM table_name
        or
        SELECT column_name
        FROM table_name AS table_alias
        BETWEEN SELECT column_name(s)
        FROM table_name
        WHERE column_name
        BETWEEN value1 AND value2
        CREATE DATABASE CREATE DATABASE database_name
        CREATE TABLE    CREATE TABLE table_name
        (
        column_name1 data_type,
        column_name2 data_type,
        column_name2 data_type,
        ...
        )
        CREATE INDEX    CREATE INDEX index_name
        ON table_name (column_name)
        or
        CREATE UNIQUE INDEX index_name
        ON table_name (column_name)
        CREATE VIEW CREATE VIEW view_name AS
        SELECT column_name(s)
        FROM table_name
        WHERE condition
        DELETE  DELETE FROM table_name
        WHERE some_column=some_value
        or
        DELETE FROM table_name 
        (Note: Deletes the entire table!!)
        DELETE * FROM table_name 
        (Note: Deletes the entire table!!)
        DROP DATABASE   DROP DATABASE database_name
        DROP INDEX  DROP INDEX table_name.index_name (SQL Server)
        DROP INDEX index_name ON table_name (MS Access)
        DROP INDEX index_name (DB2/Oracle)
        ALTER TABLE table_name
        DROP INDEX index_name (MySQL)
        DROP TABLE  DROP TABLE table_name
        GROUP BY    SELECT column_name, aggregate_function(column_name)
        FROM table_name
        WHERE column_name operator value
        GROUP BY column_name
        HAVING  SELECT column_name, aggregate_function(column_name)
        FROM table_name
        WHERE column_name operator value
        GROUP BY column_name
        HAVING aggregate_function(column_name) operator value
        IN  SELECT column_name(s)
        FROM table_name
        WHERE column_name
        IN (value1,value2,..)
        INSERT INTO INSERT INTO table_name
        VALUES (value1, value2, value3,....)
        or
        INSERT INTO table_name
        (column1, column2, column3,...)
        VALUES (value1, value2, value3,....)
        INNER JOIN  SELECT column_name(s)
        FROM table_name1
        INNER JOIN table_name2 
        ON table_name1.column_name=table_name2.column_name
        LEFT JOIN   SELECT column_name(s)
        FROM table_name1
        LEFT JOIN table_name2 
        ON table_name1.column_name=table_name2.column_name
        RIGHT JOIN  SELECT column_name(s)
        FROM table_name1
        RIGHT JOIN table_name2 
        ON table_name1.column_name=table_name2.column_name
        FULL JOIN   SELECT column_name(s)
        FROM table_name1
        FULL JOIN table_name2 
        ON table_name1.column_name=table_name2.column_name
        LIKE    SELECT column_name(s)
        FROM table_name
        WHERE column_name LIKE pattern
        ORDER BY    SELECT column_name(s)
        FROM table_name
        ORDER BY column_name [ASC|DESC]
        SELECT  SELECT column_name(s)
        FROM table_name
        SELECT *    SELECT *
        FROM table_name
        SELECT DISTINCT SELECT DISTINCT column_name(s)
        FROM table_name
        SELECT INTO SELECT *
        INTO new_table_name [IN externaldatabase]
        FROM old_table_name
        or
        SELECT column_name(s)
        INTO new_table_name [IN externaldatabase]
        FROM old_table_name
        SELECT TOP  SELECT TOP number|percent column_name(s)
        FROM table_name
        TRUNCATE TABLE  TRUNCATE TABLE table_name
        UNION   SELECT column_name(s) FROM table_name1
        UNION
        SELECT column_name(s) FROM table_name2
        UNION ALL   SELECT column_name(s) FROM table_name1
        UNION ALL
        SELECT column_name(s) FROM table_name2
        UPDATE  UPDATE table_name
        SET column1=value, column2=value,...
        WHERE some_column=some_value
        WHERE   SELECT column_name(s)
        FROM table_name
        WHERE column_name operator value