SQL基本语句总结

你的名字 2022-08-21 09:45 245阅读 0赞

执行顺序:
    　　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