學習mysql語法——基礎篇（一）

知識 06-23

前言
mysql
mysql語法--本篇學習都是通過使用Navicat Premium（資料庫管理工具），連接mysql數據.
本篇學習主要有兩個部分：
一、創建用戶，創建資料庫，給用戶分配許可權，刪除用戶許可權。

二、MYSQL中常見的數據類型
三、表-創建表、主鍵、外鍵
四、資料庫設計的三大範式
以下所有代碼全部在新建查詢表裡面使用mysql語法編輯。

/* SQL 多行注釋 */

-- SQL 單行注釋

創建用戶：CREATE USER "用戶名"@"主機名" IDENTIFIED BY "密碼";

主機名可以為空，為空默認為%許可權，表示所有主機可連接。

給用戶分配許可權： GRANT 許可權名 ON 資料庫名.表明 TO 用戶名@主機名

刪除用戶許可權： REVOKE 許可權名 ON 資料庫名.表明 FROM 用戶名@主機名;

創建資料庫： CREATE DATABASE [IF NOT EXISTS] 資料庫名[CHARACTER SET[=] "UTF8"];

<<<如果省略 [IF NOT EXISTS] 在重複創建資料庫時，會報錯！

查詢本機中所有的資料庫：SHOW DATABASES

↓使用mydb這個資料庫↓，表示下面的查詢都將默認針對mydb資料庫

USE mydb;

查詢資料庫中所有數據表： SHOW TABLES [FROM 資料庫]

2 MYSQL中常見的數據類型

一、字元型：

① CHAR(N):固定N個字元長度的字元串，如果長度不夠會自動空格補齊；

② VARCHAR(N):存儲可邊長度的字元節。常用的 0~255；

③ TEXT:存儲可變長度的字元串。(常用語發布文章等大段內容)0~((2^16-1)*10^2);

④ TINYTEXT:0~((2^8-1)*10);

⑤ MEDIUMTEXT：0~((2^24-1)*10^3)；

⑥ LINGTEXT:0~((2^32-1)*10^4)；

⑦ enum("男","女")：枚舉類型，欄位只能容納枚舉出的數據。

二、整形：

① TINYINT: 無符號0~2^8-1 有符號 -2^7~2^7-1；

② SMALLINT: 無符號0~2^16-1 有符號 -2^15~2^15-1；

③ MEDIUMINT: 無符號0~2^24-1 有符號 -2^23~2^23-1；

④ INT: 無符號0~2^32-1 有符號 -2^31~2^31-1 最常用！

⑤ BIGINT: 無符號0~2^64-1 有符號 -2^63~2^63-1；

三、浮點型：

① FLOAT：可以精確到小數點後7位有效數字；

② DOUBLE: 可以精確到小數點後15位到16位有效數字；

四、日期時間數據類型

注意：由於時間存儲使用字元串或者時間戳存儲，所以資料庫中幾乎不用日期類型。

① DATE：存儲日期和時間數據

② TIMESTAMP：比DATE更精確

3、表-創建表、主鍵、外鍵

【創建表】：

CREATE TABLE [IF NOT EXISTS] 表單名（

IF NOT EXISTS 可以省略，省略後重複創建報錯.如果不省略，則創建時會檢測表是否已存在，如果表存在則不再執行創建語句

定義列：列名數據類型列表關鍵字

）

常用的列定義關鍵字：

① UNSIGNED：設置列表為無符號列。只能設置類型為數字類型的列

② AUTO_INCREMENT PRIMARY KEY 設置列為自動增長列。自動增長列必須是主鍵。

③ NOT null：設置列為非空約束

④ UNIQUE：設置唯一性約束。該欄位不能出現重複值。

⑤ DEFAULT：設置默認值約束。

【主鍵】：

1、主鍵的注意事項？主鍵默認為空！主鍵默認唯一性約束！

只有主鍵才能設置自動增長（主鍵不一定自動增長，自動增長必須是主鍵）

2、設置主鍵的方式？

① 在列定義是設置： age SMALLINT(3) PRIMARY KEY,

② 在列定義完成後設置： PRIMARY KEY(age),

【外鍵】

1、設置外鍵有哪些注意事項

① 只有innodb的資料庫引擎支持外鍵，修改mysql.ini文件：default-storage-engine=INNODB

② 外鍵與參照列的數據類型必須相同。(數值型要求長度和無符號都相同，字元串要求類型相同，長度可以不同。)

③ 設置外鍵的欄位必須要有索引。如果沒有索引，設置外鍵時會自動生成一個索引。

2、設置外鍵的語法？

[CONSTRAINT 外鍵名] FOREIGN KEY (外鍵欄位) REFERENCES 參照表(參照欄位) [ON DELETE SET NULL on update CASCADE]-- 設置參照完整性

3、外鍵約束的參照完整性操作？

參照操作：當對參照表的參照欄位進行刪除或者更新是，外鍵表中的外鍵如何應對。

參照操作可選值：restrict 拒絕參照表刪除或更新參照欄位；(默認)

NO ACTION 與 restrict一樣，但這個指令只在mysql生效；

cascade 刪除或更新參照表的參照欄位時，外鍵表的記錄同步刪除或更新；（外鍵表和參照表同步）

set null 刪除或更新參照表的參照欄位時，外鍵表的外鍵設為null；

CREATE TABLE IF NOT EXISTS tb1(
-- IF NOT EXISTS 可以省略，省略後重複創建報錯.如果不省略，則創建時會檢測表是否已存在，如果表存在則不再執行創建語句
id INT(3),
`name` VARCHAR(255) NOT null, -- name是系統關鍵字，所以使用反引號``包裹
age SMALLINT(3) AUTO_INCREMENT PRIMARY KEY,
lalala INT UNIQUE,
height DOUBLE(3,2) DEFAULT 1.2 -- 設置默認值約束：默認值為1.2
-- PRIMARY KEY(age)
);

create table if not exists classes(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
classname VARCHAR(255) NOT NULL

);

CREATE table if not EXISTS `user`(
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
clsId INT UNSIGNED,
`name` VARCHAR(255) NOT NULL,
CONSTRAINT user_fk_classes FOREIGN KEY (clsid) REFERENCES classes(id) ON DELETE SET NULL on update CASCADE
)
-- auto_increment

-- 顯示錶結構
SHOW TABLES;

-- 顯示錶內容結構
SHOW COLUMNS FROM TB1;

-- 現實表的建表語句
show create TABLE tb1;

-- 刪除表
DROP TABLE IF EXISTS TB1;
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS `user`;

-- 修改表名
ALTER table tb1 rename tb2;

-- 修改欄位列
-- alter table 表名 change 舊列名新列名列定義 [first|after某一列]
-- first 將這個欄位調整為表格第一列； after某一列：將這個欄位放到某一列後面
alter table tb1 change height width VARCHAR(200) not NULL FIRST;

-- 刪除表中某一列
alter table tb1 drop name;

-- 新增一列：必選部分：alter table tb1 add haha DOUBLE(8,2)
alter table tb1 add haha DOUBLE(8,2) DEFAULT 1.2 after age;

-- 新增多列：不能調整列的位置，只能插在最後。
alter table tb1 add (
ha1 DOUBLE(3,2) UNSIGNED,
ha2 VARCHAR(255)
);

-- 同時修改多表明 rename table tb3 to tb1[,`USER`to user1];
rename table tb2 to tb1,`USER`to user1;

-- 增加主鍵約束
alter table tb1 add PRIMARY KEY(id);

-- 刪除主鍵約束
alter table tb1 drop PRIMARY KEY;

-- 新增唯一性約束
ALTER table tb1 add unique key(ha1);

-- 刪除唯一性約束：由於創建唯一性約束會默認創建索引，所以刪除時，需刪除索引
ALTER table tb1 drop index ha1;

-- 設置默認值約束：前提必須設置default 屬性
ALTER table tb1 alter ha1 set default 20;

-- 刪除默認值約束
ALTER table tb1 alter haha drop default;

-- 設置外鍵約束必選部分 alter table tb1 add foreign key (clsid)REFERENCES classes(id)
alter table tb1 add constraint waijianming foreign key (clsid)REFERENCES classes(id) ON DELETE SET NULL on update CASCADE;

-- 刪除外鍵約束，由於常見外鍵時會生成索引，所以刪除外鍵後，需要刪索引。
alter table tb1 drop foreign key waijianming;

alter table tb1 drop INDEX waijianming;

SHOW COLUMNS FROM TB1;

4 資料庫的三大範式

1、第一範式（1NF）：數據表中的每一列（欄位），必須是不可拆分的最小單元。也就是確保每一列的原子性。

例如： userInfo："山東省煙台市 13181621008"

userads："山東省煙台市" userTel："13181621008"

2、第二範式（2NF）：滿足1NF後，要求：表中的所有列，都必須依賴於主鍵，而不能有任何一列與主鍵沒有關係。也就是說，一個表只描述一件事情。

例如：訂單表，只能描述訂單相關的信息，所以所有的欄位都必須與訂單ID相關;

產品表，只能描述產品相關的信息，所以所有的欄位都必須與產品ID相關；

因此：不能在同一張表中同事出現訂單信息與產品信息。

3、第三範式（3NF）：表中的每一列都要與主鍵直接相關，而不是間接相關。（表中的每一列，只能依賴於主鍵）。