Python操作三大數(shù)據(jù)庫(kù) - MySQL數(shù)據(jù)庫(kù)(Part1)

1. MySQL的介紹

1.1 介紹

  1. 學(xué)習(xí)目標(biāo)
    了解關(guān)系型數(shù)據(jù)庫(kù)的重要性
    掌握MySql的安裝和配置
    實(shí)踐用戶創(chuàng)建、分配權(quán)限和密碼修改
  2. 內(nèi)容
    了解關(guān)系型數(shù)據(jù)庫(kù)的誕生原因和獨(dú)特優(yōu)勢(shì)
    安裝并初始化MySQL數(shù)據(jù)庫(kù)
    學(xué)習(xí)管理MySQL服務(wù)
    創(chuàng)建新用戶，并分配權(quán)限
    了解MySQL常用配置參數(shù)

1.2 MySQL的下載與安裝

  1. 數(shù)據(jù)庫(kù)簡(jiǎn)介
    數(shù)據(jù)庫(kù)系統(tǒng)(DBMS)是指一個(gè)能為用戶提供信息服務(wù)的系統(tǒng)。它實(shí)現(xiàn)了有組織地、動(dòng)態(tài)地儲(chǔ)存大量相關(guān)數(shù)據(jù)的功能，提供了數(shù)據(jù)處理和信息資源共享的便利手段。
    注意：數(shù)據(jù)的價(jià)值要遠(yuǎn)遠(yuǎn)大于程序的價(jià)值。
  2. 操作系統(tǒng)中數(shù)據(jù)存放的載體
    windows、linux和macOS都是基于文件的操作系統(tǒng)。因此，數(shù)據(jù)庫(kù)中的數(shù)據(jù)也是基于文件保存的。
  3. 數(shù)據(jù)庫(kù)相對(duì)于一般文件的優(yōu)勢(shì)
    支持條件語句快速查詢
    支持不同表的關(guān)聯(lián)查詢
  4. 關(guān)系型數(shù)據(jù)庫(kù)
    關(guān)系型數(shù)據(jù)庫(kù)系統(tǒng)(RDBMS)是指使用了關(guān)系模型的數(shù)據(jù)庫(kù)系統(tǒng)。
    關(guān)系模型中，數(shù)據(jù)是分類存放的，數(shù)據(jù)之前可以有聯(lián)系。
  5. 第一個(gè)關(guān)系型數(shù)據(jù)庫(kù)
    1976年IBM的System R團(tuán)隊(duì)在出版的論文中闡述了關(guān)系模型。1979年Oracle公司推出了首個(gè)關(guān)系型數(shù)據(jù)庫(kù)成品。
  6. 主流關(guān)系型數(shù)據(jù)庫(kù)
    DB2：隨著硬件服務(wù)器贈(zèng)送的，但硬件非常貴。
    Oracle：未開源，無法定制。
    MySQL：性能不如DB2和Oracle，但免費(fèi)開源。
    SQL Server：免費(fèi)，但不支持Linux系統(tǒng)。?
  7. 非關(guān)系型數(shù)據(jù)庫(kù)(NoSQL)
    非關(guān)系型數(shù)據(jù)庫(kù)指的是數(shù)據(jù)分類存放，但是數(shù)據(jù)之間沒有關(guān)聯(lián)關(guān)系的數(shù)據(jù)庫(kù)系統(tǒng)。
  8. 主流非關(guān)系型數(shù)據(jù)庫(kù)
    Redis：用內(nèi)存保存數(shù)據(jù)
    MemCache：用內(nèi)存保存數(shù)據(jù)
    MongoDB：用硬盤保存數(shù)據(jù)
    Neo4J：用硬盤保存數(shù)據(jù)
  9. MySQL數(shù)據(jù)庫(kù)
    MySQL是應(yīng)用最廣泛、普及度最高的開源關(guān)系型數(shù)據(jù)庫(kù)。
    MySQL由瑞典MySQL AB公司開發(fā)，目前屬于Oracle旗下產(chǎn)品。
  10. MySQL大事記
    1996年 - MySQL 1.0版本誕生
    1999年 - MySQL AB公司成立
    2000年 - MySQL 4.0發(fā)布
    2003年 - MySQL 5.0發(fā)布(支持事務(wù)機(jī)制)
  11. MySQL版權(quán)歸屬
    2008年，MySQL AB被SUN公司收購(gòu)。
    2009年，SUN被Oracle公司收購(gòu)。
  12. MySQL衍生版
    MySQL、PERCONA、MariaDB
  13. MySQL下載安裝
    MySQL下載安裝
    MySQL安裝目錄：/usr/local/mysql
    MySQL數(shù)據(jù)庫(kù)數(shù)據(jù)文件路徑：/usr/local/mysql/data
    MySQL密碼重置
    MySQL密碼：299***
    注意：在密碼重置命令SET PASSWORD FOR 'root'@'localhost' = PASSWORD('XXXXXX');中，localhost表示該用戶只允許本地登錄。
  14. MySQL啟動(dòng)
    (1) 圖形界面
    image.png

    (2) 命令行
    啟動(dòng)
    sudo /usr/local/mysql/support-files/mysql.server start
    停止
    sudo /usr/local/mysql/support-files/mysql.server stop
    重啟
    sudo /usr/local/mysql/support-files/mysql.server restart
  15. MySQL簡(jiǎn)單操作
    登錄：mysql -u root -p
    查看數(shù)據(jù)庫(kù)：mysql> show databases;
    退出數(shù)據(jù)庫(kù)：mysql> quit;
    注釋：root為管理員用戶。
  16. 使用Navicat管理MySQL用戶
    (1) 下載安裝Navicat
    (2) 創(chuàng)建MySQL數(shù)據(jù)連接
    image.png

    注釋：MySQL默認(rèn)端口號(hào)為3306。
    (3) 新建用戶
    image.png

    (4) 設(shè)置用戶常規(guī)信息
    image.png

    注釋：主機(jī)%表示可以在任何主機(jī)登錄，localhost表示該用戶只允許本地登錄。密碼為123456。
    (5) 設(shè)置用戶權(quán)限
    image.png

    注釋：服務(wù)器權(quán)限為對(duì)所有數(shù)據(jù)庫(kù)的權(quán)限；對(duì)象權(quán)限是對(duì)指定數(shù)據(jù)庫(kù)的權(quán)限。
    (6) 測(cè)試自定義用戶連接
    image.png
  17. MySQL配置文件
    在my.cnf文件中，我們可以設(shè)置各種MySQL配置。例如：字符集、端口號(hào)、目錄地址等等。

(1) 查看默認(rèn)指定的my.cnf路徑
mysql --help|grep 'my.cnf'

nimengwei@nimengweideMacBook-Pro ~ % mysql --help|grep 'my.cnf'
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 
nimengwei@nimengweideMacBook-Pro ~ %

這是四個(gè)默認(rèn)的指定路徑，MySQL從第一個(gè)路徑依次去找my.cnf配置文件。
(2) 創(chuàng)建my.cnf文件

nimengwei@nimengweideMacBook-Pro /etc % sudo vim my.cnf

(3)寫入配置內(nèi)容
默認(rèn)配置文件地址
(4) 修改文件權(quán)限
sudo chmod 664 /etc/my.cnf
(5) 重啟MySQL
注意：macOS下為my.cnf，windows下為my.ini。
參考：Mac 安裝MySQL my.cnf配置文件

1.3 總結(jié)

  1. 技能清單
    掌握MySQL數(shù)據(jù)庫(kù)的安裝和管理。明白邏輯庫(kù)、數(shù)據(jù)表和數(shù)據(jù)目錄的對(duì)應(yīng)關(guān)系。
    懂得MySQL數(shù)據(jù)庫(kù)的常用參數(shù)設(shè)置：端口號(hào)、字符集、IP綁定、連接數(shù)等。
    掌握MySQL數(shù)據(jù)庫(kù)的用戶管理，能創(chuàng)建用戶并分配權(quán)限，設(shè)置遠(yuǎn)程登錄。
    對(duì)于MySQL數(shù)據(jù)庫(kù)上忘記密碼的賬戶，能重置該賬戶的密碼信息。

2. 數(shù)據(jù)庫(kù)表的相關(guān)操作

2.1 介紹

  1. 管理邏輯庫(kù)和數(shù)據(jù)表
    創(chuàng)建、刪除、修改邏輯庫(kù)和數(shù)據(jù)表
  2. 了解常用的數(shù)據(jù)類型和約束
    字符串、整數(shù)、浮點(diǎn)數(shù)、精確數(shù)字、日期、枚舉。
    主鍵約束、非空約束、唯一約束、外鍵約束。
  3. 掌握索引運(yùn)行機(jī)制和使用原則
    排序?yàn)槭裁纯梢蕴岣邤?shù)據(jù)檢索速度？
    怎么創(chuàng)建和刪除索引？
    什么條件下使用索引？

2.2 數(shù)據(jù)庫(kù)表的創(chuàng)建

  1. SQL語言
    SQL是用于訪問和處理數(shù)據(jù)的標(biāo)準(zhǔn)的計(jì)算機(jī)語言。
  2. SQL語言分類
    數(shù)據(jù)操作語言(DML) - 添加、修改、刪除、查詢
    數(shù)據(jù)控制語言(DCL) - 用戶、權(quán)限、事務(wù)
    數(shù)據(jù)定義語言(DDL) - 邏輯庫(kù)、數(shù)據(jù)表、視圖、索引
  3. SQL語句注意事項(xiàng)
    SELECT "HelloWorld";
    SQL語句不區(qū)分大小寫，但是字符串區(qū)分大小寫。
    SQL語句必須以分號(hào)結(jié)尾。
    SQL語句中的空白和換行沒有限制，但是不能破壞語法。
  4. SQL語句的注釋
    # 單行注釋
    /* 多行注釋 */
  5. 操作邏輯庫(kù)
SHOW DATABASES;  # 顯示所有數(shù)據(jù)庫(kù)
CREATE DATABASE demo; # 創(chuàng)建數(shù)據(jù)庫(kù)
DROP DATABASE demo; # 刪除指定數(shù)據(jù)庫(kù)
  1. 創(chuàng)建數(shù)據(jù)表
CREATE TABLE 數(shù)據(jù)表(
    列名1 數(shù)據(jù)類型 [約束] [COMMENT 注釋],
    列名1 數(shù)據(jù)類型 [約束] [COMMENT 注釋],
    ...... 
)[COMMENT = 注釋];

USE test;
CREATE TABLE student(
    id INT UNSIGNED PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    sex CHAR(2) NOT NULL,
    birthday DATE NOT NULL,
    tel CHAR(11) NOT NULL,
    remark VARCHAR(200)
)default charset = utf8;

對(duì)應(yīng)數(shù)據(jù)庫(kù)表位置：/usr/local/mysql/data/test/student.ibd
UNSIGNED：無符號(hào)整數(shù)。
注意：中括號(hào)內(nèi)為可選內(nèi)容。

  1. 設(shè)置編碼
    創(chuàng)建數(shù)據(jù)表時(shí)指定編碼
CREATE TABLE student(
  ...
)default charset = utf8;

設(shè)置數(shù)據(jù)庫(kù)編碼

ALTER DATABASE XXX CHARACTER SET utf8;
  1. 數(shù)據(jù)表添加數(shù)據(jù)
INSERT INTO student VALUES(1, "李強(qiáng)", "男", "1995-5-15", "13356789012", NULL);

創(chuàng)建數(shù)據(jù)表時(shí)需要設(shè)置utf8編碼，否則添加數(shù)據(jù)時(shí)報(bào)以下錯(cuò)誤：
1366 - Incorrect string value: '\xE6\x9D\x8E\xE5\xBC\xBA' for column 'name' at row 1, Time: 0.000000s。

  1. 數(shù)據(jù)表的其他操作
SHOW  TABLES; # 查看當(dāng)前數(shù)據(jù)庫(kù)中的數(shù)據(jù)表
DESC student;  # 查看某個(gè)數(shù)據(jù)表結(jié)構(gòu)
SHOW CREATE TABLE student; # 查看建表時(shí)候的SQL語句
DROP TABLE student; #刪除數(shù)據(jù)庫(kù)表
  1. 數(shù)據(jù)類型

(1) 數(shù)字

類型 大小 說明
TINYINT 1字節(jié) 小整數(shù)
SMALLINT 1字節(jié) 普通整數(shù)
MEDIUMINT 3字節(jié) 普通整數(shù)
INT 4字節(jié) 較大整數(shù)
BAGINT 8字節(jié) 大整數(shù)
FLOAT 4字節(jié) 單精度浮點(diǎn)數(shù)
DOUBLE 8字節(jié) 雙精度浮點(diǎn)數(shù)
DECIMAL ------ 高精度小數(shù) DECIMAL(10, 2)

十進(jìn)制的浮點(diǎn)數(shù)無法在計(jì)算機(jī)中用二進(jìn)制精確表達(dá)。
例如：0.2 = 1/8 + 1/16 + 1/32 + ……
DECIMAL保存小數(shù)不會(huì)丟失精度，10表示整數(shù)和小數(shù)一共10個(gè)字符，2表示2個(gè)小數(shù)。

CREATE TABLE temp(
    id INT UNSIGNED PRIMARY KEY,
    num FLOAT(20,10)
)
INSERT INTO temp VALUES(1, 0.2); #保存為 0.2000000030

CREATE TABLE temp(
    id INT UNSIGNED PRIMARY KEY,
    num DECIMAL(20,10)
)
INSERT INTO temp VALUES(1, 0.2);  # 保存為 0.2000000000

(2) 字符串

類型 大小 說明
CHAR 1-255字符 固定長(zhǎng)度字符串
VARCHAR 1-65535字符 不固定長(zhǎng)度字符串
TEXT 1-65535字符 不確定長(zhǎng)度字符串
MEDIUMTEXT 1-1千6百萬字符 不確定長(zhǎng)度字符串
LONGTEXT 1-42億字符 不確定長(zhǎng)度字符串

(3) 日期類型

類型 大小 說明
DATE 3字節(jié) 日期
TIME 3字節(jié) 時(shí)間
YEAR 1字節(jié) 年份
DATETINE 8字節(jié) 日期時(shí)間
TIMESTAMP 4字節(jié) 時(shí)間戳
  1. 修改表結(jié)構(gòu)

(1) 添加字段

ALTER TABLE student
ADD address VARCHAR(200) NOT NULL,
ADD home_tel CHAR(11) NOT NULL;

(2) 修改字段類型和約束

ALTER TABLE student
MODIFY address VARCHAR(300) NOT NULL,
MODIFY home_tel VARCHAR(20) NOT NULL;

DESC student; # 查看某個(gè)數(shù)據(jù)表結(jié)構(gòu)

(3) 修改字段名稱

ALTER TABLE student
CHANGE address home_address VARCHAR(300) NOT NULL;
DESC student; # 查看某個(gè)數(shù)據(jù)表結(jié)構(gòu)

(4) 刪除字段

ALTER TABLE student
DROP home_address,
DROP home_tel;
DESC student; # 查看某個(gè)數(shù)據(jù)表結(jié)構(gòu)

2.3 數(shù)據(jù)庫(kù)表字段約束

  1. 數(shù)據(jù)庫(kù)的范式
    構(gòu)造數(shù)據(jù)庫(kù)必須遵守一定的規(guī)則，這種規(guī)則就是范式。

(1) 第一范式 - 原子性
數(shù)據(jù)表的每一列都是不可分割的基本數(shù)據(jù)項(xiàng)，同一列中不能有多個(gè)值，也不能存在重復(fù)的屬性。

學(xué)號(hào) 姓名 班級(jí)
1000 劉娜 高3年級(jí)1班

高3年級(jí)1班數(shù)據(jù)可分割。

學(xué)號(hào) 姓名 年級(jí) 班級(jí)
1000 劉娜 高3 1班

(2) 第二范式 - 唯一性
數(shù)據(jù)表中的每條記錄必須是唯一的。為了實(shí)現(xiàn)區(qū)分，通常要為表加上一個(gè)列用來存儲(chǔ)唯一標(biāo)識(shí)，這個(gè)唯一屬性列被稱作主鍵列。

學(xué)號(hào) 考試成績(jī) 日期
230 58 2018-07-15
230 58 2018-07-15

無法區(qū)分重復(fù)數(shù)據(jù)。

流水號(hào) 學(xué)號(hào) 考試成績(jī) 日期
201807151087 230 58 2018-07-15
201807152396 230 58 2018-07-15

(3) 第三范式 - 關(guān)聯(lián)性
每列都與主鍵有直接關(guān)系，不存在傳遞依賴。

爸爸 兒子 女兒 女兒的玩具 女兒的衣服
陳華 陳浩 陳婷 海綿寶寶 校服

后兩列與主鍵無直接關(guān)系。

爸爸 兒子 女兒
陳華 陳浩 陳婷
女兒 女兒的玩具 女兒的衣服
陳婷 海綿寶寶 校服

依照第三范式，數(shù)據(jù)可以拆分保存到不同的數(shù)據(jù)表，彼此保持關(guān)聯(lián)。

編號(hào) 部門 電話
10 財(cái)務(wù)部 1001
20 技術(shù)部 1002
30 銷售部 1003
編號(hào) 姓名 性別 部門 入職日期
1 陳浩 男 10 2018-5-10
2 李婷婷 女 30 2018-3-22
  1. 字段約束
    MySQL中的字段約束共有四種。
約束名稱 關(guān)鍵字 描述
主鍵約束 PRIMARY KEY 字段值唯一，且不能為NULL
非空約束 NOT NULL 字段值不能為NULL
唯一約束 UNIQUE 字段值唯一(非NULL時(shí))，且可以為NULL
外鍵約束 FOREIGN KEY 保持關(guān)聯(lián)數(shù)據(jù)的邏輯性
  1. 主鍵約束
    主鍵約束要求字段的值在全表必須唯一，而且不能為NULL值。
    由于數(shù)字檢索非?？欤ㄗh主鍵使用數(shù)字類型。
    如果主鍵是數(shù)字類型，還可以設(shè)置自動(dòng)增長(zhǎng)。
CREATE TABLE teacher(
    id INT PRIMARY KEY AUTO_INCREMENT,
    ...
);

AUTO_INCREMENT表示id未指定時(shí)，自增。

  1. 非空約束
    非空約束要求字段值不能為NULL。
CREATE TABLE t_teacher(
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    married BOOLEAN NOT NULL DEFAULT FALSE
);

DEFAULT表示默認(rèn)值。

  1. 唯一約束
    唯一約束要求字段值如果不為NULL，那么在全表必須唯一。
CREATE TABLE t_teacher(
    id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    tel CHAR(11) NOT NULL UNIQUE,
    married BOOLEAN NOT NULL DEFAULT FALSE
)default charset = utf8;
  1. 外鍵約束
    外鍵約束用來保證關(guān)聯(lián)數(shù)據(jù)的邏輯關(guān)系。
    父表t_dept如下：
deptno dname tel
10 財(cái)務(wù)部 1001
20 技術(shù)部 1002
30 銷售部 1003

子表t_emp如下：

empno ename sex deptno hiredate
1 陳浩 男 10 2018-05-10
2 李婷婷 女 30 2018-03-22 
CREATE TABLE t_dept(
    deptno INT UNSIGNED PRIMARY KEY,
    dname VARCHAR(20) NOT NULL UNIQUE,
    tel CHAR(4) UNIQUE
);

CREATE TABLE t_emp(
    empno INT UNSIGNED PRIMARY KEY,
    ename VARCHAR(20) NOT NULL,
    sex ENUM("男", "女") NOT NULL,
    deptno INT UNSIGNED NOT NULL,
    hiredate  DATE NOT NULL,
    FOREIGN KEY (deptno) REFERENCES t_dept(deptno)
)

由于存在外鍵約束，如果想刪除某一個(gè)部門，必須先刪除該部門內(nèi)全部員工。

  1. 外鍵約束閉環(huán)問題
    如果形成外鍵閉環(huán)，我們將無法刪除任何一張表的記錄。
    開發(fā)過程中，應(yīng)避免外鍵約束。
  2. 數(shù)據(jù)庫(kù)的索引機(jī)制
    一旦數(shù)據(jù)排序之后，查找的速度就會(huì)翻倍，現(xiàn)實(shí)世界和程序世界都是如此。
    主鍵默認(rèn)有索引，除了主鍵之外，我們可以給其他字段添加索引。字段添加索引可以加快查詢速度。
  3. 創(chuàng)建索引的方式
    (1) 創(chuàng)建數(shù)據(jù)表時(shí)候添加索引
CREATE TABLE 表名稱(
    ......,
    INDEX [索引名稱] (字段),
    ......
);

CREATE TABLE t_message(
    id INT UNSIGNED PRIMARY KEY,
    content VARCHAR(200) NOT NULL,
    type ENUM("公告", "通報(bào)", "個(gè)人通知") NOT NULL,
    create_time TIMESTAMP NOT NULL,
    INDEX idx_type (type)
);

(2) 創(chuàng)建數(shù)據(jù)表之后再添加索引

//添加索引方式一
CREATE INDEX 索引名稱 ON 表名(字段);
//添加索引方式二
ALTER TABLE 表名稱 ADD INDEX [索引名](字段);
//查看索引
SHOW INDEX FROM 表名;
//刪除索引
DROP INDEX 索引名稱 ON 表名;

CREATE TABLE t_message(
    id INT UNSIGNED PRIMARY KEY,
    content VARCHAR(200) NOT NULL,
    type ENUM("公告", "通報(bào)", "個(gè)人通知") NOT NULL,
    create_time TIMESTAMP NOT NULL,
    INDEX idx_type (type)
);

SHOW INDEX FROM t_message;
DROP INDEX idx_type ON t_message;
CREATE INDEX idx_type ON t_message(type);
DROP INDEX idx_type ON t_message;
ALTER TABLE t_message ADD INDEX idx_type(type);
  1. 索引的使用原則
    數(shù)據(jù)量很大，而且經(jīng)常被查詢的數(shù)據(jù)表可以設(shè)置索引。
    索引只添加在經(jīng)常被用作索引條件的字段上。
    不要在大字段上設(shè)置索引。

2.4 總結(jié)

  1. 掌握了數(shù)據(jù)庫(kù)和數(shù)據(jù)表的管理
  2. 了解MySQL常用數(shù)據(jù)類型
  3. 掌握MySQL的字段約束
  4. 掌握索引機(jī)制以及適用場(chǎng)景

3. 數(shù)據(jù)庫(kù)的基本查詢

3.1 介紹

  1. 數(shù)據(jù)的簡(jiǎn)單查詢
    無條件查詢記錄，字段的計(jì)算和字段的別名。
  2. 數(shù)據(jù)的高級(jí)查詢
    數(shù)據(jù)排序、分頁(yè)、去除重復(fù)數(shù)據(jù)。
  3. 數(shù)據(jù)的有條件查詢
    條件表達(dá)式：數(shù)字運(yùn)算符、比較運(yùn)算法、邏輯運(yùn)算符、按位運(yùn)算符。

3.2 數(shù)據(jù)操作語言的基本操作

  1. 數(shù)據(jù)表的基本查詢
    查詢某一個(gè)表的所有字段：SELECT * FROM 表名;
    查詢某一個(gè)表的某些字段：SELECT 字段1, 字段2, 字段3 FROM 表名;
    SELECT語句屏蔽了物理層的操作，用戶不必關(guān)心數(shù)據(jù)的真實(shí)存儲(chǔ)，交由數(shù)據(jù)庫(kù)高效的查找數(shù)據(jù)。
USE demo;
SELECT * FROM t_emp;
# 7369  SMITH   CLERK   7902    1980-12-17  800.00      20
# 7499  ALLEN   SALESMAN    7698    1981-02-20  1600.00 300.00  30
# 7521  WARD    SALESMAN    7698    1981-02-22  1250.00 500.00  30

SELECT empno,ename,sal FROM t_emp; 
# 7369  SMITH   800.00
# 7499  ALLEN   1600.00
# 7521  WARD    1250.00
  1. 列設(shè)置別名
    SELECT子句中如果使用了表達(dá)式，那么這列的名字就默認(rèn)為表達(dá)式。我們可以通過AS對(duì)列進(jìn)行重命名。
SELECT empno, sal * 12 FROM t_emp;
# empno sal*12 
# 7369  9600.00
# 7499  19200.00

SELECT empno, sal * 12 AS "income" FROM t_emp;
# empno income 
# 7369  9600.00
# 7499  19200.00
  1. 查詢部分?jǐn)?shù)據(jù)(數(shù)據(jù)分頁(yè))
    SELECT 字段 FROM 數(shù)據(jù)表 LIMIT 起始位置, 數(shù)據(jù)量; 
SELECT empno, ename FROM t_emp LIMIT 10, 5;
# 7876  ADAMS
# 7900  JAMES
# 7902  FORD
# 7934  MILLER

注意：LIMIT N等價(jià)于LIMIT 0 N，表示查詢前N條數(shù)據(jù)。
子句執(zhí)行順序：FROM > SELECT > LIMIT。

  1. 查詢結(jié)果排序
    SELECT 字段 FROM 數(shù)據(jù)表 ORDER BY 列名 [ASC|DESC];
    排序規(guī)則：如果是數(shù)字類型，按照數(shù)據(jù)大??；如果是日期類型，按照日期先后；如果是字符串類型，按照字符集序號(hào)。
SELECT empno, ename, sal, deptno FROM t_emp ORDER BY sal; # 升序
# 7369  SMITH   800.00  20
# 7900  JAMES   950.00  30
# 7876  ADAMS   1100.00 20
# 7521  WARD    1250.00 30

SELECT empno, ename, sal, hiredate FROM t_emp ORDER BY hiredate DESC; # 降序
# 7876  ADAMS   1100.00 1983-01-12
# 7788  SCOTT   3000.00 1982-12-09
# 7934  MILLER  1300.00 1982-01-23
# 7900  JAMES   950.00  1981-12-03
  1. 多個(gè)排序字段
    首先采用第一個(gè)排序字段進(jìn)行排序，如果第一個(gè)排序字段的值相同，則繼續(xù)采用第二個(gè)排序字段，依次類推。如果所有的排序字段的值都相同，則比較主鍵大小。
SELECT empno, ename, sal, hiredate FROM t_emp ORDER BY sal DESC, hiredate; #工資降序，日期升序
# 7902  FORD    3000.00 1981-12-03
# 7788  SCOTT   3000.00 1982-12-09
  1. 排序并查詢部分?jǐn)?shù)據(jù)
SELECT empno, ename, sal, hiredate FROM t_emp ORDER BY sal DESC LIMIT 3;
# 7839  KING    5000.00 1981-11-17
# 7902  FORD    3000.00 1981-12-03
# 7788  SCOTT   3000.00 1982-12-09

子句執(zhí)行順序：FROM > SELECT > ORDER BY > LIMIT。

  1. 查詢結(jié)果去重
    SELECT DISTINCT 字段 FROM 數(shù)據(jù)表 
SELECT DISTINCT job FROM t_emp;
# CLERK
# SALESMAN
# MANAGER
# ANALYST
# PRESIDENT
  1. DISTINCT關(guān)鍵字注意事項(xiàng)
    使用DISTINCT的SELECT子句中只能查詢一列數(shù)據(jù)，如果查詢多列，去除重復(fù)記錄就會(huì)失效。
SELECT DISTINCT job, ename FROM t_emp;
# CLERK SMITH
# SALESMAN  ALLEN
# SALESMAN  WARD
# MANAGER   JONES

DISTINCT關(guān)鍵字只能在SELECT子句中使用一次且只能修飾第一個(gè)字段。

SELECT job, DISTINCT ename FROM t_emp; 
# SELECT使用多次，報(bào)錯(cuò)
SELECT DISTINCT job, DISTINCT ename FROM t_emp; 
# SELECT修飾第二個(gè)字段，報(bào)錯(cuò)

3.3 條件查詢

  1. 條件查詢
    SELECT 字段 FROM 數(shù)據(jù)表 WHERE 條件 [AND|OR] 條件; 
SELECT empno, ename, sal FROM t_emp 
WHERE (deptno = 10 OR deptno = 30) AND sal > 2000;
# 7698  BLAKE   2850.00
# 7782  CLARK   2450.00
# 7839  KING    5000.00
  1. 運(yùn)算符
    數(shù)字運(yùn)算符、比較運(yùn)算符、邏輯運(yùn)算符、按位運(yùn)算符
  2. 數(shù)字運(yùn)算符
表達(dá)式 意義 例子
+ 加 1+ 2
- 減 2 - 4
* 乘 7 * 9
/ 除 351 / 15
% 求模 10 % 3 
SELECT 10 + NULL; # NULL
SELECT NOW(); # 2019-12-11 21:34:49

SELECT ename, sal, hiredate, comm
FROM t_emp
WHERE deptno = 10 AND (sal + IFNULL(comm,0)) * 12 > 15000
AND DATEDIFF(NOW(),hiredate) / 365 >= 20
# CLARK 2450.00 1981-06-09  
# KING  5000.00 1981-11-17  
# MILLER    1300.00 1982-01-23

注意：IFNULL(M, N)表示當(dāng)M為NULL時(shí)，值為N；否則，值為M。

  1. 比較運(yùn)算符
表達(dá)式 意義 例子
> 大于 age > 18
>= 大于等于 age >= 18
< 小于 age < 18
<= 小于等于 age <= 18
= 等于 age = 18
!= 不等于 age != 18
IN 包含 age IN(18, 20, 22)
IS NULL 為空 comm IS NULL
IS NOT NULL 不為空 comm IS NOT NULL
BETWEEN AND 范圍 sal BETWEEN 200 AND 300
LIKE 模糊查詢 ename LIKE "_A%"(_表示一個(gè)字符，%表示任意個(gè)字符)
REGEXP 正則表達(dá)式 ename REGEXP "[a-zA-Z]{4}" 
SELECT empno, ename, sal, deptno, hiredate
FROM t_emp
WHERE deptno IN(10, 20, 30) 
AND job != "SALESMAN"
AND hiredate < "1981-06-01"
# 7369  SMITH   800.00  20  1980-12-17
# 7566  JONES   2975.00 20  1981-04-02
# 7698  BLAKE   2850.00 30  1981-05-01

SELECT ename,comm, sal 
FROM t_emp 
WHERE comm IS not NULL
AND sal BETWEEN 1000 AND 3000
AND ename LIKE "%A%";
# ALLEN 300.00  1600.00
# WARD  500.00  1250.00
# MARTIN    1400.00 1250.00

注意：使用IS NULL判斷是否為空，= NULL無法判斷。

  1. 邏輯運(yùn)算符
表達(dá)式 意義 例子
AND 與 age > 18 AND sex = "男"
OR 或 age > 18 OR sex = "男"
NOT 非 NOT deptno = 20 (等價(jià)于 deptno != 20)
XOR 異或 age > 18 XOR sex = "男" 
SELECT 
ename, deptno
FROM t_emp
WHERE NOT deptno IN(10, 20) AND sal > 2000;
# BLAKE 30

注釋：AND的優(yōu)先級(jí)高于OR。

  1. 二進(jìn)制按位運(yùn)算
    二進(jìn)制位運(yùn)算的實(shí)質(zhì)是將參與運(yùn)算的兩個(gè)操作符，按對(duì)應(yīng)的二進(jìn)制數(shù)逐位進(jìn)行邏輯運(yùn)算。
表達(dá)式 意義 例子
& 與 3&7
| 或 3|7
~ 取反 ~10
^ 異或 3^7
<< 左移 10<<1
>> 右移 10>>1 
SELECT 3 & 7   #0011 & 0111 = 0011 即 3
  1. WHERE子句的注意事項(xiàng)
    WHERE子句中，我們應(yīng)該把索引條件或者篩選掉記錄最多的條件寫在最左側(cè)。
    從左到右：索引條件 > 篩選掉最多記錄的條件 > 普通檢索條件
SELECT empno, ename FROM t_emp WHERE ename="FORD" AND sal >= 2000;
SELECT empno, ename FROM t_emp WHERE deptno = 10 AND sal > 2000;
  1. 子句執(zhí)行順序
    子句執(zhí)行順序：FROM > WHERE >SELECT > ORDER BY > LIMIT。
    找數(shù)據(jù)表 > 條件過濾 > 字段過濾 > 排序 > 數(shù)量過濾

3.4 總結(jié)

  1. 掌握SELECT子句中的列別名和去除重復(fù)記錄
  2. 掌握數(shù)據(jù)排序語法
  3. 掌握數(shù)據(jù)分頁(yè)語法
  4. 掌握有條件查詢的語法和運(yùn)算符

4. 數(shù)據(jù)庫(kù)的高級(jí)查詢

4.1 介紹

  1. 數(shù)據(jù)統(tǒng)計(jì)分析
    聚合函數(shù)、分組查詢、HAVING子句。
  2. 多表連接查詢
    內(nèi)連接、外連接以及多表查詢的多種用法。
  3. 子查詢
    單行子查詢、多行子查詢、WHERE子查詢、FROM子查詢、SELECT子查詢。

4.2 高級(jí)查詢

  1. 聚合函數(shù)
    聚合函數(shù)可以對(duì)數(shù)據(jù)求和、求最大值、最小值以及平均值等等。
SELECT AVG(sal + IFNULL(comm,0)) AS avg FROM t_emp;
# 2195.000000
  1. SUM函數(shù)
    SUM函數(shù)用于求和，只能用于數(shù)字類型，字符類型的統(tǒng)計(jì)結(jié)果為0，日期類型的統(tǒng)計(jì)結(jié)果為毫秒數(shù)相加。
SELECT SUM(sal) FROM t_emp WHERE deptno IN(10, 20);
# 19625.00
  1. MAX函數(shù)
    MAX函數(shù)用于獲得非空值的最大值。
SELECT MAX(sal + IFNULL(comm, 0)) FROM t_emp WHERE deptno IN(10, 20);
# 5000.00
SELECT MAX(LENGTH(ename)) FROM t_emp;
# 6
  1. MIN函數(shù)
    MIN函數(shù)用于獲得非空值的最小值。
SELECT MIN(empno), MIN(hiredate) FROM t_emp;
# 7369  1980-12-17
  1. AVG函數(shù)
    AVG函數(shù)用于獲得非空值的平均值，非數(shù)字?jǐn)?shù)據(jù)統(tǒng)計(jì)結(jié)果為0。
SELECT AVG(sal + IFNULL(comm, 0)) FROM t_emp; # 2195.000000
SELECT  ROUND(AVG(sal + IFNULL(comm, 0)), 1) FROM t_emp; # 2195.0
SELECT AVG(ename) FROM t_emp; # 0

使用ROUND可以對(duì)數(shù)據(jù)進(jìn)行四舍五入。

  1. COUNT函數(shù)
    COUNT(*)用于獲得所有(包含空值)的記錄數(shù)，COUNT(列名)用于獲得列值為非空值的記錄數(shù)。
SELECT COUNT(*), COUNT(comm) FROM t_emp;  # 15  5
  1. 查詢10和20部門中，底薪超過2000元并且工齡超過15年的員工數(shù)量
SELECT COUNT(*) 
FROM t_emp 
WHERE deptno IN(10, 20) 
AND sal >=2000 
AND DATEDIFF(NOW(),hiredate) / 365 >= 15; # 5
  1. WHERE語句中不可以使用聚合函數(shù)
    聚合函數(shù)是對(duì)WHERE語句查詢出的數(shù)據(jù)集合進(jìn)行求和、求最值以及平均值等。聚合函數(shù)依賴于WHERE語句查詢出的數(shù)據(jù)結(jié)果。因此，WHERE語句中不能使用聚合函數(shù)。
    條件過濾是查詢前的過濾條件，聚合函數(shù)是查詢后的統(tǒng)計(jì)分析。即：條件過濾 → 查詢結(jié)果 → 聚合函數(shù)。
    例如：查詢1985年以后入職的員工，底薪超過公司平均底薪的員工數(shù)量。
SELECT COUNT(*) FROM t_emp 
WHERE hiredate >= '1985-01-01' AND sal > AVG(sal); # 報(bào)錯(cuò)

注釋：正確寫法參考內(nèi)連接查詢。

  1. 分組查詢
    默認(rèn)情況下，聚合函數(shù)是對(duì)全表范圍內(nèi)查詢到的數(shù)據(jù)做統(tǒng)計(jì)。無法對(duì)數(shù)據(jù)進(jìn)行分組，分別計(jì)算各組使用聚合函數(shù)的統(tǒng)計(jì)結(jié)果。
    GROUP BY子句的作用是通過一定的規(guī)則將一個(gè)數(shù)據(jù)集劃分成若干個(gè)小的區(qū)域，然后分別對(duì)每個(gè)小區(qū)域進(jìn)行數(shù)據(jù)匯總處理。
SELECT deptno, ROUND(AVG(sal)) FROM t_emp GROUP BY deptno;
# 10    2917
# 20    2175
# 30    1557

注意：分組查詢SELECT語句除了聚合函數(shù)結(jié)果之外，只能夠輸出GROUP BY對(duì)應(yīng)的組別。

  1. 逐級(jí)分組
    數(shù)據(jù)庫(kù)支持多列分組條件，執(zhí)行的時(shí)候逐級(jí)分組。
    例如：查詢每個(gè)部門里，每種職位的員工數(shù)量和員工底薪。
SELECT deptno, job, COUNT(*), ROUND(AVG(sal) )
FROM t_emp 
GROUP BY deptno, job
ORDER BY deptno;

-- 10   CLERK   1   1300
-- 10   MANAGER 1   2450
-- 10   PRESIDENT   1   5000
-- 20   ANALYST 2   3000
-- 20   CLERK   2   950
-- 20   MANAGER 1   2975
-- 30   CLERK   1   950
-- 30   MANAGER 1   2850
-- 30   SALESMAN    5   1420
  1. 分組查詢對(duì)SELECT子句的要求
    查詢語句中如果含有GROUP BY子句，那么SELECT子句中的內(nèi)容就必須要遵守以下規(guī)定：SELECT子句中可以包含聚合函數(shù)，或者GROUP BY子句的分組列，其余內(nèi)容均不可以出現(xiàn)在SELECT子句中。
    分析：如果使用GROUP BY子句分組查詢，那么聚合函數(shù)結(jié)果和GROUP BY子句的分組列結(jié)果在一組中只有一個(gè)結(jié)果。而其他內(nèi)容，一組中有多個(gè)結(jié)果。因此，無法匹配。
SELECT deptno, COUNT(*), ROUND(AVG(sal)) FROM t_emp GROUP BY deptno;
-- 10   3   2917
-- 20   5   2175
-- 30   7   1557

SELECT deptno, COUNT(*), sal FROM t_emp GROUP BY deptno; # 報(bào)錯(cuò)

如果數(shù)據(jù)通過deptno分成了n組。那么deptno的數(shù)量等于n，表示每一組的deptno；COUNT(*)的數(shù)量等于n，表示每一組數(shù)據(jù)的個(gè)數(shù)；sal的數(shù)量不等于n。

  1. 對(duì)分組結(jié)果集合再次做匯總計(jì)算
SELECT deptno, COUNT(*), AVG(sal), MAX(sal), MIN(sal), SUM(sal)
FROM t_emp 
GROUP BY deptno 
WITH ROLLUP;

-- 10 3 2916.666667 5000.00 1300.00 8750.00
-- 20   5   2175.000000 3000.00 800.00  10875.00
-- 30 7 1557.142857 2850.00 950.00  10900.00
-- NULL 15  2035.000000 5000.00 800.0030525.00
  1. GROUP_CONCAT函數(shù)
    GROUP_CONCAT函數(shù)可以把分組查詢中的某個(gè)字段拼接成一個(gè)字符串。
    例如：查詢每個(gè)部門中的底薪超過2000元的人數(shù)和員工姓名。
SELECT deptno, COUNT(*), ename
FROM t_emp 
WHERE sal > 2000 
GROUP BY deptno;  # 報(bào)錯(cuò)

SELECT deptno, COUNT(*), GROUP_CONCAT(ename)  
FROM t_emp 
WHERE sal > 2000 
GROUP BY deptno;
-- 10   2   CLARK,KING
-- 20   3   JONES,SCOTT,FORD
-- 30   1   BLAKE

注意：WHERE語句必須在GROUP BY語句之前。

  1. 各種子句的執(zhí)行順序
    FROM > WHERE > GROUP BY > SELECT > ORDER BY > LIMIT
    找數(shù)據(jù)表 > 條件過濾 > 分組 > 字段過濾 > 排序 > 數(shù)量過濾
  2. 分組查詢遇到的困難？
    WHERE語句中不可以使用聚合函數(shù)。
    例如：查詢部門平均底薪超過2000元的部門編號(hào)。
SELECT deptno FROM t_emp 
WHERE AVG(sal) > 2000 GROUP BY deptno; # 報(bào)錯(cuò)
  1. HAVING子句的使用
    HAVING子句和WHERE子句類似，都可以用來做條件篩選，區(qū)別在于HAVING子句寫在GROUP BY子句之后。GROUP BY子句執(zhí)行分組之后，會(huì)立即執(zhí)行HAVING子句。
    例如：查詢部門平均底薪超過2000元的部門編號(hào)。
SELECT deptno FROM t_emp 
GROUP BY deptno HAVING AVG(sal) >= 2000;
-- 10
-- 20

查詢每個(gè)部門中，1982年以后入職的員工超過2個(gè)人的部門編號(hào)

SELECT deptno FROM t_emp 
WHERE hiredate >= "1982-01-01" 
GROUP BY deptno 
HAVING COUNT(*) >= 2
ORDER BY deptno;
-- 20

SELECT deptno FROM t_emp 
WHERE hiredate >= "1982-01-01" 
GROUP BY deptno 
HAVING sal >= AVG(sal); -- 報(bào)錯(cuò)
  1. WHERE子句與HAVING子句
    WHERE子句：對(duì)一組數(shù)據(jù)進(jìn)行過濾。
    GROUP BY子句：對(duì)一組數(shù)據(jù)進(jìn)行分組。
    HAVING子句：對(duì)多組數(shù)據(jù)進(jìn)行過濾。
SELECT deptno, COUNT(*) FROM t_emp 
GROUP BY 1 
HAVING deptno in (10, 20);
-- 10   3
-- 20   5

SELECT deptno, COUNT(*) FROM t_emp 
WHERE deptno in (10, 20)
GROUP BY 1; 
-- 10   3
-- 20   5

注釋：按照數(shù)字1分組，會(huì)依據(jù)SELECT子句中的第一列進(jìn)行分組。

  1. 字段表達(dá)式和聚合函數(shù)表達(dá)式
    (1) 字段表達(dá)式
    ① 將一組數(shù)據(jù)進(jìn)行過濾，結(jié)果為一組數(shù)據(jù)。
    ② 將一組數(shù)據(jù)進(jìn)行輸出，結(jié)果為一組數(shù)據(jù)。
    (2) 聚合函數(shù)(和、最值、平均值)表達(dá)式
    ③ 將一組數(shù)據(jù)進(jìn)行統(tǒng)計(jì)輸出，結(jié)果為一個(gè)數(shù)據(jù)。
    ④ 將多組數(shù)據(jù)進(jìn)行統(tǒng)計(jì)輸出，結(jié)果為一組數(shù)據(jù)。
    ⑤ 將多組數(shù)據(jù)的統(tǒng)計(jì)結(jié)果進(jìn)行過濾，結(jié)果為一組數(shù)據(jù)。
    SELECT語句(無GROUP BY)：對(duì)一組數(shù)據(jù)進(jìn)行輸出。使用②、③，但②、③不可以同時(shí)使用。
    SELECT語句(有GROUP BY)：對(duì)多組數(shù)據(jù)進(jìn)行輸出。使用④。
    WHERE語句：對(duì)一組數(shù)據(jù)進(jìn)行過濾。使用①。
    HAVING語句：對(duì)多組數(shù)據(jù)進(jìn)行過濾。使用⑤。
    注意：分組字段與普通字段不同，與聚合函數(shù)的使用場(chǎng)景相同。

4.3 表的內(nèi)外連接

  1. 從多張表中查詢數(shù)據(jù)
    從多張表中提取數(shù)據(jù)，必須規(guī)定連接條件的表連接語句，否則就會(huì)出現(xiàn)無條件連接，兩張表的數(shù)據(jù)會(huì)交叉連接，產(chǎn)生笛卡爾積。
SELECT empno, ename, dname FROM t_emp JOIN t_dept;
-- 產(chǎn)生笛卡爾積， 共 15 * 4 = 60 條記錄

SELECT e.empno, e.ename, d.dname
FROM t_emp e JOIN t_dept d ON e.deptno = d.deptno
-- 共15條數(shù)據(jù)

注釋：笛卡爾乘積是指在數(shù)學(xué)中，兩個(gè)集合X和Y的笛卡爾積（Cartesian product），又稱直積，表示為X × Y，第一個(gè)對(duì)象是X的成員而第二個(gè)對(duì)象是Y的所有可能有序?qū)Φ钠渲幸粋€(gè)成員。

  1. 表連接分類
    內(nèi)連接：結(jié)果集中只保留符合連接條件的記錄。
    外連接：不管是否符合連接條件，記錄都保留在結(jié)果集中。
  2. 內(nèi)連接
    內(nèi)連接是最常見的一種表連接，用于查詢多張關(guān)系表符合連接條件的記錄。
    SELECT ... FROM 表1 JOIN 表2 ON 連接條件 JOIN 表3 ON 連接條件;
    SELECT ... FROM 表1 JOIN 表2 WHERE 連接條件;
    SELECT ... FROM 表1, 表2 WHERE 連接條件; 
SELECT e.empno, e.ename, d.dname
FROM t_emp e JOIN t_dept d 
ON e.deptno = d.deptno
-- 寫法1

SELECT e.empno, e.ename, d.dname
FROM t_emp e JOIN t_dept d 
WHERE e.deptno = d.deptno
-- 寫法2

SELECT e.empno, e.ename, d.dname
FROM t_emp e, t_dept d 
WHERE e.deptno = d.deptno
-- 寫法3
  1. 內(nèi)連接練習(xí)
    (1) 查詢每個(gè)員工的工號(hào)、姓名、部門名稱、底薪、職位、工資等級(jí)
-- 寫法一
SELECT e.empno, e.ename, d.dname, e.sal, e.job, s.grade
FROM t_emp e 
JOIN t_dept d ON  e.deptno = d.deptno 
JOIN t_salgrade s ON e.sal BETWEEN s.losal AND s.hisal;

-- 寫法二
SELECT e.empno, e.ename, d.dname, e.sal, e.job, s.grade
FROM t_emp e, t_dept d, t_salgrade s 
WHERE e.deptno = d.deptno AND e.sal BETWEEN s.losal AND s.hisal;

總結(jié)：內(nèi)連接的數(shù)據(jù)表不一定必須有同名字段，只要字段之間符合邏輯關(guān)系(例如：e.sal BETWEEN s.losal AND s.hisal)即可。
(2) 查詢與員工SCOTT相同部門的員工

-- 寫法1：子查詢的寫法, 性能很差
SELECT ename FROM t_emp 
WHERE deptno = (SELECT deptno FROM t_emp WHERE ename = "SCOTT") AND ename != "SCOTT";
-- SMITH JONES ADAMS FORD

-- 寫法2：表連接的寫法，性能很好
SELECT e2.ename FROM t_emp e1 
JOIN t_emp e2 
ON e1.deptno = e2.deptno
WHERE e1.ename = "SCOTT" AND e2.ename != "SCOTT"
-- SMITH JONES ADAMS FORD

-- 寫法3：表連接的等價(jià)寫法
SELECT e2.ename FROM t_emp e1, t_emp e2 
WHERE e1.ename = "SCOTT" AND e1.deptno = e2.deptno AND e2.ename != "SCOTT";
-- SMITH JONES ADAMS FORD

總結(jié)：相同的數(shù)據(jù)表也可以做表連接。
(3) 查詢底薪超過公司平均底薪的員工信息

SELECT e2.ename, e2.empno, e2.sal
FROM t_emp e1 JOIN t_emp e2
ON e2.sal >=  AVG(e1.sal); 
-- 報(bào)錯(cuò): 聚合函數(shù)不能寫在ON子句或WHERE子句中

SELECT e.ename, e.empno, e.sal
FROM t_emp e JOIN (SELECT  AVG(sal) avg FROM t_emp) t 
ON e.sal >= t.avg;
-- JONES    7566    2975.00
-- BLAKE    7698    2850.00
-- CLARK    7782    2450.00
-- SCOTT    7788    3000.00
-- KING 7839    5000.00
-- FORD 7902    3000.00

總結(jié)：t為t_emp表中平均月薪的查詢結(jié)果，t也是一張數(shù)據(jù)表。
(4) 查詢RESEARCH部門人數(shù)、最高底薪、最低底薪、平均底薪、平均工齡

SELECT COUNT(*), MAX(e.sal), MIN(e.sal), AVG(e.sal), 
FLOOR(AVG(DATEDIFF(NOW(), e.hiredate)/365)) avg
FROM t_emp e JOIN t_dept t ON e.deptno = t.deptno WHERE t.dname = "RESEARCH"
-- 5    3000.00 800.00  2175.000000 37

(5) 查詢每種職業(yè)的最高工資、最低工資、平均工資、最高工資等級(jí)和最低工資等級(jí)。

SELECT e.job, 
MAX(e.sal + IFNULL(e.comm,0)), 
MIN(e.sal + IFNULL(e.comm,0)), 
AVG(e.sal + IFNULL(e.comm,0)), 
MAX(s.grade),
MIN(s.grade)
FROM t_emp e JOIN t_salgrade s 
ON (e.sal + IFNULL(e.comm,0)) BETWEEN s.losal AND s.hisal
GROUP BY e.job;

-- ANALYST  3000.00 3000.00 3000.000000 4   4
-- CLERK    1300.00 800.00  1037.500000 2   1
-- MANAGER  2975.00 2450.00 2758.333333 4   4
-- PRESIDENT    5000.00 5000.00 5000.000000 5   5
-- SALESMAN 2650.00 1500.00 1900.000000 4   3

(6) 查詢每個(gè)底薪超過部門平均底薪的員工信息

SELECT  e.empno, e.ename, e.deptno
FROM t_emp e 
JOIN (SELECT AVG(sal) avg, deptno FROM t_emp GROUP BY deptno) d
ON e.deptno = d.deptno AND e.sal > d.avg;
-- 7839 KING    10
-- 7566 JONES   20
-- 7788 SCOTT   20
-- 7902 FORD    20
-- 7499 ALLEN   30
-- 7698 BLAKE   30
  1. 外連接
    外連接與內(nèi)連接的區(qū)別在于，除了符合條件的記錄之外，結(jié)果集中還會(huì)保留不符合條件的記錄(保留所有記錄)。
SELECT e.empno, e.ename, e.deptno 
FROM t_emp e JOIN t_dept d 
ON e.deptno = d.deptno;
-- 14條記錄
-- ......
-- 7902 FORD    20
-- 7934 MILLER  10

SELECT e.empno, e.ename, e.deptno 
FROM t_emp e LEFT JOIN t_dept d 
ON e.deptno = d.deptno;
-- 15條記錄
-- .....
-- 7902 FORD    20
-- 7934 MILLER  10
-- 8000 陳浩
  1. 左連接與右連接
    左外連接(LEFT JOIN)：保留左表所有數(shù)據(jù)記錄，與右表做連接。如果右表沒有匹配項(xiàng)，則使用NULL值進(jìn)行匹配。右外連接(RIGHT JOIN)相反。
SELECT e.empno, e.ename, e.deptno 
FROM t_emp e LEFT JOIN t_dept d 
ON e.deptno = d.deptno;
-- 15條記錄

SELECT e.empno, e.ename, e.deptno 
FROM t_dept d RIGHT JOIN t_emp e
ON e.deptno = d.deptno;
-- 15條記錄
  1. 外連接練習(xí)
    查詢每個(gè)部門的名稱和人數(shù)，如果部門沒有員工，員工數(shù)量為0。
-- 錯(cuò)誤寫法
SELECT d.dname, COUNT(*) 
FROM t_dept d LEFT JOIN t_emp e ON
d.deptno = e.deptno GROUP BY d.deptno;
-- ACCOUNTING   3
-- RESEARCH 5
-- SALES    6
-- OPERATIONS   1  錯(cuò)誤結(jié)果

--正確寫法
SELECT d.dname, COUNT(e.deptno) 
FROM t_dept d LEFT JOIN t_emp e ON
d.deptno = e.deptno GROUP BY d.deptno;
-- ACCOUNTING   3
-- RESEARCH 5
-- SALES    6
-- OPERATIONS   0

注意：?jiǎn)T工使用NULL與左邊的部門匹配，COUNT(*)計(jì)算結(jié)果為1。

  1. UNION關(guān)鍵字可以將多個(gè)查詢語句的結(jié)果集進(jìn)行合并。
    查詢語句 UNION 查詢語句 UNION ......
    要求：不同查詢語句的結(jié)果集的字段相同。
    例如：查詢每個(gè)部門的名稱和人數(shù)。如果部門沒有員工，員工數(shù)量為0。如果員工沒有部門，部門名稱使用NULL代替。
(SELECT d.dname, COUNT(e.deptno) 
FROM t_dept d LEFT JOIN t_emp e 
ON d.deptno = e.deptno 
GROUP BY d.deptno) 
UNION
(SELECT d.dname, COUNT(*) 
FROM t_dept d RIGHT JOIN t_emp e 
ON d.deptno = e.deptno 
GROUP BY d.deptno);
-- ACCOUNTING   3
-- RESEARCH 5
-- SALES    6
-- OPERATIONS   0
-- NULL 1
  1. 查詢每個(gè)員工的編號(hào)、姓名、部門名稱、月薪、工資等級(jí)、工齡、上司編號(hào)、上司姓名、上司部門
SELECT 
e1.empno, 
e1.ename, 
d1.dname, 
e1.sal + IFNULL(e1.comm, 0), 
s.grade, 
DATEDIFF(NOW(),e1.hiredate) / 365,
e2.empno AS mgrempno, 
e2.ename AS mgrname, 
d2.dname AS mgrdname
FROM t_emp e1 
LEFT JOIN t_emp e2 
ON e1.mgr = e2.empno
LEFT JOIN t_dept d1 
ON e1.deptno = d1.deptno
LEFT JOIN t_salgrade s
ON e1.sal BETWEEN s.losal AND s.hisal
LEFT JOIN t_dept d2 
ON e2.deptno = d2.deptno;
  1. 內(nèi)連接只保留符合條件的記錄，所以查詢條件寫在ON子句和寫在WHERE子句中的效果是相同的。在外連接里，條件寫在ON子句里，不符合條件的記錄仍會(huì)保留；但條件寫在WHERE子句里，不符合條件的記錄會(huì)被過濾掉。
SELECT e.empno, e.ename, d.dname
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno = d.deptno AND e.deptno = 10;
-- 15條記錄
-- ......
-- 7900 JAMES    NULL
-- 7902 FORD     NULL

SELECT e.empno, e.ename, d.dname
FROM t_emp e LEFT JOIN t_dept d
ON e.deptno = d.deptno WHERE e.deptno = 10;
-- 3條記錄
-- 7782 CLARK   ACCOUNTING
-- 7839 KING    ACCOUNTING
-- 7934 MILLER  ACCOUNTING

4.4 子查詢的語法規(guī)則

  1. 子查詢
    在查詢語句中嵌套查詢語句。
  2. SELECT、FROM、WHERE子句中都可以定義子查詢。但只推薦使用FROM子句的子查詢。
    FROM子句中的子查詢，把查詢結(jié)果當(dāng)做一張表來使用，只執(zhí)行一次，推薦使用。
    WHERE子句和SELECT子句中的子查詢，會(huì)執(zhí)行多次，不推薦使用。
  3. WHERE子查詢
    最簡(jiǎn)單，容易理解。
    每輸出一條記錄時(shí)都要重新執(zhí)行一次子查詢，效率低。
    例如：查詢底薪超過員工平均底薪的員工信息。
SELECT empno, ename, sal 
FROM t_emp 
WHERE sal > (SELECT AVG(sal) FROM t_emp);
  1. FROM子查詢
    這種子查詢只會(huì)執(zhí)行一次，所以執(zhí)行效率很高。
    例如：查詢底薪超過員工平均底薪的員工信息。
SELECT e.empno, e.ename, e.sal 
FROM t_emp e 
JOIN (SELECT AVG(sal) avg FROM t_emp) e1
ON e.sal > e1.avg;
  1. SELECT子查詢
    每輸出一條記錄時(shí)都要重新執(zhí)行一次子查詢，效率低。
    例如：查詢每個(gè)員工的姓名、部門編號(hào)、部門名稱。
SELECT e.empno, e.ename, 
(SELECT dname FROM t_dept WHERE deptno = e.deptno) dname
FROM t_emp e;
  1. 單行子查詢與多行子查詢
    單行子查詢的結(jié)果集只有一條記錄，多行子查詢的結(jié)果集有多條記錄。
    多行子查詢只能出現(xiàn)在WHERE子句和FROM子句中，不能出現(xiàn)在SELECT子句中。
  2. 使用子查詢查找FORD和MARTIN兩個(gè)人的同事
-- 寫法1：WHERE子查詢
SELECT ename 
FROM t_emp
WHERE deptno IN(SELECT deptno FROM t_emp WHERE ename IN ('FORD', 'MARTIN'))
AND ename NOT IN('FORD', 'MARTIN');

-- 寫法2： FORM子查詢
SELECT e1.ename FROM t_emp e1 
JOIN (SELECT deptno FROM t_emp WHERE ename IN ('FORD', 'MARTIN')) e2
ON e1.deptno = e2.deptno
AND e1.ename NOT IN('FORD', 'MARTIN');
  1. WHERE子句中的多行子查詢
    WHERE子句中，可以使用IN、ALL、ANY、EXISTS來處理多行表達(dá)式結(jié)果集的條件判斷。
    (1) 查詢比FORD和MARTIN兩個(gè)人底薪都高的員工
SELECT ename FROM t_emp
WHERE sal > ALL(SELECT sal FROM t_emp WHERE ename IN('FORD', 'MARTIN'))
AND ename NOT IN('FORD', 'MARTIN');

(2) 查詢比FORD和MARTIN任意一個(gè)人底薪高的員工

SELECT ename FROM t_emp
WHERE sal > ANY(SELECT sal FROM t_emp WHERE ename IN('FORD', 'MARTIN'))
AND ename NOT IN('FORD', 'MARTIN');
  1. EXISTS關(guān)鍵字
    EXISTS關(guān)鍵字是把原來在子查詢之外的條件判斷，寫到了子查詢的里面。
    SELECT ... FORM 表名 WHERE [NOT] EXISTS(子查詢);
    例如：查詢工資等級(jí)是3級(jí)或者4級(jí)的員工信息
-- 寫法1：性能很差
SELECT ename, empno, sal FROM t_emp WHERE EXISTS(
    SELECT * FROM t_salgrade 
    WHERE sal BETWEEN losal AND hisal
    AND grade IN(3, 4)
)

-- 寫法2：FORM子查詢，性能好。
SELECT ename, empno 
FROM t_emp e JOIN
(SELECT losal, hisal FROM t_salgrade WHERE grade IN(3, 4)) s
ON e.sal BETWEEN s.losal AND s.hisal;

4.5 總結(jié)

  1. 技能清單
    掌握了匯總函數(shù)和分組統(tǒng)計(jì)
    掌握了表連接語法，內(nèi)連接和外連接
    掌握了子查詢的語法
    掌握了子查詢與表連接的轉(zhuǎn)換
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

  • 什么是數(shù)據(jù)庫(kù)？ 數(shù)據(jù)庫(kù)是存儲(chǔ)數(shù)據(jù)的集合的單獨(dú)的應(yīng)用程序。每個(gè)數(shù)據(jù)庫(kù)具有一個(gè)或多個(gè)不同的API，用于創(chuàng)建，訪問，管理...
    chen_000閱讀 4,148評(píng)論 0贊 19
  • 數(shù)據(jù)庫(kù)優(yōu)化 sql語句優(yōu)化 索引優(yōu)化 加緩存 讀寫分離 分區(qū) 分布式數(shù)據(jù)庫(kù)（垂直切分） 水平切分 MyISAM和I...
    半瓶陽光o_o閱讀 664評(píng)論 0贊 2
  • 1. SQL 簡(jiǎn)介 SQL 的目標(biāo) 理想情況下，數(shù)據(jù)庫(kù)語言應(yīng)允許用戶： 建立數(shù)據(jù)庫(kù)和關(guān)系結(jié)構(gòu) 完成基本數(shù)據(jù)管理任務(wù)...
    板藍(lán)根plank閱讀 2,542評(píng)論 0贊 11
  • 一、列屬性 真正的對(duì)列的約束是依賴于數(shù)據(jù)的類型，但是這種約束比較單一，所以需要更多的約束，整個(gè)時(shí)候就是用到了字段的...
    滿天繁星_28c5閱讀 664評(píng)論 0贊 2
  • 今天白天終于沒有下雨了，可以正常走市場(chǎng)了。早上經(jīng)銷商沒來開會(huì)，昨晚又喝多了。如果喊我一起去，應(yīng)該可以少喝點(diǎn)。天氣挺...
    不知道耶_1fda閱讀 233評(píng)論 2贊 2

友情鏈接更多精彩內(nèi)容