mysql 改数据库表结构

SQL语句修改数据库表结构
版权
一、查看表结构

    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);

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`

ADD CONSTRAINT `id` PRIMARY KEY (`id`);

注意:

通过命令行操作时,每个表只允许有一个主键,报错:

1068 - Multiple primarykey defined

解决方法:

可以选择删除原来的主键,然后重新加入新的联合主键。 

5、增加唯一键约束

ALTER TABLE DEPT

ADD CONSTRAINT id_name UNIQUE (id,name);

 问题1

增加唯一键约束前先保证表中没有重复的两字段相同的数据。

解决方法:

找出数据库表中id与name均相同的数据,并删除。

You can't specify target table 'DEPT' for update in FROM clause

不能先select出同一表中的某些值,再update这个表(在同一语句中)。应该如下:

create table tmp as select id from xxx group by xxx,xxx having count(*)>1;

delete from xxx where id in (select id from tmp);

drop table tmp;

6、删除唯一键约束

ALTER TABLE idsm_snapshot_point DROP INDEX pointId_lunId;

7、删除normal索引

ALTER TABLE `DEPT`

DROP INDEX `name`;

遇到问题:

问题1

1025 - Error on rename of '.\xxx\#sql-648_3' to '.\xxx\xxx'(errno: 150)

解决方法:

该索引可能与外键的名称相同,这种索引是添加外键时产生的,所以需要先删除该外键,然后删除该索引。

8、增加nrmal索引

ALTER TABLE  `DEPT`

ADD INDEX `name` (`name`);

9、增加唯一索引

ALTER TABLE `DEPT` ADD UNIQUE INDEX`name` (`name`);

注意:

添加唯一索引时必须要保证数据库中该索引的数据没有重复,否则报错。

问题1

1062 - Duplicate entry 'haha1' for key 'name'

解决方法:

添加唯一索引前,数据库中的数据该索引字段存在重复。只需要对重复的名称进行修改,唯一索引即可添加成功。

 

六、修改表的属性

ALTER TABLE `DEPT` 

ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULTCHARSET=utf8;
 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页