MySQL指令(新增、修改、刪除、查詢)

發表於 更新於 分類於 閱讀次數： Disqus： 文章字數： 968

前言

學習MySQL資源有許多，在youtube也有許多影片教學。在這會紀錄我在GrandmaCan - 我阿嬤都會與菜鳥教程中學習到的指令與紀錄

MySQL結構是n個資料庫 -> n個表格
指令大致可以區分新增、修改、刪除、查詢四大功能，其中關鍵字使用大寫、變數使用 `` 包圍，並且使用 ; 結尾。當然，使用小寫直接撰寫也不會影響程式的執行。

資料庫(Database)

1
2
3
4
SHOW DATABASES; -- 顯示所有資料庫
CREATE DATABASE `database_name`; -- 創建"database_name"資料庫
DROP DATABASE `database_name`; -- 刪除"database_name"資料庫
USE `database_name`;

表格(Table)

1
2
3
4
5
6
7
8
9
10
11
SHOW TABLES;    -- 顯示所有表格
CREATE TABLE `table_name`(  -- 創建"table_name"表格
    `int_variable_name` INT,    -- 整數
    `float_variable_name` DECIMAL(3,2), -- 共'3'位數, 小數點在第'2'位 的浮點數
    `string_variable_name` VARCHAR(10), -- '10'位元的字串
    `date_variable_name` DATE,    -- 'YYYY-MM-DD' 日期
    `time_variable_name` TIMESTAMP, -- 'YYYY-MM-DD HH:MM:SS' 日期+時間
    `file_variable_name` BLOB   -- (Binary Large Object)圖片, 影片, 檔案
);
DESCRIBE `table_name`;  -- 顯示表格資訊
DROP TABLE `table_name`;    -- 刪除"table_name"表格

創建並關聯表格

創建一個四個屬性的”學生”表格

學號 姓名 主修 GPA 成績
001 李白 文學 1.92 90 
1
2
3
4
5
6
7
CREATE TABLE `student`(
	`student_id` INT PRIMARY KEY AUTO_INCREMENT, -- 主鍵, 自動補齊順序
    `name` VARCHAR(20) UNIQUE, -- 不可重複 NOT 
    `major` VARCHAR(20) NULL, -- 不可為 NULL
    `gpa` DECIMAL(3,2) DEFAULT 0 -- 預設值為 0
    `score` INT
);

更改”學生”表格

1
2
ALTER TABLE `student` ADD `note` VARCHAR(20);   -- 新增"備註"屬性
ALTER TABLE `student` DROP COLUMN `note`    -- 刪除"備註"屬性

新增、更改、刪除數據

1
2
3
4
5
6
7
8
9
10
INSERT INTO `student` VALUES(001, "李白", "文學", 1.92, 90);
INSERT INTO `student`(`student_id`, `name`, `major`, `score`) VALUES(002, "郭台銘", "商管", 60); -- 更改輸入時的順序

UPDATE `student`    -- 更新"學生"表格, 若"name"為郭台銘, "gpa"改為4.0
SET `gpa` = 4.0
WHERE `name` = "郭台銘";

DELETE FROM `student`   -- 刪除"學生"表格, 若"student_id"為2, 刪除該筆資料
WHERE `student_id` = 2;
DELETE FROM `student`;  -- 刪除"學生"表格內所有數據

查詢數據

1
2
3
4
5
6
SELECT * FROM `student`;    -- 查詢"studnet"所有數據

SELECT `name` FROM `student`    -- 查詢"student"中的"name"
WHERE `score` > 60 AND `gpa` > 1 -- 條件為...
ORDER BY `student_id` DESC  -- 依學號高至低(預設為ASC)
LIMIT 3;    -- 限制前三筆資料
聚合函數(Aggregate Functions)
1
2
SELECT COUNT(*) FROM `student`;	-- 取得`student`資料筆數量
-- SUM: 加總, AVG: 平均數, MAX: 最大值, MIN: 最小值

另外聚合函數也可搭配分組(Group By),條件篩選(Having) + 聚合函數使用

萬用字元(Wildcards)
1
2
3
SELECT * FROM `student`
WHERE `name` LIKE "李%";    -- 條件為"李..."
-- %: 多個字元, _: 一個字元
關聯

關聯是將兩個有相同屬性的表格連接在一起，這裡我們先新建兩個新的表格

1. 學生表格
學生編號 學生姓名 課程編號
s001 李帽 c005
s002 楊梅 c001
s003 黃尚 c003
2. 課程表格
課程編號 教師編號 教師姓名
c001 t001 寶傑
c003 t002 達叔
創建並關聯兩表格 
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE `student`(
    `sno` VARCHAR(10) PRIMARY KEY,
    `sname` VARCHAR(20),
    `cno` VARCHAR(10),
);
CREATE `course`(
    `cno` VARCHAR(10) PRIMARY KEY,
    `tno` VARCHAR(10),
    `tname` VARCHAR(20),
);
ALTER TABLE `student`
ADD FOREIGN KEY(`cno`)  -- 設定新增"cno"為外鍵
REFERENCES `course`(`cno`) ON DELETE SET NULL;   -- 對映至"course"的"cno", 若student資料刪除時, 對映到NULL
1
2
3
4
5
6
SELECT * FROM `student`
JOIN `course`   -- 關聯"course"表格
ON `student`.`cno` = `course`.`cno`;    -- 關聯條件為...
-- 此方法會將兩表格"cno"同時列出

-- USING(`cno`) -- 關聯條件相同, 只列出一列
聯集
1
2
3
4
SELECT `sname` FROM `student`
UNION
SELECT `tname` FROM `course`;
-- 將學生姓名與教師姓名列出
子查詢
1
2
3
4
5
SELECT * FROM `student` -- 3. 列出全部資料
WHERE `cno` = ( -- 4. 條件為...
    SELECT `cno` FROM `course`  -- 1. 列出"cno"
    WHERE `tname` = "寶傑"  -- 2. 條件為...
);