TASK5--MySQL实战_mysql taskable-优快云博客

本文深入探讨了SQL在实际场景中的应用，通过具体案例讲解如何使用SQL进行数据导入、导出，以及复杂的查询操作，包括部门最高薪资员工的查询和座位表的调整等，适合SQL初学者和进阶者参考。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2019427更新了第一题的详细感悟

20190517更新：好好看第2题

将Excel文件导入MySQL表：Table Data Import Wizard

参考：https://baijiahao.baidu.com/s?id=1572962766220128&wfr=spider&for=pc

MySQL导出表到Excel文件：Table Data Export Wizard

参考：https://www.cnblogs.com/pkangping/p/9462720.html

第一题代码

CREATE TABLE Employee
 (
 Id            INT          NOT NULL PRIMARY KEY,
 Name          VARCHAR(50)  NOT NULL,
 Salary        INT          NOT NULL,
 Departmentid  INT          NOT NULL
 )	;
 
INSERT INTO Employee(Id,Name,Salary,Departmentid) VALUES (1,'Joe',70000,1);
INSERT INTO Employee(Id,Name,Salary,Departmentid) VALUES (2,'Henry',80000,2);
INSERT INTO Employee(Id,Name,Salary,Departmentid) VALUES (3,'Sam',60000,2);
INSERT INTO Employee(Id,Name,Salary,Departmentid) VALUES (4,'Max',90000,1);

CREATE TABLE Department ( 
Id            INT          NOT NULL PRIMARY KEY, 
Name          VARCHAR(50)  NOT NULL );

INSERT INTO Department(Id,Name) VALUES (1,'IT');
INSERT INTO Department(Id,Name) VALUES (2,'Sales');

SELECT b.Name AS Department,        
       a.Name AS Employee,       
       MAX(a.Salary) AS Salary   
  FROM Employee AS a  
 INNER JOIN Department AS b     
    ON a.Departmentid = b.Id  
 GROUP BY b.Name ;

项目7做错了。返回结果不是想要的

结果如上：是最大的salary但是和emplyee不对应！！！！

正确代码

SELECT b.Name AS Department,
       a.Name AS Employee ,
       a.Salary
 FROM employee AS a  
INNER JOIN Department AS b
   ON a.DepartmentId = b.Id
  AND a.Salary = (SELECT MAX(Salary)
					FROM employee 
				   WHERE DepartmentID  = b.Id);

分析1：

SELECT MAX(Salary)
FROM employee,Department
WHERE DepartmentID = Department.Id;

只会返回一个最大的Salary而不是每个部门对应的SALary，但是为什么正确代码可以有每个部门的呢？

分析2：

SELECT b.Name AS Department,
a.Name AS Employee ,
a.Salary
FROM employee AS a
INNER JOIN Department AS b
ON a.DepartmentId = b.Id；

将2个表连接：怎么连接？左边的表里的记录一个一个找和b表里面id对应

分析3：加上AND后面的条件:代表我连接：1.一个记录一个记录连接，（比如id=1，且此时id=1下的工资最大值）这和分析1区别出来了！！

当然可以把上述语句的ON。。。AND换成ON。。。WHERE（本人更喜欢ONWHERE

SELECT d.Name AS Department,
	   e.Name AS employee,
	   e.salary
  FROM Employee e
  JOIN Department d
    ON e.Departmentid = d.Id
 WHERE e.salary = (SELECT MAX(salary) 
					FROM employee e2
                    WHERE e2.Departmentid = e.Departmentid);

为甚么我写的有bug？因为你groupby的是id，max发挥作用没有错误，但是剩下的emplyee我们有多个，你没指定哪一个就的随便反返回了

SELECT bname AS Department,
	   Name  AS employee,
        salary
FROM
           (SELECT a.*,        
                   b.Name AS bname     
              FROM Employee AS a  
             INNER JOIN Department AS b     
                ON a.Departmentid = b.Id  ) AS t1

WHERE salary IN (SELECT MAX(salary) FROM 
                      (SELECT a.*,        
                   b.Name AS bname     
              FROM Employee AS a  
             INNER JOIN Department AS b     
                ON a.Departmentid = b.Id) AS t1
                       GROUP BY t1.bname);

上个是自己想的另一种方法，比较繁琐

第二题代码

DROP TABLE seat;

CREATE TABLE seat
(
id            INT          NOT NULL ,
student       VARCHAR(50)  NOT NULL
);

INSERT INTO seat(id,student) VALUES (1,'Abbot');
INSERT INTO seat(id,student) VALUES (2,'Doris');
INSERT INTO seat(id,student) VALUES (3,'Emerson');
INSERT INTO seat(id,student) VALUES (4,'Green');
INSERT INTO seat(id,student) VALUES (5,'Jeames');


SELECT MAX(id) FROM seat;
SELECT MAX(id) FROM seat;
UPDATE seat 
   SET id = CASE

         WHEN id=5          Then  5
         WHEN mod(id,2) = 0  Then  id-1
         ELSE                 id+1

        END;
SELECT * FROM seat order by id;

第二题做的很不好，希望以后借鉴下其他小伙伴的结果

优秀结果1.


select (case

      when mod(id,2)!=0 and id!=counts then id+1

      when mod(id,2)!=0 and id=counts then id

      else id-1 end)as id,student

      from seat,(select count(*) as counts from seat)as seat_counts
     -- 注意这个from seat和seat_counts代表select来自这两个表鸭

                order by id;

优秀结果2




select s.id , s.student from

(

select id-1 as id , student from seat where mod(id,2)=0

union

select id+1 as id , student from seat where mod(id,2) = 1 and id != (select count(*) from seat)

union

select id , student from seat where mod(id,2) = 1 and id = (select count(*) from seat)

) s order by id;

CREATE TABLE scores ( 
Id            INT          NOT NULL , 
Score         DECIMAL(10,2) NOT NULL 
);

INSERT INTO scores(Id,Score) VALUES (1,3.50);
INSERT INTO scores(Id,Score) VALUES (2,3.65);
INSERT INTO scores(Id,Score) VALUES (3,4.00);
INSERT INTO scores(Id,Score) VALUES (4,3.85);
INSERT INTO scores(Id,Score) VALUES (5,4.00);
INSERT INTO scores(Id,Score) VALUES (6,3.65);



SELECT * FROM scores;

-- 此题的思路是对于每一个分数，找出表中有多少个大于、等于该分数的不同的分数，然后按降序排列即可。

SELECT
    Score,
    (
        SELECT
            count(DISTINCT score)
        FROM
            scores
        WHERE
            Score >= S.score
    ) AS '排名'
FROM
    scores s
ORDER BY
    Score DESC;

第三题做的时候很困难，参考别人的，以后需要勤加练习