MySQL常用SQL语句实例：从建库到查询的完整指南

数据库与表的基础管理

在开始操作之前，理解“模式（Schema）”即数据库的概念很有必要。以下是针对数据库和表的基本操作命令，用于创建、查看和销毁对象。

第1步：查看当前所有数据库

目的：列出服务器中现有的所有数据库名称。

SHOW DATABASES;

说明：该命令罗列所有数据库的名称，帮助了解当前环境。

第2步：创建新数据库

目的：建立一个名为TEST的新数据库。

CREATE DATABASE TEST;

第3步：选择并进入数据库

目的：激活指定数据库以便后续操作。

USE TEST;

第4步：查看当前库中的表结构

目的：显示当前数据库中存在的表格及其基本信息。

SHOW TABLES;

若需查看特定表的详细结构（包括字段类型、键信息等），可使用：

SHOW [FULL] COLUMNS FROM <表名>;
-- 或者简写为
DESC <表名>;

说明：SHOW FULL COLUMNS 会比 DESC 提供更详细的列级信息。此外，SHOW CREATE TABLE <表名> 可查看建表的完整SQL语句。

第5步：创建基本表

目的：定义新表的结构，包含字段名、数据类型及约束条件。

语法模板：

CREATE TABLE <表名> (
    <列名> <数据类型> [约束条件],
    ... 
);

实例1：创建学生表

CREATE TABLE STUDENT (
    studentId int(30) PRIMARY KEY,
    name varchar(255),
    address varchar(255)
);

实例2：创建教师表（含外键约束）

CREATE TABLE TEACHER (
    teacherId int(30),
    name varchar(255),
    age int(20),
    studentId int(30),
    PRIMARY KEY (teacherId),
    FOREIGN KEY (studentId) REFERENCES STUDENT(studentId)
);

修改表结构与完整性约束

在生产环境中，随着需求变更，经常需要调整表结构或约束。操作前请确保已备份相关数据。

第6步：修改表属性

目的：重命名表、添加列、修改列名或类型、删除列。

-- 重命名表
ALTER TABLE TEACHER RENAME S;

-- 添加新列
ALTER TABLE TEACHER ADD COLUMN ADDRESS varchar(255);

-- 修改列名及类型
ALTER TABLE TEACHER CHANGE COLUMN ADDRESS address varchar(230);

-- 删除列（column关键字可选）
ALTER TABLE TEACHER DROP [COLUMN] address;

第7步：删除基本表

风险提醒：删除表是不可逆操作，且可能受外键约束限制。执行前务必确认数据安全或有备份。

-- RESTRICT：只有当该表不被其他表引用（无外键）、无视图、无触发器等时才允许删除。
DROP TABLE STUDENT RESTRICT;

-- CASCADE：删除该表的同时，删除依赖它的对象（如视图）。但在MySQL中，若存在未解除的外键约束，直接CASCADE也可能失败。
DROP TABLE STUDENT CASCADE;

注意：如果在MySQL中遇到 Cannot delete or update a parent row: a foreign key constraint fails 错误，通常是因为子表中仍有记录引用了父表的主键，或者存在其他外键关联未解除。需先处理子数据或禁用外键检查。

第8步：管理完整性约束

目的：动态添加或删除主键、外键等约束。

-- 添加主键约束
ALTER TABLE teacher ADD CONSTRAINT pk_teacher_id PRIMARY KEY (id);

-- 删除主键约束
ALTER TABLE teacher DROP PRIMARY KEY;

-- 添加外键约束
ALTER TABLE student ADD CONSTRAINT fk_student_teacher 
FOREIGN KEY (teacherId) REFERENCES teacher(id);

索引的建立与删除

索引能显著提升查询速度，但会降低写入性能。合理使用索引是数据库优化的关键。

第9步：创建索引

方法一：使用 CREATE INDEX

-- UNIQUE表示唯一索引，每个索引值对应唯一记录
-- CLUSTER表示聚簇索引（InnoDB引擎默认主键即为聚簇索引）
CREATE UNIQUE INDEX id_index ON teacher(id ASC);

方法二：使用 ALTER TABLE

ALTER TABLE teacher ADD UNIQUE INDEX id_index (id ASC);

第10步：删除索引

方法一：使用 DROP INDEX

DROP INDEX id_index ON teacher;

方法二：使用 ALTER TABLE

ALTER TABLE teacher DROP INDEX id_index;

数据查询核心语句

SELECT是最复杂的SQL语句，包含多个子句。标准顺序为：SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY。

第11步：基础查询与计算

目的：从表中选取数据，支持别名、表达式计算及函数使用。

-- 使用别名和表达式
SELECT teacherId AS id, salary - 100 AS S FROM teacher;

-- 使用字符串常量和内置函数（如转小写）
SELECT teacherid AS id, 'birth', salary - 20 AS SA, LOWER(name) FROM teacher;

-- 消除重复行
SELECT DISTINCT name FROM teacher;

第12步：条件过滤（WHERE子句）

目的：根据特定条件筛选元组。

• 比较：=, >, <, >=, <=, !=, <>
• 范围：BETWEEN ... AND ..., NOT BETWEEN ... AND ...
• 集合：IN (...), NOT IN (...)
• 字符匹配：LIKE '%...%', LIKE '_...' （%代表任意长度，_代表单个字符。注意中文占两个字符位）
• 空值判断：IS NULL, IS NOT NULL（注意不能用 = NULL）

-- 组合条件示例
SELECT * FROM teacher 
WHERE name LIKE '%s%' OR salary = 500;

第13步：排序与分组

排序：ORDER BY 列名 [ASC|DESC]，缺省为升序ASC。

SELECT * FROM teacher ORDER BY name DESC, salary ASC;

聚集函数：COUNT, SUM, AVG, MAX, MIN。缺省作用于ALL（全部记录），可用DISTINCT去重后计算。

SELECT COUNT(DISTINCT name), SUM(salary), AVG(salary) FROM teacher;

分组与HAVING：按列分组后，用HAVING筛选满足条件的组。

SELECT cno, COUNT(*) FROM teacher 
GROUP BY cno 
HAVING COUNT(*) >= 4;

高级查询技巧

第14步：连接查询（JOIN）

等值连接：利用WHERE条件连接两表。

SELECT s.*, t.* 
FROM student AS s, teacher AS t 
WHERE s.teacherid = t.teacherid;

外连接：保留一侧表的所有记录，另一侧若无匹配则填NULL。MySQL不支持FULL OUTER JOIN。

-- 左外连接：保留左边student表所有行
SELECT s.sno, s.name, c.cno, c.name 
FROM student AS s 
LEFT OUTER JOIN class AS c ON s.cno = c.cno;

-- 右外连接：保留右边class表所有行
SELECT c.cno, c.name, s.sno, s.name 
FROM student AS s 
RIGHT OUTER JOIN class AS c ON s.cno = c.cno;

第15步：嵌套与集合查询

带有IN的子查询：

SELECT sno, name FROM student 
WHERE cno IN (
    SELECT cno FROM student WHERE name = '华雄'
);

带有ANY/ALL的子查询：

-- 找出成绩小于等于所有学生最低分的学生（即最低分的那几个）
SELECT name, score FROM student 
WHERE score <= ALL (SELECT score FROM student);

集合运算：MySQL仅支持UNION（并集），不支持INTERSECT（交集）和EXCEPT（差集）。

SELECT * FROM student WHERE cno=1 
UNION 
SELECT * FROM student WHERE cno=2;

数据更新与维护

第16步：插入与复制数据

单条插入：

INSERT INTO student (cno, name, score) VALUES (2, '横切', 85);

批量插入（子查询结果）：

-- 将student表数据复制到studentcopy表
INSERT INTO studentcopy SELECT * FROM student;

第17步：修改与删除数据

UPDATE：若不加WHERE，将更新所有行！

-- 修改特定行
UPDATE studentcopy SET score=80 WHERE sno=1;
-- 批量更新（谨慎使用）
UPDATE studentcopy SET score=score-20;

DELETE：若不加WHERE，将清空全表！建议先在测试环境验证。

-- 删除特定行
DELETE FROM studentcopy WHERE sno=1;
-- 带子查询删除
DELETE FROM studentcopy 
WHERE cno IN (SELECT cno FROM student AS s WHERE s.cno = 2);

视图的操作与管理

视图是基于SQL查询结果的虚拟表，用于简化复杂查询或限制数据访问。若底层基表结构变化，视图可能失效。

第18步：创建视图

-- 简单视图
CREATE VIEW part_student AS
SELECT * FROM student WHERE cno = 2;

-- 多表连接视图（建议明确指定列名）
CREATE VIEW student_class (sno, student_name, class_name, score) AS
SELECT s.sno, s.name, c.name, s.score
FROM student AS s, class AS c
WHERE s.cno = c.cno;

WITH CHECK OPTION：确保通过视图更新的数据符合视图定义的条件。

第19步：管理与删除视图

-- 删除视图，若有依赖视图需使用CASCADE级联删除
DROP VIEW <视图名> [CASCADE];

更新视图的逻辑与更新基本表类似，但受限于视图定义（如包含聚合函数、GROUP BY、DISTINCT等的视图通常不可直接更新）。

总结

以上涵盖了MySQL日常开发中最核心的SQL语句操作，从基础的DDL建库建表，到中层的索引优化与复杂查询，再到视图的管理。掌握这些实例能有效提升数据库操作的效率与规范性。以上就是MySQL常用SQL语句与操作实例的详细教程，更多关于数据库开发与优化的资料请关注本站其它相关文章！