创建数据库

CREATE DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

• CHARACTER SET：指定字符集（例如 utf8mb4 支持完整的 Unicode 字符）。
• COLLATE：指定排序规则（如 utf8mb4_unicode_ci 是一种不区分大小写的排序规则）。

查看数据库

SHOW DATABASES;

• 列出当前服务器上的所有数据库。

使用数据库

USE db_name;

• 切换到指定的数据库。

删除数据库

DROP DATABASE db_name;

• 注意：此命令不可逆，会永久删除数据库及其所有数据。

表的操作

创建表

CREATE TABLE table_name (
    id INT AUTO_INCREMENT PRIMARY KEY, -- 自增主键
    name VARCHAR(100) NOT NULL,        -- 非空字符串字段
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- 自动记录创建时间
);

查看表结构

DESCRIBE table_name;

• 查看表的字段、类型、是否为空等信息。

修改表结构

ALTER TABLE table_name ADD COLUMN age INT; -- 添加新列
ALTER TABLE table_name DROP COLUMN age;   -- 删除列
ALTER TABLE table_name MODIFY COLUMN name VARCHAR(150); -- 修改列

删除表

DROP TABLE table_name;

• 删除整个表及其数据。

数据操作 (CRUD)

插入数据

INSERT INTO table_name (name) VALUES ('Alice');

• 向指定字段插入值，未提供值的字段将采用默认值。

批量插入

INSERT INTO table_name (name) VALUES ('Bob'), ('Charlie');

• 一次插入多行数据。

查询数据

SELECT * FROM table_name; -- 查询所有字段
SELECT name FROM table_name WHERE id = 1; -- 查询特定条件数据

• 使用 WHERE 子句筛选数据。

更新数据

UPDATE table_name
SET name = 'Updated Name'
WHERE id = 1;

• 仅更新符合条件的行。

删除数据

DELETE FROM table_name WHERE id = 1;

• 注意：若不加 WHERE，将删除表中所有数据。

常用查询功能

排序查询

SELECT * FROM table_name ORDER BY name ASC; -- 升序
SELECT * FROM table_name ORDER BY name DESC; -- 降序

分页查询

SELECT * FROM table_name LIMIT 10 OFFSET 20;

• LIMIT：限制返回的行数。
• OFFSET：指定起始位置。

聚合查询

SELECT COUNT(*) AS total FROM table_name; -- 统计总数
SELECT AVG(age) AS avg_age FROM table_name; -- 平均值
SELECT MAX(age) AS max_age FROM table_name; -- 最大值
SELECT MIN(age) AS min_age FROM table_name; -- 最小值
SELECT SUM(age) AS sum_age FROM table_name; -- 求和

分组查询

SELECT age, COUNT(*) AS total
FROM table_name
GROUP BY age
HAVING total > 1; -- 筛选分组后的结果

常用函数

字符串函数

SELECT CONCAT('Hello', ' ', 'World') AS greeting; -- 拼接字符串
SELECT LENGTH('Hello') AS len; -- 字符串长度
SELECT UPPER('hello') AS upper_text; -- 转为大写
SELECT LOWER('HELLO') AS lower_text; -- 转为小写
SELECT SUBSTRING('abcdef', 2, 3) AS substr; -- 提取子字符串（从第2位开始，长度为3）

数学函数

SELECT ROUND(123.456, 2) AS rounded; -- 四舍五入到2位小数
SELECT CEIL(10.2) AS ceil_val; -- 向上取整
SELECT FLOOR(10.8) AS floor_val; -- 向下取整
SELECT RAND() AS random_num; -- 随机数

日期和时间函数

SELECT NOW() AS current_time; -- 当前日期时间
SELECT CURDATE() AS current_date; -- 当前日期
SELECT CURTIME() AS current_time_only; -- 当前时间
SELECT DATE_ADD(NOW(), INTERVAL 7 DAY) AS next_week; -- 日期加减
SELECT DATEDIFF('2024-12-31', '2024-01-01') AS days_diff; -- 计算日期差

条件函数

SELECT IF(1 > 0, 'True', 'False') AS condition_check; -- 条件判断
SELECT CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 60 THEN 'Adult'
    ELSE 'Senior'
END AS age_group
FROM table_name;

索引与优化

创建索引

CREATE INDEX idx_name ON table_name (name);

• 提高查询效率。

查看索引

SHOW INDEX FROM table_name;

删除索引

DROP INDEX idx_name ON table_name;

事务管理

开启事务

START TRANSACTION;

提交事务

COMMIT;

回滚事务

ROLLBACK;

• 事务确保一组操作要么全部成功，要么全部失败。

用户和权限管理

创建用户

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

授权

GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'host';

查看权限

SHOW GRANTS FOR 'username'@'host';

撤销权限

REVOKE ALL PRIVILEGES ON db_name.* FROM 'username'@'host';

删除用户

DROP USER 'username'@'host';