MySQL基础练习

MySQL基础练习

#一.字符函数
1 CONCAT 拼接字符
SELECT CONCAT(‘hello’,first_name,last_name) 备注 FROM employees;

2 LENGTH 获取字节长度
SELECT LENGTH(‘hello,中国’);

3 CHAR_LENGTH 获取字符个数
SELECT CHAR_LENGTH(‘hello,中国’);

4 SUBSTRING 截取字符串
/*
substr(str,起始索引,截取字符的长度);
*/
SELECT SUBSTR(‘张三丰爱上了郭襄’,1,3);

SELECT SUBSTR(‘张三丰爱上了郭襄’,5);

5 INSTR 获取字符第一次出现的索引
SELECT INSTR(‘三打a白骨精aa白骨精bb白骨精’,‘白骨精’);

6 TRIM 去前后指定的字符， 默认是去空格
SELECT TRIM(’ 虚 竹 ') AS a;

SELECT TRIM(‘xxxxxxx 虚xxx竹 xxxxxxxxxx’) AS a;

SELECT TRIM(‘x’ FROM ‘xxxxxxx虚xxx竹xxxxxxxxxx’) AS a;

7 LPAD/RPAD 左填充/右填充
SELECT LPAD(‘木婉清’,10,‘a’);
SELECT RPAD(‘木婉清’,10,‘a’);
SELECT RPAD(‘木婉清’,1,‘a’);

8 UPPER/LOWER 变大写、变小写
#案例：查询员工表的姓名，要求格式：姓首字符大写，其他字符小写，
#名所有字符大写，且名和姓直接用_分割吗，最后起别名"OUTPUT"

SELECT UPPER(SUBSTR(first_name,1,1)) FROM employees 首大写;
SELECT LOWER(SUBSTR(first_name,2)),first_name FROM employees 其他小写;
SELECT UPPER(last_name),last_name FROM employees;

SELECT CONCAT(UPPER(SUBSTR(first_name,1,1)),
LOWER(SUBSTR(first_name,2)),’_’,UPPER(last_name)) FROM employees AS OUTPUT;

9 STRCMP 比较两个字符大小
SELECT STRCMP(‘abc’,‘aaa’);

10 LEFT/RIGHT 截取子串
SELECT LEFT(‘鸠摩智’,2);
SELECT RIGHT(‘鸠摩智’,2);