MySQL

MySQL

1. MySQL基本信息

1.1 数据库分类

关系型数据库：(SQL)

• MySQOL, Oracle, Sql Server, DB2, SQLlite
• 通过表和表之间，行和列之间的关系进行数据的存储

非关系型数据库：(NOSQL) Not Only SQL

• Redis,MongDB
• 非关系型数据库，对象存储，通过对象自身的属性来决定

1.2 DBMS（数据库管理系统）

DB存数据，DBMS管理和操作数据

• 数据库的管理软件，科学有效地管理数据，维护和获取数据；
• MySQL本质是数据库管理系统。

1.3 MySQL

是一个关系型数据库管理系统。

2. 操作数据库

2.1 数据库的字段属性

Unsigned：无符号整数，声明该列不能声明为负数；
zerofill：0填充，不足的位数使用0填充；
自增：自动在上一条记录的基础上+1，通常用来设置唯一的主键，必须为整数类型。可以自定义设计自增的起始值和步长；
非空：如果不赋值会报错；
default:如果不指定该列的值，则设置为默认值

2.2 创建数据库表

格式：

CREATE TABLE [IF NOT EXISTS] `表名`(
	`字段名` 列类型 [属性] [索引] [注释],
	`字段名` 列类型 [属性] [索引] [注释]
)[表类型][字符集设置][注释]

常用命令：

• SHOW CREATE DATABASE school --查看创建数据库的语句
• SHOW CREATE TABLE student --查看student数据表的定义语句
• DESC student --显示表的结构

数据库引擎：

	MYISAM	INNODB
事务支持	不支持	支持
数据行锁定	不支持	支持
外键约束	不支持	支持
全文索引	支持	不支持
表空间的大小	较小	较大，约为2倍

• MYISAM：节约空间，速度较快
• INNODB：安全性高，事务的处理，多表多用户操作

2.3 修改删除表

修改：

• ALTER TABLE 旧表名 RENAME AS 新表名：修改表名
• ALTER TABLE 表名 ADD 字段名 列属性：增加表的字段
• ALTER TABLE 表名 MODIFY 字段名 列属性[]：修改表的字段
• ALTER TABLE 表名 CHANGE 旧名字 新名字 列属性[]：字段重命名
  删除：
  DROP TABLE IF EXISTS 表名

2.4 DML

Data Manipulation Language数据库操纵语言

• 插入语句：insert into 表名([字段1,字段2,字段3])values(‘值1’),(‘值2’),(‘值3’)…
• 修改语句：update 表名 set column_name = value where [条件]
  删除语句：
• delete from 表名 [where 条件]
• truncate 表名

注意：
1.都可以删除数据，并且不会删除表结构；
2.truncate重新设置自增列，计数器会归零；不会影响事务；
3.使用delete删除后重启数据库，如果使用InnoDB，自增列会从1开始（存储在内存中，断电即失）；如果使用MyISAM，继续从上一个自增量开始（存在文件中，不会丢失）。

2.5 DQL

Data Query Language:数据查询语言

• 查询指定字段：select 字段… from 表
• 去除重复数据：select distinct 字段 from 表
  联表查询
• inner join：只返回两个表中联结字段相等的行
• left join：会从左表中返回所有的值，即使右表中没有匹配
• right join：会从右表中返回所有的值，即使左表中没有匹配
• 排序：order by 字段名 asc/desc
• 分页：limit 起始下标,页面大小
• 分组：group by 字段名,having 条件
  聚合函数
• count(字段)：会忽略null值
• count(*)/count(1)：不会忽略null值

select完整语法

select distinct 要查询的表 from 表名
xxx join要连接的表 on 等值判断
where 具体的值，子查询语句
group by 字段
having 过滤分组的信息
order by 字段 desc/asc
limit startindex,pagesize

2.6 注意

运算符：

• BETWEEN…AND…：在某个范围内
• AND：与
• OR：或
• NOT：非
• IS NULL：如果值为NULL，结果为真
• IS NOT NULL:如果值为为NULL，结果为真
• LIKE：%代表0到任意个字符，_代表一个字符
• IN：a IN {a1,a2,a3…} 假设a在其中，结果为真

3. 事务

3.1 基本信息

1.ACID原则：原子性(Atomicity)，一致性(Consistency)，隔离性(Isolation)，持久性(Durability)；
2.原子性：是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生；
3.一致性：事务前后数据的完整性必须保持一致；
4.隔离性：多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，事务之间要相互隔离；
5.持久性：指事务结束后的数据不随外界原因导致数据丢失
隔离所导致的问题：

• 脏读：指一个事务读取了另一个事务未提交的数据；
• 不可重复读：在一个事务内读取表中的某一行数据，多次读取结果不同；
• 虚读（幻读）：在一个事务内读取到了别的事务插入的数据，导致前后读取不一致。

4. 索引

4.1 分类

• 主键索引(primary key):唯一的标识，主键不可重复，只能有一个列作为主键；
• 唯一索引(unique key):避免重复的列出现；
• 常规索引(key/index):默认的，index/key关键字来设置；
• 全文索引(fulltext):在特定的数据库引擎下才有MyISAM；快速定位数据。

4.2 索引原则

• 不要对经常变动的数据加索引
• 索引一般加载常用来查询的字段上，且小数据量不需要加索引

5. 三大范式

• 第一范式（1NF）：原子性，保证每一列不可再分；
• 第二范式（2NF）：满足第一范式的前提下，每张表只描述一件事情；
• 第三范式（3NF）：满足第二范式的前提下，确保数据表中的每一列数据都和主键直接相关，而非间接相关。

6. JDBC

6.1 步骤

• 定义用户信息和url
• 连接数据库DriverManager
• 获得执行sql的对象Statement
• 获得返回的结果集
• 释放连接
  **url：**协议://主机地址:端口号/数据库名?参数1&参数2&参数3

6.2 Connection类

代表数据库

• commit():事务提交
• rollback():事务回滚
• setAutoCommit():数据库设置自动提交

6.3 Statement类

用于向数据库发送sql语句，像完成对数据库的增删改查，只需要通过这个对象向数据库发送增删改查语句即可。

• executeQuery():查询操作，返回ResultSet；
• execute():执行任何sql；
• executeUpdate():更新/插入/删除。返回一个受影响的行数。

6.4ResultSet类

查询结果集，封装了所有的查询结果。

• getObject():在不知道列类型的情况下使用
  知道结果的类型就使用指定类型
• getString()/getInt()…
  遍历
• next():移动到下一个数据
• beforeFirst():移动到最前面
• afterLast():移动到最后面
• previous():移动到前一行
• absolute(row):移动到指定行

6.5 PreparedStatement

防止sql注入问题：把传递进来的参数当作字符，假设其中存在转义，比如’。

6.5 注意

需要释放资源

• resultSet.close();
• statement.close();
• connection.close();

6.6 数据库连接池

池化技术：预先准备好一些资源以便连接。