mysql 常用知識點總結

MySQL 是一種廣泛使用的關係型資料庫管理系統（RDBMS），它基於結構化查詢語言（SQL）。瞭解 MySQL 的語法對資料庫管理和操作非常重要。以下是 MySQL 語法的詳細完整解釋，涵蓋基本概念、創建表、查詢、修改數據等內容。

1. 基礎概念

• 資料庫 (Database): 資料庫是存儲數據的容器，裡面可以包含多個表。
• 表 (Table): 表是資料庫中的核心元素，包含行（記錄）和列（欄位）。
• 行 (Row): 一行數據代表表中的一條記錄。
• 列 (Column): 列是數據的屬性，每個列都有一個特定的數據類型。
• 數據類型 (Data Types): 定義列中數據的類型，如整數、浮點數、字元串、日期等。

2. 常見數據類型

3. 資料庫管理

• 創建資料庫

  CREATE DATABASE db_name;
  

  • db_name: 資料庫名稱。

• 選擇資料庫

  USE db_name;
  

• 查看現有資料庫

  SHOW DATABASES;
  

• 刪除資料庫

  DROP DATABASE db_name;
  

4. 表操作

• 創建表

  CREATE TABLE table_name (
    column_name1 data_type constraints,
    column_name2 data_type constraints,
    ...
  );
  

  • table_name: 表的名稱。
  • data_type: 列的數據類型。
  • constraints: 列的約束，如 PRIMARY KEY、NOT NULL 等。

  示例：

  CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    age INT,
    enrollment_date DATE
  );
  

• 查看表結構

  DESCRIBE table_name;
  

• 刪除表

  DROP TABLE table_name;
  

• 修改表結構

  • 添加新列：

    ALTER TABLE table_name ADD new_column_name data_type;
    

  • 修改列類型：

    ALTER TABLE table_name MODIFY column_name new_data_type;
    

  • 刪除列：

    ALTER TABLE table_name DROP COLUMN column_name;
    

5. 數據操作

5.1 插入數據

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

示例：

INSERT INTO students (id, name, age, enrollment_date) VALUES (1, 'John Doe', 20, '2023-09-01');

5.2 查詢數據

• 基本查詢

  SELECT column1, column2, ... FROM table_name;
  

  示例：

  SELECT name, age FROM students;
  

• 查詢所有列

  SELECT * FROM table_name;
  

• 使用條件過濾查詢

  SELECT column1, column2, ... FROM table_name WHERE condition;
  

  示例：

  SELECT * FROM students WHERE age > 18;
  

• 排序

  SELECT * FROM table_name ORDER BY column1 [ASC|DESC];
  

  示例：

  SELECT * FROM students ORDER BY age DESC;
  

• 限制查詢結果

  SELECT * FROM table_name LIMIT number;
  

  示例：

  SELECT * FROM students LIMIT 5;
  

• 聚合函數

  • COUNT()：返回記錄數
  • SUM()：返回總和
  • AVG()：返回平均值
  • MIN()：返回最小值
  • MAX()：返回最大值

  示例：

  SELECT COUNT(*) FROM students;
  

5.3 更新數據

UPDATE table_name SET column1 = value1, column2 = value2 WHERE condition;

示例：

UPDATE students SET age = 21 WHERE id = 1;

5.4 刪除數據

DELETE FROM table_name WHERE condition;

示例：

DELETE FROM students WHERE id = 1;

6. 連接 (JOIN) 操作

• 內連接 (INNER JOIN): 返回兩個表中匹配的記錄。

  SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column;
  

• 左連接 (LEFT JOIN): 返回左表中的所有記錄，即使右表中沒有匹配。

  SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;
  

• 右連接 (RIGHT JOIN): 返回右表中的所有記錄，即使左表中沒有匹配。

  SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;
  

7. 索引 (Index)

索引用於提高查詢速度。

• 創建索引

  CREATE INDEX index_name ON table_name (column_name);
  

• 刪除索引

  DROP INDEX index_name ON table_name;
  

8. 事務 (Transaction)

事務確保一組 SQL 操作要麼全部執行，要麼全部不執行。

• 開始事務

  START TRANSACTION;
  

• 提交事務

  COMMIT;
  

• 回滾事務

  ROLLBACK;
  

9. 視圖 (View)

視圖是基於 SQL 查詢的虛擬表。

• 創建視圖

  CREATE VIEW view_name AS SELECT columns FROM table_name WHERE condition;
  

• 刪除視圖

  DROP VIEW view_name;
  

10. 存儲過程 (Stored Procedure)

存儲過程是一組預編譯的 SQL 語句，封裝起來可以復用。

• 創建存儲過程

  CREATE PROCEDURE procedure_name (parameters)
  BEGIN
    SQL_statements;
  END;
  

• 調用存儲過程

  CALL procedure_name(parameters);
  

• 刪除存儲過程

  DROP PROCEDURE procedure_name;
  

11. 觸發器 (Trigger)

觸發器在某個事件（如插入、更新或刪除）發生時自動執行。

• 創建觸發器

  CREATE TRIGGER trigger_name BEFORE|AFTER INSERT|UPDATE|DELETE ON table_name
  FOR EACH ROW
  BEGIN
    SQL_statements;
  END;
  

• 刪除觸發器

  DROP TRIGGER trigger_name;
  

12. 許可權管理

• 創建用戶

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

• 賦予許可權

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

• 撤銷許可權

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

• 刪除用戶

  DROP USER 'username'@'host';
  

13. 常用工具

• EXPLAIN: 查看查詢的執行計劃。

  EXPLAIN SELECT * FROM table_name;
  

• SHOW PROCESSLIST: 查看當前運行的 SQL 查詢。

  SHOW PROCESSLIST;
  

這是 MySQL 的完整語法概述，適用於基本資料庫操作、數據查詢、表結構管理、索引、事務、存儲過程等內容。通過學習和使用這些語法，能夠有效管理和查詢資料庫。