MySQL必知必會3

来源:https://www.cnblogs.com/train99999/archive/2019/07/25/11247484.html
-Advertisement-
Play Games

創建和操縱表 創建表 輸入 分析 表名緊跟在CREATE TABLE 關鍵字後面 ，實際的表定義括在圓括弧中。各列之間用逗號分隔。這個表由9列組成。每列的定義以列名開始，後跟列的數據類型。表的主鍵可以在創建表時用PRIMARY KEY關鍵字指定，這裡，列cust_id指定作為主鍵列。整條語句由右圓括 ...


創建和操縱表

創建表

輸入

CREATE TABLE customers
(
    cust_id int NOT NULL AUTO_INCREMENT,
    cust_name char(50) NOT NULL ,
    cust_address char(50) NULL ,
    cust_city char(50) NULL ,
    cust_state char(5) NULL ,
    cust_zip char(10) NULL , 
    cust_country char(50) NULL ,
    cust_contact char(50) NULL ,
    cust_email char(255) NULL ,
    PRIMARY KEY(cust_id)
)ENGINE=InnoDB;

分析

表名緊跟在CREATE TABLE 關鍵字後面 ，實際的表定義括在圓括弧中。各列之間用逗號分隔。這個表由9列組成。每列的定義以列名開始，後跟列的數據類型。表的主鍵可以在創建表時用PRIMARY KEY關鍵字指定，這裡，列cust_id指定作為主鍵列。整條語句由右圓括弧後的分號結束

NULL值

NULL值就是沒有值或缺值，允許值的列也允許在插入行時不給出該列的值。不允許NULL值的列不接受該列沒有值的行

更新表

ALTER TABLE vendors
ADD vend_phone char(20);

分析

這條語句給vendors表增加一個名為vend_phone的列，必須明確其數據類型

刪除列

ALTER TABLE vendors
DROP COLUMN vend_phone;

定義外鍵

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY (order_num) REFERENCES orders(order_num);

刪除表

DROP TABLE customers1;

重命名錶

RENAME TABLE customers2 TO customers;

使用視圖

視圖

需要MySQL5 MySQL5添加了對視圖的支持，本章的內容適用於MySQL5及以後的版本

視圖是虛擬的表，與包含數據的表不一樣，視圖只包含使用時動態檢索數據的查詢

理解視圖

SELECT cust_name,cust_contact
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
    AND orderitems.order_num = orders.order_num
    AND prod_id = 'TNT2';

​ 此查詢用來檢索訂購了某個特定產品的客戶，任何需要這個數據的人都必須理解相關表的結構，並且知道如何創建查詢和對錶進行聯結。為了檢索其他產品的相同數據，必須修改最後的WHERE子句

現在，假如可以把整個查詢包裝成一個名為productcustomers的虛擬表，則可以如下輕鬆地檢索出相同的數據

輸入

SELECT cust_name,cust_contact
FROM productcustomers
WHERE prod_id = 'TNT2';

這就是視圖的作用。productcustomers是一個視圖，作為視圖，它不包含表中應該有的任何或數據，它包含的是一個SQL查詢

視圖的規則和限制

與表一樣，視圖必須唯一命名(不能給視圖取與別的視圖或表相同的名字)

對於可以創建的視圖數目沒有限制

為了創建視圖，必須具有足夠的訪問許可權，這些限制通常由資料庫管理人員授予

視圖可以嵌套，即可以利用從其他視圖中檢索數據的查詢來構造一個視圖

ORDER BY可以用在視圖中，但如果從該視圖檢索數據的SELECT語句也含有ORDER BY,那麼該視圖中的ORDER BY將被覆蓋

視圖不能索引，也不能有關聯的觸發器或預設值

視圖可以和表一起使用。例如，編寫一條聯結表和視圖的SELECT語句

使用視圖

視圖用CREATE VIEW 語句創建

使用SHOW CREATE VIEW viewname; 來查看創建視圖的語句

用DROP刪除視圖，其語法為DROP VIEW viewname;

更新視圖時，可以先用DROP再用CREATE ，也可以直接用CREATE OR REPLACE VIEW ，如果想要更新的視圖不存在，則第二條語句會創建一個視圖，如果要更新的視圖存在，則第二條語句會替換原有視圖

利用視圖簡化複雜的聯結

輸入

CREATE VIEW productcustomers AS 
SELECT cust_name,cust_contact,prod_id
FROM customers,orders,orderitems
WHERE customers.cust_id = orders.cust_id
AND orderitems.order_num = order.order_num;

分析

這條語句創建了一個名為productcustomers的視圖，它聯結三個表，以返回已訂購了任意產品的所有客戶列表，如果執行

SELECT * FROM productcustomers，將列出訂購了任意產品的客戶

用視圖重新格式化檢索出的數據

視圖的另一種常見用途是重新格式化檢索出的數據。下麵的SELECT語句在單個組合計算列中返回供應商名和位置

輸入

SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;
+------------------------+
| vend_title             |
+------------------------+
| ACME(USA)              |
| Anvils R Us(USA)       |
| Furball Inc.(USA)      |
| Jet Set(England)       |
| Jouets Et Ours(France) |
| LT Supplies(USA)       |
+------------------------+

現在加入經常需要這個格式的結果，不必再每次需要時執行聯結，創建一個視圖，每次需要時使用它即可，

輸入

CREATE VIEW vendorlocations AS
SELECT CONCAT(RTRIM(vend_name),'(',RTRIM(vend_country),')') AS vend_title
FROM vendors
ORDER BY vend_name;

分析

這條語句使用與以前的SELECT語句相同的查詢創建視圖。為了檢索出以創建所有郵件標簽的數據，可以如下進行

SELECT * FROM vendorlocations;

使用視圖過濾不想要的數據

CREATE VIEW customeremaillist AS
SELECT cust_id,cust_name,cust_email
FROM customers
WHERE cust_email IS NOT NULL;

分析

顯然，在發送電子郵件到郵件列表時，需要排除沒有電子郵件地址的用戶。這裡的WHERE子句過濾了cust_email列中具有NULL值的行，

現在，可以像使用其他表一樣使用視圖customeremaillist

SELECT * FROM customeremaillist;

使用視圖與計算欄位

輸入

CREATE VIEW orderitemsexpanded AS
SELECT order_num,
       prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price
FROM orderitems;

為檢索訂單2005的詳細內容

輸入

SELECT * FROM orderitemsexpanded
WHERE order_num=20005;

更新視圖

通常，視圖是可更新的(INSERT、UPDATE和DELETE),更新一個視圖將更新基表

如果視圖定義了以下操作，則不能進行視圖更新

分組(使用GROUP BY和HAVING);

聯結；

子查詢；

並；

聚集函數(Min()、Count()、Sum()等)；

DISTINCT;

導出(計算)列

換句話說，本章許多例子中的視圖都是不可以更新的，這聽上去好像是一個嚴重的限制，但實際上不是，因為視圖主要用於數據檢索

使用存儲過程

需要MySQL5 MySQL5添加了對存儲過程的支持，因此，本章內容適用於MySQL5及以後的版本

存儲過程簡單來說，就是為以後的使用而保存的一條或多條MySQL語句的集合。可以將其視為批文件，雖然他們的作用不限於批處理

使用存儲過程

使用存儲過程需要指定如何執行他們，存儲過程的執行遠比其定義更經常遇到，因此，我們將從執行存儲過程開始介紹，然後在介紹創建和使用存儲過程

執行存儲過程

MySQL稱存儲過程的執行為調用，因此MySQL執行存儲過程的語句為CALL。CALL接受存儲過程的名字以及需要傳遞給他的任意參數

CALL productpricing(
    @pricelow,
    @pricehigh,
    @priceaverage;
)

分析

其中，執行名為productpricing的存儲過程，他計算並返回產品的最低、最高和平均價格

創建存儲過程

輸入

DELIMITER //

CREATE PROCEDURE  productpricing()
BEGIN
    SELECT AVG(prod_price) AS priceaverage
    FROM products;
END //

DELIMITER ;

分析

此儲存過程名為productpricing,用CREATE PROCEDURE productpricing()語句定義。如果存儲過程接受參數，他們將在()中列舉出來。此存儲過程沒有參數但後跟的括弧仍然需要。BEGIN和END語句用來限定存儲過程體，過程體本身僅是一個簡單的SELECT語句

那麼，如何使用這個存儲過程

輸入

CALL productpricing();
+--------------+
| priceaverage |
+--------------+
|    16.133571 |
+--------------+

分析

CALL productpricing();執行剛創建的存儲國產過程並顯示返回的結果，因為存儲過程實際上是一種函數，所以存儲過程名後需要有()符號

刪除存儲過程

DROP PROCEDURE productprincing；

使用參數

輸入

DELIMITER //

CREATE PROCEDURE productpricing(
    OUT pl DECIMAL(8,2),
    OUT ph DECIMAL(8,2),
    OUT pa DECIMAL(8,2)
)
BEGIN
    SELECT Min(prod_price)
    INTO pl
    FROM products;
    SELECT Max(prod_price)
    INTO ph
    FROM products;
    SELECT Avg(prod_price)
    INTO pa
    FROM products;
END //

DELIMITER ;

分析

此存儲過程接受3個參數：pl存儲產品最低價格.....

每個參數必須具有指定的類型，這裡使用十進位值。關鍵字OUT指出相應的參數用來從存儲過程傳出一個值(返回給調用者)，MySQL支持IN(傳遞給存儲過程),OUT(從存儲過程傳出),INOUT(對存儲過程傳入和傳出)類型的參數。存儲過程的代碼位於BEGIN和END語句內

調用存儲過程

CALL productpricing(
    @pricelow,
    @pricehigh,
    @priceaverage
);

分析

由於存儲過程要求3個參數，因此必須正好傳遞三個參數，在調用時，這條語句並不顯示任何數據

為了顯示檢索的產品平均價格，如下

SELECT @priceaverage；

使用游標

​ MySQL檢索操作返回一組稱為結果集的行，這組返回行都是與SQL語句相匹配的行，使用簡單的SELECT語句，例如，沒有辦法得到第一行，下一行或前十行，也不存在每次一行地處理所有行的簡單方法

​ 有時，需要在檢索出來的行中前進或後退一行或多行。這就是使用游標的原因

​ 游標是一個存儲在MySQL伺服器上的資料庫查詢，他不是一條SELECT語句，而是被該語句檢索出來的結果集。在存儲了游標之後，應用程式可以根據需要滾動或瀏覽其中的數據

使用游標

​ 在能夠使用游標前，必須聲明它，這個過程實際上沒有檢索數據，它只是定義要使用的SELECT語句

​ 一旦聲明後，必須打開游標以供使用。這個過程用前面定義的SELECT語句把數據實際檢索出來

​ 對於填有數據的游標，根據需要去除各行

​ 在結束游標使用時，必須關閉游標

​ 在聲明游標後，可根據需要頻繁地打開和關閉游標，在游標打開後，可根據需要頻繁地執行取操作

創建游標

游標用DECLARE語句創建。DECLARE命名游標，並定義相應的SELECT語句，根據需要帶WHERE和其他子句。例如，下麵的語句定義了名為ordernumbers的游標，使用了可以檢索所有訂單的SELECT語句

DELIMITER //

CREATE PROCEDURE processorders3()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
END //
DELIMITER ;

分析

這個存儲過程並沒有做很多事情，DECLARE語句用來定義和命名游標，這裡為ordernumbers.存儲過程處理完成後，游標就消失。

打開和關閉游標

OPEN ordernumbers;

在處理OPEN語句時執行查詢，存儲檢索出來的數據以供瀏覽和滾動

游標處理完後，應當使用如下語句關閉游標

CLOSE ordernumbers;

輸入

DELIMITER //

CREATE PROCEDURE processorders()
BEGIN
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    OPEN ordernumbers;
    
    CLOSE ordernumbers;
END //
DELIMITER ;

分析

這個存儲過程聲明、打開、和關閉一個游標。但對檢索出的數據聲明也沒有做

使用游標數據

在一個游標被打開後，可以使用FETCH語句分別訪問它的每一行。FETCH指定檢索聲明數據，檢索出來的數據存儲在什麼地方。他還向前移動游標中的內部指針，使下一條FETCH語句檢索下一行

輸入

DELIMITER //

CREATE PROCEDURE processorders()
BEGIN

DECLARE o INT;

DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;

OPEN ordernumbers;

FETCH ordernumbers INTO o;

CLOSE ordernumbers;

END //

DELIMITER ;

分析

其中FETCH用來檢索當前行的order_num列從第一行開始到一個名為o的局部聲明變數中，對檢索出的數據不做任何處理

輸入

DELIMITER //

CREATE PROCEDURE processorders7()
BEGIN

DECLARE done BOOLEAN DEFAULT 0;
DECLARE o INT;

DECLARE ordernumbers CURSOR
FOR
SELECT order_num FROM orders;

DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

OPEN ordernumbers;

REPEAT

    FETCH ordernumbers INTO o;
    
    UNTIL done END REPEAT;
    
    CLOSE ordernumbers;
    
END //

DELIMITER ;

與前一個列子一樣，這個列子使用FETCH檢索當前order_num到聲明的名為o的變數中。但與前一個列子不一樣的是，這個列子中的FETCH是在REPEAT內，因此他反覆執行直到done為真(有UNTIL done END REPEAT;規定) 。為使他起作用，用一個DEFAULT 0(假，不結束)定義變數done。那麼,done這樣才能在結束時被設置為真呢?答案是用以下語句

​ DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;

​ 這條語句定義了一個CONTINUE HANDLER,她是在條件出現時被執行的代碼。這裡，他指出當SQLSTATE '02000'出現時，SET done=1。SQLSTATE '02000'是一個未找到的條件，當REPEAT由於沒有更多的行供迴圈而不能繼續時，出現這個條件

​ 如果調用這個存儲過程，他將定義幾個變數和一個CONTINUE HANDLER ，定義並打開一個游標，重覆讀取所有行，然後關閉游標

輸入

DELIMITER //

CREATE PROCEDURE processorders9()
BEGIN

    DECLARE done BOOLEAN DEFAULT 0;
    DECLARE o INT;
    DECLARE t DECIMAL(8,2);
    
    DECLARE ordernumbers CURSOR
    FOR
    SELECT order_num FROM orders;
    
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1;
    
    CREATE TABLE IF NOT EXISTS ordertotals
    (order_num INT, total DECIMAL(8,2));
    
    OPEN ordernumbers;
    
    REPEAT
    
        FETCH ordernumbers INTO o;
        
        CALL ordertotal(o,1,t);
        
        INSERT INTO ordertotals(order_num,total)
        VALUES(o,t);
        
        UNTIL done END REPEAT;
        
        CLOSE ordernumbers;
END //

DELIMITER ;

管理事務處理

事務處理(transaction processiong)可以用來維護資料庫的完整性，他保證成批的MySQL操作要麼完全執行，要麼完全不執行

控制事務處理

使用ROLLBACK

MySQL的ROLLBACK命令用來回退MySQL語句，

SELECT * FROM ordertotals;
START TRANSACTION;
DELETE FROM ordertorals;
SELECT * FROM ordertotals;
ROLLBACK;
SELECT * FROM ordertotals;

分析

這個列子從顯示ordertotals表的內容開始，首先執行一條SELECT以顯示該表不為空。然後開始一個事務處理，用一條DELETE語句刪除ordertotals中所有的表。另一條SELECT語句驗證ordertotals確實為null，這時用一條ROLLBACK語句回退START TRANSACTION之後的所有語句，最後一條SELECT語句顯示該表不為null

使用COMMIT

一般的MySQL語句都是直接針對資料庫表執行和編寫的，這就是所謂的隱含提交(implicit commit)，即提交操作是自動進行的

但是，在事務處理塊中，提交不會隱含的進行。為進行明確的提交，使用COMMIT語句

輸入

START TRANSACTION
DELETE FROM orderitems WHERE order_num = 20010;
DELETE FROM orders WHERE order_num = 20010;
COMMIT;

分析

在這個例子中，從系統中完全刪除訂單20010.因為涉及更新兩個資料庫表orders和orderitems,所以使用事務處理塊來保證訂單不被部分刪除，最後的COMMIT語句僅在不出錯是寫出更改，如果第一條DELETE起作用，但第二條失敗，則DELETE不會提交


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

-Advertisement-
Play Games
更多相關文章
  • Linux使用BIND提供功能變數名稱解析服務
    DNS（Domain Name System，功能變數名稱系統）用於管理和解析功能變數名稱與IP地址對應關係的技術。 主伺服器：在特定區域內具有唯一性，負責維護該區域內的功能變數名稱與IP地址之間的對應關係。 從伺服器：從主伺服器中獲得功能變數名稱與IP地址的對應關係併進行維護，以防主伺服器宕機等情況。 緩衝伺服器：通過向其他功能變數名稱 ...
  • 執行shell腳本的四種方式
    這篇文章主要介紹了Linux中執行shell腳本的4種方法,即總結在Linux中運行shell腳本的4種方法。 前提：bash shell 腳本的方法有多種,假設我們編寫好的shell腳本的文件名為hello.sh，文件位置在/data/shell目錄中並已有執行許可權。（提供許可權的話解決了絕對路徑無 ...
  • devops持續集成，Centos7.6下gitlab+jenkins(pipeline)實現代碼自動上線
    持續集成 gitlab+jenkins(pipeline)實現代碼自動上線 環境準備：Centos7.6版本ip:192.168.0.13 主機名:gitip:192.168.0.23 主機名:jenkinsip:192.168.0.15 主機名:web01關閉防火牆關閉selinux分發公鑰使兩兩 ...
  • Ubuntu18.04安裝PHP7.3
    因為近期需要做畢業設計，需要用到Linux系統，在此分享一下在Linux-Ubuntu系統下安裝PHP環境的一小點知識，如有偏差錯誤的，請各位學友多多指教哈！ sudo apt-get install software-properties-common python-software-proper ...
  • linux命令之sed
    sed 是一種線上編輯器，它一次處理一行內容。處理時，把當前處理的行存儲在臨時緩衝區中， 稱為“模式空間”（pattern space），接著用 sed 命令處理緩衝區中的內容，處理完成後，把緩衝區的內容送往屏幕。接著處理下一行，這樣不斷重覆，直到文件末尾。文件內容並沒有 改變，除非你使用重定向存儲 ...
  • linux命令之grep
    grep (global search regular expression(RE) and print out the line,全面搜索正則表達式 並把行列印出來)是一種強大的文本搜索工具，它能使用正則表達式搜索文本，並把匹配的行列印出來。Unix 的 grep 家族包括 grep、egrep ...
  • Ubuntu：一個部署好的tomcat應用(war包)怎麼用Nginx實現動靜分離？
    ubuntu nginx tomcat 虛擬目錄 反向代理 動靜分離 ...
  • 使用Docker運行SQL Server
    現在.net core已經跨平臺了，大家也都用上了linux用上了docker。跟.net經常配套使用的SQL SERVER以前一直是windows only，但是從SQL Server 2017開始已經支持運行在docker上，也就說現在SQL Serer已經可以運行在linux下了。 下麵在Ub ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類