sql 函数存储过程

函数

1).字符串类

---3 ??

SELECT gender,length(gender) from employee   

SELECT deptName,length(deptName) from dept

SELECT * from employee

SELECT EmpName,REPLACE(EmpName,'o','T') from employee

SELECT SUBSTRING(EmpName,1,3) from employee

SELECT EmpName,concat(SUBSTRING(EmpName,1,3),'***') from employee

(2).数学类

select abs(-1)

select pow(2,4)

select CEILING(2.8)

select CEILING(1.1)

select floor(1.1)

select floor(1.9)

select format(2.8148,2)

select format(2.8158,2) 

select bin(10)      -1010

select hex(10)  

select hex(12)  

(3).日期时间类

select now()

select year(NOW());

select month(NOW());

select day(NOW());

select date(NOW());

select time(NOW());

select dayofweek(NOW());

select dayofyear(NOW());

-- 10.1还有几天？

DATEDIFF (date1 ,date2 ) //两个日期差

select DATEDIFF('2019/10/1',date(NOW()))-1

存储过程 (Stored Procedure)

（1） 根据一个参数：工号empId=101，显示 这个人的姓名  (输入参数 “101”,输出参数“empName”)

     select empName from employee where empId=‘101'

删除为   drop 存储名

CREATE PROCEDURE Proc_GetEmpNameById(in _empId char(10),out _empName varchar(20) )

BEGIN

        select empName into _empName from employee where empId=_empId;

END;

set @empName='';

call Proc_GetEmpNameById('101',@empName);

select @empName;

（2）  根据一个参数：工号empId=101，显示 这个人的所有信息 (输入参数，得至一个多行多列的结果集)

    select * from employee where empId='101'

    select * from employee where gender='女'

CREATE PROCEDURE Proc_GetEmpByGender(in _gender char(10) )

BEGIN

        select  *  from employee where gender=_gender;

END;

调用

call Proc_GetEmpByGende(‘女');