【冷啟動#1】實用的MySQL基礎

dayceng發表於2024-06-28
MySql

簡單安裝一下MySQL

Windows下（5.7.x）

本體安裝

1、首先先下載安裝包，名字如下：

mysql-5.7.19-winx64.zip

2、配置環境變數，將解壓之後的bin目錄新增一下

3、在解壓目錄下建立my.ini檔案，內容如下：

[ client ]
port=3306
default-character-set=utf8
[ mysqld ]
#設為自己MYSQL的安裝目錄
basedir=D:\zpg\MySQL\mysql-5.7.19-winx64
#設定為MYSQL的資料目錄
datadir=D:\zpg\MySQL\mysql-5.7.19-winx64\data
port-3306
character_set_server=utf8
#跳過安全檢查
#skip-grant-tables #如果不註釋，進入MySQL時就不用輸入密碼

4、使用管理員許可權開啟cmd，安裝mysql

切換到解壓目錄的bin目錄下

cd D:\...\MySQL\mysql-5.7.19-winx64\bin

安裝

mysqld -install
mysqld --initialize-insecure --user=mysql #建立data目錄

啟動MySQL服務

net start mysql

登入（此時沒有密碼，直接回車即可）

mysql -u root -p

5、修改MySQL密碼

登入MySQL後會出現mysql的操作終端，輸入以下指令

use mysql;
update user set authentication_string=password('ag') where user='root' and Host='localhost';

修改完成，此時如果在ini中註釋了skip-grant-tables，那麼下次登入就必須輸入正確的密碼

命令列連線

mysql -h 127.0.0.1 -P 3306 -u root -pag

Linux下（8.0）

5.79的安裝教程：https://blog.csdn.net/qq_39724355/article/details/131332473

以下是8.0的（即Ubuntu20.04預設支援的版本）

apt install -y mysql-server-8.0

ps：

  • 下載有問題記得換下源
  • 不加y有可能會在安裝過程中要求設定密碼

新建一個資料庫

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

mysql> creat database game;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'creat database game' at line 1
mysql> create database game;
Query OK, 1 row affected (0.00 sec)

mysql>

在Linux的終端下進入MySQL-shell然後操作，別打錯單詞

GUI安裝

教程：https://www.cnblogs.com/FRIM/p/16978145.html

SQLyog安裝（免費）

滾吧mtfk

解除安裝MySQL

如果你不幸安裝了錯誤版本的MySQL，請使用以下方法將其解除安裝（生產環境別這樣幹）（ref）

dpkg --list|grep mysql # 檢視MySQL依賴
sudo apt-get remove mysql-common # 解除安裝
# 版本對應即可
sudo apt-get autoremove --purge mysql-server-8.0
# 清除殘留資料
dpkg -l|grep ^rc|awk '{print$2}'|sudo xargs dpkg -P
dpkg --list|grep mysql # 這裡一般就沒有輸出了，如果有執行下一步
# 繼續刪除剩餘依賴項
sudo apt-get autoremove --purge mysql-apt-config

遠端連線MySQL

使用Navicat等資料庫管理工具連線的話，要進行以下設定

設定root密碼

root賬號預設沒有密碼（前面可知，回車即可進入mysql-shell）

將root賬號密碼設定為"root"，還是在user表中操作

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '102030';

注意，MySQL 8.0中不再使用authentication_string欄位，而是使用plugin和authentication_string欄位進行身份驗證。因此以下語句錯誤

update user set authentication string =password('root') where user = 'root';

狀態檢視與配置檔案位置

首先，你可以檢視MySQL服務的狀態

service mysql status
service mysql start / stop / restart
systemctl enable mysql 開機自啟

要遠端連線需要編輯MySQL的配置檔案，可能位於以下常見位置

【Linux】
• /etc/my.cnf
• /etc/mysql/my.cnf
• /etc/mysql/debian.cnf【Ubuntu上看密碼】
• /etc/mysql/mysql.conf.d/mysqld.cnf【Ubuntu上改IP配置】
【Windows】
• C:\ProgramData\MySQL\MySQL Server X.X\my.ini
• C:\Program Files\MySQL\MySQL Server X.X\my.ini
【Mac】
• /opt/homebrew/etc/my.cnf

以Ubuntu為例，找到mysqld.cnf檔案後，修改其中的bind-addres引數為0.0.0.0（或者直接註釋掉）

sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
# 修改 bind-address
bind-address = 0.0.0.0

然後重啟MySQL服務

修改登入限制

首先進入MySQL-Shell

mysql -u root -p

預設情況下沒密碼，回車就行

用show databases;檢視當前存在的表，use mysql切換到"mysql表"進行後續操作

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| game               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
...                        
| user                                                 |
+------------------------------------------------------+
37 rows in set (0.01 sec)
mysql>

查詢出表中使用者名稱為"root"的內容

mysql> select host, user from user where user='root';
+-----------+------+
| host      | user |
+-----------+------+
| localhost | root |
+-----------+------+
1 row in set (0.00 sec)

mysql>

要允許任何IP登入，需要將"host"的屬性值更改成'%'

mysql> update user set host='%'where user='root' and host='localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

重新整理一下flush privileges;

CURD快速實踐

關鍵字說明

MySQL中的關鍵字分別負責不同的功能需求，可以分為以下幾類（ref）：

建立一個資料庫以及修改表結構

可以在sql-shell裡面直接用SHOW DATABASES XX;來建立資料庫，之後在Navicat中就可以檢視得到（也可以在Navicat中直接建立）

這裡建立了一個game資料庫

選擇資料庫game（use game;），就可以在game中建立要使用的表了

建立一個玩家表player用來儲存玩家資訊

CREATE TABLE player(
	id INT,
	name VARCHAR(100), # 長度為100的可變字串
	leveal INT,
	exp INT,
	gold DECIMAL(10,2) # 長度為10並保留兩位小數的十進位制數
)

使用DESC player;來檢視錶結構（DESC是"描述"的縮寫）

建立完表發現有問題怎麼辦？

使用ALTER關鍵字來修改表結構

例如，修改玩家名稱的長度限制，使用ALTER修改（MODIFY）表player中name列（COLUMN）的資料型別

ALTER TABLE player MODIFY COLUMN name VARCHAR(200);

想修改列的名稱也可以，比如把name改為nick_name

ALTER TABLE player RENAME COLUMN name TO nick_name;

或者新增新的列也可以，比如新增玩家最後的登入時間

ALTER TABLE player ADD COLUMN final_login DATETIME;
ALTER TABLE player DROP COLUMN final_login; # 刪掉

刪除整個表的話->DROP TABLE player;

表中資料的CURD

插入資料

用INSERT來插入資料，player後面可以不寫列名，這樣就會按照預設順序插入

INSERT INTO player(id, name, leveal, exp, gold) VALUES (1, 'ag', 1, 1, 1);
# 插入多條
INSERT INTO player(id, name) VALUES (2, 'xixi'), (3, 'coco');
# 指定leveal欄位的預設值為1
ALTER TABLE player MODIFY COLUMN leveal INT DEFAULT 1;
INSERT INTO player(id, name) VALUES (1, 'tutu');

插完用SELECT查詢一下

SELECT * FROM player;

修改資料

用UPDATE配合SET修改已有列欄位中的資料

UPDATE player SET exp = 20 WHERE name = 'ag';
# 當然，不加where條件就可以修改所有資料
UPDATE player SET leveal = 1, gold = 0, exp = 1;

實際中，在UPDATE或者DELETE時不加條件十分危險

刪除資料

DELETE FROM player WHERE exp = 1;

資料的匯入匯出

匯出

在終端中使用mysqldump來匯出某個資料庫

mysqldump -u root -p xxx game > game.sql

將game資料庫匯出到game.sql（是一條條的sql語句）

匯入

假設你剛剛安裝好MySQL

此時需要建立一個資料庫game

create database game;

然後退出MySQL的命令列介面，在終端中執行如下命令來匯入資料：

mysql -u root -p game < game.sql

資料來源：https://github.com/geekhournet/mysql-course

常用的語句

IN:指定多個值

在使用查詢關鍵字SELECT時，可以透過WHERE關鍵字限定查詢範圍，AND\OR\NOT可以處理一些邏輯條件，他們的優先順序為：NOT>AND>OR

例如，查詢等級大於1小於5或者經驗值大於1小於5的玩家

SELECT * FROM player WHERE level > 1 AND level < 5 OR exp > 1 AND exp < 5;

(Ps:括號可以改變優先順序順序)

要查詢多個不同等級的玩家，就可以使用IN實現

例如，查詢等級為1、3、5的玩家

SELECT * FROM player WHERE level IN (1,3,5);

(通常與子查詢連用)

BETWEEN...AND:範圍查詢

這個很好理解，例如，查詢等級1到10的玩家

SELECT * FROM player WHERE level BETWEEN 1 AND 10;

加上NOT可以取反，例如等級不在1到10之間的玩家

SELECT * FROM player WHERE level NOT BETWEEN 1 AND 10;

NOT可以加在任何條件語句之前

LIKE:模糊查詢

實際上就是對於萬用字元和正則的一些使用

例如，查詢名字裡面有“王”的玩家

SELECT * FROM player WHERE name LIKE '王%'; # 姓王的玩家
SELECT * FROM player WHERE name LIKE '%王%'; # 包含王字的玩家
SELECT * FROM player WHERE name LIKE '王_'; # 姓王且名字只有兩個字的玩家

在萬用字元中，‘%’表示匹配之後任意個字元，‘_’表示匹配一個字元【正則中沒有這兩個】

藉助正規表示式進行查詢

常用萬用字元：

'.' -- 任意一個字元；

'^' -- 開頭； '$' -- 結尾； 'A|B' -- A或B；

'[abc]' -- 其中任意一個字元； '[a-z]' -- 範圍內的任意一個字元（0-9也行）;

還是查詢王姓的兩個字的玩家，用正規表示式可以這麼寫：

SELECT * FROM player WHERE name REGEXP '^王.$'; # ^表示開始位置，$表示結束位置，.是匹配任意字元
SELECT * FROM player WHERE name REGEXP '王'; # 查詢包含王字的玩家

如果要查詢包含王或者張的玩家，可以使用'[]'，其會匹配中括號中的任意字元

SELECT * FROM player WHERE name REGEXP '[王張]'; # 查詢包含王或張字的玩家
SELECT * FROM player WHERE name REGEXP '王|張';
練習

1、查詢郵件地址以zhangsan開頭的玩家

SELECT * FROM player WHERE email REGEXP 'zhangsan';

2、查詢郵件地址以a\b\c開頭的玩家

SELECT * FROM player WHERE email REGEXP '^[abc]';
SELECT * FROM player WHERE email REGEXP '^[a-c]';

3、查詢郵件地址以net結尾的玩家

SELECT * FROM player WHERE email REGEXP 'net$';
SELECT * FROM player WHERE email LIKE '%net';

NULL:空值

在查詢空值資料的時候有一個需要注意的地方，即不能使用'='作為判斷條件

需要使用"IS NULL"來做，例如，查詢沒有填郵箱的玩家

SELECT * FROM player WHERE email IS NULL; # 郵箱為null值
SELECT * FROM player WHERE email IS NULL OR email = ''; # 郵箱為null或空值（空字串）

ORDER BY:排序

為查詢資料進行排序，不指定順序預設升序

例如，等級從小到大排序

SELECT * FROM player ORDER BY level; # 升序
SELECT * FROM player ORDER BY 5 DESC; # 或者使用列的序號也可以
SELECT * FROM player ORDER BY level DESC; # 降序

對多個列排序

追加列名就行

SELECT * FROM player ORDER BY level DESC, exp; #等級降序， 經驗升序

聚合函式

常用聚合函式：

AVG() -- 返回集合的平均值； COUNT() -- 返回集合中的專案數量；

MAX() -- 返回最大值； MIN() -- 返回最小值；SUM() -- 求和;

例如，求玩家總數

SELECT COUNT(*) FROM player;
SELECT AVG(level) FROM player;

分組查詢

GROUP BY由於將查詢後的結果進行分組，在關鍵字後面接上列名即可將指定列框定為一個“組”（group）

SELECT * FROM player GROUP BY level; # 按等級分組

與複合函式連用可以對分組後的資料進行計算處理

SELECT level, COUNT(level) FROM player GROUP BY level; # 統計每個等級的玩家數量
與HAVING配合

GROUP BY常與HAVING連用，可對分組後的資料進行過濾篩選

例如，想知道等級大於4的玩家

SELECT level, COUNT(level) FROM player GROUP BY level HAVING COUNT(level) > 4; #
與ORDER BY配合

GROUP BY與ORDER BY連用通常原來為結果排序

SELECT level, COUNT(level) FROM player GROUP BY level HAVING COUNT(level) > 4 ORDER BY COUNT(level) DESC; # 此處DESC表示降序
練習

統計每個姓氏玩家的數量，並將結果安裝數量來降序排序，只顯示數量大於等於5的姓氏

下意識會這麼寫：

SELECT name COUNT(name) FROM player GROUP BY name HAVING COUNT(name) > 5 ORDER BY COUNT(name) DESC;

但是不行，因為name是字串，不能直接進行計數

並且我們需要統計的是姓氏，而不是整個名字出現的次數，顯然需要對字串進行擷取

這裡需要使用一個函式：SUBSTR（跟c++一樣™的）用於擷取字串，思路如下：

SELECT SUBSTR(name, 1, 1), COUNT(SUBSTR(name, 1, 1)) FROM player # 首先擷取出姓氏並統計出現次數
GROUP BY SUBSTR(name, 1, 1) # 對擷取出來的姓氏列進行分組
HAVING COUNT(SUBSTR(name, 1, 1)) >= 6 # 篩選出出現次數大於等於5次的姓氏
ORDER BY COUNT(SUBSTR(name, 1, 1)) DESC # 降序排序
LIMIT 3 # 如果只想返回三條結果
LIMIT 3,3 # 第一個3是偏移量，表示從第四名開始，第二個是返回數量，也就是第四名後三個
# ↑即分頁查詢的原理

DISTINCT:查詢結構去重

SELECT DISTINCT sex FROM player;

UNION:合併查詢結果（並集）

查詢所有等級為1-4以及經驗為1-3的玩家

SELECT * FROM player WHERE level BETWEEN 1 AND 3
UNION # UNION會預設去重，UNION ALL不會去重
SELECT * FROM player WHERE exp BETWEEN 1 AND 3;

tips：

  • 這種合併方式是全外連線，也就是將表連線起來
  • UNION與OR有點類似，但OR合併的是兩個條件，而UNION合併的是兩個查詢結果
  • 注意，連線的兩個語句不要寫分號不然會有問題（非要寫可以在最後寫一個）

INTERSECT:合併結果集（交集）

INTERSECT用於查詢兩個結果的交集

SELECT * FROM player WHERE level BETWEEN 1 AND 3
INTERSECT 
SELECT * FROM player WHERE exp BETWEEN 1 AND 3

EXCEPT:合併結果集（差集）

查詢等級為1-3的但是經驗不在1-3之間的玩家

SELECT * FROM player WHERE level BETWEEN 1 AND 3
EXCEPT
SELECT * FROM player WHERE exp BETWEEN 1 AND 3

子查詢

如果想使用一個查詢的結果作為另一個查詢的條件，可以透過子查詢實現

例如，先使用AVG求出所有玩家的平均等級，然後再查出大於平均等級的玩家

SELECT AVG(level) FROM player;
SELECT * FROM player WHERE level > (SELECT AVG(level) FROM player);

又例如，想查詢所有玩家等級與平均等級的差值

SELECT level, ROUND((SELECT AVG(level) FROM player)) FROM player; # ROUND可以將浮點數取整
SELECT level, ROUND((SELECT AVG(level) FROM player)), level - ROUND((SELECT AVG(level) FROM player)) FROM player; # 作差即可

但是這樣寫又臭又長，可以用AS給列起個別名看起來方便一些

SELECT level, ROUND((SELECT AVG(level) FROM player)) AS average,
level - ROUND((SELECT AVG(level) FROM player)) AS diff
FROM player;

還可以用子查詢來建立新的表

又又例如，我們想講等級小於5的玩家先查詢出來然後單獨拎到一個表中儲存

SELECT * FROM player WHERE level < 5 # 先查詢
CREATE TABLE new_player SELECT * FROM player WHERE level < 5 # 使用子查詢建表
SELECT * FROM new_player # 查詢新表

插入等級在6-10之間的玩家到新表中

SELECT * FROM player WHERE level BETWEEN 6 AND 10; # 還是先查詢
#使用INSERT INTO按子查詢結果插入資料到新表
INSERT INTO new_player SELECT * FROM player WHERE level BETWEEN 6 AND 10;

又又又例如，你想查詢是否存在等級大於100的玩家，可以使用EXISTS配合子查詢來實現

SELECT EXISTS(SELECT * FROM player WHERE level > 100)

表關聯

表關聯用於查詢多個表中的資料，關聯的表中需要含有相同欄位

一般透過表的主鍵和外來鍵來關聯（概念理解詳見）

以game資料庫為例，裡面除了玩家以外，還有裝備資料。有時候我們希望將玩家與裝備資訊關聯起來進行查詢

DESC equip # 先檢視一下equip表的結構

SELECT * FROM player # 查詢player表中的所有項
INNER JOIN equip # 將player表和equip表關接起來
ON player.id = equip.player_id # 指定關聯的欄位（即兩個表透過什麼條件進行關聯）

具體左右連線的區別，見詳情

表連線的本質就是笛卡爾積+過濾條件，所以如果沒有正確使用條件的話會導致資料異常（會有一些奇怪的組合）

相關文章