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

Posted1 by 呼延十 on September 2, 2018 Hot:

前一阵在写很多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文件

  • 导出数据库的结构及数据
    mysqldump -u 数据库用户名 -p 数据库名>导出路径及文件名
    //example
    mysqldump -u huyanshi -p mydb>~/Desktop/mydb.sql
    
  • 导出数据库中某张表结构及数据
    mysqldump -u 数据库用户名 -p 数据库名 表名>导出路径及文件名
    //example
    mysqldump -u huyanshi -p mydb mytable>~/Desktop/mydb_mytable.sql
    
  • 导出数据库全部表结构
    mysqldump -u 数据库用户名 -p -d --add-drop-table 数据库名>导出路径及文件名
    //example
    mysqldump -u huyanshi -p -d --add-drop-table mydb >~/Desktop/mydb_st.sql
    
  • 导出某张表的结构
    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)。

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

从sql文件导入

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

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

从xls文件导入

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

关键字使用

distinct

distinct的效果为去重,举例如下:
现有数据

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

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

select distinct name from record;

返回结果为:

name
zhangsan
lisi
huyan

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

select distinct name,socre from record;

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

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

group by

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

select name from record group by name

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

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

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

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

得到结果如下图:

name sum(score)
zhangsan 830
lisi 920
huyan 600

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

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

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

having 语句

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

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

该语句会按name分组查询并取到score的总和,且过滤掉总和小于650的行。结果为: name | sum(score)| —|— zhangsan | 830 lisi | 920

order by

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

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

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

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

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