MySql数据库6666 思维笔记模板_ProcessOn思维导图、流程图

数据库介绍

数据库概念

数据库（Database）是按照数据结构来组织、存储和管理数据的仓库

数据库的发展史

最早期以穿孔卡片的方式，按照一定的排列方式记录数据

数据库管理系统DBMS

是一种操纵和管理数据库的大型软件，用于建立，使用和维护数据库，简称DBSM

它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性

数据库管理系统是数据库的核心，是管理数据库的软件

我们一般说的数据库，就是指DBMS，数据库管理系统

常见的数据库

Oracle

运行稳定，可移植性高，功能齐全，性能超群，适用于大型企业领域

DB2

速度快，可靠性好，适用于海量数据，恢复性极强，适用于大中型企业领域

Mysql

开源，体积小，速度快，适用于中小型企业领域

SQL Server

全面，效率高，界面友好，操作容易，但是不跨平台，适用于中小型企业领域

专业术语

表

具有固定的列数和任意的行数

列

一个数据项 Field 字段

行

一条记录 row

数据库

数据库是一些关联表的集合

主键

主键是唯一的，一个数据表只能包含一个主键，可以使用主键来查询数据

外键

外键用于关联两个表

索引

使用索引可快速访问数据库表中的特定信息，索引是对数据库表中一列或多列的值进行排序的一种结构，类似于书籍的目录

MySql数据库

MySql介绍

MySql是一个关系型数据库管理系统，由瑞典MySql AB公司开发，目前属于Oracle公司
MySql是一种关联数据库管理系统，关联数据库将数据保存在不同的表中，而不是将所有
数据放在一个大仓库中，这样就增加了处理速度，提高了灵活性

MySql是开源的，所以不需要支付额外的费用

MySql支持大型的数据库，可以处理拥有上千万条记录的大型数据库

MySql使用标准的SQL数据语音形式

MySql可以应用于多个系统上，并且支持多种语言，这些编程语言包括C、C++、Python、Java、PHP

MySql安装

官网下载，注意安装电脑对应系统位数的软件

配置环境变量

常用数据库命令

MySql数据库分为两种

系统数据库（自带4个）

information_schema

存储数据库对象信息

如用户表信息，列信息，字符，分区

里面的内容我们不能动

performance_schema

存储服务器性能参数信息

mysql

存储数据库用户权限信息

sys

通过这个库可以快速的了解系统的元数据信息

用户数据库

用户自己创建的数据库，一个项目用一个数据库

创建数据库

create database 数据库名;

删除数据库

drop database 数据库名;

查看数据库

show databases;

使用数据库

use 数据库名;

查看当前数据库中有多少张表

show tables;

注意写的顺序，先使用数据库，再去查看有多少张表

连接Navicat

Navicats是一套数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。

Navicat是以直觉化的图形用户界面而建的，可以安全和简单地创建，组织，访问并共用数据库信息

字符集

字符集的由来

计算机只能识别二进制代码，无论是计算机程序还是数据，最终都会转成二进制，计算机才能认识

为了计算机不只能做科学计算，也能处理文字信息。
人们想出了给每一个文字符号编码以便于计算机识别处理的办法，这就是计算机字符集的由来。

我 00001

人 00010

ASSCll

一套文字符号及其编码，比较规则的集合

20世纪60年代初，美国标准化组织ANSI发布了第一个字符集，ASCII

后来又进一步变成了国际标准ISO-646

各大字符集

自ASCII后，为了处理不同的文字，各大计算机公司，各国，
标准化政府、组织先后发明了几百种字符集

这些五花八门的字符集从收录的字符集到编码规则各不相同，给计算机软件开发
和移植带来了很大的困难。
一个软件要在文字不同的国家和地区发布，必须要做本地化开发

基于这个原因，要统一字符编码

Unicode

为了统一字符编码，国际标准化组织ISO的一些成员国于1984年发起制定了新的字符集标准，
容纳全世界各种语言、文字和符号，最后这个标准ISO-10646

ISO-10646发布后，遭到了美国计算机公司的反对。

1988年，Xerox公司提议制定了新的以16位编码统一字符集，并联合Apple，IBM，SUN，
Microsoft等公司成立了Unicode技术委员会，专门负责收集，整理和编码，于1991年推出了
Unicode1.0

都是为了字符编码统一问题，ISO和Unicode协会推出了不同的标准，显然是不利的。
双方开始谈判，1991年10月达成协议，ISO将Unicode收编，起名BMP

Unicode是用0至65535之间的数字来表示所有字符

其中0-127，这128个数字表示的字符和ASCII完全一样

UTF-16

怎么把0至65535这些数字转化成01串保存到计算机中

于是出现了UTF（Unicode transformation format）

UTF-16比较好理解，就是任何字符对应的数字都用两个字节表示

但是很显然，如果都是英文字符，明明用一个字节能表示一个字符，有点浪费

UTF-8

基于以上原因，于是又有了UTF-8

这里的8不是指一个字节

当用UTF-8表示一个字符是可变的，有可能是用一个字节表示一个字符，
也可能是两个三个，是根据字符对应的数字大小来确定。

汉字的一些常用字符集

GB2312

GB13000

GBK

GB18030

MySql存储引擎

什么是存储引擎

MySql中的数据用各种不同的技术存储在文件（或内存）中，这些技术中的
每一种技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛
的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或功能，
从而改变你的应用的整体功能。

不同的存储引擎性能是不一样的

存储引擎分类

MYISAM

它不支持事务，也不支持外键，尤其是访问速度快，对事务完整性没有要求或者
以SELECT、INSERT为主的应用基本都可以使用这个引擎来创建

每个MyISAM在磁盘上存储成3个文件，其中文件名和表名
都相同，但是扩展名不同

.frm（存储表定义）

MYD(MyData，存储数据)

MYI（MyIndex,存储索引）

INNODB

InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比
MyISAM的存储引擎，InnoDB写的处理效率差一些并且会占用更多的磁盘空间
以保留数据和索引。

MEMORY

memory使用存在内存中的内容来创建表。每个MEMORY表实际对应一个磁盘文件，格式是.frm

MEMORY类型的表访问非常快，因为它的数据是放在内存中的，并且默认使用HASH索引，
但是一旦服务器关闭，表中数据会丢失，表会继续存在。

什么是SQL

SQL是Structureed Query language（结构化查询语言）的缩写

SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。

在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的

SQL功能分类

DDL-数据定义语言

用来定义数据库对象：创建库，表，字段等

DML-数据操作语言

用来操作数据库表中的记录

DQL-数据查询语言

用来查询数据

DCL-数据控制语言

用来定义访问权限和安全级别

SQL数据类型

MySql中定义数据字段的类型对你数据库的优化是非常重要的。

MySQL支持所有标准SQL数值数据类型

MySQL支持多种类型，大致分为三类

数值型

子主题

字符串类型

日期和时间类型

常用数据类型

double：浮点型，例如double（5,2）表示最多5位，其中必须有
2位小数，即最大值999.99

char：固定长度字符串类型；char（10） 'abc '

vachar：可变长度字符串类型；varchar（10） 'abc'

text:字符串类型

blob：二进制类型

date：日期类型，格式：yyyy-MM-dd

time：时间类型，格式：hh:mm:ss

datetime:日期时间类型：yyyy-MM-dd hh:mm:ss

在MySQL中，字符串类型和日期类型都要用单引号括起来。'2021-12-5'

DDL

创建数据库

create database 数据库名 character set utf-8;

修改数据库

alter database 数据库名 character set gbk;

创建学生表

create table

添加一列

ALTER TABLE 表名 ADD 列名数据类型 default 默认值;

修改字段的类型

ALTER TABLE 表名 MODIFY 字段名数据类型;

修改表名

RENAME TABLE 原表名 TO 新表名;

修改表的字符集为gbk

ALTER TABLE 表名 CHARACTER SET 字符集名称;

修改表的列名

ALTER TABLE 表名 CHANGE 原列名新列名数据类型;

查看表的字段信息

DESC 表名;

查看表的创建细节

SHOW CREATE TABLE 表名;

删除一列

ALTER TABLE 表名 DROP 字段名;

删除表

DROP TABLE 表名;

DML

查询表中所有数据

SELECT * FROM 表名;

DML是对表中数据进行增、删、改的操作

插入操作

INSERT INTO 表名（列名1，列名2..） VALUE(列值1，列值2..);

注意事项

列名与列值的类型，个数，顺序要一一对应

如果不写列名，默认全部不为空列名都要有值

值不要超出列定义的长度

插入的日期和字符一样，要使用引号括起来

批量插入

INSERT INTO 表名（列名1，列名2..） VALUES(列值1，列值2..),(列值1，列值2..);

更新操作

UPDATE 表名 SET 列名1=列值1，列名2=列值2...WHERE 列名=值;

把所有学生年龄加一岁

把姓名为小明的身高改为1.88

把姓名为李四的年龄改为20，身高加2厘米

把小红的年龄在原来的基础上加1岁

修改数据库密码

mysql8.0之前

use mysql;

update mysql.user set authentication_string=password("12345") where user="root" and Host="localhost";

flush privileges;刷新MySql的系统权限相关表

mysql8.0

ALTER USER 'root'@'localhost' IDENTIFIED BY '新密码' ;

使用mysqladmin修改

mysqladmin -u root -p password 新密码;

删除操作

DELETE FROM 表名 [where 列名=列值];

TRUNCATE TABLE 表名;

DELETE与TRUNCATE的区别

DELETE 删除表中的数据，表的结构还在

TRUNCATE 删除是把表直接DROP掉，然后再创建一个同样的新表，执行速度比DELETE快

DQL

查询所有列

SELECT * FROM 表名;

结果集

数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端

查询返回的结果集是一张虚拟表

查询指定列的数据

SELECT 列名1，列名2...FROM 表名;

条件查询

条件查询就是在查询时给出WHERE子句，在WHERE子句中可以使用一些运算符及关键字

条件查询运算符及关键字

=（等于）、！=（不等于）、<>(不等于)、<(小于)、<=（小于等于）、>、>=

BETWEEN~AND

IN(固定值);固定的范围值

IS NULL; IS NOT NULL

AND 与

OR 或

NOT 非

使用

查询性别为男，年龄为20的学生记录

查询学号为202208或名字为‘郭靖’的学生记录

查询学号为202203,202204,202206的学生记录

查询身高或体重信息为空的学生记录

查询年龄在20-25之间的女学生记录

查询身高1.6以下和身高1.75以上的学生信息

查询身高不为null，年龄25以下的男学生记录

模糊查询

根据指定的关键进行查询

使用LIKE关键字后跟通配符

通配符

_ : 代表任意一个字符

% : 代表0-n个字符

使用

查询所有姓刘的学生记录

查询名字由三个字组成的学生记录

查询第二个字为'杰'字的学生记录

查询所有名字中带有'杰'字的学生记录

字段控制查询

去除重复记录

SELECT DISTINCT 列名 FROM 表名;

把查询字段的结果进行运算，必须都是数值型

如果未知是否有NULL值，加IFNULL（列名，赋值）

对查询结果起别名

SELECT 列名 AS 别名 FROM 表名;

排序

对查询结果进行排序

使用关键字ORDER BY

排序类型

升序ASC（默认）

降序DESC

使用

查询所有学生，按照身高降序排列

查询所有学生，按照年龄降序排列，如果年龄相同，按照id降序排列

聚合函数

对查询的结果进行统计计算

常用的聚合函数

COUNT()：统计指定列不为NULL的记录行数

MAX()：计算指定列的最大值

MIn()：计算指定列的最小值

SUM()：计算指定列的数值和；如果指定列不是数值型，结果为0

AVG()：计算指定列的平均数；如果指定列不是数值型，结果为0

使用

COUNT

查询student表中有多少条记录数

查询表中年龄在20岁以下人数

查询低于男生平均身高的男生数量

SUM和AVG

查询班级总分

查询班级平均身高和平均年龄

查询所有学生年龄和分数的总和

MAX和MIN

查询班级年龄最小值和身高最高值

分组查询

什么是分组查询

将查询结果按照1个或多个字段进行分组，字段值相同的为一组

分组使用

SELECT gender FROM student group by gender；

根据gender字段来分组，gender字段的全部值只有（'男','女'）,所以分了两组

当group by单独使用时，只能显示每组的第一条数据

所以group by单独使用的实际意义不大

group by+group_concat()

group_concat(字段名)可以作为一个输出字段来使用

表示分组后，根据分组结果，使用group_concat()来放置每一组的某个字段的值的集合

SELECT gender,group_concat(name) from student group by gender;

group by+聚合函数

通过group_concat()的启发，我们可以统计出每个分组的某字段的集合，
那我们也可以通过函数来对这个值的集合进一步操作

使用

查询每个系的系别名称和每个系的总人数

查询每个系的系别名称和每个系的平均身高

查询每个系的系别名称以及每个系年龄大于23的总人数

group by+having

用来分组查询后指定一些条件来输出查询结果

having作用和where一样，但having只能用于group by

查询总分数大于400的学生id以及总分数

having与where的区别

查询语文成绩大于90，总分数大于270的学生学号以及总分数

查询语文成绩大于90

每个学生总分

总分大于450

总分大于450，按照总分降序排列

LIMIT

从哪一行开始，总共查几行

Limit 参数1，参数2

参数1-从哪一行开始

参数2-一共要查几行

下角标从0开始

格式：select * from student limit 0,3;

书写顺序

select-->from-->where-->group by-->having-->order by-->limit

执行顺序

from-->where-->group by-->having-->select-->order by-->limit

数据完整性

什么是数据完整性

保证用户输入的数据保存到数据库中是正确的

如何添加数据完整性

在创建表时给表添加约束

完整性分类

实体完整性

域完整性

参照完整性

实体完整性

什么是实体完整性

表中的一行(一条记录)代表一个实体

实体完整性的作用

标识一行数据不重复。行级约束

约束类型

主键约束(primary key)

唯一约束(unique)

自动增长列(auto_increment)

主键约束

特点

每个表中要有一个主键

数据唯一，且不能为null

添加方式

CREATE TABLE 表名(字段名1 数据类型 primary key，字段2 数据类型)

CREATE TABLE 表名(字段名1 数据类型，字段2 数据类型，primary key(主键字段名))

CREATE TABLE 表名(字段名1 数据类型，字段2 数据类型，primary key(主键1，主键2))

联合主键

两个字段信息完全重复，才会违反主键约束

唯一约束

特点

指定列数据不能重复

可以为空值

格式

CREATE TABLE 表名(字段名1 数据类型，字段2 数据类型 UNIQUE);

自动增长列

特点

指定列的数据自动增长

即使数据删除，还是会从删除的序号继续增长

格式

CREATE TABLE 表名(字段名1 数据类型 primary key auto_increment ，字段2 数据类型 UNIQUE);

域完整性

什么是域完整性

又名列完整性，指列值必须符合某种数据类型或约束

约束类型

数据类型(包括长度，小数点等)

非空约束not null，唯一约束unique

格式

CREATE TABLE 表名(字段名1 数据类型 not null，字段2 数据类型 UNIQUE);

参照完整性

什么是参照完整性

是指表与表之间的一种对应关系，通常情况下可以通过设置两表之间的主键、外键关系，或者编写两表的触发器来实现。

作用

保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。使两张表形成关联，外键只能引用外表中的列的值或使用空值。

建立外键条件

两张表必须都是InnoDB表，并且它们没有临时表。

建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

建立外键关系的对应列必须建立了索引。

语句

建表时加外键

CONSTRAINT 外键名 FOREIGN KEY(从表字段) REFERENCES 主表名(主表字段)

给已存在的表加外键

ALTER TABLE 表名 ADD CONSTRAINT 外键名 FOREIGN KEY(从表字段) REFERENCES 主表名(主表字段)

删除外键

ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

外键对于更新或删除四种约束

CASCADE

从父表中删除或更新对应的行，同时自动的删除或更新自表中匹配的行

SET NULL

从父表中删除或更新对应的行，同时将子表中的外键列设为空

NO ACTION

InnoDB拒绝删除或者更新父表

RESTRICT

拒绝删除或者更新父表

表之间关系

一对一

一张表里的数据对应另一张表里的一条数据

一对多

一张表里的一条数据可以对应另一张表多条数据，例如一个学生可以有多个学科成绩记录；一般在多的一方建立外键

多对多

例如学生选课，一个学生可以选多门课，每门课也对应多个学生；一般会创建一个多对多关系表

多表操作

合并结果集

什么是合并结果集

合并结果集就是把两个select语句的查询结果合并到一起

合并结果集的两种方式

UNION

合并时去除重复记录

UNION ALL

合并时不去除重复记录

格式

SELECT * FROM 表1 UNION SELECT * FROM 表2;

SELECT * FROM 表1 UNION ALL SELECT * FROM 表2;

注意事项

列数，列类型必须相同

多表联查

什么是多表联查

也可以叫跨表查询，需要关联多个表进行查询

什么是笛卡尔集

假设集合A={a,b},集合B={0,1,2}

两个集合的笛卡尔积为{(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)}

同时查询两个表，出现的就是笛卡尔集结果

查询时给表起别名

多表联查，如何保证数据正确

在查询时要把主键和外键保持一致

子表中的数据参照主表中的数据

原理:逐行判断，符合条件的留下

连接查询

内连接

图示

语句

SELECT * FROM table_A INNER JOIN table_B ON A.key=B.key ;

左连接

图示

子主题

语句

SELECT * FROM table_A LEFT JOIN table_B ON A.key=B.key ;

右连接

图示

子主题

语句

SELECT * FROM table_A RIGHT JOIN table_B ON A.key=B.key ;