MySQL 基礎學習_Day03

1. 前言
2. DQL語言的學習（三）
- 2.1 連接配接查詢（二）
- - 2.1.1 内連接配接
  - 2.1.2 外連接配接&交叉連接配接
- 2.2 子查詢
- 2.3 分頁查詢

1. 前言

2. DQL語言的學習（三）

學習架構：

MySQL基礎學習筆記_Day031. 前言2. DQL語言的學習（三）

2.1 連接配接查詢（二）

sql99文法的連接配接查詢：

MySQL基礎學習筆記_Day031. 前言2. DQL語言的學習（三）

2.1.1 内連接配接

inner join

:在表中存在至少一個比對時，

INNER JOIN

關鍵字傳回行。

select 查詢清單
from 表1 别名
inner join 表2 别名 
on 連接配接條件;

特定：

① 添加排序、分組、篩選

②

inner

可以省略

③ 篩選條件放在

where

後面，連接配接條件放在

on

後面，提高分離性，便于閱讀

④

inner join

連接配接和sql92文法中的等值連接配接效果是一樣的，都是查詢多表的交集

#1、等值連接配接
#案例1.查詢員工名、部門名
SELECT last_name,department_name
FROM departments d
JOIN  employees e
ON e.`department_id` = d.`department_id`;



#案例2.查詢名字中包含e的員工名和工種名（添加篩選）
SELECT last_name,job_title
FROM employees e
INNER JOIN jobs j
ON e.`job_id`=  j.`job_id`
WHERE e.`last_name` LIKE '%e%';



#案例3. 查詢部門個數>3的城市名和部門個數，（添加分組+篩選）
#①查詢每個城市的部門個數
#②在①結果上篩選滿足條件的
SELECT city,COUNT(*) 部門個數
FROM departments d
INNER JOIN locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;


#案例4.查詢哪個部門的員工個數>3的部門名和員工個數，并按個數降序（添加排序）
#①查詢每個部門的員工個數
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name

#② 在①結果上篩選員工個數>3的記錄，并排序
SELECT COUNT(*) 個數,department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

#5.查詢員工名、部門名、工種名，并按部門名降序（添加三表連接配接）
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.`department_id`=d.`department_id`
INNER JOIN jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;

#二）非等值連接配接
#查詢員工的工資級别
SELECT salary,grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`;
 
#查詢工資級别的個數>20的個數，并且按工資級别降序
SELECT COUNT(*),grade_level
FROM employees e
JOIN job_grades g
ON e.`salary` BETWEEN g.`lowest_sal` AND g.`highest_sal`
GROUP BY grade_level
HAVING COUNT(*)>20
ORDER BY grade_level DESC;
 
 
#三）自連接配接
#查詢員工的名字、上級的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`;
 
#查詢姓名中包含字元k的員工的名字、上級的名字
SELECT e.last_name,m.last_name
FROM employees e
JOIN employees m
ON e.`manager_id`= m.`employee_id`
WHERE e.`last_name` LIKE '%k%';

2.1.2 外連接配接&交叉連接配接

應用場景：用于查詢一個表中有，另一個表沒有的記錄

特點：

① 外連接配接的查詢結果為主表中的所有記錄

② 如果從表中有和它比對的，則顯示比對的值

③ 如果從表中沒有和它比對的，則顯示

null

④ 外連接配接查詢結果=内連接配接結果+主表中有而從表沒有的記錄

⑤ 左外連接配接，

left join

左邊的是主表

⑥ 右外連接配接，

right join

右邊的是主表

⑦ 左外和右外交換兩個表的順序，可以實作同樣的效果

⑧ 全外連接配接=内連接配接的結果+表1中有但表2沒有的+表2中有但表1沒有的

sql92 和 sql99 比較

功能：sql99支援的較多

可讀性：sql99實作連接配接條件和篩選條件的分離，可讀性較高

交叉連接配接：産生了一個結果集，該結果集是兩個關聯表的行的乘積。通常，如果每個表分别具有n和m行，則結果集将具有n*m行

#引入：查詢男朋友 不在男神表的的女神名
 SELECT * FROM beauty;
 SELECT * FROM boys;
 
 #左外連接配接
 SELECT b.*,bo.*
 FROM boys bo
 LEFT OUTER JOIN beauty b
 ON b.`boyfriend_id` = bo.`id`
 WHERE b.`id` IS NULL;
 
 #案例1：查詢哪個部門沒有員工
 #左外
 SELECT d.*,e.employee_id
 FROM departments d
 LEFT OUTER JOIN employees e
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
 
 
 #右外
 SELECT d.*,e.employee_id
 FROM employees e
 RIGHT OUTER JOIN departments d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;
 
 #全外
 USE girls;
 SELECT b.*,bo.*
 FROM beauty b
 FULL OUTER JOIN boys bo
 ON b.`boyfriend_id` = bo.id;
 
 #交叉連接配接
 SELECT b.*,bo.*
 FROM beauty b
 CROSS JOIN boys bo;

2.2 子查詢

含義：

出現在其他語句中的 select
語句，稱為子查詢或内查詢；

而出現在外部的查詢語句，稱為主查詢或外查詢

MySQL基礎學習筆記_Day031. 前言2. DQL語言的學習（三）

2.2.1

where

或

having

後面

/*
1、标量子查詢（單行子查詢）
2、列子查詢（多行子查詢）
3、行子查詢（多列多行）

特點：
①子查詢放在小括号内
②子查詢一般放在條件的右側
③标量子查詢，一般搭配着單行操作符使用
> < >= <= = <>

列子查詢，一般搭配着多行操作符使用
in、any/some、all
④子查詢的執行優先于主查詢執行，主查詢的條件用到了子查詢的結果
*/
#1.标量子查詢★
#案例1：誰的工資比 Abel 高?
#①查詢Abel的工資
SELECT salary
FROM employees
WHERE last_name = 'Abel'

#②查詢員工的資訊，滿足 salary>①結果
SELECT *
FROM employees
WHERE salary>(
	SELECT salary
	FROM employees
	WHERE last_name = 'Abel'

);

#案例2：傳回job_id與141号員工相同，salary比143号員工多的員工 姓名，job_id 和工資
#①查詢141号員工的job_id
SELECT job_id
FROM employees
WHERE employee_id = 141

#②查詢143号員工的salary
SELECT salary
FROM employees
WHERE employee_id = 143

#③查詢員工的姓名，job_id 和工資，要求job_id=①并且salary>②
SELECT last_name,job_id,salary
FROM employees
WHERE job_id = (
	SELECT job_id
	FROM employees
	WHERE employee_id = 141
) AND salary>(
	SELECT salary
	FROM employees
	WHERE employee_id = 143

);


#案例3：傳回公司工資最少的員工的last_name,job_id和salary
#①查詢公司的 最低工資
SELECT MIN(salary)
FROM employees

#②查詢last_name,job_id和salary，要求salary=①
SELECT last_name,job_id,salary
FROM employees
WHERE salary=(
	SELECT MIN(salary)
	FROM employees
);


#案例4：查詢最低工資大于50号部門最低工資的部門id和其最低工資
#①查詢50号部門的最低工資
SELECT  MIN(salary)
FROM employees
WHERE department_id = 50

#②查詢每個部門的最低工資
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id

#③ 在②基礎上篩選，滿足min(salary)>①
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  MIN(salary)
	FROM employees
	WHERE department_id = 50
);

#非法使用标量子查詢/錯誤類型（即子查詢不是一行一列）
SELECT MIN(salary),department_id
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(
	SELECT  salary
	FROM employees
	WHERE department_id = 250


);

#2.列子查詢（多行子查詢）★
#案例1：傳回location_id是1400或1700的部門中的所有員工姓名
#①查詢location_id是1400或1700的部門編号
SELECT DISTINCT department_id
FROM departments
WHERE location_id IN(1400,1700)

#②查詢員工姓名，要求部門号是①清單中的某一個
SELECT last_name
FROM employees
WHERE department_id  <>ALL(
	SELECT DISTINCT department_id
	FROM departments
	WHERE location_id IN(1400,1700)
);


#案例2：傳回其它工種中比job_id為‘IT_PROG’工種任一工資低的員工的員工号、姓名、job_id 以及salary
#①查詢job_id為‘IT_PROG’部門任一工資
SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG'

#②查詢員工号、姓名、job_id 以及salary，salary<(①)的任意一個
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ANY(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MAX(salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

#案例3：傳回其它部門中比job_id為‘IT_PROG’部門所有工資都低的員工   的員工号、姓名、job_id 以及salary
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<ALL(
	SELECT DISTINCT salary
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

#或
SELECT last_name,employee_id,job_id,salary
FROM employees
WHERE salary<(
	SELECT MIN( salary)
	FROM employees
	WHERE job_id = 'IT_PROG'
) AND job_id<>'IT_PROG';

#3、行子查詢（結果集一行多列或多行多列）--了解即可用得比較少
#兩個條件都是相同的
#案例：查詢員工編号最小并且工資最高的員工資訊

SELECT * 
FROM employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM employees
);

#①查詢最小的員工編号
SELECT MIN(employee_id)
FROM employees

#②查詢最高工資
SELECT MAX(salary)
FROM employees

#③查詢員工資訊
SELECT *
FROM employees
WHERE employee_id=(
	SELECT MIN(employee_id)
	FROM employees
)AND salary=(
	SELECT MAX(salary)
	FROM employees
);

2.2.2

select

後面

僅僅支援标量子查詢

#案例：查詢每個部門的員工個數
SELECT d.*,(
	SELECT COUNT(*)
	FROM employees e
	WHERE e.department_id = d.`department_id`
 ) 個數
 FROM departments d;
 
#案例2：查詢員工号=102的部門名 --**錯誤展示**
SELECT (
	SELECT department_name,e.department_id
	FROM departments d
	INNER JOIN employees e
	ON d.department_id=e.department_id
	WHERE e.employee_id=102
) 部門名;

2.2.3

from

後面

将子查詢結果充當一張表，要求必須起别名

#案例：查詢每個部門的平均工資的工資等級
#①查詢每個部門的平均工資
SELECT AVG(salary),department_id
FROM employees
GROUP BY department_id
SELECT * FROM job_grades;

#②連接配接①的結果集和job_grades表，篩選條件平均工資 between lowest_sal and highest_sal
SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

2.2.4

exists

後面（相關子查詢）

文法：exists(完整的查詢語句)

結果：1或0

上語句運作結果：

MySQL基礎學習筆記_Day031. 前言2. DQL語言的學習（三）

#案例1：查詢有員工的部門名
#in
SELECT department_name
FROM departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM employees
)

#exists
SELECT department_name
FROM departments d
WHERE EXISTS(
	SELECT *
	FROM employees e
	WHERE d.`department_id`=e.`department_id`
);


#案例2：查詢沒有女朋友的男神資訊
#in
SELECT bo.*
FROM boys bo
WHERE bo.id NOT IN(
	SELECT boyfriend_id
	FROM beauty
)

#exists
SELECT bo.*
FROM boys bo
WHERE NOT EXISTS(
	SELECT boyfriend_id
	FROM beauty b
	WHERE bo.`id`=b.`boyfriend_id`
);

2.3 分頁查詢

應用場景：當要顯示的資料，一頁顯示不全，需要分頁送出sql請求

文法：

select 查詢清單
from 表
【join type join 表2
on 連接配接條件
where 篩選條件
group by 分組字段
having 分組後的篩選
order by 排序的字段】
limit 【offset,】size;

#offset要顯示條目的起始索引（起始索引從0開始）
#size 要顯示的條目個數

特點： limit語句放在查詢語句的最後；

分頁公式：要顯示的頁數 page，每頁的條目數size;

(page-1)*size

select 查詢清單
from 表
limit (page-1)*size,size;

#案例1：查詢前五條員工資訊
SELECT * FROM  employees LIMIT 0,5;
SELECT * FROM  employees LIMIT 5;
 
#案例2：查詢第11條——第25條
SELECT * FROM  employees LIMIT 10,15;

#案例3：有獎金的員工資訊，并且工資較高的前10名顯示出來
SELECT 
    * 
FROM
    employees 
WHERE commission_pct IS NOT NULL 
ORDER BY salary DESC 
LIMIT 10 ;

MySQL基礎學習筆記_Day031. 前言2. DQL語言的學習（三）

MySQL 基礎學習_Day03

1. 前言

2. DQL語言的學習（三）

2.1 連接配接查詢（二）

2.1.1 内連接配接

2.1.2 外連接配接&交叉連接配接

2.2 子查詢

2.3 分頁查詢

繼續閱讀

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

sqlServer根據經緯查距離

SequoiaDB巨杉資料庫C++驅動概述