MySQL

概述

存储与管理数据的仓库是为数据库

数据库早期分为：层次式数据库与网络型数据库。目前则分为：关系型数据库，非关系型数据库

关系型数据库是以二维表的形式保存数据

什么是数据库

数据库(database)属于数据库服务器的一部分，每一个数据库（database)大部分情况之下都是存放一个网站中的所有数据，数据库服务器本质就是一个软件，但电脑安装了一个数据库服务器时，就可以对外提供存取数据的服务，一个数据库服务器可以有多个数据库(database)

表

每一个数据库（database）中都可以创建多张表（table），每一张表（table）都可以存放具体的一类数据，表中的每一行都代表一条具体的数据

SQL

SQL是用于操作关系型数据库的通用语言（使用SQL可以操作所有的关系型数据库）

SQL是一个标准通用的操作关系型数据库的语言，但是不同厂商所提供的数据库为了增强自身数据库的功能，都提供了支持自己数据库的语言，称之为方言。方言不可通用

数据库操作

SQL对大小写并不敏感，推荐关键字使用大写，自定义名称使用小写，自定义名称时，多个单词使用下划线连接

库操作

创建一个数据库 create database xxx;或create database if not exists xxx;

查看所有数据库 show databases;

使用某一数据库 use xxx;

删除数据库 drop database xxx;或drop database if exists xxx;

查看建表语句 show create database xxx;

表操作

显示所有表 show tables;

创建一张表 create table xxx(列名 数据类型，列名 数据类型，列名 数据类型....);

删除一张表 drop table xxx;或drop table if exists xxx;

查看表结构 desc stu;

增删改查

增：insert into 表（列名1，列名2...) values(值1，值2...)列名可以省略，但values的值必须与列名顺序一致

删：delete from 表 where 条件判断不加条件默认删除表中所有数据

改：update 表 set 列=值，列=值... where 判断条件

查：select 列名/* from 表名

MySQL数据类型

数值类型

tinyint：占用1个字节，相对于Java中的byte

smallint：占用2个字节，相对于Java中的short

int：占用4个字节，相对于Java中的int

bigint：占用8个字节，相对于Java中long

float：4字节单精度浮点型，相当于Java中的float

double：8字节双精度浮点型，相对于Java中的double

字符串类型

char（n）

定长字符串，最长255个字符，n表示字符数。

如果存入的数据小于指定长度时，剩余的空间会用空格填充，造成空间的浪费

varchar（n）

变长字符串，最长不超过65535个字节，n表示字符数，一般超过255个字符，会使用text类型

根据编码表的不同能够存储的上线字符也有所不同

iso8859-1码表：1个字符占用1个字节
utf8码表：1个中文汉字占用3个字节
GBK码表：1个中文汉字占用2个字节

变长字符串的长度不固定，如果传入数据的长度小于指定长度时，剩余的空间可以给其他的数据继续使用

长文本类型

最长65535个字节，一般超过255个字符列的会使用text

如果需求更大的存储类型则可以使用bigtext类型，存储长度约为4GB

日期类型

date：年月日

time：时分秒

datetime：年月日 时分秒

timestamp：时间戳，与datetime存储日时间格式相同，区别在于timestamp最大表示2038年，而datetime范围时1000-9999，在插入数据，修改数据时，可以自动更新系统当前时间

查询相关

WHERE字句查询

语法： select 列名/* from 表 where 列运算符 值;

where之后跟的是查询条件，条件可以有多个，多个条件之间用(and或or)进行连接

常用的运算符与Java相似，除此之位还有between…and…在某个范围内与like搜索某种模式

ifnull(列名，值)判断传入列名的值是否为null，如果是则替换成指定值

where xxx in(值1，值2...)通过in能够一次性判断多个条件，只要符合其中之一则认为满足要求等效于xxx=xxx or xxx = xxx

is null判断值是否为null

模糊查询

语法：select 列/* from 表 where 列名 like 值

like操作符用在``where`字句中搜索列的指定模式

值可以和通配符%与_配合使用，%代表0个或任意多个字符,_代表任意一个字符;

多行函数查询

语法：select 列/* 聚合函数 from 表

多行函数也叫做聚合函数，根据一列或多列进行统计，如果访问到null值则直接丢弃，不参与统计

如果查询语句使用了分组的话则会根据分的组进行统计，有多少个组就会统计处多少个结果

常用函数：

count(列名|*)		统计结果集中指定列的记录行数
max(列名)			统计结果集中某一列值的最大值
min(列名)			统计结果集中某一列值的最小值
sum(列名)			统计结果集中某一列所有值的和
avg(列名)			统计结果集中某一列值的平均值

分组查询

语法：select * from 表 group by 列名

通过group by能够按照指定列名对查询结果进行分组,可以通过聚合函数来进行一系列的判断

排序查询

语法：select 列名/* from 表 order by 列名 asc/desc

通过order by将查询结果中记录根据指定的列排序后再返回

默认为升序排序asc可以省略，desc则是降序排序

分页查询

语法：select 列名/* from 表 limit 跳过条数，显示条数

通过limit能够实现分页查询，第一个参数为跳过的条数，通过（页面-1）* 显示条数进行计算

其他函数

curdate()			获取当前日期，格式：年月日
curtime()			获取当前时间，格式：时分秒
now()				获取当前日期+时间，格式：年月日 时分秒
year(date)			返回date中的年份
month(date)			返回date中的月份
day(date)			返回date中的天数
hour(date)			返回date中的小时
minute(date)		返回date中的分钟
second(date)		返回date中的秒
concat(s1,s2..)		将s1，s2等多个字符串拼接成一个字符串

字段约束

主键约束(primary key)

给一个列添加了主键约束，则这个列就是主键，主键是唯一且不为空的，主键的主要目的就是给列添加一个唯一标识，如同身份证一般的作用

通常为了方便插入主键并且保证插入的主键不报错，还需要添加一个自增长策略，在列添加主键字段的后面添加auto_increment即可

设置了自增长策略的主键会由数据库自动为主键分配一个值，默认从1开始，后面依次+1，如果手动给主键插入值，为保证自增长可用，下个主键的值将会在本次插入的值的基础上+1

非空约束(not null)

如果给一个列添加了非空约束，那么这个列的值就不能为空(null)，但是可以重复

唯一约束(unique)

如果给一个列添加了一个唯一约束，那么这个列的值必须是唯一不可重复的，但是可以为空

非空约束与唯一约束的连用并不意味着主键约束

外键约束(foreign key)

在本张表创建时的参数中添加foregin key(列名) references 另一张表(列名)

外键是用来通知数据库，两张表数据之间有相应的关系，一张表中的外键则是另一张表中主键

使用外键之后能够让数据库帮忙维护两张表之间的关系，删除另一张表中主键时如果发现其主键还有关联的外键的话，数据库会拒绝执行删除操作并抛出错误

表关系

一对一

对于两张表来说，对应的关系都是唯一的，例如一个班级对应唯一的一间教室，反过来说一间教室也是对应着唯一的班级

一对多

对于两张表来说，有其中一张表的主键被另一张表多次关联，例如一个部门下可以有多个员工，而员工只属于一个部门

多对多

两张表之间有多个互相关联的数据，这时候专门创建第三张关系表来保存两张表之间的多对多关系，例如一个班级的学生有很多的授课教师，而授课教师也需要教授多个班级的学生

多表查询

笛卡尔积查询

查询两张表时，其中一张表有m条数据，另一张表中有n条数据，笛卡尔积查询结果为m*n条数据

左/右外连接

left/right join...on为左/右外连接

left join...on查询的是左表中的所有数据与右表中符合的数据，如果左表中的数据没有查询到右表中的关联数据的话，则右表中的数据显示为null，right join...on则是相反

全外连接查询

将两张表中的所有数据都查询出来称为全外连接

MySQL不支持全外连接查询，但是能够通过union来模拟全外连接查询，具体操作是将两条分别使用了左/右外连接的查询语句通过union进行连接

事务

事务是对数据库的一组操作，这组操作要么都实现，要么都不实现

常规的数据库都提供了对事务的支持，即数据库本身提供了保证一组操作要么都实现，要么都不实现的功能

事务的实现

  • 命令式事务
    在数据库客户端下，使用SQL语句操作事务
    start transaction 开启事务
    commit 提交事务
    rollback 回滚事务
    默认情况下，每条SQL语句独占一个事务
  • 编程式事务
    通过JDBC的API实现数据库事务操作
    conn.setAutoCommit(false) 开启事务
    conn.commit() 提交事务
    conn.rollback() 回滚事务
  • 声明式事务
    利用Spring框架提供的@Transactional注解实现
    @Transactionnal默认只对RuntimeException及其子类异常回滚
    可以通过显示声明rollbackfor属性，来配置该注解对Exception回滚：@Transactional(rollback = Exception.class)@Transacational也可以添加到类上，代表该类中所有的方法都是在事务下运行的

事务的4大特性

事务具备ACID四种特性，Atomic（原子性），Consistency（一致性），Isolation（隔离性），Durability（持久性）的英文缩写

  • 原子性(Atomic)
    事务最基本的操作单元，要么全部成功，要么全部失败，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚至事务开始前的状态
  • 一致性(Consistency)
    事务在执行前后数据库都必须处于一致性状态，即数据库的完整性是一致的。数据的完整性，业务的完整性
  • 隔离性(Isolation)
    并发事务之间，应保证彼此隔离，互不干扰，每个事务都有各自的完整数据空间
  • 持久性(Durability)
    事务只要成功结束，它对数据库所有的更新就必须永久保存下来，其操作结果是持久的，后续再进行多次rollback也不会改变之前的结果

事务的隔离级别

数据库事务的隔离级别是保证事务隔离性的解决方案，在不同的业务场景下对隔离性的要求是不同的，所以数据库设计者设置了不同的隔离级别，隔离级别越高越安全，但是效率也会越低

  • Read uncommitted
    读未提交，一个事务可以读取到另一个事务未提交的操作结果。该级别不保证任何的隔离性。该隔离级别可能会出现脏读，不可重复读，幻读问题
  • Read committed
    读已提交，一个事务仅能读取到另一个事务已提交的数据。可以保证部分隔离性，可以防止脏读问题，但是具有不可重复读和幻读问题
  • Repeatable read
    可重复读，一个事务无法读取另一个后开启事务已提交的操作结果。可以防止脏读，不可重复读，但是会出现幻读
  • serializable
    保证完全隔离，可以防止脏读，幻读，不可重复读，本质是加锁

脏读，幻读，不可重复读

  • 脏读
    一个事务使用了另一个事务未提交的数据
  • 不可重复读
    指在一个事务内，多次读同一数据，得到的结果不同
  • 幻读
    一个事务读取全表数据时，读取到另一个事务向表中新增，删除操作提交的结果

事务传播机制

使用声明式事务@Transactional时可以使用propagation属性来配置事务的传播行为

REQUIRED（默认）:支持使用当前事务，如果当前事务不存在，则创建一个新事务

SUPPORTS:支持使用当前事务，如果当前事务不存在，则不使用使用事务

MANDATORY:中文翻译强制，支持使用当前事务，如果当前事务不存在，则抛出异常

REQUIRES_NEW:创建一个新事务，如果当前事务存在，则把当前事务挂起

NOT_SUPPORTS:无事务执行，如果当前事务存在，把当前事务挂起

NEVER:无事务执行，如果当前有事务则抛出异常

NESTED:嵌套事务，如果当前事务存在，那么在嵌套的事务中执行。如果当前不存在，则表现跟REQUIRED一样