Mysql中的常用语句模板

下面是MySQL中常用的语法模板示例：

1. 数据库相关：

   # 创建数据库
   CREATE DATABASE database_name;
   
   # 删除数据库
   DROP DATABASE database_name;
   
   # 修改数据库名称
   RENAME DATABASE old_database_name TO new_database_name;
   
   
   # 使用示例:
   DROP DATABASE IF EXISTS `database_name`;
   # 创建数据库时，全局统一设置字符集和校对规则，它将应用于当前数据库的所有表、字段等，成为默认设置
   CREATE DATABASE `database_name` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 
   USE `database_name`;
   

2. 数据表相关：

   # 创建数据表
   CREATE TABLE `table_name` (
       column1 datatype,
       column2 datatype,
       ...
   );
   
   # 删除数据表
   DROP TABLE `table_name`;
   
   # 修改数据表名称
   RENAME TABLE `old_table_name` TO `new_table_name`;
   
   
   # 使用示例:
   DROP TABLE IF EXISTS `table_name`;
   CREATE TABLE `table_name` 
   (
       `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
       `order_no` bigint NOT NULL 
       `del_flag` char(1) DEFAULT '0' COMMENT '删除标识 -1: 已删除 0: 正常',
       `create_time` datetime DEFAULT NULL COMMENT '创建时间',
       `create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
       `update_time` datetime DEFAULT NULL COMMENT '更新时间',
       `update_by` varchar(64) DEFAULT NULL COMMENT '更新人',
        PRIMARY KEY (`id`),
        UNIQUE KEY `unique_flag` (`order_no`)
   ) 
   ENGINE=InnoDB COMMENT='表描述';
   

3. 数据行相关：

   # 添加数据行
   INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);
   
   # 删除数据行
   DELETE FROM table_name WHERE condition;
   
   # 修改数据行
   UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
   
   # 查询数据行
   SELECT column1, column2, ... FROM table_name WHERE condition;
   

4. 数据列相关：

   # 增加数据列
   ALTER TABLE table_name ADD COLUMN column_name data_type [NULL | NOT NULL] [DEFAULT default_value] COMMENT 'column_comment';
   
   # 删除数据列
   ALTER TABLE table_name DROP COLUMN column_name;
   
   # 修改数据列，字段名一旦设计好了就不要修改，我们只能修改字段的某些属性
   ALTER TABLE table_name MODIFY COLUMN column_name new_data_type [NULL | NOT NULL] [DEFAULT default_value];
   

5. 数据行去重：

   # 可以同时对多个字段去重
   SELECT DISTINCT column_name1, column_name2, ...
   FROM table_name
   WHERE condition;
   

6. 对查询结果排序：

   SELECT column1, column2, ...
   FROM table_name
   WHERE condition;
   ORDER BY column1 ASC/DESC;
   

7. 子查询：

   SELECT column1, column2, ...
   FROM table_name1
   WHERE columnN IN (
       SELECT column_name
       FROM table_name2
       WHERE condition
   );
   
   # 示例
   SELECT name, age
   FROM t_student ts
   WHERE ts.student_id IN (
       SELECT student_id
       FROM t_score
       WHERE subject = 'Math'
       AND score >= 90
   );
   

8. 连接查询：

   SELECT column1, column2, ...
   FROM table_name1
   JOIN table_name2 ON table_name1.column = table_name2.column
   WHERE conditions;
   
   SELECT tor.order_id, tc.customer_name, tor.order_date
   FROM t_order tor
   JOIN t_customer tc ON tor.customer_id = tc.customer_id
   WHERE tor.order_date >= '2023-01-01';
   

9. 分页查询

   # LIMIT 关键字的位置是固定的，应该放在 OFFSET 关键字之前
   # 形式一，OFFSET、LIMIT 分开写
   SELECT column1, column2, ...
   FROM table_name1
   LIMIT number_of_rows OFFSET offset_value;
   
   # 形式二、直接在 LIMIT 中写
   # 这种写法第一个数值一定是偏移量，第二个数值表示限定数量
   SELECT column1, column2, ...
   FROM table_name
   WHERE condition1
     AND condition2
     ...
   ORDER BY column1, column2, ...
   LIMIT offset_value, number_of_rows;
   
   # offset_value 指定偏移量，表示从第几行开始返回结果，number_of_rows 表示返回的行数。
   

以上是MySQL中常用的语法模板示例，具体语句根据需要进行修改和补充。请注意，在实际使用时，需要根据具体的表名、列名和条件进行相应的替换。

子查询和连接查询的优劣

子查询和连接查询都是在数据库中进行表之间关联的常见查询方式，它们各有优劣，适用于不同的场景。以下是它们的一些优劣势比较：

子查询的优势：

1. 灵活性高：子查询可以嵌套在其他查询语句中，具有更大的灵活性，可以根据需要进行多层嵌套。
2. 可读性好：子查询通常比连接查询更容易理解和阅读，尤其对于复杂的查询逻辑。
3. 适用于小数据集：当处理的数据集较小时，使用子查询可能会更加高效，因为它只需要执行一次查询。

连接查询的优势：

1. 性能较好：连接查询通常能够利用数据库引擎的优化技术，对查询进行优化，从而提供更好的性能。
2. 可扩展性好：当处理大型数据集时，连接查询通常比子查询更具可扩展性和效率。
3. 操作灵活：连接查询可以使用不同类型的连接，如内连接、外连接和交叉连接，以满足不同的关联需求。

然而，子查询和连接查询也存在一些劣势：

子查询的劣势：

1. 可能较低的性能：当子查询的结果集较大时，可能会导致性能下降，尤其是嵌套多层子查询时。
2. 可读性较差：嵌套多个子查询可能会导致查询语句的可读性下降，难以理解和维护。

连接查询的劣势：

1. 复杂性高：连接查询涉及多个表之间的关联，可能需要在连接条件、过滤条件等方面进行更复杂的处理。
2. 可能产生冗余行：在进行连接查询时，如果没有正确的连接条件，可能会导致结果中出现冗余行。

综上所述，子查询和连接查询都有各自的优势和劣势。在实际使用中，应根据具体的需求、数据规模和性能要求来选择合适的查询方法。通常情况下，连接查询更适合处理大型数据集和复杂关联查询，而子查询更适用于简单的查询逻辑和小规模数据集。

实际RBAC权限模型使用示例：

DROP DATABASE IF EXISTS `vue3_blog`;

CREATE DATABASE `vue3_blog` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; 

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

USE `vue3_blog`;

-- ----------------------------
-- Table structure for sys_user
-- ----------------------------
DROP TABLE IF EXISTS `sys_user`;
CREATE TABLE `sys_user` 
(
	`user_id` bigint NOT NULL COMMENT '用户ID',
	`username` varchar(64) NOT NULL COMMENT '用户名',
	`password` varchar(255) NOT NULL COMMENT '密码',
	`salt` varchar(255) DEFAULT NULL COMMENT '随机盐',
	`phone` varchar(20) DEFAULT NULL COMMENT '手机号',
	`avatar` varchar(255) DEFAULT NULL COMMENT '头像',
	`dept_id` bigint DEFAULT NULL COMMENT '部门ID',
	`lock_flag` char(1) DEFAULT '0' COMMENT '0-正常，9-锁定',
	`del_flag` char(1) DEFAULT '0' COMMENT '0-正常，1-删除',
	`create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
	`update_time` datetime DEFAULT NULL COMMENT '修改时间',
	`update_by` varchar(64) DEFAULT NULL COMMENT '修改人',
 	 PRIMARY KEY (`user_id`),
 	 UNIQUE KEY `user_idx1_username` (`username`)
) 
ENGINE=InnoDB ROW_FORMAT=DYNAMIC COMMENT='用户表';

-- ----------------------------
-- Records of sys_user
-- ----------------------------
BEGIN;
INSERT INTO `sys_user` VALUES (1, 'admin', '$2a$10$RpFJjxYiXdEsAGnWp/8fsOetMuOON96Ntk/Ym2M/RKRyU0GZseaDC', NULL, '17034642999', '', 1, '0', '0', '2018-04-20 07:15:18', '2019-01-31 14:29:07', NULL, NULL);
COMMIT;

-- ----------------------------
-- Table structure for sys_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` 
(
	`role_id` bigint NOT NULL COMMENT '角色ID',
	`role_name` varchar(64) NOT NULL COMMENT '角色名称',
	`role_code` varchar(64) NOT NULL COMMENT '角色编号',
	`role_desc` varchar(255) DEFAULT NULL COMMENT '角色描述',
	`del_flag` char(1) DEFAULT '0' COMMENT '删除标识（0-正常,1-删除）',
	`create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
	`update_time` datetime DEFAULT NULL COMMENT '修改时间',
	`update_by` varchar(64) DEFAULT NULL COMMENT '修改人',
	 PRIMARY KEY (`role_id`),
	 UNIQUE KEY `unique_role_code` (`role_code`)
) 
ENGINE=InnoDB ROW_FORMAT=DYNAMIC COMMENT='系统角色表';

-- ----------------------------
-- Records of sys_role
-- ----------------------------
BEGIN;
INSERT INTO `sys_role` VALUES (1, '管理员', 'ROLE_ADMIN', '管理员', '0', '2017-10-29 15:45:51', '2018-12-26 14:09:11', NULL, NULL);
INSERT INTO `sys_role` VALUES (2, '普通用户','GENERAL_USER', '普通用户', '0', '2022-03-30 09:59:24', '2022-03-30 09:59:24', 'admin', 'admin');
COMMIT;

-- ----------------------------
-- Table structure for sys_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_menu`;
CREATE TABLE `sys_menu` 
(
 	`menu_id` bigint NOT NULL COMMENT '菜单ID',
 	`name` varchar(32) NOT NULL COMMENT '菜单名称',
 	`permission` varchar(32) DEFAULT NULL COMMENT '菜单权限标识',
 	`path` varchar(128) DEFAULT NULL COMMENT '前端URL',
 	`parent_id` bigint DEFAULT NULL COMMENT '父菜单ID',
 	`icon` varchar(32) DEFAULT NULL COMMENT '图标',
 	`sort_order` int NOT NULL DEFAULT '0' COMMENT '排序值',
 	`keep_alive` char(1) DEFAULT '0' COMMENT '0-开启，1- 关闭',
 	`type` char(1) DEFAULT NULL COMMENT '菜单类型 （0菜单 1按钮）',
 	`del_flag` char(1) DEFAULT '0' COMMENT '逻辑删除标记(0--正常 1--删除)',
	`create_time` datetime DEFAULT NULL COMMENT '创建时间',
    `create_by` varchar(64) DEFAULT NULL COMMENT '创建人',
	`update_time` datetime DEFAULT NULL COMMENT '修改时间',
	`update_by` varchar(64) DEFAULT NULL COMMENT '修改人',
 	 PRIMARY KEY (`menu_id`)
)	 
ENGINE=InnoDB ROW_FORMAT=DYNAMIC COMMENT='菜单权限表';

-- ----------------------------
-- Records of sys_menu
-- ----------------------------
BEGIN;
INSERT INTO `sys_menu` VALUES ('1000', '权限管理', null, '/admin', '-1', 'icon-quanxianguanli', '1', '0', '0', '0', ' ', '2018-09-28 08:29:53', ' ', '2020-03-11 23:58:18');
COMMIT;

-- ----------------------------
-- Table structure for sys_user_role
-- ----------------------------
DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` 
(
	`user_id` bigint NOT NULL COMMENT '用户ID',
	`role_id` bigint NOT NULL COMMENT '角色ID',
	 PRIMARY KEY (`user_id`,`role_id`)
) 
ENGINE=InnoDB ROW_FORMAT=DYNAMIC COMMENT='用户角色表';

-- ----------------------------
-- Records of sys_user_role
-- ----------------------------
BEGIN;
INSERT INTO `sys_user_role` VALUES (1, 1);
COMMIT;

-- ----------------------------
-- Table structure for sys_role_menu
-- ----------------------------
DROP TABLE IF EXISTS `sys_role_menu`;
CREATE TABLE `sys_role_menu` 
(
	`role_id` bigint NOT NULL COMMENT '角色ID',
	`menu_id` bigint NOT NULL COMMENT '菜单ID',
	 PRIMARY KEY (`role_id`,`menu_id`)
) 
ENGINE=InnoDB ROW_FORMAT=DYNAMIC COMMENT='角色菜单表';

-- ----------------------------
-- Records of sys_role_menu
-- ----------------------------
BEGIN;
INSERT INTO `sys_role_menu` VALUES (1, 1000);
INSERT INTO `sys_role_menu` VALUES (1, 1100);
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;