12. 查询财务部员工的平均收入。
select avg(inCome) as 平均收入 from salary
where employeeID in (select employeeID from employees where departmentID =
(select departmentID from departments where departmentName='财务部'));
13. 返回字母’H’的ASCII码值,返回ASCII值为97、98、99的字符,组合成一个字符串。
select ASCII('H');
select CHAR(97,98,99);
14. 查询EMPLOYEES表中住址的最左侧的3个字符。
use yggl
select LEFT(address,3) from employees;
15. 连接‘我的’和‘理想’两个字符串。
select CONCAT('我的','理想');
16. 使用*号分别左填补、右填补‘我的’和‘理想’两个字符串为字符个数为5 的字符串。
select RPAD('我的',5,'*'),LPAD('我的',5,'*'),RPAD('理想',5,'*'),LPAD('理想',5,'*');
17. 查询EMPLOYEES表,在一列中显示所有研发部和财务部的员工的姓氏,在另一列中显示名字。
use yggl
select SUBSTRING(name,1,1)as 姓氏,SUBSTRING(name,2,length(name)-1)as 名
from employees where employeeID in
(select employeeID from employees where departmentID=
(select departmentID from departments where departmentName ='研发部'))
or
employeeID in
(select employeeID from employees where departmentID=
(select departmentID from departments where departmentName ='财务部'));
18. 获取当前的日期和时间。
select NOW();
19. 查询所有女员工的出生年份。
use yggl
select name,birthday from employees
where sex=0;
20. 查询研发部员工的年龄。
use yggl
select employeeid,name,YEAR(NOW())-YEAR(birthday) as 年龄
from employees
where departmentID=
(select departmentID from departments where departmentName ='研发部');
21. 获取当前时间的星期名。
select DAYNAME('2020-5-14');
22. 获得当前所选数据库名、当前用户和MyQL版本信息。
select DATABASE(),USER(),VERSION();
23. 查询编号大于’020000’的员工的编号、姓名和等级,收入大于等于3000元的等级显示“富豪”,小于3000元的等级显示“贫民”。
select employees.employeeid,name,
(select case
when inCome>3000 then '富豪'
when inCome < 3000 then '贫民' end )
As 等级
from employees,salary
where employees.employeeID =salary .employeeID
and employees.employeeid>020000;