10.1 聚合函數(shù)

1. 聚合函數(shù)的介紹

聚合函數(shù)又叫組函數(shù)，通常是對表中的數(shù)據(jù)進行統(tǒng)計和計算，一般結合分組(group by)來使用，用于統(tǒng)計和計算分組數(shù)據(jù)。

注意：聚合函數(shù)不對空值（null）統(tǒng)計

常用的聚合函數(shù):

count(col): 表示求指定列的總行數(shù)

max(col): 表示求指定列的最大值

min(col): 表示求指定列的最小值

sum(col): 表示求指定列的和

avg(col): 表示求指定列的平均值

2. 求總行數(shù)

-- 返回非NULL數(shù)據(jù)的總行數(shù). select count(height) from students; -- 返回總行數(shù)，包含null值記錄; select count(*) from students;

3. 求最大值

-- 查詢女生的編號最大值 select max(id) from students where gender = '女';

4. 求最小值

-- 查詢未刪除的學生最小編號 select min(id) from students where is_delete = 0;

5. 求和

-- 查詢男生的總身高 select sum(height) from students where gender = '男'; -- 平均身高 select sum(height) / count(*) from students where gender = '男';

6. 求平均值

-- 求男生的平均身高, 聚合函數(shù)不統(tǒng)計null值，平均身高有誤 select avg(height) from students where gender = 1; -- 求男生的平均身高, 包含身高是null的 select avg(ifnull(height,0)) from students where gender = 1;

說明

• ifnull函數(shù): 表示判斷指定字段的值是否為null，如果為空使用自己提供的值。

7. 聚合函數(shù)的特點

• 聚合函數(shù)默認忽略字段為null的記錄 要想列值為null的記錄也參與計算，必須使用ifnull函數(shù)對null值做替換。

8. 小結

• count(col): 表示求指定列的總行數(shù)
• max(col): 表示求指定列的最大值
• min(col): 表示求指定列的最小值
• sum(col): 表示求指定列的和
• avg(col): 表示求指定列的平均值

10.2 分組查詢

1. 分組查詢介紹

分組查詢就是將查詢結果按照指定字段進行分組，字段中數(shù)據(jù)相等的分為一組。

分組查詢基本的語法格式如下：

GROUP BY 列名 [HAVING 條件表達式] [WITH ROLLUP]

說明:

• 列名: 是指按照指定字段的值進行分組。
• HAVING 條件表達式: 用來過濾分組后的數(shù)據(jù)。
• WITH ROLLUP：在所有記錄的最后加上一條記錄，顯示select查詢時聚合函數(shù)的統(tǒng)計和計算結果

查詢性別的種類（之前的寫法）：

select distinct gender from students;

2. group by的使用

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

-- 根據(jù)gender字段來分組(group by后面跟什么字段，那select后面就必須跟什么字段) select gender from students group by gender; -- 根據(jù)name和gender字段進行分組 select name, gender from students group by name, gender;

se3. group by + group_concat()的使用

group_concat(字段名): 統(tǒng)計每個分組指定字段的信息集合，每個信息之間使用逗號進行分割

-- 根據(jù)gender字段進行分組， 查詢gender字段和分組的name字段信息 select gender,group_concat(name) from students group by gender;

4. group by + 聚合函數(shù)的使用

-- 統(tǒng)計不同性別的人的平均年齡 select gender,avg(age) from students group by gender; -- 統(tǒng)計不同性別的人的個數(shù) select gender,count(*) from students group by gender;

?

5. group by + having的使用

having作用和where類似都是過濾數(shù)據(jù)的，但having是過濾分組數(shù)據(jù)的，只能用于group by

-- 根據(jù)gender字段進行分組，統(tǒng)計分組條數(shù)大于2的 select gender,count(*) from students group by gender having count(*)>2;

6. group by + with rollup的使用

with rollup的作用是：在最后記錄后面新增一行，顯示select查詢時聚合函數(shù)的統(tǒng)計和計算結果

-- 根據(jù)gender字段進行分組，匯總總人數(shù) select gender,count(*) from students group by gender with rollup; -- 根據(jù)gender字段進行分組，匯總所有人的年齡 select gender,group_concat(age) from students group by gender with rollup;

?

7. 小結

• group by 根據(jù)指定的一個或者多個字段對數(shù)據(jù)進行分組
• group_concat(字段名)函數(shù)是統(tǒng)計每個分組指定字段的信息集合
• 聚合函數(shù)在和 group by 結合使用時, 聚合函數(shù)統(tǒng)計和計算的是每個分組的數(shù)據(jù)
• having 是對分組數(shù)據(jù)進行條件過濾
• with rollup在最后記錄后面新增一行，顯示select查詢時聚合函數(shù)的統(tǒng)計和計算結果

10.3 連接查詢-內連接

1. 連接查詢的介紹

連接查詢可以實現(xiàn)多個表的查詢，當查詢的字段數(shù)據(jù)來自不同的表就可以使用連接查詢來完成。

連接查詢可以分為:

內連接查詢

左連接查詢

右連接查詢

自連接查詢

2. 內連接查詢

查詢兩個表中符合條件的共有記錄

內連接查詢效果圖:

內連接查詢語法格式:

select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2

說明:

• inner join 就是內連接查詢關鍵字
• on 就是連接查詢條件

例1：使用內連接查詢學生表與班級表:

select * from students inner join classes on students.c_id = classes.id;

select s.name,c.name from students s inner join classes c on s.c_id = c.id;

?

3. 小結

• 內連接使用inner join .. on .., on 表示兩個表的連接查詢條件
• 內連接根據(jù)連接查詢條件取出兩個表的 “交集”

10.4 連接查詢-左連接

1. 左連接查詢

以左表為主根據(jù)條件查詢右表數(shù)據(jù)，如果根據(jù)條件查詢右表數(shù)據(jù)不存在使用null值填充

左連接查詢效果圖:

左連接查詢語法格式:

select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2

說明:

• left join 就是左連接查詢關鍵字
• on 就是連接查詢條件
• 表1 是左表
• 表2 是右表

例1：使用左連接查詢學生表與班級表:

select * from students s left join classes c on s.c_id = c.id;

2. 小結

• 左連接使用left join .. on .., on 表示兩個表的連接查詢條件
• 左連接以左表為主根據(jù)條件查詢右表數(shù)據(jù)，右表數(shù)據(jù)不存在使用null值填充。

10.5 連接查詢-右連接

1. 右連接查詢

以右表為主根據(jù)條件查詢左表數(shù)據(jù)，如果根據(jù)條件查詢左表數(shù)據(jù)不存在使用null值填充

右連接查詢效果圖:

右連接查詢語法格式:

select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2

說明:

• right join 就是右連接查詢關鍵字
• on 就是連接查詢條件
• 表1 是左表
• 表2 是右表

例1：使用右連接查詢學生表與班級表:

select * from students s right join classes c on s.s_id = c.id;

?

2. 小結

• 右連接使用right join .. on .., on 表示兩個表的連接查詢條件
• 右連接以右表為主根據(jù)條件查詢左表數(shù)據(jù)，左表數(shù)據(jù)不存在使用null值填充。

10.6 連接查詢-自連接

1. 自連接查詢

左表和右表是同一個表，根據(jù)連接查詢條件查詢兩個表中的數(shù)據(jù)。

創(chuàng)建areas表:

create table areas(id varchar(30) not null primary key, title varchar(30), pid varchar(30) );

執(zhí)行sql文件給areas表導入數(shù)據(jù):

source areas.sql;

說明:

• source 表示執(zhí)行的sql文件

自連接查詢的用法:

-- select * from areas c inner join areas p on c.pid = p.id limit 20; -- select c.id,c.title,c.pid,p.title from areas c inner join areas p on c.pid = p.id limit 20; -- select c.id,c.title,c.pid,p.title from areas c inner join areas p on c.pid = p.id where p.title = '山西省';

說明:

• 自連接查詢必須對表起別名

小結

• 自連接查詢就是把一張表模擬成左右兩張表，然后進行連表查詢。
• 自連接就是一種特殊的連接方式，連接的表還是本身這張表

總結：右連接和自連接用的情況非常少，用的最頻繁的還是內連接和左連接

10.7 子查詢

1. 子查詢的介紹

在一個 select 語句中,嵌入了另外一個 select 語句, 那么被嵌入的 select 語句稱之為子查詢語句，外部那個select語句則稱為主查詢.

主查詢和子查詢的關系:

子查詢是嵌入到主查詢中

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

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

2. 子查詢的使用

例1. 查詢大于平均年齡的學生:

select * from students where age > (select avg(age) from students);

?

例2. 查詢學生在班的所有班級名字:

select * from classes where id in (select c_id from students where c_id is not null);

?

例3. 查找年齡最大,身高最高的學生:

select * from students where age = (select max(age) from students) and height = (select max(height) from students); ---簡寫方式 select * from students where (age,height) = (select max(age),max(height) from students);

3. 小結

• 子查詢是一個完整的SQL語句，子查詢被嵌入到一對小括號里面

MYSQL數(shù)據(jù)庫遠程登陸：

終端執(zhí)行vim /etc/mysql/mysql.conf.d/mysqld.cnf，將?bind-address = 127.0.0.1這一行注釋掉。

然后終端登陸mysql，依次執(zhí)行show databases；use mysql;show tables;select * from user \G;update user set host = '%' where user = 'root';Ctrl+d

重啟服務端 sudo service mysql restart;

10.8 數(shù)據(jù)庫設計之三范式

1. 數(shù)據(jù)庫設計之三范式的介紹

范式: 對設計數(shù)據(jù)庫提出的一些規(guī)范，目前有跡可尋的共有8種范式，一般遵守3范式即可。

• 第一范式（1NF）: 強調的是列的原子性，即列不能夠再分成其他幾列。
• 第二范式（2NF）: 滿足 1NF，另外包含兩部分內容，一是表必須有一個主鍵；二是非主鍵字段 必須完全依賴于主鍵，而不能只依賴于主鍵的一部分。
• 第三范式（3NF）: 滿足 2NF，另外非主鍵列必須直接依賴于主鍵，不能存在傳遞依賴。即不能存在：非主鍵列 A 依賴于非主鍵列 B，非主鍵列 B 依賴于主鍵的情況。

2. 第一范式的介紹

如圖所示的表結構:

說明:

• 這種表結構設計就沒有達到 1NF，要符合 1NF 我們只需把列拆分，即：把 contact 字段拆分成 name 、tel、addr 等字段。

3. 第二范式的介紹

如圖所示的表結構:

說明:

• 這種表結構設計就沒有達到 2NF，因為 Discount（折扣），Quantity（數(shù)量）完全依賴于主鍵（OrderID），而 UnitPrice單價，ProductName產(chǎn)品名稱 只依賴于 ProductID, 所以 OrderDetail 表不符合 2NF。
• 我們可以把【OrderDetail】表拆分為【OrderDetail】（OrderID，ProductID，Discount，Quantity）和【Product】（ProductID，UnitPrice，ProductName）這樣就符合第二范式了。

4. 第三范式的介紹

如圖所示的表結構:

說明:

• 這種表結構設計就沒有達到 3NF，因為 OrderDate，CustomerID，CustomerName，CustomerAddr，CustomerCity 等非主鍵列都完全依賴于主鍵（OrderID），所以符合 2NF。不過問題是 CustomerName，CustomerAddr，CustomerCity 直接依賴的是 CustomerID（非主鍵列），而不是直接依賴于主鍵，它是通過傳遞才依賴于主鍵，所以不符合 3NF。
• 我們可以把【Order】表拆分為【Order】（OrderID，OrderDate，CustomerID）和【Customer】（CustomerID，CustomerName，CustomerAddr，CustomerCity）從而達到 3NF。

5. E-R模型的介紹

E-R模型即實體-關系模型，E-R模型就是描述數(shù)據(jù)庫存儲數(shù)據(jù)的結構模型。

E-R模型的使用場景:

對于大型公司開發(fā)項目，我們需要根據(jù)產(chǎn)品經(jīng)理的設計，我們先使用建模工具, 如:power designer，db desinger等這些軟件來畫出實體-關系模型(E-R模型)

然后根據(jù)三范式設計數(shù)據(jù)庫表結構

E-R效果圖：

說明:

• 實體: 用矩形表示，并標注實體名稱
• 屬性: 用橢圓表示，并標注屬性名稱，
• 關系: 用菱形表示，并標注關系名稱
  • 一對一
  • 一對多
  • 多對多

一對一的關系:

說明:

• 關系也是一種數(shù)據(jù)，需要通過一個字段存儲在表中
• 1對1關系，在表A或表B中創(chuàng)建一個字段，存儲另一個表的主鍵值

一對多的關系:

說明:

• 1對多關系，在多的一方表(學生表)中創(chuàng)建一個字段，存儲班級表的主鍵值

多對多的關系:

說明:

• 多對多關系，新建一張表C，這個表只有兩個字段，一個用于存儲A的主鍵值，一個用于存儲B的主鍵值

5. 小結

• 范式就是設計數(shù)據(jù)庫的一些通用規(guī)范。
  • 1NF強調字段是最小單元，不可再分
  • 2NF強調在1NF基礎上必須要有主鍵和非主鍵字段必須完全依賴于主鍵，也就是說 不能部分依賴
  • 3MF強調在2NF基礎上 非主鍵字段必須直接依賴于主鍵，也就是說不能傳遞依賴(間接依賴)。
• E-R模型由 實體、屬性、實體之間的關系構成，主要用來描述數(shù)據(jù)庫中表結構。
• 開發(fā)流程是先畫出E-R模型，然后根據(jù)三范式設計數(shù)據(jù)庫中的表結構

10.9 外鍵SQL語句的編寫

1. 外鍵約束作用

外鍵約束:對外鍵字段的值進行更新和插入時會和引用表中字段的數(shù)據(jù)進行驗證，數(shù)據(jù)如果不合法則更新和插入會失敗，保證數(shù)據(jù)的有效性

2. 對于已經(jīng)存在的字段添加外鍵約束

-- 為cls_id字段添加外鍵約束 alter table students add foreign key(c_id) references classes(id);

3. 在創(chuàng)建數(shù)據(jù)表時設置外鍵約束

-- 創(chuàng)建學校表 create table school(id int unsigned not null primary key auto_increment, name varchar(30) );-- 創(chuàng)建老師表 create table teacher(id int unsigned not null primary key auto_increment, name varchar(30) not null, s_id int unsigned, foreign key(s_id) references school(id) );

4. 刪除外鍵約束

-- 需要先獲取外鍵約束名稱,該名稱系統(tǒng)會自動生成,可以通過查看表創(chuàng)建語句來獲取名稱 show create table teacher;-- 獲取名稱之后就可以根據(jù)名稱來刪除外鍵約束 alter table teacher drop foreign key 外鍵名;

5. 小結

• 添加外鍵約束: alter table 從表 add foreign key(外鍵字段) references 主表(主鍵字段);
• 刪除外鍵約束: alter table 表名 drop foreign key 外鍵名;

10.10 演練-分組和聚合函數(shù)的組合使用

1. 數(shù)據(jù)準備

-- 創(chuàng)建 "京東" 數(shù)據(jù)庫 create database jing_dong charset=utf8;-- 使用 "京東" 數(shù)據(jù)庫 use jing_dong;-- 創(chuàng)建一個商品goods數(shù)據(jù)表 create table goods(id int unsigned primary key auto_increment not null,name varchar(150) not null,cate_name varchar(40) not null,brand_name varchar(40) not null,price decimal(10,3) not null default 0,is_show bit not null default 1,is_saleoff bit not null default 0 );-- 向goods表中插入數(shù)據(jù)insert into goods values(0,'r510vc 15.6英寸筆記本','筆記本','華碩','3399',default,default); insert into goods values(0,'y400n 14.0英寸筆記本電腦','筆記本','聯(lián)想','4999',default,default); insert into goods values(0,'g150th 15.6英寸游戲本','游戲本','雷神','8499',default,default); insert into goods values(0,'x550cc 15.6英寸筆記本','筆記本','華碩','2799',default,default); insert into goods values(0,'x240 超極本','超級本','聯(lián)想','4880',default,default); insert into goods values(0,'u330p 13.3英寸超極本','超級本','聯(lián)想','4299',default,default); insert into goods values(0,'svp13226scb 觸控超極本','超級本','索尼','7999',default,default); insert into goods values(0,'ipad mini 7.9英寸平板電腦','平板電腦','蘋果','1998',default,default); insert into goods values(0,'ipad air 9.7英寸平板電腦','平板電腦','蘋果','3388',default,default); insert into goods values(0,'ipad mini 配備 retina 顯示屏','平板電腦','蘋果','2788',default,default); insert into goods values(0,'ideacentre c340 20英寸一體電腦 ','臺式機','聯(lián)想','3499',default,default); insert into goods values(0,'vostro 3800-r1206 臺式電腦','臺式機','戴爾','2899',default,default); insert into goods values(0,'imac me086ch/a 21.5英寸一體電腦','臺式機','蘋果','9188',default,default); insert into goods values(0,'at7-7414lp 臺式電腦 linux ）','臺式機','宏碁','3699',default,default); insert into goods values(0,'z220sff f4f06pa工作站','服務器/工作站','惠普','4288',default,default); insert into goods values(0,'poweredge ii服務器','服務器/工作站','戴爾','5388',default,default); insert into goods values(0,'mac pro專業(yè)級臺式電腦','服務器/工作站','蘋果','28888',default,default); insert into goods values(0,'hmz-t3w 頭戴顯示設備','筆記本配件','索尼','6999',default,default); insert into goods values(0,'商務雙肩背包','筆記本配件','索尼','99',default,default); insert into goods values(0,'x3250 m4機架式服務器','服務器/工作站','ibm','6888',default,default); insert into goods values(0,'商務雙肩背包','筆記本配件','索尼','99',default,default);

表結構說明:

• id 表示主鍵 自增
• name 表示商品名稱
• cate_name 表示分類名稱
• brand_name 表示品牌名稱
• price 表示價格
• is_show 表示是否顯示
• is_saleoff 表示是否售完

2. SQL語句演練

查詢類型cate_name為 '超極本' 的商品名稱、價格

select name,price from goods where cate_name = '超級本';

顯示商品的分類

--方法一 select distinct cate_name from goods; --方法二 select cate_name from goods group by cate_name;

求所有電腦產(chǎn)品的平均價格,并且保留兩位小數(shù)

select round(avg(price),2) as avg_price from goods;

顯示每種商品的平均價格

select cate_name,avg(price) from goods group by cate_name;

查詢每種類型的商品中 最貴、最便宜、平均價、數(shù)量

select cate_name,max(price),min(price),avg(price),count(*) from goods group by cate_name;

查詢所有價格大于平均價格的商品，并且按價格降序排序

--細致的寫法select id,name,price from goods where price > (select round(avg(price),2) as avg_price from goods) order by price desc; --不那么細致的寫法 select * from goods where price > (select avg(price) from goods) order by price desc;

總結

以上是生活随笔為你收集整理的十、MYSQL数据库的条件查询的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內容還不錯，歡迎將生活随笔推薦給好友。