SQL1 —— as、inner join、 on、 where

最新推荐文章于 2024-02-13 03:53:31 发布

在家慢慢长胖

最新推荐文章于 2024-02-13 03:53:31 发布

阅读量726

点赞数

文章标签： mysql

本文链接：https://blog.csdn.net/csdn_yanice/article/details/105187694

版权

题目描述1

查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号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 salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_to
from
dept_manager right join salaries on emp_no
where salaries.to_date='9999-01-01'

纠正：

select salaries.*, dept_manager.dept_no
from salaries inner join dept_manager
on salaries.emp_no = dept_manager.emp_no
where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01'

更好的答案：

select salaries.*,dept_manager.dept_no
from salaries inner join dept_manager
on dept_manager.emp_no = salaries.emp_no
and dept_manager.to_date = '9999-01-01'
and salaries.to_date = '9999-01-01';

总结：

1. 简写 salaries.*

2. 细心：dept_no写成了dept_to; 没有写两次'9999-01-01'

3. 取别名as （在select里取的别名，不能用在where里）

4. 条件写在on或where内的而区别：

条件写在where：where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

select s.*,d.dept_no
from salaries as s inner join dept_manager as d
on s.emp_no=d.emp_no
where s.to_date='9999-01-01' and d.to_date='9999-01-01'

条件写在on： on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

select s.*,d.dept_no
from salaries as s inner join dept_manager as d
on s.emp_no=d.emp_no
and d.to_date = '9999-01-01'
and s.to_date = '9999-01-01';

由于此处是inner join所以结果没有区别，但lef join、right join、full join（MYSQL没有全连接）有差别！

题目描述2

查找薪水涨幅超过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(from_date) as t
from salaries
group by emp_no
having t > 15

总结：

1. HAVING与GROUP BY 配合使用（运行到GROUP BY开始才可以使用SELECT中的别名，它返回的是一个游标，而不是一个虚拟表，所以在where中不可以使用select中的别名，而having却可以使用)

2. sql执行顺序

from —— on —— join —— where —— group by —— avg,sum,count,min,max等统计函数 —— having —— select —— distinct —— order by —— limit

题目描述3

获取所有非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 ; left join & is null ; 集合运算：EXCEPT（差）、UNION（并）、INTERSECT（交）

-- 方法1 not in 
-- 16ms 3560k  not in在实际使用中，因为not in会转化成多表连接，而且不使用索引
select emp_no
from employees
where emp_no not in (
select emp_no
from dept_manager);

-- 方法2 left join & is null
-- 17ms 3312k
select e.emp_no
from employees as e left join dept_manager as d
on e.emp_no = d.emp_no
where d.emp_no is null;


-- 方法3 集合运算：EXCEPT（差）、UNION（并）、INTERSECT（交）
-- 16ms 3552k
select employees.emp_no
from employees
except
select dept_manager.emp_no
from dept_manager;

题目描述

获取所有部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary
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`));

分组与统计函数使用时需要注意的陷阱 —— 多列取值问题 —— 解决：多做一次表联接

-- 方法有缺陷emp_no列的取值与最大的salary不一定对应;
-- emp_no却默认取了分组后的第一条记录的emp_no
select d.dept_no, d.emp_no, max(s.salary) as salary
from dept_emp as d inner join salaries as s
on d.emp_no = s.emp_no
where d.to_date = '9999-01-01'
group by d.dept_no;

-- 做两次连接解决上述缺陷
select d.dept_no, d.emp_no, s.salary
from dept_emp d inner join salaries s
on d.emp_no = s.emp_no
and d.to_date = '9999-01-01' and s.to_date = '9999-01-01'
where s.salary = (
select max(s2.salary)
from dept_emp d2 inner join salaries s2
on d2.emp_no = s2.emp_no
and d2.to_date = '9999-01-01' and s2.to_date = '9999-01-01'
where d2.dept_no = d.dept_no
group by d2.dept_no)
order by d.dept_no;

代码简化

-- 自己
select (s2.salary - s1.salary) as growth
from
(select salary from salaries where emp_no = 10001 order by from_date limit 1) as s1, 
(select salary from salaries where emp_no = 10001 order by to_date desc limit 1) as s2

-- 别人
SELECT ( 
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date DESC LIMIT 1) -
(SELECT salary FROM salaries WHERE emp_no = 10001 ORDER BY to_date ASC LIMIT 1)
) AS growth

排名问题

-- 普通方法，比较s1.salary <= s2.salary
-- 即取出表的每一行记录的salary，全表有多少salary >=这条工资，就是该工资的排名
-- tips: 相同salary并列, 所以要用distinct去重
select s1.emp_no, s1.salary, count(distinct s2.salary) as rank
from salaries as s1, salaries as s2
where s1.to_date = '9999-01-01' and s2.to_date = '9999-01-01' and s1.salary <= s2.salary
group by s1.emp_no
order by rank;

-- SQL 8.0可以使用窗口函数的dense_rank()+over
select emp_no, salaries, dense_rank() over(order by salary desc) as rank
where to_date = '9999-01-01'
order by salary desc, emp_no

题目描述(省题)：查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

！！！电影数目>=5 是这类电影的所有数目，并不是包含了robot的这类电影的数目。

为解决这一问题，需要单独把电影数量>=5部出一张虚拟表，再跟现有的表做联接

film表

字段	说明
film_id	电影id
title	电影名称
description	电影描述信息

CREATE TABLE IF NOT EXISTS film (

film_id smallint(5) NOT NULL DEFAULT '0',

title varchar(255) NOT NULL,

description text,

PRIMARY KEY (film_id));

category表

字段	说明
category_id	电影分类id
name	电影分类名称
last_update	电影分类最后更新时间

CREATE TABLE category (

category_id tinyint(3) NOT NULL ,

name varchar(25) NOT NULL, `last_update` timestamp,

PRIMARY KEY ( category_id ));

film_category表

字段	说明
film_id	电影id
category_id	电影分类id
last_update	电影id和分类id对应关系的最后更新时间

CREATE TABLE film_category (

film_id smallint(5) NOT NULL,

category_id tinyint(3) NOT NULL, `last_update` timestamp);

查找描述信息中包括robot的电影对应的分类名称以及电影数目，而且还需要该分类对应电影数量>=5部

SELECT c.name, COUNT(fc.film_id)
FROM
(select category_id, COUNT(film_id) AS category_num FROM film_category  GROUP BY category_id HAVING count(film_id)>=5
) AS cc,
 film AS f, film_category AS fc, category AS c
WHERE  f.description LIKE '%robot%'
AND f.film_id = fc.film_id
AND c.category_id = fc.category_id
AND c.category_id= cc.category_id

select c.name,count(fcc.film_id)as num
from film_category fc
inner join category c on c.category_id=fc.category_id
inner join (
    select * 
    from film f 
    where f.description like '%robot%') as f 
on f.film_id=fc.film_id
inner join (
    select *,count (fc.film_id)as num 
    from film_category fc 
    group by category_id 
    having num>=5)as fcc
on fc.category_id=fcc.category_id

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

解题：不能写from子查询，要写where子查询

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

select title, description
from film
where film_id in (
    select film_id 
    from film_category 
    where category_id = (
        select category_id 
        from category 
        where name = 'Action'));