MySQL快速回顧：資料庫和表操作

来源:https://www.cnblogs.com/flunggg/archive/2020/01/13/12186488.html
-Advertisement-
Play Games

前提要述：參考書籍《MySQL必知必會》 利用空閑時間快速回顧一些資料庫基礎。 4.1 連接 在最初安裝MySQL，可能會要求你輸入一個管理登錄（通常為root）和一個口令（密碼）。 連接MySQL需要以下信息： 主機名（電腦名）——如果連接到本地MySQL伺服器，為localhost； 埠（如 ...


前提要述：參考書籍《MySQL必知必會》
利用空閑時間快速回顧一些資料庫基礎。

4.1 連接

在最初安裝MySQL，可能會要求你輸入一個管理登錄（通常為root）和一個口令（密碼）。

連接MySQL需要以下信息：

  • 主機名（電腦名）——如果連接到本地MySQL伺服器，為localhost；
  • 埠（如果使用預設埠3306之外的埠）；
  • 一個合法的用戶名（預設為root）
  • 用戶口令（密碼，如果需要的話）。

cmd登錄前一篇有說。

會簡單介紹Navicat Premium 12，但是後面都是執行命令，所以在Navicat Premium 12的命令行視窗做或者在cmd做都可以。

現在說下：Navicat Premium 12，打開它。

3.2 創建資料庫

《MySQL必知必會》前面一開始就開講SELETE查詢等，而像新手可能就會不知道怎麼創建資料庫，創建表；然後利用這些表去測試所學的SELETE查詢等。

下麵提供兩種方式創建資料庫：

  1. 圖形界面創建資料庫

那麼資料庫就創建完成。

  1. 命令行創建資料庫

然後輸入：

CREATE DATABASE ts;

更加規範的寫法（推薦）：
格式：

CREATE DATABASE IF NOT EXISTS <資料庫名> DEFAULT CHARACTER SET='utf8';

比如：

CREATE DATABASE IF NOT EXISTS ts DEFAULT CHARACTER SET='utf8';

解釋：

  • CREATE DATABASE ts：表示創建一個名為ts的資料庫；
  • IF NOT EXISTS：翻譯過來就是如果不存在，防止當資料庫存在時會報錯；
  • CREATE DATABASE IF NOT EXISTS ts：表示如果不存在一個名為ts的資料庫，則創建該資料庫；
  • CHARACTER SET='utf8'：這個跟我們上面圖形界面設置的一樣，就是設置資料庫的字元集。
  • ; ：分號表示語句結束，所以每一條語句都需要分號結尾。

註意：寫的時候註意中英文符號，比如 ''。

4.3 刪除資料庫

格式：

drop database <資料庫名>;

4.4 選擇資料庫

可以看到我們剛剛是在my連接這裡開啟命令界面，我們操作的使用並不是對所有資料庫進行操作，而是對單個資料庫進行操作，那麼就需要輸入選擇資料庫的語句。

格式：

USE <資料庫名>;

選擇剛剛建立的ts資料庫：

USE ts;

命令行界面會返回：

Database changed # 表示成功

4.5 瞭解資料庫和表

資料庫、表、用戶、列、許可權等信息被存儲在資料庫和表中（MySQL用MySQL來存儲這些信息）。不過，內部的表一般不直接訪問。可用MySQL的SHOW命令來顯示這些信息（MySQL從內部表中提取這些信息）。

  1. 查詢已經存在的資料庫(包括MySQL系統資料庫：mysql和information_schema)：
SHOW DATABASES;

輸出：

  1. 獲取一個資料庫中的表的列表，前提是已經選擇資料庫，不然也不知道你要獲取哪個資料庫，假設獲取mysql資料庫的表。這裡只是演示，一般別去動mysql資料庫，畢竟是mysql的系統資料庫。
USE mysql;
SHOW TABLES; # 顯示資料庫的全部表

輸出：

  1. 顯示一個表中的列
USE mysql;
SHOW COLUMNS FROM 表名稱;

輸出：

MySQL支持用 DESCRIBE 作為 SHOW COLUMNS FROM 的一種快捷方式。

4.其他的SHOW語句：

  • SHOW STATUS：用於顯示廣泛的伺服器狀態信息；
  • SHOW CREATE DATABASE和SHOW CREATE TABLE：分別用來顯示創建特定資料庫或表的MySQL語句；
  • SHOW GRANTS：用來顯示授予用戶（所有用戶或特定用戶）的安全許可權；
  • SHOW ERRORS和SHOW WARNINGS：用來顯示伺服器錯誤或警告消息。

4.6 MySQL數據類型

轉自菜鳥教程

MySQL中定義數據欄位的類型對你資料庫的優化是非常重要的。

MySQL支持多種類型，大致可以分為三類：數值、日期/時間和字元串(字元)類型。

4.6.1 數值類型

MySQL支持所有標準SQL數值數據類型。這些類型包括嚴格數值數據類型(INTEGER、SMALLINT、DECIMAL和NUMERIC)，以及近似數值數據類型(FLOAT、REAL和DOUBLE PRECISION)。

關鍵字INT是INTEGER的同義詞，關鍵字DEC是DECIMAL的同義詞。

BIT數據類型保存位欄位值，並且支持MyISAM、MEMORY、InnoDB和BDB表。

作為SQL標準的擴展，MySQL也支持整數類型TINYINT、MEDIUMINT和BIGINT。下麵的表顯示了需要的每個整數類型的存儲和範圍。

4.6.2 日期和時間類型

表示時間值的日期和時間類型為DATETIME、DATE、TIMESTAMP、TIME和YEAR。

每個時間類型有一個有效值範圍和一個"零"值，當指定不合法的MySQL不能表示的值時使用"零"值。

TIMESTAMP類型有專有的自動更新特性，將在後面描述。

4.6.3 字元串類型

字元串類型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。該節描述了這些類型如何工作以及如何在查詢中使用這些類型。

CHAR 和 VARCHAR 類型類似，但它們保存和檢索的方式不同。它們的最大長度和是否尾部空格被保留等方面也不同。在存儲或檢索過程中不進行大小寫轉換。

BINARY 和 VARBINARY 類似於 CHAR 和 VARCHAR，不同的是它們包含二進位字元串而不要非二進位字元串。也就是說，它們包含位元組字元串而不是字元字元串。這說明它們沒有字元集，並且排序和比較基於列值位元組的數值值。

BLOB 是一個二進位大對象，可以容納可變數量的數據。有 4 種 BLOB 類型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它們區別在於可容納存儲範圍不同。

有 4 種 TEXT 類型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。對應的這 4 種 BLOB 類型，可存儲的最大長度不同，可根據實際情況選擇。

轉自菜鳥教程評論區：

關於 char、varchar 與 text 平時沒有太在意，一般來說，可能現在大家都是用 varchar。但是當要存儲的內容比較大時，究竟是選擇 varchar 還是 text 呢？

這三種類型比較：

  1. char: char 不用多說了，它是定長格式的，==但是長度範圍是 0~255。 當你想要儲存一個長度不足 255 的字元時，Mysql 會用空格來填充剩下的字元。因此在讀取數據時，char 類型的數據要進行處理，把後面的空格去除。== 這就是一個坑。
  2. varchar: 關於 varchar，有的說最大長度是 255，也有的說是 65535，查閱很多資料後發現是這樣的：++varchar 類型在 5.0.3 以下的版本中的最大長度限製為 255++，而++在 5.0.3 及以上的版本中，varchar 數據類型的長度支持到了 65535++，也就是說可以存放 65532 個位元組（==註意是位元組而不是字元！！！==）的數據（起始位和結束位占去了3個位元組），也就是說，在 5.0.3 以下版本中需要使用固定的 TEXT 或 BLOB 格式存放的數據可以在高版本中使用可變長的 varchar 來存放，這樣就能有效的減少資料庫文件的大小。
  3. text: 與 char 和 varchar 不同的是，text 不可以有預設值，其最大長度是 2 的 16 次方-1

總結起來，有幾點：

  • 經常變化的欄位用 varchar
  • 知道固定長度的用 char（比如性別）
  • 超過 255 字元的只能用 varchar 或者 text
  • 能用 varchar 的地方不用 text
  • 理論上在表中的列全都可以使用varchar來定義列的數據類型，但是千萬不要這樣，最好根據列的實際情況選擇對應的數據類型，並且可以優化資料庫，比如：性別-》char，學生學號位一般用整型來存儲。

下麵轉載
數據類型的選擇特別重要：

  1. 數據類型會影響存儲空間的開銷。
  2. 數據類型會影響資料庫查詢性能。

所以當一個數據類型可以有多種選擇多種類型的時候，應該優先考慮數字類型，其次是日期或二進位類型，最後應該是字元類型。對於相同級別的數據類型，應該優先選擇占用空間小的數據類型。
原理：在對數據進行比較（查詢條件，JOIN條件及排序）操作時：同樣的數據，字元處理往往比數字處理慢，而且在資料庫中，數據的處理是以頁為單位，列的長度越小，數據類型占用的空間越小，利於性能的提升。

更多的優化等我看《高性能MySQL》再總結。

4.7 創建資料庫表

創建表有兩種方式：

  • 使用具有互動式創建和管理表的工具（Navicat Premium 12 圖形界面）；
  • 表也可以直接用MySQL語句操縱。

利用CREATE TABLE創建表需要以下信息：

  • 新表的名稱，再關鍵字CREATE TABLE之後給出；
  • 表列的名字和定義，用逗號隔開

創建表的語法：

CREATE TABLE 表名(
    列名1 列的定義比如數據類型，是否為空，是否為主鍵等,
    列名2 列的定義，
    ...
);

簡單創建單表的例子：

CREATE TABLE student(
    stu_id INT AUTO_INCREMENT,
    stu_name varchar(10) NOT NULL,
    stu_sex char(1) NULL,
    PRIMARY KEY(stu_id)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;;

解釋：

  • 如果你不想欄位為 NULL 可以設置欄位的屬性為 NOT NULL， 在操作資料庫時如果輸入該欄位的數據為NULL ，就會報錯。
  • AUTO_INCREMENT定義列為自增的屬性，一般用於主鍵，數值會自動加1。
  • PRIMARY KEY關鍵字用於定義列為主鍵。表中的每個行必須具有唯一的主鍵值，也就是一個表中至少有一個主鍵。 可以使用多列來定義主鍵，列間以逗號分隔。該關鍵字也可以直接寫在stu_id的定義中。
  • ENGINE 設置存儲引擎（這個先別管），CHARSET 設置編碼。

額外：

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

理解NULL：不要把NULL值與空串相混淆。NULL值是沒有值，它不是空串。如果指定''（兩個單引號，其間沒有字元），這在NOT NULL列中是允許的。空串是一個有效的值，它不是無值。NULL值用關鍵字NULL而不是空串指定。

主鍵中只能使用不允許NULL值得列。允許NULL值得列不能作為唯一標識。

AUTO_INCREMENT：被該關鍵字指定的列，表示每次插入數據時會對該列自動增長，通常使用它去定義主鍵。比如在增加一個新記錄時，需要一個ID值，這ID可以任意，只要它是唯一的即可，那麼最簡單的就是使用下一個ID值，即當前表中最後一條記錄的ID為1，那麼新插入數據時，新插入的數據的ID就為2。所以就可以使用AUTO_INCREMENT自動增加ID值，我們在插入的時候也不用去理會ID是多少。
如何在表中獲得AUTO_INCREMENT的值，輸入：
SELECT last_insert_id();
就會返回最後一個AUTO_INCREMENT值。
也可以自己修改AUTO_INCREMENT的值，只要是唯一的。

DEFAULT：指定列的預設值，註意預設值不允許為函數，《MySQL必知必會》推薦使用預設值而不是使用NULL值。否則使用NOT NULL也可以。

評論區說的註意事項

4.8 引擎類型

上面創建表中最後有一個 ENGINE=InnoDB。下麵來解釋一下：

MySQL與其他DBMS一樣有一個具體管理和處理數據的內部引擎。比如當你使用CREATE TABLE語句時，該引擎具體創建表，而在你使用SELECE查詢語句時或進行其他資料庫處理時，該引擎在內部處理你的請求。所以它是對我們透明化的。

MySQL與其他DBMS不一樣的是，MySQL具有多種引擎。這些引擎都隱藏在MySQL伺服器內，全都能執行CREATE TABLE和SELECE等命令。

為什麼要這麼多引擎，因為它們具有各自不同的功能和特效，為不同的任務選擇正確的引擎能夠獲得良好的功能和靈活性。

但是在CREATE TABLE時也可以忽略它，會自動使用預設引擎（很可能是MyISAM），多數SQL語句都會預設使用它。

以下是幾個必須知道的引擎：

  • InnoBD是一個可靠的事務處理引擎，它步支持全文本搜索；
  • MEMORY在功能等同於MyISAM，但由於數據存儲在記憶體（不是磁碟）中，速度很快（特別適合於臨時表）。
  • MyISAM是一個性能極高的引擎，它支持全文本搜索，但不支持事務處理。

引擎類型可以混用。但是外鍵不能跨引擎。混合引擎有一個很大缺陷。外鍵（用於強制實施引用完整性）不能跨引擎，即使用一個引擎的表不能引用具有使用不同引擎的表的外鍵。

4.9 刪除表

格式：

DROP TABLE <表名> ;

4.10 更新表

語法：

ALTER TABLE <表名>
操作

比如：
給表添加一個列：

ALTER TABLE student
 ADD stu_phone CHAR(11);

給表刪除一個列：

ALTER TABLE student
 DROP stu_phone;

外鍵可以在創建表定義，也可以使用ALTER TABLE來定義，下麵是定義外鍵的一種方式：

再創建一張班主任表

CREATE TABLE Headmaster(
    ma_id INT PRIMARY KEY AUTO_INCREMENT,
    ma_name VARCHAR(10) NOT NULL
    )ENGINE=InnoDB DEFAULT CHARSET='utf8';

先給學生表添加一個 班主任的列：

ALTER TABLE student ADD ma_id INT;

定義外鍵：與學生表相關聯。

ALTER TABLE student
 ADD CONSTRAINT fk_student_master
 FOREIGN KEY (ma_id) REFERENCES Headmaster(ma_id);

外鍵語法：在創建表直接加上下麵這句，修改表得再加個ADD

CONSTRAINT <外鍵名，自定義>
 FOREIGN KEY (<當前表的列，比如學生表>) REFERENCES <要關聯的表，比如班主任表>(<要關聯的列>);

複雜得表結構更改一般需要手動刪除過程，它涉及一下步驟：

  • 用新的列佈局創建一個新表；
  • 使用INSERT SELECT語句（待瞭解）從舊表複雜數據到新表。如果有必要，可使用轉換函數和計算欄位；
  • 校驗包含所需數據的新表；
  • 重命名舊表（如果確定，可以刪除它）；
  • 用舊表原來的名字重命名新表；
  • 根據需要，重新創建觸發器，存儲過程，索引和外鍵（除外鍵其他待瞭解）。

小心使用ALTER TABLE ：應該在改動前做一個完整的備份（模式和數據的備份）。資料庫表的更改不能撤銷，如果增加了不需要的列，可能不能刪除它們。類似地，如果刪除了不應該刪除的列，可能會丟失該列中的所有數據。

4.11 重命名錶

語法：

RENAME TABLE <原表名> TO <新表名>;

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

-Advertisement-
Play Games
更多相關文章
  • 0.5 Linux的聯通性命令彙總
    linux下網路埠連通性測試命令彙總 一、telnet ip port 1.1 安裝： 安裝telnet服務 【centos、ubuntu】安裝telnet命令的方法.】 yum list telnet* 列出telnet相關的安裝包 yum install telnet-server 安裝tel ...
  • Tomcat的性能優化及JVM記憶體工作原理
    JVM性能優化原則： 代碼運算性能、記憶體回收、應用配置（影響Java程式主要原因是垃圾回收機制） 代碼層優化： 避免過多迴圈嵌套、調用和複雜邏輯。 Tomcat調優主要內容 1、增加最大連接數 2、調整工作模式 3、啟用gzip壓縮 4、調整JVM記憶體大小 5、作為web伺服器時、無Apache整合 ...
  • linux 安裝 Redis
    選擇在Linux下安裝redis，現在採用虛擬機安裝的centos7 進行安裝的 1.安裝gcc redis是c語言編寫的 yum -y install gcc 2.下載redis安裝包,在root目錄下執行 wget http://download.redis.io/releases/redis- ...
  • Oracle v$session視圖顯示客戶端IP地址
    在Oracle資料庫中，我們使用session相關視圖（v$session、v$active_session_history,dba_hist_active_session_history等）查找問題時，往往可以定位到相應的主機名，但是，想要更進一步查找IP地址時，卻發現各個數據字典表裡面都沒有記錄... ...
  • mysql 不能載入表問題
    記錄一次 mysql 5.7 下，出現重啟資料庫後不能載入特定表的問題處理。 搜索了很多的類似的錯誤，大多都是說因為外鍵同名的索引丟失的情況。但在5.7這個版本下，會禁止更新外鍵關聯的索引。 最後經過分析，發現是之前更新了主外鍵的字元集導致的。資料庫的預設字元集是utf8mb4（預期是uft8），如 ...
  • mac 安裝 navicat for mysql 直接安裝
    mac 安裝 navicat for mysql 直接安裝 首先打開mac控制台輸入命令行：sudo spctl --master-disable 百度盤，提取碼: vrtr 失效請留言，會及時更新，謝謝 ...
  • uniapp後臺api設計(微信user表)
    MySQL 創建資料庫: CREATE DATABASE [IF NOT EXISTS] <資料庫名> [[DEFAULT] CHARACTER SET <字元集名>] [[DEFAULT] COLLATE <校對規則名>]; 註: [IF NOT EXISTS] 方法判斷資料庫是否存在,不存在才創 ...
  • php操作mysql（資料庫常規操作）
    php操作資料庫八步走 <?php // 1、建立連接 $connection = mysqli_connect('127.0.0.1', 'root', '123456'); // 2、判斷連接是否成功 if (mysqli_connect_error() != null) { die(mysql ...
一周排行
    -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.數據驗證 在伺服器端進行嚴格的數據驗證，確保接收到的數據符合預期格 ...
所有分類