MySQL的DML进阶、分页查找、SQL约束及多表操作方法

目录

1. DML进阶操作
   • 1.1 插入数据
   • 1.2 更新数据
   • 1.3 删除数据
   • 1.4 替换数据
2. 分页查找
   • 2.1 LIMIT子句
   • 2.2 分页查询的实现
3. SQL约束
   • 3.1 主键约束
   • 3.2 唯一约束
   • 3.3 外键约束
   • 3.4 非空约束
   • 3.5 默认值约束
   • 3.6 检查约束
4. 多表操作方法
   • 4.1 内连接
   • 4.2 左连接
   • 4.3 右连接
   • 4.4 全外连接
   • 4.5 自连接
   • 4.6 子查询
   • 4.7 联合查询
5. 总结

DML进阶操作

DML（Data Manipulation Language）是用于操作数据库中数据的语言，主要包括插入（INSERT）、更新（UPDATE）、删除（DELETE）等操作。在实际开发中，我们经常需要对数据进行复杂的操作，因此掌握DML的进阶操作是非常必要的。

1.1 插入数据

插入数据是向数据库表中添加新记录的操作。MySQL提供了多种插入数据的方式。

1.1.1 插入单行数据

最基本的插入操作是插入单行数据，语法如下：

INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);

例如：

INSERT INTO students (name, age, gender)
VALUES ('张三', 20, '男');

1.1.2 插入多行数据

如果需要一次性插入多行数据，可以使用以下语法：

INSERT INTO 表名 (列1, 列2, 列3, ...)
VALUES 
(值1, 值2, 值3, ...),
(值1, 值2, 值3, ...),
(值1, 值2, 值3, ...);

例如：

INSERT INTO students (name, age, gender)
VALUES 
('李四', 21, '女'),
('王五', 22, '男'),
('赵六', 23, '女');

1.1.3 插入查询结果

有时候我们需要将查询结果插入到另一个表中，可以使用以下语法：

INSERT INTO 目标表名 (列1, 列2, 列3, ...)
SELECT 列1, 列2, 列3, ...
FROM 源表名
WHERE 条件;

例如：

INSERT INTO new_students (name, age, gender)
SELECT name, age, gender
FROM students
WHERE age > 20;

1.2 更新数据

更新数据是修改表中已有记录的操作。MySQL提供了UPDATE语句来实现这一功能。

1.2.1 更新单列数据

更新单列数据的语法如下：

UPDATE 表名
SET 列名 = 新值
WHERE 条件;

例如：

UPDATE students
SET age = 25
WHERE name = '张三';

1.2.2 更新多列数据

如果需要同时更新多列数据，可以使用以下语法：

UPDATE 表名
SET 列1 = 新值1, 列2 = 新值2, ...
WHERE 条件;

例如：

UPDATE students
SET age = 25, gender = '女'
WHERE name = '张三';

1.2.3 更新所有数据

如果不指定WHERE条件，UPDATE语句将会更新表中的所有记录。例如：

UPDATE students
SET age = 25;

1.3 删除数据

删除数据是从表中移除记录的操作。MySQL提供了DELETE语句来实现这一功能。

1.3.1 删除单行数据

删除单行数据的语法如下：

DELETE FROM 表名
WHERE 条件;

例如：

DELETE FROM students
WHERE name = '张三';

1.3.2 删除多行数据

如果需要删除多行数据，可以使用以下语法：

DELETE FROM 表名
WHERE 条件;

例如：

DELETE FROM students
WHERE age > 25;

1.3.3 删除所有数据

如果不指定WHERE条件，DELETE语句将会删除表中的所有记录。例如：

DELETE FROM students;

1.4 替换数据

替换数据是MySQL提供的一种特殊的插入操作，如果插入的数据与表中已有数据的主键或唯一键冲突，则会先删除原有数据，再插入新数据。

1.4.1 替换单行数据

替换单行数据的语法如下：

REPLACE INTO 表名 (列1, 列2, 列3, ...)
VALUES (值1, 值2, 值3, ...);

例如：

REPLACE INTO students (id, name, age, gender)
VALUES (1, '张三', 25, '男');

1.4.2 替换多行数据

如果需要一次性替换多行数据，可以使用以下语法：

REPLACE INTO 表名 (列1, 列2, 列3, ...)
VALUES 
(值1, 值2, 值3, ...),
(值1, 值2, 值3, ...),
(值1, 值2, 值3, ...);

例如：

REPLACE INTO students (id, name, age, gender)
VALUES 
(1, '张三', 25, '男'),
(2, '李四', 26, '女'),
(3, '王五', 27, '男');

分页查找

在实际应用中，我们经常需要从数据库中查询大量数据，但一次性将所有数据加载到内存中可能会导致性能问题。因此，分页查找是一种常见的解决方案。

2.1 LIMIT子句

MySQL提供了LIMIT子句来实现分页查找。LIMIT子句用于限制查询结果的数量，语法如下：

SELECT 列1, 列2, 列3, ...
FROM 表名
LIMIT 偏移量, 数量;

其中，偏移量表示从第几条记录开始查询，数量表示查询多少条记录。

例如：

SELECT * FROM students
LIMIT 0, 10;

上述查询语句表示从第0条记录开始，查询10条记录。

2.2 分页查询的实现

在实际应用中，我们通常需要根据页码和每页显示的记录数来计算偏移量。假设每页显示10条记录，那么第n页的偏移量可以通过以下公式计算：

偏移量 = (n - 1) * 每页显示的记录数

例如，查询第3页的数据，可以使用以下SQL语句：

SELECT * FROM students
LIMIT 20, 10;

上述查询语句表示从第20条记录开始，查询10条记录，即第3页的数据。

SQL约束

SQL约束是用于限制表中数据的规则，以确保数据的完整性和一致性。MySQL提供了多种约束类型，包括主键约束、唯一约束、外键约束、非空约束、默认值约束和检查约束。

3.1 主键约束

主键约束用于唯一标识表中的每一行数据。主键列的值必须是唯一的，且不能为NULL。

3.1.1 创建主键约束

在创建表时，可以通过以下语法定义主键约束：

CREATE TABLE 表名 (
    列1 数据类型 PRIMARY KEY,
    列2 数据类型,
    ...
);

例如：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    gender CHAR(1)
);

3.1.2 添加主键约束

如果表已经存在，可以通过以下语法添加主键约束：

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

例如：

ALTER TABLE students
ADD PRIMARY KEY (id);

3.1.3 删除主键约束

如果需要删除主键约束，可以使用以下语法：

ALTER TABLE 表名
DROP PRIMARY KEY;

例如：

ALTER TABLE students
DROP PRIMARY KEY;

3.2 唯一约束

唯一约束用于确保列中的值是唯一的，但与主键约束不同的是，唯一约束允许NULL值。

3.2.1 创建唯一约束

在创建表时，可以通过以下语法定义唯一约束：

CREATE TABLE 表名 (
    列1 数据类型 UNIQUE,
    列2 数据类型,
    ...
);

例如：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) UNIQUE,
    age INT,
    gender CHAR(1)
);

3.2.2 添加唯一约束

如果表已经存在，可以通过以下语法添加唯一约束：

ALTER TABLE 表名
ADD UNIQUE (列名);

例如：

ALTER TABLE students
ADD UNIQUE (name);

3.2.3 删除唯一约束

如果需要删除唯一约束，可以使用以下语法：

ALTER TABLE 表名
DROP INDEX 列名;

例如：

ALTER TABLE students
DROP INDEX name;

3.3 外键约束

外键约束用于建立表与表之间的关系，确保一个表中的数据与另一个表中的数据保持一致。

3.3.1 创建外键约束

在创建表时，可以通过以下语法定义外键约束：

CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    ...
    FOREIGN KEY (列名) REFERENCES 另一表名(列名)
);

例如：

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

3.3.2 添加外键约束

如果表已经存在，可以通过以下语法添加外键约束：

ALTER TABLE 表名
ADD FOREIGN KEY (列名) REFERENCES 另一表名(列名);

例如：

ALTER TABLE orders
ADD FOREIGN KEY (product_id) REFERENCES products(product_id);

3.3.3 删除外键约束

如果需要删除外键约束，可以使用以下语法：

ALTER TABLE 表名
DROP FOREIGN KEY 外键名;

例如：

ALTER TABLE orders
DROP FOREIGN KEY fk_product_id;

3.4 非空约束

非空约束用于确保列中的值不能为NULL。

3.4.1 创建非空约束

在创建表时，可以通过以下语法定义非空约束：

CREATE TABLE 表名 (
    列1 数据类型 NOT NULL,
    列2 数据类型,
    ...
);

例如：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    gender CHAR(1)
);

3.4.2 添加非空约束

如果表已经存在，可以通过以下语法添加非空约束：

ALTER TABLE 表名
MODIFY 列名 数据类型 NOT NULL;

例如：

ALTER TABLE students
MODIFY name VARCHAR(50) NOT NULL;

3.4.3 删除非空约束

如果需要删除非空约束，可以使用以下语法：

ALTER TABLE 表名
MODIFY 列名 数据类型;

例如：

ALTER TABLE students
MODIFY name VARCHAR(50);

3.5 默认值约束

默认值约束用于为列指定默认值，当插入数据时，如果没有为该列指定值，则使用默认值。

3.5.1 创建默认值约束

在创建表时，可以通过以下语法定义默认值约束：

CREATE TABLE 表名 (
    列1 数据类型 DEFAULT 默认值,
    列2 数据类型,
    ...
);

例如：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT DEFAULT 18,
    gender CHAR(1)
);

3.5.2 添加默认值约束

如果表已经存在，可以通过以下语法添加默认值约束：

ALTER TABLE 表名
ALTER 列名 SET DEFAULT 默认值;

例如：

ALTER TABLE students
ALTER age SET DEFAULT 18;

3.5.3 删除默认值约束

如果需要删除默认值约束，可以使用以下语法：

ALTER TABLE 表名
ALTER 列名 DROP DEFAULT;

例如：

ALTER TABLE students
ALTER age DROP DEFAULT;

3.6 检查约束

检查约束用于确保列中的值满足指定的条件。

3.6.1 创建检查约束

在创建表时，可以通过以下语法定义检查约束：

CREATE TABLE 表名 (
    列1 数据类型,
    列2 数据类型,
    ...
    CHECK (条件)
);

例如：

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT CHECK (age >= 18),
    gender CHAR(1)
);

3.6.2 添加检查约束

如果表已经存在，可以通过以下语法添加检查约束：

ALTER TABLE 表名
ADD CHECK (条件);

例如：

ALTER TABLE students
ADD CHECK (age >= 18);

3.6.3 删除检查约束

如果需要删除检查约束，可以使用以下语法：

ALTER TABLE 表名
DROP CHECK 约束名;

例如：

ALTER TABLE students
DROP CHECK chk_age;

多表操作方法

在实际应用中，我们经常需要从多个表中查询数据，或者将多个表中的数据进行关联操作。MySQL提供了多种多表操作方法，包括内连接、左连接、右连接、全外连接、自连接、子查询和联合查询。

4.1 内连接

内连接（INNER JOIN）是最常用的连接方式，它返回两个表中满足连接条件的记录。

4.1.1 基本语法

内连接的基本语法如下：

SELECT 列1, 列2, 列3, ...
FROM 表1
INNER JOIN 表2
ON 表1.列名 = 表2.列名;

例如：

SELECT students.name, courses.course_name
FROM students
INNER JOIN courses
ON students.id = courses.student_id;

4.1.2 多表内连接

如果需要连接多个表，可以使用以下语法：

SELECT 列1, 列2, 列3, ...
FROM 表1
INNER JOIN 表2 ON 表1.列名 = 表2.列名
INNER JOIN 表3 ON 表2.列名 = 表3.列名;

例如：

SELECT students.name, courses.course_name, grades.grade
FROM students
INNER JOIN courses ON students.id = courses.student_id
INNER JOIN grades ON courses.id = grades.course_id;

4.2 左连接

左连接（LEFT JOIN）返回左表中的所有记录，以及右表中满足连接条件的记录。如果右表中没有匹配的记录，则返回NULL。

4.2.1 基本语法

左连接的基本语法如下：

SELECT 列1, 列2, 列3, ...
FROM 表1
LEFT JOIN 表2
ON 表1.列名 = 表2.列名;

例如：

SELECT students.name, courses.course_name
FROM students
LEFT JOIN courses
ON students.id = courses.student_id;

4.2.2 多表左连接

如果需要连接多个表，可以使用以下语法：

SELECT 列1, 列2, 列3, ...
FROM 表1
LEFT JOIN 表2 ON 表1.列名 = 表2.列名
LEFT JOIN 表3 ON 表2.列名 = 表3.列名;

例如：

SELECT students.name, courses.course_name, grades.grade
FROM students
LEFT JOIN courses ON students.id = courses.student_id
LEFT JOIN grades ON courses.id = grades.course_id;

4.3 右连接

右连接（RIGHT JOIN）返回右表中的所有记录，以及左表中满足连接条件的记录。如果左表中没有匹配的记录，则返回NULL。

4.3.1 基本语法

右连接的基本语法如下：

SELECT 列1, 列2, 列3, ...
FROM 表1
RIGHT JOIN 表2
ON 表1.列名 = 表2.列名;

例如：

SELECT students.name, courses.course_name
FROM students
RIGHT JOIN courses
ON students.id = courses.student_id;

4.3.2 多表右连接

如果需要连接多个表，可以使用以下语法：

SELECT 列1, 列2, 列3, ...
FROM 表1
RIGHT JOIN 表2 ON 表1.列名 = 表2.列名
RIGHT JOIN 表3 ON 表2.列名 = 表3.列名;

例如：

SELECT students.name, courses.course_name, grades.grade
FROM students
RIGHT JOIN courses ON students.id = courses.student_id
RIGHT JOIN grades ON courses.id = grades.course_id;

4.4 全外连接

全外连接（FULL OUTER JOIN）返回左表和右表中的所有记录，如果某一边没有匹配