mariadb：SQL日常使用总结

1.关联删除

DELETE T_Base_Resource_Action FROM T_Base_Resource_Action  INNER JOIN T_Base_Resource ON T_Base_Resource_Action.permission &＃61; T_Base_Resource.permission
WHERE T_Base_Resource.resID&＃61;’course’ AND T_Base_Resource_Action.roleID&＃61;’service’ and T_Base_Resource_Action.rootOrgID&＃61;’3’;

如果用别名的话就会报错&＃xff1a;1109 Unknown table &＃39;T_Base_Resource_Action&＃39; in MULTI DELETE

2.查询分区

USE information_schema;

SELECT PARTITION_NAME,TABLE_ROWS
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE TABLE_NAME &＃61; &＃39;t_gps&＃39;;

SELECT table_name,table_rows FROM TABLES
WHERE TABLE_SCHEMA &＃61; &＃39;t_gps&＃39;
ORDER BY table_rows DESC;

3.取最新的记录

select *,max(gpsDate) maxDate from t_gps
where gpsDate>&＃61;&＃39;2018-08-29 00:00:00&＃39; and gpsDate<&＃61;&＃39;2018-08-29 23:59:59&＃39;
group by carCard

备注&＃xff1a;记得标准的SQL&＃xff0c;非group字段不能放到select中