MySQL简单的基础知识（笔记）

偏执的太偏执、 2022-11-27 00:46 586阅读 0赞

## MySQL基础知识 ##

这几天一直在学数据库(只是初步了解一下)，同时也整理了一下笔记。如下：

*  MySQL简单介绍
 *  SQL基础语法（增删改查）
 *  MySQL的数据类型
 *  约束
 *  三大范式（略）
 *  表与表的关系
 *  单表查询
 *  多表查询
 *  索引
 *  执行计划（略）
 *  MySQL事务（略）

### MySQL简单介绍 ###

MySQL是一种关系型数据库。关系型数据库的特点：

*  数据以表格的形式出现
 *  每行为各种记录名称
 *  每列为记录名称所对应的数据域
 *  许多的行和列组成一张表单
 *  若干的表单组成database

### SQL基础语法（增删改查） ###

关于数据库(database)的基本操作：

<table> 
 <tbody> 
  <tr> 
   <th>有关数据库的基础语法</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>use 数据库名;</td> 
   <td>选择数据库</td> 
  </tr> 
  <tr> 
   <td>create database 数据库名 (charset 编码格式);</td> 
   <td>创建数据库(增)</td> 
  </tr> 
  <tr> 
   <td>drop database 数据库名;</td> 
   <td>删除数据库(删)</td> 
  </tr> 
  <tr> 
   <td>alter database 数据库名 (charset 修改后的编码格式);</td> 
   <td>修改数据库(改)</td> 
  </tr> 
  <tr> 
   <td>show create database 数据库名;</td> 
   <td>查看当前新创建的数据库(查)</td> 
  </tr> 
  <tr> 
   <td>show databases;</td> 
   <td>查看所有数据库(查)</td> 
  </tr> 
 </tbody> 
</table>

#db1为数据库名（举例声明）
    use db1;                             #选择db1的数据库进行操作
    create database db1 charset utf8;    #创建一个名为db1的数据库（编码格式为utf8，不写则为默认值）
    drop db1;                            #删除名为db1的数据库
    alter database db1 charset gbk;      #将db1数据库的编码格式改成gbk
    show create database db1;            #查看当前新创建的db1数据库
    show databases;                      #查看所有数据库

关于数据表(table)的基本操作：

<table> 
 <tbody> 
  <tr> 
   <th>有关数据表的基础语法</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>create table 数据表名(数据名,数据类型);</td> 
   <td>创建数据表(增)</td> 
  </tr> 
  <tr> 
   <td>drop table 数据表名;</td> 
   <td>删除数据表(删)</td> 
  </tr> 
  <tr> 
   <td>alter table 数据表名 modify 数据名 修改后的数据类型;</td> 
   <td>修改数据表(改)</td> 
  </tr> 
  <tr> 
   <td>show create table 数据表名;</td> 
   <td>查看当前新创建的数据表(查)</td> 
  </tr> 
  <tr> 
   <td>show tables;</td> 
   <td>查看所有数据表(查)</td> 
  </tr> 
  <tr> 
   <td>describe(desc) 数据表名;</td> 
   <td>查看数据表的结构(查)</td> 
  </tr> 
 </tbody> 
</table>

#t1为数据表名 id,name为变量名 int,char(20)为数据类型（举例声明）
    create table t1(id int,name char(20));    #创建一个名为t1的数据表
    drop table t1;                            #删除名为t1的数据表
    alter table t1 modify id tinyint;         #将t1数据表中的id数据的数据类型改成tinyint
    show create table t1;                     #查看当前新创建的数据表t1
    show tables;                              #查看当前数据库中的所有数据表
    describe t1;                              #查看数据表的结构

关于数据记录的基本操作：

<table> 
 <tbody> 
  <tr> 
   <th>有关数据记录的基础语法</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td>insert into 数据表名 (数据名) value (数据值) ;</td> 
   <td>创建数据记录(增)</td> 
  </tr> 
  <tr> 
   <td>delete from 数据表名 where 条件;</td> 
   <td>删除数据记录(删)</td> 
  </tr> 
  <tr> 
   <td>update 数据表名 set 数据名=新数据值 where 条件;</td> 
   <td>修改数据记录(改)</td> 
  </tr> 
  <tr> 
   <td>select 数据名 from 数据表名;</td> 
   <td>查看数据表中的数据记录(查)</td> 
  </tr> 
 </tbody> 
</table>

#t1为数据表名 id,name为变量名 int,char(20)为数据类型(举例声明)
    insert into t1 (id,name) values (1,'wula');     #往数据表t1中添加数据记录
    delete from t1 where id = 1;                    #删除数据表t1中id为1的数据记录
    update t1 set id = 2 where id = 1;              #将数据表t1中id为1的数据值改成2
    select id from t1;                              #查看数据表t1中id的数据记录
    select * from t1;                               #查看数据表t1中的所有数据记录

### MySQL的数据类型 ###

MySQL的数据类型大致可以分为三类：数值类型、日期时间类型和字符串类型。

<table> 
 <tbody> 
  <tr> 
   <th>类型名</th> 
   <th>大小</th> 
   <th>描述</th> 
  </tr> 
  <tr> 
   <td colspan="3">数值类型</td> 
  </tr> 
  <tr> 
   <td>TINYINT</td> 
   <td>1 byte</td> 
   <td>小整数值</td> 
  </tr> 
  <tr> 
   <td>SMALLINT</td> 
   <td>2 bytes</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>MEDIUMINT</td> 
   <td>3 bytes</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>INT</td> 
   <td>4 bytes</td> 
   <td>大整数值</td> 
  </tr> 
  <tr> 
   <td>BIGINT</td> 
   <td>8 bytes</td> 
   <td>极大整数值</td> 
  </tr> 
  <tr> 
   <td>FLOAT</td> 
   <td>4 bytes</td> 
   <td>单精度浮点值</td> 
  </tr> 
  <tr> 
   <td>DOUBLE</td> 
   <td>8 bytes</td> 
   <td>双精度浮点值</td> 
  </tr> 
  <tr> 
   <td>DECIMAL</td> 
   <td>*</td> 
   <td>小数值</td> 
  </tr> 
  <tr> 
   <td colspan="3">日期时间类型</td> 
  </tr> 
  <tr> 
   <td>DATE</td> 
   <td>3 bytes</td> 
   <td>日期</td> 
  </tr> 
  <tr> 
   <td>TIME</td> 
   <td>3 bytes</td> 
   <td>时间</td> 
  </tr> 
  <tr> 
   <td>YEAR</td> 
   <td>1 byte</td> 
   <td>年份</td> 
  </tr> 
  <tr> 
   <td>DATETIME</td> 
   <td>8 bytes</td> 
   <td>日期加时间</td> 
  </tr> 
  <tr> 
   <td>TIMESTAMP</td> 
   <td>4 bytes</td> 
   <td>时间戳</td> 
  </tr> 
  <tr> 
   <td colspan="3">字符串类型</td> 
  </tr> 
  <tr> 
   <td>CHAR</td> 
   <td>0~255 bytess</td> 
   <td>定长字符串</td> 
  </tr> 
  <tr> 
   <td>VARCHAR</td> 
   <td>0~65535 bytes</td> 
   <td>变长字符串</td> 
  </tr> 
  <tr> 
   <td>TINYBLOB</td> 
   <td>0~255 bytes</td> 
   <td>二进制字符</td> 
  </tr> 
  <tr> 
   <td>TINYTEXT</td> 
   <td>0~255 bytes</td> 
   <td>短文本字符串</td> 
  </tr> 
  <tr> 
   <td>BLOB</td> 
   <td>0~65535 bytes</td> 
   <td>二进制形式的长文本数据</td> 
  </tr> 
  <tr> 
   <td>TEXT</td> 
   <td>0~65535 bytes</td> 
   <td>长文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMBLOB</td> 
   <td>0~16777215 bytes</td> 
   <td>二进制形式的中长文本数据</td> 
  </tr> 
  <tr> 
   <td>MEDIUMTEXT</td> 
   <td>0~16777215 bytes</td> 
   <td>中长文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGBLOB</td> 
   <td>*</td> 
   <td>二进制形式的极大文本数据</td> 
  </tr> 
  <tr> 
   <td>LONGTEXT</td> 
   <td>*</td> 
   <td>极大文本数据</td> 
  </tr> 
  <tr> 
   <td colspan="3">其他类型</td> 
  </tr> 
  <tr> 
   <td>enum</td> 
   <td>*</td> 
   <td>枚举（单选）</td> 
  </tr> 
  <tr> 
   <td>set</td> 
   <td>*</td> 
   <td>集合（多选）</td> 
  </tr> 
 </tbody> 
</table>

简单使用一下枚举与集合：

#枚举的使用 
    create table t1(gender enum('female','male','no'));  #数据值只能在female，male，no三个中选一个，单选
    create table t1(hobby set('football','swim','run')); #数据值只能在footall，swim，run三个里面选，可多选

### 约束 ###

**非空约束(not null)：字段可以重复但不能为空  
默认值约束(default)：字段为空时会有默认值  
唯一约束(unique)：字段不能重复单但可以为空  
主键约束(primary key)：字段不能重复且不能为空  
外键约束(foreign key)：将两个表进行联系。其中被关联表叫做主表，关联表叫做从表**

#t1,t2为数据表名 id,name为变量名 int,char(20)为数据类型（举例声明）
    #以创建数据表为例子
    
    #非空约束(not null)
    create table t1(id int no null,name char(20));      #对id变量加上非空约束
    
    #默认值约束(default)
    create table t1(id int default 1,name char(20));    #对id变量加上默认值为1约束
    
    #唯一约束(unique)
    create table t1 (id int unique,name char(20));      #对id变量加上唯一性约束
    create table t1 (id int,name char(20),unique(id));  #对id变量加上唯一性约束
    
    #主键约束(primary key)  效果：primary key=not null + unique
    create table t1 (id int primary key,name char(20));       #建表前对id变量加上主键约束
    create table t1 (id int,name char(20),primary key(id));   #建表前对id变量加上主键约束
    alter table t1 add primary key (id);                      #建表后对id变量加上主键约束
    
    #外键约束(foreign key)
    #（方法1）将主表与从表的id2与id1之间建立联系。主表中的数据记录被从表引用时，主表中的该记录不可被删除
    create table t1 (id1 int,name1 char(20));           #主表
    create table t2 (id2 int,name2 char(20),constraint link foreign key(id2) references t1(id1));     #从表
    #（方法2）将主表与从表的id2与id1之间建立联系。主表中的数据记录被从表引用时，删除主表中的数据记录时，会相应删除从表中的数据记录
    create table t1 (id1 int,name1 char(20));           #主表
    create table t2 (id2 int,name2 char(20),            #从表
                     constraint link foreign key(id2) references t1(id1) 
                     on delete cascade on update cascade    #同步删除
                     );

tip：多字段添加约束与联合添加约束的不同

#以唯一约束(unique)为例子
    
    #多字段添加唯一约束:代表数据记录中不能存在id或name相同的数据。(id不同和name不同的数据才能添加)
    create table t1 (id int unique,
                     name char(20) unique
                     );
    #联合添加唯一约束：代表数据记录中不能存在id与name相同的数据。(id不同或name不同的数据也能添加)
    create table t1 (id int ,
                     name char(20) ,
                     unique(id,name)
                     );

### 三大范式（略） ###

**第一范式：** 所有字段值都是不可拆分的原子值

**第二范式：** 主键以外的其他列都必须依赖于主键的全部

**第三范式：** 出来主键以外的其他列之间不能有传递历来关系

### 表与表的关系 ###

表与表的关系可分成三种：一对多/多对一，多对多，一对一

先把两张表看做表1与表2，再进行比较判断

**一对多/多对一**：表1中的一条记录对应表2中的多条记录或表2中的多条记录对应表1中的多条记录  
例：游戏里的一个英雄对应多个皮肤

**多对多**：表1中的一条记录对应表2中的多条记录与表2中的多条记录对应表1中的多条记录  
例：一名学生能够选多个课程，一个课程也可以被多个学生选

**一对一**：表1中的一条记录唯一对应表2中的一条记录与表2中的一条记录唯一对应表1中的一条记录  
例：一个人对应一个身份证号，一个身份证号对应一个人

### 单表查询 ###

**关键字的优先级顺序：from→where→group by→having→distinct→order by→limit**

**1.from（寻表）**

select * from t1;

**2.where（约束）**  
–比较运算符：>,<,>=,<=,<>(不等于),!=  
–between a and b 值在a与b之间  
–in(a,b,c) 值是a或b或c  
–like’ ’ %代表任意多个字符，\_代表一个字符  
–逻辑运算符and or not

select * from t1 where age > 18;                      #查找t1表中年龄大于18的数据
    select * from t1 where age between 18 and 20;         #查找t1表中年龄在18至20之间的数据
    select * from t1 where age in(18,20);                 #查找t1表中年龄为18,20的数据
    select * from t1 where name like '黄%';               #查找t1表中姓黄的数据
    select * from t1 where age > 18 and name like '黄%';  #查找t1表中年龄大于18且姓黄的数据

**3.group by（分组）**  
聚合函数：

*  max() 最大值
 *  min() 最小值
 *  sum() 求和
 *  count() 求总个数
 *  avg() 求平均值

select gender avg(age) from t1 group by gender;   #按照性别分组求各分组的平均年龄

**4.having（过滤）**

select count(id) from t1 group by gender having count(id) > 10;  #按照性别分组求出总人数大于10的性别

**5.distinct（去重）**

select distinct age from t1;   #在t1表中求出不重复的年龄（去重）

**6.order by（排序）**  
–asc（升序）  
–desc（降序）

select * from t1 order by age asc;            #在t1表中按照年龄age升序排序
    select * from t1 order by age desc;           #在t1表中按照年龄age降序排序
    select * from t1 order by age asc,id desc;    #在t1表中先按照年龄age升序排序，再按照id降序排序

**7.limit（限制）**

select * from t1 limit 5;         #从头开始获取5个数据（id1~id5）
    select * from t1 limit 5,5;       #从第5个开始获取5个数据（id6~id10）

### 多表查询 ###

多表查询分成三种：多表连接查询，符合条件查询，子查询

**多表连接查询**  
多表连接查询分成内连接，左连接，右连接与外连接。

#数据库t1中有id1数据记录，数据库t2中有id2数据记录(举例声明)
    #内连接：（只获取匹配的记录）
    select * from t1 inner join t2 on t1.id1 = t2.id2;
    
    #左连接：（只获取左表的所有记录）
    select * from t1 left join t2 on t1.id1 = t2.id2;
    
    #右连接：（只获取右表的所有记录）
    select * from t1 right join t2 on t1.id1 = t2.id2;
    
    #外连接：（获取所有记录）
    select * from t1 left join t2 on t1.id1 = t2.id2 union select * from t1 right join t2 on t1.id1 = t2.id2;

**符合条件查询**  
就是在多表连接查询的条件下加上其他约束

select * from t1 inner join t2 on t1.id1 = t2.id2 where age > 18 ;

**子查询**  
–子查询是将一个查询语句嵌套在另一个查询语句中  
–内层查询语句的查询结果，可以为外层查询语句提供查询条件  
–子查询中可以包含：IN,NOT IN,ANY,ALL,EXISTS,NOTEXISTS等关键字  
–子查询中还可以包含比较运算符：>,<,!=等

select name from t1 where id1 in(select id2 from t2 where age > 18);  
    select * from t1 where age >(select avg(age) from t1);

### 索引 ###

在拥有庞大数据量时，很必要建立索引。有索引的查询速度比无索引快。  
常见的索引类型：普通索引，唯一索引，主键索引，联合索引

**普通索引**  
特点：快速查找

#普通索引举例
    #在建表创建时添加普通索引
    create table t1 (id int auto_increment ,
                     name char(20) not null,
                     index sy_name(name)    #给name添加普通索引
                     );
    #在建表后添加普通索引
    create index sy_name on t1(name);       #给name添加普通索引 
    alter table t1 add index sy_name(name)  #给name添加普通索引
    #删除普通索引(其他的索引也一样)
    drop index sy_name on t1;               #删除name的普通索引
    #查看索引（其他的索引也一样）
    show index from t1;                     #查看t1数据表所添加的索引

**唯一索引**  
特点：快速查找+唯一性

#唯一索引举例
    #在建表创建时添加唯一索引
    create table t1 (id int auto_increment ,
                     name char(20) not null,
                     unique index usy_name(name)     #给name添加唯一索引
                     );
    #在建表后添加唯一索引
    create unique index usy_name on t1(name);        #给name添加唯一索引 
    alter table t1 add unique index usy_name(name)   #给name添加唯一索引

**主键索引**  
特点：加快查找+唯一性+不为空

#主键索引举例（用法和添加主键一样）
    #在建表创建时添加主键索引
    create table t1 (id int auto_increment primary key,   #给id添加主键索引
                     name char(20) not null,
                     );
    #在建表后添加主键索引
    alter table t1 add primary key (id);                  #给id添加主键索引

**联合索引**  
特点：合并查找速度更快（联合索引的查询速度比索引合并的查询速度快）

#在建表创建时添加联合索引
    create table t2 (id int auto_increment ,
                     name char(20) not null,
                     index sy_name(id)                    #给id和name添加联合索引
                     );
    #在建表后添加联合索引
    create index sy_name on t1(id,name);                  #给id和name添加联合索引

对于联合索引（以上述为例），在进行查找时，查找id会使用索引，查找id+name会使用索引，查找name则不会使用索引。

### 执行计划（略） ###

通过explain来查看查询的速度。

explain select * from t1;            #得到的查询速度是ALL

查询速度比较：（慢）all<index<range<index\_range<ref\_or\_null<ref<eq\_ref<system<const（快）

### MySQL事务（略） ###

在默认情况下，MySQL会自动管理事务，一条SQL语句独占一个事务。

事务的特点：

*  原子性：是指事务是一个不可分割的整体，事务的操作要么成功，要么失败
 *  一致性：事务处理前后的数据的完整性必须保持一致
 *  隔离性：是指多个用户访问同一个数据库时，一个用户的事务不会受到干扰，多个并发的事务相互隔离
 *  持久性：是指一个事务一旦被提交，其对数据库中的数据是永久改变的

END!