mysql增删改查 常用脚本

-- 查看mysql安装路径select @@basedir as basePath from dual;-- 查看所有存储过程show procedure status;-- 把某个表某字段值 更改 其它某个表的某个字段值UPDATE shop s,shop_temp st     set s.ship_start=st.ship_strat WHERE s.id=st.shop_id;-- 删除表中重复数据delete from history where id not in (select maxid from (select max(id) as maxid from history group by entity_type,entity_id,user_id) b);-- 删除数据条件是其他表delete search_rank from search_rank,task where search_rank.task_id = task.id and task.`status`='D';-- 删除字段USE `lanecard`;ALTER TABLE `lanecard`.`trade_order` ADD COLUMN `community_id` INT(5) NULL COMMENT '订单所属社区ID' AFTER `address`,ADD COLUMN `community_name` VARCHAR(30) NULL COMMENT '订单社区名称' AFTER `community_id`;-- 修改字段备注ALTER table trade_order MODIFY `status` char(1) DEFAULT NULL COMMENT '订单状态（P待支付 N待发货 D已发货  R已收货  C已完成  X已取消）';-- 修改字段类型alter table system_log modify column entity_id varchar(20);-- 修改字段默认值alter table表名alter column字段名drop default; (若本身存在默认值，则先删除)alter table表名 alter column 字段名 set default默认值;(若本身不存在则可以直接设定)-- 修改字段允许为空alter table表名 modify字段名字段类型(长度) null;-- 删除字段alter table `user_movement_log` drop column Gatewayid;-- 索引操作添加：ALTER TABLE table_name ADD INDEX index_name (column_list)ALTER TABLE table_name ADD UNIQUE (column_list)ALTER TABLE table_name ADD PRIMARY KEY (column_list)CREATE INDEX index_name ON table_name (column_list)CREATE UNIQUE INDEX index_name ON table_name (column_list)1.添加PRIMARY KEY（主键索引） mysql>ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 2.添加UNIQUE(唯一索引) mysql>ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 3.添加INDEX(普通索引) mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) 4.添加FULLTEXT(全文索引) mysql>ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 5.添加多列索引 mysql>ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )删除：DROP INDEX index_name ON talbe_nameALTER TABLE table_name DROP INDEX index_nameALTER TABLE table_name DROP PRIMARY KEY查看：show index from tblname;show keys from tblname;