MySQL指南之SQL语句基础
生活随笔
收集整理的這篇文章主要介紹了
MySQL指南之SQL语句基础
小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.
個人所有文章整理在此篇，將陸續(xù)更新收錄:知無涯，行者之路莫言終（我的編程之路）
零、結(jié)構(gòu)化查詢語言：SQL(Structured Query Language)
DDL 數(shù)據(jù)定義語言 管理庫，表 DML 數(shù)據(jù)操作語言 增刪改查 DCL 數(shù)據(jù)控制語言 數(shù)據(jù)控制，權(quán)限訪問等準(zhǔn)備活動：創(chuàng)建庫和表
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) );目前狀態(tài)：
mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | datatype | | information_schema | | mycode | | mysql | | performance_schema | | seckill | +--------------------+ mysql> USE datatype; Database changedmysql> 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 數(shù)據(jù)庫記錄操作 LEVEL 1
LEVEL 1先簡單掌握一下下面的用法
MySQL基礎(chǔ)操作LEVER1.png1、記錄的插入操作
INSERT INTO <表名> (屬性,...) VALUES (值,...),...;
|-- 插入一條數(shù)據(jù) INSERT INTO <表名> (屬性,...) VALUES (值,...); INSERT INTO type_number(type,byte,range_singed,range_unsinged,info) VALUES ('TINYINT',1,'-2? ~ 2?-1','0 ~ 2?-1','很小整數(shù)');|-- 查詢所有 SELECT * FROM <表名>; mysql> SELECT * FROM type_number; +---------+------+----------------+----------------+--------------+ | type | byte | range_singed | range_unsinged | info | +---------+------+----------------+----------------+--------------+ | TINYINT | 1 | -2? ~ 2?-1 | 0 ~ 2?-1 | 很小整數(shù) | +---------+------+----------------+----------------+--------------+|-- 你也可以一次，插入多條數(shù)據(jù) INSERT INTO type_number(type,byte,range_singed,range_unsinged,info) VALUES ('TINYINT',1,'-2? ~ 2?-1','0 ~ 2?-1','很小整數(shù)'), ('SMALLINT',2,'-21? ~ 21?-1','0 ~ 21?-1','小整數(shù)'), ('MEDIUMINT',3,'-22? ~ 22?-1','0 ~ 22?-1','中等整數(shù)'), ('INT',4,'-232 ~ 232-1','0 ~ 232-1','標(biāo)準(zhǔn)整數(shù)'), ('BIGINT',8,'-2?? ~ 2??-1','0 ~ 2??-1','大整數(shù)');mysql> SELECT * FROM type_number; +-----------+------+----------------------+----------------+--------------+ | type | byte | range_singed | range_unsinged | info | +-----------+------+----------------------+----------------+--------------+ | TINYINT | 1 | -2? ~ 2?-1 | 0 ~ 2?-1 | 很小整數(shù) | | TINYINT | 1 | -2? ~ 2?-1 | 0 ~ 2?-1 | 很小整數(shù) | | SMALLINT | 2 | -21? ~ 21?-1 | 0 ~ 21?-1 | 小整數(shù) | | MEDIUMINT | 3 | -22? ~ 22?-1 | 0 ~ 22?-1 | 中等整數(shù) | | INT | 4 | -232 ~ 232-1 | 0 ~ 232-1 | 標(biāo)準(zhǔn)整數(shù) | | BIGINT | 8 | -2?? ~ 2??-1 | 0 ~ 2??-1 | 大整數(shù) | +-----------+------+----------------------+----------------+--------------+2、記錄的更新操作
UPDATE <表名> SET 屬性 = 值,... WHERE 條件;
UPDATE type_number SET info='微型整數(shù)' WHERE type = 'TINYINT';mysql> SELECT * FROM type_number; +-----------+------+----------------------+----------------+--------------+ | type | byte | range_singed | range_unsinged | info | +-----------+------+----------------------+----------------+--------------+ | TINYINT | 1 | -2? ~ 2?-1 | 0 ~ 2?-1 | 微型整數(shù) | | TINYINT | 1 | -2? ~ 2?-1 | 0 ~ 2?-1 | 微型整數(shù) | | SMALLINT | 2 | -21? ~ 21?-1 | 0 ~ 21?-1 | 小整數(shù) | | MEDIUMINT | 3 | -22? ~ 22?-1 | 0 ~ 22?-1 | 中等整數(shù) | | INT | 4 | -232 ~ 232-1 | 0 ~ 232-1 | 標(biāo)準(zhǔn)整數(shù) | | BIGINT | 8 | -2?? ~ 2??-1 | 0 ~ 2??-1 | 大整數(shù) | +-----------+------+----------------------+----------------+--------------+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 | -21? ~ 21?-1 | 0 ~ 21?-1 | 小整數(shù) | | MEDIUMINT | 3 | -22? ~ 22?-1 | 0 ~ 22?-1 | 中等整數(shù) | | INT | 4 | -232 ~ 232-1 | 0 ~ 232-1 | 標(biāo)準(zhǔn)整數(shù) | | BIGINT | 8 | -2?? ~ 2??-1 | 0 ~ 2??-1 | 大整數(shù) | +-----------+------+----------------------+----------------+--------------+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 (LEVER 2)
這個是用來記錄圖片信息的表，數(shù)據(jù)準(zhǔn)備過程詳見番外篇：
[番外]-練習(xí)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足夠了 ， 給個默認(rèn)值 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.查詢是屬性可參與運(yùn)算
|-- CONCAT函數(shù)用于連接字符串 注意：\需要轉(zhuǎn)義 mysql> SELECT CONCAT('E:\\SpringBootFiles\\imgs\\',pic_path) AS 絕對路徑, pic_width * pic_height AS '像素點個數(shù)' FROM pic WHERE pic_height>1200;+----------------------------------------------+-----------------+ | 絕對路徑 | 像素點個數(shù) | +----------------------------------------------+-----------------+ | 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條件的千變?nèi)f化
MySQL的WHERE.png4.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: 區(qū)間匹配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:比較符號= != < > <= >=
小學(xué)生都知道的，就不廢話了，查看一下小于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發(fā)送與接收消息_控制臺.png | 9184 | +----------------------------------------------+-------------+5. GROUP BY分組查詢
會先排序，再列出
|--- GROUP BY SELECT pic_mime AS "類型", avg(pic_length) AS '平均大小/byte' , count(pic_length) AS '總數(shù)量/個' , min(pic_length) AS '最小值/byte' , max(pic_length) AS '最大值/byte' , sum(pic_length) AS '總和/byte' FROM pic GROUP BY pic_mime;+--------+-------------------+---------------+----------------+----------------+-------------+ | 類型 | 平均大小/byte | 總數(shù)量/個 | 最小值/byte | 最大值/byte | 總和/byte | +--------+-------------------+---------------+----------------+----------------+-------------+ | 0 | 141518.8734 | 229 | 4001 | 829338 | 32407822 | | 1 | 2133272.8000 | 60 | 10158 | 116342886 | 127996368 | +--------+-------------------+---------------+----------------+----------------+-------------+6.結(jié)果集篩選：HAVING
現(xiàn)在查詢寬高比在1.1和1.3之間的圖片
|-- 如果用WHERE 來查詢 感覺有點不優(yōu)雅 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的運(yùn)動.png | 1.2884 | | 洛天依.jpg | 1.1990 | +------------------------------------------------------------------+-----------+|-- AS 相當(dāng)于將列取了變量，對結(jié)果集再進(jìn)行篩選用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的運(yùn)動.png | 1.2884 | | 洛天依.jpg | 1.1990 | +------------------------------------------------------------------+--------+7.結(jié)果排序：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的運(yùn)動.png | 1.2884 | | 洛天依.jpg | 1.1990 | +------------------------------------------------------------------+--------+8.控制條目數(shù)：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
|--- 出現(xiàn)在其他SQL語句內(nèi)的SELECT語句 |--- 子查詢必須在()內(nèi) |--- 增刪改查都可以進(jìn)行子查詢,返回：標(biāo)量，行，列或子查詢 |-- 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 | | 虛擬機(jī)棧.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 | +----+----------------------------------------------+-------------+|--- 將查詢結(jié)果當(dāng)做一張表，再查詢操作 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.創(chuàng)建關(guān)聯(lián)表
首先連接查詢要多張表，現(xiàn)在建一個mime_type 的表
|--- 建表 CREATE TABLE mime_type(mime_id SMALLINT UNSIGNED PRIMARY KEY,mime_info CHAR(24));|--- 插入數(shù)據(jù) 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表添加一條測試數(shù)據(jù)：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.內(nèi)連接查詢 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 | 虛擬機(jī)棧.png | image/png | 63723 | | 288 | 統(tǒng)一返回.png | image/png | 29485 | | 287 | 洛天依.jpg | image/jpeg | 42117 | | 286 | 標(biāo)記整理.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 | 虛擬機(jī)棧.png | image/png | 63723 | | 288 | 統(tǒng)一返回.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不支持全外連接，所以只能采取關(guān)鍵字UNION來聯(lián)合左、右連接的方法
UNION : 將若干條sql的查詢結(jié)果集合并成一個。 UNION ALL不會覆蓋相同結(jié)果
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、關(guān)于操作數(shù)據(jù)庫
SHOW DATABASES; # 顯示所有的數(shù)據(jù)庫 SHOW CREATE DATABASE <數(shù)據(jù)庫名> # 查看數(shù)據(jù)庫創(chuàng)建信息 USE <數(shù)據(jù)庫名>; # 使用數(shù)據(jù)庫 CREATE DATABASE <數(shù)據(jù)庫名> [CHARACTER SET <字符集>]; # 創(chuàng)建一個將的數(shù)據(jù)庫指定字符集 ALTER DATABASE <數(shù)據(jù)庫名> CHARACTER SET <字符集>; # 修改數(shù)據(jù)庫字符集 DROP DATABASE <數(shù)據(jù)庫名>; # 傳說中的刪庫跑路 SELECT DATABASE(); # 查看當(dāng)前選中的數(shù)據(jù)庫2.顯示數(shù)據(jù)庫信息
SHOW TABLES; # 展示當(dāng)前數(shù)據(jù)庫中的表 SHOW TABLES FROM mysql # 展示指定數(shù)據(jù)庫中的表 DESC <表名>; # 查看表結(jié)構(gòu) SHOW COLUMNS FROM <表名>; # 查看表結(jié)構(gòu)3.創(chuàng)建表
|-- UNSIGNED 無符號 AUTO_INCREMENT 自增長 |-- ZEROFILL 前面自動填 0 , 默認(rèn) 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 (); #默認(rèn)值測試mysql> SELECT * FROM create_test; +----+-------+ | id | code | +----+-------+ | 1 | 00005 | | 2 | 00000 | +----+-------+4.為表增加屬性
ALTER TABLE <表名> ADD 屬性信息 [AFTER 屬性] ;
|-- 看一下當(dāng)前表結(jié)構(gòu)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可將屬性排在指定屬性之后(強(qiáng)迫癥專用) |-- 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 新表名;
SQL 的基礎(chǔ)就這樣 , 下篇見
總結(jié)
以上是生活随笔為你收集整理的MySQL指南之SQL语句基础的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 软件测试之客户端（Client）测试
- 下一篇： 布局平板电脑 英特尔Atom新核芯全解析