MYSQL练习

MYSQL

查找最晚入职员工的所有信息

题目描述
查找最晚入职员工的所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天(sqlite里面的注释为–,mysql为comment)
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));


select * from employees
where hire_date = (select max(hire_date) from employees)




考试分数(一)

题目描述
牛客每次考试完,都会有一个成绩表(grade),如下:
在这里插入图片描述

第1行表示用户id为1的用户选择了C++岗位并且考了11001分

。。。

第8行表示用户id为8的用户选择了前端岗位并且考了9999分

在这里插入图片描述
请你写一个sql语句查询各个岗位分数的平均数,并且按照分数降序排序,结果保留小数点后面3位(3位之后四舍五入):

(注意: sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5,sqlite四舍五入的函数为round)



ROUND(X)
返回参数X, 其值接近于最近似的整数。
ROUND(X,D)
在有两个参数的情况下,返回 X ,其值保留到小数点后D位,
而第D位的保留方式为四舍五入。若要接保留X值小数点左边的D 位,可将 D 设为负值。


select job, round(avg(score), 3) as `avg`
from grade
group by job
order by `avg` desc



牛客每个人最近的登录日期

题目描述
牛客每天有很多人登录,请你统计一下牛客每个用户最近登录是哪一天。
有一个登录(login)记录表,简况如下:
在这里插入图片描述

第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第4行表示id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网

请你写出一个sql语句查询每个用户最近一天登录的日子,并且按照user_id升序排序,上面的例子查询结果如下:
在这里插入图片描述

查询结果表明:
user_id为2的最近的登录日期在2020-10-13
user_id为3的最近的登录日期也是2020-10-13


select user_id, max(date) as d
from login
group by user_id
order by user_id



找到每个人的任务

题目描述
有一个person表,主键是id,如下:
在这里插入图片描述

有一个任务(task)表如下,主键也是id,如下:
在这里插入图片描述

请你找到每个人的任务情况,并且输出出来,没有任务的也要输出,而且输出结果按照person的id升序排序,输出情况如下:
在这里插入图片描述


select p.id, p.name, t.content
from person p left join task t
on p.id = t.person_id
order by p.id



出现三次及以上相同计分的情况

题目描述

在牛客刷题的小伙伴们都有着牛客积分,积分(grade)表简化可以如下:
在这里插入图片描述

id为用户主键id,number代表积分情况,让你写一个sql查询,积分表里面出现三次以及三次以上的积分,查询结果如下:

在这里插入图片描述

having字句可以让我们筛选分组之后的各种数据,where字句在聚合前先筛选记录
where>聚合函数(sum,min,max,avg,count)>having
若须引入聚合函数来对group by 结果进行过滤 则只能用having。

select number from grade
group by number
having count(number) >= 3



将titles_test表名修改为titles_2017

题目描述
将titles_test表名修改为titles_2017。

CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

/*mysql中修改表名和表中的字段名都用ALTER*/

alter table titles_test rename titles_2017



将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

题目描述
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现,直接使用update会报错了

CREATE TABLE titles_test (
   id int(11) not null primary key,
   emp_no  int(11) NOT NULL,
   title  varchar(50) NOT NULL,
   from_date  date NOT NULL,
   to_date  date DEFAULT NULL);

insert into titles_test values
('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

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

题目描述
查找入职员工时间排名倒数第三的员工所有信息,为了减轻入门难度,目前所有的数据里员工入职的日期都不是同一天
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));
输入描述:

输出描述:
emp_no birth_dat first_name last_name gender hire_date
10005 1955-01-21 Kyoichi Maliniak M 1989-09-12


select * from employees
order by hire_date desc
limit 2, 1
/*
limit 2, 1   ===   limit 1 offset 2
*/

/*
select * from 表名 limit m,n
limit索引从0开始,m表示索引的位置,n表示limit(限制)的条数
*/



查找所有已经分配部门的员工的last_name和first_name以及dept_no

题目描述
查找所有已经分配部门的员工的last_name和first_name以及dept_no(请注意输出描述里各个列的前后顺序)
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));
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));


select e.last_name, e.first_name, d.dept_no
from dept_emp d left join employees e
on d.emp_no = e.emp_no
/*
或直接内连接取交集
*/



查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t

题目描述
查找薪水变动超过15次的员工号emp_no以及其对应的变动次数t
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 emp_no, count(emp_no) t
from salaries
group by emp_no
having t > 15

/*
严格来说涨幅超过15次,则相应的记录应该超过16次
select emp_no, count(distince from_date)-1 t
*/



找出所有员工当前(to_date=‘9999-01-01’)具体的薪水salary情况

题目描述
找出所有员工当前(to_date=‘9999-01-01’)具体的薪水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 distinct salary
from salaries
where to_date = '9999-01-01'
order by salary desc

对于distinct与group by的使用: 1、当对系统的性能高并数据量大时使用group by 
2、当对系统的性能不高时使用数据量少时两者皆可 3、尽量使用group by
*/

select salary
from salaries
where to_date = '9999-01-01'
group by salary
order by salary desc



将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005

题目描述
将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=10005,其他数据保持不变,使用replace实现,直接使用update会报错了。

CREATE TABLE titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values
(‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);


/*
REPLACE(str,from_str,to_str) 
在字符串 str 中所有出现的字符串 from_str 均被 to_str替换,然后返回这个字符串 

如果使用replace into插入的数据的唯一索引或者主键索引与之前的数据有重复的情况,
将会删除原先的数据,然后再进行添加。
replace into table( col1, col2, col3 ) values ( val1, val2, val3 )
*/
update titles_test set emp_no = replace(emp_no, 10001, 10005)
where id = 5


/*
全字段替换
*/

replace into titles_test values(5, 10005, 'Senior Engineer', '1986-06-26', '9999-01-01')



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

题目描述
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values (‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);


/*
UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]
你可以同时更新一个或多个字段。
你可以在 WHERE 子句中指定任何条件。
你可以在一个单独表中同时更新数据。
*/

update titles_test set to_date = null, from_date = '2001-01-01'
where to_date = '9999-01-01'



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

题目描述
删除emp_no重复的记录,只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values (‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);


delete from titles_test
where id not in(
   select * from(
       select min(id)
       from titles_test
       group by emp_no
   ) as a   
)

/*
要给中间查询派生出的表设置一个别名 a
否则会出现错误:
Every derived table must have its own alias
*/



批量插入数据

题目描述
题目已经先执行了如下语句:
drop table if exists actor;
CREATE TABLE actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update DATETIME NOT NULL)
请你对于表actor批量插入如下数据(不能有2条insert语句哦!)

actor_id first_name last_name last_update
1 PENELOPE GUINESS 2006-02-15 12:34:33
2 NICK WAHLBERG 2006-02-15 12:34:33


insert into actor values(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33')



将employees表的所有员工的last_name和first_name拼接起来作为Name

题目描述
将employees表的所有员工的last_name和first_name拼接起来作为Name,中间以一个空格区分
(注:sqllite,字符串拼接为 || 符号,不支持concat函数,mysql支持concat函数)
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));


/*
 
 concat(str1, str2,...) 
 返回结果为连接参数产生的字符串,如果有任何一个参数为null,则返回值为null。
 
 concat_ws(separator, str1, str2, ...)

       说明:第一个参数指定分隔符。
       需要注意的是分隔符不能为null,如果为null,则返回结果为null。
*/

select concat_ws(' ', last_name, first_name)
from employees



获取所有非manager的员工emp_no

题目描述
获取所有非manager的员工emp_no
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL,
emp_no int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));
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));


/* not in + 子查询
select emp_no 
from employees
where emp_no not in(
    select emp_no
    from dept_manager
)
*/

/*
left join + is null
*/

select e.emp_no
from employees e left join dept_manager d
on e.emp_no = d.emp_no
where dept_no is null



从titles表获取按照title进行分组

题目描述
从titles表获取按照title进行分组,每组个数大于等于2,给出title以及对应的数目t。
CREATE TABLE IF NOT EXISTS “titles” (
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);


select title, count(title) t
from titles
group by title
having t >= 2

/*
执行顺序:Where, Group By, Having, Order by
*/



查找员工编号emp_no为10001其自入职以来的薪水salary涨幅

题目描述
查找员工编号emp_no为10001其自入职以来的薪水salary涨幅(总共涨了多少)growth(可能有多次涨薪,没有降薪)
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 (MAX(salary) - MIN(salary)) growth
from salaries
where emp_no = 10001


  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
create table sailors( sid char(10) primary key, sname char(20), rating int, age int); create table boats( bid char(10) primary key, bname char(20), color char(10)); create table reserves( sid char(10) , bid char(10) , rdate date, primary key(sid,bid,rdate), foreign key (sid) references sailors(sid) on delete cascade, foreign key (bid) references boats(bid) on delete cascade); insert into sailors(sid,sname,rating,age) values("22","dustin",7,45) ("29","brustus",1,33), ("31","lubber",8,56), ("32","andy",8,26), ("58","rusty",10,35), ("64","horatio",7,35), ("71","zorba",10,35), ("74","horatio",9,35), ("85","art",3,26), ("86","john",1,17), ("95","bob",3,64), ("96","frodo",3,26), ("98","tom",3,17); insert into boats(bid,bname,color) values("101","A","red"), ("102","B","green"), ("103","C","blue"), ("104","D","white") ("105","E","red"), ("106","F","blue"), ("107","G","green"); insert into reserves(sid,bid,rdata) values("22","101","2010-01-08"), ("22","102","2010-01-09"), ("29","103","2010-01-09"), ("31","102","2010-02-11"), ("22","104","2010-03-08"), ("22","103","2010-03-10"), ("32","105","2010-03-11"), ("32","106","2010-03-18"), ("32","102","2010-03-19"), ("58","104","2010-03-20"), ("64","105","2010-03-20"), ("95","101","2010-04-02"), ("85","102","2010-04-05"), ("22","101","2010-04-07"), ("22","105","2010-05-01"), ("22","106","2010-06-18"), ("22","107","2010-07-09"), ("31","106","2010-08-06"), ("32","105","2010-08-06"), ("29","104","2010-08-07"), ("64","103","2010-09-05"), ("58","102","2010-09-09"), ("64","104","2010-11-03"), ("64","105","2010-11-04"), ("31","106","2010-12-0

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值