mysql學習筆記-2

relsoul發表於2016-08-18
MySql筆記

約束

  • 約束保證資料的完整性與一致性
  • 約束分為表級約束和列級約束
  • 約束型別包括 -- NOT NULL (非空約束) -- PRIMARY KEY (主鍵約束) -- UNIQUE KEY (唯一約束) -- DEFAULT (預設約束) -- FOREIGN KEY (外來鍵約束)

外來鍵約束的要求解析

FOREIGN KEY 保證資料的一致性,完整性. 實現一對一或一對多關係

  • 父表與子表必須使用相同的儲存引擎,而且禁止使用臨時表.
  • 資料表的儲存引擎只能為InnoDB
  • 外來鍵列和參照列必須具有相似的資料型別.其中數字的長度或者是否有符號位必須相同;而字元的長度則可以不同.
  • 外來鍵列和參照列必須建立索引.如果外來鍵列不存在索引的話,MySQL將自動建立索引

顯示資料表的建立資料

SHOW CREATE TABLE tbl_name;

顯示資料表索引

SHOW INDEXES FROM tbl_name;

建立兩個表並且進行外來鍵約束

``` CREATE TABLE provinces( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, pname VARCHAR(20) NOT NULL );

CREATE TABLE users( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id) );

``` 外來鍵與主鍵資料型別必須的一直 比如 pname SMALLINT 對應的 pid SMALLINT 而且符號位也必須相同 有外來鍵的表稱為子表 子表參照的表稱為附表 參照列自動建立了索引

外來鍵約束的參照操作

  • CASCADE:從父表刪除或更新且自動刪除或更新子表中匹配的行
  • SET NULL:從父表刪除或更新行,並設定子表中的外來鍵列為NULL.如果使用該選項,必須保證子表列沒有指定NOT NULL.
  • RESTRICT:拒絕對父表的刪除或更新操作.
  • NO ACTION:標準的SQL關鍵字,在MYSQL中與RESTRICT相同.

外來鍵約束僅僅支援innodb引擎

CREATE TABLE user1( id SMALLINT UNSIGNED PRIMARY KEY AUTO_INCREMENT, username VARCHAR(10) NOT NULL, pid SMALLINT UNSIGNED, FOREIGN KEY(pid) REFERENCES provinces(id) ON DELETE CASCADE ); 參照操作需要開啟ON DELETE後再加上引數

新增資料

``` INSERT provinces(pname) VALUES("A"); INSERT provinces(pname) VALUES("B"); INSERT provinces(pname) VALUES("C");

//插入使用者資料

INSERT user1(username,pid) VALUES("Tom",3); ``` 插入使用者資料只需要對應設定外來鍵的id即可.

如果記錄沒有寫入成功,但是編號會自動遞增

刪除記錄

刪除provinces id為3的記錄 DELETE FROM provinces WHERE id = 3; 那麼相應的子表中外來鍵id為3的也會刪除.

表級約束與列級約束

  • 對一個資料列建立的約束,稱為列級約束.
  • 對多個資料列建立的約束,稱為表級約束.
  • 列級約束既可以在列定義時宣告,也可以在列定義後宣告.
  • 表級約束只能在列定義後宣告.

修改資料表

新增單列

ALTER TABLE tbl_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name] FIRST 指的是插入的這一列會在最前面.AFTER col_name則是在某一列後面

新增單列資料

SHOW COLUMNS FROM user1; ALTER TABLE user1 ADD age INT UNSIGNED NOT NULL DEFAULT 10; ALTER TABLE user1 ADD password VARCHAR(32) NOT NULL AFTER username;//插入某列後面 ALTER TABLE user1 ADD truename VARCHAR(20) NOT NULL FIRST; DEFAULT:未明確表明資料的時候.預設指定的資料 UNSIGNED:針對數值型型別 是否有符號

新增多列

新增多列無法指定位置

ALTER TABLE tbl_name ADD [COLUMN] (col_name column_definition);

給user1新增多列資料

ALTER TABLE user1 ADD( num INT UNSIGNED NOT NULL DEFAULT 1, sex ENUM("男","女") NOT NULL DEFAULT "男" );

刪除列

ALTER TABLE user1 DROP truename;

刪除多列

ALTER TABLE user1 DROP truename,DROP password;

修改列的時候可以刪除的時候再新增.中間通過逗號分隔.

刪除的同時再新增列

ALTER TABLE user1 DROP num, ADD pm FLOAT UNSIGNED DEFAULT 15;

新增主鍵約束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY [index_type](index_col_name,...)

範例一

CREATE TABLE user2( username VARCHAR(20) NOT NULL, pid SMALLINT UNSIGNED ); //增加主鍵 ALTER TABLE user2 ADD id SMALLINT UNSIGNED; //增加主鍵約束 ALTER TABLE user2 ADD CONSTRAINT PK_user2_id PRIMARY KEY (id); CONSTRAINT:用來起別名

新增唯一約束

ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type](index_col_name,...)

範例一

ALTER TABLE user2 ADD UNIQUE (username);

新增外來鍵約束

範例一

user2中pid參照provinces; ALTER TABLE user2 ADD FOREIGN KEY (pid) REFERENCES provinces (id);

新增/刪除預設約束

ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}

範例一

ALTER TABLE user2 ADD age TINYINT UNSIGNED NOT NULL; ALTER TABLE user2 ALTER age SET DEFAULT 15; 給age新增預設約束

刪除主鍵約束

任何一個表有且只有一個主鍵 ALTER TABLE user2 DROP PRIMARY KEY;

刪除主鍵索引

ALTER TABLE user2 DROP INDEX username; 刪除的僅僅是索引

顯示索引列表名稱

SHOW INDEXes FROM user2;

刪除外來鍵約束

刪除約束是刪除約束的名稱 ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbok

範例

``` //查詢外來鍵名稱

SHOW CREATE TABLE user2; //找到pid的外來鍵名稱為 user2_ibfk_1;然後進行刪除

ALTER TABLE user2 DROP FOREIGN KEY user2_ibfk_1;

雖然外來鍵不在了,但是索引還是存在的 如果不想要索引則刪除即可 ALTER TABLE user2 DROP INDEX pid; ```

修改列定義和更名資料表

修改列定義

ALTER TABLE tbl_name MODIFY [COLUMN] col_name column_definition [FIRST |AFTER col_name]

修改列順序

ALTER TABLE user2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; column_definition指的是列定義 不變

修改列定義-範例

ALTER TABLE user2 MODIFY id TINYINT UNSIGNED NOT NULL; 修改列定義的時候有可能會造成資料的丟失.

修改列名稱

ALTER TABLE tbl_name CHANGE [COLUMN] old_col_name new col_name column_definition [FIRST |AFTER col_name]

修改id的型別與名稱 ALTER TABLE user2 CHANGE pid p_id TINYINT UNSIGNED NOT NULL;

資料表更名

一. ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;

二. RENAME TABLE tbl_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2];

範例 ``` ALTER TABLE user2 RENAME users2;

RENAME TABLE users2 TO user2; ```

總結

約束 - 按功能化為:NOT NULL,PRIMARY KEY, UNIQUE KEY, DEFAULT,FOREIGN KEY - 按資料列的數目化為:表級約束,列級約束

修改資料表 - 針對欄位的操作:新增/刪除欄位,修改列定義,修改列名稱等 - 針對約束的操作:新增/刪除各種約束. - 針對資料表的操作:資料表更名(兩種方式).

列級約束：只能應用於一列上。 表級約束：可以應用於一列上，也可以應用在一個表中的多個列上。

預設約束(DEFAULT)與非空約束(NOT NULL)不存在表級約束

相關文章