目录
一、数据库操作
1、导出数据
1.1、msyqldump
mysqldump -u用户名 -p密码 -h主机 数据库名 表名 查询条件 > 导出sql文件 --skip-lock-tables
示例:
[localhost@root] # mysqldump -uroot -p123456 -hxxx.xxx db_0 user --where="age=11 and sex=0" > /data/user_20220530.sql --skip-lock-tables
1.2、客户端软件导出excel
常见客户端软化:Navicat、sqlyog、dbeaver,图形导出很简单,省略。
1.3、命令行导出excel
1.3.1、服务器命令行
mysql -h127.0.0.1 -uroot -p123123 testdb -e 'select * from my_table where id > 10' > /tmp/test_01.xls
乱码问题:
以文本方式打开xxx.xls,然后另存为,编码选择ANSI编码,保存, 再次以excel打开
1.3.2、mysql命令行客户端
select * from my_table where id>10 into outfile '/tmp/data/test_01.xls'
2、导入数据
2.1、服务端
mysql –u用户名 –p密码 -h主机 –D数据库 < sql脚本文件路径全名
注:如果文件中使用use 数据库,则-D参数忽略,,实际使用中-D不好用,报错
示例:
[localhost@root] # mysql -uroot -p123456 -haa.bb.cc < /data/sql/user_20220530.sql
2.2、客户端
source xx.sql
示例:
mysql > source /data/sql/user_20220530.sql
3、退出命令
客户端输入命令,或者错误命令想重新输入怎么办呢?
3.1、客户端
命令行中断命令 "\c 或 '\c 或exit
示例1:
mysql > '\c
示例2:
mysql > "\c
示例3:
mysql > exit
注:exit退出后需输入账号密码登录连接
二、常用sql语法
1、修改表
1.1、添加列
新增一列:
alter table TABLE_NAME add column COLUMN_NAME data_type not null;
指定位置新增列:
alter table TABLE_NAME add column COLUMN_NAME data_type not null after COLUMN_NAME;
新增到第一列:
alter table TABLE_NAME add column COLUMN_NAME data_type not null first;
如:
alter table user add column email varchar(64) not null;
1.2、修改列
修改列的定义
alter table TABLE_NAME modify COLUMN_NAME new_data_type;
修改列名称:
alter table TABLE_NAME change OLD_COLUMN_NAME NEW_COLUMN_NAME data_type;
1.3、删除列
删除列
alter table TABLE_NAME drop COLUMN_NAME;
1.4、备份表
5.5 create table t_copy select * from t;
5.7 create table t_copy like t; insert into t_copy select * from t;
2、常见sql
2.1、使用索引
select * from t where biz_id in ("xxxx");
改
select * from t use index(biz_id_idx) where biz_id in ("xxxx");
delete from t where biz_id in ("xxxx");
改
delete t.* from t use index(biz_id_idx) where biz_id in ("xxxx");
2.2、分组字符串拼接
GROUP_CONCAT 对分组后的数据进行拼接
-- 默认按照逗号分隔
select GROUP_CONCAT(biz_id) from t;
-- result: a,b,b,c,a
-- DISTINCT去重
select GROUP_CONCAT(DISTINCT biz_id) from t;
-- result: a,b,c
-- 指定分割符 SEPARATOR
select GROUP_CONCAT(biz_id SEPARATOR '|' ) from t;
-- result: a|b|b|c|a