mysql总结

最新推荐文章于 2023-01-16 16:52:22 发布

原创最新推荐文章于 2023-01-16 16:52:22 发布 · 10w+ 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

mysql 专栏收录该内容

6 篇文章

订阅专栏

INNER JOIN(简写join) : 内连接或等值连接获取两个表中字段匹配关系的记录
LEFT JOIN(左连接)：获取左表所有记录，即使右表没有对应匹配的记录。 left outer join 就是left join
RIGHT JOIN(右连接)：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。
DATE(date) 返回日期或日期/时间表达式的日期部分。
select DATE('2019-01-01 11:11:11') 结果为2019-01-01
DATE_FORMAT(date,format)用于以不同的格式显示日期/时间数据
select DATE_FORMAT('2019-01-01 11:11:11','%Y-%m') 结果为2019-01
CURDATE() 返回当前的日期。SYSDATE()返回系统的日期和时间。NOW() 返回当前的日期和时间
NOW()取的是语句开始执行的时间。SYSDATE()取的是动态的实时时间。
select CURDATE(),SYSDATE(),NOW() 结果为2019-3-13 | 2019-3-13 14:00:59 | 2019-2-13 14:00:59
DATE_ADD() 向日期添加指定的时间间隔.。 DATE_SUB()从日期减去指定的时间间隔。
select DATE_ADD('2019-03-31',INTERVAL 1 MONTH),DATE_SUB('2019-03-31',INTERVAL 1 MONTH) 结果为2019-04-30 | 2019-02-08
DATEDIFF() 返回两个日期之间的天数
SELECT DATEDIFF('2018-11-12','2018-11-11') 结果为 1
Null 值不会等于或者不等于任何值，甚至不能与其自身作比较。因此，不能使用 = 或 != 来测试某一列的值是否为 Null。判断一行是否含有 Null，必须使用 IS Null。你也可以使用 IS NOT Null 来找到给定列的值不是 Null 的所有行。
COALESCE(arg0,arg1,…) 返回传入的参数中第一个非null的值。这个参数使用的场合为：假如某个字段默认是null，你想其返回的不是null，而是比如0或其他值，可以使用这个函数
SELECT COALESCE(1,2,NULL) 结果 1
SELECT COALESCE(NULL,2,3) 结果 2
not in 和 in存在陷阱，当not in 里面的参数存在null 将查不到任何值(需要排除空值)，而 in 不存在这种问题
假设 emp 表有2条记录 id=1 和 id=3
1）select *from emp where id in(1,2,null) 等价于 select *from emp where (id=1 or id=2 or id=null)
2）select *from emp where id not in(1,2,null) 等价于 select *from emp where not (id=1 or id=2 or id=null)
对于1）拿 id=1 来说明 (1=1 or 1=2 or 1=null)-->(true or false or null) OR运算有一真则表达式为真所以结果为true
对于2）拿id=3 来说明 (1=3 or 2=3 or null=3 )-->(false or false or null) OR运算，null不确定，所以整个表达式为null NULL表示的是未知的含义，而增加一个NOT操作后，并不能使NULL变为一个确定的值，如果是TRUE，NOT TRUE将变为FALSE，如果是FALSE，NOT FALSE将变为TRUE，值不确定，所以为null
exists 和 not exists EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False EXISTS 指定一个子查询，检测行的存在。
select *from user a where exists (select null from dept b where b.userId=a.userId) 查询在部门表里存在的人员信息
EXISTS与IN的使用效率的问题：通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
CAST (expression AS data_type) CAST函数用于将某种数据类型的表达式显式转换为另一种数据类型。CAST()函数的参数是一个表达式，它包括用AS关键字分隔的源值和目标数据类型。
可以转换的类型以下值其中的一个：
二进制，同带binary前缀的效果 : BINARY
字符型，可带参数 : CHAR()
日期 : DATE
日期时间型 : DATETIME
浮点数 : DECIMAL
整数 : SIGNED
无符号整数 : UNSIGNED
例：select userId,name,phone,cast(money as DECIMAL(10,2))as money FROM user
IFNULL(expr1,expr2) IFNULL函数是MySQL控制流函数之一，它接受两个参数，如果不是NULL，则返回第一个参数。否则，IFNULL函数返回第二个参数。两个参数可以是文字值或表达式。
select IFNULL(null,1) 结果1 | select IFNULL(2,1) 结果 2
$ 和 # mybatis中 #会预编译防止sql注入，而$不会，通常在字段设置值的时候，我们要使用#， $方式一般用于传入数据库对象，例如传入表名.。
select *from user where id in ( #{ids} )
select *from user where id in ( ${ids} )
如果ids为1用# 和 $ 都可以，如果ids 为1,2,3 用 $可以，#这会把 1,2,3看作一个整体字符串“1,2,3”
limit,offset 区别
limit 2,1 表示从第三条开始读读取1条
limit 2 offset 1 表示从第二条开始读读取2条
mybatis中的转义字符

符号	转义
>	`>`
<	`<`
&	`&`
’	`'`
"	`"`

QPS： Queries Per Second意思是“每秒查询率”，是一台服务器每秒能够相应的查询次数，是对一个特定的查询服务器在规定时间内所处理流量多少的衡量标准。
TPS：是Transactions Per Second的缩写，也就是事务数/秒。它是软件测试结果的测量单位。一个事务是指一个客户机向服务器发送请求然后服务器做出反应的过程。客户机在发送请时开始计时，收到服务器响应后结束计时，以此来计算使用的时间和完成的事务个数。
sql执行顺序
写的顺序：select ... from... where.... group by... having... order by.. limit [offset,]
执行顺序：from... where...group by... having.... select ... order by... limit
MyISAM与InnoDB mysql常用引擎
MyISAM 不支持事务,只支持表级锁,支持全文类型索引,不支持外健
InnoDB 支持事务,支持行级锁,不支持全文类型索引,支持外健
ORM（ORM Obeject Relational Mapping）对象关系映射 ORM模型就是数据库的表与简单Java对象（POJO）的映射模型，它主要解决数据库数据和POJO对象的相互映射，优点如下：
更加贴合面向对象的编程语意
技术和业务解耦，开发者无需对数据库相关的知识深入了解
不需要开发者去关注释放数据库连接资源了
设置自增列AUTO_INCREMENT 值
ALTER TABLE tableName AUTO_INCREMENT =1;
根据身份证获取年龄
year(CURDATE())-if(length(id_card)=18,substring(id_card,7,4), if(length(id_card)=15,concat('19',substring(id_card,7,2)),null)) as age
concat 和 group_concat
concat :将多个字符串连接成一个字符串 concat(str1, str2,...)
返回结果为连接参数产生的字符串，如果有任何一个参数为null，则返回值为null。
group_concat 将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
分区就是把一张表的数据分成N个区块，在逻辑上看最终只是一张表，但底层是由N个物理区块组成的
什么时候考虑使用分区？
一张表的查询速度已经慢到影响使用的时候。(数据量大,sql经过优化)
表中的数据是分段的
对数据的操作往往只涉及一部分数据，而不是所有的数据
分表就是把一张表按一定的规则分解成N个具有独立存储空间的实体表。系统读写时需要根据定义好的规则得到对应的字表明，然后操作它。
什么时候考虑分表？
一张表的查询速度已经慢到影响使用的时候。(数据量大,sql经过优化)
当频繁插入或者联合查询时，速度变慢
分区和分表的区别与联系
分区和分表的目的都是减少数据库的负担，提高表的增删改查效率。
分区只是一张表中的数据的存储位置发生改变，分表是将一张表分成多张表。
当访问量大，且表数据比较大时，两种方式可以互相配合使用。
当访问量不大，但表数据比较多时，可以只进行分区。
水平拆分和垂直拆分拆分一般分为水平拆分和垂直拆分，这并不单指对数据库或者缓存的拆分，主要是表达一种分而治之的思想和逻辑。
水平拆分是指由于单一节点无法满足需求，需要扩展为多个节点，多个节点具有一致的功能，组成一个服务池，一个节点服务一部分请求量，所有节点共同处理大规模高并发的请求量。
垂直拆分指按照功能进行拆分，秉着“专业的人干专业的事”的原则，把一个复杂的功能拆分为多个单一、简单的功能，不同单一简单功能组合在一起，和未拆分前完成的功能是一样的。由于每个功能职责单一、简单，使得维护和变更都变得更简单、容易、安全，所以更易于产品版本的迭代，还能够快速的进行敏捷发布和上线。
replace 替换函数
格式化手机号,①去掉所有空格，②去掉所有+86 ③去掉所有-
SELECT mobile ,replace(replace(replace(mobile,'-',''),'+86',''),' ','') as 'formatMobile' FROM user
show processlist 查看数据库连接情况
查看正在执行的线程，并按 Time 倒排序，看看有没有执行时间特别长的线程
select * from information_schema.processlist where Command != 'Sleep' order by Time desc;
按客户端 IP 分组，看哪个客户端的链接数最多
select client_ip,count(client_ip) as client_num from (select substring_index(host,':' ,1) as client_ip from information_schema.processlist ) as connect_info group by client_ip order by client_num desc;
找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀
select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;
mysql字符串截取总结：left()、right()、substring()、substring_index()
left(str, length)，即：left(被截取字符串，截取长度)
例：select left('hy.zx.com',2) 结果 hy
right(str, length)，即：right(被截取字符串，截取长度)
例：select right('hy.zx.com',2) 结果：om
substring(str, pos)，即：substring(被截取字符串，从第几位开始截取)
substring(str, pos, length)，即：substring(被截取字符串，从第几位开始截取，截取长度)
例：
SELECT SUBSTRING('hy.zx.com',4); 结果：zx.com
SELECT SUBSTRING('hy.zx.com',4,2); 结果：zx
SELECT SUBSTRING('hy.zx.com',-4); 倒数第四个开始结果：.com
SELECT SUBSTRING('hy.zx.com',-4,2); 倒数第四个开始结果：.c
substring_index(str, delim, count)，即：substring_index(被截取字符串，关键字，关键字出现的次数)
例：
SELECT SUBSTRING_INDEX('hy.zx.com','.',2) 截取第二个“.”之前的所有字符结果：hy.zx
SELECT SUBSTRING_INDEX('hy.zx.com','.',-2) 截取倒数第二个“.”之后的所有字符结果：zx.com
SELECT SUBSTRING_INDEX('hy.zx.com','_',2) 如果关键字不存在，则返回整个字符串结果 hy.zx.com
回表查询与覆盖索引
回表查询：二级索引（非聚簇索引都是二级索引,又叫辅助索引）无法直接查询所有列的数据，所以通过二级索引查询到聚簇索引后，再查询到想要的数据，这种通过二级索引查询出来的过程，就叫做回表（通过输出的extra列来判断，对于一个回表查询，显示为Using index condition）
覆盖索引：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。(通过输出的extra列来判断，对于一个索引覆盖查询，显示为Using index)