目錄

一负饲、查詢準(zhǔn)備

二堤魁、條件查詢

三、排序

四返十、聚合函數(shù)

五妥泉、分組

六、分頁(yè)

七洞坑、連接查詢

八盲链、自關(guān)聯(lián)

九、子查詢

一迟杂、查詢準(zhǔn)備

1刽沾、創(chuàng)建數(shù)據(jù)庫(kù)、數(shù)據(jù)表

• 創(chuàng)建數(shù)據(jù)庫(kù)
  create database python_niubiqigai charset=utf8;
• 使用數(shù)據(jù)庫(kù)
  use python_niubiqigai
• 創(chuàng)建students表

  create table students(
      id int unsigned primary key auto_increment not null,
      name varchar(20) default '',
      age tinyint unsigned default 0,
      height decimal(5,2),
      gender enum('男','女','中性','保密') default '保密',
      cls_id int unsigned default 0,
      is_delete bit default 0
  );
  

• 創(chuàng)建classes表

  create table classes (
      id int unsigned auto_increment primary key not null,
      name varchar(30) not null
  );
  

2排拷、準(zhǔn)備數(shù)據(jù)

• 向students表中插入數(shù)據(jù)

  insert into students values
  (0,'小張',18,180.00,2,1,0),
  (0,'小王',18,180.00,2,2,1),
  (0,'小白',29,185.00,1,1,0),
  (0,'小黑',59,175.00,1,2,1),
  (0,'小丁',38,160.00,2,1,0),
  (0,'小李',28,150.00,4,2,1),
  (0,'小周',18,172.00,2,1,1),
  (0,'小城',36,NULL,1,1,0),
  (0,'小花',27,181.00,1,2,0),
  (0,'小芳',25,166.00,2,2,0),
  (0,'小薇',33,162.00,3,3,1),
  (0,'小趙',12,180.00,2,4,0),
  (0,'小郭',12,170.00,1,4,0),
  (0,'小謝',34,176.00,2,5,0);
  

• 向classes表中插入數(shù)據(jù)
  insert into classes values (0, "物理1班"), (0, "物理2班");

• 查詢所有字段
  select * from 表名;
  例：
  select * from students;

• 查詢指定字段
  select 列1,列2,... from 表名;
  例:
  select name from students;

• 使用 as 給字段起別名
  select id as 序號(hào), name as 名字, gender as 性別 from students;

• 可以通過(guò) as 給表起別名
  -- 如果是單表查詢 可以省略表明
  select id, name, gender from students;
  -- 表名.字段名
  select students.id,students.name,students.gender from students;
  -- 可以通過(guò) as 給表起別名
  select s.id,s.name,s.gender from students as s;

3侧漓、消除重復(fù)行

• 在select后面列前使用distinct可以消除重復(fù)的行
  select distinct 列1,... from 表名;
  例：
  select distinct gender from students;

二、條件查詢

使用where子句對(duì)表中的數(shù)據(jù)篩選监氢，結(jié)果為true的行會(huì)出現(xiàn)在結(jié)果集中

• 語(yǔ)法如下：
  select * from 表名 where 條件;
  例：
  select * from students where id=1;
• where后面支持多種運(yùn)算符布蔗，進(jìn)行條件的處理
  • 比較運(yùn)算符
  • 邏輯運(yùn)算符
  • 模糊查詢
  • 范圍查詢
  • 空判斷

比較運(yùn)算符

• 等于: =
• 大于: >
• 大于等于: >=
• 小于: <
• 小于等于: <=
• 不等于: != 或 <>

例1：查詢編號(hào)大于3的學(xué)生
select * from students where id > 3;

例2：查詢編號(hào)不大于4的學(xué)生
select * from students where id <= 4;

例3：查詢姓名不是“黃蓉”的學(xué)生
select * from students where name != '黃蓉';

例4：查詢沒(méi)被刪除的學(xué)生
select * from students where is_delete=0;

邏輯運(yùn)算符

• and
• or
• not

例5：查詢編號(hào)大于3的女同學(xué)
select * from students where id > 3 and gender=0;
例6：查詢編號(hào)小于4或沒(méi)被刪除的學(xué)生
select * from students where id < 4 or is_delete=0;
模糊查詢

• like
• %表示任意多個(gè)任意字符
• _表示一個(gè)任意字符

例7：查詢姓黃的學(xué)生
select * from students where name like '黃%';
例8：查詢姓黃并且“名”是一個(gè)字的學(xué)生
select * from students where name like '黃_';
例9：查詢姓黃或叫靖的學(xué)生
select * from students where name like '黃%' or name like '%靖';

范圍查詢

• in表示在一個(gè)非連續(xù)的范圍內(nèi)
  例10：查詢編號(hào)是1或3或8的學(xué)生
  select * from students where id in(1,3,8);
• between ... and ...表示在一個(gè)連續(xù)的范圍內(nèi)
  例11：查詢編號(hào)為3至8的學(xué)生
  select * from students where id between 3 and 8;
  例12：查詢編號(hào)是3至8的男生
  select * from students where (id between 3 and 8) and gender=1;

空判斷

• 注意：null與''是不同的

• 判空is null
  例13：查詢沒(méi)有填寫(xiě)身高的學(xué)生
  select * from students where height is null;

• 判非空is not null
  例14：查詢填寫(xiě)了身高的學(xué)生
  select * from students where height is not null;
  例15：查詢填寫(xiě)了身高的男生
  select * from students where height is not null and gender=1;

優(yōu)先級(jí)

• 優(yōu)先級(jí)由高到低的順序?yàn)椋盒±ㄌ?hào)，not浪腐，比較運(yùn)算符纵揍，邏輯運(yùn)算符
• and比or先運(yùn)算，如果同時(shí)出現(xiàn)并希望先算or议街，需要結(jié)合()使用

三泽谨、排序

為了方便查看數(shù)據(jù)，可以對(duì)數(shù)據(jù)進(jìn)行排序
語(yǔ)法：
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]
說(shuō)明

• 將行數(shù)據(jù)按照列1進(jìn)行排序傍睹，如果某些行列1的值相同時(shí)隔盛，則按照列2排序，以此類(lèi)推
• 默認(rèn)按照列值從小到大排列（asc）
• asc從小到大排列拾稳，即升序
• desc從大到小排序吮炕，即降序
  例1：查詢未刪除男生信息，按學(xué)號(hào)降序
  select * from students where gender=1 and is_delete=0 order by id desc;
  例2：查詢未刪除學(xué)生信息访得，按名稱升序
  select * from students where is_delete=0 order by name;
  例3：顯示所有的學(xué)生信息龙亲，先按照年齡從大-->小排序陕凹，當(dāng)年齡相同時(shí) 按照身高從高-->矮排序
  select * from students order by age desc,height desc;

四、聚合函數(shù)

為了快速得到統(tǒng)計(jì)數(shù)據(jù)鳄炉，經(jīng)常會(huì)用到如下5個(gè)聚合函數(shù)
1. 總數(shù)

• count(*)表示計(jì)算總行數(shù)杜耙，括號(hào)中寫(xiě)星與列名，結(jié)果是相同的
  例1：查詢學(xué)生總數(shù)
  select count(*) from students;

2. 最大值

• max(列)表示求此列的最大值
  例2：查詢女生的編號(hào)最大值
  select max(id) from students where gender=2;

3. 最小值

• min(列)表示求此列的最小值
  例3：查詢未刪除的學(xué)生最小編號(hào)
  select min(id) from students where is_delete=0;

4. 求和

• sum(列)表示求此列的和
  例4：查詢男生的總年齡
  select sum(age) from students where gender=1;
  -- 平均年齡
  select sum(age)/count(*) from students where gender=1;

5. 平均值

• avg(列)表示求此列的平均值
  例5：查詢未刪除女生的編號(hào)平均值
  select avg(id) from students where is_delete=0 and gender=2;

五拂盯、分組

group by

• group by的含義:將查詢結(jié)果按照1個(gè)或多個(gè)字段進(jìn)行分組佑女，字段值相同的為一組
• group by可用于單個(gè)字段分組，也可用于多個(gè)字段分組

select gender from students group by gender;

select gender from students group by gender;

group by + group_concat()

• group_concat(字段名)可以作為一個(gè)輸出字段來(lái)使用嚎花，
  表示分組之后，根據(jù)分組結(jié)果呀洲，使用group_concat()來(lái)放置每一組的某字段的值的集合紊选。
  select gender,group_concat(name) from students group by gender;
  
  select gender,group_concat(name) from students group by gender;
  
  select gender,group_concat(id) from students group by gender;
  
  select gender,group_concat(id) from students group by gender;

group by + 集合函數(shù)

• 通過(guò)group_concat()的啟發(fā)，我們既然可以統(tǒng)計(jì)出每個(gè)分組的某字段的值的集合道逗，那么我們也可以通過(guò)集合函數(shù)來(lái)對(duì)這個(gè)值的集合做一些操作
  select gender,group_concat(age) from students group by gender;
  
  1
  
  分別統(tǒng)計(jì)性別為男/女的人年齡平均值
  select gender,avg(age) from students group by gender;
  
  2
  
  分別統(tǒng)計(jì)性別為男/女的人的個(gè)數(shù)
  select gender,count(*) from students group by gender;
  
  3

group by + having

• having 條件表達(dá)式：用來(lái)分組查詢后指定一些條件來(lái)輸出查詢結(jié)果
• having作用和where一樣兵罢，但having只能用于group by
  select gender,count(*) from students group by gender having count(*)>2;
  
  4

group by + with rollup

• with rollup的作用是：在最后新增一行，來(lái)記錄當(dāng)前列里所有記錄的總和
  select gender,count(*) from students group by gender with rollup;
  
  5

六憔辫、分頁(yè)

1趣些、獲取部分行
當(dāng)數(shù)據(jù)量過(guò)大時(shí)，在一頁(yè)中查看數(shù)據(jù)是一件非常麻煩的事情

• 語(yǔ)法
  select * from 表名 limit start,count
• 說(shuō)明
  從start開(kāi)始贰您，獲取count條數(shù)據(jù)
  例1：查詢前3行男生信息
  select * from students where gender=1 limit 0,3;

2坏平、示例：分頁(yè)

• 已知：每頁(yè)顯示m條數(shù)據(jù)，當(dāng)前顯示第n頁(yè)
  • 求總頁(yè)數(shù)：此段邏輯后面會(huì)在python中實(shí)現(xiàn)
  • 查詢總條數(shù)p1
  • 使用p1除以m得到p2
  • 如果整除則p2為總數(shù)頁(yè)
  • 如果不整除則p2+1為總頁(yè)數(shù)
• 求第n頁(yè)的數(shù)據(jù)
  select * from students where is_delete=0 limit (n-1)*m,nm

七锦亦、連接查詢

當(dāng)查詢結(jié)果的列來(lái)源于多張表時(shí)舶替，需要將多張表連接成一個(gè)大的數(shù)據(jù)集，再選擇合適的列返回
mysql支持三種類(lèi)型的連接查詢杠园，分別為：

• 內(nèi)連接查詢：查詢的結(jié)果為兩個(gè)表匹配到的數(shù)據(jù)
• 右連接查詢：查詢的結(jié)果為兩個(gè)表匹配到的數(shù)據(jù)顾瞪，右表特有的數(shù)據(jù)，對(duì)于左表中不存在的數(shù)據(jù)使用null填充
• 左連接查詢：查詢的結(jié)果為兩個(gè)表匹配到的數(shù)據(jù)抛蚁，左表特有的數(shù)據(jù)陈醒，對(duì)于右表中不存在的數(shù)據(jù)使用null填充

語(yǔ)法
select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列

例1：使用內(nèi)連接查詢班級(jí)表與學(xué)生表
select * from students inner join classes on students.cls_id = classes.id;

例2：使用左連接查詢班級(jí)表與學(xué)生表
此處使用了as為表起別名，目的是編寫(xiě)簡(jiǎn)單
select * from students as s left join classes as c on s.cls_id = c.id;

例3：使用右連接查詢班級(jí)表與學(xué)生表
select * from students as s right join classes as c on s.cls_id = c.id;

例4：查詢學(xué)生姓名及班級(jí)名稱
select s.name,c.name from students as s inner join classes as c on s.cls_id = c.id;

八瞧甩、自關(guān)聯(lián)

• 設(shè)計(jì)省信息的表結(jié)構(gòu)provinces
  id
  ptitle
• 設(shè)計(jì)市信息的表結(jié)構(gòu)citys
  id
  ctitle
  proid
• citys表的proid表示城市所屬的省的id钉跷，對(duì)應(yīng)著provinces表的id值

問(wèn)題：

1. 能不能將兩個(gè)表合成一張表呢？

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

意義：
存儲(chǔ)的都是地區(qū)信息彬坏，而且每種信息的數(shù)據(jù)量有限，沒(méi)必要增加一個(gè)新表膝晾，或者將來(lái)還要存儲(chǔ)區(qū)栓始、鄉(xiāng)鎮(zhèn)信息，都增加新表的開(kāi)銷(xiāo)太大

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

• id
• atitle
• pid

說(shuō)明:

• 因?yàn)槭](méi)有所屬的省份幻赚，所以可以填寫(xiě)為null
• 城市所屬的省份pid，填寫(xiě)省所對(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ū)等信息

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

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='廣州市';

九喊括、子查詢

在一個(gè) select 語(yǔ)句中,嵌入了另外一個(gè) select 語(yǔ)句, 那么被嵌入的 select 語(yǔ)句稱之為子查詢語(yǔ)句
1胧瓜、主查詢
主要查詢的對(duì)象,第一條 select 語(yǔ)句
2、主查詢和子查詢的關(guān)系

• 子查詢是嵌入到主查詢中
• 子查詢是輔助主查詢的,要么充當(dāng)條件,要么充當(dāng)數(shù)據(jù)源
• 子查詢是可以獨(dú)立存在的語(yǔ)句,是一條完整的 select 語(yǔ)句

3郑什、子查詢分類(lèi)

• 標(biāo)量子查詢: 子查詢返回的結(jié)果是一個(gè)數(shù)據(jù)(一行一列)
• 列子查詢: 返回的結(jié)果是一列(一列多行)
• 行子查詢: 返回的結(jié)果是一行(一行多列)

4府喳、標(biāo)量子查詢

• 1）查詢班級(jí)學(xué)生平均年齡
• 2）查詢大于平均年齡的學(xué)生
  select * from students where age > (select avg(age) from students);

5、列級(jí)子查詢

• 查詢還有學(xué)生在班的所有班級(jí)名字
  1）找出學(xué)生表中所有的班級(jí) id
  2）找出班級(jí)表中對(duì)應(yīng)的名字
  select name from classes where id in (select cls_id from students);

6蘑拯、行級(jí)子查詢

• 需求: 查找班級(jí)年齡最大,身高最高的學(xué)生
• 行元素: 將多個(gè)字段合成一個(gè)行元素,在行級(jí)子查詢中會(huì)使用到行元素
  select * from students where (height,age) = (select max(height),max(age) from students);

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

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

☆祝您生活愉快！☆

個(gè)人博客：
https://blog.csdn.net/niubiqigai/article/details/82927773