MySQL--练习38-67

SQL38 针对actor表创建视图actor_name_view

CREATE VIEW actor_name_view AS SELECT
first_name AS first_name_v,
last_name AS last_name_v 
FROM
	actor;

SELECT * FROM actor_name_view;

SQL39 针对上面的salaries表emp_no字段创建索引idx_emp_no

SELECT * FROM salaries FORCE INDEX(idx_emp_no) WHERE emp_no=10005

SQL40 在last_update后面新增加一列名字为create_date

ALTER TABLE actor ADD COLUMN  create_date  datetime  NOT NULL DEFAULT '2020-10-01 00:00:00' AFTER  last_update;

触发器是一种与表操作有关的数据库对象

# 即表的操作事件触发表上的触发器的执行
# 创建触发器 mysql
/*
在MySQL中，创建触发器语法如下：
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW trigger_stmt

其中：
trigger_name：标识触发器名称，用户自行指定；
trigger_time：标识触发时机，取值为 BEFORE 或 AFTER；
trigger_event：标识触发事件，取值为 INSERT、UPDATE 或 DELETE；
tbl_name：标识建立触发器的表名，即在哪张表上建立触发器；
trigger_stmt：触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句。

由此可见，可以建立6种触发器，即：BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER 

DELETE。
另外有一个限制是不能同时在一个表上建立2个相同类型的触发器，因此在一个表上最多建立6个触发器
*/
# SQL41 构造一个触发器audit_log
# 触发名字  触发时机  触发事件  在那张表上建立触发器
CREATE TRIGGER audit_log AFTER INSERT ON employees_test FOR EACH ROW
BEGIN
	INSERT INTO audit
VALUES
	( new.id, new.NAME );
END;
链接 https://blog.csdn.net/weixin_41177699/article/details/80302987

SQL42 删除emp_no重复的记录，只保留最小的id对应的记录。

DELETE FROM  titles_test 
WHERE id NOT in (SELECT * FROM (SELECT min(id) FROM titles_test GROUP BY emp_no) a )

#MySQL中不允许在子查询的同时删除表数据（不能一边查一边把查的表删了）

分组获取到最小id

SELECT min(id) FROM titles_test GROUP BY emp_no

SQL43 将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。

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'

SQL50 将employees表中的所有员工的last_name和first_name通过引号连接起来。

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