## 1. 连接到TiDB
$ mysql -h 192.168.1.401 -P 4000 -u root -p

## 2. 创建数据库
mysql> CREATE DATABASE IF NOT EXISTS fgedudb
-> CHARACTER SET utf8mb4
-> COLLATE utf8mb4_general_ci;

## 3. 查看数据库
mysql> SHOW DATABASES;
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
| fgedudb |
+——————–+

## 4. 选择数据库
mysql> USE fgedudb;

## 5. 查看数据库信息
mysql> SHOW CREATE DATABASE fgedudb;
+———-+———————————————————————————————————————————-+
| Database | Create Database |
+———-+———————————————————————————————————————————-+
| fgedudb | CREATE DATABASE `fgedudb` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */ /*!80016 DEFAULT ENCRYPTION=’N’ */ |
+———-+———————————————————————————————————————————-+

## 1. 创建用户表
mysql> CREATE TABLE IF NOT EXISTS fgedu_users (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(50) NOT NULL,
-> age INT DEFAULT 0,
-> email VARCHAR(100) UNIQUE,
-> phone VARCHAR(20),
-> address VARCHAR(255),
-> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

## 2. 创建订单表
mysql> CREATE TABLE IF NOT EXISTS fgedu_orders (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> user_id INT,
-> order_no VARCHAR(32) UNIQUE,
-> amount DECIMAL(10,2) NOT NULL,
-> status VARCHAR(20) DEFAULT ‘pending’,
-> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> FOREIGN KEY (user_id) REFERENCES fgedu_users (id) ON DELETE CASCADE ON UPDATE CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

## 3. 创建产品表
mysql> CREATE TABLE IF NOT EXISTS fgedu_products (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> name VARCHAR(100) NOT NULL,
-> price DECIMAL(10,2) NOT NULL,
-> stock INT DEFAULT 0,
-> description TEXT,
-> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

## 4. 创建订单详情表
mysql> CREATE TABLE IF NOT EXISTS fgedu_order_items (
-> id INT PRIMARY KEY AUTO_INCREMENT,
-> order_id INT,
-> product_id INT,
-> quantity INT NOT NULL,
-> price DECIMAL(10,2) NOT NULL,
-> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
-> FOREIGN KEY (order_id) REFERENCES fgedu_orders (id) ON DELETE CASCADE ON UPDATE CASCADE,
-> FOREIGN KEY (product_id) REFERENCES fgedu_products (id) ON DELETE CASCADE ON UPDATE CASCADE
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

## 5. 查看表结构
mysql> DESCRIBE fgedu_users;
+————+————–+——+—–+——————-+———————————————–+
| Field | Type | Null | Key | Default | Extra |
+————+————–+——+—–+——————-+———————————————–+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | | NULL | |
| age | int | YES | | 0 | |
| email | varchar(100) | YES | UNI | NULL | |
| phone | varchar(20) | YES | | NULL | |
| address | varchar(255) | YES | | NULL | |
| created_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+————+————–+——+—–+——————-+———————————————–+

## 1. 添加列
mysql> ALTER TABLE fgedu_users ADD COLUMN gender VARCHAR(10) DEFAULT ‘unknown’;

## 2. 修改列
mysql> ALTER TABLE fgedu_users MODIFY COLUMN age TINYINT DEFAULT 0;

## 3. 删除列
mysql> ALTER TABLE fgedu_users DROP COLUMN address;

## 4. 添加索引
mysql> ALTER TABLE fgedu_users ADD INDEX idx_name (name);
mysql> ALTER TABLE fgedu_orders ADD INDEX idx_user_id (user_id);

## 5. 修改表名
mysql> ALTER TABLE fgedu_products RENAME TO fgedu_goods;

## 6. 查看修改后的表结构
mysql> DESCRIBE fgedu_users;
+————+————–+——+—–+——————-+———————————————–+
| Field | Type | Null | Key | Default | Extra |
+————+————–+——+—–+——————-+———————————————–+
| id | int | NO | PRI | NULL | auto_increment |
| name | varchar(50) | NO | MUL | NULL | |
| age | tinyint | YES | | 0 | |
| email | varchar(100) | YES | UNI | NULL | |
| phone | varchar(20) | YES | | NULL | |
| gender | varchar(10) | YES | | unknown | |
| created_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED |
| updated_at | datetime | YES | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+————+————–+——+—–+——————-+———————————————–+

## 7. 查看索引
mysql> SHOW INDEX FROM fgedu_users;
+————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+
| fgedu_users | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| fgedu_users | 0 | email | 1 | email | A | 0 | NULL | NULL | YES | BTREE | | |
| fgedu_users | 1 | idx_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | |
+————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+—————+