Python MySQL數(shù)據(jù)庫2：數(shù)據(jù)庫查詢

總體內(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;
```

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

1.2畸裳、使用數(shù)據(jù)庫（pythonTestDataBase）
```
use pythonTestDataBase;
```

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

create table animalTable(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    age tinyint unsigned default 0,
    gender enum('雄','雌','保密') default "保密",
    is_delete bit default 0
);

create table personTable(
    id int unsigned primary key auto_increment not null,
    name varchar(20) default '',
    gender enum('男','女','保密') default "保密",
    skinColor varchar(20) default '',
    is_delete bit default 0
);

創(chuàng)建兩個表

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);

創(chuàng)建好的數(shù)據(jù)

1.6帅容、基本的查詢

(1)、查詢所有字段

select * from 表名;

例如：

select * from animalTable;
select * from personTable;
select id, name from animalTable;

(2)伍伤、查詢指定字段

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

例如：

select name,gender from personTable;

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

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

例如：

select name as 姓名, gender as 性別 from personTable;

使用 as 給字段起別名

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

select 表名.name, 表名.age from 表名;

例如：

select personTable.name, personTable.gender from personTable;

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

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

例如

select s.name, s.gender from personTable as s;

(6)麦乞、消除重復行(distinct 字段)
```
select distinct gender from personTable;
```

二、條件查詢

personTable表

animalTable 表

2.1劝评、比較運算符（使用 animalTable 表）

(1)姐直、>

// 查看大于 6 歲的動物
select * from animalTable where age>6;
select id,name,gender from animalTable where age>6;

(2)、<

// 查看小于 6 歲的動物
select * from animalTable where age<6;

(3)蒋畜、=声畏、>=、<=

 // 查看 等于姻成、等于等于插龄、小于等于 6 歲的動物
select * from animalTable where age=6;
select * from animalTable where age>=6;
select * from animalTable where age<=6;

(4)愿棋、!=

// 查看不等于 6 歲的動物
select * from animalTable where age!=6;

2.2、邏輯運算符（使用 animalTable 表）

(1)辫狼、and

// 3到12之間的動物信息
select * from animalTable where age>3 and age<12;

`and`

(2)初斑、or

// 6以上或者性別是保密的
select * from animalTable where age>6 or gender="保密";

(3)辛润、not

// 不在 7歲以上的雄性 這個范圍內(nèi)的信息 
select * from animalTable where not (age>7 and gender = "雄");

不在 7歲以上的雄性這個范圍內(nèi)的信息

// 年齡不是小于或者等于7 并且是雄性
select * from animalTable where  (not age <=7) and gender = "雄"膨处；

年齡不是小于或者等于7 并且是雄性

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

personTable表

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

% 替換1個或者多個

// 查詢姓名中 有 "杰" 所有的名字
select * from personTable where name like "%杰%";

查詢姓名中有 "杰" 所有的名字

_ 替換1個

// 查詢姓名中 有姓 "周" 所有2個字的名字
select * from personTable where name like "周_";
// 查詢姓名中 有姓 "周" 所有3個字的名字
select * from personTable where name like "周__";
// 查詢至少有2個字的名字
select name from personTable where name like "__%";

查詢姓名中以 "關(guān)鍵字名" 開始的名字

// 查詢姓名中 以 "小" 開始的名字
select name from personTable where name like "小%";

(2)突硝、rlike 正則

// 查詢以 周開始的姓名
select * from personTable where name rlike "^周.*";
// 查詢以 周開始、倫結(jié)尾的姓名
select name from personTable where name rlike "^周.*倫$";

查詢以周開始置济、倫結(jié)尾的姓名

2.4解恰、范圍查詢、null（使用 personTable 表）

personTable內(nèi)容更新

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

// 查詢 身高為為172浙于、178 的姓名
select * from personTable where height in (172, 178);

查詢身高為為172护盈、178 的姓名

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

// 查詢 不是 身高為為172、178 的姓名
select * from personTable where height not in (172, 178);

between ... and ...表示在一個連續(xù)的范圍內(nèi)
```
查詢 身高在 在172到180之間的的信息
select * from personTable where height betwen 172 in 180;
```
查詢身高在在172到180之間的的信息

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

查詢 身高在 不 在172到180之間的的信息
select * from personTable where height not betwen 172 in 180;
或者 （使用上面的即可）
select * from personTable where not height betwen 172 in 180;

空(null)判斷: 判空is null
理解一個概念： name = null 與 name = "" 的區(qū)別,前者是 name沒有指向任何地址羞酗，后者是指向一個空的地址
```
// 查詢身高為 空(null) 的信息
select * from personTable where height is null;
```
查詢身高為空(null) 的信息

判非空 is not null

 // 查詢身高 不為 空(null) 的信息
 select * from personTable where height is not null;

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

animalTable表

3.1檀轨、order by` 字段
- asc 從 小到大 排列胸竞，即升序
- desc 從 大到小 排序，即降序

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

// 查詢年齡在3到12歲之間的雄性動物(默認按照 id 排序 )
select * from animalTable where (age between 3 and 12) and gender = "雄";
查詢年齡在3到12歲之間的雄性動物卫枝，按照年齡從小到大排序
select * from animalTable where (age between 3 and 12) and gender = "雄" order by age;
select * from animalTable where (age between 3 and 12) and gender = "雄" order by age asc;

查詢年齡在3到12歲之間的雄性動物，按照年齡從小到到排序

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到12歲之間的雄性動物淤袜，按照年齡從小到大排序痒谴，如果年齡相同的情況下按照 id 從大到小排序( id 默認是從小到大的)

3.4、不需要 where 約束

按照年齡從小到大铡羡、id 從大到小的排序
```
select * from animalTable order by age asc,id desc;
```
不需要 where 約束,按照年齡從小到大积蔚、id 從大到小的排序

四、聚合烦周、分組

animalTable表

4.1尽爆、聚合
- (1)怎顾、count 計算個數(shù)
  
  查詢雄性有多少人
```
select count(*) as 雄性數(shù)量 from animalTable where gender = "雄";
```
  查詢雄性有多少人
  
  查詢雌性有多少
```
select count(*) as 女性人數(shù) from personTable where gender = "女";
```
- (2)、最大值 max 與最小值 min
  查詢最大的年齡
```
select max(age) from animalTable;
```
  查詢最大的年齡
- (3)漱贱、求和 sum
  求年齡的和
```
select sum(age) from animalTable;
```
- (4)槐雾、平均值 avg
  求年齡的平均值
```
select avg(age) from animalTable;
```
  還可以用 sum(age)/count(*) 來計算平均年齡
```
select sum(age)/count(*) from animalTable;
```
- (5)、四舍五入 round(值 , 保留小數(shù)的位數(shù)) 小數(shù)
  計算所有動物的平均年齡幅狮，保留2位小數(shù)
```
select round(avg(age),2) from animalTable;
```
  四舍五入 `round(值 , 保留小數(shù)的位數(shù))` 小數(shù)
  
  計算雄性的平均年齡保留2位小數(shù)
```
select round(avg(age),2) from animalTable where gender = "雄";
```
4.2募强、分組 group by
- group by的含義:將查詢結(jié)果按照1個或多個字段進行分組，字段值相同的為一組
- group by可用于單個字段分組崇摄，也可用于多個字段分組
- (1)擎值、按照性別分組,查詢所有的性別
  查詢所有性別的動物組
```
select gender from animalTable group by gender;
```
- (2)、計算每種性別中的人數(shù)
```
select gender,count(*) from animalTable group by gender;
```
  計算每種性別中的人數(shù)
- (3)逐抑、計算雄性的數(shù)量(取出分組中雄性的組)鸠儿，根據(jù)條件取出分組中的某一個分組
```
select gender,count(*) from animalTable where gender = "雄" group by gender;
```
  計算雄性的數(shù)量
- (4)、group by + group_concat() 顯示分組中的指定字段
  顯示分組中的 name
  - group_concat(字段名)可以作為一個輸出字段來使用厕氨，
  - 表示分組之后进每，根據(jù)分組結(jié)果，使用group_concat()來放置每一組的某字段的值的集合
```
select gender,group_concat(name) from animalTable where gender="雄" group by gender;
```
  group_concat(...) 顯示分組中的指定字段
  
  顯示分組中的 name, age, id
```
select gender,group_concat(name, age, id) from animalTable where gender=1 group by gender;
```
  顯示分組中的 name, age, id
  
  由于上面的 name, age, id 混在了一起命斧，我們需要分開他們田晚，看的更直觀一些
```
select gender,group_concat(name,"_",age, "_",id) from animalTable where gender=1 group by gender;
```
  由于上面的 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;
```
  查詢平均年齡超過30歲的性別泞莉，以及姓名
  
  查詢每種性別中的數(shù)量多于2個的信息
```
select gender, group_concat(name) from animalTable group by gender having count(*)>2;
```
- (6)、group by + with rollup
  with rollup 的作用是：在最后新增一行船殉，來記錄當前列里所有記錄的總和
```
select gender,count(*) from animalTable group by gender with rollup;
```
  **with rollup** 的作用是：在最后新增一行鲫趁，來記錄當前列里所有記錄的總和

五、分頁

personTable表

5.1利虫、當數(shù)據(jù)量過大時熬词，在一頁中查看數(shù)據(jù)是一件非常麻煩的事情
語法：select * from 表名 limit start,count,解釋：start 是頁碼嘶伟，count是一頁顯示的數(shù)量

5.2、查詢前2行男生信息

select * from personTable where gender = "男" limit 0,2;

查詢前2行男生信息

5.3、求第n頁的數(shù)據(jù)田篇，每頁 m 條數(shù)據(jù)（不足m條洲赵，有多少顯示多少條）
```
select * from personTable limit (n-1)*m,m;
```
舉例：如果展示：第三頁的數(shù)據(jù),每頁2個數(shù)據(jù)：n=3,m=2
```
select * from personTable limit 4,2;
```
錯誤寫法：
```
select * from personTable limit (3-2)*2,2;
```
提示：limit 要放在其他約束之后捉偏，最后面

六捷兰、連接查詢 (多表的時候有用，單表的時候幾乎沒有用)，當查詢結(jié)果的列來源于多張表時壤躲，需要將多張表連接成一個大的數(shù)據(jù)集城菊，再選擇合適的列返回，mysql支持三種類型的連接查詢碉克，分別為：內(nèi)連接查詢凌唬、左連接查詢、右連接查詢(有了左連接漏麦，一般不使用右連接)
語法： inner join ... on...

select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

6.1客税、準備
- 給 personTable 添加 class_id 字段，并賦值唁奢，效果如下
  
  alter table personTable add class_id int unsigned;
  
  給 personTable 添加 class_id 字段霎挟，并賦值窝剖，效果如下
- 創(chuàng)建一個班級的數(shù)據(jù)表麻掸，并賦值
```
mysql> create table classTable(
    ->  id int unsigned primary key auto_increment not null,
    -> class_name varchar(20) default ''
);

mysql> insert into classTable values(0,"一班"),(0,"二班"),(0,"三班")
```
  classTable表
6.2、內(nèi)連接查詢：inner join ... on (取交集)：查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)

內(nèi)連接查詢：查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)
- select ... from 表A inner join 表B;
```
select * from personTable inner join classTable;
```
- (1)赐纱、查詢有能夠?qū)嗉壍膶W生以及班級信息
```
select * from personTable inner join classTable on personTable.class_id=classTable.id;
```
  查詢有能夠?qū)嗉壍膶W生以及班級信息
- (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ù)表 起別名（取表名的首字母）
```
select p.name,c.class_name from personTable as p inner join classTable as c on p.class_id=c.id;
```
  5161545485353_.pic_hd.jpg
  - (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ū)嗉壍膶W生以及班級信息, 按照班級降序進行排序
```
select c.class_name,p.name from personTable as p inner join classTable as c on p.class_id=c.id order by c.class_name desc;
```
  - (6)久又、當時同一個班級的時候，按照學生的id進行從大到小排序
```
select p.name,p.id,c.class_name from personTable as p inner join classTable as c on p.class_id=c.id order by p.id desc;
```
    當時同一個班級的時候效五，按照學生的id進行從大到小排序
6.3地消、左連接查詢:left join (取左邊)：查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)，左表特有的數(shù)據(jù)畏妖，對于右表中不存在的數(shù)據(jù)使用null填充

image.png
- (1)脉执、查詢每位學生對應的班級信息
```
select * from personTable left join classTable on personTable.class_id= classTable.id;
```
  查詢每位學生對應的班級信息
- (2)、查詢沒有對應班級信息的學生(用 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;
```
  查詢沒有對應班級信息的學生(用 `having` 就好半夷，對結(jié)果進行處理)
6.4、右連接查詢(取右邊)：查詢的結(jié)果為兩個表匹配到的數(shù)據(jù)迅细，右表特有的數(shù)據(jù)巫橄，對于左表中不存在的數(shù)據(jù)使用null填充

image.png

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

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

7.1湘换、自關(guān)聯(lián)的引用
- 設(shè)計省信息的表結(jié)構(gòu)provinces
```
id
ptitle
```
- 設(shè)計市信息的表結(jié)構(gòu)citys
```
id
ctitle
proid
```
- citys表的proid表示城市所屬的省，對應著provinces表的id值
- 問題：能不能將兩個表合成一張表呢？
- 思考：觀察兩張表發(fā)現(xiàn)枚尼，citys表比provinces表多一個列proid贴浙，其它列的類型都是一樣的
- 意義：存儲的都是地區(qū)信息，而且每種信息的數(shù)據(jù)量有限署恍，沒必要增加一個新表崎溃，或者將來還要存儲區(qū)、鄉(xiāng)鎮(zhèn)信息盯质，都增加新表的開銷太大
- 答案：定義表areas袁串，結(jié)構(gòu)如下
```
id
atitle
pid
```
- 說明:
  - 因為省沒有所屬的省份，所以可以填寫為null
  - 城市所屬的省份pid呼巷，填寫省所對應的編號id
  - 這就是自關(guān)聯(lián)囱修，表中的某一列，關(guān)聯(lián)了這個表中的另外一列王悍，但是它們的業(yè)務邏輯含義是不一樣的破镰，城市信息的pid引用的是省信息的id
  - 在這個表中，結(jié)構(gòu)不變压储，可以添加區(qū)縣鲜漩、鄉(xiāng)鎮(zhèn)街道、村社區(qū)等信息
7.1集惋、準備工作
把本地的 city.sql(地區(qū)文件內(nèi)容) 拷貝到服務器root家目錄下孕似，下面代碼：前面的city.sql 是本地的路徑，后面的是服務器的路徑刮刑，我是直接放到了 root的家目下
```
scp -P 22 city.sql root@47.93.240.8: city.sql 
```
在 pythonTestDataBase 庫下建立表 city
創(chuàng)建city表的語句如下：
```
create table city(
      id int unsigned primary key auto_increment not null,
      pid int unsigned default 0,
      cityname varchar(20) default ' ',
      type int unsigned default null
);
```
導入數(shù)據(jù)
```
source  city;
```

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="青島市")

八雷绢、子查詢

animalTable表

8.1泛烙、子查詢與主查詢
- 子查詢語句：在一個 select 語句中,嵌入了另外一個 select 語句, 那么被嵌入的 select 語句。
- 主查詢：主要查詢的對象,第一條 select 語句习寸。
- 主查詢和子查詢的關(guān)系
  - 子查詢是嵌入到主查詢中
  - 子查詢是輔助主查詢的,要么充當條件,要么充當數(shù)據(jù)源
  - 子查詢是可以獨立存在的語句,是一條完整的 select 語句
8.2胶惰、子查詢分類
- 標量子查詢: 子查詢返回的結(jié)果是一個數(shù)據(jù)(一行一列)
- 列子查詢: 返回的結(jié)果是一列(一列多行)
- 行子查詢: 返回的結(jié)果是一行(一行多列)
8.3、標量子查詢
查詢動物的平均年齡
```
select avg(age) from animalTable;
```
查詢大于平均年齡的動物
```
select * from animalTable where age > (select avg(age) from animalTable);
```
查詢動物的平均年齡與查詢大于平均年齡的動物
8.4霞溪、列級子查詢

列級子查詢
- 查詢還有學生在班的所有班級名字
  - 找出學生表中所有的班級 id
  - 找出班級表中對應的名字
    
    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坊饶、查詢的完整格式

SELECT select_expr [,select_expr,...] [      
   FROM tb_name
   [WHERE 條件判斷]
   [GROUP BY {col_name | postion} [ASC | DESC], ...] 
   [HAVING WHERE 條件判斷]
   [ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
   [ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]

9.2、完整的select語句

select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count

9.3殴蓬、執(zhí)行順序為：
- from 表名
- where ....
- group by ...
- select distinct *
- having ...
- order by ...
- limit start,count
9.4匿级、實際使用中蟋滴，只是語句中某些部分的組合，而不是全部