mysql的常用命令、函数等

> SELECT * FROM test.person INTO OUTFILE '/tmp/test.sql';  #在mysql命令行可以执行，不过需要用户权限比较大  (INTO…OUTFILE)
> delete from 表名  where 字段值='';  #删除表里某一行数据
> update 表名  set  字段值='新值' where 字段值='';  #更新某个字段值
> insert into 表名(username,domain,groupid,description) values('12345678911','0.0.0.0',1004,'did');     #加列名，插入数据
> insert into 表名 values('12345678911','0.0.0.0',1004,'did');   #不加列名，插入数据，需要和列匹配
> create table user_bak like user; #备份表结构
> insert into user_bak select * from user; #备份表数据
> select database() #查看当前是哪个数据库
> ALTER table 表名 add 新字段名 数据类型 [约束条件] after 已经存在的字段;  #添加字段,在指定的字段后面添加新的字段，默认是在最后加
> DESC  表名;              #查看表结构
> drop table  表名;        #删除表
> truncate table 表名;     #清空表数据
> SHOW PROCEDURE STATUS;   #查看所有存储过程
> show processlist；       #查看mysql的进程列表
> drop database 库名;      #删除数据库
> source /tmp/xxx.sql;      #执行已经写好的sql文件
> select * from 表名 limit 0,10;    #查看前10行内容
> rename table 表名 表名_new；       #重命名表
> delete from 表名 where 字段值 in/not in (1,2,3,4);    #使用in/not in删除数据
> delete from 表名 where 字段值 in (select id from 表名 where xxid in (0,1,2));   #删除和查询结合使用
> update 表名 set 字段1 = (select 字段1 from (select 字段1 from 表名 where id='xx') as subquery) where id='xxx';  
#mysql不允许update语句中直接引用正在被更新的表。所以使用子查询，把结果用别名或者虚拟表展示，之后正常update
> select * from 表名 where 字段值 like '%xx%';    #通过like模糊匹配查询，%表示任意多个字符
> create table `username` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `number` varchar(64) NOT NULL DEFAULT '', `status` int(10) unsigned NOT NULL DEFAULT '0', `city` varchar(64) NOT NULL DEFAULT '', `tag` varchar(64) NOT NULL DEFAULT '', PRIMARY KEY (`id`), );

#mysqldump  -h IP -u账户 -p -q --set-gtid-purged=OFF 库名 > /opt/ter.sql       备份数据库,不备份gtid(实际是备份的当时sql命令)
#mysqldump -h IP -u账 -p密码 q --set-gtid-purged=OFF 库名 表名 > /opt/ter.sql   备份表
#mysqldump -h ip -u账号 -p密码 --opt --skip-extended-insert 库名 表名 > /opt/iner.sql  输出这个表的insert语句，--opt包含很多，默认开启。--skip-ex，导出多个insert语句，默认一个
#mysqldump -h ip -P 端口 -u账号 -p密码 --no-create-db --no-create-info 库名 表名 --skip-triggers --skip-extended-insert --set-gtid-purged=OFF -w "id='xx'" --result-file=/tmp/xx.sql
-w 后加查询条件，这个命令可以导出满足条件的内容的相关insert语句文件，该文件可以直接导入mysql数据库
-P 端口，不加默认用3306 
#mysql -h ip -P 端口 -u账号 -p密码 --force --database=库名 < /tmp/xx.sql   可以进行数据迁移
--force 当前sql语句执行错误不会直接退出，可以继续执行后续的sql语句
#mysql -h -u -p -d -e "source /tmp/test.sql" > /tmp/test.csv   -d 库名 -e 接sql语句，可以写成一个sql文件，然后再加载 之后可以导出成一个excel文件
#mysql -h -u -p -d -se "select * from 表名"    -e和-se的区别是查出来的数据有没有外面的虚框，-se会去掉虚框，赋值给变量后可以直接调用 
--skip-column-names 该参数可以去掉表头字段(-N也可以去掉表头字段)，结合-se使用，可以把查询内容直接重定向到文本文件

shell中mysql查出来的值赋值给变量
abc=$(mysql -u$user -p$pass -h$host -se "select xxx from database.xxx")
abc=`mysql -u$user -p$pass -h$host -se "select xxx from database.xxx"`
echo $abc

ifnull函数，数据库查询出来的结果是null，返回set2，不是null返回set1
当是null时，可以替换为0，方便在shell中使用，避免在where子句中使用，影响性能
select ifnull(set1,set2) from xxx;

>select * from table1 a,table2 b where a.field1 = b.field2;  #联表查询
>rename table 表old to 表new;            #修改表名
>alter table 表old rename to 表new;      #修改表名
>explain sql语句;
重点参数type  结果从好到坏 
system> const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > all
一般查询至少达到range，最好达到ref
filtered  符合条件的记录数的百分比
>show create table 表名;            #查看建表语句
>insert ignore into ...;           #插入数据时忽略重复数据(主键和唯一键)，后续数据可以正常执行
>select * from table1 limit 0,10;  #limit是指初始位置，显示的行数，第一个初始位置是0，limit 0,10  表示从第一行开始，显示10行