mysql查询语句速查

中午得睡会儿

已于 2024-07-09 10:11:23 修改

阅读量1.3k

点赞数

文章标签： sql 数据库

于 2022-05-31 08:19:23 首次发布

本文链接：https://blog.csdn.net/qq_41975451/article/details/124969277

版权

1.YEARWEEK

MySQL 的 YEARWEEK 是获取年份和周数的一个函数，函数形式为 YEARWEEK(date[,mode])
例如 2010-3-14 ，礼拜天

SELECT YEARWEEK('2010-3-14') 返回 11 
SELECT YEARWEEK('2010-3-14',1) 返回 10

查询当前这周的数据

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 name,submittime from enterprise  where date_format(submittime,'%Y-%m')=date_format(now(),'%Y-%m')

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

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

查询上个月的数据

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 下月第一天;

查询当天、本周，本月，上一个月的数据

--今天
select * from 表名 where to_days(时间字段名) = to_days(now());

--昨天
SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) <= 1;

--近7天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= date(时间字段名);

--近30天
SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(时间字段名);

--本月
SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' )

--上一月

SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( 时间字段名, '%Y%m' ) ) =1

--查询本季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(now());

--查询上季度数据
select * from `ht_invoice_information` where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

--查询本年数据
select * from `ht_invoice_information` where YEAR(create_date)=YEAR(NOW());

--查询上年数据
select * from `ht_invoice_information` where year(create_date)=year(date_sub(now(),interval 1 year));

--查询当前这周的数据
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;

2. WEEKOFYEAR

weekofyear函数是计算出当前日期所在周数，和YEARWEEK(‘日期’,1)的周数一致，但YEARWEEK(‘日期’,1)在小于10的时候，不带0
select WEEKOFYEAR(‘2016-2-2’); =5

3、IF()函数的使用

IF(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，则返回expr3的值。

SELECT IF(TRUE,'A','B');    -- 输出结果：A
SELECT IF(FALSE,'A','B');   -- 输出结果：B

4、IFNULL()函数的使用

IFNULL(expr1,expr2)，如果expr1的值为null，则返回expr2的值，如果expr1的值不为null，则返回expr1的值。

SELECT IFNULL(NULL,'B');    -- 输出结果：B
SELECT IFNULL('HELLO','B'); -- 输出结果：HELLO

5、NULLIF()函数的使用

NULLIF(expr1,expr2)，如果expr1=expr2成立，那么返回值为null，否则返回值为expr1的值。

SELECT NULLIF('A','A');     -- 输出结果：null
SELECT NULLIF('A','B');     -- 输出结果：A

6、ISNULL()函数的使用

ISNULL(expr)，如果expr的值为null，则返回1，如果expr1的值不为null，则返回0。

SELECT ISNULL(NULL);        -- 输出结果：1
SELECT ISNULL('HELLO');     -- 输出结果：0

7、DATE_FORMAT()函数的使用

ISNULL(expr1,expr2)，如果expr1的值为时间，expr2的值为时间格式

SELECT DATE_FORMAT(SYSDATE(),"%Y-%m-%d")        -- 输出结果：2021-07-06

补充一个mybatis语法

        SELECT
            <choose>
                <when test='dimension == "year"'>
                    DATE_FORMAT(create_time,'%Y')
                </when>
                <when test='dimension == "month"'>
                    DATE_FORMAT(create_time,'%Y-%m')
                </when>
                <otherwise>
                    DATE_FORMAT(create_time,'%Y-%m-%d')
                </otherwise>
            </choose>  as abscissa

8、FIND_IN_SET()函数的使用

FIND_IN_SET(expr1,expr2)，如果expr1的值为查询的字段，expr2中按，分割后的数组如果包含expr1，则会返回

1.数据库中有一个表sys_dept,其中的ancestors很关键，它可以使我们省去递归查询子数据的操作
在这里插入图片描述
2.使用下面的语句查询


--mysql语句
select * from sys_dept where find_in_set('100A', ancestors)

结果
在这里插入图片描述

9、any(),in(),some(),all()函数的使用

any,in,some,all分别是子查询关键词之一，

any 可以与=、>、>=、<、<=、<>结合起来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的任何一个数据。
all可以与=、>、>=、<、<=、<>结合是来使用，分别表示等于、大于、大于等于、小于、小于等于、不等于其中的其中的所有数据。

select s1 from t1 where s1 > any (select s1 from t2);

假设表t1中有一行包含(10)，t2包含(21,14,6),则表达式为true；如果t2包含（20,10），或者表t2为空表，则表达式为false。如果表t2包含（null，null，null），则表达式为unkonwn。

all的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”
例如：

select s1 from t1 where s1 > all(select s1 from t2);

假设表t1中有一行包含（10）。如果表t2包含（-5,0，+5），则表达式为true，因为10比t2中的查出的所有三个值大。如果表t2包含（12,6，null，-100），则表达式为false，因为t2中有一个值12大于10。如果表t2包含（0，null，1），则表达式为unknown。如果t2为空表，则结果为true。

语句some是any的别名，用法相同。

10、EXISTS()函数的使用

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False

select m.id , m.name, m.pid, m.url,m.levels,m.icon,m.rank
        from sys_menus m
        where m.is_del = '0' and m.is_enable = '1'
        AND EXISTS (
        SELECT
            1
        FROM
            sys_user_role ur,
            sys_role_menu rm
        WHERE
            ur.role_id = rm.role_id
            AND ur.user_id = '6a1360933bb84af8a5599ae753b63d99'
        AND m.id = rm.menu_id
        )
    ORDER BY m.levels, m.rank;

11.MySQL的INNODB引擎-主键重置问题

mysql重启后自增计数器(auto-increment)丢失问题.
该问题是在mysql8.0版本才修复的，

「innodb 引擎(低版本)：Innodb 表中把自增列作为主键 ID 时，自增列是通过 auto-increment 计数器实现的，计数器的最大值是记录到内存中的，重启数据库后，会导致 auto-increment 计数器重置，从而会导致主键 ID 重置。」

「MyISam 引擎：MyISAM 表会把自增列(auto-increment 计数器)最大值是记录到数据文件里，重启 MySQL 自增列(计数器)最大值不会丢失，从而使用自增列作为主键 ID 时也不会丢失。

12、concat()以及group_concat的使用

1.语法：concat(str1, str2,…)

SELECT CONCAT("小红","年龄","12")

在这里插入图片描述
2.语法：concat_ws(separator, str1, str2, …)

SELECT CONCAT_WS(",","小红","年龄","12")

在这里插入图片描述
3.语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

在这里插入图片描述

SELECT GROUP_CONCAT(id order by id asc separator ',') FROM `ryxx` GROUP BY xbdm;

在这里插入图片描述

SELECT GROUP_CONCAT(CONCAT_WS('-',id,xm)order by id SEPARATOR ',' ) FROM `ryxx` GROUP BY xbdm;

在这里插入图片描述

13、CASE WHEN THEN END的使用

SELECT
	GROUP_CONCAT(
CASE
	sex 
	WHEN '0' THEN
	CONCAT( name, '_', status) 
	WHEN '1' THEN
	CONCAT( name, '=', status) 
	END)
	detail
FROM
	`t_user`

在这里插入图片描述

14、表迁移

insert into table1 select * from table2;

/*例子*/
INSERT INTO `ysms`.`sys_map_point_camera` ( `point_id`, `camera_no`, `camera_name`, `is_del`, `create_time` ) SELECT DISTINCT  p.`id` ,p.`camera_no` ,CONCAT( p.`name`, '摄像头' ) ,'0' ,p.`create_time` FROM `ysms`.`sys_map_point` p  WHERE p.`camera_no` IS NOT NULL;

15、新增删除字段和表

/*新增字段*/
ALTER TABLE people ADD COLUMN name VARCHAR(100) DEFAULT NULL COMMENT '姓名' AFTER age;

/*修改字段类型和描述*/
ALTER TABLE people modify name VARCHAR(100) DEFAULT NULL COMMENT '注释'; 
/*修改字段类型和描述以及字段名，将字段name1改成name*/
ALTER TABLE people CHANGE name1 name varchar(100) COMMENT '注释';

/*删除字段*/
ALTER TABLE `ysms`.`sys_map_point` DROP  COLUMN `camera_no`;

/*删除表*/
DROP TABLE table_name ;

16、根据字段删除重复数据

DELETE FROM gjryinfo
WHERE
	id IN ( SELECT a.id FROM ( SELECT *, count( txsjbsm ) AS count FROM gjryinfo GROUP BY txsjbsm HAVING count > 1 ) a );

17、给定经纬度，查询以此经纬度为圆心，周围X公里内的数据

一、
select
(
6371* ACOS(
COS(RADIANS(MyLatitude))
*COS(RADIANS(latitude))
*COS(RADIANS(longitude)-RADIANS(MyLongitude))+SIN(RADIANS(MyLatitude))
SIN(RADIANS(latitude))
)
)as distance,mdp.
from map_device_point mdp
having distance<X

二、

select
st_distance_sphere(point(MyLongitude,MyLatitude),point(longitude,latitude))/1000
as distance,
mdp.point_name as name,
mdp.device_code as cameraIndexCode,
mdp.*
from map_device_point mdp
having distance <= X

字段解释: MyLatitude=圆心的纬度; MyLongitude=圆心的经度; latitude=表中纬度的字段名; longitude=表中的经度字段名;x=要查询的范围(千米为单位); distance=距离圆心的位置(千米为单位)

/*例子一*/
select 
(
	6371* ACOS(
		COS(RADIANS(33.374413))
		*COS(RADIANS(latitude))
		*COS(RADIANS(longitude)-RADIANS(120.188939))+SIN(RADIANS(33.374413))
		*SIN(RADIANS(latitude))
		)
	)as distance,mdp.*
from map_device_point mdp
having distance<20

/*例子二*/
 select
      st_distance_sphere(point(120.188939,33.374413),point(longitude,latitude))/1000
      as distance,
      mdp.point_name as name,
      mdp.device_code as cameraIndexCode,
      mdp.*
      from map_device_point mdp
      having distance <= 20

三、mariadb添加st_distance_sphere方法函数

CREATE DEFINER=`root`@`%` FUNCTION `st_distance_sphere`(`pt1` POINT,
	`pt2` POINT
) RETURNS decimal(18,8)
    COMMENT '计算球面距离'
BEGIN
    return 6371 * 2 * ASIN(SQRT(
     POWER(SIN((ST_Y(pt2) - ST_Y(pt1)) * pi()/180 / 2),
     2) + COS(ST_Y(pt1) * pi()/180 ) * COS(ST_Y(pt2) *
     pi()/180) * POWER(SIN((ST_X(pt2) - ST_X(pt1)) *
     pi()/180 / 2), 2))) * 1000;
    END

18、查询表中的所有列名（字段名），并以逗号分隔

--获取表明逗号分割--
SELECT GROUP_CONCAT(COLUMN_NAME SEPARATOR ",") FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = '数据库名' AND TABLE_NAME = '表名';

--获取数据库中的所有表--
select table_name,table_comment from information_schema.tables where table_schema='数据库名';
--获取表中的所有字段--
select column_name,column_comment,column_type,column_key,column_default from information_schema.columns where table_schema='数据库名'' and table_name='表名'

19、TIMESTAMPDIFF 与 DATEDIFF 计算时间差函数

TIMESTAMPDIFF函数，有参数设置，可以精确到天（DAY）、小时（HOUR），分钟（MINUTE）和秒（SECOND），使用起来比datediff函数更加灵活。对于比较的两个时间，时间小的放在前面，时间大的放在后面。

--相差3年--
select TIMESTAMPDIFF(YEAR, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
--相差0月--
select TIMESTAMPDIFF(MONTH, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
--相差1天--
select TIMESTAMPDIFF(DAY, '2018-03-20 23:59:00', '2015-03-22 00:00:00');
select DATEDIFF("2021-12-31","2021-12-30") 
--相差49小时
select TIMESTAMPDIFF(HOUR, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差2940分钟
select TIMESTAMPDIFF(MINUTE, '2018-03-20 09:00:00', '2018-03-22 10:00:00');
--相差176400秒
select TIMESTAMPDIFF(SECOND, '2018-03-20 09:00:00', '2018-03-22 10:00:00');

20、关联表进行修改

UPDATE tp_rollback ttr,
ts_user tu 
SET ttr.audit_user_id = ttr.updater,
ttr.audit_user_name = tu.real_name 
WHERE
	ttr.audit_status != '1' 
	AND ttr.updater IS NOT NULL 
	AND ttr.updater = tu.pid;

21、根据备注查表

select * from `jeeplus-vue-base`.`TABLES` where table_comment like '%教案%' 
and table_schema ='usc';

22、根据备注查字段

select * from `information_schema`.`COLUMNS` where column_comment like '%销售%'
and table_schema ='usc' and table_name='tt_pub_stock';

23、根据字段名查表

-- 根据字段名查表（包含某字段的所有表）
SELECT DISTINCT TABLE_NAME
FROM information_schema.COLUMNS
WHERE COLUMN_NAME = 'professional_Id' AND TABLE_SCHEMA='school_management' ;

24、清空表

truncate  jz_ov_collegejz_ov_student

25、复制表

-- mytbl_new 是你的新表
CREATE TABLE jz_ov_patent_person LIKE graduate_management.jz_ov_patent_person;
-- A.mytbl  是A数据库（要复制的数据库） mytbl（要复制的表）  表
INSERT mytbl_new SELECT * FROM A.mytbl;

26、月度查询

----- 月度查询
SELECT SUM(xh.duration) AS value, MONTH(xh.create_date) AS name
FROM xc_history xh
WHERE
 1=1 AND DATE_FORMAT(xh.create_date,'%Y') = DATE_FORMAT(SYSDATE(),'%Y') AND xh.module IN ('xxja', 'xxzl', 'xxkc')
GROUP BY DATE_FORMAT(xh.create_date,"%Y%m")
ORDER BY DATE_FORMAT(xh.create_date,"%Y%m")

27、表结构查询

SELECT
    COLUMN_NAME 列名,
    COLUMN_TYPE 数据类型,
    DATA_TYPE 字段类型,
    CHARACTER_MAXIMUM_LENGTH 长度,
    IS_NULLABLE 是否为空,
    COLUMN_DEFAULT 默认值,
    COLUMN_COMMENT 备注 
FROM
    INFORMATION_SCHEMA.COLUMNS
WHERE table_schema='baishan_community'          
      and table_name  = 'basis_person_info_work_information'

28、时间处理的函数

DATE_SUB(date,INTERVAL expr type) 函数从日期减去指定的时间间隔。date 参数是合法的日期表达式。expr 参数是您希望添加的时间间隔。type 是时间单位（年月日时分秒）

a.reporting_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)

比较的单位interval可以为以下数值
FRAC_SECOND。表示间隔是毫秒
SECOND：秒
MINUTE：分钟
HOUR：小时
DAY：天
WEEK：星期
MONTH：月
QUARTER：季度
YEAR：年

29、if 判断

select  IF ( '2' = bei.source, p.people_name, u.nick_name ) reportPersonName

30、EXPLAIN

Explain是Mysql自带的sql执行的性能分析器。

EXPLAIN select * from

31、SHOW PROCESSLIST 连接数查询

SHOW PROCESSLIST

32、自动排序

窗口函数--

rank()：是并列排序，会跳过重复序号
dense_rank()：是并列排序，不会跳过重复序号
row_number()：是顺序排序，不跳过任何一个序号，就是行

SELECT
	score,
	(
		DENSE_RANK() OVER (
			ORDER BY
				Score DESC
		)
	) AS "rank"
FROM
	Scores;

中午得睡会儿

关注

0
点赞
踩
6

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫