MySQL数据库

MySQL数据库基础

介绍数据库

数据库(DataBase)，顾名思义，是存储数据的仓库中。只不过这个仓库是在计算机存储设备上，而且数据是按一定的格式存放的。数据库管理数据有诸多优点，如降低存储数据的冗余度，存储的数据可以共享，便于维护数据完整性，能够实现数据的安全性等。数据库的发展经历了萌芽、初级、中级、和高级阶段。

1、第一个阶段使用磁盘文件来存储数据；初级阶段的数据库也就是第一代数据库，出现了网状模型、层次模型数据库；当然，用文件来保存数据是再正常不过的事了，虽然是第一代，但是并不代表就无用武之地。比如我们现在使用的Excel, XML，YAML等，确切来说，也是属于文件型数据。

上图为层次模型和网状模型

2、中级阶段的数据库也称之为第二代数据库，这一阶段出现了关系型数据库（RDB）和结构化查询语言（SQL）

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JTQJMA5J-1659684383314)(F:\笔记图片\image-20220803151649625.png)]

3、数据库发展到高级阶段出现了**“关系-对象”**型数据库。目前使用最多的数据库还是关系型数据库。以关系模型来创建的数据库称为关系型数据库（Relational Database）。关系模型的理论是1970年由IBM的Codd博士提出的。关系模型把世界看作是由实体（Entity）和联系（Relationship）组成的。所谓实体就是指在现实世界中客观存在并可相互区别的事物。关系型数据库的核心单元是表，它是存储数据的地方。

4、除些之外，数据库现在已经发展到第四代，就是我们平时听到的“大数据”（Big Data）。这是一种非结构化的数据，本章内容主要为大家介绍目前软件系统最核心最重要的数据库：关系型数据库。

数据库常用操作

数据库管理系统是位于用户与操作系统之间的一层数据管理软件，其主要目标是使数据作为一种可管理的资源来处理，主要功能如下：

• 数据定义功能：DBMS提供数据定义语言（Data Definition Language，简称DDL），用户通过它可以方便地对数据库中的数据对象进行定义。
• 数据操纵功能：DBMS也提供数据操作语言（Data Manipulation Language，简称DML），供用户实现对数据的基本操作，如插入、删除、修改
• 数据查询功能：DBMS还提供数据查询语言（Data Query Language，简称DQL），供用户实现对数据的查询操作
• 数据控制功能：DBMS还提供数据控制语言（Data Control Language，简称DCL），主要用于对数据库的用户、角色和权限进行管理和控制

总体上，对数据的操作，基本上都支持CRUD：Create，Retrieve，Update，Delete

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-S3337o3o-1659684383315)(F:\笔记图片\image-20220803152001490.png)]

• 数据库：一个用于存放数据的仓库，可以分类为：关系型数据库，非关系型数据库，内存型数据库。
• 数据库发展历程：一代数据库（层次或网状数据库），二代数据库（关系型数据库以及SQL查询语言），三代数据库（“关系-对象”型数据库）
• 常见关系型数据库：ACCESS，MS SQLServer，DB2，MYSQL，PostgreSQL，Oracle等
• 关系型数据库中常见的关系模式：一对一关系，一对多关系，多对多关系
• SQL 结构化查询语言，是一种专门用于管理关系型数据库，并与之通讯的计算机语言。
• SQL分类为：DDL，DQL，DML，DCL

数据类型

数值型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lDLWNEnL-1659684383316)(F:\笔记图片\image-20220803153401962.png)]

字符型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RwirEZ3o-1659684383316)(F:\笔记图片\image-20220803153434764.png)]

日期和时间类型

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QFDld3re-1659684383317)(F:\笔记图片\image-20220803153536933.png)]

DCL数据控制语言

数据控制语言 (Data Control Language) 在SQL语言中，是一种可对数据访问权进行控制的指令，它可以控制特定用户账户对数据表、查看表、存储程序、用户自定义函数等数据库对象的控制权。由 GRANT 和 REVOKE 两个指令组成

DCL（Data Control Language）是数据库控制语言。是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。

在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Kk7ltRqJ-1659684383317)(F:\笔记图片\image-20220805091448549.png)]

grant（授权用户权限）

grant 权限类型 [列名] [权限类型 列名 ] on 对象 权限级别 to 用户
grant 权限1,权限2... on 数据库.对象名 to '用户'@‘授权ip’

# *.* 代表所有数据库的所有对象
# IDENTIFIED BY 'password' 密码
# %所有的主机
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION; // %:表示从任何主机远程连接到mysql服务器

GRANT ALL PRIVILEGES ON *.* TO 'user'@'116.30.70.187' IDENTIFIED BY 'password' WITH GRANT OPTION;//表示从指定ip从任何主机远程连接到mysql服务器

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-otW4fZ0K-1659684383318)(F:\笔记图片\image-20220804144727489.png)]

权限类型：

• 授予数据库权限时
  • SELECT：表示授予用户可以使用选择语句访问特定数据库中所有表和视图的权限。
  • INSERT: 表示授予用户可以使用INSERT语句向特定数据库中所有表添加数据行的权限。
  • DELETE:表示授予用户可以使用DELETE语句删除特定数据库中所有表的数据行的权限。
  • UPDATE:表示授予用户可以使用UPDATE语句更新特定数据库中所有数据表的值的权限。
  • REFERENCES:表示授予用户可以创建指向特定的数据库中的表外键的权限。
  • CREATE:表示授权用户可以使用CREATE TABLE语句在特定数据库中创建新表的权限。
  • ALTER:表示授予用户可以使用ALTER TABLE语句修改特定数据库中所有数据表的权限。
  • SHOW VIEW:表示授予用户可以查看特定数据库中已有视图的视图定义的权限。
  • CREATE ROUTINE:表示授予用户可以为特定的数据库创建存储过程和存储函数的权限。
  • ALTER ROUTINE:表示授予用户可以更新和删除数据库中已有的存储过程和存储函数的权限。
  • INDEX:表示授予用户可以在特定数据库中的所有数据表上定义和删除索引的权限。
  • DROP:表示授予用户可以删除特定数据库中所有表和视图的权限。
  • CREATE TEMPORARY TABLES:表示授予用户可以在特定数据库中创建临时表的权限。
  • CREATE VIEW:表示授予户可以在特定数据库中创建新的视图的权限。
  • EXECUTE ROUTINE:表示授予胪可以调用特定数据库的存储过程和存储函数的权限。
  • LOCK TABLES:表示授予用户可以锁定特定数据库的已有数据表的权限。
  • ALL或ALL PRIVILEGES:表示以上所有权限。
• 授予表权限时
  • SELECT:授予用户可以使用SELECT语句进行访问特定表的权限。
  • INSERT: 授予胪可以使用INSERT语句向-一个特定表中添加数据行的权限。
  • DELETE:授予用户可以使用DELETE语句从一个特定表中删除数据行的权限。
  • DROP:授予用户可以删除数据表的权限。
  • UPDATE:授予用户可以使用UPDATE语句更新特定数据表的权限。
  • ALTER:授予用户可以使用ALTER TABLE语句修改数据表的权限。
  • REFERENCES:授予用户可以创建- - 个外键来参照特定数据表的权限。
  • CREATE:授予用户可以使用特定的名字创建- 个数据表的权限。
  • INDEX:授予用户可以在表上定义索引的权限。
  • ALL或ALL PRIVILEGES:所有的权限名。

revoke（取消授权）

revoke 权限1,权限2,... on 数据库名.对象名 from '用户名'@'允许登录的IP'
flush privileges # 刷新权限

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5EyH516Y-1659684383318)(F:\笔记图片\image-20220804144739486.png)]

DDL数据定义功能

简介

数据库模式定义语言DDL：用于描述数据库中要存储的现实世界实体的语言

数据库操作

# 创建数据库
create database db_name;

# 删除数据库
drop database db_name;

数据表操作

# 创建数据表
create table t_name(
    字段名1 数据类型 [约束][默认值],
    字段名2 数据类型 [约束][默认值],
    ......
    [表级别约束条件]
);
	# mysql数据库有保留字，命名为保留字会报错，要加上``就不会报错
create table `name`(id tinyint,name varchar(15),phone tinyint);

# 修改数据表名
alter table 表名 rename[to] new_表名;

# 修改字段数据类型
alter table 表名 modify 字段名 数据类型;

# 修改字段名
alter table 表名 change 旧字段名 新字段名 数据类型;

# 添加字段名
alter table 表名 add 新字段名 数据类型 [约束条件]

# 删除字段名
alter table 表名 drop 字段名

# 删除数据表
drop table [if exists] 表1,表2,...

DML数据操纵功能

数据操纵语言（Data Manipulation Language, DML）是用于数据库操作，对数据库其中的对象和数据运行访问工作的编程语句，通常是数据库专用编程语言之中的一个子集

插入数据

# 数值型可以不加单引号  一般使用单引号包裹字符
insert into 表名 (列1,列2,...) values (列1,列2,...)

# 如果所有的列都要插入数据可以省略第一个括号
insert into 表名 values (列1,列2,...)

# 插入多条数据
insert into 表名 values 
(列1,列2,...),
(列1,列2,...),
(列1,列2,...);

更新数据

update 表名 set 列1=值1,列2=值2,... where 条件;

删除数据

delete from 表名 [where 条件];

delete from 表名 # 删除全部数据
TRUNCATE TABLE person;#删除全部数据，对表进行初始化

DQL数据查询语言

数据库表约束和范式

表设计范式

第一范式：确保每列的原子性

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。

第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算 满足了数据库的第一范式

第二范式：确保每行的唯一性

第二范式必须先满足第一范式。第二范式要求数据库表中的每个实例或行必须可以被唯一区分，即要求每个实体都必须具有主键进行区分，要求列名与主键要有相关性

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5wzF1QGp-1659684383319)(F:\笔记图片\image-20220803181348621.png)]

应该为

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ICxkaMN2-1659684383319)(F:\笔记图片\image-20220803181406139.png)]

第三范式：确保每列都和主键列直接相关而不是间接相关

第三范式需要确保数据表除外键外。其他非主键不得重复出现在第二张表中

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VVYkkmqu-1659684383320)(F:\笔记图片\image-20220803181417714.png)]

约束

约束：就是指我们在创建表的过程中，给表的字段添加一些条件！

可以保证数据的唯一性，完整性。有的约束针对整行有效 ，有的约束只针对某一列有效

完整性约束

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-x6XU7yPV-1659684383320)(F:\笔记图片\image-20220803154305572.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DlitUa8k-1659684383320)(F:\笔记图片\image-20220803154312699.png)]

• 域完整性：限制数据类型、外键约束、默认值、非空约束
• 实体完整性：唯一约束、主键约束、自增列
• 参照完整性：主外键关联
• 自定义完整性：规则、存储过程、触发器

主键约束

表中有一列或几列组合的值能用来唯一地标识表中的每一行，这样的一列或者多列的组合叫做表的主键。如：学号可以作为学生表的主键

一个表只能有一个主键，主键约束确保了表中的行是唯一的。

表中可以没有主键，但是通常情况下应当为表设置一个主键。

主键选择原则：

• 最少性：尽量选择一个键作为主键
• 稳定性：尽量选择数值更新少的值作为主键
• 去业务性：尽量不选择具备业务含义的列作为主键，例如：身份证，电话号码……

创建主键方式

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ewlqQAXN-1659684383320)(F:\笔记图片\image-20220803161728274.png)]

字段定义后面声明

create table duyun(
id int primary key,
name varchar(15),
);

constraint声明主键

create table duyun(
id int,
name varchar(15),
constraint pk_duyun primary key (id)
# pk_表名
)engine=innodb character set=utf8mb4;

create table duyun(
id int,
name varchar(15),
primary key (id,name)
# 复合主键
)engine=innodb character set=utf8mb4;

alter

alter table duyun add constraint pk_duyun primary key (id);

删除主键

alter table duyun drop primary key;

外键约束

什么是外键：简单地说，就是“子表”中对应于“主表”的列，在子表中称为外键或者引用键。

它的值要求与主表的主键或者唯一键相对应，外键用来强制引用完整性。例如在成绩表中，学号为外键。一个表可以有多个外键。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-VStRaWIN-1659684383321)(F:\笔记图片\image-20220803154925223.png)]

指定外键更新或删除的行为：

• 子表试图创建/修改一个在父表不存在的外键值，InnoDB会拒绝任何insert或update操作
• 父表试图删除/修改任何子表中存在或匹配的外键值，取决于外键约束定义中的ON UPDATE和ON DELETE
• InnoDB支持5种不同的动作，如果没有指定ON DELETE或者ON UPDATE，默认的动作为RESTRICT
  • CASCADE: 从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行
  • SET NULL: 从父表中删除或更新对应的行，同时将子表中的外键列设为空。注意，这些在外键列没有被设为NOT NULL时才有效
  • NO ACTION: InnoDB拒绝删除或者更新父表
  • RESTRICT: 拒绝删除或者更新父表

创建外键：

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QS87vpsS-1659684383321)(F:\笔记图片\image-20220803161650480.png)]

建表时使用 foreign key声明

create table duyun(
id int primary key,
name varchar(15),
);

create table duyun_fk(
    id int,
    name varchar(15),
    foreign key (id) references duyun(id) on delete cascade on update cascade,
    # on ......  设置规则
);

使用constraint

create table duyun(
id int primary key,
name varchar(15),
);

create table duyun_fk(
    id int,
    name varchar(15),
);
alter table duyun_fk add constraint id foreign key (id) references duyun(id) on delete cascade on update cascade;

删除外键

alter table 表名 drop foreign key 外键名字;

1. 数据库中约束可分为：列/域完整性，实体完整性，参照完整性，自定义完整性。
2. 列完整性，主要控制列的条件。而实例完整性，主要控制行的条件。参照完整性，主要描述表与表之间的相关性
3. 主键约束的目的：主要保证行数据的完整性，以及唯一性
4. 外键约束的目的：主要保证“子表”能根据外键，找到“主表”的数据
5. 当然外键约束，可能存在一定的级联操作，特别是更新或删除数据时。

自增长

自增长，也是一种约束。但是这种约束，只能定义在主键上，且一张表只能有1个自增长！

自增长约束的目的，让主键列可以实现自动变化，程序员减少对主键的生成性的操作。

auto_increment 只针对数值类型有效，其他类型将无法使用！

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JChfpEgD-1659684383321)(F:\笔记图片\image-20220803162842840.png)]

create table student(
    id int primary key auto_increment
);

唯一约束

唯一约束是确保数据行完整性的一种

唯一约束的目的：保证某一列数据的唯一性，例如：身份证，电话号码…… ，如果重复添加，将抛出异常！

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-WjTGCcb0-1659684383322)(F:\笔记图片\image-20220803162806886.png)]

create table student(
    name varchar(15),
    phone varchar(11) unique,
)

非空约束

非空约束，主要保证某一列的值，一定要有值，不能为Null

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XCHl3lQ9-1659684383322)(F:\笔记图片\image-20220803162903559.png)]

create table student(
    name varchar(15) not null,
    phone varchar(11) unique,
)

默认情况下，如果列名后未加not null，该列数据是可以添加null数据的

检查约束

检查约束：检查某一个列的值，要求是自己规定的值

create table student(
    name varchar(15) not null,
    phone varchar(11) unique,
    sex set('男','女'), # enum
)

alter table 表名 modify 列名 set(值)

查询语句

单表查询

基础

select 列名/所有列*/函数 from 表名1,表名2,... [where 条件 | group by 分组条件 | having 过滤条件 | order by 排序列];

 # <> 就是 !=  where后面跟的是条件
select sname from students where age >= 18 and sex='男' and degree <> '大专';

# 查询是 大专和本科的
select sname from students where degree in ('大专','本科');

# 排序
select * from students  where sex='男' order by age asc; # 按照age从小到大排序 默认
select * from students  where sex='男' order by age desc; # 按照age从大小达排序
select * from students  where sex='男' order by 1 asc; # 按照第一列从小到大排序

# 去重
select distinct(sid) from student;

# 范围查找
select * from student where score >= 90 and score <= 100; # 等价
select * from student where score between 90 and 100;

# 查询最值、平均值
select max(sorce) from student;
select min(sorce) from student;
select * from student where age=32;
# 子查询  =要确保子查询只有一条  多条要使用in
select * from student where age=(select max(sorce) from student);
# 平均值
select avg(score) from student where class='安全2005';

# 统计个数
select count(*) from student;

# 求和
select sum(sorce) from student;

# 模糊查询like % 代表任意字符   _ 代表一个字符
SELECT * FROM goods WHERE goodsname LIKE '健康___';
SELECT * FROM goods WHERE goodsname LIKE '健康%'; # 推荐使用
# 包含子的商品
SELECT * FROM goods WHERE goodsname LIKE '%子%';

# 起别名 as
select avg(score) as 平均值 from student where class='安全2005';
select avg(score) 平均值 from student where class='安全2005';

# 查询考试成绩排名前十的学生
select * from grade order by socre decs limit 10;
# 查询考试成绩前三的学生，要求并列成绩
SELECT * FROM grade WHERE score IN (SELECT DISTINCT(score) FROM(SELECT DISTINCT(score) FROM grade ORDER BY score DESC LIMIT 3)as tips); 
SELECT * FROM grade WHERE score IN (SELECT score FROM(SELECT score FROM grade GROUP BY score ORDER BY score DESC LIMIT 3)as tips);
# 注意点 1、起别名 否则报错
#		2、子查询不支持limit，要加一层循环
#		3、limit完整语法：limit 开始行数,查询行数 limit 0,10 第一行开始查询10g

聚合函数

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-s3KWJjZT-1659684383322)(F:\笔记图片\image-20220803235014741.png)]

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-BUXESij7-1659684383322)(F:\笔记图片\image-20220803235031257.png)]

补充

• in / not in：查询条件在一个集合种，存在 / 不存在 可以使用or代替

• any / all：any用于条件判断的子查询，里面是sql语句

• is null / is not null：是否为空

• union：将两条sql语句的查询结果拼成一个结果集

  • 两张结果的表的列数必须一样
  • 数据类型要兼容

• select xxx

# in / not in
select * from grade where socre=100 or socre=90;
select * from grade where socre in (90,100); 

# any 任意   or    any小于最大的，大于最小的  查询本科学生比研究生年纪最大的小的
select * from studet where degree='本科' and age < any(select distinct(age) from student where degree='研究生') ; # age小于其中任意一个 age<max  

# all 全部   and   all大于最大，小于最小  查询本科学生比研究生年纪最小的还小的
select * from studet where degree='本科' and age < all(select distinct(age) from student where degree='研究生') ; 

# 分数加2
update grade set score=score+2;
# 分数列和其他列相乘
select gradeid * score from grade;

分组查询

group by

# 查询每门课程的平均成绩，按照courseId分组，然后根据分组之后的执行avg。自带去重，可以代替distinct
select courseId,avg(score) from grade group by courseId;

# 对group by 聚合函数计算出来的数据时候 having 过滤
select courseId,avg(score) as avgScore from grade group by courseId having avgScore>=70;

# 可以针对多列进行分组
#sex有2种值 degree有3种值  所有有六列
select sex,degree,avg(age) from grade group by sex,degree;

子查询

临时表

1、子查询把一个select查询的结果当作select的from必须起别名 否则报错

2、子查询不支持limit，要加一层循环(外面套一层)

3、limit完整语法：limit 开始行数,查询行数 limit 0,10 第一行开始查询10g

# 查询考试分数前三的学生
SELECT * FROM grade WHERE score IN (SELECT DISTINCT(score) FROM(SELECT DISTINCT(score) FROM grade ORDER BY score DESC LIMIT 3)as tips); 
SELECT * FROM grade WHERE score IN (SELECT score FROM(SELECT score FROM grade GROUP BY score ORDER BY score DESC LIMIT 3)as tips);

# 查询考试排名前十的学生，有并列查询出所有并列的，并按照从大到小排序 distinct加不加都可以
SELECT * FROM grade WHERE score IN (SELECT DISTINCT(score) from (SELECT score FROM grade ORDER BY score DESC LIMIT 10)as a) ORDER BY score DESC;
# SELECT score FROM grade ORDER BY score DESC LIMIT 10#查询前十位的成绩
# (SELECT DISTINCT(score) from (SELECT score FROM grade ORDER BY score DESC LIMIT 10)as a)#对前十位的成绩进行去重

# 拆分子查询：从内zh
select * from grade where courseid='C10' and score in (select score from (select score from grade where courseid='C10' order by score desc limit 10)as temp where temp.score>97)

多表查询

查询出来的也是一张单表

内连接

一般是两张表有关系

在进行多表连接查询时，必须指定连接条件（通常是主外键），但是主外键关系没有明确在表中建立也可以使用。表中无主外键，键在心中

数据库要维持主外键的关系是有性能开销的，因为删除和修改外键时，要扫描一边外表

• ,
• join…on
• inner join … join … join … on

# 笛卡尔积 必须有一个条件是主外键相等进行过滤
SELECT * FROM sell,sellsum WHERE sell.sellsumid = sellsum.sellsumid;
SELECT sell.sellid,sell.sellsumid,goodsname,paymethod FROM sell,sellsum WHERE sell.sellsumid = sellsum.sellsumid and ...; # 可以将and前面的结果看作一个临时表 temp

# 对表重命名，简化SQL语句
SELECT se.sellid,se.sellsumid,se.goodsname,ss.paymethod FROM sell se,sellsum ss WHERE se.sellsumid = ss.sellsumid;

# 除了使用 from a,b,c 还可以使用 join ... on  on后面跟的是连接的条件
SELECT sell.sellid,sell.sellsumid,goodsname,paymethod FROM sell join sellsum on sell.sellsumid = sellsum.sellsumid;
# 三个表
SELECT * FROM sell,sellsum,customer WHERE sell.sellsumid = sellsum.sellsumid and customer.customerid=sellsum.customerid and unitprice='188';

外连接

左表和右表都不做限制，所有的记录都显示，两表不足的地方用null 填充。 全外连接不支持（+）这种写法。

• left join：左边的表为基准
• right join：右边的表为基准
• full join

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ugycUFlT-1659684383323)(F:\笔记图片\image-20220804175344544.png)]

视图

create view 试图的名字 as 
SQL语句;
# 将某些SQL语句的查询结果永久的保存在类似一张表的结构中，可以非常方便的把一些复杂的SQL语句和业务逻辑封装起来
# 本质类似于子查询中的临时表
# 如果某天表的结果发生变化，写在视图中只需要修改视图一个地方
# 针对视图可以删除 修改 插入，会对应物理表中操作，仅仅建议查询其余不建议

MySQL补充

索引

**作用：**index，目录，宝珠数据库提升查询性能，避免全表扫描导致速度极慢，数据量越大越需要索引

Hash索引

MySQL中只有Memory（Memory表只存在内存中，断电消失，适用于临时表）存储引擎显示支持Hash索引，是Memory表的默认索引类型，也可以使用B+Tree索引。

hash索引把数据的索引以hash形式组织起来，因此当查找某一条记录的时候，速度很快。

hash结构每个键对应一个值，散列式分布的，不支持范围查找和排序等功能

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-KUd33oue-1659684383323)(F:\笔记图片\image-20220804183621046.png)]

B+Tree索引

B+Tree从Mysql5.5之后式使用最频繁的一个索引数据结构，式InnoDB和MyISAM存储引擎模式的索引类型。

在查找单条记录速度比不上hash，但是更适合排序等操作

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yMTkXX7p-1659684383323)(F:\笔记图片\image-20220804194959839.png)]

常见索引类型

primary key（主键索引）：alter table table_name add primary key (col)

unique（唯一索引）：alter table table_name add unique unique_name(col)

index（普通索引）：alter table table_name add index index_name(col)

fulltext（全文索引）：alter table table_name add fulltext(col)

证实

加索引和不加索引的查询

EXPLAIN SELECT * FROM `user` WHERE `password`='admin123';  # explain 执行计划

加索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-EOEwG7yX-1659684383323)(F:\笔记图片\image-20220804200603610.png)]

不加索引

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Fcebwu60-1659684383324)(F:\笔记图片\image-20220804200442785.png)]

注意：使用like和聚合函数，无法使用索引。

创建索引需要花费计算资源和存储资源，不使用频繁插入和更新数据的表

select其他用法

select "xxxxx" into outfile /tmp/xxx.t # 把xxxxx写入/tmp/xxx.txt

# 创建临时表：保存在内存中的表，执行速度很快 
# 断开连接表消息，无法使用show tables 开看到临时表
create temporary table 表名(列名,列类型)

# 利用select创建表
create table name select ... from ...

备份

# 备份
mysqldump -uxxx -pxxx -hxxxx xxx>/opt/xxx.sql
# 还原数据库
mysql -uxxx -pxxx -hxxxx xxx</opt/xxx.sql 

权限DCL

具体MySQL基础的DCL

任何情况下，先授予最小权限，再根据实际操作授予具体权限

MySQL进阶

主从复制

复制类型

• 基于语句的复制。在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。
• 基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
• 混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时， 就会采用基于行的复制。

主从复制：两台机器进行数据的实时同步。Master Slave

所有数据以Master为准，Slave进行实时复制同步、实施备份

读写分离：写数据到Master，读数据从Slave

配置ntp服务（保证主从服务器时间一致）

# 主服务器
yum -y install ntp
vi /etc/ntp,conf
	server 127.127.1.0
	fudge 127.127.1.0 stratum 8
systemctl restart ntpd
systemctl enable ntpd # 开启自启动

# 从服务器
yum -y install ntpdate
ntpdate 192.168.10.1(主服务器ip)

安装mysql服务器

rpm -ivh  xxxxxx.rpm
vi /etc/yum.repos.d/mysql-community.repo

#修改  5.6版本节点 enable=1  8.0版本节点 enable=0
yum install mysql-server

允许远程连接

grant all privileges on *.* to 'user'@'%' identified by 'duyun' with grant option;
flush privileges;

# 如果仅仅是为了同步数据，权限可以分配为 replication slave 不需要其他权限

配置主服务器

修改mysql服务器

# 修改配置文件 my.cnf

# 下载的开源的mysql：vi /etc/my.cnf
# xampp的MySQL：vi /opt/lampp/etc/my.cnf
[mysqld]
log_bin=mysql-bin  # 开启二进制日志
server-id=1     # 设置server-id

重启mysql，创建用于同步的用户账号

打开mysql会话shell->mysql -hlocalhost -uname -ppassword

创建用户并授权：用户：rel 密码：slavepasswd

create user 'rep'@'192.168.10.66' identified by 'slavepasswd' # 创建用户
grant replication slave on *.* to 'rep'@'192.168.10.66';# 分配权限
flush privileges; # 刷新权限

查看master状态，记录二进制文件名（mysql-bin.000001）和位置（120）

show master status;
+-----------------+-----------------+-----------------+----------------+
| File            |Position         |Binlog_Do_DB     |Binlog_Ignore_DB| 
+-----------------+-----------------+-----------------+----------------+
|mysql-bin.000001 |120              |test             |manual,mysql    |
+-----------------+-----------------+-----------------+----------------+

配置从服务器

修改mysql服务器

[mysqld]
server-id=2 # 必须唯一

重启mysql，打开mysql会话，执行同步sql语句

需要主服务器主机名、登录凭证、二进制文件名称和位置

mysql>change master to master_host="192.168.10.25(主服务器ip)",master_port=3306,master_user='username',master_password='password',master_log_file='mysql-bin.000001',master_log_pos=120,
master_connect_retry=10;

启动salve同步进程

start slave

查看slave状态

show slave status\G;
# 找 Slave_IO_Running,Slave_SQL_Running

# 如果不是yes的话，修改slave上的UUID
vi /var/lib/mysql/auto.cnf
server-uuid=xxxxxxxxxxxxxxxxxx
# 任意修改一位uuid  保持不重复即可，然后重启mysql

读写分离

读写分离就是只在主服务器上写，只在从服务器上读，基本的原理是让主数据库处理事务性查询，而从数据库处理 select 查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库

分类

1、基于程序代码内部实现

在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。 优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支；缺点是需 要开发人员来实现，运维人员无从下手。

2、基于中间代理层实现

• 代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后 端数据库，有两个代表性程序
  • MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行 SQL 判断，虽然是 MySQL 官方产品，但是 MySQL 官方并不建议将 MySQL-Proxy 用到生产环境。
  • Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由 Java 语言进行开发，阿里巴巴将其用于生产环境。它不支持事务和存储过程。

配置：https://www.woniuxy.com/book/reading/215

内置函数

# substr(开始，长度)    截取字符串
# concat 拼接
# hex  转化十六进制  和字符串相比不需要加引号
select * from student where name=0x4321498abef

select user() # 获取当前用户
select version()# 数据库版本
select database() # 当前使用的数据库

https://www.runoob.com/mysql/mysql-functions.html