Mysql基础操作-常见SQL语句

背景知识

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';

注意事项

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

六、mysql数据类型

整型

• TINYINT: 1 字节，范围 -128 到 127 或 0 到 255（无符号）。
• SMALLINT: 2 字节，范围 -32768 到 32767 或 0 到 65535（无符号）。
• MEDIUMINT: 3 字节，范围 -8388608 到 8388607 或 0 到 16777215（无符号）。
• INT 或 INTEGER: 4 字节，范围 -2147483648 到 2147483647 或 0 到 4294967295（无符号）。
• BIGINT: 8 字节，范围 -9223372036854775808 到 9223372036854775807 或 0 到 18446744073709551615（无符号）。

浮点型

• 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: 年份格式 YYYY 或 YY。
• DATETIME: 日期和时间格式 YYYY-MM-DD HH:MM:SS。
• TIMESTAMP: 日期和时间格式 YYYYMMDDHHMMSS，自动维护当前时间戳。

二进制/布尔类型

• BIT(M): 二进制位字段，M 是位的最大长度。
• BOOLEAN 或 BOOL: 布尔类型，可以是 TRUE 或 FALSE。