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;