MySql 基础学习

• JavaEE：企业级开发 web 前端+后端+数据库

  
  

• 前端：页面展示数据

• 后台：连接前端与数据库（JDBC）

• 数据库：存数据

• 数据库是所有软件体系中最核心的存在----DBA

• 数据仓库，软件，安装在操作系统之上

1.1数据库分类

• 关系型数据库：Excel 行列（SQL）

• • Mysql，Oracle，Sql Server，DB2，SqlLite
  • 表和表之间，行列之间的关系进行存储

• 非关系型数据库：（key，value）

• • Redis，MongDB
  • 对象存储，通过自身属性决定
  • NOSQL NOT only SQl

• DBMS 数据库管理系统

• • 科学有效的管理数据
  • 维护和获取数据

• MySql ： 关系型数据库管理系统

• Sql语言

• 最好的关系型数据库管理系统

• 开源

• 小，快！

1.2MySql安装下载

MySQL 5.7安装教程-KuangStudy-文章

1.3Sqlyog安装使用

• 每一个sqlyog操作都对应历史记录

1.4连接数据库

• 命令行连接数据库

• CMD窗口所有语句 ； 结尾
• 查看所有数据库

• DDL 数据库定义语言
• DML 操作
• DQL 查询
• DCL 控制

• 操作数据库

• • 操作数据库中的表
  • • 操作表的数据

• 操作数据库：创建，删除，使用

• 学习中对照Sqlyog可视化历史记录

2.1列的数据类型

数值

• tinyint 特别小的 1个字节
• smallint 较小的 2个字节
• int 正常的 4个字节
• bigint 较大的 8个字节
• float 浮点数 4个字节
• double 浮点数 8个字节
• decimal 字符串式的浮点数 超精密计算（金融）

字符串

• char 字符串固定大小 0-255
• varchar 可变字符串 0-65535（String）
• tinytext 微型文本
• text 文本串（大） 保存大文本

时间日期

• date 日期
• time 时分秒
• datetime
• timestamp 1970.1.1到现在的毫秒数
• year

null

• null 避免进行运算

2.2数据库字段属性

• Unsigned 无符号整数，不能为负数
• Zerofill 不足的位数默认0填充
• 自增 自动+1，设置主键，整数类型
• 可以高级设置里设置主键起始值和步长max，min
• NULL ， not NULL
• 默认：默认值
• 主键：正常必须有，唯一标识

2.3创建数据库表

• 使用sql创建

格式

设置字符节编码

2.4MyISAM 与 InnoDB区别

• 关于数据库引擎

• MyISAM ：早些年使用的

• • 不支持事务
  • 不支持数据行锁定
  • 不支持外键约束
  • 支持全文索引
  • 表空间较小
  • 节省空间，速度快

• InnoDB：默认

• • 支持事务
  • 支持行锁定
  • 支持外键约束
  • 不支持全文索引
  • 表空间约为MyISAM两倍
  • 安全性高，事务处理，多表多用户关联操作

• 数据库所有的文件本质上还是文件的存储（data目录下）

• InnoDB在数据库表中只有一个*from文件，其他是备份，以及上级目录下的ibdata1文件

• MyISAM：*from定义文件，*MYD数据文件（data），MYI索引文件（index）

2.5修改和删除表 alter

修改

删除

3.1 数据库级别的外键

方式一，在创建表时候，增加约束

• 删除有外键关系的表（grade表）时，必须先删除引用的表（student表）

方式二，创建表成功后添加外键约束

• 数据库级别的外键 不建议使用，避免数据库过多

最佳实践

• 数据库就是单纯的表，只用来存数据
• 想用多张表的数据，去程序实现使用外键

3.2 Insert 语句

DML 语言 – 数据操作语言

• Insert
• Update
• Delete
• Select

3.3 Update 语句

update 修改谁 （条件） set 原来的值 = 新值

3.4 Delete 语句

delete from 表明 where 条件

• 删除表 优选 truncate

• delete 与 truncate 区别

• • 都能删除数据，且不修改表结构
  • Truncate 重新设置自增列，子增量 计数器归零
  • Truncate 不会影响事务
  • delete 不影响自增

• delete 删除 ，重启数据库

• • InnoDB 自增列从1开始（存在内存中，断电即失）
  • MyISAM 继续从子增量开始 （存在文件中，丢不了）

3.4 Select 语句

• DQL 查询数据：数据查询语言
• 数据库中最核心的语言，最重要的，使用频率最高的

Select 找啥？ FROM 在哪？Where 条件？

• 别名 ， 给结果起个名字 AS 表名和字段名都可以起别名

• 拼接字符串

• select 啥都能查

3.5 去重 distinct

• distinct 去重
• all 全部数据

3.6 where 子句

• 检索数据中符合条件的值

• 尽量使用英文符号-and = && , or = || , != / not

3.7 模糊查询

• sql里面运算符不一定是符号

比较运算符

• like a like b a匹配到b，结果为真
• in a in b a在b里面某一个，结果为真

• 不能写= null 要写 is null
• 通配符只能在like中使用

3.8 连表查询 joinON

join 对比

• 左右查询中on 后的匹配结果不影响是否显示，不匹配顶多是null

• join on 连接查询

• where 等值查询

select 找啥 frm （左表名） join (连接的右表名) on （条件）

3.9 自连接

• 自己的表和自己的表连接
• 核心思想：给表或字段起不同的别名那就是两份数据

3.10 分页和排序

• limit order By

Order By 排序

limit 分页

• 用户体验好

• 瀑布流----无限加载，没有分页 抖音/快手-无限划没有尽头，刷视频

• 所有语句的最后一个

3.11 子查询和嵌套查询

• where（值是计算出来的）
• 本质：where 中嵌套一个查询语句

3.12 MySql 常用函数 *

3.13聚合函数及分组过滤

• count（） -计数

• sum（） -求和

• avg（） -平均值

• max（） -最大值

• min（） -最小值

• group by 通过**字段分组

• select 语法 优先级

• • inner join – 联合查询
  • where – 条件 一个查询语句只能有一个
  • group by – 按哪几个字段分组
  • having – 过滤一个或多个条件，也是where
  • order by – 排序
  • limt – 分页

3.14 数据库级别的MD5 加密

• 增强算法复杂度，不可逆！
• MD5破解原理，背后有字典，只有简单的

4.1 ACID原则，脏读，不可重复读，幻读

什么是事务？

• 将一组sql放在一个批次执行

事务原则 - ACID 原子性，一致性，隔离性，持久性 -脏读，幻读

• 原子性：要么都成功，要么都失败，不能只发生一部分动作
• 一致性：结果一致性：事务前后的数据完整性保持一致
• 持久性：事务结束后不会因为外界原因使数据丢失，没提交将恢复到原状，提交后持久化到数据库，不可逆
• 隔离性：多个用户操作一个数据需要隔离
• 脏读：一个事务读取到了另外一个事务未提交的数据
• 不可重复读：一个事务内重复读取表数据，数据发生变化
• 幻读：事务中读取到了其他事务读取到了其他事务更新的数据

4.2 测试事务实现转账

• mysql默认开启事务自动提交

5.1 索引及其分类

• 索引：帮助Mysql高效获取数据的数据结构，提取句子主干，可以得到索引的本质，索引是数据结构

索引的分类

• 主键索引：primary key

• • 唯一标识，不可重复，只能有一个列作为主键

• 唯一索引：unique key

• • 避免重复列出现，唯一索引可以重复，多个列可以表示为唯一索引

• 常规索引：key，index

• • 默认的

• 全文索引：fulltext

• • 在特定的数据库引擎才有，MyISAM
  • 快速定位数据

• 创建表时增加索引或创建完毕增加索引

5.2 超大数据测试索引

• 索引在小数据无意义，只有在庞大数据量面前才会有效

5.3 索引原则

• 索引不是越多越好
• 不对经常变动的数据加索引
• 小数据靠边别瞎搞，起码也得几百万条数据
• 一般加在常用来查询到字段上

hash 类型的索引

btree ：innodb 默认的类型

• 度娘直接搜：mysql索引背后的数据结构及算法原理

6.1 数据库用户管理

Sqlyog 可视化管理

• 创建新用户：localhost与127.0.0.1 有强制要求

• 对初始mysql数据库中user表进行增删改查

• root 用户拥有最高权限

6.2 数据库备份

• 保证重要的数据不丢失
• 数据转移

• 备份方法

• 拷贝data文件

• 在sqlyog可视化工具中手动导出 – 结构跟数据都带走

• cmd命令行导出 – mysqldump -localhost -u -p 库 表

• ​ 导入 – source （先登录）

7.1 规范数据库设计

• 当数据库较复杂时，需要设计
• mysql数据库不区分大小写，没有驼峰命名，下划线处理

糟糕的设计

• 数据沉余，浪费空间
• 数据插入删除麻烦，异常（外键问题）
• 程序性能差

良好的数据库设计

• 节省空间
• 保证数据完整性
• 方便开发系统

软件开发中，数据库的设计

• 分析需求
• 概要设计：设计关系图（E-R图）

设计步骤 （个人博客）

• 收集信息，需求分析
• • 用户表
  • 分类表（文章分类，文章作者）
  • 文章表（文章信息）
  • 自定义表 -系统信息，某个关键字
  • 评论表
• 标识实体—需求落到每个关键字
• 标识实体间关系：表之间相互关联

7.2 三大范式

• 关系型数据库设计六大范式，前三个就够了

第一范式

• 原子性，保证每一列不可再分

第二范式

• 满足第一范式
• 一张表只能描述一件事
• 每一列与主键直接相关

第三范式

• 满足一二范式
• 消除传递依赖，有关联地方就行

规范性和性能问题

• 关联查询不能太多表
• 性能更加重要！
• 故意给某些表增加一些冗余字段
• 故意增加一些计算列 --从大数据量降低为小数据量的计算 – 索引（占内存）

10.1 数据库驱动

• 驱动：声卡，显卡，数据库
• JDBC：java操作数据库的规范
• 这些规范的实现由具体的厂商去做
• 对于开发人员来说只需掌握jdbc接口即可
• 没有什么是加一层解决不了的
• 需要导入数据库驱动包

10.2 第一个JDBC程序

• 导入mysql-connector包，版本最好与数据库版本一致

连接步骤

• 加载驱动
• 准备用户信息
• 连接数据库
• 获得数据库对象
• 获得sql执行对象
• 操作数据库
• 释放连接

10.3 jdbc中的对象解释

10.4 Statement对象

• 提取工具类—每次执行sql语句要变动的只有sql与Statement对象

• 准备sql语句

• 连接

• src目录下db.properties文件

• util配置文件

• 测试连接

• 增加删除修改统一都是executeUpdate（）

  只有查询是 executeQuery（）

10.5 SQL注入问题

• sql存在漏洞，会被攻击导致数据泄露

• sql会被拼接 如查询语句添加 or ’1=1’ 这种永久成立的判断

• • input 输入框输入 sql语句等危险情况

• 几乎所有的sql都存在sql注入的漏洞

10.6 PreparedStatement对象

• 防止sql注入，效率更高

10.7 使用idea连接数据库

• 注意版本对应

10.8 jdbc操作事务

• ACID原则

• idea里面一组完整事务跟sqlyog对比只需要：
• • 开启事务：connection.setAutoCommit(false);
  • 提交：connection.commit();
  • 最后回滚可以写但没必要，失败默认自动回滚（牛啊！）

10.9 数据库连接池

• 每次操作数据库都要连接释放资源，其本身也是对系统资源的很大浪费
• 池化技术：准备一些预留的资源，过来直接连接
• • 最大最小连接数 ------- 最大最小承载资源
  • 等待超时：报个异常，下次再来

开源数据源实现—当下较火的

• DBCP —传统的

• C3P0 —传统的

• Druid：阿里巴巴 德鲁伊 —这个厉害，为监控而生

• • 无需代码连接数据库的操作