MySQL常用函数

目录

1.rank() 函数

2.不同排序之间的区别：

 3. 判空

4.CASE WHEN THEN ELSE END 

5.sql 单双引号的区别

6. 常见注解方式

7. 常见小数操作函数

8. avg(),min(),max(),count() 与空和null

9.字符串处理函数

10.count(1),count(*)函数

11.select 1,0,null from test

12.inner join \left join\right join区别：

13.union

14.delete\ drop\truncate区别

15.创建表

16.grant、REVOKE 

17.#{}与${}的区别：

---

1.rank() 函数

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]  // 按什么分组
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]  //按什么排序
) 

例子：查找每个部门工资第二的人

select * from(
select *, rank() over ( partition by part order by salary desc) as ranking from test
) as p where p.ranking =2;

MySQL 窗口函数 | 新手教程

---

2.不同排序之间的区别：

    row_number（）排名递增、不跳序；
    rank() 排名重复、有跳序；
    dense_rank() 排名递增、重复、不跳序；

 select *, ROW_NUMBER()  over ( order by salary desc) as ranking from test where part='bb';

select *, RANK() over ( order by salary desc) as ranking from test where part='bb';

 

select *, DENSE_RANK()  over ( order by salary desc) as ranking from test where part='bb';

---

 3. 判空

IF(expr,v1,v2)	如果表达式 expr 成立，返回结果 v1；否则，返回结果 v2。	SELECT IF(1 > 0,'正确','错误') ->正确
IFNULL(v1,v2)	如果 v1 的值为 NULL，则返回 v2，否则返回v1	SELECT IFNULL(null,'Hello Word') ->Hello Word
ISNULL(expression)	判断表达式是否为 NULL，是返回1不是返回0	SELECT ISNULL(NULL); ->1

---

4.CASE WHEN THEN ELSE END 

select *,
case 
when part='bb' then 1
when part='cc' then 3
else 2 
end as num
from test

---

5.sql 单双引号的区别

表示字符串都可以使用，一般用单，互相嵌套时要用另外一种，否则遇到相同的需要进行转义；

列名可以不加，但加一定要加 `name`

select 
'a',
"aa",
"a'a",
'a"a',
"aaaaa\"aaaa",
'aaaa\'aaaa';

---

6. 常见注解方式

-- 包括0不包括1
#包括0不包括1
/*
包括0不包括1
*/

注意：--后面有个空格；

---

7. 常见小数操作函数

select round(3.1415926, 3) ,
rand()*10,
-- 包括0不包括1
#包括0不包括1
/*
包括0不包括1
*/
truncate(3.1415926,3),
# 不会四舍五入
floor(-1.5),ceil(-1.5),sqrt(4),pow(2,3)
/*
avg(),min(),max(),count()
*/;

---

8. avg(),min(),max(),count() 与空和null

mysql 在计算avg() 时 会自动将null值过滤掉不参与计算，因为NULL与数字不同0。

官方原文：

MySQL :: MySQL 8.0 Reference Manual :: B.3.4.3 Problems with NULL Values

对于 SQL 新手来说，NULL 值的概念是一个常见的混淆来源，他们常常认为 NULL 与空字符串 '' 是同一回事。不是这种情况。例如，以下语句是完全不同的：

mysql> INSERT INTO my_table (phone) VALUES (NULL);

mysql> INSERT INTO my_table (phone) VALUES ('');

两个语句都将值插入到phone 列中，但第一个语句插入NULL 值，第二个语句插入空字符串。第一个的含义可以被视为“电话号码未知”，第二个的含义可以被视为“已知该人没有电话，因此没有电话号码”。

为了帮助处理 NULL，您可以使用 IS NULL 和 IS NOT NULL 运算符以及 IFNULL() 函数。

在 SQL 中，与任何其他值（甚至 NULL）相比，NULL 值永远不会为真。包含 NULL 的表达式始终生成 NULL 值，除非表达式中涉及的运算符和函数的文档中另有说明。以下示例中的所有列都返回 NULL：

mysql> SELECT NULL, 1+NULL, CONCAT('Invisible',NULL);

要搜索 NULL 列值，不能使用 expr = NULL 测试。以下语句不返回任何行，因为 expr = NULL 对于任何表达式都不是 true：

mysql> SELECT * FROM my_table WHERE phone = NULL;

要查找 NULL 值，必须使用 IS NULL 测试。以下语句显示如何查找 NULL 电话号码和空电话号码：

mysql> SELECT * FROM my_table WHERE phone IS NULL;

mysql> SELECT * FROM my_table WHERE phone = '';

有关更多信息和示例，请参阅第 3.3.4.6 节“使用 NULL 值”。

如果您使用的是 MyISAM、InnoDB 或 MEMORY 存储引擎，则可以在可以具有 NULL 值的列上添加索引。否则，必须将索引列声明为 NOT NULL，并且不能将 NULL 插入到该列中。

使用 LOAD DATA 读取数据时，空列或缺失列将用 '' 更新。要将 NULL 值加载到列中，请在数据文件中使用 \N。在某些情况下也可以使用字面值 NULL。请参阅第 13.2.9 节“LOAD DATA 语句”。

使用 DISTINCT、GROUP BY 或 ORDER BY 时，所有 NULL 值都被视为相等。

使用 ORDER BY 时，首先显示 NULL 值，如果指定 DESC 以降序排序，则最后显示 NULL 值。

诸如 COUNT()、MIN() 和 SUM() 之类的聚合（组）函数会忽略 NULL 值。 COUNT(*) 是一个例外，它计算行而不是单个列值。例如，以下语句产生两个计数。第一个是表中行数的计数，第二个是 Age 列中非 NULL 值的数量：

 mysql> SELECT COUNT(*), COUNT(age) FROM person;

对于某些数据类型，MySQL 以特殊方式处理 NULL 值。例如，如果将 NULL 插入具有 AUTO_INCRMENT 属性的整数或浮点列，则会插入序列中的下一个数字。在某些条件下，如果将 NULL 插入 TIMESTAMP 列，则会插入当前日期和时间；此行为部分取决于服务器 SQL 模式（请参阅第 5.1.11 节“服务器 SQL 模式”）以及explicit_defaults_for_timestamp 系统变量的值。

总结：

1.与任何其他值（甚至 NULL）相比，NULL 值永远不会为真。包含 NULL 的表达式始终生成 NULL 值，要查找 NULL 值，必须使用 IS NULL 测试。

2.使用 DISTINCT、GROUP BY 或 ORDER BY 时，所有 NULL 值都被视为相等。使用 ORDER BY 时，首先显示 NULL 值，如果指定 DESC 以降序排序，则最后显示 NULL 值。诸如 COUNT()、MIN() 和 SUM() 之类的聚合（组）函数会忽略 NULL 值。 COUNT(*) 是一个例外，它计算行而不是单个列值。

3.插入数据时忽略主键，使用自增可以使用指定列名字，不然需要设置主键值；

insert into test(name,part,salary) VALUES(null,null,null);

---

9.字符串处理函数

MySQL 函数 | 菜鸟教程

CONCAT(s1,s2...sn)

字符串 s1,s2 等多个字符串合并为一个字符串

合并多个字符串 SELECT CONCAT("SQL ", "Runoob ", "Gooogle ", "Facebook") AS ConcatenatedString;

INSERT(s1,x,len,s2)

字符串 s2 替换 s1 的 x 位置开始长度为 len 的字符串

从字符串第一个位置开始的 6 个字符替换为 runoob： SELECT INSERT("google.com", 1, 6, "runoob"); -- 输出：runoob.com

LEFT(s,n)

返回字符串 s 的前 n 个字符

返回字符串 runoob 中的前两个字符： SELECT LEFT('runoob',2) -- ru

LOWER(s)

将字符串 s 的所有字母变成小写字母

字符串 RUNOOB 转换为小写： SELECT LOWER('RUNOOB') -- runoob

REPLACE(s,s1,s2)	将字符串 s2 替代字符串 s 中的字符串 s1	将字符串 abc 中的字符 a 替换为字符 x： SELECT REPLACE('abc','a','x') --xbc
REVERSE(s)	将字符串s的顺序反过来	将字符串 abc 的顺序反过来： SELECT REVERSE('abc') -- cba
RIGHT(s,n)	返回字符串 s 的后 n 个字符	返回字符串 runoob 的后两个字符： SELECT RIGHT('runoob',2) -- ob

STRCMP(s1,s2)

比较字符串 s1 和 s2，如果 s1 与 s2 相等返回 0 ，如果 s1>s2 返回 1，如果 s1<s2 返回 -1

比较字符串： SELECT STRCMP("runoob", "runoob"); -- 0

SUBSTRING(s, start, length)

从字符串 s 的 start 位置截取长度为 length 的子字符串，等同于 SUBSTR(s, start, length)

从字符串 RUNOOB 中的第 2 个位置截取 3个 字符： SELECT SUBSTRING("RUNOOB", 2, 3) AS ExtractString; -- UNO

UPPER(s)

将字符串转换为大写

将字符串 runoob 转换为大写： SELECT UPPER("runoob"); -- RUNOOB

---

10.count(1),count(*)函数

• count(expr)函数的参数 expr可以是任意的表达式，该函数用于统计在符合搜索条件的记录总数；
• count(expr)函数执行效率从低到高排序为：count(非主键字段) < count(主键) < count(1) ≈ count(*) ；
• count(列字段) 只统计不为 NULL 的总行数，比如，count(name)，当name字段值为NULL时，就不会被count；而其他的count，它返回检索到的行数，无论它们是否包含 NULL值；
• count(NULL) 总是返回 0；
• count（常量）与count(1)等价， count(*)会被转化成cout(0),所以对于 count(1) 和 count(*) ，效率相当，建议尽量使用 count(*)，因为 MySQL 优化器会选择最小的索引树进行统计，把优化的问题交给 MySQL 优化器去解决就可以了；

---

11.select 1,0,null from test

总结：
（1）select 1语句就会返回相应的记录行数的结果集,所有行无论是否为null，并且列名为1，每一行的值也都是1。这里的1并不是指的第一个字段，它可以替换成任意的常量，此时返回的列名和值都是该常量。比如select 2。
（2）select null 和select 1类似，只是返回的列名和值变成了NULL
（3）select 3+4这种每行的值为3+4=7。但如果里面有NULL，如3+null则结果全为NULL。
（4）任何值和NULL使用运算符（>、<、>=、<=、!=、<>）或者（in、not in、any/some、all）比较时，返回值都为NULL，NULL作为布尔值的时候，不为1也不为0。
（5）判断是否为空只能用IS NULL、IS NOT NULL。
（6）count(字段)无法统计字段为NULL的值，count(*)可以统计值为null的行。
————————————————
版权声明：本文为优快云博主「一只python菜鸟」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.youkuaiyun.com/qq_43589642/article/details/123264223

---

12.inner join \left join\right join区别：

MySQL 内连接、左连接、右连接 - 知乎

参考集合的交并，inner是交集，剩下两个是并

---

13.union

mysql基础知识——UNION - 知乎

union all不去重

---

14.delete\ drop\truncate区别

MySQL中的删除：drop，delete，truncate的区别和联系_mysql drop_友农的博客-优快云博客

4.1 相同点
都可以删除整张表中的数据
4.2 不同点
删除的范围：drop（删除表中所有数据及表结构）>truncate（删除表中所有数据）>=delete（删除表中所有数据或部分数据）
查询条件：delete可以使用查询条件进行表中数据删除，drop和truncate不可以
命令类型：delete属于DML，drop和truncate属于DDL
数据能否恢复：delete删除的数据可以恢复，但是drop和truncate删除的数据不能恢复
执行效率：drop>truncate>delete
————————————————
版权声明：本文为优快云博主「友农」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.youkuaiyun.com/m0_49622667/article/details/125079671

---

15.创建表

root@host# mysql -u root -p
Enter password:*******
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_tbl(
   -> runoob_id INT NOT NULL AUTO_INCREMENT,
   -> runoob_title VARCHAR(100) NOT NULL,
   -> runoob_author VARCHAR(40) NOT NULL,
   -> submission_date DATE,
   -> PRIMARY KEY ( runoob_id )
   -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.16 sec)

---

16.grant、REVOKE 

[MySQL] - MySQL的Grant命令 - 知乎

https://www.cnblogs.com/wugh8726254/p/15313673.html

对于特定列的授权

GRANT SELECT (column1, column2, ...) ON table_name TO user_name;

---

17.#{}与${}的区别：

在MyBatis 的映射配置文件中，动态传递参数有两种方式：

1、#{} 占位符 可以防止sql注入；

2、${} 拼接符

#{} 和 ${} 在使用中的技巧和建议

1、不论是单个参数，还是多个参数，一律都建议使用注解@Param("")

2、 能用 #{} 的地方就用 #{}，不用或少用${}

3、表名作参数时，必须用${}。如：select * from ${tableName}

4、order by 时，必须用 ${}。如：select * from t_user order by ${columnName}

5、表名处用#{}会直接报错；order by后面用#{}排序不生效

6、使用 ${} 时，要注意何时加或不加单引号，即${} 和 '${}'。一般字段类型为char或者varchar时需要加单引号

ENG FRN