第一部分1、MySQL数值类型1.1数值类型数值类型分为整数型和浮点数类型int 整数bigint 大整数decimal 浮点数1.2日期类型1.3字符串类型char 和varchar数据类型区别char长度是定长字符串类型括号内写多少长度就实际占据多少varchar长度是不定长的字符串类型 占用空间由实际传入的值决定2、数据库设计数据库设计主要用于在创建项目的时候通过一些特定的范式、关系去完成项目中表格的创建2.1设计范式设计范式主要指的是在设计表的字段的时候需要遵循一些规则设计范式分为第一范式1NF、第二范式2NF、第三范式3NF、BC范式等主要需要遵守三大范式也就是前三个范式第一范式每个字段是原子单元字段信息不可再分比如地址需要拆分成省、市、区/县、街道 联系方式要拆分成qq联系和电话联系第二范式在第一范式的基础上非主键属性要完全依赖主键非空且唯一属性第三范式满足第一、第二范式的基础上非主键属性和主键属性间不存在传递依赖2.2表和表的关系表和表之间的关系指的是将来在设计不同的表的时候怎么让表产生关联一对一一张表中的数据的一条数据和另一张表中的一条数据是一一对应的关系实现设计表的时候将一张表的数据直接放入另一张表一对多一条数据对应了另一张表中的多条数据比如一个部门有多个员工、一个班级有多个学生...实现方案设计表的时候在多的数据的一方建立外键指向一的一方的主键多对多一张表中的一条数据可以对应另一张表B中的多表数据表B中的一条数据也可以对应表A中的多条数据比如一个学生可以选修多门课一门课可以被多个学生选修一件商品可以被多个用户购买一个用户可以购买多个商品实现创建一个中间表最少有两个字段2.3数据库的ER图ER图也成为实体模型图将实体关系转化为图形形式的一种概念矩形实体表椭圆属性字段菱形关系示例图例子用户表、商品表、商品分类、地址2.3数据库设计的补充说明①实际上在做数据库设计的时候不一定会完全遵守三范式②有的时候会通过多余字段数据来提高数据库查询的性能③数据库分表在表数据库里非常大的时候会采取的一种优化方案-----垂直分表把一张表的不同字段拆分到多个独立表中每张表共用主键关联比如说用户表中经常使用的字段是用户名和密码用户信息就可以拆到一个信息表里面-----水平分表按行拆分拆分完的表的字段完全一样的比如一个学校的学生有很多可以按照专业拆分成每个专业的学生表订单表中可以按照生成订单的月份④表设计一定要考虑将来的实际的业务量以及实际读查询写增删改业务了解表的使用频次千万级别以下的不考虑优化3、视图view视图是由查询定义的一个虚拟的表可以像表一样被查询、做DML操作视图的操作语法和表完全相同,后期可以把新创建的视图当做一个表来使用视图也可以实现增删改DML最终执行的是原来的表。如果视图中出现了分组函数distinctgroup by等会将表数据分组的结果不能使用增删改视图特点①视图是虚拟表不占用磁盘物理空间②视图可以将一些表中敏感数据隐藏留下基本数据使用③视图一般用来将复杂的SQL语句做包装可以减少复杂SQL语句的重复执行创建视图语法:create[or replace] view 表名 as select 语句中括号里面的内容可以省略----创建视图子查询统计每个学生总分筛选出总分高于全体学生平均总分的学生 create view v_stusummark as select sid,sum(cmark) summk from group by sid; -- 创建一个每个人的总分视图 create view v_stuSumMark as SELECT sid,sum(cmark) summk FROM mark GROUP BY sid; -- 把视图当作表直接使用 SELECT sid FROM v_stuSumMark WHERE summk (SELECT avg(summk) FROM v_stuSumMark); -- 创建一个视图包含学生学号、姓名、课程名、分数 create view v_stuInfo as SELECT s.sid , s.sname , c.cname , m.cmark FROM student s join mark m on s.sid m.sid join course c on m.cid c.cid; -- 查询学生姓名和平均分 SELECT sname , avg(cmark) from v_stuInfo group by sid,sname;4、索引重要默认情况下表中如果没有索引都是执行全表扫描有多少数据扫描多少次基本概念索引是一种用来加快查询的数据结构可以看做是书籍的目录通过目录可以快速的检索到想要查找的数据4.1索引的优缺点背优点加快查询、连接、排序、分组效率唯一索引可以保证数据的唯一性减少磁盘IO输入、输出有些索引可以避免回表缺点占用储存空间降低DML增删改写入性能存在失效场景无法百分百生效小表格创建索引没有太大用4.2索引的分类背1按照索引功能①主键索引创建主键的时候自动生成索引主键索引是最常用的索引②唯一索引创建唯一约束的时候自动生成唯一索引保证数据的唯一性create unique index 索引名字 on 表名(字段名)③普通索引index按照自己的需求去给不同的字段加上普通索引create index 索引名称 on 表名(字段名)④联合索引(复合索引)把多个字段放在一起创建索引create index 索引名称 on 表名(字段1字段2字段3......)查看索引命令show index from 表名删除索引alter table 表名 drop index 索引名2按照索引的存放位置分类①聚簇索引主键索引、效率高----索引列和数据捆绑在一起存放的一个索引对应了一行数据----一张表只能有一个聚簇索引----默认情况下是把主键作为聚簇索引没有主键找唯一没有唯一系统会生成隐式的rowid②非聚簇索引二级索引、非主键、会造成回表----普通索引、唯一索引、联合索引都属于非聚簇索引索引创建好之后只存索引列主键将来查询全部的完整数据需要回表特点索引和数据是分开存放的一张表可以创建多个非聚簇索引查询的时候会回表查询回表查询通过普通索引找到主键还要拿着主键去聚簇索引找完整的数据多一次IO这个过程就叫做回表3按照存储结构结构分类①B树索引----大部分的业务索引都是B树结构主键、普通、联合都是B树。----叶子结点存放数据形成有序的链表范围查询、排序、分组性能好②hash索引哈希计算----基于哈希表使用哈希计算如果是等值查询效率比较快不支持范围查询、排序----可以使用 或 in 去查询不能使用 between like③全文索引full text----专门用来给长文本模糊查询的一种结构可以解决 like%关键字% 不能走索引问题4.3索引创建原则背1按需创建只为频繁查询的列创建索引一般只要给where、join、order by等使用较多的列创建索引如果表只做增加几乎没有查询也不需要创建索引2高选择性的字段可以创建索引唯一值里面筛掉重名选择性唯一值数量/总行数量越接近1越好比如说手机号、身份证号码、用户id都比较合适但是性别、状态码只有两三个值不合适3避免过度创建索引降低写的效率一般情况下一张表中的普通索引不超过5个如果要大批量导入数据可以先临时删除索引导入之后再重建索引4复合索引遵循最左前缀匹配原则(要从最左边开始否则就会失效)复合索引abc给a b c三个字段创建索引索引生效where a?,where a? and b?,where a? and b? and c?索引失效where b?,where b? and c?5优先使用覆盖索引消除回表索引中包含SQL查询的全部字段无需回到主键索引读取完整数据行比如select idname from user where phonexxxx那么在这个句子中可以给phone 和 那name 创建一个联合索引 index_phone_name(phone,name),可以避免回表6如果表比较小的话可以不用创建索引如果表的大小小于5M的话一般可以不使用索引5M5000kb5000*1000b5000000b / 100b50000条4.4索引失效的场景创建了索引但是由于在编写SQL的时候使用某些语法导致索引没有生效查询索引是否工作explain写法explain select ...查看type、key等字段Explain select*from student where sname张三key字段有值就表示使用索引key 为null 索引失败全表扫描type 的值如果是all表示全表扫描失败情况1、索引列使用了函数EXPLAIN select * from student where UPPER(sname) 张三; -- 无效 EXPLAIN select * from student where sname 张三; -- 有效2、索引列存在隐式的转换字符串和数字混用值如果是数字写一个字符串作为条件或者字段是一个字符串写一个数字作为条件都会导致索引失效3、使用or连接无索引的字段or的两侧任意一列没有索引整个索引都会失效EXPLAIN select * from student where sname 张三 or sid 10004; -- 索引生效 EXPLAIN select * from student where sname 张三 or sclass 1班; -- 索引失效4、like模糊查询通配符开头EXPLAIN SELECT * from student where sname like %小%; EXPLAIN SELECT * from student where sname like 徐%;5、复合索引不遵循给最左前缀原则create index index_name_smajor on student(sname,snativeplace,smajor) EXPLAIN SELECT * from student where sname 张三 and snativeplace 江苏 ; -- 有效 EXPLAIN SELECT * from student where snativeplace 江苏 and smajor 计算机 ; -- 无效6、字段数据分布极度不均匀使用低选择性的字段加了索引7、查询结果集占表数据量较大筛选后的数据超过了表总量的30%MySQL认为回表开销较大会放弃索引8、索引列参与了数学计算explain select * from student where sid 1 10002; -- 无效9、使用了/not / not in / 几个字符的也会导致索引失败4.5MySQL索引数据结构数据结构指的是数据存放的一种结构模式MySQL中默认采用的是B树结构Oracle中默认采用的是B树B树和B树的区别是①B树中的非叶子叶子节点存放的是完整的数据索引键一个节点中不能存放太多条数据B树中的非叶子节点只存索引键子节点指针不存数据一个节点能存更多的索引相比来说将来B树的高度会更低IO次数会更少②B树可以支持等值查询、范围查询、节点内数据有序支持排序B树所有数据都存在叶子节点且形成链表所以更有利于范围查询5、事务重要事务指的是在数据库中对数据执行的一系列的增删改操作事务一般指的是一个操作中包含多个增删改业务这些业务要么全部执行要么全部不执行不能部分执行。转账A-B转账A账户减钱 update xx set money money -1000 where xxxB账户加钱 update xx set money money 1000 where xxx这两个操作属于一个事务5.1MySQL中事务的操作commit 提交事务操作完成后把所有的操作同意提交归档永久生效rollback 回滚事务操作过程发生异常把操作回滚撤销所有操作MySQL中的事务默认是自动提交的只要执行了增删改语句都会自动把数据提交所以想要事务生效都需要手动开启事务开启事务的命令start transaction;一个事物从 start transaction 开始到 commit 或者 rollback 结束insert into user values (null,jack,123123,2000-10-10,南京) insert into user values (null,tom,123456,2001-11-11,南京) start transaction ;-----开启事务 insert into user values (null,lucy,666666,2001-11-11,南京) update user set upassword666666 where usernamejack;-----修改 delect from user where usernametom;-----删除 commit;-----提交 rollback;-----回滚5.2事物的特性①原子性一个事物可以看成是一个原子单元一个事务中的所有操作不可能拆分要么一起提交要么一起回滚②一致性事务执行前后数据保持一致比如转账业务的前后③隔离性多个事务之间相互隔离、互不影响④持久性指的是事务一旦提交或者回滚数据就会持久化存储不会改变除非重新开启一个新事务去操作数据5.3并发隔离级别将来多个事务同时操作称为并发事务并发就可能出现一些问题将来可以设置数据库中的隔离级别控制问题的发生。脏读指的是一个事物读取到另一个事务未提交的数据脏读在实际开发中不允许出现不可重复读:一个事务在开始的时候查询一条数据另一个事务B修改了这条数据并提交事务A查询数据前后不一致了实际开发中会根据需求确定是否可以出现不可重复读幻读一个事务A在做操作的时候另一个事务B添加了一条数据但是事务A这个时候查询不到这条数据事务A也去添加同一条数据会报错或者事务A修改这条数据可以修改成功就像产生幻觉一样称为幻读幻读也是允许出现的read uncommited 读未提交 会出现脏读 、不可重复读、幻读read commited 读已提交 不会出现脏读会出现不可重复度和幻读repeatable read 可重复读 不会出现脏读、不可重复读会出现幻读serializable 可串行化 不会出现脏读 、不可重复读、幻读mysql 中默认的隔离级别是 repeatable readoracle 中默认的隔离级别是read committed设置语法 查询mysql全局事务隔离级别select global.transaction_isolation;select session.transaction_isolation;MySQL开始事务之前需要加上 START TRANSACTION -----(开启事务)设置当前会话隔离级别为读未提交SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;设置当前会话隔离级别为读已提交SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;设置当前会话隔离级别为可重复读SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;设置当前会话隔离级别为串行化级别高、限制高SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;测试脏读、不可重复读和幻读数据步骤