Mysql中的DQL語句

1.基本語法

SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name [ASC | DESC]]
[LIMIT number]; 

* ORDER BY column_name [ASC | DESC] 是一個可選的子句，用於指定結果集的排序順序，預設是升序（ASC）。
* LIMIT number 是一個可選的子句，用於限制返回的行數。

2. 其他功能

* 函式concat()
select concat('姓名:',StudentName) as 新名字 from student
--將查出的結果與字串進行拼接

* select後跟表示式
* 發現重複資料，進行去重
select distinct 'StudentNo' from result
* 查詢系統版本
select version()
* 用來計算
select 100*3+1 as 計算結果
* 查詢自增的步長
select @@auto_increment_increment
* 查詢學生成績後進行+1操作
select 'StudentNo','StudentResult'+1 as '提分後' from result

3. 運算子列表，可以用在where子句中

運算子	描述	例項
=	等號，檢測兩個值是否相等，如果相等返回true	(A = B) 返回false。
<>, !=	不等於，檢測兩個值是否相等，如果不相等返回true	(A != B) 返回 true。
>	大於號，檢測左邊的值是否大於右邊的值, 如果左邊的值大於右邊的值返回true	(A > B) 返回false。
<	小於號，檢測左邊的值是否小於右邊的值, 如果左邊的值小於右邊的值返回true	(A < B) 返回 true。
>=	大於等於號，檢測左邊的值是否大於或等於右邊的值, 如果左邊的值大於或等於右邊的值返回true	(A >= B) 返回false。
<=	小於等於號，檢測左邊的值是否小於或等於右邊的值, 如果左邊的值小於或等於右邊的值返回true	(A <= B) 返回 true。

4. 模糊查詢

比較運算子：

運算子	語法	描述
is null	a is null	運算子為null,結果為真
is not null	a is not null	運算子不為null,結果為真
between	a between b and c	a在b和c之間,結果為真
like	a like b	a匹配b,結果為真
in	a in (a1,a2...)	a在a1,a2..裡,結果為真

SELECT * FROM [user] WHERE u_name LIKE '%三%';
--%表示任意 0 個或多個字元
SELECT * FROM [user] WHERE u_name LIKE '_三_';
--表示任意單個字元。
SELECT * FROM [user] WHERE u_name in ('張三'，'李四');
--匹配在（）中的內容
SELECT * FROM [user] WHERE u_name is not null;
--查詢名字不為空的user

5. 連表查詢

連線方法	描述
INNER JOIN	如果表中有至少一個匹配，則返回行
LEFT JOIN	即使右表中沒有匹配，也從左表返回所有的行
RIGHT JOIN	即使左表中沒有匹配，也從右表返回所有的行
FULL JOIN	只要其中一個表中存在匹配，則返回行(oracle裡面有full join,但在mysql中沒有full join)

內連線：INNER JOIN （JOIN）關鍵字在表中存在至少一個匹配時返回行。

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
--* columns：要顯示的列名。
--* table1：表1的名稱。
--* table2：表2的名稱。
--* column_name：表中用於連線的列名。

例項驗證：
"Websites" 表的資料：

"access_log" 網站訪問記錄表的資料：

--inner join：
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count;

--left join：
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;

--right join：
SELECT websites.name, access_log.count, access_log.date
FROM websites
RIGHT JOIN access_log
ON access_log.site_id=websites.id
ORDER BY access_log.count DESC;

--MySQL中不支援 FULL OUTER JOIN，你可以在 SQL Server 測試以下例項。
--full join：
SELECT Websites.name, access_log.count, access_log.date
FROM Websites
FULL OUTER JOIN access_log
ON Websites.id=access_log.site_id
ORDER BY access_log.count DESC;
--FULL OUTER JOIN 關鍵字返回左表（Websites）和右表（access_log）中所有的行。如果 "Websites" 表中的行在 "access_log" 中沒有匹配或者 "access_log" 表中的行在 "Websites" 表中沒有匹配，也會列出這些行。

自連線：使用者登入表
使用者登入表login(id，uid，update_time)

計算回訪使用者數（在一前一後的不同日期登入過）

--自連線：
select count(DISTINCT a.uid) as 回訪人數
from login a left join login b
on a.uid = b.uid
where a.update_time < b.update_time;

6.order by

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

• column1, column2, ...：要排序的欄位名稱，可以為多個欄位。
• ASC：表示按升序排序。
• DESC：表示按降序排序。

7. limit

select _column,_column 
from _table 
[where Clause] 
[limit N][offset M]

• select * : 返回所有記錄
• limit N : 返回 N 條記錄
• offset M : 跳過 M 條記錄, 預設 M=0, 單獨使用似乎不起作用
• limit N,M : 相當於 limit M offset N , 從第 N 條記錄開始, 返回 M 條記錄

8. 子查詢
   查詢不存在年齡為20歲的員工的部門。
   select dname from department where did not in(select did from employee where age=20);

9. mysql函式
   常用函式：

函式	返回值
SELECT ABS(-1);	1
SELECT CEILING(1.5);	2
SELECT FLOOR(1.5);	1
SELECT EXP(3);	計算 e 的三次方,返回20.085536923188
SELECT GREATEST(3, 12, 34, 8, 25);	返回以下數字列表中的最大值34
SELECT LEAST(3, 12, 34, 8, 25);	返回以下數字列表中的最小值3
SELECT LN(2);	返回 2 的自然對數：0.6931471805599453
SELECT RAND();	返回 0 到 1 的隨機數，若()裡面有數字,RAND(x),x相同時，返回值相同
SELECT ROUND(1.23456);	返回離 x 最近的整數：1
SELECT LENGTH('1234');	返回字串 s 的字元數：4
SELECT CONCAT('hel','llo');	多個字串合併為一個字串：返回hello
SELECT TRIM(' RUNOOB ');	去掉字串 s 開始和結尾處的空格：返回RUNOOB
SELECT REPEAT('runoob',3);	將字串 s 重複 n 次：返回runoobrunoobrunoob
SELECT REVERSE('abc');	將字串s的順序反過來：返回cba
SELECT CURRENT_TIME();	返回11:40:45
SELECT CURRENT_TIMESTAMP();	返回當前日期和時間

聚合函式：

函式	返回值
SELECT SUM(age) AS totalAage FROM Student;	返回age的總和
SELECT MAX(age) AS maxAge FROM Student;	age最大值
SELECT MIN(age) AS minAge FROM Student;	age最小值
SELECT AVG(age) FROM student;	age的平均值
SELECT COUNT(*);	掃描整個表的所有行，不考慮任何列的值，包括含有NULL值的行
SELECT COUNT(column);	遍歷該列的每一行，檢查是否為NULL，然後累計計數。
SELECT COUNT(1);	忽略所有列中的NULL值，只計算非空行的數目

10. 分組過濾

SELECT id,sex,COUNT(*) AS total_peo 
FROM employee 
GROUP BY sex 
HAVING COUNT(*)>3;

11. 資料庫級別的MD5加密

-- =====================測試MD5加密=======================
-- MD5資訊摘要演算法
CREATE TABLE `testMD5 01`(
 `id` INT(4) NOT NULL,
 `name` VARCHAR(10) NOT NULL, 
 `pwd` VARCHAR(50) NOT NULL,
 PRIMARY KEY(`id`)
)ENGINE=INNODB  DEFAULT CHARSET=utf8

-- 明文密碼
INSERT INTO `testmd5 01` VALUES (1,'張san','123456'),(2,'張六','123456'),(3,'李四','123456'),(4,'王五','123456')

-- 加密
UPDATE `testmd5 01` SET pwd =MD5(pwd)  WHERE id=2  -- 14e1b600b1fd579f47433b88e8d85291

-- 在插入的時候加密
INSERT INTO `testmd5 01` VALUES (6,'huawei',MD5(123456))
INSERT INTO `testmd5 01` VALUES (7,'oppo',MD5(123456789))

-- 如何校驗  將使用者傳遞進來的密碼，進行MD5加密，然後比對加密後的值90(傳遞進來的值一樣，加密後的值也一樣)

--                           接收使用者傳來的使用者名稱和密碼
SELECT * FROM `testmd5 01` WHERE `name`='oppo' AND pwd=MD5('123456789')-- 25f9e794323b453885f5181f1b624d0b

12. select 的完整語法

SELECT[ALL|DISTINCT|DISTINCTROW|TOP]
{*|talbe.*|[table.]field1[AS alias1][,[table.]field2[AS alias2][,…]]}
FROM tableexpression[,…][IN externaldatabase]
[WHERE…]
[GROUP BY…]
[HAVING…]
[ORDER BY…]
[LIMIT…]

執行步驟：

* 先從from字句一個表或多個表建立工作表
* 將where條件應用於1）的工作表，保留滿足條件的行
* GroupBy 將2）的結果分成多個組
* Having 將條件應用於3）組合的條件過濾，只保留符合要求的組。
* Order By對結果進行排序。
* Limit對結果進行分頁