mysql-常用命令记录与数据导入导出

database►MySQL 评论

前一阵在写很多sql及类sql语句，发现自己的记忆力可以说是相当烂了，上午刚查过插入语句怎么写，下午就忘记了需要重新查，，而且隐隐约约的有点强迫症？？只要记得不是特别清晰，就需要去重新查，看，记一遍，十分浪费时间，因此在这里将自己用到的sql语句记下来，方便后续的查找！！

PS:以个人习惯记录，切勿直接copy执行。文中命令/方法只是本人使用过的一些，不覆盖所有，例如：数据库导入导出还有其他多种方式，有兴趣可以自行研究。

PSS:我发现我终于记住了，在写下面表格时基本没有去查了。。

PSSS: 所有命令都手动实践过(不包含拼写错误)，因此可以放心使用。

PS4: 如果大家觉得我写的不好，给大家介绍一个mysql学习的网站unoob.com ,这个网站有队mysql的基本入门介绍，且分类，示例都做的比较好。（我也是在上面学习的哈哈哈哈）。

基础语句

功能 语句 备注
插入语句 insert into 表(字段) values (值);
查找语句 select 字段 from 表 where 条件 order by 列 limit 数量 order/limit非必须
删除语句 delete from 表名 where 条件
修改语句 update 表名 set 字段=值 where 条件
去重值 select distinct 列名 from 表 where 条件 列名可以为多个，但同时作用于多个，使用需谨慎
模糊查找 like select 列名 from 表 where 列 like ‘%嘻嘻%’； 谨慎使用，有性能问题
模糊查找 contact 省略前面 where 列 contact(‘%’,#{name},’’); 用于mybatis

数据库导入与导出

导出sql文件

  • 导出数据库的结构及数据
    1
    2
    3
    mysqldump -u 数据库用户名 -p 数据库名>导出路径及文件名
    //example
    mysqldump -u huyanshi -p mydb>~/Desktop/mydb.sql
  • 导出数据库中某张表结构及数据
    1
    2
    3
    mysqldump -u 数据库用户名 -p 数据库名 表名>导出路径及文件名
    //example
    mysqldump -u huyanshi -p mydb mytable>~/Desktop/mydb_mytable.sql
  • 导出数据库全部表结构
    1
    2
    3
    mysqldump -u 数据库用户名 -p -d --add-drop-table 数据库名>导出路径及文件名
    //example
    mysqldump -u huyanshi -p -d --add-drop-table mydb >~/Desktop/mydb_st.sql
  • 导出某张表的结构
    1
    2
    3
    mysqldump -u 数据库用户名 -p -d --add-drop-table 数据库名 表名>导出路径及文件名
    //example
    mysqldump -u huyanshi -p -d --add-drop-table mydb mytable >~/Desktop/mydb_mytable_st.sql

导出excel

有时会有一些运营/hr/IT来找你让你导出一些简单的数据，这个时候呢，工具化当然是一劳永逸的方法，但是大部分时间下我们正在着急的完成新feature(改bug)，并没有时间来将数据查询工具化，那么此时，简单的给其一个excel就是一种良好的替代方法，excel也可以较为清晰的展(TA)示(men)数(xi)据(huan)。

1
2
3
mysql -u 数据库用户名 -p -e "查询语句" 数据库名>导出路径及文件名
//example
mysql -u huyanshi -p - e "select * from mytable" mydb>~/Desktop/result.xls

从sql文件导入

1.进入mysql命令行，创建数据库，使用数据库后执行source

1
2
3
4
mysql -u 数据库用户名 -p
输入密码
use mydb
source 文件路径及文件名

从xls文件导入

如果有这个需求的话，我建议写一个脚本进行导入，因为可以在脚本中保证数据的正确性，防止后续对数据进行处理时出现各种意外情况，同时，个人在遇到此需求时也都是使用脚本进行导入，对简单的命令没有研究，因此不做介绍。后续如果碰到新的需求可能会将脚本贴在这里。

关键字使用

distinct

distinct的效果为去重，举例如下：
现有数据

name score
zhangsan 600
lisi 520
zhangsan 230
huyan 600
lisi 400

假设上表是学生的考试成绩，现在我们想知道表格中共有多少个学生，即获取第一列不重复的值

1
select distinct name from record;

返回结果为：

name
zhangsan
lisi
huyan

基本用法很简单，但是distinct 有一个限制，即去重列必须是返回列，比如：

1
select distinct name,socre from record;

该语句会对(name+score)的组合进行去重，即（zhangsan，600）和（zhangsan，230）会被认为是不同的两个记录。

想要实现类似于：“name字段去重，对于重复值取分数最高”这种效果需要自己实现，distinct不可以。

group by

group by语句可以实现对数据以一列或者多列进行分组，例如可以使用group by实现distinct的功能。

1
select name from record group by name

而在实际使用过程中，group by 语句经常和函数（求和，求均值，计数等）一起使用，(毕竟如果分组不是为了统计，那将毫无意义)。语句使用规范如下： 

1
select 列名,函数 from 表名 group by 列名  having 条件

例如统计每个学生的分数之和： 

1
select name,sum(score) from record group by name;

得到结果如下图：

name sum(score)
zhangsan 830
lisi 920
huyan 600

group by语句也有类似于distinct的限制，查询的字段除了聚合函数（SUM ,AVG,COUNT…)以外 必须只能是分组的字段。即：

1
2
//错误语句
select name,score from record group by name;

会报错。其实想想，如果该语句不报错，那么同一个name对应的两个score应该返回哪一个值呢？

having 语句

having 语句一般与group 语句一起使用，作用为：对分组后的数据进行筛选，类似于where子句的语法，但是where子句作用于每一独立行，having语句作用于每一分组后的行。
示例：

1
select name,sum(score) from record having sum(score) > 650;

该语句会按name分组查询并取到score的总和，且过滤掉总和小于650的行。结果为：

name sum(score)
zhangsan 830
lisi 920

order by

order by 语句一般在其他的基础语句后连接使用，功能为对返回结果进行排序，使用规则如下：

1
select * from record order by 列名1,列名2 [ASC][DESC]

mysql 可以对多个列排序，当有多个列时，仅当第一个列相同时使用第二个列进行排序。
mysql 默认使用升序，可以使用[DESC] 来使查询结果逆序输出。
示例：

1
select * from record order by score DESC;

查询结果为：

name score
zhangsan 600
huyan 600
lisi 520
lisi 400
zhangsan 230

完。





ChangeLog

2018-09-02 添加基础语句 2018-09-04 添加数据库的导入导出 2018-09-08 添加distinct,group by,order by, having等基础语句的使用

以上皆为个人所思所得，如有错误欢迎评论区指正。

欢迎转载，烦请署名并保留原文链接。

联系邮箱：huyanshi2580@gmail.com

更多学习笔记见个人博客——>呼延十

呼延十Developer

个人简介。