查看数据表结构

• 查看表的字段信息 DESC 表名

  • NULL表示该列可以存储NULL值
  • key 指约束条件
  • Extra 指附加信息 例如主键自增

mysql> desc tb_empl;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | varchar(25) | YES | | NULL | || deptId | int(11) | YES | | NULL | || salary | float | YES | | NULL | |+--------+-------------+------+-----+---------+-------+4 rows in set (0.00 sec)

• 查看更加详细的表结构

mysql> show create table tb_dept2\G;*************************** 1. row *************************** Table: tb_dept2Create Table: CREATE TABLE `tb_dept2` ( `id` int(11) NOT NULL, `name` varchar(22) DEFAULT NULL, `location` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)ERROR:No query specified

修改数据表

MySQL使用 ALTER TABLE语句修改表

• 修改表名

mysql> ALTER TABLE tb_empl RENAME new_empl;Query OK, 0 rows affected (0.35 sec)# 可以看到tb_empl表名变为new_emplmysql> SHOW TABLES;+-------------------+| Tables_in_test_db |+-------------------+| new_empl || tb_dept2 || tb_dept3 || tb_dept7 || tb_dept8 || tb_emp5 || tb_temp6 |+-------------------+7 rows in set (0.02 sec)

• 修改字段的数据类型

# 没有修改之前的表结构数据类型mysql> DESC NEW_EMPL;+--------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | varchar(25) | YES | | NULL | || deptId | int(11) | YES | | NULL | || salary | float | YES | | NULL | |+--------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)# 修改之后的mysql> ALTER TABLE new_empl MODIFY name char(50);Query OK, 0 rows affected (0.28 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc new_empl;+--------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+--------+----------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || name | char(50) | YES | | NULL | || deptId | int(11) | YES | | NULL | || salary | float | YES | | NULL | |+--------+----------+------+-----+---------+-------+4 rows in set (0.05 sec)mysql>

• 修改字段名

  • 修改后的数据类型可以与原来一样但是不能为空
    ```sql
    mysql> ALTER TABLE new_empl change name new_name char(20);
    Query OK, 0 rows affected (0.17 sec)
    Records: 0 Duplicates: 0 Warnings: 0

mysql> desc new_empl;
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| new_name | char(20) | YES | | NULL | |
| deptId | int(11) | YES | | NULL | |
| salary | float | YES | | NULL | |
+----------+----------+------+-----+---------+-------+
4 rows in set (0.01 sec)
```

• 添加字段

mysql> ALTER TABLE new_empl add country varchar(50);Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc new_empl;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || new_name | char(20) | YES | | NULL | || deptId | int(11) | YES | | NULL | || salary | float | YES | | NULL | || country | varchar(50) | YES | | NULL | |+----------+-------------+------+-----+---------+-------+5 rows in set (0.01 sec)

• 删除字段

mysql> ALTER TABLE new_empl DROP deptID;Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc new_empl;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || new_name | char(20) | YES | | NULL | || salary | float | YES | | NULL | || country | varchar(50) | YES | | NULL | |+----------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)

• 修改字段的排列位置

  • 修改字段为表的第一个字段

  # 将字段修改为表的第一个字段mysql> ALTER TABLE new_empl MODIFY country varchar(50) FIRST;Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc new_empl;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| country | varchar(50) | YES | | NULL | || id | int(11) | NO | PRI | NULL | || new_name | char(20) | YES | | NULL | || salary | float | YES | | NULL | |+----------+-------------+------+-----+---------+-------+4 rows in set (0.02 sec)

  • 修改字段到表的指定列之后

  mysql> ALTER TABLE new_empl MODIFY country varchar(50) AFTER salary;Query OK, 0 rows affected (0.28 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> DESC NEW_EMPL;+----------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+----------+-------------+------+-----+---------+-------+| id | int(11) | NO | PRI | NULL | || new_name | char(20) | YES | | NULL | || salary | float | YES | | NULL | || country | varchar(50) | YES | | NULL | |+----------+-------------+------+-----+---------+-------+4 rows in set (0.01 sec)

• 更改表的存储引擎,如果有外键必须删除外键

mysql> ALTER TABLE new_empl ENGINE=MyISAM;ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails# 删除外键后mysql> ALTER TABLE new_empl ENGINE=MyISAM;Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table new_empl\G;*************************** 1. row *************************** Table: new_emplCreate Table: CREATE TABLE `new_empl` ( `id` int(11) NOT NULL COMMENT ‘员工编号‘, `new_name` char(20) DEFAULT NULL, `salary` float DEFAULT NULL COMMENT ‘工资‘, `country` varchar(50) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci1 row in set (0.00 sec)ERROR:No query specified

• 删除表的外键约束

mysql> ALTER TABLE tb_emp5 DROP FOREIGN KEY fk_tb_empl;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0

删除数据表

• 删除没有被关联的表

mysql> DROP TABLE IF EXISTS new_empl;Query OK, 0 rows affected, 1 warning (0.01 sec)mysql> show tables;+-------------------+| Tables_in_test_db |+-------------------+| tb_dept2 || tb_dept3 || tb_dept7 || tb_dept8 || tb_emp5 || tb_temp6 |+-------------------+6 rows in set (0.00 sec)

• 删除被其他表关联的主表

  • 外键关联的情况下，想删除主表，必须先删除和他关联的子表
  • 如果想保留字表，则必须解除外键关联

最近公司要求做电商数据分析报告，未来有事情要做了