mysql 常用知識點總結

小程xy發表於2024-09-16
MySql

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

1. 基礎概念

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

2. 常見資料型別

資料型別 描述
INT 整數
FLOAT 浮點數
VARCHAR 變長字串
CHAR 固定長度字串
TEXT 大文字
DATE 日期（格式：YYYY-MM-DD）
DATETIME 日期和時間（格式：YYYY-MM-DD HH:MM:SS）

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 的完整語法概述，適用於基本資料庫操作、資料查詢、表結構管理、索引、事務、儲存過程等內容。透過學習和使用這些語法，能夠有效管理和查詢資料庫。

相關文章