MySQL 查询语句
注意:Nodejs操作Mysql 需要将字段名和变量值紧贴在一块不然会报错
0】Mysql查询并排序输出,在 limit 前面加上 order by id desc 或 order by id asc
select * from life_data where id = '12345' AND uid >0 order by id desc limit 10
1】SQL读取N条数据
select * from life_data limit 0,10 //第一条至第十条 select * from life_data limit 10,20 //第十条至第二十条
2】SQL根据id字段查询一整行数据
select * from life_data where id = '2022072412223412320221002'
3】SQL根据id字段 查询另一个字段的内容
select position from life_data where id = '2022072412223412320221002'
4】SQL根据id查询一条数据并且含第一条
select * from life_data GROUP BY id = '2022072412223412320221004'
5】SQL根据id查询往下10条数据
select * from life_data where id < '2022072412223412320221002' order by id desc limit 0,3 //向下读取3条
6】SQL根据id查询往上10条数据
select * from life_data where id > '2022072412223412320221002' order by id asc limit 0,2 //向上读取2条
7】SQL查询整表全部数据
select * from life_data
SQL·操作语句
8】SQL根据id更新某字段中的值 ‘WHERE’ 后面是条件
UPDATE `life_data` SET `idSchool` = '14', `userid` = '24' WHERE `life_data`.`id` = '123';//单单更新 insert into statistics (`id`,`shool`,`userid`) values ('202','222','') on duplicate key update `view` = `view` + 1,`user` = user + 1; //没有就创建
9】SQL根据id删除一整条数据
DELETE FROM `life_data` WHERE `life_data`.`id` = '12'
10】SQL插入数据
INSERT INTO `life_data` (`id`, `idSchool`, `userid`) VALUES ('1234', '1', '2')
11】MySQL根据id替换某字段里部分内容
UPDATE `life_data` SET `arrGood`=replace(arrGood,'1002','1004') WHERE `life_data`.`id` = '202207241222341232022103'
12】MySQL根据id对某字段内容值 +1
update `life_data` set ids = ids + 1 where id = 1;
13】MySQL根据字段 搜索存在的内容 (SQL查找内容)
select * from `life_data` where `idSchool` = '1001' AND `intShow` >0 AND `texts` like '%念得%' OR `arrLabel` like '%跑腿%' order by id desc limit 30
Mysql 统计内容数量
14】MySQL根据表单统计 全表根据id统计某内容出现数量 (返回单个结果)
SELECT count(0) FROM life_data WHERE `arrGood` LIKE '%1001%'//返回单个结果 //或 SELECT count(0) FROM life_data WHERE `arrGood` = '1001'//返回单个结果 //或 select userid,count(userid) from life_data WHERE userid='1002' //或 select `userid`,count(`userid`) as count from life_data WHERE `userid`='1002' //或 select userid,count(userid) from life_data WHERE userid like '%1001%'
15】MySQL根据表单统计 全表根据字段统计内容出现数量 (返回全部列表)
select userid,count(userid) as count from life_data group by userid //或 select userid,count(userid) as count from life_data WHERE userid like '%1001%' group by userid
16】Mysql 多表查询
select id,intShow,idSchool from ad_life where `idSchool` = '0' union all select id,intShow,idSchool from ad_like where `idSchool` = '0' union all select id,intShow,idSchool from ad_info where `idSchool` = '0' order by id desc
17】Mysql 多表查询 并自定义返回值 做标示
select id,intShow,@a:='a' from a_data where `intShow` = 2 union all select id,intShow,@a:='b' from b_data where `intShow` = 2 union all select id,intShow,@a:='c' from c_data where `intShow` = 2 order by id desc
18】MySQL 更新字段内容,没有id的条件下就增加
insert into statistics (`id`,`shool`,`userid`) values ('202','222','') on duplicate key update `view` = `view` + 1,`user` = user + 1;
19】MySQL向表中某字段后追加一段字符串
update `table_name` set `userid`=CONCAT(`userid`,'str') WHERE `id` = '202' update `table_name` set `userid`=CONCAT('str',`userid`)
20】Mysql 自定义值返回字段( 格式:’值’ AS 字段)
SELECT id,intShow,idSchool,'life' AS block,expireDate,date FROM `life` UNION ALL SELECT id,intShow,idSchool,'like' AS block,expireDate,date FROM `like` UNION ALL SELECT id,intShow,idSchool,'info' AS block,expireDate,date FROM `info` UNION ALL SELECT id,intShow,idSchool,'my' AS block,expireDate,date FROM `my` ORDER BY id DESC
1,658 Views