總體內(nèi)容

一、數(shù)據(jù)準備、基本的查詢

二、條件查詢

三、排序

四、聚合、分組

五、分頁

六、連接查詢(多表的時候有用，單表的時候幾乎沒有用)

七、自關(guān)聯(lián)

八、子查詢

九、總結(jié)

一、數(shù)據(jù)準備、基本的查詢

1.1、創(chuàng)建數(shù)據(jù)庫 (pythonTestDataBase: 數(shù)據(jù)庫名)

create database pythonTestDataBase charset=utf8;

1.2、 使用數(shù)據(jù)庫（pythonTestDataBase）

use pythonTestDataBase;

1.3、創(chuàng)建兩個數(shù)據(jù)表 animalTable 與 personTable

1.4、查看表結(jié)構(gòu)

desc animalTable;

desc personTable;

1.5、插入一些數(shù)據(jù)

insert into animalTable values(0, "梅花鹿", 3,"保密",0),(0, "熊貓", 2, "雌",0),(0, "東北虎", 6,"雄",0);

insert into personTable values(0, "小王","保密","黃",0),(0, "小李","男","黑",0),(0, "小杜","女","白",0);

1.6、基本的查詢

(1)、查詢所有字段

select * from 表名;

(2)、查詢指定字段

select 列1,列2,... from 表名;

(3)、使用 as 給字段起別名

select 字段 as 名字.... from 表名;

(4)、select 表名.字段 .... from 表名;

select表名.name,表名.agefrom表名;

(5)、可以通過 as 給表起別名

select 別名.字段 .... from 表名 as 別名;

(6)、消除重復(fù)行(distinct 字段)

select distinct gender from personTable;

二、條件查詢

2.1、比較運算符（使用 animalTable 表）< > != = >= <=

select * from animalTable where age!=6

2.2、邏輯運算符 and or not?

// 3到12之間的動物信息

select * from animalTable where age>3 and age<12;

2.3、模糊查詢（使用 personTable 表），效率比較低

(1)、like：%?替換1個或者多個、_?替換1個、查詢姓名中 以?"關(guān)鍵字名"?開始的名字

%?替換1個或者多個

_?替換1個

(2)、rlike?正則

2.4、范圍查詢、null(in not in? between and? not between and )

in (1, 3, 8)表示在一個非連續(xù)的范圍內(nèi)

not in?不 非連續(xù) 的范圍之內(nèi)

between ... and ...表示在一個連續(xù)的范圍內(nèi)

not between ... and ...?表示 不在一個連續(xù)的范圍內(nèi)

空(null)判斷: 判空is null

理解一個概念：?name = null?與?name = ""?的區(qū)別,前者是 name沒有指向任何地址，后者是指向一個空的地址

判非空 is not null

三、排序?order by?字段，使用 animalTable 表

3.1、order by` 字段

asc?從?小到大?排列，即?升序

desc?從?大到小?排序，即?降序

.2、默認是 升序(第2句與第3句一個意思)

3.3、order by 多個字段

查詢年齡在3到12歲之間的雄性動物，按照年齡從小到大排序，如果年齡相同的情況下按照 id 從大到小排序( id 默認是從小到大的)

select * from animalTable where (age between 3 and 12) and gender = "雄" order by age asc,id desc;

3.4、不需要 where 約束

按照年齡從小到大、id 從大到小的排序

select*fromanimalTable order by age asc,id desc;

四、聚合函數(shù)、分組

4.1、聚合函數(shù)

(1)、count 計算個數(shù)

查詢雄性有多少人

(2)、最大值?max?與 最小值?min

查詢最大的年齡

(3)、求和 sum

求年齡的和

(4)、平均值?avg

求年齡的平均值

select sum(age)/count(*) from animalTable

(5)、四舍五入?round(值 , 保留小數(shù)的位數(shù))?小數(shù)

計算所有動物的平均年齡，保留2位小數(shù)

4.2、分組group by

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

group by可用于單個字段分組，也可用于多個字段分組

(1)、按照性別分組,查詢所有的性別

查詢所有性別的動物組

(2)、計算每種 性別 中的人數(shù)

3)、計算雄性的數(shù)量(取出分組中 雄 性的組)，根據(jù)條件取出分組中的某一個分組

(4)、group by + group_concat()顯示分組中的 指定字段

顯示分組中的 name

group_concat(字段名)可以作為一個輸出字段來使用，

表示分組之后，根據(jù)分組結(jié)果，使用group_concat()來放置每一組的某字段的值的集合

顯示分組中的 name, age, id

由于上面的 name, age, id 混在了一起，我們需要分開他們，看的更直觀一些

5)、group by + having

having 條件表達式：用來分組查詢后指定一些條件來輸出查詢結(jié)果

having作用和where一樣，但having只能用于group by

查詢平均年齡超過7歲的性別，以及姓名

select gender, group_concat(name),avg(age) from animalTable group by gender having avg(age)>7;

查詢每種性別中的數(shù)量多于2個的信息

select gender, group_concat(name) from animalTable group by gender having count(*)>2;

group by + with rollup

with rollup?的作用是：在最后新增一行，來記錄當前列里所有記錄的總和

五、分頁

5.1、當數(shù)據(jù)量過大時，在一頁中查看數(shù)據(jù)是一件非常麻煩的事情

語法：select * from 表名 limit start,count,解釋：start 是頁碼，count是一頁顯示的數(shù)量

5.2、查詢前2行男生信息

5.3、求第n頁的數(shù)據(jù)，每頁 m 條數(shù)據(jù)（不足m條，有多少顯示多少條）

六、連接查詢(多表的時候有用，單表的時候幾乎沒有用)，當查詢結(jié)果的列來源于多張表時，需要將多張表連接成一個大的數(shù)據(jù)集，再選擇合適的列返回，mysql支持三種類型的連接查詢，分別為：內(nèi)連接查詢、左連接查詢、右連接查詢(有了左連接，一般不使用右連接)

語法：inner join ... on...

6.1、準備

給 personTable 添加 class_id 字段，并賦值，效果如下

alter table personTable add class_id int unsigned;

6.2、內(nèi)連接查詢：inner join ... on?(取交集)：查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)

select ... from 表A inner join 表B；

查詢 有能夠?qū)?yīng)班級的學生以及班級信息

(2)、在上面查詢的基礎(chǔ)上 按照要求顯示姓名、班級:不再使用?*,展示什么:?表名.字段

select personTable.*,classTable.class_name from personTable inner join classTable on personTable.class_id=classTable.id;

// 只顯示兩個組的名字

select personTable.name,classTable.class_name from personTable inner join classTable on personTable.class_id=classTable.id;

(3)、給數(shù)據(jù)表?起別名（取表名的首字母）

selectp.name,c.class_namefrompersonTableasp innerjoinclassTableasc on p.class_id=c.id;

(4)、在以上的查詢中，將班級姓名顯示在第1列（將上面的p.name,c.class_name調(diào)換一下順序）

select c.class_name,p.name from personTable as p inner join classTable as c on p.class_id=c.id;

(5)、查詢 有能夠?qū)?yīng)班級的學生以及班級信息, 按照班級降序進行排序

selectc.class_name,p.namefrompersonTableasp innerjoinclassTableasc on p.class_id=c.id order by c.class_name desc;

6.3、左連接查詢:left join?(取左邊)：查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)，左表特有的數(shù)據(jù)，對于右表中不存在的數(shù)據(jù)使用null填充

(1)、查詢每位學生對應(yīng)的班級信息

(2)、查詢沒有對應(yīng)班級信息的學生(用having就好，對結(jié)果進行處理)

select ... from xxx as s left join xxx as c on..... where .....或者select ... from xxx as s left join xxx as c on..... having .....

select * from personTable left join classTable on personTable.class_id= classTable.id having classTable.class_name is null;

或者 where 來替換 having

select * from personTable left join classTable on personTable.class_id= classTable.id where classTable.class_name is null;

6.4、右連接查詢(取右邊)：查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)，右表特有的數(shù)據(jù)，對于左表中不存在的數(shù)據(jù)使用null填充

提示： 將數(shù)據(jù)表名字互換位置，用left join完成?right join...on...

七、自關(guān)聯(lián)

7.1、自關(guān)聯(lián)的引用

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

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

問題：能不能將兩個表合成一張表呢？

思考：觀察兩張表發(fā)現(xiàn)，citys表比provinces表多一個列proid，其它列的類型都是一樣的

意義：存儲的都是地區(qū)信息，而且每種信息的數(shù)據(jù)量有限，沒必要增加一個新表，或者將來還要存儲區(qū)、鄉(xiāng)鎮(zhèn)信息，都增加新表的開銷太大

答案：定義表areas，結(jié)構(gòu)如下

說明:

因為省沒有所屬的省份，所以可以填寫為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ū)等信息

7.1、準備工作

把本地的 city.sql(地區(qū)文件內(nèi)容) 拷貝到 服務(wù)器root家目錄下，下面代碼：前面的city.sql 是本地的路徑，后面的是服務(wù)器的路徑，我是直接放到了 root的家目下

scp -P 22 city.sql root@47.93.240.8: city.sql

7.2、查詢出山東省有哪些市

select * from city as province inner join city as citys on citys.pid=province.aid having province.atitle="山東省";

select province.atitle, citys.atitle from city as province inner join city as citys on citys.pid=province.aid having province.atitle="山東省";

7.3、 查詢出青島市有哪些縣城

select province.atitle, citys.atitle from city as province inner join city as citys on citys.pid=province.aid having province.atitle="青島市";

select * from city where pid=(select aid from city where atitle="青島市")

八、子查詢

8.1、 子查詢 與 主查詢

子查詢語句：在一個 select 語句中,嵌入了另外一個 select 語句, 那么被嵌入的 select 語句。

主查詢：主要查詢的對象,第一條 select 語句。

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

子查詢是嵌入到主查詢中

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

子查詢是可以獨立存在的語句,是一條完整的 select 語句

8.2、子查詢分類

標量子查詢: 子查詢返回的結(jié)果是一個數(shù)據(jù)(一行一列)

列子查詢: 返回的結(jié)果是一列(一列多行)

行子查詢: 返回的結(jié)果是一行(一行多列)

8.3、標量子查詢

查詢動物的平均年齡

8.4、列級子查詢

查詢還有學生在班的所有班級名字

找出學生表中所有的班級 id

找出班級表中對應(yīng)的名字

select class_name from classTable where id in (select class_id from personTable);

8.5、行級子查詢

需求: 查找班級年齡最大,身高最高的學生

行元素: 將多個字段合成一個行元素,在行級子查詢中會使用到行元素

select * from students where (height,age) = (select max(height),max(age) from students);

8.6、子查詢中特定關(guān)鍵字使用

in 范圍

格式: 主查詢 where 條件 in (列子查詢)

九、總結(jié)

9.1、查詢的完整格式

9.3、執(zhí)行順序為：

from 表名

where ....

group by ...

select distinct *

having ...

order by ...

limit start,count

9.4、實際使用中，只是語句中某些部分的組合，而不是全部

作者：IIronMan

鏈接：http://www.itdecent.cn/p/8caca10f055a

來源：簡書

著作權(quán)歸作者所有。商業(yè)轉(zhuǎn)載請聯(lián)系作者獲得授權(quán)，非商業(yè)轉(zhuǎn)載請注明出處。