15-基础SQL-函数-字符串函数

什么是函数：是指一段可以直接被另一段程序调用的程序或代码

MySQL的函数主要包括：字符串函数、数值函数、日期函数、流程函数。

字符串函数

MySQL中内置了很多字符串函数，常用的几个如下:

CONCAT(s1,s2) 演示：将字符串"Hello"和"MySQL"进行拼接

SELECT CONCAT("Hello","MySQL");

LOWER(str) 演示：将字符串"HELLO"转化为小写

SELECT LOWER("HELLO");

UPPER(str) 演示：将字符串"hello"转化为大写

SELECT UPPER("hello");

LPAD(str,n,pad) 演示：将字符串"01"左填充到五个长度，用"-"进行填充

SELECT LPAD("01",5,"-")

RPAD(str,n,pad) 演示：将字符串"01"右填充到五个长度，用"-"进行填充

SELECT RPAD("01",5,"-")

TRIM(str) 演示：去掉字符串 "  Hello  MySQL  " 头部和尾部的空格

SELECT TRIM("  Hello  MySQL  ")

SUBSTRING(str,start,len) 演示：从字符串"Hello MySQL"的第1个位置起，截取5个长度的字符串"Hello"

SELECT SUBSTRING("Hello MySQL",1,5)

 

案例：创建一个 emp 员工表，添加一些员工数据

CREATE TABLE emp(
    id int comment "编号",
    workno varchar(10) comment "工号",
    name varchar(10) comment "姓名",
    gender char(1) comment "性别",
    age tinyint unsigned comment "年龄",
    idcard char(18) comment "身份证号",
    workaddress varchar(50) comment "工作地址",
    entrydate date comment "入职时间"
) comment "员工表";

INSERT INTO emp (id,workno,name,gender,age,idcard,workaddress,entrydate)
VALUES    
(1,"1","员工1","男","23","123456789000000000","江西","2023-8-31"),
(2,"2","员工2","男","26","123456789000000001","河北","2023-12-31"),
(3,"3","员工3","女","23","123456789000000002","河北","2023-5-31"),
(4,"4","员工4","女","24","123456789000000003","山西","2023-3-31"),
(5,"5","员工5","男","23","123456789000000000","江西","2023-8-31"),
(6,"6","员工6","男","26","123456789000000001","河北","2023-12-31"),
(7,"7","员工7","女","23","123456789000000002","河北","2023-5-31"),
(8,"8","员工8","女","24","123456789000000003","山西","2023-3-31"),
(9,"9","员工9","男","23","123456789000000000","江西","2023-8-31"),
(10,"10","员工10","男","26","123456789000000001","河北","2023-12-31"),
(11,"11","员工11","女","23","123456789000000002","河北","2023-5-31"),
(12,"12","员工12","女","24","123456789000000003","山西","2023-3-31")

1）由于业务需求变更，企业员工的工号，统一为五位数，目前不足五位数的全部在前面补0，比如：1号员工的工号应该是00001

UPDATE emp SET workno = LPAD(workno,5,"0");

SELECT * FROM emp;