MYSQL(2)

最新推荐文章于 2021-10-09 16:46:50 发布

weixin_43947653

最新推荐文章于 2021-10-09 16:46:50 发布

阅读量261

点赞数

分类专栏：牛客SQL篇文章标签： mysql 数据库

本文链接：https://blog.csdn.net/weixin_43947653/article/details/113357934

版权

牛客SQL篇专栏收录该内容

3 篇文章 0 订阅

订阅专栏

MYSQL(2)

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

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

。。。

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

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:

在这里插入图片描述

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

select a.id, a.job, a.score
from grade a left join 
(select b.id, b.job, avg(b.score) p
  from grade b
  group by b.job
) as c
on a.job = c.job
where a.score > c.p
order by a.id

/*
不能直接用group by做，
直接按照job分组后，其他job相同元素会据合在一起，会出现一个表格内有多个id，score
*/

查找当前薪水详情以及部门编号

题目描述
查找各个部门当前(dept_manager.to_date=‘9999-01-01’)领导当前(salaries.to_date=‘9999-01-01’)薪水详情以及其对应部门编号dept_no
(注:输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列)
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));
CREATE TABLE dept_manager (
dept_no char(4) NOT NULL, – ‘部门编号’
emp_no int(11) NOT NULL, – ‘员工编号’
to_date date NOT NULL,
PRIMARY KEY (emp_no,dept_no));

select s.*, d.dept_no
from dept_manager d , salaries s
where d.to_date = '9999-01-01' and s.to_date = '9999-01-01' and s.emp_no = d.emp_no
order by s.emp_no

查找所有员工的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 employees e left join dept_emp d
on e.emp_no = d.emp_no

查找所有员工入职时候的薪水情况

题目描述
查找所有员工入职时候的薪水情况，给出emp_no以及salary，并按照emp_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));
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 e.emp_no, salary
from employees e, salaries s
where e.emp_no = s.emp_no and e.hire_date = s.from_date
order by e.emp_no desc

获取所有部门当前manager的当前薪水情况，给出dept_no, emp_no以及salary

题目描述
获取所有部门当前(dept_manager.to_date=‘9999-01-01’)manager的当前(salaries.to_date=‘9999-01-01’)薪水情况，给出dept_no, emp_no以及salary，输出结果按照dept_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 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 dept_no, d.emp_no, salary
from dept_manager d , salaries s
where d.emp_no = s.emp_no and d.to_date = '9999-01-01' and s.to_date = '9999-01-01'
order by dept_no

获取所有员工当前的manager

题目描述
获取所有员工当前的(dept_manager.to_date=‘9999-01-01’)manager，如果员工是manager的话不显示(也就是如果当前的manager是自己的话结果不显示)。输出结果第一列给出当前员工的emp_no,第二列给出其manager对应的emp_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 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));

select de.emp_no, dm.emp_no manager_no
from dept_emp de, dept_manager dm
where de.dept_no = dm.dept_no and de.emp_no <> dm.emp_no and dm.to_date = '9999-01-01'

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

题目描述
从titles表获取按照title进行分组，每组个数大于等于2，给出title以及对应的数目t。
注意对于重复的emp_no进行忽略(即emp_no重复的title不计算，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);
如插入：
INSERT INTO titles VALUES(10001,‘Senior Engineer’,‘1986-06-26’,‘9999-01-01’);
INSERT INTO titles VALUES(10002,‘Staff’,‘1996-08-03’,‘9999-01-01’);
INSERT INTO titles VALUES(10003,‘Senior Engineer’,‘1995-12-03’,‘9999-01-01’);
INSERT INTO titles VALUES(10004,‘Engineer’,‘1986-12-01’,‘1995-12-01’);
INSERT INTO titles VALUES(10004,‘Senior Engineer’,‘1995-12-01’,‘9999-01-01’);
INSERT INTO titles VALUES(10005,‘Senior Staff’,‘1996-09-12’,‘9999-01-01’);
INSERT INTO titles VALUES(10005,‘Staff’,‘1989-09-12’,‘1996-09-12’);
INSERT INTO titles VALUES(10006,‘Senior Engineer’,‘1990-08-05’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Senior Staff’,‘1996-02-11’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Staff’,‘1989-02-10’,‘1996-02-11’);
INSERT INTO titles VALUES(10008,‘Assistant Engineer’,‘1998-03-11’,‘2000-07-31’);
INSERT INTO titles VALUES(10009,‘Assistant Engineer’,‘1985-02-18’,‘1990-02-18’);
INSERT INTO titles VALUES(10009,‘Engineer’,‘1990-02-18’,‘1995-02-18’);
INSERT INTO titles VALUES(10009,‘Senior Engineer’,‘1995-02-18’,‘9999-01-01’);
INSERT INTO titles VALUES(10010,‘Engineer’,‘1996-11-24’,‘9999-01-01’);
INSERT INTO titles VALUES(10010,‘Engineer’,‘1996-11-24’,‘9999-01-01’);

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

统计出当前各个title类型对应的员工当前薪水对应的平均工资。

题目描述
统计出当前(titles.to_date=‘9999-01-01’)各个title类型对应的员工当前(salaries.to_date=‘9999-01-01’)薪水对应的平均工资。结果给出title以及平均工资avg。
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));
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);

如插入：
INSERT INTO salaries VALUES(10001,88958,‘1986-06-26’,‘9999-01-01’);
INSERT INTO salaries VALUES(10003,43311,‘2001-12-01’,‘9999-01-01’);
INSERT INTO salaries VALUES(10004,70698,‘1986-12-01’,‘1995-12-01’);
INSERT INTO salaries VALUES(10004,74057,‘1995-12-01’,‘9999-01-01’);
INSERT INTO salaries VALUES(10006,43311,‘2001-08-02’,‘9999-01-01’);
INSERT INTO salaries VALUES(10007,88070,‘2002-02-07’,‘9999-01-01’);

INSERT INTO titles VALUES(10001,‘Senior Engineer’,‘1986-06-26’,‘9999-01-01’);
INSERT INTO titles VALUES(10003,‘Senior Engineer’,‘2001-12-01’,‘9999-01-01’);
INSERT INTO titles VALUES(10004,‘Engineer’,‘1986-12-01’,‘1995-12-01’);
INSERT INTO titles VALUES(10004,‘Senior Engineer’,‘1995-12-01’,‘9999-01-01’);
INSERT INTO titles VALUES(10006,‘Senior Engineer’,‘2001-08-02’,‘9999-01-01’);
INSERT INTO titles VALUES(10007,‘Senior Staff’,‘1996-02-11’,‘9999-01-01’);

/*
AVG(*)是自动命名为avg的，所以不用重命名

“Where” 是一个约束声明，使用Where来约束来之数据库的数据，
Where是在结果返回之前起作用的，且Where中不能使用聚合函数。

“Having”是一个过滤声明，是在查询返回结果集以后对查询结果进行的过滤操作，
在Having中可以使用聚合函数。
*/
select title, avg(salary)
from titles t, salaries s
where t.emp_no = s.emp_no and t.to_date = '9999-01-01' and s.to_date = '9999-01-01'
group by title

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

题目描述
获取当前（to_date=‘9999-01-01’）薪水第二多的员工的emp_no以及其对应的薪水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 emp_no, salary
from salaries
where to_date = '9999-01-01' and salary = 
             (select distinct salary from salaries order by salary desc limit 1,1)

查找所有员工的last_name和first_name以及对应的dept_name

题目描述
查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (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 last_name, first_name, dept_name
from employees e left join dept_emp de on e.emp_no = de.emp_no
left join departments d on de.dept_no = d.dept_no

统计各个部门的工资记录数

题目描述
统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序
CREATE TABLE departments (
dept_no char(4) NOT NULL,
dept_name varchar(40) NOT NULL,
PRIMARY KEY (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 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 d.dept_no, d.dept_name, count(s.salary) `sum`
from salaries s, dept_emp de, departments d 
where de.dept_no = d.dept_no and s.emp_no = de.emp_no
group by d.dept_no 
order by d.dept_no

使用join查询方式找出没有分类的电影id以及名称

select f.film_id `电影id`, f.title `名称`
from film f left join film_category fc on f.film_id = fc.film_id
where fc.category_id is null

你能使用子查询的方式找出属于Action分类的所有电影对应的title,description吗

三表连接

select title, description
from film f, category c, film_category fc
where f.film_id = fc.film_id and fc.category_id = c.category_id
and c.name = 'Action'

子查询

select title, description
from film f
where f.film_id in (select fc.film_id from film_category fc
                   where fc.category_id in (select c.category_id from category c
                                           where c.name = 'Action'))

创建一个actor表，包含如下列信息

在这里插入图片描述

create table if not exists `actor`(
    actor_id smallint(5) primary key not null comment '主键id',
    first_name varchar(45) not null comment '名字',
    last_name varchar(45) not null comment '姓氏',
    last_update date not null comment '日期'
)ENGINE = InnoDB DEFAULT CHARSET = UTF8;

题目已经先执行了如下语句:

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);
insert into actor values ('3', 'WD', 'GUINESS', '2006-02-15 12:34:33');

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
actor_id first_name last_name last_update
‘3’ ‘ED’ ‘CHASE’ ‘2006-02-15 12:34:33’

insert ignore into actor values(3, 'ED', 'CHASE', '2006-02-15 12:34；33')

/*
insert ignore
当出现主键或唯一索引重复之后，插入会失败，但不会报错，会忽略此次插入

replace into
当出现主键或唯一索引重复之后，会删除原先的数据，并将这个新的记录插入进去
*/

创建一个actor_name表

对于如下表actor，其对应的数据为:
在这里插入图片描述

请你创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.
actor_name表结构如下：
在这里插入图片描述

create table if not exists `actor_name` 
select first_name, last_name from actor

对first_name创建唯一索引uniq_idx_firstname

题目描述
针对如下表actor结构创建索引：
(注:在 SQLite 中,除了重命名表和在已有的表中添加列,ALTER TABLE 命令不支持其他操作，
mysql支持ALTER TABLE创建索引)

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);

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

alter table actor add unique uniq_idx_firstname(first_name);
alter table actor add index idx_lastname(last_name);

/*
要加分号！！！
*/

针对actor表创建视图actor_name_view

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v：

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);

create view actor_name_view(first_name_v, last_name_v) 
as 
select first_name, last_name from actor

针对salaries表emp_no字段创建索引idx_emp_no

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005, 使用强制索引。
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));
create index idx_emp_no on salaries(emp_no);

select * from salaries force index(idx_emp_no) 
where emp_no = 10005

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

存在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);
现在在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’

alter table actor 
add column create_date datetime not null default'2020-10-01 00:00:00'

构造一个触发器audit_log

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中。
CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
CREATE TABLE audit(
EMP_no INT NOT NULL,
NAME TEXT NOT NULL
);

/*
触发器是一种特殊类型的存储过程，它不同于存储过程，
主要是通过事件触发而被执行的，即不是主动调用而执行的；而存储过程则需要主动调用其名字执行


create trigger triggerName  
after/before insert/update/delete on 表名  
for each row   #这句话在mysql是固定的  
begin  
    sql语句;  
end;  


触发器不能修改，只能删除

语法：drop trigger + 触发器名字


触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中

语法：old/new.字段名

需要注意的是，old 和 new 不是所有触发器都有,insert, update, delete触发器有

*/

create trigger audit_log
after insert on employees_test for each row
begin
   insert into audit values(new.ID, new.NAME);
end;

在audit表上创建外键约束

在audit表上创建外键约束，其emp_no对应employees_test表的主键id。
(以下2个表已经创建了)

CREATE TABLE employees_test(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

CREATE TABLE audit(
EMP_no INT NOT NULL,
create_date datetime NOT NULL
);

alter table audit add foreign key (EMP_no)
references employees_test(id)

将所有获取奖金的员工当前的薪水增加10%

请你写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)
create table emp_bonus(
emp_no int not null,
btype smallint not null);
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));
如：
INSERT INTO emp_bonus VALUES (10001,1);
INSERT INTO salaries VALUES(10001,85097,‘2001-06-22’,‘2002-06-22’);
INSERT INTO salaries VALUES(10001,88958,‘2002-06-22’,‘9999-01-01’);

update salaries
   set salary = salary * 1.1
where emp_no in (
   select emp_no from emp_bonus
)and salaries.to_date = '9999-01-01'

将employees表中的所有员工的last_name和first_name通过(’)连接起来

将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持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));

/*
将查询结果拼接成一个字符串，返回结果为连接参数产生的字符串。
如有任何一个参数为NULL ，则返回值为 NULL。

单引号+转义字符， 或 双引号+符号
*/

select concat(last_name ,'\'', first_name) as name 
from employees

查找字符串’10,A,B’ 中逗号’,'出现的次数cnt。

/*
注：
length:   是计算字段的长度一个汉字是算三个字符,一个数字或字母算一个字符;
char_length:不管汉字还是数字或者是字母都算是一个字符;
*/

select (length("10,A,B") - length(replace("10,A,B", ",", ""))) as cnt

/*
语法：replace(object,search,replace)
语义：把object对象中出现的的search全部替换成replace。

replace具备替换拥有唯一索引或者主键索引重复数据的能力，
也就是如果使用replace into插入的数据的唯一索引或者主键索引与之前的数据有重复的情况，
将会删除原先的数据，然后再进行添加。
语法：replace into table( col1, col2, col3 ) values ( val1, val2, val3 )
语义：向table表中col1, col2, col3列replace数据val1，val2，val3
*/

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列
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));

/*
一.从左开始截取字符串

用法：left(str, length)，即：left(被截取字符串， 截取长度)

二.从右开始截取字符串

用法：right(str, length)，即：right(被截取字符串， 截取长度)

三.截取特定长度的字符串

substring(str, pos)，即：substring(被截取字符串， 从第几位开始截取)
substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)

 pos 开始截取的位置(正数表示从左向右数第几个，负数相反是从右向左，位置从1开始数)
*/

select first_name
from employees
order by right(first_name, 2)

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees
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));


select dept_no, group_concat(emp_no separator ',') employees
from dept_emp
group by dept_no



/*
group_concat()

1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc  ] [separator '分隔符'] )

concat()函数

1、功能：将多个字符串连接成一个字符串。
2、语法：concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null

concat_ws()函数

1、功能：和concat()一样，将多个字符串连接成一个字符串，
但是可以一次性指定分隔符～（concat_ws就是concat with separator）
2、语法：concat_ws(separator, str1, str2, ...)
说明：第一个参数指定分隔符。需要注意的是分隔符不能为null，如果为null，则返回结果为null。

*/

查找排除最大、最小salary之后的当前员工的平均工资avg_salary

查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_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));
如：
INSERT INTO salaries VALUES(10001,85097,‘2001-06-22’,‘2002-06-22’);
INSERT INTO salaries VALUES(10001,88958,‘2002-06-22’,‘9999-01-01’);
INSERT INTO salaries VALUES(10002,72527,‘2001-08-02’,‘9999-01-01’);
INSERT INTO salaries VALUES(10003,43699,‘2000-12-01’,‘2001-12-01’);
INSERT INTO salaries VALUES(10003,43311,‘2001-12-01’,‘9999-01-01’);
INSERT INTO salaries VALUES(10004,70698,‘2000-11-27’,‘2001-11-27’);
INSERT INTO salaries VALUES(10004,74057,‘2001-11-27’,‘9999-01-01’);

select avg(salary) avg_salary
from salaries
where to_date = '9999-01-01'
and salary < (select max(salary) from salaries where to_date = '9999-01-01')
and salary > (select min(salary) from salaries where to_date = '9999-01-01')


/*
where不能使用聚合函数
*/

分页查询employees表，每5行一页，返回第2页的数据

分页查询employees表，每5行一页，返回第2页的数据
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
limit 5, 5

/*
LIMIT 后的数字代表返回几条记录，OFFSET 后的数字代表从第几条记录开始返回
*/
/*
select * from employees
limit 5 offset 5
*/

使用含有关键字exists查找未分配具体部门的员工的所有信息

使用含有关键字exists查找未分配具体部门的员工的所有信息。
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));
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));

select * from employees
where not exists (
    select emp_no 
    from dept_emp 
    where employees.emp_no = dept_emp.emp_no)


/*
EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False
*/

/*
in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次

exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，
判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次
*/

刷题通过题目排名

在牛客刷题有一个通过题目个数的(passing_number)表，id是主键，简化如下:
在这里插入图片描述

第1行表示id为1的用户通过了4个题目;

…

第6行表示id为6的用户通过了4个题目;

请你根据上表，输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列，数据如下:
在这里插入图片描述

id为5的用户通过了5个排名第1，

id为1和id为6的都通过了2个，并列第2

窗口函数

select id, number, dense_rank() over (order by number desc) 'rank'
from passing_number
order by 'rank', id


/*
窗口函数

序号函数
ROW_NUMBER()：顺序排序——1、2、3
RANK()：并列排序，跳过重复序号——1、1、3
DENSE_RANK()：并列排序，不跳过重复序号——1、1、2
*/

/*
select a.id, a.number,
(select count(distinct b.number) from passing_number b where b.number >= a.number)
from passing_number a
order by a.number desc, a.id asc

*/

weixin_43947653

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL(2)

MYSQL(2)题目描述牛客每次考试完，都会有一个成绩表(grade)，如下:第1行表示用户id为1的用户选择了C++岗位并且考了11001分。。。第8行表示用户id为8的用户选择了前端岗位并且考了9999分请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)select a.id,
复制链接

扫一扫