mysql-字符串函数

作者: ssttIsme | 来源:发表于2020-04-04 21:06 被阅读0次

mysql-字符串函数
MYSQL-函数
mysql-函数:数字函数
mysql-函数:日期函数
PHP字符串处理函数
MySQL进阶之常用函数
mysql-内置函数
MySQL-窗口函数
mysql函数汇总之字符串函数
MySQL基本使用

USE guns;
SELECT * FROM sys_menu;
SELECT CHAR_LENGTH(NAME) FROM sys_menu; -- 字符数
SELECT LENGTH(NAME) FROM sys_menu; -- 字节数
SELECT CONCAT(CODE,'+',NAME) FROM sys_menu; -- 字符串连接
SELECT CONCAT_WS(',',pcode,NAME) FROM sys_menu;-- 使用分隔符连接字符串
SELECT INSTR(pcode,'e') FROM sys_menu;-- 返回第一个子串的位置,找不到返回0
SELECT pcode FROM sys_menu WHERE INSTR(pcode,'r')=3;
SELECT INSERT(NAME,1,2,'--')FROM sys_menu; -- 从1位置的2个字符用--替换
SELECT UPPER(CODE) FROM sys_menu; -- 变大写
SELECT LOWER(CODE) FROM sys_menu; -- 变小写
SELECT LEFT(CODE,4) FROM sys_menu; -- 取最左边的四个字符
SELECT RIGHT(CODE,4) FROM sys_menu; -- 取最左边的四个字符
SELECT LPAD(CODE,9,'*') FROM sys_menu; -- 左侧填充*，总长为9，不足9补*，足够不补
SELECT RPAD(CODE,9,'*') FROM sys_menu; -- 右侧填充*，总长为9，不足9补*，足够不补
SELECT TRIM(CODE) FROM sys_menu; -- 去除两端空格
SELECT SUBSTRING(CODE,5) FROM sys_menu; -- 从第5个位置开始的所有字符
SELECT SUBSTRING(CODE,1,3) FROM sys_menu;  -- 从第1个位置开始取3个字符
SELECT REPEAT(pcode,2) FROM sys_menu; -- 重复两次
SELECT REPLACE(CODE,'dept','department') FROM sys_menu; -- 字符串替换
SELECT REVERSE(pcode) FROM sys_menu; -- 翻转字符串
SELECT SPACE(5); -- 返回5个空格

建表sql


CREATE DATABASE /*!32312 IF NOT EXISTS*/`guns` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `guns`;

/*Table structure for table `sys_menu` */

CREATE TABLE `sys_menu` (
  `MENU_ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `CODE` varchar(255) DEFAULT NULL COMMENT '菜单编号',
  `PCODE` varchar(255) DEFAULT NULL COMMENT '菜单父编号',
  `PCODES` varchar(255) DEFAULT NULL COMMENT '当前菜单的所有父菜单编号',
  `NAME` varchar(255) DEFAULT NULL COMMENT '菜单名称',
  `ICON` varchar(255) DEFAULT NULL COMMENT '菜单图标',
  `URL` varchar(255) DEFAULT NULL COMMENT 'url地址',
  `SORT` int(65) DEFAULT NULL COMMENT '菜单排序号',
  `LEVELS` int(65) DEFAULT NULL COMMENT '菜单层级',
  `MENU_FLAG` varchar(32) DEFAULT NULL COMMENT '是否是菜单(字典)',
  `DESCRIPTION` varchar(255) DEFAULT NULL COMMENT '备注',
  `STATUS` varchar(32) DEFAULT 'ENABLE' COMMENT '菜单状态(字典)',
  `NEW_PAGE_FLAG` varchar(32) DEFAULT NULL COMMENT '是否打开新页面的标识(字典)',
  `OPEN_FLAG` varchar(32) DEFAULT NULL COMMENT '是否打开(字典)',
  `CREATE_TIME` datetime DEFAULT NULL COMMENT '创建时间',
  `UPDATE_TIME` datetime DEFAULT NULL COMMENT '修改时间',
  `CREATE_USER` bigint(20) DEFAULT NULL COMMENT '创建人',
  `UPDATE_USER` bigint(20) DEFAULT NULL COMMENT '修改人',
  PRIMARY KEY (`MENU_ID`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='菜单表';



insert  into `sys_menu`(`MENU_ID`,`CODE`,`PCODE`,`PCODES`,`NAME`,`ICON`,`URL`,`SORT`,`LEVELS`,`MENU_FLAG`,`DESCRIPTION`,`STATUS`,`NEW_PAGE_FLAG`,`OPEN_FLAG`,`CREATE_TIME`,`UPDATE_TIME`,`CREATE_USER`,`UPDATE_USER`) values (105,'system','0','[0],','系统管理','fa-user','#',30,1,'Y',NULL,'ENABLE',NULL,'1',NULL,NULL,NULL,NULL),(106,'mgr','system','[0],[system],','用户管理','','/mgr',1,2,'Y',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(107,'mgr_add','mgr','[0],[system],[mgr],','添加用户',NULL,'/mgr/add',1,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(108,'mgr_edit','mgr','[0],[system],[mgr],','修改用户',NULL,'/mgr/edit',2,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(109,'mgr_delete','mgr','[0],[system],[mgr],','删除用户',NULL,'/mgr/delete',3,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(110,'mgr_reset','mgr','[0],[system],[mgr],','重置密码',NULL,'/mgr/reset',4,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(111,'mgr_freeze','mgr','[0],[system],[mgr],','冻结用户',NULL,'/mgr/freeze',5,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(112,'mgr_unfreeze','mgr','[0],[system],[mgr],','解除冻结用户',NULL,'/mgr/unfreeze',6,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(113,'mgr_setRole','mgr','[0],[system],[mgr],','分配角色',NULL,'/mgr/setRole',7,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(114,'role','system','[0],[system],','角色管理',NULL,'/role',2,2,'Y',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(115,'role_add','role','[0],[system],[role],','添加角色',NULL,'/role/add',1,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(116,'role_edit','role','[0],[system],[role],','修改角色',NULL,'/role/edit',2,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(117,'role_remove','role','[0],[system],[role],','删除角色',NULL,'/role/remove',3,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(118,'role_setAuthority','role','[0],[system],[role],','配置权限',NULL,'/role/setAuthority',4,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(119,'menu','system','[0],[system],','菜单管理',NULL,'/menu',4,2,'Y',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(120,'menu_add','menu','[0],[system],[menu],','添加菜单',NULL,'/menu/add',1,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(121,'menu_edit','menu','[0],[system],[menu],','修改菜单',NULL,'/menu/edit',2,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(122,'menu_remove','menu','[0],[system],[menu],','删除菜单',NULL,'/menu/remove',3,3,'N',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(128,'log','system','[0],[system],','业务日志',NULL,'/log',6,2,'Y',NULL,'ENABLE',NULL,'0',NULL,NULL,NULL,NULL),(130,'druid','system','[0],[system],','监控管理',NULL,'/druid',7,2,'Y',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(131,'dept','system','[0],[system],','部门管理',NULL,'/dept',3,2,'Y',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(132,'dict','system','[0],[system],','字典管理',NULL,'/dict',4,2,'Y',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(133,'loginLog','system','[0],[system],','登录日志',NULL,'/loginLog',6,2,'Y',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(134,'log_clean','log','[0],[system],[log],','清空日志',NULL,'/log/delLog',3,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(135,'dept_add','dept','[0],[system],[dept],','添加部门',NULL,'/dept/add',1,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(136,'dept_update','dept','[0],[system],[dept],','修改部门',NULL,'/dept/update',1,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(137,'dept_delete','dept','[0],[system],[dept],','删除部门',NULL,'/dept/delete',1,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(138,'dict_add','dict','[0],[system],[dict],','添加字典',NULL,'/dict/add',1,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(139,'dict_update','dict','[0],[system],[dict],','修改字典',NULL,'/dict/update',1,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(140,'dict_delete','dict','[0],[system],[dict],','删除字典',NULL,'/dict/delete',1,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(141,'notice','system','[0],[system],','通知管理',NULL,'/notice',9,2,'Y',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(142,'notice_add','notice','[0],[system],[notice],','添加通知',NULL,'/notice/add',1,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(143,'notice_update','notice','[0],[system],[notice],','修改通知',NULL,'/notice/update',2,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(144,'notice_delete','notice','[0],[system],[notice],','删除通知',NULL,'/notice/delete',3,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(145,'hello','system_message','[0],[system_message],','系统消息','fa-rocket','/notice/hello',1,2,'Y',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(149,'api_mgr','dev_tools','[0],[dev_tools],','接口文档','fa-leaf','/swagger-ui.html',2,2,'Y',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(150,'to_menu_edit','menu','[0],[system],[menu],','菜单编辑跳转','','/menu/menu_edit',4,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(151,'menu_list','menu','[0],[system],[menu],','菜单列表','','/menu/list',5,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(152,'to_dept_update','dept','[0],[system],[dept],','修改部门跳转','','/dept/dept_update',4,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(153,'dept_list','dept','[0],[system],[dept],','部门列表','','/dept/list',5,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(154,'dept_detail','dept','[0],[system],[dept],','部门详情','','/dept/detail',6,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(155,'to_dict_edit','dict','[0],[system],[dict],','修改菜单跳转','','/dict/dict_edit',4,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(156,'dict_list','dict','[0],[system],[dict],','字典列表','','/dict/list',5,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(157,'dict_detail','dict','[0],[system],[dict],','字典详情','','/dict/detail',6,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(158,'log_list','log','[0],[system],[log],','日志列表','','/log/list',2,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(159,'log_detail','log','[0],[system],[log],','日志详情','','/log/detail',3,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(160,'del_login_log','loginLog','[0],[system],[loginLog],','清空登录日志','','/loginLog/delLoginLog',1,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(161,'login_log_list','loginLog','[0],[system],[loginLog],','登录日志列表','','/loginLog/list',2,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(162,'to_role_edit','role','[0],[system],[role],','修改角色跳转','','/role/role_edit',5,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(163,'to_role_assign','role','[0],[system],[role],','角色分配跳转','','/role/role_assign',6,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(164,'role_list','role','[0],[system],[role],','角色列表','','/role/list',7,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(165,'to_assign_role','mgr','[0],[system],[mgr],','分配角色跳转','','/mgr/role_assign',8,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(166,'to_user_edit','mgr','[0],[system],[mgr],','编辑用户跳转','','/mgr/user_edit',9,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(167,'mgr_list','mgr','[0],[system],[mgr],','用户列表','','/mgr/list',10,3,'N',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(171,'dev_tools','0','[0],','开发工具','fa-code','#',20,1,'Y',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL),(172,'system_message','0','[0],','系统消息','fa-rocket','#',10,1,'Y',NULL,'ENABLE',NULL,NULL,NULL,NULL,NULL,NULL);