MySQL基础总结，认真看完这篇就够了！！！

￡神魔★判官ぃ 2023-07-24 08:43 16阅读 0赞

### 文章目录 ###

*  MySQL基础
 *   *  服务的启动和停止
     *  数据库的相关操作
     *  数据库表的相关操作
     *  表中数据的相关操作
     *  排序和分页
     *  分组查询
     *  联表查询
 *  MySQL事务基础
 *   *  事务简介
     *  事务操作
     *  事务特征(ACID)
     *  事务安全隐患
 *  MySQL索引基础
 *   *  索引简介
     *  主键索引
     *  唯一索引
     *  普通索引
     *  组合索引
     *  全文索引

**这几天在学习关于MySQL的基础知识，今天把所学的总结一下，方便以后复习。**

# MySQL基础 #

## 服务的启动和停止 ##

**停止数据库服务，在DOS下以管理员身份运行**

net stop mysql

**开启数据库服务，在DOS下以管理员身份运行**

net start mysql

## 数据库的相关操作 ##

**连接数据库**

mysql -uroot -p #回车输入密码即可

**修改密码，把你自己的密码填到最后一个单引号内即可**

alter user 'root'@'localhost' identified by 'root';

**创建数据库**

create database 数据库名;

**创建数据库，判断不存在再创建**

create database if not exists 数据库名;

**显示所有数据库，其中四个是默认的**

show databases;

**使用数据库**

use 数据库名;

**删除数据库**

drop database 数据库名;

**删除数据库，判断存在再删除**

drop database if exists 数据库名;

## 数据库表的相关操作 ##

**创建数据库表**

create table 表名 (
    	列名1 数据类型1,
    	列名2 数据类型2,
    	...
    	列名n 数据类型n,
    	primary key 列名(主键)
     );

**查看数据库中所有表**

show tables;

**查看数据库表结构**

desc 表名;

**删除表**

drop table 表名;

**修改数据库表名**

alter table 表名 rename to 新表名;

**添加列**

alter table 表名 add 列名 数据类型;

**删除列**

alter table 表名 drop 列名;

## 表中数据的相关操作 ##

**添加数据**

insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n); #列名和值要相对应。

insert into 表名 values(值1,值2,...值n); #这里的值要包含表里的所有列。

**删除数据**

delete from 表名 where 条件; #删除满足条件的数据。

delete from 表名; #如果不写条件，则默认删除表里的所有数据。

**修改数据**

update 表名 set 列名1 = 值1, 列名2 = 值2,... where 条件; #这里是修改满足条件的数据。

update 表名 set 列名1 = 值1, 列名2 = 值2,...; #如果是不加条件，则修改所有的数据。

**查询数据**

select 列名 from 表名 #查询某列的数据。

select * from 表名 #查询所有列的数据。

select distinct 列名 from 表名 #查询去重后的数据。

**条件语句(where)**  
为了演示的方便，这里查询出来的结果都用 \* 来代替。  
![\-][-]

**查询同时满足条件1和条件2的数据。**

select * from 表名 where 条件1 and 条件2

**查询满足条件1或条件2的数据。**

select * from 表名 where 条件1 or 条件2

**查询不满足条件1的数据。**

select * from 表名 where not 条件1

![在这里插入图片描述][20200405115726923.png]  
**查询某列为空的数据。**

select * from 表名 where 列名 is null;

**查询某列非空的数据。**

select * from 表名 where 列名 is not null;

**查询某列在值1和值2之间的数据。**

select * from 表名 where 列名 between 值1 and 值2;

**查询所有以hello开头的数据，like结合%使用，其中%代表0到任意个字符。**

select * from 表名 where 列名 like 'hello%';

**查询所有以hello开头并且后面只跟一个字符的数据，like结合\_使用，\_代表1个字符。**

select * from 表名 where 列名 like 'hello_';

**查询某列在某区域内的数据。**

select * from 表名 where 列名in (值1,值2,...);

## 排序和分页 ##

> 排序关键词是order by，其默认是升序，如果想要降序，只需加上desc，其语法就是order by后面加上要排序的字段。

**通过该列进行升序排序。**

select * from 表名 order by 列名;

**通过该列进行降序排序。**

select * from 表名 order by 列名 desc;

**查询索引从offset(第一个数据索引是0)开始，每页显示pagesize个元素。**

select * from 表名 limit offset,pagesize;

**查询出的用户表数据，从第1个用户开始显示，每页显示10个。**

select* from user limit 0,10;

**查询出的用户表数据，从第10个用户开始显示，每页显示10个。**

select* from user limit 10,10;

*  第一页 0,10
 *  第二页 10,10
 *  第三页 20,10
 *  第n页 (n-1) \* 10,10

## 分组查询 ##

> 分组查询的关键字是group by，还可以与聚合函数(count()，sum()，max()等)搭配使用，下面通过一个user表进行演示。

select 字段名 from 表名 group by 字段名

select 字段名 count(字段名) from 表名 group by 字段名

![在这里插入图片描述][20200425102439981.png]

select user_type from user group by user_type;

![在这里插入图片描述][20200425102547672.png]

select user_type,count(user_type) from user group by user_type;

![在这里插入图片描述][20200425102644938.png]

## 联表查询 ##

> 联表查询的关键字是join，如果需要判断条件的话是join on（on后面加判断条件），这两个一般是成对出现的，这里以两个表的连接进行讲解，首先给出两个表，分别是student学生表和result成绩表。

![在这里插入图片描述][20200405122450247.png]  
![在这里插入图片描述][20200405122457593.png]

> 首先我们进行讲解**内连接**（inner join也叫join），也是最常用的一种联表查询，所谓inner join，也就是当我们查询学生的姓名和成绩时，我们需要用到student学生表和result成绩表，而inner join查出的结果就是，学生表中有该学生而且成绩表中对应的有该学生的成绩，满足这一条件的会被查出来。

![在这里插入图片描述][2020040512252069.png]

> 下面进行演示查询学生的姓名和成绩，我们会看到，姓名和成绩都是相对应的，没有出现学生没成绩，也没有出现成绩没学生。

![在这里插入图片描述][20200405122539665.png]

> 外连接包括左外连接和右外连接，也称为左连接和右连接。所谓的**左连接**（left join），也就是在内连接的基础上，又把左表中所有的信息打印出来了。

![在这里插入图片描述][20200405122553848.png]

> 还是以查询学生姓名和成绩为例，左连接会把没有成绩的姓名打印出来（前提是student表在前，也就是在左），下面进行演示。

![在这里插入图片描述][20200405122610828.png]

> **右连接**（right join）跟左连接差不多，就是在内连接的基础上把右边表的信息打印出来了。

![在这里插入图片描述][20200405122626368.png]

> 还是以查询学生姓名和成绩为例，右连接会把没有姓名的成绩打印出来（前提是result表在后，也就是在右），下面进行演示。

![在这里插入图片描述][20200405122643792.png]

# MySQL事务基础 #

## 事务简介 ##

**事务(Transaction)**

> 事务是一个最小的不可再分的工作单元。通常一个事务对应一个完整的业务(例如银行账户转账业务)。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

## 事务操作 ##

**开启事务**

start transaction;

**提交事务，即将数据写入到磁盘中**

commit

**回滚事务，回到最初的状态**

rollback

**查看事务是否开启自动提交**

show variables like 'autocommit';

![在这里插入图片描述][20200408074659360.png]

> 可以看出在MySQL中事务默认是自动提交的，为了演示事务，首先我们需要关闭自动提交。

**关闭事务自动提交**

set autocommit=off;

![在这里插入图片描述][20200408075247868.png]  
**下面通过一个银行转账的例子进行演示事务，a账户要给b账户转账100元**  
![在这里插入图片描述][20200408081103563.png]  
**下面这两条命令必须同时成功或者同时失败，这就是一个事务**

update bank set money=700 where id=1;
    update bank set money=600 where id=2;

**所以在执行这两句之前需要先开启事务**

start transaction;

**然后执行事务**

update bank set money=700 where id=1;
    update bank set money=600 where id=2;

**最后提交事务**

commit;

这样一波操作成功之后，数据才能被持久化到磁盘，如果仅仅第一条执行成功了，数据只是在内存中保存，而没有持久化到数据库，如果第二条也成功了，那么可以通过commit提交事务保存到磁盘中，如果第二条语句失败了，可以通过rollback回滚到最初的状态，要达到这种目的就需要事务。

## 事务特征(ACID) ##

*  **原子性(Atomicity)**：事务是最小的单位，不可再分
 *  **一致性(Consistency)**：事务要求所有的操作，必须保证同时成功或同时失败
 *  **隔离性(Isolation)**：事务与事务之间互相隔离
 *  **持久性(Durability)**：事务执行成功会，会持久到磁盘中

## 事务安全隐患 ##

**事务和事务之间具有隔离性，隔离性分为四个级别**

*  **读未提交(read uncommitted)**，事务B读取了事务A未提交的数据，也叫做：**读脏数据**
 *  **读已提交(read committed)**，事务B读取了事务A提交的数据(解决了脏读，这是Oracle的默认隔离级别)，也就是说，事务A提交了数据，然后事务B可以读取，提交一次，只能读取一次，这也叫做：**不可重复读**
 *  **可重复读(repeatable read)**，事务A提交的数据，事务B可以重复读取(解决了脏读和不可重复读，这是MySQL的默认隔离级别)，但是，可能出现两次读取的结果不同，这就引起了：**幻读**
 *  **串行化(serializable)**，事务A在处理数据的时候，事务B只能排队(解决了脏读、不可重复读和幻读)，即是串行化，不能并发，效率很低  
    ![在这里插入图片描述][20200408092246648.png]  
    **查看事务的隔离界别**

sqlselect @@transaction_isolation; #可以看出MySQL默认的是可重复读。

![在这里插入图片描述][202004080927057.png]  
**设置隔离级别为读未提交**

set session transaction isolation level read uncommitted;

**设置隔离界别为读已提交**

set session transaction isolation level read committed;

**设置隔离级别为可重复读**

set session transaction isolation level repeatable read;

**设置隔离界别为可串行化**

set session transaction isolation level serializable;

# MySQL索引基础 #

## 索引简介 ##

> 索引是为了提高数据库查询效率而生的。对于一些查询多，修改少的字段很适合用索引，以提高查询效率。如果是修改多的话，用索引会降低其效率，因为维护这个索引表很耗时。索引分为，**主键索引(primary key)**、**唯一索引(unique index)**、**普通索引(index)**、**组合索引、全文索引**。  
> 下面先演示一下用索引和不用索引的区别。

**查看表中数据数量**

select count(*) from 表名;

![在这里插入图片描述][20200409081904260.png]  
**查看表中索引**

show index from 表名;

![在这里插入图片描述][20200409082358301.png]  
可以看出，这里只有一个主键索引(userId)。而username字段不是索引字段。  
这里我们通过查询username为70000的数据，进行比较用索引和不用索引的区别。  
![在这里插入图片描述][20200409082925934.png]  
可以清楚的看到，用索引查询速度很快。

**删除索引**

drop index 索引名 on 表名;

**删除主键索引，也就是删除了该字段**

alter table 表名 drop 主键字段名;

## 主键索引 ##

> 主键索引，也就是我们常用的主键，主键只能有一个，但一个主键里可以有多个字段。

**创建表的时候创建主键索引**

create table test(
       id int(11),
       name varchar(25),
       primary key (id)
    );

**创建表之后给表添加主键索引**

alter table test add constraint id primary key(id);

## 唯一索引 ##

> 唯一索引，索引字段中的值必须是唯一的，但是允许为空值。

**创建表的时候创建唯一索引**

create table test(   
    	id int(11),   
    	name varchar(25),   
    	unique index_unique_test_name (name)
    );

**创建表之后创建唯一索引**

create unique index index_unique_test_name on test(name);

**修改表结构为唯一索引**

alter table test add unique index index_unique_test_name (name);

## 普通索引 ##

> 普通索引是最基本的索引，仅用来加速查询。

create table test(   
    	id int(11),   
    	name varchar(25),   
    	index index_test_name (name)
    );

**创建表之后创建普通索引**

create index index_test_name on test(name);

**修改表结构为普通索引**

alter table test add index index_test_name (name);

## 组合索引 ##

> 指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

create table test(   
    	id int(11),   
    	name varchar(25),   
    	index index_test_id_name (id,name)
    );

**创建表之后创建组合索引**

create index index_test_id_name on test(id,name);

**修改表结构为普通索引**

alter table test add index index_test_id_name (id,name);

## 全文索引 ##

> 全文索引，是在char，varchar，text类型字段上使用全文索引。就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"欢迎阅读此文章"通过此文章，可能就可以找到该条记录。

**创建表的时候创建全文索引**

create table test(   
    	id int(11),   
    	name varchar(25),   
    	content text,
    	fulltext (text)
    );

**创建表之后创建组合索引**

create fulltext index index_content on test(content);

**修改表结构为普通索引**

alter table test add fulltext index index_content (content);

[-]: /images/20230528/1bf54df67bbe460984a7d38e00714c02.png
[20200405115726923.png]: /images/20230528/87fd2a3910ef4c868a537a3357680d1e.png
[20200425102439981.png]: /images/20230528/ff3b514924ca4298820ba19f1ed72872.png
[20200425102547672.png]: /images/20230528/925f110b47654da6b794e1aff99a9ac2.png
[20200425102644938.png]: /images/20230528/38037a77d02a48c389f776962031b01b.png
[20200405122450247.png]: /images/20230528/3925446f99e543a795857d560b7f6a69.png
[20200405122457593.png]: /images/20230528/563ea5c4608443ffb2013f1aa0198787.png
[2020040512252069.png]: /images/20230528/18d1edb6a19440a18e2fb46d544a5376.png
[20200405122539665.png]: /images/20230528/1129c7f3122340e990ea8739dd1e8119.png
[20200405122553848.png]: /images/20230528/7fade3f6ddcd4dfd929f761517576b3f.png
[20200405122610828.png]: /images/20230528/62c3b8e88a594958b690a559aecc0b95.png
[20200405122626368.png]: /images/20230528/a56d7559bfba4947a81c363e3bb8a606.png
[20200405122643792.png]: /images/20230528/c3ffde5098ca414192e1c0a690e1c5b4.png
[20200408074659360.png]: /images/20230528/ee337bf704d847ca9e2133a8e76daa54.png
[20200408075247868.png]: /images/20230528/88609cc9ea1c4d6ba91443dfa71d5c62.png
[20200408081103563.png]: /images/20230528/e5d9be7057b943098d281e6ee3a92fa0.png
[20200408092246648.png]: /images/20230528/1436c044f4064834974ecc6485b22964.png
[202004080927057.png]: /images/20230528/c3c7f0a456024ac3a874616c03853b2c.png
[20200409081904260.png]: /images/20230528/709be4407e6d408cb658ced2758ee531.png
[20200409082358301.png]: /images/20230528/db51cd3e04a947a280e77d5957830b04.png
[20200409082925934.png]: /images/20230528/f83d6b3552c849adb8d05fd165bd6f77.png