• Mysql8.0 数据库表的基本操作

    1，创建表

    CREATE TABLE 表名称(

    字段名1 数据类型，

    字段名2 数据类型，

    字段名3 数据类型，

    )

    1. mysql> create table tb_student(
    2.     -> sid INT,
    3.     -> sname VARCHAR(20)
    4.     -> );
    5. Query OK, 0 rows affected (1.19 sec)
    6.  
    7. mysql> create table tb_teacher(
    8.     -> tid INT,
    9.     -> tname VARCHAR(20)
    10.     -> );
    11. Query OK, 0 rows affected (1.19 sec)

    2，查看表

    SHOW TABLES;

    1. mysql> show tables;
    2. +------------------+
    3. | Tables_in_test01 |
    4. +------------------+
    5. | tb_student       |
    6. | tb_teacher       |
    7. +------------------+
    8. 2 rows in set (0.00 sec)

    使用show tables；前必须先use 数据库名称，选择具体的数据库。

    如果不选择数据库，那么使用如下语句：

    SHOW TABLES FROM 数据库名称；

    1. mysql> show tables from test01;
    2. +------------------+
    3. | Tables_in_test01 |
    4. +------------------+
    5. | tb_student       |
    6. | tb_teacher       |
    7. +------------------+
    8. 2 rows in set (0.00 sec)

    3，查看表结构

    DESCRIBE 表名称；简写 DESC 表名称;

    或者

    SHOW CREATE TABLE 表名称;

    3.1 DESC 表名称
    1. mysql> desc tb_student;
    2. +-------+-------------+------+-----+---------+-------+
    3. | Field | Type        | Null | Key | Default | Extra |
    4. +-------+-------------+------+-----+---------+-------+
    5. | sid   | int         | YES  |     | NULL    |       |
    6. | sname | varchar(20) | YES  |     | NULL    |       |
    7. +-------+-------------+------+-----+---------+-------+
    8. 2 rows in set (0.00 sec)
    3.2 SHOW CREATE TABLE 表名称
    1. mysql> show create table tb_student\G
    2. *************************** 1. row ***************************
    3.        Table: tb_student
    4. Create Table: CREATE TABLE `tb_student` (
    5.   `sid` int DEFAULT NULL,
    6.   `sname` varchar(20) DEFAULT NULL
    7. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    8. 1 row in set (0.00 sec)

    这种方式查看数据库表定义不只是字段，还有字符集、字符集校对规则和存储引擎（例如，InnoDB）等信息。

    4，修改表

    4.1 增加字段

    ALTER TABLE 表名称 ADD COLUMN 新增字段名 数据类型;

    1. mysql> alter table tb_student add column score INT;
    2. Query OK, 0 rows affected (0.65 sec)
    3. Records: 0  Duplicates: 0  Warnings: 0
    4.  
    5. mysql> desc tb_student;
    6. +-------+-------------+------+-----+---------+-------+
    7. | Field | Type        | Null | Key | Default | Extra |
    8. +-------+-------------+------+-----+---------+-------+
    9. | sid   | int         | YES  |     | NULL    |       |
    10. | sname | varchar(20) | YES  |     | NULL    |       |
    11. | score | int         | YES  |     | NULL    |       |
    12. +-------+-------------+------+-----+---------+-------+
    13. 3 rows in set (0.00 sec)

    默认在所有字段末尾添加新字段。 可以通过AFTER和FIRST指定添加位置。

    ALTER TABLE 表名称 ADD COLUMN 新增字段名 数据类型 AFTER 字段名;

    ALTER TABLE 表名称 ADD COLUMN 新增字段名 数据类型 FIRST;

    1. mysql> desc tb_student;
    2. +-------+-------------+------+-----+---------+-------+
    3. | Field | Type        | Null | Key | Default | Extra |
    4. +-------+-------------+------+-----+---------+-------+
    5. | sid   | int         | YES  |     | NULL    |       |
    6. | sname | varchar(20) | YES  |     | NULL    |       |
    7. | score | int         | YES  |     | NULL    |       |
    8. +-------+-------------+------+-----+---------+-------+
    9. 3 rows in set (0.00 sec)
    10.  
    11. mysql> alter table tb_student add column age INT AFTER sname;
    12. Query OK, 0 rows affected (0.80 sec)
    13. Records: 0  Duplicates: 0  Warnings: 0
    14.  
    15. mysql> desc tb_student;
    16. +-------+-------------+------+-----+---------+-------+
    17. | Field | Type        | Null | Key | Default | Extra |
    18. +-------+-------------+------+-----+---------+-------+
    19. | sid   | int         | YES  |     | NULL    |       |
    20. | sname | varchar(20) | YES  |     | NULL    |       |
    21. | age   | int         | YES  |     | NULL    |       |
    22. | score | int         | YES  |     | NULL    |       |
    23. +-------+-------------+------+-----+---------+-------+
    24. 4 rows in set (0.00 sec)
    4.2 删除字段

    ALTER TABLE 表名称 DROP COLUMN 字段名;

    1. mysql> desc tb_student;
    2. +-------+-------------+------+-----+---------+-------+
    3. | Field | Type        | Null | Key | Default | Extra |
    4. +-------+-------------+------+-----+---------+-------+
    5. | sid   | int         | YES  |     | NULL    |       |
    6. | sname | varchar(20) | YES  |     | NULL    |       |
    7. | age   | int         | YES  |     | NULL    |       |
    8. | score | int         | YES  |     | NULL    |       |
    9. +-------+-------------+------+-----+---------+-------+
    10. 4 rows in set (0.00 sec)
    11.  
    12. mysql> alter table tb_student drop column age;
    13. Query OK, 0 rows affected (0.88 sec)
    14. Records: 0  Duplicates: 0  Warnings: 0
    15.  
    16. mysql> desc tb_student;
    17. +-------+-------------+------+-----+---------+-------+
    18. | Field | Type        | Null | Key | Default | Extra |
    19. +-------+-------------+------+-----+---------+-------+
    20. | sid   | int         | YES  |     | NULL    |       |
    21. | sname | varchar(20) | YES  |     | NULL    |       |
    22. | score | int         | YES  |     | NULL    |       |
    23. +-------+-------------+------+-----+---------+-------+
    24. 3 rows in set (0.00 sec)
    4.3 修改字段名、数据类型、位置等

    修改字段名称可以通过CHANGE关键字实现，而修改字段数据类型、位置等属性则需要通过MODIFY关键字实现。

    修改字段名称：

    ALTER TABLE 表名称 CHANGE COLUMN 字段名 新字段名 数据类型;

    1. mysql> alter table tb_student change column score chinese INT;
    2. Query OK, 0 rows affected (0.79 sec)
    3. Records: 0  Duplicates: 0  Warnings: 0
    4.  
    5. mysql> desc tb_student;
    6. +---------+-------------+------+-----+---------+-------+
    7. | Field   | Type        | Null | Key | Default | Extra |
    8. +---------+-------------+------+-----+---------+-------+
    9. | sid     | int         | YES  |     | NULL    |       |
    10. | sname   | varchar(20) | YES  |     | NULL    |       |
    11. | chinese | int         | YES  |     | NULL    |       |
    12. +---------+-------------+------+-----+---------+-------+
    13. 3 rows in set (0.00 sec)

    修改字段类型：

    ALTER TABLE 表名称 MODIFY COLUMN 字段名 新数据类型;

    ALTER TABLE 表名称 MODIFY COLUMN 字段名 数据类型 AFTER 字段名;

    ALTER TABLE 表名称 MODIFY COLUMN 字段名 数据类型 FIRST;

    1. mysql> alter table tb_student modify column sname VARCHAR(30);
    2. Query OK, 0 rows affected (0.26 sec)
    3. Records: 0  Duplicates: 0  Warnings: 0
    4.  
    5. mysql> desc tb_student;
    6. +---------+-------------+------+-----+---------+-------+
    7. | Field   | Type        | Null | Key | Default | Extra |
    8. +---------+-------------+------+-----+---------+-------+
    9. | sid     | int         | YES  |     | NULL    |       |
    10. | sname   | varchar(30) | YES  |     | NULL    |       |
    11. | chinese | int         | YES  |     | NULL    |       |
    12. +---------+-------------+------+-----+---------+-------+
    13. 3 rows in set (0.00 sec)
    4.4 修改表名称

    ALTER TABLE 表名称 RENAME TO 新表名称;

    RENAME TABLE 表名称 TO 新表名称;

    1. mysql> show tables;
    2. +------------------+
    3. | Tables_in_test01 |
    4. +------------------+
    5. | tb_student       |
    6. | tb_teacher       |
    7. +------------------+
    8. 2 rows in set (0.00 sec)
    9.  
    10. mysql> alter table tb_student rename to student;
    11. Query OK, 0 rows affected (0.75 sec)
    12.  
    13. mysql> show tables;
    14. +------------------+
    15. | Tables_in_test01 |
    16. +------------------+
    17. | student          |
    18. | tb_teacher       |
    19. +------------------+
    20. 2 rows in set (0.00 sec)
    4.5 修改表的编码方式

    ALTER TABLE 表名称 CHARACTER SET 字符集名称 COLLATE 字符集对应的校对规则;

    1. mysql> show create table tb_student\G
    2. *************************** 1. row ***************************
    3.        Table: tb_student
    4. Create Table: CREATE TABLE `tb_student` (
    5.   `sid` int DEFAULT NULL,
    6.   `sname` varchar(30) DEFAULT NULL,
    7.   `chinese` int DEFAULT NULL
    8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
    9. 1 row in set (0.00 sec)
    10.  
    11. mysql> alter table tb_student character set utf8 collate utf8_general_ci;
    12. Query OK, 0 rows affected, 2 warnings (0.34 sec)
    13. Records: 0  Duplicates: 0  Warnings: 2
    14.  
    15. mysql> show create table tb_student\G
    16. *************************** 1. row ***************************
    17.        Table: tb_student
    18. Create Table: CREATE TABLE `tb_student` (
    19.   `sid` int DEFAULT NULL,
    20.   `sname` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
    21.   `chinese` int DEFAULT NULL
    22. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3
    23. 1 row in set (0.00 sec)

    5，删除表

    DROP TABLE 表名称;

    1. mysql> show tables;
    2. +------------------+
    3. | Tables_in_test01 |
    4. +------------------+
    5. | tb_student       |
    6. | tb_teacher       |
    7. +------------------+
    8. 2 rows in set (0.00 sec)
    9.  
    10. mysql> drop table tb_teacher;
    11. Query OK, 0 rows affected (2.60 sec)
    12.  
    13. mysql> show tables;
    14. +------------------+
    15. | Tables_in_test01 |
    16. +------------------+
    17. | tb_student       |
    18. +------------------+
    19. 1 row in set (0.00 sec)

  • 相关阅读:
    分析报告有样板了-奥威BI数据可视化报表模板
    kafka命令
    学习太极创客 — ESP8226 （十一）用 WiFiManager 库配网
    深入探讨 AutoGPT：彻底改变游戏的自主 AI
    Callable接口（类似于Runnable）
    langchain LLMRequestsChain
    Android动态日志ProtoLog简介和使用
    ChatGPT4.0怎么收费
    矩阵系统全方位管理多平台1000多个账号，实现精准化运营获客！
    Flutter 离线数据方案 Flutter_Data 包
  • 原文地址：https://blog.csdn.net/xharvard/article/details/136328722