個人所有文章整理在此篇，將陸續更新收錄:知無涯，行者之路莫言終（我的程式設計之路）
零、結構化查詢語言：SQL(Structured Query Language)
DDL 資料定義語言 管理庫，表
DML 資料操作語言 增刪改查
DCL 資料控制語言 資料控制，許可權訪問等
複製程式碼準備活動：建立庫和表
CREATE DATABASE datatype;
USE datatype;
CREATE TABLE type_number(
type CHAR(12),
byte TINYINT UNSIGNED,
range_singed VARCHAR(20),
range_unsinged VARCHAR(20),
info VARCHAR(40)
);
複製程式碼目前狀態：
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| datatype |
| information_schema |
| mycode |
| mysql |
| performance_schema |
| seckill |
+--------------------+
mysql> USE datatype;
Database changed
mysql> SHOW TABLES;
+--------------------+
| Tables_in_datatype |
+--------------------+
| type_number |
+--------------------+
mysql> DESC type_number;
+----------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+-------+
| type | char(12) | YES | | NULL | |
| byte | tinyint(3) unsigned | YES | | NULL | |
| range_singed | varchar(20) | YES | | NULL | |
| range_unsinged | varchar(20) | YES | | NULL | |
| info | varchar(40) | YES | | NULL | |
+----------------+---------------------+------+-----+---------+-------+
複製程式碼一、DML 資料庫記錄操作 LEVEL 1
LEVEL 1先簡單掌握一下下面的用法
1、記錄的插入操作
INSERT INTO <表名> (屬性,...) VALUES (值,...),...;
|-- 插入一條資料 INSERT INTO <表名> (屬性,...) VALUES (值,...);
INSERT INTO
type_number(type,byte,range_singed,range_unsinged,info)
VALUES
('TINYINT',1,'-2⁷ ~ 2⁷-1','0 ~ 2⁸-1','很小整數');
|-- 查詢所有 SELECT * FROM <表名>;
mysql> SELECT * FROM type_number;
+---------+------+----------------+----------------+--------------+
| type | byte | range_singed | range_unsinged | info |
+---------+------+----------------+----------------+--------------+
| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 很小整數 |
+---------+------+----------------+----------------+--------------+
|-- 你也可以一次，插入多條資料
INSERT INTO
type_number(type,byte,range_singed,range_unsinged,info)
VALUES
('TINYINT',1,'-2⁷ ~ 2⁷-1','0 ~ 2⁸-1','很小整數'),
('SMALLINT',2,'-2¹⁶ ~ 2¹⁶-1','0 ~ 2¹⁶-1','小整數'),
('MEDIUMINT',3,'-2²⁴ ~ 2²⁴-1','0 ~ 2²⁴-1','中等整數'),
('INT',4,'-2³² ~ 2³²-1','0 ~ 2³²-1','標準整數'),
('BIGINT',8,'-2⁶⁴ ~ 2⁶⁴-1','0 ~ 2⁶⁴-1','大整數');
mysql> SELECT * FROM type_number;
+-----------+------+----------------------+----------------+--------------+
| type | byte | range_singed | range_unsinged | info |
+-----------+------+----------------------+----------------+--------------+
| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 很小整數 |
| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 很小整數 |
| SMALLINT | 2 | -2¹⁶ ~ 2¹⁶-1 | 0 ~ 2¹⁶-1 | 小整數 |
| MEDIUMINT | 3 | -2²⁴ ~ 2²⁴-1 | 0 ~ 2²⁴-1 | 中等整數 |
| INT | 4 | -2³² ~ 2³²-1 | 0 ~ 2³²-1 | 標準整數 |
| BIGINT | 8 | -2⁶⁴ ~ 2⁶⁴-1 | 0 ~ 2⁶⁴-1 | 大整數 |
+-----------+------+----------------------+----------------+--------------+
複製程式碼2、記錄的更新操作
UPDATE <表名> SET 屬性 = 值,... WHERE 條件;
UPDATE type_number
SET
info='微型整數'
WHERE type = 'TINYINT';
mysql> SELECT * FROM type_number;
+-----------+------+----------------------+----------------+--------------+
| type | byte | range_singed | range_unsinged | info |
+-----------+------+----------------------+----------------+--------------+
| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 微型整數 |
| TINYINT | 1 | -2⁷ ~ 2⁷-1 | 0 ~ 2⁸-1 | 微型整數 |
| SMALLINT | 2 | -2¹⁶ ~ 2¹⁶-1 | 0 ~ 2¹⁶-1 | 小整數 |
| MEDIUMINT | 3 | -2²⁴ ~ 2²⁴-1 | 0 ~ 2²⁴-1 | 中等整數 |
| INT | 4 | -2³² ~ 2³²-1 | 0 ~ 2³²-1 | 標準整數 |
| BIGINT | 8 | -2⁶⁴ ~ 2⁶⁴-1 | 0 ~ 2⁶⁴-1 | 大整數 |
+-----------+------+----------------------+----------------+--------------+
複製程式碼3.記錄的刪除操作
DELETE FROM <表名> WHERE 條件;
|--- 刪除操作
DELETE FROM type_number
WHERE type = 'TINYINT';
mysql> SELECT * FROM type_number;
+-----------+------+----------------------+----------------+--------------+
| type | byte | range_singed | range_unsinged | info |
+-----------+------+----------------------+----------------+--------------+
| SMALLINT | 2 | -2¹⁶ ~ 2¹⁶-1 | 0 ~ 2¹⁶-1 | 小整數 |
| MEDIUMINT | 3 | -2²⁴ ~ 2²⁴-1 | 0 ~ 2²⁴-1 | 中等整數 |
| INT | 4 | -2³² ~ 2³²-1 | 0 ~ 2³²-1 | 標準整數 |
| BIGINT | 8 | -2⁶⁴ ~ 2⁶⁴-1 | 0 ~ 2⁶⁴-1 | 大整數 |
+-----------+------+----------------------+----------------+--------------+
複製程式碼4.記錄的查詢操作
SELECT 屬性,... FROM <表名> WHERE 條件;
mysql>
SELECT
type,range_unsinged
FROM type_number
WHERE byte>=4;
+--------+----------------+
| type | range_unsinged |
+--------+----------------+
| INT | 0 ~ 2³²-1 |
| BIGINT | 0 ~ 2⁶⁴-1 |
+--------+----------------+
複製程式碼二、圖片表pic (LEVER 2)
這個是用來記錄圖片資訊的表，資料準備過程詳見番外篇：
[番外]-練習MySQL沒素材？來一波字串操作
1.建表語句
CREATE TABLE pic(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
pic_path VARCHAR(120) NOT NULL,
pic_length INT UNSIGNED DEFAULT 0,
pic_mime TINYINT UNSIGNED,
pic_width SMALLINT UNSIGNED,
pic_height SMALLINT UNSIGNED
);
|--- id 為主鍵 自增長
|--- pic_path表示名字，不定長度 ，給個VARCHAR 120 吧，差不多夠用吧
|--- 圖片檔案大小不會非常大，給個INT足夠了 ， 給個預設值 0
|--- pic_mime 0 表示 image/png 1表示 image/jpeg 給個最小的
|--- pic_width和pic_height也不會非常大，無符號SMALLINT足夠
複製程式碼2.查詢操作 AS 的作用
|-- 查詢高大於1200畫素的記錄，使用AS 來 臨時更改查詢輸出的屬性名(不會改變實際記錄)
mysql>
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE pic_height>1200;
+----------------------+--------+--------+
| 路徑 | 寬/px | 高/px |
+----------------------+--------+--------+
| 30000X20000.jpg | 30000 | 20000 |
| 3000X2000.jpg | 3000 | 2000 |
| ecNKedygCmSjTWWF.jpg | 700 | 1352 |
| gtQiXnRfkvvTLinw.jpg | 2880 | 2025 |
| HXqqASHJETSlvpnc.jpg | 3600 | 2400 |
| ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 |
| screen.png | 1080 | 1920 |
| XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 |
+----------------------+--------+--------+
複製程式碼3.查詢是屬性可參與運算
|-- CONCAT函式用於連線字串 注意：\需要轉義
mysql>
SELECT
CONCAT('E:\\SpringBootFiles\\imgs\\',pic_path) AS 絕對路徑,
pic_width * pic_height AS '畫素點個數'
FROM pic
WHERE pic_height>1200;
+----------------------------------------------+-----------------+
| 絕對路徑 | 畫素點個數 |
+----------------------------------------------+-----------------+
| E:\SpringBootFiles\imgs\30000X20000.jpg | 600000000 |
| E:\SpringBootFiles\imgs\3000X2000.jpg | 6000000 |
| E:\SpringBootFiles\imgs\ecNKedygCmSjTWWF.jpg | 946400 |
| E:\SpringBootFiles\imgs\gtQiXnRfkvvTLinw.jpg | 5832000 |
| E:\SpringBootFiles\imgs\HXqqASHJETSlvpnc.jpg | 8640000 |
| E:\SpringBootFiles\imgs\ndbMXlwKuCpiiVqC.jpg | 3857868 |
| E:\SpringBootFiles\imgs\screen.png | 2073600 |
| E:\SpringBootFiles\imgs\XQWGrglfjGVuJfzJ.jpg | 2035200 |
+----------------------------------------------+-----------------+
複製程式碼4.WHERE條件的千變萬化
4.1: 條件與 -- AND 和 &&
條件必須全部滿足
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE pic_height>1200 AND
pic_width > 1500;
+----------------------+--------+--------+
| 路徑 | 寬/px | 高/px |
+----------------------+--------+--------+
| 30000X20000.jpg | 30000 | 20000 |
| 3000X2000.jpg | 3000 | 2000 |
| gtQiXnRfkvvTLinw.jpg | 2880 | 2025 |
| HXqqASHJETSlvpnc.jpg | 3600 | 2400 |
| ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 |
+----------------------+--------+--------+
|--- AND 效果等於 &&
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE pic_height>1200 &&
pic_width > 1500;
複製程式碼4.2: 條件或 -- OR 和 ||
條件滿足一個即可
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE pic_height>1200 OR
pic_width > 1500;
+----------------------+--------+--------+
| 路徑 | 寬/px | 高/px |
+----------------------+--------+--------+
| 30000X20000.jpg | 30000 | 20000 |
| 3000X2000.jpg | 3000 | 2000 |
| ecNKedygCmSjTWWF.jpg | 700 | 1352 |
| gtQiXnRfkvvTLinw.jpg | 2880 | 2025 |
| HXqqASHJETSlvpnc.jpg | 3600 | 2400 |
| ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 |
| screen.png | 1080 | 1920 |
| XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 |
+----------------------+--------+--------+
|--- OR 效果等於 ||
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE pic_height>1200 ||
pic_width > 1500;
複製程式碼4.3: 條件非 -- NOT 和 !
對條件取反
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE NOT pic_height < 1200;
+----------------------+--------+--------+
| 路徑 | 寬/px | 高/px |
+----------------------+--------+--------+
| 30000X20000.jpg | 30000 | 20000 |
| 3000X2000.jpg | 3000 | 2000 |
| ecNKedygCmSjTWWF.jpg | 700 | 1352 |
| gtQiXnRfkvvTLinw.jpg | 2880 | 2025 |
| HXqqASHJETSlvpnc.jpg | 3600 | 2400 |
| ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 |
| screen.png | 1080 | 1920 |
| XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 |
+----------------------+--------+--------+
複製程式碼4.4: 散點匹配IN(v1,v2,v3,...)
符合v1,v2,v3,...之一可匹配
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE pic_height IN (1696,2268);
+----------------------+--------+--------+
| 路徑 | 寬/px | 高/px |
+----------------------+--------+--------+
| ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 |
| XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 |
+----------------------+--------+--------+
複製程式碼4.5: 區間匹配BETWEEN v1 AND v2
v1,v2之間可匹配
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE pic_height BETWEEN 1696 AND 2268;
+----------------------+--------+--------+
| 路徑 | 寬/px | 高/px |
+----------------------+--------+--------+
| 3000X2000.jpg | 3000 | 2000 |
| gtQiXnRfkvvTLinw.jpg | 2880 | 2025 |
| ndbMXlwKuCpiiVqC.jpg | 1701 | 2268 |
| screen.png | 1080 | 1920 |
| XQWGrglfjGVuJfzJ.jpg | 1200 | 1696 |
+----------------------+--------+--------+
複製程式碼4.6：模糊查詢：LIKE
'%'匹配任意多個字元,'_'匹配任意單個字元
mysql>
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE pic_path LIKE 'androi%';
+----------------------------------------------+--------+--------+
| 路徑 | 寬/px | 高/px |
+----------------------------------------------+--------+--------+
| android\008525ebc2b7d434070e74c00841a30f.png | 544 | 544 |
| android\054d98e2d96dc42d9b2b036126fccf49.png | 544 | 544 |
| android\05baf2d03651d1110d7a403f14aee877.png | 544 | 544 |
| android\0655e07d6717847489cd222c9c9e0b1d.png | 500 | 500 |
| android\079c4cb46c95b2365b5bc5150e7d5213.png | 544 | 544 |
| android\07a4dc9b4b207cb420a71cbf941ad45a.png | 544 | 544 |
| android\07abb7972a5638b53afa3b5eb98b19c1.png | 500 | 500 |
......
mysql>
SELECT
pic_path AS 路徑 ,
pic_width AS '寬/px',
pic_height AS '高/px'
FROM pic
WHERE pic_path LIKE 'p_em%';
+--------------------------------------------+--------+--------+
| 路徑 | 寬/px | 高/px |
+--------------------------------------------+--------+--------+
| poem\世界·綻放.jpg | 1148 | 712 |
| poem\我愛你，是火山岩的緘默.jpg | 690 | 397 |
| poem\枝·你是樹的狂舞.jpg | 500 | 333 |
| poem\海與鹿王.jpg | 799 | 499 |
| poem\遊夢人·詩的誕生.jpg | 800 | 444 |
| poem\珊瑚墓地.jpg | 1104 | 719 |
+--------------------------------------------+--------+--------+
複製程式碼4.7:比較符號= != < > <= >=
小學生都知道的，就不廢話了，檢視一下小於10Kb的圖片
mysql>
SELECT
pic_path AS 路徑 ,
pic_length AS '大小/byte'
FROM pic
WHERE pic_length < 10*1024;
+----------------------------------------------+-------------+
| 路徑 | 大小/byte |
+----------------------------------------------+-------------+
| 30X20.jpg | 10158 |
| android\613f2b8f0eaa8f63bedce9781527c9ab.png | 4001 |
| android\94b5c41232f9761403890c09c2b1aae3.png | 4001 |
| android\d3fd676f224f0734beb48d0c0d2f4e66.png | 4001 |
| udp傳送與接收訊息_控制檯.png | 9184 |
+----------------------------------------------+-------------+
複製程式碼5. GROUP BY分組查詢
會先排序，再列出
|--- GROUP BY
SELECT
pic_mime AS "型別",
avg(pic_length) AS '平均大小/byte' ,
count(pic_length) AS '總數量/個' ,
min(pic_length) AS '最小值/byte' ,
max(pic_length) AS '最大值/byte' ,
sum(pic_length) AS '總和/byte'
FROM pic
GROUP BY pic_mime;
+--------+-------------------+---------------+----------------+----------------+-------------+
| 型別 | 平均大小/byte | 總數量/個 | 最小值/byte | 最大值/byte | 總和/byte |
+--------+-------------------+---------------+----------------+----------------+-------------+
| 0 | 141518.8734 | 229 | 4001 | 829338 | 32407822 |
| 1 | 2133272.8000 | 60 | 10158 | 116342886 | 127996368 |
+--------+-------------------+---------------+----------------+----------------+-------------+
複製程式碼6.結果集篩選：HAVING
現在查詢寬高比在1.1和1.3之間的圖片
|-- 如果用WHERE 來查詢 感覺有點不優雅
SELECT
pic_path AS 路徑 ,
pic_width/pic_height AS '寬高比'
FROM pic
WHERE pic_width/pic_height > 1.1 && pic_width/pic_height<1.3;
+------------------------------------------------------------------+-----------+
| 路徑 | 寬高比 |
+------------------------------------------------------------------+-----------+
| dQXbnTRjUdNxhiyl.jpg | 1.2308 |
| JsXHWmKqOlziKmeA.jpg | 1.2600 |
| logo\android\Android原生繪圖之讓你瞭解View的運動.png | 1.2884 |
| 洛天依.jpg | 1.1990 |
+------------------------------------------------------------------+-----------+
|-- AS 相當於將列取了變數，對結果集再進行篩選用HAVING,用WHERE則報錯，找不到列
SELECT
pic_path AS 路徑 ,
pic_width/pic_height AS ratio
FROM pic
HAVING ratio > 1.1 && ratio <1.3;
+------------------------------------------------------------------+--------+
| 路徑 | ratio |
+------------------------------------------------------------------+--------+
| dQXbnTRjUdNxhiyl.jpg | 1.2308 |
| JsXHWmKqOlziKmeA.jpg | 1.2600 |
| logo\android\Android原生繪圖之讓你瞭解View的運動.png | 1.2884 |
| 洛天依.jpg | 1.1990 |
+------------------------------------------------------------------+--------+
複製程式碼7.結果排序：ORDER BY
按照ratio將序排列
SELECT
pic_path AS 路徑 ,
pic_width/pic_height AS ratio
FROM pic
HAVING ratio > 1.1 && ratio <1.3;
ORDER BY ratio DESC
+------------------------------------------------------------------+--------+
| 路徑 | ratio |
+------------------------------------------------------------------+--------+
| dQXbnTRjUdNxhiyl.jpg | 1.2308 |
| JsXHWmKqOlziKmeA.jpg | 1.2600 |
| logo\android\Android原生繪圖之讓你瞭解View的運動.png | 1.2884 |
| 洛天依.jpg | 1.1990 |
+------------------------------------------------------------------+--------+
複製程式碼8.控制條目數：LIMIT
|-- 偏移一條，取兩條
SELECT
pic_path AS 路徑 ,
pic_width/pic_height AS ratio
FROM pic
HAVING ratio > 1.1 && ratio <1.3
ORDER BY ratio DESC LIMIT 1,2;
+----------------------+--------+
| 路徑 | ratio |
+----------------------+--------+
| JsXHWmKqOlziKmeA.jpg | 1.2600 |
| dQXbnTRjUdNxhiyl.jpg | 1.2308 |
+----------------------+--------+
複製程式碼三、子查詢 (LEVER 3)
1.查詢大於平均尺寸的圖片 -- WHERE
|--- 出現在其他SQL語句內的SELECT語句
|--- 子查詢必須在()內
|--- 增刪改查都可以進行子查詢,返回：標量，行，列或子查詢
複製程式碼|-- 1-1：查出圖片平均大小
SELECT
ROUND(AVG(pic_length),2) AS '平均大小'
FROM pic;
+--------------+
| 平均大小 |
+--------------+
| 555031.80 |
+--------------+
1 row in set (0.00 sec)
|-- 1-2：在用WHERE 篩選
SELECT
pic_path AS 路徑 ,
pic_length AS '大小/byte'
FROM pic
WHERE pic_length > 555031.80;
+----------------------------------------------+-------------+
| 路徑 | 大小/byte |
+----------------------------------------------+-------------+
| 30000X20000.jpg | 116342886 |
| 3000X2000.jpg | 3404969 |
| android\12284e5f7197d8be737fa967c8b00fbe.png | 829338 |
| android\594665add495ac9da8b6bbee1c63f1b8.png | 598974 |
| android\7cc97458727e23f7d161b8a1a7c6b453.png | 559420 |
| android\cbb1524f5ab4266698f3a6fc2992ccae.png | 829338 |
| android\d52539b1b508a594d1f2865037ff50c5.png | 598974 |
| android\f07ddfe5a103e4a024e14e2569f1d70e.png | 829338 |
| android\f0d1e7713d5557a8f9c74c9904843e09.png | 559420 |
| bg.png | 688207 |
| gtQiXnRfkvvTLinw.jpg | 771187 |
| poem\珊瑚墓地.jpg | 984472 |
| XoazFNMQROveEPQn.jpg | 795364 |
+----------------------------------------------+-------------+
|--- 也就是將一個語句包在WHERE 條件裡
SELECT
pic_path AS 路徑 ,
pic_length AS '大小/byte'
FROM pic
WHERE pic_length > (
SELECT
ROUND(AVG(pic_length),2)
FROM pic
);
複製程式碼2.查出每種型別的最新插入的圖片 -- WHERE
SELECT
pic_path AS 路徑 ,
pic_mime AS 型別
FROM pic
WHERE id IN (
SELECT
max(id)
FROM pic
GROUP BY pic_mime
);
+------------------+--------+
| 路徑 | 型別 |
+------------------+--------+
| 洛天依.jpg | 1 |
| 虛擬機器棧.png | 0 |
+------------------+--------+
複製程式碼3.FROM子查詢 -- FROM
SELECT
id,
pic_path AS 路徑 ,
pic_length AS '大小/byte'
FROM pic
WHERE id>=10&&id<=15
ORDER BY pic_length DESC;
+----+----------------------------------------------+-------------+
| id | 路徑 | 大小/byte |
+----+----------------------------------------------+-------------+
| 15 | android\0f3bf63796ac370a08ee97b056b0587b.png | 178849 |
| 14 | android\0951ef0be68f0c498ca34ffcd7fc7faa.png | 175842 |
| 11 | android\079c4cb46c95b2365b5bc5150e7d5213.png | 86996 |
| 10 | android\0655e07d6717847489cd222c9c9e0b1d.png | 53764 |
| 12 | android\07a4dc9b4b207cb420a71cbf941ad45a.png | 46270 |
| 13 | android\07abb7972a5638b53afa3b5eb98b19c1.png | 43360 |
+----+----------------------------------------------+-------------+
|--- 將查詢結果當做一張表，再查詢操作
SELECT
id,路徑 FROM (
SELECT
id,
pic_path AS 路徑 ,
pic_length AS '大小/byte'
FROM pic
WHERE id>=10&&id<=15
ORDER BY pic_length DESC
) AS result
WHERE `大小/byte` < 59999;
+----+----------------------------------------------+
| id | 路徑 |
+----+----------------------------------------------+
| 10 | android\0655e07d6717847489cd222c9c9e0b1d.png |
| 12 | android\07a4dc9b4b207cb420a71cbf941ad45a.png |
| 13 | android\07abb7972a5638b53afa3b5eb98b19c1.png |
+----+----------------------------------------------+
複製程式碼四、連線查詢
0.建立關聯表
首先連線查詢要多張表，現在建一個
mime_type的表
|--- 建表
CREATE TABLE mime_type(
mime_id SMALLINT UNSIGNED PRIMARY KEY,
mime_info CHAR(24)
);
|--- 插入資料
INSERT INTO mime_type(mime_id,mime_info) VALUES
(0,'image/png'),
(1,'image/jpeg'),
(2,'image/svg+xml'),
(3,'video/mp4'),
(4,'text/plain');
|--- 效果
mysql> select * from mime_type;
+---------+---------------+
| mime_id | mime_info |
+---------+---------------+
| 0 | image/png |
| 1 | image/jpeg |
| 2 | image/svg+xml |
| 3 | video/mp4 |
| 4 | text/plain |
+---------+---------------+
|-- 為了說明問題，pic表新增一條測試資料：pic_mime = 8 也就是 mime_type表找不到時
INSERT INTO pic(pic_path,pic_length,pic_mime,pic_width,pic_height) VALUES('test.jpg',100,8,300,200);
複製程式碼1.內連線查詢 INNER JOIN
SELECT 待查屬性 FROM 表1 INNER JOIN 表2 ON 條件 WHERE 條件
SELECT id, pic_path AS 路徑 , mime_type.mime_info AS 型別 , pic_length
FROM pic INNER JOIN mime_type
ON pic.pic_mime = mime_type.mime_id
ORDER BY id DESC LIMIT 4;
+-----+------------------+------------+------------+
| id | 路徑 | 型別 | pic_length |
+-----+------------------+------------+------------+
| 289 | 虛擬機器棧.png | image/png | 63723 |
| 288 | 統一返回.png | image/png | 29485 |
| 287 | 洛天依.jpg | image/jpeg | 42117 |
| 286 | 標記整理.png | image/png | 29288 |
+-----+------------------+------------+------------+
複製程式碼2.左連線查詢 : LEFT JOIN
保持左表的記錄完整性，右表查不到就擺 NULL
SELECT id, pic_path AS 路徑 , mime_type.mime_info AS 型別 , pic_length
FROM pic LEFT JOIN mime_type
ON pic.pic_mime = mime_type.mime_id
ORDER BY id DESC LIMIT 4;
+-----+------------------+------------+------------+
| id | 路徑 | 型別 | pic_length |
+-----+------------------+------------+------------+
| 290 | test.jpg | NULL | 100 |
| 289 | 虛擬機器棧.png | image/png | 63723 |
| 288 | 統一返回.png | image/png | 29485 |
| 287 | 洛天依.jpg | image/jpeg | 42117 |
+-----+------------------+------------+------------+
複製程式碼3. 右(外)連線查詢 :RIGHT JOIN
保持右表的記錄完整性，左表查不到就擺 NULL
SELECT id, pic_path AS 路徑 , mime_type.mime_info AS 型別 , pic_length
FROM pic RIGHT JOIN mime_type
ON pic.pic_mime = mime_type.mime_id
ORDER BY id LIMIT 8;
+------+--------------------------------------+---------------+------------+
| id | 路徑 | 型別 | pic_length |
+------+--------------------------------------+---------------+------------+
| NULL | NULL | text/plain | NULL |
| NULL | NULL | video/mp4 | NULL |
| NULL | NULL | image/svg+xml | NULL |
| 1 | 30000X20000.jpg | image/jpeg | 116342886 |
| 2 | 3000X2000.jpg | image/jpeg | 3404969 |
| 3 | 300X200.jpg | image/jpeg | 99097 |
| 4 | 30X20.jpg | image/jpeg | 10158 |
| 5 | 6dc9e8455c47d964e1a8a4ef04cf9477.jpg | image/jpeg | 236254 |
+------+--------------------------------------+---------------+------------+
複製程式碼4. 全(外)連線 (偽):使用UNION
MySQL不支援全外連線，所以只能採取關鍵字UNION來聯合左、右連線的方法 UNION : 將若干條sql的查詢結果集合併成一個。
UNION ALL不會覆蓋相同結果
SELECT id, pic_path AS 路徑 , mime_type.mime_info AS 型別 , pic_length
FROM pic LEFT JOIN mime_type ON pic.pic_mime = mime_type.mime_id
UNION(
SELECT id, pic_path AS 路徑 , mime_type.mime_info AS 型別 , pic_length
FROM pic RIGHT JOIN mime_type ON pic.pic_mime = mime_type.mime_id
)
ORDER BY id DESC;
+------+------------------------------------------------------------------------------------+---------------+------------+
| id | 路徑 | 型別 | pic_length |
+------+------------------------------------------------------------------------------------+---------------+------------+
| 290 | test.jpg | NULL | 100 |
| 289 | 虛擬機器棧.png | image/png | 63723 |
| 288 | 統一返回.png | image/png | 29485 |
| 287 | 洛天依.jpg | image/jpeg | 42117 |
...
| 3 | 300X200.jpg | image/jpeg | 99097 |
| 2 | 3000X2000.jpg | image/jpeg | 3404969 |
| 1 | 30000X20000.jpg | image/jpeg | 116342886 |
| NULL | NULL | text/plain | NULL |
| NULL | NULL | video/mp4 | NULL |
| NULL | NULL | image/svg+xml | NULL |
+------+------------------------------------------------------------------------------------+---------------+------------+
複製程式碼5. UNION小測試
CREATE TABLE a(
id CHAR(4),
num INT
);
INSERT INTO a(id,num) VALUES
('a',4),('b',6),('c',2),('d',8);
CREATE TABLE b(
id CHAR(4),
num INT
);
INSERT INTO b(id,num) VALUES
('b',8),('c',7),('d',3),('e',18);
mysql> SELECT * FROM a; mysql> SELECT * FROM b;
+------+------+ +------+------+
| id | num | | id | num |
+------+------+ +------+------+
| a | 4 | | b | 8 |
| b | 6 | | c | 7 |
| c | 2 | | d | 3 |
| d | 8 | | e | 18 |
+------+------+ +------+------+
SELECT id,sum(num) FROM
(SELECT * FROM a
UNION ALL
SELECT * FROM b) as temp
GROUP BY id;
+------+----------+
| id | sum(num) |
+------+----------+
| a | 4 |
| b | 14 |
| c | 9 |
| d | 11 |
| e | 18 |
+------+----------+
複製程式碼六、DDL 建庫/表
1、關於運算元據庫
SHOW DATABASES; # 顯示所有的資料庫
SHOW CREATE DATABASE <資料庫名> # 檢視資料庫建立資訊
USE <資料庫名>; # 使用資料庫
CREATE DATABASE <資料庫名> [CHARACTER SET <字符集>]; # 建立一個將的資料庫指定字符集
ALTER DATABASE <資料庫名> CHARACTER SET <字符集>; # 修改資料庫字符集
DROP DATABASE <資料庫名>; # 傳說中的刪庫跑路
SELECT DATABASE(); # 檢視當前選中的資料庫
複製程式碼2.顯示資料庫資訊
SHOW TABLES; # 展示當前資料庫中的表
SHOW TABLES FROM mysql # 展示指定資料庫中的表
DESC <表名>; # 檢視錶結構
SHOW COLUMNS FROM <表名>; # 檢視錶結構
複製程式碼3.建立表
|-- UNSIGNED 無符號 AUTO_INCREMENT 自增長
|-- ZEROFILL 前面自動填 0 , 預設 UNSIGNED
CREATE TABLE create_test(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
code TINYINT(5) ZEROFILL DEFAULT 0
);
INSERT INTO create_test(code) VALUES (5);
INSERT INTO create_test VALUES (); #預設值測試
mysql> SELECT * FROM create_test;
+----+-------+
| id | code |
+----+-------+
| 1 | 00005 |
| 2 | 00000 |
+----+-------+
複製程式碼4.為表增加屬性
ALTER TABLE <表名> ADD 屬性資訊 [AFTER 屬性] ;
|-- 看一下當前表結構
mysql> DESC create_test;
+-------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| code | tinyint(5) unsigned zerofill | YES | | 00000 | |
+-------+------------------------------+------+-----+---------+----------------+
mysql> ALTER TABLE create_test ADD age SMALLINT UNSIGNED NOT NULL;
mysql> DESC create_test;
+-------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| code | tinyint(5) unsigned zerofill | YES | | 00000 | |
| age | smallint(5) unsigned | NO | | NULL | |
+-------+------------------------------+------+-----+---------+----------------+
|-- AFTER可將屬性排在指定屬性之後(強迫症專用)
|-- ALTER TABLE create_test ADD password VARCHAR(32) AFTER id;
mysql> ALTER TABLE create_test ADD password VARCHAR(32) AFTER id;
mysql> DESC create_test;
+----------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| password | varchar(32) | YES | | NULL | |
| code | tinyint(5) unsigned zerofill | YES | | 00000 | |
| age | smallint(5) unsigned | NO | | NULL | |
+----------+------------------------------+------+-----+---------+----------------+
|-- 一次新增多個屬性
ALTER TABLE create_test ADD (aaa VARCHAR(32), bbb VARCHAR(32),ccc VARCHAR(32));
mysql> DESC create_test;
+----------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| password | varchar(32) | YES | | NULL | |
| code | tinyint(5) unsigned zerofill | YES | | 00000 | |
| age | smallint(5) unsigned | NO | | NULL | |
| aaa | varchar(32) | YES | | NULL | |
| bbb | varchar(32) | YES | | NULL | |
| ccc | varchar(32) | YES | | NULL | |
+----------+------------------------------+------+-----+---------+----------------+
複製程式碼5.為表刪除屬性
ALTER TABLE <表名> DROP 屬性
ALTER TABLE create_test DROP aaa,DROP bbb,DROP ccc;
mysql> DESC create_test;
+----------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| password | varchar(32) | YES | | NULL | |
| code | tinyint(5) unsigned zerofill | YES | | 00000 | |
| age | smallint(5) unsigned | NO | | NULL | |
+----------+------------------------------+------+-----+---------+----------------+
複製程式碼6.修改屬性的型別
ALTER TABLE <表名> MODIFY 屬性 屬性型別 [FIRST];
|-- 把password改成VARCHAR(40)
ALTER TABLE create_test MODIFY password VARCHAR(40);
mysql> DESC create_test;
+----------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| password | varchar(40) | YES | | NULL | |
| code | tinyint(5) unsigned zerofill | YES | | 00000 | |
| age | smallint(5) unsigned | NO | | NULL | |
+----------+------------------------------+------+-----+---------+----------------+
|-- 將某個屬性移到最頂
ALTER TABLE create_test MODIFY password VARCHAR(40) FIRST;
+----------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------------------------+------+-----+---------+----------------+
| password | varchar(40) | YES | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| code | tinyint(5) unsigned zerofill | YES | | 00000 | |
| age | smallint(5) unsigned | NO | | NULL | |
+----------+------------------------------+------+-----+---------+----------------+
複製程式碼7.修改表的屬性名
ALTER TABLE <表名> CHANGE 原屬性 新屬性 新屬性型別;
mysql> ALTER TABLE create_test CHANGE password pw varchar(40);
mysql> DESC create_test;
+-------+------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------------------+------+-----+---------+----------------+
| pw | varchar(40) | YES | | NULL | |
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| code | tinyint(5) unsigned zerofill | YES | | 00000 | |
| age | smallint(5) unsigned | NO | | NULL | |
+-------+------------------------------+------+-----+---------+----------------+
複製程式碼8.修改表名
方式一：
ALTER TABLE 舊錶名 RENAME 新表名;方式二：RENAME TABLE 舊錶名 TO 新表名;
ALTER TABLE create_test RENAME 阿姆斯特朗迴旋加速噴氣式阿姆斯特朗炮;
mysql> SHOW TABLES;
+--------------------------------------------------------+
| Tables_in_datatype |
+--------------------------------------------------------+
| 阿姆斯特朗迴旋加速噴氣式阿姆斯特朗炮 |
| a |
| b |
| mime_type |
| pic |
| type_number |
+--------------------------------------------------------+
RENAME TABLE 阿姆斯特朗迴旋加速噴氣式阿姆斯特朗炮 TO toly;
mysql> SHOW TABLES;
+--------------------+
| Tables_in_datatype |
+--------------------+
| a |
| b |
| mime_type |
| pic |
| toly |
| type_number |
+--------------------+
複製程式碼SQL 的基礎就這樣 , 下篇見