SQL基础教程：从DQL到DCL的指南-CSDN博客

本文链接：https://blog.csdn.net/m0_67624967/article/details/136478759

SQL语言主要可以分为四个子集或类别，这些类别基于它们的主要功能来区分。

DQL（数据查询语言）

1. 基本查询

从单个或多个表中检索数据。

SELECT column1, column2 FROM table_name;

2. 条件查询

使用WHERE子句来过滤结果集。

SELECT column1, column2 FROM table_name WHERE condition;

3. 排序查询

使用ORDER BY子句对结果集进行排序。

SELECT column1, column2 FROM table_name ORDER BY column1 ASC;

4. 分组与聚合查询

使用GROUP BY子句将结果集按一个或多个列进行分组，通常与聚合函数（如SUM, AVG, COUNT等）结合使用。

SELECT column1, COUNT(column2) FROM table_name GROUP BY column1;

5. 连接查询

使用JOIN子句将多个表的数据组合在一起，包括INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN、UNION、UNION ALL等。

SELECT table1.column1, table2.column2 FROM table1 
INNER JOIN table2 ON table1.id = table2.table1_id;

6. 子查询

在查询中使用另一个查询作为条件或数据源。

SELECT column1 FROM table_name 
WHERE column2 IN (SELECT column2 FROM another_table);

7. 其他

DISTINCT：用于返回唯一不同的值，消除结果集中的重复行。

    SELECT DISTINCT email FROM customers_east;

DML（数据操纵语言）

包括INSERT（插入数据）、UPDATE（更新数据）、DELETE（删除数据）、CALL（调用）等语句。

1. INSERT

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

2. UPDATE

UPDATE table_name SET column1 = value1 WHERE condition;

3. DELETE

DELETE FROM table_name WHERE condition;

4. CALL

-- SimpleProcedure()是存储过程
CALL SimpleProcedure();

DDL（数据定义语言）

包括CREATE（创建对象）、ALTER（修改对象）、DROP（删除对象）、TRUNCATE（清空）等语句

1. CREATE

(1) 创建表：使用CREATE TABLE语句创建新表。

    CREATE TABLE table_name ( column1 datatype, column2 datatype, ... );

(2) 创建或更新视图：使用CREATE OR REPLACE语句创建或更新视图

CREATE OR REPLACE view_name AS 
SELECT column1, column2 FROM table_name WHERE condition;

(3) 创建索引：使用CREATE INDEX语句创建索引以提高查询性能。

    CREATE INDEX index_name ON table_name (column1, column2, ...);

(4) 创建存储过程：使用CREATE PROCEDURE语句创建存储过程。

CREATE PROCEDURE procedure_name (parameter1 datatype, parameter2 datatype, ...) 
BEGIN 
-- SQL statements 
END; 
-- 调用存储过程 -- CALL procedure_name(value1, value2, ...);

(5) 创建函数：使用CREATE FUNCTION语句创建函数。

CREATE FUNCTION function_name (parameter1 datatype, parameter2 datatype, ...) 
RETURNS return_datatype 
BEGIN 
-- SQL statements RETURN value; 
END; 
-- 调用函数 -- SELECT function_name(value1, value2, ...);

2. ALTER

ALTER 语句在 MySQL 中用于修改现有数据库对象，如表（tables）、索引（indexes）、触发器（triggers）、存储过程和函数等。

(1) 修改表结构：如添加、删除或修改列，添加或删除约束等。

    ALTER TABLE table_name ADD column_name datatype;

(2) 修改存储过程或函数：

    ALTER PROCEDURE procedure_name -- 或者 ALTER FUNCTION function_name -- 修改内容

3. DROP

-- 删除数据库
DROP DATABASE database_name;
-- 删除表
DROP TABLE table_name;
-- 删除索引
DROP INDEX index_name ON table_name;
-- 删除视图
DROP VIEW view_name;
-- 删除存储过程或函数
DROP PROCEDURE procedure_name;  
DROP FUNCTION function_name;
-- 删除触发器
DROP TRIGGER trigger_name;

4. TRUNCATE

清空表中的所有数据，但不删除表本身。

TRUNCATE TABLE table_name;

DCL（数据控制语言）

1. GRANT

赋予用户或角色特定的权限。

2. REVOKE

撤销之前授予用户或角色的权限。

-- 赋予用户对所有数据库的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'username'@'hostname'; 
-- 撤销用户对所有数据库的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'username'@'hostname';
-- 赋予用户对特定数据库的所有权限
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname'; 
-- 赋予用户对特定表的特定权限
GRANT SELECT, INSERT, UPDATE ON database_name.table_name TO 'username'@'hostname';
-- 赋予用户对特定列的特定权限
GRANT SELECT (column1, column2) ON database_name.table_name TO 'username'@'hostname';
-- 赋予用户创建和删除表的权限
GRANT CREATE, DROP ON database_name.* TO 'username'@'hostname';
-- 赋予权限并指定密码
GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'hostname' IDENTIFIED BY 'password';

其他命令

1. SHOW

检索关于数据库、表、索引、列、系统变量、状态信息、权限、存储过程和函数等信息。

-- 显示所有数据库
SHOW DATABASES;
-- 显示当前数据库的所有表
SHOW TABLES;
-- 显示表的创建信息
SHOW CREATE TABLE table_name;
-- 显示表的结构
SHOW COLUMNS FROM table_name;
-- 显示表的索引
SHOW INDEXES FROM table_name;
-- 显示字符集
SHOW CHARACTER SET;
-- 显示校对规则
SHOW COLLATION;
-- 显示用户权限
SHOW GRANTS FOR 'user_name'@'host_name'; -- 一个用户名为user_name的用户，他从IP地址host_name连接到MySQL服务器
-- 显示当前运行的进程
SHOW PROCESSLIST;
-- 显示错误
SHOW ERRORS; -- 显示最后一个错误
-- 显示存储过程和函数
SHOW PROCEDURE STATUS; -- 显示存储过程的状态  
SHOW FUNCTION STATUS; -- 显示函数的状态
-- 显示表的引擎
SHOW TABLE STATUS LIKE 'table_name'; -- 显示表的引擎和其他状态信息
-- 显示MySQL的版本信息
SHOW VERSION;
-- 显示MySQL的服务器状态
SHOW SERVER STATUS;
-- 显示当前使用的数据库
SHOW DATABASE;
-- 显示MySQL支持的存储引擎
SHOW ENGINES;

2. 事务管理

(1) 开始一个新的事务

    BEGIN TRANSACTION;

(2) 提交当前事务

    COMMIT; --显式提交

(3) 撤销当前事务中的更改

    ROLLBACK; -- 回滚使数据库状态回到上次最后提交的状态

(4) 设置事务的属性

SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE};
-- READ UNCOMMITTED：允许读取尚未提交的数据。这是最低的隔离级别，可能会导致“脏读”。
-- READ COMMITTED：只允许读取已提交的数据。大多数数据库系统的默认隔离级别。
-- REPEATABLE READ：在同一个事务中，多次读取同一行数据的结果是一致的。这是MySQL的默认隔离级别。
-- SERIALIZABLE：最高隔离级别，强制事务串行执行，避免了并发问题，但性能开销最大。

(5) 提交数据有三种类型

显式提交、隐式提交及自动提交。

显式提交

用COMMIT命令直接完成的提交为显式提交。其格式为：

    SQL>COMMIT；

隐式提交

用SQL命令间接完成的提交为隐式提交。这些命令是：

ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

自动提交

若把AUTOCOMMIT设置为ON，则在插入、修改、删除语句执行后，

系统将自动进行提交，这就是自动提交。其格式为：

    SET AUTOCOMMIT ON；

3. 变量和流程控制

在存储过程和函数中，可能会使用到变量和流程控制语句。

(1) 声明变量

    DECLARE variable_name datatype;

(2) 条件语句

IF condition THEN  
   -- code  
ELSE  
   -- code  
END IF;

(3) 循环语句

    WHILE condition DO  
       -- code  
    END WHILE;

(4) CASE语句

CASE  
   WHEN condition1 THEN result1  
   WHEN condition2 THEN result2  
      ...  
   ELSE result  
END CASE;

(5) WITH ... AS (...)：创建临时结果集

WITH RankedSales AS (  
  SELECT  
    product_id,  
    sale_date,  
    amount,  
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY sale_date DESC) AS rn  
  FROM  
    sales  
)