2.5 數(shù)據(jù)表查詢操作
學(xué)習(xí)目標
能夠熟練對數(shù)據(jù)表進行各種查詢操作
--------------------------------------------------------------------------------
2.5.1 數(shù)據(jù)查詢操作介紹
在數(shù)據(jù)庫操作中，使用頻率最多的是查詢操作懈词。
查詢數(shù)據(jù)時，根據(jù)不同的需求辩诞，條件對數(shù)據(jù)庫中的數(shù)據(jù)進行查詢 坎弯，并返回結(jié)果。
2.5.2 準備工作,導(dǎo)入數(shù)據(jù)庫
在查詢之前译暂，首先要有數(shù)據(jù)表和相應(yīng)的數(shù)據(jù)抠忘。
在 輔助資料中 有 school.sql 文件，通過導(dǎo)入該文件來快速準備數(shù)據(jù)外永。
? 導(dǎo)入之前需要先創(chuàng)建一個數(shù)據(jù)庫
? 使用新創(chuàng)建的數(shù)據(jù)庫
? 使用 source 文件地址 導(dǎo)入數(shù)據(jù)
? ? ? 注意崎脉，導(dǎo)入數(shù)據(jù)庫時的文件地址盡量使用絕對路徑，或者把文件放在家目錄下
2.5.3 單表查詢數(shù)據(jù)
查詢數(shù)據(jù)庫使用 select 命令伯顶。 這個命令相對比較復(fù)雜囚灼。可變化樣式較多祭衩，這里分功能依次講解灶体。
1.數(shù)據(jù)表單表查詢數(shù)據(jù)總結(jié)：
select 字段名[,(字段名…)] from 表名;
? <1>查詢數(shù)據(jù)表中所有數(shù)據(jù)
? ? ? 語法：select * from 表名
? ? ? select * from t_student;
? <2>查詢指定字段的顯示
? ? ? 語法：select 字段1，字段2掐暮，... from 表名
? ? ? select c_id,c_name,c_address from t_student蝎抽；
? <3>as 別名
? ? ? 在查詢時，默認結(jié)果顯示的字段和表中字段名相同路克，可以通過別名來修改顯示的樣
? ? ? 給字段起別名
? ? ? ? ? ■? 語法：select 字段1 as 別名，字段2 別名瓢宦，... from 表名
? ? ? ? ? ■ select c_id as 學(xué)號 ,c_name as 姓名 ,c_address 地址 from t_student;
? ? ? 給數(shù)據(jù)表起別名
? ? ? ? ? ■ 語法：select 字段1破花，字段2 別名前鹅，... from 表名 as 別名
? ? ? 注意：
? ? ? ? ? ■ 在給字段或表起別名時，可以使用 as 口四，也可以直接在字段后跟別名蔓彩，省略 as 顺又。
? ? ? ? ? ■ 如果起了別名蹂空，原名將不起作用！后面的語句中只能使用別名
? <4>消除重復(fù)數(shù)據(jù)distinct
? ? ? 在查詢數(shù)據(jù)時，查詢結(jié)果可能會有很多重復(fù)的數(shù)據(jù)，如果不想重復(fù)，可以使用 distinct 來實現(xiàn)去重去团。
? ? ? 語法：select distinct 字段名 from 表名
? ? ? 語法：select distinct 字段名1 字段名2 from 表名
? ? ? select distinct c_address from t_student;
? ? ? 注意：
? ? ? ? ? ■ 可以指定多個字段名
? ? ? ? ? ■ distinct 在去重時昼汗，會比較所有的指定字段，只有完全相同時才認為是重復(fù)的。
? <5>帶條件查詢where
? ? ? where 子句 查詢數(shù)據(jù)時谓着，需要根據(jù)不同的需求設(shè)置條件。 通過 where 子句來設(shè)置查詢條件
? ? ? 語法：select 字段名…? form? 表名 where? 條件;
? ? ? select * from t_student where c_gender='男';
? ? ? 注意：
? ? ? ? ? ■ where 條件可以使用運算符操作改抡。
? 1>比較運算符
? ? ? 等于: =
? ? ? 大于: >
? ? ? 大于等于: >=
? ? ? 小于: <
? ? ? 小于等于: <=
? ? ? 不等于: != 或 <>
? ? ? ? ? ■ select * from t_student where c_age < 20;
? ? ? 注意sql中只有一個等號夷陋，沒有==這種形式
? 2>邏輯運算符
? ? ? and
? ? ? or
? ? ? not
? ? ? ? ? ■ select * from t_student where c_age < 20 and c_gender = '女';
? <6>模糊查詢like
? ? ? like（這是一個操作符）
? ? ? % 表示任意多個任意字符
? ? ? _ 表示一個任意字符
? ? ? ? ? ■ 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 表示在一個非連續(xù)的范圍內(nèi) , 可以使用 or 實現(xiàn)
? ? ? ? ? ■ select * from t_students where id in(1,3,8);
? ? ? between ... and ... 表示在一個連續(xù)的范圍內(nèi)，可以使用 and 實現(xiàn) ```
? ? ? ? ? ■ select * from t_students where c_id between 2 and 5;
? ? ? ? ? ■ 相當于select * from t_students where c_id >=2 and c_id <= 5;
? <8>空判斷is null、is not null
? ? ? 在數(shù)據(jù)庫中，允許在數(shù)據(jù)添加是沒有數(shù)據(jù)，使用空值來表示棺耍。 空值不等于0，也不等于''种樱，需要使用特殊的判斷方式
? ? ? 判斷空值
? ? ? ? ? ■ 語法：is null
? ? ? ? ? ? ? select * from t_student where c_age is null;
? ? ? 判斷非空值
? ? ? ? ? ■ 語法：is not null
? ? ? ? ? ? ? select * from t_student where c_age is not null;
? <9>查詢結(jié)果排序order by
? ? ? 排序order by（子句）是一個在查詢數(shù)據(jù)時非常重要的操作蒙袍。比如買東西時，想按一定的條件進行有序顯示嫩挤。就需要使用排序
? ? ? 排序使用 order by 子段名 asc(默認) 升序 / desc 降序
? ? ? 語法：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;
? 默認使用就是升序排序害幅，可以不指定 asc ，效果相同岂昭。
? ? ? 多字段排序
? ? ? ? ? ■ 可以對多個字段進行排序以现，只需將字段的排序方式依次寫在 order by 后面即可，字段間使用逗號分隔
? ? ? ? ? ■ select * from t_student order by c_age desc,c_id asc;
? <10>分頁查詢limit
? ? ? 查詢數(shù)據(jù)庫時约啊，由于數(shù)據(jù)較多邑遏，在顯示過程中不可能將數(shù)據(jù)全部顯示。 可以使用分頁查詢恰矩，只顯示指定的一部分數(shù)據(jù)
? ? ? 語法：select 字段名 from 表名 limit start=0(開始索引默認為0),count(記錄條數(shù))
? ? ? ? ? ■ 從start開始记盒，獲取count條數(shù)據(jù)，start默認值為0
? ? ? ? ? ■ 需要獲取數(shù)據(jù)的前n條的時候可以直接寫 limit n
? ? ? ? ? ■ select * from t_student limit 3;
? ? ? ? ? ■ select * from t_student limit 2,3;
? ? ? ? ? ■ 注意：查詢第 N 頁 M 條數(shù)據(jù)外傅，可以通過公式算出：(N - 1) * M
? <11>聚合函數(shù)
? ? ? 在 MySQL 中提供了一些定義好的函數(shù)纪吮，利用這些函數(shù)提供對數(shù)據(jù)的統(tǒng)計功能，聚合函數(shù)一般配合分組功能一起使用萎胰。
? ? ? 1>sum 求和函數(shù) 對指定的字段求和
? ? ? ? ? ■ select sum(c_age) from t_student;
? ? ? 2>avg 求平均值函數(shù) 對指定字段求平均值
? ? ? ? ? ■ 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)計記錄總數(shù)
? ? ? ? ? ■ select count(*) from t_student;
? ? ? ? ? ■ select count(*) from t_student where c_gender = '女';
? ? ? 6>group_concat() 拼接分組中的數(shù)據(jù)
? <12>分組group by
? ? ? 分組就是將相同數(shù)據(jù)放到一起進行處理碾盟。 單純的分組是沒有意義的，需要配合聚合函數(shù)一起使用技竟。
? ? ? group by的含義:將查詢結(jié)果按照1個或多個字段進行分組冰肴，字段值相同的為一組
? ? ? group by可用于單個字段分組，也可用于多個字段分組
? ? ? 語法： select 分組的字段名,聚合函數(shù)... from 表名 group by 分組字段名 having 分組后的條件
? ? ? 注意：
? ? ? ? ? ■ 在執(zhí)行 group by 分組時灵奖，select 后只能有被分組的字段嚼沿，不允許有其它字段，除非這些字段在聚合函數(shù)中
? ? ? ? ? ? ? 查詢顯示的字段必須和分組的字段相同
? ? ? ? ? ■ 分組一般配合聚合函數(shù)使用瓷患，做數(shù)據(jù)統(tǒng)計功能
? ? ? ? ? ■ 分組后如果需要設(shè)置條件要使用 having 指定
? ? ? 1>單字段分組
? ? ? ? ? ■ select c_gender from t_student group by c_gender;
? ? ? 2>多字段分組（了解）
? ? ? ? ? ■ 可以對多個字段進行分組骡尽，作用同上，需要注意的是多字段時擅编，只有對應(yīng)字段完全相同攀细，才能分為同一組
? ? ? ? ? ■? select c_gender,c_address from t_student group by c_gender,c_address;
? ? ? 3>group by + group_concat()
? ? ? ? ? ■ group_concat(字段名)可以作為一個輸出字段來使用箫踩，表示分組之后，根據(jù)分組結(jié)果谭贪，使用group_concat()來放置每一組的某字段的值的sel集合
? ? ? ? ? ■ 作用：根據(jù)分組結(jié)果境钟，使用group_concat()來獲取分組中指定字段的集合
? ? ? ? ? ■ 語法：group_concat(字段名)
? ? ? ? ? ? ? select c_gender,group_concat(c_name) from t_student group by c_gender;
? ? ? 4>分組和聚和函數(shù)使用
? ? ? ? ? ■ 單純的使用分組并沒有實際意義，需要使用聚合函數(shù)對數(shù)據(jù)進行處理俭识。
? ? ? ? ? ? ? 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 類似慨削，用來對分組數(shù)據(jù)進行篩選
? ? ? ? ? ■ 注意having只能配合分組使用
? ? ? ? ? ■ where 是對 form 表 中取數(shù)據(jù)時進行篩選
? ? ? ? ? ■ having 是對 group by 分組后的數(shù)據(jù)進行篩選
? ? ? ? ? ■ 因為在執(zhí)行順序上，在執(zhí)行 where 時套媚，分組還沒有執(zhí)行缚态，得先根據(jù) where 的條件取出數(shù)據(jù)，才能去取出的數(shù)據(jù)進行分組堤瘤。
? ? ? ? ? ? ? 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>分組匯總（無大用玫芦，了解即可）
? ? ? ? ? ■ 作用：會在分組下方，加一行本辐，顯示匯總
? ? ? ? ? ■ 語法：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ù)庫操作中桥帆，數(shù)據(jù)往往不是存在一張表中的，同一個項目中慎皱，根據(jù)設(shè)計范式老虫，數(shù)據(jù)可能分散在不同的多張表中，這時查詢數(shù)據(jù)時茫多，就需要多表查詢张遭。
? <1>普通多表查詢（無意義）
? ? ? 作用：直接將表放在from后面，進行讀取地梨。
? ? ? 語法：select 表名.字段 ... from 表名1菊卷，表名2...
? ? ? ? ? ■? select * from t_student,t_class;
? ? ? 這種查詢方式?jīng)]有任何意義。 在查詢時宝剖，數(shù)據(jù)庫會將表1中的數(shù)據(jù)逐條和表2中的所有數(shù)據(jù)連接洁闰，組成一條新記錄。 查詢的結(jié)果為 M * N 條万细，實際就是笛卡爾積結(jié)果扑眉。
? <2>多表查詢連接條件
? ? ? 在多表個表進行查詢時，表與表之間應(yīng)該是有有關(guān)系的赖钞，一般會以外鍵的形式來建立表間的關(guān)系腰素。 查詢時按照條件建立記錄的匹配規(guī)則。 比如學(xué)生表中保存了學(xué)生的信息和所在班級的ID雪营，班級表中保存了班級的信息弓千。 在查詢學(xué)生的班級信息時，可以通過學(xué)生表中的班級ID和班級表中的ID匹配進行查詢
? ? ? 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é)果為兩個表匹配到的數(shù)據(jù)献起，內(nèi)連接指定連接條件取兩表的交集
? ? ? 語法：select * from 表1 inner join 表2 on 表1.列 運算符 表2.列
? ? ? 數(shù)據(jù)庫默認的連接方式就是內(nèi)連接查詢洋访，inner join 可以不顯示的寫出來镣陕。 不指定連接條件時，實際就是普通多表連接姻政，會以笛卡爾積的形式進行連接呆抑。 所以在連接時，必須要給定連接條件汁展。 連接條件使用 on 進行指定鹊碍。盡量不要使用 where,where在其它連接方式時，指定的連接條件無效食绿。
? ? ? ? ? ■ 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ù)進行連接妹萨，如果右表中沒有滿足條件的記錄，則連接空值炫欺。
? ? ? 注意：連接條件只能使用 on 指定
? ? ? 語法： select * from 表1 left join 表2 on 表1.列 運算符 表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ù)進行連接，如果左表中沒有滿足條件的記錄熏兄，則連接空值品洛。
? ? ? 注意：連接條件只能使用 on 指定
? ? ? 語法： select * from 表1 right join 表2 on 表1.列 運算符 表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;
? ? ? 在實際工作中，右連接使用的非常少摩桶，因為左連接完全可以替代右連接桥状，在連接過程中，只需要調(diào)整表的順序即可硝清。
? <6>子查詢
? ? ? 作用：在一個 select 語句中,嵌入了另外一個 select 語句, 那么被嵌入的 select 語句稱之為子查詢語句辅斟，子查詢語句是一個可以獨立執(zhí)行的查詢語句
? ? ? 語法： select * from 表1 where 條件 運算符 （select 查詢）
? 外部那個select語句則稱為主查詢
? 主查詢和子查詢的關(guān)系
? ? ? ? ? ■ 子查詢是嵌入到主查詢中
? ? ? ? ? ■ 子查詢是輔助主查詢的,要么充當條件,要么充當數(shù)據(jù)源
? ? ? ? ? ■ 子查詢是可以獨立存在的語句,是一條完整的 select 語句
? 1>標量子查詢
? ? ? 作用：子查詢返回的結(jié)果是一個數(shù)據(jù)(一行一列)
? ? ? 語法：主查詢 where 條件 比較運算符 (列子查詢)
? ? ? ? ? ? ? 查詢班級中年齡大于平均年齡的學(xué)生信息
? ? ? ? ? ? ? 查詢班級學(xué)生平均年齡
? ? ? ? ? ? ? 查詢大于平均年齡的學(xué)生
? ? ? ? ? ? ? select * from t_student where c_age > (select avg(c_age) from t_student);
? 2>列級子查詢
? ? ? 作用：子查詢返回的結(jié)果是一列(一列多行)
? ? ? 語法：主查詢 where 條件 in (列子查詢)
? ? ? ? ? ? ? 查詢所有學(xué)生所在班級的班級名稱
? ? ? ? ? ? ? 找出學(xué)生表中所有的班級 id
? ? ? ? ? ? ? 找出班級表中對應(yīng)的名字
? ? ? ? ? ? ? select * from t_class where c_id in (select c_class_id from t_student);
? 3>行級子查詢
? ? ? 作用：子查詢返回的結(jié)果是一行(一行多列)
? ? ? 語法：主查詢 where (字段1,2,...) = (行子查詢)
? ? ? ? ? ? ? 查找班級年齡最大,所在班號最小的的學(xué)生
? ? ? ? ? ? ? 找出最大年齡和最小班號
? ? ? ? ? ? ? 找出年齡和班號滿足條件的學(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ù)時，只有一張表芦拿，查詢時使用自己連接自己士飒。
? ? ? 語法： select * from 表1 inner join 表2 on 表1.列 運算符 表2.列 where 條件
? 為什么需要自連接
? ? ? 設(shè)計表結(jié)構(gòu)來存儲 全國 所有的省份和 全國所有的市
? ? ? 設(shè)計省信息的表結(jié)構(gòu)provinces
? ? ? id 省的編號
? ? ? ptitle 省名稱
? ? ? 設(shè)計市信息的表結(jié)構(gòu)citys
? ? ? id 市編號
? ? ? ctitle 市名稱
? ? ? proid 市所屬的省的編號
? citys表的proid表示城市所屬的省，對應(yīng)著provinces表的id值
? 將兩個表合為一個
? 定義表areas蔗崎，結(jié)構(gòu)如下
? ? ? ? ? ■ id
? ? ? ? ? ■ atitle
? ? ? ? ? ■ pid
? ? ? 關(guān)于這個表的說明:
? ? ? ? ? ■ 因為省沒有所屬的省份酵幕，所以可以填寫為null
? ? ? ? ? ■ 城市所屬的省份pid，填寫省所對應(yīng)的編號id
? ? ? ? ? ■ 這就是自關(guān)聯(lián)缓苛，表中的某一列未桥，關(guān)聯(lián)了這個表中的另外一列冬耿，但是它們的業(yè)務(wù)邏輯含義是不一樣的亦镶，城市信息的pid引用的是省信息的id
? ? ? ? ? ■ 在這個表中，結(jié)構(gòu)不變懂讯，可以添加區(qū)縣褐望、鄉(xiāng)鎮(zhèn)街道瘫里、村社區(qū)等信息
? areas表和自身進行連接這種形式的連接 就成為自連接谨读。
? 創(chuàng)建areas表的語句如下：
? ? ? 注意劳殖，表所在的數(shù)據(jù)庫字符集必須是utf8的哆姻，如果不是會導(dǎo)入數(shù)據(jù)出錯
? ? ? ? ? ■ create table areas(aid int primary key,atitle varchar(20),pid int);
? 從sql文件中導(dǎo)入數(shù)據(jù)
? ? ? source areas.sql;
? 查詢一共有多少個省
? ? ? 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='廣州市';
