MYSQL資料庫基礎和常用文法彙總03篇-資料查詢

【資料查詢語言】

(1) 查詢系統内部函數傳回内容和算式計算

顯示MYSQL的版本：select version();

顯示目前時間：select now();

顯示年月日：SELECT DAYOFMONTH(CURRENT_DATE);

顯示字元串：SELECT "welecome to my blog!";

當電腦用：select ((4 * 4) / 10 ) + 25;

（2）基本select查詢，注意格式： select ... from ...where ...

select * from stu; #查詢表中所有存在資料

select * from stu where name="lishi"; #查詢表中指定一條件後對應記錄

select * from stu where name="lishi" and age=21; #查詢表中指定兩條件篩選後記錄

select DISTINCT age from stu; #去掉重複項

select DISTINCT name,age from stu; #去掉重複項

SELECT * from stu c where c.age=21; #給表取别名

SELECT * from stu c,stu11 c1 where c.id=c1.id; #給2表取别名，并關聯查詢

SELECT * from stu as c,stu11 as c1 where c.id=c1.id; #同上，但帶了as

SELECT sex as "性别",age as "abc" from stu WHERE age=21; #給表中列取别名

(3)where子句

邏輯關系：= > >= <= <> or(或) and(且)

包含關系：between...and... in not in

模糊比對：like

是否為空：is null

select * from stu where age>21; #查找年齡大于21

select * from stu where age<=25; #查找年齡小于25

select * from stu where age<>25; #查找年齡不為25的其它記錄

select * from stu where id=1 or id=5; #查找id為1或5的記錄，隻要存在1或5就輸出

select * from stu where id=1 or sex='female'; #查找id為1或性别為female的記錄，滿足2條件的都輸出

select * from stu where age between 20 and 30; #查找年齡在20-30之間

select * from stu where age>=20 and age<=30; #同上

select * from stu where age in('21','25'); #查找年齡在21，25，滿足這2條件的

SELECT * from stu where id in(1,8) and price in(10,15)

SELECT * from stu where id in(1,8) or price in(8,15)

select * from stu where age not in('23','25'); #查找年齡不在23，25的，排除這2條件的其它值輸出

SELECT * from fruit where name="鳳梨"

select * from stu where name like '%chen%'; # 模糊查詢包含有chen的

select * from stu where name like 'chen%'; # 模糊查詢以chen開頭的

select * from stu where name like '%chen'; # 模糊查詢以chen結尾的

select * from stu where name like '%\%%'; #模糊查詢包含有%,具有相同字元%的，加\進行轉義處理

select * from stu c where c.tel is NULL; # 查找記錄為空的

------------------------------------

(4)算術表達式 + — * /

select *，age,age-5,age+5 from stu c where c.sex='female'; #對字段進行增加或減少5，輸出時會有2新列出現

SELECt age,(age+5)*2 from stu c WHERE c.sex='female'; #對字段進行組合運算

（5）字元串的連接配接、排序、分組、、

　Ａ，字元串連接配接

SELECT CONCAT(name,'-',c.age) FROM stu; #表示把前後兩個字元拼接起來，中間用字元'-'隔開

　Ｂ，排序

排序：select * from table1 order by field1,field2 [desc] #預設為升序ASC，可以省略

SELECT * from stu order by age DESC;　　＃按年齡降序輸出

SELECT *,age as a from stu order by a DESC;　　＃按年齡（加别名）降序輸出

SELECT *,age as a from stu order by a ASC;　　＃按年齡（加别名）升序輸出，ASC可省略　

SELECT * from stu ORDER BY 3 ASC; #按【列序号】排序，其中3代表age字段位置

SELECT * from meat where id in(1,3) ORDER BY price DESC; 指定行進行排序

SELECT * from stu c ORDER BY c.age,c.sex; #多列排序，先按第1個字段排序，再按第2個字段排序(基于在第1列中的重複項範圍)

如果第1個字段中有重複值,則重複值中再按照第2個字段進行排序

C,統計函數

求和：select sum(field1) as sumvalue from table1

平均：select avg(field1) as avgvalue from table1

最大：select max(field1) as maxvalue from table1

最小：select min(field1) as minvalue from table1

總數：select count(*) as totalcount from table1 #totalcount為别名,統計總資料數，count(id)

練習題

1，求出年齡超過平均值的人數

SELECT COUNT(sage) from stu WHERE sage>AVG(sage) #錯誤，無法運作

SELECT COUNT(sage) from stu WHERE sage>(select AVG(sage) from stu) #Ok

D，分組(結合統計函數) group by 分組查詢,HAVING為條件

select sex from stu group by sex; # 對單個列進行分組

select sex,count(sex) from stu group by sex; # 對單個列進行分組,且統計組中個數

select sex,max(age) from stu group by sex; # 對單個列進行分組,且統計組中年齡最大

#min()求最小值, avg()求平均值

select sex,max(age) from stu group by sex HAVING count(sex)>1; #先按性别分組，分組後再統計各組人數，超過1人的進行輸出，并在輸出的組中求出最大年齡

select id,name,sex, from stu group by sex order by age desc;

#先按性别分組，分組後再按年齡降序

【讀取行資料】

讀取所有行 select * from stu;

讀取前三行 select * from stu limit 1,3;

#從首行開始往下讀取3行

從第2行開始讀取三行 select * from stu limit 1,3;

#從第2行開始往下讀取3行

從第2行開始讀取三行 select * from stu order by ID asc limit 0,3;

#按ID列先升序，再從首行開始(0位置)往下讀取3行

讀取最後一行 select * from stu order by ID desc limit 0,3;

#倒序讀取3行

讀取前100行 select * from stu order by ID limit 0,100;

#若沒有100行，則輸出最大行

讀取第i行 select * from stu order by ID limit i-1,1;

#讀取第i行

SELECT * from stu LIMIT 4 OFFSET 2 # 從第3個位置往後輸出4行，Offset後面接的數字代表位置

【子查詢】

概念：在标準的查詢語句中的條件處嵌套了另外一個查詢語句

<标量子查詢>

是指子查詢傳回的是單一值的标量，如一個數字或一個字元串，也是子查詢中最簡單的傳回形式。 可以使用 = > < >= <= <> 這些操作符對子查詢的标量結果進行比較，通常子查詢的位置在比較式的右側

SELECT * FROM article WHERE uid = (SELECT uid FROM user WHERE status=1 ORDER BY uid DESC LIMIT 1)

SELECT * FROM t1 WHERE column1 = (SELECT MAX(column2) FROM t2)

SELECT * FROM article AS t WHERE 2 = (SELECT COUNT(*) FROM article WHERE article.uid = t.uid)

如：

SELECT * from animal where id = (SELECT id from fruit where name="荔枝");

SELECT * from animal where id = (SELECT MIN(price) from fruit)

SELECT * from stu where sage>(SELECT tage from teac where tage BETWEEN 30 and 40)

#當子查詢語句傳回的結果為多個數值時，無法傳遞給主查詢，執行查詢語句會報錯

<列子查詢>

指子查詢傳回的結果集是 N 行一列，該結果通常來自對表的某個字段查詢傳回。

可以使用 IN、ANY和 ALL 操作符

SELECT * FROM article WHERE uid IN(SELECT uid FROM user WHERE status=1)

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s2 FROM t2) # ANY是指取出 t2表中s2最小值

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s2 FROM t2) # ALL 大于t2表中s2的最大值

舉例如下：

SELECT * from stu where sage>(SELECT tage from teac where tage BETWEEN 30 and 40)

#當子查詢語句傳回的結果為多個數值時，無法傳遞給主查詢，執行查詢語句會報錯

SELECT * from stu where sage in(SELECT tage from teac where tage BETWEEN 30 and 40)

#當子查詢語句傳回的結果為多個數值時，可以通過列子查詢傳遞

SELECT * from stu where sage> ANY(SELECT tage from teac where tage BETWEEN 30 and 40)

#ANY是指在子查詢的結果中取最小值進行輸出

SELECT * from stu where sage> ALL(SELECT tage from teac where tage BETWEEN 30 and 40)

#ALL是指在子查詢的結果中取最大值進行輸出

<行子查詢>

指子查詢傳回的結果集是一行 N 列，該子查詢的結果通常是對表的某行資料進行查詢而傳回的結果集。

SELECT * FROM article WHERE (title,content,uid) = (SELECT title, content,uid FROM blog WHERE bid=2)

SELECT * from meat where (id,price)=(SELECT id,price from friut where name="banana") ;#同時可以指定2個或多個條件去比對另外的表

<表子查詢>

指子查詢傳回的結果集是 N 行 N 列的一個表資料。

SELECT * FROM article WHERE (title, content, uid) IN (SELECT title, content, uid FROM blog)

（6）多表查詢（UNION,JOIN）

1)連接配接查詢

同時涉及多個表的查詢

[等值連接配接]

例：查詢每個學生及其選修課程的情況

SELECT st.*,sc.* FROM st,sc WHERE st.sno = sc.sno;

2）union 聯合，，拼接

要拼接成一個表，2個表必須是列數相同（上下連接配接）；

可以聯合2個表進行查詢，合并輸出，表1：stu 表2：stu11

select name from stu union [ALL | DISTINCT] #ALL: 可選，傳回所有結果集，包含重複資料。

select name from stu11; #DISTINCT: 可選，删除結果集中重複的資料。預設情況下UNION 已經删除了重複資料

或

select name

from stu

where id=1

union

select name

from stu11

where id=2;

SELECT id,NAME,price from animal UNION SELECT id,NAME,price from fruit; #指定相同列，2表進行連接配接，縱向顯示；

也可以實作三表連接配接查詢，如下

SELECT * from meat UNION DISTINCT SELECT * from friut union SELECT * from veg;

3）連接配接（JOIN）

A, INNER JOIN（内連接配接）

接下來我們就使用MySQL的INNER JOIN(也可以省略 INNER 使用 JOIN，效果一樣)來連接配接以上兩張表來讀取

舉栗子：

SELECT c.id,c.tel,c1.name,c1.score from stu c INNER JOIN stu11 c1 on c.id=c1.id;

B, LEFT JOIN(左連接配接)

MySQL left join 與 join 有所不同。 MySQL LEFT JOIN 會讀取左邊資料表的全部資料，即便右邊表無對應資料。

舉栗子：

SELECT c.id,c.tel,c1.name,c1.score from stu c left JOIN stu11 c1 on c.id=c1.id;

#查詢後顯示結果條數以左表數量為主，即使右邊資料多，也不顯示

C，RIGHT JOIN（右連接配接）

MySQL RIGHT JOIN 會讀取右邊資料表的全部資料，即便左邊邊表無對應資料。

SELECT c.id,c.tel,c1.name,c1.score from stu c RIGHT JOIN stu11 c1 on c.id=c1.id;

#查詢後顯示結果條數以右表數量為主，即使左邊資料多，也不顯示

D，其它連接配接方式

[使用外連接配接]

A、left （outer） join：

左外連接配接（左連接配接）：結果集幾包括連接配接表的比對行，也包括左連接配接表的所有行。

SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c

1、左外連接配接：是A和B的交集再并上A的所有資料。

SELECT * FROM a LEFT OUTER JOIN b ON a.`ageId` = b.`id`

2、左外連接配接：其運算方式為：A左連接配接B的記錄=圖3公共部分記錄集C＋表A記錄集A1

B：right （outer） join:

1、右外連接配接：是A和B的交集再并上B的所有資料。

右外連接配接(右連接配接)：結果集既包括連接配接表的比對連接配接行，也包括右連接配接表的所有行。

SELECT * FROM a right OUTER JOIN b ON a.`ageId` = b.`id`

2、右外連接配接：其運算方式為：A右連接配接B的記錄=圖3公共部分記錄集C＋表B記錄集B1 。

C：cross （outer） join：

全外連接配接：不僅包括符号連接配接表的比對行，還包括兩個連接配接表中的所有記錄。

SELECT * from student_new s cross JOIN teacher t on s.sno=t.tno #效果類似于等值連接配接和内連接配接