管理脚本

最新推荐文章于 2024-08-26 18:56:03 发布

weixin_34219944

最新推荐文章于 2024-08-26 18:56:03 发布

阅读量98

点赞数

文章标签： python 运维数据库

原文链接：https://my.oschina.net/jamescasta/blog/700285

版权

2019独角兽企业重金招聘Python工程师标准>>>

插入测试数据脚本

INSERT INTO T_SYS_USER (
        SUSER_ID ,SUSER_SEC_ID ,SUSER_ACCOUNT ,SUSER_PASS ,
  SUSER_HEAD ,SUSER_SEC_NAME ,SUSER_CREATE_TIME ,SUSER_CREATETOR
 ,SUSER_TYPE,SUSER_NAME 
        )
 VALUES (
        91500 ,1000 ,'XXX' ,'123456' ,'' ,'技术部' ,sysdate ,'220','f',''
        );

更改

update t_sys_user a set a.suser_pass='7BDAD8C3BD624AD3F3FEA3C4E74A74A2' 
WHERE a.suser_account='xxxx';

select t.house_id, group_concat(t.house_pic) name From zwb_house_album t 
 group by house_id HAVING 条件

给表增加多个字段：

alter table {table_name}
add column account_name varchar(20) NOT NULL,
add column contact varchar(20) NOT NULL COMMENT '联系',  
add columncontact_type int(4) NOT NULL COMMENT '1  2  3 微信';

删除表的多个字段

ALTER TABLE {table_name}
 drop COLUMN account_name, 
drop COLUMN contact, 
drop COLUMN contact_type;

修改表名：

alter table {old_table_name}rename {new_table_name}; --修改表名  
alter table {table_name} change  column {old_column}{new_column} varchar(30)--修改表列名
alter table {table_name} modify {column_name} char(40) --修改表列类型
||alter table test change address address  char(40)

数据库锁表

CREATE TABLE `methodLock` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `method_name` varchar(64) NOT NULL DEFAULT '' COMMENT '锁定的方法名',
  `desc` varchar(1024) NOT NULL DEFAULT '备注信息',
  `update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '保存数据时间，自动生成',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uidx_method_name` (`method_name `) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='锁定中的方法';

存储过程：

delimiter //
create procedure liwei()
     begin
     declare done boolean default 0;
     declare li int;
     declare wei decimal(8,2);
     declare numb cursor
     for
     select order_num from orders;
     declare continue handler for sqlstate '02000' set done=1;
     --     declare continue handler for not found set done=1;
     create table if not exists ordertotals
     (order_num int,total decimal(8,2));
     open numb;
     repeat
     fetch ordernumbers into li;
     call ordertotals(tx,1,t);
     insert into ordertotals(order_num, total)
     values(li,wei);
     until done end repeat;
     close numd;
     end
     //

高效利用ssh 存入一些常用的快捷命令

https://jingyan.baidu.com/article/1876c8527297c4890b1376b3.html

转载于:https://my.oschina.net/jamescasta/blog/700285