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;