添加列#

1
ALTER TABLE 表名 ADD COLUMN 列名 数据类型 [约束];

示例：

1
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
2
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

修改列#

1
ALTER TABLE 表名 MODIFY COLUMN 列名 新数据类型;
2
-- 或修改列名
3
ALTER TABLE 表名 CHANGE COLUMN 旧列名 新列名 数据类型;

示例：

1
-- 修改数据类型
2
ALTER TABLE users MODIFY COLUMN email VARCHAR(200);
3

4
-- 修改列名
5
ALTER TABLE users CHANGE COLUMN username nickname VARCHAR(50);

删除列#

1
ALTER TABLE 表名 DROP COLUMN 列名;

示例：

1
ALTER TABLE users DROP COLUMN phone;

添加主键#

1
ALTER TABLE 表名 ADD PRIMARY KEY (列名);

删除主键#

1
ALTER TABLE 表名 DROP PRIMARY KEY;

查询所有字段#

使用通配符*为所有列

1
SELECT * FROM 数据表;

查询指定字段#

1
SELECT 字段1,字段2,... FROM 数据表;

查询指定记录#

当想要查询数据库中符合一定条件的数据时，可以使用WHERE子句对表中的记录进行筛选，语法格式如下：

1
SELECT 字段1,字段2,... FROM 数据表 WHERE 条件;

WHERE子句中可以使用多条条件判断符

多条件查询#

1. 使用AND关键字查询：只有符合所有条件的记录才会被返回

   1
   -- 查询价格在 5 到 50 之间的商品信息。
   2
   SELECT * FROM goods WHERE price >=5 and price <=50;

2. 使用OR关键字查询：只需要符合所有条件中的任意一条才会返回

   1
   -- 查询库存数量大于 20 或者价格超过 100 的商品信息。
   2
   SELECT * FROM goods WHERE num > 20 or price > 100;

3. 使用IN关键字查询：可以查询字段值等于指定集合中任意一个值的记录

   1
   -- 查询goods表中id值为1和3的记录
   2
   SELECT * FROM goods WHERE id IN(1,3);

   也可以和OR关键字一起使用

   1
   -- 查询goods表中id值为1或7的记录
   2
   SELECT * FROM goods WHERE id=1 OR id=7;

   也可以和NOT关键字一起使用

   1
   -- 查询goods表中id值不为1和3的记录
   2
   SELECT * FROM goods WHERE id NOT IN (1,3);

查询空值#

IS NULL 关键字，用于查询字段值为NULL的记录

1
-- 查询库存数量不为 NULL 的商品信息。
2
SELECT * FROM goods where num is not NULL;

查询结果不重复#

可以用DISTINCT关键字去重

1
-- 查询表中所有不同类型的商品，并去除重复项。
2
SELECT DISTINCT type from goods;

范围查询#

使用BETWEEN AND关键字：用于查询字段值在某个范围内的记录

1
-- 查询goods表中price值为50到100的商品信息
2
SELECT * FROM goods WHERE price BETWEEN 50 AND 100;

字符匹配查询#

使用LIKE关键字：该查询又称模糊查询，通常用于查询字段值包括某些字符的记录

1
-- 查询goods表中以"糖"字结尾的记录
2
SELECT * FROM goods WHERE name LIKE '%糖';

通配符_只能匹配一个字符

1
SELECT * FROM goods WHERE name LIKE '_瓜';

排序查询#

使用ORDER BY关键字：把查询结果按照指定的顺序排列

可以进行单字段排序，也可以进行多字段排序

1
-- 单字段排序
2
SELECT id,name FROM goods ORDER BY id;
3
-- 多字段排序
4
SELECT * FROM goods ORDER BY price,num;

使用DESC关键字：对查询的结果进行降序排序

1
SELECT * FROM goods ORDER BY price DESC;

当按照多字段进行排序的时候，也可以使用DESC关键字进行降序排序

1
-- 查询goods表中所有记录，并按照price字段和num字段先降序后升序排序
2
SELECT * FROM goods ORDER BY price DESC,num;

限制查询结果的数量#

使用LIMIT关键字：可以限制查询结果的数量

1
-- 查询goods表中前3条记录
2
SELECT * FROM goods LIMIT 3;
3
-- 查询goods表中从第三条记录开始，总条数为3的记录(也就是查询第3，4，5条记录)
4
SELECT * FROM goods LIMIT 2,3;

聚合函数#

一共有五种分别为

• COUNT( )：计算表中记录的条数
• SUM( )：求和
• AVG( )：求平均值
• MAX( )：求最大值
• MIN( )：求最小值

COUNT()函数用于统计数据记录条数，返回表中总的记录条数或符合特定条件的记录条数

使用方法有两种

• COUNT(*)：计算表中总的记录数，不管表字段中是否包含NULL值
• COUNT(col_name)：计算表中指定字段的记录数，忽略NULL值

分组查询#

分组查询是将查询结果按照某个或多个字段进行分组，MySQL是使用GROUP BY语句对数据进行分组

1. 简单分组查询

   将GROUP BY关键字与聚合函数COUNT()一起使用可以查询每组的数量

   1
   -- 将goods表中的记录按照type字段进行分组，并统计每组的数量
   2
   SELECT type,count(*) FROM goods GROUP BY type;

   需要将每种类型中包含的商品名称显示出来，可以使用group_concat()函数

   1
   -- 将goods表中的记录按照type字段进行分组，并显示每组中的商品名称
   2
   SELECT type,group_concat(name) FROM goods GROUP BY type;

2. 使用HAVING过滤分组后数据

   GROUP BY和HAVING一起使用，可以指定显示记录所需满足的条件，只有满足条件的分组才会被显示

   1
   -- 将goods表中的记录按照type字段分组并统计每组的数量，然后只取商品数量大于1的分组
   2
   SELECT type,count(*) FROM goods GROUP BY type HAVING COUNT(*)>1;

3. 使用多个字段进行分组

   使用GROUP BY不止可以按照一个字段进行分组，还可以按多个字段进行分组，分组层次从左到右，按照第一个字段进行分组，然后对第一个字段值相同的记录，再根据第二个字段进行分组，以此类推

   1
   -- 将goods表中的记录按照type和num字段进行分组并统计，显示每个分组中商品类别、库存、商品名称和商品数量
   2
   SELECT type,num,group_concat(name),count(name) FROM goods GROUP BY type,num;

内连接查询#

普通内连接查询：

1
-- 两个表之间的关系通过 WHERE 连接
2
SELECT staff_id,name,sex,section_title,phone_number FROM staff,section WHERE staff.section_id = section.section_id;

显式内连接查询：

1
SELECT staff_id,name,sex,section_title,phone_number
2
FROM staff
3
INNER JOIN section ON staff.section_id = section.section_id;

自连接查询：

1
-- 从 staff 表中查询薪资低于 15000 的员工的 staff_id、name、money
2
SELECT s1.staff_id,s1.name,s2.money FROM staff AS s1
3
INNER JOIN staff AS s2 ON s1.staff_id = s2.staff_id AND s2.money < 15000;

外连接查询#

外连接查询(OUTER JOIN)是以一张表为基表，根据连接条件，与另一张表的每一行进行匹配

1. 左外连接 (LEFT JOIN) 返回左表中的所有记录，以及右表中匹配的行

   1
   -- 查询所有员工信息，包括部门名称（没有部门的员工部门显示为NULL）
   2
   SELECT e.name, d.department_name
   3
   FROM employees e
   4
   LEFT JOIN departments d ON e.dept_id = d.id;

2. 右外连接 (RIGHT JOIN) 返回右表中的所有记录，以及左表中匹配的行

   1
   -- 查询所有部门信息，包括员工（没有员工的部门员工信息显示为NULL）
   2
   SELECT e.name, d.department_name
   3
   FROM employees e
   4
   RIGHT JOIN departments d ON e.dept_id = d.id;

3. 全外连接 (FULL OUTER JOIN) 返回两个表中的所有记录，匹配的行合并，没有匹配的显示为NULL

   1
   -- MySQL不支持FULL OUTER JOIN，可以用UNION实现
   2
   SELECT e.name, d.department_name
   3
   FROM employees e
   4
   LEFT JOIN departments d ON e.dept_id = d.id
   5
   UNION
   6
   SELECT e.name, d.department_name
   7
   FROM employees e
   8
   RIGHT JOIN departments d ON e.dept_id = d.id;

子查询#

子查询是将一个查询语句嵌套在另一个查询语句中

1. 标量子查询：返回单一值

   1
   -- 查询工资高于平均工资的员工
   2
   SELECT * FROM employees WHERE salary > (SELECT AVG(salary) FROM employees);

2. 列子查询：返回一列值

   1
   -- 查询在"销售部"工作的所有员工
   2
   SELECT * FROM employees WHERE dept_id IN (SELECT id FROM departments WHERE name = '销售部');

3. 表子查询：返回多行多列

   1
   -- 查询每个部门中工资最高的员工
   2
   SELECT * FROM employees WHERE (dept_id, salary) IN (
   3
       SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id
   4
   );

创建索引#

1
-- 创建普通索引
2
CREATE INDEX idx_name ON employees(name);
3

4
-- 创建唯一索引
5
CREATE UNIQUE INDEX idx_email ON employees(email);
6

7
-- 创建复合索引
8
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);
9

10
-- 在创建表时添加索引
11
CREATE TABLE users (
12
    id INT PRIMARY KEY,
13
    name VARCHAR(50),
14
    INDEX idx_name (name)
15
);

查看索引#

1
SHOW INDEX FROM 表名;

删除索引#

1
DROP INDEX idx_name ON 表名;

添加约束#

1
-- 添加主键
2
ALTER TABLE 表名 ADD PRIMARY KEY (列名);
3

4
-- 添加外键
5
ALTER TABLE 表名 ADD FOREIGN KEY (列名) REFERENCES 关联表(关联列);
6

7
-- 添加唯一约束
8
ALTER TABLE 表名 ADD UNIQUE (列名);

事务控制语句#

1
-- 开启事务
2
START TRANSACTION;
3

4
-- 或
5
BEGIN;
6

7
-- 提交事务
8
COMMIT;
9

10
-- 回滚事务
11
ROLLBACK;
12

13
-- 设置保存点
14
SAVEPOINT 保存点名称;
15

16
-- 回滚到保存点
17
ROLLBACK TO SAVEPOINT 保存点名称;

事务特性（ACID）#

• Atomicity（原子性）：事务是最小执行单位，不可分割
• Consistency（一致性）：事务执行前后，数据库状态保持一致
• Isolation（隔离性）：并发事务之间相互隔离
• Durability（持久性）：事务提交后，对数据库的修改永久保存

事务隔离级别#

1
-- 查看当前隔离级别
2
SELECT @@transaction_isolation;
3

4
-- 设置隔离级别
5
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

创建视图#

1
CREATE VIEW view_name AS
2
SELECT column1, column2
3
FROM table_name
4
WHERE condition;

查看视图#

1
-- 查看视图定义
2
SHOW CREATE VIEW view_name;
3

4
-- 查看所有视图
5
SELECT * FROM information_schema.VIEWS;

更新和删除视图#

1
-- 更新视图（可更新的条件：单表、无聚合、无DISTINCT等）
2
CREATE OR REPLACE VIEW view_name AS new_query;
3

4
-- 删除视图
5
DROP VIEW IF EXISTS view_name;

创建存储过程#

1
DELIMITER $$
2

3
CREATE PROCEDURE procedure_name(IN param1 INT, OUT param2 VARCHAR(50))
4
BEGIN
5
    DECLARE variable1 INT DEFAULT 0;
6

7
    -- SQL语句
8
    SELECT COUNT(*) INTO variable1 FROM table_name WHERE id = param1;
9

10
    SET param2 = CONCAT('Total: ', variable1);
11
END$$
12

13
DELIMITER ;

调用存储过程#

1
-- 调用存储过程
2
CALL procedure_name(1, @result);
3

4
-- 查看结果
5
SELECT @result;

删除存储过程#

1
DROP PROCEDURE IF EXISTS procedure_name;

备份单个数据库#

1
mysqldump -u root -p 数据库名 > 备份文件.sql

示例：

1
mysqldump -u root -p mydb > mydb_backup.sql

备份多个数据库#

1
mysqldump -u root -p --databases db1 db2 > backup.sql

备份所有数据库#

1
mysqldump -u root -p --all-databases > all_backup.sql

只备份表结构（不备份数据）#

1
mysqldump -u root -p --no-data 数据库名 > structure.sql

只备份数据（不备份表结构）#

1
mysqldump -u root -p --no-create-info 数据库名 > data.sql

导出查询结果到文件#

1
SELECT * INTO OUTFILE '/path/to/file.csv'
2
FIELDS TERMINATED BY ','
3
LINES TERMINATED BY '\n'
4
FROM 表名;

从文件导入数据#

1
LOAD DATA INFILE '/path/to/file.csv'
2
INTO TABLE 表名
3
FIELDS TERMINATED BY ','
4
LINES TERMINATED BY '\n';

适合创建索引的场景#

• 主键和外键
• 经常用于 WHERE 子句的列
• 经常用于 JOIN 的列
• 经常用于 ORDER BY 的列
• 经常用于 GROUP BY 的列

不适合创建索引的场景#

• 表记录太少
• 经常增删改的表
• 数据重复且分布均匀的列
• 频繁更新的列

使用 EXPLAIN 分析查询#

1
EXPLAIN SELECT * FROM users WHERE id = 1;

EXPLAIN 结果说明：

优化建议#

1. **避免 SELECT ***：只查询需要的字段
2. 使用 LIMIT：限制返回结果数量
3. 避免全表扫描：确保 WHERE 条件使用索引
4. 优化 JOIN：小表驱动大表，关联字段加索引
5. 避免子查询：能用 JOIN 就不用子查询
6. 使用 UNION ALL：比 UNION 效率高（不去重）

优化表#

1
OPTIMIZE TABLE 表名;

分析表#

1
ANALYZE TABLE 表名;

检查表#

1
CHECK TABLE 表名;

查看当前配置#

1
SHOW VARIABLES;
2
SHOW VARIABLES LIKE 'max_connections';

修改配置（临时）#

1
SET GLOBAL max_connections = 500;

修改配置（永久）#

编辑 my.ini 或 my.cnf 文件：

1
[mysqld]
2
max_connections = 500
3
innodb_buffer_pool_size = 1G
4
query_cache_size = 64M