T35.对于表actor插入如下数据,如果数据已经存在,请忽略(不支持使用replace操作)
insert ignore into actor values("3","ED","CHASE","2006-02-15 12:34:33");
T52.获取employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列
SELECT first_name FROM employees ORDER BY RIGHT(first_name,2);
T53.按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees
select dept_no,group_concat(emp_no) as employees
from dept_emp
group by dept_no
T55.分页查询employees表,每5行一页,返回第2页的数据
SELECT *
FROM employees
LIMIT i,j
#i : 为查询结果的索引值(默认从0开始);j : 为查询结果返回的数量
T57.使用含有关键字exists查找未分配具体部门的员工的所有信息。
select * from employees e
where not exists
(select emp_no from dept_emp d where d.emp_no = e.emp_no);
T60.按照salary的累计和running_total,其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和,其他以此类推。 具体结果如下Demo展示。
#方法一
select emp_no,
salary,
sum(salary) over(order by emp_no) as running_total
from salaries
where to_date="9999-01-01"
# #方法二
select t1.emp_no, t1.salary, sum(t2.salary) as running_total
from salaries t1 join salaries t2
on t1.emp_no >= t2.emp_no
where t1.to_date="9999-01-01" and t2.to_date="9999-01-01"
group by t1.emp_no