MySQL相关学习

于 2020-11-23 00:05:48 发布
阅读量66 收藏
点赞数
文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_50873002/article/details/109965155
版权

数据库:全称DataBase，简称DB

用于存储和管理数据的仓库

  • 可以持久化存储数据
  • 方便存储和管理数据
  • 使用了统一的方式操作数据库 – SQL语句

目前市面上常见的数据库在这里插入图片描述
MySQL数据库介绍

  • 小型的数据库
  • 开源免费(6版本之前免费)
  • 所属于Oracle公司

数据库、数据表、数据的关系介绍

  • 数据库

    • 用于存储和管理数据的仓库
    • 一个库中可以包含多个数据表

  • 数据表

    • 数据库最重要的组成部分之一
    • 它由纵向的列和横向的行组成(类似excel表格)
    • 可以指定列名、数据类型、约束等
    • 一个表中可以存储多条数据

  • 数据

    • 想要永久化存储的数据

    在这里插入图片描述

SQL介绍

Structured Query Language：结构化查询语言
其实就是定义了操作所有关系型数据库的规则。每一种数据库操作的方式可能会存在一些不一样的地方，我们称为“方言”。

  • SQL通用语法
    • SQL 语句可以单行或多行书写，以分号结尾。
    • 可使用空格和缩进来增强语句的可读性。
    • MySQL 数据库的 SQL 语句不区分大小写，关键字建议使用大写。
    • 数据库的注释：
      • 单行注释：-- 注释内容 #注释内容(mysql特有)
      • 多行注释：/* 注释内容 */
  • SQL分类
    • DDL(Data Definition Language)数据定义语言
      • 用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等
    • DML(Data Manipulation Language)数据操作语言
      • 用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
    • DQL(Data Query Language)数据查询语言
      • 用来查询数据库中表的记录(数据)。关键字：select, where 等
    • DCL(Data Control Language)数据控制语言(了解)
      • 用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等

在这里插入图片描述
DDL:数据库定义语言, 专门"库"和"表"进行增删改查的操作
库:

create database 数据库名--->创建数据库
drop database 数据库名----> 删除数据库
alter database 数据库名 character set 字符集名称 -->改
show databases;查询所有数据库 --->查所有
show create database 数据库名字 ---->查指定数据库
use 数据库名; ---->切换数据库
select database(); ---->查询正在使用的数据库

表:

create table 表名(
	列名 数据类型 [约束], 
	列名 数据类型 [约束], 
	.....
	列名 数据类型 [约束]

)------->增
drop table 表名 ------>删
alter table 表名 add 新列名 数据类型 [约束];-->动态添加一列
alter table 表名 drop 列名; ---->删除一列
alter table 表名 modify 列名 新的数据类型 约束;--->修改某一列的数据类型
alter table 表名 change 旧列名 新列名  新的数据类型 约束;
---->修改表的某一列的列名以及数据类型
show tables ---->查询一个库中所有的表
desc 表名 ---->查看表的结构信息,主要查表头
show create table 表名 ----->查看表的创建语句, 可以看到编码, 引擎, 外键名等等重要的信息
show table status from 数据库 like 表名 ---->查询某个库中的某张表的详细信息

DML:对数据的 增 删 改 操作

增
insert into 表名 (列名列表) values (值的列表) --->列名和值要一一对应, 个数一样,类型也要一样
insert into 表名 (一部分的列名) values (值的列表)--->列名和值要一一对应, 个数一样,类型也要一样
insert into 表名 values (值的列表) ---->必须给所有的列赋值!
批量插入
insert into 表名 (列名列表) values (值的列表) , (值的列表), (值的列表), (值的列表) --->指定列批量添加
insert into 表名 values (值的列表),(值的列表),(值的列表),(值的列表);---->所有列的批量添加
删
delete from 表名 where 条件 ----->一定要有条件,否则全删
改
update 表名 set 列名1 = 列值1,  列名2 = 列值2 where 条件
注意一定要有条件,否则全改

DQL 对数据的查询语句
关键字:

select   ----> 必须  
	列名列表 ...
from    -----> 必须
	表名列表 ..
where    ----->可选
	分组前条件 ...
group by   ----->可选
	分组字段
having     ----->可选
	分组后的条件 ...
order by     ----->可选
	排序字段 排序方式, 
limit      ----->可选
	从哪开始查, 查多少条数据

基础查询

select * from 表名 ----> 查询表中所有列的所有数据
select 列名1,列名2...  from 表名 ---> 查询指定列
select distinct 列名列表 from 表名 --->查询指定列, 要去重, 除非每个数据都重复才算重复
select 数字列 运算(+ - * / %)  from 表名 --->列的计算,如果列的值为null的话, 想要设置默认值, ifnull(列, 默认值)
select 列名1 [AS] 别名1, 列名2 [AS] 别名2,... from 表名 -->起别名 

where条件
 大于 > , 小于 < , 大于等于 >= , 小于等于 <= , 等于= , 
不等于!=(<>) ,与 &&(and) , 或 ||(or) , 非 !(not)
简化 >= && <=  ---> between  小值  and 大值
简化多个 || 的书写  ---->   in( 值1, 值2 ,值3)
----->所有的条件对时间是有效

迷糊查询关键字 like
" % " 匹配任意个字符   " _ " 匹配单个字符
like如果没有和"%,_"结合的化和等号一样的效果
特殊查询 ----> null只能用is null或者 is not null

聚合函数:对列的纵向计算, 聚合排除null进行计算的
COUNT(列)计算个数 一般写*偷懒的写法, 也可以指定没有null值的列也行
max(列) 计算最大值-----> 数字列时间列
min(列) 计算最小值-----> 数字列时间列
sum(列) 计算最和值-----> 数字列
avg(列) 计算最平均值-----> 数字列

分组group by
对查询的列有要求, 必须是分组列(如性别,班级,工资等级)或者聚合函数, 其他'私人'不能查询,如姓名等
where 分组前过滤，后面不能跟聚合函数,因为聚合函数的值还没算出来
having 分组后过滤，可以跟聚合函数,因为此时已经分完组了, 聚合函数的值也算出来的

排序(order by)
 order by 列名 排序方式, 列名 排序方式...可以跟多个列名
 排序方式:升序ASC(默认值),降序DESC
 
 分页(limit) 只有mysql才能使用这个关键字
 limit 开始的索引,要的数据条数
 开始的索引 = ( 当前页 - 1) * 每页的个数;

多表查询

一对多：在多的一方建立外键, 关联一的一方的主键
多对多:需要一张中间表, 这个中间表, 至少有两个字段分别作为外键, 关联两张主表的主键

内连接作用: 查多张表中的交集部分
显式内连接
select
	列名列表,列名列表,列名列表...
from
	表名1
[inner] join
	表名2
on
	条件
[inner] join
	表名3
on
	条件
....            条件用on而不是where!!!


隐式内连接
select
	列名,列名,列名...
from
	表名 as 别名,
	表名,
	表名,
	...
where
	条件            条件用where而不是on!!!  


外连接:查某张表的全部, 以及两张表的交集
左外连接
select
	列名列表
from
	表1   ---- > 主表 显示此表的全部内容
left [outer] join
	表2   ---> 显示此表与主表的交集部分
on
	条件
left [outer] join
	表3   ---> 显示此表与主表的交集部分
on
	条件
....   

右外连接 效果与左外相同
  select
	列名列表
from
	表1
right [outer] join
	表2
on
	条件
right [outer] join
	表3
on
	条件
....

子查询:select 条件语句中包含另一个select的结果---> 比较抽象也可以用内连接或外连接实现
查询的结果是单行单列：经常作为条件, 使用 >,<,>=,<=, ,= ,!= 
查询的结果单列多行：经常用于条件, 使用in 或 not in
查询结果是多行多列: 作为一张虚拟表在和其他表进行联查

约束:

  • 主键约束:特点是 一张表只有一个主键, 这个主键可以有一个列组成, 也可以由多个列组成, 非空且唯一, 一般情况主键由一列组成, 作为这条数据的唯一标识

  • 在建表添加约束：
    create table 表名 (
    列名 数据类型 primary key,
    …
    列名 数据类型 [约束]
    );
    注意最后一行列名定义结束不能有逗号
    auto_increment主键自增不会拐弯, 终于一天会超出int的最大值, 导致插入不进去所以将主键定义成varchar类型, 利用java的UUID的randomUUID()生成一个随机不重复的字符串作为主键。

  • 外键约束:作用是约束表和表之前的数据, 保证数据安全完整，只有关系型数据库支持外键约束

  • 在创建表时添加
    create table 表名(
    列名 数据类型 约束，
    列名 数据类型 约束，
    …
    列名 数据类型 约束
    [CONSTRAINT] [外键名] foreign key (外键列) references 主表(主键)
    );
    外键级联更新与外键级联删除慎用:

alter table 表名 add [CONSTRAINT] [外键名] foreign key (外键列) references 主表(主键) ON DELETE CASCADE ON UPDATE CASCADE
  • 非空约束:not null
  • 在建表添加约束
    create table 表名 (
    列名 数据类型 not null,
    …
    )
  • 唯一约束：unique 注意: 唯一约束可以有 null 值
  • 在建表添加约束
    create table 表名 (
    列名 数据类型 unique
    …
    )

视图：作用是将复杂的sql语句封装起来, 简化sql使用, 用在银行或者信息级别比较高的公司,但是看不到语句细节导致不清楚查询细节所以用起来比较麻烦

create view 视图名称[( 给列名起名字 )] AS 查询语句 ---> 创建视图
select * from 视图名 ---> 使用视图
drop view 视图名称 ----> 删除视图
alter view 视图名称[( 给列名起新名字 )] AS 查询语句 --->修改视图的列
更新视图中的数据(增删改)源表中的数据也会变!

图形化界面SQLyog备份数据库
在这里插入图片描述
在这里插入图片描述

存储过程和函数：
存储过程：作用是可以封装多条sql语句, 还可以业务逻辑, 来减少java代码层面的逻辑,但把逻辑写到数据库层面, 造成数据库服务器压力比较大!

定义变量:DECLARE 变量名 数据类型 default 默认值
为变量赋值:set 变量名 = 值 或 select xxx列 into 变量名 from xxx表
查询变量的值: select 变量名

if语句:
if 条件 then 操作
elseif 条件 then 操作
....
else 操作
end if

while循环:
WHILE 条件 DO 
 语句体    ---->如果条件满足则执行"语句体",否则结束循环
END WHILE

没有返回值用过程，有返回值用函数

储存过程: 
DELIMITER $
create procedure 存储过程的名称(参数)
begin
	sql语句, 甚至可以写逻辑
end$
DELIMITER ;
参数说明: IN 输入参数,希望调用者赋值
		 OUT 输出参数,方法的返回值
		 INOUT 即是输入, 也是输出
对out类型的要使用 @参数名 ---> 传递参数
select @变量名  ---> 查询结果

调用存储过程:CALL 存储过程名称(实际参数)
查询存储过程: select * from mysql.proc where db = '数据库名称'
删除存储过程: drop procedure 存储过程的名字


存储函数:必须有返回值
DELIMITER $
create function 函数的名称(参数)
returns 返回值类型
begin
	sql语句, 甚至可以写逻辑
	return 结果;
end$
DELIMITER ;

调用存储函数: select 函数名(参数)
删除存储函数: drop function 函数的名字

触发器:记录操作日志, 方便后期回滚, 保证数据安全

INSERT:只有NEW, 没有OLD
DELIMITER $
create trigger 触发器的名字
AFTER INSERT
ON 表名
FOR EACH ROW
BEGIN
	new获得插入后的数据
END$
DELIMITER ;


UPDATE:既有NEW又有OLD
DELIMITER $
create trigger 触发器的名字
AFTER UPDATE
ON 表名
FOR EACH ROW
BEGIN
	old获得修改前的数据
	new获得修改后的数据
END$
DELIMITER ;


DELETE: 只有OLD,没有NEW
DELIMITER $
create trigger 触发器的名字
AFTER DELETE
ON 表名
FOR EACH ROW
BEGIN
	old获得删除前的数据
END$
DELIMITER ;

事务作用: 保证一组操作要么一起成功, 要么一起失败

start transaction 开启事务
commit  提交事务
rollback  回滚事务

mysql:自动提交 , @@autocommit = 1 ,在mysql一条增删改语句, 就直接持久化存储
oracle:手动提交 , @@autocommit = 0,在oracle一条增删改语句, 必须手动提交才有效

事务的四大特征:

  • 原子性:代表多条sql是一个整体,要么一起成功要么一起失败
  • 一致性:事务前后, 数据保证逻辑总量不变
  • 持久性:事务提交或者回滚后, 会持久化到硬盘上
  • 隔离性:期望事务和事务相互不影响, 但实际中相互影响的

隔离各种级别:

  • READ UNCOMMITED 读未提交 会造成:脏读, 虚读, 幻读
  • READ COMMITED (oracle默认的级别) 读已提交 会造成 虚读, 幻读
  • REPEATABLE READ(mysql默认的级别) 可重复读 会造成 幻读
  • SERIALIZABLE 串行化 没有问题出现但效率极慢

脏读:一个事务受到其他事务的影响, 读到另外一个事务未提交的数据
不可重复读(虚读):一个事务受到其他事务的影响update语句的影响, 造成当前事务多次查询的结果不一样
幻读:一个事务受到其他事务的影响insert,delete语句的影响, 造成当前事务多次查询的结果不一样,操作时候出现幻觉

mysql支持多种引擎:
MYISAM:不支持事务和外键
INNODB:占用相对比较大mysql默认
MEMORY:基于内存存储

索引作用: 提高"查询"效率

添加索引
create [unique| fulltext]  index 索引名 on 表名(列名.....)
alter table 表名 add [unique] index (列名...)  
查询索引
show index from 表名
删除索引
drop index 索引名 on 表名
注意: 主键, 外键, 唯一  本来就是一种索引!

查询很快是因为mysql帮我们维护一颗树,提高查询效率

BTree: 它的节点包括了 地址值, 键 , 键对应的在磁盘中的数据 ,在每次根据索引对子树进行查找时，它会把其涉及到
该磁盘片中的所有内容都读取到内存中，其中就包含了大量磁盘中的数据，虽然可以找到我们需要的数据，
但会做大量的磁盘读写操作，效率较低而且占内存。
每一个节点中包含了key值和数据，查询数据时会增加磁盘I/O的次数，效率低

B+Tree: 它组成就包含了 地址值 和 键 而数据都存储在了所有的叶子节点上，在查找时会根据索引找到
对应的叶子节点上的数据，这样减少了磁盘读写的次数而且叶子节点的数据都是连续的方便进行范围查找!
非叶子节点只存储key值，所有的数据存储在叶子节点中，所有叶子节点之间都有连接指针方便范围查找.
优点提高查询速度，减少磁盘的IO次数，树的结构相对于BTree来讲会比较小.

在查询数据时如果涉及到索引列时查询效率会很快，因为MySQL会自动帮我们定义的所有索引列以树结构的形式
在底层维护，查询时会根据存储规则和索引对对应的子树进行查找 所以会很快速的查找到对应的数据。
虽然查找效率很快，但是在添加索引的时候要慎重，因为每添加一个索引列MySQL都会维护一颗树形结构
这样如果一个表中的索引列过多时，在对数据进行增删改的操作时仅仅去维护这些树形结构
MySQL就会花费大量时间，效率反而会变慢！

所以添加索引要慎用, 因为成本比较高, 树比较多, 增删改会特别慢!以后在查询的时候, 条件最好是索引列(主键,外键, 唯一列);注意: 组合索引遵循了最左匹配原则

MySQL锁

在数据库中，数据是一种供许多用户共享访问的资源，如何保证数据并发访问的一致性、有效性，是所有数据库必须解决的一个问题，MySQL由于自身架构的特点，在不同的存储引擎中，都设计了面对特定场景的锁定机制，所以引擎的差别，导致锁机制也是有很大差别的。

锁的分类
  • 按操作分类：
    • 共享锁：也叫读锁。针对同一份数据，多个事务读取操作可以同时加锁而不互相影响 ，但是不能修改数据记录。
    • 排他锁：也叫写锁。当前的操作没有完成前,会阻断其他操作的读取和写入
  • 按粒度分类：
    • 表级锁：操作时，会锁定整个表。开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低。偏向于MyISAM存储引擎！
    • 行级锁：操作时，会锁定当前操作行。开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高。偏向于InnoDB存储引擎！
    • 页级锁：锁的粒度、发生冲突的概率和加锁的开销介于表锁和行锁之间，会出现死锁，并发性能一般。
  • 按使用方式分类：
    • 悲观锁：每次查询数据时都认为别人会修改，很悲观，所以查询时加锁。
    • 乐观锁：每次查询数据时都认为别人不会修改，很乐观，但是更新时会判断一下在此期间别人有没有去更新这个数据
  • 不同存储引擎支持的锁
存储引擎表级锁行级锁页级锁
MyISAM支持不支持不支持
InnoDB支持支持不支持
MEMORY支持不支持不支持
BDB支持不支持支持

共享锁(读锁)：SQL的查询语句where 条件 lock in share mode。
条件列如果是索引列,加的是行锁
条件列是不是索引列,加的表锁

myisam:lock table 表名 read(表锁) 加锁
unlock tables 释放锁
其他连接支持普通查询

排他锁(写锁)：SQL的查询语句where 条件 for update
条件列是索引列，加的是行锁
条件列是不是索引列，加的表锁

myisam：lock table 表名 write(表锁) 加锁
unlock tables 释放锁
其他链接不支持所有操作
注意：锁的兼容性

  • 共享锁和共享锁 兼容
  • 共享锁和排他锁 冲突
  • 排他锁和排他锁 冲突
  • 排他锁和共享锁 冲突

乐观锁和悲观锁

  • 悲观锁的概念
    • 就是很悲观，它对于数据被外界修改的操作持保守态度，认为数据随时会修改。
    • 整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系型数据库提供的锁机制。
    • 行锁，表锁不论是读写锁都是悲观锁。
  • 乐观锁的概念
    • 就是很乐观，每次自己操作数据的时候认为没有人会来修改它，所以不去加锁。
    • 但是在更新的时候会去判断在此期间数据有没有被修改。
    • 需要用户自己去实现，不会发生并发抢占资源，只有在提交操作的时候检查是否违反数据完整性。
  • 悲观锁和乐观锁使用前提
    • 对于读的操作远多于写的操作的时候，这时候一个更新操作加锁会阻塞所有的读取操作，降低了吞吐量。最后还要释放锁，锁是需要一些开销的，这时候可以选择乐观锁。
    • 如果是读写比例差距不是非常大或者系统没有响应不及时，吞吐量瓶颈的问题，那就不要去使用乐观锁，它增加了复杂度，也带来了业务额外的风险。这时候可以选择悲观锁。
  • 乐观锁的实现方式
    版本号
    • 给数据表中添加一个version列，每次更新后都将这个列的值加1。
    • 读取数据时，将版本号读取出来，在执行更新的时候，比较版本号。
    • 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
    • 用户自行根据这个通知来决定怎么处理，比如重新开始一遍，或者放弃本次更新。

时间戳

  • 和版本号方式基本一样，给数据表中添加一个列，名称无所谓，数据类型需要是timestamp
  • 每次更新后都将最新时间插入到此列。
  • 读取数据时，将时间读取出来，在执行更新的时候，比较时间。
  • 如果相同则执行更新，如果不相同，说明此条数据已经发生了变化。
锁的总结
  • 表锁和行锁
    • 行锁：锁的粒度更细，加行锁的性能损耗较大。并发处理能力较高。InnoDB引擎默认支持！
    • 表锁：锁的粒度较粗，加表锁的性能损耗较小。并发处理能力较低。InnoDB、MyISAM引擎支持！
  • InnoDB锁优化建议
    • 尽量通过带索引的列来完成数据查询，从而避免InnoDB无法加行锁而升级为表锁。
    • 合理设计索引，索引要尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定。
    • 尽可能减少基于范围的数据检索过滤条件。
    • 尽量控制事务的大小，减少锁定的资源量和锁定时间长度。
    • 在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
    • 对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁的产生。
Request不会写
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值