msyql练习题（不定期会补充几道题）

谁借莪１个温暖的怀抱￠ 2022-11-06 00:03 69阅读 0赞

一、将当前日期显示成：xxxx年xx月xx日

select (now(), '%Y年%m月%d日') current_t

![20210307091757720.png][]

二、

***已知学员信息表 - stuinfo**（stuId、stuName、gender、majorId）*

***已知专业表 - major**（id、majorName）*

***已知成绩表 - result**（id成绩编号、majorId、stuId、score）*

**1、查询所有男生的姓名、专业名和成绩，使用SQL99**

select s.stuName, m.majorName, r.score from stuinfo s 
    join major m on s.majorId = m.id 
    join result r on s.stuId = r.stuId 
    where s.gender = '男'

** 2、查询每个性别的每个专业的平均成绩和学生名，并按成绩降序**

select s.stuName, avg(r.score) avg_score
    from sutinfo s
    join result r on s.stuId = r.stuId -- 连接2个表
    group by s.gender,s.majorId -- 可以是多个分组，以逗号隔开
    order by avg_score desc; -- 这里按成绩降序一定是按照平均成绩，要注意这一点

三、

**1、查找tb表中重复的 num及个数**

示例：

![20210308155034507.png][]

分析：可以先按num进行分组，之后通过having语句筛选出每个组num的个数大于1的数据

select num, count(num) count_num from tb group by num having count(num) > 1;

![20210308155421257.png][]

**2、有一张world表，编写一个SQL查询，输出表中所有大国家的名称、人口和地区。**

（*如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。*）

![20210308160613946.png][]

select name,population,continent from world where area > 3000000 or population > 25000000;

**3、作为该电影院的信息部主管，您需要编写一个 SQL查询，找出所有影片描述为非 `boring` (不无聊) 的并且 id 为奇数 的影片，结果请按等级 `rating` 排列**。

如下表：cinema

![20210309093717327.png][]

在mysql中，不等于符号为 ***<>***或  ***!=***；判断一个数是否为奇数：***n%2=1***，n为奇数；***n%2=0***，n为偶数。

select c.movie from cinema c where (description <> 'boring' and c.id % 2 = 1) order by c.rating desc;

**4、给定一个salary表，如下所示，有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如，将所有 f 值更改为 m，反之亦然)。要求使用一个更新查询，并且没有中间临时表。**

如下表：

![20210309095403327.png][]   正确结果为：![20210309095422830.png][]

select * from salary where sex = if(sex = 'm', 'f', 'm');

**5、组合两个表**

表1 Person ：![20210309102050240.png][]**             **表2 Address：![20210309102109683.png][]

编写一个sql查询，满足：无论person是否有地址，都需要给予上述2个表提供 person 的一下信息：

*FirstName,  LastName,  City,  State *

select p.FirstName, p.LastName, ad.City, ad.State from Person p left join Address ad on p.PersonId = ad.PersonId;

**6、查询两个表Customers 表 和Orders表，编写一个sql找出所有不订购任何东西的客户。**

Customers表：![20210309104442524.png][]     Orders 表：  ![20210309104500269.png][]

返回结果应该为：![20210309104522519.png][]

**分析：**这道题使用 left join 比子查询效率可能更高一些：先把Customers表中所有数据都查出来，然后使用 where 来过滤*（不订购的客户一定在Orders表中没有，所以，CustomerId 为null的 , 是不订购的客户） *

select * from Customers c left join Orders o on c.Id = o.CustomerId where o.CustomerId is null;
    -- 写法二：使用子查询
    select * from Customers c where c.Id not in (select o.CustomerId from Orders)

**7、查询Employee表中第二高的薪水（Salary）**

![20210309154626501.png][]**  **返回结果听改为：![20210309154712252.png][]

**分析：**这里相当于取了个巧，查询出去重之后的薪资，按Salary降序，然后使用limit来获取第二条数据。

select distinct SecondHieghtSalary from Employee order by Salary desc limit 1, 1;

**8、有一个courses表，查询超过5名学生的课程及学生**

![2021030916383469.png][]   输出结果应该为：![20210309163851888.png][]

**分析：**我们可以把课程分组，然后筛选出课程数量大于5的课程

select class, group_concat(student) stu from courses group by class having count(class) >= 5;

**9、有2个表**

**emp员工表**

**字段解释：**empno : 员工编号、ename：员工名称、job：职位、mgr：上级领导编号、sal: 工资、comm：奖金、deptno：部门编号

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70][]

**(1)  查询每个部门最高薪水的员工**

嵌套子查询，在开发中多数用在from 后边，也就是先查出一个表，当做临时表，与原表进行连接查询。。。

**分析：**这个题非常典型，首先考虑部门最高工资有可能会有多个，所以不可能简单的单纯求部门工资的最大值，以下是我的思路：

先以部门分组，查询出每个部门的最高工资，以查询出的这个表为临时表 t，和emp表链接查询，连接条件是：**t.deptno = emp.deptno,  **where条件是 **t.最高工资 = emp.工资**，这样可查询出多个最高工资的数据。

select emp.deptno, emp.ename, emp.sal from  
    (select deptno, max(sal) max_salary from emp group by deptno) t
    join emp on t.deptno = emp.deptno 
    where t.max_salary = emp.sal
    order by emp.deptno desc;

![20210322162728452.png][]

**（2)  哪些人的薪水在 部门平均薪水之上**

**分析：**此题跟第一道题类似，也是需要先查询出每个部门的平均薪水，然后以这个表为临时表 t ，连接原始表 emp，where筛选条件为：**t.deptno = emp.deptno  **且  ** emp.sal > t.avg\_salary**

select emp.deptno, emp.ename, emp.sal from  
    (select e.deptno, avg(e.sal) avg_salary from emp e group by e.deptno) t
    join emp on emp.deptno = t.deptno and emp.sal > t.avg_salary 
    order by deptno asc;

![20210322160744774.png][]

**（3） 查询出部门所有人的平均工资等级**

salgrade薪资等级表：

**字段：**grade：薪资等级、lasal：最低薪资、hisal：最高工资

![20210322143945914.png][]

分析：先查找出部门所有人的平均工资，把这个表当做临时表 t，连接薪资等级表 salgrade，on条件为：**临时表中的平均工资 在salgrade.losal和hisal之间**

select t.deptno, t.avg_sal, s.grade from 
    (select emp.deptno, avg(emp.sal) avg_sal from emp group by deptno) t
    join salgrade s on t.avg_sal between s.losal and hisal;

![20210322154651169.png][]

**（4）不准用max组函数， 查询出最高薪水**

**分析：**

**方法一** \- 很简单，倒序，然后limit 第一条即可

select sal from emp order by sal desc limit 1;

**方法二** \- 有些绕，理解了也是很简单，可以把emp 表看做2张表 a表 和 b表，连接查询的条件为 a.sal < b.sal，这种连接条件可以把a表中的最高工资排除在外，我们以这个表为临时表 ， 查询原始表 emp， where筛选条件为：emp.sal 工资不在临时表t中的，那么就是最大工资。

select sal from emp 
    where sal not in  
    (select distinct a.sal from emp a join emp b on a.sal < b.sal)

![20210322154034816.png][]

**（5）查询出平均薪资最高的部门的部门编号**

**分析：**这个题很简单，先以部门分组，查找出每个部门的平均工资，然后以查找出的这个表为临时表 t，查找使用max 查询出部门的最高平均工资( 其实这个题也有可能平均工资最高的部门有可能会有多个 )：

select t.deptno, max(t.avg_sal) max_avg_sal from 
    (select deptno, avg(sal) avg_sal from emp group by deptno) t;

![20210322153627632.png][]

*2021年03月23日*

下边是emp员工表：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70][]

**（6）查找出平均薪水最高的部门的部门ID（这题跟上边重复了）**

**分析：**做这种题需要拆分步骤，还要考虑一点的是薪水最高的有可能不止一个，所以你不可能单纯的查找出每个部门的平均工资，直接使用 max( ) 组函数求最大值，然后就结束了，还要根据这个最大值来筛选出 **平均工资 = 这个最大值的。**

-- 从最底部向上查找
    -- 最后在根据部门的平均工资 筛选出 = 最高工资的 部门编号 
    select deptno from emp group by deptno having avg(sal) = 
    (
    select max(t.avg_sal) max_sal from -- 在根据平均工资这个临时表t，来查询出最高的平均工资
    (select avg(sal) avg_sal from emp group by deptno) t -- 先求出每个部门的薪资的平均数
    )

**（7）查找出平均薪水最高的部门的部门名称**

部门表：

![20210323143007334.png][]

**分析：**这道题跟上边第6道题没什么区别，只不过是求部门名称，只需要连接查询部门表即可。

select dept.dname from emp join dept on emp.deptno = dept.deptno 
    group by dept.deptno having avg(sal) = 
    (
    select max(t.avg_sal) max_sal from 
    (select avg(sal) avg_sal from emp group by deptno) t
    )

**（8）求平均薪水的等级最低的部门的部门名称**

薪水等级表：

![2021032315130175.png][]

**分析：**这道题需要根据3张表（薪水等级表、部门表、员工表）查询，有点恶心，但是你如果把步骤拆分开的话，不难，跟前几道题类似，先以部门分组，查询出部门平均工资，然后查找出平均薪水最低的部门，注意前边这几个步骤跟几道题一样，此时会查找出部门的所有的最低工资，然后接下来我们查询最低工资的等级，以这个查询出的最低工资集合为临时表，链接查询 等级表，连接条件为 ***最低工资   between  losal  and  hisal  ,*** 此时会把最低平均工资的等级查询出来，最后链接部门表，条件为：最低平均工资的部门id = 部门表的id，最后可查询出**部门名称。**

select * from
    (select deptno, avg(sal) min_avg_sal from emp group by emp.deptno having avg(emp.sal) = 
        (select min(t.avg_sal) from
            (select avg(sal) avg_sal from emp group by deptno) t
        )
    ) o 
    join salgrade s on o.min_avg_sal BETWEEN s.losal AND s.hisal
    join dept on o.deptno = dept.deptno;

![20210323153754331.png][]

[20210307091757720.png]: /images/20221023/57907608f2074841a13dec55d8921801.png
[20210308155034507.png]: /images/20221023/8eedc168c4a4417997b98683de394dd3.png
[20210308155421257.png]: /images/20221023/3632d7b7f92441d093077e8447985e7b.png
[20210308160613946.png]: /images/20221023/9a3524d51f9a4a4da2b02979f9b3d809.png
[20210309093717327.png]: /images/20221023/4d9eadcae8bd47848adf1ad3be927bec.png
[20210309095403327.png]: https://img-blog.csdnimg.cn/20210309095403327.png
[20210309095422830.png]: /images/20221023/0ac4ca8c865542a6b67c7a79f3141a67.png
[20210309102050240.png]: https://img-blog.csdnimg.cn/20210309102050240.png
[20210309102109683.png]: /images/20221023/b91c78683da94b20a470b0f64033d0f8.png
[20210309104442524.png]: https://img-blog.csdnimg.cn/20210309104442524.png
[20210309104500269.png]: /images/20221023/93cc76e30bfd42e886048415ff688731.png
[20210309104522519.png]: /images/20221023/3c6b81874b474f18be5846dff24cd73c.png
[20210309154626501.png]: https://img-blog.csdnimg.cn/20210309154626501.png
[20210309154712252.png]: /images/20221023/3b5d343bf7324de6b18481cc32c64cfc.png
[2021030916383469.png]: https://img-blog.csdnimg.cn/2021030916383469.png
[20210309163851888.png]: /images/20221023/15f2ca6f995e4954a7cd83ec2ee07bde.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQyNzc4MDAx_size_16_color_FFFFFF_t_70]: /images/20221023/7c80d650491f46938a255d429c860151.png
[20210322162728452.png]: /images/20221023/76dcb01f280c4fd2930c4539e8ca50ac.png
[20210322160744774.png]: /images/20221023/b14a847950f545ca9eaa4723f269ae38.png
[20210322143945914.png]: /images/20221023/571475679cb74ed0bbe07dfa15d7c905.png
[20210322154651169.png]: /images/20221023/04f96c8e9db447ceab754953f85f960d.png
[20210322154034816.png]: /images/20221023/a027362d3fe7401aaa71a8883b51e9d1.png
[20210322153627632.png]: /images/20221023/f0d7b51f83124fb38b14bfe432b51ccf.png
[20210323143007334.png]: /images/20221023/6153a7fb7f414226b78f923da5befa2d.png
[2021032315130175.png]: /images/20221023/4c6c68e3d269431087b9e11ad2f7611f.png
[20210323153754331.png]: /images/20221023/bbea205cf60a4454a5ad641fa083a922.png