推薦學Java——數據表高級操作

前言

上一節內容學習了關于數據表的基本操作，也就是針對單表的增刪改查以及創建和刪除，而在實際開發中，往往是多表聯合操作，尤其是插入和查詢用的最多，而這兩步都要經過一個“篩選”的過程，這個過程要根據具體業務邏輯，綜合不同的表，查詢后決定是否滿足插入或其他條件。

本節內容涉及的廣泛一些，我們需要創建多個表，進行復雜一點的操作，數據庫管理工具這里使用的是 Navicat 12 ，還有很多類似的軟件，比如：sqlyog、SQL-Front等等。

Navicat 12 安裝

官網：https://www.navicat.com.cn/download/navicat-premium

解決 Navicat12 鏈接MySQL的錯誤：2059 - authentication plugin 'caching_sha2_password’

解決方案：https://jingyan.baidu.com/article/0aa22375e7966ac8cc0d64b3.html

這里特別注意最后一句命令：ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root'; BY后面的字符串就是你的 mysql 密碼，如果你沒有正確輸入，那么測試鏈接數據庫的時候會提示這個錯誤：1045 Access denied for user 'root'@'localhost' (using password: YES) 其實就相當于修改了 root 賬戶的密碼。

本節涉及到表結構

字段分別是：部門編號、部門名稱、部門所在地址位置。

字段分別是：員工編號、員工名稱、職位、上級、入職日期、薪資、補貼、所屬部門編號、所使用的語言。

字段分別是：等級、最低工資、最高工資。

以上表中的數據大家可以自己修改和填充

工具和sql文件在公眾號 推薦學java 回復navicat獲取。

常見的 MySQL 內置函數

與之對應，還有多行處理函數，前者是說有一條輸入，對應一條輸出結果；后者則是多行輸入，對應一條輸出結果，比如：求和函數sum() 。

LOWER()：轉小寫字母

select lower(列名) from 表名; // 也可以帶表名 select lower(列名) as l_Name from 表名;

UPPER()：轉大寫字母

LENGTH(列名)：字符數量，就是字段的長度

CONCAT(string1,string2)：字符串拼接，不能使用 + .

// 將列 LANGUAGE 首字母轉大寫 select concat(upper(substr(language,1,1)),substr(language,2,length(language)-1)) as '語言' from emp;

INSTR()：字符出現的位置

INSERT()：插入/替換字符

REPLACE()：替換字符

SUBSTR(字符串,開始截取的位置,結束的位置)：截取字符串。下標是從1開始的

select substr(ename,1,1) from emp; select ename from emp where substr(ename,1,1) like '老%';

SUBSTRING(字符串,開始截取的位置,偏移量)：截取字符串

LPAD(字符串,填充后字符串總長度,要填充的字符串)：在字符串的左側填充給定字符串。

RPAD(字符串，填充后字符串總長度,要填充的字符串)：在字符串的右側填充給定字符串。

TRIM()：去掉字符串的首位空格

ROUND(列名,保留位數) ：四舍五入，根據指定位數來保留小數位。

// 十位四舍五入，沒有小數位 select round(sal,-1) as '薪資' from emp; // 去尾，只保留整數部分 select round(sal,0) as '薪資' from emp; // 四舍五入，保留一位小數（如果本來只有一位小數，則會直接保留） select round(sal,1) as '薪資' from emp; // 四舍五入，保留兩位小數 select round(sal,2) as '薪資' from emp;

ifnull(值1, 值2) ：空處理函數，可以將 null 轉換成一個具體值。 如果值1為null，那么會取值2參與運算。

select (sal + IFNULL(comm,0)) *12 as '年薪' from emp;

format(列名,'格式') ：數字格式化，例如顯式千分位：

select e.ename,format(e.sal,'$999,999') as sal from emp e;

連接查詢

上一節內容涉及的都是但張表操作和基本的sql關鍵詞的使用，下面的內容涉及多張表之間的查詢操作，以及相關SQL專業知識。

SQL92：1992年出現的語法

SQL99：1999年出現的語法

內連接

等值連接

非等值連接

自連接

外連接

左外連接（左連接）

右外連接（右鏈接）

全連接（基本不用，可不了解）

當多張表關聯查詢時，如果不加任何約束條件，那么查詢結果是多張表記錄的乘機。

需求：

查詢每個員工所在的部門名稱，要求顯示員工姓名、部門名稱。

分析：

員工表中只有部門編號，部門名稱是在部門表中，所以就需要兩張表關聯查詢。

SQL語句：

select e.ename, d.dname from emp e join dept d on e.deptno = d.deptno; // on 后的條件是等值的，叫做等值連接

需求：

找出每個員工的薪資等級，要求顯示員工姓名、薪資、薪資等級。

分析：

薪資等級是一張單獨的表，員工姓名和薪資在員工表中，同樣需要兩張表聯合查詢。

SQL語句：

// on 后的條件不是等值的，叫做非等值連接 select e.ename, e.sal, s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;

需求：

找出每個員工的領導的姓名，顯示員工姓名、領導姓名。

分析：

員工表中有員工姓名和員工的上級領導編號，領導同樣也是員工，也存在員工表中，所以需要將一張表（員工表）看做兩張表來查詢，故稱作這種情況為自連接查詢。

SQL語句：

select e.ename as '員工姓名',em.ename as '領導姓名' from emp e join emp em on e.mgr = em.empno;

需求：

找出每個員工所在的部門，顯示員工姓名、部門名稱。

分析：

員工在員工表中，存在部門編號，部門名稱是在部門表中。

SQL語句：

select e.ename, d.dname from emp e right join dept d on e.deptno = d.deptno;

帶有right的是右外連接，又稱右鏈接，此時查詢會以right右邊的表作為主表來查詢，任何一個右鏈接查詢都可以寫出對應的左連接查詢，反之亦然。

對應左連接查詢SQL：

select e.ename, d.dname from dept d left join emp e on e.deptno = d.deptno;

總結：

外連接的查詢結果一定是 >= 內連接的查詢結果。

一條SQL中可以同時出現內連接和外連接，混合使用沒有問題。

語法格式：

select a.xxx,b.xxx,c.xxx from a表 a join b表 b on a和b的鏈接條件 join c表 c on a和c的鏈接條件

**案例1：**三張表關聯查詢

找出每個員工的部門名稱以及工資等級，顯示員工姓名、薪資、部門名稱、薪資等級。

SQL語句：

select e.ename,e.sal,d.dname,s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and s.hisal;

案例2： 三張表鏈接+左連接查詢

找出每個員工的部門名稱以及工資等級，顯示員工姓名、薪資、領導名、部門名稱、薪資等級。

SQL語句：

select e.ename, em.ename as '上級領導', e.sal, d.dname, s.grade from emp e join dept d on e.deptno = d.deptno join salgrade s on e.sal between s.losal and hisal left join emp em on e.mgr = em.empno;

子查詢

select語句中嵌套select語句，那么被嵌套的select語句稱為子查詢。

select ...(select) from ...(select) where ...(select)

案例：

找出比最低工資（員工的工資，不是工資登記表中的最低工資）高的員工姓名和工資。

SQL語句：

select e.ename,e.sal from emp e where e.sal > (select min(sal) from emp);

from子句中的子查詢結果可以當做一張臨時表來使用，因為from后面本身就是表名

案例：

找出每個工作崗位的平均薪資的工資等級。

SQL語句：

select s.job,s.avg,sal.grade from (select job, avg(sal) avg from emp group by job) s join salgrade sal on s.avg between sal.losal and sal.hisal ;

案例：

找出每個員工的部門名稱，顯示員工姓名、部門名稱。

SQL語句：

select e.ename, (select d.dname from dept d where d.deptno = e.deptno) d from emp e;

union用法

將查詢結果集進行合并，比如：兩條select語句的結果拼接在一起。

案例：

將 job 為 四絕之一 和 Java全棧工程師 的員工查出來，顯示員工名、職位名。

SQL語法：

select e.ename, e.job from emp e where e.job = '四絕之一' union select e.ename,e.job from emp e where e.job ='Java全棧工程師';

就這個需求而言，SQL查詢可以用之前學的 or 或者 in(值1,值2) 都可以實現結果，但在多表鏈接查詢時 union 效率更高。

union注意事項：

在進行結果集合并的時候，要求結果集的列數相同

limit用法（必須掌握）

將查詢結果集的一部分取出來，通常多用于分頁查詢中。

用法示例：

-- 將所有職位查詢來降序排列，只顯示前5條 select * from emp order by job desc limit 5; -- 將查詢結果 從第四條開始，向后取5條數據出來 select e.ename, e.sal from emp e order by sal desc limit 3,5;

limit注意事項：

limit startIndex,length. startIndex如果不寫，默認從第一條開始

mySQL當中，limit 在 order by 之后執行。

通用分頁公式：

limit (pageNo-1)*pageSize, pageSize;

存儲引擎（了解即可）

這個名詞是MySQL特有的，Oracle中也有類似技術，但不叫這個名字。存儲引擎就是一張表存儲/組織數據的方式。

MySQL默認的存儲引擎是 InnoDB

MySQL默認的字符編碼是 utf8

創建引擎語法格式：

create table t_studying ( id int primary key auto_increment, name varchar(255) not null, grade int not null ) engine = InnoDB default charset=utf8;

查看MySQL支持的存儲引擎，命令：show engines \G

這道理，查詢相關高級知識已經完結了，下面內容是偏理論性的，但也很重要，屬于高級操作，請結合知識導圖查看。

事務（重點）

一個事務就是一個完整的業務邏輯。

只有增、刪、改才會有事務一說。

這和存儲引擎有關系，MySQL使用 InnoDB ，而 InnoDB 對沒次操作的DML語句都會有日志記錄文件，事務的執行結果要么多條語句全部失敗，要么全部成功，無論是什么結果都會清空日志文件，所以事務支持回滾。

原子性

一致性

隔離性

持久性

讀未提交（Read Uncommited）：事務A只能讀取到事務B未提交的數據，這種隔離級別基本不用。

讀已提交（Read Commited）：事務A只能讀取到事務B已提交的數據，最常用的隔離級別，Oracle數據庫默認隔離級別。

可重復讀（Repeatable Read）：事務A開啟事務后，每次讀到的數據都是相同的，就是事務B已經提交事務了，也同樣讀取不到事務B提交后的數據。MySQL數據庫默認隔離級別。

序列化（Serializable）：隔離級別最高，效率最低，每次讀取到的都是最真實的數據，但事務不能并行，需要排隊。

select @@tx_isolation

索引

索引是在數據庫表的字段上添加的，是為了提高查詢效率提供的一種機制。一個字段可以添加一個索引，當然也可以多個字段聯合起來添加索引。

在任何數據庫中，主鍵都會自動添加索引

在MySQL中，如果一個字段有 unique 約束的話，也會自動添加索引

在任何數據庫中，任意一條記錄在硬盤上都有對應的物理存儲編號

不同的存儲引擎索引以不同的形式存在。在 InnoDB 中，索引存儲在一個邏輯名叫 tablespace 中，是一種二叉樹（B-Tree）結構。

數據量龐大

該字段經常出現在 where 后面，一條件的形式存在，也就是經常被掃描

該字段很少的MDL（insert delete update）操作（因為DML之后，索引需要重新排序）

語法：

create index 索引名 on 表名(字段名);

語法：

drop index 索引名 on 表名;

語法：

explain sql查詢語句;

說明： 通過查看結果中的 type 和 rows 值來做判斷，前者如果是 ref 則說明是通過索引來掃描的。

模糊匹配當中以 % 開頭了

使用 or 的時候，要能使用索引，那么要求 or 兩邊的字段都要有索引，如果只要一邊有索引，那么索引會失效

使用復合索引（兩個或多個字段聯合起來添加一個索引）的時候，沒有使用左側的列查找，索引失效

在 where 當中，索引列參加了運算，索引失效

在 where 當中，索引列使用了函數，索引失效

視圖

不同角度看待同一份數據。

只有 DQL 語句才能以 view 的形式創建。

create view 視圖名稱 as select語句;

drop view 視圖名稱;

我們可以面向視圖對象進行增刪改查，這將會導致原表數據被操作。方便、簡化開發、利于維護

數據庫設計三范式

要求所有表都必須有主鍵，每一個字段都是原子性不可再分。

在第一范式的基礎上，要求每個非主鍵字段完全依賴主鍵，不要產生部分依賴。

在第二范式的基礎上，要求所有非主鍵字段完全依賴主鍵，不要產生傳遞依賴。

總結

關于多表的操作，最常用的就是查詢，且是最重要的內容，本章節內容會影響到我們后面的進階內容，需要加強練習。這里建議大家在學習的過程中寫 SQL 語句在相應的工具中進行，本文開始介紹了工具，還沒下載的可以去公眾號獲取。

Java MySQL SQL 數據庫

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。