背景知识

SQL语句分类

  • 数据定义语言 (Data Definition Language, DDL)

这类语句用于定义数据库结构,包括创建、修改和删除数据库对象如表、索引、视图等。

  • 数据操纵语言 (Data Manipulation Language, DML)

这类语句用于添加、读取、更新和删除数据。

  • 数据查询语言 (Data Query Language, DQL)

这类语句主要用于检索数据,通常指的是 SELECT 语句。

  • 事务控制语言 (Transaction Control Language, TCL)

这类语句用于管理事务,确保数据的一致性和完整性。

  • 数据控制语言 (Data Control Language, DCL)

这类语句用于管理数据库用户的权限。

一、mysql 常见DDL语句

1. CREATE DATABASE

用于创建新的数据库。

CREATE DATABASE database_name;

2. USE

选择要使用的数据库。

USE database_name;

3. CREATE TABLE

用于创建新的表。

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

4. ALTER TABLE

用于修改现有的表结构。

添加列
ALTER TABLE table_name
ADD column_name datatype constraints;
修改列
ALTER TABLE table_name
MODIFY column_name datatype constraints;
删除列
ALTER TABLE table_name
DROP COLUMN column_name;
重命名列
ALTER TABLE table_name
CHANGE old_column_name new_column_name datatype constraints;
添加约束
ALTER TABLE table_name
ADD CONSTRAINT constraint_name constraint_definition;
删除约束
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

5. DROP TABLE

用于删除表。

DROP TABLE table_name;

6. TRUNCATE TABLE

用于清空表中的所有数据。

TRUNCATE TABLE table_name;

7. CREATE INDEX

用于创建索引。

CREATE INDEX index_name
ON table_name (column_name);

8. DROP INDEX

用于删除索引。

DROP INDEX index_name ON table_name;

9. CREATE VIEW

用于创建视图。

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

10. DROP VIEW

用于删除视图。

DROP VIEW view_name;

11. CREATE PROCEDURE

用于创建存储过程。

CREATE PROCEDURE procedure_name (IN param1 datatype, ...)
BEGIN
    -- SQL statements
END;

12. DROP PROCEDURE

用于删除存储过程。

DROP PROCEDURE procedure_name;

二、mysql 常见DML语句

1. INSERT

用于向表中插入新记录。

插入单行数据
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...);
插入多行数据
INSERT INTO table_name (column1, column2, ...)
VALUES (value1, value2, ...),
       (value1, value2, ...),
       ...;

2. SELECT

用于从表中检索数据。

选择所有列
SELECT * FROM table_name;
选择指定列
SELECT column1, column2, ... FROM table_name;
带条件的选择
SELECT column1, column2, ... FROM table_name
WHERE condition;

3. UPDATE

用于更新表中的现有记录。

更新单行或多行数据
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

4. DELETE

用于从表中删除记录。

删除单行或多行数据
DELETE FROM table_name
WHERE condition;

示例

假设我们有一个名为 employees 的表,包含以下列:id, first_name, last_name, email, hire_date

插入数据
INSERT INTO employees (first_name, last_name, email, hire_date)
VALUES ('John', 'Doe', 'john.doe@example.com', '2020-01-01'),
       ('Jane', 'Smith', 'jane.smith@example.com', '2020-02-01');
选择数据
SELECT * FROM employees;
更新数据
UPDATE employees
SET email = 'new.email@example.com'
WHERE id = 1;
删除数据
DELETE FROM employees
WHERE id = 2;

三、mysql 常见DQL语句

1. 基础查询

选择所有列:

SELECT * FROM table_name;

选择特定列:

SELECT column1, column2 FROM table_name;

2. 条件过滤

使用 WHERE 子句:

SELECT * FROM table_name WHERE condition;

其中 condition 是一个逻辑表达式,例如 column_name = 'value'

3. 排序结果

使用 ORDER BY 子句:

SELECT * FROM table_name ORDER BY column_name [ASC|DESC];

其中 [ASC|DESC] 指定排序方向,默认为 ASC(升序)。

4. 分组结果

使用 GROUP BY 子句:

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

aggregate_function 可以是 COUNT(), SUM(), AVG(), MIN(), MAX() 等。

5. 过滤分组结果

使用 HAVING 子句:

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

HAVING 子句与 GROUP BY 结合使用,用于过滤分组后的结果。

6. 连接表

使用 JOIN:

SELECT t1.column1, t2.column2
FROM table1 t1
INNER JOIN table2 t2 ON t1.common_column = t2.common_column;

这里使用了 INNER JOIN,还有 LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN 等其他类型的连接。

7. 子查询

作为 WHERE 子句的一部分:

SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2);

这里的子查询 (SELECT column2 FROM table2) 作为 WHERE 子句的一部分使用。

8. 使用 LIMIT 和 OFFSET

限制返回的行数:

SELECT * FROM table_name LIMIT number_of_rows;

如果需要跳过某些行再开始限制,可以使用 OFFSET:

SELECT * FROM table_name LIMIT offset, number_of_rows;

mysql常见TCL语句

1. BEGIN / START TRANSACTION

用于开始一个新的事务。

开始事务
START TRANSACTION;

或者

BEGIN;

2. COMMIT

用于提交当前事务中的所有更改。

提交事务
COMMIT;

3. ROLLBACK

用于撤销当前事务中的所有更改。

回滚事务
ROLLBACK;

4. SAVEPOINT

用于设置保存点,可以在之后回滚到这个保存点。

设置保存点
SAVEPOINT savepoint_name;
回滚到保存点
ROLLBACK TO SAVEPOINT savepoint_name;
释放保存点
RELEASE SAVEPOINT savepoint_name;

五. mysql常见DCL语句

1. GRANT

用于授予用户访问权限。

授予权限
GRANT [权限1], [权限2], ... ON database.table TO 'username'@'host';

以下是可用的权限: SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, INDEX, REFERENCES, TRIGGER, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER, PROCESS, RELOAD, SHUTDOWN, SUPER, CREATE USER, AUDIT SYSTEM EVENTS, REPLICATION CLIENT, REPLICATION SLAVE, REPLICATION SLAVE ADMIN, REPLICATION SLAVE CONNECTION_ADMIN, REPLICATION SLAVE RESTART_ADMIN, REPLICATION SLAVE SQL_ADMIN, REPLICATION SLAVE USER_ADMIN, REPLICATION SLAVE X_ADMIN, REPLICATION SLAVE X_USER_ADMIN, SSL, XADMIN, XUSER, LOCK TABLES, CREATE TEMPORARY TABLES, CREATE ROLE, DROP ROLE 等。

授予所有权限
GRANT ALL PRIVILEGES ON database.* TO 'username'@'host';

2. REVOKE

用于撤销已授予的权限。

撤销权限
REVOKE privilege1, privilege2, ... ON database.table FROM 'username'@'host';
撤销所有权限
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'username'@'host';

注意事项

  • GRANTREVOKE 语句中,用户名和主机名需要指定。例如 'new_user'@'localhost' 表示 new_user 用户只能从本地主机登录。
  • 如果需要授予全局权限(例如 CREATE USER, SHUTDOWN 等),可以省略 ON database.table 部分。
  • GRANT OPTION 特权允许被授予该特权的用户再次授予其他人相同的权限。

六、mysql数据类型

整型

  • TINYINT: 1 字节,范围 -1281270255(无符号)。
  • SMALLINT: 2 字节,范围 -3276832767065535(无符号)。
  • MEDIUMINT: 3 字节,范围 -83886088388607016777215(无符号)。
  • INT 或 INTEGER: 4 字节,范围 -2147483648214748364704294967295(无符号)。
  • BIGINT: 8 字节,范围 -92233720368547758089223372036854775807018446744073709551615(无符号)。

浮点型

  • FLOAT: 单精度浮点数,最多 7 位有效数字。
  • DOUBLE: 双精度浮点数,最多 15 位有效数字。
  • DECIMAL(M, D): 可变精度十进制数,M 是总位数,D 是小数点后的位数。

字符串类型

  • CHAR(M): 固定长度字符字符串,M 是字符的最大长度。
  • VARCHAR(M): 可变长度字符字符串,M 是字符的最大长度。
  • BINARY(M): 固定长度二进制字符串,M 是字节的最大长度。
  • VARBINARY(M): 可变长度二进制字符串,M 是字节的最大长度。
  • TEXT: 大文本字符串。
  • BLOB: 大二进制对象。
  • TINYTEXT: 较小的文本字符串。
  • TINYBLOB: 较小的二进制对象。
  • MEDIUMTEXT: 中等大小的文本字符串。
  • MEDIUMBLOB: 中等大小的二进制对象。
  • LONGTEXT: 非常大的文本字符串。
  • LONGBLOB: 非常大的二进制对象。
  • ENUM(‘value1’, ‘value2’, …): 枚举类型,只能从给定列表中选取一个值。
  • SET(‘value1’, ‘value2’, …): 集合类型,可以选取给定列表中的多个值。
日期/时间类型
  • DATE: 日期格式 YYYY-MM-DD
  • TIME: 时间格式 HH:MM:SS
  • YEAR: 年份格式 YYYYYY
  • DATETIME: 日期和时间格式 YYYY-MM-DD HH:MM:SS
  • TIMESTAMP: 日期和时间格式 YYYYMMDDHHMMSS,自动维护当前时间戳。
二进制/布尔类型
  • BIT(M): 二进制位字段,M 是位的最大长度。
  • BOOLEAN 或 BOOL: 布尔类型,可以是 TRUEFALSE

点赞(0) 打赏

评论列表 共有 0 条评论

暂无评论

微信公众账号

微信扫一扫加关注

发表
评论
返回
顶部