• 【MySql】牛客SQL刷题(下)


    【MySql】牛客SQL刷题(上)

    1. 将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分

      拼接:concat函数,"||"拼接字符串

      -- 方法一 
      select concat(concat(last_name," "),first_name) as name from employees;
      -- 方法二
      select concat(last_name," ",first_name) as name from employees;
      -- 方法三:"||"拼接字符串
      select last_name||" "||first_name as name from employees;
      
    2. 创建一个actor表

      其中要求 最后更新时间为默认系统当前时间:DEFAULT (datetime('now','localtime'))

      create table actor(
          actor_id smallint(5) not null,
          first_name varchar(45) not null,
          last_name varchar(45) not null,
          last_update timestamp not null DEFAULT (datetime('now','localtime')),
          PRIMARY KEY(actor_id)
      );
      
    3. 对于表actor批量插入数据

      insert into actor values(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),(2,'NICK','WAHLBERG','2006-02-15 12:34:33');
      
    4. 对于表actor批量插入如下数据,如果数据已经存在,请忽略,不使用replace操作

      IGNORE:存在,自动忽略

      INSERT IGNORE INTO actor values(3,'ED','CHASE','2006-02-15 12:34:33');
      
    5. 创建一个actor_name表,从actor表中导入数据

      create table actor_name(
          first_name varchar(45) not null,
          last_name varchar(45) not null
      );
      INSERT INTO actor_name select first_name,last_name from actor;
      
    6. 表actor结构创建索引

      对first_name创建唯一索引uniq_idx_firstname,对last_name创建普通索引idx_lastname

      create unique index uniq_idx_firstname on actor(first_name);
      create index idx_lastname on actor(last_name);
      
    7. 针对actor表创建视图actor_name_view,并给字段起别名;

      -- 方法一
      create view actor_name_view (first_name_v,last_name_v) as
      select first_name ,last_name from actor;
      -- 方法二
      CREATE VIEW actor_name_view AS
      SELECT first_name AS fist_name_v, last_name AS last_name_v
      FROM actor
      
    8. 针对salaries表emp_no字段创建索引idx_emp_no,查询emp_no为10005, 使用强制索引

      -- 创建索引
      create index idx_emp_no on salaries(emp_no);
      -- 强制使用索引 indexed
      select * from salaries
      indexed by idx_emp_no
      where emp_no = '10005'
      
    9. 添加列:last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL,默认值为'0000 00:00:00'

      ALTER table actor add create_date datetime not null default '0000-00-00 00:00:00';
      
    10. 构造一个触发器audit_log,在向employees_test表中插入一条数据的时候,触发插入相关的数据到audit中

      create trigger audit_log after insert on employees_test
      -- 触发内容在begin,end间,并且内容必须分号结尾
      begin
      -- new:拿到上个表中的id,name对应值(此值是employees更新之后的值)
      -- old:拿到更新之前的值
           insert into audit values(new.id,new.name);
      end;
      
    11. 删除emp_no重复的记录,只保留最小的id对应的记录

      (一个表中,多个emp_no重复,删除,并保留其中一个最小的)

      思路:通过emp_no分组,只要不是min id,就删除;

      delete
      from titles_test
      where id not in(
      	select min(id) from titles_test group by emp_no
      );
      
    12. 更新日期:

      将所有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_data = '9999-01-01';
      
    13. 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现。

      replace:

      • 全字段替换
      • replace函数:replace(x,y,z)
      replace into titles_test 
      values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01');
      
    14. 将titles_test表名修改为titles_2017

      alter table titles_test rename to titles_2017;
      
    15. 在audit表上创建外键约束,其emp_no对应employees_test表的主键id。

      alter table audit
      add foreign key(emp_no) references employees_test(id)
      
    16. 通过某字段(emp_no)获取两张表的相同数据

      select em.*
      from employees as em,emp_v as ev
      where em.emp_no = ev.emp_no;
      
    17. 将所有获取奖金的员工当前的薪水增加10%;

      两个表:奖金表,员工薪水表

      查询奖金表中的emp_no

      update salaries
      set salary = salary * 1.1
      where emp_no in (select emp_no from emp_bonus);
      
    18. 将employees表中的所有员工的last_name和first_name通过(')连接起来

      select last_name||"'"||first_name as name from employees;
      
    19. 查找字符串'10,A,B' 中逗号','出现的次数cnt

      没有直接统计字符串数量的方法;

      通过构造两个字符串,相减,求出差值;

      select length('10,A,B') -length(replace('10,A,B',",","")) as cnt;
      
    20. 获取Employees中的first_name,查询按照first_name最后两个字母,按照升序进行排列

      substr(string,start,length)

      length可以省略,表示截取到最后;

      select first_name 
      from employees
      order by substr(first_name,length(first_name)-1)
      
    21. 按照dept_no进行汇总,属于同一个部门的emp_no按照逗号进行连接,结果给出dept_no以及连接出的结果employees

      group_concat聚合函数:group_concat(X,Y)

      x:药连接的字段

      y:连接用的符号(默认逗号,可以省略)

      select dept_no,group_concat(emp_no) AS employees
      from dept_emp
      group by dept_no
      order by dept_no;
      
    22. 查找排除当前最大、最小salary之后的员工的平均工资avg_salary

      排除某些条件:not in(<>)

      select avg(salary) as avg_salary
      from salaries
      where salary not in (select max(salary) from salaries)
      and salary not in (select min(salary) from salaries);
      
    23. 分页查询employees表,每5行一页,返回第2页的数据

      5行一页,第二页:6~10

      分页:limit(5,5)—从第六条数据开始,显示5条

      select *
      from employees
      limit 5,5
      
    24. 获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received ,没有分配具体的员工不显示

      left join:以左表为主,先满足左表数据

      select m.emp_no,m.dept_no,e.btype,e.recevied  
      from dept_emp as m
      left join emp_bonus as e
      on m.emp_no = e.emp_no
      
    25. 使用含有关键字exists查找未分配具体部门的员工的所有信息。

      -- 不用exists
      select * from employees
      where emp_no not in (select emp_no from dept_emp);
      -- 用exists
      SELECT * FROM employees as e WHERE NOT EXISTS 
      (SELECT emp_no FROM dept_emp WHERE emp_no = e.emp_no)
      
    26. 获取有奖金的员工相关信息。

      bonus类型btype为1其奖金为薪水salary的10%,btype为2其奖金为薪水的20%,其他类型均为薪水的30%

      通过btype,算出bonus,需要用到case

      select e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
      (case b.btype
          when 1 then s.salary*0.1
          when 2 then s.salary*0.2
          else s.salary*0.3 end
      ) as bonus
      from employees as e
      join salaries as s
      on e.emp_no = s.emp_no
      join emp_bonus as b
      on e.emp_no=b.emp_no
      and s.to_date='9999-01-01';
      
    27. 按照salary的累计和running_total,其中running_total为前两个员工的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;
      
    28. 对于employees表中,给出奇数行的first_name

      注意这里是奇数行,不是emp_no是奇数,emp_no可能是从偶数开始;

      所以:首先需要拿到表记录总数count(*)

      (select count(*) from employees as e2
      WHERE e1.first_name <= e2.first_name
      )

      这条语句返回的是:当前的记录时第几条记录;

      select first_name
      from employees as e1
      where (select count(*) from employees as e2
            WHERE e1.first_name <= e2.first_name
            ) % 2 = 1;
      
  • 相关阅读:
    oracle中常用的函数
    请求转发和URL重定向的原理和区别
    servlet的生命周期和servlet的继承关系
    Jdbc来操作事物 完成模拟银行的转账业务
    Map的嵌套 练习
    正则表达式练习
    学习 day4 html 盒子模型
    学习day03
    学习day02
    学习day01
  • 原文地址:https://www.cnblogs.com/mussessein/p/12295233.html
Copyright © 2020-2023  润新知