MySql学习系列（三）

小咪咪 2022-01-30 07:07 156阅读 0赞

### MySql学习系列（三） ###

*  2.1 MySQL 基础 （二）- 表操作
 *   *  \#学习内容\#
     *  数据导入导出
     *   *   *  1、导出表是CSV格式
             *   *   *  问题一：MySQL导出数据遇到secure-file-priv问题的解决方法
             *  2、再将CSV表导入数据库
             *   *  1、mysql 命令导入
                 *  2、使用 LOAD DATA 导入数据
     *  \#作业\#
     *   *  项目七: 各部门工资最高的员工（难度：中等）
         *  项目八: 换座位（难度：中等）
         *  项目九: 分数排名（难度：中等）

# 2.1 MySQL 基础 （二）- 表操作 #

## \#学习内容\# ##

## 数据导入导出 ##

#### 1、导出表是CSV格式 ####

将之前创建的任意一张MySQL表导出,且是CSV格式

使用 SELECT … INTO OUTFILE 语句导出数据  
以下实例中我们将数据表 runoob\_tbl 数据导出到 /tmp/runoob.txt 文件中：

mysql> SELECT * FROM runoob_tbl 
           -> INTO OUTFILE '/tmp/runoob.txt';

你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：

mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/runoob.txt'
           -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
           -> LINES TERMINATED BY '\r\n';

在下面的例子中，生成一个文件，各值用逗号隔开。这种格式可以被许多程序使用。

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM test_table;

SELECT … INTO OUTFILE 语句有以下属性：  
1、LOAD DATA INFILE是SELECT … INTO OUTFILE的逆操作，SELECT句法。为了将一个数据库的数据写入一个文件，使用SELECT … INTO OUTFILE，为了将文件读回数据库，使用LOAD DATA INFILE。

###### 问题一：MySQL导出数据遇到secure-file-priv问题的解决方法 ######

通过命令查看secure-file-priv的当前值：

show variables like '%secure%';

能够立即定位到 MySQL 数据文件的存储位置方法：

show global variables like "%datadir%";

可以通过打开my.ini配置文件进行查看及修改：

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

当secure\_file\_priv的值为null ，表示限制mysqld 不允许导入|导出  
![在这里插入图片描述][20190518165902429.png]  
解决方案：

1.找到mysql安装目录下my.ini文件，在\[mysqld\]下添加secure\_file\_priv = ‘’ 保存并退出;  
2.dos环境下进入数据库，重启数据库，net stop mysql net start mysql  
登陆数据库：`mysql -u root mysql` 这样登陆才有权限  
3.执行命令 show variables like ‘%secure%’; 查看secure\_file\_priv的值，  
![在这里插入图片描述][201905181658441.png]  
4.进入数据库

show databases;
    use test;
    show tables;
    SELECT * FROM runoob_tbl  INTO OUTFILE 'D:/test.txt';

导出数据指定为csv格式，mysql的执行语句:

select * from 表名 into outfile "C:/data.csv" fields terminated by ',' enclosed by '"'
    
    lines terminated by '\r\n';
    
    其中"C:/data.csv"表示路径和文件名

> [https://blog.csdn.net/weixin\_39631030/article/details/79873936][https_blog.csdn.net_weixin_39631030_article_details_79873936]

#### 2、再将CSV表导入数据库 ####

##### 1、mysql 命令导入 #####

使用 mysql 命令导入语法格式为：

mysql -u 用户名    -p密码    <  要导入的数据库数据(runoob.sql)

实例：

# mysql -u root -p123456 < runoob.sql

以上命令将将备份的整个数据库 runoob.sql 导入。

##### 2、使用 LOAD DATA 导入数据 #####

MySQL 中提供了LOAD DATA INFILE语句来插入数据。 以下实例中将从当前目录中读取文件 dump.txt ，将该文件中的数据插入到当前数据库的 mytbl 表中。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

两个命令的 FIELDS 和== LINES 子句==的语法是一样的。两个子句都是可选的，但是如果两个同时被指定，FIELDS 子句必须出现在 LINES 子句之前。  
如果用户指定一个 FIELDS 子句，它的子句 （TERMINATED BY、\[OPTIONALLY\] ENCLOSED BY 和 ESCAPED BY) 也是可选的，不过，用户必须至少指定它们中的一个。

mysql> LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
      -> FIELDS TERMINATED BY ':'
      -> LINES TERMINATED BY '\r\n';

## \#作业\# ##

### 项目七: 各部门工资最高的员工（难度：中等） ###

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id,name, salary 和 department Id。

CREATE TABLE employee(
    		`id` INT NOT NULL PRIMARY KEY,
    		 `name` VARCHAR(225),
    		 `salary` INT NOT NULL,
    		 `department_id` INT NOT NULL);
    INSERT INTO employee
    VALUES
        (1,'Joe',70000,1),
    	(2,'Henry',80000,2),
    	(3,'Sam',60000,2),
    	(4,'Max',90000,1);

![在这里插入图片描述][20190518180520176.png]  
创建Department 表，包含公司所有部门的信息。

CREATE TABLE department(
    		`id` INT NOT NULL PRIMARY KEY,
    		`name` VARCHAR(225));
    INSERT INTO department
    VALUES
        (1,'IT'),
    	(2,'Sales');

![在这里插入图片描述][20190518180635284.png]

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

SELECT department.`name` AS `department`,t.employee,t.salary
    FROM
    (SELECT e1.name Employee,e1.salary Salary,e1.department_id
    FROM employee e1 LEFT JOIN employee e2 
    on e1.salary < e2.salary AND e1.department_id = e2.department_id
    WHERE e2.id is NULL)t
    JOIN department ON department.id = t.department_id;

![在这里插入图片描述][20190518181344204.png]

### 项目八: 换座位（难度：中等） ###

小美是一所中学的信息科技老师，她有一张 seat 座位表，平时用来储存学生名字和与他们相对应的座位 id。

其中纵列的 \*\*id \*\*是连续递增的

小美想改变相邻俩学生的座位。

你能不能帮她写一个 SQL query 来输出小美想要的结果呢？

请创建如下所示seat表：  
示例：

±--------±--------+ | id | student | ±--------±--------+ | 1 | Abbot | | 2 | Doris | | 3 | Emerson | | 4 | Green | | 5 | Jeames | ±--------±--------+

CREATE TABLE seat(
    		`id` INT NOT NULL PRIMARY KEY,
    		`student` VARCHAR(225));
    INSERT INTO seat
    VALUES
         (1,'Abbot'),
         (2,'Doris'),
         (3,'Emerson'),
         (4,'Green'),
         (5,'Jeames');

假如数据输入的是上表，则输出结果如下：

±--------±--------+ | id | student | ±--------±--------+ | 1 | Doris | | 2 | Abbot | | 3 | Green | | 4 | Emerson | | 5 | Jeames | ±--------±--------+  
注意：

如果学生人数是奇数，则不需要改变最后一个同学的座位。

SELECT t.id,t.student
    FROM
    (SELECT id-1 id,student FROM seat
    WHERE MOD(id,2) = 0
    UNION
    SELECT id+1 id,student FROM seat
    WHERE MOD(id,2) = 1 AND id != (SELECT MAX(id) FROM seat)
    UNION
    SELECT id,student FROM seat
    WHERE MOD(id,2) = 1 AND id = (SELECT MAX(id) FROM seat))t
    ORDER BY id;

![在这里插入图片描述][20190518192602850.png]

### 项目九: 分数排名（难度：中等） ###

编写一个 SQL 查询来实现分数排名。如果两个分数相同，则两个分数排名（Rank）相同。请注意，平分后的下一个名次应该是下一个连续的整数值。换句话说，名次之间不应该有“间隔”。  
创建以下score表：

±—±------+ | Id | Score | ±—±------+ | 1 | 3.50 | | 2 | 3.65 | | 3 | 4.00 | | 4 | 3.85 | | 5 | 4.00 | | 6 | 3.65 | ±—±------+

例如，根据上述给定的 Scores 表，你的查询应该返回（按分数从高到低排列）：

±------±-----+ | Score | Rank | ±------±-----+ | 4.00 | 1 | | 4.00 | 1 | | 3.85 | 2 | | 3.65 | 3 | | 3.65 | 3 | | 3.50 | 4 | ±------±-----+

CREATE TABLE score(
    		`id` INT NOT NULL PRIMARY KEY,
    		 `Score` double NOT NULL);

INSERT INTO score
         values
         (1,3.50),
         (2,3.65),
         (3,4.00),
         (4,3.85),
         (5,4.00),
         (6,3.65);

![在这里插入图片描述][20190518195234719.png]

SELECT
        -> score,
        -> (SELECT
        -> count(DISTINCT score)
        -> FROM score
        -> where score >= s.score)
        -> AS Ranks
        -> FROM score s
        -> ORDER BY
        -> score DESC;

![在这里插入图片描述][20190518195152497.png]

[20190518165902429.png]: /images/20220130/1182503cefc04f9388919be2a3acc568.png
[201905181658441.png]: /images/20220130/44264331b18c4fd9ac92aa36eb0501a0.png
[https_blog.csdn.net_weixin_39631030_article_details_79873936]: https://blog.csdn.net/weixin_39631030/article/details/79873936
[20190518180520176.png]: /images/20220130/37a6617f8dbb4182b416cae8dacfdd2f.png
[20190518180635284.png]: /images/20220130/3d868af4f0c84109b3a7ed3c8f8d4869.png
[20190518181344204.png]: /images/20220130/a77312b9bb3d413280c8417996da9583.png
[20190518192602850.png]: /images/20220130/bcaac3789e98491cbbfb268fa715e8f8.png
[20190518195234719.png]: /images/20220130/448c33d8b9b74712ba061e59b1ad72ca.png
[20190518195152497.png]: /images/20220130/730e306efca340a3894230309992716b.png