MYSQL学习04--数据导入导出及实战练习

原文链接:http://www.cnblogs.com/Miles-mjy/p/10665618.html

1、数据导入导出

  • MySQL表导出,且是CSV格式
    我使用navicat导入导出,导出过程为:
    右键表名->导出向导->选择csv文件->下一步选择源表名->下一步选择导出字段->勾选包含列标题等配置->
    开始->导出成功
    990253-20190407154308409-1233605086.png

  • 再将CSV表导入数据库
    右键表名->导入向导->选择csv文件->下一步选择导入文件->下一步设置分隔符->下一步设置字段名称,导入起始结束行 ->下一步设置源表目标表->设置字段 ->下一步选择导入模式->点击开始导入->导入成功
    990253-20190407154325240-1515312101.png

2、作业

  • 项目七: 各部门工资最高的员工(难度:中等)
    编写一个 SQL 查询,找出每个部门工资最高的员工。例如,根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
------------------------------------------------------------
-----**************项目七******************************-----
------------------------------------------------------------
CREATE TABLE employee (
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
salary INT(10) NOT NULL,
departmentId INT UNSIGNED NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO employee(name,salary,departmentId) VALUES("Joe",70000,1);
INSERT INTO employee(name,salary,departmentId) VALUES("Henry",80000,2);
INSERT INTO employee(name,salary,departmentId) VALUES("Sam",60000,2);
INSERT INTO employee(name,salary,departmentId) VALUES("Max",90000,1);

CREATE TABLE department(
departmentId INT UNSIGNED AUTO_INCREMENT,
d_name VARCHAR(20) NOT NULL,
PRIMARY KEY(departmentId)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO department(d_name) VALUES('IT');
INSERT INTO department(d_name) VALUES('Sales');

--GROUP BY 和MAX()函数不能直接一起用,GROUP BY返回的是小组第一行的值,MAX()返回的是最大的,2组值组合在一起,会有错误

SELECT d.d_name,e.name,e.salary FROM employee e LEFT JOIN department d on e.departmentId=d.departmentId 
where e.salary IN
    (SELECT MAX(salary)
        FROM employee
        GROUP BY departmentId);
  • 项目八: 换座位(难度:中等)
    小美是一所中学的信息科技老师,她有一张 seat 座位表,平时用来储存学生名字和与他们相对应的座位 id。
    其中纵列的 id 是连续递增的
    小美想改变相邻俩学生的座位。
    你能不能帮她写一个 SQL query 来输出小美想要的结果呢?
------------------------------------------------------------
-----**************项目八、换座位************************---
------------------------------------------------------------
CREATE TABLE seats(
    id INT UNSIGNED AUTO_INCREMENT,
    sname VARCHAR(40) NOT NULL,
    PRIMARY KEY(id)
)ENGINE= INNODB DEFAULT CHARSET=utf8;

INSERT INTO seats(sname) VALUES("Abbot");
INSERT INTO seats(sname) VALUES("Doris");
INSERT INTO seats(sname) VALUES("Emerson");
INSERT INTO seats(sname) VALUES("Green");
INSERT INTO seats(sname) VALUES("Jeames"); 

SELECT (
        CASE WHEN id%2=1 and id = (SELECT COUNT(*) FROM seats) THEN id
        WHEN id%2 =1 THEN id+1
        ELSE id-1
        END
        ) AS id,sname
FROM seats
ORDER BY id; 
  • 项目九: 分数排名(难度:中等)
    编写一个 SQL 查询来实现分数排名。如果两个分数相同,则两个分数排名(Rank)相同。请注意,平分后的下一个名次应该是下一个连续的整数值。换句话说,名次之间不应该有“间隔”。
------------------------------------------------------------
-----**************项目九、分数排名************************--
------------------------------------------------------------
CREATE TABLE score (
    sco_id INT UNSIGNED AUTO_INCREMENT,
    score FLOAT NOT NULL,
    PRIMARY KEY(sco_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO score(score) VALUES(3.50);
INSERT INTO score(score) VALUES(3.65);
INSERT INTO score(score) VALUES(4.00);
INSERT INTO score(score) VALUES(3.50);
INSERT INTO score(score) VALUES(3.85);
INSERT INTO score(score) VALUES(4.00);
INSERT INTO score(score) VALUES(3.65);

SELECT score,(
    SELECT COUNT(DISTINCT(score))+1
    FROM score
    WHERE Score>s1.Score
) AS RANK
FROM score AS s1
ORDER BY Score DESC,sco_id ASC;

3、MySQL 实战 - 复杂项目

  • 项目十:行程和用户(难度:困难)
------------------------------------------------------------
-----*******项目十、行程和用户****************************--
------------------------------------------------------------
CREATE TABLE trips(
id INT UNSIGNED AUTO_INCREMENT,
client_id INT NOT NULL,
driver_id INT NOT NULL,
city_id INT NOT NULL,
status enum('completed','cancelled_by_driver','cancelled_by_client'),
request_at DATE NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(1,10,1,'completed','2013-10-01');
INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(2,11,1,'cancelled_by_driver','2013-10-01');
INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(3,12,6,'completed','2013-10-01');
INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(4,13,6,'cancelled_by_client','2013-10-01');
INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(1,10,1,'completed','2013-10-02');
INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(2,11,6,'completed','2013-10-02');
INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(3,12,6,'completed','2013-10-02');
INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(2,12,12,'completed','2013-10-03');
INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(3,10,12,'completed','2013-10-03');
INSERT INTO trips(client_id,driver_id,city_id,status,request_at) VALUES(4,13,12,'cancelled_by_client','2013-10-03');

CREATE table users (
userId INT UNSIGNED AUTO_INCREMENT,
banned VARCHAR(4) NOT NULL,
role enum('client','driver','partner'),
PRIMARY KEY(userId)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO users(banned,role) VALUES( "No",'client');
INSERT INTO users(banned,role) VALUES( "Yes",'client');
INSERT INTO users(banned,role) VALUES( "No",'client');
INSERT INTO users(banned,role) VALUES( "No",'client');
INSERT INTO users(banned,role) VALUES( "No",'driver');
INSERT INTO users(banned,role) VALUES( "No",'driver');
INSERT INTO users(banned,role) VALUES( "No",'driver');
INSERT INTO users(banned,role) VALUES( "No",'driver');



SELECT t.request_at AS 'Day',
ROUND((SUM(CASE WHEN t.Status LIKE 'cancelled%' THEN 1 ELSE 0 END))/COUNT(*),2) AS 'Cancellation Rate' 
FROM trips AS t INNER JOIN users AS u 
ON u.userId = t.client_id AND u.banned = 'No' 
GROUP BY t.request_at;
  • 项目十一:各部门前3高工资的员工(难度:中等)
    将项目7中的employee表清空,重新插入以下数据(其实是多插入5,6两行),编写一个 SQL 查询,找出每个部门工资前三高的员工。例如,根据上述给定的表格,查询结果应返回,此外,请考虑实现各部门前N高工资的员工功能。
CREATE TABLE employee (
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
salary INT(10) NOT NULL,
departmentId INT UNSIGNED NOT NULL,
PRIMARY KEY(id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO employee(name,salary,departmentId) VALUES("Joe",70000,1);
INSERT INTO employee(name,salary,departmentId) VALUES("Henry",80000,2);
INSERT INTO employee(name,salary,departmentId) VALUES("Sam",60000,2);
INSERT INTO employee(name,salary,departmentId) VALUES("Max",90000,1);
INSERT INTO employee(name,salary,departmentId) VALUES("Janet",69000,1);
INSERT INTO employee(name,salary,departmentId) VALUES("Randy",85000,1);
  • 项目十二 分数排名 - (难度:中等)
    依然是昨天的分数表,实现排名功能,但是排名是非连续的,如下:
    +-------+------+
    | Score | Rank |
    +-------+------+
    | 4.00 | 1 |
    | 4.00 | 1 |
    | 3.85 | 3 |
    | 3.65 | 4 |
    | 3.65 | 4 |
    | 3.50 | 6 |
    +-------+------
CREATE TABLE score (
    sco_id INT UNSIGNED AUTO_INCREMENT,
    score FLOAT NOT NULL,
    PRIMARY KEY(sco_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO score(score) VALUES(3.50);
INSERT INTO score(score) VALUES(3.65);
INSERT INTO score(score) VALUES(4.00);
INSERT INTO score(score) VALUES(3.50);
INSERT INTO score(score) VALUES(3.85);
INSERT INTO score(score) VALUES(4.00);
INSERT INTO score(score) VALUES(3.65);

转载于:https://www.cnblogs.com/Miles-mjy/p/10665618.html

原文链接:加载失败,请重新获取