mysql工作中用的语句

podo表：

根据域id查询201406月的收发信数量： 

select count(*) from mail_log_201406 where mail_from like ‘%@js.158pe.com‘;

select count(*) from mail_log_201406 where rcpt_to like ‘%@js.158pe.com‘;

根据域id，更改落地机：

update mailbox set host=新地址的int型转换 where domain=域id 

企业扩容和用户数：

select maxquota,maxuser from domain where domainid = 2940 ;

update domain set maxquota=13000 where domainid=2940

mop表：

user_order表，usermobile   access_app_id=‘nm‘   

查看域名和域id

mysql -h10.101.120.38 -P3308 -uroot -pcanada mop

select * from corp_mx where mx_id=‘ks-fuji.com‘;

select * from corp where corp_id  in （上一条语句查询出来的corp_id字段的值）;  

set names utf8; 更改字体

select * from corp where corp_id=(select corp_id from corp_mx where mx_id=‘lygjqgdgs.com‘);

select corp_id from corp where ecname=‘大连凯荣国际货运代理有限公司‘;

select * from corp_mx where corp_id = ‘325320‘;

mysql -h10.101.120.38 -P3308 -uroot -pcanada mop

select user_mobile from user_order where access_app_id=‘sx‘ limit 2;   手机邮箱

select user_mobile from user_order where access_app_id=‘sxcm‘ limit 2;   企业邮箱

查找用户密码：

use configdb;

select * from context where name=‘jsbchina.cn‘;

select * from context_server2db_pool where cid=5240;

use oxdatabase_8

select * from mop2oxusers where cid=5240 and uid=‘admin‘;

查看企邮和手邮的企业数：（江苏的）

mysql -uroot -p -h 10.101.120.38 -P 3308 mop

select count(*) from corp_order where access_app_id =‘jscm‘

select count(*) from corp_order where access_app_id =‘js‘

查看企邮和手邮的用户数： （江苏的）

 select distinct count(*) from uc_user where ec_code in (select distinct eccode from corp_order where access_app_id =‘jscm‘);

select distinct count(*) from uc_user where ec_code in (select distinct eccode from corp_order where access_app_id =‘js‘);

查看福建企邮的所有企业名称

mysql -uroot -pcanada  -h 10.101.120.38 -P 3308 mop -e "set names utf8;select ecname  from corp where access_app_id =‘fjcm‘">/tmp/code.txt

group by 的使用

select eccode，count(*) from user_order where eccode in (select eccode from corp where corp_id in (select corp_id from corp where access_app_id =‘fjcm‘)) group by eccode;

delete from mailbox where domainid =4849 and userid in(2038,2520,2718,2745,3085,3110,3156,3157,3159,3161,3162,3163,3164,3165,3166,3168,3169,3170,3171,3172,3173,3174,3175,3176,3177,3178,3179,3180,3181,3182,3183,3184,3185,3186,3187,3188,3189,3190,3191,3192,3193,3194,3195,3196,3197,3198,3199,3200,3201,3202,3203,3204,3205,3206,3207,3208,3209,3210,3211,3212,3213,3214,3215,3216,3217,3218,3219,3220,3221,3222,3224,3225,3226,3227,3228,3229,3230,3231,3232,3233,3234,3235,3236,3237,3238,3239,3240,3242,3244,3245,3247,3248,3249,3250,3251,3252,3253,3254,3255,3256,3257,3258,3259,3260,3261,3262,3263,3264,3265,3266,3267,3268,3269,3270,3271,3272,3273,3274,3275,3276,3277,3278,3279,3280,3281,3282,3283,3284,3285,3286,3287,3288,3289,3290,3291,3296,3297,3298,3299,3300,3301,3302,3303,3304,3305,3306,3307,3308,3309,3310,3311,3312,3313,3314,3315,3316,3317,3318,3319,3320,3321,3322,3323,3324,3325,3326,3327,3328,3329,3330,3331,3332,3333,3334,3335,3336,3337,3338,3339,3340,3341,3342,3343,3344,3345,3346,3347,3348,3349,3350,3351,3352,3353,3354,3355,3356,3357,3358,3359,3360,3361,3362,3363,3364,3365,3366,3367,3368,3369,3370,3371,3372,3374,3375,3376,3377,3378,3379,3380,3381,3382,3383,3384,3385,3386,3387,3388,3389,3390,3391,3392,3393,3394,3396,3398,3399,3400,3401,3402,3403,3404,3405,3406,3407,3408,3409,3410,3411,3412,3413,3414,3415,3416,3417,3418,3419,3420,3421,3422,3423,3424,3425,3426,3427,3428,3429,3430,3431,3432,3433,3434,3435,3436,3437,3439,3440,3441,3442,3443,3444,3446,3447,3448,3449,3450,3451,3452,3453,3454,3455,3456,3457,3458,3459,3460,3461,3462,3463,3464,3465,3466,3467,3468,3469,3470,3471,3472,3473,3474,3475,3476,3477,3478,3479,3480,3481,3482,3483,3484,3485,3486,3487,3488,3489,3490,3491,3492,3493,3494,3495,3496,3497,3498,3499,3500,3501,3502,3503,3504,3505,3506,3507,3508,3509,3510,3511,3512,3513,3514,3515,3516,3517,3518,3519,3520,3521,3522,3523,3524,3525,3526,3527,3528,3529,3530,3531,3532,3533,3534,3535,3536,3537,3538,3539,3540,3541,3542,3543,3544,3545,3546,3547,3548,3549,3550,3551,3552,3553,3554,3556,3557,3558,3559,3560,3561,3563,3564,3565,3566,3567,3568,3569,3570,3571,3572,3574,3576);

DELETE FROM tbl_name WHERE 要删除的记录