MySQL学习 || 任务4 联系表联结和复杂的SQL查询

（好多不会，再学一点知识再做）)

一、MySQL 实战

数据导入导出

在navicat中

导出：选中一张表，点击导出向导，按步骤导出，任务中让导出CSV格式，由于没有这个选项，选择了xls格式导出。
导入：选好数据库，点击导入向导，按步骤操作即可。

作业

作业1

代码：

--创建Employee表并插入数据
create table Employee(
id char(10),
Name char(20),
Salary int,
Departmentid char(10));
insert into Employee values('1','Joe',70000,'1');
insert into Employee values('2','Henry',80000,'2');
insert into Employee values('3','Sam',60000,'2');
insert into Employee values('4','Max',90000,'1');

--创建Department表并插入数据 
create table Department(
id char(10),
Name char(20));
insert into Department values('1','IT');
insert into Department values('2','Sales');

查询语句（错误，但不会改.....）：
    select Department.Name AS Department,Employee.Name AS Employee，Salary
    from Employee,Department limit 1
    group by Departmentid having Employee.Departmentid=Department.id 
    order by Salary;

作业2

代码：

创建表并插入数据：
create table seat(
id int,
student char(20));
insert into seat values (1,'Abbot');
insert into seat values(2,'Doris');
insert into seat values(3,'Emerson');
insert into seat values(4,'Green');
insert into seat values(5,'Jeames');
查询：
select (CASE
    when id%2=1 and id=(SELECT COUNT(*) FROM seat) then id
	when id%2=1  then id+1
	else id-1
    end)AS id,student
from seat
order by id;

作业3

代码：（勉强看懂别人的o(╥﹏╥)o）

SELECT 
    Score,
    (SELECT 
            COUNT(DISTINCT Score)
     FROM
            scores
     WHERE
            Score >= s.Score) AS Rank
FROM
    Scores s
ORDER BY Score DESC;

二、MySQL 实战 - 复杂项目

作业

作业1

哭了o(╥﹏╥)o

作业2

作业3

代码：

SELECT 
    s.Score,
    (SELECT 
      	COUNT(*) + 1
     FROM
         Scores AS s1
     WHERE
         s1.Score > s.Score) AS Rank
FROM
    scores s