MySQL自訂函數

標籤：str   rem   alt   concat   employee   values   efault   publish   har   

 set names gbk;
 DATE_FORMAT(publish_datetime,‘%Y-%m-%d‘);

select date_format(now(),‘%Y年%m月%d日 %H時:%i分:%s秒‘);

建立一個現即時間的函數：

 create function f1() returns varchar(30) return  date_format(now(),‘%Y年%m月%d日 %H時%i分%s秒‘);

 調用這個函數：

 

select f1();

 

CONCAT（）函數用於將多個字串串連成一個字串，是最重要的mysql函數之一

 

CREATE TABLE employees  (      employee_id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,      employee_name VARCHAR(50) NOT NULL,      employee_sex VARCHAR(10) DEFAULT ‘男‘,      hire_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,    employee_mgr INT(11),      employee_salary FLOAT DEFAULT 3000,      department_id INT(11)  ); 

 

 

INSERT INTO employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (‘David Tian‘,‘男‘,10,7500,1);  INSERT INTO employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (‘Black Xie‘,‘男‘,10,6600,1);  INSERT INTO employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (‘Moses Wang‘,‘男‘,10,4300,1);  INSERT INTO employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (‘Rena Ruan‘,‘女‘,10,5300,1);  INSERT INTO employees(employee_name,employee_sex,employee_mgr,employee_salary,department_id) VALUES (‘Sunshine Ma‘,‘女‘,10,6500,2);

建立函數－根據ID擷取員工姓名與員工工資

DELIMITER //  CREATE FUNCTION GetEmployeeInformationByID(id INT)  RETURNS VARCHAR(300)  BEGIN      RETURN(SELECT CONCAT(‘employee name:‘,employee_name,‘---‘,‘salary: ‘,employee_salary) FROM employees WHERE employee_id=id);  END//  DELIMITER ;  

SELECT GetEmployeeInformationByID(1) ;

 

 

MySQL自訂函數