Java教程:JavaWeb ---MySQL高級

在這里插## 標題入圖片描述

> 						大家好,我是程序員小羊!

?博客主頁: https://blog.csdn.net/m0_63815035?type=blog

💗《博客內容》:大數據開發、.NET、Java.測試開發、Python、Android、Go、Node、Android前端小程序等相關領域知識
📢博客專欄: https://blog.csdn.net/m0_63815035/category_11954877.html
📢歡迎點贊 👍 收藏 ?留言 📝
📢本文為學習筆記資料,如有侵權,請聯系我刪除,疏漏之處還請指正🙉
📢大廈之成,非一木之材也;大海之闊,非一流之歸也?

在這里插入圖片描述

前言:

歡迎來到小羊的《Java教程:JavaWeb —MySQL高級》系列的學習之旅!無論你是初學者還是希望深化對Java編程語言理解的開發者,這系列教程都將為你提供系統化的知識和實用的技能,幫助你從零基礎起步,逐步掌握Java編程的核心概念和技術。希望大家多多支持小羊吧!!讓我們一起開啟這段精彩的編程旅程,從零開始Go Go

目錄

  • 前言:
  • 本節目標
  • 1,約束
    • 1.1 概念
    • 1.2 分類
    • 1.3 非空約束
    • 1.4 唯一約束
    • 1.5 主鍵約束
    • 1.6 默認約束
    • 1.7 約束練習
  • 1.8 外鍵約束
    • 1.8.1 概述
    • 1.8.2 語法
    • 1.8.3 練習
  • 2,數據庫設計
    • 2.1 數據庫設計簡介
    • 2.3 表關系(多對多)
    • 2.4 表關系(一對一)
    • 2.5 數據庫設計案例
  • 3,多表查詢
    • 3.1 內連接查詢
    • 3.2 外連接查詢
    • 3.3 子查詢
    • 3.4 案例
  • 4,事務
    • 4.1 概述
    • 4.2 語法
    • 4.4 事務的四大特征

本節目標

  • 掌握約束的使用
  • 掌握表關系及建表原則
  • 重點掌握多表查詢操作
  • 掌握事務操作

1,約束

在這里插入圖片描述

上面表中可以看到表中數據存在一些問題:

  • id 列一般是用標示數據的唯一性的,而上述表中的id為1的有三條數據,并且 馬花疼 沒有id進行標示
  • 柳白 這條數據的age列的數據是3000,而人也不可能活到3000歲
  • 馬運 這條數據的math數學成績是-5,而數學學得再不好也不可能出現負分
  • 柳青 這條數據的english列(英文成績)值為null,而成績即使沒考也得是0分

針對上述數據問題,我們就可以從數據庫層面在添加數據的時候進行限制,這個就是約束。

1.1 概念

  • 約束是作用于表中列上的規則,用于限制加入表的數據
    例如:我們可以給id列加約束,讓其值不能重復,不能為null值。
  • 約束的存在保證了數據庫中數據的正確性、有效性和完整性
    添加約束可以在添加數據的時候就限制不正確的數據,年齡是3000,數學成績是-5分這樣無效的數據,繼而保障數據的完整性。

1.2 分類

  • 非空約束: 關鍵字是 NOT NULL
    保證列中所有的數據不能有null值。
    例如:id列在添加 馬花疼 這條數據時就不能添加成功。
  • 唯一約束:關鍵字是 UNIQUE
    保證列中所有數據各不相同。
    例如:id列中三條數據的值都是1,這樣的數據在添加時是絕對不允許的。
  • 主鍵約束: 關鍵字是 PRIMARY KEY
    主鍵是一行數據的唯一標識,要求非空且唯一。一般我們都會給沒張表添加一個主鍵列用來唯一標識數據。
    例如:上圖表中id就可以作為主鍵,來標識每條數據。那么這樣就要求數據中id的值不能重復,不能為null值。
  • 檢查約束: 關鍵字是 CHECK
    保證列中的值滿足某一條件。
    例如:我們可以給age列添加一個范圍,最低年齡可以設置為1,最大年齡就可以設置為300,這樣的數據才更合理些。

注意:MySQL不支持檢查約束。
這樣是不是就沒辦法保證年齡在指定的范圍內了?從數據庫層面不能保證,以后可以在java代碼中進行限制,一樣也可以實現要求。

  • 默認約束: 關鍵字是 DEFAULT
    保存數據時,未指定值則采用默認值。
    例如:我們在給english列添加該約束,指定默認值是0,這樣在添加數據時沒有指定具體值時就會采用默認給定的0。
  • 外鍵約束: 關鍵字是 FOREIGN KEY
    外鍵用來讓兩個表的數據之間建立鏈接,保證數據的一致性和完整性。
    外鍵約束現在可能還不太好理解,后面我們會重點進行講解。

1.3 非空約束

  • 概念
    非空約束用于保證列中所有數據不能有NULL值
  • 語法
    • 添加約束
-- 創建表時添加非空約束
CREATE TABLE 表名(列名 數據類型 NOT NULL,); 
-- 建完表后添加非空約束
ALTER TABLE 表名 MODIFY 字段名 數據類型 NOT NULL;
  -- 刪除約束ALTER TABLE 表名 MODIFY 字段名 數據類型;

1.4 唯一約束

  • 概念
    唯一約束用于保證列中所有數據各不相同
  • 語法
    • 添加約束
   -- 創建表時添加唯一約束CREATE TABLE 表名(列名 數據類型 UNIQUE [AUTO_INCREMENT],-- AUTO_INCREMENT: 當不指定值時自動增長); CREATE TABLE 表名(列名 數據類型,[CONSTRAINT] [約束名稱] UNIQUE(列名)); 
-- 建完表后添加唯一約束
ALTER TABLE 表名 MODIFY 字段名 數據類型 UNIQUE;
  • 刪除約束
ALTER TABLE 表名 DROP INDEX 字段名;

1.5 主鍵約束

  • 概念
    主鍵是一行數據的唯一標識,要求非空且唯一
    一張表只能有一個主鍵
  • 語法
    • 添加約束
      – 創建表時添加主鍵約束
CREATE TABLE 表名(列名 數據類型 PRIMARY KEY [AUTO_INCREMENT],); 
CREATE TABLE 表名(列名 數據類型,[CONSTRAINT] [約束名稱] PRIMARY KEY(列名)
); 
        -- 建完表后添加主鍵約束ALTER TABLE 表名 ADD PRIMARY KEY(字段名);
  - 刪除約束ALTER TABLE 表名 DROP PRIMARY KEY;

1.6 默認約束

  • 概念
    保存數據時,未指定值則采用默認值
  • 語法
    • 添加約束
  -- 創建表時添加默認約束CREATE TABLE 表名(列名 數據類型 DEFAULT 默認值,); 
-- 建完表后添加默認約束
ALTER TABLE 表名 ALTER 列名 SET DEFAULT 默認值;
  - 刪除約束ALTER TABLE 表名 ALTER 列名 DROP DEFAULT;

1.7 約束練習

根據需求,為表添加合適的約束

-- 員工表
CREATE TABLE emp (id INT,  -- 員工id,主鍵且自增長ename VARCHAR(50), -- 員工姓名,非空且唯一joindate DATE,  -- 入職日期,非空salary DOUBLE(7,2),  -- 工資,非空bonus DOUBLE(7,2)  -- 獎金,如果沒有將近默認為0
);

上面一定給出了具體的要求,我們可以根據要求創建這張表,并為每一列添加對應的約束。建表語句如下:

DROP TABLE IF EXISTS emp;
-- 員工表
CREATE TABLE emp (id INT PRIMARY KEY, -- 員工id,主鍵且自增長ename VARCHAR(50) NOT NULL UNIQUE, -- 員工姓名,非空并且唯一joindate DATE NOT NULL , -- 入職日期,非空salary DOUBLE(7,2) NOT NULL , -- 工資,非空bonus DOUBLE(7,2) DEFAULT 0 -- 獎金,如果沒有獎金默認為0
);

通過上面語句可以創建帶有約束的 emp 表,約束能不能發揮作用呢。接下來我們一一進行驗證,先添加一條沒有問題的數據

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'張三','1999-11-11',8800,5000);
  • 驗證主鍵約束,非空且唯一
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'張三','1999-11-11',8800,5000);

執行結果如下:
在這里插入圖片描述

從上面的結果可以看到,字段 id 不能為null。那我們重新添加一條數據,如下:

INSERT INTO emp(id,ename,joindate,salary,bonus) values(1,'張三','1999-11-11',8800,5000);

執行結果如下:
在這里插入圖片描述

從上面結果可以看到,1這個值重復了。所以主鍵約束是用來限制數據非空且唯一的。那我們再添加一條符合要求的數據

INSERT INTO emp(id,ename,joindate,salary,bonus) values(2,'李四','1999-11-11',8800,5000);

執行結果如下:
在這里插入圖片描述

  • 驗證非空約束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,null,'1999-11-11',8800,5000);

執行結果如下:
在這里插入圖片描述

從上面結果可以看到,ename 字段的非空約束生效了。

  • 驗證唯一約束
INSERT INTO emp(id,ename,joindate,salary,bonus) values(3,'李四','1999-11-11',8800,5000);

執行結果如下:

在這里插入圖片描述

從上面結果可以看到,ename 字段的唯一約束生效了。

  • 驗證默認約束
  INSERT INTO emp(id,ename,joindate,salary) values(3,'王五','1999-11-11',8800);

執行完上面語句后查詢表中數據,如下圖可以看到王五這條數據的bonus列就有了默認值0。

在這里插入圖片描述

注意:默認約束只有在不給值時才會采用默認值。如果給了null,那值就是null值。

如下:

INSERT INTO emp(id,ename,joindate,salary,bonus) values(4,'趙六','1999-11-11',8800,null);

執行完上面語句后查詢表中數據,如下圖可以看到趙六這條數據的bonus列的值是null。
在這里插入圖片描述

  • 驗證自動增長: auto_increment 當列是數字類型 并且唯一約束

重新創建 emp 表,并給id列添加自動增長

-- 員工表
CREATE TABLE emp (id INT PRIMARY KEY auto_increment, -- 員工id,主鍵且自增長ename VARCHAR(50) NOT NULL UNIQUE, -- 員工姓名,非空并且唯一joindate DATE NOT NULL , -- 入職日期,非空salary DOUBLE(7,2) NOT NULL , -- 工資,非空bonus DOUBLE(7,2) DEFAULT 0 -- 獎金,如果沒有獎金默認為0
);

接下來給emp添加數據,分別驗證不給id列添加值以及給id列添加null值,id列的值會不會自動增長:

INSERT INTO emp(ename,joindate,salary,bonus) values('趙六','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'趙六2','1999-11-11',8800,null);
INSERT INTO emp(id,ename,joindate,salary,bonus) values(null,'趙六3','1999-11-11',8800,null);

1.8 外鍵約束

1.8.1 概述

外鍵用來讓兩個表的數據之間建立鏈接,保證數據的一致性和完整性。

如何理解上面的概念呢?如下圖有兩張表,員工表和部門表:
在這里插入圖片描述

員工表中的dep_id字段是部門表的id字段關聯,也就是說1號學生張三屬于1號部門研發部的員工。現在我要刪除1號部門,就會出現錯誤的數據(員工表中屬于1號部門的數據)。而我們上面說的兩張表的關系只是我們認為它們有關系,此時需要通過外鍵讓這兩張表產生數據庫層面的關系,這樣你要刪除部門表中的1號部門的數據將無法刪除。

1.8.2 語法

  • 添加外鍵約束
-- 創建表時添加外鍵約束
CREATE TABLE 表名(列名 數據類型,[CONSTRAINT] [外鍵名稱] FOREIGN KEY(外鍵列名) REFERENCES 主表(主表列名) 
); 
-- 建完表后添加外鍵約束
ALTER TABLE 表名 ADD CONSTRAINT 外鍵名稱 FOREIGN KEY (外鍵字段名稱) REFERENCES 主表名稱(主表列名稱);
- 刪除外鍵約束ALTER TABLE 表名 DROP FOREIGN KEY 外鍵名稱;

1.8.3 練習

根據上述語法創建員工表和部門表,并添加上外鍵約束:

-- 刪除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;-- 部門表
CREATE TABLE dept(id int primary key auto_increment,dep_name varchar(20),addr varchar(20)
);
-- 員工表 
CREATE TABLE emp(id int primary key auto_increment,name varchar(20),age int,dep_id int,-- 添加外鍵 dep_id,關聯 dept 表的id主鍵CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)	
);

添加數據

-- 添加 2 個部門
insert into dept(dep_name,addr) values
('研發部','廣州'),('銷售部', '深圳');-- 添加員工,dep_id 表示員工所在的部門
INSERT INTO emp (NAME, age, dep_id) VALUES 
('張三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('趙六', 20, 2),
('孫七', 22, 2),
('周八', 18, 2);

此時刪除 研發部 這條數據,會發現無法刪除。

刪除外鍵

alter table emp drop FOREIGN key fk_emp_dept;

重新添加外鍵

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

2,數據庫設計

2.1 數據庫設計簡介

  • 軟件的研發步驟
    在這里插入圖片描述

  • 數據庫設計概念

    • 數據庫設計就是根據業務系統的具體需求,結合我們所選用的DBMS,為這個業務系統構造出最優的數據存儲模型。
    • 建立數據庫中的表結構以及表與表之間的關聯關系的過程。
    • 有哪些表?表里有哪些字段?表和表之間有什么關系?
  • 數據庫設計的步驟

    • 需求分析(數據是什么? 數據具有哪些屬性? 數據與屬性的特點是什么)

    • 邏輯分析(通過ER圖對數據庫進行邏輯建模,不需要考慮我們所選用的數據庫管理系統)
      如下圖就是ER(Entity/Relation)圖:
      在這里插入圖片描述

    • 物理設計(根據數據庫自身的特點把邏輯設計轉換為物理設計)

    • 維護設計(1.對新的需求進行建表;2.表優化)

  • 表關系

    • 一對一

      • 如:用戶 和 用戶詳情
      • 一對一關系多用于表拆分,將一個實體中經常使用的字段放一張表,不經常使用的字段放另一張表,用于提升查詢性能
        在這里插入圖片描述

      上圖左邊是用戶的詳細信息,而我們真正在展示用戶信息時最長用的則是上圖右邊紅框所示,所以我們會將詳細信息查分成兩周那個表。

    • 一對多

      • 如:部門 和 員工
      • 一個部門對應多個員工,一個員工對應一個部門。如下圖:
        在這里插入圖片描述
    • 多對多

      • 如:商品 和 訂單
      • 一個商品對應多個訂單,一個訂單包含多個商品。如下圖:
        在這里插入圖片描述

2.2 表關系(一對多)

  • 一對多

    • 如:部門 和 員工
    • 一個部門對應多個員工,一個員工對應一個部門。
  • 實現方式
    在多的一方建立外鍵,指向一的一方的主鍵

  • 案例
    我們還是以 員工表 和 部門表 舉例:
    在這里插入圖片描述

    經過分析發現,員工表屬于多的一方,而部門表屬于一的一方,此時我們會在員工表中添加一列(dep_id),指向于部門表的主鍵(id):
    在這里插入圖片描述

    建表語句如下:

 -- 刪除表DROP TABLE IF EXISTS tb_emp;DROP TABLE IF EXISTS tb_dept;-- 部門表CREATE TABLE tb_dept(id int primary key auto_increment,dep_name varchar(20),addr varchar(20));-- 員工表 CREATE TABLE tb_emp(id int primary key auto_increment,name varchar(20),age int,dep_id int,-- 添加外鍵 dep_id,關聯 dept 表的id主鍵CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES tb_dept(id)	);

查看表結構模型圖:
在這里插入圖片描述

2.3 表關系(多對多)

  • 多對多

    • 如:商品 和 訂單
    • 一個商品對應多個訂單,一個訂單包含多個商品
  • 實現方式
    建立第三張中間表,中間表至少包含兩個外鍵,分別關聯兩方主鍵

  • 案例
    我們以 訂單表 和 商品表 舉例:
    在這里插入圖片描述

    經過分析發現,訂單表和商品表都屬于多的一方,此時需要創建一個中間表,在中間表中添加訂單表的外鍵和商品表的外鍵指向兩張表的主鍵:
    在這里插入圖片描述

    建表語句如下:


```sql```sql
-- 刪除表DROP TABLE IF EXISTS tb_order_goods;DROP TABLE IF EXISTS tb_order;DROP TABLE IF EXISTS tb_goods;-- 訂單表CREATE TABLE tb_order(id int primary key auto_increment,payment double(10,2),payment_type TINYINT,status TINYINT);-- 商品表CREATE TABLE tb_goods(id int primary key auto_increment,title varchar(100),price double(10,2));-- 訂單商品中間表CREATE TABLE tb_order_goods(id int primary key auto_increment,order_id int,goods_id int,count int);-- 建完表后,添加外鍵alter table tb_order_goods add CONSTRAINT fk_order_id FOREIGN key(order_id) REFERENCES tb_order(id);alter table tb_order_goods add CONSTRAINT fk_goods_id FOREIGN key(goods_id) REFERENCES tb_goods(id);

查看表結構模型圖:
在這里插入圖片描述

2.4 表關系(一對一)

  • 一對一

    • 如:用戶 和 用戶詳情
    • 一對一關系多用于表拆分,將一個實體中經常使用的字段放一張表,不經常使用的字段放另一張表,用于提升查詢性能
  • 實現方式
    在任意一方加入外鍵,關聯另一方主鍵,并且設置外鍵為唯一(UNIQUE)

  • 案例
    我們以 用戶表 舉例:
    在這里插入圖片描述

    而在真正使用過程中發現 id、photo、nickname、age、gender 字段比較常用,此時就可以將這張表查分成兩張表。

在這里插入圖片描述

建表語句如下:

create table tb_user_desc (id int primary key auto_increment,city varchar(20),edu varchar(10),income int,status char(2),des varchar(100)
);create table tb_user (id int primary key auto_increment,photo varchar(100),nickname varchar(50),age int,gender char(1),desc_id int unique,-- 添加外鍵CONSTRAINT fk_user_desc FOREIGN KEY(desc_id) REFERENCES tb_user_desc(id)	
);

查看表結構模型圖:

在這里插入圖片描述

2.5 數據庫設計案例

根據下圖設計表及表和表之間的關系:
在這里插入圖片描述

經過分析,我們分為 專輯表 曲目表 短評表 用戶表 4張表。
在這里插入圖片描述

一個專輯可以有多個曲目,一個曲目只能屬于某一張專輯,所以專輯表和曲目表的關系是一對多

一個專輯可以被多個用戶進行評論,一個用戶可以對多個專輯進行評論,所以專輯表和用戶表的關系是 多對多

一個用戶可以發多個短評,一個短評只能是某一個人發的,所以用戶表和短評表的關系是 一對多

在這里插入圖片描述

3,多表查詢

多表查詢顧名思義就是從多張表中一次性的查詢出我們想要的數據。我們通過具體的sql給他們演示,先準備環境

DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;# 創建部門表CREATE TABLE dept(did INT PRIMARY KEY AUTO_INCREMENT,dname VARCHAR(20));# 創建員工表CREATE TABLE emp (id INT PRIMARY KEY AUTO_INCREMENT,NAME VARCHAR(10),gender CHAR(1), -- 性別salary DOUBLE, -- 工資join_date DATE, -- 入職日期dep_id INT,FOREIGN KEY (dep_id) REFERENCES dept(did) -- 外鍵,關聯部門表(部門表的主鍵));-- 添加部門數據INSERT INTO dept (dNAME) VALUES ('研發部'),('市場部'),('財務部'),('銷售部');-- 添加員工數據INSERT INTO emp(NAME,gender,salary,join_date,dep_id) VALUES('孫悟空','男',7200,'2013-02-24',1),('豬八戒','男',3600,'2010-12-02',2),('唐僧','男',9000,'2008-08-08',2),('白骨精','女',5000,'2015-10-07',3),('蜘蛛精','女',4500,'2011-03-14',1),('小白龍','男',2500,'2011-02-14',null);	

執行下面的多表查詢語句

select * from emp , dept;  -- 從emp和dept表中查詢所有的字段數據

結果如下:
在這里插入圖片描述

從上面的結果我們看到有一些無效的數據,如 孫悟空 這個員工屬于1號部門,但也同時關聯的2、3、4號部門。所以我們要通過限制員工表中的 dep_id 字段的值和部門表 did 字段的值相等來消除這些無效的數據,

select * from emp , dept where emp.dep_id = dept.did;

執行后結果如下:
在這里插入圖片描述

上面語句就是連接查詢,那么多表查詢都有哪些呢?

  • 連接查詢
    在這里插入圖片描述

    • 內連接查詢 :相當于查詢AB交集數據
    • 外連接查詢
      • 左外連接查詢 :相當于查詢A表所有數據和交集部門數據
      • 右外連接查詢 : 相當于查詢B表所有數據和交集部分數據
  • 子查詢

3.1 內連接查詢

  • 語法
-- 隱式內連接
SELECT 字段列表 FROM1,2WHERE 條件;-- 顯示內連接
SELECT 字段列表 FROM1 [INNER] JOIN2 ON 條件;

內連接相當于查詢 A B 交集數據
在這里插入圖片描述

  • 案例
    • 隱式內連接
SELECT*
FROMemp,dept
WHEREemp.dep_id = dept.did;

執行上述語句結果如下:
在這里插入圖片描述

  • 查詢 emp的 name, gender,dept表的dname
SELECTemp. NAME,emp.gender,dept.dname
FROMemp,dept
WHEREemp.dep_id = dept.did;

執行語句結果如下:
在這里插入圖片描述

上面語句中使用表名指定字段所屬有點麻煩,sql也支持給表指別名,上述語句可以改進為

SELECTt1. NAME,t1.gender,t2.dname
FROMemp t1,dept t2
WHEREt1.dep_id = t2.did;
  • 顯式內連接
select * from emp inner join dept on emp.dep_id = dept.did;-- 上面語句中的inner可以省略,可以書寫為如下語句select * from emp  join dept on emp.dep_id = dept.did;

執行結果如下:
在這里插入圖片描述

3.2 外連接查詢

  • 語法
  -- 左外連接SELECT 字段列表 FROM1 LEFT [OUTER] JOIN2 ON 條件;-- 右外連接SELECT 字段列表 FROM1 RIGHT [OUTER] JOIN2 ON 條件;

左外連接:相當于查詢A表所有數據和交集部分數據
右外連接:相當于查詢B表所有數據和交集部分數據
在這里插入圖片描述

  • 案例
    • 查詢emp表所有數據和對應的部門信息(左外連接)
 select * from emp left join dept on emp.dep_id = dept.did;

執行語句結果如下:
在這里插入圖片描述

結果顯示查詢到了左表(emp)中所有的數據及兩張表能關聯的數據。

  • 查詢dept表所有數據和對應的員工信息(右外連接)
   select * from emp right join dept on emp.dep_id = dept.did;

執行語句結果如下:
在這里插入圖片描述

結果顯示查詢到了右表(dept)中所有的數據及兩張表能關聯的數據。
要查詢出部門表中所有的數據,也可以通過左外連接實現,只需要將兩個表的位置進行互換:

select * from dept left join emp on emp.dep_id = dept.did;

3.3 子查詢

  • 概念
    查詢中嵌套查詢,稱嵌套查詢為子查詢。
    什么是查詢中嵌套查詢呢?我們通過一個例子來看:
    需求:查詢工資高于豬八戒的員工信息。
    來實現這個需求,我們就可以通過二步實現,
    第一步:先查詢出來 豬八戒的工資
  select salary from emp where name = '豬八戒'

第二步:查詢工資高于豬八戒的員工信息

  select * from emp where salary > 3600;

第二步中的3600可以通過第一步的sql查詢出來,所以將3600用第一步的sql語句進行替換

  select * from emp where salary > (select salary from emp where name = '豬八戒');

這就是查詢語句中嵌套查詢語句。

  • 子查詢根據查詢結果不同,作用不同
    • 子查詢語句結果是單行單列,子查詢語句作為條件值,使用 = != > < 等進行條件判斷
    • 子查詢語句結果是多行單列,子查詢語句作為條件值,使用 in 等關鍵字進行條件判斷
    • 子查詢語句結果是多行多列,子查詢語句作為虛擬表
  • 案例
    • 查詢 ‘財務部’ 和 ‘市場部’ 所有的員工信息
 -- 查詢 '財務部' 或者 '市場部' 所有的員工的部門did
select did from dept where dname = '財務部' or dname = '市場部';select * from emp where dep_id in (select did from dept where dname = '財務部' or dname = '市場部');
  • 查詢入職日期是 ‘2011-11-11’ 之后的員工信息和部門信息
-- 查詢入職日期是 '2011-11-11' 之后的員工信息
select * from emp where join_date > '2011-11-11' ;
-- 將上面語句的結果作為虛擬表和dept表進行內連接查詢
select * from (select * from emp where join_date > '2011-11-11' ) t1, dept where 	t1.dep_id = dept.did;

3.4 案例

  • 環境準備:
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;
DROP TABLE IF EXISTS job;
DROP TABLE IF EXISTS salarygrade;-- 部門表
CREATE TABLE dept (did INT PRIMARY KEY PRIMARY KEY, -- 部門iddname VARCHAR(50), -- 部門名稱loc VARCHAR(50) -- 部門所在地
);-- 職務表,職務名稱,職務描述
CREATE TABLE job (id INT PRIMARY KEY,jname VARCHAR(20),description VARCHAR(50)
);-- 員工表
CREATE TABLE emp (id INT PRIMARY KEY, -- 員工idename VARCHAR(50), -- 員工姓名job_id INT, -- 職務idmgr INT , -- 上級領導joindate DATE, -- 入職日期salary DECIMAL(7,2), -- 工資bonus DECIMAL(7,2), -- 獎金dept_id INT, -- 所在部門編號CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
-- 工資等級表
CREATE TABLE salarygrade (grade INT PRIMARY KEY,   -- 級別losalary INT,  -- 最低工資hisalary INT -- 最高工資
);-- 添加4個部門
INSERT INTO dept(did,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'學工部','上海'),
(30,'銷售部','廣州'),
(40,'財務部','深圳');-- 添加4個職務
INSERT INTO job (id, jname, description) VALUES
(1, '董事長', '管理整個公司,接單'),
(2, '經理', '管理部門員工'),
(3, '銷售員', '向客人推銷產品'),
(4, '文員', '使用辦公軟件');-- 添加員工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孫悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'盧俊義',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林沖',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'劉備',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'豬八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'羅貫中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吳用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龍',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'關羽',4,1007,'2002-01-23','13000.00',NULL,10);-- 添加5個工資等級
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);
  • 需求
  1. 查詢所有員工信息。查詢員工編號,員工姓名,工資,職務名稱,職務描述/*分析:1. 員工編號,員工姓名,工資 信息在emp 員工表中2. 職務名稱,職務描述 信息在 job 職務表中3. job 職務表 和 emp 員工表 是 一對多的關系 emp.job_id = job.id*/-- 方式一 :隱式內連接SELECTemp.id,emp.ename,emp.salary,job.jname,job.descriptionFROMemp,jobWHEREemp.job_id = job.id;-- 方式二 :顯式內連接SELECTemp.id,emp.ename,emp.salary,job.jname,job.descriptionFROMempINNER JOIN job ON emp.job_id = job.id;
  1. 查詢員工編號,員工姓名,工資,職務名稱,職務描述,部門名稱,部門位置
 /*分析:1. 員工編號,員工姓名,工資 信息在emp 員工表中2. 職務名稱,職務描述 信息在 job 職務表中3. job 職務表 和 emp 員工表 是 一對多的關系 emp.job_id = job.id4. 部門名稱,部門位置 來自于 部門表 dept5. dept 和 emp 一對多關系 dept.id = emp.dept_id*/-- 方式一 :隱式內連接SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.locFROMemp,job,deptWHEREemp.job_id = job.idand dept.id = emp.dept_id;-- 方式二 :顯式內連接SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.locFROMempINNER JOIN job ON emp.job_id = job.idINNER JOIN dept ON dept.id = emp.dept_id
  1. 查詢員工姓名,工資,工資等級
 /*分析:1. 員工姓名,工資 信息在emp 員工表中2. 工資等級 信息在 salarygrade 工資等級表中3. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary*/SELECTemp.ename,emp.salary,t2.*FROMemp,salarygrade t2WHEREemp.salary >= t2.losalaryAND emp.salary <= t2.hisalary
  1. 查詢員工姓名,工資,職務名稱,職務描述,部門名稱,部門位置,工資等級
 /*分析:1. 員工編號,員工姓名,工資 信息在emp 員工表中2. 職務名稱,職務描述 信息在 job 職務表中3. job 職務表 和 emp 員工表 是 一對多的關系 emp.job_id = job.id4. 部門名稱,部門位置 來自于 部門表 dept5. dept 和 emp 一對多關系 dept.id = emp.dept_id6. 工資等級 信息在 salarygrade 工資等級表中7. emp.salary >= salarygrade.losalary  and emp.salary <= salarygrade.hisalary*/SELECTemp.id,emp.ename,emp.salary,job.jname,job.description,dept.dname,dept.loc,t2.gradeFROMempINNER JOIN job ON emp.job_id = job.idINNER JOIN dept ON dept.id = emp.dept_idINNER JOIN salarygrade t2 ON emp.salary BETWEEN t2.losalary and t2.hisalary;
  1. 查詢出部門編號、部門名稱、部門位置、部門人數
 /*分析:1. 部門編號、部門名稱、部門位置 來自于部門 dept 表2. 部門人數: 在emp表中 按照dept_id 進行分組,然后count(*)統計數量3. 使用子查詢,讓部門表和分組后的表進行內連接*/-- 根據部門id分組查詢每一個部門id和員工數select dept_id, count(*) from emp group by dept_id;SELECTdept.id,dept.dname,dept.loc,t1.countFROMdept,(SELECTdept_id,count(*) countFROMempGROUP BYdept_id) t1WHEREdept.id = t1.dept_id

4,事務

4.1 概述

數據庫的事務(Transaction)是一種機制、一個操作序列,包含了一組數據庫操作命令

事務把所有的命令作為一個整體一起向系統提交或撤銷操作請求,即這一組數據庫命令要么同時成功,要么同時失敗

事務是一個不可分割的工作邏輯單元。

這些概念不好理解,接下來舉例說明,如下圖有一張表
在這里插入圖片描述

張三和李四賬戶中各有100塊錢,現李四需要轉換500塊錢給張三,具體的轉賬操作為

  • 第一步:查詢李四賬戶余額
  • 第二步:從李四賬戶金額 -500
  • 第三步:給張三賬戶金額 +500

現在假設在轉賬過程中第二步完成后出現了異常第三步沒有執行,就會造成李四賬戶金額少了500,而張三金額并沒有多500;這樣的系統是有問題的。如果解決呢?使用事務可以解決上述問題
在這里插入圖片描述

從上圖可以看到在轉賬前開啟事務,如果出現了異常回滾事務,三步正常執行就提交事務,這樣就可以完美解決問題。

4.2 語法

  • 開啟事務
  START TRANSACTION;或者  BEGIN;
  • 提交事務
  commit;
  • 回滾事務
  rollback;

4.3 代碼驗證

  • 環境準備
  DROP TABLE IF EXISTS account;-- 創建賬戶表CREATE TABLE account(id int PRIMARY KEY auto_increment,name varchar(10),money double(10,2));-- 添加數據INSERT INTO account(name,money) values('張三',1000),('李四',1000);
  • 不加事務演示問題
  -- 轉賬操作-- 1. 查詢李四賬戶金額是否大于500-- 2. 李四賬戶 -500UPDATE account set money = money - 500 where name = '李四';出現異常了...  -- 此處不是注釋,在整體執行時會出問題,后面的sql則不執行-- 3. 張三賬戶 +500UPDATE account set money = money + 500 where name = '張三';

整體執行結果肯定會出問題,我們查詢賬戶表中數據,發現李四賬戶少了500。
在這里插入圖片描述

  • 添加事務sql如下:
  -- 開啟事務BEGIN;-- 轉賬操作-- 1. 查詢李四賬戶金額是否大于500-- 2. 李四賬戶 -500UPDATE account set money = money - 500 where name = '李四';出現異常了...  -- 此處不是注釋,在整體執行時會出問題,后面的sql則不執行-- 3. 張三賬戶 +500UPDATE account set money = money + 500 where name = '張三';-- 提交事務COMMIT;-- 回滾事務ROLLBACK;

上面sql中的執行成功進選擇執行提交事務,而出現問題則執行回滾事務的語句。以后我們肯定不可能這樣操作,而是在java中進行操作,在java中可以抓取異常,沒出現異常提交事務,出現異常回滾事務。

4.4 事務的四大特征

  • 原子性(Atomicity): 事務是不可分割的最小操作單位,要么同時成功,要么同時失敗
  • 一致性(Consistency) :事務完成時,必須使所有的數據都保持一致狀態
  • 隔離性(Isolation) :多個事務之間,操作的可見性
  • 持久性(Durability) :事務一旦提交或回滾,它對數據庫中的數據的改變就是永久的

說明:

mysql中事務是自動提交的。

也就是說我們不添加事務執行sql語句,語句執行完畢會自動的提交事務。

可以通過下面語句查詢默認提交方式:

SELECT @@autocommit;

查詢到的結果是1 則表示自動提交,結果是0表示手動提交。當然也可以通過下面語句修改提交方式

set @@autocommit = 0;

差不多大概講解完畢了,大家趕快動手練習一下吧。感謝大家觀看,我是程序員小羊!!!

今天這篇文章就到這里了,大廈之成,非一木之材也;大海之闊,非一流之歸也。感謝大家觀看本文

在這里插入圖片描述

本文來自互聯網用戶投稿,該文觀點僅代表作者本人,不代表本站立場。本站僅提供信息存儲空間服務,不擁有所有權,不承擔相關法律責任。
如若轉載,請注明出處:http://www.pswp.cn/news/914027.shtml
繁體地址,請注明出處:http://hk.pswp.cn/news/914027.shtml
英文地址,請注明出處:http://en.pswp.cn/news/914027.shtml

如若內容造成侵權/違法違規/事實不符,請聯系多彩編程網進行投訴反饋email:809451989@qq.com,一經查實,立即刪除!

相關文章

Audio筆試和面試題型解析

本專欄預計更新90期左右。當前第27期-音頻部分. 音頻硬件在消費電子(手機、電腦、耳機、智能音箱)、汽車、專業音響等領域是用戶體驗的關鍵組成部分。大廠(如蘋果、三星、Google、華為、小米、各種汽車Tier 1供應商等)的硬件工程師在設計和優化音頻系統時,需要對喇叭(揚…

DeepSeek俄羅斯方塊網頁版HTML5(附源碼)

用DeepSeek生成一個俄羅斯方塊游戲網頁版的&#xff0c;基于HTML5&#xff0c;效果很棒。 提示詞prompt 幫我做一個俄羅斯方塊 網頁版的 基于HTML5游戲功能說明 基本功能&#xff1a; 完整的俄羅斯方塊游戲邏輯 7種不同形狀的方塊 分數計算系統 等級提升系統(速度會隨等級提高)…

企業電商平臺搭建:ZKmall開源商城服務器部署與容災方案

企業級電商平臺最核心的訴求&#xff0c;就是得讓 “業務一直在線”—— 不管是平時運營要穩如磐石&#xff0c;還是突然出故障了能火速恢復&#xff0c;都離不開靠譜的服務器部署架構和周全的容災方案。ZKmall 開源商城攢了 6000 多家企業客戶的實戰經驗&#xff0c;琢磨出一套…

【軟件運維】前后端部署啟動的幾種方式

.sh啟動 #!/bin/bash# 解析軟鏈接&#xff0c;獲取真實腳本目錄 SOURCE"${BASH_SOURCE[0]}" while [ -L "$SOURCE" ]; doDIR"$( cd -P "$( dirname "$SOURCE" )" && pwd )"SOURCE"$(readlink "$SOURCE&q…

[爬蟲知識] DrissionPage:強大的自動化工具

相關爬蟲實戰案例&#xff1a;[爬蟲實戰] 使用 DrissionPage 自動化采集小紅書筆記 相關爬蟲專欄&#xff1a;JS逆向爬蟲實戰 爬蟲知識點合集 爬蟲實戰案例 逆向知識點合集 前言&#xff1a; 在當今數據驅動的世界里&#xff0c;網絡爬蟲和自動化測試扮演著越來越重要的角…

數據分析師如何構建自己的底層邏輯?

目錄 一、什么是“底層邏輯”&#xff1f; 二、底層邏輯的核心是什么&#xff1f;三句話講清楚 1. 你到底在解決什么問題&#xff1f; 2. 你有沒有一套“框架”來組織你的分析思路&#xff1f; 3. 你能不能用數據說出“結論 因果 建議”&#xff1f; 三、從 BI 視角出發…

殘差連接+層歸一化:Transformer訓練穩定秘訣

什么是:殘差連接+層歸一化 殘差連接 (Residual Connection):防止梯度消失 核心原理 簡單理解:走樓梯時,既可以走樓梯,也可以坐電梯,最后在同一層匯合。 # 殘差連接的數學表示 輸出 = F(輸入) + 輸入 # ↑處理后 ↑原始輸入具體數值例子 處理句子"我愛學習…

公網 IP 不穩定監控實戰:用多點 Ping 策略實現高可達率保障

更多云服務器知識&#xff0c;盡在hostol.com 你有沒有遇到過這種情況&#xff1a;明明服務器的監控系統說一切正常&#xff0c;服務狀態綠油油一片&#xff0c;但用戶那邊卻反饋“時好時壞”、“丟包嚴重”甚至“根本連不上”。你掏出手機連上公網去試試&#xff0c;誒&#…

uniapp類似抖音視頻滑動

最近需求說要做個類似抖音那種視頻的&#xff0c;我二話不說就用了swiper-view組件&#xff0c;但是效果不太理想&#xff0c;后面改用css屬性先放效果圖&#xff1a;<template><view class"video-scroll-container" touchstart"handleTouchStart"…

Umi-OCR 的 Docker(win制作鏡像,Linux(Ubuntu Server 22.04)離線部署)

前置博客&#xff1a;Ubuntu-Server 22.04.4 詳細安裝圖文教程 wget命令在windows終端下不能使用的原因及解決辦法 在 Ubuntu 22.04 LTS 上離線安裝 Docker 手把手教你在Win11下安裝docker Umi-OCR 完整部署流程 第一步&#xff1a;在 Windows 上構建/獲取 Umi-OCR Docker…

AI Agent革命:當大模型學會使用工具、記憶與規劃

以下是針對Lilian Weng的AI Agent綜述文章&#xff08;原文鏈接&#xff09;的深度解析與整理&#xff1a; AI Agent革命&#xff1a;當大模型學會使用工具、記憶與規劃 ——解析LLM驅動的下一代智能體技術架構 一、核心范式轉變 傳統AI模型&#xff08;如ChatGPT&#xff09…

Claude Code:完爆 Cursor 的編程體驗

前言 最近&#xff0c;聽說Claude Code這款代碼輔助編寫產品很強&#xff0c;有人把Cursor比作實習生水平&#xff0c;Claude Code比作高級工程師水平。 起初不以為意&#xff0c;因為特殊原因&#xff0c;Claude 無法直接訪問。然而&#xff0c;有人做了鏡像站&#xff0c;可以…

ModbusTCP通訊

supply服務-ModbusTCP通訊&#xff1a; winForm-HZHControls-Sqllite本地小項目架構補充&#xff1a;

前端面試專欄-算法篇:23. 圖結構與遍歷算法

&#x1f525; 歡迎來到前端面試通關指南專欄&#xff01;從js精講到框架到實戰&#xff0c;漸進系統化學習&#xff0c;堅持解鎖新技能&#xff0c;祝你輕松拿下心儀offer。 前端面試通關指南專欄主頁 前端面試專欄規劃詳情 圖結構與遍歷算法 在計算機科學中&#xff0c;圖&a…

滲透測試之木馬后門實驗

一、實驗背景 根據CNCERT的監測數據顯示&#xff0c;2018年位于美國的1.4萬余臺木馬或僵尸網絡控制服務器&#xff0c;控制了中國境內334萬余臺主機&#xff1b;2018年位于美國的3325個IP地址向中國境內3607個網站植入木馬&#xff0c;根據對控制中國境內主機數量及控制中國境內…

【LeetCode 熱題 100】24. 兩兩交換鏈表中的節點——(解法一)迭代+哨兵

Problem: 24. 兩兩交換鏈表中的節點 題目&#xff1a;給你一個鏈表&#xff0c;兩兩交換其中相鄰的節點&#xff0c;并返回交換后鏈表的頭節點。你必須在不修改節點內部的值的情況下完成本題&#xff08;即&#xff0c;只能進行節點交換&#xff09;。 文章目錄整體思路完整代碼…

微積分核心考點全解析

一、微積分核心知識框架 1. 極限與連續&#xff08;重點&#xff01;&#xff09; 核心概念&#xff1a; 極限定義&#xff08;ε-δ語言&#xff09;重要極限&#xff1a;lim?x→0sin?xx1limx→0?xsinx?1&#xff0c;lim?x→∞(11x)xelimx→∞?(1x1?)xe連續性判定&am…

TypeScript---泛型

一.簡介TypeScript 就引入了“泛型”&#xff08;generics&#xff09;。泛型的特點就是帶有“類型參數”&#xff08;type parameter&#xff09;。在日常 TypeScript 編程中&#xff0c;我們經常會遇到這樣的場景&#xff1a;函數的參數類型與返回值類型密切相關。此時&#…

手把手一起使用Miniforge3+mamba平替Anaconda(Win10)

Anaconda 開始對企業收費&#xff0c;目前急需平替Anaconda。這里采用Minforgemamba作為替代&#xff0c;可以避免Anaconda追責&#xff0c;并100%兼容原conda倉庫及使用方式&#xff0c;如果各位小伙伴有更好的平替方式&#xff0c;歡迎分享。 Miniforge3安裝 下載并安裝Min…

【Note】Linux Kernel 主題學習之“完整的嵌入式 Linux 環境、構建工具、編譯工具鏈、CPU 架構”

Linux Kernel 主題學習之“完整的嵌入式 Linux 環境、構建工具、編譯工具鏈、CPU 架構” 一、完整的嵌入式 Linux 環境 一個嵌入式 Linux 系統通常包括以下關鍵組件&#xff08;以 Jetson、樹莓派等 ARM 版 SBC 為例&#xff09;&#xff1a; 交叉編譯工具鏈&#xff08;cros…