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
更多相關文章
  • linux下網路埠連通性測試命令彙總 一、telnet ip port 1.1 安裝: 安裝telnet服務 【centos、ubuntu】安裝telnet命令的方法.】 yum list telnet* 列出telnet相關的安裝包 yum install telnet-server 安裝tel ...
  • JVM性能優化原則: 代碼運算性能、記憶體回收、應用配置(影響Java程式主要原因是垃圾回收機制) 代碼層優化: 避免過多迴圈嵌套、調用和複雜邏輯。 Tomcat調優主要內容 1、增加最大連接數 2、調整工作模式 3、啟用gzip壓縮 4、調整JVM記憶體大小 5、作為web伺服器時、無Apache整合 ...
  • 選擇在Linux下安裝redis,現在採用虛擬機安裝的centos7 進行安裝的 1.安裝gcc redis是c語言編寫的 yum -y install gcc 2.下載redis安裝包,在root目錄下執行 wget http://download.redis.io/releases/redis- ...
  • 在Oracle資料庫中,我們使用session相關視圖(v$session、v$active_session_history,dba_hist_active_session_history等)查找問題時,往往可以定位到相應的主機名,但是,想要更進一步查找IP地址時,卻發現各個數據字典表裡面都沒有記錄... ...
  • 記錄一次 mysql 5.7 下,出現重啟資料庫後不能載入特定表的問題處理。 搜索了很多的類似的錯誤,大多都是說因為外鍵同名的索引丟失的情況。但在5.7這個版本下,會禁止更新外鍵關聯的索引。 最後經過分析,發現是之前更新了主外鍵的字元集導致的。資料庫的預設字元集是utf8mb4(預期是uft8),如 ...
  • mac 安裝 navicat for mysql 直接安裝 首先打開mac控制台輸入命令行:sudo spctl --master-disable 百度盤,提取碼: vrtr 失效請留言,會及時更新,謝謝 ...
  • MySQL 創建資料庫: CREATE DATABASE [IF NOT EXISTS] <資料庫名> [[DEFAULT] CHARACTER SET <字元集名>] [[DEFAULT] COLLATE <校對規則名>]; 註: [IF NOT EXISTS] 方法判斷資料庫是否存在,不存在才創 ...
  • php操作資料庫八步走 <?php // 1、建立連接 $connection = mysqli_connect('127.0.0.1', 'root', '123456'); // 2、判斷連接是否成功 if (mysqli_connect_error() != null) { die(mysql ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...