mariadb的查詢流程圖
select語句的從句分析順序：from(過濾表)-->where(過濾行)-->group by(分組)-->having(分組過濾)-->order by(排序)--
>select(選取字段)-->limit(查詢限制)-->最終結果
DISTINCT: 數據去重
SQL_CACHE: 顯式指定存儲查詢結果於緩存之中
SQL_NO_CACHE: 顯式查詢結果不予緩存
show global variables like '%query%';
query_cache_type | ON 表示緩存開啟
query_cache_size | 0 表示緩存空間大小，如果為0則不緩存
query_cache_type的值為'DEMAND'時，顯式指定SQL_CACHE的SELECT語句才會緩存；其它均不予緩存
緩存並不會緩存所有查詢結果，例如select now()；就不會緩存
WHERE子句：指明過濾條件以實現“選擇”的功能
算術操作符：+, -, *, /, %
比較操作符：=, !=, <>, <=>, >, >=, <, <=
BETWEEN min_num AND max_num
IN (element1, element2, ...)
IS NULL
IS NOT NULL
LIKE:
%: 任意長度的任意字符
_：任意單個字符
RLIKE
邏輯操作符： NOT,AND,OR
GROUP：根據指定的條件把查詢結果進行“分組”以用於做“聚合”運算：avg(), max(), min(), count(), sum()
HAVING: 對分組聚合運算後的結果指定過濾條件
ORDER BY: 根據指定的字段對查詢結果進行排序：升序ASC 降序：DESC
LIMIT [[offset,]row_count]：對查詢的結果進行輸出行數數量限制
例如：
select name,age from students where age/2=11;
select name,age from students where age+30>50;
select distinct gender from students;
select name as stuname from students;
select name,classid from students where classid is null;
select avg(age),gender from students group by gender;
select avg(age) as ages,gender from students group by gender having ages>20;
select count(stuid) as NO,classid from students group by classid;
select count(stuid) as NO,classid from students group by classid having NO>2;
select name,age from students order by age limit 10,10; (第一個表示偏移10個，第二個表示取10個)
多表查詢：
交叉連接：笛卡爾乘積(最消耗資源的一種查詢) 例如：select * from students,teachers; 如果students有20行，teachers也有20行，則顯示400行
內連接：
等值連接：讓表之間的字段以“等值”建立連接關系；
不等值連接
自然連接
自連接
外連接：
左外連接 例如：FROM tb1 LEFT JOIN tb2 ON tb1.col=tb2.col
右外連接 例如：FROM tb1 RIGHT JOIN tb2 ON tb1.col=tb2.col
等值連接：
select s.name as studentname,t.name as teachername from students as s,teachers as t where s.teacherid=t.tid;(這個夠復雜)
select s.name,c.class from students as s,classes as c where s.classid=c.classid;
左外連接：
select s.name,c.class from students as s left join classes as c on s.classid=c.classid;
以左表作為基准
select s.name,c.class from students as s right join classes as c on s.classid=c.classid;
以右表為基准
子查詢：在查詢語句嵌套著查詢語句,基於某語句的查詢結果再次進行的查詢 (mariadb對子查詢優化不夠，建議一般情況不使用)
select name,age from students where age>(select avg(age) from students);
select name,age from students where age in (select age from teachers);
select s.ages,s.classid from (select avg(age) as ages,classid from students where classid is not null group by classid) as s where s.ages>30;
聯合查詢：UNION
select name,age from students union select name,age from teachers;
