Mysql 笔记

个人笔记，记一些平时写过的mysql语句，会一直更新。

查询

select * from t_user where id=?

//分页返回个人发帖列表 page页数 pageSize每页条数 
int startNum = (page - 1) * pageSize;//startNum开始的行数
select * from t_topic where userid=? limit startNum,pageSize;

//从消息表查出所有字段，并从用户表查出发送、接收消息人的昵称和头像。
select t1.*,t2.headimgurl,t2.nickname,t3.headimgurl as bheadimgurl,t3.nickname as bnickname from t_message t1 left join t_user t2 on t1.sendid=t2.id left join t_user t3 on t1.receiveid=t3.id where t1.sendid=? or t1.receiveid=? order by t1.id desc

//查询某个用户的所有信息和发布话题数量、被评论数量、被点赞数量。
select a.*, (select count(*) from t_topic b where a.id=b.userid) as topicNum,(select count(*) from t_comment c where a.id=c.buserid) as commentNum,(select count(*) from t_like d where a.id=d.buserid) as likeNum from  t_user a where a.id=?

//通过receiveid（收消息人id）查出所有发消息人的信息（头像昵称等）和对应的未读数（status=0表示未读）
select t1.*,t.unread from t_user t1 right join (select count(id) as unread,sendid from t_message where `status`=0 and receiveid=? group by sendid) t on t1.id=t.sendid

//查询某人参与话题列表（评论过或点赞过）
select * from t_topic where id in (select topicid from t_comment where userid=14 union select topicid from t_like where userid=14)
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
UNION 合并后无重复值; UNION ALL 返回所有值。
UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每条 SELECT 语句中的列的顺序必须相同。

//查询被助力（总记录数）排名前十的用户
select count(*) as num,t1.friendid,t2.nickname,t2.headimgurl from t_help t1 left join t_user t2 on t1.friendid=t2.id group by t1.friendid order by num desc limit 0,10

//查询某用户被助力（记录条数）的排名
select count(*) from (select count(*) as num from t_help group by friendid) t where t.num>=(select count(*) from t_help where friendid=?)

//多字段查询 price+0是将varchar转换为int类型
select * from t_goods ORDER BY price+0 desc,num+0 desc,uid desc

//一个字段中包含多个id的查询语句
	t_product        t_carcity
	-------------------------------                
	id	area 	 |   id   code
	1   1,3      |   1    豫A
				 |   2    豫B
				 |   3    豫C
	-------------------------------
	结果：
	-------------------------------
	id   area    areacode
	1    1,3     豫A,豫C
	-------------------------------
SELECT p.*,(SELECT GROUP_CONCAT(c.code) FROM t_carcity c WHERE FIND_IN_SET(c.id,p.area)) AS areacode FROM t_product p where FIND_IN_SET(1,area)

//查询总分前两名学生得分
SELECT SUM(score) as sumscore,usrename FROM t_score
GROUP BY userid ORDER BY sumscore DESC LIMIT 2;

//查询商品名称滞热销状态
SELECT name,CASE 
WHEN salenum>1000 THEN '热销'
WHEN salenum<100 THEN '滞销'
ELSE '正常' END as status
FROM t_goods

插入

insert into t_user (nickname,headimgurl,time) values (?,?,?)

//不存在就插入，存在则无操作。
insert into t_user (openid,headimgurl,nickname,time,sex) select ?,?,?,?,? from dual where not exists (select * from t_user where openid=?)

修改

update t_user set headimgurl=?,nickname=? where id=?

修改saleMoney小于targetMoney的记录
update t_project set status=2 where saleMoney<ifnull(targetMoney,saleMoney+1) and status=0

修改saleMoney大于targetMoney的记录
update t_project set status=1 where saleMoney>ifnull(targetMoney,saleMoney-1) and status=0

删除

delete from t_user where id=?

函数

SELECT ROUND('1234.56',1),ROUND('1234.52',1),ROUND('1234.56',0),ROUND('1234',1)
---> 1234.6   1234.5   1235   1234.0

1234.51 -> 1234.5     1234.58 -> 1234.6
SELECT CAST('1234.51' AS DECIMAL(12,1)) AS num