大家好,我在这里整理了一些在工作中关于mysql用到的一些方式方法,做了一个整理,希望可以帮助到大家。
此内容省去了一大部分特别基础的知识,如insert、delete之流,较为mysql适合进阶的同学那么开始~
内容主要分为:
- mysql常用数据类型
- 工作中日常遇见的函数,索引,加密,解密等
- 常遇见的MYSQL面试题
1. mysql常用数据类型
(1) 数值类型,数字类型
数据类型 | 描述 | 大小 |
tinyint | 十分小的数据 | 1个字节 |
smallint | 较小的数据 | 2个字节 |
mediumint | 中等大小的数据 | 3个字节 |
int | 标准的整数 | 4个字节 |
bigint | 较大的数据 | 8个字节 |
float | 浮点数 | 4个字节 |
double | 浮点数 | 8个字节 |
decimal | 字符串形式的浮点数,一般用于金融计算 |
(2) 字符串类型
数据类型 | 描述 | 大小 |
char | 字符串固定大小 | 0~255 |
varchar | 可变字符串 | 0~65535 |
tinytext | 微型文本 | 2^8-1 |
text | 文本串 | 2^16-1 |
(3) 时间类型
数据类型 | 描述 | 大小 |
date | 日期格式 | YYYY-MM-DD |
time | 时间格式 | HH:mm:ss |
datetime | 最常用的时间格式 | YYYY-MM-DD HH:mm:ss |
timestamp | 时间戳,1970.1.1到现在的毫秒数 | |
year | 年份标识 |
拓展小知识:
1.创建表的时候一定要有这五个字段:
id (主键)
version (乐观锁)
is_del (伪删除)
create_time (创建时间)
update_time (修改时间)
2. mysql函数
1.查询中的方法
(1)LEFT JOIN 左连接查询
(2)RIGHT JOIN 右链接查询
(3)INNER JOIN 内连接查询
(4)GROUP BY 指定结果按照哪几个字段分组
(5)HAVING 过滤分组的记录必须满足次要条件
(6)ORDER BY 指定查询记录按一个或者多个条件排序
(7)LIMIT 指定查询的记录从第几条到第几条
2.函数
(1)常用函数
SELECT ABS(-8); --绝对值
SELECT CEIL(5.1); --向上取整
SELECT CEILING(5.1); --向下取整
SELECT RAND(); --返回0~1之间的一个随机数
SELECT SIGN(-10); --返回一个数的符号;0返回0,正数返回1;负数返回-1
(2)字符串函数
SELECT CHAR_LENGTH(‘我喜欢你’) --字符串长度
SELECT CONCAT(‘我’,’喜欢’,’你’) --拼接字符串
SELECT UPPER(‘zsd’) --转大写
SELECT LOWER(‘ADD’) --转小写
SELECT INSTR(‘zsdc’,’s’) --返回第一次出现字符串索引的位置
SELECT REPLACE(‘加油就能胜利’,’加油’,’坚持’) --替换出现指定字符串
SELECT SUBSTR(‘坚持就是胜利’,3,6) --返回指定的字符串(源字符串,截取位置,截取长度)
SELECT REVERSE(‘RSZ’) --反转字符串
(3)时间日期函数
SELECT CURRENT_DATE(); --获取当前日期
SELECT CURDATE(); --获取当前日期
SELECT now(); --获取当前时间
SELECT LOCALTIME(); --本地时间
SELECT SYSDATE; --系统时间
(4)聚合函数(在函数中用的最多的,重点、重点、重点)
MAX() --获取最大值
MIN() --获取最小值
SUM() --求和
AVG() --平均值
COUNT() --计算个值
3.MD5加密解密
插入的时候加密
INSERT INTO ‘test’ VALUES(4,’汤米’,MD5(‘123456’))
查询时候解密密码
SELECT * FROM ‘test’ WHERE `NAME`=’barry’ AND ‘PWD’=MD5(‘123456’)
4.explain:模拟优化器执行SQL语句,在查询语句之前加上即可大幅度提高查询效率。
3. mysql概念
1.索引
索引作用:
- 提高查询速度
- 确保数据的唯一性
- 可以加速表和表之间的链接,实现表与表之间的参照完整性
- 使用分组和排序子句进行数据检索时,可以显著减少分组和配许的时间
索引的分类:
1.主键索引:唯一的标识,主键不可重复,只有一个列作为主
最常见的索引类型,不允许为空值。
确保数据记录的唯一性
确定特定数据记录在数据库中的位置
2.普通索引:默认的,快速定位特定数据
index 和 key 关键字都可以设置常规索引
应加在查询找条件的字段
不宜添加太多常规索引,影响数据的插入,删除和修改操作
3.唯一索引:与普通索引类似,索引列的值必须唯一,但允许有空值。
4.全文索引:快速定位特定数据
在特定的数据库引擎下才有:MyISAM
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集
索引原则:
在特定的数据库引擎下才有:MyISAM
只能用于CHAR , VARCHAR , TEXT数据列类型
适合大型数据集
2.事务原则
1.事务特性
原子性:原子性是指一个不可分割的工作单位,事务中的操作要么都发生,要么都不发生。
一致性:事务前后数据的完整性必须保持一致。
持久性:持久性是指一个事务一旦被提交,它对数据库中数据的改变是永久性的,接下来即使数据库发生故障也不应该对其有任何影响。
隔离性:事务的隔离性是多个用户并发访问数据库时,数据库为每一个用户开启的事务,不能被其他事务的操作作数据所干扰,多个并发事务之间要相互隔离。
2. 事务并发导致的问题
脏读 | 指一个事务读取了另外一个事务未提交的数据。 |
不可重复读 | 在一个事务内读取表中的某一行数据,多次读取结果不同。 |
虚读(幻读) | 是指在一个事务内读取到了别的事务插入的数据,导致前后读取不一致。 |
3.事务的隔离级别
读未提交:一个事务读取到其他事务未提交的数据;这种隔离级别下,查询不会加锁,一致性最差,会产生脏读、不可重复读、幻读的问题
读已提交:一个事务只能读取到其他事务已经提交的数据;该隔离级别避免了脏读问题的产生,但是不可重复读和幻读的问题仍然存在;读提交事务隔离级别是大多数流行数据库的默认事务隔离级别,比如 Oracle,但是不是 MySQL 的默认隔离界别
可重复读:事务在执行过程中可以读取到其他事务已提交的新插入的数据,但是不能读取其他事务对数据的修改,也就是说多次读取同一记录的结果相同;该个里级别避免了脏读、不可重复度的问题,但是仍然无法避免幻读的问题
串行化:事务串行化执行,事务只能一个接着一个地执行,并且在执行过程中完全看不到其他事务对数据所做的更新;缺点是并发能力差,最严格的事务隔离,完全符合ACID原则,但是对性能影响比较大
事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
读未提交(read-uncommitted) | 是 | 是 | 是 |
读已提交(read-committed) | 否 | 是 | 是 |
可重复读(repeatable-read) | 否 | 否 | 是 |
串行化(serializable) | 否 | 否 | 否 |
3. Mysql三大范式
第一范式:第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值,就说明该数据库表满足了第一范式。
第二范式:第二范式需要确保数据库表中的每一列都和主键相关,而不能只与主键的某一部分相关(主要针对联合主键而言)。也就是说在一个数据库表中,一个表中只能保存一种数据,不可以把多种数据保存在同一张数据库表中。
第三范式:要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说,表的信息,如果能够被推导出来,就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。