前端學資料庫之數據表操作

目錄

前面的話

　　mysql資料庫中的數據存儲在被稱為表(tables)的資料庫對象中。表是相關的數據項的集合，它由列(欄位)和行(記錄)組成。下麵將詳細介紹數據表操作

準備工作

　　在進行數據表操作之前，需要先登錄mysql伺服器，創建一個資料庫，並使用創建好的資料庫

創建數據表

　　下麵在db1資料庫中創建數據表tb1

CREATE TABLE [IF NOT EXISTS] table_name(column_name data_type,...)

　　在下麵的數據表tb1中，創建一個VARCHAR(20)的變長字元型的username欄位；創建一個TINYINT UNSIGNED的一個位元組長度的無符號(0-255)整型的age欄位；創建一個salary FLOAT(8,2) UNSIGNED的數字總長度為8，小數位數為2的無符號浮點數的salary欄位

查看數據表

SHOW TABLES [FROM db_name] [LIKE 'pattern' | WHERE expr]

查看數據表的列項

SHOW COLUMNS FORM tbl_name

記錄操作

　　記錄操作的第一步是寫入記錄

　　在寫入記錄時，要註意的是，如果沒有省略的欄位，則按照參數順序直接賦值即可；如果有省略的欄位，則欄位名和參數值都需要寫出來

INSERT [INTO] tbl_name [(col_name,...)] VALUES(VAL,...)

　　下麵來查找記錄

SELECT EXPR,...FROM tbl_name

記錄約束

空值

NULL         欄位值可以為空
NOT NULL     欄位值禁止為空

　　首先，創建一個數據表tb2，username欄位禁止為空，而age欄位可以為空。插入記錄時，如果username欄位為空，則提示錯誤

主鍵

　　每張數據表只能存在一個主鍵(PRIMARY KEY)，主鍵保證記錄的唯一性，且自動為NOT NULL

自動編號

　　自動編號(AUTO_INCREMENT)必須與主鍵組合使用，預設情況下，起始值為1，每次增量為1

唯一

　　唯一約束(UNIQUE KEY)是指選定的記錄中不可以存在相同值的情況，這樣可以保證記錄的唯一性，唯一約束的欄位可以為空值NULL，每張數據表可以存在多個唯一約束

　　下麵記錄中對username欄位進行唯一約束限制，添加username為'Tom'記錄後，不允許再添加username為'Tom'記錄

預設值

　　當插入記錄時，如果沒有明確為欄位賦值，則自動賦予預設值(DEFAULT)

外鍵

　　外鍵約束(FOREIGN KEY)用來保持數據一致性和完整性，實現一對一或一對多的關係

　　外鍵列是指加入(FOREIGN KEY)的列，外鍵列參照的那一列叫做參照列，外鍵列和參數列必須具有相似的數據類型。其中數字的長度或是否有符號位必須相同；而字元的長度則可以不同

　　索引是一種特殊的文件，在InnoDB數據表上的索引是表空間的一個組成部分，它們包含著對數據表中所有記錄的引用指針。外鍵列和參照列必須創建索引，如果參照列不存在索引的話，MySQL將自動創建索引

　　子表指有外鍵列的表，子表所參照的表叫做父表。父表和子表必須使用相同的存儲引擎，而且禁止使用臨時表。數據表的存儲引擎只能是InnoDB

　　所以，首先需要修改mysql配置文件my.ini中的預設存儲引擎

default-storage-engine = INNODB

　　創建父表provices，參照列為id

　　創建子表users，外鍵列為pid

　　父表provices的參照列id為主鍵列，主鍵在創建的同時，會自動創建索引

　　下麵來查看父表provices的索引

　　下麵來查看子表users的索引

　　外鍵約束的參照操作中，一共存在四個選項。用來設置更新父表時，子表是否也進行相應操作

　　1、CASCADE: 從父表刪除或更新且自動刪除或更新子表中匹配的行

　　2、SET NULL: 從父表刪除或更新行，並設置子表中的外鍵列為NULL。如果使用該選項，必須保證子表列沒有指定NOT NULL

　　3、RESTRICT: 拒絕對父表的刪除或更新操作

　　4、NO ACTION: 標準SQL的關鍵字，在MYSQL中與RESTRICT相同

　　首先，創建父表provinces

　　創建子表users1，並設置選項cascade

　　在父表中插入記錄'A'、'B'、'C'

　　在子表中，插入名字'a1'、'a2'、'a3'、'a4'

　　刪除父表中id為2的記錄，並查看刪除後父表和子表的結果

　　在實際的開發過程中，我們很少使用物理的外鍵約束，很多都去使用邏輯的外鍵約束，因為物理的外鍵約束只有INNODB這種引擎才會支持，像我們另外的一種引擎MYISAM的引擎則不支持，反過來說，如果我想創建的數據表，假設存儲引擎為MYISAM，而且又想使用外鍵約束的話，其實是不可能實現的，所以說，我們在實際的項目開發中，我們不去定義物理的外鍵，所謂的邏輯外鍵指的是就是我們在定義兩張表的結構的時候，我們是按照存在的某種結構的方式去定義，但是不去使用FOREIGN KEY這個關鍵詞來定義

【表級約束和列級約束】

　　約束除了按照功能，分為上面介紹過的5種約束外，也可以按照作用範圍分為表級約束和列級約束

　　對一個數據列建立的約束，稱為列級約束，對多個數據列建立的約束，稱為表級約束。列級約束既可以在列定義時聲明，也可以在列定義後聲明。而表級約束只能在列定義後聲明

　　在實際開發中，用列級約束比較多，表級約束很少用，除此之外，在所有的約束中，並不是說每種約束都存在著表級或列級約束，其中，NOT NULL 非空約束，DEFAULT約束這兩種約束就不存在表級約束，它們只有列級約束，而對於其他的三種，像主鍵，唯一，外鍵，它們都可以存在表級和列級約束

列操作

添加單列

ALTER TABLE tb!_name ADD[COLUMN] col_name column_definition [FIRST|AFTER col_name];

　　添加單列有三個位置選擇，位於起始處，位於指定列的後面和位於最後

　　fisrt加入的放在整張表最前面，after放在指定列後面，不填則放在整張表最後

添加多列

ALTER TABLE tb1_name ADD[COLUMN] (col_name column_definition,...);

　　添加多列只能位於最後

刪除單列

ALTER TABLE tb1_name DROP [COLUMN] col_name

刪除多列

ALTER TABLE tb1_name DROP col1_name, DROP col2_name, ...

約束操作

添加主鍵約束

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

　　在未添加主鍵前，表users2有'username'、'pid'和'id'三個field

　　向欄位'id'添加主鍵約束

添加唯一約束

　　唯一約束與主鍵約束的不同之處在於，唯一約束可以存在多個欄位，而主鍵約束只能有一個

ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] UNIQUE [INDEX|KEY] [index_name] [index_type]

添加外鍵約束

ALTER TABLE tb1_name ADD [CONSTRAINT[symbol]] FOREIGN KEY [index_name] (index_col_name,...) reference_definition

添加或刪除預設約束

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

刪除主鍵約束

ALTER TABLE tbl_name DROP PRIMARY KEY

刪除唯一約束

ALTER TABLE tbl_name DROP {INDEX|KEY} index_name

刪除外鍵約束

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol

　　刪除外鍵約束後，如果索引也不要，可以接著刪除之索引

修改列

修改列定義

　　修改列定義指修改列的類型或位置

ALTER TALBE tb1_name MODIFY [COLUMN] col_name column_definition [FIRST|AFTER col_name]

　　下麵將欄位'id'的位置調整到第一個

　　下麵，將欄位'id'的類型由smallint轉換為tinyint

　　[註意]在由大範圍的類型轉換為小範圍類型時，可能會造成數據丟失

修改列名稱

　　使用下麵的CHANGE語法，比MODIFY語法更加強大，可以在修改列名稱的同時，修改列類型

ALTER TABLE tb1_name CHANGE [COLUMN] old_col_name new_col_name column_definition [FIRST|AFTER col_name]

數據表更名

方法一 

ALTER TABLE tb1_name RENAME [TO|AS] new_tb1_name

方法二

　　使用RENAME方法可以為多張數據表更名

RENAME TABLE tb1_name TO new_tbl_name [,tbl_name2 TO new_tbl_name2] ...

　　[註意]不要隨意改變數據列和數據表的名字