牛客SQL实战

文章目录

• 预备知识点
• • 1. mysql常用方法
  • 2. if语句
  • 3. 索引
  • 4. 视图
  • 5. 触发器
  • 功能点
  • • 1.mysql分页
    • 2. 生成序列号
    • 3. 数据复制
    • 4. 格式化时间
    • 5. 累计求和
    • 案例
    • • 案例一
      • 案例二
      • 案例三
      • 案例四
      • 案例五
      • 案例六
      • ���例七

        预备知识点

        1. mysql常用方法

        GROUP_CANCAT、LENGTH、SUBSTRING …

        （）

        詳見：https://zhuanlan.zhihu.com/p/360367679

        2. if语句

        # postgre if判断语句DO $$BEGIN    IF NOT EXISTS (        SELECT 1FROM subsystemsWHERE identifier in ('ColdHeatSourceSystem')    ) thenINSERT INTO public.subsystems (id, identifier, "name", model_status, create_user, create_time, update_user, update_time) VALUES('9c85bed1-5da1-43d0-806e-6cb7ff22fd4d'::uuid, 'ColdHeatSourceSystem', '冷热源系统', 1, '0ba6771a-d316-4c07-ae87-06eb6a49f381'::uuid, '2023-03-16 19:36:17.051', NULL, NULL);END IF;END$$;# msyql if判断语句IF condition THEN       statements;    ELSE       else-statements;END IF;# MySQL IGNORE关键字用于在执行INSERT语句时，如果遇到主键或唯一索引冲突，则忽略该条数据。INSERT IGNORE INTO actor VALUES(4, 'ED', 'CHAS1E', '2006-02-15 12:34:33');

        3. 索引

        -- 创建普通索引create index idx_lastname on actor(last_name);-- 创建唯一索引create unique index uniq_idx_firstname on actor(first_name); -- 删除索引drop index idx_lastname on actor;-- 查看所有索引show index from actor;-- 使用强制索引select *from salaries s force index(idx_emp_no)  -- 前提：该索引已创建where emp_no = 10005;

        4. 视图

        -- 创建视图：视图不存在则创建，存在则更新create or replace view actor_name_view asselect first_name first_name_v, last_name last_name_vfrom actor;-- 修改视图alter view actor_name_view asselect first_namefrom actor;-- 删除视图drop view actor_name_view;-- 查看视图详情SHOW CREATE VIEW actor_name_view;

        5. 触发器

        create trigger audit_log after insert on employees_testfor each rowbegin    insert into audit values(new.id,new.name);end详见：https://cloud.tencent.com/developer/article/2232342

        功能点

        1.mysql分页

        limit：一般用法为limit X，Y意思为跳过X条数据读取Y条数据（包括X）    select * from table limit 3,6；            # 跳过前三条然后取6条数据，也就是取出4-9条数据offset：一般用法为limit x offset y，表示从y后面开始读取x条数据（不包括y）    select * from table limit 6 offset 3；  # 偏移量是3所以跳过前3条数据，然后再取出后面的6条数据。

        2. 生成序列号

        # ROW_NUMBER()：将select查询到的数据进行排序，每一条数据加一个序号，当排序字段值相同，排序也是递增的# 标准的序列号 1 2 3 4。    SELECT *, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_num    FROM salaries;# RANK()函数：顾名思义排名函数，可以对某一个字段进行排名，当排序字段值相同时，序列号也是相同的# 后续序号是跳跃不连续的，会累加 比如 1 2 2 4    SELECT *, rank() OVER (ORDER BY salary DESC) AS rank_num    FROM salaries;# DENSE_RANK()函数：也是排名函数，当排序字段值相同时，序列号也是相同的# 后续序号是连续的，不会累加 比如 1 2 2 3    SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) AS rank_num    FROM salaries;

        3. 数据复制

        表中数据复制，从一个表读数据复制到另一个表insert into actor_name(first_name, last_name)select first_name, last_namefrom actor;

        4. 格式化时间

        -- mysql获取当前时间,可自定义格式select curdate(); -- 2024-02-20SELECT DATE_FORMAT(NOW(), '%Y-%m-%d') as current_datetime; -- 2024-02-20SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS current_datetime; -- 2024-02-20 18:10:01-- postgre获取当前时间,可自定义格式SELECT TO_CHAR(NOW(), 'YYYY-MM-DD') AS current_datetime;SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS') AS current_datetime;

        5. 累计求和

        SQL281 最差是第几名(一)-- 累计求和select grade, sum(`number`) over (order by grade) t_rankfrom class_grade cg 

        案例

        案例一

        SQL196 查找入职员工时间排名倒数第三的员工所有信息

        （）

            # --有问题，只能找出一个排名倒数第三入职的员工，可能有多个入职时间相同的情况    # --select * from employees    # --order by hire_date desc    # --limit 2, 1select *from employeeswhere hire_date = (    select distinct  # -- 精确查找倒数第三个员工的入职日期    hire_date    from  employees     order by hire_date desc    limit 2, 1)

        案例二

        SQL206 获取每个部门中当前员工薪水最高的相关信息

        # 2.根据最大薪水、部门号，进行三表关联找最大薪水的人是谁select distinct temp.dept_no, ss.emp_no, temp.maxSalaryfrom (# 1.临时表，获取每个部门最大薪水，但不知道最大薪水的人是谁select distinct de.dept_no, max(s.salary) maxSalaryfrom dept_emp deleft join salaries s on de.emp_no = s.emp_nogroup by de.dept_no) templeft join salaries ss on temp.maxSalary = ss.salaryleft join dept_emp de on de.emp_no = ss.emp_noorder by temp.dept_no asc;

        案例三

        SQL212 获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

        # 1. 关联查询第二高薪水的员工信息select e.emp_no, s.salary, e.last_name, e.first_namefrom employees eleft join salaries s on e.emp_no  = s.emp_no where s.salary = (# 1. 第二高薪水具体值select max(salary) from salarieswhere salary  
        案例四
           
        SQL253 获取有奖金的员工相关信息
          
        select e.emp_no ,e.first_name ,e.last_name , eb.btype , s.salary, ( # 2. 根据字段值写IF语句case eb.btype when 1 then round(s.salary * 0.1,1) # 1. 四舍五入，保存一位小数when 2 then round(s.salary * 0.2,1)else round(s.salary * 0.3,1)end) as bonus # 当前工资的比例，不是总工资from employees einner join emp_bonus eb on e.emp_no = eb.emp_no  # 3. 关联查询，主要和leftjoin的区别inner join salaries s on e.emp_no = s.emp_no  where s.to_date ='9999-01-01'order by e.emp_no asc 
         
        案例五
           
        SQL279 实习广场投递简历分析(二)
          
        select job, date_format(`date`, '%Y-%m') mon,  -- 根据月分组，不是日sum(num) cnt  from resume_info ri where date_format(`date`, '%Y') = '2025'    -- 2025年的数据group by job, monorder by mon desc ,cnt desc
         
        案例六
           
        SQL285 获得积分最多的人(三)
          
        SELECT u.id, u.name, SUM(if(type='add', grade_num, -grade_num)) as total_gradeFROM user uJOIN grade_info g ON u.id = g.user_idGROUP BY u.id, u.nameHAVING total_grade = (-- 用户最高分    SELECT MAX(total_grade)    FROM (        -- 每个用户的分数之和，有加有减！！        SELECT SUM(if(type='add', grade_num, -grade_num)) as total_grade        FROM grade_info        GROUP BY user_id    ) as subquery)ORDER BY u.id ASC;
         
        案例七
           
        SQL255 给出employees表中排名为奇数行的first_name
          
        -- 不能直接where rank_num % 2 = 1;-- SELECT emp_no, first_name, ROW_NUMBER() OVER (ORDER BY first_name asc) AS rank_num-- FROM employees e-- WHERE rank_num % 2 = 1;-- 在查出来的结果基础上再封装一层select first_name from (SELECT emp_no, first_name, ROW_NUMBER() OVER (ORDER BY first_name asc) AS rank_num -- 排序FROM employees e ) tempwhere temp.rank_num % 2 = 1order by emp_no;