数值
int 整数型 4个字节
float 浮点数 4个字节
double 浮点数 4个字节(四舍五入 存在精度丢失问题)
decimal 字符串形式的浮点数 ,涉及到精密计算就使用该类型(对应java中的BigDecimal)
字符串
char 字符型 0~255
varcher 可变字符串 0~65535
text 大文本 2^16-1
时间类型
datetime YYYY-MM-DD HH:mm:ss 常用时间格式
timestamp 时间戳, 1970.1.1 精确到现在毫秒。
NULL 没有值 未知的。
使用注意细节:
int(3) 此时的3可不是只能保存到3位,它仅仅指的是可见显示最大的宽度,与你实际保存的整数长度无关。
比如 你保存个 3、30 、301
其实显示的是 003 、030 、301 。
===================================================
mysql 表创建语法
create table 表名称 ();
例:
if not exists 创建之间判断当前是否存在 my_user表
not null 表示当前列数据不可为null
default 表示当前列插入默认的数据
primary key 主键
engine=指定存储引擎
defalut=utf-8 指定默认的编码
CREATE TABLE IF NOT EXISTS my_user(
id INT(10) NOT NULL COMMENT '学号',
name VARCHAR(30) NOT NULL COMMENT'姓名',
password VARCHAR(10) NOT NULL DEFAULT '123456' COMMENT'密码',
birthday DATETIME DEFAULT NULL COMMENT '出生日期'
PRIMARY key(id)
)ENGINE=INNODB DEFAULT=utf-8
展示表 创建语句
show create table roles
========================================
mysql存储引擎InnoDB和MYISAM区别
MYISAM InnoDB
事务支持 不支持 支持
数据表行锁 不支持(表锁) 支持
全文索引 支持 不支持
表空间大小 较小 较大
外键约束 不支持 支持
表数据插入语法:
insert into 表名 (‘字段1’,‘字段2’) values(‘值1’,‘值2’);
表数据修改语法:
update 表名 set colnumName1=value1, colnumName2 =value2;
表数据删除语法:
delete from 表名
清空数据库表中数据:
truncate 表名 作用:完全清空一个表,表结构和索引不会改变。
delete和truncate 本质区别是,truncate会把自增列重新归零,且不影响事务。
表数据查询语法:
select * from 表名 --查询所有
select id as 'id',name as '名称' from 表名 --起别名查询
select distinct name from 表名 -- 单个字段去重查询
逻辑查询
select * from 表名 where 字段属性 between xx and xx --取值范围查询
select * from 表名 where 字段属性 >= xx and <= xx --取值范围查询
模糊查询
select * from 表名 where 名称 like'%字段属性%'; -- %匹配任意字符
select * from 表名 where 名称 like'字段属性_' -- _只匹配最后一个数据
集合中查询
select * from 表名 where id in('2','1') --查询集合中数据
表与表之间存在的一定关系,因为业务的复杂性,很少将数据保存在一张表中,更多的是创建多个表,分别存储,那么如何联表查询呐,看下面一张图(此图是借鉴哔哩哔哩狂神的)。
有图可知,绿色部分仅仅查询的左表数据,蓝色部分查询的右表,红色则是取两者的并集。
--inner join 等值连接
select
from user a
inner join role r
on a.rid = r.id
where a.del_flag=0
-- left join 左连接
select a.*,r.*
from user a
left join role r
on a.rid = r.id
where a.del_flag=0
mysql中事务四大特性:
原子性:执行一组数据要么同时成功要么同时失败
一致性:事务前后数据 完整性保持一致(银行转账实例)。
持久性:事务一旦提交就不可逆,同时数据被持久化到数据库中。
事务提交:持久化到磁盘中,就不再改变值
事务未提交:恢复原来的值。
隔离性:并发访问时,事务相互独立进行(隔离),互不干扰。
如果隔离失败一定会产生问题,如脏读、不可重复读、幻读
脏读: 一个事务读取到了其他事务未提交的数据。
不可重复读:在一个事务内读取表中某一行数据,多次读取结果不同,(有些场景下不对)
幻读: 一个事务内部读取到了别的事务插入的数据,导致前后读取的数据不一致。
(其实就是多插入了一行数据)
既然隔离失败产生了问题,那么就有对应的解决方法,此处就引入了MySql数据库的隔离级别。
1、Read uncommitted(读未提及): 最低级别,也就是可能读取到其他会话中未提交事务修改的数据,任何情况下都无法保证数据一致性。
2、Read committed(读已提交):只能读取到已经提交的数据,可避免脏读发生。(oracle默认)
3、Repeatable read(可重复读):就是在开始读取数据(事务开启)时,不再允许修改操作,可以避免脏读、不可重复读的发生。(mysql默认)
4、Serializable(串行化): 完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞,可避免脏读、不可重复读、幻读的发生。
=====================================================================
索引:帮助MySQL高效获取数据的数据结构。本职是提高查询效率。
分类:单列索引(普通、唯一)、组合索引、全文索引
主键索引(PRIMARY KEY): 唯一的标识,主键不可重复,仅只能有一个列作主键。
唯一索引(UNIQUE KEY):避免重复的列出现,多个列都可以标识为唯一索引。
普通索引(KEY/INDEX):默认的索引
全文索引(FullText):特定的数据库引擎下有,MyISAM
组合索引::一个组合索引包含两个或两个以上的列
— EXPLAIN 分析sql执行计划。
索引原则:
1、索引不是越多越好。
2、不用对经常变动的数据添加索引。(更新数据时,反而速度很慢)因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
3、小数据量的无需添加索引。
4、索引一般加在常用来查询的字段上。
**常见索引失效:**
1、使用like时,如果以%为前缀,则索引失效。后缀%可以。
2、or语句前后没有同时使用索引。当or左右查询字段只有一个是索引,
该索引失效,只有当or左右查询字段均为索引时,才会生效
3、使用组合索引,如果不使用第一列索引,则失效
4、在索引字段上使用not,<>,!=。不等于操作符是永远不会用到索引的,
因此对它的处理只会产生全表扫描。
优化方法: key<>0 改为 key>0 or key<0。
5、当全表扫描速度比索引速度快时,mysql会使用全表扫描,此时索引失效
===============================================================
数据库创建表三范式
第一范式:
原子性,保证表中每一列都是不可分割的单位。
同一列中不能有多个值,即实体中的某个属性不能有多个值或者不能有重复的属性,如果出现重复的属性则需要重新构建实体,新的实体由重复的属性构成。
第二范式:
前提 满足第一范式。
每张表只描述一件事情。
第三范式:
前提 满足第二范式。
确保数据表中的每一列数据都和主键直接相关,而不是间接相关。
规范性 和性能的问题
关联查询的表不得超过三张表(阿里规约)
1、考虑商业化的需求和目标,(成本、用户体验)数据库的性能更加重要。
2、在规范性能问题的时候,需要适当的考虑规范性。
3、故意增加一些计算列,(从大数据量降低为小数据量的查询:索引)
4、故意给某些表增加一些冗余的字段。(从多表查询中变为单表查询)
所以简而言之,表不是拆分的越简单越好,综合考虑。