第7章：字符串函数

字符串函数

1.字符串函数

 2.举例

select ASCII('A'),CHAR_LENGTH('hello'),CHAR_LENGTH('我们'),
LENGTH('hello'),LENGTH('我们')
from dual;

 3.举例

①select CONCAT(emp.last_name,'-worked for-',mgr.last_name) "details"
from employees emp
left join employees mgr on emp.manager_id = mgr.employee_id

②
select CONCAT_WS('-worked for-',emp.last_name,mgr.last_name) "details"
from employees emp
left join employees mgr on emp.manager_id = mgr.employee_id

4.举例

 ① 字符串从1开始后的前3个字符用***替换

select insert(last_name,1,3,'***')
from employees

 

 ②字符串出现’a’用’*’代替

select replace(last_name,'a','*')
from employees

 5.举例

 ①last_name全是大写，first_name全是小写，用’-’拼接在一起

select concat(upper(last_name),'-',lower(first_name))
from employees

6.举例

 ①

select left(last_name,2),right(last_name,3)
from employees

 ②

select employee_id,last_name,salary,lpad(salary,10,'*'),rpad(salary,10,'*')
from employees

 7.字符串函数2

 

8.举例

①trim去除的是前尾空格

select trim('   zhi lei     '),LENGTH(trim('   zhi lei     '))
from dual

 ②去除字符串lol的开头和结尾的’l’

select trim('l' from 'lol')
from dual

③单独去除字符串开头的l和结尾的l

select trim(leading 'l' from 'lol'),trim(trailing 'l' from 'lol')
from dual

9.举例

①重复输出4次’hello’

select repeat('hello',4)
from dual

②返回5个空格

select space(5)
from dual

③比较字符串的大小 1 0 -1

select strcmp('abc','def')
from dual

④从hello字符串的第2个位置，取1个字符。得到e

select substr('hello',2,1)
from dual

10.举例

 

①返回’a’在last_name首次出现的位置

select last_name,locate('a',last_name)
from employees

②返回第一个字符串

select elt(1,'a','b','c')
from dual

③返回字符串a出现的位置

select field('a','a','b','c')
from dual

加密和解密函数

1.加密与解密函数

 2.mysql信息函数

 【练习题】

#1.显示系统时间(注: 日期+时间)
SELECT NOW()
FROM DUAL;

# 2.查询员工号，姓名，工资，以及工资提高百分之20%后的结果 (new salary)
SELECT employee_id,last_name,salary,salary * 1.2 "new salary"
FROM employees

# 3.将员工的姓名按首字母排序(按长度排序)，并写出姓名的长度(length)
SELECT last_name,LENGTH(last_name) name_len
FROM employees
ORDER BY last_name
或
SELECT last_name,LENGTH(last_name) "name_len"
FROM employees
ORDER BY name_len

# 4.查询员工id,last_name,salary，并作为一个列输出，别名为OUT_PUT
SELECT CONCAT(employee_id,'-',last_name,'-',salary) "OUT_PUT"
FROM employees

# 5.查询公司各员工工作的年数、工作的天数，并按工作年数的降序排序
SELECT employee_id,DATEDIFF(NOW(),hire_date)/365 "worked_years",DATEDIFF(NOW(),hire_date) "worked_days"
FROM employees
ORDER BY worked_years DESC

# 6.查询员工姓名，hire_date ，department_id，满足以下条件: 雇用时间在1997年之后，department_id 为80或9日或118.commission_pct不为空
select last_name,hire_date,commission_pct
from employees
where department_id in (80,90,110)
and commission_pct is not null
and hire_date >= '1997-01-01'
order by hire_date

# 7.查询公司中入职超过10000天的员工姓名、入职时间
SELECT last_name,DATEDIFF(NOW(),hire_date) "worked_days"
FROM employees
WHERE DATEDIFF(NOW(),hire_date)>=10000

# 8.做一个查询，产生下面的结果
<last_name> earns <salary> monthly but wants <salary*3>

select CONCAT(last_name,'earns ',salary,'monthly but wants',salary*3) "dream_salary"
from employees