sql语句----表头管理

吉730

已于 2022-07-06 17:32:38 修改

阅读量1.8k

点赞数 3

文章标签： mysql 数据库 java

于 2022-07-06 16:43:52 首次发布

本文链接：https://blog.csdn.net/weixin_69899223/article/details/125642763

版权

修改表：
    命令格式： alter  table  库名.表名   操作命令；
    可以做哪些修改呢？（操作命令）
	添加新表头   add         
	删除表头   drop          
	修改表头存储数据的 数据类型  modify 
	修改（表头名 存储数据 null default）  change
	修改表名  rename 

mysql> alter table studb.stu rename studb.stuinfo;  修改表名 
mysql> alter table studb.stuinfo drop age ;  删除字段
mysql> use studb;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_studb |
+-----------------+
| stuinfo         |
+-----------------+
1 row in set (0.00 sec)

mysql> desc stuinfo;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| name   | char(10) | YES  |     | NULL    |       |
| class  | char(9)  | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table studb.stuinfo add  mail  char(30) ; 添加在末尾


添加在指定位置 

mysql> alter table  studb.stuinfo add  number  char(9) first , add  school char(10) after name;

mysql> desc studb.stuinfo; 查看表结构
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(9)  | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| school | char(10) | YES  |     | NULL    |       |
| class  | char(9)  | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
| mail   | char(30) | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+


mysql> alter table  studb.stuinfo   修改字段类型
    -> modify
    -> mail varchar(50) not null default "plj@tedu.cn";

mysql> desc studb.stuinfo;
+--------+-------------+------+-----+-------------+-------+
| Field  | Type        | Null | Key | Default     | Extra |
+--------+-------------+------+-----+-------------+-------+
| number | char(9)     | YES  |     | NULL        |       |
| name   | char(10)    | YES  |     | NULL        |       |
| school | char(10)    | YES  |     | NULL        |       |
| class  | char(9)     | YES  |     | NULL        |       |
| gender | char(4)     | YES  |     | NULL        |       |
| mail   | varchar(50) | NO   |     | plj@tedu.cn |       |
+--------+-------------+------+-----+-------------+-------+

mysql> alter table studb.stuinfo change  class class_name char(9) ; 修改表头名
mysql> desc studb.stuinfo;查看修改
+------------+-------------+------+-----+-------------+-------+
| Field      | Type        | Null | Key | Default     | Extra |
+------------+-------------+------+-----+-------------+-------+
| number     | char(9)     | YES  |     | NULL        |       |
| name       | char(10)    | YES  |     | NULL        |       |
| school     | char(10)    | YES  |     | NULL        |       |
| class_name | char(9)     | YES  |     | NULL        |       |
| gender     | char(4)     | YES  |     | NULL        |       |
| mail       | varchar(50) | NO   |     | plj@tedu.cn |       |
+------------+-------------+------+-----+-------------+-------+


mysql> alter table  studb.stuinfo  drop school , drop 班级 ,drop email ;    一起删除多个表头
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc  studb.stuinfo; 查看 
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(9)  | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
| age    | int(11)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> 

使用modify  修改表头的位置  （表头下边的数据也跟着换位置了）
mysql> alter  table studb.stuinfo  modify age int  after name ;   
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc studb.stuinfo;
+--------+----------+------+-----+---------+-------+
| Field  | Type     | Null | Key | Default | Extra |
+--------+----------+------+-----+---------+-------+
| number | char(9)  | YES  |     | NULL    |       |
| name   | char(10) | YES  |     | NULL    |       |
| age    | int(11)  | YES  |     | NULL    |       |
| gender | char(4)  | YES  |     | NULL    |       |
+--------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)