mysql常用整理

Mysql建表语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for mysql_table
-- ----------------------------
DROP TABLE IF EXISTS `mysql_table`;
CREATE TABLE `mysql_table`  (
`id` bigint(20) NULL DEFAULT NULL COMMENT 'ID，主键',
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '姓名',
`address` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '地址',
`mobile` bigint(20) NULL DEFAULT NULL COMMENT '手机号',
`other_info` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '其他信息',
`tenant_id` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '租户ID',
`created_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '创建者',
`created_time` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '创建时间',
`updated_time` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '更新时间',
`updated_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NULL DEFAULT NULL COMMENT '更新者',
`version` bigint(20) NULL DEFAULT NULL COMMENT '版本号，乐观锁',
`is_deleted` bigint(20) NULL DEFAULT NULL COMMENT '是否删除'
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of mysql_table
-- ----------------------------
INSERT INTO `mysql_table` VALUES (1, 'a', 'CQ', 123, 'info1', NULL, NULL, '20210819', NULL, NULL, NULL, NULL);
INSERT INTO `mysql_table` VALUES (2, 'b', 'SZ', 123, 'info2', NULL, NULL, '20210819', NULL, NULL, NULL, NULL);

SET FOREIGN_KEY_CHECKS = 1;

跨库查询：

select count(table1.id) from table1 INNER JOIN table2  on table1.name=table2.name

嵌套查询

SELECT count(name) FROM table1 WHERE name IN (SELECT name  FROM table2 )