mysql基礎 - xingli

MySQL 基礎#

今日目標：

完成 MySQL 的安裝及登錄基本操作

能通過 SQL 對數據庫進行 CRUD

能通過 SQL 對表進行 CRUD

能通過 SQL 對數據進行 CRUD

登錄 mysql#

mysql -uroot -p 123456

1，數據庫相關概念#

以前我們做系統，數據持久化的存儲採用的是文件存儲。存儲到文件中可以達到系統關閉數據不會丟失的效果，當然文件存儲也有它的弊端。

假設在文件中存儲以下的數據：

姓名 年齡 性別 住址
張三 23 男 北京西三旗
李四 24 女 北京西二旗
王五 25 男 西安軟件新城

現要修改李四這條數據的性別數據改為男，我們現學習的 IO 技術可以通過將所有的數據讀取到內存中，然後進行修改再存到該文件中。通過這種方式操作存在很大問題，現在只有三條數據，如果文件中存儲 1T 的數據，那麼就會發現內存根本就存儲不了。

現需要既能持久化存儲數據，也要能避免上述問題的技術使用在我們的系統中。數據庫就是這樣的一門技術。

1.1 數據庫#

== 存儲和管理數據的倉庫，數據是有組織的進行存儲。==
數據庫英文名是 DataBase，簡稱 DB。

數據庫就是將數據存儲在硬盤上，可以達到持久化存儲的效果。那又是如何解決上述問題的？使用數據庫管理系統。

1.2 數據庫管理系統#

== 管理數據庫的大型軟件 ==
英文：DataBase Management System，簡稱 DBMS

在電腦上安裝了數據庫管理系統後，就可以通過數據庫管理系統創建數據庫來存儲數據，也可以通過該系統對數據庫中的數據進行數據的增刪改查相關的操作。我們平時說的 MySQL 數據庫其實是 MySQL 數據庫管理系統。

通過上面的描述，大家應該已經知道了 數據庫管理系統 和 數據庫 的關係。那么有有哪些常見的數據庫管理系統呢？

1.3 常見的數據庫管理系統#

接下來對上面列舉的數據庫管理系統進行簡單的介紹：

Oracle：收費的大型數據庫，Oracle 公司的產品
==MySQL==：開源免費的中小型數據庫。後來 Sun 公司收購了 MySQL，而 Sun 公司又被 Oracle 收購
SQL Server：MicroSoft 公司收費的中型的數據庫。C#、.net 等語言常使用
PostgreSQL：開源免費中小型的數據庫
DB2：IBM 公司的大型收費數據庫產品
SQLite：嵌入式的微型數據庫。如：作為 Android 內置數據庫
MariaDB：開源免費中小型的數據庫

我們課程上學習的是 MySQL 數據庫管理系統，PostgreSQL 在一些公司也有使用，此時大家肯定會想以後在公司中如果使用我們沒有學習過的 PostgreSQL 數據庫管理系統怎麼辦？這點大家大可不必擔心，如下圖所示：

我們可以通過數據庫管理系統操作數據庫，對數據庫中的數據進行增刪改查操作，而怎麼樣讓用戶跟數據庫管理系統打交道呢？就可以通過一門編程語言（SQL）來實現。

1.4 SQL#

英文：Structured Query Language，簡稱 SQL，結構化查詢語言
操作關係型數據庫的編程語言
定義操作所有關係型數據庫的統一標準，可以使用 SQL 操作所有的關係型數據庫管理系統，以後工作中如果使用到了其他的數據庫管理系統，也同樣的使用 SQL 來操作。

2，MySQL

2.1-2.4 mysql 安裝#

MySQL 安裝文檔

2.5 MySQL 數據模型#

關係型數據庫：

關係型數據庫是建立在關係模型基礎上的數據庫，簡單說，關係型數據庫是由多張能互相連接的二維表組成的數據庫

如下圖，訂單信息表 和 客戶信息表 都是有行有列的二維表我們將這樣的稱為關係型數據庫。

接下來看關係型數據庫的優點：

都是使用表結構，格式一致，易於維護。
使用通用的 SQL 語言操作，使用方便，可用於複雜查詢。
關係型數據庫都可以通過 SQL 進行操作，所以使用方便。
複雜查詢。現在需要查詢 001 號訂單數據，我們可以看到該訂單是 1 號客戶的訂單，而 1 號訂單是李聰這個客戶。以後也可以在一張表中進行統計分析等操作。
數據存儲在磁碟中，安全。

數據模型：

如上圖，我們通過客戶端可以通過數據庫管理系統創建數據庫，在數據庫中創建表，在表中添加數據。創建的每一個數據庫對應到磁碟上都是一個文件夾。比如可以通過 SQL 語句創建一個數據庫（數據庫名稱為 db1），語句如下。該語句咱們後面會學習。

我們可以在數據庫安裝目錄下的 data 目錄下看到多了一個 db1 的文件夾。所以，在 MySQL 中一個數據庫對應到磁碟上的一個文件夾。

而一個數據庫下可以創建多張表，我們到 MySQL 中自帶的 mysql 數據庫的文件夾目錄下：

而上圖中右邊的 db.frm 是表文件，db.MYD 是數據文件，通過這兩個文件就可以查詢到數據展示成二維表的效果。

小結：

MySQL 中可以創建多個數據庫，每個數據庫對應到磁碟上的一個文件夾
在每個數據庫中可以創建多個表，每張都對應到磁碟上一個 frm 文件
每張表可以存儲多條數據，數據會被存儲到磁碟中 MYD 文件中

3，SQL 概述#

了解了數據模型後，接下來我們就學習 SQL 語句，通過 SQL 語句對數據庫、表、數據進行增刪改查操作。

3.1 SQL 簡介#

英文：Structured Query Language，簡稱 SQL
結構化查詢語言，一門操作關係型數據庫的編程語言
定義操作所有關係型數據庫的統一標準
對於同一個需求，每一種數據庫操作的方式可能會存在一些不一樣的地方，我們稱為 “方言”

3.2 通用語法#

SQL 語句可以單行或多行書寫，以分號結尾。

show databases;

如上，以分號結尾才是一個完整的 sql 語句。

MySQL 數據庫的 SQL 語句不區分大小寫，關鍵字建議使用大寫。

同樣的一條 sql 語句寫成下圖的樣子，一樣可以運行出結果。

Show DataBases;

注釋
單行注釋: -- 注釋內容或 #注釋內容 (MySQL 特有)

注意：使用 -- 添加單行注釋時，-- 後面一定要加空格，而 #沒有要求。

多行注釋: /* 注釋 */

-- 注釋內容
#注釋內容(MySQL 特有)
/* 注釋 */

3.3 SQL 分類#

·DDL(Data Definition Language) 數據定義語言，用來定義數據庫對象：數據庫，表，列等

·DML(Data Manipulation Language) 數據操作語言，用來對數據庫中表的數據進行增刪改

·DQL(Data Query Language) 數據查詢語言，用來查詢數據庫中表的記錄（數據）

·DCL(Data Control Language) 數據控制語言，用來定義數據庫的訪問權限和安全級別，及創建用戶

DDL (Data Definition Language) ：數據定義語言，用來定義數據庫對象：數據庫，表，列等

DDL 簡單理解就是用來操作數據庫，表等

DML (Data Manipulation Language) 數據操作語言，用來對數據庫中表的數據進行增刪改

DML 簡單理解就是對表中數據進行增刪改

DQL (Data Query Language) 數據查詢語言，用來查詢數據庫中表的記錄 (數據)

DQL 簡單理解就是對數據進行查詢操作。從數據庫表中查詢到我們想要的數據。

DCL (Data Control Language) 數據控制語言，用來定義數據庫的訪問權限和安全級別，及創建用戶

DML 簡單理解就是對數據庫進行權限控制。比如我讓某一個數據庫表只能讓某一個用戶進行操作等。

注意：以後我們最常操作的是 DML 和 DQL ，因為我們開發中最常操作的就是數據。

4，DDL: 操作數據庫#

我們先來學習 DDL 來操作數據庫。而操作數據庫主要就是對數據庫的增刪查操作。

4.1 查詢#

查詢所有的數據庫

SHOW DATABASES;

運行上面語句效果如下：

上述查詢到的是的這些數據庫是 mysql 安裝好自帶的數據庫，我們以後不要操作這些數據庫。

4.2 創建數據庫#

創建數據庫：

CREATE  DATABASE  數據庫名稱;

運行語句效果如下：

而在創建數據庫的時候，我並不知道 db1 數據庫有沒有創建，直接再次創建名為 db1 的數據庫就會出現錯誤。

為了避免上面的錯誤，在創建數據庫的時候先做判斷，如果不存在再創建。

創建數據庫 (判斷，如果不存在則創建)

CREATE  DATABASE  IF  NOT  EXISTS 數據庫名稱;

運行語句效果如下：

從上面的效果可以看到雖然 db1 數據庫已經存在，再創建 db1 也沒有報錯，而創建 db2 數據庫則創建成功。

4.3 刪除數據庫#

刪除數據庫

DROP  DATABASE 數據庫名稱;

刪除數據庫 (判斷，如果存在則刪除)

DROP  DATABASE  IF  EXISTS 數據庫名稱;

運行語句效果如下：

4.4 使用數據庫#

數據庫創建好了，要在數據庫中創建表，得先明確在哪兒個數據庫中操作，此時就需要使用數據庫。

使用數據庫

USE 數據庫名稱;

查看當前使用的數據庫

SELECT  DATABASE();

運行語句效果如下：

5，DDL: 操作表#

操作表也就是對表進行增（Create）刪（Retrieve）改（Update）查（Delete）。

5.1 查詢表#

查詢當前數據庫下所有表名稱

SHOW TABLES;

我們創建的數據庫中沒有任何表，因此我們進入 mysql 自帶的 mysql 數據庫，執行上述語句查看

查詢表結構

DESC 表名稱;

查看 mysql 數據庫中 func 表的結構，運行語句如下：

5.2 創建表#

創建表

CREATE  TABLE  表名 (
字段名1 數據類型1,
字段名2 數據類型2,
…
字段名n 數據類型n
);

注意：最後一行末尾，不能加逗號

知道了創建表的語句，那麼我們創建如下結構的表

create  table  tb_user (
id int,
# varchar代表字符串類型 括號內是限制長度
username varchar(20),
password  varchar(32)
);

運行語句如下：

5.3 數據類型#

MySQL 支持多種類型，可以分為三類：

數值

tinyint : 小整數型，占一個字節
int ： 大整數類型，占四個字節
eg ： age int
double ： 浮點類型
使用格式： 字段名 double(總長度,小數點後保留的位數)
eg ： score double(5,2)

日期

date ： 日期值。只包含年月日
eg ：birthday date ：
datetime ： 混合日期和時間值。包含年月日時分秒

字符串

char ： 定長字符串。
優點：存儲性能高
缺點：浪費空間
eg ： name  char(10) 如果存儲的數據字符個數不足10個，也會占10個的空間
varchar ： 變長字符串。
優點：節約空間
缺點：存儲性能底
eg ： name  varchar(10) 如果存儲的數據字符個數不足10個，那就數據字符個數是幾就占幾個的空間

注意：其他類型參考資料中的《MySQL 數據類型].xlsx》

案例：

需求：設計一張學生表，請注重數據類型、長度的合理性
1. 編號
2. 姓名，姓名最長不超過10個漢字
3. 性別，因為取值只有兩種可能，因此最多一個漢字
4. 生日，取值為年月日
5. 入學成績，小數點後保留兩位
6. 郵件地址，最大長度不超過 64
7. 家庭聯繫電話，不一定是手機號碼，可能會出現 - 等字符
8. 學生狀態（用數字表示，正常、休學、畢業...）

語句設計如下：

create  table  student (
id int,
name  varchar(10),
gender char(1),
birthday date,
score double(5,2),
email varchar(15),
tel varchar(15),
status  tinyint
);

5.4 刪除表#

刪除表

DROP  TABLE 表名;

清空表中的數據：truncate

格式：truncate table 表名

SELECT * FROM ADDRESS;

清空地址表: truncate table ADDRESS;

查詢地址表中的所有記錄：SELECT * FROM ADDRESS;

刪除表時判斷表是否存在

DROP  TABLE  IF  EXISTS 表名;

運行語句效果如下：

5.5 修改表#

修改表名

ALTER  TABLE 表名 RENAME TO 新的表名;

-- 將表名student修改為stu
alter  table student rename to stu;

添加一列

ALTER  TABLE 表名 ADD 列名 數據類型;

-- 給stu表添加一列address，該字段類型是varchar(50)
alter  table stu add  address  varchar(50);

修改數據類型

ALTER  TABLE 表名 MODIFY 列名 新數據類型;

-- 將stu表中的address字段的類型改為 char(50)
alter  table stu modify  address  char(50);

修改列名和數據類型

ALTER  TABLE 表名 CHANGE 列名 新列名 新數據類型;

-- 將stu表中的address字段名改為 addr，類型改為varchar(50)
alter  table stu change address addr varchar(50);

刪除列

ALTER  TABLE 表名 DROP 列名;

-- 將stu表中的addr字段 刪除
alter  table stu drop addr;

6，navicat 使用#

通過上面的學習，我們發現在命令行中寫 sql 語句特別不方便，尤其是編寫創建表的語句，我們只能在記事本上寫好後直接複製到命令行進行執行。那么有沒有剛好的工具提供給我們進行使用呢？有。

6.1 navicat 概述#

Navicat for MySQL 是管理和開發 MySQL 或 MariaDB 的理想解決方案。
這套全面的前端工具為數據庫管理、開發和維護提供了一款直觀而強大的圖形界面。
官網： http://www.navicat.com.cn

6.2 navicat 安裝#

參考：资料 \navicat 安裝包 \navicat_mysql_x86\navicat 安裝步驟.md

6.3 navicat 使用#

6.3.1 建立和 mysql 服務的連接#

第一步：點擊連接，選擇 MySQL

第二步：填寫連接數據庫必要的信息

以上操作沒有問題就會出現如下圖所示界面：

6.3.2 操作#

連接成功後就能看到如下圖界面：

修改表結構

通過下圖操作修改表結構：

點擊了設計表後即出現如下圖所示界面，在圖中紅框中直接修改字段名，類型等信息：

編寫 SQL 語句並執行

按照如下圖所示進行操作即可書寫 SQL 語句並執行 sql 語句。

7，DML#

DML 主要是對數據進行增（insert）刪（delete）改（update）操作。

7.1 添加數據#

給指定列添加數據

INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);

給全部列添加數據

INSERT INTO 表名 VALUES(值1,值2,…);

批量添加數據

INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;

練習

為了演示以下的增刪改操作是否操作成功，故先將查詢所有數據的語句介紹給大家：

select * from stu;

-- 給指定列添加數據
INSERT INTO stu (id, NAME) VALUES (1, '張三');
-- 給所有列添加數據，列名的列表可以省略的
INSERT INTO stu (id,NAME,sex,birthday,score,email,tel,STATUS) VALUES (2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1);

INSERT INTO stu VALUES (2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1);

-- 批量添加數據
INSERT INTO stu VALUES
(2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1),
(2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1),
(2,'李四','男','1999-11-11',88.88,'[email protected]','13888888888',1);

7.2 修改數據#

修改表數據

UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 條件] ;

注意：

修改語句中如果不加條件，則將所有數據都修改！

像上面的語句中的中括號，表示在寫 sql 語句中可以省略這部分

練習
將張三的性別改為女

update stu set sex =  '女'  where  name  =  '張三';

將張三的生日改為 1999-12-12 分數改為 99.99

update stu set birthday =  '1999-12-12', score =  99.99  where  name  =  '張三';

注意：如果 update 語句沒有加 where 條件，則會將表中所有數據全部修改！

update stu set sex =  '女';

上面語句的執行完後查詢到的結果是：

7.3 刪除數據#

刪除數據

DELETE  FROM 表名 [WHERE 條件] ;

練習

-- 刪除張三記錄
delete  from stu where  name  =  '張三';

-- 刪除stu表中所有的數據
delete  from stu;

8，DQL#

下面是黑馬程序員展示試題庫數據的頁面

頁面上展示的數據肯定是在數據庫中的試題庫表中進行存儲，而我們需要將數據庫中的數據查詢出來並展示在頁面給用戶看。上圖中的是最基本的查詢效果，那麼數據庫其實是很多的，不可能在將所有的數據在一頁進行全部展示，而頁面上會有分頁展示的效果，如下：

當然上圖中的難度字段當我們點擊也可以實現排序查詢操作。從這個例子我們就可以看出，對於數據庫的查詢是靈活多變的，需要根據具體的需求來實現，而數據庫查詢操作也是最重要的操作，所以此部分需要大家重點掌握。

接下來我們先介紹查詢的完整語法：

SELECT
字段列表
FROM
表名列表
WHERE
條件列表
GROUP BY
分組字段
HAVING
分組後條件
ORDER BY
排序字段
LIMIT
分頁限定

為了給大家演示查詢的語句，我們需要先準備表及一些數據：

-- 刪除stu表
drop  table  if  exists stu;

-- 創建stu表
CREATE  TABLE  stu (
id int, -- 編號
name  varchar(20), -- 姓名
age int, -- 年齡
sex varchar(5), -- 性別
address  varchar(100), -- 地址
math double(5,2), -- 數學成績
english double(5,2), -- 英語成績
hire_date date  -- 入學時間
);

-- 添加數據
INSERT INTO stu(id,NAME,age,sex,address,math,english,hire_date)
VALUES
(1,'馬運',55,'男','杭州',66,78,'1995-09-01'),
(2,'馬花疼',45,'女','深圳',98,87,'1998-09-01'),
(3,'馬斯克',55,'男','香港',56,77,'1999-09-02'),
(4,'柳白',20,'女','湖南',76,65,'1997-09-05'),
(5,'柳青',20,'男','湖南',86,NULL,'1998-09-01'),
(6,'劉德花',57,'男','香港',99,99,'1998-09-01'),
(7,'張學右',22,'女','香港',99,99,'1998-09-01'),
(8,'德瑪西亞',18,'男','南京',56,65,'1994-09-02');

接下來咱們從最基本的查詢語句開始學起。

8.1 基礎查詢#

8.1.1 語法#

查詢多個字段

SELECT 字段列表 FROM 表名;
SELECT * FROM 表名; -- 查詢所有數據

去除重複記錄

SELECT DISTINCT 字段列表 FROM 表名;

起別名

AS: AS 也可以省略

8.1.2 練習#

查詢 name、age 兩列

select  name,age from stu;

查詢所有列的數據，列名的列表可以使用 * 替代

select * from stu;

上面語句中的 * 不建議大家使用，因為在這寫 * 不方便我們閱讀 sql 語句。我們寫字段列表的話，可以添加註釋對每一個字段進行說明

而在上課期間為了簡約課程的時間，老師很多地方都會寫 *。

查詢地址信息

select  address  from stu;

執行上面語句結果如下：

從上面的結果我們可以看到有重複的數據，我們也可以使用 distinct 關鍵字去重重複數據。

去除重複記錄

select distinct  address  from stu; -- 會把重複的湖南香港刪除

查詢姓名、數學成績、英語成績。並通過 as 給 math 和 english 起別名（as 關鍵字可以省略）

select  name,math as 數學成績,english as 英文成績 from stu;

-- 可省略as 但是原始名和別名至少留一個空格
select  name,math 數學成績,english 英文成績 from stu;

8.2 條件查詢#

8.2.1 語法#

SELECT 字段列表 FROM 表名 WHERE 條件列表; -- where類似於if
where  >=xx && <bb; -- 並且
where  >=xx and  <bb; -- 並且
where  between xx and bb; -- 在xx和bb之間 相當於>=xx <=bb
# 在mysql中 日期data 可直接用以上方法篩選範圍
# mysql中 判斷等於 不能用 == 只能使用=
<>  !=; # 不等於
|| or; # 或者
-- 或者例子
select * from stu where age =  18  or age =  20  or age =  22; -- 繁瑣
select * from stu where age in (18,20 ,22); -- 簡寫
#查詢null數據
-- 查詢null 不能用 = 和!= 只能用 is is not
# 例子
select * from stu where english is  null; # 查詢英語空值的數據
select * from stu where english is not null; # 查詢英語不為空的所有數據
-- like 模糊查詢 _單個字符 %多個任意字符
# 查詢姓'馬'的學員信息
select * from stu where  name  like  '馬%'; # %代表任意字符 不限數量
# 查詢第二個字是'花'的學員信息
select * from stu where  name  like  '_花%'; # _任意單個字符
# 查詢名字中包含 '德' 的學員信息
select * from stu where  name  like  '%德%'; # 前方任意後方任意 包含德

條件

條件列表可以使用以下運算符

8.2.2 條件查詢練習#

查詢年齡大於 20 歲的學員信息

select * from stu where age >  20;

查詢年齡大於等於 20 歲的學員信息

select * from stu where age >=  20;

查詢年齡大於等於 20 歲並且年齡小於等於 30 歲的學員信息

select * from stu where age >=  20 && age <=  30;
select * from stu where age >=  20  and age <=  30;

上面語句中 && 和 and 都表示並且的意思。建議使用 and 。

也可以使用 between ... and 來實現上面需求

select * from stu where age BETWEEN  20  and  30;

查詢入學日期在 '1998-09-01' 到 '1999-09-01' 之間的學員信息

select * from stu where hire_date BETWEEN  '1998-09-01'  and  '1999-09-01';

查詢年齡等於 18 歲的學員信息

select * from stu where age =  18;

查詢年齡不等於 18 歲的學員信息

select * from stu where age !=  18;
select * from stu where age <>  18;

查詢年齡等於 18 歲或者年齡等於 20 歲或者年齡等於 22 歲的學員信息

select * from stu where age =  18  or age =  20  or age =  22;
select * from stu where age in (18,20 ,22);

查詢英語成績為 null 的學員信息

null 值的比較不能使用 = 或者！= 。需要使用 is 或者 is not

select * from stu where english =  null; -- 這個語句是不行的
select * from stu where english is  null;
select * from stu where english is not null;

8.2.3 模糊查詢練習#

模糊查詢使用 like 關鍵字，可以使用通配符進行占位:

（1）_ : 代表單個任意字符

（2）% : 代表任意個數字符

查詢姓 ' 馬' 的學員信息

select * from stu where  name  like  '馬%';

查詢第二個字是 ' 花' 的學員信息

select * from stu where  name  like  '_花%';

查詢名字中包含 ' 德 ' 的學員信息

select * from stu where  name  like  '%德%';

8.3 排序查詢#

8.3.1 語法#

SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;

上述語句中的排序方式有兩種，分別是：

ASC ：升序排列 （默認值）
DESC ：降序排列

注意：如果有多個排序條件，當前邊的條件值一樣時，才會根據第二條件進行排序

8.3.2 練習#

查詢學生信息，按照年齡升序排列

select * from stu order by age ;

查詢學生信息，按照數學成績降序排列

select * from stu order by math desc ;

查詢學生信息，按照數學成績降序排列，如果數學成績一樣，再按照英語成績升序排列

select * from stu order by math desc , english asc ;

8.4 聚合函數#

8.4.1 概念#

== 將一列數據作為一個整體，進行縱向計算。==

如何理解呢？假設有如下表

現有一需求讓我們求表中所有數據的數學成績的總和。這就是對 math 字段進行縱向求和。

8.4.2 聚合函數分類#

函數名	功能
count (列名)	統計數量（一般選用不為 null 的列）
max (列名)	最大值
min (列名)	最小值
sum (列名)	求和
avg (列名)	平均值

8.4.3 聚合函數語法#

SELECT 聚合函數名(列名) FROM 表;

注意：null 值不參與所有聚合函數運算

8.4.4 練習#

統計班級一共有多少個學生

select  count(id) from stu;
select  count(english) from stu;

上面語句根據某個字段進行統計，如果該字段某一行的值為 null 的話，將不會被統計。所以可以在 count () 來實現。* 表示所有字段數據，一行中也不可能所有的數據都為 null，所以建議使用 count ()

select  count(*) from stu; # 統計所有數據

查詢數學成績的最高分

select  max(math) from stu; # 只會查到最高分 一個數據

查詢數學成績的最低分

select  min(math) from stu; # 只會查到最低分 一個數據

查詢數學成績的總分

select  sum(math) from stu; # 查到總分 一個數據

查詢數學成績的平均分

select  avg(math) from stu; # 查到平均分 一個數據

查詢英語成績的最低分

select  min(english) from stu; # 無法查到空值null

8.5 分組查詢#

8.5.1 語法#

SELECT 字段列表 FROM 表名 [WHERE 分組前條件限定]  GROUP BY 分組字段名 [HAVING 分組後條件過濾];

注意：分組之後，查詢的字段為聚合函數和分組字段，查詢其他字段無任何意義

8.5.2 練習#

查詢男同學和女同學各自的數學平均分

select sex, avg(math) from stu group by sex; # 通過性別分組
# 性別縱列顯示 字段數據橫排顯示

注意：分組之後，查詢的字段為聚合函數和分組字段，查詢其他字段無任何意義

select  name, sex, avg(math) from stu group by sex; -- 這裡查詢name字段就沒有任何意義

name	sex	avg(math)
馬化騰	女	91
馬雲	男	72.6

查詢男同學和女同學各自的數學平均分，以及各自人數

select sex, avg(math),count(*) from stu group by sex;

sex	avg(math)	count(*)
女	91	3
男	72.6	5

查詢男同學和女同學各自的數學平均分，以及各自人數，要求：分數低於 70 分的不參與分組

select sex, avg(math),count(*) from stu where math >  70  group by sex;

查詢男同學和女同學各自的數學平均分，以及各自人數，要求：分數低於 70 分的不參與分組，分組之後人數大於 2 個的

select sex, avg(math),count(*) from stu where math >  70  group by sex having  count(*) >  2; # having 和 where一樣 用在分組查詢裡

where 和 having 區別：

執行時機不一樣：where 是分組之前進行限定，不滿足 where 條件，則不參與分組，而having 是分組之後對結果進行過濾。
可判斷的條件不一樣：where 不能對聚合函數進行判斷，having 可以。
執行順序： where > 聚合函數 > having

8.6 分頁查詢#

如下圖所示，大家在很多網站都見過類似的效果，如京東、百度、淘寶等。分頁查詢是將數據一頁一頁的展示給用戶看，用戶也可以通過點擊查看下一頁的數據。

接下來我們先說分頁查詢的語法。

8.6.1 語法#

SELECT 字段列表 FROM 表名 LIMIT 起始索引 , 查詢條目數;

注意：上述語句中的起始索引是從 0 開始

計算公式：起始索引 =(當前頁碼 - 1) * 每頁顯示的條數

tips：

分頁查詢 limit 是 MySQL 數據庫的方言
Oracle 分頁查詢使用 rownumber
SQL Server 分頁查詢使用 top

8.6.2 練習#

從 0 開始查詢，查詢 3 條數據

select * from stu limit  0 , 3; # 索引計數規則和數組相似

每頁顯示 3 條數據，查詢第 1 頁數據

select * from stu limit  0 , 3; -- 0 1 2

每頁顯示 3 條數據，查詢第 2 頁數據

select * from stu limit  3 , 3; -- 3 4 5

每頁顯示 3 條數據，查詢第 3 頁數據

select * from stu limit  6 , 3; -- 6 7 8

從上面的練習推導出起始索引計算公式：

起始索引 = (當前頁碼 - 1) * 每頁顯示的條數