连接mysql:
mysql -uroot -proot -h127.0.0.1 dbname
-u用户名
-h远程主机名(本地可不用)
-p密码
显示所有库:
show databases;
显示所有表:
show tables
显示表task的字段:
desc task
显示建表语句:
show create table task;
修改表的字段类型:
alter table conf_chain_shop_copy
modify kind varchar(61)
字符串替换:
update tablename
set obj = replace(obj,'268435456','536870912')
where rule_id = 'CK_RD_NAV_REGIONAL_10';
其中,obj为待处理字符串,'268435456'为需要被替换的串, '536870912'为目标串
字符串链接:
concat('hello','yellow');
竖排查看一条字段,在查询语句尾部加上 \G
select * from tablename where rule_id like 'MYID_184_1' \G;
连表检查:
select t1.id,t1.subject,t1.kind,t2.kind
from conf_chain_shop_copy as t1 join conf_chain_shop_new_copy as t2
on t2.id = t1.id
where 0= position(t1.kind in t2.kind);
连表更新:
update
conf_chain_shop_copy as t1 join conf_chain_shop_new_copy as t2
on t1.subject = t2.subject
set t1.id = t2.id
增加字段:
alter table conf_chain_shop add mark varchar(45);
alter table rule_check_all add redundancy_log int(11) not null default '0' comment '0不存在冗余,1存在冗余';
时间相关:
datetime 类型计算时间差 timediff(endtime,starttime):
select rule_id,timediff(end_time,start_time) as time
from dp_task_detail
where task_id in('1105968','1106033')
order by time;
显示库表字段等信息:
http://www.cnblogs.com/zhwl/archive/2012/08/28/2660532.html
简单正则(regexp):
SELECT rule_id,descption FROM `mytable_all` WHERE descption regexp 'NAV_XX_FORM|nav_xx_form'
导出整个库
mysqldump -h168.111.54.43 -uroot -p'iampassword' data_process>data_process_Jan9.sql
导出一个表
mysqldump -h168.111.54.43 -uroot -p'iampassword' data_process -t rule_xx_all>rule_xx_all.sql
导出某个表的部分数据:
五元组后街表名 --where = '条件'
mysqldump -uroot -proot mydbname task_statistics --where = 'task_mid = 58464';
导出查询内容到excel:
select * from rule_check_all where in_out = 0 and enable = 'yes' and category = 'daolu' into outfile '/home/map/users/hzq/dd/hzq2.xls'
如果某字段是中文,且编码是utf8,导出excel之后该中文会变成乱码,需要转换一下,比如rule_check_all表中的rd为中文字符,可以convert(rd using gb2312)
select rule_id,convert(rd using gb2312) from rule_xx_all where in_out = 0 and enable = 'yes' and category = 'daolu' into outfile '/home/map/users/hzq/dd/hzq3.xls';
linux查看mysql 命令历史:
history|grep mysql
注释:
行注释:
#注释的内容
-- 注释的内容,注意,--之后跟着一个空格
段注释:
/*注释的内容*/
例子:
SELECT
/*rule_id,*/
hzq_rule_type
FROM `update_history` limit 10;
创建数据库:
create database hzq_data_process;
使用数据库:
use hzq_data_process
创建视图:
create view rule_hzq as select * from rule_xx_all where enable = 'yes' and category = 'ROAD';
创建表:
create table checkout_list_hzq( id bigint(20) primary key NOT NULL AUTO_INCREMENT COMMENT '执行记录编号', rule_id char(50), pri char(10) ); |
插入记录:
insert into checkout_list_hzq (id,rule_id,pri) values ('1','hzq_CONN','P1'), ('2','hzq_LEVEL_7','P0'), ('4','hzq_LEVEL_1','P0'); |
在顺德内网的规则库可能有些差别(表明和字段名需要加上特殊的单引号`),插入语句如:
INSERT INTO `rule_hzq_suite` (`id`, `title`, `hzq_chn`, `xxx`, `category`, `version`, `flag`) VALUES
('HZQ_FACE', '关系记录检查', '检查', 'HZQ_EXP_IMP_NEW', 'BACK', NULL, 0);
查看触发器
SELECT * FROM information_schema.`TRIGGERS`;
函数:
mysql> delimiter // — 将提交符号改为//,默认为;
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
-> BEGIN
-> SELECT COUNT(*) INTO param1 FROM t;
-> END;
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL simpleproc(@a); – 调用函数
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @a ;
+------+
| @a |
+------+
| 3 |
+------+
1 row in set (0.00 sec)