mysql学习笔记

个人认为学习最好的办法就是实践之后总结，很长时间都在用框架，原生的sql语句几乎写不出来，这样不好。。。。。。

<1>select count(tid) as 帖子总数,FROM_UNIXTIME(t.postdate),f.name  from pw_threads t,pw_forums f  where t.fid=f.fid group by t.fid

 

<2>SELECT  f.name as fname,ff.name as ffname from `pw_forums` f,`pw_forums` ff where f.fid=ff.fup;单表自连接

 

<3>select count(tid) as total from pw_threads where fid in (SELECT f.fid from pw_forums f where f.fup=43) or fid IN(43)

 

<4>phpwind查询版块发帖信息的sql语句：

SELECT
f.fid,
f. NAME,
f.type,
f.childid,
f.fup,
f.logo,
f.descrip,
f.metadescrip,
f.forumadmin,
f.across,
f.allowhtm,
f. PASSWORD,
f.allowvisit,
f.showsub,
f.ifcms,
fd.tpost,
fd.topic,
fd.article,
fd.subtopic,
fd.top1,
fd.lastpost
FROM
pw_forums f
LEFT JOIN pw_forumdata fd USING (fid)
WHERE
f.ifsub = '0'
AND f.ifcms != 2
AND f.cms != '1'
ORDER BY
f.vieworder

说明：pw_forumdata这张表保存了论坛各板块帖子的数量信息，不需要自己写专门的sql查询数据。

 

 <5>左连接查询

select pw_forums.`name`,pw_forumdata.article from pw_forums LEFT JOIN pw_forumdata ON pw_forumdata.fid=pw_forums.fid

 

<6>mysql嵌套查询

select sum(totalNum) as ttac from (SELECT
count(tid) AS totalNum,
FROM_UNIXTIME(t.postdate),
f. NAME,
f.fid,
f.fup
FROM
pw_threads t,
pw_forums f
WHERE
t.fid = f.fid
AND t.postdate > 1246924800
AND t.postdate < 1373328000
GROUP BY
t.fid
) as dfd GROUP BY dfd.fup写着么长一个sql语句竟然没有用，不过好歹知道了mysql是支持嵌套查询的。

 

 

<6>又写了一条SQL语句，

SELECT
m.id,
m.awardee,
m. LEVEL,
m.timelimit,
i.`name`,
i.issuetimelimit,
FROM_UNIXTIME(m.awardtime),
m.action,
m.why
FROM
pw_medalslogs m,
pw_medalinfo i
WHERE
m.timelimit > 0
AND m.awardtime + timelimit * 2592000 < UNIX_TIMESTAMP()
AND m.`level` = i.id
AND m.awardee='永莲'

 

<7>sql语句中OR比较复杂的情况，加括号执行

SELECT
pw_medalslogs.id,
pw_medalslogs.awardee,
pw_medalslogs.LEVEL,
pw_medalinfo.name,
pw_medalinfo.state
FROM
pw_medalslogs,
pw_medalinfo
WHERE
(
pw_medalslogs.action IN (1, 4)
AND pw_medalslogs.state = '0'
AND pw_medalslogs.timelimit > 0
) or (pw_medalinfo.id=pw_medalslogs.level and pw_medalinfo.state=0)

 

<8>通过mysql group By查询出组内某字段的最大值：

SELECT t.mark,t.eid,t.id,MAX(value) FROM `pw_elements` t where type='usersort' GROUP BY mark;