关于MySQL的学习笔记（超详细超完整）

SQL

1.什么是数据库?

​ 1)全称: DataBase 简称:DB
​ 2)数据库其实就是按照一定的格式进行存储数据的一些文件或者文件集合
​ 理解:存储数据的仓库,而这些数据在实时存储的过程中是有特定格式的.

2.什么是数据库管理系统?

​ 1)全称: DataBaseManagement System 简称:DBMS

2).数据库管理系统专用来管理数据库中数据
可以对数据库中的数据进行增删改查操作

3)数据库管理系统类型:
(1)关系型数据库
a.以行和列的方式进行存储
b.常见的关系型数据库:
Mysql Oracle sqlServer DB2 MariaDB
sybase MariaDB
(2)非关系型数据库 - 大数据背景下产生
a.数据结构化存储方式的集合
b.常见的非关系型数据库:
MongoDB Redis Hbase neo4j

3.安装Mysql数据库管理系统

​ 1).默认安装
​ 2).需要进行myql数据库的配置
​ Mysql默认端口号: 3306
​ 端口号通常会和IP地址在一起,IP地址是用来定位计算机的,而端口号是用来定位计算机中某个服务器/应用!

4.配置MYSQL环境变量

​ 1).其实就是配置MYSQL的bin目录
​ 2).默认路径:
​ C:\Program Files\MySQL\MySQL Server 8.0\bin
​ 3).我的电脑 -> 属性 -> 高级系统设置 -> 环境变量 -> 系统变量 -> path -> 添加默认路径(bin)

5.安装的配置

​ 1).Mysql Server --> Mysql服务
​ Mysql workbench --> Mysql官方客户端

2).连接服务器的协议:
C/S -> Client(客户端)/Server(服务器) TCP/IP协议
B/S -> Broswer(浏览器)/Server(服务器) HTTP协议

3).ip + 端口号(port) -> 套接字
a. ip:
本机IP: 127.0.0.1
域名:DNS

​ b. 端口号
​ 端口号是唯一的,所以可以根据端口号找到电脑上相对应的程序.

​ Mysql: 默认3306
​ Oracle: 默认1521
​ SQLServer: 默认1433
​ Tomcat: 默认80

6.命令行使用mysql服务

​ 1). mysql -hlocalhost(127.0.0.1) -uroot -p
​ 2)查看所有数据库
​ show databases;
​ 注意: 以英文的 ; 结尾
​ 3)切换选择数据库
​ use + 数据库名
​ 4)创建一个数据库
​ create database 数据库名
​ 5)退出mysql
​ exit/quit

7.常见连接数据库的客户端:

​ workbench --只能连接mysql
​

SQL Developer --只能连接Oracle,纯java开发的,依赖JRE

navicat --支持连接多种数据库

SQLog --支持连接多种数据库(win)

PLSQL --支持连接多种数据库

8.他们之间都存在什么样的关系?

​ 数据库 数据库管理系统 表 记录

任何一张表中都是存在行 和 列
行(row): 被称为记录/数据
列(column): 被称为字段

总结:
1.一个数据库管理系统中包含多个数据库
2.一个数据库中包含多张表
3.一张表中包含多条记录

9.什么是SQL?

​ 全称: Structured Query Language
​ 结构化查询语言
​

SQL是一套标准,DBMS负责执行SQL语句,来最终完成的数据库中的增删改查操作

10.SQL语句中的分类有哪些?

​ DQL:数据查询语言
​ 凡是带有select关键词的都是属于查询语句
​ select…

DML:数据操作语言
凡是对表中的数据进行增删改的操作
insert:增
delete:删
update:改

DDL:数据定义语言
凡是带有create drop alter都是属于DDL
主要操作的是表的结构,而不是表中的数据
create:增
drop:删
alter:改
show:查

TCL:事务控制语言
事务提交:commit
事务回滚:rollback

DCL:数据控制语言
对用户权限进行设置
例如:
grant(授权)
revoke(撤销权限)

DDL:数据定义语言

一.操作数据库:CRUD

1.C(Create):创建
创建数据库:
create database 数据库名字;
创建数据库之前,判断是否存在,再进行创建:
create database if not exists 数据库名字;

2.R(Retrieve):查询
查询所有数据库的名字:
show database;
查询某个数据库的字符集;查询某个数据库的创建语句
show create database 数据库名字.

3.U(update): 修改
修改数据库的字符集
alter database db_2107 character set ‘字符集名字’

4.D(delete):删除
删除数据库
drop database 数据库名字;

删除数据库之前,进行判断
drop database if exists 数据库名字;

二.操作表:CRUD

​ 1.C(Create):创建
​ create table user(
​ id int comment ‘序号’,
​ name varchar(20) comment ‘名字’,
​ age int(3) comment ‘年龄’
​ );
​ 注意: 最后一列,不需要添加 (,)

​ Mysql数据类型 和 Oracle数据类型对比:
​ 1).数值类型
​ 整数类型
​ int: 不规定长度
​ int(2):最大值99,以10进制方式输出

​ 小数类型:
​ double - 不规定长度
​ double(3,1) - 最大值为99.9,一共有3位数,其中一位是小数

​ oracle数值类型:
​ number(3)
​ number(3,1)

​ 2)文本类型:
​ varchar: 字符串
​ varchar(20): 最大长度为20
​ char(3): 固定长度为3

​ 3)时间类型:
​ date:日期,只包含年月日
​ datetime:日期,包含年月日时分秒
​ timestamp:日期包含年月日时分秒
​ 如果不给这个字段赋值,或者赋值为null
​ 则默认使用当前系统时间,自动赋值

2.R(Retrieve):查询
查询某个数据库中的所有表名称:
show tables;
查询表结构:
desc 表名;
3.U(update): 修改
修改表名:
alter table 表名 rename to 新的表名
修改列名:
alter table 表名 change 列名 新列名 新列名类型;
修改列的类型:
alter table 表名 modify 列名 新数据类型
新增列:
alter table 表名 add 列名 数据类型
修改表的字符集:
alter table 表名 character set ‘字符集名字’

4.D(delete):删除
drop table 表名;
drop table if exists 表名;

DML:数据操作语言

1.添加数据:
语法:
insert into 表名(列名1,列名2)values(值1,值2…)
注意:
a.列名要和值一一对应
b.如果表名后,不定义列名,则默认给所有列添加值
c.除了数字类型,其他类型需要使用引号(‘’ 或者 “”)引起来

2.删除数据:
语法:
delete from 表名 [where 条件]
注意:
1.如果不加条件,则删除表中所有记录
2.如果要删除表中所有记录
truncate table 表名;
注意: 先删除表,后创建一张一模一样的表,推荐使用!! 因为效率高

3.修改数据:
语法:
update 表名 set 列1 = 值1,列2 = 值2 [where 条件]
注意:
如果不添加任何条件,则会将表中所有的记录全部修改

DQL:查询语句

1.简单查询

a.查询一个字段
select 字段名 from 表名;
注意:
a. select 和 from都是关键词
b. 字段名 和 表名 都是标识符

b.查询多个字段
注意: 使用 , 隔开

c.查询所有字段
方式一: 可以把所有的字段名都写上
方式二: 使用 * 表示全部
注意: 这种方式有缺点,查询效率低,可读性差,在实际开发过程中不建议使用

d.给查询的列起别名
使用as 关键词起别名
as 关键字可以省略

e.字段可以结合运算符使用 + - * /

2.条件查询

1).where子句后跟的就是查询条件
2)运算符
> < >= <= = <>

3.模糊查询

like:

占位符:
_: 表示单个任意字符
%: 表示多个任意字符

in(集合)
is null
and 相当于 &&
or 相当于 ||
not 相当于 !

4.分组查询

group by: 按照某个字段或者某些字段进行分组
having: 是对分组后的数据在进行再次过滤

注意:
a.执行顺序:
from xx -> group by xx -> avg(sal)

b.where 后不能跟 分组函数
因为执行顺序: from xx -> where 条件 -> group by xx -> avg(sal)

c.普通函数 和 聚合函数(分组函数)
1).聚合函数是所以数据库通用的函数
count():求总数
max():求最大值
min():求最小值
avg():求平均值
sum():求和
2)注意:
a.在mysql中是不支持聚合函数的嵌套使用
但是在oracle中是支持聚合函数的嵌套使用
b.在mysql中支出聚合函数和普通函数的嵌套使用
c.聚合函数会自动将null值排除在外
①.选择不包含非空的列进行计算,则直接计算
②.如果要包含 则需要结合 ifnull()使用
d.聚合函数得出的结构都是单行单列的结果

3)count() 和 count(字段名)
count():不是统计某个字段中数据的个数,而是统计总记录数.
count(comm):表示统计comm字段中不为null的数据总数量.

5.分页查询

1)语法: limit 开始的索引,每页查询的条数
2)公式: 开始的索引 = (当前的页面 - 1) * 每页查询的条数
3)limit 属于mysql中独有的

6.排序查询

order by

asc

desc

11.约束:

1.什么是约束?
在创建表的时候,可以给表中的一些字段添加上一些约束,来保证表中数据的完整性/有效性.

作用: 为了保证数据的有效!

2.约束的分类:
非空约束: not null
主键约束: primary key (简称PK)
唯一约束: unique
外键约束: foreign key (简称FK)
检查约束: check(mysql不支持,oracle支持)

1)非空约束: not null
特点: 值不能为空

2)主键约束: primary key (简称PK)
特点:非空且唯一
作用: 用来标识唯一

主键类型:
使用代理主键高达8 90%
代理主键: 没有任何业务意义,只需要保证是非空且唯一
自然主键: 在表中本身存在一个非空且唯一的有效字段

自动增长:
varchar: 使用java代码随机生成一套字符串
int: 在数据库内部使用自增长来完成 -> auto_increment

3)唯一约束: unique
特点:
a.值不能为null
b.mysql中可以存在多个null值

4)外键约束: foreign key (简称FK)
a.特点: 让表和表之间产生关联关系,从而保证数据的正确性

b.语法:
constraint 外键名 foreign key
(外键列名) references class(主表列名)

12.数据库的设计三范式:

1.什么是数据库的设计范式?
数据库表的设计依据,教你怎么进行数据库表的设计.

2.数据库的设计范式 - 6个 -> 重点 3个
第一范式:
要求任何一张表必须要有主键,每一个字段原子性不可再分.
第二范式:
建立在第一范式的基础之上,要求所有非主键字段完全依赖主键,不要产生部分依赖.
第三范式:
建立在第二范式的基础之上,要求所有非主键字段值直接依赖主键,不要产生传递依赖.

目的:数据库的设计是为了解决数据冗余的情况.

3.表的设计:
1)一对多(多对一)
两张表,多的表添加外键
实现方式: 在多的一方添加外键,指向一的一方的主键

2)多对多
三张表,关系表两个外键
实现方式: 需要借助第三张表作为中间表,中间表至少含有两个字段,这两个字段作为第三张表的外键,分别指向两张表的主键

3)一对一(了解)
一对一,唯一外键
实现方式: 一对一关系实现,可以在任意一方添加唯一外键,指向另一方的主键.

用户表
------------------------
用户id（pk）              用户名字           

分类表
----------------------
分类id（pk）                 分类内容                    

关系表
-------------
id（pk）                             用户id（fk）               分类id（fk）


路线表
------------
路线id（pk）                 路线内容                 分类id（fk）

13.多表查询(连接查询/表连接查询)

1.什么是表连接查询?
a.从一张表中单独查询,称为单表查询
b.例如:想要找员工对应的部门名字
emp表中和dept表联合起来进行查询数据,需要先从emp表中取出员工姓名,再从dept表中取出部门名字
这种跨表查询,多张表联合起来查询数据,被称为多表查询.

2.表连接查询分类:
1)从语法上进行区分:
SQL92
SQL99(算是比较新的语法)

2)表连接的连接方式区分:
a.内连接
等值连接
例如:e.deptno = d.deptno
条件是等量关系,所以被称为等值连接
非等值连接
例如:e.sal between s.losal and s.hisal
条件不是一个等量关系,被称为非等值连接
自连接
技巧:将一张表看成两张表使用
使用起别名的方式

b.外连接
左外连接(左连接): 驱动表放在 join 之前
语法:left outer join
outer可以省略
右外连接(右连接): 驱动表放在 join 之后
语法:right outer join
outer可以省略

驱动表: 如果你需要一张表中所有的数据都显示出来
那么这张表就作为驱动表

​

c.全连接(不讲 - mysql)

3.当两张表连接没有任何条件限制的时候
最终查询出来的结果条数 -> 是两张表条数的乘积 这种现象被称为: 笛卡尔积现象

如何避免笛卡尔积现象的发生?
在连接时添加条件,满足这个条件的记录则被筛选出来

14.子查询

1.什么是子查询?
select语句中嵌套select语句,被嵌套的select语句被称为子查询.

2.子查询都可以出现在什么地方?
select
…(select子查询)…
from
…(select子查询)…
where
…(select子查询)…

15.存储引擎

show engines;

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-MPqKjDyr-1631270534553)(C:\Users\Lenovo\AppData\Roaming\Typora\typora-user-images\image-20210825092517594.png)]

每一种存储引擎都有自己的优缺点，默认InnoDB

1.什么是存储引擎?存储引擎有什么用?
存储引擎其实就是mysql中的一个术语,每一个存储引擎都对应一种不同的存储方式.
每一种存储引擎都有自己的优缺点.默认InnoDB

1)MYISAM存储引擎
三个文件组成一张表:
mytable.frm - 负责表中格式文件
mytable.MYD - 负责表中的数据文件
mytable.MYI - 负责表中索引文件

优势:可以转换为压缩,只读表来节省空间.
缺点: 不支持事务

2)MEMORY存储引擎
优点:因为是存储在内存中,所以运行速度比较快
缺点: 不支持事务

3)InnoDB存储引擎 - 默认存储
优点: 支持事务,外键,行级锁,这种存储引擎方式中的数据安全会得到保障.
这种存储引擎在数据库崩溃后会提供自动恢复机制.

可以修改字符集方式:
默认utf8mb3
修改: gbk

存储文件: xxx.frm

16.事务

（Transaction Control Language）TCL

1.什么是事务？

​ 一个事务是一个完整的业务逻辑单元，不可再分

什么是完整的业务逻辑单元？

例如：转账，

2. 和事务相关的语句只有：DML语句

   （insert delete update） 因为只有以上这三个语句是对数据库中的数据进行增删改，一旦涉及数据的增删改操作就一定会考虑数据安全问题，而事务的存在就是为了保证数据的完整性和安全性

3. 事务实现的原理：

1)事务的本质
一个事务其实就是多条DML语句同时成功或者同时失败

2)事务提交:commit
事务回滚:rollback
设置保存点:savepoint 名字
回滚到保存点: rollback to 保存点名字

4.事务的四大特性：ACID

A -> 原子性:事务最小的工作单位,不可再分
C -> 一致性:事务必须保证多条DML语句同时成功或者同时失败.
I -> 隔离性:事务A 与 事务B之间具有隔离.
D -> 持久性:持久性就是说最终的数据必须持久化到硬盘文件中,事务才算成功的结束.

5.事务之间的隔离等级:
第一级别:读未提交(read uncommitted)

​ 对方事务还没有提交,我们当前事务就可以读到了对方未提交的数据.
​ 读未提交存在脏读(dirty read)现象: 表示读到脏的数据.

第二等级:读已提交(read committed)

对方事务提交之后的数据我们当前事务可以读到
这种隔离等级解决了: 脏读现象
存在问题: 不可重复读.

第三等级:可重复读(repeatable read)

​ 这种隔离等级解决了: 不可重复读.
​ 存在问题: 读到的数据是幻象

第四等级:序列化读/串行化读(serializable)

​ 解决了所有问题.
​ 但是因为是事务需要排队执行,所以效率低下

oracle默认隔离等级: 读已提交
mysql默认隔离等级: 可重复读

6.修改事务隔离等级.
1.进入数据库
2.修改事务隔离等级
set global transaction isolation level 隔离等级

3.关闭数据库重新进入
a.进入数据库
b.关闭自动提交,开启手动提交方式
strat transaction;
b.在事务1添加数据
在事务2查看数据

17.索引

1.什么是索引？有什么用？

a.索引相当于是一个目录，通过目录快速定位找到对应的资源

b.在数据库中，查询一张表的检索方式：

​ 全表查询

​ 根据索引进行扫描（效率很高）

注意：

添加索引是给某一个字段添加，也可以给某些字段添加

索引虽然可以大大的提高检索效率，但是不能随意添加，因为索引也是数据库中的对象，也是需要数据库进行维护，需要维护成本

2.怎么创建索引对象？怎么删除索引对象？

-- 给sal添加索引   on
create index sal_index on emp(sal); 
explain select ename, sal from emp where sal=5000;
-- 删除索引对象  drop
drop index sal_index on emp;
explain select ename, sal from emp where sal=5000;

3.什么时候考虑给字段添加索引？（满足什么条件）

​ a.数据量庞大（根据客户需求）

​ b.该字段很少进行DML操作（增删改）

​ c.出现在where子句中（经常根据哪个字段进行查询）

4.索引的实现原理

原理实现: 底层数据结构是B + Tree来进行缩小查询范围,底层索引进行了排序,排序完成后进行分区
分区中的数据会携带表中的’物理地址’
最终会通过索引检测到数据之后,获得相关联的物理地址,通过物理地址定位到表中的数据,从而得到最高的查询效率.

通过索引转换为:
select ename form emp where 物理地址 = 0x1

5.索引的分类:
主键索引: 主键上会自动添加索引
唯一索引: 有unique约束的字段上会自动添加索引
单一索引: 给单个字段添加索引
复合索引: 给多个字段添加索引

6.索引什么时候会失效?
select ename from emp where like ‘%S%’
注意:模糊查询的时候,第一个通配符使用的是 % ,这个时候索引是失效的.

18.视图

1.什么是视图
站在不同的角度去看到数据
(看到的是同一张表的数据)

2.怎么创建视图?怎么删除视图
创建视图:
create view 表 as select,ename from emp;
删除视图:
drop view 表名;

注意:只有DQL语句才能以视图的对象方式创建出来.

3.对视图进行增删改查,都会影响到原表中的数据.