MySQL基本知識 1.資料庫 1.1.創建資料庫語法： CREATE DATABASE [IF NOT EXISTS] db_name [create_specification[,create_specification]...] create_specification: [DEFAULT] ...

MySQL基本知識

1.資料庫

1.1.創建資料庫

語法：

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification[,create_specification]...]
create_specification:
    
    [DEFAULT]CHARACTER SET charset_name
    [DEFAULT]COLLATE collation_name

CHARACTER SET：指定資料庫採用的字元集，如果不指定字元集，預設為utf8
COLLATE：指定資料庫字元集的校對規則（常用的uft8_bin[區分大小寫]、utf8_general_ci[不區分大小寫] 預設是 utf8_general_ci）

練習：

創建一個名為hsp_db01的資料庫[圖形和指令演示]
創建一個utf8字元集的hsp_db02的資料庫
創建一個使用utf8字元集，並帶校對隊則的hsp_db03資料庫

指令創建：

#演示資料庫的操作
#1. 創建一個名為hsp_db01的資料庫
CREATE DATABASE hsp_db01;

#刪除資料庫指令
DROP DATABASE hsp_db01;

#2. 創建一個utf8字元集的hsp_db02的資料庫
CREATE DATABASE hsp_db02 CHARACTER SET utf8

#3. 創建一個使用utf8字元集，並帶校對隊則的hsp_db03資料庫
CREATE DATABASE hsp_db03 CHARACTER SET utf8 COLLATE utf8_bin
#校對規則 utf8_bin 區分大小寫   預設utf8_general_ci 不區分大小寫

可以看見在不指定採用字元集和校驗規則的情況下，字元集預設為utf8，校驗規則預設為utf8_general_ci

關於字元集和校驗規則：

如果在創建表的時候沒有指定字元集和校驗規則，則遵循和資料庫一樣的規則

如下：在資料庫hsp_db02中創建表t1，t1設為預設字元集和校驗規則。

在表t1中用select查詢可以得到不區分大小寫的兩條數據：

在資料庫hsp_03中創建同樣的表t1並插入同樣的數據，查詢後只得到一條數據：

因為資料庫hsp_03在創建時指定校驗規則為utf8_bin，區分大小寫，查詢返回的數據也區分了大小寫

資料庫hsp_02沒有指定大小寫，返回的查詢沒有區分大小寫

因此可知當表設置預設的字元集和校驗規則時，其則遵循資料庫的字元集和校驗規則

1.2查看、刪除資料庫

語法：

#顯示資料庫語句
#顯示資料庫創建時的語句
#資料庫刪除語句[一定要慎用]

練習

查看當前伺服器中的所有資料庫
```
SHOW DATABASES
```
查看前面創建的hsp_02資料庫的定義信息
```
SHOW CREATE DATABASE hsp_db02
```
刪除前面創建的資料庫

關鍵字作名字創建資料庫：
- 說明1：資料庫名用反引號括起來，是為了規避數據名字為關鍵字
例如：創建一個名為create的資料庫，不使用反引號就會報錯

使用了反引號之後，就可以成功創建資料庫

使用關鍵字創建，在刪除的時候也要在名字旁邊反引號
- 說明2：後面的語句用/*!40100...*/ 說明版本要在4以上執行這條語句

1.3備份恢復

1.3.1備份恢複數據庫

語法：

備份資料庫 (註意：在DOS執行)

mysqldump -u 用戶名 -p -B 資料庫1 資料庫2 資料庫n > 文件名.sql

恢複數據庫（註意：進入MySQL命令行再執行）

Source 文件名.sql

練習：備份恢複數據庫

備份hsp_db02和hsp_db03庫中的數據，並恢復

備份：

如下，在Dos視窗下輸入指令，指明備份的資料庫和保存的文件名

在指定的路徑下生成了對應的sql文件

這個備份的文件就是對應的sql語句：

恢復

首先刪除掉資料庫hsp_db02和hsp_db03

登錄賬號，進入mysql命令行，輸入指令 Source d:\\\bak.sql

可以看到資料庫及裡面的信息成功恢復：

1.3.2備份恢複數據庫表

語法：

備份資料庫的表

mysqldump -u 用戶名 -p 資料庫 表1 表2 表n > 文件名.sql

(註意：在DOS執行)

恢復和資料庫同理（註意進入mysql命令行執行）

2.表

2.1創建表

語法：

CREATE TABLE table_name
(
    field1 datatype,
    field2 datatype,
    field3 datatype
)character set 字元集 collate 校對規則 engine 存儲引擎

# field：指定列名  datatype：指定列類型（欄位類型）
# character set：如不指定則為所在的資料庫字元集
# collate：如不指定則為所在的資料庫的校驗規則
# engine：引擎（這個涉及較多，後面單獨講解）

練習

在資料庫hsp_db02創建一張表，根據需求的數據創建相應的列，並根據數據的類型定義相應的列類型

user表：

id  		整形
name  		字元串
passwoed  	字元串
birthday 	日期

#id  		整形
#name  		字元串
#passwoed  	字元串
#birthday 	日期
CREATE TABLE `user`(
	id INT,
	`name` VARCHAR(255),
	`password` VARCHAR(255),
	`birthday` DATE)
	CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

3.MySQL常用數據類型（列類型）

Mysql列類型即mysql的數據類型

詳見MySQL的參考手冊

3.1列類型之整型

說明

數值型（整型）的基本使用

說明：使用規範：在能夠滿足需求的情況下儘量選擇空間小的類型

應用實例

3.1.2.1無符號TINYINT

在資料庫hsp_02中創建一個表t2，在表中插入列類型tinyint，列名為id

CREATE TABLE t2(
id TINYINT);

在表中插入數據 -129，執行後提示錯誤，可以看到t2為空表

往表中插入數據-128，執行成功，表中成功插入數據

同理，分別往表中插入數據128、127，只有127插入成功

說明在有符號情況下，tinyint的範圍為-128~127

有符號TINYINT

在列類型後面指定無符號

CREATE TABLE t3(
id TINYINT UNSIGNED);

INSERT INTO t3 VALUES(-1);#錯誤，超出範圍
INSERT INTO t3 VALUES(0);
INSERT INTO t3 VALUES(256);#錯誤，超出範圍
INSERT INTO t3 VALUES(255);

3.2列類型之BIT

說明

bit欄位顯示時，按照位的方式顯示（按照指定位數的二進位顯示）
查詢的時候仍然可以使用添加時的數值（比如十進位）來查詢
如果一個值只有0,1，可以考慮使用bit(1)，可以節省空間
位類型。M指定位數，預設值1，範圍1-64

練習

#演示bit類型使用
#說明
#1.bit(M) M在1-64位
#2.添加數據 的範圍是按照你給定的M的位數來確定，例如M=8 表示一個位元組(8bit) 0~255
create table t5 (num bit(8));
INSERT INTO t5 VALUES(5);
insert into t5 values(3);
select * from t5;
#3.查詢時仍然可以按照十進位數來查詢
select * from t5 where num = 5;

3.3列類型之小數類型

說明

FLOAT/DOUBLE[UNSIDNED]

float 單精度，double 雙精度
DECIMAL[M,D] [UNSIGNED]
- 可以支持更加精確的小數位。M是小數位數(精度)的總數，D是小數點(標度)後面的位數。
- 如果D是0，則值沒有小數點或分數部分。M最大65，D最大是30。如果D被省略，預設D是0；如果M被省略，預設M是10.
- 建議：如果希望小數點的精度高，推薦使用decimal

練習

#案例演示 float、double、decimal的使用
CREATE TABLE t6(
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20));
#添加數據
INSERT INTO t6 VALUES(88.12345678912345,88.12345678912345,88.12345678912345);

#查詢
SELECT * FROM t6;

#decimal可以存放很大的數

3.4列類型之字元串(文本類型)

說明

字元串的基本使用：

CHAR(size)

固定長度字元串最大255字元

VARCHAR(size)

可變長度字元串最大65532位元組

[ utf8編碼最大字元為21844字元，1-3個位元組用於記錄大小，uft8編碼每個字元占用三個位元組 ]

如果編碼是uft8 則varchar(size) size = (65535-3) / 3 = 21844

如果編碼是gbk 則varchar(size) size = (65535-3) / 2 = 32766

錯誤使用:（uft8）

成功插入：(uft8)

gbk字元集下：

字元串使用細節
細節1：
- 如 char(4) //這個4表示字元數（最大255），不是位元組數，不管是中文還是字母都是放四個，按字元計算
- varchar(4) //這個4表示字元數，(這四個字元占用多少個位元組取決於你定義的編碼) 不管是字母還是中文都以定義好的表的編碼來存放數據
上面的例子不管是中文還是字母都是最多存放4個，是按照字元來存放的
細節2：
- char(4)是定長（固定的大小），就是說，即時你插入'aa'，也會占用分配的4個字元
- varchar(4)是可變長，即如果你插入的是'aa'，實際占用的空間大小並不是4個字元，而是按照實際占用空間來分配。VARCHAR值保存時只保存需要的字元數，另加一個位元組來記錄長度(如果列聲明的長度超過255，則使用兩個位元組) ，因此varchar的實際長度=實際的數據大小+（1-3個位元組）

細節3：使用的的時機
- 如果數據是定長，推薦使用char，比如md5的密碼，郵編，手機號，身份證號碼等
- 如果一個欄位的長度不定，就使用varchar，比如留言，文章
- 查詢速度 char > varchar
細節4：
- 在存放文本時，也可以使用text數據類型。可以將text列視為varchar列，註意text不能有預設值，大小 0~2^16位元組
- 如果希望存放更多字元，可以選擇mediumtext 0~2^24 或者 longtext 0~2^32

例子：

在下圖中，不管插入的是中文還是字母，每一個都按一個字元來算，因此'abcde'相當5個字元長度，插入失敗；varchar同理。

失敗：

成功：

例子2：關於text，mediumtext 和 longtext的使用

如上圖所示：在插入的數據後面顯示了實際使用的數值大小

content1有兩個中文=2*3=6bit，每個數字占用一個位元組，共計9bit

content2也有兩個中文=2*3=6bit，有八個數字，共計14bit

3.5列類型之日期類型

日期類型的細節說明

TimeStamp在Insert和Update時，會自動更新

例子：

#演示時間相關的案例
#創建一張表，data ，datatime，timestamp
CREATE TABLE t13(
	birthday DATE,-- 生日
	job_time DATETIME,-- 記錄年月日 時分秒
	login_time TIMESTAMP
		NOT NULL DEFAULT CURRENT_TIMESTAMP
		ON UPDATE CURRENT_TIMESTAMP);-- 登錄時間，如果希望login_time列自動更新，需要配置

INSERT INTO t13(birthday,job_time)
VALUES('2022-11-11','2022-11-11 10:10:10');
#如果我們更新了t13的某條記錄，login_time會自動地以當前時間來進行更新

SELECT * FROM t13;

day02-MySQL基礎知識