MySQL系列:数据表基本操作

时间:2021-10-26 04:58:38

1. 指定数据库

mysql> use portal;

2. 数据库表基本操作

2.1 查看数据表

mysql> show tables;

3. 创建表

3.1 创建表语法

  语法:

CREATE TABLE table_name (column_name column_type);

  示例:

mysql> CREATE TABLE product
-> (
-> ProductID INT,
-> ProductName VARCHAR(50)
-> );

3.2 设置主键

  单字段主键:

column_name column_type PRIMARY KEY
mysql> CREATE TABLE Product
-> (
-> ProductID INT PRIMARY KEY,
-> ProductName VARCHAR(50)
-> );

  多字段主键:

PRIMARY KEY (column_name, ... , column_name)
mysql> CREATE TABLE Product
-> (
-> ProductID INT,
-> CategoryID INT,
-> ProductName VARCHAR(50),
-> PRIMARY KEY (ProductID,CategoryID)
-> );

3.3 设置外键

  语法:

CONSTRAINT constraint_name FOREIGN KEY(column_name, ... , column_name) REFERENCES table_name(column_name, ... ,column_name)

  示例:

mysql> CREATE TABLE category
-> (
-> CategoryID INT PRIMARY KEY,
-> CategoryName VARCHAR(50)
-> );
mysql> CREATE TABLE product
-> (
-> ProductID INT PRIMARY KEY,
-> ProductName VARCHAR(50),
-> CategoryID INT,
-> CONSTRAINT FK_product_category FOREIGN KEY(CategoryID) REFERENCES category(CategoryID)
-> );

3.4 设置非空约束

  语法:

column_name column_type NOT NULL

  示例: 

mysql> CREATE TABLE product
-> (
-> ProductID INT PRIMARY KEY,
-> ProductName VARCHAR(50) NOT NULL
-> );

3.5 设置唯一约束

  语法:

column_name column_type unique

  示例:

mysql> CREATE TABLE product
-> (
-> ProductID INT PRIMARY KEY,
-> ProductName VARCHAR(50) UNIQUE
-> );

3.6 设置字段值自动增加

  语法:

column_name column_type AUTO_INCREMENT

  示例:

mysql> CREATE TABLE product
-> (
-> ProductID INT AUTO_INCREMENT PRIMARY KEY,
-> ProductName VARCHAR(50)
-> );

3.7 设置字段默认值

  语法:

column_name column_type DEFAULT default_value

  示例: 

mysql> CREATE TABLE product
-> (
-> ProductID INT PRIMARY KEY AUTO_INCREMENT,
-> ProductName VARCHAR(50),
-> UnitPrice FLOAT DEFAULT 0
-> );

4. 查看表

4.1 查看表基本结构语句 DESC

  语法:

DESC table_name

  示例:

mysql> DESC product;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| ProductID | int(11) | NO | PRI | NULL | auto_increment |
| ProductName | varchar(50) | YES | | NULL | |
| UnitPrice | 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

  示例:

mysql> ALTER TABLE product MODIFY ProductName VARCHAR(100);
mysql> ALTER TABLE product MODIFY ProductName VARCHAR(100) NOT NULL;

5.3 修改字段名

  语法:

ALTER TABLE table_name CHANGE old_column_name new_column_name new_column_type

  示例:

mysql> ALTER TABLE product CHANGE ProductName Name VARCHAR(50);

5.4 增加字段

  语法:

ALTER TABLE table_name ADD column_name column_type [CONSTRAINT] [FIRST | AFTER column_name];

  示例:

mysql> ALTER TABLE product ADD UnitPrice FLOAT NOT NULL;
mysql> ALTER TABLE product ADD ID INT NOT NULL FIRST;
mysql> ALTER TABLE product ADD ProductNo VARCHAR(50) NOT NULL AFTER ProductID;

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 ProductNo VARCHAR(50) FIRST;
mysql> ALTER TABLE product MODIFY ProductNo VARCHAR(50) AFTER ProductID;

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> ALTER TABLE product DROP FOREIGN KEY FK_product_category;

6. 删除表

  语法:

DROP TABLE table_name ;

  示例:

mysql> DROP TABLE product;