1、Mysql查詢數(shù)據(jù)單表查詢6使用集合函數(shù)查詢10連接查詢11子查詢12合并查詢結(jié)果13為表和字段取別名14本章所用到的數(shù)據(jù)表/*顧客表*/CREATE TABLE customers( c_id int NOT NULL AUTO_INCREMENT, c_name char(50) NOT NULL, c_address char(50) NULL, c_city char(50) NULL, c_zip char(10) NULL, c_contact char(50) NULL, c_email char(255) NULL, PRIMARY KEY (c_id);/*水果表*/CREA

2、TE TABLE fruits( f_id char(10) NOT NULL, s_id INTNOT NULL, f_name char(255) NOT NULL, f_price decimal(8,2) NOT NULL, PRIMARY KEY(f_id) ;/*供貨方表*/CREATE TABLE suppliers( s_id int NOT NULL AUTO_INCREMENT, s_name char(50) NOT NULL, s_city char(50) NULL, s_zip char(10) NULL, s_call CHAR(50) NOT NULL, PRI

3、MARY KEY (s_id) ;/*訂單明細(xì)表*/CREATE TABLE orderitems( o_num int NOT NULL, o_item int NOT NULL, f_id char(10) NOT NULL, quantity int NOT NULL, item_price decimal(8,2) NOT NULL, PRIMARY KEY (o_num,o_item) ;/*訂單表*/CREATE TABLE orders( o_num int NOT NULL AUTO_INCREMENT, o_date datetime NOT NULL, c_id int N

4、OT NULL, PRIMARY KEY (o_num) ;/*-插入數(shù)據(jù)-*/INSERT INTO customers(c_id, c_name, c_address, c_city, c_zip, c_contact, c_email) VALUES(10001, RedHook, 200 Street , Tianjin, 300000, LiMing, LMing),(10002, Stars, 333 Fromage Lane, Dalian, 116000, Zhangbo,Jerry),(10003, Netbhood, 1 Sunny Place, Qingdao, 2660

5、00, LuoCong, NULL),(10004, JOTO, 829 Riverside Drive, Haikou, 570000, YangShan, sam);INSERT INTO fruits (f_id, s_id, f_name, f_price)VALUES(a1, 101,apple,5.2),(b1,101,blackberry, 10.2),(bs1,102,orange, 11.2),(bs2,105,melon,8.2),(t1,102,banana, 10.3),(t2,102,grape, 5.3), (o2,103,coconut, 9.2),(c0,101

6、,cherry, 3.2),(a2,103, apricot,2.2),(l2,104,lemon, 6.4),(b2,104,berry, 7.6),(m1,106,mango, 15.6),(m2,105,xbabay, 2.6),(t4,107,xbababa, 3.6),(m3,105,xxtt, 11.6),(b5,107,xxxx, 3.6);INSERT INTO suppliers(s_id, s_name,s_city, s_zip, s_call)VALUES(101,FastFruit Inc.,Tianjin,300000,48075),(102,LT Supplies

7、,Chongqing,400000,44333),(103,ACME,Shanghai,200000,90046),(104,FNK Inc.,Zhongshan,528437,11111),(105,Good Set,Taiyuang,030000, 22222),(106,Just Eat Ours,Beijing,010, 45678),(107,DK Inc.,Zhengzhou,450000, 33332);INSERT INTO orderitems(o_num, o_item, f_id, quantity, item_price)VALUES(30001, 1, a1, 10,

8、 5.2),(30001, 2, b2, 3, 7.6),(30001, 3, bs1, 5, 11.2),(30001, 4, bs2, 15, 9.2),(30002, 1, b3, 2, 20.0),(30003, 1, c0, 100, 10),(30004, 1, o2, 50, 2.50),(30005, 1, c0, 5, 10),(30005, 2, b1, 10, 8.99),(30005, 3, a2, 10, 2.2),(30005, 4, m1, 5, 14.99);INSERT INTO orders(o_num, o_date, c_id)VALUES(30001,

9、 2008-09-01, 10001),(30002, 2008-09-12, 10003),(30003, 2008-09-30, 10004),(30004, 2008-10-03, 10005),(30005, 2008-10-08, 10001);單表查詢查詢所有字段 查詢指定字段【例7.1】從fruits表中檢索所有字段的數(shù)據(jù)查詢單個(gè)字段【例7.2】查詢當(dāng)前表中f_name列所有水果名稱，輸入如下語(yǔ)句：查詢多個(gè)字段【例7.3】例如，從fruits表中獲取f_name和f_price兩列，輸入如下語(yǔ)句：查詢指定記錄【例7.4】查詢價(jià)格為10.2元的水果的名稱，輸入如下語(yǔ)句：【例7.5】

10、查找名稱為“apple”的水果的價(jià)格,輸入如下語(yǔ)句：【例7.6】查詢價(jià)格小于10的水果的名稱，輸入如下語(yǔ)句：帶in關(guān)鍵字的查詢【例7.7】s_id為101和102的記錄，輸入如下語(yǔ)句：【例7.8】查詢所有s_id不等于101也不等于102的記錄，輸入如下語(yǔ)句：帶between and 的范圍查詢【例7.9】查詢價(jià)格在2.00元到10.5元之間水果名稱和價(jià)格【例7.10】查詢價(jià)格在2.00元到10.5元之外的水果名稱和價(jià)格帶like的字符匹配查詢，百分號(hào)通配符% ,匹配任意長(zhǎng)度的字符【例7.11】查找所有以b字母開(kāi)頭的水果，輸入如下語(yǔ)句：【例7.12】在fruits表中，查詢f_name中包含字

11、母g的記錄【例7.13】查詢以b開(kāi)頭，并以y結(jié)尾的水果的名稱下劃線通配符_一次只能匹配任意一個(gè)字符【例7.14】在fruits表中，查詢以字母y結(jié)尾，且y前面只有4個(gè)字母的記錄查詢空值在子句中 不能使用= 使用 is null 表示空值【例7.15】查詢customers表中c_email為空的記錄的c_id、c_name和c_email字段值：【例7.16】查詢customers表中c_email不為空的記錄的c_id、c_name和c_email字段值帶and 的多條件查詢【例7.17】在fruits表中查詢s_id = 101，并且f_price大于5價(jià)格和名稱的記錄【例7.18】在fr

12、uits表中查詢s_id = 101或者102，并且f_price大于5，并且f_name=apple的記錄價(jià)格和名稱帶OR的多條件查詢【例7.19】查詢s_id=101或者s_id=102的水果供應(yīng)商的f_price和f_name，SQL語(yǔ)句如下：【例7.20】查詢s_id=101或者s_id=102的水果供應(yīng)商的f_price和f_name查詢結(jié)果不重復(fù) 使用distinct 語(yǔ)法：select distinct 字段名 from 表名【例7.21】查詢fruits表中s_id字段的值，并返回s_id字段值不得重復(fù)對(duì)差詢結(jié)果進(jìn)行排序 order by 默認(rèn)按升序來(lái)排序 即ASC單列排序【例

13、7.22】查詢fruits表的f_name字段值，并對(duì)其進(jìn)行排序多列排序【例7.23】查詢fruits表中的f_name和f_price字段，先按f_name排序，再按f_price排序備注：對(duì)多列排序的時(shí)候，首先排序的第一列必須是相同的值，才會(huì)對(duì)第二列進(jìn)行排序， 如果第一列數(shù)據(jù)中所有的值都是唯一的，將不再對(duì)第二列進(jìn)行排序【例7.24】查詢fruits表中的f_name和f_price字段，對(duì)結(jié)果按f_price降序方式排序指定排序的方向 ASC 升序， DESC 降序【例7.25】查詢fruits表，先按f_price降序排序，再按f_name字段升序排序，SQL語(yǔ)句如下：分組查詢：grou

14、p by關(guān)鍵字通常和集合函數(shù)一起使用例如：max（），min(),count(),sun() ,avg()每一個(gè)水果供應(yīng)商提供多種水果，根據(jù)水果供應(yīng)商分組使用count可以查看水果供應(yīng)商供應(yīng)的水果種類(lèi)數(shù)【例7.26】根據(jù)s_id對(duì)fruits表中的數(shù)據(jù)進(jìn)行分組group_concat()可以將分組的子元素查看出來(lái)【例7.27】根據(jù)s_id對(duì)fruits表中的數(shù)據(jù)進(jìn)行分組，將每個(gè)供應(yīng)商的水果名稱顯示出來(lái)having條件過(guò)濾【例7.28】根據(jù)s_id對(duì)fruits表中的數(shù)據(jù)進(jìn)行分組，并顯示水果種類(lèi)大于1的分組信息使用with rollup:記錄計(jì)算查詢出的所有記錄的總和，即統(tǒng)計(jì)記錄數(shù)量【例7.29

15、】根據(jù)s_id對(duì)fruits表中的數(shù)據(jù)進(jìn)行分組，并顯示記錄數(shù)量多字段分組【例7.30】根據(jù)s_id和f_name字段對(duì)fruits表中的數(shù)據(jù)進(jìn)行分組， SQL語(yǔ)句如下，group by 和order by 一起使用o_num訂單號(hào)，訂單價(jià)格【例7.31】查詢總訂單價(jià)格大于100的訂單號(hào)和總訂單價(jià)格使用limit限制查詢結(jié)果的數(shù)量limit 位置偏移量，行數(shù)【例7.32】顯示fruits表查詢結(jié)果的前4行，輸入如下語(yǔ)句：記錄從0開(kāi)始【例7.33】在fruits 表中，使用LIMIT子句，返回從第5個(gè)記錄開(kāi)始的，行數(shù)長(zhǎng)度為3的記錄使用集合函數(shù)查詢count（）函數(shù) 返回某一列行的總數(shù)【例7.34】

16、查詢customers表中總的行數(shù)【例7.35】查詢customers表中有電子郵箱的顧客的總數(shù)，輸入如下語(yǔ)句：【例7.36】在orderitems表中，使用COUNT()函數(shù)統(tǒng)計(jì)不同訂單號(hào)中訂購(gòu)的水果種類(lèi)sum（）求和函數(shù)【例7.37】在orderitems表中查詢30005號(hào)訂單一共購(gòu)買(mǎi)的水果總量，輸入如下語(yǔ)句：【例7.38】在orderitems表中，使用SUM()函數(shù)統(tǒng)計(jì)不同訂單號(hào)中訂購(gòu)的水果總量avg（）平均值函數(shù)【例7.39】在fruits表中，查詢s_id=103的供應(yīng)商的水果價(jià)格的平均值，SQL語(yǔ)句如下：【例7.40】在fruits表中，查詢每一個(gè)供應(yīng)商的水果價(jià)格的平均值，SQ

17、L語(yǔ)句如下：max()最大值【例7.41】在fruits表中查找市場(chǎng)上價(jià)格最高的水果，SQL語(yǔ)句如下：【例7.42】在fruits表中查找不同供應(yīng)商提供的價(jià)格最高的水果【例7.43】在fruits表中查找f_name的最大值，SQL語(yǔ)句如下min（）最小值【例7.44】在fruits表中查找市場(chǎng)上價(jià)格最低的水果，SQL語(yǔ)句如下：【例7.45】在fruits表中查找不同供應(yīng)商提供的價(jià)格最低的水果連接查詢內(nèi)連接查詢：【例7.46】在fruits表和suppliers表之間使用內(nèi)連接查詢，查詢之前，查看兩個(gè)表的結(jié)構(gòu)，使用Inner join 語(yǔ)法進(jìn)行內(nèi)連接查詢 語(yǔ)法 select 列名 from 表

18、名 inner join 表名 on 條件【例7.47】在fruits表和suppliers表之間使用INNER JOIN語(yǔ)法進(jìn)行內(nèi)連接查詢自連接查詢：在一個(gè)查詢中涉及到的兩張表都是同一張表【例7.48】查詢供應(yīng)f_id=a1的水果供應(yīng)商提供的其他水果種類(lèi)備注：為防止二義性對(duì)表使用別名自連接，可以將一張表看成兩張完全相同的數(shù)據(jù)表，相當(dāng)于數(shù)據(jù)表的復(fù)制表外連接查詢：包括左連接，右連接 返回主表中的所有記錄和附表中連接字段相等的記錄【例7.49】在customers表和orders表中，查詢所有客戶，包括沒(méi)有訂單的客戶，SQL語(yǔ)法如下，【例7.50】在customers表和orders表中，查詢所有

19、訂單，包括沒(méi)有客戶的訂單復(fù)合條件聯(lián)合查詢【例7.51】在customers表和orders表中，使用INNER JOIN語(yǔ)法查詢customers表中ID為10001、的客戶的訂單信息【例7.52】在fruits表和suppliers表之間使用INNER JOIN語(yǔ)法進(jìn)行內(nèi)連接查詢，并對(duì)查詢結(jié)果排序 子查詢定義兩個(gè)表tb11和tb12CREATE table tb11 ( num1 INT NOT NULL);CREATE table tb12 ( num2 INT NOT NULL);向兩個(gè)表中插入數(shù)據(jù)，INSERT INTO tb11 values(1), (5), (13), (27);INSERT INTO tb12 values(6), (14), (11), (20);帶in關(guān)鍵字的子查詢【例7.58】在orderitems表中查詢訂購(gòu)f_id為c0的訂單號(hào)，并根據(jù)訂單號(hào)查詢具有訂單號(hào)的客戶c_id【例7.59】與前一個(gè)例子語(yǔ)句類(lèi)似，但是在SELECT語(yǔ)句中使用NOT IN操作符帶比較運(yùn)算符的子查詢【例7.60】