2022.7.11-2022.7.17：mysql数据库练习

2022.7.11-2022.7.17

196.删除表内重复的电子邮箱，只保留唯一的电子邮箱：

select p1

from person p1 , person p2

where p1.id=p2.id and p1.id>p2.id

627.变更性别

update salary

set

    sex = case sex

        when 'm' then 'f'

        else 'm'

    end;

1667.修复表中的名字

select user_id,

    concat(upper(left(name,1)),lower(substring(name,2)))as name

from Users

order by user_id

#    concat函数将多个字符床拼接在一起

#    upper函数将字符串中的所有符号转换为大写

#    lelt(str,length)函数从最左边开始截取字符串，length是截取的长度

#    lower函数将字符床中的所有符号转换为小写

#    substring(str,2)从第二个截取到最后

1484.按日期分组销售产品

select

    sell_date,

    //对product日期相同product不同进行聚合，作为num_sold表输出

    count(distinct product) num_sold,

    //对product日期相同product不同的，将多个字符串连接成一个字符串，作为product输出

    GROUP_CONCAT(distinct product) products

from

    activities

group by sell_date

order by sell_date;

#group by 用于结合聚合函数，根据一个或多个列对结果集进行分组

#order by 进行排序

#count(),将多个字符串连接成一个字符串

#GROUP_CONCAT函数将组中的字符串连接成为具有各种选项的单个字符串

1527.患某种疾病的患者

select patient_id,patient_name,conditions

from Patients

   where conditions like 'DIAB1%' or conditions like '% DIAB1%';

1965.丢失信息的雇员：

写出一个查询语句，找到所有 丢失信息 的雇员id。当满足下面一个条件时，就被认为是雇员的信息丢失：

雇员的 姓名 丢失了，或者

雇员的 薪水信息 丢失了，或者

返回这些雇员的id  employee_id ， 从小到大排序 。

 select employee_id from (

     select employee_id from Employees

     union all//联合多表

     select employee_id from Salaries

 ) as ans

 group by employee_id//按照id分组

 having count(employee_id) = 1 //条件筛选，如果id只出现了一次，视为丢失

 order by employee_id;//升序排列

1795.608每个产品在不同商店的价格

请你重构 Products 表，查询每个产品在不同商店的价格，使得输出的格式变为(product_id, store, price) 。如果这一产品在商店里没有出售，则不输出这一行。

输出结果表中的 顺序不作要求 。

select

    product_id,'store1' as store, store1 as price

from

    Products

where

    store1 is not null

union all

select

    product_id,'store2', store2

from

    Products

where

    store2 is not null

union all

select

    product_id,'store3', store3

from

    Products

where

    store3 is not null

608.树节点

给定一个表tree，id是树节点的编号，p_id是它父节点的id

树中每个节点属于以下三种类型之一：

叶子：如果这个节点没有任何孩子节点。

根：如果这个节点是整棵树的根，即没有父节点。

内部节点：如果这个节点既不是叶子节点也不是根节点。

# Write your MySQL query statement below

SELECT

    id, 'Root' AS Type

FROM

    tree

WHERE

    p_id IS NULL

UNION

SELECT

    id, 'Leaf' AS Type

FROM

    tree

WHERE

    id NOT IN (SELECT DISTINCT

            p_id

        FROM

            tree

        WHERE

            p_id IS NOT NULL)

        AND p_id IS NOT NULL

UNION

SELECT

    id, 'Inner' AS Type

FROM

    tree

WHERE

    id IN (SELECT DISTINCT

            p_id

        FROM

            tree

        WHERE

            p_id IS NOT NULL)

        AND p_id IS NOT NULL

ORDER BY id;

176.第二高的薪水：

编写一个 SQL 查询，获取并返回 Employee 表中第二高的薪水 。如果不存在第二高的薪水，查询应该返回 null 。

方法一：

SELECT

    (SELECT DISTINCT

            Salary

        FROM

            Employee

        ORDER BY Salary DESC

        LIMIT 1 OFFSET 1) AS SecondHighestSalary

;

//limit m offset n,意思是从 第n条记录开始，返回m条记录

//desc关键字

方法二：

select max(Salary) SecondHighestSalary

from Employee

where Salary < (select max(Salary) from Employee)

175.组合两个表：

select FirstName, LastName, City, State

from Person left join Address

on Person.PersonId = Address.PersonId

多表的联结又分为以下几种类型：

1）左联结（left join），联结结果保留左表的全部数据

2）右联结（right join），联结结果保留右表的全部数据

3）内联结（inner join），取两表的公共数据

1581.进店却未进行过交易的顾客：

# Write your MySQL query statement below

SELECT

    customer_id, COUNT(customer_id) count_no_trans

FROM

    visits v//visits表的代称v

LEFT JOIN

    transactions t ON v.visit_id = t.visit_id//保留左表合并，在visit_id相同的情况下

WHERE amount IS NULL//没有购物

GROUP BY customer_id;//按照customer_id 分组

1148.文章浏览1：

# Write your MySQL query statement below

select distinct author_id as id

from Views

where author_id = viewer_id

order by id;

197.上升的温度：

SELECT

    weather.id AS 'Id'

FROM

    weather

        JOIN

    weather w ON DATEDIFF(weather.recorddate, w.recorddate) = 1

        AND weather.Temperature > w.Temperature

//使用datediff来比较两个日期类型的值

不使用join的第二种写法

SELECT w2.Id

FROM Weather w1, Weather w2

WHERE DATEDIFF(w2.RecordDate, w1.RecordDate) = 1

AND w1.Temperature < w2.Temperature

607.销售员：

SELECT

    s.name

FROM

    salesperson s

WHERE

    s.sales_id NOT IN (SELECT

            o.sales_id

        FROM

            orders o

                LEFT JOIN

            company c ON o.com_id = c.com_id

        WHERE

            c.name = 'RED')

;