牛客SQL编程SQL46-SQL55

SQ46.在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
解题思路：在哪个表上创建外键约束，改变这个表，关联另一个表的主键

alter table audit
add foreign key(EMP_no)
references employees_test(ID);

SQL48.请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)
解题思路：注意是当前员工，所以要筛选出已经离职的员工。

update salaries
set salary=salary*1.1
where emp_no in (select emp_no from emp_bonus
                where to_date='9999-01-01');

SQL50.将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)
解题思路：使用cancat,参数之间加连接符。返回结果为连接参数产生的字符串，如果有一个参数为null，则返回null。

select concat(last_name,"'",first_name) as name
               from employees;

也可以用concat_ws(separator,str1,str2…)，第一个参数是连接符。如果连接符为null，返回结果为null；这个函数会忽略值为null的参数，但不会忽略为空的参数。

select concat_ws("'",last_name,first_name) as name
               from employees;

SQL50.查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。
解题思路：把原字符串中的“，”用空串代替，再用原字符串的长度减去现在字符串的长度就是逗号“，”出现的次数。

select (length("10,A,B")-length(replace("10,A,B",",",""))) as cnt;

SQL52.获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
解题思路：可以使用substr函数，截取后两位字母排序。

select first_name
from employees
order by substr(first_name,-2,2);

也可以用right函数

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

SQL53.按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
CREATE TABLE dept_emp (
emp_no int(11) NOT NULL,
dept_no char(4) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
解题思路：使用聚集函数group_concat(x,y)，第一个参数是连接字段，第二个参数是连接符，可以省略，默认为逗号，需要和group by子句一起使用。
这题不需要置顶连接符为逗号，我试过了，会出现两个逗号

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

SQL54.查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary。
CREATE TABLE salaries ( emp_no int(11) NOT NULL,
salary int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,from_date));
解题思路：排除掉最大值，最小值后，再求员工的平均工资

SELECT avg(salary) as avg_salary
from salaries
where to_date="9999-01-01"
and salary!=(select max(salary) from salaries where to_date="9999-01-01")
and salary!=(select min(salary) from salaries where to_date="9999-01-01");

SQL55.分页查询employees表，每5行一页，返回第2页的数据
CREATE TABLE employees (
emp_no int(11) NOT NULL,
birth_date date NOT NULL,
first_name varchar(14) NOT NULL,
last_name varchar(16) NOT NULL,
gender char(1) NOT NULL,
hire_date date NOT NULL,
PRIMARY KEY (emp_no));
解题思路：分页查询使用limit语句，分页一般会用order by+limit，limit,x,y x：偏移量，即从第几条记录后开始返回，y：返回几条记录。

select * from employees
order by emp_no
limit 5,5;