MySQL創(chuàng)建數(shù)據(jù)庫、表、以及增刪改查數(shù)據(jù)

目錄

1 MySQL基本概念

2 建庫

3 建表

4 增刪改查

5 復(fù)雜查詢

1 MySQL基本概念

MySQL是一種開源的關(guān)系型數(shù)據(jù)庫管理系統(tǒng)（RDBMS），它是目前最流行的數(shù)據(jù)庫之一。下面是MySQL數(shù)據(jù)庫的一些基本概念：

1. 數(shù)據(jù)庫（Database）：數(shù)據(jù)庫是一個(gè)用于存儲(chǔ)和組織數(shù)據(jù)的容器。它由一組表（Tables）和其他相關(guān)對象組成。
2. 表（Table）：表是MySQL數(shù)據(jù)庫中的主要對象，用于存儲(chǔ)數(shù)據(jù)。它由一組行（Rows）和列（Columns）組成。每個(gè)表都有一個(gè)唯一的名稱，并且包含一個(gè)定義了各列名稱、數(shù)據(jù)類型和約束的結(jié)構(gòu)。
3. 列（Column）：列是表中的一個(gè)數(shù)據(jù)字段，用于存儲(chǔ)特定類型的數(shù)據(jù)。每個(gè)列都有一個(gè)名稱和數(shù)據(jù)類型，例如整數(shù)、字符、日期等。
4. 行（Row）：行是表中的一個(gè)記錄，它包含了表中每個(gè)列的具體值。每行都有一個(gè)唯一的標(biāo)識符，稱為主鍵（Primary Key），用于唯一標(biāo)識該行。
5. 主鍵（Primary Key）：主鍵是表中用于唯一標(biāo)識每行的列或列的組合。它確保了表中的每個(gè)行都有唯一的標(biāo)識符。
6. 外鍵（Foreign Key）：外鍵是一個(gè)表中的列，它與另一個(gè)表的主鍵相關(guān)聯(lián)。通過外鍵，可以在多個(gè)表之間建立關(guān)聯(lián)，實(shí)現(xiàn)數(shù)據(jù)的引用完整性和一致性。
7. 索引（Index）：索引是一種數(shù)據(jù)結(jié)構(gòu)，用于提高查詢效率。它可以根據(jù)一個(gè)或多個(gè)列的值快速定位到表中的特定行。
8. 查詢（Query）：查詢是從數(shù)據(jù)庫中檢索數(shù)據(jù)的操作。使用結(jié)構(gòu)化查詢語言（SQL），可以編寫查詢語句來執(zhí)行各種操作，如插入、更新、刪除和查詢數(shù)據(jù)。
9. 視圖（View）：視圖是基于一個(gè)或多個(gè)表的查詢結(jié)果構(gòu)建的虛擬表。它可以簡化復(fù)雜的查詢操作，并提供了一個(gè)更簡潔、易讀的數(shù)據(jù)訪問接口。
10. 觸發(fā)器（Trigger）：觸發(fā)器是與表相關(guān)聯(lián)的特殊類型的存儲(chǔ)過程。它在特定的數(shù)據(jù)庫操作（如插入、更新、刪除）前后自動(dòng)執(zhí)行一系列定義的操作。
11. 函數(shù)（Function）：函數(shù)是一段可重用的代碼塊，接受輸入?yún)?shù)并返回一個(gè)值。MySQL提供了許多內(nèi)置函數(shù)，用于處理和操作數(shù)據(jù)，例如數(shù)學(xué)函數(shù)（如SUM、AVG、MAX、MIN）、字符串函數(shù)（如CONCAT、SUBSTRING、UPPER）和日期函數(shù)（如NOW、DATE_FORMAT）等。
12. 存儲(chǔ)過程（Stored Procedure）：存儲(chǔ)過程是一組預(yù)編譯的SQL語句和控制結(jié)構(gòu)，作為一個(gè)單元存儲(chǔ)在數(shù)據(jù)庫中。存儲(chǔ)過程可以接受輸入?yún)?shù)，并可以執(zhí)行復(fù)雜的邏輯和數(shù)據(jù)操作。它們提供了一種將常用操作封裝起來并在需要時(shí)進(jìn)行調(diào)用的方法，可以提高性能和代碼重用性。

2 建庫

在MySQL中，創(chuàng)建數(shù)據(jù)庫的語法使用CREATE DATABASE語句。以下是創(chuàng)建數(shù)據(jù)庫的基本語法：

CREATE DATABASE database_name;

其中，database_name是要?jiǎng)?chuàng)建的數(shù)據(jù)庫的名稱。請注意，數(shù)據(jù)庫名稱通常要遵循命名規(guī)范，如只使用字母、數(shù)字和下劃線，且以字母開頭。

除了基本語法之外，還可以使用一些可選參數(shù)來自定義數(shù)據(jù)庫的屬性。以下是創(chuàng)建數(shù)據(jù)庫時(shí)可用的一些常見選項(xiàng)：

• 字符集（Character Set）：指定數(shù)據(jù)庫中存儲(chǔ)的字符編碼方式，默認(rèn)為服務(wù)器的默認(rèn)字符集。常見的字符集包括UTF8、UTF8mb4等。

CREATE DATABASE database_name CHARACTER SET utf8;

• 排序規(guī)則（Collation）：指定字符排序規(guī)則，用于比較和排序字符串。默認(rèn)為字符集的默認(rèn)排序規(guī)則。

CREATE DATABASE database_name COLLATE utf8_general_ci;

• 數(shù)據(jù)庫選項(xiàng)（Database Options）：其他可選的數(shù)據(jù)庫選項(xiàng)，如設(shè)置訪問權(quán)限、存儲(chǔ)引擎等。

CREATE DATABASE database_name
 ?CHARACTER SET utf8
 ?COLLATE utf8_general_ci
 ?/* other options */;

創(chuàng)建數(shù)據(jù)庫后，可以使用USE語句切換到新創(chuàng)建的數(shù)據(jù)庫，開始在其中創(chuàng)建表和插入數(shù)據(jù)：

USE database_name;

當(dāng)數(shù)據(jù)庫不再使用時(shí)，可以使用DROP DATABASSE語句刪除數(shù)據(jù)庫，此操作需慎重：

DROP DATABASE database_name;

3 建表

在MySQL中，創(chuàng)建表的語法使用CREATE TABLE語句。以下是創(chuàng)建表的基本語法：

CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ...
);

其中，table_name是要?jiǎng)?chuàng)建的表的名稱。括號內(nèi)的部分定義了表的列以及每個(gè)列的數(shù)據(jù)類型。

在列定義中，需要指定列名和數(shù)據(jù)類型。數(shù)據(jù)類型定義了該列可以存儲(chǔ)的數(shù)據(jù)類型，如整數(shù)、字符、日期等。常見的數(shù)據(jù)類型包括：

• 整數(shù)類型（Integer Types）：如INT、BIGINT、TINYINT等。
• 字符串類型（String Types）：如VARCHAR、CHAR、TEXT等。
• 小數(shù)類型（Decimal Types）：如DECIMAL、FLOAT、DOUBLE等。
• 日期和時(shí)間類型（Date and Time Types）：如DATE、DATETIME、TIMESTAMP等。

以下是一個(gè)簡單的示例，演示如何創(chuàng)建一個(gè)包含幾個(gè)常見列的表：

CREATE TABLE employees (
  id INT,
  name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE
);

show create table employees;

上述示例創(chuàng)建了一個(gè)名為employees的表，包含了id、name、department、salary和hire_date這幾個(gè)列。

在定義列時(shí)，還可以添加其他約束條件，如主鍵約束、唯一約束、非空約束等。例如，以下示例在創(chuàng)建表時(shí)指定了id列作為主鍵：

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  department VARCHAR(50),
  salary DECIMAL(10,2),
  hire_date DATE
);

除了列定義外，還可以在CREATE TABLE語句中指定其他選項(xiàng)，如存儲(chǔ)引擎、字符集、排序規(guī)則等。這些選項(xiàng)可以根據(jù)具體需求進(jìn)行自定義設(shè)置。

當(dāng)數(shù)據(jù)表不再使用時(shí)，可以使用DROP TABLE語句刪除數(shù)據(jù)表：

DROP TABLE table_name;

4 增刪改查

基于上述示例的建表語句，以下是MySQL中常用的增刪改查（CRUD）語法示例：

1.插入數(shù)據(jù)（Insert）：

插入一行數(shù)據(jù)到employees表中：

INSERT INTO employees (id, name, department, salary, hire_date)
VALUES 
 ?(1, 'John Doe', 'Sales', 5000.00, '2022-01-01'),
 ?(2, 'Jane Smith', 'Marketing', 6000.00, '2022-02-01'),
 ?(3, 'Mike Johnson', 'IT', 5500.00, '2022-03-01'),
 ?(4, 'Sarah Davis', 'Sales', 4500.00, '2022-04-01'),
 ?(5, 'Tom Wilson', 'IT', 5200.00, '2022-05-01');

2.查詢數(shù)據(jù)（Select）：

查詢所有員工的信息：

SELECT * FROM employees;

查詢特定部門的員工信息：

SELECT * FROM employees WHERE department = 'Sales';

3.更新數(shù)據(jù)（Update）：

更新員工的薪資：

UPDATE employees SET salary = 5500.00 WHERE id = 1;

4.刪除數(shù)據(jù)（Delete）：

刪除特定員工的記錄：

DELETE FROM employees WHERE id = 1;

刪除全部記錄：

DELETE FROM employees ;

或

TRUNCATE TABLE employees;

5 復(fù)雜查詢

為了提供更復(fù)雜的查詢示例，需要擴(kuò)展示例數(shù)據(jù)庫，添加一個(gè)新的表departments來演示聚合函數(shù)、排序、分組以及不同類型的連接（inner join、left join、right join）的使用。

以下是擴(kuò)展的建表語句：

CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  location VARCHAR(100),
  manager VARCHAR(50)
);

插入示例數(shù)據(jù)：

INSERT INTO departments (id, name, location, manager)
VALUES
 ?(1, 'Sales', 'New York', 'John Smith'),
 ?(2, 'Marketing', 'London', 'Jane Doe'),
 ?(3, 'IT', 'San Francisco', 'Mike Johnson');

下面是一些復(fù)雜查詢的示例：

1.使用聚合函數(shù)（Aggregate Functions）：

• 計(jì)算員工表中的平均薪資：

SELECT AVG(salary) AS average_salary FROM employees;

2.排序（Sorting）：

• 按照薪資從高到低對員工表進(jìn)行排序：

SELECT * FROM employees ORDER BY salary DESC;

注：ASC是升序。

3.分組（Grouping）：

• 按照部門對員工表進(jìn)行分組，并計(jì)算每個(gè)部門的平均薪資：

SELECT department, AVG(salary) AS average_salary FROM employees GROUP BY department;

4.內(nèi)連接（Inner Join）：

• 使用內(nèi)連接獲取員工表和部門表中相關(guān)聯(lián)的數(shù)據(jù)：

SELECT e.*,d.*
FROM employees e
INNER JOIN departments d ON e.department = d.name;

5.左連接（Left Join）：

• 使用左連接獲取員工表中的所有記錄和關(guān)聯(lián)的部門信息（如果有）：

INSERT INTO employees (id, name, department, salary, hire_date)
VALUES (6, 'Alex Johnson', 5, 4800, '2022-06-01');


SELECT e.*,d.*
FROM employees e
LEFT JOIN departments d ON e.department = d.name;

6.右連接（Right Join）：

• 使用右連接獲取部門表中的所有記錄和關(guān)聯(lián)的員工信息（如果有）：

INSERT INTO departments (id, name, location, manager)
VALUES (4, 'Finance', 'Chicago', 'David Brown');

SELECT e.*,d.*
FROM employees e
RIGHT JOIN departments d ON e.department = d.name;