Navicat Premium 10.1.3激活(MySQL基础知识总结)

MySQL

1.简介

MySQL是一种关系型数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

1.1 数据库分类

关系型：（SQL）

• 通过表之间，行，列之间的关系进行数据的存储
• MySQL，Oracle…

非关系型：(NoSQL)

• 对象存储，通过对象自身属性来决定
• Redis，MongDB

DBMS（数据库管理系统）

• 作用：管理和操作数据库
• MySQL:关系型数据库管理系统

数据库xxx语言

• DDL 定义

• DML 操作

• DQL 查询

• DCL 控制

2.操作数据库

如果表名或者字段名是特殊字符，需要带~ ~

数据库引擎

常规操作：

• MYISAM：节约空间，速度较快
• INNODB：安全性高，支持事务，适合多表（外键）多用户（事务）操作

3.MySQL数据管理

3.1外键

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

删除外键关系的表，先删除从表，再删除主表

方式二：创建表之后添加外键约束

以上为物理外键。

最佳实践:不用物理外键

• 数据库是单纯的表，只用来存储数据
• 用程序实现外键

3.2 DML语言

3.3 添加

insert

语法：

3.4 修改

条件子句中通过操作符编写

3.5 删除

delete 命令

语法：

Truncate 命令

完全删除一个数据库表，表的结构和索引不会变化

语法：

delete与truncate的区别

• 相同：删除数据库数据，不影响表结构

• 不同:

  • truncate使得自增列从0开始
  • truncate不影响事务

delete删除的问题

重启数据库后的现象

• InnoDB：自增列从1开始，（存在内存中的，断电即失）
• MyISAM:继续从上一个自增列开始（存在文件中，不会丢失）

4.DQL查询数据

4.1 DQL

数据查询语言

select 字段名 from table

SELECT语法

4.2 指定查询字段

去重

数据库中的表达式

4.3 where条件子句

模糊查询

4.4 联表查询

• 根据业务需求写SQL

• 使用left/right join时，on和where的区别

  • on条件后会生成临时表，不管不管条件是否为真，都会返回左表所有的行（left join），返回右表所有的行（right join）
  • where在临时表生成后通过其后的条件对on生成的临时表进行过滤，left/right join的效果丢失（可以理解为inner join，只返回相关联的记录），条件（on条件+where条件）不为真的全部过滤掉，不显示

  

• student表和result表概况
  student表
  
  result表
  

分析查询步骤，将多次查询阶段拆分成多次

思路：

1. 要查询哪些数据 select
2. 从哪几个表中查询 from 表 xxx join 连接的表 on 交叉条件
3. 若有多张表查询，先查询两张表，再查询剩下的

自连接

表和自身连接，把一张表拆成两张表

父类（顶级id）

子类

操作：查询父类对应的子类关系

4.5 分页和排序

limit语法：limit(起始值，pagesize)

order by：

4.6 子查询

where（条件的值是查询得到的）

即：where（select * from）

将子查询得到的结果作为条件在主查询中查询

4.7 过滤和分组

5.MySQL函数

5.1 常用函数

5.2 聚合函数

5.3 MD5加密

6.事务

6.1 定义

一组SQL放在一个批次中执行

事务原则（ACID）

原子性：要么都成功，要么都失败。

一致性：事务前后的数据完整性保证一致。总和不变

隔离性：不同用户同时访问数据库，每个用户单独一个事务，不能被其他事务操作干扰。

持久性：事务一旦提交就持久化到数据库，不可逆。

隔离导致的问题

脏读：一个事务读取了另外一个事务未提交的数据。

不可重复读：一个事务多次读取某一行记录，多次读取的结果不一致。

幻读：一个事务内读取到了其他事务插入的数据，前后读取结果不一致。

7.索引

索引帮助mysql快速获取数据的数据结构。

7.1 索引分类

• 主键索引 （PRIMARY KEY）
  • 只能有一个列作为主键，主键不可重复
• 唯一索引 （UNIQUE KEY）
  • 避免重复列的出现，可以有多个列都为唯一索引，且唯一索引的记录可以重复
• 常规索引 (KEY/INDEX)
• 全文

语法

java中的事务代码

7.2测试索引

索引效果：

7.3 索引原则

• 不是越多越好
• 不要对经常变动的数据加索引
• 小数据量的表不需要加索引
• 索引一般在常用来查询的字段上（索引覆盖）

索引的数据结构

btree：InnoDB默认的数据结构

8.权限管理和备份

8.1权限管理

sql命令操作

用户表：mysql.user

权限管理的本质：对用户表进行crud

用户管理：

8.2 MySQL备份

备份原因：

• 保护数据不丢失
• 数据转移

备份方式

• 拷贝物理文件
• 在可视化工具手动导出
• 使用命令行导出：mysqldump

可视化工具导出（Navicat Premium）

命令行导入

9.规范数据库设计

9.1 为什么要设计

数据库比较复杂时，就需要设计

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

• 分析需求
• 设计关系图E-R图

设计数据库的步骤：

例（个人博客）

• 分析需求（要哪些表）：

  • 用户表（用户登录注销，用户个人信息，写博客，创建分类）
    • id,username,password,gender,age,signature
  • 分类表（文章分类，谁创建的）
    • id,category_name,create_name_id
  • 文章表（文章内容）
    • id,title,author_id,category_id,content,create_time,update_time,love
  • 评论表
    • id,blog_id,user_id,content,create_time,user_id_parent(其回复的评论id)
  • 友链表（链接信息）
    • id,link(链接名称)，href(链接地址)，sort（友链排序）
  • 自定义表（系统信息，某个关键的子或者字段）key：value

• 标识实体（把需求落地到每个表的字段）

• 标识实体之间的关系

  • 写博客：user->blog
  • 创建分类：user->category
  • 关注：user->user
  • 友链：links
  • 评论：user-user-blog

  9.2 三大范式

  为什么需要规范化？

  • 信息重复
  • 更新异常
  • 插入异常
  • 删除异常

  三大范式

  第一范式

  原子性：每个字段的值不能再拆分

  第二范式(在1NF基础上)

  针对联合主键（多个字段构成的主键）：所有字段必须依赖于主键，而不能依赖于主键的一部分

  每张表只描述一件事情

第三范式(在1NF和2NF基础上)

每个字段都和主键直接相关，而不能间接相关

规范性和性能不可兼得

关联查询的表不超过三张表

• 商业化的需求，性能更重要
• 在考虑性能的时候，需要适当考虑规范性
• 故意增加冗余字段，从多表查询变为单表查询
• 故意增加计算列（如select count（*），添加计算列，每插入一条数据，该列加1），或者添加索引（但是索引树会占内存）

10.JDBC

10.1 数据库驱动

通过数据库的驱动把数据库和Java程序连通在一起。

10.2 JDBC

在数据库驱动和应用程序之间的规范，工程师只需要面向JDBC接口编程。

10.3 第一个JDBC程序

1.创建测试数据库

2.引入jar包：mysql-connector-java-5.1.47.jar

3.编写java程序

JDBC程序步骤总结：

1.加载驱动

2.url，username，password

3.获取数据库对象 DriverManager

4.新建执行sql的statement对象

5.新建sql语句，并用statement执行sql，显示返回的resultSet

6.释放连接

所用类简介

DriverManager

URL

Statement 执行SQL的对象 PrepareStatement 执行sql的对象

ResultSet 查询的结果集：返回了所有的查询结果

获得指定数据类型

遍历，指针

释放资源

按照资源声明倒序依次关闭

10.4 statement对象

提取公共方法，将连接到数据库和获取连接，释放连接抽取到公共工具类

1、提取公共参数driver、url、username、password

2、通过类加载器导入配置文件流getResourceAsStream，参数载入文件流properties.load(in)，为每个公共参数赋值

3、加载驱动

4、获取连接和释放连接的函数

对于不同的sql任务，只需要改变sql语句，不同任务的业务代码用同一个模板。

1、提取公共参数Connection、Statement、ResultSet

2、建立连接和创建statement

3、写sql，并用statement执行sql，对产生的ResultSet进行显示

4、调用工具类的方法释放连接

测试插入数据：

测试删除数据：

更改数据：

查询数据：

SQL注入

sql存在漏洞，被攻击导致数据泄露，本质：sql会被拼接，拼接为
1=1为true，这样，非合法的用户名和密码也能查询数据库中所有的结果，导致数据泄露。

sql注入格式：pwd输入为：’or’1=1

测试代码：

10.5、PreparedStatement对象

PreparedStatement可以防止SQL注入，且效率更高。

select测试代码：

防止sql注入

10.6 IDEA连接数据库

1.连接数据库

2.配置用户名，密码，测试连接

连接错误情况：

3.选择对应数据库

10.7、JDBC操作事务

10.8、数据库连接池

池化技术：由于连接-释放浪费系统资源，所以准备预先资源，过来就连接准备好的

最小连接数

最大连接数

等待超时：

编写连接池，实现DataSource接口

开源数据源实现

• DBCP

• C3P0

• Druid

DBCP

用到的jar包：

commons-dbcp-1.4、commons-pool-1.6

操作步骤：

1、导入jar包

2、src下配置properties文件

3、创建工具类，读取配置文件，工厂模式创建数据源

4、测试DBCP，通过JdbcUtils_DBCP获取Connection

C3P0

用到的jar包

c3p0-0.9.55、mchange-commons-java-0.2.19

1、添加两个jar包到lib目录

2、src目录下添加c3p0.xml配置文件

3、建立c3p0Util，通过new ComboPooledDataSource(“MySQL”)的方式创建数据源（MySQL为配置文件中某个配置的名称）

4、测试c3p0，只需要通过c3p0Util获取Connection（本质上是DataSource返回的connection）和释放连接。

总结

更换数据源，DataSource接口不会变，方法不变，本质上只是更换数据源的获取方式。

Navicat Premium 10.1.3激活今天的文章
Navicat Premium 10.1.3激活(MySQL基础知识总结)分享到此就结束了，感谢您的阅读。