MySQL 15 条常用的 SQL 知识 (18/100)

2022 年 3 月 06 日
本文字数：4011 字
阅读完需：约 13 分钟

MySQL 15 条常用的 SQL 知识

平时在日常工作中会用到的 SQL 都一点一点记录下来。放在知识库里，今天整理出来分享给各位。

MySQL 中数据库名、表名、字段名使用关键字、保留字或中文的方法

（键盘数字1左侧键）是 MySQL 的转义符，避免和 mysql 的本身的关键字冲突，只要你不在列名、表名中使用 mysql 的保留字或中文，就不需要转义。所有的数据库都有类似的设置，不过mysql用的是而已。通常用来说明其中的内容是数据库名、表名、字段名，不是关键字。例如：
select from from table;第一个 from 是字段名，最后的 table 表名，但是同时也是 mysql 关键字，这样执行的时候就会报错，所以应该使用 select from from table;当然，为了便于阅读，不建议使用关键字作为字段名、表名，同时，应该对数据库名、表名、字段名用一对儿反引号包含。

查询某段时间的记录

-- 今天

select fullName,addedTime from t_user where to_days(addedTime) <= to_days(now());

-- 昨天

select fullName,addedTime from t_user where to_days(NOW()) - TO_DAYS(addedTime) <= 1;

-- 近 7 天

select fullName,addedTime from t_user where date_sub(CURDATE(),INTERVAL 7 DAY) <= DATE(addedTime);

-- 近 30 天

SELECT fullName,addedTime FROM t_user where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= date(addedTime);

-- 本月

SELECT fullName,addedTime FROM t_user WHERE DATE_FORMAT( addedTime, '%Y%m' ) = DATE_FORMAT( CURDATE() , '%Y%m' );

-- 上一月

SELECT fullName,addedTime FROM t_user WHERE PERIOD_DIFF( date_format( now( ) , '%Y%m' ) , date_format( addedTime, '%Y%m' ) ) =1;

-- 查询本季度数据

select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(now());

-- 查询上季度数据

select fullName,addedTime FROM t_user where QUARTER(addedTime)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));

-- 查询本年数据

select fullName,addedTime FROM t_user where YEAR(addedTime)=YEAR(NOW());

-- 查询上年数据

select fullName,addedTime FROM t_user where year(addedTime)=year(date_sub(now(),interval 1 year));

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

select fullName,addedTime FROM t_user where addedTime between date_sub(now(),interval 6 month) and now();

-- 查询当前这周的数据

SELECT fullName,addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now());

-- 查询上周的数据

SELECT fullName,addedTime FROM t_user WHERE YEARWEEK(date_format(addedTime,'%Y-%m-%d')) = YEARWEEK(now())-1;

-- 查询上个月的数据

select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),'%Y-%m');

-- 查询当前月份的数据

select fullName,addedTime FROM t_user where DATE_FORMAT(addedTime,'%Y%m') = DATE_FORMAT(CURDATE(),'%Y%m');
select fullName,addedTime FROM t_user where date_format(addedTime,'%Y-%m')=date_format(now(),'%Y-%m');

-- 查询指定时间段的数据

select fullName,addedTime FROM t_user where addedTime between '2017-1-1 00:00:00' and '2018-1-1 00:00:00';
select fullName,addedTime FROM t_user where addedTime >='2017-1-1 00:00:00' and addedTime < '2018-1-1 00:00:00';

————————————————

归纳一下：

1、查询时间段内的数据，一般可以用 between and 或 <> 来指定时间段。

2、mysql 的时间字段类型有：datetime，timestamp，date，time，year。

3、获取系统当前时间的函数：

   select CURDATE();
   select NOW();

复制代码

4、获取时间差的函数：

   period_diff()    datediff(date1,date2)      timediff(time1,time2)

复制代码

5、日期加减函数：

   date_sub() 


   date_add()     adddate()      addtime()


   period_add(P,N)

复制代码

————————————————

原文链接：https://blog.csdn.net/ls1645/article/details/79118464

去重统计

可以一同使用 DISTINCT 和 COUNT 关键词，来计算非重复结果的数目。

语法

SELECT COUNT(DISTINCT column(s)) FROM table

运算符

mysql 中有 4 类运算符，它们是：

算术运算符比较运算符逻辑运算符位操作运算符算术操作符

算术操作符是 SQL 中最基本的操作运算符，主要有一下几种运算符：

+（加）、 -（减）、 *（乘）、 /（除）、 %（求余或者模）

比较运算符

一个比较运算符的结果总是 1,0 或者是 NULL。MySQL 中的比较运算符有：

=、<=>、<> (!=)、<=、>=、>、IS NULL、IS NOT NULL、LEAST、GREATEST、BETWEEN . . . AND. . . 、ISNULL、IN、NOT IN、LIKE、REGEXP

逻辑运算符

逻辑运算符的求值所得结果均为 TRUE、FALSE 或 NULL。

逻辑运算符有:

NOT 或者 !AND 或者 &&OR 或者 ||XOR（异或）

更多内容详见文章： https://www.cnblogs.com/doctorJoe/p/5338048.html

另外在 mysql 中 1/2 得到 0，如何得到 0.5 呢

处理如下，只要将字段 1 的值转成 DOUBLE 或者 FLOAT 类型即可

SELECT CAST(field1 AS FLOAT)/field2 FROM TB;

联合多表查询

INNER JOIN 关键字在表中存在至少一个匹配时返回行，相当于取交集

LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为 NULL。

RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表中没有匹配，则结果为 NULL。

sql 中的 IF 条件语句的用法

IF 表达式

IF( expr1 , expr2 , expr3 )

expr1 的值为 TRUE，则返回值为 expr2 expr1 的值为 FALSE，则返回值为 expr3

SELECT IF(TRUE,1+1,1+2);

-> 2

SELECT IF(FALSE,1+1,1+2);

-> 3

SELECT IF(STRCMP("111","222"),"不相等","相等");

-> 不相等

IFNULL 表达式

IFNULL( expr1 , expr2 )

判断第一个参数 expr1 是否为 NULL：

如果 expr1 不为空，直接返回 expr1；

如果 expr1 为空，返回第二个参数 expr2

常用在算术表达式计算和组函数中，用来对 null 值进行转换处理(返回值是数字或者字符串)

在 expr1 的值不为 NULL 的情况下都返回 expr1，否则返回 expr2，如下：

SELECT IFNULL(NULL,"11");

-> 11

SELECT IFNULL("00","11");

————————————————

原文链接：https://blog.csdn.net/qq_36850813/article/details/80449860

IN 和 OR 的性能比较

结论：在查询中使用 IN 的性能要优于使用 OR 语句。

参考： https://stackoverflow.com/questions/782915/mysql-or-vs-in-performance

数据库授权命令

参考【Mysql 用户权限管理】 https://www.cnblogs.com/keme/p/10288168.html

----------创建权限--------------

-- CREATE USER 'dpplus'@'%' IDENTIFIED BY 'mypass';

-- grant select on oakvip_dpplus.card to 'dpplus'@'%';

-- grant select on oakvip_dpplus.user to 'dpplus'@'%';

-- grant select on oakvip_dpplus.namespace to 'dpplus'@'%';

-- flush PRIVILEGES

----------查看权限--------------

-- SELECT * FROM user where user="dpplus"

-- SELECT * FROM DB WHERE user="dpplus"

-- select * from tables_priv where user='dpplus'

-- select * from columns_priv where user='root'

-- show grants for dpplus@'%'

在 sql 中使用变量

示例一

set @tt = 1;

set @yy = 0;

SELECT count(1) FROM oakvip.card WHERE is_active = @tt

示例二

set @id = '123'; /设置变量 @id/

update customer_info set auth = '1' where id = @id;/要执行的 sql，id=变量值/

union 必知必会

where 和 Union 在多数情况下都可以实现相同的结果集。where 可以实现的语句一定可以使用 Union 语句来实现。但是反过来就不一定正确了，比如下面将会说到的去重和不去重。

Union 有他的强大之处，详细介绍之前，首先明确一下 Union 的使用注意规则。

Union 必须由两条或者两条以上的 SELECT 语句组成，语句之间使用 Union 链接。Union 中的每个查询必须包含相同的列、表达式或者聚合函数，他们出现的顺序可以不一致（这里指查询字段相同，表不一定一样）列的数据类型必须兼容，兼容的含义是必须是数据库可以隐含的转换他们的类型

MySQL 实现阶段累加的 sql 写法

统计每日充值/消费之后的余额，对于这种需求，其实也很简单，只需要在每次充值/消费后，计算下余额，然后保存下来就可以了。但是对于这种需求，一条 sql 就能搞定，都不需要做冗余字段

select t.*, sum(temp.price) as total_pricefrom t_charge t,t_charge tempwhere t.date <= temp.dategroup by t.id;

多条件语句

条件:1、>=90：优秀 2、>=80：良好 3、>=60：及格 4、<60：不及格

执行:select id as 学号, name as 姓名, score as 分数,(casewhen score >= 90 then '优秀'when score >= 80 and score < 90 then '良好'when score >= 60 and score < 80 then '及格'when score < 60 then '不及格'end)as 等级 from scores;