mysql中使用技巧总结

一、数据库安装

1. 免安装数据库

2.修改root或其他用户密码
以windows为例： 
1. 关闭正在运行的MySQL服务。 
2. 打开DOS窗口，转到mysql\bin目录。 
3. 输入mysqld --skip-grant-tables 回车。--skip-grant-tables 的意思是启动MySQL服务的时候跳过权限表认证。 
4. 再开一个DOS窗口（因为刚才那个DOS窗口已经不能动了），转到mysql\bin目录。 
5. 输入mysql回车，如果成功，将出现MySQL提示符:  mysql >。 
6. 连接权限数据库： use mysql; 。 
6. 改密码：update user set password=password("123") where user="root";（别忘了最后加分号） 。 
7. 刷新权限（必须步骤）：flush privileges;　。 
8. 退出 quit。 
9. 注销系统，再进入，使用用户名root和刚才设置的新密码123登录（或者 在任务管理器中找到mysql进程关闭后重新启动该服务即可）。

修改数据库用户密码借鉴：mysql如何修改root用户的密码

3. 查看mysql版本（百度有其他方法）

select version() from dual;

---

二、简易技巧

1. 获取数据库表名、表字段信息

use information_schema; -- 语句分开执行
select 
	a.COLUMN_NAME,    -- 字段名
	a.TABLE_NAME,     -- 表名       
	a.COLUMN_COMMENT  -- 字段备注
from INFORMATION_SCHEMA.Columns a
where 
table_schema='数据库名'

2.获取数据库中所有表名及数据量

use information_schema; -- 语句分开执行
select table_name,table_rows from tables 
where TABLE_SCHEMA = '数据库的名称' order by table_rows desc;

3.数据库操作导致表锁死处理方法

使用权限用户查询数据库进程：

SELECT * FROM information_schema.innodb_trx ;

# 找到阻塞的进程后，杀死进程： 

kill 5040

4.修改已有大量数据（百万级）表字段

创建新表，进行数据迁移，修改表名称，保留原始数据

5.获取当前数据库连接数及对应的ip地址

select SUBSTRING_INDEX(host,':',1) as ip , count(*) from information_schema.processlist group by ip;

6. many connection errors 解决办法

错误：

　　Host is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'

原因：

　　同一个ip在短时间内产生太多（超过mysql数据库max_connection_errors的最大值）中断的数据库连接而导致的阻塞；

7. 循环新增数据

delimiter //                            #定义标识符为双斜杠
drop procedure if exists test;          #如果存在test存储过程则删除
create procedure test()                 #创建无参存储过程,名称为test
begin
    declare i int;                      #申明变量
    set i = 0;                          #变量赋值
    while i < 10 do                     #结束循环的条件: 当i大于10时跳出while循环
        INSERT INTO `test` (`ID`) VALUES ('0');    #往test表添加数据
        set i = i + 1;                  #循环一次,i加一
    end while;                          #结束while循环
end
//                                 #结束定义语句
call test();                            #调用存储过程

8. 拼接sql

set @pcsConfigId = '1523956314869334094';
-- 查询接口 的所有设备和接口字段
SELECT
	dev.device_name,
	dev.device_code,
	dev.id as device_id,
	par.param_code,
	par.id as param_id,
	par.param_name,
	par.param_suffix,
	par.param_prefix,
	CONCAT("INSERT INTO pcs_device_param(`fk_pcs_id`, `fk_pcs_param_id`, `fk_pcs_device_id`, `measure_code`, `del_flag`) VALUES (",@pcsConfigId,", ",par.id,", ",dev.id,", '",CONCAT(par.param_prefix,SUBSTR(dev.device_code,4),par.param_suffix),"', 0);") as insertSql
FROM
	pcs_device_info dev
	LEFT JOIN pcs_param_info par ON 1 = 1 
WHERE
	dev.fk_pcs_id = @pcsConfigId 
	AND par.fk_pcs_id = @pcsConfigId
	
	-- 去除已经保存过的点位信息
	AND CONCAT(dev.id,'____',par.id) NOT IN (select CONCAT(fk_pcs_device_id,'____',fk_pcs_param_id) as unKey FROM pcs_device_param WHERE fk_pcs_id = @pcsConfigId )
	;

-- CONCAT("select '", deviceLists,"' as aaaaa from t_performance limit 1");
-- INSERT INTO pcs_device_param(`fk_pcs_id`, `fk_pcs_param_id`, `fk_pcs_device_id`, `measure_code`, `del_flag`) VALUES (1523956314869334094, 6, 1552839199189631009, 'AIRTU010009', 0);



-- SELECT device_code from pcs_device_info where fk_pcs_id = @pcsConfigId;
-- SELECT param_code from pcs_param_info where fk_pcs_id = @pcsConfigId;
-- 需要新增的sql数量   本类型设备数量 * 接口参数数量
select 92*21 - count(1)  from pcs_device_param WHERE fk_pcs_id = @pcsConfigId ;

9. 存储过程 游标循环

-- 建立存储过程之前需要判断该存储过程是否存在，若存在则删除
DROP PROCEDURE IF EXISTS savePcsPoint;
-- 创建存储过程
CREATE PROCEDURE savePcsPoint(in pcsConfigId varchar(30), out changeCount int )
-- 标识存储过程开始
BEGIN
	-- 定义变量
	DECLARE d int DEFAULT 0;
	DECLARE p int DEFAULT 0;
	-- 循环的设备编号
	DECLARE deviceCode varchar(20);
	DECLARE paramCode varchar(20);
	-- 定义设备列表、接口字段 游标，并将sql结果集赋值到游标中
	DECLARE deviceLists CURSOR FOR SELECT device_code from pcs_device_info where fk_pcs_id = pcsConfigId;
	DECLARE paramLists CURSOR FOR SELECT param_code from pcs_param_info where fk_pcs_id = pcsConfigId;
	-- 声明当游标遍历完后将标志变量置为某个值
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET d = 1;
	-- DECLARE CONTINUE HANDLER FOR NOT FOUND SET p = 1;
	
	SET @excuteSql = '';
	
	-- 打开游标
	OPEN deviceLists;
	-- 将游标中的值赋值给变量，注意：变量名不要与sql返回的列名相同，变量顺序要和sql结果列的顺序一致
	FETCH deviceLists INTO deviceCode;
	-- 当s不等于1时，也就是未遍历完时，会一直循环
	WHILE d <> 1 DO
	-- 执行业务逻辑
	SET @excuteSql = CONCAT(@excuteSql , ',	' , deviceCode);
	-- UPDATE member_album_count t SET t.img_pass_count = count WHERE t.member_id = memberId;
	-- 当s等于1时代表遍历已完成，退出循环
	FETCH deviceLists INTO deviceCode;
	END WHILE;
	-- 关闭游标
	CLOSE deviceLists;
-- 标识存储过程结束


		set @aaa = CONCAT("select '", @excuteSql,"' as aaaaa from pcs_device_info limit 1");
		PREPARE stmt FROM @aaa;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

		-- 处理返回值
		set changeCount = 1;
END;

-- 查询存储过程
-- SHOW PROCEDURE STATUS;
 
-- 调用
set @pcsConfigId = '1523956314869334094';
CALL savePcsPoint(@pcsConfigId, @changeCount);
select @changeCount;