mysql面试题
以下语句的作用是：（ C ）
SELECT ename,salary FROM emp WHERE salary <((SELECT min(salary ) FROM emp)+1000)"
A 显示工资低于1000元的雇员信息
B将工资小于1000元的雇员工资增加1000后显示
C 显示不超过最低工资+1000元的雇员信息
D 显示超过最低工资+1000元的雇员信息
以下哪个符号在SQL语句中可以组合两个字符串？（ B ）
A &&
B ||
C %%
D *
简单查询
表STUDENT（学生）：
| 中文 | 编码 | 类型 |
|---|---|---|
| 学号 | STUDENT_ID | BIGINT |
| 姓名 | NAME | VARCHAR(200) |
| 年龄 | AGE | INT |
| 性别 | SEX | INT |
表SCORE（成绩）：
| 中文 | 编码 | 类型 |
|---|---|---|
| 学号 | STUDENT_ID | BIGINT |
| 课程名 | SUBJECT | VARCHAR(200) |
| 成绩 | SCORE | INT |
1). 用Sql找出姓名有重名的学生，查询字段包括：姓名及对应的人数。
Select name , count(*) as count from student group by name having count(*) >1 ;
2). 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程名降序排列，查询字段包括：课程名、平均成绩。
Select subject ,AVG(score) as avg_score from score group by subject order by avg_score asc ,subject desc;
3). 查出所有学生姓名及成绩列表，如果选修了“数学”的，则显示具体成绩，否则为0 。
| 姓名 | 成绩 |
|---|---|
| 张三 | 90 |
| 李四 | 0 |
Select st.name, case when '数学' then sc.score else 0 end from student as st ,score as sc where st. STUDENT_ID=sc. STUDENT_ID;
组合两个表
表1: Person
| 列名 | 类型 |
|---|---|
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
表2: Address
| 列名 | 类型 |
|---|---|
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
编写一个 SQL 查询，满足条件：无论 person 是否有地址信息，都需要基于上述两表提供 person 的以下信息：
FirstName, LastName, City, State
- 使用左连接
- 会从左表中返回所有的值，即使右表中没有匹配（以左表为基准）
SELECT p.FirstName,p.LastName,a.City,a.State FROM Person AS p LEFT JOIN Address AS a ON p.PersonId=a.PersonId
第二高的薪水
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary） 。
| Id | Salary |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。
| SecondHighestSalary |
|---|
| 200 |
答题
- 通过IFNULL函数，如果不存在返回null
- 通过desc降序，从大到小排序
- 通过distinct排除重复数据
- 通过limit 1,1从第二个数据获取，只获取一条数据
- 最后将查询出来的数据字段起别名为SecondHighestSalary
select IFNULL(
(select distinct (Salary) from Employee order by Salary desc limit 1,1) ,NULL)
as SecondHighestSalary
超过经理薪水的员工
Employee 表包含所有员工，他们的经理也属于员工。每个员工都有一个 Id，此外还有一列对应员工的经理的 Id。
| Id | Name | Salary | ManagerId |
|---|---|---|---|
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
给定 Employee 表，编写一个 SQL 查询，该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中，Joe 是唯一一个收入超过他的经理的员工。
| Employee |
|---|
| Joe |
答题
- 通过自连接，把一个表当成两个表来连接查询
- a表相当于员工表，b表相当于经理表
- 判断条件：
a的ManagerId和b的Id相同且a的salary大于b的salary
select a.Name as Employee
from Employee as a,Employee as b
where a.ManagerId = b.id
and a.Salary > b.Salary
查找重复的邮箱
编写一个 SQL 查询，查找 Person 表中所有重复的电子邮箱。
| Id | |
|---|---|
| 1 | a@b.com |
| 2 | c@d.com |
| 3 | a@b.com |
根据以上输入，你的查询应返回以下结果：
| a@b.com |
答题
-
通过Email进行分组
-
查询总数大于1的Email
select Email from Person group by Email having count(Email)>1
查询从不订购的用户
某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。
Customers 表：
| Id | Name |
|---|---|
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
Orders 表：
| Id | CustomerId |
|---|---|
| 1 | 3 |
| 2 | 1 |
例如给定上述表格，你的查询应返回：
| Customers |
|---|
| Henry |
| Max |
答题
- 使用左连接，查询所有的Customers
select * from Customers left join Orders on Customers.id = Orders.CustomerId
| Id | Name | Id | CustomerId |
|---|---|---|---|
| 1 | Joe | 2 | 1 |
| 2 | Henry | null | null |
| 3 | Sam | 1 | 3 |
| 4 | Max | null | null |
- 再找到Orders.id为空的字段
select Customers.Name as Customers
from Customers
left join Orders
on Customers.id = Orders.CustomerId
where Orders.id is null
查找第二天温度比第一天温度高的日期
表结构
| id | recordDate | Temperature |
|---|---|---|
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
答题
DATEDIFF函数，返回第一个数减去第二个数的差值- Mysql 和 SQL Serve之间 datediff函数有微小却严重的区别 Mysql的是
datediff(被减数, 减数)SQL Serve的datediff(时间单位, 减数, 被减数)这两个不仅MySQL没有时间单位，而且做差的减数被减数位置相反.
select a.id
from Weather as a, Weather as b
where a.Temperature > b.Temperature
and datediff(a.recordDate,b.recordDate)=1
查找大的国家
World 表
| name | continent | area | population | gdp |
|---|---|---|---|---|
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
如果一个国家的面积超过 300 万平方公里，或者人口超过 2500 万，那么这个国家就是大国家。
编写一个 SQL 查询，输出表中所有大国家的名称、人口和面积。
答题
- 使用 or 会使索引失效，在数据量较大的时候查找效率较低，通常建议使用 union 代替 or。
- 对于单列来说，用or是没有任何问题的，但是or涉及到多个列的时候，每次select只能选取一个index，如果选择了area，population就需要进行table-scan，即全部扫描一遍，但是使用union就可以解决这个问题，分别使用area和population上面的index进行查询。 但是这里还会有一个问题就是，UNION会对结果进行排序去重，可能会降低一些性能(这有可能是方法一比方法二快的原因），所以最佳的选择应该是两种方法都进行尝试比较。
select name,population,area from world where area>3000000 or population>25000000
select name,population,area from world where area>3000000
union
select name,population,area from world where population>25000000
查找电影
找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列。
例如，下表 cinema:
| id | movie | description | rating |
|---|---|---|---|
| 1 | War | great 3D | 8.9 |
| 2 | Science | fiction | 8.5 |
| 3 | irish | boring | 6.2 |
| 4 | Ice song | Fantacy | 8.6 |
| 5 | House card | Interesting | 9.1 |
对于上面的例子，则正确的输出是为：
| id | movie | description | rating |
|---|---|---|---|
| 5 | House card | Interesting | 9.1 |
| 1 | War | great 3D | 8.9 |
答题
- mod（x,y）函数，求余函数。返回x除以y之后的余数
- < >，
select * from cinema
where description <> 'boring'
and mod(id,2)=1
order by rating desc
变更性别
给定一个 salary 表，如下所示，有 m = 男性 和 f = 女性 的值。交换所有的 f 和 m 值（例如，将所有 f 值更改为 m，反之亦然）。要求只使用一个更新（Update）语句，并且没有中间的临时表。
注意，您必只能写一个 Update 语句，请不要编写任何 Select 语句。
例如：
| id | name | sex | salary |
|---|---|---|---|
| 1 | A | m | 2500 |
| 2 | B | f | 1500 |
| 3 | C | m | 5500 |
| 4 | D | f | 500 |
运行你所编写的更新语句之后，将会得到以下表:
| id | name | sex | salary |
|---|---|---|---|
| 1 | A | f | 2500 |
| 2 | B | m | 1500 |
| 3 | C | f | 5500 |
| 4 | D | m | 500 |
答题
- 通过case when改变sex的值
update salary set sex=(case when sex='m' then 'f' else 'm' end);
删除重复的电子邮箱
编写一个 SQL 查询，来删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个。
| Id | |
|---|---|
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
Id 是这个表的主键。
例如，在运行你的查询语句之后，上面的 Person 表应返回以下几行:
| Id | |
|---|---|
| 1 | john@example.com |
| 2 | bob@example.com |
提示：
执行 SQL 之后，输出是整个 Person 表。
使用 delete 语句。
答题
- 使用自连接，通过email连接，删除id大于email相同的id的列
delete p1 from person p1,person p2
where p1.email=p2.email and p1.id>p2.id
调换座位
小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。其中纵列的 id 是连续递增的，小美想改变相邻俩学生的座位。你能不能帮她写一个 SQL query 来输出小美想要的结果呢？
| id | student |
|---|---|
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
假如数据输入的是上表，则输出结果如下：
| id | student |
|---|---|
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames注意： |
如果学生人数是奇数，则不需要改变最后一个同学的座位。
答题
- 先判断最后一位id时候是奇数
id=(select count(*) from seat) and mod(id,2)=1最后一位且是奇数，id不变 - id是奇数，id+1
- id是偶数，id-1
- 最后按照id进行升序排序
select (case
when id=(select count(*) from seat) and mod(id,2)=1 then id
when mod(id,2)=1 then id+1
else id-1
end)as id,student
from seat
order by id asc
分数排名
编写一个 SQL 查询来实现分数排名。
如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。
| Id | Score |
|---|---|
| 1 | 3.50 |
| 2 | 3.65 |
| 3 | 4.00 |
| 4 | 3.85 |
| 5 | 4.00 |
| 6 | 3.65 |
例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：
| Score | Rank |
|---|---|
| 4.00 | 1 |
| 4.00 | 1 |
| 3.85 | 2 |
| 3.65 | 3 |
| 3.65 | 3 |
| 3.50 | 4 |
重要提示：对于 MySQL 解决方案，如果要转义用作列名的保留字，可以在关键字之前和之后使用撇号。例如 Rank
答题
- 新版mysql有了rank函数
- rank函数会把要求排序的值相同的归为一组且每组序号一样，排序不会连续执行
- dense_rank排序是连续的，也会把相同的值分为一组且每组排序号一样
- rank必须加`
select score, dense_rank() OVER(order by score desc) as `rank` from scores
内连接和外连接区别
- 内连接查询结果只包含符合条件的值。
- 外连接又分为左外连接，右外连接和全连接。左连接不仅包含符合条件的值，还包括左表中不匹配的值。
- 全连接就是在满足条件的值基础上，将左右两张表其余的值也都查询出来。
浙公网安备 33010602011771号