MySQL填坑

安装使用(win)

通过这个网站可以下载安装包,记得下载database,下载完成之后根据提示一步一步安装即可。

不同版本的下载使用不同,都是很简单的英文,稍微花点时间就能看懂,这里不多赘述。

2681584243973_.pic_hd

安装的时候如果让你选择了安装位置,那么安装之后的位置就是你自己选择的,如果没有选择,那么默认是安装在C:\Program Files\Mysql下,配置文件在C:\Program Files\MySQL\MySQL Server 8.0\data(win7在C:\ProgramData\Mysql)。Mysql是作为一个服务在Windows下运行的,如果要停止它,可以直接停止服务。

如果要卸载的话,记得卸载完成之后,把数据库的数据也一并处理掉,否则下次会安装不成功。

安装使用(linux)

很简单,直接:

1
2
sudo apt-get update
sudo apt-get install mysql-server

配置文件在/etc/mysql/mysql.conf.d/mysqld.cnf

SQL

SQL是一种关系型数据库通用的语言,自然而然也是适用于MySQL的。

SQL可以简单分成四类:

  • DDL(Data Definition Language),数据定义语言,从Definition也可以知道这个是用来定义数据库、表和列的,代表的是create、drop和alert等
  • DML(Data Manipulation Language),操作语言,常用的增加、修改和删除这三类操作
  • DQL(Data Query Language),用来查数据库的数据的。
  • DCL(Data Control Language),用来控制数据库的,比如创建用户啊,授予用户权限之类的。

下面再对这四类语言进行简单的说明。

DDL-操作数据库和表

其实数据库和表,这两个其实也算是“数据”,所以它们也有增删改查的方法

数据库

  • C(create):创建
    • 创建数据库并且指定字符集:create database if not exists 数据库名 character set 字符集名称;其本质上就是在数据目录下创建了一个和你数据库名同名的文件夹,并在该文件夹下面放了一个叫db.opt的文件,该文件里面写了字符编码。
  • R(Retrieve):查询
    • 查询所有数据库的名字:show databases;
    • 查询特定数据库的字符集:show create database 数据库名
  • U(Update):更新
    • 修改数据库的字符集:alter database 数据库名称 character set 字符集名称
  • D(Drop):删除
    • 删除指定数据库:drop database if exists 数据库名
  • 使用数据库:
    • 之前也说了,数据库其实是一个文件夹,所以需要进入这个文件夹:use 数据库名
    • 查询目前正在使用的数据库:select database();

  • C(create):创建
    • 创建表:create table 表名(列名1 数据类型,列名2 数据类型);
    • 常见数据类型:
      • int 整数
      • double(5,2) 五位小数,小数点后两位,最大即999.99
      • date 只包含年月日的日期
      • datetime 包含年月日和时分秒的日期
      • timestamp 时间戳,和datetime表达时间一致,只是系统自动会给当前时间,即系统自动会赋值当前时间
      • varchar(20) 字符串,最大20个字符
    • 复制一个表:create table 表名 like 被复制的表名;
  • R(Retrieve):查询
    • 查询某个数据库中所有的表的名字:show tables;
    • 查询表结构:desc 表名
    • 查询表的字符集:show create table 表名
  • U(Update):更新
    • 修改表名:alter table 表名 rename to 新表名
    • 修改表的字符集:alter table 表名 chracter set 字符集;
    • 添加一列:alter table 表名 add 列名 数据类型;
    • 修改一列:alter table 表名 change 列名 新列名 新的数据类型;
    • 删除一列:alter table 表名 drop 列名;
  • D(Drop):删除
    • 删除指定数据库:drop table if exists 表名

DML-增删改表中数据

  • 添加数据:insert into 表名(列名1...列名n) values(值1...值n);,如果给所有的列进行赋值,那可以不写列名
  • 删除数据:delete from 表名 [where ....]; 不加条件会把表里所有的数据都删除掉,切记!如果要删除表里的所有数据,请使用TRUNCATE TABLE 表名,它会完全删除表,然后在创建一个一模一样的,没有数据的表。
  • 修改数据:update 表名 set 列名1=值1 .... [where 条件];

DQL-查询表中数据

总体的语法:

1
2
3
4
5
6
7
select 列名1,列名2....
from 表名1,表名2....
where 条件1,条件2....
group by 分组条件1,分组条件2....
having 分组之后的条件1,分组之后的条件2
order by 排序条件
limit 分页限定

基础查询

  • 多个字段的查询:select 字段名1,字段名2 from 表名; 虽然可以通过*来表示全部,但是推荐写上所有的表名,清楚明了。

  • 去重:使用select distinct 字段名1,字段名2 from 表名;,去重必须要结果集完完全全一致才可以去掉。

  • 计算列:对列的值进行计算,可以直接写表达式;如果有null参与,则答案全是null;所以如果要去除null的影响,可以使用ifnull(列名,替换值),意思就是当该列为null的时候,用替换值来进行计算。

  • 起别名:直接用as关键字就可以换列名。

条件查询

条件查询就是跟在where子句后面的内容。

  • 大于小于不等于和Java一样,如果是范围查询,比如[20,30],可以用between 20 and 30,当然各种条件之间也可以用and or not。唯一跟之前不一样的,就是可以用in关键字来找集合内的。
  • null值不能使用=等去判断,必须使用is null来进行判断。
  • like作为模糊查询,有两个占位符,_单个任意字符,%任意多个任意字符。

排序查询

Order by语句之后,基础语法就是order by 排序字段 排序方法,排序字段 排序方法;

排序方法有asc升序desc降序。且只有当前面的条件是相同的时候,才会考虑后面的排序。

聚合函数

简单来说,就是将一列数据看成是一个整体,进行纵向的计算。值得注意的是聚合函数的计算会排除Null。

  • count() 计算个数,选择非空的列,主键尤其合适
  • max() 计算最大值
  • min() 计算最小值
  • sum() 求和
  • avg() 计算平均值

分组

group by 分组字段,分组之后的查询函数,要么使用分组字段,要么使用聚合函数,其它的查询字段没有意义。

where这个关键字所做的条件约束,是在分组之前,也就是不满足where中的条件,则不参与分组,而having中的条件,则是在分组之后生效,不满足则不显示。两者的第二个区别是where中不能使用聚合函数,having中可以。

分页

语法:limit 开始的索引,每页查询的条数,索引从0开始。开始的索引计算公式=(当前页码-1)*每页所显示条数

limit分页是MySQL的方言。

DCL-管理用户

这里只进行简单的介绍,因为这个操作不是开发人员来做的,是由DBA来操作的。MySQL对于用户的数据的操作,其实本质上还是操作一个叫mysql的数据库,里面有一张叫user的表(但是别自己修改这张表)。

  • 新增用户:create user '用户名'@'主机名' identified by '密码';,主机名如果是%则说明任意主机都可以。
  • 删除用户:drop user '用户名'@'主机名';
  • 修改用户密码:set password for '用户名'@'主机名' = password('新密码');
  • 特殊操作-root密码忘记了
    • 首先关闭mysql,在win下用管理员cmd下net stop mysql,linux下sudo service mysql stop
    • 再次启动服务:mysqld --skip-grant-tables,然后重开一个shell直接输入mysql即可登录,改密码就行了。改完密码记得kill掉mysqld,完事儿。

权限操作:

  • 查询某个用户的权限:show grants for '用户名'@'主机名';
  • 授予权限:grant 权限列表 on 数据库名.表名 to '用户名'@'主机名',其中ALL可以代表所有权限,*可以代表所有数据库名和所有表名。
  • 撤销权限:revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'

约束

对表里的数据进行约束,保证数据的正确性、准确性。有四种分类:

  • 非空约束 not null,直接在创建表的时候可以直接定,也可以通过之前DDL中修改表中属性进行修改(新增约束和删除约束都可以)。
  • 唯一约束 unique,注意的是,null是可以重复的。如果需要删除这个约束,必须使用alter table 表名 drop index 列名;如果需要增加这个约束(当然需要首先保证列中没有重复的),和DDL中修改表中属性相同。
  • 主键约束 primary key,非空且唯一。一张表只能有一个字段作为主键。删除主键需要用alter table 表名 drop primary key;,因为一张表只能有一个主键,所以可以这么写。添加和上面的是一样的。
    • 自动增长:如果某一列是数值类型,那么可以用auto_increment来实现自动增长,数据只跟上一个对应的值有关系。一般配合主键使用,这样主键就可以是NULL了。自动增长可以通过普通修改列属性进行删除和添加。
  • 外键约束 foreign key,语法特别麻烦。外键是可以使null的。
    • 在建表的最后:constraint 自己取的外键名称 foreign key (外键列名称) references 主表的名称(被关联的主表中的列名,一般是主键)
    • 删除外键:alter table 表名 drop foreign key 外键名称;
    • 添加外键:alter table 表名 add 然后再加上上面“建表的最后中那一长串”即可。
    • 设置级联更新:在最后加上on update cascade即可。
    • 设置级联删除:在最后加上on delete cascade即可。级联操作切记,很危险!

数据库设计

表之间的关系

  • 一对一:两者之间可以互相反推,即每个A只能有每个B,而每个B也只能对应每个A。但是实际中比较少。
  • 一对多:一个A只能有一个B,而一个B可以有多个A。
  • 多对多:这个更简单了,现实中非常多。每个A都可以有多个B,同样每个B也可以有多个A。

如何实现这些关系呢?

  • 一对多:假设员工和部门,一个员工只能在一个部门,但是一个部门里可以有多个员工,即员工是多,部门是1,那么可以在多的那端,即员工,新增一个外键来对应部门的主键。在多的一方建立外键,指向一的一方的主键即可。
  • 多对多:假设学生和课程。可以创建一张中间表,中间表中至少包含两个字段,分别指向两张表的主键。也可以说是,中间表的外键指向了两张表的主键,最后可以让这两列合二为一作为一个主键,用primary key(id1,id2);即可。
  • 一对一:更简单了,只需要在任意一张表里添加另一张表的外键,且该外键唯一即可。

范式

就是设计数据库的时候,需要遵循的规则,主要是为了解决冗余。

  • 第一范式:每一列都是不可分割的原子数据项。最简单的就是,每一列下面又有多个子列,比如部门下面又有部门名称和部门经理这两个小列。如要使其符合第一范式,只需要分割一下就行了。当然不符合第一范式的表格在数据库中连创建都没办法创建。

  • 第二范式:在第一范式的基础上,非码属性必须完全依赖于码(定义见下)。具体做法是,首先分析出一张表里,通过哪个属性组可以唯一确定其他所有的值(也就是找到码),然后分析除了码中的属性,其他另外的属性中,那些部分依赖的列(也就是只需要码中的部分属性就可以确定的),把它们拆分到另外一张表格中。这样表里面就只剩下完全依赖于码的列和码本身了。

    • 函数依赖:如果通过A能够得到B,那么就说B依赖于A,例子就是通过身份证号肯定能够确定你的名字,那么名字可以说依赖于身份证号码。简单来说就是某个属性(或者多个属性,称为属性组)能不能唯一确定另外的一个值。
    • 完全函数依赖:如果需要属性组内所有的属性来确定另外一个值,那么就说是完全依赖。比如成绩,单靠学号无法确定成绩,单靠学科也不可以,但是二者一起就可以,那么说成绩完全函数依赖于(学号,成绩)。
    • 部分函数依赖:只需要依赖属性组其中的一些属性即可。
    • 传递函数依赖:看名字就知道,A->B,B->C
    • 码:在一张表中,一个属性(属性组),被其它所有属性所完全依赖,那么这个属性(属性组)就是这个表的码。
      • 主属性:码这个属性组里面的所有属性。
      • 非主属性:其余的。
  • 第三范式:在第二范式的基础上,任何非主属性不依赖于其它非主属性,即非主属性只能依赖于主属性。这样做是为了消除依赖传递。

备份和还原

  • 备份的语法:mysqldump -u用户名 -p密码 数据库名字 > /root/backup.sql 备份操作不需要登录到数据库中。打开这个文件你会发现,本质上其实就是建表、插入数据的操作。
  • 还原的语法:首先需要登录到数据库中,然后创建一个数据库,进入到这个数据库里,并且使用source /root/backup.sql即可。我当时用的是远程连接,即我登录到另外一台机器上的mysql中,直接用source提示文件不存在,后来经过测试,source执行的是你当前所用机器中的对应位置的sql文件来创建表格和数据的。

多表查询

默认多表查询,查询出来的会是笛卡尔积,即A表中的每一条都会和B表中的每一条发生关系。但是实际中显然这个不是我们需要的,所以需要下面的几个查询来帮助。

内连接查询

  • 隐式内连接查询:使用where条件来消除没有用的数据。直接where table1.name = table2.name这样子就可以了。
  • 显示内连接查询:select 字段列表 from 表名1 [inner] join 表名2 on 条件;,本质上和上面的隐式内连接一模一样。
  • 一些tips:
    • 确定从哪些表里取数据
    • 确定好条件
    • 确定好哪些字段是需要的

###外连接查询

如何区分左右表:先出现的就是左表,后出现的就是右表。

  • 左外连接:select 字段列表 from 表名1 left [outer] join 表名2 on 条件;查询的是左表所有数据及其交集的所有部分。内连接就是仅仅交集的部分。
  • 右外连接:select 字段列表 from 表名1 right [outer] join 表名2 on 条件;

子查询

查询中嵌套查询,就叫子查询。

  • 如果子查询的结果是单行单列的:直接作为条件,可以用<>=这些符号来进行。
  • 如果子查询的结果是多行单列的:也可以作为条件,直接用in运算符来操作。
  • 如果子查询的结果是多行多列的:直接作为一张表,然后搞个别名,就可以了。

事务

事务简单来说就是用来管理多个步骤的业务操作的,这些业务操作要么同时成功,要么都失败。最简单的例子就是银行转账。如果其中某一步骤失败了,那么就会回滚。

基本操作

  • 开启事务:start transaction/begin
  • 回滚:rollback
  • 提交事务:commit

在MySQL中,一条DML语句就会提交一个事务。查看事务的默认提交方式:select @@autocommit;,关闭自动提交:set @@autocommit=0;oracle默认是手动提交。

四大特征:

  • 原子性:是不可分割的最小操作单位。
  • 持久性:如果事务提交或者回滚后,数据库就会持久化的变化。
  • 隔离性:多个事务之间相互独立,但是实际中会发生影响。
  • 一致性:事务操作前后数据总量不变。

隔离级别

多个事务操作同一组数据,就可能发生问题。可能存在的问题:

  • 脏读:一个事务读取到另外一个事务没有提交的数据。
  • 不可重复读:同一个事务中,两次读到的数据不一样。即你已经使用start transaction开启事务,但是使用select查询了两次,发现前后不一致。
  • 幻读:一个事务对所有记录进行了增删改记录,同时另外一个事务新增了一条数据,那么第一个事务就会看不到自己的修改。

MySQL支持四种隔离级别,查询使用select @@tx_isolation;,设置的话用set global transaction isolation level 级别;,设置完需要重启连接才会生效。

  • read uncommited:以上三种问题都可能会发生。
  • read commited:只有一个事务读完,另外一个事务才可以继续读。脏读在这里就不会发生了,但是幻读还是会发生。
  • repeatable read:只会产生幻读。(mysql默认的级别,而且mysql在这个模式下并不会发生幻读。)
  • Serializable:这里叫串行化,解决所有的问题。实现的原理就是只要对某一个表格开启了事务,那就把那张表格锁定,就不会有问题了。

那么mysql是如何实现这些隔离级别的呢?

  • read uncommited:不需要做任何处理。
  • read commited:每一行其实隐藏了两个列来完成这个功能,分别叫trx_id和roll_pointer,第一个trx_id用来记录事务的id(即哪个事务修改为这条数据),而roll_pointer则是用来回滚到上一个事务修改的。而每一个事务,则在生成的时候,获得一个叫readView的东西,这个东西里面有当前系统中,正活跃的事务的id,有了它,配合上之前行中的隐藏信息,就可以判断出哪些信息“应该”被自己所读到(注意,其实它是能看到所有的事务所做的修改的,不论有没有commit,只不过它自己选择了忽视那些它不应该看到的)。
  • repeatable read:只在第一次读取的时候获得ReadView,之后每次都不更新,即复用第一次获得即可。
  • Serializable:把表锁住即可。

其实上面这四种实现方式就是那么简单,每个事务只需要在要读取的时候获取一下ReadView(根据生成ReadView的时机不同,来区分两种隔离模式),然后看看目前正在活跃的这些事务和行数据中,有没有匹配的,如果有就说明目前事务正在修改,如果没有就说明事务已经提交了,这就是大名鼎鼎的MVCC(Multi-Version Concurrency Control),

连接池

为什么要用连接池呢?因为在实际中,获取连接然后释放资源非常浪费资源,所以想到了一个办法:申请一个容器,里面有很多初始化好的connection对象,要用的时候问容器要,用完之后会把连接对象还给容器。Java标准中就有一个叫DataSource的接口:

  • getConnection() 获取连接对象
  • 如果连接对象是从连接池里拿的,那么connection.close()方法会归还这个对象。

Druid

你无法抵挡大自然的力量!这个是阿里巴巴团队出品的一个数据库连接池实现,直接去他们官网查看即可。

原理部分

这部分相较于上一部分有所深入。

我们执行一条sql语句,比如select * from users where id > 1000;这条语句,sql中的innoDB会给你拿出一页,因为根据局部性原理:一旦你用了某个数据,你极有可能用到这个数据部分附近的数据。

这里说的页,其实本质上和操作系统中页很相似,这里的是innoDB的页数据,可以通过show global status like 'InnoDB_page_size';来查看,默认是16KB。存放不同的数据有不同的页,页的结构如下所示:

名称 中文名 占用空间大小 简单描述
File Header 文件头 38字节 描述页的信息
Page Header 页头 56字节 页的状态信息
Infimum + SupreMum 最小记录和最大记录 26字节 两个虚拟的行记录
User Records 用户记录 不确定 实际存储的行记录内容
Free Space 空闲空间 不确定 页中尚未使用的空间
Page Directory 页目录 不确定 页中的记录相对位置
File Trailer 文件结尾 8字节 结尾信息

行信息

从表中查出的一行一行的数据,在我们眼前显示的是行,但是实际在磁盘中是如何存储的呢?

image-20200405004142408

  • 变长字段长度列表,这是为了类似varchar(10)这样的变长数据用的,记录这些变长字段所对应的长度。
  • NULL字段为了节省空间而设计的。
  • 记录头则是一些额外的信息以及寻找下一条记录的地址,一共是5个字节。
  • 一行数据最多为65535个字节(不包括上面的记录头,即不算这5个字节,同样也不包括下面三个隐藏列),而变长字段长度列表(2字节)+NULL标志位(1字节)加起来是3字节。
  • 最后还有一个额外的记录,有三个隐藏列,分别用来标识这条行记录的id值(如果你的表没有主键也没有唯一值的话,mysql会帮你增加一个主键,就是这个id)、记录事务的id和回滚的指针。

上面说了一页是16KB=16*1024=16384字节,显然一行的数据大于一页的数据,即行溢出。那么有两种处理方式:一种是我在一页的最后稍微留出一丢丢空间来指向下一页的地址;还有一种使用一页里面全部存放地址,然后从下一页开始再使用上面的那种,好处是这一页就可以存非常多的数据。

索引

根据上面的页和行的关系,其实我们已经可以看到一些索引的端倪了。

在你插入数据的同时,mysql会帮你把这些数据按照主键来进行排序(排序规则有很多,可以自己指定),并且生成B+树,B+树简单来说就是一个多路查找的平衡N叉树。索引就是靠它,可以非常快速的找到想要的东西。

􏶄􏺩􏱨聚簇索引

聚簇索引的特点:

  • 叶子节点内部用的是主键从小到大排列的单项链表。
  • 叶子节点之间用的是主键从小到大排列的双向链表。所以只要走到叶子节点,就可以遍历完整个B+树了。
  • B+树的同一层的页目录是按照主键从小到大排列的双向链表。

我们甚至不需要显式的进行创建,InnoDB自动为我们创建。

二级索引

显然如果我们可以保证我们之后每次查询用的都是主键,那么确实只需要聚簇索引就够了。但是如果我们还想用别的索引,那就需要用到二级索引了。

其实二级索引非常简单:

  • 按照给定的索引的值来进行排序(废话)
  • 叶子节点存的不是一条记录,而是主键值,你找到了主键值,再根据主键值回到上面的聚簇索引中搜索你要的数据。这个过程也叫回表。

说白了就是额外再来个B+树…只不过不存行的数据而是存放了主键而已。

首先需要明确一下,如果仅仅是select,那么是不会上锁,也不会受锁的影响的。接下来锁只有两种,一种是读锁(Shared Locks),另外一种是写锁(Exclusive Locks),除了读锁上面还能加读锁,其它所有情况下的锁都是互斥的。

读操作

select ... lock in share mode;,为查出的数据加上一把读锁,这样别的事务就不能改了,但是自己也不一定能改(因为别人也是可以再加入读锁的)。

select ... for update;,为查出的数据加上一把写锁,这样除了自己以外,别人均无法读写。

写操作

delete:先对记录增加写锁,然后再删除。

insert:隐式写锁。这个相当于之前的事务id对比,如果有另外一个事务想来对这条记录(尚未commit)进行加锁,发现id不对称,此时会发生写锁。

update:如果空间不会发生变化,直接加写锁修改;如果空间发生变化,则还是先加写锁,然后删除并且insert一条。

行锁

首先需要明白一点,MySQL InnoDB的锁就是靠锁住索引项来实现的,而不是锁住一条数据来实现的。

  • LOCK_REC_NOT_GAP:单个行记录上的锁。
  • LOCK_GAP:间隙锁,开区间锁,由于开区间,可能导致幻读。(因为别人可以继续插入数据)
  • LOCK_ORDINARY:闭区间锁,解决了幻读。

在􏱜􏲃􏲚􏱥read committed级别下,只会对查出的数据进行加行锁;而在repeatable read下,使用主键和唯一索引还是锁的查出来的数据,但是使用普通索引会加上间隙锁,不使用索引的会对表里查出的所有记录以及间隙加锁。