MySQL （六）

来源:http://www.cnblogs.com/xuweiweiailixing/archive/2017/08/24/7420976.html
-Advertisement-
Play Games

1 外鍵 外鍵：foreign key，外面的鍵（鍵不在自己表中），如果一張表中有一個欄位（非主鍵）指向另外一張表的主鍵，那麼將該欄位稱為外鍵。 1.1 增加外鍵 外鍵可以在創建表的時候或創建表之後增加（但是要考慮數據的問題）。 方案一：在創建表的時候增加外鍵，在所有的表欄位之後，使用foreign ...


1 外鍵

  • 外鍵：foreign key，外面的鍵（鍵不在自己表中），如果一張表中有一個欄位（非主鍵）指向另外一張表的主鍵，那麼將該欄位稱為外鍵。

 

1.1 增加外鍵

  • 外鍵可以在創建表的時候或創建表之後增加（但是要考慮數據的問題）。

 

  • 方案一：在創建表的時候增加外鍵，在所有的表欄位之後，使用foreign key（外鍵欄位） references 外部表 （主鍵欄位）;
-- 創建班級
CREATE TABLE my_class(
    id INT PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(20) NOT NULL,
    room VARCHAR(20)
);
-- 創建學生表
CREATE TABLE my_student1(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    c_id INT ,
    CONSTRAINT fk_c_id FOREIGN KEY (c_id) REFERENCES my_class (id)
);

 

  • 方案二：在新增表之後，增加外鍵，所以需要修改表結構。
alter table 表名 add [constraint 外鍵名字] foreign key (外鍵欄位) references 父表(主鍵欄位);
-- 創建班級
CREATE TABLE my_class(
    id INT PRIMARY KEY AUTO_INCREMENT,
    c_name VARCHAR(20) NOT NULL,
    room VARCHAR(20)
);
-- 創建學生表
CREATE TABLE my_student2(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(20) NOT NULL,
    c_id INT 
);
ALTER TABLE my_student2 ADD CONSTRAINT fk_c_id FOREIGN KEY (c_id) REFERENCES my_class (id);

 

1.2 修改外鍵&刪除外鍵

  •  外鍵不可以修改：只能先刪除後新增。
alter table 表名 drop foreign key 外鍵名;--一張表中可以有多個外鍵，但是名字不能相同
ALTER TABLE my_student2 DROP FOREIGN KEY fk_c_id;

 

1.3 外鍵作用

  • 外鍵預設的作用有兩點：
    • 一個對父表：父表數據進行寫操作（刪和改，都必須設計到主鍵本身），如果對應的主鍵在字表中已經被數據所引用，那麼就不允許操作。
    • 一個對字表（外鍵欄位所在的表）：字表數據進行寫操作(增和改)的時候，如果對應的外鍵欄位在父表找不到對應的匹配，操作會失敗。　　

 

1.4 外鍵條件

  • 外鍵要存在：首先必須表的存儲引擎是innodb。如果不是innodb存儲引擎，那麼外鍵可以創建成功，但是沒有約束效果。
  • 外鍵欄位的欄位類型（列類型）必須和父表的主鍵類型完全一致。
  • 一張表中的外鍵名字不能重覆。
  • 增加外鍵的欄位（數據已經存在），必須保證數據和父表主鍵要求對應。

 

1.5 外鍵約束

  • 外鍵約束：就是外鍵的作用。

 

  • 外鍵約束有三種約束模式：都是針對父表的約束
    • district：嚴格模式（預設的），父表不能刪除或更新一個已經被子表數據引用的記錄（主鍵）。
    • cascade：級聯模式，父表的操作，對應子表關聯的數據也隨之變化。
    • set null：置空模式，父表的操作之後，子表對應的數據（外鍵）被置空。　　
  • 通常：父表刪除的時候，子表置空；更新的時候，子表級聯操作。
constraint 外鍵名字 foreign key (外鍵欄位) references 主表(主鍵) on delete set null;
constraint 外鍵名字 foreign key (外鍵欄位) references 主表(主鍵) on update cascade;
constraint 外鍵名字 foreign key (外鍵欄位) references 主表(主鍵) on delete set null on update cascade;

 

  • 刪除置空的前提：外鍵欄位為空（如果不滿足條件，外鍵無法創建）。

 

  • 外鍵雖然很強大，能夠進行各種約束，但是對於java來說，外鍵約束降低了java對數據的可控性。所以，通常情況下，在實際開發中，很少使用外鍵的級聯模式和置空模式。

 

2 聯合查詢

  •  聯合查詢：將多次查詢語句，在記錄上進行拼接。

 

  • 基本語法
多條select語句構成：每一條select語句獲取的欄位必須嚴格一致（但是欄位類型無關）
select 語句1 union [union 選項] 語句2……;
-- union選項：與select選項一樣有兩個
-- all 保留所有（不管重覆）
-- distinct 去重（整個重覆）：預設的

 

  • 示例：聯合查詢
SELECT * FROM my_class
UNION 
SELECT * FROM my_class;

 

  •  示例：聯合查詢
SELECT id,c_name,room FROM my_class
UNION 
SELECT id,c_id,NAME FROM my_student2;

 

  • 聯合查詢的意義
    • 查詢同一張表，但是需求不同：如查詢學生信息，男生身高升序，女生身高降序（面試題）。
    • 多表查詢：多張表的結構是完全一樣的，保存的數據（結構）也是一樣的。　　

 

  •  聯合查詢Order by使用
    • 在聯合查詢中：order by不能直接使用，需要對查詢語句使用括弧才行。　　
(SELECT * FROM my_student2 WHERE sex = '男' ORDER BY height ASC)
UNION 
(SELECT * FROM my_student2 WHERE sex = '女' ORDER BY height DESC);

    • 如果想order by生效：必須搭配limit,limit使用限定的最大數即可。　　
(SELECT * FROM my_student2 WHERE sex = '男' ORDER BY height ASC LIMIT 999999999)
UNION 
(SELECT * FROM my_student2 WHERE sex = '女' ORDER BY height DESC LIMIT 999999999);

 

 3 子查詢

  •  子查詢：查詢是在某個查詢結果之上進行的（一條select語句內部包含了另外一條select語句）。

 

  • 子查詢分類方式：
    • 按位置分類：子查詢（select語句）在外部查詢（select語句）中出現的位置。
      • From子查詢：子查詢在From之後
      • where子查詢：子查詢出現在where條件中
      • exists子查詢：子查詢出現在exists裡面
    • 按照結果分類：根據子查詢得到的數據進行分類（理論上任何一個查詢得到的結果都可以理解為二維表）
      • 標量子查詢：子查詢得到的結果是一行一列。（出現在where之後）
      • 列子查詢：子查詢得到的結果是一列多行。（出現在where之後）
      • 行子查詢：子查詢得到的結果是多列一行（多行多列）（出現在where之後）
      • 表子查詢：子查詢得到的結果是多行多列（出現的位置在From之後）　　　　　　　　

 

  • 示例腳本
-- 創建班級表
CREATE TABLE my_class(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵',
    c_name VARCHAR(20) NOT NULL COMMENT '班級名字',
    room VARCHAR(20) NOT NULL COMMENT '班級所在教室'
);
-- 插入班級信息
INSERT INTO my_class VALUES (NULL,'java001班','A01');
INSERT INTO my_class VALUES (NULL,'Linux003班','C15');
INSERT INTO my_class VALUES (NULL,'c005班','B23');

-- 創建學生表 
CREATE TABLE my_student(
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主鍵',
    NAME VARCHAR(20) NOT NULL COMMENT '學生姓名',
    age INT NOT NULL COMMENT '學生年齡',
    gender VARCHAR(2) NOT NULL COMMENT '學生性別',
    c_id INT COMMENT '外鍵' ,
    CONSTRAINT fk_c_id FOREIGN KEY (c_id) REFERENCES my_class (id)    
);
-- 插入學生信息
INSERT INTO my_student VALUES (NULL,'張三',20,'男',1);
INSERT INTO my_student VALUES (NULL,'李四',18,'女',2);
INSERT INTO my_student VALUES (NULL,'王五',19,'女',2);
INSERT INTO my_student VALUES (NULL,'趙六',25,'男',3);
INSERT INTO my_student VALUES (NULL,'田七',14,'女',1);
INSERT INTO my_student VALUES (NULL,'王八',19,'男',3);

3.1 標量子查詢

  • 示例：查詢java001班的所有學生。 
    • ①確定數據源：獲取所有的學生
select * from my_student where c_id = ?;
    • ②獲取班級id:可以通過名字確定。　　
SELECT id FROM my_class WHERE c_name = 'java001班' ; -- 一行一列
    • ③最後的執行SQL語句　　
SELECT * FROM my_student WHERE c_id = (SELECT id FROM my_class WHERE c_name = 'java001班' );

 

3.2 列子查詢

  • 示例：查詢所有在讀班級的學生（班級表中存在的班級）　　
    • ①確定數據源：學生 　　
SELECT *FROM my_student WHERE c_id IN (?);
    • ②確定有效班級的id：所有班級的id　　
SELECT id FROM my_class
    • 最後的執行SQL語句　　
SELECT *FROM my_student WHERE c_id IN (SELECT id FROM my_class);

 

  • 列子查詢返回的結果會比較：一列多行，需要使用in作為條件匹配：其實在mysql中還有幾個類似的條件：all、some、any。 

 

3.3 行子查詢

  • 行子查詢：返回的結果可以是多行多列（一行多列） 

 

  • 修改學生表
ALTER TABLE my_student ADD height INT NOT NULL COMMENT '身高';
UPDATE my_student SET height = 180 WHERE id = 1;
UPDATE my_student SET height = 170 WHERE id = 2;
UPDATE my_student SET height = 165 WHERE id = 3;
UPDATE my_student SET height = 190 WHERE id = 4;
UPDATE my_student SET height = 155 WHERE id = 5;
UPDATE my_student SET height = 160 WHERE id = 6;

 

  • 示例：要求查詢整個學生中，年齡最大且身高
    • 思路1：　　
      • ①確定數據源　　　　
select * from my_student where age = ? and height = ?;
      • ②確定最大的年齡和最高的身高　　　　
select max(age),max(height) from my_student;
      • ③最後的SQL執行語句　　　　
SELECT * FROM my_student WHERE (age = (SELECT MAX(age) FROM my_student)) AND  (height =  (SELECT MAX(height) FROM my_student));

      • ④貌似上面的是對的哦，但是如果最高的年齡最大，身高最高的不是一個人呢？這樣就不能查到數據呢，所以，綜上所述，上面的思路貌似正確，但是不合題意。　　　　
    • 思路2：正確解法
      • 需要構造行元素：行元素是由多個欄位構成。　　 　　
SELECT * FROM my_student WHERE (age,height) = (SELECT MAX(age),MAX(height) FROM my_student);

3.4 表子查詢

  •  表子查詢：子查詢返回的結果是當做二維表來使用。

 

  • 示例：找出每個班中最高的一個學生。 
    • ①先對學生按照身高降序　　
SELECT * FROM my_student  ORDER BY height DESC
    • ②對排序後的學生臨時表按照班級分組　　
SELECT * FROM (SELECT * FROM my_student  ORDER BY height DESC) AS student GROUP BY student.c_id;
    • 當然，這一題可能有人會這樣想？我先對學生分組，求出最大的年齡，然後用in子句，就可以了。　　
SELECT * FROM my_student WHERE height IN (SELECT MAX(height) FROM my_student GROUP BY c_id);
    • 當然，第二種思路也是可以的。　　　　　　

 

3.5 exists子查詢

  •  exists：是否存在，exists子查詢是用來判斷某些條件是否滿足（跨表），exists是在where之後，exists返回的結果是0或1。

 

  • 示例：查詢所有的學生，前提條件是班級存在。
    • ①確定數據源　　
SELECT * FROM my_student WHERE ？;
    • ②確定條件是否滿足　　
EXISTS(SELECT * FROM my_class)
    • 最後的執行SQL語句　　　　
SELECT * FROM my_student WHERE EXISTS(SELECT * FROM my_class);

 


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

-Advertisement-
Play Games
更多相關文章
  • [MySQL]快速解決"is marked as crashed and should be repaired"故障
    具體報錯如下： Table '.\mysql\proc' is marked as crashed and should be repaired 我的解決辦法： 找到mysql的安裝目錄的bin/myisamchk工具，右擊【以管理員身份運行】修複下即可。 網上解決辦法： 找到mysql的安裝目錄的 ...
  • MS sql 查詢動態表名的方法
    ALTER PROCEDURE [dbo].[sp_GetClassCountData] @BatchId NVARCHAR(50), @ExamId VARCHAR(100), @ClassId VARCHAR(100), @SubjectId NVARCHAR(50)ASBEGIN DECLAR ...
  • [20170824]11G備庫啟用DRCP連接.txt
    [20170824]11G備庫啟用DRCP連接.txt--//參考鏈接:http://blog.itpub.net/267265/viewspace-2099397/blogs.oracle.com/database4cn/adg%e5%a4%87%e5%ba%93%e7%9a%84drcp%e8% ...
  • mysql_view
    概述： 視圖即是虛擬表，也稱為派生表，因為它們的內容都派生自其它表的查詢結果。雖然視圖看起來感覺和基本表一樣，但是它們不是基本表。基本表的內容是持久的，而視圖的內容是在使用過程中動態產生的。——摘自《SQLite權威指南》 使用視圖的優點： 1.可靠的安全性 2.查詢性能提高 3.有效應對靈活性的功 ...
  • Oracle筆記----oracle數字類型number自增
    創建序列 create sequence seq_student start with 6 increment by 1 maxvalue 500 nominvalue nocycle nocache; 創建觸發器 create or replace trigger trigger_student ... ...
  • 1.淺析Hadoop之HDFS
    HDFS ,Hadoop Distribute File System,hadoop分散式文件系統。 主從架構，分主節點NameNode,從節點DataNode.當然還有個SecondaryName,但這不是淺析里的點.這裡主要講下namenode和datanode的基本概念， 並描述下讀寫過程. ...
  • 學習筆記TF051:生成式對抗網路
    生成式對抗網路(gennerative adversarial network,GAN)，谷歌2014年提出網路模型。靈感自二人博弈的零和博弈，目前最火的非監督深度學習。GAN之父，Ian J.Goodfellow，公認人工智慧頂級專家。 原理。生成式對搞網路包含一個生成模型(generative ...
  • MySQL緩存機制詳解（一）
    本文章拿來學慣用||參考資料:http://www.2cto.com/database/201308/236361.html 對MySql查詢緩存及SQL Server過程緩存的理解及總結 一、MySql的Query Cache 1、Query Cache MySQL Query Cache是用來緩 ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類