mysql常用命令和脚本
修改表的auto_increment
select auto_increment from information_schema.tables where table_schema='ybl_des' and table_name='goods_sync';
alter table ybl_des.goods_sync auto_increment=1
- 1
- 2
清空数据表
truncate ybl_des.goods_sync;
- 1
内连接和外连接的区别：
1.内连接：显示两个表中有联系的所有数据
2.左连接：以左表为参照，显示所有的数据
3.右连接：以右表为参照，显示所有的数据
使用sql计算两点之间的距离：
ROUND(
6378.138 * 2 * asin(
sqrt(
pow(sin((shop.shop_latitude * pi() / 180 - #{latitude} * pi() / 180) / 2),2)
+ cos(shop.shop_latitude * pi() / 180)
* cos(#{latitude} * pi() / 180)
* pow(sin((shop.shop_longitude * pi() / 180 - #{longitude} * pi() / 180) / 2),2)
)
)
,2
)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
锁表语句：
LOCK TABLES `stor_order_entry` WRITE;
insert into ...
UNLOCK TABLES;
- 1
- 2
- 3
确认是否开启了日志： mysql> show variables like ‘log_bin’
存储过程：
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPar VARCHAR(1000);
SET sTemp = '';
SET sTempPar =rootId;
#循环递归
WHILE sTempPar is not null DO
#判断是否是第一个，不加的话第一个会为空
IF sTemp != '' THEN
SET sTemp = concat(sTemp,',',sTempPar);
ELSE //注意这里有一个else
SET sTemp = sTempPar;
END IF;
SET sTemp = concat(sTemp,',',sTempPar);
SELECT group_concat(pid) INTO sTempPar FROM treenodes where pid<>id and FIND_IN_SET(id,sTempPar)>0;
END WHILE;
RETURN sTemp;
END
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
可以参考的表：
CREATE TABLE `config_kv` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`c_type` VARCHAR(200) NULL DEFAULT NULL COMMENT '业务类型',
`c_key` VARCHAR(100) NULL DEFAULT NULL COMMENT '键:config_key',
`c_value` VARCHAR(200) NULL DEFAULT NULL COMMENT '值:config_value',
`remark` VARCHAR(500) NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
)
COMMENT='键值对配置表'
CREATE TABLE `config_mapping` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`config_type` VARCHAR(255) NULL DEFAULT NULL COMMENT '映射类型',
`from_system` VARCHAR(255) NULL DEFAULT NULL COMMENT '来源系统:third_system_info.code',
`from_value` VARCHAR(255) NULL DEFAULT NULL COMMENT '来源系统value值',
`to_system` VARCHAR(255) NULL DEFAULT NULL COMMENT '目标系统:third_system_info.code',
`to_value` VARCHAR(255) NULL DEFAULT NULL COMMENT '目标系统value值',
`is_delete` BIT(1) NULL DEFAULT b'0' COMMENT '逻辑删除：0：未删除,1：已删除',
`remark` VARCHAR(500) NULL DEFAULT NULL COMMENT '备注',
PRIMARY KEY (`id`) USING BTREE
)
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
判断某个字段中是否有重复的字段并显示该字段的名字：
select a,b from(
select count(*) as a, c_key as b from config_kv group by c_key ) as c
where a > 1;
- 1
- 2
- 3
数据库脚本的参考例子：
#call altername('库名')
delimiter //
DROP PROCEDURE IF EXISTS altername//
CREATE PROCEDURE altername(IN dbname VARCHAR(200))
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE oldname VARCHAR(200);
DECLARE cur CURSOR FOR SELECT table_name FROM information_schema.TABLES WHERE table_schema = dbname;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur;
REPEAT
FETCH cur INTO oldname;
-- 这一步可以防止表名不存在
SET @newname = UPPER(oldname);
SET @isNotSame = @newname <> BINARY oldname;
IF NOT done && @isNotSame THEN
SET @SQL = CONCAT('alter table `',oldname,'` rename to `', 'bak_', LOWER(@newname),'`');
PREPARE tmpstmt FROM @SQL;
EXECUTE tmpstmt;
-- SET @SQL = CONCAT('alter table `',oldname,'` rename to `', SUBSTRING(LOWER(@newname),5),'`');
-- PREPARE tmpstmt FROM @SQL;
-- EXECUTE tmpstmt;
DEALLOCATE PREPARE tmpstmt;
END IF;
UNTIL done END REPEAT;
CLOSE cur;
END;
//
delimiter ; //
call altername("stock-server");原文地址https://blog.csdn.net/jiaweicheng77/article/details/81239225
