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