首页 > 代码库 > MySQL自定义函数

MySQL自定义函数

2024-09-10 16:12:57   217人阅读

 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自定义函数


联系
我们