MYSQL笔记

MYSQL

修改表连接字段报错

show variables where Variable_name like 'collation%';  -- 查看数据库
 show create table table_name; -- 查看表属性

 alter table table_name default character set utf8mb4 collate=utf8mb4_0900_ai_ci;  -- 修改表属性
 ALTER TABLE table_name convert to CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci; -- 修改表字段属性

执行SQL

-- 不需要参数 需要在存储过程中执行
/*指定SQL内容*/
SET @sql_content="select 1999";
/*预处理SQL，如果SQL格式不对，则会报错。*/
PREPARE sql_sentence FROM @sql_content;
/*执行SQL*/
EXECUTE sql_sentence;
/*取消预处理。*/
DEALLOCATE PREPARE sql_sentence;

-- 带参数
/*传递参数，以传递@param_id、@param_id2为例。*/
SET @param_id='1', @param_id2='2';
SET @sql_content="select ? param_id,? param_id2";
PREPARE sql_sentence FROM @sql_content;
/*注意参数顺序。*/
EXECUTE sql_sentence USING @param_id,@param_id2;
DEALLOCATE PREPARE sql_sentence;

将表的结果导出文件

show variables like '%secure%' 
select * into outfile 'D:\app11my1test.xls' from t_students  ;
-- 查看路径
本地的my.ini配置文件修改此属性
-- 解决乱码
SELECT a.*
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/mytest3.csv' 
CHARACTER SET gbk
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
from eemployee a ;

MySQL连接MySQL(DBLINK)

查看本地mysql的有没有federated 引擎：
mysql> show engines;

在mysql目录下的my.ini文件里添加一行

federated
重启mysql服务，然后查看federated 

CREATE TABLE dblink_view (
bank_name VARCHAR(50),
sys_bank_code VARCHAR(12),
PROV_INPUT_NAME VARCHAR(20),
NAME VARCHAR(10)
)ENGINE=FEDERATED CONNECTION='mysql://bankinfo:bankinfo@192.168.3.42:3307/t2_cpv2/view_bt_input_bank_info';

engine=federated connection = 'mysql://用户:密码@IP地址:端口/库名称/表名称';

完成后就可以本地数据库查到远程库数据：SELECT * FROM dblink_view；

查询存储过程中含有某字符sql

select name
from sysobjects o, syscomments s
where o.id = s.id
and text like '%222010%'
and o.xtype = 'P'

打印错误信息

GET DIAGNOSTICS CONDITION 1 @v_sqlstate=RETURNED_SQLSTATE,@v_message= MESSAGE_TEXT;
SELECT @v_sqlstate,@v_message;

程序过程中发生死锁

 SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;-- 查询死锁
  kill 791985; -- 杀掉

MYSQL游标的使用

CREATE DEFINER=`root`@`localhost` PROCEDURE `p111`()
begin
    declare TID int;
    declare TName varchar(15);
		declare flag int default 0;  -- 定义变量
    -- 声明游标
    declare mc cursor for  select teacherid,teachername from   teachers; 
		declare CONTINUE HANDLER for not found set flag=1; --      这句话的意思是当游标执行到最后一条（游标会多执行一次），系统设置这个变量为1
    -- 打开游标
    open mc;
    -- 获取结果
	  Re:loop  -- 循环，将表的内容都转移到class2中
    fetch mc into TID,TName;
     -- 当无法fetch会触发handler continue
    if flag=1 then 
		leave Re;
		end if;
	 
    select TID,TName;
    -- 关闭游标
    end loop;
		
      
    -- 关闭游标
    close mc;
    
end

动态参数写法

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`(P_find VARCHAR(2000))
begin 
DECLARE  v_sqlcounts  varchar(500);
DECLARE  recordcount  varchar(500);

set v_sqlcounts = concat('select *  from  teachers where  name like  \'%?\'%');  
set @sqlcounts := v_sqlcounts;  
#预处理动态SQL  
set @n=P_find;
prepare stmt from @sqlcounts;  
#传递动态SQL内参数   
execute stmt using @n;  
deallocate prepare stmt;  
#获取动态SQL语句返回值  
SELECT @b;
end

DBlink MYSQL连MYSQL

参考自 https://www.cnblogs.com/luoyanga/p/13885462.html

在mysql目录下的my.ini文件里添加一行

federated
重启mysql服务，然后查看federated 

mysql> show engines;
在本地创建和远程数据库的表的表结构一致的表，以及远程数据库的连接：例如

CREATE TABLE dblink_view (
bank_name VARCHAR(50),
sys_bank_code VARCHAR(12),
PROV_INPUT_NAME VARCHAR(20),
NAME VARCHAR(10)
)ENGINE=FEDERATED CONNECTION='mysql://bankinfo:bankinfo@192.168.3.42:3307/t2_cpv2/view_bt_input_bank_info';

 engine=federated connection = 'mysql://用户:密码@IP地址:端口/库名称/表名称';

完成后就可以本地数据库查到远程库数据：SELECT * FROM dblink_view；

将多个结果合并到一行

select id,group_concat(name) from aa group by id;

以id分组，把name字段的值打印在一行，分号分隔 
mysql> select id,group_concat(name separator ';') from aa group by id;

以id分组，把去冗余的name字段的值打印在一行， 
逗号分隔

以id分组，把name字段的值打印在一行，逗号分隔，以name排倒序 
mysql> select id,group_concat(name order by name desc) from aa group by id;
mysql> select id,group_concat(distinct name) from aa group by id;

将获取的结果附加行号

SELECT @rownum:=@rownum+1 AS rownum, tablename.*
FROM (SELECT @rownum:=0) r, tablename;

调整字段顺序

ALTER TABLE 表名 MODIFY 字段名1 数据类型 FIRST ｜ AFTER 字段名2;

分隔符报错

MySQL是以; 分号结尾儿提交查询的，当函数或者存储过程中，会误将直接提交，使用前者将真正的分隔符设置为//或者其他也可
,再在结尾处将其还原为; 
使用该语法
delimiter //
 //
delimiter ;

日历横标写法

with A AS (
select CONCAT(YEAR(TERM),'年-',MONTH(TERM)) As YEARs,day(term) AS days,xtype from ACALENDAR where    day(term)=1
 ),
B AS (
select CONCAT(YEAR(TERM),'年-',MONTH(TERM)) As YEARs ,day(term)  AS days,xtype from ACALENDAR where    day(term)=2
),
C AS (
select CONCAT(YEAR(TERM),'年-',MONTH(TERM)) As YEARs ,day(term)=29  AS days,xtype from ACALENDAR where  day(term)=29 
union 
select CONCAT(YEAR(TERM),'年-',MONTH(TERM)) As YEARs ,29  AS days,null As xtype from ACALENDAR where  day(term)=1 and 
 MONTH(term) not in (
select MONTH(TERM)  AS days from ACALENDAR where  day(term)=29 )
)

select DISTINCT A.YEARs,A.xtype,B.xtype,C.xtype
from A,B,C 
where A.YEARs=b.YEARs and A.YEARs=C.YEARs;

查询创建结构

show create procedure aSP_Month_Submit1 ;
show create table mytest ;
show create view skywfemployee;

MYSQL纵转横

--查询实现

SELECT tb_test.id,

MAX(IF(tb_test.attr = 'age', tb_test.attr_value, NULL)) AS age,

MAX(IF(tb_test.attr = 'sex', tb_test.attr_value, NULL)) AS sex,

MAX(IF(tb_test.attr = 'name', tb_test.attr_value, NULL)) AS `name`,

MAX(IF(tb_test.attr = 'hobby', tb_test.attr_value, NULL)) AS hobby

FROM tb_test;