SQL语句修改数据库表结构_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
1
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 2274 | 回复: 0   主题: SQL语句修改数据库表结构        下一篇 
oraclexie
注册用户
等级:新兵
经验:36
发帖:67
精华:0
注册:2011-8-21
状态:离线
发送短消息息给oraclexie 加好友    发送短消息息给oraclexie 发消息
发表于: IP:您无权察看 2014-11-21 10:21:46 | [全部帖] [楼主帖] 楼主

一、查看表结构

    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`
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、增加normal索引

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;


--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
总帖数
1
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论