MySQL資料庫應用總結（四）—MySQL資料庫表的基本操作（上）

SQL語法預覽：

查看所有資料庫表; 【show tables;】

創建數據表：【create table 表名(欄位名1,數據類型 列級別約束條件 默認值,欄位名2,數據類型 列級別約束條件 默認值,…表級別約束條件); 】

單子段主鍵約束：【create table 表名(欄位名,數據類型 列級別約束條件 默認值 primary key,…); 】

多欄位聯合主鍵：【create table 表名(欄位名,數據類型 列級別約束條件 默認值,…primary key (欄位1,欄位2,…,欄位n)); 】

定義外鍵約束：【create table 子表名(欄位名,數據類型 列級別約束條件 默認值primary key,…constraint 外鍵約束名稱 foreign key(欄位) references 父表名(主鍵欄位)); 】

定義非空約束：【create table 表名(欄位1 數據類型 默認值 primary key,欄位2 數據類型 默認值 not null,…); 】

直接定義唯一約束：【create table 表名(欄位1 數據類型 默認值 primary key,欄位2 數據類型 默認值 unique,…);】

靠後定義唯一約束：【create table 表名(欄位1 數據類型 默認值 primary key,欄位2 數據類型默認值 ,…,constraint sth unique(欄位1，欄位2,…)); 】

定義默認值約束：【create table 表名(欄位1 數據類型 默認值 primary key,欄位2 數據類型 default 默認值 ,…); 】

定義表屬性的自動增加：【create table 表名(欄位1 數據類型 默認值 primary key auto_increment,欄位2 數據類型 默認值 ,…); 】

查看錶的基本結構：【describe 表名; 或者 desc 表名; 】

查看錶的詳細結構：【show create table 表名G; 】

修改表名：【alter table 舊錶名 rename 新表名; 】

修改欄位的數據類型：【alter table 表名 modify 欄位名 數據類型; 】

修改欄位名：【alter table表名 change 舊欄位名 新欄位名 新數據類型; 】

添加無完整性約束欄位：【alter table 表名add 新欄位 數據類型; 】

添加有完整性約束欄位：【alter table 表名add 新欄位 數據類型 約束條件; 】

添加欄位在第一列: 【alter table 表名 add 欄位名 數據類型 first; 】

添加欄位到指定列：【alter table 表名 add 欄位名 數據類型 after 已存在欄位名; 】

刪除欄位：【alter table表名 drop欄位名; 】

修改欄位為表第一個欄位：【alter table 表名modify 欄位名 數據類型 first; 】

修改欄位到指定欄位後：【alter table 表名modify 欄位1 數據類型 after欄位2; 】

更改表的存儲引擎：【alter table 表名 engine=更改後的存儲引擎名; 】

刪除表的違建約束：【alter table 表名 drop foreign key 外鍵約束名; 】

刪除資料庫表：【drop table if exists 表名1,表名2,…表名n; 】

詳解：

一、 創建資料庫表

創建資料庫表得先創建或者選擇資料庫，這樣表就包含在當前資料庫內，方便後續管理。注意：不要重複創建和選擇同一資料庫，不然會報錯！

1. 創建表的語法形式

語法結構：create

案例：創建資料庫db_demo1，在資料庫中創建表tb_emploee，表中包含欄位：id，name，departmentID，salary。

命令語句：

查看當前所有資料庫show databases;

創建資料庫：create database db_demo1;

選擇資料庫：use db_demo1;

創建包含欄位表：create table tb_employee(id int(11),name varchar(25),departmentIdint(11),salary float);

2. 查看資料庫表

語法結構：use 資料庫名;

show tables;

案例：查看資料庫db_demp1下所有表,SQL語句及運行結果如下：

命令語句：use db_demo1;

show tables;

執行結果：

看到在資料庫db_demo1下有一張表tb_employee，注意如果沒有選擇資料庫db_demo1，直接輸入show tables；是會報錯的，因為資料庫不知道你要查看哪個資料庫中的表。如下圖：

3. 定義主鍵約束

主鍵是唯一能夠表示一條記錄的欄位，他們之間是一一對應的關係，就像身份證號碼和人之間的關係一樣，通過主鍵可以定義不同數據表之間的關係。主鍵分為兩種類型：單欄位主鍵和多欄位聯合主鍵。

（1） 單欄位主鍵

·同時定義單欄位主鍵

語法結構：create table 表名(欄位名,數據類型 列級別約束條件 默認值 primary key,…);

案例：定義數據表tb_employee2,主鍵為id，SQL語句如下：

命令語句：create table tb_employee2(id int(11) primary key,name varchar (25),departmentId int(11),salary float);

執行結果：

·最後定義單欄位主鍵

語法結構：create table 表名(欄位名,數據類型 列級別約束條件 默認值,…primary

案例：定義數據表tb_employee3,主鍵為id，SQL語句如下：

命令語句：create table tb_employee3(id int(11),name varchar(25),departmentId int(11),salary float,primary key(id));

執行結果：

（2） 多欄位聯合主鍵

語法結構：create table 表名(欄位名,數據類型 列級別約束條件 默認值,…primary

案例：定義數據表tb_employee4,聯合主鍵為name,department，SQL語句如下：

命令語句：create table tb_employee4(name varchar(25),departmentId int(11),salary float,primary key(name,departmentId));

執行結果：

4. 定義外鍵約束

外鍵是表中的一個欄位，它不是本表的主鍵，但對應另外一個表的主鍵，一個表可以有一個或多個外鍵，多個外鍵欄位名稱不能相同。外鍵用來在兩個表之間建立連接，它可以是一列或多列。

外鍵對應的是參照完整性，一個表的外鍵可以是空值，如果不為空值，怎每一個外鍵值必須等於另一個表中主鍵的某個值。

語法結構：create table 子表名(欄位名,數據類型 列級別約束條件 默認值primary key，…constraint 外鍵約束名稱 foreign key(欄位) references 父表名(主鍵欄位));

先創建部門表tb_department1：

命令語句：create table tb_department1(id int(11) primary key,name varchar(22) not null,location varchar(50));

案例：定義數據表tb_employee5,讓它的鍵departmentId作為外鍵連接到tb_department1表的主鍵id，使用SQL語句如下：

命令語句：create table tb_employee5(id int(11) primary key,name varchar(25),departmentId int(11),salary float,constraint fk_employee_department1 foreign key(departmentId) references tb_department1(id));

執行結果：

5. 定義非空約束

非空約束是指欄位的值不能為空，對於使用了非空約束的欄位，如果用戶在添加數據時沒有指定值，數據系統會報錯。

語法結構：create table 表名(欄位1 數據類型 默認值 primary key,欄位2 數據類型 默認值 not null,…);

案例：定義數據表tb_employee6,指定員工的名稱不能為空，使用的SQL語句如下：

命令語句：create table tb_employee6(id int(11) primary key,name varchar(25) not null,departmentId int(11),salary float);

執行結果如下：

6. 定義唯一性約束

唯一性要求該列唯一，允許為空，但只能出現一個空值，唯一性能確保一列或幾列值不重複。

（1） 在定義玩列後直接定義唯一約束，語法如下：

語法結構：create table 表名(欄位1 數據類型 默認值 primary key,欄位2 數據類型 默認值 unique,…);

案例：定義數據表tb_department2，指定部門的名稱唯一，SQL語句如下：

命令語句：create table tb_department2(id int(11) primary key,name varchar(22) unique,location varchar(50));

執行結果如下：

（2） 在定義完所有列之後指定唯一約束，語法規則如下：

語法結構：create table 表名(欄位1 數據類型 默認值 primary key,欄位2 數據類型默認值 ,…,constraint sth unique(欄位1，欄位2,…));

案例：定義數據表tb_department3，指定部門的名稱唯一，使用的SQL語句如下：

命令語句：create table tb_department3(id int(11) primary key,name varchar(22),location varchar(50),constraint sth unique(name));

執行結果如下：

7. 定義默認值約束

默認約束可以指定某列的默認值，如果男性同學較多，性別就可以默認為「男」，不賦值情況下，會自動默認性別為「男」。語法規則如下：

語法結構：create table 表名(欄位1 數據類型 默認值 primary key,欄位2 數據類型 default 默認值 ,…);

案例：定義數據表tb_employee7，指定員工部門編號默認為1111，使用的SQL語句如下：

命令語句：create table tb_employee7(id int(11) primary key,name varchar(25) not null,departmentId int(11) default 1111,salary float);

執行結果如下：

8. 定義表屬性值的自動增加

在資料庫應用中，經常希望在每次插入新紀錄時，系統會自動生成欄位的主鍵值，這可以通過為主鍵添加AUTO_INCREMENT關鍵字來實現。MySQL中它的初始值默認為1。注意，一個表只能有一個一個欄位使用自增約束，且該欄位為主鍵的一部分。它可以是任意的整數類型（tinyint，smallint，int，bigint等）。

語法結構：create table 表名(欄位1 數據類型 默認值 primary key auto_increment,欄位2 數據類型 默認值 ,…);

案例：定義數據表tb_employee8，指定員工編號自動遞增，使用的SQL語句如下：

命令語句：create table tb_employee8(id int(11) primary key auto_increment,name varchar(25) not null,departmentId int(11),salary float);

執行結果如下：

二、 查看數據表結構

1. 查看錶的基本結構

用describe/desc查看欄位信息，其中包括欄位名，欄位數據類型，是否為主鍵、是否有默認值等。

語法結構：describe表名; 或者 desc 表名;

案例：用describe/desc語句查看錶tb_employee1和tb_department3的結構，使用的SQL語句如下：

命令語句：describe tb_employee;

desc tb_department3;

執行結果如下：

2. 查看錶的詳細結構

語法結構：show create table 表名G;

注意：如果不加G也可以執行，顯示結果可能非常混亂，加上G之後，可以顯示的更加直觀，更易於查看。

案例：查看tb_employee2的詳細信息。

命令語句：show create table tb_employee2G;

執行結果如下：

未完待續...