MYSQL数据库中DDL、DML、DQL、DCL详解

DDL：数据定义语言，用来定义数据库对象：库、表、列(字段)等，
常用的语句关键字有创建(create)、删除(drop)、修改(alter)等等

一、库操作

1、查询库：
   #语法：SHOW DATABASES;(查看所有数据库)   SELECT DATABASE();(查看当前正在使用的库)
2、使用库：
   #语法：USE <库名>;  
   /*eg：USE mydb1; */    
3、创建库：  
   #语法：CREATE DATABASE [IF NOT EXISTS] <库名> [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
          [IF NOT EXISTS]：是判断数据库是否存在，当不存在时创建（防止重复创建）
          [DEFAULT CHARSET]：默认的字符集，如果没有设置，默认是utf8；推荐使用utf8mb4；
          [COLLATE]：排序规则，如果没有设置，默认是：uft8_general_ci；推荐使用utf8mb4_bin；
   /*eg: CREATE DATABASE IF NOT EXISTS mydb1 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_bin; */
4、删除库：
   #语法：DROP DATABASE [IF EXISTS] <库名>;
          [IF EXISTS]：当数据库存在时删除数据库（防止出错）
   /*eg：DROP DATABASE mydb1; */

二、表操作

1、查询表：
   #语法：SHOW TABLES;(查询库中所有表) DESC <表名>;(查看表结构) SHOW CREATE TABLE <表名>;(查询创表语句)
   /*eg：DESC student;   SHOW CREAT TABLE student; */
2、创建表：
   #语法：CREATE TABLE <表名>(<列名> <列类型> [COMMENT 列备注],<列名> <列类型>,...);
   /*eg：CREATE TABLE student (
           id varchar(50) NOT NULL COMMENT '学生id',
           name varchar(50) DEFAULT NULL COMMENT '学生姓名',
           birth date DEFAULT NULL COMMENT '生日',
           sex varchar(50) DEFAULT NULL COMMENT '性别',
           PRIMARY KEY (`id`)
         ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '学生表' */
3、修改表：
   可以使用[ALTER TABLE]语句来改变原有表的结构，例如增加或删除列、更改原有列类型、重新命名列或表等
   --#语法：修改表名  ALTER TABLE <旧表名> RENAME [TO] <新表名>; 
   /*eg：ALTER TABLE student RENAME TO new_student; */
   --#语法：修改表字符集/校对规则  ALTER TABLE <表名> [DEFAULT] CHARACTER SET <字符集> [DEFAULT] COLLATE <校对规则名>; 
   /*eg：ALTER TABLE student CHARACTER SET gb2312 DEFAULT COLLATE gb2312_chinese_ci; */
   --#语法：为表添加字段  ALTER TABLE <表名> ADD [COLUMN] <列名> <列类型>[约束条件] [FIRST/AFTER <指定列>];
          [COLUMN] 可以使用可以不使用
          [FIRST/AFTER <指定列>] 不使用时默认添加在了末尾，使用FIRST时添加在了开头，使用AFTER <指定列>时添加在了指定列后面
   /*eg：ALTER TABLE student ADD COLUMN age int(11) COMMENT '年龄'; */(默认直接添加在了末尾)
   /*eg：ALTER TABLE student ADD COLUMN age int(11) COMMENT '年龄' FIRST; */(添加在了开头)    
   /*eg：ALTER TABLE student ADD COLUMN age int(11) COMMENT '年龄' AFTER birth; */(添加在了birth字段之后)
   --#语法：修改字段名称  ALTER TABLE <表名> CHANGE [COLUMN] <旧列名> <新列名> <新列数据类型> [约束条件] [AFTER <指定列>]; 
          [COLUMN] 可以使用可以不使用
          [AFTER <指定列>] 想要调整字段顺序时可以使用
   /*eg：ALTER TABLE student CHANGE birth birthday date AFTER sex; */(修改字段名称并调整了字段顺序)
   --#语法：修改字段数据类型  ALTER TABLE <表名> MODIFY [COLUMN] <列名> <新列数据类型>;
   /*eg：ALTER TABLE student MODIFY name varchar(30); */(修改name字段的数据类型由varchar(50)变为varchar(30))
   --#语法：修改字段默认值  ALTER TABLE <表名> ALTER [COLUMN] <列名> DROP DEFAULT/SET DEFAULT <默认值>;
          [COLUMN] 可以使用可以不使用
          DROP DEFAULT 使用时为删除默认值
          SET DEFAULT <默认值> 使用时为修改默认值
   /*eg：ALTER TABLE student ALTER COLUMN sex DROP DEFAULT; */(删除默认值)
   /*eg：ALTER TABLE student ALTER COLUMN sex SET DEFAULT '男'; */(修改性别默认值为男)
   --#语法：删除字段  ALTER TABLE <表名> DROP [COLUMN] <列名>; 
   /*eg：ALTER TABLE student DROP age; */
4、删除表：
   #语法：DROP TABLE [IF EXISTS] <表名1>[,表名2,表名3...];
          [IF EXISTS]：当表存在时删除表（防止出错）
          [,表名2,表名3...] DROP TABLE 一次可以删除多个表
   /*eg：DROP TABLE student; */

修改表：ALTER TABLE 表名 {修改选项};
增加列	ADD COLUMN <列名> <类型>
修改列名或类型	CHANGE [COLUMN] <旧列名> <新列名> <新列类型>
修改/删除 列的默认值	ALTER [COLUMN] <列名> { SET DEFAULT <默认值> | DROP DEFAULT }
修改列类型	MODIFY [COLUMN] <列名> <类型>
删除列	DROP [COLUMN] <列名>
修改表名	RENAME TO <新表名>
修改字符集	CHARACTER SET <字符集名>

三、视图操作

1、创建视图：
    #语句：CREATE VIEW <视图名> AS <SELECT语句>;
       <SELECT语句>：指定创建视图的 SELECT 语句，可用于查询多个基础表或源视图。
    /*eg：CREATE VIEW student_view AS SELECT * FROM student; */
2、查看视图：
    #语句：DESCRIBE/DESC <视图名>;(查看视图字段列表) 
    #语句：SHOW CREATE VIEW <视图名> [\G];(查看视图创建语句，以\G结尾，这样能使显示结果格式化)
    /*eg：DESC student_view;  SHOW CREATE VIEW student_view \G */
3、修改视图：
    #语句：ALTER VIEW <视图名> AS <SELECT语句>;
    /*eg：ALTER VIEW student_view AS SELECT id,name,sex FROM student; */
4、删除视图：
    #语句：DROP VIEW <视图名>;
    /*eg：DROP VIEW student_view; */

DML：数据操作语言，用来对数据库表中的数据进行增删改
常用的语句关键字有插入(insert)、修改(update)、删除(datete)

1、插入数据：
    第一种：直接插入一行或多行数据，主键列数据不能重复不能为空，一行的数据先后顺序要和表中字段数据一一对应
    #语法：INSERT INTO <表名> VALUES (值1,值2,值3...),(值1,值2,值3...);
    /*eg：INSERT INTO student VALUES 
            ('1001' , '' , '2000-01-01' , '男'),
            ('1002' , null , '2000-12-21' , '男'),
            ('1003' , NULL , '2000-05-20' , '男'),
            ('1004' , '张三' , '2000-08-06' , '男'),
            ('1005' , '李四' , '2001-12-01' , '女'),
            ('1006' , '张三' , '2001-12-02' , '女'); */
            //如果数据类型是时间的话，想要插入当前时间可以使用now()函数进行插入
    第二种：指定字段，然后进行插入，指定的字段顺序可以与表中的字段先后顺序不同，但插入值的数据要与
            指定的字段先后顺序一一对应。
    #语法：INSERT INTO <表名>(field1,field2,field3) values (值1,值2,值3),(值1,值2,值3)...;
    /*eg：INSERT INTO student(id,name,sex) VALUES 
            ('1007' , '王五' , '男'),
            ('1008' , '赵六' , '女'),
            ('1009' , '刘七' , '女'); */
    第三种：向表中插入其他表的数据
    #语法：INSERT INTO <表名1>(field1,field2,field3) SELECT field1,field2,field3 FROM <表名2> [WHERE...];
    /*eg：INSERT INTO student(id,name,sex) VALUES SELECT id,name,sex FROM user WHERE sex ='男';*/
2、修改数据：
    #语法：UPDATE <表名> SET <字段1> = <值1>[,<字段2>=<值2>...] [WHERE 子句] [OEDER BY 子句] [LIMIT 子句];
        [WHERE 子句]：可选项。用于限定表中要修改的行。若不指定，则修改表中所有的行。
        [ORDER BY 子句]：可选项。用于限定表中的行被修改的次序。
        [LIMIT 子句]：可选项。用于限定被修改的行数。
    /*eg：UPDATE student name='张珊',sex='女' WHERE id ='1001';*/
3、删除数据：
    #语法：DELETE FROM <表名> [WHERE 子句] [OEDER BY 子句] [LIMIT 子句];
        [WHERE 子句]：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行。
        [ORDER BY 子句]：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除。
        [LIMIT 子句]：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值。
    /*eg：DELETE FROM student WHERE id ='1009';*/

drop、delete、truncate 关键字区别
名称	操作对象	作用
drop(DDL)	数据库、表、字段、字段的默认值	删除数据库（DROP DATABASE 库名） 删除表（DROP TABLE 表名），将表完全删除，包括表结构 删除表中某个字段（ALTER TABLE 表名 DROP [COLUMN] 字段名） 删除某个字段的默认值（ALTER TABLE 表名 ALTER [COLUMN] 列名 DROP DEFAULT）
delete(DML)	表中数据	删除表数据（DELETE FROM 表名 [where...]），保留表的结构，而且可以加where只删除一行或者多行，不会将自增字段的值进行重置，它是一行一行删除数据的，支持事务回滚，返回删除数据的行数。
truncate(DDL)	表中数据	删除表数据（TRUNCATE TABLE 表名），保留表的结构，不能加where，会将自增字段的值进行重置，当需要删除表中所有数据时，truncate速度比delete快，它实际上是删除了原来的表，然后新建了一个一模一样的表，所以不支持事务回滚，且只会返回0。

DQL：数据查询语言，用来查询数据库中表的记录
常用的语句关键字有 select、distinct、form、where、group by、having、order by...

#语句：SELECT [DISTINCT] <字段名1>,<字段名2>... FROM 表名 [WHERE 条件子句] [GROUP BY 分组子句] [HAVING 分组后条件子句] [ORDER BY 排序子句] [LIMIT 子句]
        [DISTINCT] 关键字的主要作用就是对数据表中一个或多个字段重复的数据进行过滤，只返回其中的一条数据给用户，它必须在所有字段的最前面
        [WHERE] 和 [HAVING] 关键字也存在以下几点差异：
            一般情况下，WHERE 用于过滤数据行，而 HAVING 用于过滤分组。
            WHERE 查询条件中不可以使用聚合函数，而 HAVING 查询条件中可以使用聚合函数。
            WHERE 在数据分组前进行过滤，而 HAVING 在数据分组后进行过滤 。
            WHERE 针对数据库文件进行过滤，而 HAVING 针对查询结果进行过滤。也就是说，WHERE 根据数据表中的字段直接进行过滤，而 HAVING 是根据前面已经查询出的字段进行过滤。
            WHERE 查询条件中不可以使用字段别名，而 HAVING 查询条件中可以使用字段别名。
执行顺序为：
    FROM--->WHERE--->GROUP BY--->HAVING--->SELECT--->ORDER BY--->LIMIT
MySQL常见的聚合函数有以下几个：
    1、count(col): 表示求指定列的总行数
    2、max(col): 表示求指定列的最大值
    3、min(col): 表示求指定列的最小值
    4、sum(col): 表示求指定列的和
    5、avg(col): 表示求指定列的平均值
其中 忽略值为NULL的行 的函数是：AVG(), MAX(), MIN(), SUM()
对于COUNT()而言：
    如果使用 COUNT(*) 或者 COUNT(1) 对表中行的数目进行计数，则不忽略NULL；
    如果使用 COUNT(特定列) 时具有值的行进行计数，则忽略NULL

mysql中常见的聚合函数

DCL：数据控制语言，用来创建数据库用户，控制数据库的访问权限

一、管理用户
 --查询用户
    #语法：select * FROM user;
 --创建用户
    #语法：create user '用户名'@'主机名' identified by '密码';
 --修改用户密码
    #语法：alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
 --删除用户
    #语法：drop user '用户名'@'主机名';
    
二、权限控制
 --查询权限
    #语法：show grants for '用户名'@'主机名';
 --授予权限
    #语法：grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
    /*eg：grant usage on *.* to 'nkw'@'%';
    (授权用户nkw对该机器下的所有数据库下的所有表有登录权限，只允许登录-其他什么也不允许做) */
 --撤销权限
    #语法：revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
    /*eg：revoke update,insert on user.* from 'nkw'@'%';
    (回收用户nkw对数据库user的update和insert权限) */

深入了解mysql的权限有哪些：mysql中的权限有哪些 - MySQL数据库 - 亿速云

对mysql从入门到精通：数据库入门