MySQL基础使用

MySQL数据库

简介

数据库：

Database，简称DB 就是按一定格式存储数据的一些文件的组合

数据库管理系统：

​ Database Management System，简称DBMS

​ 是专门用来管理数据库中的数据

SQL (Structure Query Language)：

​ 结构化查询语言

​

三者之间的关系：

​ DBMS –执行 -> SQL –操作-> DB

数据库当中最基本的单元就是表 table

SQL大小写并不敏感，但是如果是引起来的字符，则是区分大小写的，

命名规范：

​ 所有的标识符都是小写，单词和单词之间使用下划线进行衔接

SQL语句的分类

DQL（Data Query Language）：

​ 数据查询语言 （凡是带有select关键字的都是DQL）

DML（Data Manipulation Language）：

​ 数据操作语言（凡是对表当中的数据进行增删改的都是DML）

​ 主要操作表中的数据

DDL（Data Definition Languages）：

​ 数据定义语言（凡是带有create，drop，alter的都是DDL）

​ 主要操作的是表的结构，不是表中的数据

TCL（Transaction Control Language）：

​ 事务控制语言

DCL（Data Control Language）：

​ 数据控制语言

==DQL==

单表查询

select 表名 from 数据库 where 条件 order by ;

改别名
select name as 'newname' from 数据库; -- 可以不加单引号，但有空格一定要加

条件语句
between ... and ... 	-- 等同于 >= and <=
is null 		-- null不能用等号，因为null代表什么都没有，不是一个值
and 并且  or 或者  -- 同时出现的话，and优先级高，可以用小括号改变优先级
in 包含  	     -- 等于多个 or  in不是区间，而是具体的值
like 模糊查询   -- %匹配多个字符，_匹配一个字符

排序
order by 字段 (asc/ desc) -- 升序和降序,不写默认升序

单行处理函数特点：一个输入对应一个输出。

多行处理函数特点：多个输入，对应1个输出！

单行处理函数

函数	含义
Lower	转换小写
upper	转换大写
substr	取子串（substr(被截取的字符串,起始下标, 截取的长度**)**）下标从1开始
length	取长度
trim	去空格
str_to_date	将字符串转换成日期 varchar转date
date_format	格式化日期 data转varchar
format	设置千分位
round	四舍五入 (值，取整位数)
rand()	生成随机数
ifnull	可以将 null 转换成一个具体值

多行处理函数（分组函数）

函数	含义
count	取得记录数
sum	求和
avg	取平均
max	取最大的数
min	取最小的数

分组函数自动忽略null

count(具体字段) ：表示统计该字段下所有不为null的元素总数量

count（*）：统计表当中的总行数

分组函数不能直接使用在where语句中，==where后面只能跟表中存在的字段==

因为分组函数在使用的时候必须先分组之后才能使用

分组查询

select 表名 from 数据库 where 条件 group by ... having ... order by ... ;
执行顺序 1 from 2 where 3 group by 4 having 5 select 6 order by

在一条select语句中， 如果有group by语句，

select后面只能跟：参加分组的字段和分组函数

如果查询结果 想去除重复记录 使用 distinct

select distinct job,deptno from emp;    -- 只能写在所有字段的前面 

连接查询 （多表查询）

内连接：

• 等值连接
• 非等值连接
• 自连接

外连接：

• 左连接（左外连接）
• 右连接（右外连接）
• 全连接

当两张表进行连接查询，没有任何条件限制的时候，最终的查询结果条数是两张表条数的乘积

这种现象被称为：==笛卡尔积现象==

表的连接次数越多， 执行效率越低，尽量避免表的连接次数

笛卡尔积现象不可避免，但可以通过加条件过滤来筛选结果，但匹配次数没有减少

内连接

select ... from  a (inner) join b on a和b的连接条件 where 筛选条件

等值连接: 连接条件是一个等量关系

非等值连接：连接条件不是一个等量关系

自连接：把自身看成两个表

外连接

​ 表与表之间有 主次之分

select ... from a left/ right (outer) join b on a和b的连接条件 where 筛选条件

左连接：带有left为主表， 查询结果 >= 内连接的查询结果 + 左表的查询结果

右连接：带有right为主表，查询结果 >= 内连接的查询结果 + 右表的查询结果

多表连接

select   
			...
		from
			a
		join
			b
		on
			a和b的连接条件
		join
			c
		on
			a和c的连接条件
		right join
			d
		on
			a和d的连接条件       -- 内连接和外连接可以混合

子查询

​ select语句中嵌套select语句，被嵌套的select语句就是子查询

可以出现在哪？

select
	..(select)
from
	..(select)
where
	..(select)

union 合并查询结果集

​ union的效率比表连接高一些，因为union没有笛卡尔积，减少了匹配的次数

​ 把乘法变成了加法运算

​ 要求两个结果集的列数相同 (在Oracle里数据类型也要相同)

limit

​ 将查询结果集的一部分提出来，通常使用在分页查询当中

​ 分页的作用是为了提高用户体验

​ 分页公式：limit (pageNo - 1) * pageSize

select 
		ename,sal
	from
		emp
	order by 
		sal desc
	limit 5;  -- 取前5   也可以写成 0, 5   limit startIndex, length

DDL

create table 表名(			 -- 创建 
	字段名1 数据类型, 
	字段名2 数据类型, 
	字段名3 数据类型
);

drop table (if exists) 表名;    -- 删除

alter table 表名 add(
   	字段名1 数据类型, 
	字段名2 数据类型,
   )

常见数据类型

==varchar(最长255)==

​ 可变长度的字符串
​ 比较智能，节省空间
​ 会根据实际的数据长度动态分配空间

​ 优点：节省空间
​ 缺点：需要动态分配空间，速度慢

==char(最长255)==

​ 定长字符串
​ 不管实际的数据长度是多少
​ 分配固定长度的空间去存储数据
​ 使用不恰当的时候，可能会导致空间的浪费

​ 优点：不需要动态分配空间，速度快
​ 缺点：使用不当可能会导致空间的浪费

varchar和char我们应该怎么选择？
性别字段你选什么？因为性别是固定长度的字符串，所以选择char
姓名字段你选什么？每一个人的名字长度不同，所以选择varchar

==int(最长11)==
数字中的整数型。等同于java的 int

==bigint==

​ 数字中的长整型。等同于java中的long

==float==

​ 单精度浮点型数据

==double==

​ 双精度浮点型数据

==date==

​ 短日期类型

==datetime==

​ 长日期类型

==clob==

​ 字符大对象
​ 最多可以存储4G的字符串。
​ 比如：存储一篇文章，存储一个说明。
​ 超过255个字符的都要采用CLOB字符大对象来存储。
​ Character Large Object

==blob==

​ 二进制大对象
​ Binary Large Object
​ 专门用来存储图片、声音、视频等流媒体数据。
​ 往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
​ 你需要使用IO流才行

DML

insert into 表名(字段名1，字段名2..) values(值1，值2..);  -- 插入
    	-- 字段名和值要一一对应 ，数据类型要对应，数量也要对应
    	
update 表名 set 字段名1=值1,字段名2=值2,字段名3=值3... where 条件; -- 改
		-- 注意：没有条件限制会导致所有数据全部更新
		
delete from 表名 where 条件;  
		-- 注意：没有条件，整张表的数据会全部删除！

delete语句删除数据的原理

​ 表中的数据被删除，但是这个数据在硬盘上的真实存储空间不会被释放

​ 缺点：删除效率低

​ 优点：支持回滚，后悔了可以恢复数据

truncate语句删除数据的原理

​ 效率高，表被一次截断，物理删除

​ 缺点：不支持回滚

​ 优点：快速

==约束==

constraint

​ 非空约束：not null

​ 唯一性约束： unique

​ 主键约束： primary key

​ 外键约束： foreign key

​ 检查约束： check （ MySQL不支持，Oracle支持 ）

约束的书写位置：

​ 列级约束：写在列后面

​ 表级约束：没有写在列后面，一般用于联合约束，复合主键(不建议使用)

MySQL中一个字段 加not null和unique 自动变成主键

联合约束

​ unique（字段1，字段2） 两个字段值相同时才报错

主键

一个表中只能存在 一个主键

不建议使用varchar，一般主键值都是数字，都是 定长的

自然主键：主键值是一个自然数

业务主键：主键值和业务紧密关联, 例如拿银行卡账号做主键值

在实际开发中使用自然主键多，因为主键只要做到不重复就行了，不需要有意义

业务主键，当业务发生变动的时候，可能会影响到主键值

实现主键值自增：auto_increment

外键

foreign key(字段名) references 父表名(字段)

引用表是父表，使用外键的表是子表

​ 删除表的顺序？
​ 先删子，再删父。

​ 创建表的顺序？
​ 先创建父，再创建子。

​ 删除数据的顺序？
​ 先删子，再删父。

​ 插入数据的顺序？
​ 先插入父，再插入子。

子表中的外键引用的父表中的某个字段，被引用的这个字段必须是主键吗？
不一定是主键，但至少具有unique约束

​

外键值可以为NULL

存储引擎

MySQL中特有的一个术语，其他数据库中没有

存储引擎实际上就是==一个表存储/组织数据的方式==

MySQL默认 engine：InnoDB

​ charset：utf8

常用存储引擎

MyISAM存储引擎

​ 它管理的表具有以下特征：
​ 使用三个文件表示每个表：
​ 格式文件 — 存储表结构的定义（mytable.frm）
​ 数据文件 — 存储表行的内容（mytable.MYD）
​ 索引文件 — 存储表上索引（mytable.MYI）：索引是一本书的目录，缩小扫描范围，提高查询效率的一种机制。
​ 可被转换为压缩、只读表来节省空间

​ 提示一下：
​ 对于一张表来说，只要是主键，
​ 或者加有unique约束的字段上会自动创建索引。

​ MyISAM存储引擎特点：
​ 可被转换为压缩、只读表来节省空间
​ 这是这种存储引擎的优势！！！！
​
​ MyISAM不支持事务机制，安全性低。

InnoDB存储引擎

​ 这是mysql默认的存储引擎，同时也是一个重量级的存储引擎。
​ InnoDB支持事务，支持数据库崩溃后自动恢复机制。
​ InnoDB存储引擎最主要的特点是：非常安全。

它管理的表具有下列主要特征：
– 每个 InnoDB 表在数据库目录中以.frm 格式文件表示
– InnoDB 表空间 tablespace 被用于存储表的内容（表空间是一个逻辑名称。表空间存储数据+索引。）

​ – 提供一组用来记录事务性活动的日志文件
​ – 用 COMMIT(提交)、SAVEPOINT 及ROLLBACK(回滚)支持事务处理
​ – 提供全 ACID 兼容
​ – 在 MySQL 服务器崩溃后提供自动恢复
​ – 多版本（MVCC）和行级锁定
​ – 支持外键及引用的完整性，包括级联删除和更新

InnoDB最大的特点就是支持事务：
以保证数据的安全。效率不是很高，并且也不能压缩，不能转换为只读，
不能很好的节省存储空间。

MEMORY存储引擎

使用 MEMORY 存储引擎的表，其数据存储在内存中，且行的长度固定，
这两个特点使得 MEMORY 存储引擎非常快。

MEMORY 存储引擎管理的表具有下列特征：
– 在数据库目录内，每个表均以.frm 格式的文件表示。
– 表数据及索引被存储在内存中。（目的就是快，查询快！）
– 表级锁机制。
– 不能包含 TEXT 或 BLOB 字段。

MEMORY 存储引擎以前被称为HEAP 引擎。

MEMORY引擎优点：查询效率是最高的。不需要和硬盘交互。
MEMORY引擎缺点：不安全，关机之后数据消失。因为数据和索引都是在内存当中

==事务==

Transaction

一个事务就是一个完整的业务逻辑，是一个最小的工作单元，不可再分.

什么是一个完整的业务逻辑？
假设转账，从A账户向B账户中转账10000.
将A账户的钱减 去10000（update语句）
将B账户的钱加上10000（update语句）
这就是一个完整的业务逻辑。

​ 以上的操作是一个最小的工作单元，要么同时成功，要么同时失败，不可再分。
​ 这两个update语句要求必须同时成功或者同时失败，这样才能保证钱是正确的。

只有DML语句才有事务，因为一旦涉及数据的增删改，一定要考虑安全问题

到底什么是事务？

​ 本质上，一个事务就是多条DML语句同时成功，或者同时失败

事务是怎么做到多条DML语句同时成功和同时失败的呢？

​ InnoDB存储引擎：提供一组用来记录事务性活动的日志文件

在事务的执行过程中，每一条DML的操作都会记录到“事务性活动的日志文件”中
在事务的执行过程中，我们可以提交事务，也可以回滚事务

提交事务
清空事务性活动的日志文件，将数据全部彻底持久化到数据库表中
提交事务标志着，事务的结束。并且是一种全部成功的结束

回滚事务
将之前所有的DML操作全部撤销，并且清空事务性活动的日志文件
回滚事务标志着，事务的结束，并且是一种全部失败的结束

提交事务：commit; 语句

回滚事务：rollback; 语句

MySQL默认的事务行为是支持 自动提交事务

也就是每执行一条DML语句，就提交一次

自动提交实际上不符合我们的开发习惯，因为一个业务通常是需要多条DML语句共同执行才能完成的，为了保证数据的安全性，必须要求同时成功之后再提交，所有不能执行一条就提交一条

​ 关闭自动提交机制 命令： start transaction;

事务的四个特性

A：原子性 atomicity
事务是最小的工作单元, 不可再分

C：一致性 consistency
在事务开始之前和结束之后，数据库都保持一致状态

I：隔离性 isolation
A事务和B事务之间具有一定的隔离
教室A和教室B之间有一道墙，这道墙就是隔离性

D：持久性 durability
指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统 崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能够将其恢复到事 务成功结束后的状态

隔离级别

1. 读未提交：read uncommitted （没有提交就能读）

​ 事务A可以读取到事务B未提交的数据

​ 这种现象存在的问题：脏读（dirty read）

​ 这种隔离级别都是理论上的，大多数的数据库隔离级别都是二档起步

2. 读已提交：read committed （提交之后才能读）

​ 事务A只能读取到事务B提交之后的数据。

​ 这种隔离级别解决了什么问题？

​ 解决了脏读的现象。

​ 这种隔离级别存在什么问题？

​ 不可重复读取数据。

什么是不可重复读取数据呢？

​ 在事务开启之后，第一次读到的数据是3条，当前事务还没有
​ 结束，可能第二次再读取的时候，读到的数据是4条，3不等于4
​ 称为不可重复读取。

​ 这种隔离级别是比较真实的数据，每一次读到的数据是绝对的真实

​ oracle数据库默认的隔离级别是：read committed

3. 可重复读：repeatable read （提交之后读取的永远都是刚开启事务时的数据）

​ 什么是可重复读取？

​ 事务A开启之后，不管是多久，每一次在事务A中读取到的数据

​ 都是一致的。即使事务B将数据已经修改，并且提交了，事务A

​ 读取到的数据还是没有发生改变，这就是可重复读

​ 可重复读解决了什么问题？

​ 解决了不可重复读取数据。

可重复读存在的问题是什么？

​ 可以会出现幻影读。

​ 每一次读取到的数据都是幻象。不够真实！
​

早晨9点开始开启了事务，只要事务不结束，到晚上9点，读到的数据还是那样！

读到的是假象。不够绝对的真实。

mysql中默认的事务隔离级别就是这个！

4. 序列化/串行化：serializable

​ 这是最高隔离级别，效率最低。解决了所有的问题。

​ 这种隔离级别表示事务排队，不能并发！

​ 每一次读取到的数据都是最真实的，并且效率是最低的。
​

索引

index

​ 索引是在数据库表的字段上添加的，为了==提高查询效率==存在的一种机制

​ 一个字段可以添加一个索引，多个字段联合起来也可以添加索引

​ 索引等于一本书的目录，为了==缩小扫描范围==而存在的一种机制

-- 创建索引
create index 索引名 on 表(字段);
-- 删除索引
drop index 索引名 on 表; 
-- 查找是否使用索引
explain ~~;

mysql查询的两种方式：

1. 全盘扫描
2. 根据索引查询

在mysql中的索引是一个 B+Tree 的数据结构

遵循左小右大原则存放, 采用中序遍历方式遍历取数据

提醒1：在任何数据库当中主键上都会自动添加索引对象

另外在mysql当中，一个字段上如果有unique约束的话，也会自动
创建索引对象。

提醒2：在任何数据库当中，任何一张表的任何一条记录在硬盘存储上都有

一个硬盘的物理存储编号。

提醒3：在mysql当中，索引是一个单独的对象，不同的存储引擎以不同的形式

存在，在MyISAM存储引擎中，索引存储在一个.MYI文件中。在InnoDB存储引擎中

索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引

被存储在内存当中。不管索引存储在哪里，索引在mysql当中都是一个树的形式

存在。（自平衡二叉树：B-Tree）

大概原理 （不严谨）

什么条件下，我们会考虑给字段添加索引？

​ 条件1：数据量庞大

​ 条件2：该字段经常出现在where的后面，以条件的形式存在，也就是说总是被扫描

​ 条件3：该字段很少DML操作（增删改）,因为DML之后索引需要重新排序

不要随便添加索引，因为索引也是需要维护的没太多反而降低系统的性能

建议通过主键，unique的字段来进行查询，效率比较高

导致索引失效的一些情况

1. 模糊匹配以%开头

   2. or , 必须两边的字段都有索引才能走索引
   3. 使用复合索引的时候，没有使用左侧的列查找，索引失效 .最左优先,在检索数据时从联合索引的最左边开始匹配
   4. 在where中索引字段参加了运算
   5. 在where中索引字段使用了函数

​

索引是各种数据库进行优化的重要手段，优化的时候优先考虑的因素就是索引

分类：

​ 单一索引：一个字段上添加索引

​ 复合索引：两个字段或者更多的字段上添加索引

​ 主键索引：主键上添加索引

​ 唯一性索引：具有unique约束的字段上添加索引
​ …..

​ 注意：唯一性比较弱的字段上添加索引用处不大

视图

view：站在不同的角度看待同一份数据

创建视图对象：
create view dept2_view as select * from dept2;

删除视图对象：
drop view dept2_view;

注意：只有DQL语句才能以view的形式创建。
create view view_name as 这里的语句必须是DQL语句;

用视图做什么？

​ 我们可以面向视图对象进行增删改查

​ 对视图对象进行增删改查(CRUD)，会导致原表被操作

视图对象在实际开发中到底有什么用？

​ 方便，简化开发，利于维护

==设计三范式==

​ 数据库表的设计依据，可以避免表中数据的冗余，空间的浪费

第一范式：要求任何一张表必须有主键，每一个字段原子性不可再分

第二范式：建立在第一范式的基础之上，要求所有非主键字段完全依赖主键，

​ 不要产生部分依赖

第三范式：建立在第二范式的基础之上，要求所有非主键字段直接依赖主键，

​ 不要产生传递依赖

多对多怎么设计？

​ 多对多，三张表，关系表两个外键

一对多怎么设计？

​ 一对多，两张表，多的表加外键

一对一怎么设计？

​ 一对一，多张表，外键唯一 （单表字段庞大的情况下）

数据库设计三范式是理论上的。

实践和理论有的时候有偏差。

最终的目的都是为了满足客户的需求，有的时候会拿冗余换执行速度。

因为在sql当中，表和表之间连接次数越多，效率越低。（笛卡尔积）

有的时候可能会存在冗余，但是为了减少表的连接次数，这样做也是合理的，
并且对于开发人员来说，sql语句的编写难度也会降低。

面试的时候把这句话说上：他就不会认为你是初级程序员了！

授权

grant insert,update(属性) on student to '赵华'
revoke insert,update(属性) on student from '赵华'