MySQL資料庫-表操作-SQL語句（二）

来源:https://www.cnblogs.com/yif0118/archive/2019/08/06/11310979.html
-Advertisement-
Play Games

1. MySQL多表查詢 1.1 外鍵約束 為了消除多張表查詢出現的笛卡爾積的現象，MySQL在建表併進行多表之間的關鍵查詢可以使用外鍵關聯查詢。 外鍵：從表1（sub）的某列引用（ref）另外一個表2（main）的某列的值，把表1的這列叫做表2這列的外鍵。 1.2 外鍵的設置使用 比如上述最簡單的 ...


1. MySQL多表查詢

1.1 外鍵約束

為了消除多張表查詢出現的笛卡爾積的現象，MySQL在建表併進行多表之間的關鍵查詢可以使用外鍵關聯查詢。

外鍵：從表1（sub）的某列引用（ref）另外一個表2（main）的某列的值，把表1的這列叫做表2這列的外鍵。

1.2 外鍵的設置使用

比如上述最簡單的員工(employee)和部門表(department)，設置外鍵dept_id與id相關聯。

步驟如下：

①在多方表中設置fk外鍵，使用外鍵dept_id關聯一方表中的主鍵id，並選擇好參考表；

 

②修改選項設置中的存儲引擎為InnoDB，支持設置外鍵操作；

 

註意:在MySQL中,InnoDB支持事務和外鍵.MyISAM 不支持事務和外鍵。

 

上述操作也可以使用SQL語句方式修改存儲引擎為InnDB：

ALTER TABLE 表名 ENGINE='InnoDB';

如在命令行界面輸入如下語句：

 

同樣使用SQL語句創建外鍵關聯：

ALTER TABLE employee ADD CONSTRAINT employee_fk(外鍵名) FOREIGN KEY (dept_id) REFERENCES  dept(dept_id)；

 

1.3 連接查詢分類

連接查詢總的分類可以用一張圖來簡單描述，主要分為外連接查詢（左外連接、右外連接、全連接）、內連接查詢（內連接、自連接）。

 

1.4 內連接查詢

內連接兩張表的情況如下圖，連接查詢得到的是兩張表的交集部分。

 

SQL語句寫法上可分為顯式和隱式寫法：

隱式內連接寫法：

select <selectList>
From table1,table2 where table1.列 = table2.列;

顯式內連接寫法（推薦寫法）：

select <selectList>
From table1 [inner] join table2 on table1.列 = table2.列;

查詢實例：

說明：本例以下所有查詢以product、product_category、product_stock三張表為例；

 

 

①需求:查詢所有商品的名稱和分類名稱:

隱式寫法：

SELECT p.product_name, pc.category_name

FROM product p, product_category pc

WHERE p.category_id = pc.id;

顯式寫法：

SELECT p.product_name, pc.category_name

FROM product p INNER JOIN product_category pc

ON p.category_id = pc.id;

 
SELECT p.product_name, pc.category_name

FROM product p JOIN product_category pc

ON p.category_id = pc.id;

②需求: 查詢貨品id，貨品名稱，貨品所屬分類名稱；

隱式寫法：

SELECT *

FROM product p, product_category pc

WHERE p.category_id = pc.id AND

p.sale_price > 200 AND pc.category_name = '無線滑鼠';

 

顯式寫法：

SELECT *

FROM product p

JOIN product_category pc ON p.category_id = pc.id

AND p.sale_price > 200 AND pc.category_name = '無線滑鼠';

1.5 外連接查詢

外連接查詢分為左外連接查詢和右外連接查詢。

左外連接：查詢出JOIN左邊表的全部數據與右表滿足ON條件的部分，JOIN右邊表不匹配的數據使用NULL來填充數據行。

右外連接：查詢出JOIN右邊表的全部數據與左表滿足ON條件的部分，JOIN左邊表不匹配的數據使用NULL來填充數據行。

 

語法格式：

select <selectList>

from table1 left/right [outer] join table2

on table1.列 = table2.列；

查詢實例：

查詢每種商品名稱，分類的名稱和包含的具體商品總數（storeNum）

 

SELECT p.product_name, pc.category_name, IFNULL(ps.store_num,0)

FROM product p

LEFT JOIN product_category pc

ON p.category_id = pc.id

LEFT JOIN product_stock ps

ON p.id = ps.product_id;

註：IFNULL(expr1,expr2)的使用是如果當前expr1為NULL，則顯示expr2的值。

 

1.6 自連接查詢

在特定的查詢場景下（商品分類、地區、許可權），需要設計將表中的數據進行分類或二級關聯時，可能會用到自連接查詢的表設計方法。

自連接方式：

查詢實例：

查詢每個商品分類的名稱和父分類名稱

#隱式寫法

SELECT pare.category_name, sub.category_name FROM

product_category sub, product_category pare

WHERE sub.id = pare.parent_id;

 

# 顯示寫法

SELECT pare.category_name, sub.category_name FROM

product_category sub JOIN product_category pare

ON sub.id = pare.parent_id;

查詢結果：

 

1.7 子查詢

子查詢（嵌套查詢）：一個查詢語句嵌套在另一個查詢語句中，內層查詢的結果可以作為外層查詢條件。（相當於查詢出來一個結果，然後把結果當著一張表在進行查詢）

一般的，嵌套在WHERE或者FROM字句中。

 

子查詢（嵌套查詢）一般分為單行單列子查詢和單行多列子查詢。

 

查詢實例：

① 單行單列子查詢

# 單行單列子查詢

# 查詢零售價比羅技MX1100更高的所有商品信息

SELECT * FROM product WHERE

sale_price > ( SELECT sale_price FROM product WHERE product_name = '羅技MX1100' );

查詢結果：

 

②單行多列子查詢

# 查詢分類編號和折扣與羅技M100相同的所有商品信息

SELECT * FROM product WHERE (category_id, cutoff) IN (SELECT category_id, cutoff FROM product WHERE product_name = '羅技MX1100');

 

SELECT * FROM product WHERE (category_id, cutoff) = (SELECT category_id, cutoff FROM product WHERE product_name = '羅技MX1100');

查詢結果：

 

2.MySQL數據操作DML語句

2.1 插入語句（insert）

插入語句：一次只插入一行。

 

語法：

insert into table_name (column1,column2,column3...) values (value1, value2, value3…);

 

插入多行數據記錄。

語法：

insert into table_name (column1,column2,column3...) values

(value1, value2, value3…), (value4, value5, value6…), (value7, value8, value9…);

實例：

# 插入一行

INSERT INTO mytable(id, name, age, sex) VALUES (1, '琳', 21, 0);

 

INSERT INTO mytable(sex, age, name, id) VALUES (1, 18, '小白', 2); #順序可以打亂，只要插入的鍵值一一對應即可

 

# 插入多行-- MySQL特有
INSERT INTO mytable(id, name, age, sex) 
VALUES (3, 'test01', 26, 0), (4, 'test02', 27, 1), (5, 'test03', 28, 1);

 

2.2 修改語句（update）

修改語法：

update table_name set column1 = value1, column2 = value2, column3 = value3…

[where condition];

註意：如果省略了where語句，則是修改全表的數據。

 

修改實例：

# 修改數據
# 將零售價大於300的貨品零售價上調0.2倍
UPDATE product SET sale_price = sale_price * 1.2 WHERE sale_price > 300;

# 將零售價大於300的有線滑鼠的貨品零售價上調0.1倍
UPDATE product p JOIN product_category pc ON p.category_id = pc.id 
SET sale_price = sale_price * 1.1 WHERE sale_price > 300 AND pc.category_name = '有線滑鼠';

 

2.3 刪除語句（delete）

語法：

delete from table_name [where condition];

 

註：如果省略了where，則會全表數據都進行刪除。

 

實例：

# 刪除一條

DELETE FROM mytable WHERE id = 4;

# 刪除多條

DELETE FROM mytable WHERE id >=3;

 

3. MySQL數據備份

MySQL數據備份有兩種方式：通過Navicat工具的SQL導入/導出和使用命令行的方式導入/導出。這裡主要說明使用命令行的方式。

 

語法：

導出：mysqldump -u賬戶 -p密碼 資料庫名稱>腳本文件存儲地

MySql自身的資料庫維護

通過cmd命令進入dos視窗：

mysqldump -uroot -padmin jdbcdemo> C:/shop_bak.sql

導入：mysql -u賬戶 -p密碼 資料庫名稱< 腳本文件存儲地址

mysql -uroot -padmin jdbcdemo< C:/shop_bak.sql

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 壞道的基本介紹及恢復方法
    簡要介紹硬碟壞道可以說是硬碟致命性故障。出現硬碟壞道以後硬碟往往也就代表著硬碟生命結束，硬碟在出現壞道以後不但是某些壞扇區不可用，還會直接殃及到其它可用磁碟扇區，這樣硬碟生命也不會太長，一般利用不了多長時間後便無法再繼續使用，造成整塊硬碟損壞。硬碟出現壞道，意味著用來存儲數據不安全，硬碟壞了可以再買 ...
  • Windows 下的常規命令（收藏）
    1. gpedit.msc 組策略 2. sndrec32 錄音機 3. Nslookup IP地址偵測器 4. explorer 打開資源管理器 5. logoff 註銷命令 6. tsshutdn 60秒倒計時關機命令 7. lusrmgr.msc 本機用戶和組 8. services.msc ...
  • Linux學習筆記（九）shell基礎：echo、命令別名和常用快捷鍵
    一、echo在屏幕上列印內容 echo [選項] [輸出內容] -e 支持轉義字元控制的字元轉換 輸出帶顏色的文本 二、第一個腳本 編寫腳本 註意： 運行腳本 兩種方式 （1）賦予執行許可權，直接運行 （2）通過bash調用執行腳本 三、bash的基本功能 （1）命令別名 顯示已有的別名 alisa ...
  • vi編輯器基礎指令
    vi -- 終端中的編輯器 visual interface ssh-- secure shell vim vi improved 打開和新建文件 vi 文件名 #如果文件已經存在,會直接打開文件 #如果文件不存在,會新建一個文件 打開文件並定位行 vi 文件 游標定位在最開頭 vi 文件 + 游標 ...
  • 手動編譯PHP開發環境
    [TOC] 手動編譯PHP開發環境 這是一篇來自深夜加班的手稿 問題復盤 你有沒有遇到過這樣的情況，部署了集成環境，每次添加擴展的時候，總是需要找一堆的配置文件的位置（其實很多人都能熟練使用集成環境） 你有沒有遇到過這樣的情況，去面試，面試官問你: 有沒有自己手動編譯過環境? 你卻回答 我一般都使用 ...
  • MySQL-線上數據遷移實戰記錄
    1. 遷移背景和限制條件 隨著功能的迭代或者數據表中數據量的增加，將現有數據進行遷移已是工作中經常遇到的事情。通常我們在平時遷移數據數據的時候，只需要用mysqldump、mysqlimport指令就能完成遷移功能，但在實際工作中，作為開發者的我們往往沒有這麼大的許可權（例如寫許可權）來操作線上數據，只 ...
  • 比hive快10倍的大數據查詢利器presto部署
    目前最流行的大數據查詢引擎非hive莫屬，它是基於MR的類SQL查詢工具，會把輸入的查詢SQL解釋為MapReduce，能極大的降低使用大數據查詢的門檻， 讓一般的業務人員也可以直接對大數據進行查詢。但因其基於MR，運行速度是一個弊端，通常運行一個查詢需等待很久才會有結果。對於此情況，創造了hive ...
  • SQL入門經典（第四版）學習記錄——SQL語法（二）
    一、創建表 create table 表裡包含什麼類型的數據 表的名稱是什麼 主鍵 列的名稱是什麼 每一列的數據類型是什麼 每一列的長度是多少 表裡哪些列可以是空的 語法： create table table_name (field1 data_type [not null], field2 da ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發（一）：使用.NET MAUI開發第一個安卓APP
    移動開發（一）：使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說，近來想嘗試開發一款安卓APP，考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP，畢竟是使用Visual Studio開發工具，使用起來也比較的順手，結合微軟官方的教程進行了安卓 ...
  • wpf ToggleButton選中效果和一個登錄界面
    前言 QuestPDF 是一個開源 .NET 庫，用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫，它徹底改變了我們生成 PDF 文檔的方 ...
  • 彈幕樹洞項目功能新增篇
    項目地址 項目後端地址： https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址： ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址： http://tree ...
  • 第27篇 sqlserver2022詳細安裝步驟
    話不多說，直接開乾 一.下載 1.官方鏈接下載： https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe，運行開始下載SQL server； 二. ...
  • .NET 開源高性能 MQTT 類庫
    前言 隨著物聯網（IoT）技術的迅猛發展，MQTT（消息隊列遙測傳輸）協議憑藉其輕量級和高效性，已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫，為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog文檔翻譯系列（六） - 可用的接收器、增強器、格式化輸出
    Serilog支持多種接收器用於日誌存儲，增強器用於添加屬性，LogContext管理動態屬性，支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項，適用於不同需求。 ...
  • 警惕 Visual Studio 屬性求值副作用導致邏輯不符合預期
    目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據，爬取信息時需要模擬瀏覽器行為，否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下： 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • [使用目前最新版]HybridCLR6.9.0+YooAsset2.2.4實現純C# Unity熱更新方案 (一)
    1.前言 什麼是熱更新 游戲或者軟體更新時，無需重新下載客戶端進行安裝，而是在應用程式啟動的情況下，在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR，Xlua，ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles，Addressable， ...
  • 在 ASP.NET Core Web API 中使用操作篩選器統一處理通用操作
    本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺，其實像阿裡雲，騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後，它會送 ...
  • 第28篇 如何.net中實現高效可靠數據同步api
    通過以下方式可以高效，並保證數據同步的可靠性 1.API設計 使用RESTful設計，確保API端點明確，並使用適當的HTTP方法（如POST用於創建，PUT用於更新）。 設計清晰的請求和響應模型，以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類