MySQL系列：資料表基本操作（2）

libingql發表於2017-02-10
MySql

1. 指定資料庫

mysql> use portal;

2. 資料庫表基本操作

2.1 檢視資料表

mysql> show tables;
+------------------+
| Tables_in_portal |
+------------------+
| product          |
+------------------+

3. 建立表

3.1 建立表語法

CREATE TABLE table_name (column_name column_type);
mysql> CREATE TABLE product
    -> (
    -> product_id INT,
    -> product_name VARCHAR(50)
    -> );

3.2 設定主鍵

　　單欄位主鍵：

column_name column_type PRIMARY KEY
mysql> CREATE TABLE product
    -> (
    -> product_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    -> product_name VARCHAR(50)
    -> );

　　多欄位主鍵：

PRIMARY KEY (column_name, ... , column_name)
mysql> CREATE TABLE product
    -> (
    -> product_id INT,
    -> category_id INT,
    -> product_name VARCHAR(50)，
    -> PRIMARY KEY (product_id, category_id)
    -> );

3.3 設定外來鍵

CONSTRAINT constraint_name FOREIGN KEY(column_name, ... , column_name) REFERENCES table_name(column_name, ... ,column_name)
mysql> CREATE TABLE category
    -> (
    -> category_id INT PRIMARY KEY,
    -> category_name VARCHAR(50)
    -> );
mysql> CREATE TABLE product
    -> (
    -> product_id INT PRIMARY KEY,
    -> product_name VARCHAR(50),
    -> category_id INT,
    -> CONSTRAINT FK_product_category FOREIGN KEY(category_id) REFERENCES category(category_id)
    -> );

3.4 設定非空約束

column_name column_type NOT NULL
mysql> CREATE TABLE product
    -> (
    -> product_id INT PRIMARY KEY,
    -> product_name VARCHAR(50) NOT NULL
    -> );

3.5 設定唯一約束

column_name column_type unique
mysql> CREATE TABLE product
    -> (
    -> product_id INT PRIMARY KEY,
    -> product_name VARCHAR(50) UNIQUE
    -> );

3.6 設定欄位值自動增加

column_name column_type AUTO_INCREMENT
mysql> CREATE TABLE product
    -> (
    -> product_id INT AUTO_INCREMENT PRIMARY KEY,
    -> product_name VARCHAR(50)
    -> );

　　MySQL規定自增長列必須為主鍵。

　　AUTO_INCREMENT預設從1開始，每次加1。

　　建立表時指定AUTO_INCREMENT自增值的初始值（即起始值）：

mysql> CREATE TABLE product
    -> (
    -> product_id INT AUTO_INCREMENT PRIMARY KEY,
    -> product_name VARCHAR(50)
    -> ) AUTO_INCREMENT = 100;

　　通過 ALTER TABLE 修改初始值（但是要大於表中的 AUTO_INCREMENT 自增值，否則設定無效）：

mysql> ALTER TABLE product AUTO_INCREMENT = 100;

　　查詢指定表的AUTO_INCREMENT值：

mysql> SELECT AUTO_INCREMENT FROM information_schema.TABLES WHERE table_name = "product";

　　檢視AUTO_INCREMENT相關的變數設定：

mysql> SHOW VARIABLES LIKE 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

　　其中，auto_increment_increment：自增量每次增加的值，

　　　　   auto_increment_offset：載入數值時的偏移值的個位值。

　　修改設定auto_increment_increment：

mysql> SET @@auto_increment_increment = 10;
mysql> SHOW VARIABLES LIKE 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 1     |
+--------------------------+-------+
mysql> INSERT INTO product(product_name) VALUES ('商品01'),('商品02'),('商品03'),('商品04'),('商品05');
mysql> SELECT product_id,product_name FROM product;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
|          1 | 商品01       |
|         11 | 商品02       |
|         21 | 商品03       |
|         31 | 商品04       |
|         41 | 商品05       |
+------------+--------------+

　　修改設定auto_increment_increment及auto_increment_offset：

mysql> SET @@auto_increment_increment = 10;
mysql> SET @@auto_increment_offset = 5;
mysql> SHOW VARIABLES LIKE 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 10    |
| auto_increment_offset    | 5     |
+--------------------------+-------+
mysql> INSERT INTO product(product_name) VALUES ('商品01'),('商品02'),('商品03'),('商品04'),('商品05');
mysql> SELECT product_id,product_name FROM product;
+------------+--------------+
| product_id | product_name |
+------------+--------------+
|          5 | 商品01       |
|         15 | 商品02       |
|         25 | 商品03       |
|         35 | 商品04       |
|         45 | 商品05       |
+------------+--------------+

　　當auto_increment_offset設定的值大於auto_increment_increment值時，auto_increment_offset設定無效。

3.7 設定欄位預設值

column_name column_type DEFAULT default_value
mysql> CREATE TABLE product
    -> (
    -> product_id INT PRIMARY KEY AUTO_INCREMENT,
    -> product_name VARCHAR(50),
    -> unit_price FLOAT DEFAULT 0
    -> );

4. 檢視錶

4.1 檢視錶基本結構語句 DESC

DESC table_name
mysql> DESC product;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| product_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| product_name | varchar(50) | YES  |     | NULL    |                |
| unit_price   | float       | YES  |     | 0       |                |
+-------------+-------------+------+-----+---------+----------------+
3 rows in set (0.02 sec)

4.2 檢視錶詳細結構語句SHOW CREATE TABLE

SHOW CREATE TABLE table_name;
mysql> SHOW CREATE TABLE product;

5. 修改表

5.1 修改表名

ALTER TABLE old_table_name RENAME [TO] new_table_name;
mysql> ALTER TABLE product RENAME product1;

5.2 修改欄位資料型別

ALTER TABLE table_name MODIFY column_name column_type FIRST | AFTER column_name
mysql> ALTER TABLE product MODIFY product_name VARCHAR(100);
mysql> ALTER TABLE product MODIFY product_name VARCHAR(100) NOT NULL;
mysql> ALTER TABLE product MODIFY product_name VARCHAR(100) FIRST;
mysql> ALTER TABLE product MODIFY product_name VARCHAR(100) AFTER id;

5.3 修改欄位名

ALTER TABLE table_name CHANGE old_column_name new_column_name new_column_type
mysql> ALTER TABLE product CHANGE product_name Name VARCHAR(50);

5.4 增加欄位

ALTER TABLE table_name ADD column_name column_type [CONSTRAINT] [FIRST | AFTER column_name];
mysql> ALTER TABLE product ADD unit_price FLOAT NOT NULL;
mysql> ALTER TABLE product ADD ID INT NOT NULL FIRST;
mysql> ALTER TABLE product ADD product_no VARCHAR(50) NOT NULL AFTER product_id;

5.5 刪除欄位

ALTER TABLE table_name DROP column_name;
mysql> ALTER TABLE product DROP ID;

5.6 修改欄位排列位置

ALTER TABLE table_name MODIFY column_name column_type FIRST | AFTER colunm_name;
mysql> ALTER TABLE product MODIFY product_no VARCHAR(50) FIRST;
mysql> ALTER TABLE product MODIFY product_no VARCHAR(50) AFTER product_id;

5.7 修改表儲存引擎

ALTER TABLE table_name ENGINE=engine_name;
mysql> ALTER TABLE product ENGINE = MyISAM;
mysql> SHOW CREATE TABLE product\G;

5.8 刪除表的外來鍵約束

ALTER TABLE table_name DROP FOREIGN KEY constraint_name;
mysql> CREATE TABLE category
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> category_name VARCHAR(100) NOT NULL,
    -> PRIMARY KEY(id)
    -> );
mysql> CREATE TABLE product
    -> (
    -> id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    -> category_id INT UNSIGNED NOT NULL,
    -> product_name VARCHAR(100) NOT NULL,
    -> PRIMARY KEY(id),
    -> CONSTRAINT fk_product_category FOREIGN KEY (category_id) REFERENCES category(id)
    -> );
mysql> ALTER TABLE product DROP FOREIGN KEY fk_product_category;

6. 刪除表

DROP TABLE [IF EXISTS] table_name, table_name, table_name, ...;
mysql> DROP TABLE product;

相關文章