记录一些常用的sql语句

Li--AiTao

已于 2023-06-08 17:39:26 修改

阅读量1.5k

点赞数

分类专栏：数据库 MySQL 文章标签： sql

于 2020-11-12 16:13:33 首次发布

本文链接：https://blog.csdn.net/VABTC/article/details/109646502

版权

数据库同时被 2 个专栏收录

13 篇文章 1 订阅

订阅专栏

MySQL

11 篇文章 1 订阅

订阅专栏

1、关于常用时间的查询

1.1、查询当前这周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());

1.2、查询上周的数据

SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;

1.3、查询当前月份的数据

1）select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

2）select suggest_id, count(id) as cnt from zhfw_suggest_rank

WHERE read_time >= (DATE_FORMAT(CURRENT_DATE(),'%m')) <= (DATE_FORMAT(CURRENT_DATE(),'%m'))

1.4、查询距离当前现在6个月的数据

select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();

1.5、查询上个月的数据

select name,submittime from enterprise where date_format(submittime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m')
select * from `user` where DATE_FORMAT(pudate,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m') ;
select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = WEEKOFYEAR(now())
select * from user where MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())
select * from [user] where YEAR(FROM_UNIXTIME(pudate,'%y-%m-%d')) = YEAR(now()) and MONTH(FROM_UNIXTIME(pudate,'%y-%m-%d')) = MONTH(now())
select * from [user] where pudate between 上月最后一天 and 下月第一天

1.6、查询当天、昨天、前天的数据：

SELECT * FROM zhfw_service_theme WHERE TO_DAYS(created_time) = TO_DAYS(NOW())

SELECT count(*) FROM `t_view` WHERE DATEDIFF(now(),create_time) = 0
SELECT count(*) FROM `t_view` WHERE DATEDIFF(now(),create_time) = 1
SELECT count(*) FROM `t_view` WHERE DATEDIFF(now(),create_time) = 2

2、多条件查询

多条件查询比较简单，即将相应的条件以添加AND的形式加在后面，如：

SELECT * FROM Employee 
WHERE FNumber BETWEEN 'DEV001' AND 'DEV008' 
AND FName LIKE '%J%' 
AND FSalary BETWEEN 3000 AND 6000

3、给查询结果增添行号

SELECT @ROWNO := @ROWNO + 1 AS ROWNO, T.*
  FROM (SELECT T.ARTICLE_TITLE, T1. NAME, T.ARTICLE_CREATEDATE
          FROM T_ARTICLE T
          LEFT JOIN T_ARTICLE_TYPE T1
            ON T.TYPEID = T1. NAME
         WHERE T.ARTICLE_TITLE LIKE '%博士%'
         ORDER BY ARTICLE_CREATEDATE DESC) T,
       (SELECT @ROWNO := 0) T3
 ORDER BY ROWNO

4、从第N条记录开始查询M条数据

select * from tablename limit N,M

//查询前N行记录
 -- 方法一
 select * from table1 limit 0,n;
 -- 方法二
 select * from table1 limit n;

//查询后N行记录
-- 倒序排序，取前n行 id为自增形式
select * from table1 order by id desc dlimit n；

//查询一条记录($id)的下一条记录
select * from table1 where id>$id  order by id asc dlimit 1
//查询一条记录($id)的上一条记录
select * from table1 where id<$id  order by id desc dlimit 1

5、更新语句

1、子查询更新
UPDATE C SET YJSJE = b.YJSJE from(
	SELECT SUM(sqdmx.HZJE) as YJSJE, sqd.htid from mx sqdmx LEFT JOIN ZFSQD sqd on sqdmx.ZFSQDID = sqd.ZFSQDID GROUP BY sqd.htid
)b WHERE b.htid = C.htid

2、根据主表更新子表
UPDATE FKJH set YFKJE = 0 from XX
WHERE XX.htbh = 'xxx' and TXX.ID = FKJH.HTID

3、多字段更新
update a set
   a = b.a,b=b.b,c=b.c
from t a,t b
where (a.条件1) and (b.条件2)

6、常用函数

1)CAST 函数用于将某种数据类型的表达式显式转换为另一种数据类型

SELECT *,CAST(CAST(A字段 AS VARBINARY(max)) AS VARCHAR(400)) AS B字段 FROM FJB WHERE
 FJID IN(SELECT HTID FROM HTZY WHERE HTZT = 2)
 
2)HAVING 类似于WHERE，因WHERE 关键字无法与合计函数一起使用
 
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000

3)DISTINCT 去除重复的值
SELECT DISTINCT Company FROM Orders 

4)MySQL截取的几种方法
（1）LEFT
例：left（name,2），截取左边的2个字符；
SELECT LEFT(2022,2)
结果：20
（2）RIGHT
例：right（name,2）截取右边的2个字符
SELECT RIGHT(2022,2)
结果：22
（3）SUBSTRING 和 substring_index
SUBSTRING(name,5,3) 截取name这个字段 从第五个字符开始 只截取之后的3个字符
例1：SELECT SUBSTRING('美国唐人街办事处',5,3)
结果1：办事处

SUBSTRING(name,3) 截取name这个字段 从第三个字符开始，之后的所有个字符
例2：SELECT SUBSTRING('美国唐人街办事处',3)
结果2：唐人街办事处

SUBSTRING(name, -4) 截取name这个字段的第 4 个字符位置（倒数）开始取，直到结束
例3：SELECT SUBSTRING('美国唐人街办事处',-4)
结果3：街办事处

SUBSTRING(name, -4，2) 截取name这个字段的第 4 个字符位置（倒数）开始取，只截取之后的2个字符
例4：SELECT SUBSTRING('美国唐人街办事处',-4,2)
结果4：街办

substring_index（“待截取有用部分的字符串”，“截取数据依据的字符”，截取字符的位置N）
例5：SELECT SUBSTRING_INDEX(‘192,168,8,203’,’,’,1); 
结果5：192

5）SELECT IF
例1：SELECT IF(1<2,'trur','false');
例2：SELECT IF(score>=60,’pass’,’fail’) FROM score;
例3：SELECT IF(profile LIKE '%female','female','male') gender

6）REPLACE(str,old_string,new_string)
例:SELECT device_id,replace( blog_url,'http:/url/','') as user_name FROM user_submit

7)TRIM([{BOTH|LEADING|TRAILING} [removed_str]] FROM str);
例：SELECT device_id,trim('http:/url/' from blog_url) as user_name FROM user_submit

8)AVG函数是一个聚合函数，用于计算集合的平均值
例1：SELECT avg(age) FROM `user_profile`
例2：（进阶）SELECT avg(if(age>25,1,0)) FROM `user_profile`

6）7）的结果

7、日期的加减方法

1）天数+1
 date_add('2022-04-21', interval +1 day)
2）天数-1
 date_add('2022-04-21', interval -1 day)

其中day是可变的，可以是一天，可以是一小时，可以是一年，具体参数如下：
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND
HOUR_MINUTE
DAY_MICROSECOND
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH

8、数据表的备份

SELECT * into 新表 from 老表;

MySQL快速备份表 - JaxYoun - 博客园

9、查询生成新ID方法

1）SQL Server NewID函数 (生成GUID)

使用：

SELECT NEWID() FROM `user`

insert IN TO (ID) VALUES (NEWID() )

2）MySQL的UUID（）方法

SELECT UUID(),NAME FROM `user`

INSERT IN TO (ID) VALUES(UUID())

3）pgsql的uuid_generate_v4()方法

注意这里要看是不是支持uuid_generate_v4();方法

select uuid_generate_v4();

10、SQL中OVER（PARTITION BY）详解

OVER（PARTITION BY）详解

案例1：按科目ID进行分组，并按分级码进行排序
SELECT row_number() OVER(PARTITION BY KMID ORDER BY FJM) as XUHAO, FJM, KMID,FJ1_Layer,FJ1_IsDetail,SSXMID,XMID FROM YS

案例2：给查询出的SQL记录添加序号列
方法1)：
select ROW_NUMBER() OVER (ORDER BY a.字段 ASC) AS XUHAO,a.* from table a
方法2):
 select RANK()  OVER (ORDER BY a.字段 ASC) AS XUHAO,a.* from table a

RANK（）也为每一组的行生成一个序号，与ROW_NUMBER()不同的是如果按照ORDER
BY的排序，如果有相同的值会生成相同的序号，并且接下来的序号是不连序的。例如两个相同的行生成序号3，那么接下来会生成序号5。

11、数据库判断是否存在某个字段，存在，则增加条件；若不存在则不增加

SELECT
    ht.ID AS ID 
FROM
	HTXX ht
	left JOIN HTXXBG htbg ON htbg.YHTID = ht.ID 
WHERE
	(EXISTS(SELECT 1 FROM HTXXBG WHERE htbg.YHTID = ht.ID AND htbg.HTZT != '-2' ) 
			OR NOT EXISTS(SELECT 1 FROM HTXXBG WHERE htbg.YHTID = ht.ID ) )

12、case when后如何按别名进行group by

SELECT
	dw.a,
	(
	CASE
			
			WHEN lx.bh = '01' THEN
			'1' 
			WHEN lx.bh = '02' THEN
			'2' 
			WHEN lx.bh IN ( '03' ) THEN
			'3' 
			WHEN lx.bh = '04' THEN
			'4' 
			WHEN lx.bh IN ( '05' ) THEN
			'5' 
		END 
		) AS LB,
		COUNT ( 1 ) AS SL,
		SUM ( ht.JE ) AS DJE,
		SUM ( ht.BJE + ht.FJE + ht.SJE ) AS ZJE 
	FROM
		TXX ht
		JOIN W dw ON ht.DWID = dw.ID
		JOIN LX lx ON lx.ID = ht.HTLX 
	WHERE
		ht.HTZT IN ( '3' ) 
	GROUP BY
	dw.CWDWDM,(
	CASE
			
			WHEN lx.bh = '01' THEN
			'1' 
			WHEN lx.bh = '02' THEN
			'2' 
			WHEN lx.bh IN ( '03' ) THEN
			'3' 
			WHEN lx.bh = '04' THEN
			'4' 
			WHEN lx.bh IN ( '05' ) THEN
			'5' 
		END 
	)

13、关联表，主表字段，减去关联表某个字段的和

思路：可以反向关联

select  YJSJE-SUM(JE), HTID  from SQD2023 left join TXX on HTID = TXX.ID where  ISNULL(SQD2023.HTID , '') <> '' group by HTID, YJSJE

14、SQL Server日期与字符串之间的转换

15、sqlserver update join 多关联更新

1）通过 inner join语法实现多关联更新

update a set a.name  = b.name
from product_detail a
inner join product_base_info b on a.id = b.id

2）也可以直接用where语句

update a set a.name = b.name
from product_detail a,product_base_info b
where a.id = b.id

16、多值like

	 SELECT 
	sum(c1.htzys) as htzys, b.ssxmid,b.id
FROM
	JCJSYSZCZX2023_copy1 c1 
	join (
		SELECT 
			id,fjm,ssxmid
		FROM
			JCJSYSZCZX2023_copy1
		WHERE
		 DWID = 'C376D02F-B898-464B-AFB9-0601AD5CDE63' and SSXMID = 'c93ba73f-887e-4cc6-8932-d8302edee973'
			AND YSLX != '5' AND SFXM = '0' and FJ1_IsDetail = '0' 
	)b on b.ssxmid = c1.ssxmid
WHERE
 c1.FJ1_IsDetail = '1' and c1.fjm like (b.fjm+'%')
 GROUP BY b.ssxmid,b.id

17、根据数据库A字段的值为B字段名命

SELECT 
sum( isnull(CASE  WHEN sqd.DJLXBH='09' THEN je  else 0 END,0)) AS syyfje,
sum( isnull(CASE  WHEN sqd.DJLXBH='10' THEN je  else 0 END,0)) AS syjsje,
sum( isnull(CASE  WHEN sqd.DJLXBH='11' THEN BCYLZBJ  else 0 END,0)) AS sylzbje,
sqd.DJLXBH,ht.id
FROM
 JCJSZFSQD2023 sqd 
 JOIN JCJSHTXX ht on ht.id = sqd.HTID
WHERE
 A
 GROUP BY ht.id,sqd.DJLXBH

18、对两个表查询求和后，再次求和

SELECT htxx.YDXYFJE,b.* FROM JCJSHTXX htxx 
join (
		SELECT SUM(SYDXYFJE) as TYDXYFJE,a.htid from (
				SELECT  ISNULL(sum(CHJE),0) as SYDXYFJE,sqd.htid from JCJSZFSQD2022 sqd
				join jcjshtxx ht on ht.id = sqd.htid
				WHERE sqd.DJLXBH = '10' and sqd.djzt in ( '20', '21')
				GROUP BY sqd.htid
				union all
				SELECT ISNULL(sum(CHJE),0) as SYDXYFJE,sqd.htid from JCJSZFSQD2023 sqd
				join jcjshtxx ht on ht.id = sqd.htid
				WHERE sqd.DJLXBH = '10' and sqd.djzt in ( '20', '21')
				and (EXISTS(SELECT 1 FROM JCJSZFSQD2022 s1 WHERE s1.htid = sqd.htid)  )
				GROUP BY sqd.htid
			)a WHERE 1=1
			GROUP BY a.htid
) b on b.htid = htxx.id
WHERE htxx.YDXYFJE != b.TYDXYFJE

19、查出本表中某字段重复（或者不重复的数据）

1、查找表中多余的重复记录，重复记录是根据单个字段(peopleId)来判断

代码如下:

select * from people

where peopleId in (select peopleId from people group by peopleId having count

(peopleId) = 1（或者是》1）)

20、NSERT INTO SELECT语句简介

要将其他表中的数据插入另一个表中，请使用以下SQL Server INSERT INTO SELECT语句：

批量插入、批量复制

Li--AiTao

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
记录一些常用的sql语句

查询当前这周的数据SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now());查询上周的数据SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,'%Y-%m-%d')) = YEARWEEK(now())-1;查询当前月份的数据select nam
复制链接

扫一扫

专栏目录