2.5 數(shù)據(jù)表查詢操作

學(xué)習(xí)目標(biāo)

能夠熟練對(duì)數(shù)據(jù)表進(jìn)行各種查詢操作

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

2.5.1 數(shù)據(jù)查詢操作介紹

在數(shù)據(jù)庫(kù)操作中舵稠，使用頻率最多的是查詢操作。

查詢數(shù)據(jù)時(shí)浓体，根據(jù)不同的需求，條件對(duì)數(shù)據(jù)庫(kù)中的數(shù)據(jù)進(jìn)行查詢 熄浓，并返回結(jié)果越驻。

2.5.2 準(zhǔn)備工作,導(dǎo)入數(shù)據(jù)庫(kù)

在查詢之前讳苦，首先要有數(shù)據(jù)表和相應(yīng)的數(shù)據(jù)芒炼。

在 輔助資料中 有 school.sql 文件锉走，通過(guò)導(dǎo)入該文件來(lái)快速準(zhǔn)備數(shù)據(jù)滨彻。

? 導(dǎo)入之前需要先創(chuàng)建一個(gè)數(shù)據(jù)庫(kù)

? 使用新創(chuàng)建的數(shù)據(jù)庫(kù)

? 使用 source 文件地址 導(dǎo)入數(shù)據(jù)

? ? ? 注意，導(dǎo)入數(shù)據(jù)庫(kù)時(shí)的文件地址盡量使用絕對(duì)路徑挪蹭，或者把文件放在家目錄下

2.5.3 單表查詢數(shù)據(jù)

查詢數(shù)據(jù)庫(kù)使用 select 命令亭饵。 這個(gè)命令相對(duì)比較復(fù)雜×豪鳎可變化樣式較多辜羊，這里分功能依次講解。

1.數(shù)據(jù)表單表查詢數(shù)據(jù)總結(jié)：

select 字段名[,(字段名…)] from 表名;

? <1>查詢數(shù)據(jù)表中所有數(shù)據(jù)

? ? ? 語(yǔ)法：select * from 表名

? ? ? select * from t_student;

? <2>查詢指定字段的顯示

? ? ? 語(yǔ)法：select 字段1词顾，字段2八秃，... from 表名

? ? ? select c_id,c_name,c_address from t_student；

? <3>as 別名

? ? ? 在查詢時(shí)肉盹，默認(rèn)結(jié)果顯示的字段和表中字段名相同昔驱，可以通過(guò)別名來(lái)修改顯示的樣

? ? ? 給字段起別名

? ? ? ? ? ■? 語(yǔ)法：select 字段1 as 別名，字段2 別名垮媒，... from 表名

? ? ? ? ? ■ select c_id as 學(xué)號(hào) ,c_name as 姓名 ,c_address 地址 from t_student;

? ? ? 給數(shù)據(jù)表起別名

? ? ? ? ? ■ 語(yǔ)法：select 字段1，字段2 別名航棱，... from 表名 as 別名

? ? ? 注意：

? ? ? ? ? ■ 在給字段或表起別名時(shí)睡雇，可以使用 as ，也可以直接在字段后跟別名饮醇，省略 as 它抱。

? ? ? ? ? ■ 如果起了別名，原名將不起作用朴艰！后面的語(yǔ)句中只能使用別名

? <4>消除重復(fù)數(shù)據(jù)distinct

? ? ? 在查詢數(shù)據(jù)時(shí)观蓄，查詢結(jié)果可能會(huì)有很多重復(fù)的數(shù)據(jù)混移，如果不想重復(fù)，可以使用 distinct 來(lái)實(shí)現(xiàn)去重侮穿。

? ? ? 語(yǔ)法：select distinct 字段名 from 表名

? ? ? 語(yǔ)法：select distinct 字段名1 字段名2 from 表名

? ? ? select distinct c_address from t_student;

? ? ? 注意：

? ? ? ? ? ■ 可以指定多個(gè)字段名

? ? ? ? ? ■ distinct 在去重時(shí)歌径，會(huì)比較所有的指定字段，只有完全相同時(shí)才認(rèn)為是重復(fù)的亲茅。

? <5>帶條件查詢where

? ? ? where 子句 查詢數(shù)據(jù)時(shí)回铛，需要根據(jù)不同的需求設(shè)置條件。 通過(guò) where 子句來(lái)設(shè)置查詢條件

? ? ? 語(yǔ)法：select 字段名…? form? 表名 where? 條件;

? ? ? select * from t_student where c_gender='男';

? ? ? 注意：

? ? ? ? ? ■ where 條件可以使用運(yùn)算符操作克锣。

? 1>比較運(yùn)算符

? ? ? 等于: =

? ? ? 大于: >

? ? ? 大于等于: >=

? ? ? 小于: <

? ? ? 小于等于: <=

? ? ? 不等于: != 或 <>

? ? ? ? ? ■ select * from t_student where c_age < 20;

? ? ? 注意sql中只有一個(gè)等號(hào)茵肃，沒(méi)有==這種形式

? 2>邏輯運(yùn)算符

? ? ? and

? ? ? or

? ? ? not

? ? ? ? ? ■ select * from t_student where c_age < 20 and c_gender = '女';

? <6>模糊查詢like

? ? ? like（這是一個(gè)操作符）

? ? ? % 表示任意多個(gè)任意字符

? ? ? _ 表示一個(gè)任意字符

? ? ? ? ? ■ select * from t_student where c_name like '孫';

? ? ? ? ? ■ select * from t_student where c_name like '孫%';

? ? ? ? ? ■ select * from t_student where c_name like '孫_';

? <7>范圍查詢，非連續(xù)范圍in袭祟，連續(xù)范圍between ... and ...

? ? ? in 表示在一個(gè)非連續(xù)的范圍內(nèi) , 可以使用 or 實(shí)現(xiàn)

? ? ? ? ? ■ select * from t_students where id in(1,3,8);

? ? ? between ... and ... 表示在一個(gè)連續(xù)的范圍內(nèi)验残，可以使用 and 實(shí)現(xiàn) ```

? ? ? ? ? ■ select * from t_students where c_id between 2 and 5;

? ? ? ? ? ■ 相當(dāng)于select * from t_students where c_id >=2 and c_id <= 5;

? <8>空判斷is null、is not null

? ? ? 在數(shù)據(jù)庫(kù)中巾乳，允許在數(shù)據(jù)添加是沒(méi)有數(shù)據(jù)您没，使用空值來(lái)表示。 空值不等于0想鹰，也不等于''紊婉，需要使用特殊的判斷方式

? ? ? 判斷空值

? ? ? ? ? ■ 語(yǔ)法：is null

? ? ? ? ? ? ? select * from t_student where c_age is null;

? ? ? 判斷非空值

? ? ? ? ? ■ 語(yǔ)法：is not null

? ? ? ? ? ? ? select * from t_student where c_age is not null;

? <9>查詢結(jié)果排序order by

? ? ? 排序order by（子句）是一個(gè)在查詢數(shù)據(jù)時(shí)非常重要的操作。比如買東西時(shí)辑舷，想按一定的條件進(jìn)行有序顯示喻犁。就需要使用排序

? ? ? 排序使用 order by 子段名 asc(默認(rèn)) 升序 / desc 降序

? ? ? 語(yǔ)法：select * from 表名 order by 列1 asc/desc [,列2 asc/desc,...]

? ? ? 單字段排序

? select * from t_student order by c_age;

? select * from t_student order by c_age asc;

? 默認(rèn)使用就是升序排序，可以不指定 asc 何缓，效果相同肢础。

? ? ? 多字段排序

? ? ? ? ? ■ 可以對(duì)多個(gè)字段進(jìn)行排序，只需將字段的排序方式依次寫在 order by 后面即可碌廓，字段間使用逗號(hào)分隔

? ? ? ? ? ■ select * from t_student order by c_age desc,c_id asc;

? <10>分頁(yè)查詢limit

? ? ? 查詢數(shù)據(jù)庫(kù)時(shí)传轰，由于數(shù)據(jù)較多，在顯示過(guò)程中不可能將數(shù)據(jù)全部顯示谷婆。 可以使用分頁(yè)查詢慨蛙，只顯示指定的一部分?jǐn)?shù)據(jù)

? ? ? 語(yǔ)法：select 字段名 from 表名 limit start=0(開(kāi)始索引默認(rèn)為0),count(記錄條數(shù))

? ? ? ? ? ■ 從start開(kāi)始，獲取count條數(shù)據(jù)纪挎，start默認(rèn)值為0

? ? ? ? ? ■ 需要獲取數(shù)據(jù)的前n條的時(shí)候可以直接寫 limit n

? ? ? ? ? ■ select * from t_student limit 3;

? ? ? ? ? ■ select * from t_student limit 2,3;

? ? ? ? ? ■ 注意：查詢第 N 頁(yè) M 條數(shù)據(jù)期贫，可以通過(guò)公式算出：(N - 1) * M

? <11>聚合函數(shù)

? ? ? 在 MySQL 中提供了一些定義好的函數(shù)，利用這些函數(shù)提供對(duì)數(shù)據(jù)的統(tǒng)計(jì)功能异袄，聚合函數(shù)一般配合分組功能一起使用通砍。

? ? ? 1>sum 求和函數(shù) 對(duì)指定的字段求和

? ? ? ? ? ■ select sum(c_age) from t_student;

? ? ? 2>avg 求平均值函數(shù) 對(duì)指定字段求平均值

? ? ? ? ? ■ select avg(c_age) from t_student;

? ? ? 3>max 求最大值函數(shù)

? ? ? ? ? ■ select max(c_age) from t_student where c_gender = '男';

? ? ? 4>min 求最小值函數(shù)

? ? ? ? ? ■ select min(c_age) from t_student where c_gender = '女';

? ? ? 5>count 統(tǒng)計(jì)記錄總數(shù)

? ? ? ? ? ■ select count(*) from t_student;

? ? ? ? ? ■ select count(*) from t_student where c_gender = '女';

? ? ? 6>group_concat() 拼接分組中的數(shù)據(jù)

? <12>分組group by

? ? ? 分組就是將相同數(shù)據(jù)放到一起進(jìn)行處理。 單純的分組是沒(méi)有意義的，需要配合聚合函數(shù)一起使用封孙。

? ? ? group by的含義:將查詢結(jié)果按照1個(gè)或多個(gè)字段進(jìn)行分組迹冤，字段值相同的為一組

? ? ? group by可用于單個(gè)字段分組，也可用于多個(gè)字段分組

? ? ? 語(yǔ)法： select 分組的字段名,聚合函數(shù)... from 表名 group by 分組字段名 having 分組后的條件

? ? ? 注意：

? ? ? ? ? ■ 在執(zhí)行 group by 分組時(shí)虎忌，select 后只能有被分組的字段泡徙，不允許有其它字段，除非這些字段在聚合函數(shù)中

? ? ? ? ? ? ? 查詢顯示的字段必須和分組的字段相同

? ? ? ? ? ■ 分組一般配合聚合函數(shù)使用呐籽，做數(shù)據(jù)統(tǒng)計(jì)功能

? ? ? ? ? ■ 分組后如果需要設(shè)置條件要使用 having 指定

? ? ? 1>單字段分組

? ? ? ? ? ■ select c_gender from t_student group by c_gender;

? ? ? 2>多字段分組（了解）

? ? ? ? ? ■ 可以對(duì)多個(gè)字段進(jìn)行分組锋勺，作用同上，需要注意的是多字段時(shí)狡蝶，只有對(duì)應(yīng)字段完全相同庶橱，才能分為同一組

? ? ? ? ? ■? select c_gender,c_address from t_student group by c_gender,c_address;

? ? ? 3>group by + group_concat()

? ? ? ? ? ■ group_concat(字段名)可以作為一個(gè)輸出字段來(lái)使用，表示分組之后贪惹，根據(jù)分組結(jié)果苏章，使用group_concat()來(lái)放置每一組的某字段的值的sel集合

? ? ? ? ? ■ 作用：根據(jù)分組結(jié)果，使用group_concat()來(lái)獲取分組中指定字段的集合

? ? ? ? ? ■ 語(yǔ)法：group_concat(字段名)

? ? ? ? ? ? ? select c_gender,group_concat(c_name) from t_student group by c_gender;

? ? ? 4>分組和聚和函數(shù)使用

? ? ? ? ? ■ 單純的使用分組并沒(méi)有實(shí)際意義奏瞬，需要使用聚合函數(shù)對(duì)數(shù)據(jù)進(jìn)行處理枫绅。

? ? ? ? ? ? ? select c_gender,max(c_age),min(c_age),sum(c_age),avg(c_age),count(*) from t_student group by c_gender;

? ? ? ? ? ? ? select c_gender,max(c_age),min(c_age),sum(c_age),avg(c_age),count(c_age) from t_student group by c_gender;

? ? ? 5>having 作用和 where 類似，用來(lái)對(duì)分組數(shù)據(jù)進(jìn)行篩選

? ? ? ? ? ■ 注意having只能配合分組使用

? ? ? ? ? ■ where 是對(duì) form 表 中取數(shù)據(jù)時(shí)進(jìn)行篩選

? ? ? ? ? ■ having 是對(duì) group by 分組后的數(shù)據(jù)進(jìn)行篩選

? ? ? ? ? ■ 因?yàn)樵趫?zhí)行順序上硼端，在執(zhí)行 where 時(shí)并淋，分組還沒(méi)有執(zhí)行，得先根據(jù) where 的條件取出數(shù)據(jù)珍昨，才能去取出的數(shù)據(jù)進(jìn)行分組县耽。

? ? ? ? ? ? ? select c_gender,group_concat(c_name) from t_student group by c_gender having c_gender = '女';

? ? ? ? ? ? ? select c_gender,group_concat(c_name) from t_student where c_age > 50 group by c_gender having c_gender = '女';

? ? ? 6>分組匯總（無(wú)大用，了解即可）

? ? ? ? ? ■ 作用：會(huì)在分組下方镣典，加一行兔毙，顯示匯總

? ? ? ? ? ■ 語(yǔ)法：with rollup

? ? ? ? ? ? ? select c_gender from t_student group by c_gender with rollup;

? ? ? ? ? ? ? select c_gender,count(*) from t_student group by c_gender with rollup;

2.5.4 多表查詢數(shù)據(jù)

在數(shù)據(jù)庫(kù)操作中，數(shù)據(jù)往往不是存在一張表中的兄春，同一個(gè)項(xiàng)目中澎剥，根據(jù)設(shè)計(jì)范式，數(shù)據(jù)可能分散在不同的多張表中赶舆，這時(shí)查詢數(shù)據(jù)時(shí)哑姚，就需要多表查詢。

? <1>普通多表查詢（無(wú)意義）

? ? ? 作用：直接將表放在from后面芜茵，進(jìn)行讀取叙量。

? ? ? 語(yǔ)法：select 表名.字段 ... from 表名1，表名2...

? ? ? ? ? ■? select * from t_student,t_class;

? ? ? 這種查詢方式?jīng)]有任何意義夕晓。 在查詢時(shí)宛乃，數(shù)據(jù)庫(kù)會(huì)將表1中的數(shù)據(jù)逐條和表2中的所有數(shù)據(jù)連接悠咱，組成一條新記錄蒸辆。 查詢的結(jié)果為 M * N 條征炼，實(shí)際就是笛卡爾積結(jié)果。

? <2>多表查詢連接條件

? ? ? 在多表個(gè)表進(jìn)行查詢時(shí)躬贡，表與表之間應(yīng)該是有有關(guān)系的谆奥，一般會(huì)以外鍵的形式來(lái)建立表間的關(guān)系。 查詢時(shí)按照條件建立記錄的匹配規(guī)則拂玻。 比如學(xué)生表中保存了學(xué)生的信息和所在班級(jí)的ID酸些，班級(jí)表中保存了班級(jí)的信息。 在查詢學(xué)生的班級(jí)信息時(shí)檐蚜，可以通過(guò)學(xué)生表中的班級(jí)ID和班級(jí)表中的ID匹配進(jìn)行查詢

? ? ? select t_student.c_name,t_class.c_name? from t_student,t_class where t_student.c_class_id = t_class.c_id;

? <3>內(nèi)連接查詢（交集）

? ? ? 作用：查詢的結(jié)果為兩個(gè)表匹配到的數(shù)據(jù)魄懂，內(nèi)連接指定連接條件取兩表的交集

? ? ? 語(yǔ)法：select * from 表1 inner join 表2 on 表1.列 運(yùn)算符 表2.列

? ? ? 數(shù)據(jù)庫(kù)默認(rèn)的連接方式就是內(nèi)連接查詢，inner join 可以不顯示的寫出來(lái)闯第。 不指定連接條件時(shí)市栗，實(shí)際就是普通多表連接，會(huì)以笛卡爾積的形式進(jìn)行連接咳短。 所以在連接時(shí)填帽，必須要給定連接條件。 連接條件使用 on 進(jìn)行指定咙好。盡量不要使用 where,where在其它連接方式時(shí)篡腌，指定的連接條件無(wú)效。

? ? ? ? ? ■ select ts.c_name, tc.c_name from t_student as ts inner join t_class tc on ts.c_class_id = tc.c_id;

? <4>左連接查詢

? ? ? 作用：查詢的結(jié)果為根據(jù)左表中的數(shù)據(jù)進(jìn)行連接勾效，如果右表中沒(méi)有滿足條件的記錄嘹悼，則連接空值。

? ? ? 注意：連接條件只能使用 on 指定

? ? ? 語(yǔ)法： select * from 表1 left join 表2 on 表1.列 運(yùn)算符 表2.列

? ? ? ? ? ■ select ts.c_name, tc.c_name from t_student as ts left join t_class tc on ts.c_class_id = tc.c_id;

? <5>右連接查詢

? ? ? 作用：查詢的結(jié)果為根據(jù)右表中的數(shù)據(jù)進(jìn)行連接葵第，如果左表中沒(méi)有滿足條件的記錄绘迁，則連接空值。

? ? ? 注意：連接條件只能使用 on 指定

? ? ? 語(yǔ)法： select * from 表1 right join 表2 on 表1.列 運(yùn)算符 表2.列

? ? ? ? ? ■ select ts.c_name, tc.c_name from t_student as ts right join t_class tc on ts.c_class_id = tc.c_id;

? ? ? 在實(shí)際工作中卒密，右連接使用的非常少缀台，因?yàn)樽筮B接完全可以替代右連接，在連接過(guò)程中哮奇，只需要調(diào)整表的順序即可膛腐。

? <6>子查詢

? ? ? 作用：在一個(gè) select 語(yǔ)句中,嵌入了另外一個(gè) select 語(yǔ)句, 那么被嵌入的 select 語(yǔ)句稱之為子查詢語(yǔ)句，子查詢語(yǔ)句是一個(gè)可以獨(dú)立執(zhí)行的查詢語(yǔ)句

? ? ? 語(yǔ)法： select * from 表1 where 條件 運(yùn)算符 （select 查詢）

? 外部那個(gè)select語(yǔ)句則稱為主查詢

? 主查詢和子查詢的關(guān)系

? ? ? ? ? ■ 子查詢是嵌入到主查詢中

? ? ? ? ? ■ 子查詢是輔助主查詢的,要么充當(dāng)條件,要么充當(dāng)數(shù)據(jù)源

? ? ? ? ? ■ 子查詢是可以獨(dú)立存在的語(yǔ)句,是一條完整的 select 語(yǔ)句

? 1>標(biāo)量子查詢

? ? ? 作用：子查詢返回的結(jié)果是一個(gè)數(shù)據(jù)(一行一列)

? ? ? 語(yǔ)法：主查詢 where 條件 比較運(yùn)算符 (列子查詢)

? ? ? ? ? ? ? 查詢班級(jí)中年齡大于平均年齡的學(xué)生信息

? ? ? ? ? ? ? 查詢班級(jí)學(xué)生平均年齡

? ? ? ? ? ? ? 查詢大于平均年齡的學(xué)生

? ? ? ? ? ? ? select * from t_student where c_age > (select avg(c_age) from t_student);

? 2>列級(jí)子查詢

? ? ? 作用：子查詢返回的結(jié)果是一列(一列多行)

? ? ? 語(yǔ)法：主查詢 where 條件 in (列子查詢)

? ? ? ? ? ? ? 查詢所有學(xué)生所在班級(jí)的班級(jí)名稱

? ? ? ? ? ? ? 找出學(xué)生表中所有的班級(jí) id

? ? ? ? ? ? ? 找出班級(jí)表中對(duì)應(yīng)的名字

? ? ? ? ? ? ? select * from t_class where c_id in (select c_class_id from t_student);

? 3>行級(jí)子查詢

? ? ? 作用：子查詢返回的結(jié)果是一行(一行多列)

? ? ? 語(yǔ)法：主查詢 where (字段1,2,...) = (行子查詢)

? ? ? ? ? ? ? 查找班級(jí)年齡最大,所在班號(hào)最小的的學(xué)生

? ? ? ? ? ? ? 找出最大年齡和最小班號(hào)

? ? ? ? ? ? ? 找出年齡和班號(hào)滿足條件的學(xué)生

? ? ? ? ? ? ? select * from t_student where(c_age,c_class_id) = (select max(c_age),min(c_class_id) from t_student);

? <7>自連接查詢

? ? ? 作用：在查詢數(shù)據(jù)時(shí)鼎俘，只有一張表哲身，查詢時(shí)使用自己連接自己。

? ? ? 語(yǔ)法： select * from 表1 inner join 表2 on 表1.列 運(yùn)算符 表2.列 where 條件

? 為什么需要自連接

? ? ? 設(shè)計(jì)表結(jié)構(gòu)來(lái)存儲(chǔ) 全國(guó) 所有的省份和 全國(guó)所有的市

? ? ? 設(shè)計(jì)省信息的表結(jié)構(gòu)provinces

? ? ? id 省的編號(hào)

? ? ? ptitle 省名稱

? ? ? 設(shè)計(jì)市信息的表結(jié)構(gòu)citys

? ? ? id 市編號(hào)

? ? ? ctitle 市名稱

? ? ? proid 市所屬的省的編號(hào)

? citys表的proid表示城市所屬的省贸伐，對(duì)應(yīng)著provinces表的id值

? 將兩個(gè)表合為一個(gè)

? 定義表areas勘天，結(jié)構(gòu)如下

? ? ? ? ? ■ id

? ? ? ? ? ■ atitle

? ? ? ? ? ■ pid

? ? ? 關(guān)于這個(gè)表的說(shuō)明:

? ? ? ? ? ■ 因?yàn)槭](méi)有所屬的省份，所以可以填寫為null

? ? ? ? ? ■ 城市所屬的省份pid，填寫省所對(duì)應(yīng)的編號(hào)id

? ? ? ? ? ■ 這就是自關(guān)聯(lián)脯丝，表中的某一列商膊，關(guān)聯(lián)了這個(gè)表中的另外一列，但是它們的業(yè)務(wù)邏輯含義是不一樣的宠进，城市信息的pid引用的是省信息的id

? ? ? ? ? ■ 在這個(gè)表中晕拆，結(jié)構(gòu)不變，可以添加區(qū)縣材蹬、鄉(xiāng)鎮(zhèn)街道实幕、村社區(qū)等信息

? areas表和自身進(jìn)行連接這種形式的連接 就成為自連接。

? 創(chuàng)建areas表的語(yǔ)句如下：

? ? ? 注意堤器，表所在的數(shù)據(jù)庫(kù)字符集必須是utf8的昆庇，如果不是會(huì)導(dǎo)入數(shù)據(jù)出錯(cuò)

? ? ? ? ? ■ create table areas(aid int primary key,atitle varchar(20),pid int);

? 從sql文件中導(dǎo)入數(shù)據(jù)

? ? ? source areas.sql;

? 查詢一共有多少個(gè)省

? ? ? select count(*) from areas where pid is null;

? 例1：查詢省的名稱為“山西省”的所有城市

? ? ? select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';

? 例2：查詢市的名稱為“廣州市”的所有區(qū)縣

? ? ? select dis.* from areas as dis inner join areas as city on city.aid=dis.pid where city.atitle='廣州市';