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
主鍵可以寫為KEY或PRIMARY KEY自動編號
自動編號(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] ...
[注意]不要隨意改變數據列和數據表的名字