Mysql常用命令

1.查看所有数据库

Show databases;

2.查看所有账号用户，并且修改某个数据库的密码

Use mysql（只有在名为“mysql”的数据库中才有user这个表存在）;

Select * from user;

Select user from user;

修改用户密码：

Set password for XXX=password(‘mima’);

PS：，可以参考工具类别的另一篇博客。

3.操作XXX数据库的表

use XXX数据库;

查看

Show tables;

删除

Drop table XXX;

4.选择数据记录(在php代码中的写法)

$sql="select * from tb_topics  group by course_id where course_id='$cousre_id' and best=0  order by lastdate desc,time desc limit 10";

网页的简单搜索功能可以这样实现：

$query="select * from tb_courses WHERE course_name like '%$query%' group by course_id";

5.插入数据记录(在php代码中的写法)

$sql="insert into tb_replysset tid='$tid',content='$content',picture='$picture',user_id='$user_id'";

6.更新数据记录(在php代码中的写法)

$sql2="update tb_topicssetupdate_time='$create_time',comment_nums=comment_nums+1whereid='$tid'";

----------------------------------------------------20150810-------------------------------------------------------

1.sql获取当前时间并转换成秒数。

TO_SECONDS(NOW())

2.sql获取子字符串。

用到了两个函数locate和substring

locate('要查询的字符','字符所在的字符串')

eg:locate('_','test_cd');

substring('被截取的字符串',开始下标,结束下标)

eg:substring('test_cd',1,4)

两个函数可以合并使用

SELECT SUBSTRING('test_cd',1,LOCATE('_','test_cd')-1);

结果为test

3.sql的级联写法

select b.battype as battype,

count(a.batNAME) as batnumer,

sum(a.tasknumber) as tasknumber,

sum(a.scenenumber) as scenenumber,

sum(ifnull(d.SP_military_num,0)) as SP_military_num,

sum(ifnull(d.SP_civil_num,0)) as SP_civil_num,

sum(ifnull(d.SP_militaryfinal_num,0)) as SP_militaryfinal_num,

sum(ifnull(d.PP_single_num,0)) as PP_single_num,

sum(ifnull(d.EP_reco_scenenum,0)) as EP_reco_scenenum,

sum(ifnull(d.EP_trans_scenenum,0)) as EP_trans_scenenum,

sum(ifnull(d.EP_ppmnotnull_num,0)) as EP_ppmnotnull_num,

sum(ifnull(d.EP_tabA_num,0)) as EP_tabA_num,

sum(ifnull(d.EP_tabB_num,0)) as EP_tabB_num,

sum(ifnull(d.EP_resultphoto_num,0)) as EP_resultphoto_num from (

(select batname,tasknumber,scenenumber from bat_version where bat_vendor_finish_time >= '2015-07-01' and bat_vendor_finish_time <='2015-07-20' ) a left join

(select battype,batname from bat_version) b on a.batname=b.batname left join

(select batname,SP_military_num,SP_civil_num,SP_militaryfinal_num,PP_single_num,EP_reco_scenenum,EP_trans_scenenum,EP_ppmnotnull_num,EP_tabA_num,EP_tabB_num,EP_resultphoto_num from bat_info) d on b.batname=d.batname

) group by b.battype;

4.sql的关联写法

select a.type,

count(*) as successbatnum,

sum(b.tasknumber) as successtasknum,

sum(b.scenenumber) as successscenenum

from bat_log_task a, bat_version b where a.batname=b.batname and a.endtime>='2015-07-01' and a.endtime<='2015-07-20' and a.result='success' group by a.type;

5.sql的if(S,A,B)用法。

select sum( if( qty > 0, qty, 0)) as total_qty,
sum( if( qty < 0, 1, 0 )) as negative_qty_count
from inventory_product
group by product_id

6. limit a,b 表示从第a行开始的总共b行的数据。

sql="select task,type,upload_type,scenenum,state,upload_key from upload_queue where state < 100 and tocon = 'line' order by weight,create_time asc limit 2,1;"