sql 一些常用语句累积

Bertha 。 2022-08-25 08:25 221阅读 0赞

1.查看表分区

select  
partition\_name part,  
partition\_expression expr,  
partition\_description descr,  
table\_rows  
from information\_schema.partitions where  
table\_schema = schema()  
and table\_name='表名';

删除分区

alter table xx\_log drop partition p202003;

增加分区

alter table xx\_log add partition(partition p202002 values LESS THAN (737850) ENGINE = InnoDB);

2.分区中partition\_description 的数字737668 是日期转天而来的。两者可以互转

\-- 737668  
SELECT TO\_DAYS(20190901);

\-- 20190901

select FROM\_DAYS(737668)

3.-- 查看事件是否开启  
SHOW VARIABLES LIKE 'event\_scheduler';

4.-- 查看事件 如下三个命令都可以  
1. SELECT \* FROM mysql.event;

2. SELECT \* FROM information\_schema.events;  
3. show events;

4.修改事件执行的时间

ALTER EVENT 事件名  
ON SCHEDULE EVERY 1 DAY starts '2020-04-04 02:00:00';

创建事件并制定执行时间

create event EVENT\_lqy\_test\_insert\_23 on schedule every 1 day starts 2020-04-04 02:00:00';

下面演示mysql event 由于是多语句执行，须要使用定界符io

delimiter |  
create event et\_test  
on schedule every 1 day starts '2017-11-24 00:00:00'  
do  
begin  
insert into test.aaa values (1);  
insert into test.bbb values (2);  
commit;  
end|  
delimiter ;  
event

执行成功后，验证是否成功建立form

select a.EVENT\_SCHEMA,a.EVENT\_NAME,a.EVENT\_DEFINITION,a.\`STATUS\` from information\_schema.\`EVENTS\` a where a.EVENT\_NAME = 'et\_test';  
test

更改event 状态

alter event et\_test disable | enable;

删除事件

drop event et\_test;

5.一对多。多的那一列用逗号拼接。

SELECT

id,  
GROUP\_CONCAT(xx\_id) AS ids,  
GROUP\_CONCAT(xx\_name) AS NAMES  
FROM  
aa  
GROUP BY  
id;

6.使用if函数判断IF(a.xx\_ID='0',b,c)取b还是c的值

7.今天删除数据库中数据，提示因为设置了foreign key,无法修改删除  
可以通过设置FOREIGN\_KEY\_CHECKS变量来避免这种情况。

SET FOREIGN\_KEY\_CHECKS=0;  
  
删除，更新数据，

恢复外键

SET FOREIGN\_KEY\_CHECKS=1;  
  
另：查看当前 FOREIGN\_KEY\_CHECKS的值

SELECT @@FOREIGN\_KEY\_CHECKS;

8.mysql 查询表字段信息（字段名、描述、类型、长度）  
select column\_name,column\_comment,data\_type,column\_type from information\_schema.columns where table\_name=表名;

9.获取当前表的最大id+1执行插入语句

insert into xx(id, config\_code, config\_name, config\_value) values  
((select id from (select max(id)+1 as id from xx) as tb),'xx','xx','xx');

10.执行如下语句就可以用navicat导出表里面的字段类型说明等导出成word格式

SELECT COLUMN\_NAME 列名, COLUMN\_COMMENT 名称 , COLUMN\_TYPE 数据类型, DATA\_TYPE 字段类型, CHARACTER\_MAXIMUM\_LENGTH 长度, IS\_NULLABLE 是否必填, COLUMN\_DEFAULT 描述 FROM INFORMATION\_SCHEMA.COLUMNS where -- dbname为数据库名称 table\_schema ='dbname' AND -- tablename，到时候换成你要导出的表的名称 table\_name = 'tablename'