MySQL入门 | 四

一.数据导入导出

参考教材: http://www.runoob.com/mysql/mysql-database-export.html

个人还是推荐用图形化界面如:workbench 以及 navicat 进行直接操作,而不是输代码.

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

SELECT * INTO OUTFILE 'D:/MySQL/customer.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

CSV文件包含结果集中的行集合。每行由一个回车序列和由LINES TERMINATED BY '\r\n’子句指定的换行字符终止。文件中的每行包含表的结果集的每一行记录。
每个值由FIELDS ENCLOSED BY '"'子句指示的双引号括起来。 这样可以防止可能包含逗号(，)的值被解释为字段分隔符。 当用双引号括住这些值时，该值中的逗号不会被识别为字段分隔符。

2.再将CSV表导入数据库

参考: https://www.yiibai.com/mysql/import-csv-file-mysql-table.html

LOAD DATA INFILE 'D:/mysql/customers.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

文件的字段由FIELD TERMINATED BY ','指示的逗号终止，并由ENCLOSED BY '"'指定的双引号括起来。因为文件第一行包含列标题，列标题不需要导入到表中，因此通过指定IGNORE 1 ROWS选项来忽略第一行。

二.作业

1. 项目七

创建employee表

create table employee
(id int not null primary key,
Name varchar(255),
Salary varchar(255),
DepartmentId int);
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);
select * from employee;

创建department表

create table department
(id int not null primary key,
Name varchar(255));
Insert into department values(
1, 'IT');
Insert into department values(
2, 'Sales');
select * from department;

筛选每个部门工资最高的员工

SELECT department.Name as department, employee.Name as employee , employee.Salary
from department , employee
where department.id = employee.DepartmentId 
and  employee.Salary in 
(select max(Salary) from employee
group by DepartmentId);

项目八

创建seat表

create table seat
(id int not null primary key,
Student varchar(255));
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 * from seat;

select (case 
when (select count(*) from seat)%2=1 and id=(select count(*) from seat) then id
when id%2=0 then id-1 
else id+1
end) as id, Student from seat
order by id;

项目九

创建 Scores表

create table Scores
(id int not null primary key,
Score float);
Insert into Scores  values(
1, 3.50);
Insert into Scores values(
2, 3.65);
Insert into Scores values(
3, 4.00);
Insert into Scores values(
4, 3.85);
Insert into Scores values(
5, 4.00);
Insert into Scores values(
6, 3.65);
select * from Scores;

项目十

项目十一

项目十二