MySQL·查询语句与MySQL操作语句统计语句多表查询

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

4,085 Views

MySQL·查询语句与MySQL操作语句统计语句多表查询

linwute

留下评论