MySQL 基礎入門

奔跑的瓜牛發表於2020-05-05

MySql

MySQL 是最流行的關係型資料庫管理系統，在 WEB 應用方面 MySQL 是最好的 RDBMS(Relational Database Management System：關聯式資料庫管理系統)應用軟體之一。

下面就開始一步一步的認識 MySQL 的真面目。

什麼是資料庫？

資料庫（Database）是按照資料結構來組織、儲存和管理資料的倉庫。

每個資料庫都有一個或多個不同的 API 用於建立，訪問，管理，搜尋和複製所儲存的資料。

我們也可以將資料儲存在檔案中，但是在檔案中讀寫資料速度相對較慢。

所以，現在我們使用關係型資料庫管理系統（RDBMS）來儲存和管理大資料量。所謂的關係型資料庫，是建立在關係模型基礎上的資料庫，藉助於集合代數等數學概念和方法來處理資料庫中的資料。

RDBMS 即關聯式資料庫管理系統(Relational Database Management System)的特點：

1.資料以表格的形式出現
2.每行為各種記錄名稱
3.每列為記錄名稱所對應的資料域
4.許多的行和列組成一張表單
5.若干的表單組成database

RDBMS 術語

在我們開始學習MySQL 資料庫前，讓我們先了解下RDBMS的一些術語：

資料庫: 資料庫是一些關聯表的集合。
資料表: 表是資料的矩陣。在一個資料庫中的表看起來像一個簡單的電子表格。
列: 一列(資料元素) 包含了相同型別的資料, 例如郵政編碼的資料。
行：一行（=元組，或記錄）是一組相關的資料，例如一條使用者訂閱的資料。
冗餘：儲存兩倍資料，冗餘降低了效能，但提高了資料的安全性。
主鍵：主鍵是唯一的。一個資料表中只能包含一個主鍵。你可以使用主鍵來查詢資料。
外來鍵：外來鍵用於關聯兩個表。
複合鍵：複合鍵（組合鍵）將多個列作為一個索引鍵，一般用於複合索引。
索引：使用索引可快速訪問資料庫表中的特定資訊。索引是對資料庫表中一列或多列的值進行排序的一種結構。類似於書籍的目錄。
參照完整性: 參照的完整性要求關係中不允許引用不存在的實體。與實體完整性是關係模型必須滿足的完整性約束條件，目的是保證資料的一致性。

MySQL 為關係型資料庫(Relational Database Management System), 這種所謂的"關係型"可以理解為"表格"的概念, 一個關係型資料庫由一個或數個表格組成, 如圖所示的一個表格:

表頭(header): 每一列的名稱;
列(col): 具有相同資料型別的資料的集合;
行(row): 每一行用來描述某條記錄的具體資訊;
值(value): 行的具體資訊, 每個值必須與該列的資料型別相同;
鍵(key): 鍵的值在當前列中具有唯一性。

MySQL 安裝、管理

安裝過程很簡單，網上也有很多資料，這裡就不多講了，具體參考：MySQL 安裝

現在基本都是用視覺化工具來管理資料庫，這使得資料庫的登入、連線變得很簡單。所以這裡也不做過多說明。

注意

下面要介紹的 MySQL 也是可以用視覺化工具做的，不必使用命令列。視覺化很簡單，一看就會了，不做說明。

MySQL操作

下面我會分為資料庫操作、資料表操作和資料操作（CUDR）來講，重點是 CUDR 的講解。

基礎知識

建立資料庫

語法格式：

CREATE DATABASE <資料庫名>;

大部分情況我們使用下面這條命令：

CREATE DATABASE IF NOT EXISTS <資料庫名> DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

該命令的作用：

如果資料庫不存在則建立，存在則不建立。
建立資料庫<資料庫名>，並設定編碼集為 utf8

刪除資料庫

語法格式：

drop database <資料庫名>;

建立資料表

語法格式：

CREATE TABLE table_name (column_name column_type);

舉個?：

CREATE TABLE IF NOT EXISTS `runoob_tbl`(
   `runoob_id` INT UNSIGNED AUTO_INCREMENT,
   `runoob_title` VARCHAR(100) NOT NULL,
   `runoob_author` VARCHAR(40) NOT NULL,
   `submission_date` DATE,
   PRIMARY KEY ( `runoob_id` )
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

?解析：

如果你不想欄位為 NULL 可以設定欄位的屬性為 NOT NULL，在運算元據庫時如果輸入該欄位的資料為NULL ，就會報錯。
AUTO_INCREMENT定義列為自增的屬性，一般用於主鍵，數值會自動加1。
PRIMARY KEY關鍵字用於定義列為主鍵。您可以使用多列來定義主鍵，列間以逗號分隔。
ENGINE 設定儲存引擎，CHARSET 設定編碼。

注意

表名和欄位名外面的符號 ` 不是單引號，是反引號

MySQL 欄位屬性應該儘量設定為 NOT NULL

空值('')是不佔空間的，NULL佔空間

刪除資料表

語法格式：

DROP TABLE table_name;

資料型別

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

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

數值型別

數值型別中又細分為整數型別、浮點數型別、定點數型別和位型別。

整數型別

整數型別	佔用位元組	範圍
TINYINT	1	有符號：[-128,127] 或無符號：[0,255]
SMALLINT	2	有符號：[-32768,32767]或無符號：[0,65535]
MEDIUMINT	3	有符號：[-8333608,8388607]或無符號：[0,1677215]
INT、INTEGER	4	有符號:[-21億多，21億多]或無符號：[0,42億多]
BIGINT	8	很大，19位數字的範圍

記住 MySQL 的整數型別有5種，分別是 tinyint、smallint、mediumint、int 和 bigint。
另外還有兩個特性：

所有整數型別都有一個可選的屬性 UNSIGNED(無符號)，此時上限取值是原來的2倍。
還有一個屬性是AUTO_INCREMENT(自增)，該屬性只能用於整數型別。

浮點數型別

浮點數型別	佔用位元組	範圍
FLOAT	4	[1.175494351E-38,3.402823466E+38]
DOUBLE	8	[2.2..E-308,1.7...E+308]

定點數型別

定點數型別	佔用位元組	範圍
DECIMAL(M,D)	M+2	最大取值範圍與DOUBLE相同，有效範圍由M,D決定

位型別

位型別	佔用位元組	範圍
DOUBLE	8	[2.2..E-308,1.7...E+308]

浮點數和定點數都可以用（M,D）的方式來進行表示。
（M,D）表示該值一共顯示M位數字（整數位+小數位），其中D位位於小數點後面。

M：精度
D：標度
注意在一些表述貨幣等精確數字的場景必須使用DECIMAL型別。
BIT型別用來存放多為二進位制數，資料在插入BIT型別欄位時，首先會轉換成二進位制數。因此，直接使用SELECT命令將不會看到結果。可以用bin()或hex()函式進行讀取。

字串型別

MySQL中提供了多種對字元資料的儲存型別，不同版本可能有所差異，本文以5.0版本為例。

字串型別	描述
CHAR(M)	M為0-255之間的整數
VARCHAR(M)	M為0-65535之間的整數
TINYBLOB	允許0-255位元組
BLOB	允許0-65535位元組
MEDIUBLOB	允許0-167772150位元組
LONGBLOB	允許0-4294967295位元組
TINYTEXT	允許0-255位元組
TEXT	允許0-65535位元組
MEDIUMTEXT	允許0-167772150位元組
LONGTEXT	允許0-4294967295位元組
VARBINARY(M)	允許長度0-M個位元組的邊長位元組字串
BINARY(M)	允許0-M個位元組的定長位元組字串

CHAR 和 VARCHAR 很類似，都用來儲存 MySQL 中較短的字串。二者的主要區別在於儲存方式不同：

CHAR列的長度固定為建立時表明的長度；
而VARCHAR列中的值為可變長字串。

比如儲存字串“abc”，CHAR(5)佔用了5個位元組，而VARCHAR(5)佔用3個位元組。

日期型別

分類：

表示年月日，用DATE
表示年月日時分秒，用DATETIME
表示時分秒，用TIME

日期時間型別	位元組	範圍
DATE	4	1000-01-01到9999-12-31
DATETIME	8	1000-01-01 00:00:00到9999-12-31 23:59:59
TIMESTAMP	4	19700101080001到2038年某個時刻
TIME	3	-838:59:59到838:59:59
YEAR	1	1901到2155

注意

TIMESTAMP 有一個特點就是和時區相關。

當插入日期時，會先轉換為本地時區後存放；而從資料庫裡面取出時，也同樣需要將日期轉換為本地時區後顯示。
檢視當前時區的SQL：show variables like 'time_zone';

資料操作CUDR

插入資料

語法格式：

INSERT INTO 表名稱 (列名1, 列名2,...) VALUES (值1, 值2,....);

舉個?：

-- 向表 Persons 插入一條欄位 LastName = JSLite 欄位 Address = shanghai
INSERT INTO Persons (LastName, Address) VALUES ('JSLite', 'shanghai');

-- 向表 meeting 插入 欄位 a=1 和欄位 b=2
INSERT INTO meeting SET a=1,b=2;

-- SQL實現將一個表的資料插入到另外一個表的程式碼
-- 如果只希望匯入指定欄位，可以用這種方法：
-- INSERT INTO 目標表 (欄位1, 欄位2, ...) SELECT 欄位1, 欄位2, ... FROM 來源表;
INSERT INTO orders (user_account_id, title) SELECT m.user_id, m.title FROM meeting m where m.id=1;

刪除資料

語法格式：

DELETE FROM 表名稱 WHERE 列名稱 = 值

舉個?：

-- 在不刪除table_name表的情況下刪除所有的行，清空表。
DELETE FROM table_name
-- 或者
DELETE * FROM table_name

-- 刪除 Person表欄位 LastName = 'JSLite' 
DELETE FROM Person WHERE LastName = 'JSLite' 

-- 刪除 表meeting id 為2和3的兩條資料
DELETE from meeting where id in (2,3);

查詢資料

語法格式：

SELECT 列名稱 [*] FROM 表名稱

舉個?：

-- 表station取個別名叫s，表station中不包含 欄位id=13或者14 的，並且id不等於4的 查詢出來，只顯示id
SELECT s.id from station s WHERE id in (13,14) and user_id not in (4);

-- 從表 Persons 選取 LastName 列的資料
SELECT LastName FROM Persons

-- 結果集中會自動去重複資料
SELECT DISTINCT Company FROM Orders 
-- 表 Persons 欄位 Id_P 等於 Orders 欄位 Id_P 的值，
-- 結果集顯示 Persons表的 LastName、FirstName欄位，Orders表的OrderNo欄位
SELECT p.LastName, p.FirstName, o.OrderNo FROM Persons p, Orders o WHERE p.Id_P = o.Id_P 

-- gbk 和 utf8 中英文混合排序最簡單的辦法 
-- ci是 case insensitive, 即 “大小寫不敏感”
SELECT tag, COUNT(tag) from news GROUP BY tag order by convert(tag using gbk) collate gbk_chinese_ci;
SELECT tag, COUNT(tag) from news GROUP BY tag order by convert(tag using utf8) collate utf8_unicode_ci;

更改資料

語法格式：

UPDATE 表名稱 SET 列名稱 = 新值 WHERE 列名稱 = 某值

舉個?：

-- update語句設定欄位值為另一個結果取出來的欄位
update user set name = (select name from user1 where user1 .id = 1 )
where id = (select id from user2 where user2 .name='小蘇');

-- 更新表 orders 中 id=1 的那一行資料更新它的 title 欄位
UPDATE `orders` set title='這裡是標題' WHERE id=1;

資料過濾

`WHERE`子句

語法格式：

SELECT 列名稱 FROM 表名稱 WHERE 列 運算子 值

舉個?：

-- 從表 Persons 中選出 Year 欄位大於 1965 的資料
SELECT * FROM Persons WHERE Year>1965

-- MySQL 的 WHERE 子句的字串比較是不區分大小寫的。
-- 你可以使用 BINARY 關鍵字來設定 WHERE 子句的字串比較是區分大小寫的
SELECT * FROM Persons WHERE BINARY name="Jack"

注意

where：資料庫中常用的是where關鍵字，用於在初始表中篩選查詢。它是一個約束宣告，用於約束資料，在返回結果集之前起作用。

group by：對select查詢出來的結果集按照某個欄位或者表示式進行分組，獲得一組組的集合，然後從每組中取出一個指定欄位或者表示式的值。

having：用於對where和group by查詢出來的分組經行過濾，查出滿足條件的分組結果。它是一個過濾宣告，是在查詢返回結果集以後對查詢結果進行的過濾操作。

執行順序

select –>where –> group by–> having–>order by

`AND`運算子

語法格式：

WHERE boolean_expression_1 AND boolean_expression_2

如果第一個條件和第二個條件都成立，則符合篩選條件。

舉個?：

-- 刪除 meeting 表欄位 
-- id=2 並且 user_id=5 的資料  和
-- id=3 並且 user_id=6 的資料 
DELETE from meeting where id in (2,3) and user_id in (5,6);

-- 使用 AND 來顯示所有姓為 "Carter" 並且名為 "Thomas" 的人：
SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter';

`OR`運算子

語法格式：

WHERE boolean_expression_1 OR boolean_expression_2

如果第一個條件和第二個條件中只要有一個成立，則符合篩選條件。

舉個?：

-- 使用 OR 來顯示所有姓為 "Carter" 或者名為 "Thomas" 的人：
SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

`IN`運算子

語法格式：

SELECT "欄位名"FROM "表格名"WHERE "欄位名" IN ('值一', '值二', ...);

IN 操作符允許我們在 WHERE 子句中規定多個值，每個值都進行匹配。IN 取值規律，由逗號分割，全部放置括號中。

舉個?：

-- 從表 Persons 選取 欄位 LastName 等於 Adams、Carter
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

注意

還存在NOT IN，用法與之相反。

`BETWEEN`運算子

語法格式：

expr [NOT] BETWEEN begin_expr AND end_expr;

expr 是一個表示式，它用於測試值是否在定義的範圍begin_expr 和 end_expr 之間。

expr， begin_expr和end_expr必須具有相同的資料型別。

舉個?：

-- 查詢購買價格在90美元和100美元之間的產品（AND）
SELECT productName, buyPrice FROM products WHERE buyPrice >= 90 AND buyPrice <= 100;

-- 等同於下面使用 BETWEEN AND
SELECT productName, buyPrice FROM products WHERE buyPrice BETWEEN 90 AND 100;

BETWEEN...AND是給定的範圍是包含第一個值和第二個值的

注意

還存在NOT BETWEEN...AND，用法與之相反。

`LIMIT`子句

LIMIT子句在SELECT語句中用於約束結果集中的行數。LIMIT子句接受一個或兩個引數。兩個引數的值必須為零或正整數。

SELECT column1,column2,... FROM table_name LIMIT offset, count;

語法解釋如下：

offset指定第一行要返回的偏移量。第一行的偏移量（offset）為 0，而不是1；
count指定要返回最大的行數；

MySQL LIMIT子句簡介

舉個?：

-- 只有一個引數的情況，LIMIT score 相當於 LIMIT 0, score;
-- 查詢students表中成績最好的5名同學姓名
SELECT name FROM students ORDER BY score LIMIT 5;

-- 查詢students表中成績第二好的同學姓名
SELECT name FROM students ORDER BY score LIMIT 1, 1;

`LIKE`子句

語法：

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

WHERE 子句中可以使用等號 = 來設定獲取資料的條件，而使用LIKE就是為了獲取模糊的查詢條件。

如果沒有使用百分號 %, LIKE 子句與等號 = 的效果是一樣的。

LIKE匹配/模糊匹配，會與%、_和escape(預設的轉義字元是\)結合使用。

'%a'     //以a結尾的資料
'a%'     //以a開頭的資料
'%a%'    //含有a的資料
'_a_'    //三位且中間字母是a的
'_a'     //兩位且結尾字母是a的
'a_'     //兩位且開頭字母是a的
'%\_20%' //預設的轉義字元是'\'
'%$_20%' ESCAPE '$' //通過 ESCAPE 子句,指定使用 $ 作為轉義符

注意

MySQL 允許您結合NOT運算子和LIKE運算子來查詢不匹配特定模式的字串。

例如：查詢不含“張”開頭的人員資訊
SELECT * FROM employees WHERE name NOT LIKE '張%'; 

補充：

MySQL 中使用 REGEXP 操作符來進行正規表示式匹配。

例如：

SELECT name FROM person_tbl WHERE name REGEXP '^[aeiou]|ok$';

由於是入門教程，此處略過。

`NULL`值處理

語法格式：

value IS [NOT] NULL

判斷值是否為NULL

舉個?：

-- 獲取成績為 NULL 的同學姓名
SELECT name FROM students WHERE score IS NULL;

-- 獲取成績不為 NULL 的同學姓名
SELECT name FROM students WHERE score IS NOT NULL;

資料排序

`ORDER BY`子句

ORDER BY 子句是對查詢到的結果集排序，它的作用是：

按單列或多列對結果集排序
按升序或降序對不同列進行結果集排序

語法格式：

SELECT column1, column2,...
FROM tbl
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC],...

ASC代表升序，DESC代表降序。如果未指定排序規則，預設情況下，ORDER BY子句按升序ASC對結果集進行排序。

舉個?：

-- students表中按成績降序排列出來
SELECT name FROM students ORDER BY score DESC

-- students表中按成績降序,名字升序排列出來
SELECT name FROM students ORDER BY score DESC，name ASC;

-- students表中按姓名和成績權重值 sw 排序
SELECT name, score*weight as sw FROM students ORDER BY name, sw;

MySQL自定義排序函式FIELD()

ORDER BY子句允許您使用FIELD()函式為列中的值定義自己的排序順序。

舉個?：

-- 自定義 status 欄位按 In Process、Cancelled、Resolved順序進行排序
SELECT 
    orderNumber, status
FROM
    orders
ORDER BY FIELD(status,
        'In Process',
        'Cancelled',
        'Resolved');

MySQL 入門部分先告一段落，進階部分的知識點還有聚合函式、資料分組、UNION、JOIN、巢狀子查詢、索引...

這些都是後話，每一個都能單獨拿出來寫一篇文章。

參考：

Mysql都支援哪些資料型別？

寫給初學者的 SQL 入門教程

21分鐘MySQL基礎入門

MySql入門--基礎知識
2016-12-09
MySql
入門MySQL——基礎語句篇
2020-06-02
MySql
mysql 基礎命令入門學習
2013-08-02
MySql
MySQL基礎和SQL入門【臨時】
2020-12-05
MySql
資料庫MySQL（帶你零基礎入門MySQL）
2021-01-17
資料庫MySql
RabbitMQ基礎入門
2020-08-23
MQ
mongodb基礎入門
2020-01-21
MongoDB
ZooKeeper 基礎入門
2020-06-06
Elasticsearch 基礎入門
2019-03-04
Elasticsearch
Vim 入門：基礎
2019-04-20
Bootstrap基礎入門
2019-07-10
boot
Html基礎入門
2019-07-10
HTML
ElasticSearch基礎入門
2019-06-28
Elasticsearch
HTML 基礎入門
2019-02-16
HTML
Dart 基礎入門
2019-02-11
Dart
SQL入門基礎
2022-05-18
SQL
Nginx 基礎入門
2022-01-09
Nginx
Kafka基礎入門
2021-09-21
Kafka
Redis入門基礎
2018-08-09
Redis
Java 入門基礎
2018-11-29
Java
Maven入門基礎
2018-11-24
Maven
JavaScript入門基礎
2020-11-06
JavaScript
SQL基礎入門
2022-05-17
SQL
Zookeeper基礎入門
2022-03-01
goalng 基礎入門
2018-05-11
Go
TypeScript 基礎入門
2017-06-10
TypeScript
Mongoose基礎入門
2017-07-22
Go
systemtap基礎入門
2013-04-07
shell入門基礎
2024-04-15
sqlServer 基礎入門
2024-04-23
SQLServer
前端基礎入門四（JavaScript基礎）
2019-05-20
前端JavaScript
React Hooks入門: 基礎
2019-03-02
ReactHook
Docker | 入門 & 基礎操作
2021-09-08
Docker
圖論入門基礎
2021-03-02
圖論
TypeScript 基礎入門（一）
2019-01-16
TypeScript
Git入門-基礎命令
2018-12-04
Git
HTML入門基礎（二）
2020-12-14
HTML
rabbitmq（一）-基礎入門
2020-11-08
MQ

MySQL 基礎入門

什麼是資料庫？

RDBMS 術語

MySQL 安裝、管理

MySQL操作

基礎知識

建立資料庫

刪除資料庫

建立資料表

刪除資料表

資料型別

數值型別

字串型別

日期型別

資料操作CUDR

插入資料

刪除資料

查詢資料

更改資料

資料過濾

WHERE子句

AND運算子

OR運算子

IN運算子

BETWEEN運算子

LIMIT子句

LIKE子句

NULL值處理

資料排序

ORDER BY子句

相關文章

`WHERE`子句

`AND`運算子

`OR`運算子

`IN`運算子

`BETWEEN`運算子

`LIMIT`子句

`LIKE`子句

`NULL`值處理

`ORDER BY`子句