Linux-关于MySQL（二）

最新推荐文章于 2024-07-13 10:38:18 发布

乌冬吴亦几

最新推荐文章于 2024-07-13 10:38:18 发布

阅读量102

点赞数

分类专栏：乌冬linux 文章标签： mysql 数据库 linux

本文链接：https://blog.csdn.net/Joan_H/article/details/107639006

版权

本文详细介绍了在Linux系统中如何修改MySQL的表结构，包括添加、修改字段，以及变更表名。此外，还深入讨论了MySQL的键值类型，如普通索引、唯一索引、主键和外键的使用，包括创建、查看和删除索引的操作，以及主键和外键的约束规则和功能验证。

摘要由CSDN通过智能技术生成

一、修改表结构

1.1 字段约束条件（限制字段赋值）
查看字段约束 desc 库名.表名；
1.1.1 是否允许字段赋空（null）值默认允许

字段名类型 not null ;


mysql> create table db1.t12( name char(10) not  null , addr  char(30) not null );
Query OK, 0 rows affected (0.05 sec)

mysql> desc db1.t12;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | NO   |     | NULL    |       |
| addr  | char(30) | NO   |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> insert into db1.t12 values (null , null );
ERROR 1048 (23000): Column 'name' cannot be null
mysql> 
mysql> insert into db1.t12 values ("null" , null );
ERROR 1048 (23000): Column 'addr' cannot be null
mysql> 
mysql> insert into db1.t12 values ("null" , "beijing" );
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t12;
+------+---------+
| name | addr    |
+------+---------+
| null | beijing |
+------+---------+
1 row in set (0.00 sec)

mysql> insert into db1.t12 values ("" , "shanghai" );
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t12;
+------+----------+
| name | addr     |
+------+----------+
| null | beijing  |
|      | shanghai |
+------+----------+
2 rows in set (0.00 sec)

mysql>

1.1.2 设置字段默认值默认情况默认值是null
添加新行时，不给字段赋值使用默认值赋值

字段名类型 default 默认值

create table db1.t13(name char(10) not null , age int default 25 , class char(7)   default "nsd2004" , loves  set("a","b","c") not null default "c,a" );

mysql> desc db1.t13;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| name  | char(10)         | NO   |     | NULL    |       |
| age   | int(11)          | YES  |     | 25      |       |
| class | char(7)          | YES  |     | nsd2004 |       |
| loves | set('a','b','c') | NO   |     | a,c     |       |
+-------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into  db1.t13(name) values("bob");
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t13;
+------+------+---------+-------+
| name | age  | class   | loves |
+------+------+---------+-------+
| bob  |   25 | nsd2004 | a,c   |
+------+------+---------+-------+
1 row in set (0.00 sec)

mysql> 
mysql> insert into db1.t13 values ("tom",null,"nsd2012",null );
ERROR 1048 (23000): Column 'loves' cannot be null
mysql> 
mysql> insert into db1.t13 values ("tom",null,"nsd2012","b" );
Query OK, 1 row affected (0.00 sec)

mysql> select  * from db1.t13;
+------+------+---------+-------+
| name | age  | class   | loves |
+------+------+---------+-------+
| bob  |   25 | nsd2004 | a,c   |
| tom  | NULL | nsd2012 | b     |
+------+------+---------+-------+
2 rows in set (0.00 sec)

mysql>

1.1.3 键值（默认都没有设置）
1.1.4 额外设置（默认没有设置）

1.2 修改表结构
1.2.1命令格式
alter table 库.表操作动作；
操作如下：

1.2.2 添加新字段 add

mysql> desc t1;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name  | char(10) | YES  |     | NULL    |       |
| age   | int(11)  | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> alter table  db1.t1 add  class char(7) not null  default "nsd2004" , add email  varchar(30) ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name  | char(10)    | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
| class | char(7)     | NO   |     | nsd2004 |       |
| email | varchar(30) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter table  db1.t1  add stu_num  int  first;
mysql> desc db1.t1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_num | int(11)     | YES  |     | NULL    |       |
| name    | char(10)    | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| class   | char(7)     | NO   |     | nsd2004 |       |
| email   | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table   db1.t1 add  QQ  char(11) after name ;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc db1.t1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| stu_num | int(11)     | YES  |     | NULL    |       |
| name    | char(10)    | YES  |     | NULL    |       |
| QQ      | char(11)    | YES  |     | NULL    |       |
| age     | int(11)     | YES  |     | NULL    |       |
| class   | char(7)     | NO   |     | nsd2004 |       |
| email   | varchar(30) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql>

		1.2.3 删除表中字段 drop
mysql> alter table  db1.t1  drop  class , drop email;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select  * from db1.t1;
+---------+------+------+------+
| stu_num | name | QQ   | age  |
+---------+------+------+------+
|    NULL | abc  | NULL |   99 |
|    NULL | NULL | NULL | NULL |
|    NULL |      | NULL | NULL |
+---------+------+------+------+
3 rows in set (0.00 sec)

mysql> desc db1.t1;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| stu_num | int(11)  | YES  |     | NULL    |       |
| name    | char(10) | YES  |     | NULL    |       |
| QQ      | char(11) | YES  |     | NULL    |       |
| age     | int(11)  | YES  |     | NULL    |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql>

1.2.3 修改字段类型 modify
如果字段已经有数据了，修改的设置不能与已经存储的数据冲突，冲突不允许修改。

mysql> delete from  db1.stuinfo;
mysql> select  * from stuinfo;

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

mysql> alter  table db1.stuinfo modify age int not null default 19 ;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stuinfo;
+---------+----------+------+-----+---------+-------+
| Field   | Type     | Null | Key | Default | Extra |
+---------+----------+------+-----+---------+-------+
| stu_num | int(11)  | YES  |     | NULL    |       |
| name    | char(10) | YES  |     | NULL    |       |
| QQ      | char(11) | YES  |     | NULL    |       |
| age     | int(11)  | NO   |     | 19      |       |
+---------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> alter  table db1.stuinfo modify age tinyint ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc stuinfo;
+---------+

最低0.47元/天解锁文章

乌冬吴亦几

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Linux-关于MySQL（二）

一、修改表结构1.1 字段约束条件（限制字段赋值）查看字段约束 desc 库名.表名；1.1.1 是否允许字段赋空（null）值默认允许字段名类型 not null ;mysql> create table db1.t12( name char(10) not null , addr char(30) not null );Query OK, 0 rows affected (0.05 sec)mysql> desc db1.t12;+-------+------
复制链接

扫一扫