vlambda博客
学习文章列表

第六弹：MySQL常见问题处理

力扣--mysql

# 1、查找重复的电子邮箱 （group by having）
select Email
from Person
group by Email
having count(Email) > 1;

# 2、大的国家
SELECT
    name, population, area
FROM
    world
WHERE
    area > 3000000 OR population > 25000000
;

## 3、删除重复的邮箱（DELETE + 自连接）

DELETE P1
FROM Person P1, Person P2
WHERE P1.Email = P2.Email   -- 利用where进行自连接
AND P1.Id > P2.Id   -- 选择Id较大的行



# 4、交换数值
update 班级表
set 班级 = (case 班级
                when 1 then 2
                when 2 then 1
                else 3
           end);


# 4.1、在name列 增加一列
ALTER TABLE `insert_sql`
    ADD COLUMN `sex` TINYINT(2) NOT NULL DEFAULT '0' COMMENT '0男士1为女士' AFTER `name`;

# 4.2、更新一列 将1改为3 0改为2, 其他都改为9
update insert_sql
set sex = (case sex
                when 1 then 3
                when 0 then 2
                else 9
            end);



# 5、找出重复出现n次的数据。
select 列名
from 表名
group by 列名
having count(列名) > n;



## 有趣的电影 ID奇数

SELECT
    id,
    movie,
    description,
    rating
FROM
    cinema
WHERE
    id%2 = 1
AND description != 'boring'
ORDER BY
    rating DESC


## 表自连接---薪水超过经理的员工
select e.Name as Employee
from Employee e
join Employee m on e.managerid=m.id
where e.salary>m.salary;

## 表连接--在A表不在B表的数据

select a.姓名 as 不近视的学生名单
from 学生表 as a
left join 近视学生表 as b
on a.学号=b.学生学号
where b.序号 is null;


## 超过五名学生的课程
select class
from courses
group by class
having count(distinct student) >= 5

## 第二高的薪水

select ifNull(
(select distinct salary
from Employee
order by Salary Desc
limit 1,1),null
) as SecondHighestSalary;