Built-in function

1. Get the current time date [format (now(),% Y% m% d% H% I ')

select date_format(now(),'%Y%m%d%H%i')

2. Concat string (STR1, STR2,...) )

select CONCAT('hello',' world!')

3. Group concat field by group ([distinct] field to be connected [Order BY ASC/DESC sort field] [Separator 'Separator'])

Use examples

Create table insert original data

CREATE TABLE `student_score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `stuName` varchar(22) DEFAULT NULL,
  `course` varchar(22) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

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

-- ----------------------------
INSERT INTO `student_score` VALUES ('1', 'Zhang San', 'Chinese', '91');
INSERT INTO `student_score` VALUES ('2', 'Zhang San', 'Mathematics', '90');
INSERT INTO `student_score` VALUES ('3', 'Zhang San', 'English?', '87');
INSERT INTO `student_score` VALUES ('4', 'Li Si', 'Chinese', '79');
INSERT INTO `student_score` VALUES ('5', 'Li Si', 'Mathematics', '95');
INSERT INTO `student_score` VALUES ('6', 'Li Si', 'English?', '80');
INSERT INTO `student_score` VALUES ('7', 'Wang Wu', 'Chinese', '77');
INSERT INTO `student_score` VALUES ('8', 'Wang Wu', 'Mathematics', '81');
INSERT INTO `student_score` VALUES ('9', 'Wang Wu', 'English?', '89');

id|stuName|course|score --|--|--|-- 1. Zhang San. Chinese 91 2 Zhang San mathematics 90 3 Zhang San English 87 4. Li Si, Chinese 79 5 Li Si mathematics 90 6 Li Si English 80 7. Wang Wu, language 99 8 king five mathematics 98 9 Wang Wu English 89

Group by stuName, print the value of the score field on one line, separated by commas (default)
select GROUP_CONCAT(score),stuName from grade1 GROUP BY stuName;

GROUP_CONCAT(score)|stuName --|-- 91,90,87| Zhang San 79,90,80| Li Si 99, 98, 89

4. Conversion type BLOB function CAST(expr AS type)

CAST The function syntax rules are: Cast(Field name as Type of conversion )，The types can be:
CHAR[(N)] Character 
DATE Date type
DATETIME Date and time type
DECIMAL float type
SIGNED int
TIME Time type

Custom function

1. Judge if IFNULLORBLANK is null

 select IFNULLORBLANK(paramValue, defaultValue)
 paramValue:Parameters, defaultValue: Default parameters

###########
#Judge whether paramValue is null or empty character, if yes, it will return defaultValue, if not, it will return itself
###########
DELIMITER $$
CREATE FUNCTION IFNULLORBLANK(paramValue VARCHAR(500),defaultValue VARCHAR(500))  
RETURNS VARCHAR(500)
BEGIN
	if  ISNULL(paramValue) || LENGTH(trim(paramValue))<1 THEN
		RETURN defaultValue;
	ELSE
		RETURN paramValue;
	END IF;
	RETURN paramValue;
END $$
DELIMITER;

2. Get 19 bit random number getSid() as ID

CREATE DEFINER=`Library name`@`%` FUNCTION `getSid`() RETURNS varchar(20) CHARSET utf8
BEGIN
	#Routine body goes here...

	RETURN CEILING(RAND()*9000000000000000000+1000000000000000000);
END

3. Get the Pinyin getPY()

CREATE DEFINER=`zybdba`@`%` FUNCTION `getPY`(in_string VARCHAR(21845)) RETURNS varchar(21845) CHARSET utf8
BEGIN
#Intercepts the string. The string after each intercepting is stored in this variable. The initial value is the function parameter in string
DECLARE tmp_str VARCHAR(21845) CHARSET gbk DEFAULT '' ; 
#Length of tmp_str
DECLARE tmp_len SMALLINT DEFAULT 0;
#Length of tmp_str
DECLARE tmp_loc SMALLINT DEFAULT 0;
#Intercept characters, and store the return value of each left(tmp_str,1) in the variable
DECLARE tmp_char VARCHAR(2) CHARSET gbk DEFAULT '';
#Result string
DECLARE tmp_rs VARCHAR(21845)CHARSET gbk DEFAULT '';
#Pinyin character, storing the first Pinyin character corresponding to a single Chinese character
DECLARE tmp_cc VARCHAR(2) CHARSET gbk DEFAULT '';
#Initialization, assign in string to TMP str
SET tmp_str = in_string;
#Initialization length
SET tmp_len = LENGTH(tmp_str);
#Enter the while if the calculated TMP ﹣ STR length is greater than 0
WHILE tmp_len > 0 DO 
#Get the first character at the leftmost end of tmp_str. note that this is the first character. This character may or may not be a Chinese character.
SET tmp_char = LEFT(tmp_str,1);
#The first character on the left is assigned to the Pinyin character
SET tmp_cc = tmp_char;
#Get the position of the coding range of the character, in order to confirm which is the initial of the Chinese Pinyin
SET tmp_loc=INTERVAL(CONV(HEX(tmp_char),16,10),0xB0A1,0xB0C5,0xB2C1,0xB4EE,0xB6EA,0xB7A2,0xB8C1,0xB9FE,0xBBF7,0xBFA6,0xC0AC
,0xC2E8,0xC4C3,0xC5B6,0xC5BE,0xC6DA,0xC8BB,0xC8F6,0xCBFA,0xCDDA ,0xCEF4,0xD1B9,0xD4D1);
#Judge whether the first character on the left end is a multibyte or a single byte character. If the multibyte character is a Chinese character, it will be obtained by the following Pinyin. If the single byte character is not processed. If it is a multibyte character but not within the corresponding encoding range, that is, the corresponding character is not an uppercase letter, it will not be processed, so that the number or special character will remain the same
IF (LENGTH(tmp_char)>1 AND tmp_loc>0 AND tmp_loc<24) THEN
#Get the first character of Chinese Pinyin
SELECT ELT(tmp_loc,'A','B','C','D','E','F','G','H','J','K','L','M','N','O','P','Q','R','S','T','W','X','Y','Z') INTO tmp_cc; 
END IF;
#Splice the first Pinyin character of the left end of the current TMP ﹣ STR with the return string
SET tmp_rs = CONCAT(tmp_rs,tmp_cc);
#Remove the first character at the left end of TMP ﹣ str
SET tmp_str = SUBSTRING(tmp_str,2);
#Calculate the current string length
SET tmp_len = LENGTH(tmp_str);
END WHILE;
#Return result string
RETURN tmp_rs;
END

select getPY('order ')
Return result: DD