mySQL学习20221209

-----字符串函数-----

1.ASCII()

eg:select ASCII('Abcde') from DUAL;

=>65

ps:返回的是字符串首位的ASCII码值

---

2.CHAR_LENGTH();

eg:select CHAR_LENGTH('hello'),CHAR_LENGTH(我们') from DUAL;

=>5,2

ps:返回的是字符的个数

---

3.LENGTH()

eg:select LENGTH('hello'),LENGTH('我们') from DUAL;

=>5,6

ps:返回的是字节的个数

---

4.CONCAT()

eg:select  CONCAT(emp.last_name,' worked for ',mgr.last_name) from employees emp,employees mgr where emp.manager_id=mgr.employees_id;

=> xxx worked for xxx

ps:字符串拼接

---

5.CONCAT_WS()

eg:select CONCAT_WS('-' 'hello','world') from DUAL;

=>hello-world

---

6.INSERT()

eg:select INSERT('helloworld','2','3','aaa') from DUAL;

=>haaaoworld

ps:字符串的索引是从1开始的

---

7.REPLACE()

eg:select REPLACE('hello','ll','mmm' ) from DUAL;

=>hemmmo

ps:如果替换不成功，不会报错，返回的是原字符串

---

8.

LOWER()

UPPER()

eg:select LOWER('aBcdtTFUv') from DUAL;

ps:字符串内容全部改为小写

---

9.

LEFT()

RIGHT()

eg:select LEFT('hello',2) from DUAL;

=>he

ps:返回左边2个字符

eg:select RIGHT('hello',3) from DUAL;

=>llo

ps:返回右边3个字符

---

10.

LPAD()

RPAD()

eg:select LPAD(emp.salary,'10','*') from employees emp;

=>**64000.00

ps:左填充，右对齐，共为10个字符，不足从左以*号补齐

---

11.

TRIM()

LTRIM()

RTRIM()

eg:select TRIM('  abc de f   ') from DUAL;

=>abc de f

eg:select LTRIM('  abc de f   ') from DUAL;

=>abc de f__

ps:去掉字符串两边儿空格;去掉字符串左边空格；去掉字符串右边空格

TRIM('s1' from 's')

eg:select TRIM('oo' from 'oohelloo') from DUAL;

=>hell

ps:去掉字符串s中开始与结尾的s1

---

12.REPLACE()

eg:select REPLACE('hello','3') from DUAL;

=>hellohellohello

ps:重复次数

---

13.SPACE()

eg:select LENGTH(SPACE(5)) from DUAL;

=>5

ps:5个空格

---

14.STRCMP()

eg:select STRCMP('abc','abd') from DUAL;

=>-1

ps:两个字符串逐次比较，大于返回结果为1，小于为-1，等于为0

---

15.substr()

eg: select substr('hello','2','2') from DUAL;

=>el

ps:返回字符串的index位置其len个字符串

---

16.LOCATE()

eg:select LOCATE('a','latin') from DUAL:

=>2

---

17.ELT(m,s1,s2,s3..)

eg:select ELT(2,'a','b','c') from DUAL;

=>b

---

18.NULLIF()

eg:select NULLIF(LENGTH(emp.fisrt_name),LENGTH(emp.last_name)) from employees emp;

=>value1和value2度相等返回null,不相等返回value1