MySQL 使用笔记

MySQL 使用笔记

一、 问题列表

1. mysql workbench 远程连接提示：Access denied for user ‘root’

解决方法：在MySQL服务器上使用 root登录后，执行如下SQL语句：

// 登录 mysql
mysql -u root -p
// 开启远程登录
grant all privileges on *.* to 'root'@'%' identified by 'mypassword' with grant option; 
// 刷新特权，别忘了
FLUSH PRIVILEGES;

二、 SQL 用法

1. 函数

DATE_FORMAT(date,format) 函数

// %Y-%m-%d %H:%i:%s 与 yyyy-MM-dd HH:mm:ss 相对应，也是最常用的格式
SELECT DATE_FORMAT(NOW(),'%Y-%m-%d %H:%i:%s');  

// ifnull
a.WEIGHT_IN - ifnull(a.WEIGHT_OUT, 0) AS sjsl,

GROUP_CONCAT() 函数

SELECT
		a.EXE_STORAGE_ACCOUNT_ID,
		GROUP_CONCAT( b.NAME ) AS KEEPER 
	FROM
		EXE_STORAGE_STAFF a
		LEFT JOIN RES_PERSON b ON b.RES_PERSON_ID = a.RES_PERSON_ID 
	GROUP BY
		a.EXE_STORAGE_ACCOUNT_ID

2. 条件

CASE WHEN

case k.CODE
when '1' then '1'
else '2' end

3. 工具

// 查看一个表有多少个字段
SELECT count(*)
FROM information_schema.columns
WHERE table_name = 'EXE_FUMIGATION_RECORD'

4. 迭代

// 
WITH recursive cls_barn_base(CLS_BARN_ID, CODE, PARENT_ID) AS (
	SELECT t.CLS_BARN_ID, t.CODE, t.PARENT_ID 
	FROM cls_barn t 
	WHERE t.CODE = '5000000' AND t.is_enabled = 1 
	UNION ALL
	SELECT b.CLS_BARN_ID, b.CODE, b.PARENT_ID
	FROM cls_barn_base a
	LEFT JOIN cls_barn b ON a.cls_barn_id = b.parent_id 
	WHERE b.is_enabled = 1 
	) 
SELECT * 
FROM cls_barn_base;

执行结果 ：

--查找所有上级节点
--查询树状结构某节点(44)的上级所有根节点
with cte_parent(id, title, parent)
as
(
--起始条件
select id, title, parent 
from tb_menu
where id = 44   -- 列出子节点查询条件
union all
--递归条件
select a.id, a.title,a. parentfrom tb_menu a
inner joincte_parent b
--执行递归，这里就要理解下了
on a.id=b.parent
)
select * from cte_parent;

5. left join、right join、inner join的区别

6. 日期操作

// 日期操作函数
uploadtime：2022-11-04 10:49:30
year(uploadtime) 获取时间字段中的年，结果为2022
month(uploadtime) 获取时间字段中的月，结果为11
day(uploadtime) 获取时间字段中的日，结果为04

// 按月分类统计
select year(uploadtime) as yy, month(uploadtime) as mm,count(*),sum(filetypeid) from sys_base_fileindex GROUP BY yy,mm

// mysql 数据库查询今天、昨天、本月、上月、上季度的数据
今天： select * from 表名 where to_days(时间字段名) = to_days(now());
昨天： SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1
七天：SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名)
近30天：SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名)
本月：SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, "%Y%m" ) = DATE_FORMAT( CURDATE( ) , "%Y%m" )
上一月：SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , "%Y%m" ) , date_format( 时间字段名, "%Y%m" ) ) =1
查询本季度：select * from ht_invoice_information where QUARTER(create_date)=QUARTER(now());
查询上一季度：select * from ht_invoice_information where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));
查询本年：select * from ht_invoice_information where YEAR(create_date)=YEAR(NOW());
查询上一年：select * from ht_invoice_information where year(create_date)=year(date_sub(now(),interval 1 year));
查询当前这周:SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,"%Y-%m-%d")) = YEARWEEK(now());
查询上周: SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,"%Y-%m-%d")) = YEARWEEK(now())-1;
查询当前月份：select name,submittime from enterprise where date_format(submittime,"%Y-%m")=date_format(now(),"%Y-%m")
查询距当前6个月的数据：select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

参考链接：https://blog.csdn.net/m0_37635053/article/details/103899068

三、 IDE 使用技巧

1. DBeaver 导出达梦数据库的字段名和注释

// 导出达梦数据库的字段名和注释
SELECT
	a.COLUMN_NAME,
	b.COMMENTS, 
	a.DATA_TYPE,
	a.DATA_LENGTH,
	a.NULLABLE
FROM
	user_tab_columns a
	LEFT JOIN user_col_comments b ON a.Table_Name = b.Table_Name 
	AND a.COLUMN_NAME = b.COLUMN_NAME 
WHERE
	a.Table_Name = 'IVY_ACCOUNT_SUMMARY' 
ORDER BY
	a.COLUMN_ID

2. DBeaver 达梦查违反约束原因

达梦查违反约束原因：
SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_NAME = 'CONS134250333'