UPDATE titles_test
set to_date=null,
from_date='2001-01-01'
WHERE to_date='9999-01-01'
#将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现,直接使用update会报错
UPDATE titles_test
SET emp_no = REPLACE(emp_no,'10001','10005')
WHERE id = 5;
SQL45 将titles_test表名修改为titles_2017
ALTER TABLE titles_test RENAME to titles_2017
在audit表上创建外键约束,其emp_no对应employees_test表的主键id
ALTER TABLE audit ADD CONSTRAINT FOREIGN KEY(EMP_no) REFERENCES employees_test(id);
SQL48 将所有获取奖金的员工当前的薪水增加10%
UPDATE salaries sa
INNER JOIN emp_bonus bo on bo.emp_no = sa.emp_no
SET salary = salary *1.1
WHERE sa.to_date='9999-01-01'
SELECT CONCAT(last_name,"'",first_name) name FROM employees
SQL51 查找字符串中逗号出现的次数
SELECT id , LENGTH(string) - LENGTH(REPLACE(string,',','')) FROM strings
SQL52 获取employees中的first_name
SELECT first_name FROM employees ORDER BY SUBSTR(first_name,-2)
# 第二方法
# LEFT(s,n)返回字符串 s 的前 n 个字符
# RIGHT(s,n)返回字符串 s 的后 n 个字符
select first_name
from employees
order by right(first_name,2)
SQL53 按照dept_no进行汇总
SELECT
dept_no,
GROUP_CONCAT( emp_no ) AS employees
FROM
dept_emp
GROUP BY dept_no
# GROUP_CONCAT 聚合函数group_concat(X,Y),其中X是要连接的字段,Y是连接时用的符号,可省略,默认为逗号
SQL54 平均工资salaries
# 查找排除在职(to_date = '9999-01-01' )员工的最大、最小salary之后,其他的在职员工的平均工资avg_salary
SELECT (SUM(salary)-min(salary)-MAX(salary))/(COUNT(*)-2) avg FROM salaries WHERE to_date = '9999-01-01'
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')
#分页查询employees表,每5行一页,返回第2页的数据
SELECT * FROM employees LIMIT 5,5
#使用含有关键字exists查找未分配具体部门的员工的所有信息。
SELECT
*
FROM
employees es
WHERE
NOT EXISTS ( SELECT de.emp_no FROM dept_emp de WHERE de.emp_no = es.emp_no )
SQL59 获取有奖金的员工相关信息。
SELECT
es.emp_no,
first_name,
last_name,
btype,
salary,
CASE
btype
WHEN 1 THEN TRUNCATE(salary * 0.1,1)
WHEN 2 THEN TRUNCATE(salary * 0.2,1)
ELSE TRUNCATE(salary * 0.3,1)
END AS bonus
FROM
emp_bonus bo,
salaries sa,
employees es
WHERE
bo.emp_no = sa.emp_no and bo.emp_no= es.emp_no
AND to_date = '9999-01-01'
ORDER BY es.emp_no
SQL60 统计salary的累计和running_total
# 按照salary的累计和running_total,其中running_total为前N个当前( to_date = '9999-01-01')员工的salary累计和
SELECT
s1.emp_no,
s1.salary,
( SELECT SUM( s2.salary ) FROM salaries AS s2 WHERE s2.emp_no <= s1.emp_no AND s2.to_date = '9999-01-01' ) AS running_total
FROM
salaries AS s1
WHERE
s1.to_date = '9999-01-01'
ORDER BY
s1.emp_no
#窗口函数
SELECT
emp_no,
salary,
SUM( salary ) OVER ( ORDER BY emp_no ) AS running_total
FROM
salaries
WHERE
to_date = '9999-01-01'
SQL61 给出employees表中排名为奇数行的first_name
SELECT
e.first_name
FROM employees e JOIN
(
SELECT
first_name
, ROW_NUMBER() OVER(ORDER BY first_name ASC) AS r_num
FROM employees
) AS t
ON e.first_name = t.first_name
WHERE t.r_num % 2 = 1;
SQL62 出现三次以上相同积分的情况
SELECT number FROM grade GROUP BY number HAVING count(number) >=3
SQL63 刷题通过的题目排名
SELECT
id,
number,
dense_rank() over( ORDER BY number desc ) as t_rank
FROM
passing_number
SQL64 找到每个人的任务
SELECT
p.id,
NAME,
content
FROM
person p
LEFT JOIN task t ON p.id = t.person_id
ORDER BY p.id asc
SQL65 异常的邮件概率
select email.date, round(
sum(case email.type when'completed' then 0 else 1 end)*1.0/count(email.type),3
) as p
from email
join user as u1 on (email.send_id=u1.id and u1.is_blacklist=0)
join user as u2 on (email.receive_id=u2.id and u2.is_blacklist=0)
group by email.date order by email.date;
SQL66 牛客每个人最近的登录日期(一)
SELECT
user_id,
Max( date ) AS id
FROM
login
GROUP BY
user_id
ORDER BY
user_id
SQL67 牛客每个人最近的登录日期(二)
SELECT
u.`name` u_n,
c.`name` c_n,
date
FROM
USER u,
client c,
( SELECT * FROM login WHERE ( user_id, date ) IN ( SELECT user_id, max( date ) FROM login GROUP BY user_id ) ) lo
WHERE
lo.user_id = u.id
AND c.id = lo.client_id
ORDER BY u_n
SQL38 针对actor表创建视图actor_name_viewCREATE VIEW actor_name_view AS SELECTfirst_name AS first_name_v,last_name AS last_name_v FROM actor;SELECT * FROM actor_name_view;SQL39 针对上面的salaries表emp_no字段创建索引idx_emp_noSELECT * FROM salaries FORCE INDEX(idx_em