MySQL中的SQL详解（DDL、DML、DQL、DCL）

目录

一、什么是SQL语言？

1、DDL（数据定义语言）

1.新增操作（create、add）

1.1.新增库表（create） 

1.2.新增字段（add）

2.删除库、表、字段（drop）

3.修改（modify）

3.1.修改字段名（modify）

3.2.修改数据类型（数据类型）

3.3.修改表名

4.查看（show、desc）

2、DML（数据操作语言）

2.1.INSERT（新增表数据）

2.2.DELETE（删除表数据）

2.3.UPDATE（更改表数据）

2.4.SELECT（查找表数据，数据查询语言） 

3、DCL（数据控制语言）

3.1、用户管理操作

3.1.1.创建用户

3.1.2.修改用户密码

3.1.3.删除用户

3.2、权限控制操作

3.2.1.查看当前权限

3.2.2.授予权限

3.2.3.权限传递

3.2.4.撤销权限

---

一、什么是SQL语言？

SQL(Stuctured Query Language)是 1974年由 Boyce 和 Chamberlin 提出的，是关系数据库中最普遍使用的语言，是一种通用的、功能强大的关系数据库的标准语言。SQL 包括数据査询(query)、数据操纵(manipulation)、数据定义(definition)和数据控制(control)功能。一个SQL数据库是表的汇集，它用一个或多个 SQL 模式定义。基本表是实际存储在数据库中的表，而视图是由若干个基本表或其他视图导出的表。视图与基本表不同，是一个虚表。作为SOL的用户可以是应用程序，也可以是终端用户。

SQL语言简洁、易学易用。

SQL语言功能极强，完成核心功能只用了9个动词，包括如下4类:

数据查询:SELECT，该动词是 SQL中用得最多的动词。

数据定义:CREATE、DROP、ALTER，用于创建新表、修改表和除表

数据操纵:INSERT、UPDATE、DELETE，用于数据的插入、修改和删除

数据控制:GRANT、REVORK，用于数据库对象访问的权限授予和收回。

1、DDL（数据定义语言）

数据定义语言(Data Definition Language，DDL)用来定义数据库模式。DDL 包括数据库模式定义、数据库存储结构和存取方法定义，数据库模式的修改和删除功能。数据定义子语言的处理程序分为数据库模式定义处理程序，数据库存储结构和存取方法定义处理程序。数据库模式定义处理程序接收用 DDL 表示的数据模式定义，把其转变为内部表示形式，存储到数据字典中。数据库存储结构和存取方法定义处理程序接收数据库系统存储结构和存取方法定义,在存储设备上创建相关的数据库文件，建立物理数据库。简而言之DDL（数据库定义语言）：其主要操作是针对我们MySQL中的表结构，表结构的增删查改，表结构的查询。

1.新增操作（create、add）

1.1.新增库表（create） 

DDL中alter包含的关键词增（create、add）、删（drop）、改（modify、change）、查（show，desc）牢记：数据库结构的操作语言中，涉及到修改表结构操作得都必须是alter table起手 

语句格式:ALTER TABLE<表名>[ADD<新列名><数据类型>[完整性约束条件]] 

                                [DROP<完整性约束名>]

                                [MODIFY<列名><数据类型>];

创建一个测试库-->语法：create database 库名；

创建一个测试表-->语法：CREATE TABLE<表名>(<列名><数据类型>[列级完整性约束条件]

                                                [,<列名><数据类型习[列级完整性约束条件1…
                                                [,<表级完整性约束条件>]);
列级完整性约束条件有 NULL(空)和 UNIOUE(取值唯一),如 NOT NULL UNIQUE 表示取值唯一，不能取空值。

show databases;

use test;

show tables;

create database self_learn;

show databases;

查看所有的库

很多时候，我们想知道当前所在的数据库在哪儿？可以使用select database();一般带括号的东西都是函数。

选择我们的创建库之后，我们的就可以开始建表了

use self_learn;

create table test1(
    id int comment '学院编号',
    student_id char(3) comment '学号',
    student_name varchar(10) comment '学生姓名'
);

show tables;

1.2.新增字段（add）

        语法：alter talbe 表名 add 列名 数据类型 [first | after 旧字段名]；其中中括号里边的项为可选项，也就是可以指定我们的字段的所放在表的位置，默认是追加放置。

        比如，当我们发现我们应该给每个学生增加一个记录学生年龄的字段，这时就可以使用下边的语法进行添加

执行： 

alter table test1 add age tinyint unsigned;

指定位置添加字段：比如，此刻想添加一个说明学生专业的字段在姓名字段的后边，

执行：

alter table test1 add major varchar(20) after student_name;

增加到此为止！

2.删除库、表、字段（drop）

        删除库：drop database 库名；

        删除表：drop table 表名；

        删除表字段：牢记alter table起手，语法：alter table 表名 drop column 列名；注意：DDL中有两个语法需要添加column关键字，这是其一，还有一个在modify中出现

        比如，我们觉得，说明学院的编号该字段没必要，执行：alter table test1 drop column id;

3.修改（modify）

        修改：修改稍稍较难，但是牢记两个关键字modify（该数据类型）、change（该字段名称）

3.1.修改字段名（modify）

        语法：alter table 表名 modify 原字段名称 新数据类型；

                   alter table 表名 change 旧字段名 新字段名 原数据类型；

        注意：这个字段原数据类型必须跟上。

        比如，我们觉得单纯一个age并不能给我们提供很多一眼就知道的信息，我们想把它做修改

执行：alter table test1 change age student_age tinyint unsigned; 

3.2.修改数据类型（数据类型）

比如在这里把专业名称-->major字段的类型给定为test（文本）类型

执行：

alter table test1 modify student_name text; 

3.3.修改表名

        语法：alter table 旧表名 rename to 新表名； 

执行：alter table test1 rename to bigdata_class02_student;

4.查看（show、desc）

        desc 表名；查看表结构，以列表的形式进行展示。

        show create table 表名；详细查看建表语句。

        show tables；查看当前数据库下的所有表。

        show databases；查看当前所有的数据库。

实例：

使用desc test1查看我们的建表结构：

使用show create table test1查看建表的语句（包含默认的引擎选择）:

 

2、DML（数据操作语言）

数据操纵语言(Data ManipulationLanguage，DML)用来表示用户对数据库的操作请求。一般地，数据操纵语言 DML 能表示如下的数据库操作:

1. INSERT：向数据库插入新的信息。
2. DELETE：从数据库删除信息。
3. UPDATE：修改数据库中的信息
4. SELECT：查询数据库中的信息。

2.1.INSERT（新增表数据）

-- 插入单行（指定列）
INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...);

-- 插入多行
INSERT INTO 表名 (列1, 列2, ...)
VALUES (值1, 值2, ...),
       (值3, 值4, ...);

-- 从其他表插入数据
INSERT INTO 目标表 (列1, 列2, ...)
SELECT 列A, 列B, ...
FROM 源表
WHERE 条件;

2.2.DELETE（删除表数据）

DELETE FROM 表名 WHERE 条件;  -- 无 WHERE 会删除全表数据！

2.3.UPDATE（更改表数据）

UPDATE 表名 SET 列1 = 新值1, 列2 = 新值2, ... WHERE 条件;  -- 无 WHERE 会更新全表！

2.4.SELECT（查找表数据，数据查询语言） 

数据库查询是数据库的核心操作，SOL语言提供了 SELECT语句进行数据库的查询。

语句格式:SELECT [ALL | DISTINCT] <目标列表达式> [,<目标列表达式>]…

                                FROM <表名或视图名> [,<表名或视图名>]

                                [WHERE <条件表达式>]

                                [GROUP BY <列名 1>]

                                [HAVING <条件表达式>]]

                                [ORDER BY <列名 2> [ASC | DESC]…]

        SOL, 查询中的子句顺序为 SELECT、FROM、WHERE、GROUP BY、HAVING 和 ORDER BY。其中，SELECT、FROM 是必须的，HAVING条件子句只能与 GROUP BY 搭配使用。        

        (1)SELECT 子句对应的是关系代数中的投影运算，用来列出查询结果中的属性。其输出可以是列名、表达式、集函数(AVG、COUNT、MAX、MIN、SUM)，DISTINCT 选项可以保证查询的结果集中不存在重复元组。

        (2)FROM 子句对应的是关系代数中的笛卡儿积，它列出的是表达式求值过程中需扫描的关系，即在 FROM 子句中出现多个基本表或视图时，系统首先执行笛卡儿积操作。

        (3)WHERE 子句对应的是关系代数中的选择谓词。WHERE 子句的条件表达式中可以使用的运算符如下所示。

运 算 符		含 义	运 算 符		含 义
集合成员运算符	IN NOT IN	在集合中 不在集合中	算术运算符	< ≥ > ≤ = ≠	大于 大于等于 小于 小于等于 等于 不等于
字符串匹配 运算符	LIKE	与_%进行 单个、多个 字符匹配
空值比较运算符	IS NULL IS NOT NULL	为空 不能为空	逻辑运算符	AND OR NOT	与 或 非

3、DCL（数据控制语言）

数据控制语言（DCL）是SQL中用于管理数据库用户权限和安全的核心组件，通过控制用户对数据库对象的访问权限保障数据安全。其核心操作包括用户管理、权限授予与撤销

DCL主要用于：

1.  用户管理：创建、修改、删除用户账户。
2.  权限控制：授予或撤销用户对数据库对象的操作权限（如查询、修改等）

3.1、用户管理操作

3.1.1.创建用户

语法：CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';

主机名：localhost（仅本地访问）或%（允许远程访问）。

示例：创建一个登录用户，并允许login_user可以从如何主机进行登录

create user 'login_user'@'%' identified by 'Passwd123';

测试：由于我们只对账户进行新增，并没有对账户进行授权，所以不能看到数据库的有效信息。

3.1.2.修改用户密码

语法：ALTER USER '用户名'@'主机名' IDENTIFIED BY '新密码';

示例：更新login_user的登录密码。

ALTER USER 'login_user'@'%' IDENTIFIED BY 'NewPasswd123';

3.1.3.删除用户

语法：DROP USER '用户名'@'主机名';

示例：DROP USER 'john'@'%';  -- 删除john用户。

3.2、权限控制操作

3.2.1.查看当前权限

语法：SHOW GRANTS FOR '用户'@'主机';

语法：SHOW GRANTS;  -- 默认查看当前登录用户的权限

示例：

SHOW GRANTS FOR 'login_user'@'%';  -- 显示login_user的权限列表。

SHOW GRANTS;  -- 默认查看当前登录用户的权限

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION

3.2.2.授予权限

语法：GRANT 权限列表 ON 数据库.对象 TO '用户'@'主机';

权限类型：SELECT（查询）、INSERT（插入）、ALL PRIVILEGES（所有权限）等。

作用范围：*.*（全局）、mydb.*（某数据库所有表）、mydb.orders（某表）。

示例：

-- 允许login_user具有查询表、新增数据库，表的权利。

GRANT SELECT, CREATE, SHOW DATABASES ON grant_test.* TO 'login_user'@'%';

提示错误：ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES

错误的原因是因为，赋予的权限中存在全局权限，而全局权限不可以指定在某一个库上，所以，把创建用户语句更改为如下即可：

GRANT SELECT, create, SHOW DATABASES ON *.* TO 'login_user'@'%';

3.2.3.权限传递

GRANT 'read_only' TO 'login_user'@'%';

权限传递（WITH GRANT OPTION）

GRANT SELECT ON grant_test.* TO 'login_user'@'%' WITH GRANT OPTION;  -- 允许login_user将此权限授予他人。

操作同上。

3.2.4.撤销权限

语法：REVOKE 权限列表 ON 数据库.对象 FROM '用户'@'主机';

示例：REVOKE INSERT ON grant_test.orders FROM 'login_user'@'%';  -- 撤销login_user的订单表插入权限。

操作同上。