mysql的DML進階、分頁查找、SQL約束及多表操作方法

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）返回左表和右表中的所有記錄，如果某一邊沒有匹配