【編測編學】MySQL資料庫基礎知識2

一、常見函式：

字元函式:length  concat substr instr trim upper lower lpad rpad replace

數學函式： round ceil floor truncate mod

日期函式：now curdate curtime year month monthname day hour minute second str_to_date  date_format

1、 字元函式

#length 獲取引數值的位元組個數

SELECT LENGTH('john');#4

SELECT LENGTH(' 張三丰hahaha');#15，utf8一個漢字佔3個位元組

#concat 拼接字串（用下劃線拼接）

SELECT CONCAT(last_name,'_',first_name) FROM manba;

#upper,lower SELECT UPPER('john');# 變大寫SELECT LOWER('JOHN');#變小寫

# 示例：將姓變大寫，名變小寫，拼接

SELECT CONCAT(UPPER(last_name),LOWER(first_name)) AS 姓名 FROM manba;

#substr ，substring

# 注意索引從1開始，下語句輸出:和李四

# 擷取從指定索引處後面所有字元

SELECT SUBSTR(' 張三和李四',3) out_put;

# 擷取從指定索引處指定字元長度的字元

# 下面語句輸出：張三

SELECT SUBSTR(' 張三和李四',1,2) out_put;

# 案例：姓名中首字元大寫，其他字元小寫，用_拼接，顯示出來

SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) output FROM manba;

#instr

# 返回子串的起始索引，找不到返回0

SELECT INSTR(' 楊不悔愛上了殷六俠','殷六俠') AS out_put

#trim 去掉首尾的

# 輸出張翠山

SELECT LENGTH(TRIM(' 張翠山')) AS out_put;

# 輸出張aaaa翠山

SELECT TRIM('a' FROM 'aaaa 張aaaa翠山aaaaaaaa') AS out_put;

#lpad  用指定的字元實現左填充指定長度

# 輸出*******殷素素

SELECT LPAD(' 殷素素',10,'*') AS out_put;

# 輸出：殷素

SELECT LPAD(' 殷素素',2,'*') AS out_put;

#rpad  用指定的字元實現右填充指定長度

# 輸出：殷素素ababababa

SELECT RPAD(' 殷素素',12,'ab') AS out_put;

#replace 替換

SELECT REPLACE(' 張無忌愛上了周芷若','周芷若','趙敏') AS out_put;

2、 數字函式

#round 四捨五入

SELECT ROUND(1.65);#2

SELECT ROUND(-1.45);#-1

SELECT ROUND(1.567,2);#1.57, 小數點後保留2位

#ceil 向上取整(返回>=該引數的最小整數)

SELECT CEIL(-1.02);#-1

SELECT CEIL(1.00);#1

#floor 向下取整，返回<=該引數的最大整數

SELECT FLOOR(-9.99);#-10

#truncate 截斷

SELECT TRUNCATE(1.65,1);#1.6;

#mod 取餘

mod(a,b) : a-a/b*b

mod(-10,-3) : -10-(-10)/(-3)*(-3)=-1;

SELECT MOD(10,-3);#1

3、 日期函式

#now: 返回當前系統日期加時間

SELECT NOW();

#curdate 返回當前系統日期，不包含時間

SELECT CURDATE();

#curtime() 返回當前時間，不包含日期

SELECT CURTIME();

# 可以獲取指定的部分，年，月，日，小時，分，秒

SELECT YEAR(NOW()) AS 年;

SELECT YEAR('1998-1-1') 年;

SELECT YEAR(hiredate) 年 FROM employees;

SELECT MONTH(NOW()) 月;

SELECT MONTHNAME(NOW()) 月;#顯示英文月份

#str_to_date 將日期格式的字元轉換成指定格式的日期

%Y 四位的年份

%y 2 位的年份

%m 月份 （01,02，...12）

%c 月份（1,2，..., 12）

%d 日

%H 小時（24）%h（12）

%i 分鐘  %s秒

SELECT STR_TO_DATE('9-13-1999','%m-%d-%Y') 日期;#1999-09-13

SELECT STR_TO_DATE ('2020-4-17','%Y-%c-%d') AS output;#2020-4-17

# 查詢入職日期為1992-4-3的員工資訊

SELECT * FROM employees WHERE hiredate='1992-4-3';

SELECT * FROM employees WHERE hiredate=STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_format 將日期轉換成字元

SELECT DATE_FORMAT(NOW(),'%y 年%m月%d日') AS output;#20年4月17日

# 查詢有獎金的員工名和入職日期(xx月/xx日 xx年)

SELECT last_name,DATE_FORMAT(hiredate,'%m 月/%d日 %Y年') 入職日期

FROM employees

WHERE commission_pct IS NOT NULL;

4、 聚合函式

# 簡單的使用

SELECT SUM(salary) FROM manba;

SELECT SUM(salary) 和,ROUND(AVG(salary),2) 平均 FROM manba;

SELECT MAX(salary) 最高,MIN(salary) 最低 FROM manba;

SELECT MIN(salary) FROM manba;

SELECT COUNT(salary) FROM manba;

# 引數支援哪些型別

SELECT SUM(last_name),AVG(last_name) FROM manba;

SELECT SUM(hiredate),AVG(hiredate) FROM manba;

# 無意義，不這樣用

SELECT MAX(last_name),MIN(last_name) FROM manba;

SELECT MAX(hiredate),MIN(hiredate) FROM manba;

# 支援

SELECT COUNT(last_name) FROM manba;# 計算非空的值 107 SELECT COUNT(commission_pct) FROM manba;#35

# 是否忽略null

SELECT SUM(commission_pct),AVG(commission_pct) FROM manba;

# 和distinct搭配

SELECT SUM(DISTINCT salary), SUM(salary) FROM manba;

SELECT COUNT(DISTINCT salary), COUNT(salary) FROM manba; 

#count 函式的詳細介紹

SELECT COUNT(salary) FROM manba;

SELECT COUNT(*) FROM manba;# 統計每一列的數目，即所有行數

SELECT COUNT(1) FROM manba;# 和上一語句效果一樣

#6 、和分組函式一同查詢的欄位有限制

SELECT AVG(salary),manba _id FROM manba;# 這個員工id查出來沒有意義

 

二、分組查詢

語法select分組函式（max，min等），列（要求出現在group by後面）fro表

【where 篩選條件】group by 分組的列表【order by】子句

注意：查詢列表必須特殊，要求是分組函式和group by後出現的欄位

# 簡單的分組查詢

# 案例1：查詢每個工種的最高工資

SELECT MAX(salary),job_id

FROM manba

GROUP BY job_id;

# 案例2：查詢每個位置上的部門個數

SELECT COUNT(*),location_id

FROM departments

GROUP BY location_id;

# 新增分組前篩選條件

# 案例1：查詢郵箱中包含a字元的，每個部門的平均工資

SELECT AVG(salary),department_id

FROM manba

WHERE email LIKE '%a%'

GROUP BY department_id;

# 案例2：查詢有獎金的每個領導手下員工的最高工資

SELECT MAX(salary),manager_id

FROM manba

WHERE commission_pct IS NOT NULL

GROUP BY manager_id;

# 新增分組後的篩選條件

# 案例1：查詢哪個部門的員工個數大於2

# ①查詢每個部門的員工個數

SELECT COUNT(*),department_id

FROM manba

GROUP BY department_id;

# ②根據1的結果進行篩選

SELECT COUNT(*),department_id

FROM manba

GROUP BY department_id

HAVING COUNT(*)>2;

# 案例2：查詢每個工種有獎金的員工的最高工資>12000的 工種編號和其最高工資

# ①查詢每個工種有獎金的員工的最高工資

SELECT MAX(salary),job_id

FROM manba

WHERE commission_pct IS NOT NULL

GROUP BY job_id;

# ②根據1的結果繼續篩選，最高工資>12000

SELECT MAX(salary),job_id

FROM manba

WHERE commission_pct IS NOT NULL

GROUP BY job_id

HAVING MAX(salary)>12000;

# 案例3：查詢領導編號>102的每個領導手下的最低工資>5000的領導編號

# ①查詢領導編號>102的每個領導手下的最低工資

SELECT MIN(salary),manager_id

FROM manba

WHERE manager_id>102

GROUP BY manager_id;

# ②在1的基礎上，最低工資>5000

SELECT MIN(salary),manager_id

FROM manba

WHERE manager_id>102

GROUP BY manager_id

HAVING MIN(salary)>5000

# 按表示式或函式分組

# 案例：按員工姓名的長度分組，查詢每一組的員工個數，篩選員工個數>5的

SELECT COUNT(*),LENGTH(last_name) len_name

FROM manba

GROUP BY len_name

HAVING COUNT(*)>5;

# 按多個欄位分組

# 案例：查詢每個部門每個工種的員工的平均工資

SELECT AVG(salary),department_id,job_id

FROM manba

GROUP BY department_id,job_id;

# 新增排序

## 案例：查詢每個部門每個工種的員工的平均工資,並將>10000的按高低排序

SELECT AVG(salary) a,department_id,job_id

FROM manba

GROUP BY department_id,job_id

HAVING a>10000

ORDER BY AVG(salary) DESC;

三、連結查詢（ 又稱多表查詢，當查詢的欄位來自多個表，就會用到連線查詢 ）

語法：select 查詢列表

    from 表1 別名 【連線型別】

    join 表2 別名 on 【連線條件】

    on 連線條件

    【where 篩選條件】

    【group by 分組】

    【having 篩選條件】

    【order by 排序列表】

分類：內連線：inner

外連線：左外 left 【outer】

               右外 right 【outer】

               全外 full 【outer】

交叉連線：cross

1、 內連線（等值連線、非等值連線、自連線）

# 等值連線

# 案例 1 ：查詢哪個部門的部門員工個數 > 3 的部門名，和員工個數，並按個數進行降序

SELECT COUNT ( * ), department_name

FROM manba e

INNER JOIN departments d

ON e . `department_id` = d . `department_id`

GROUP BY department_name

HAVING COUNT ( * ) > 3

ORDER BY COUNT ( * ) DESC ;

# 案例 2 ：查詢員工名、部門名、工種名，並按部門名降序（三表連線）

SELECT last_name , department_name , job_title

FROM manba 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 ;

# 非等值

# 查詢工資的個數 > 20 的級別個數，並且按工資級別降序

SELECT COUNT ( * ), grade_level

FROM manbae

INNER 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 manba e

JOIN manba m

ON e . `manager_id` = m . `manba_id` ;

# 加篩選：姓名中包含字元 k 的員工名字、上級名字

SELECT e . last_name , m . last_name

FROM manba e

JOIN manba m

ON e . `manager_id` = m . `manba _id`

WHERE e . `last_name` LIKE '%k%' ;

2 、外連線

# 查詢男朋友不在男生表的女生名

# 左外連線

SELECT be . name , bo . *

FROM beauty be

LEFT OUTER JOIN boys bo

ON be . boyfriend_id = bo . id

WHERE bo . `id` IS NULL ;

# 右外連線

SELECT be . name , bo . *

FROM boys bo

RIGHT OUTER JOIN beauty be

ON be . boyfriend_id = bo . id

WHERE bo . `id` IS NULL ;

 

四、子查詢（ 出現在其他語句內部的 select 語句，稱為子查詢或內查詢

外部的查詢語句，稱為主查詢或外查詢 ）

1. 查詢和Zlotkey相同部門的員工姓名和工資

SELECT last_name,salary

FROM manba

WHERE department_id=(

SELECT department_id

FROM manba

WHERE last_name='Zlotkey');

#2 、查詢工資比公司平均工資高的員工的員工號，姓名，工資

SELECT last_name,employee_id,salary

FROM manba

WHERE salary>(SELECT AVG(salary)FROM manba);

#3 、查詢各部門中工資比本部門平均工資高的員工號，姓名，工資

SELECT employee_id,last_name,salary,e.`department_id`

FROM manba e INNER JOIN(SELECT AVG(salary) ag,department_id

FROM manba

GROUP BY department_id)avg_dep

ON e.`department_id`=avg_dep.department_id

WHERE salary>avg_dep.ag;

 

#4 、查詢，和姓名中包含字母u的員工在相同部門的員工，的員工號和姓名

SELECT last_name,manba_id

FROM manba

WHERE department_id IN(

SELECT DISTINCT department_id

FROM manba

WHERE last_name LIKE '%u%');

#5 、查詢在部門的location id為1700的部門工作的員工的員工號

SELECT manba_id

FROM manba

WHERE department_id=ANY(

SELECT department_id

FROM departments

WHERE location_id=1700);

#6 、查詢管理者是king的員工姓名和工資

SELECT last_name,salary

FROM manba

WHERE manager_id IN(

SELECT manba_id

FROM manba

WHERE last_name='K_ing');

#7 、查詢工資最高的員工的姓名，要求first和last_name顯示為一列，列名為姓名

SELECT CONCAT(first_name,last_name) " 姓名"

FROM manba WHERE salary=(SELECT MAX(salary)

FROM manba

 

 

【編測編學】  教學內容為：

 

測試基礎知識、專案實戰、測試管理、敏捷測試、探索式測試、APP測試、Linux、資料庫、測試環境搭建、Python程式設計、WEB端UI自動化測試、APP端UI自動化、介面功能測試、效能測試、介面自動化測試、Jenkins持續整合  等內容。

 

 

透過學習，學員能  掌握軟體測試企業最新技術，對標一線網際網路企業專案要求，使學員達到中高階測試工程師的水平，畢業後可快速融入企業實際工作中。

 

不管你是  文科生、專科生、0基礎、女同學  都可以輕鬆學會！