MySql初步學習

標籤：ping   串連   結構   create   lte   logs   constrain   1.2   cti   

  建立使用者: CREATE USER ‘使用者名稱‘[@‘主機名稱‘] IDENTIFIED BY ‘密碼‘;
  主機名稱可以為空白，為空白預設為%許可權，表示所有主機可串連。
  create USER ‘jredu‘@‘loaclhost‘ IDENTIFIED by ‘jredu‘;
 
 
  給使用者指派許可權: GRANT 許可權名 on 資料庫名.表名 to 使用者@主機
  ALL表示所有許可權，*.* 表示所有資料庫中的表
 GRANT ALL ON *.* to ‘jredu‘@‘loaclhost‘;
 
  REVOKE FROM 刪除使用者權限
 REVOKE ALL ON *.*from ‘jredu‘@‘loaclhost‘;
 
 GRANT SELECT,INSERT,UPDATE,DELETE ON pingjiao.`admin` to ‘jredu‘@‘loacalhost‘;
 
 建立資料庫：create DATABASE [IF NOT EXISTS] 資料庫名 [CHARACTER SET [=] ‘utf8‘];
 如果省略if NOT EXISTS 再重複建立資料庫時，會報錯！
 
 CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET ‘utf8‘;
 
  刪除資料庫： DROP DATABASE [IF EXISTS] 資料庫名;
 DROP DATABASE IF EXISTS mydb;
 
  查詢本機中所有資料庫
 SHOW DATABASES;
 
 
  使用mydb這個資料庫，表示下面的查詢都將針對mydb資料庫。
 USE mydb;

 查詢資料庫中所有資料表
 SHOW TABLES;

【資料類型】

 一、字元型：
 ① CHAR(N):固定N個長度的字串，如果長度不夠會自動空格補齊。N的範圍(0-255)
 ② VARCHAR(): 儲存可變長度的字串，最常用的字串類型。0-2^16-1 *10;
 ③ TEXT：可以儲存可變長度的字串。 (常用於發布文章等大段內容) 0-2^16-1 *10^2
 ④ TINYTEXT： 0 ~ 2^8-1 *10
 ⑤ MEDIUMTEXT: 0 ~ 2^24-1 *10^3
 ⑥ LONGTEXT： 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^32 ~ 2^32-1
 ⑤ BIGINT：無符號 0~2^64-1 有符號 -2^63 ~ 2^63-1

 三、浮點型：
 ① FLOAT：精確到小數點後7位
 ② DOUBLE：精確到小數點後15~16位有效數字。
 
 四、日期時間資料類型
 注意：由於時間儲存使用字串或時間戳記儲存，所以資料庫中幾乎不同日期類型。
 ① DATE：儲存日期和時間資料
 ② TIMESTAMP：比DATE更精確。

【資料庫設計的三大範式】

1.第一範式(1NF):資料表中的每一列(欄位)，必須是不可拆分的最小單元。也就是說
要確保每一列的原子性。

 

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

 

 

例如：訂單表，只能描述訂單相關的資訊，所以所有的欄位必須與訂單ID相關；
產品表，只能描述產品相關的資訊，所以所有的欄位必須與產品ID相關；
因此：不能在同一張表中，同時出現訂單資訊和產品資訊。

3.第三範式(3NF):滿足2NF後，要求：表中的每一列都要與主鍵直接相關，而不是間接相關。
(表中的每一列，都只能依賴於主鍵)

 

例如：訂單表中，需要有客戶相關資訊，在分離出客戶表之後。
訂單表中，只需要有一個使用者ID即可。而不能有其他的客戶資訊。因為，其他的
使用者資訊是直接關聯與使用者ID，而不是關聯與訂單ID。

【第二範式與第三範式的本質區別】
在於有沒有分出兩張表，第二範式是說一張表中包含了多種不同的實體的屬性，
那麼必須要分成多張表。第三範式要求，已經分好了多張表的話，那麼，一張表中只能有另一張
表中的ID(主鍵)，而不能有其他的任何資訊(其他任何資訊，一律用主鍵在另一張表中查詢)。

 

USE mydb;

-- 建立表：
-- IF NOT EXISTS 可以省略，省略後重複建立報錯。

-- 定義列：列名 資料類型 列定義關鍵字
-- 常用的列定義關鍵字：
-- UNSIGNED：設定列為無符號列。 無符號只能限制類型為數字類型的列。
-- AUTO_INCREMENT:設定列為自動成長列。
-- 自動成長必須是主鍵！！主鍵不一定是自動成長，但一定是UNIQUE

 

/*【主鍵】
1.主鍵注意事項：主鍵預設非空！主鍵預設唯一性限制式！
只有主鍵才能設定自動成長(主鍵不一定自增，自增必須是主鍵)

2.設定主鍵的方式？
① 在列定義的時候：id int PRIMARY KEY
② 在列定義完成後設定： PRIMARY KEY(id)

PRIMARY KEY：設定主鍵約束。
NOT_NULL：設定非空約束。
UNIQUE：設定唯一性限制式，該欄位不能出現重複值。UNIQUE的可以是任意欄位。
DEFAULT：預設值約束。heighe DOUBLE(3,2) DEFAULT 1.2 heigh如果不輸入預設為1.2
FOREIGN KEY: 設定外鍵約束

【外鍵】
1.設定外鍵的注意事項：
① 只有INNODB的資料庫引擎支援外鍵
修改my.ini檔案設定default-storge-engin=INNODB
② 外加與參照列的資料類型必須相同。
(數值型要求長度和無符號都相同，字串要求類型相同，長度可以不同。)
③ 設定外鍵的欄位必須要有索引。如果沒有索引，設定外鍵時會自動產生索引。

2.設定外鍵的文法：
[CONSTRAINT 外鍵名] FOREIGN KEY (外鍵欄位) REFERENCES 參照表(參照欄位)

3.外鍵約束的參照操作：
參照操作：當對參照表的參照欄位進行刪除或更新時，外鍵表中的外鍵如何應對。
參照操作可選值： RESTRICT 拒絕參照表刪除或更新參照欄位。(預設)
no action 與 RESTRICT 相同，但這個指令只在MySql生效
CASCADE 刪除或更新參照表的參照欄位時，外鍵表的記錄同步刪除更新
SET NULL 刪除或更新參照表的參照欄位時，外鍵表的外鍵設為NULL

*/

DROP TABLE IF EXISTS tbl;

-- IF NOT EXISTS 建立表時會確認表是否存在，存在就不執行了
CREATE TABLE IF NOT EXISTS tbl(
id INT(1) UNSIGNED PRIMARY KEY auto_increment,
`name` VARCHAR(255) not NULL,-- name 是系統關鍵字，所以使用反引號包裹
age TINYINT(3) ,
height DOUBLE(3,2) DEFAULT 1.2,
clsId INT UNSIGNED,
CONSTRAINT tbl_fk_classes FOREIGN KEY (clsid)REFERENCES
)auto_increment=8 default-ENGINE;

DROP TABLE IF EXISTS `cls`;
CREATE TABLE IF NOT EXISTS cls(
id INT UNSIGNED PRIMARY KEY auto_increment,
classname VARCHAR(255) not NULL
);

-- SHOW COLUMNS FROM cls;

DROP TABLE IF EXISTS `user`;

CREATE TABLE IF NOT EXISTS `user`(
id INT UNSIGNED PRIMARY KEY auto_increment,
clsId INT UNSIGNED ,
`name` varchar(255) not null,
CONSTRAINT aaa FOREIGN KEY (clsId) REFERENCES cls(id) ON DELETE SET NULL ON UPDATE CASCADE
);

SHOW TABLES;
-- 顯示表結構 COLUMNS
SHOW COLUMNS from tbl;

-- 顯示表的建表語句
SHOW CREATE TABLE tbl;

-- 刪除表(如果存在則刪除)
DROP TABLE IF EXISTS tbl;

-- 修改表名
-- 1.alter table 舊錶名 RENAME [to] 新表名;
ALTER TABLE tbl RENAME tbl2;
-- 2.同時修改多表：rename TABLE 舊錶名 to 新表名[, 另一箇舊表名 TO 另一個新表名];
RENAME TABLE tbl2 TO tbl1;

-- 修改欄位 列
-- ALTER TABLE 表名 CHANGE 舊列名 新列名 列定義 [FIRST | AFTER 某一列列名]
-- FIRST將此欄位調整為表格第一列，after 將一列放到某一列後面。
ALTER TABLE tbl CHANGE `name` `username` VARCHAR(200) not NULL after age
-- MODIFY只修改列定義，不能修改列名
ALTER TABLE tbl MODIFY `name` VARCHAR(200) not NULL AFTER age

-- 刪除表中某一列
ALTER TABLE tbl DROP height

-- 為表新增某一列
ALTER TABLE tbl ADD height DOUBLE(3,2) DEFAULT 1.2 AFTER age
-- 為表新增多列,不能調整列的位置，只能插在最後。
ALTER TABLE tbl ADD(
weight DOUBLE(3,2) UNSIGNED,
school VARCHAR(255)
);

-- 增加主鍵約束
ALTER TABLE tbl ADD PRIMARY KEY(age)
-- 刪除主鍵約束
ALTER TABLE tbl DROP PRIMARY KEY

-- 新增唯一性限制式
ALTER TABLE tbl ADD UNIQUE KEY(username)
-- 刪除唯一性限制式：由於建立唯一性限制式會預設建立索引，所以刪除時，需刪除索引。
ALTER TABLE tbl DROP INDEX username

-- 設定預設值約束
ALTER TABLE tbl ALTER age SET DEFAULT 20;
-- 刪除預設值約束
ALTER TABLE tbl ALTER age DROP DEFAULT;

 

-- 設定外鍵約束(按照外鍵名刪除外鍵)
ALTER TABLE tbl add constraint waijianming foreign key (clsId)
REFERENCES cls(id)on delete set null update cascade;

-- 刪除外鍵約束。由於建立外鍵時會預設建立索引，所以刪除外鍵後，需要再刪除索引
ALTER TABLE tbl DROP FOREIGN KEY aaa;
ALTER TABLE tbl DROP INDEX aa

 

 

 

 

MySql初步學習