mysql在cmd下启动/导出操作
开启
1.设置环境变量
将mysql安装位置对应下的bin文件夹的位置复制到path之中
2.开启服务
net start mysql57(服务名)
3.进入mysql
mysql -hlocalhost -uroot -p 回车
其中-h代表的是服务器,-u代表用户,-p代表密码
操作
1.查看MySql的导入与导出的目录
show variables like '%secure%';
2.mysql数据导出为csv文件
导出到哪里;怎么导,从哪里导出
1.导出顺序
SELECT INTO OUTFILE FROM
#1---需要导出什么(其实已经在括号设置好了,因为全部数据导出是在对表已经操作的情况下实行的)
SELECT *
#2---从哪里导出(实际上先对旧表movie进行操作,采用交操作,实现属性名的重新设置(也可以不用更新)新旧属性名要一一对应,之后命名为新表movie_,实际上就是复制了一份,对原先的表没有修改)
FROM (select 'mid','title','introduction','rating','releasedate' union select*from movie) movie_ #movie_就是代表要输出的表(只不过以属性的形式输出)
#3---导出到哪里,导出到movie.csv文件中,旧表不能直接导入到movie.csv文件,因为会直接破坏旧表,不利于mysql的数据库的维护.
#注意输出的地址斜杠是/,不是windows下的\,千万注意
INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/movie.csv'
#4---设置字符编码--防止乱码一般就是GBK或者utf8
character set utf-8
#5----导入规则,下面就是新表中字段导入csv表格当中的规则.
# FIELDS之间分隔的标志就是",",遇到这个前面的就为一个字段.例如
#"我爱你","20","相貌平常,经常耍流氓!哈哈" mysql数据格式
#| id | name | age | description |
#+----+----------变为----------------------------+
#| 1 | 我爱你 | 20 | 相貌平常,经常耍流氓!哈哈 | csv数据格式
FIELDS TERMINATED BY ','
#代有双引号的词组,要先将""去除,之后放入csv文件中如上面的:
#"我爱你"--->| 我爱你 |
OPTIONALLY ENCLOSED BY '"' #意思就是双引号可选择关闭
#例如电影当中的简介,一般比较长,遇到换行\r,那就说明简介完成了,就可以把它设置为一个属性.
LINES TERMINATED BY '\r';
as的用法,起别名,给已知的列起别名
引出一个代码:
SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/person.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
FROM (
select 'pid','birth','death','name','biography','birthplace'
union
select person_id,person_birth_day,person_death_day,
case
when person_name is then person_english_name
else person_name end
as name,person_biography,person_birth_place
from person
) person_;
涉及到两个知识点
2.SELECT 属性名 ,case when then else end as 属性名 FROM 表名
上面当中:
select person_id,person_birth_day,person_death_day, #特别注意这个逗号,
case
when person_name is then person_english_name
else person_name end
as name,person_biography,person_birth_place
from person
旧的属性名(旧表person的)
person_id,person_birth_day,person_death_day,#直接选出的
name ,person_biography,person_birth_place #其中name是经过case when then else end这个条件选出的属性(用as生成的),其他的属性person_biography,person_birth_place则是直接选出,这点尤其要注意
#两部分的属性都是在旧表person中选择的
新表(person_)的属性
select 'pid','birth','death','name','biography','birthplace'
并且他们一一对应,–>所以我们一开始的select不是重点所以只需要select *就可以,主要是为了规范
3.union与union all
两者都是集合操作,这就要求操作的两个表的结构相同,但union是小并操作(消除重复),union all是大并操作(不消除重复),但两者都是对这两张表的结合(为了便于说明,对如下代码进行了删减进),
SELECT * INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/person.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
FROM (select 'pid','birth','death'
union
select person_id,person_birth_day,person_death_day from person
) person_;
其数据导出的主要过程如下:
先从旧表person当中选取各种属性,之后在新表(person_)之中建立对应的属性,
然后进行一个小并操作(个人自定义,便于描述),因为新建的表是属性值都为空,加上与旧表的小并操作,,其实就相当于把旧表复制给了新表,
然后新表根据导入规则,将表中的数据进行导入到csv文件当中,这样就实现了mysql数据的导出到csv文件当中的过程
4.case函数
case可以嵌套
主要是解决不同情况的的不同输出.减少代码量.
选修多门课程的学生,要选择一门课程作为主修,主修flag里面写入 Y。只选择一门课程的学生,主修flag为N(实际上要是写入Y的话,就没有下面的麻烦事了,为了举例子,还请多多包含)。
只选修一门课程的人,返回那门课程的ID
选修多门课程的人,返回所选的主课程ID
SELECT std_id,
CASE WHEN COUNT(*) = 1 --只选择一门课程的学生的情况
THEN MAX(class_id)
ELSE MAX(CASE WHEN main_class_flg = 'Y'
THEN class_id
ELSE NULL END
)
END AS main_class
FROM Studentclass
GROUP BY std_id;
运行结果
STD_ID MAIN_class
—— ———-
100 1
200 3
300 4
400 5
500 6
case函数注意事项
先看一段代码:(两个易错点)
CASE col_1
WHEN 1 THEN 'Right'
WHEN NULL THEN 'Wrong'
END
其中’Wrong’是错误的,所以永远不会出现Wrong的情况,正确格式为
WHEN NULL THEN 'Unknown'
也可以写成
#WHEN col_1=NULL THEN 'Unknown' 是错误的
WHEN col_1 IS NULL THEN 'Unknown' #才是正确的
没有”=”千万要注意,一般col_1 IS就代表不为空
5.select as的用法
as的用法,起别名,给已知的列起别名
select id,column1 * column2 as column from table1
select count(*) as total from table1
另外注意:select Product as Description, Price, Image from test
as的作用范围为“as到它后面的第一个逗号为止”。其后面的“price,image”是select 选取的表中的其他字段。
完整的语句:
select Product as Description, Price, Image from test
查询结果为:
| product | Price | image |
| aa | 1.00 | :) |
完整编码
use movie;
#导出电影类型信息
select *
from(
select 'gid','gname' #带''是为了创建新表的属性.用字符串代表
union
select * from genre
)genre_
#输出地址包括具体的存储文件,不要写错了是csv,不是cvs,即使写错了,改下后缀名同时可以.
into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/genre.csv'
character set utf8 #设置好编码格式,否则默认是gbk
fields terminated by ','
optionally enclosed by '"';#去掉双引号在存储到csv中
#导出电影信息
select *
from(
select 'mid','title','introduction','rating','releasedate'
union
select * from movie
)movie_
into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/movie.csv'
character set utf8
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r';
#导出电影所属类型信息
select *
from(
select 'mid','gid'
union
select * from movie_to_genre
)movie_to_genre_
into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/movie_to_genre.csv'
character set utf8
fields terminated by ','
optionally enclosed by '"';
#导出演员信息
select *
from(
select 'pid','birth','death','name','biography','birthplace'
union
select person_id,person_birth_day,person_death_day,
case
when person_name then person_english_name #记住这里千万没有单独 is,只有is null
else person_name
end
as name, #case函数作为选取person_name的条件,其函数结果用name属性表示,实现形式主要是采用as方法
person_biography,person_birth_place from person
)person_ #新表person_
into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/person.csv'
character set utf8
fields terminated by ','
optionally enclosed by '"'
lines terminated by '\r'; #
#导出演员与所演电影关系表
select *
from(
select 'pid','mid'
union
select * from person_to_movie
)person_to_movie_
into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/person_to_movie.csv'
character set utf8
fields terminated by ','
optionally enclosed by '"';