SQL知识小结

常用函数

不同数据库的函数往往各不相同，因此不可移植。本节主要以 MySQL 的函数为例。

文本处理

函数	说明
`LEFT()`、`RIGHT()`	左边或者右边的字符
`LOWER()`、`UPPER()`	转换为小写或者大写
`LTRIM()`、`RTIM()`	去除左边或者右边的空格
`LENGTH()`	长度
`SOUNDEX()`	转换为语音值

其中， SOUNDEX() 可以将一个字符串转换为描述其语音表示的字母数字模式。

1
2
3

SELECT *
FROM mytable
WHERE SOUNDEX(col1) = SOUNDEX('apple')

日期和时间处理

日期格式：YYYY-MM-DD
时间格式：HH:MM:SS

函数	说明
`AddDate()`	增加一个日期（天、周等）
`AddTime()`	增加一个时间（时、分等）
`CurDate()`	返回当前日期
`CurTime()`	返回当前时间
`Date()`	返回日期时间的日期部分
`DateDiff()`	计算两个日期之差
`Date_Add()`	高度灵活的日期运算函数
`Date_Format()`	返回一个格式化的日期或时间串
`Day()`	返回一个日期的天数部分
`DayOfWeek()`	对于一个日期，返回对应的星期几
`Hour()`	返回一个时间的小时部分
`Minute()`	返回一个时间的分钟部分
`Month()`	返回一个日期的月份部分
`Now()`	返回当前日期和时间
`Second()`	返回一个时间的秒部分
`Time()`	返回一个日期时间的时间部分
`Year()`	返回一个日期的年份部分

数值处理

函数	说明
SIN()	正弦
COS()	余弦
TAN()	正切
ABS()	绝对值
SQRT()	平方根
MOD()	余数
EXP()	指数
PI()	圆周率
RAND()	随机数

汇总

函数	说明
`AVG()`	返回某列的平均值
`COUNT()`	返回某列的行数
`MAX()`	返回某列的最大值
`MIN()`	返回某列的最小值
`SUM()`	返回某列值之和

AVG() 会忽略 NULL 行。

使用 DISTINCT 可以让汇总函数值汇总不同的值。

1 2	SELECT AVG(DISTINCT col1) AS avg_col FROM mytable

数据库容量大小

不同数据库查询方式不同，以下查询语句以MySQL为例。

查看是所有数据容量大小

select
table_schema as '数据库',
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024/1024, 2)) as '数据容量(GB)',
sum(truncate(index_length/1024/1024/1024, 2)) as '索引容量(GB)'
from information_schema.tables
group by table_schema
order by sum(data_length) desc, sum(index_length) desc;

查看所有数据库各表容量大小

select
table_schema as '数据库', 
table_name as '表名',
table_rows as '记录数',
truncate(data_length/1024/1024, 2) as '数据容量(MB)',
truncate(index_length/1024/1024, 2) as '索引容量(MB)'
from information_schema.tables
order by data_length desc, index_length desc;

数据库优化

大表数据查询如何进行优化？

索引优化
SQL语句优化
水平拆分
垂直拆分
建立中间表
使用缓存技术
固定长度的表访问起来更快
越小的列访问越快

大表如何优化？

限定数据的范围：避免不带任何限制数据范围条件的查询语句。
读写分离：主库负责写，从库负责读。
垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。
水平分表：在同一个数据库内，把一个表的数据按照一定规则拆分到多个表中。
对单表进行优化：对表中的字段、索引、查询SQL进行优化。
添加缓存

参考：https://javaguide.cn/database/sql/sql-syntax-summary.html

参考：https://www.cnblogs.com/llzhang123/p/9239682.html

参考：https://zhuanlan.zhihu.com/p/346064689

参考：https://www.tizi365.com/question/topic-411.html

MySQL特性

on duplicate key update

主要作用是当插入的数据中含主键或唯一索引，且数据库中已存在时，该语句将会进行更新操作；而不存在时则会插入。

SQL使用范例

INSERT INTO table (id,A,B,C) VALUES (9,1,2,3),(10,4,5,6)  
      ON DUPLICATE KEY UPDATE C=VALUES(A)+VALUES(B),A=VALUES(A),B=VALUES(B);   
--- 存在重复记录时(主键或唯一索引重复)，上面的语句即为更新
UPDATE table SET c=A+B,A=A,B=B WHERE id=9;
UPDATE table SET c=A+B,A=A,B=B WHERE id=10;

mybatis使用范例


<insert id="insertOrUpdate" paramerType="java.util.List">
    insert into table (id,A,B,C) 
    VALUES
     <foreach collection ="list" item="item" separator =",">
             ( #{item.id}, #{item.A},#{item.B},#{item.C})
    </foreach>
    ON DUPLICATE KEY UPDATE 
    id = VALUES(id),
    A = VALUES(A),B = VALUES(B),C = VALUES(C)
</insert>

可能会产生死锁(dead Lock)

1、执行 insert …… on duplicate key update

2、key已存在，获取该记录的S锁，获取该记录

3、对读取的记录进行修改

4、修改写进存储引擎，给该记录加上X锁

如果两个事务同时执行该语句，3、4步交叉执行则会产生死锁

https://www.cnblogs.com/better-farther-world2099/articles/11737376.html

insert ignore

主要作用是当插入的数据中含主键或唯一索引，且数据库中已存在时，该语句将停止执行；而不存在时则会插入。

**SQL使用范例

1	INSERT ignore INTO table (id,A,B,C) VALUES (9,1,2,3),(10,4,5,6)

IP转换

IP转为整形节省空间，易于存储，查找

INET_ATON() ：把 ip 转为无符号整型 (4-8 位)
INET_NTOA() :把整型的 ip 转为地址

CourageHe's Blog

MySQL知识小结