笔记总结五-MySQL

  • 时间:
  • 浏览:
  • 来源:互联网

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一样

本文链接http://www.hatan.cn/news/show-69550.html