MySQL基础知识详解

数据库

1.创建数据库

create database 数据库名;

数据库创建的时候，要求不能重复。此时就可以在创建的时候，加上一个修饰，来应对上述问题：

create database if not exists 数据库名;

此时就不会因为数据库同名而报错了，发现数据库已经存在，是不会继续创建数据库的

创建数据库的时候，可以手动指定一下字符集的

character set 字符集名字 / charset 字符集名字

咱们需要在数据库中保存中文，mysql默认的字符集是拉丁文，不支持中文。必须要在创建数据库的时候，手动指定编码方式为支持中文的编码（GBK,UTF8）

//不同的字符集，不同的编码方式下，一个汉字占几个字节，是不同的

如果使用utf8编码，一个汉字通常是3个字节

2.查看数据库

列出当前的mysql服务器上一共都有哪些数据库

show databases;

修改一下配置啥的都可以通过mysql数据库来操作，这里要慎重操作

3.选中数据库

use 数据库名;

数据库中最关键的操作，就是针对表进行增删改查，表是从属于数据库的，要针对表操作，就需要先把哪个数据库的表这个事情指定清楚

4.删除数据库

drop database 数据库名;

删除操作，删掉的不仅仅是database，而且也删除了database中所有的表，和表里所有的数据

//sql中

有特定含义的单词：关键字（数据库名，表名，列名都不能和关键字重复）

sql的关键字是大小写不敏感的

数据表的操作

针对数据表的操作，前提是先选中数据库

1.创建表

create table 表名(列名 类型，列名 类型......);

//如果确实想让表名/列名和关键字一样，可以用反引号  `  来把表名/列名引起来

2.查看该数据库中的所有表

show tables;

3.查看指定表的结构

desc 表名;

4.删除表

drop table 表名;

数据类型

上述类型，只需要掌握几个重要的即可

1.int

2.long

3.double

4.decimal：精度更高的浮点数(使用其他方式来存储小数)，使用decimal表示小数，精度是更高了，但是运算速度会变慢，占用的空间也更多

5.varchar

6.datetime

//表示钱时用int类型表示而不是double和decimal，通过将元转化为分来达到目的

MySQL表的增删改查（CRUD）

新增

insert into 表名 values(值，值......);

//into可以省略

//使用  '  或者  "  来表示字符串，sql中没有字符这个类型

指定列插入：

insert into 表名(列名，列名......) values(值，值......);

一次插入多行：

insert into 表名 values(值，值......),(值，值......)......

//一次插入多行记录，相比于一次插入一行，分多次插入，要快不少

datetime类型，如何插入：可以是用一个固定形式的字符串，来表示时间日期(例如：'2000-01-01 12:00:00') ；如果想填写的时间日期，就是当前时刻(sql提供了一个现成的函数，now() )

删除

delete from 表名 where 条件 /order by /limit;

修改

update 表名 set 列名=值 where 条件;

//set这个词，在计算机里有两种典型的含义

1.设置：getter/setter

2.集合：TreeSet/HashSet

例：将总成绩最差的三个人数学加30分

update student set math=math+30 order by chinese+math+english limit 3;

查询

1.全列查询：

select * from 表名;

//把表中的所有行和所有列都查询出来（危险操作，容易造成数据库服务器挂掉）

2.指定列查询：

select 列名，列名...... from 表名;

3.查询字段为表达式：在查询的时候，写做由列名构成的表达式，把这一列中的所有行都带入到表达式中，参与运算（例：select name,chinses-10 from student;）//这里的操作不会修改数据库服务器上的原始数据，只是在最终响应里的“临时结果”中做了计算

4.指定别名：

select 表达式 as 别名 from 表名;

（as可以省略，但是不建议省略）

//查询的时候给列/表达式指定别名（给表也能指定别名）

5.去重：

select distinct 列名，列名...... from 表名

distinct修饰某个列/多个列 值相同的行，只会保留一个

当修饰的是多个列时，只有当这几个列同时相同的行才会被去除

6.查询的时候排序：

select 列名，列名...... from 表名 order by 列名 asc/desc;

//asc是升序，desc是降序，如果省略则默认为升序排序

order by指定的列，如果你select的时候没有把这一列查出来，也不影响排序

order by还可以针对表达式来排序

//指定多个列来排序，order by后面可以写多个列，先按照第一个列排序，再然后以第二个列为排序标准将第一个列中相同的行来进行排序，以此类推

7.条件查询(where)：

select 列名 from 表名 where 条件;

//条件查询时在条件中使用别名会报错，原因参考select条件查询执行的顺序：

1.遍历表中的每个记录

2.把当前记录的值，带入条件，根据条件进行筛选

3.如果这个记录条件成立，就要保留，如何再进行列上的表达式的计算

//到第三步时才会定义别名

4.如果有order by，会在所有的行都被获取到之后(表达式也算完了)，再针对所有的结果进行排序

8.分页查询（LIMIT）：

select * from 表名 limit a offset b;

//a是这次查询查出的记录个数；b是偏移量也就是一个下标，指的是从b开始

limit可以限制这次查询最多能查出来多少个结果     

查询操作的进阶

1.查询可以搭配插入使用

insert into student2 select * from student;

//将student表中的查询结果插入到student2表中

（此处要求查询出来的结果集合，列数/类型 要和插入的这个表匹配）

2.聚合查询

表达式查询针对列和列之间进行运算

聚合查询针对行和行之间进行运算

1）聚合函数

count：

select count(*) from student;

//先执行select *，再针对结果集合进行统计(看看具体有几行)

如果将 * 换成具体的列：

1.如果当前的列里面有null，两种方式计算的count就不同了

2.指定具体列，是可以进行去重的（例：count(distinct math)）

sum：

把这一列的若干行，给进行求和(算术运算)，只能针对数字类型使用

//在求和过程中会自动将null忽略

sum进行算术运算的原理：mysql会尝试把这一列给转成double，如果转成了就可以进行运算，如果没转成就会报警告。所以理论上字符串如果是数字类型的也会参与算术运算

sum(表达式)，sum不止可以针对某一列进行运算，也可以针对表达式进行运算

2）group by

使用group by进行分组，针对每个分组，再分别进行聚合查询

针对指定的列进行分组->把这一列中，值相同的行，分成到一组中->得到若干个组->针对这些组，分别使用聚合函数

例：

select role,avg(salary) from emp group by role;

//根据role进行分组之后再求平均salary

//如果针对分组之后，不使用聚合函数，此时的结果就是查询出每一组中的某个代表数据

使用group by的时候，还可以搭配条件（需要区分清楚，该条件是分组之前的条件，还是分组之后的条件）

分组前：直接使用where即可，where子句一般写在group by的前面

分组后：使用having描述条件，having子句一般写在group by的后面

//在group by，可以一个sql同时完成这两类条件的筛选

3）having

3.联合查询/多表查询

如果这两个表里面的记录都是存在对应关系的，那么内连接和外连接的结果是一致的。如果存在不对应的记录，内连接和外连接就会出现差别

（1）内连接：

联合查询的关键思路在于理解“笛卡尔积”的工作过程（排列组合），笛卡尔积是简单无脑的排列组合，把所有可能的情况都穷举了一遍，其中就包含一些合法的数据也包含非法的无意义的数据。在进行多表查询的时候，就需要把有意义的数据筛选出来，无意义的数据过滤掉

过程：

1）先把两个表进行笛卡尔积

select * from student,score;

2）加上连接条件，筛选出有效数据

学生表的id=分数表的student_id

select * from student,score where student.id=score.student_id;

3）结合需要进一步添加条件，针对结果进行筛选

例：查询张三的成绩

select * from student,score where student.id=score.student_id and student.name='张三';

4）针对查询到的列进行精简，只保留需求中关心的列

select student.name,score.score from student,score where student.id=score.student_id and student.name='张三';

查询所有同学的总成绩以及同学的个人信息（此处是多行数据进行加和，使用聚合函数sum，还需要按照同学进行分组）：

select student.name,sum(score.score) from student,score where student.id=score.student_id group by name;

查询所有同学的成绩及同学的个人信息（三张表进行联合查询）：

select student.name as student_name,course.name as course_name,score.score from student,course,score where student.id=score.student_id and course.id=score.course_id;

//为了区分两个name，所以两个都取别名

//可以用inner join代替,  （inner可以省略）

用on代替where

（2）外连接：

左外连接：left join（以左侧表为基准，保证左侧表的每个数据都会出现在最终结果里，如果在右侧表中不存在，对应的列就填成null）

右外连接：right join

（全外连接（mysql不支持，oracle支持））

（3）自连接：

一张表，自己和自己进行笛卡尔积

有的时候需要去进行 行和行 之间的比较，而sql只能进行 列和列 之间比较

把行的关系转换成列的关系再进行比较

//不能写成

select * from score,score;

应该写成

select * from score as s1,score as s2;

（4）子查询：

本质上就是套娃，把多个简单的sql拼成一个复杂的sql

//单行子查询：

select name from student where classes_id=(select classes_id from student where name='张三') and name!='张三';

//多行子查询：

select * from score where score.course_id in (select id from course where name='语文' or name='英文');

相当于

select * from course,score where course.id=score.course_id and (course.name='语文' or course.name='英文');

（5）合并查询

把多个sql查询的结果集合合并到一起

union关键字：

select * from course where id<3 union select * from course where name='英文';

相当于

select * from course where id<3 or name='英文';

//union允许把两个不同的表的查询结果合并在一起，而or只能针对一个表（合并的两个sql的结果集的列是需要匹配的，即列的个数和类型需要一致，但是列名不需要一致）

union在合并的时候是会进行去重的

如果不想去重应该怎么办？使用union all就可以了

数据库约束

not null：指示某列不能存储NULL值

unique：保证某列的每行的值不能重复（但是可以同时存放多个null）

default：规定没有给列赋值时的默认值

primary key：not null和unique的结合。确保某列（或两个列多个列的结合）有唯一标识   //一张表里只能有一个作为身份标识的数据   //虽然只能有一个主键，但是主键不一定只是一个列，也可以用多个列共同构成一个主键（联合主键）

foreign key：保证一个表中的数据匹配另一个表中的值的参照完整性（外键是子表和父表之间相互影响的）

check：保证列中的值符合指定的条件。对于MySQL数据库，对check子句进行分析，但是忽略check子句。

一个列可以有多个约束

mysql会把带有unique和primary key的列自动生成索引，从而加快查询速度

可以做到不删表重建而是直接设置约束（alter table可以修改表结构）//比较复杂而且用的少

如何保证主键唯一呢？

mysql提供了一种"自增主键"这样的机制（将auto_increment放在primary key后面）

此时在插入时将主键位置输入成null，表示交给服务器去自动分配（如果刚才进行了手动指定，那么从刚才最大的数值开始继续往后分配）   //也可以去进行手动指定

//此处这里的id的自动分配，也是有一定局限性的

如果是单个mysql服务器是没问题的

如果是一个分布式系统，由多个mysql服务器构成的集群，这个时候依靠自增主键就不行了

分布式系统中生成唯一id的算法：分布式唯一id=时间戳+机房编号/主机编号+随机因子（此处的+是指字符串拼接，不是算术相加）

//如果添加商品的速度比较慢，直接使用时间戳就够了

外键应该在创建表的最后面写，与前面的变量用逗号分隔开

//写成

foreign key(classId) references class(classId) 

意思是当前表中的classId应该出自class表中的classId

前一个classId表示的是当前表中被约束的列

后一个class(classId)表示的是数据是被class表的classId这一列约束的（此处的classId为唯一列，可以是unique或primary key）

针对父表进行修改/删除操作时，如果当前被修改/删除的值，已经被子表引用了，此时操作也会失败

//不管子表是否引用父表的值，都无法直接删除父表，因为如果删除了父表，子表在插入元素时没有参考的值（只能先删子表，再删父表）

指定外键约束的时候，要求父表中被关联的这一列，得是主键或者unique

应用：在商品下架时子表中已经引用过了父表中的代表商品的元素，此时如何做到下架：给商品表增加一个单独的列，表示商品是否在线（不在线，就相当于下架了），以此来做到在不删除父表元素的情况下实现下架商品

索引

索引属于是针对查询操作引入的优化手段，可以通过索引来加快查询的速度，避免针对表进行遍历

索引是能提高查询速度的，但是也有代价：

1.占用更多的空间，生成索引，是需要一系列的数据结构，以及一系列的额外的数据，来存储到硬盘空间中的

2.但是可能会降低修改删除的速度

索引的相关操作

（1）查看索引

show index from 表名;

（2）创建索引

create index 索引名字 on 表名(列名);

（3）删除索引

drop index 索引名 on 表名;

//手动创建的索引可以手动删除，自动创建的索引不能删除

//主键、unique、外键约束时，都会自动生成索引

索引也是通过一定的数据结构来实现的

数据库引入的索引的中最为典型的是一种改进的树形结构，B+树（N叉搜索树）：

1.一个节点上存在N个key，划分成N个区间（少一个大于最大值的区间）

2.每个节点上N个key中，最后一个就相当于当前子树的最大值

3.父节点上的每个key都会以最大值的身份在子节点的对应区间中存在（key可能会重复出现）//叶子节点这一层，包含了整个树的数据全集

4.B+树会使用链表这样的结构，把叶子节点串起来//此时就非常方便的完成数据集合的遍历，并且也很方便的从数据集合中按照范围取出一个“子集”

B树：每个节点的度都是不确定的，一个节点上保存N个key就划分出N+1个区间，每个区间都可以衍生出一系列的子树了

//一个节点中，虽然是可以保存N个key，也不是无限制的，达到一定的规模就会触发节点的分裂；当删除元素达到一定数目，也会触发节点的合并

B+树的优点（相比于B树以及哈希，红黑树）

1.N叉搜索树，树的高度是有限的，降低IO的次数

2.非常擅长范围查询

3.所有查询最终都是要落到叶子节点，查询和查询之间的时间开销是稳定的

事务

事务可以把多个sql打包成一个整体，可以保证这些sql要么全都执行正确，要么就“一个都不执行”（看起来没执行过）//关键操作就是回滚（rollback）

事务这样的特点，称为“原子性”

1.开启事务：start transaction;

2.执行各种sql

3.事务结束：commit;

可以通过rollback主动触发回滚

回滚是怎么做到的？

以日志的方式，记录事务中的关键操作，这样的记录就是回滚的依据

事务的特性

1.原子性 回滚的方式，保证这一系列操作都能执行正确或者恢复如初

2.一致性 事务执行前后数据都不能离谱，很多时候是要靠数据库的约束以及一系列检查机制来完成的

3.持久性 事务做出的修改都是在硬盘上持久保存的，重启服务器数据仍然存在，事务执行的修改仍然是有效的

4.隔离性 （数据库并发执行多个事务）

//隔离级别，就是在“数据正确性”和“效率”之间做权衡，二者只能选其中之一

在并发执行事务的时候，会出现啥问题呢？

1.脏读问题

一个事务A正在写数据的过程中，另一个事务B读取了同一个数据。接下来事务A又修改了数据，导致B之前读到的数据是一个无效的数据/过时的数据（也称为脏数据）

//针对脏读问题的核心思路是对写操作加锁

并发性降低了，隔离性提高了，效率降低了，数据准确性提高了

2.不可重复读

事务A在两次读的之间有一个事务B修改了数据并提交，造成事务A在内部多次读取同一个数据的时候出现不同情况

//对读操作加锁

并发性降低了，隔离性提高了，效率降低了，数据准确性提高了

3.幻读

一个事务A执行过程中两次的读取操作，数据内容虽然没改变，但是读取的结果集变了

//引入串行化的方式解决幻读，保持绝对的串行执行事务，此时完全没有并发了

从根本上解决了并发中涉及到的各个问题。此时，并发程度最低（没并发），隔离性最高的，效率最低的，数据是最准确的

mysql提供了“隔离级别”概念，可以直接在mysql配置文件中修改数据库的隔离级别

1.read uncommitted(读未提交--对应脏读问题)并发程度最高，速度最快，隔离性最低，准确性最低

2.read committed(读已提交--解决脏读问题)引入了写加锁，只读写完之后提交的版本。并发程度降低了，速度降低了，隔离性提高了，准确性提高了

3.repeatable read(可重复读--解决不可重复读问题)引入了写加锁和读加锁，写的时候不能读，读的时候不能写。并发程度又进一步降低了，速度降低了，隔离性提高了，准确性提高了           //默认级别

4.serializable(串行化--解决幻读问题)严格的按照串行的方式一个一个的执行事务。并发程度最低（没有并发），速度最低，隔离性最高，准确性最高

那么到此为止，MySQL的基本知识就到此为止了，感谢你能看到这里，希望你在往后的日子里天天开心！😘😘😘