SQL语言基础回顾整理

最新推荐文章于 2024-04-16 10:05:34 发布

xmind果果

最新推荐文章于 2024-04-16 10:05:34 发布

阅读量2.2k

点赞数

分类专栏：【sql】文章标签：数据库语言 sql

本文链接：https://blog.csdn.net/mengxiangxingdong/article/details/77119354

版权

【sql】专栏收录该内容

2 篇文章 0 订阅

订阅专栏

DDL语句

DDL（Data Definition Language）是数据定义语言，简单来说，就是对数据库内部的对象进行创建、删除、修改等操作的语言

    1、创建数据库
        CREATE    DATABASE    dbname
    2、删除数据库
        DROP    DATABASE    dbname
    3、创建表
        CREATE    TABLE    tablename ( column_name_1    column_type_1    constraints,    column_name_2    column_type_2    constraints,    ...    column_name_n    column_type_n    constraints )
    4、删除表
        DROP    TABLE    tablename
    5、修改表
        1、修改表类型
            ALTER    TABLE    tablename    MODIFY    [ COLUMN ]    column_definition    [ FIRST | AFTER    col_name ]
        2、增加表字段
            ALTER    TABLE    tablename    ADD    [ COLUMN ]    column_definition    [ FIRST | AFTER    col_name ]
        3、删除表字段
            ALTER    TABLE    tablename    DROP    [ COLUMN ]    col_name
        4、字段改名
            ALTER    TABLE    tablename    CHANGE    [ COLUMN ]    old_col_name    column_definition    [ FIRST | AFTER    col_name ]
        5、更改表名
            ALTER    TABLE    tablename    RENAME    [ TO ]    new_tablename

DML语句

DML（Data Manipulation Language）操作是指对数据库中表记录的操作，主要包括表记录的插入、删除、更新和查询，是开发人员日常使用最频繁的操作。

    1、插入记录
        INSERT    INTO    tablename ( field1,     field2,    ...    fieldn )    VALUES ( value1,    value2,    ...    valuen )
    2、更新记录
        UPDATE    tablename    SET    field1=value1,    field2=value2,    ...    fieldn=valuen    [ WHERE    where_contition ]
    3、删除记录
        DELETE    FROM    tablename    [ WHERE    where_contition ]
    4、查询记录
        1、普通查询
            SELECT    *    FROM    tablename
        2、不重复查询
            SELECT    DISTINCT    *    FROM    tablename
        3、条件查询
            SELECT    *    FROM    tablename    [ WHERE    where_contition ]
        4、排列查询
            SELECT    *    FROM    tablename    [ WHERE    where_contition ]    [ ORDER    BY    field1    [ DESC | ASC ],    field2    [ DESC | ASC ],    ...    fieldn    [ DESC | ASC ] ]
        5、限制查询
            SELECT    *    FROM    tablename    [ WHERE    where_contition ]    [ LIMIT    offset_start,    row_count ]
        6、集合查询
            SELECT    [ field1,    field2,    ...    fieldn ]    fun_name    FROM    tablename    [ WHERE    where_contition ]    [ GROUP    BY    field1,    field2,    ...    fieldn    [ WITH    ROLLUP ] ]    [ HAVING    where_contition ]
        7、连接查询
            SELECT    filed1,    field2,    ...    fieldn    FROM    tablename1,    tablename2    WHERE    tablename.fieldx    =    tablename2.field
        8、嵌套查询
            SELECT    *    FROM    tablename1    WHERE    fieldx    = | IN    ( SELECT    fieldx    FROM    tablename2 )
        9、联合查询
            SELECT    *    FROM    tablename1    UNION    [ALL]    SELECT    *    FROM    tablename2    ...    UNION    [ALL]    SELECT    *    FROM    tablenamen

DCL语句

DCL（Data Control Language）语句主要是DBA用来管理系统中的的对象权限时使用，一般开发人员很少使用。

    1、创建用户
    2、授权
        GRANT    permission1,    permission2,    ...    permissionn    [ ON    DATABASE | TABLE    name ]    TO    user1,    user2,    ...    usern    [ WITH    GRANT    OPTION ]
    3、收回权限
        REVOKE    permission1,    permission2,    ...    permissionn    [ ON    DATABASE | TABLE    name ]    FROM    user1,    user2,    ...    usern