SQL语句大全(DDL+DML+DQL+DCL)

一.DDL(数据定义语言：用于定义和管理 SQL 数据库中的所有对象)

1.DDL_数据库

(1)查看所有数据库
    show databases;
(2)选择、切换数据库
    use 数据库名;
(3)创建数据库
    create database (if not exists) 数据库名 (charset=utf8);
(4)删除数据库
    drop dabatase (if exists) 数据库名;
(5)修改数据库编码
    alter database 数据库名 character set utf8;

2.DDL_表

(1)创建表
    create table (if not exists) 表名(
        列名 类型 UNSIGNED AUTO_INCREMENT,  //定义列为自增的属性，一般用于主键，数值会自动加1
        ...  ...
        列名 类型 not null unique,          //为该列添加非空和唯一约束
        PRIMARY KEY (主键1,主键2,...)       //可以使用多列来定义主键，列间以逗号分隔
        constraint 外键名称 foreign key(外键列) references 其他表(主键列)  //设置外键
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;   //ENGINE 设置存储引擎，CHARSET 设置编码

(2)查看所有表
    show tables;

(3)查看指定表的创建语句
    show create table 表名;

(4)查看表结构
    desc 表名;

(5)删除表
    drop table 表名;

(5)修改表 alter table 表名 ~
    (5.1)添加列
        alter table 表名 add(   
            列名 类型,
            列名 类型，
            ...  ... 
        );
    (5.2)修改列类型，可能会影响到已经存在的数据
        alter table 表名 modify 列名 新类型;
    (5.3)修改列名
        alter table 表名 change 原列名 新列名 新类型;
    (5.4)删除列
        alter table 表名 drop 列名;
    (5.5)修改表名称
        alter table 原表名 rename to 新表名;
    (5.6)修改表时指定主键
        alter table 表名 add primary key(主键列);
    (5.7)删除主键
        alter table 表名 drop primary key;
    (5.8)修改表时添加外键
        alter table 表名 add constraint 外键名称 foreign key(外键列) references 其他表(主键            列)
    

二.DML(数据操作语言：对表记录的操作（增删改）)

(1)插入数据
    INSERT INTO 表名称
    ( field1, field2,...fieldN )
    VALUES
    ( value1, value2,...valueN )
    ;
【注】
    a.如果没有给出要插入的列，那么表示插入所有列
    a.值的个数必须等于列的个数，并且顺序必须相同

(2)修改数据
    UPDATE table_name SET field1=new-value1, field2=new-value2, ... ...
    (where 条件)
    ;
【注】
    如果不写where语句，默认修改所有记录

(3)删除数据
    delete from 表名 (where 条件);
【注】
    如果不写where语句，默认删除所有记录
    Truncate table 表名 与 delete 不同，它是先drop表，再create表，而且无法回滚。

三.DCL(数据控制语言：是一种可对数据访问权进行控制的指令)

(1)创建用户
    //用户只能在指定的ip地址上登录
    create user 用户名@IP地址 identified by 密码;
    //用户可在任意的ip地址上登录
    create user 用户名@'%' identified by 密码;

(2)给用户授权(用户 + 权限 + 数据库)
    //给用户分派再指定数据库上指定的权限
    grant 权限1,...权限n on 数据库.* to 用户名@IP地址;
    //给用户分派再指定数据库上的所有权限
    grant all on 数据库.* to 用户名@IP地址;
【例】
    grant create,alter,drop,insert,update,delete,select on test.* to user@localhost;

(3)撤销授权(用户 + 权限 + 数据库)
    //撤销指定用户在指定数据库上的指定权限
    revoke 权限1,...权限n on 数据库.* from 用户名@IP地址;
【例】
    revoke create,alter,drop on test.* from user@localhost;

(4)查看权限
    show grants for 用户名@IP地址;

(5)删除用户
    drop user 用户名@IP地址;

四.DQL(数据查询语言：对表中的数据进行查询)

1.DQL_单表查询

(1)标准的select语句
    SELECT 列名,...,列名
    [ INTO new_table ]  
    FROM 表名
    WHERE 条件

(2)排序 order by
    order by 列名 ASC|DESC;
    order by 列名1 ASC|DESC, 列名2 ASC|DESC， ... 列名n ASC|DESC;

(3)分组 group by... having ... ：HAVING子句可以让我们筛选分组后的各组数据。
    SELECT 列名,聚合函数(列名)
    FROM table_name
    GROUP BY 列名 
    having 聚合函数(列名) 条件 ;

(4)聚合函数
    select count(*)|count(列名) FROM 表名     //求不为null的记录数或者字段数
    select max(列名) FROM 表名                //求某列最大值
    select min(列名) FROM 表名                //求某列最小值
    select avg(列名) FROM 表名                //求某列平均值
    select sum(列名) FROM 表名                //求某列总和
    select first(列名) FROM 表名              //查询指定列中第一个记录的值。
    select last(列名) FROM 表名              //查询指定列中最后一个记录的值

(5)模糊查询like
   【通配符】
        _    :    匹配单个字符
        %    :    匹配任意字符
   【例如】
        SELECT * FROM Websites WHERE url LIKE 'https%';

(6)limit 关键字,常用来做分页处理
    limit关键字用来限定查询时的起始行，以及查询的总行数:limit n,m; 
    n:查询起始下标，从0开始
    m:查询的数量
    【例如】
        一页显示 十 条记录，显示分页后第 三 页的内容：
            select * 
            from 表名
            limit  20,10

    [公式]：limit (当前页-1)*每页记录数,每页记录数

(7)查询null值
    IS NULL: 当列的值是 NULL,此运算符返回 true。
    IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。
    <=>: 比较操作符（不同于 = 运算符），当比较的的两个值相等或者都为 NULL 时返回 true。

(8)IN 操作符
    SELECT 列名,...,列名
    FROM 表名
    WHERE 列名 IN (value1,value2,...valueN);

(9)BETWEEN ... AND ... :选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。
    SELECT 列名,...,列名
    FROM 表名
    WHERE 列名 BETWEEN value1 AND value2;

(10)别名 as
    1.列别名：SELECT 列名 AS 列别名
    2.表别名：FROM 表名 AS 表别名

(11)SELECT INTO 语句从一个表复制数据，然后把数据插入到另一个新表中
    SELECT 列名,...,列名
    INTO 新表
    FROM 旧表;
【注】
    Mysql不支持SELECT ... INTO 语句，但支持 INSERT INTO ... SELECT 。
    mysql> INSERT INTO 新表(runoob_id,runoob_title,runoob_author,submission_date)
        -> SELECT runoob_id,runoob_title,runoob_author,submission_date
        -> FROM 旧表;

(12)AND & OR 运算符用于基于一个以上的条件对记录进行过滤
    AND: 如果第一个条件和第二个条件都成立，则 AND 运算符显示一条记录。
    OR:  如果第一个条件和第二个条件中只要有一个成立，则 OR 运算符显示一条记录。

(13)SELECT DISTINCT 一个列可能会包含多个重复值，仅仅列出不同（distinct）的值。
    SELECT DISTINCT 列名
    FROM 表名;

2.DQL_多表查询

//多表查询分类：
(1)合并结果集
(2)连接查询
(3)子查询

(1)合并结果集

要求被合并的表结构，列的类型和数目必须相同，有两种合并方式：

Union：合并，去除重复行

Union All：合并，不去除重复行

// 哪个结果集的表在上面 合并后就显示的哪个表的 表头
select * from 表A
UNION ALL
select * from 表B

(2)连接查询

//1、连接
select *
from a,b 
where a.属性 ? b.属性
[注] a和b表先做笛卡尔积，然后根据比较符"？"进行筛选记录

//2、等值连接
select *
from a,b 
where a.属性 = b.属性
[注] 当 连接 中的比较符为 "="时，此时的连接成为 等值连接

//3、自然连接
select *
from a NATURAL JOIN b
[注] 同等值连接相比，自然连接要求 双方表中必须有 同名属性(这也是为什么相比于等值连接不需要"on"语句)，并且 最后会 只保留 一个同名属性列。

//4、内连接
select *
from a (INNER可省) JOIN b 
on a.属性 = b.属性
[注] 与等值连接很相似，只需把","改为“inner join”，把"where" 改为 "on"

//5、外连接
    //5.1、左外连接：结果表中除了包括满足连接条件的行外，还包括左表的所有行，没有字段的用NULL填充。
    select *
    from a LEFT OUTER JOIN b 
    on a.属性 = b.属性
    
    //5.2、右外连接：结果表中除了包括满足连接条件的行外，还包括右表的所有行，没有字段的用NULL填充。
    select *
    from a RIGHT OUTER JOIN b 
    on a.属性 = b.属性

    //5.3、全外连接（MySQL不支持，但可以通过合并结果集(5.1 UNION 5.2)实现该功能）：结果表中除了包括满足连接条件的行外，还包括两个表的所有行，没有字段的用NULL填充。
    select *
    from a FULL OUTER JOIN b 
    on a.属性 = b.属性

【注】

等值连接和自然连接的区别：

A、等值连接中不要求相等属性值的属性名相同，而自然连接要求相等属性值的属性名必须相同，即两关系只有在同名属性才能进行自然连接。
B、等值连接不将重复属性去掉，而自然连接去掉重复属性。

(3)子查询(查询中有查询，多个SELECT)

1、子查询出现的位置
a、  where关键字后作为条件存在
b、  from 后当作表存在(即从结果集中查)

2、常见子查询格式
    //a
    select * 
    from 表A 
    where A.属性 [=,>,<,<=,>=,!=,<>,in,all,any] (select B.属性 from 表B where 条件)

    //b
    select *
    from 表A，(select ~)
    where 条件