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;