SQL语句修改数据库表结构_sql语句修改表字段结构-CSDN博客

本文链接：https://blog.csdn.net/bai_juaner/article/details/8669970

一、查看表结构

1、查看表的基本结构语句：

DESCRIBE `DEPT`;

2、查看表详细结构语句：

SHOW CREATE TABLE `DEPT`;

二、对表的操作

1、增加表

CREATE TABLE `dept` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`description` varchar(255) DEFAULT '',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

2、删除表

DROP TABLE `DEPT`;

注意：

删除被其他表关联的父表则先要删除外键约束，然后再删除父表。

3、重命名表

ALTER TABLE `DEPT` RENAME `DEPT1`;

三、对数据的操作

1、插入数据

INSERT INTO `DEPT` VALUES(NULL,'ZHANGSAN',NULL);

遇到问题：

问题1

1366 - Incorrect string value: '\xBF\xE9' for column 'name' at row 1

解决方法：

修改编码格式，因为插入的中文字符编码不是UTF-8。打开文本框，选择另存为，编码格式选择UTF-8，重新执行插入语句。

问题2

1062 - Duplicate entry '1' for key 'PRIMARY'

解决方法：

切记插入数据时主键字段的值不可以重复。

问题3

在插入数据前判断该数据是否已经存在，如果已经存在则不重复插入。

解决方法:

INSERT IGNORE INTO `DEPT` VALUES(1,'ZHANGSAN',NULL);

2、将一个表中的数据插入另一个表中

INSERT INTO system_log

(time,level,description,user_name,user_id,user_ip,service_id,service_type)

SELECT time,level,description,user_name,user_id,user_ip,service_id,service_type

FROM operation_log;

INSERT INTO capacity

(contract_no,end_time,service_status,responsible_id,create_time,dept_id,original_id)

SELECT contract_no,end_time_after,service_status,responsible_id,create_time,dept_id,pre_service_id

FROM (select xxx from xxxx（一条select语句）) as table1;

注意：

如果两张表中的字段完全相同，而且目标表中不存在主键唯一性的话，可以直接用如下语句：

INSERT INTO idsm_system_log

SELECT *

FROM idsm_service_operation_log;

3、修改数据

UPDATE `DEPT` SET NAME='LISI' WHERE NAME='ZHANGSAN';

UPDATE system_log,(select a.id as newId,b.id from capacity a, extension b

where a.xxx = b.xxx and a.xxx = 3) as t1

set service_id=t1.newId,service_type=13

where service_id=t1.id and service_type=2;

遇到问题：

问题1

1054 - Unknown column '’xxx’' in 'field list'

解决方法：

切记name 后边的字符串要用单引号“ ' ”,而不是“ ’ ”

4、删除数据

DELETE FROM DEPT;

四、对字段的操作

1、增加字段

ALTER TABLE `DEPT`

ADD COLUMN `loc_id` int(10) unsigned DEFAULT '0' AFTER `description`;

2、修改字段

ALTER TABLE `DEPT`

CHANGE `id` `id` int(11) NOT NULL AUTO_INCREMENT;

3、删除字段

ALTER TABLE `DEPT`

derp column `loc_id`;

4、调整字段顺序

ALTER TABLE `DEPT`

CHANGE `id` `id` int(10) unsigned NOT NULL AUTO_INCREMENT AFTER `name`;

五、对约束关系的操作

1、删除外键

ALTER TABLE `DEPT`

DROP FOREIGN KEY `loc_id`;

2、增加外键

ALTER TABLE `DEPT`

add CONSTRAINT `loc_id` FOREIGN KEY(`loc_id`) REFERENCES `t2` (`id`) ON DELETE CASCADE ON UPDATECASCADE

注意：

增加外键前必须要保证子表中的外键值在父表中都存在，又不存在的会报错，修改后即可。

遇到问题：

问题1

1452 - Cannot add or update a child row: a foreign key constraint fails (`dept`.`#sql-818_e`, CONSTRAINT `group_id` FOREIGN KEY (`center_id`) REFERENCES `group` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

解决方法：

在数据库中存在一些数据需要关联的外键值在父表中没有，这时候需要把这些数据关联的外键值进行修改，或者在父表中加入所需的数据。

3、删除主键

ALTER TABLE `DEPT`

DROP PRIMARY KEY;

注意：

删除主键前需要先取消主键的自动增长，否则删除失败，报错：

1075 - Incorrect tabledefinition; there can be only one auto column and it must be defined as a key

解决方法：

先执行修改字段的语句取消自增特性，然后删除主键

4、增加主键

ALTER TABLE `DEPT`