sql number轉varchar_MySQL 指南之 SQL 語句基礎

個人所有文章整理在此篇，將陸續更新收錄:知無涯，行者之路莫言終(我的編程之路)

零、結構化查詢語言：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;

+----------------+---------------------+------+-----+---------+-------+

+----------------+---------------------+------+-----+---------+-------+

| 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 數據庫記錄操作?`LEVEL1`

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;

+---------+------+----------------+----------------+--------------+

+---------+------+----------------+----------------+--------------+

| 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,'-21? ~ 21?-1','0 ~ 21?-1','小整數'),

('MEDIUMINT',3,'-22? ~ 22?-1','0 ~ 22?-1','中等整數'),

('INT',4,'-232 ~ 232-1','0 ~ 232-1','標準整數'),

('BIGINT',8,'-2?? ~ 2??-1','0 ~ 2??-1','大整數');

mysql> SELECT * FROM type_number;

+-----------+------+----------------------+----------------+--------------+

+-----------+------+----------------------+----------------+--------------+

| TINYINT | 1 | -2? ~ 2?-1 | 0 ~ 2?-1 | 很小整數 |

| SMALLINT | 2 | -21? ~ 21?-1 | 0 ~ 21?-1 | 小整數 |

| MEDIUMINT | 3 | -22? ~ 22?-1 | 0 ~ 22?-1 | 中等整數 |

| INT | 4 | -232 ~ 232-1 | 0 ~ 232-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;

+-----------+------+----------------------+----------------+--------------+

+-----------+------+----------------------+----------------+--------------+

| TINYINT | 1 | -2? ~ 2?-1 | 0 ~ 2?-1 | 微型整數 |

| SMALLINT | 2 | -21? ~ 21?-1 | 0 ~ 21?-1 | 小整數 |

| MEDIUMINT | 3 | -22? ~ 22?-1 | 0 ~ 22?-1 | 中等整數 |

| INT | 4 | -232 ~ 232-1 | 0 ~ 232-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;

+-----------+------+----------------------+----------------+--------------+

+-----------+------+----------------------+----------------+--------------+

| SMALLINT | 2 | -21? ~ 21?-1 | 0 ~ 21?-1 | 小整數 |

| MEDIUMINT | 3 | -22? ~ 22?-1 | 0 ~ 22?-1 | 中等整數 |

| INT | 4 | -232 ~ 232-1 | 0 ~ 232-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 ~ 232-1 |

| BIGINT | 0 ~ 2??-1 |

+--------+----------------+

二、圖片表 pic?`(LEVER2)`

這個是用來記錄圖片信息的表，數據準備過程詳見番外篇：[番外]-練習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;

+--------+-------------------+---------------+----------------+----------------+-------------+

+--------+-------------------+---------------+----------------+----------------+-------------+

| 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 |

+----------------------+--------+

三、子查詢?`(LEVER3)`

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 表1I NNER JOIN 表2O N條件 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 |

+------+--------------------------------------+---------------+------------+

| 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 |

+------+------------------------------------------------------------------------------------+---------------+------------+

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 TABLEADD屬性信息[AFTER屬性];

|-- 看一下當前表結構

mysql> DESC create_test;

+-------+------------------------------+------+-----+---------+----------------+

+-------+------------------------------+------+-----+---------+----------------+

| 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;

+-------+------------------------------+------+-----+---------+----------------+

+-------+------------------------------+------+-----+---------+----------------+

| 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;

+----------+------------------------------+------+-----+---------+----------------+

+----------+------------------------------+------+-----+---------+----------------+

| 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;

+----------+------------------------------+------+-----+---------+----------------+

+----------+------------------------------+------+-----+---------+----------------+

| 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;

+----------+------------------------------+------+-----+---------+----------------+

+----------+------------------------------+------+-----+---------+----------------+

| 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;

+----------+------------------------------+------+-----+---------+----------------+

+----------+------------------------------+------+-----+---------+----------------+

| 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;

+----------+------------------------------+------+-----+---------+----------------+

+----------+------------------------------+------+-----+---------+----------------+

| 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;

+-------+------------------------------+------+-----+---------+----------------+

+-------+------------------------------+------+-----+---------+----------------+

| 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 的基礎就這樣 , 下篇見