0409MySQL实战

缺乏、安全感 2022-04-23 18:32 45阅读 0赞

### MySQL实战 ###

*  20190408
 *   *  1、从不订购的客户（简单）
     *  2、第N高的薪水（中等）
     *  3、（困难）体育馆人流量
 *  20190409
 *   *  4、连续出现的数字
     *  5、上升的温度
     *  6、有趣的电影

# 20190408 #

## 1、从不订购的客户（简单） ##

某网站包含两个表，Customers 表和 Orders 表。编写一个 SQL 查询，找出所有从不订购任何东西的客户。

--创建表插入值
    CREATE TABLE Customers(
    	Id int(10) primary key,
    	Name VARCHAR(10)
    );
    INSERT INTO Customers(Id,Name)
    VALUES(1,"Joe");
    INSERT INTO Customers(Id,Name)
    VALUES(2,"Henry");
    INSERT INTO Customers(Id,Name)
    VALUES(3,"Sam");
    INSERT INTO Customers(Id,Name)
    VALUES(4,"Max");
    +----+-------+
    | Id | Name  |
    +----+-------+
    |  1 | Joe   |
    |  2 | Henry |
    |  3 | Sam   |
    |  4 | Max   |
    +----+-------+
    4 rows in set (0.00 sec)
    CREATE TABLE Orders(
    	Id int(10),
    	CustomerId int(10) 
    );
    INSERT INTO Orders(Id,CustomerId)
    VALUES(1,3);
    INSERT INTO Orders(Id,CustomerId)
    VALUES(2,1);
    +------+------------+
    | Id   | CustomerId |
    +------+------------+
    |    1 |          3 |
    |    2 |          1 |
    +------+------------+
    2 rows in set (0.00 sec)
    --思路：查找出Customers.Id不在(not in)Orders.CustomerId
    SELECT Customers.Name as Customers FROM Customers
    WHERE Customers.Id not in (select CustomerId from Orders);
    +-----------+
    | Customers |
    +-----------+
    | Henry     |
    | Max       |
    +-----------+
    2 rows in set (0.00 sec)

## 2、第N高的薪水（中等） ##

编写一个 SQL 查询，获取 Employee 表中第n高的薪水（Salary）。

CREATE TABLE Employee(
    	Id INT(10),
    	Salary INT(10)
    );
    INSERT INTO Employee(Id,Salary)
    VALUES(1,100);
    INSERT INTO Employee(Id,Salary)
    VALUES(2,200);
    INSERT INTO Employee(Id,Salary)
    VALUES(3,300);
    +------+--------+
    | Id   | Salary |
    +------+--------+
    |    1 |    100 |
    |    2 |    200 |
    |    3 |    300 |
    +------+--------+
    3 rows in set (0.00 sec)
    --取第二高
    方法一：
    SELECT Salary as getNthHighestSalary FROM Employee 
    order by Salary desc limit 1,1   --从1开始取1个，第一个参数为指定默认为0
    方法二：从employee表中取最大的工资，且这个工资满足的条件是小于最大工资，即取第二高。
    select MAX(Salary) as SecondHighestSalary from Employee
    where Salary <(
    select MAX(Salary) from Employee);
    
    
    --第n高
    CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
    BEGIN
      RETURN (
    SELECT c.Salary SecondHighestSalary  FROM (SELECT N as RANK) D  LEFT JOIN 
    (SELECT P1.Id,P1.salary,(SELECT COUNT(DISTINCT P2.Salary) FROM  Employee P2 WHERE P2.salary > P1.salary) + 1 AS rank 
    FROM Employee P1) C ON  C.rank = D.rank GROUP BY D.rank
      );
    END

## 3、（困难）体育馆人流量 ##

X 市建了一个新的体育馆，每日人流量信息被记录在这三列信息中：序号 (id)、日期 (date)、 人流量 (people)。  
请编写一个查询语句，找出高峰期时段，要求**连续三天及以上**，并且每天人流量均不少于100。

CREATE TABLE stadium(
    	id INT(10),
    	date DATE,
    	people INT(10)
    );
    INSERT INTO stadium(id,date,people)
    VALUES(1,'2017-01-01',10);
    INSERT INTO stadium(id,date,people)
    VALUES(2,'2017-01-02',109);
    INSERT INTO stadium(id,date,people)
    VALUES(3,'2017-01-03',150);
    INSERT INTO stadium(id,date,people)
    VALUES(4,'2017-01-04',99);
    INSERT INTO stadium(id,date,people)
    VALUES(5,'2017-01-05',145);
    INSERT INTO stadium(id,date,people)
    VALUES(6,'2017-01-06',1455);
    INSERT INTO stadium(id,date,people)
    VALUES(7,'2017-01-07',199);
    INSERT INTO stadium(id,date,people)
    VALUES(8,'2017-01-08',188);
    +------+------------+--------+
    | id   | date       | people |
    +------+------------+--------+
    |    2 | 2017-01-02 |    109 |
    |    3 | 2017-01-03 |    150 |
    |    5 | 2017-01-05 |    145 |
    |    6 | 2017-01-06 |   1455 |
    |    7 | 2017-01-07 |    199 |
    |    8 | 2017-01-08 |    188 |
    +------+------------+--------+
    6 rows in set (0.00 sec)
    
    --连续三天以上，将stadium看成三张表
    SELECT DISTINCT s1.*
    FROM stadium s1, stadium s2, stadium s3
    WHERE s1.people >=100 AND s2.people >=100 AND s3.people >=100 --先找出三张表中人数>100的
    AND (s2.id=s1.id+1 AND s3.id=s1.id+2 --s1.people为第一个
        OR s2.id=s1.id-1 AND s3.id=s1.id+1  --s1.people为第二个
        OR s2.id=s1.id-2 AND s3.id=s1.id-1) --s1.people为最后一个
    ORDER BY s1.id;
    +------+------------+--------+
    | id   | date       | people |
    +------+------------+--------+
    |    5 | 2017-01-05 |    145 |
    |    6 | 2017-01-06 |   1455 |
    |    7 | 2017-01-07 |    199 |
    |    8 | 2017-01-08 |    188 |
    +------+------------+--------+
    4 rows in set (0.00 sec)

# 20190409 #

## 4、连续出现的数字 ##

CREATE TABLE Logs(
    	Id int(10),
    	Num int(10)
    );
    
    INSERT INTO Logs(Id,Num)
    VALUES(1,1);
    INSERT INTO Logs(Id,Num)
    VALUES(2,1);
    INSERT INTO Logs(Id,Num)
    VALUES(3,1);
    INSERT INTO Logs(Id,Num)
    VALUES(4,2);
    INSERT INTO Logs(Id,Num)
    VALUES(5,1);
    INSERT INTO Logs(Id,Num)
    VALUES(6,2);
    INSERT INTO Logs(Id,Num)
    VALUES(7,2);
    +------+------+
    | Id   | Num  |
    +------+------+
    |    1 |    1 |
    |    2 |    1 |
    |    3 |    1 |
    |    4 |    2 |
    |    5 |    1 |
    |    6 |    2 |
    |    7 |    2 |
    +------+------+
    7 rows in set (0.00 sec)
    
    --编写一个 SQL 查询，查找所有至少连续出现三次的数字。
    --思路：将一张表看成三张表进行自连接。l1、l2、l3中的id依次连续出现，并且满足对应的num相同。与体育馆人流量题目有点像（连续三天及以上）。
    SELECT DISTINCT l1.Num AS ConsecutiveNums 
    FROM Logs AS l1, Logs AS l2, Logs AS l3
    WHERE l2.Id=l1.Id+1 AND l3.Id=l2.Id+1 AND l2.Num=l1.Num AND l3.Num=l2.Num;
    +-----------------+
    | ConsecutiveNums |
    +-----------------+
    |               1 |
    +-----------------+
    1 row in set (0.00 sec)

## 5、上升的温度 ##

给定一个 Weather 表，编写一个 SQL 查询，来查找与之前（昨天的）日期相比温度更高的所有日期的 Id。
    CREATE TABLE Weather(
    	Id INT(10),
    	RecordDate DATE,
    	Temperature INT(10)
    );
    INSERT INTO Weather(Id,RecordDate,Temperature)
    VALUES(1,'2015-01-01',10);
    INSERT INTO Weather(Id,RecordDate,Temperature)
    VALUES(2,'2015-01-02',25);
    INSERT INTO Weather(Id,RecordDate,Temperature)
    VALUES(3,'2015-01-03',20);
    INSERT INTO Weather(Id,RecordDate,Temperature)
    VALUES(4,'2015-01-04',30);
    +------+------------+-------------+
    | Id   | RecordDate | Temperature |
    +------+------------+-------------+
    |    1 | 2015-01-01 |          10 |
    |    2 | 2015-01-02 |          25 |
    |    3 | 2015-01-03 |          20 |
    |    4 | 2015-01-04 |          30 |
    +------+------------+-------------+
    4 rows in set (0.00 sec)
    --思路：将Weather看成两张表，查找与之前的日期即日期相差1，然后再作比较。
    --（自身作比较才知道是否进步）
    SELECT w2.Id FROM Weather as w1,Weather as w2
    WHERE w2.RecordDate=w1.RecordDate+1 AND w2.Temperature>w1.Temperature
    +------+
    | Id   |
    +------+
    |    2 |
    |    4 |
    +------+
    2 rows in set (0.00 sec)

## 6、有趣的电影 ##

CREATE TABLE cinema(
    	id INT (10),
    	movie VARCHAR(10),
    	description VARCHAR(20),
    	rating FLOAT
    );
    INSERT INTO cinema(id,movie,description,rating)
    VALUES(1,'War','great 3D',8.9);
    INSERT INTO cinema(id,movie,description,rating)
    VALUES(2,'Science','fiction',8.5);
    INSERT INTO cinema(id,movie,description,rating)
    VALUES(3,'irish','boring',6.2);
    INSERT INTO cinema(id,movie,description,rating)
    VALUES(4,'Ice song','Fantacy',8.6);
    INSERT INTO cinema(id,movie,description,rating)
    VALUES(5,'House card','Interesting',9.1);
    +------+------------+-------------+--------+
    | id   | movie      | description | rating |
    +------+------------+-------------+--------+
    |    1 | War        | great 3D    |    8.9 |
    |    2 | Science    | fiction     |    8.5 |
    |    4 | Ice song   | Fantacy     |    8.6 |
    |    5 | House card | Interesting |    9.1 |
    |    3 | irish      | boring      |    6.2 |
    +------+------------+-------------+--------+
    5 rows in set (0.00 sec)
    --思路：两个条件：描述！=‘boring’(记得加引号)且id为奇数即余数为1；一个排序：按照rating降序排列。
    SELECT * FROM cinema WHERE 
    description!='boring'AND id%2=1
    ORDER BY rating DESC;
    +------+------------+-------------+--------+
    | id   | movie      | description | rating |
    +------+------------+-------------+--------+
    |    5 | House card | Interesting |    9.1 |
    |    1 | War        | great 3D    |    8.9 |
    +------+------------+-------------+--------+
    2 rows in set (0.00 sec)

连着前面的四个任务，leetcode上面mysql实战的题目全部完成。