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
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值