HsienLu's Blog

2024/1/18

MySQL SQL

MySQL-事務(Transaction)

簡介: 事務(Transaction)是一個邏輯上的工作單位，它可以包含一個或多個 SQL 語句

簡介: 事務(Transaction)是一個邏輯上的工作單位，它可以包含一個或多個 SQL 語句。事務可以讓一組 SQL 語句作為一個單元來執行，這些語句要麼全部執行，要麼全部不執行。事務是一個不可分割的工作執行單元，事務中包含的 SQL 語句要麼全部執行，要麼全部不執行，事務在執行過程中發生錯誤，會被回滾(Rollback)到事務開始前的狀態，事務在執行過程中沒有發生錯誤，會被提交(Commit)。

事務操作

開啟事務

START TRANSACTION;
-- 或
BEGIN;

提交事務

COMMIT;

回滾事務

ROLLBACK;

事務特性(ACID)

原子性(Atomicity)

事務是一個不可分割的工作執行單元，事務中包含的 SQL 語句要麼全部執行，要麼全部不執行，事務在執行過程中發生錯誤，會被回滾(Rollback)到事務開始前的狀態，事務在執行過程中沒有發生錯誤，會被提交(Commit)。

一致性(Consistency)

事務在執行過程中，數據庫會從一個一致性狀態轉換到另一個一致性狀態，事務開始前和結束後，數據庫的完整性約束沒有被破壞。

隔離性(Isolation)

事務的隔離性是指多個事務並發執行時，一個事務的執行不會被其他事務干擾，多個事務並發執行時，一個事務內部的操作及使用的數據對其他事務是隱藏的，其他事務的操作及使用的數據對該事務也是隱藏的。

持久性(Durability)

事務完成後(提交或是回滾)，事務對數據的修改被永久保存到數據庫中，即使發生故障也不會丟失。

範例

create table account(
id int primary key auto_increment,
name varchar(20),
money int

) ;

insert into account(name,money) values
("AAA",2000),
("BBB",3000);

begin;
SET SQL_SAFE_UPDATES = 0; -- 關閉安全模式
update account set money=money-500 where name="AAA";
update account set money=money+500 where name="BBB";
SET SQL_SAFE_UPDATES = 1; -- 開啟安全模式
-- commit; 預設式自動提交在mysql中
rollback;

2024/1/18

MySQL SQL

MySQL-約束條件

MySQL 約束條件

約束概念和分類

概念:約束條件是一種限制條件，用於限制變數的取值範圍，使得變數在取值時必須滿足某種條件。
約束分類表格

約束分類	約束名稱	約束說明
NOT NULL	非空約束	限制變數不能為空值
UNIQUE	唯一約束	限制變數不能重複
PRIMARY KEY	主鍵約束	限制變數不能為空值且不能重複
CHECK	檢查約束	限制變數必須滿足某種條件
DEFAULT	默認約束	限制變數沒有給定值時，默認為某個值
FOREIGN KEY	外鍵約束	限制變數的值必須在另一個表中存在

外鍵約束

概念:外鍵約束是一種約束條件，用於限制變數的值必須在另一個表中存在。
外鍵約束的語法格式

CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵欄位名稱) REFERENCES 另一個表名稱(另一個表的欄位名稱)

外鍵約束的語法範例

create table dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int ,
constraint fk_emp_dept foreign key(dep_id)
references dept(id)
);

insert into dept (dep_name,addr) values
("RD","台北"),
("ME","台中");
insert into emp (NAMe,age,dep_id) values
("AAA",20,1),
("BBB",20,1),
("CCC",20,1),
("DDD",20,2),
("EEE",20,2),
("FFF",20,2);

主表要先建立，外鍵表要後建立，否則會報錯

2024/1/18

MySQL SQL

MySQL-關聯資料表查詢

inner join、outer join、子查詢

inner join

解釋: 用來查詢兩個表格中有關聯的資料，分成兩種寫法

SELECT * FROM table1, table2 WHERE table1.id = table2.id;

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;

相當於取交集的意思

範例

-- 建立資料
create table dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int ,
constraint fk_emp_dept foreign key(dep_id)
references dept(id)
);
insert into dept (dep_name,addr) values
("RD","台北"),
("ME","台中");

insert into emp (NAMe,age,dep_id) values
("AAA",20,1),
("BBB",20,1),
("CCC",20,1),
("DDD",20,2),
("EEE",20,2),
("FFF",20,2);
-- 連接語法開始
select * from emp , dept where emp.dep_id = dept.id;
-- 另外一種寫法
-- SELECT * FROM emp JOIN dept ON dept.id = emp.dep_id;

outer join

解釋: 用來查詢兩個表格中有關聯的資料,並且會把沒有關聯的資料也查詢出來，又分成左外連結、右外連結、全外連結

left join

解釋: 用來查詢兩個表格中有關聯的資料,並且會把沒有關聯的資料也查詢出來，但是以左邊的表格為主

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id;

取左邊表格的資料，並且把右邊表格的資料也查詢出來，如右邊沒有資料則顯示 null

範例

create table dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int
);
insert into dept (dep_name,addr) values
("RD","台北"),
("ME","台中");

insert into emp (NAMe,age,dep_id) values
("AAA",20,1),
("BBB",20,1),
("CCC",20,1),
("DDD",20,2),
("EEE",20,2),
("FFF",20,2),
("GGG",23,3);
select * from emp left join dept on emp.dep_id=dept.id;

right join

解釋: 用來查詢兩個表格中有關聯的資料,並且會把沒有關聯的資料也查詢出來，但是以右邊的表格為主

SELECT * FROM table1 RIGHT JOIN table2 ON table1.id = table2.id;

取右邊表格的資料，並且把左邊表格的資料也查詢出來，如左邊沒有資料則顯示 null

範例

create table dept(
id int primary key auto_increment,
dep_name varchar(20),
addr varchar(20)
);
create table emp(
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int
);
insert into dept (dep_name,addr) values
("RD","台北"),
("ME","台中"),
("QA","高雄"),
("EE","台南");

insert into emp (NAMe,age,dep_id) values
("AAA",20,1),
("BBB",20,1),
("CCC",20,1),
("DDD",20,2),
("EEE",20,2),
("FFF",20,2),
("GGG",23,3);
select * from emp right join dept on emp.dep_id=dept.id;

子查詢

解釋:重點是利用( )去包住查詢結果，並進一步的在外面加上查詢語法
基本語法

SELECT * FROM table1 WHERE table1.id IN (SELECT table2.id FROM table2);

2024/1/18

MySQL SQL

MySQL-SQL語法介紹-DQL 資料查詢語言

DQL(Data Query Language) 資料查詢語言

基礎查詢資料

查詢資料

SELECT 欄位名1, 欄位名2, ... FROM 資料表名 WHERE 條件;

去除重複資料

SELECT DISTINCT 欄位名1, 欄位名2, ... FROM 資料表名 WHERE 條件;

取別名

SELECT 欄位名1 AS 別名1, 欄位名2 AS 別名2, ... FROM 資料表名 WHERE 條件;

條件查詢

基本語法

SELECT 欄位名1, 欄位名2, ... FROM 資料表名 WHERE 條件;

條件查詢關鍵字表格

關鍵字	說明
>	大於
<	小於
>=	大於等於
<=	小於等於
=	等於
!=	不等於
<>	不等於
BETWEEN…AND…	在…之間
IN(…)	在…群組之中
LIKE	模糊查詢
IS NULL	為空值
IS NOT NULL	不為空值
AND	且
OR	或
NOT	非

條件查詢範例

SELECT * FROM 資料表名 WHERE 欄位名1 > 100;
SELECT * FROM 資料表名 WHERE 欄位名1 BETWEEN 100 AND 200;
SELECT * FROM 資料表名 WHERE 欄位名1 IN(100, 200, 300);
SELECT * FROM 資料表名 WHERE 欄位名1 LIKE 'A%';
SELECT * FROM 資料表名 WHERE 欄位名1 IS NULL;
SELECT * FROM 資料表名 WHERE 欄位名1 IS NOT NULL;
SELECT * FROM 資料表名 WHERE 欄位名1 > 100 AND 欄位名2 < 200;
SELECT * FROM 資料表名 WHERE 欄位名1 > 100 OR 欄位名2 < 200;
SELECT * FROM 資料表名 WHERE NOT 欄位名1 > 100;

like 模糊查詢介紹

XX%XX 代表任意長度的字串

SELECT * FROM 資料表名 WHERE 欄位名1 LIKE '%A%';

XX_XX 代表任意一個字元

SELECT * FROM 資料表名 WHERE 欄位名1 LIKE 'A_';

排序查詢

排序查詢語法

SELECT 欄位名1, 欄位名2, ... FROM 資料表名 WHERE 條件 ORDER BY 欄位名1 ASC/DESC, 欄位名2 ASC/DESC, ...;

如果有多個欄位，會先以第一個欄位排序，如果第一個欄位有相同的值，再以第二個欄位排序，以此類推

聚合函數語法

聚合函數	說明
COUNT(欄位名)	計算欄位名的總數
SUM(欄位名)	計算欄位名的總和
AVG(欄位名)	計算欄位名的平均值
MAX(欄位名)	計算欄位名的最大值
MIN(欄位名)	計算欄位名的最小值

所有聚合函數都會忽略 NULL 值

聚合函數範例

SELECT COUNT(欄位名1), SUM(欄位名2), AVG(欄位名3), MAX(欄位名4), MIN(欄位名5) FROM 資料表名;

分組查詢語法

SELECT 欄位名1, 聚合函數1, 聚合函數2, ... FROM 資料表名 GROUP BY 欄位名1;

分組查詢範例(搭配 where)

SELECT 欄位名1, 聚合函數1, 聚合函數2, ... FROM 資料表名 WHERE 條件 GROUP BY 欄位名1;

分組查詢範例(搭配 having)

SELECT 欄位名1, 聚合函數1, 聚合函數2, ... FROM 資料表名 GROUP BY 欄位名1 HAVING 條件;

執行順序: WHERE -> 聚合函數 -> HAVING

分頁查詢語法

SELECT 欄位名1, 欄位名2, ... FROM 資料表名 LIMIT 起始位置, 查詢筆數;

起始索引計算方式= (頁數 - 1) * 每頁筆數

分頁查詢範例

SELECT * FROM 資料表名 LIMIT 0, 10;

LIMIT 是 MySQL 的語法，不是標準 SQL 語法，不同的資料庫可能會有不同的寫法

DQL 小結

SELECT 欄位名1, 欄位名2, ...
FROM 資料表名
WHERE 條件
GROUP BY 欄位名1
HAVING 條件
ORDER BY 欄位名1 ASC/DESC, 欄位名2 ASC/DESC, ...
LIMIT 起始位置, 查詢筆數;

2024/1/18

MySQL SQL

MySQL-SQL語法介紹-DML 資料定義語言

DML(Data Manipulation Language) 資料操作語言

給指定欄位新增資料

INSERT INTO 資料表名(欄位名1, 欄位名2, ...) VALUES(值1, 值2, ...);

修改資料

UPDATE 資料表名 SET 欄位名1=值1, 欄位名2=值2, ... WHERE 條件;

WHERE 條件可以不寫，但是如果不寫，會將資料表所有資料都修改

刪除資料

DELETE FROM 資料表名 WHERE 條件;

WHERE 條件可以不寫，但是如果不寫，會將資料表所有資料都刪除

2024/1/18

MySQL SQL

MySQL-SQL語法介紹-DDL 資料定義語言

DDL(Data Definition Language) 資料定義語言

操作資料庫

####查詢資料庫

SHOW DATABASES;

####新增資料庫

CREATE DATABASE 資料庫名;

####刪除資料庫

DROP DATABASE 資料庫名;

####使用資料庫

USE 資料庫名;

####查看目前使用的數據庫

SELECT DATABASE();

操作資料表

查詢所有資料表

SHOW TABLES;

查詢資料表結構

DESC 資料表名;

新增資料表

CREATE TABLE 資料表名(
    欄位名1 資料型態1,
    欄位名2 資料型態2,
    ...
);

刪除資料表

DROP TABLE 資料表名;

修改資料表

####修改資料表名稱

ALTER TABLE 資料表名 RENAME 新資料表名;

####新增欄位

ALTER TABLE 資料表名 ADD 欄位名 資料型態;

####修改資料類型

ALTER TABLE 資料表名 MODIFY 欄位名 新資料型態;

####修改資料表欄位名稱和資料類型

ALTER TABLE 資料表名 CHANGE 欄位名 新欄位名 新資料型態;

####刪除欄位

ALTER TABLE 資料表名 DROP 欄位名;

部落格

MySQL-事務(Transaction)

事務操作

開啟事務

提交事務

回滾事務

事務特性(ACID)

原子性(Atomicity)

一致性(Consistency)

隔離性(Isolation)

持久性(Durability)

範例

MySQL-約束條件

約束概念和分類

外鍵約束

MySQL-關聯資料表查詢

inner join

範例

outer join

left join

範例

right join

範例

子查詢

MySQL-SQL語法介紹-DQL 資料查詢語言

基礎查詢資料

查詢資料

去除重複資料

取別名

條件查詢

基本語法

條件查詢關鍵字表格

條件查詢範例

like 模糊查詢介紹

排序查詢

排序查詢語法

聚合函數語法

聚合函數範例

分組查詢語法

分組查詢範例(搭配 where)

分組查詢範例(搭配 having)

分頁查詢語法

分頁查詢範例

DQL 小結

MySQL-SQL語法介紹-DML 資料定義語言

給指定欄位新增資料

修改資料

刪除資料

MySQL-SQL語法介紹-DDL 資料定義語言

操作資料庫

操作資料表

查詢所有資料表

查詢資料表結構

新增資料表

刪除資料表

修改資料表