mysql重要语句

查询存储过程

 select * from information_schema.triggers

1、创建带参数的存储过程

CREATE PROCEDURE tendsName (IN userIdId INT)

	SELECT
		u.userId,
		u.userName,
		u.userPassword,
		u.userSex,
		u.userPhone,
		u.token,
		u.tendersId,
		u.positionId,
		u.companyId,
		u.departmentId,
		i.imagePosition,
		sA.sysSubclasName AS tendersName,
		sB.sysSubclasName AS positionName,
		sC.sysSubclasName AS companyName,
		sD.sysSubclasName AS departmentName
	FROM
		USER AS u
	LEFT JOIN userimage AS ui ON u.userId = ui.userId
	LEFT JOIN image AS i ON ui.imageId = i.imageId
	LEFT JOIN syscode AS sA ON sA.sysSubclassCode = u.tendersId
	LEFT JOIN syscode AS sB ON sB.sysSubclassCode = u.positionId
	LEFT JOIN syscode AS sC ON sC.sysSubclassCode = u.companyId
	LEFT JOIN syscode AS sD ON sD.sysSubclassCode = u.departmentId
	WHERE
		u.userId = userIdId
	AND sA.sysCategoryCode = 'tenders'
	AND sB.sysCategoryCode = 'possition'
	AND sC.sysCategoryCode = 'company'
	AND sD.sysCategoryCode = 'department'

---------------------------------------------------------------------------------------------------

DROP PROCEDURE IF EXISTS `units_syscode_tree_head`;
CREATE  PROCEDURE `units_syscode_tree_head`(IN commonUtilUnits VARCHAR(30),IN icon_units VARCHAR(30))
BEGIN 
SET @i = 0;
SET @j = 0;
SET @levelNo = 0;
SET @icon_units = icon_units;
SET @commonUtilUnits = commonUtilUnits;
SELECT
	@i :=@i + 1 AS id ,@j :=@j + 1 AS pId ,@levelNo AS levelNo,
	s1.sysSubclassCode AS tendersOruserId,
	s1.sysSubclasName AS NAME,
	s1.icon
FROM
	(
		SELECT
			SUBSTR(s.sysSubclasName, 3) AS m,
			s.sysSubclassCode,
			s.sysSubclasName ,@icon_units AS icon
		FROM
			syscode AS s
		WHERE
			s.sysCategoryCode =@commonUtilUnits
	) AS s1
ORDER BY
	s1.m + 0;
END
;

2、调取存储过程

set @p_in=1;
 call tendsName(@p_in);

 3、将多个字段合拼

 SELECT GROUP_CONCAT( name ) name  
FROM tet 
WHERE  id='1'

4、子查询，将多个字段合拼

SELECT
	u4.*, GROUP_CONCAT(
		s4.sysSubclasName SEPARATOR '，'
	) AS companyName
FROM
	(
		SELECT
			u3.*, GROUP_CONCAT(
				s3.sysSubclasName SEPARATOR '，'
			) AS departmentName
		FROM
			(
				SELECT
					u2.*, GROUP_CONCAT(
						s2.sysSubclasName SEPARATOR '，'
					) AS possitionName
				FROM
					(
						SELECT
							u.*, GROUP_CONCAT(
								s.sysSubclasName SEPARATOR '，'
							) AS tendersName
						FROM
							`user` AS u,
							syscode AS s
						WHERE
							u.userId = '1'
						AND s.sysCategoryCode = 'tenders'
						AND s.sysSubclassCode & u.tendersId
						GROUP BY
							u.userId
					) AS u2,
					syscode AS s2
				WHERE
					s2.sysCategoryCode = 'possition'
				AND s2.sysSubclassCode & u2.positionId
			) AS u3,
			syscode AS s3
		WHERE
			s3.sysCategoryCode = 'department'
		AND s3.sysSubclassCode & u3.departmentId
	) AS u4,
	syscode AS s4
WHERE
	s4.sysCategoryCode = 'company'
AND s4.sysSubclassCode = u4.companyId

5、mysql 截取字符串排序

SELECT s.*  FROM syscode as s
WHERE s.deleteflag="1"
AND s.sysCategoryCode="tenders"
ORDER BY SUBSTRING(s.sysSubclasName,3)+1 ASC