MySQL知识总结

最新推荐文章于 2024-09-10 16:09:30 发布

Lygjhr

最新推荐文章于 2024-09-10 16:09:30 发布

阅读量230

点赞数

本文链接：https://blog.csdn.net/Lygjhr/article/details/89135030

版权

MySQL 安装

安装包在D盘，自动安装在C盘中，具体过程见word
登录密码：123456

SQLYog可视化界面安装

具体步骤见：
https://blog.csdn.net/lihua5419/article/details/73881837/
登录时 localhost填写的是127.0.0.1

SQLYog具体操作

https://www.cnblogs.com/biehongli/p/6032905.html?utm_source=itdadao&utm_medium=referral
创建数据库-创建表-表的增删改查
SQLYog和Mysql命令行可以配合使用（基本要什么命令就查什么命令好了）

MySQL基本操作

查看已创建的所有数据库

SHOW DATABASES;

使用数据库

USE mytest;

约束

1.主键约束：（加自增约束）

create table user(
id int primary key auto_increment,  
name varchar(20),
);

联合主键：只要主键值加起来不重复就可以。联合主键里任何一个都不可以为空。

create table user(
id int,
name varchar(20),
password varchar(20),
primary key(id,name)
);

添加 /删除主键约束

alter table user4 add primary key(id);
alter table user4 drop primary key(id);

2.唯一约束：约束修饰的字段的值不可以重复。

alter table user3 add unique(name);

3.非空约束：修饰的字段不能为空

create table user(
id int not null,  
name varchar(20),
);

4.默认约束：修饰的字段使用默认值

create table user(
id int,  
name varchar(20),
age varchar(20) default 10
);

5.外键约束：主表中没有的数据值，在副表中是不可以使用的。主表中的记录已经被副表引用了，那么主表中是不可以被删除的。

范式

第一范式：强调的是列的原子性，即列不能够再分成其他几列。
第二范式：在满足第一范式的情况下，第二范式除主键外的每一列都必须完全依赖于主键。如果部分依赖，就需要拆表。
第二范式就是在第一范式的基础上属性完全依赖于主键。
第三范式：在满足第二范式的情况下，除开主键列的其他列之间不能有传递依赖关系。
非主键列是直接依赖于主键

连接查询

内连接（inner join/join）

select * from person inner join card on person.cardid = card.id;

就是两张表中的数据通过某个字段相等查询出相关记录。
外连接（分为左连接 left join / 右连接 right join / 完全外连接 full join）

select * from person left join card on person.cardid = card.id;

左外连接，会把左边表里所有的数据取出来，而右边表中数据有相等的，就显示出来，没有就会补上NULL。
右外连接，会把右边表里所有的数据取出来，而左边表中数据有相等的，就显示出来，没有就会补上NULL。
完全外连接(相当于结合左连接和右连接)

select * from person left join card on person.cardid = card.id
union
select * from person right join card on person.cardid = card.id

事务

mysql 中，事务其实是一个最小的不可分割的工作单元，事务能够保证一个业务的完整性。
多条sql语句，可能会有同时完成的要求。要么同时完成，要么同时失败。
设置mysql自动提交为 false

set @@autocommit=0;

上面的操作，关闭了mysql的自动提交。这样 rollback; 之后可以撤回前一句。

set @@autocommit = 1;

这样就是开启了自动提交
事务一旦提交，就会持久的产生效果，事务具有持久性。 事务给我们提供了一个反悔的机会。
ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）。

查看数据库的隔离级别

select @@global.tx_isolation;

修改隔离级别

set global transaction isolation level read committed;

事务的隔离性
read uncommitted; 读未提交的
read committed; 读已经提交的
repeatable read; 可以重复读
serializable; 串行化

read uncommitted
如果事务a对数据进行操作，在操作过程中，事务没有被提交，但是b可以看见a的操作结果。
“例子：小明买个东西，交了钱又rollback;”
脏读：一个事务读到了另外一个事务没有提交的数据。在uncommitted 的情况下会遇到脏读。
read committed
读取同一个表的数据，发现前后不一致。这个现象叫“不可重复读”。
repeatable read
幻读问题：事务a和事务b同时操作一张表，事务a提交的数据也不能被事务b读到，就会造成幻读。
serializable
当user表被另外一个事务操作的时候，其他事务里面的写操作是不可以进行的。

从1到4隔离级别越高，性能约差。mysql默认隔离级别是 repeatable read

索引

定义：索引是由数据库表中一列或者多列组合而成，其作用是提高对表中数据的查询速度。类似于图书的目录，方便快速定位，寻找指定的内容。
优点：提高数据查询速度
缺点：在创建索引和维护索引会耗费时间,随着数据量的增加而增加
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
具体索引知识！！！
B树的每个节点可以存储多个关键字，它将节点大小设置为磁盘页的大小，充分利用了磁盘预读的功能。每次读取磁盘页时就会读取一整个节点。 也正因每个节点存储着非常多个关键字，树的深度就会非常的小。进而要执行的磁盘读取操作次数就会非常少，更多的是在内存中对读取进来的数据进行查找。

索引的类型

UNIQUE唯一索引：不可以出现相同的值,可以有NULL值
INDEX普通索引：允许出现相同的索引内容
PRIMARY KEY主键索引：不允许出现相同的值,且不能为NULL值,一个表只能有一个primary_key索引

索引的创建

适用于表创建完毕后再添加

ALTER TABLE 表名 ADD 索引类型 （unique,primary key,fulltext,index）[索引名]（字段名）
ALTER TABLE `table_name` ADD UNIQUE (`column_list`)
CREATE INDEX可对表增加普通索引或UNIQUE索引
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)

索引的删除

ALTER TABLE `table_name` DROP PRIMARY KEY

组合索引

ALTER TABLE `myIndex` ADD INDEX `name_city_age` (vc_Name(10),vc_City,i_Age);

建立这样的组合索引,其实是相当于分别建立了
vc_Name,vc_City,i_Age
vc_Name,vc_City
vc_Name
因为 mysql 组合索引"最左前缀"的结果。简单的理解就是只从最左面的开始组合。

前缀索引

如果索引列长度过长,这种列索引时将会产生很大的索引文件,不便于操作,可以使用前缀索引方式进行索引前缀索引应该控制在一个合适的点,控制在0.31黄金值即可(大于这个值就可以创建)。
在这里插入图片描述

索引的弊端

不要盲目的创建索引,只为查询操作频繁的列创建索引,创建索引会使查询操作变得更加快速,但是会降低增加、删除、更新操作的速度,因为执行这些操作的同时会对索引文件进行重新排序或更新;
引深问题：1.为什么索引用B+ 树（见美团）
2.mysql索引失效分析？
3.MySQL主要提供2种方式的索引：
B-Tree索引，Hash索引
　　B树索引具有范围查找和前缀查找的能力，对于有N节点的B树，检索一条记录的复杂度为O(LogN)。相当于二分查找。
　　哈希索引只能做等于查找，但是无论多大的Hash表，查找复杂度都是O(1)。
　　显然，如果值的差异性大，并且以等值查找（=、 <、>、in）为主，Hash索引是更高效的选择，它有O(1)的查找复杂度。
　　如果值的差异性相对较差，并且以范围查找为主，B树是更好的选择，它支持范围查找。

触发器

触发器（trigger）：监视某种情况，并触发某种操作，它是提供给程序员和数据分析员来保证数据完整性的一种方法，它是与表事件相关的特殊的存储过程，它的执行不是由程序调用，也不是手工启动，而是由事件来触发，例如当对一个表进行操作（ insert，delete， update）时就会激活它执行。
触发器经常用于加强数据的完整性约束和业务规则等。触发器创建语法四要素：
1.监视地点(table)
2.监视事件(insert/update/delete)
3.触发时间(after/before)
4.触发事件(insert/update/delete)
创建只有一个执行语句的触发器：

CREATE TRIGGER 触发器名称 BEFORE/AFTER 触发事件
          ON 表名 FOR EACH ROW 
              执行语句

创建多个执行语句的触发器：

CREATE TRIGGER 触发器名称 BEFORE/AFTER 触发事件
          ON 表名 FOR EACH ROW 
          BEGIN
              执行语句
          END

存储过程

存储过程和函数是在数据库中定义一些 SQL 语句的集合，然后直接调用这些存储过程和函数来执行已经定义好的 SQL 语句。存储过程和函数可以避免开发人员重复的编写相同的 SQL 语句。而且，存储过程和函数是在 MySQL服务器中存储和执行的，可以减少客户端和服务器端的数据传输；存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

create database db1;
use db1; 
create table PLAYERS as select * from TENNIS.PLAYERS;
create table MATCHES  as select * from TENNIS.MATCHES;
delimiter $$　　#将语句的结束符号从分号;临时改为两个$$(可以是自定义)
CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
BEGIN
     DELETE FROM MATCHES
     WHERE playerno = p_playerno;
END$$
mysql> delimiter; #将语句的结束符号恢复为分号

执行上面的命令，新建了两个表，PLAYERS,MATCHES，这时查看表里的内容入如下：

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       7 |      1 |       57 |   3 |    0 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+

调用存储过程

mysql> call delete_matches(57);

之后表中数据变为下：说明在存储过程中设置了需要传参的变量p_playerno，调用存储过程的时候，通过传参将57赋值给p_playerno，然后进行存储过程里的SQL操作。

mysql> select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
+---------+--------+----------+-----+------+

存储引擎及分类

查找自己的数据库引擎

show engines\G;

InnoDB

1.InnoDB是事务型数据库的首选引擎，支持事务安全表（ACID），支持行锁定和外键，上图也看到了，InnoDB是默认的MySQL引擎。
（原子性：这组语句要不全部执行，要不全部不执行。如果执行一半出错，要回滚到事务开始执行的地方。
一致性：事务开始前和结束后，数据库的完整性没有被破坏，比如A向B转账，A扣钱，B一定收到
隔离性：同一时间，只允许一个事务请求同一个数据。
持久性：事务完成后，事务对数据库所有的更新都会保存，不能回滚）
2. InnoDB支持外键
3. 如果数据大量执行Insert 或者 Update ，应该使用InnoDB表
（如果大量执行select,用MyISAM）
4. 支持行级锁（开销大，并发性高），通常在存储引擎层面实现。
（MyISAM表级锁（开销小、并发性低），通常在服务器层实现）

InnoDB：支持事务处理，支持外键，支持崩溃修复能力和并发控制。如果需要对事务的完整性要求比较高（比如银行），要求实现并发控制（比如售票），那选择InnoDB有很大的优势。 如果需要频繁的更新、删除操作的数据库，也可以选择InnoDB，因为支持事务的提交（commit）和回滚（rollback）。

MyISAM：插入数据快，空间和内存使用比较低。如果表主要是用于插入新记录和读出记录，那么选择MyISAM能实现处理高效率。如果应用的完整性、并发性要求比较低，也可以使用。

总结：InnoDB: 支持事务安全表（ACDI）, 支持外键，支持行级锁，并发性高
MyISAM ：不支持ACDI，不支持外键，支持表级锁，并发性低。

MEMORY：所有的数据都在内存中，数据的处理速度快，但是安全性不高。如果需要很快的读写速度，对数据的安全性要求较低，可以选择MEMOEY。它对表的大小有要求，不能建立太大的表。所以，这类数据库只使用在相对较小的数据库表。

几种开发方法

瀑布式开发

迭代式开发

在迭代式开发方法中，整个开发工作**被组织为一系列的短小的、固定长度（如3周）的小项目，被称为一系列的迭代。**每一次迭代都包括了需求分析、设计、实现与测试。采用这种方法，在需求被完整地确定之前就能启动开发工作，并在一次迭代中完成系统的一部分功能或业务逻辑的开发工作。再通过客户的反馈来细化需求，并开始新一轮的迭代。

螺旋开发

敏捷开发

敏捷开发以用户的需求进化为核心，采用迭代、循序渐进的方法进行软件开发。在敏捷开发中，软件项目在构建初期被切分成多个子项目，各个子项目的成果都经过测试，具备可视、可集成和可运行使用的特征。换言之，就是把一个大项目分为多个相互联系，但也可独立运行的小项目，并分别完成，在此过程中软件一直处于可使用状态。