mysql刷题知识总结（10.05）

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。

创建外键语句结构：
ALTER TABLE <表名>
ADD CONSTRAINT FOREIGN KEY (<列名>)
REFERENCES <关联表>（关联列）
实例：
alter table audit 
add CONSTRAINT FOREIGN key (emp_no)
references employees_test(id)

请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

子查询
update salaries
set salary=salary*1.1
where salaries.emp_no in (select emp_no from emp_bonus) and salaries.to_date='9999-01-01'

联表查询
update emp_bonus e join salaries s on e.emp_no=s.emp_no
set s.salary=s.salary*1.1
where s.to_date='9999-01-01'

update 语句可以使用 join，inner join, left join, right join 等等，和 select 语句一样的使用方式

---

将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)

#select concat(last_name,"'",first_name) name from employees;
//性能更佳
select concat_ws("'",last_name,first_name) name from employees;

查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

select (LENGTH('10,A,B')-LENGTH(replace('10,A,B',',',''))) as cnt

通过将逗号删减后长度的变化来查找逗号出现的次数。

---

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

select first_name from employees order by right(first_name,2)

本题的新点就是根据最后两个字母排序，用到mysql的right函数。
right 的作用：

返回字符串 str 中最右边的 len 个字符；如果任何参数为NULL，则返回NULL。
同理的还有left函数。

---

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果

select dept_no,GROUP_CONCAT(emp_no) as employees from dept_emp group by dept_no

聚合函数group_concat（X，Y），其中X是要连接的字段，Y是连接时用的符号，可省略，默认为逗号。
此函数必须与GROUP BY配合使用。此题以dept_no作为分组，将每个分组中不同的emp_no用逗号连接起来。

---

查找排除在职(to_date = ‘9999-01-01’ )员工的最大、最小salary之后，其他的在职员工的平均工资avg_salary。

做子查询的方式
 select avg(salary) as avg_salary  from salaries 
 where to_date='9999-01-01' 
 and salary not in (select max(salary) from salaries where to_date='9999-01-01' )
 and salary not in (select min(salary) from salaries where to_date='9999-01-01' )

自己排除最大最小项后计算的方式
 select (sum(salary)-max(salary)-min(salary))/(count(*)-2) as avg_salary  from salaries 
 where to_date='9999-01-01'