SQL語言基礎(chǔ)

本章，我們將會重點探討SQL語言基礎(chǔ)右核，學(xué)習(xí)用SQL進行數(shù)據(jù)庫的基本數(shù)據(jù)查詢操作。另外請注意本章的SQL語法基于MySQL數(shù)據(jù)庫的SQL語法。

PL/SQL: Procedural Language SQL 過程化語言 SQL

SQL定義

SQL隧膏，Structured Query Language，結(jié)構(gòu)化查詢語言嚷那。SQL 是用于訪問和處理數(shù)據(jù)庫的標(biāo)準(zhǔn)的計算機語言胞枕。

讀音：/??s kju? ??l/ 或者 /?si?kw?l/

SQL語言，和C魏宽、C++腐泻、C#、Java队询、Python派桩、PHP一樣，是一種編程語言蚌斩，每個月都可以看到Tiobe編程排行榜上SQL上榜铆惑。同時SQL又是一種標(biāo)準(zhǔn)，每個數(shù)據(jù)庫的廠商都提供了對標(biāo)準(zhǔn)SQL的支持送膳。此外每個廠商也基本上擴展了標(biāo)準(zhǔn)SQL的使用员魏。

SQL編程是指用SQL語言來完成對數(shù)據(jù)庫的邏輯性操作。這個操作可以比較簡單叠聋，只有一段SQL來完成最基本的數(shù)據(jù)庫操作撕阎；也可以比較復(fù)雜，需要多段SQL在一起建立起存儲過程來完成復(fù)雜的數(shù)據(jù)庫的操作碌补。

注釋

SQL的注釋有兩種形式：單行注釋和多行注釋虏束。

單行注釋：

單行以--開頭，該行之后的內(nèi)容以及SQL代碼將會被注釋掉厦章。

MySQL 中魄眉，--后面需要加一個空格才能代表是注釋，對于其他數(shù)據(jù)庫闷袒，不需要空格也可以坑律。

例如

-- 該行是注釋，無論是文本還是SQL代碼都會被注釋掉
-- SELECT * FROM Country;

多行注釋：

多行注釋是以/*開頭囊骤，以*/結(jié)尾的晃择，中間的部分將會被全部注釋掉。

例如

/*
該行是注釋
該行還是注釋
SELECT * FROM Country;
*/

SQL語言分類

SQL語言大體上可以分為六大類別：

一：數(shù)據(jù)查詢語言（DQL ：Data Query Language）：Select
二：數(shù)據(jù)操作語言（DML ：Data Manipulation Language）：Insert也物、Update宫屠、Delete
三：事務(wù)處理語言（TPL）：BEGIN TRANSACTION、Commit滑蚯、Rollback
四：數(shù)據(jù)控制語言（DCL）：Grant浪蹂、Revoke
五：數(shù)據(jù)定義語言（DDL）：Create抵栈、Drop、Alter（table,index etc.）
六：指針控制語言（CCL）：DECLARE CURSOR坤次，F(xiàn)ETCH INTO

DQL語言

DQL古劲，Data Query Language，數(shù)據(jù)查詢語言缰猴。

SELECT 查詢

基本語法結(jié)構(gòu)

SELECT <字段名1, 字段名2...>
FROM <表名1, 表名2...>
WHERE <條件>;

DDL語言

DDL产艾，Data Definition Language，數(shù)據(jù)定義語言滑绒。

注意：DDL中闷堡，必須加入被定義的對象

• DDL 數(shù)據(jù)定義子語言 DBMS：數(shù)據(jù)定義

1. CREATE [object] 創(chuàng)建
2. ALTER [object] 修改
3. DROP [object] 刪除

[object]:

1. DATABASE data：數(shù)據(jù) base 基地 數(shù)據(jù)庫
2. TABLE* table：桌子， 表格
3. VIEW view： 視圖疑故，用眼睛看數(shù)據(jù)
4. INDEX index： 索引杠览，目錄（優(yōu)化查詢）

CREATE創(chuàng)建

創(chuàng)建數(shù)據(jù)庫的對象。

CREATE DATABASE
CREATE TABLESPACE （Oracle 特有的結(jié)構(gòu)纵势，類似于貨架）
CREATE TABLE
CREATE VIEW
CREATE PROCEDURE （存儲過程）
CREATE USER

ALTER修改

修改數(shù)據(jù)庫的對象

ALTER TABLE
ALTER USER

DROP刪除

刪除數(shù)據(jù)庫的對象踱阿，對象中的內(nèi)容也一定一并刪除。

• 刪除數(shù)據(jù)庫：數(shù)據(jù)庫中所有的東西（表吨悍、數(shù)據(jù)……）全部都沒有了

DROP TABLE
DROP VIEW
DROP PROCEDURE
DROP USER

RENAME重命名

重命名數(shù)據(jù)庫的對象

RENAME TABLE
RENAME COLUMN

DML語言

DML，Data Manipulation Language蹋嵌，數(shù)據(jù)操縱子語言育瓜。

DML的對象一般指的是表。DML不會對數(shù)據(jù)庫對象（比如：表）的結(jié)構(gòu)進行進行任何更改栽烂，只會對數(shù)據(jù)庫對象（比如：表）的數(shù)據(jù)記錄進行增加躏仇、更新、刪除等操作腺办。此外焰手，DML的操作，需要事務(wù)提交才能真正完成怀喉。

• DML 數(shù)據(jù)操縱子語言 DBMS：數(shù)據(jù)操縱

1. SELECT 查詢 【查】(有些時候书妻，會出來一個 DQL，單獨只查詢)
2. INSERT 增加躬拢，插入 【增】
3. UPDATE 更新【改】
4. DELETE 移除【刪】

• 對象：記錄躲履，數(shù)據(jù)

INSERT插入

在表中插入記錄。增加記錄聊闯。

UPDATE更新

更新表中的記錄工猜，可以更新精確到字段。

DELETE刪除

刪除表中的記錄菱蔬，對表結(jié)構(gòu)沒有任何影響篷帅。

DCL/TPL語言

DCL史侣，Data Control Language，數(shù)據(jù)控制子語言魏身。
TPL, Transaction Process Language,事務(wù)處理語言

DCL應(yīng)用的場景惊橱，一般是授權(quán)、回收等權(quán)限操作
TPL一般是事務(wù)處理叠骑，包括事務(wù)的提交李皇、回滾。

GRANT

授權(quán)宙枷，給用戶授權(quán)掉房。授權(quán)后的用戶才可以操作數(shù)據(jù)庫。

REVOKE

取消授權(quán)

COMMIT

提交慰丛，事務(wù)提交

ROLLBACK

回滾卓囚，事務(wù)回滾

MySQL數(shù)據(jù)類型

MySQL的數(shù)據(jù)類型見如下表格

• 數(shù)值型

• 字符串型

• 日期時間型

• 混合型

表的基本操作

表的基本操作主要包括對表的增刪查改腰湾，以及創(chuàng)建雷恃、修改和移除表結(jié)構(gòu)。

命令行操作MySQL

• 登錄數(shù)據(jù)庫：mysql-u root-p
• 列出所有的數(shù)據(jù)庫: show databases;
• 選擇某個數(shù)據(jù)庫(test)：use test;
• 列出選定數(shù)據(jù)庫的表：show tables;
• 描述(desc, describe) 某個表結(jié)構(gòu): desc salesorder;

Setting environment for using XAMPP for Windows.
we@TEACHER-3 d:\xampp
# cd mysql\bin

we@TEACHER-3 D:\xampp\mysql\bin
# mysql-u root-p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.5.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| cdcol              |
| mysql              |
| performance_schema |
| phpmyadmin         |
| test               |
| webauth            |
+--------------------+
7 rows in set (0.00 sec)

mysql> use test;
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| salesorder     |
| user           |
+----------------+
2 rows in set (0.00 sec)

mysql> desc salesorder;
+---------+---------------+------+-----+---------+-------+
| Field   | Type          | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+-------+
| id      | char(10)      | YES  |     | NULL    |       |
| tx_date | datetime      | YES  |     | NULL    |       |
| amount  | decimal(9,2)  | YES  |     | NULL    |       |
| remark  | varchar(1000) | YES  |     | NULL    |       |
+---------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

SQLyog 創(chuàng)建表

1. 打開SQLyog檐盟，并連接數(shù)據(jù)庫

2. 選擇 test數(shù)據(jù)庫：用鼠標(biāo)左鍵點擊 左側(cè)列表的 test

3. 右鍵 test | 表褂萧，選擇 創(chuàng)建表

4. 填寫具體的輸入

   Snap17.jpg

• 表名稱(Table Name)：英文（或者英文+數(shù)字），不需要空格
  • 數(shù)據(jù)庫(Database)：查看是不是 test
  • 引擎(Engine)：選擇 InnoDB
  • 字符集(character set)：utf8
  • 核對(collection)：utf8_general_ci

表約束

約束葵萎，constraint导犹，用于實施數(shù)據(jù)庫中的某些數(shù)據(jù)完整性。當(dāng)給某一列增加一個約束羡忘，MySQL自動確保不滿足此約束的數(shù)據(jù)是絕對不能被接受的谎痢。如果用戶試圖寫一個不滿足約束的數(shù)據(jù)記錄，那么MySQL就會對這個非法的SQL語句產(chǎn)生一個錯誤卷雕。

約束是表級的強制規(guī)定

約束放置在表中刪除有關(guān)聯(lián)關(guān)系的數(shù)據(jù)

約束可以再創(chuàng)建或者增加包含某列的表時节猿，與該列進行關(guān)聯(lián)，也可以在表創(chuàng)建以后通過SQL命令A(yù)LTER TABLE來實現(xiàn)與該列的關(guān)聯(lián)漫雕。

主要的幾種約束類型:

• NOT NULL(非空)

  設(shè)定 NOT NULL 非空的列滨嘱，必須有值，盡管值可以重復(fù)浸间。

  場景：【名字】太雨，姓名、性別魁蒜、出生日期囊扳、課程名、客戶名稱兜看、商品名稱

任何列都可以設(shè)置為NOT NULL锥咸。如果在SQL操作中將一個NULL值賦給某個有NOT NULL約束的列，那么MySQL會為這個語句返回一個錯誤细移。

• **唯一鍵 **(UNIQUE KEY)

  設(shè)定 UNIQUE搏予， 唯一。該列不可以重復(fù)弧轧，可以不填（NULL）雪侥，如果填了就不能重復(fù)

  場景：【可有可無的唯一標(biāo)識】球涛，手機號、QQ號校镐，Email亿扁，銀行賬戶賬號，支付寶鸟廓。从祝。。

如果將某個列設(shè)置為唯一引谜，那么就不能在表中插入和這個列中已有值重復(fù)的行牍陌，也不能修改已有的列值使之與其他列值重復(fù)。

1. 字段數(shù)據(jù)允許為空
2. 如果有數(shù)據(jù)那么必須保證每行都不相同员咽，否則無法存儲
3. 系統(tǒng)也會為唯一鍵默認創(chuàng)建一個索引（高級毒涧，了解就行）
4. 可以是一個或多個字段組成
5. 一個表的唯一（唯一鍵）可以有多個
   1. 比如：第5列（手機號）本身唯一，第2列（股票代碼）+第3列（交易時間）也唯一
   2. 如上：就有兩個唯一鍵

• 主鍵(Primary Key, 簡稱PK)

  PK = NOT NULL + UNIQUE

  一個表的主鍵贝室，只能有一個契讲。

  這個主鍵可能是1列，也可能是多列一起組成滑频。

  • 比如 身份證號 做主鍵
  • 比如 銀行卡號 + 社奔衿卡號 做主鍵

每個表最多可以有一個主鍵約束。主鍵約束可以由表中的多個列組成峡迷。

主鍵：相當(dāng)于身份證號碼银伟，是唯一的，通過身份證號碼只能找到一個人绘搞，即通過主鍵列只能找到一行數(shù)據(jù)彤避，在創(chuàng)建表時，通常要有主鍵列

主鍵屬于表對象夯辖，所以主鍵有一個名字琉预，若沒給主鍵指定名字，MySQL會自動分配一個唯一的名字楼雹，在一個表中只能有一個主鍵對象

1.主鍵一定是唯一的行標(biāo)識模孩，即每行的主鍵都不會重復(fù)
2.主鍵是不允許為空
3.系統(tǒng)會為主鍵默認的創(chuàng)建一個索引
4.主鍵可以是一個或多個字段
5.通常情況下尖阔，關(guān)系型數(shù)據(jù)庫中每張表都會有主鍵

外鍵（Foreign Key,簡稱FK）

外鍵是約束贮缅，約束了該列的內(nèi)容

外鍵對應(yīng)了另外表的主鍵

外鍵的值，可以為空介却，如果不為空谴供，就必須是對應(yīng)主鍵所在表的主鍵列表的值

• 股票基本信息表

• 股票購買表

• 股票代碼1在股票基本信息表是主鍵
• 股票代碼2在股票購買表是外鍵
• 在 股票購買表 中，股票代碼2的值齿坷，必須是 股票基本信息表 中 股票代碼1的值绒尊。

外鍵約束是為數(shù)據(jù)庫中某個與其他表(稱作父表)有關(guān)系的表(稱作子表)而定義的译暂。外鍵的值必須事先出現(xiàn)在某個特定表的唯一鍵或者主鍵中表制。外鍵可包含一列或者多列，但是其所參考的鍵也必須包含相同的列佩耳。外鍵也可以和同一個表的主鍵相關(guān)聯(lián)。如果沒有其他約束限制谭跨，外鍵可以包含NULL值干厚。

1. 一張表的外鍵關(guān)聯(lián)字段通常情況下關(guān)聯(lián)的是另外一張表的主鍵
2. 一張表的外鍵關(guān)聯(lián)字段必須填寫外鍵表中存在的數(shù)據(jù)
3. 外間關(guān)聯(lián)表的數(shù)據(jù)被引用了之后，通常不允許刪除螃宙，如果一定要刪除蛮瞄，可以級聯(lián)刪除引用數(shù)據(jù)
4. 外鍵字段允許為空
5. 外鍵字段可以是一個或多個

創(chuàng)建表

表(TABLE)是關(guān)系型數(shù)據(jù)庫的核心，所有的數(shù)據(jù)都存儲在表中谆扎。

創(chuàng)建表的前提

登錄數(shù)據(jù)庫的用戶必須擁有

• CREATE TABLE權(quán)限
• 可用的存儲空間

創(chuàng)建表的基本語法

CREATE TABLE [schema.]table
        (column datatype [DEFAULT expr][, ...]);

在創(chuàng)建表的時候挂捅，必須制定：

• 表名
• 字段名（又叫做列名）
• column：欄位
• field：字段，列
• point：點堂湖，數(shù)據(jù)點
• 字段類型
• 字段長度等

表名和字段名（列名）

表名和列名:

• 必須以字母開頭（可以為中文闲先，但是不推薦用）
• 必須在 1–30 個字符之間
• 必須只能包含 A–Z, a–z, 0–9, _, $, 和 #
• 必須不能和用戶定義的其他對象重名
• 必須不能是 MySQL 的保留字

DEFAULT選項

插入時為一個列指定默認值

... hire_date DATE DEFAULT '2017-03-08', ... 

具體要求：

• 字符串, 表達式, 或SQL 函數(shù)都是合法的
• 其它列的列名和偽列是非法的
• 默認值必須滿足列的數(shù)據(jù)類型定義

表的確認與查看

SQL> DESC <表名>

或者

SQL> DESCRIBE <表名>

示例：

MariaDB [mysql]> desc db;
+-----------------------+---------------+------ +-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------ +-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(80)      | NO   | PRI |         |       |
| Select_priv           | enum('N','Y') | NO   |     | N       |       |
| Insert_priv           | enum('N','Y') | NO   |     | N       |       |
| Update_priv           | enum('N','Y') | NO   |     | N       |       |
| Delete_priv           | enum('N','Y') | NO   |     | N       |       |
| Create_priv           | enum('N','Y') | NO   |     | N       |       |
| Drop_priv             | enum('N','Y') | NO   |     | N       |       |
| Grant_priv            | enum('N','Y') | NO   |     | N       |       |
| References_priv       | enum('N','Y') | NO   |     | N       |       |
| Index_priv            | enum('N','Y') | NO   |     | N       |       |
| Alter_priv            | enum('N','Y') | NO   |     | N       |       |
| Create_tmp_table_priv | enum('N','Y') | NO   |     | N       |       |
| Lock_tables_priv      | enum('N','Y') | NO   |     | N       |       |
| Create_view_priv      | enum('N','Y') | NO   |     | N       |       |
| Show_view_priv        | enum('N','Y') | NO   |     | N       |       |
| Create_routine_priv   | enum('N','Y') | NO   |     | N       |       |
| Alter_routine_priv    | enum('N','Y') | NO   |     | N       |       |
| Execute_priv          | enum('N','Y') | NO   |     | N       |       |
| Event_priv            | enum('N','Y') | NO   |     | N       |       |
| Trigger_priv          | enum('N','Y') | NO   |     | N       |       |
+-----------------------+---------------+------ +-----+---------+-------+
22 rows in set (0.01 sec)

MariaDB [mysql]>

創(chuàng)建表腳本

-- 推薦的寫法
CREATE TABLE 名字(
  字段名 字段類型 是否為空 PRIMARY KEY,
  字段名 字段類型 是否為空 DEFAULT 默認值,
  字段名 字段類型 是否為空 UNIQUE,
  ....
  字段名1 字段類型  /* 外鍵 */,
  FOREIGN KEY(字段名1) REFERENCES 另一個表表名 (另一個表的主鍵字段名)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

如果想看最標(biāo)準(zhǔn)（MySQL 官方建議寫法），可以使用 SQLyog 導(dǎo)出表的腳本无蜂。

創(chuàng)建表示例

CREATE TABLE person 
(
  id CHAR(18),
  name VARCHAR(100),
  sex CHAR(1),
  birthday DATETIME,
  height INT,
  weight INT,
  age INT,
  hometown CHAR(6)
);
-- Table created.

上面的SQL代碼執(zhí)行后便新建了一個數(shù)據(jù)庫的表饵蒂，表的名字是person，一共有七個字段酱讶。

接下來我們可以使用之前的DESC來查看所創(chuàng)建的表的結(jié)構(gòu)退盯。請注意務(wù)必在PLSQL Developer的命令窗口執(zhí)行，或者在操作系統(tǒng)的命令行窗口執(zhí)行泻肯。

SQL> desc person
Name     Type          Nullable Default Comments 
--------------------------------------------
ID       CHAR(18)      Y                         
NAME     VARCHAR(100) Y                         
SEX      CHAR(1)       Y                         
BIRTHDAY DATETIME          Y                         
HEIGHT   INT        Y               （）          
WEIGHT   INT        Y                         
AGE      INT        Y                         
hometown    CHAR(6)       Y  

添加表的約束

還記的上一章我們曾經(jīng)學(xué)習(xí)過的表的約束么渊迁？接下來我們創(chuàng)建表的時候，來添加表的約束灶挟，實現(xiàn)數(shù)據(jù)的完整性琉朽。

約束：就是限制

首先創(chuàng)建一個表：hometown

CREATE TABLE hometown
(
  id CHAR(6) PRIMARY KEY, 
  city VARCHAR2(100) DEFAULT 'shenzhen' NOT NULL, 
  province VARCHAR2(100) DEFAULT 'guangdong' 
);
-- Table created.

可以看到上述表hometown使用了主鍵約束和非空約束若河。我們在接下來的修改表結(jié)構(gòu)小節(jié)里面來繼續(xù)討論約束。

修改表結(jié)構(gòu)

剛剛我們接連創(chuàng)建了兩個表寞宫，是person和hometown后者在創(chuàng)建的時候我們考慮了約束并且添加了萧福，但是前者在創(chuàng)建的是時候，我們沒有增加約束辈赋，接下來我們通過修改表結(jié)構(gòu)统锤，使前者也添加約束。

修改表person的字段炭庙，使的表滿足以下約束條件饲窿。

修改字段

修改表person的字段腻脏，使的表滿足以下約束條件鸦泳。

修改字段的語法

-- 修改字段
ALTER TABLE table
MODIFY     (column datatype [DEFAULT expr]
           [, column datatype]...);

-- 添加約束
ALTER TABLE  table
  ADD [CONSTRAINT constraint] type (column);

具體代碼如下：

-- id添加主鍵約束
ALTER TABLE person
ADD constraint person_id_pk primary key (ID);

-- 姓名不允許為空
ALTER TABLE person
ADD constraint person_name_nn check (name IS NOT NULL);
-- 或者這樣
alter table PERSON modify name not null;

-- 性別檢查約束
ALTER TABLE person
ADD constraint person_sex_chk check (sex = 'M' OR sex = 'F');

-- 性別默認值
ALTER TABLE person
MODIFY sex CHAR(1) DEFAULT 'F';

-- 性別字段名由sex修改為gender
ALTER TABLE person RENAME COLUMN sex TO gender;

-- 生日不允許為空
ALTER TABLE person
 ADD constraint person_birthday_nn check (birthday IS NOT NULL);

-- 生日默認值
ALTER TABLE person
MODIFY birthday DATE DEFAULT NOW();

-- 修改身高和體重的格式，使其滿足999.9
ALTER TABLE person
MODIFY(
  height DECIMAL(3,1),
  weight DECIMAL(3,1)
);

接下來是外鍵約束

-- 添加外鍵永品，使得person的hometown字段參考hometown這個表的id字段
ALTER TABLE person
  ADD CONSTRAINT person_hometown_fk FOREIGN KEY (hometown)
  REFERENCES hometown (id);

刪除字段

ALTER TABLE table
DROP (column);

具體代碼

-- 刪除年齡字段
ALTER TABLE person
DROP (age);

-- 或者
ALTER TABLE person
DROP COLUMN age;

添加字段

ALTER TABLE table
ADD        (column datatype [DEFAULT expr]
           [, column datatype]...);

具體代碼

-- 添加字段做鹰，手機。并設(shè)置為唯一約束
ALTER TABLE person
ADD (phone CHAR(11) UNIQUE);

至此鼎姐，整個person的表就已經(jīng)按照要求完全實現(xiàn)了钾麸。

整個表的一次性創(chuàng)建腳本如下

-- 完整的一次性創(chuàng)建表的腳本
CREATE TABLE person2
(
  ID CHAR(18) PRIMARY KEY,
  NAME VARCHAR(100) NOT NULL,
  SEX CHAR(1)  DEFAULT 'F' CHECK(sex='M' OR sex='F'),
  birthday DATETIME DEFAULT NOW() NOT NULL,
  height DECIMAL(4,1) ,
  weight DECIMAL(4,1),
  hometown CHAR(6) REFERENCES hometown(ID),
  phone CHAR(11) UNIQUE
);

插入記錄

-- 插入記錄到 Hometown
INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('518000', 'Shenzhen', 'Guangdong');

INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('100000', 'Beijing', 'Beijing');

INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('517000', 'Guangzhou', 'Guangdong');

INSERT INTO HOMETOWN
  (ID, CITY, PROVINCE)
VALUES
  ('516000', 'Shanghai', 'Shanghai');

-- 插入表 Person 記錄
INSERT INTO PERSON
  (ID, NAME, SEX, BIRTHDAY, HEIGHT, WEIGHT, HOMETOWN, PHONE)
VALUES
  ('123456789012345678',
   'Juliet',
   'F',
   TO_DATE('19900712', 'yyyymmdd'),
   170,
   62,
   '518000',
   '13866666666');

-- 插入表 Person 記錄
INSERT INTO PERSON
  (ID, NAME, SEX, BIRTHDAY, HEIGHT, WEIGHT, HOMETOWN, PHONE)
VALUES
  ('123456789012345679',
   'Remeo',
   'M',
   TO_DATE('19920316', 'yyyymmdd'),
   162,
   45,
   '100000',
   '13866666669');

由上述腳本可以看出，由于Person.hometown字段是外鍵炕桨，參考了Hometown.id字段饭尝，那么在插入Person記錄的時候，必須插入在Hometown.id中已經(jīng)存在的記錄献宫。

另外钥平，務(wù)必先插入Hometown記錄，再插入Person記錄姊途。

移除表結(jié)構(gòu)

DROP TABLE table;

具體代碼

-- 刪除表person
DROP TABLE person;
-- Table dropped.

表的查詢

表的查詢是整個數(shù)據(jù)庫的基礎(chǔ)涉瘾，也是我們學(xué)習(xí)的重點

講義中使用 HRDB的六個表

初始化數(shù)據(jù)庫

• 創(chuàng)建數(shù)據(jù)庫

  CREATE DATABASE HRDB;
  USE HRDB;
  

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

• 區(qū)域表 Regions

  字段名	字段類型	注釋說明
  region_id	smallint(5)	無符號、主鍵捷兰、自增長
  region_name	varchar(25)	可以為空

  DROP TABLE IF EXISTS regions;
  
  CREATE TABLE regions (
    region_id smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    region_name varchar(25) DEFAULT NULL,
    PRIMARY KEY (region_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

• 國家表 Countries

  字段名	字段類型	注釋說明
  country_id	char(2)	主鍵立叛、非空
  country_name	varchar(40)
  region_id	smallint(5)	無符號、外鍵：參考 Regions(region_id)

  DROP TABLE IF EXISTS countries;
  
  CREATE TABLE countries (
    country_id char(2) NOT NULL DEFAULT '',
    country_name varchar(40) DEFAULT NULL,
    region_id smallint(5) unsigned DEFAULT NULL,
    PRIMARY KEY (country_id),
    FOREIGN KEY (region_id) REFERENCES regions (region_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

• 位置表 Locations

  字段名	字段類型	注釋說明
  location_id	smallint(5)	主鍵
  street_address	varchar(40)	街道
  postal_code	varchar(12)	郵編
  city	varchar(30)	城市
  state_province	varchar(25)	省份
  country_id	char(2)	外鍵寂殉，關(guān)聯(lián) Countries(country_id)

  DROP TABLE IF EXISTS locations;
  
  CREATE TABLE locations (
    location_id smallint(5) unsigned NOT NULL,
    street_address varchar(40) DEFAULT NULL,
    postal_code varchar(12) DEFAULT NULL,
    city varchar(30) NOT NULL,
    state_province varchar(25) DEFAULT NULL,
    country_id char(2) DEFAULT NULL,
    PRIMARY KEY (location_id),
    FOREIGN KEY (country_id) REFERENCES countries (country_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

• 職位表 Jobs

  字段名	字段類型	注釋說明
  job_id	varchar(10)	主鍵
  job_title	varchar(35)	職位名稱
  min_salary	int(11)	職位最低工資
  max_salary	int(11)	職位最高工資

  DROP TABLE IF EXISTS jobs;
  
  CREATE TABLE jobs (
    job_id varchar(10) NOT NULL,
    job_title varchar(35) NOT NULL,
    min_salary int(11) DEFAULT NULL,
    max_salary int(11) DEFAULT NULL,
    PRIMARY KEY (job_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

  ?

• 員工表 Employees

  字段名	字段類型	注釋說明
  employee_id	mediumint(8)	主鍵
  first_name	varchar(20)	名字
  last_name	varchar(25)	姓
  email	varchar(25)
  phone_number	varchar(20)
  hire_date	date	入職日期
  salary	float(8,2)	工資
  commission_pct	float(2,2)	銷售提成比
  job_id	varchar(10)	外鍵 jobs (job_id)
  manager_id	mediumint(8)	外鍵 employees (employee_id)
  department_id	smallint(5)	外鍵 departments (department_id)

  DROP TABLE IF EXISTS employees;
  
  CREATE TABLE employees (
    employee_id mediumint(8) unsigned NOT NULL,
    first_name varchar(20) DEFAULT NULL,
    last_name varchar(25) DEFAULT NULL,
    email varchar(25) DEFAULT NULL,
    phone_number varchar(20) DEFAULT NULL,
    hire_date date DEFAULT NULL,
    job_id varchar(10) DEFAULT NULL,
    salary float(8,2) DEFAULT '10000.00',
    commission_pct float(2,2) DEFAULT NULL,
    manager_id mediumint(8) unsigned DEFAULT NULL,
    department_id smallint(5) unsigned DEFAULT NULL,
    PRIMARY KEY (employee_id),
    UNIQUE KEY email (email),
    FOREIGN KEY (manager_id) REFERENCES employees (employee_id),
    FOREIGN KEY (department_id) REFERENCES departments (department_id),
    FOREIGN KEY (job_id) REFERENCES jobs (job_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

  ?

• 部門表 Departments

  字段名	字段類型	注釋說明
  department_id	smallint(5)	主鍵
  department_name	varchar(30)	部門名稱囚巴，非空
  manager_id	mediumint(8)	外鍵原在，參考 employees (employee_id)
  location_id	smallint(5)	外鍵友扰，參考 locations (location_id)

  DROP TABLE IF EXISTS departments;
  
  CREATE TABLE departments (
    department_id smallint(5) unsigned NOT NULL,
    department_name varchar(30) NOT NULL,
    manager_id mediumint(8) unsigned DEFAULT NULL,
    location_id smallint(5) unsigned DEFAULT NULL,
    PRIMARY KEY (department_id),
    FOREIGN KEY (manager_id) REFERENCES employees (employee_id),
    FOREIGN KEY (location_id) REFERENCES locations (location_id)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  

  ?

  ?

基本查詢

查詢的語法

SELECT  *|{[DISTINCT] column|expression [alias],...}
FROM    table;

SQL的查詢有以下特點：

• SQL 語言大小寫不敏感彤叉。
• SQL 可以寫在一行或者多行
• 關(guān)鍵字不能被縮寫也不能分行
• 各子句一般要分行寫。
• 使用縮進提高語句的可讀性村怪。

-- 簡單基礎(chǔ)查詢
SELECT * FROM HRDB.EMPLOYEES;
SELECT * FROM HRDB.DEPARTMENTS D; /*表的別名 D*/
SELECT E.EMPNO, E.ENAME, E.JOB, E.SAL FROM SCOTT.EMP E;-- 表的別名E和查詢指定字段
SELECT
    SG.GRADE AS jibie,-- 字段的別名
    SG.LOSAL AS "Low Salary",-- 字段的別名
    SG.HISAL "High Salary"-- 字段的別名
FROM
    SCOTT.SALGRADE SG; 
SELECT ROWNUM,t.*,ROWID FROM SCOTT.EMP t;

• *代表所有列
• HRDB是數(shù)據(jù)庫名
• BONUS秽浇、DEPARTMENTS、EMPLOYEES甚负、SALGRADE是表名柬焕，代表查詢哪個表
• D、E梭域、SG是表的別名
• 對每個字段都可以進行重命名斑举，可以使用AS，也可以不用AS病涨。
• PS：請注意富玷，字段（列）的重命名，是在查詢結(jié)果產(chǎn)生以后進行的既穆。
• SELECT后面跟的是字段（列）
• FROM表名關(guān)注的是表赎懦，后面只能是表名或者是查詢出來的子表
• ROWNUM ROWID:偽列，系統(tǒng)自動生成的列幻工，只能查詢励两，不能進行增刪改操作，ROWNUM表示行號囊颅、ROWID表示當(dāng)前記錄數(shù)據(jù)物理存放位置

條件查詢

SELECT  *|{[DISTINCT] column|expression [alias],...}
FROM    table
[WHERE  condition(s)];

• 條件查詢是用WHERE語句進行過濾的当悔，在執(zhí)行完過濾條件后，查詢的結(jié)果才會展現(xiàn)出來踢代。
• WHERE條件關(guān)注的是字段（列）
• 使用WHERE子句先鱼，將不符合條件的記錄過濾掉。

具體的例子

基本條件查詢

-- 基本條件查詢
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HR.EMPLOYEES E
 WHERE E.EMPLOYEE_ID = 200
-- 查詢員工編號為200的員工的 員工編號奸鬓、姓名焙畔、入職日期和工作職位

-- 基本條件查詢
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID = 200
    OR E.EMPLOYEE_ID = 201;
-- 查詢員工編號為200或者201的員工
-- 還可以用：E.EMPLOYEE_ID >= 201   "<>  <= >= > <"

-- 基本條件查詢
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID IN (200, 201, 202, 205, 208);
-- 查詢員工編號為(200, 201, 202, 205, 208)范圍內(nèi)的員工

-- 基本條件查詢
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID BETWEEN 200 AND 208;
-- 查詢員工編號為200到208范圍內(nèi)的員工

-- 基本條件查詢
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID > 200
   AND E.EMPLOYEE_ID <= 209;
-- 查詢員工編號為大于200并且小于等于209范圍內(nèi)的員工

-- 基本條件查詢
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.HIRE_DATE, E.JOB_ID, E.Salary
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME = 'Jennifer'
-- 查詢員工名字為“Jennifer”的員工

NULL空值查詢

NULL，讀音/n?l/串远，表示未知的宏多，空值。

NULL是數(shù)據(jù)庫中特有的數(shù)據(jù)類型澡罚，當(dāng)一條記錄的某個列為NULL伸但，則表示這個列的值是未知的、是不確定的留搔。既然是未知的更胖，就有無數(shù)種的可能性。因此，NULL并不是一個確定的值却妨。

這是NULL的由來饵逐、也是NULL的基礎(chǔ)，所有和NULL相關(guān)的操作的結(jié)果都可以從NULL的概念推導(dǎo)出來彪标。

判斷一個字段是否為NULL倍权，應(yīng)該用IS NULL或IS NOT NULL，而不能用=捞烟，對NULL的任何操作的結(jié)果還是NULL薄声。

查詢示例

-- 空值查詢
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, E.MANAGER_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.MANAGER_ID IS NULL;
-- 查詢所有的沒有確定經(jīng)理的員工；

-- 空值查詢
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.LAST_NAME, E.JOB_ID, E.MANAGER_ID
  FROM HRDB.EMPLOYEES E
 WHERE E.MANAGER_ID IS NOT NULL;
-- 查詢所有的有確定經(jīng)理的員工题画；

模糊條件查詢

模糊條件查詢一般是指字符類型模糊查詢默辨，使用LIKE

• LIKE 后面一定用單引號 ''

• 查詢用到了通配符，選擇相似值
• %代表任意字符（零個或者一個或者多個字符）
• _代表一個字符
• 查詢字符或者數(shù)字
• %和_可以同時使用

-- 模糊條件查詢
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME Like 'J%'
-- 查詢員工名字以“J”開頭的員工

-- 模糊條件查詢
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME Like '%on%'
-- 查詢員工名字包含“on”的員工

-- 模糊條件查詢
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.FIRST_NAME Like '_a%'
-- 查詢員工名字以“任意一個字符+a”開頭的員工

-- 模糊條件查詢
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID LIKE '_2%'
-- 查詢員工編號以“任意一個字符+2”開頭的員工

-- 模糊條件查詢
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.EMPLOYEE_ID LIKE '__2'
-- 查詢員工編號為“XX2”的員工

函數(shù)條件查詢

字符大小寫轉(zhuǎn)換函數(shù)

-- 函數(shù)查詢
SELECT UPPER(E.FIRST_NAME) "Upper Name",
       LOWER(E.FIRST_NAME) "Lower Name",
       CONCAT(E.FIRST_NAME, '.', E.LAST_NAME) "Full Name"
  FROM HRDB.EMPLOYEES E;
-- 查詢所有員工的名字和姓名苍息，并直接相連廓奕。

查詢示例

-- 函數(shù)條件查詢
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE UPPER(E.First_Name) = 'PETER'; 
-- 查詢員工名字大寫為“PETER”的員工（peter, Peter, pEter...）

-- 函數(shù)條件查詢
SELECT E.EMPLOYEE_ID,
       E.FIRST_NAME,
       E.LAST_NAME,
       E.HIRE_DATE,
       E.JOB_ID,
       E.SALARY
  FROM HRDB.EMPLOYEES E
 WHERE LOWER(E.FIRST_NAME) = 'peter';
-- 查詢員工名字大寫為“PETER”的員工（peter, Peter, pEter...）

字符控制函數(shù)

查詢示例

-- 函數(shù)查詢
SELECT CONCAT(E.FIRST_NAME, E.LAST_NAME) "Full Name",
       E.JOB_ID,
       TRIM(E.first_name), 
       LENGTH(E.LAST_NAME),
       LPAD(E.SALARY, 11, '*'),
       RPAD(E.SALARY, 12, '$')
  FROM HRDB.EMPLOYEES E
 WHERE SUBSTRING(JOB_ID, 4) = 'REP';

數(shù)字處理函數(shù)

函數(shù)示例

SELECT ROUND(45.923, 2), ROUND(45.923, 0), ROUND(45.923,-1) , ROUND(45.923,-2)

SELECT  TRUNCATE(45.923,2), TRUNCATE(45.923,0), TRUNCATE(45.923,-1), TRUNCATE(45.923,-2)

SELECT LAST_NAME, SALARY, MOD(SALARY, 5000)
  FROM HRDB.EMPLOYEES WHERE JOB_ID = 'SA_REP';
-- 查詢職位ID是“SA_REP”的員工的工資對5000取余

日期處理函數(shù)

查詢優(yōu)先級

可以用括號()來改變查詢的優(yōu)先級

SELECT LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE JOB_ID = 'SA_REP'
    OR JOB_ID = 'AD_PRES'
   AND SALARY > 15000;
-- 查詢所有工作ID是“SA_REP”或者工作ID是“AD_PRES”并且工資高于15000

SELECT LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE (JOB_ID = 'SA_REP' OR JOB_ID = 'AD_PRES')
   AND SALARY > 15000;
-- 查詢所有工作ID是“SA_REP”或“AD_PRES”并且工資高于15000

排序

使用ORDER BY字句進行排序操作桌粉。

• ASC是升序， ascend衙四，如果省略ASC铃肯，一般數(shù)據(jù)庫也是默認升序。
• DESC是降序, descend
• ORDER BY字句用在SELECT語句的結(jié)尾

排序語法

SELECT     *|{[DISTINCT] column|expression [alias],...}
FROM       table
[WHERE     condition(s)]
[ORDER BY  {column, expr, alias} [ASC|DESC]];

默認排序

一般的關(guān)系型數(shù)據(jù)庫中传蹈，默認排序是升序排序押逼。就是在排序的ORDER BY子句中不添加ASC或者DESC。

查詢示例

-- 排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.DEPARTMENT_ID, E.HIRE_DATE
  FROM HRDB.EMPLOYEES E
 ORDER BY E.HIRE_DATE;
-- 默認排序惦界，查詢所有的員工挑格，并按照入職時間從小到大升序排序
-- ORDER BY E.HIRE_DATE 等同于 ORDER BY E.HIRE_DATE ASC

-- 排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.DEPARTMENT_ID, E.HIRE_DATE
  FROM HRDB.EMPLOYEES E
 ORDER BY E.HIRE_DATE DESC;
-- 默認排序，查詢所有的員工沾歪，并按照入職時間從大到小降序排序

字段別名排序

字段的別名排序漂彤，是將字段重新命名以后，按照新的名稱進行排序灾搏。

重點理解：排序的動作挫望，是在查詢表已經(jīng)呈現(xiàn)以后，并且若有字段重命名狂窑，那么排序也是在重命名之后媳板。

-- 按照別名排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY * 12 ANNUAL_SALARY
  FROM HRDB.EMPLOYEES E
 WHERE E.SALARY > 5000
 ORDER BY ANNUAL_SALARY ASC;
-- 查詢月薪高于5000的員工，并按照年薪從小到大升序排序
-- ANNUAL_SALARY 不在 HRDB.EMPLOYEES
-- 先篩選where
-- 檢查 ANNUAL_SALARY 在不在 HRDB.EMPLOYEES
    -- 如果在泉哈， 就直接排序蛉幸，然后篩選結(jié)果
    -- 如果不在破讨，就先展示結(jié)果，看結(jié)果中有無 ANNUAL_SALARY奕纫， 如果有提陶，就再排序

多字段排序

多個字段的排序是支持的。排序的效果是按照字段的優(yōu)先級進行若锁，若當(dāng)前字段無法分出高低搁骑，則依靠后續(xù)的字段進行排序確認斧吐。

-- 多個字段排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.JOB_ID, E.SALARY
  FROM HRDB.EMPLOYEES E
 ORDER BY E.LAST_NAME ASC, E.JOB_ID DESC, E.SALARY DESC;
-- 查詢員工的信息又固，按照員工名字升序，工作職位降序煤率，工資降序依次進行排序

最后確認一點仰冠，排序是可以使用不在SELECT 列表中的列進行排序的。

-- 多個字段排序
SELECT E.EMPLOYEE_ID, E.FIRST_NAME, E.SALARY
  FROM HRDB.EMPLOYEES E
 ORDER BY E.LAST_NAME ASC, E.JOB_ID DESC, E.SALARY DESC;
-- 查詢員工的信息蝶糯，按照員工名字升序洋只，工作職位降序，工資降序依次進行排序
-- JOB_ID 不在 SELECT 后面

排序總結(jié)

• order by 字段/表達式 asc/desc

• where vs order by

  1. 先執(zhí)行 where昼捍，篩選出合適的數(shù)據(jù)
  2. 再執(zhí)行 order by识虚，按照指定的列進行排序

• from vs order by

  1. 只要是排序的列在 from 后面的表中，就可以按該列排序
  2. 排序和 select 后面的列無關(guān)

• select vs order by

  1. select 只能在 order by 出來結(jié)果后妒茬，選擇顯示指定的select 后面的列
  2. order by 后面的列不在 from 的表里面担锤，那么必須在 select 的別名中。

• order by 可以跟 多個列

  1. 先按前面的列進行排序乍钻，無法決定結(jié)果的時候肛循，再考慮后面的列
  2. 每個列之間，用逗號","隔開
  3. 每個列后面银择，必須加上 asc/desc多糠，不要省略
  4. 排序的依據(jù)還可以是列的表達式（算術(shù)）

分組函數(shù)

分組函數(shù)作用于一組數(shù)據(jù)，并對一組數(shù)據(jù)返回一個值浩考。

查詢語法

SELECT  [column,] group_function(column), ...
FROM        table
[WHERE  condition]
[GROUP BY   column]
[ORDER BY   column];
-- group_function()是分組函數(shù)方法

其中：COUNT(SALARY)是求SALARY不為NULL的記錄數(shù)夹孔，COUNT(*)是求記錄總數(shù)

分組函數(shù)忽略空值NULL

查詢示例

-- 分組函數(shù)查詢
SELECT AVG(SALARY), MAX(SALARY), MIN(SALARY), SUM(SALARY)
  FROM HRDB.EMPLOYEES
 WHERE JOB_ID LIKE '%REP%';
-- 可以對數(shù)值型數(shù)據(jù)記錄使用 AVG 和 SUM 函數(shù)進行求平均值和匯總等操作
-- 可以對任意數(shù)據(jù)類型的數(shù)據(jù)使用 MIN 和 MAX 函數(shù)。

GROUP BY子句的使用

-- 分組查詢
SELECT E.DEPARTMENT_ID, MIN(E.HIRE_DATE), MAX(E.HIRE_DATE)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.DEPARTMENT_ID;
-- 對日期類型進行 MAX 和 MIN 函數(shù)操作

-- 分組函數(shù)查詢
SELECT E.JOB_ID, AVG(E.SALARY), MAX(E.SALARY), MIN(E.SALARY), SUM(E.SALARY)
  FROM HRDB.EMPLOYEES E
 WHERE E.JOB_ID LIKE '%REP%'
 GROUP BY E.JOB_ID;
-- 按照職位包含“REP”的員工的職位進行統(tǒng)計析孽，統(tǒng)計每個組的平均工資析蝴、最高工資、最低工資和工資總和绿淋。

-- 分組函數(shù)查詢
SELECT E.DEPARTMENT_ID, COUNT(*), COUNT(E.EMPLOYEE_ID), COUNT(E.MANAGER_ID)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.DEPARTMENT_ID
-- COUNT是計數(shù)函數(shù)闷畸。COUNT(*) 返回表中記錄總數(shù)，COUNT(字段)返回的是當(dāng)前組里面不是NULL的記錄數(shù)

綜合示例

--  請查詢(工資高于8000)員工信息吞滞，EMPLOYEES
--  按照部門佑菩、職位輸出：每個部門+職位盾沫，有幾個員工
--  職位，部門殿漠，員工數(shù)赴精，最低工資，最高工資绞幌，平均工資蕾哟，工資總和
SELECT 
  e.department_id,
  e.job_id,
  COUNT(*) total_count,
  MIN(e.salary) min_salary,
  MAX(e.salary) max_salary,
  AVG(e.salary) avg_salary,
  SUM(e.salary) sum_salary 
FROM
  hrdb.employees e 
WHERE e.salary > 8000 
GROUP BY e.department_id,
  e.job_id 
ORDER BY avg_salary DESC ;

-- 1. 先篩選工資高于8000的 員工
-- 2. 對篩選出來的員工進行分組
-- 3. 同時按照 department_id 和 job_id 進行分組
-- 4. 分組后，對分組的結(jié)果進行統(tǒng)計
-- 5. 重命名分組的結(jié)果（列）
-- 6. 按照分組后的 avg_salary 列進行排序

查詢結(jié)果：

department_id  job_id      total_count  min_salary  max_salary    avg_salary  sum_salary  
-------------  ----------  -----------  ----------  ----------  ------------  ------------
           90  AD_PRES               1    24000.00    24000.00  24000.000000      24000.00
           90  AD_VP                 2    17000.00    17000.00  17000.000000      34000.00
           20  MK_MAN                1    13000.00    13000.00  13000.000000      13000.00
           80  SA_MAN                5    10500.00    14000.00  12200.000000      61000.00
          110  AC_MGR                1    12000.00    12000.00  12000.000000      12000.00
          100  FI_MGR                1    12000.00    12000.00  12000.000000      12000.00
           30  PU_MAN                1    11000.00    11000.00  11000.000000      11000.00
           70  PR_REP                1    10000.00    10000.00  10000.000000      10000.00
           80  SA_REP               15     8400.00    11500.00   9660.000000     144900.00
           60  IT_PROG               1     9000.00     9000.00   9000.000000       9000.00
          100  FI_ACCOUNT            2     8200.00     9000.00   8600.000000      17200.00
          110  AC_ACCOUNT            1     8300.00     8300.00   8300.000000       8300.00
           50  ST_MAN                1     8200.00     8200.00   8200.000000       8200.00

分組總結(jié)

• 分組的目的莲蜘，作用：統(tǒng)計
• 分組后谭确，結(jié)果還是一個表，表里面有：
  1. 共同的列（分組依據(jù)）
  2. 統(tǒng)計的列
• 每組記錄的個數(shù)
  1. COUNT(*)：* 代表一條記錄
  2. COUNT(列): 列：代表不是null的列
• 每組記錄某些列（數(shù)值）的統(tǒng)計
  1. 用統(tǒng)計函數(shù)（聚合函數(shù)）
  2. 求和：SUM()
  3. 求平均：AVG()
  4. 求最大值: MAX()
  5. 求最小值: MIN()
     GROUP BY 列（多個列票渠，用","分開逐哈，表達式）

DISTINCT關(guān)鍵字

DISTINCT是去除重復(fù)記錄。

-- DISTINCT關(guān)鍵字
SELECT DISTINCT e.job_id
FROM hr.employees e;

DISTINCT后面如果加了多列问顷，那么會剔除多列共同重復(fù)的記錄

SELECT DISTINCT 
  d.location_id,
  d.manager_id 
FROM
  hrdb.departments d ;
-- 去除 location_id 和 manager_id 一起重復(fù)的記錄

查詢結(jié)果

location_id  manager_id  
-----------  ------------
       1700           200
       1800           201
       1700           114
       2400           203
       1500           121
       1400           103
       2700           204
       2500           145
       1700           100
       1700           108
       1700           205
       1700        (NULL)

DISTINCT關(guān)鍵字也可以使用在分組函數(shù)中昂秃。

COUNT(DISTINCT expr) 返回 expr非空且不重復(fù)的記錄總數(shù)

查詢示例

-- DISTINCT關(guān)鍵字
SELECT COUNT(DISTINCT E.SALARY), COUNT(E.SALARY)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.JOB_ID;
-- DISTINCT去除了每個組中重復(fù)的工資數(shù)目計數(shù)

多個字段分組

與之前的ORDER BY子句一樣，GROUP BY子句也是可以多個字段進行的杜窄。

-- 多字段分組
SELECT E.JOB_ID, E.DEPARTMENT_ID, COUNT(DISTINCT E.SALARY), COUNT(E.SALARY)
  FROM HRDB.EMPLOYEES E
 GROUP BY E.JOB_ID, E.DEPARTMENT_ID;
-- 所有的數(shù)據(jù)將會以(JOB_ID + DEPARTMENT_ID)作為依據(jù)分組肠骆，統(tǒng)計每一個組的計數(shù)
-- 第四行中：E.JOB_ID 和 E.DEPARTMENT_ID 可以互換位置，不影響結(jié)果

過濾分組 HAVING

HAVING 是在 GROUP BY 之后進行過濾塞耕，對分組的結(jié)果進行過濾

使用 HAVING 過濾分組:

1. 行已經(jīng)被分組蚀腿。
2. 使用了組函數(shù)。
3. 滿足 HAVING 子句中條件的分組將被顯示荷科。
4. 不滿足 HAVING 子句中條件的分組唯咬，將不會顯示

查詢語法

SELECT  column, group_function
FROM        table
[WHERE  condition]
[GROUP BY   group_by_expression]
[HAVING group_condition]
[ORDER BY   column];

查詢示例

-- 分組后進行過濾
 SELECT DEPARTMENT_ID, MAX(SALARY)
   FROM HRDB.EMPLOYEES
  GROUP BY DEPARTMENT_ID
 HAVING MAX(SALARY) > 10000;
-- 分組后，過濾最大工資畏浆。篩選最大工資高于10000的數(shù)據(jù)進行查詢

與WHERE子句一起使用

-- 分組后進行過濾
SELECT JOB_ID, SUM(SALARY) PAYROLL
  FROM HRDB.EMPLOYEES
 WHERE JOB_ID NOT LIKE '%REP%'
 GROUP BY JOB_ID
HAVING SUM(SALARY) > 13000
 ORDER BY SUM(SALARY);
-- 先篩選出合適的記錄進行分組統(tǒng)計胆胰，統(tǒng)計以后再次對分組統(tǒng)計好的數(shù)據(jù)進行篩選，然后排序

使用之前的例子進行查詢

--  請查詢(工資高于8000)員工信息刻获，EMPLOYEES
--  按照部門蜀涨、職位輸出：(平均工資不低于10000)每個部門+職位，有幾個員工
--  職位蝎毡，部門厚柳，員工數(shù)，最低工資沐兵，最高工資别垮，平均工資，工資總和
SELECT 
  e.department_id,
  e.job_id,
  COUNT(*) total_count,
  MIN(e.salary) min_salary,
  MAX(e.salary) max_salary,
  AVG(e.salary) avg_salary,
  SUM(e.salary) sum_salary 
FROM
  hrdb.employees e 
WHERE e.salary > 8000 
GROUP BY e.department_id,
  e.job_id 
HAVING avg_salary >= 10000 
ORDER BY avg_salary DESC ;

-- 1. 先篩選工資高于8000的 員工
-- 2. 對篩選出來的員工進行分組
-- 3. 同時按照 department_id 和 job_id 進行分組
-- 4. 分組后扎谎，對分組的結(jié)果進行統(tǒng)計
-- 5. 用 HAVING 對統(tǒng)計的結(jié)果進行過濾
-- 6. 重命名分組的結(jié)果（列）
-- 7. 按照分組后的 avg_salary 列進行排序

查詢結(jié)果

department_id  job_id   total_count  min_salary  max_salary    avg_salary  sum_salary  
-------------  -------  -----------  ----------  ----------  ------------  ------------
           90  AD_PRES            1    24000.00    24000.00  24000.000000      24000.00
           90  AD_VP              2    17000.00    17000.00  17000.000000      34000.00
           20  MK_MAN             1    13000.00    13000.00  13000.000000      13000.00
           80  SA_MAN             5    10500.00    14000.00  12200.000000      61000.00
          110  AC_MGR             1    12000.00    12000.00  12000.000000      12000.00
          100  FI_MGR             1    12000.00    12000.00  12000.000000      12000.00
           30  PU_MAN             1    11000.00    11000.00  11000.000000      11000.00
           70  PR_REP             1    10000.00    10000.00  10000.000000      10000.00

子查詢

• 子查詢作為新查詢的數(shù)據(jù)表（常見）

查詢語法

SELECT  select_list
FROM    (SELECT select_list
               FROM     table) s
WHERE   expr operator;

查詢示例

SELECT 
  s.job_id,
  s.ct,
  s.ay 
FROM
  (
  SELECT 
    e.job_id,
    COUNT(*) ct,
    AVG(e.salary) ay 
  FROM
    hrdb.employees e 
  WHERE e.salary > 10000 
  GROUP BY e.job_id 
  HAVING COUNT(*) > 2
  ) s 
WHERE s.ay > 11000 ;
-- s表碳想，是一個查詢語句
-- s表有三個字段烧董，job_id, ct, ay.
--  select xx from s where xxx

在當(dāng)前的查詢結(jié)果中，做進一步的查詢

• 子查詢作為新查詢的條件

查詢語法

SELECT  select_list
FROM    table
WHERE   expr operator
            (SELECT select_list
               FROM     table);

• 子查詢要包含在括號內(nèi)胧奔。

• 將子查詢放在比較條件的右側(cè)逊移。

• 一般情況下不要在子查詢中使用ORDER BY 子句。

• 單行操作符對應(yīng)單行子查詢龙填，多行操作符對應(yīng)多行子查詢胳泉。

  查詢示例

查詢示例

-- 子查詢
SELECT LAST_NAME, SALARY
  FROM HRDB.EMPLOYEES
 WHERE SALARY > (SELECT SALARY FROM HRDB.EMPLOYEES WHERE LAST_NAME = 'Abel');
-- 查詢工資高于“Abel”的所有員工的名字和工資
-- 這條語句有風(fēng)險，如果雇員中有多個人的 LAST_NAME 是 Abel岩遗，這條語句執(zhí)行可能失敗

• ALL：全部扇商，一般指最大值

  SELECT 
    LAST_NAME, SALARY 
  FROM
    hrdb.employees e 
  WHERE e.salary > ALL 
    (SELECT 
      e.salary 
    FROM
      hrdb.employees e 
    WHERE e.last_name = 'Cambrault') ;
  -- 查詢工資高于（全部Last_name是“Cambrault”雇員工資）的所有員工的名字和工資
  

  ?

• ANY：任何一個，一般指最小值

  SELECT 
    LAST_NAME, SALARY 
  FROM
    hrdb.employees e 
  WHERE e.salary > ANY 
    (SELECT 
      e.salary 
    FROM
      hrdb.employees e 
    WHERE e.last_name = 'Cambrault') ;
  -- 查詢工資高于（任意Last_name是“Cambrault”雇員工資）的所有員工的名字和工資
  

• 精確子查詢喘先，需要用主鍵（PRIMARY KEY）

-- 子查詢
 SELECT LAST_NAME, JOB_ID, SALARY
   FROM HRDB.EMPLOYEES
  WHERE JOB_ID = (SELECT JOB_ID FROM HRDB.EMPLOYEES WHERE EMPLOYEE_ID = 141)
    AND SALARY > (SELECT SALARY FROM HRDB.EMPLOYEES WHERE EMPLOYEE_ID = 143);
/*查詢職位和員工編號141相同钳吟，并且工資高于員工編號143的所有員工的姓廷粒，工作編號和工資*/

HAVING子句子查詢

-- HAVING子句子查詢
 SELECT DEPARTMENT_ID, MIN(SALARY)
   FROM HRDB.EMPLOYEES
  GROUP BY DEPARTMENT_ID
 HAVING MIN(SALARY) > (SELECT MIN(SALARY)
                         FROM HRDB.EMPLOYEES
                        WHERE DEPARTMENT_ID = 50);
-- 統(tǒng)計以后再次對分組的數(shù)據(jù)進行篩選窘拯，篩選條件基于一個子查詢的結(jié)果

ANY & ALL 子查詢

-- 子查詢
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE SALARY < ANY
 (SELECT SALARY FROM HRDB.EMPLOYEES WHERE JOB_ID = 'IT_PROG')
   AND JOB_ID <> 'IT_PROG';
-- any，任何一個,查出非IT_PROG職位的員工中薪水少于IT_PROG任一員工薪水的信息,小于最高工資

-- 子查詢
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, SALARY
  FROM HRDB.EMPLOYEES
 WHERE SALARY < ALL
 (SELECT SALARY FROM HRDB.EMPLOYEES WHERE JOB_ID = 'IT_PROG')
   AND JOB_ID <> 'IT_PROG';
-- all坝茎，全部涤姊，查出少于IT_PROG職位最低薪水的員工信息

EXISTS操作符

EXISTS操作符檢查在子查詢中是否存在滿足條件的行

• 如果在子查詢中存在滿足條件的行:
• 不在子查詢中繼續(xù)查找
• 條件返回TRUE
• 如果在子查詢中不存在滿足條件的行:
• 條件返回FALSE
• 繼續(xù)在子查詢中查找

-- 子查詢
select 'True' from dual WHERE EXISTS (SELECT '44'
                FROM HRDB.EMPLOYEES
               WHERE MANAGER_ID = 101);

-- 下面的思考一下原理
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, DEPARTMENT_ID
  FROM HRDB.EMPLOYEES  E
  WHERE EXISTS (SELECT NULL
                FROM HRDB.EMPLOYEES
               WHERE MANAGER_ID = E.EMPLOYEE_ID);
-- 找出員工表中職位是經(jīng)理的員工，實際上只要子查詢中有數(shù)據(jù)返回則Where條件成立
SELECT EMPLOYEE_ID, LAST_NAME, JOB_ID, (select job_title from HRDB.jobs j where E.job_id=j.job_id) "job_title",DEPARTMENT_ID
  FROM HRDB.EMPLOYEES E 
  WHERE EXISTS (SELECT NULL
                FROM HRDB.EMPLOYEES
               WHERE MANAGER_ID = E.EMPLOYEE_ID);
-- 檢驗一下看是不是都是經(jīng)理職位嗤放，這樣改一改會更容易理解

-- 子查詢
SELECT DEPARTMENT_ID, DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE NOT EXISTS
 (SELECT 'X' FROM HRDB.EMPLOYEES WHERE DEPARTMENT_ID = D.DEPARTMENT_ID);
-- 找出部門表中沒有員工的部門思喊，實際上只要子查詢中無數(shù)據(jù)返回則where條件成立

多表查詢

笛卡爾積

笛卡爾積是集合的一種，假設(shè)A和B都是集合次酌，A和B的笛卡爾積用A X B來表示恨课，是所有有序偶（a,b）的集合，其中a屬于A岳服，b屬于B剂公。

SELECT COUNT(*) FROM HRDB.EMPLOYEES E;      -- 107行數(shù)據(jù)
SELECT COUNT(*) FROM HRDB.DEPARTMENTS D;    -- 27行數(shù)據(jù)
-- 笛卡兒積
SELECT * FROM HRDB.EMPLOYEES E, HR.DEPARTMENTS D;   -- 107 * 27 = 2889行數(shù)據(jù)
-- 記錄數(shù)是兩個表的記錄數(shù)相乘
-- 所有表中的所有行互相連接

內(nèi)連接 INNER JOIN

連接語法

-- 兩種語法：第一種比較古老
SELECT  table1.column, table2.column,...
FROM    table1, table2
WHERE   table1.column1 = table2.column2
AND     table.column1 = 'value';

SELECT       table1.column, table2.column,...
FROM         table1 
INNER JOIN   table2
ON           table1.column1 = table2.column2
WHERE        table1.column1 = 'value';

• 使用連接在多個表中查詢數(shù)據(jù)。

• 在 WHERE 子句中寫入連接條件吊宋。

• 在表中有相同列時纲辽，在列名之前加上表名前綴。

  ?

  -- 多表連接查詢
  SELECT 
      E.EMPLOYEE_ID,  
      CONCAT(E.FIRST_NAME, ' ', E.LAST_NAME) AS "employee_name",
      E.SALARY,
      E.DEPARTMENT_ID DEPARTMENT_ID1,
      D.DEPARTMENT_ID DEPARTMENT_ID2,
      D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E, HRDB.DEPARTMENTS D
  WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;
  -- 查詢每個員工的員工編號璃搜、姓名拖吼、工資、和部門編號这吻、部門名稱
  

  ?

• 使用表名前綴在多個表中區(qū)分相同的列吊档。

• 使用表名可以提高效率。

• 在不同表中具有相同列名的列可以用別名加以區(qū)分唾糯。

  -- 非等值內(nèi)連接
  SELECT concat(E.FIRST_NAME, E.LAST_NAME), E.SALARY, J.JOB_TITLE
    FROM HRDB.EMPLOYEES E, HRDB.JOBS J
   WHERE E.JOB_ID = J.JOB_ID
     AND E.SALARY BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
  -- 查詢工資符合職位工資區(qū)間的員工的姓名怠硼、工資和職位
  

  ?

查詢語法2

SELECT       table1.column, table2.column,...
FROM         table1 
INNER JOIN   table2
ON           table1.column1 = table2.column2
WHERE        table1.column1 = 'value';

查詢示例

-- 多表連接查詢
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.' , E.LAST_NAME) AS "employee_name",
       E.SALARY,
       E.DEPARTMENT_ID DEPARTMENT_ID1,
       D.DEPARTMENT_ID DEPARTMENT_ID2,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 INNER JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查詢每個員工的員工編號涡上、姓名、工資拒名、和部門編號吩愧、部門名稱

-- 非等值內(nèi)連接
SELECT concat(E.FIRST_NAME, '.', E.LAST_NAME), E.SALARY, J.JOB_TITLE
  FROM HRDB.EMPLOYEES E
INNER JOIN HRDB.JOBS J
  ON E.JOB_ID = J.JOB_ID
   AND E.SALARY BETWEEN J.MIN_SALARY AND J.MAX_SALARY;
-- 查詢工資符合職位工資區(qū)間的員工的姓名、工資和職位

• 查詢示例1

  -- 查詢每個國家的編號增显、名字和所在的大洲（區(qū)域表）
  SELECT 
    c.country_id,
    c.country_name,
    r.region_name 
  FROM
    hrdb.countries c 
    INNER JOIN hrdb.regions r 
      ON c.region_id = r.region_id ;
  

• 查詢示例2

  -- 查詢每個員工的員工編號雁佳、姓名、工資同云、和部門編號糖权、部門名稱
  SELECT 
    e.employee_id,
    e.first_name,
    e.salary,
    e.department_id,
    d.department_name 
  FROM
    hrdb.employees e 
    INNER JOIN hrdb.departments d 
      ON e.department_id = d.department_id ;
  --  從員工表（部門編號）查員工的部門名稱（在部門表中）
  

• 查詢示例3

  -- 查詢每個員工的員工編號、姓名炸站、工資星澳、和職位編號、職位名稱  
  SELECT 
    e.employee_id,
    e.first_name,
    e.salary,
    e.job_id,
    j.job_title 
  FROM
    hrdb.employees e 
    INNER JOIN hrdb.jobs j 
      ON e.job_id = j.job_id ;
  

  ?

• 查詢示例4

  -- 查詢每個部門的名字和部門經(jīng)理的名字
  SELECT 
    d.department_name,
    m.first_name 
  FROM
    hrdb.departments d 
    INNER JOIN hrdb.employees m 
      ON d.manager_id = m.employee_id ;
  

  ?

• 查詢示例5

  -- 查詢每個員工的名字和員工經(jīng)理的名字
  SELECT 
    e.first_name employee_name,
    m.first_name manager_name
  FROM
    hrdb.employees e 
    INNER JOIN hrdb.employees m 
      ON e.manager_id = m.employee_id ;
  

  ?

  查詢示例

  數(shù)據(jù)庫查詢示例.png

外連接

外連接包括：左連接旱易，右連接和全連接禁偎。

• 內(nèi)連接只返回滿足連接條件的數(shù)據(jù)。
• 兩個表在連接過程中除了返回滿足連接條件的行以外還返回左（或右）表中不滿足條件的行 阀坏，這種連接稱為左（或右） 外聯(lián)接如暖。
• 兩個表在連接過程中除了返回滿足連接條件的行以外還返回兩個表中不滿足條件的行 ，這種連接稱為全 外聯(lián)接忌堂。

左連接

左連接是以左邊的表為基礎(chǔ)盒至，左邊的表符合過濾條件的都篩選出來，右邊的表若能匹配的記錄士修，直接匹配枷遂，否則補NULL。

查詢語法

SELECT  table1.column, table2.column,...
FROM    table1
LEFT JOIN table2
ON  table1.column = table2.column;

查詢示例

-- 外連接查詢棋嘲，左連接
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
  LEFT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查詢所有員工的員工編號酒唉、姓名、部門編號封字、部門名稱黔州。確保列出所有的員工。

• ORACLE支持老的語法

  -- ORACLE左連接
  SELECT    table1.column, table2.column,...
  FROM  table1, table2
  WHERE table1.column1 = table2.column2(+)
  AND     table.column1 = 'value';
  

  ?

右連接

查詢語法

SELECT  table1.column, table2.column,...
FROM    table1
RIGHT JOIN table2
ON  table1.column = table2.column;

查詢示例

-- 外連接查詢阔籽，右連接
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 RIGHT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查詢所有員工的員工編號流妻、姓名就谜、部門編號吱七、部門名稱厅须。確保列出所有的部門刹碾。

• ORACLE支持老的語法

  -- ORACLE左連接
  SELECT    table1.column, table2.column,...
  FROM  table1, table2
  WHERE table1.column1(+) = table2.column2
  AND     table.column1 = 'value';
  

  ?

全連接

查詢語法

SELECT  table1.column, table2.column,...
FROM    table1
FULL JOIN table2
ON  table1.column = table2.column;

MySQL 沒有全連接的用法，需要用UNION把左右連接聯(lián)合起來渴逻。

UNION 連接了兩段完整的 SELECT 語句桥氏，會去掉重復(fù)記錄

• UNION 查詢比 UNION ALL 慢

UNION ALL 連接了兩段完整的 SELECT 語句效拭，不會去掉重復(fù)記錄

查詢示例

-- 外連接查詢，全連接
SELECT E.EMPLOYEE_ID,
       CONCAT(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 LEFT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
UNION
SELECT E.EMPLOYEE_ID,
       CONCAT(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
 RIGHT JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

-- oracle的寫法：
SELECT E.EMPLOYEE_ID,
       concat(E.FIRST_NAME, '.', E.LAST_NAME),
       E.DEPARTMENT_ID,
       D.DEPARTMENT_ID,
       D.DEPARTMENT_NAME
  FROM HRDB.EMPLOYEES E
  FULL JOIN HRDB.DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;
-- 查詢所有員工的員工編號浑度、姓名寇窑、部門編號、部門名稱箩张。確保列出所有的員工和部門甩骏。

多表查詢總結(jié)

• 內(nèi)連接

  是否顯示	左邊表	右邊表
  Y	有	有
  N	有	無
  N	無	有

• 左連接

  是否顯示	左邊表	右邊表
  Y	有	有
  Y	有	無（補NULL）
  N	無	有

• 右連接

  是否顯示	左邊表	右邊表
  Y	有	有
  N	有	無
  Y	無（補NULL）	有

• 全連接

  是否顯示	左邊表	右邊表
  Y	有	有
  Y	有	無（補NULL）
  Y	無（補NULL）	有

Top-N分析

Top-N 分析查詢一個列中最大或最小的 n 個值:

• 銷售量最高的十種產(chǎn)品是什么?
• 銷售量最差的十種產(chǎn)品是什么?

最大和最小的值的集合是 Top-N 分析所關(guān)心的

查詢最大的幾個值的 Top-N 分析:

查詢語法

SELECT [column_list] 
FROM   table
WHERE  column...
order by column
limit M, N; -- 從第M+1行開始，獲取N行數(shù)據(jù)

查詢示例

SELECT last_name,salary 
FROM HRDB.employees
ORDER BY salary DESC
limit 0,10;
-- 查詢工資最高的十名員工

SELECT last_name,salary 
FROM HRDB.employees
ORDER BY salary DESC
limit 3,1;
-- 查詢工資第3+1高的員工

Top-N分析 Oracle

Top-N 分析查詢一個列中最大或最小的 n 個值:

• 銷售量最高的十種產(chǎn)品是什么?
• 銷售量最差的十種產(chǎn)品是什么?

最大和最小的值的集合是 Top-N 分析所關(guān)心的

查詢最大的幾個值的 Top-N 分析:

查詢語法

SELECT [column_list], ROWNUM  
FROM   (SELECT [column_list] 
        FROM table
        ORDER  BY Top-N_column)
WHERE  ROWNUM <=  N;

查詢示例

SELECT ROWNUM as RANK, last_name, salary 
FROM  (SELECT last_name,salary FROM HR.employees
       ORDER BY salary DESC)
WHERE ROWNUM <= 3;
--查詢工資最高的三名員工

ROWNUM的使用必須遵循以下特性：

• ROWNUM務(wù)必從第一個開始先慷，若沒有第一個饮笛，就沒有后續(xù)的
• ROWNUM務(wù)必連續(xù)使用。若中間斷開论熙，則段開以后的部分將不會被選擇
• ROWNUM將于查詢記錄形成的同時一起產(chǎn)生福青。ROWNUM的產(chǎn)生在ORDER BY子句以前。

SELECT RANK, LAST_NAME, SALARY
  FROM (SELECT ROWNUM AS RANK, LAST_NAME, SALARY
          FROM (SELECT LAST_NAME, SALARY
                  FROM HR.EMPLOYEES
                 ORDER BY SALARY DESC))
 WHERE RANK = 3;
--查詢工資第三高的員工

分組統(tǒng)計及Top分析綜合示例：

? 示例一：某學(xué)校系統(tǒng)中的“學(xué)分表Records”包含：學(xué)號SID脓诡、課程CID无午、分數(shù)SCORE三個字段，請查詢出總分排名前三的學(xué)生的學(xué)號及總分

創(chuàng)建表及插入數(shù)據(jù)

>mysql-u root-p
mysql>source XXX.sql

分析：

1誉券、由于需要按照學(xué)號統(tǒng)計總分指厌，因此首先要按照學(xué)號分組對分數(shù)求和,并按照分數(shù)倒序排列刊愚，如下：

SELECT r.SID, SUM(r.Score) as SS  FROM HRDB.Records r  GROUP BY r.SID

2踊跟、從上面的查詢結(jié)果中做二次查詢，以找到排名前三的學(xué)員鸥诽，最終SQL如下：

SELECT r.SID, SUM(r.Score) as SS  
FROM HRDB.Records r  
GROUP BY r.SID 
order by SS desc
limit 0,3

示例二：同上表商玫，請查詢出每門課程得到最高分的學(xué)生的學(xué)號和課程號、分數(shù)

分析：1牡借、由于要按照課程來統(tǒng)計出最高分拳昌，因此可以先用分組查詢語句查詢出每門課程的最高分

SELECT r.CID, MAX(r.Score) MS  FROM Records r  GROUP BY r.CID

2、上述的查詢結(jié)果可以作為一張臨時表

3钠龙、上述臨時表再關(guān)聯(lián)Records表炬藤，從Records表中查詢出和最高分數(shù)相等的學(xué)生的相關(guān)信息

-- 方法1,使用inner join
SELECT r.SID, r.CID, r.Score 
FROM Records r
INNER JOIN 
(SELECT r.CID, MAX(r.Score) MS
  FROM HRDB.Records r
  GROUP BY r.CID
) s
ON r.CID = s.CID
AND r.Score = s.MS;

-- 方法2 直接用where聯(lián)表
select  r.SID, r.CID, r.Score 
FROM HRDB.Records r,
(SELECT r.CID, MAX(r.Score) MS
  FROM HRDB.Records r
  GROUP BY r.CID
) s
where r.CID = s.CID and r.Score = s.MS

實用查詢示例

接下來的部分是一些查詢練習(xí)

-- 同姓的員工計數(shù)
SELECT LAST_NAME, COUNT(LAST_NAME)
  FROM HRDB.EMPLOYEES
 GROUP BY LAST_NAME
HAVING COUNT(LAST_NAME) > 1;

-- 同名的員工計數(shù)
SELECT FIRST_NAME, COUNT(FIRST_NAME)
  FROM HRDB.EMPLOYEES
 GROUP BY FIRST_NAME
HAVING COUNT(FIRST_NAME) > 1;

-- 列出至少有一個雇員的所有部門名稱
-- 方法一
 SELECT D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID IN
       (SELECT distinct E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E)
 ORDER BY DEPARTMENT_NAME ASC;
-- 方法二
SELECT D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID IN
       (SELECT E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E
         GROUP BY E.DEPARTMENT_ID
        HAVING COUNT(DEPARTMENT_ID) > 0)
 ORDER BY DEPARTMENT_NAME ASC;

-- 列出沒有任何雇員的部門名稱
-- 方法一
 SELECT D.DEPARTMENT_ID,D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID NOT IN
       (SELECT distinct E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E WHERE E.DEPARTMENT_ID IS NOT NULL)
 ORDER BY DEPARTMENT_ID ASC;
-- 方法二
SELECT D.DEPARTMENT_NAME
  FROM HRDB.DEPARTMENTS D
 WHERE D.DEPARTMENT_ID NOT IN
       (SELECT E.DEPARTMENT_ID
          FROM HRDB.EMPLOYEES E
          WHERE E.DEPARTMENT_ID IS NOT NULL
         GROUP BY E.DEPARTMENT_ID
        HAVING COUNT(DEPARTMENT_ID) > 0)
 ORDER BY DEPARTMENT_NAME ASC;

-- 列出工資大于5000的所有雇員的員工編號，姓名和 其經(jīng)理的姓名
-- 方法一
SELECT  E.EMPLOYEE_ID,E.FIRST_NAME,E.LAST_NAME,E.SALARY,      
        M.FIRST_NAME,M.LAST_NAME
FROM HRDB.EMPLOYEES E
LEFT JOIN HRDB.EMPLOYEES M
    ON E.MANAGER_ID = M.EMPLOYEE_ID
 WHERE E.SALARY > 5000
 ORDER BY E.SALARY DESC;

-- 方法二
SELECT E1.EMPLOYEE_ID, E1.FIRST_NAME,
       E1.LAST_NAME, E1.SALARY,
       M.FIRST_NAME, M.LAST_NAME
  FROM (SELECT E.EMPLOYEE_ID,
               E.FIRST_NAME,
               E.LAST_NAME,
               E.SALARY,
               E.MANAGER_ID
          FROM HRDB.EMPLOYEES E
         WHERE E.SALARY > 5000) E1
  LEFT JOIN HRDB.EMPLOYEES M
    ON E1.MANAGER_ID = M.EMPLOYEE_ID
 ORDER BY E1.SALARY DESC;

-- 列出所有雇員的職位名稱碴里，要求職位名稱不重復(fù)(列出所有雇員職位名稱的種類)
-- 方法一
 SELECT DISTINCT J.JOB_TITLE
 FROM HRDB.EMPLOYEES E, HRDB.JOBS J
 WHERE E.JOB_ID = J.JOB_ID
-- 方法二
SELECT DISTINCT J.JOB_TITLE
  FROM HRDB.EMPLOYEES E
 INNER JOIN HRDB.JOBS J
    ON J.JOB_ID = E.JOB_ID;

-- 列出平均工資最高的部門的經(jīng)理的所有信息
SELECT ROWNUM, S.*, D.*, E2.*
  FROM (SELECT E.DEPARTMENT_ID, ROUND(AVG(E.SALARY), 2)
          FROM HRDB.EMPLOYEES E
         GROUP BY E.DEPARTMENT_ID
         ORDER BY AVG(E.SALARY) DESC) S
 INNER JOIN HRDB.DEPARTMENTS D
    ON D.DEPARTMENT_ID = S.DEPARTMENT_ID
 INNER JOIN HRDB.EMPLOYEES E2
    ON E2.EMPLOYEE_ID = D.MANAGER_ID
 WHERE ROWNUM = 1