MySQL数据库知识解析_思维脑图
2021-11-16 23:36:56 1 举报AI智能生成
MySQL数据库知识解析_思维脑图
作者其他创作
大纲/内容
传统记录数据的缺点：不易保存备份困难查找不便
现代化手段——文件对于数据容量较大的数据，不能够很好的满足，而且性能较差不易扩展
关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。
包括MySQL、Oracle、MsSqlserver等等
设计原则：命名规范化数据的一致性和完整性减少数据冗余范式理论（3NF）
优点：复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。事务支持使得对于安全性能很高的数据访问要求得以实现。
关系型数据库
非关系型数据库主要是基于“非关系模型”的数据库（由于关系型太大，所以一般用“非关系型”来表示其他类型的数据库）
类型：span style=\"font-size: inherit;\
优点：性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
非关系型数据库
数据库：持久化存储读写速度极高保证数据的有效性（通过约束条件来保证）对程序的支持性非常好，容易扩展
数据存储
数据列：字段数据行：记录数据表：数据行的集合数据库：数据表的集合单元格：存储的数据ID主键：能够唯一标识某个记录的外键：字段所存储的数据是别的表的主键
理解数据库
简介：MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，后被Sun公司收购，Sun公司后来又被Oracle公司收购，目前属于Oracle旗下产品。
特点：使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性支持多种操作系统，如Linux、Windows、AIX等为多种编程语言提供了API，如C、C++、Python、Java等支持多线程，充分利用CPU资源优化的SQL查询算法，有效提高查询速度提高多语言支持，常见的编码如GB2312、BIG5、UTF8提供TCP/IP、ODBC和JDBC等多种数据库连接途径提供用于管理、检查优化数据库操作的管理工具大型的数据库，可以处理上千万条记录支持多种储存引擎MySQL使用标准的SQL数据语言形式MySQL是可以定制的，采用了GPL协议，你可以修改源代码来开发自己的MySQL系统MySQL软件采用了双授权政策，分为商业版和社区版，由于其体积小、速度快、总体拥有成本低，尤其是开放源代码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库复制全局事务标识复制无崩溃从机复制多线程从机在线DDL更改功能
存数据——快、持久化存储、数据有效性、扩展好
MySQL
1、数据库介绍
分为客户端和服务端，安装的是服务端。然后通过cmd/cmder连接、操作。（充当了MySQL的客户端）都是命令行的方式来操作数据库。（重点）
直接安装（不建议用）下载地址：www.mysql.com/downloadsMySQL社区（GPL）下载 —》适用于Windows的MySQL Installer —》出来俩，下载哪个都可以，大的更全面 —》得到.msi文件安装：https://jingyan.baidu.com/article/0aa223751ed91188cc0d643f.html
集成安装phpstudy——官网下载，安装和QQ很像，可以开启MySQL服务xampp
打开SQLyog连接到我的SQL主机新建-保存的连接-命名即可我的SQL主机地址：127.0.0.1或localhost用户名：root（默认，不修改）密码：root（默认，不修改）端口：3306（默认，不修改）
打开后右键创建数据库基字符集：utf8数据库排序规则：utf8_general_ci
打开数据库右键创建表引擎：InnoDB字符集：utf8核对：utf8_general_ci
一般工作时不会接触到图形化管理工具，原因：安全、权限。所以用于辅助学习
选中语句后按F9执行；注释符号为--
SQLyog
图形化管理工具PHPmyadminNavicatSQLyog——操作数据库
下载地址：http://cmder.net/设置环境变量，CMDER_HOME=cmder.exe所在目录，并在path中增加%CMDER_HOME%。右击我的电脑->属性->（左侧）高级系统设置->（下侧）环境变量->系统变量的新建（变量名是：CMDER_HOME，变量值可以浏览目录找到cmder.exe所在目录）同时编辑用户变量的path，添加%CMDER_HOME%这样可以在运行中输入cmder，打开cmder了
cmder的安装和配置
建议搭配：phpstudy+SQLyog+cmder（比cmd好用）
MySQL的安装和配置
为什么要配置环境变量？https://blog.csdn.net/qq_42707118/article/details/85337235
phpstudy下载安装完成后，设置环境变量需要将MySQL文件夹下的bin文件路径（即。。phpStudy\\PHPTutorial\\MySQL\\bin）添加到环境变量中才能使用cmd/cmder操作。右击我的电脑->属性->（左侧）高级系统设置->（下侧）环境变量->系统变量的新建
2、环境搭建
1、数据库的介绍和环境搭建
结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过SQL操作Oracle，SQL server，MySQL等关系型数据库。
SQL语句主要分为DQL：（data query language）数据查询语言，用于对数据进行查询，如selectDML：（data manipulation language）数据操作语言，对数据进行增加、修改、删除，如insert、update、deleteDDL：（ data definition language）数据定义语言，进行数据库、表的管理等，如create、drop
重点是数据的crud（增删改查），必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作。
SQL
在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束
数据完整性
整数类型（6）：int=integer
定点数（2）：decimal和numeric
浮点数（3）：float，double和real
BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64，默认为1。BIT其实就是存入二进制的值，类似010110。如果存入一个BIT类型的值，位数少于M值，则左补0.
BIT（1）：bit
1.数值类型（12）
char和varchar类型声明的长度表示你想要保存的最大字符数char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '；varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'；
通常情况下varchar更好，存储空间需要的更小
char和varchar类型（2）
binary和varbinary类型类似于char和varchar类型，但是不同的是，它们存储的不是字符字符串，而是二进制串。所以它们没有字符集，并且排序和比较基于列值字节的数值值。
binary和varbinary类型（2）
blob是一个二进制大对象，可以容纳可变数量的数据。有四种blob类型：tinyblob，blob，mediumblob，longblob有四种text类型：tinytext，text，mediumtext，longtext
blob和text与varbinary和varchar的相似点：blob列被视为二进制字符串。text列被视为字符字符串，类似char和binary。在大多数方面，可以将blob列视为能够足够大的varbinary列。同样，可以将text列视为varchar列。当保存或检索blob和text列的值时不删除尾部空格。(这与varbinary和varchar列相同）。不同点：比较时将用空格对text进行扩充以适合比较的对象，正如char和varchar。对于blob和text列的索引，必须指定索引前缀的长度。对于char和varchar，前缀长度是可选的。blob和text列不能有默认值。
blob和text类型（8）
如果输入的不是枚举值，可以插入空字符串\"\"或NULL或第1个元素：如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且默认值为NULL。如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。
enum（1）
set类型（1）
2.字符串类型（14）
date：用'YYYY-MM-DD'格式检索和显示，支持的范围是'1000-01-01'到 '9999-12-31'。datetime：以'YYYY-MM-DD HH:MM:SS'格式检索和显示，支持的范围为'1000-01-01 00:00:00'到'9999-12-31 23:59:59'。timestamp：包含日期和时间，范围从'1970-01-01 00:00:01' UTC 到'2038-01-19 03:14:07' UTC。函数返回的结果，其值适合DATETIME、DATE或者TIMESTAMP上下文，例如NOW()或CURRENT_DATE。
date，datetime，timestamp类型（3）
MySQL以'HH:MM:SS'格式检索和显示time值(或对于大的小时值采用'HHH:MM:SS'格式)。
范围可以从'-838:59:59'到'838:59:59'。小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。
time类型（1）
year类型是一个单字节类型用于表示年。
MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。
year类型（1）
3.日期时间类型（5）
4.几何类型（8）
对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径更全的数据类型参考https://blog.csdn.net/anxpp/article/details/51284106
常见的数据类型
取够用的就行，尽量减小存储空间
数值类型
字符串
日期时间类型
各种数据类型占用的存储
1.SQL介绍&常见的数据类型
主键primary key：物理上存储的顺序，必须非空。只有主键才有自增属性非空not null：此字段不允许null唯一unique：此字段的值不允许重复无符号unsigned：非负数默认default：当不填写时使用默认值（在索引中设置）自增auto_increment：插入新数据时，插入数据时无需设置此列，默认将自增，表中只有一个自增，中间项被删除后仍按原顺序增加外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
2.数据库约束
2、数据类型及约束
数据库的连接——mysql -uroot -p或mysql -u root -proot（不推荐，不安全）连接后可直接看到数据库的版本显示数据库版本——select version();查看所有数据库——show databases;显示时间——show now();退出数据库——exit 或 quit
创建数据库——create database 数据库名 charset=utf8；使用数据库——use 数据库名；查看创建数据库的命令——show create database 数据库名；查看当前使用的数据库——select database();删除数据库——drop database 数据库名；【不可使用！！】
sql语句最后需要有分号；结尾 MySQL不区分大小写
1.数据库操作
修改表-添加字段——alter table 表名 add 列名 类型及约束；修改表-修改字段：不重命名版——alter table 表名 modify 列名 类型及约束；修改表-修改字段：重命名版——alter table 表名 change 原名 新名 类型及约束；修改表-删除字段——alter table 表名 drop 列名；
2.数据表操作
3、数据库与数据表的基本操作
......表示按列名顺序输入信息，数据之间用逗号，隔开，中文加上引号例如向students表中插入一个学生信息insert into students values(font color=\"#0076b3\
全列插入——insert into 表名 values(......)；
枚举类型插入：下标是从1开始的
想要部分插入成功，【非空】且【无默认值】约束的列名必须要有信息输入
新增
语法：update 表名 set 字段1=新值1，字段2=新值2... where 条件;
修改
不加where条件时，该表内的数据将会被全部删掉例如：将students表中id=3的记录删掉delete from students where id=3；
删除的是数据，几乎不这么操作！
物理删除——delete from 表名 where 条件；
由于直接删除无法找回原来的数据记录，故添加字段is_delete 用1 （是） 0（否）表示是否删除；则需要删除一个记录时直接将其is_delete列数据修改为0即可
数据不是被删除了，而是被隐藏了
例如，在students表中新建is_delete字段alter table add is_delete tinyint default 0 comment'0是未删除，1是已删除';将id=4的记录删除update students set is_delete=1 where id=4；查找表students所有被删除的字段select * from students where is_delete=1；
逻辑删除
删除
4、数据的基本操作：增删改查（curd）
查询完整语法：select 去重选项 字段列表 [as 字段别名] from 数据源 [where子句] [group by 子句] [having子句] [order by 子句] [limit子句];查询所有字段查询指定字段使用 as 给字段起别名可以通过 as 给表起别名在select后面列前使用distinct可以消除重复的行
1.基本语法
很少这么做，数据量过大
查询所有列——select * from 表名；
例如，查询students表中所有的不同的名字select distinct name from students；
使用as为查询的结果的列或表指定别名
2.简单查询
语法：select ... from 表名 where 条件；例如，select name from students where id=3;select * from students where name<>'shell';
having 条件表达式：用来分组查询后指定一些条件来输出查询结果having作用和where一样，where作用于原始数据集；having作用于分组后的结果集
基本语法
条件写成类似于18<age<28无意义，不会报错，但按默认没加该条件的情况运行
比较运算符：等于=大于>大于等于>=小于<小于等于<=不等于 !=或<>
比较运算符
逻辑运算符：and交——查询18岁以上的女性select * from students where age>18 and gender='女';or并——查询编号小于4或没被删除的学生select * from students where id<4 or is_delete=0；not取反，补——加括号以确定优先级，括号里的先计算 查询不是18岁女性的学生select * from students where not (age=18 and gender='女'); 查询年龄不是18岁的女性select * from students where not age=18 and gender=2;MySQL数据库中优先级：not>and>or，一般通过加括号的形式解决，且可读性更高。
逻辑运算符
查询姓名以“小”开始的学生信息select * from students where name like '小%';查询姓名有“小”的学生信息select * from students where name like'%小%';查询姓名是两个字的学生信息select * from students where name like'__';查询姓名至少是两个字的学生信息select * from students where name like'__%';
like'......'%表示任意个字符串_表示一个字符串
数据量大时不使用，like的使用效率低。
like
正则表达式：正则表达式通常被用来检索、替换那些符合某个模式(规则)的文本。. 匹配任意单个字符串* 匹配0个或多个前一个得到的字符[] 匹配任意一个[]内的字符，[ab]*可以匹配空字符串、a、b、或由任意个a和b组成的字符串。^ 匹配开头，如^s匹配以s开头的字符串$ 匹配结尾，如s$匹配以s结尾的字符串{n} 匹配前一个字符反复n次
例如查询姓名以周开始的学生信息select * from students where name rlike\"^周.*\";查询姓名以周开始、伦结尾的学生信息select * from students where name rlike\"^周.*伦$\";
用法与like相同，但rlike支持正则表达式
rlike
模糊查询
in表示在一个非连续范围内not in不非连续的范围之内between...and...表示在一个连续的范围内not between...and...表示不在一个连续的范围内
范围查询
查询姓名信息为空的男生信息select * from students where name is null and gender=1；
判空is null判非空is not null注意：不能用=null(占用空间地址的)与\"\
空判断
3.条件查询
count(*)总数 表示计算总行数，括号中写星与列名，结果是相同的max(列)最大值 表示求此列的最大值min(列)最小值 表示求此列的最小值sum(列)求和 表示求此列的和_如果求和字段是varchar 则为0；若字段内容是数字，则相加span style=\"font-size: inherit;\
4.聚合函数
group by的含义：将查询结果按照1个或多个字段进行分组，字段值相同的为一组group by可用于单个字段分组，也可用于多个字段分组
select ...字段1 from 表名 group by 字段；...字段1一般与聚合函数共同使用，且字段1是真正能区分这个组的字段。
语法
group by
group_concat(字段名)可以作为一个输出字段来使用 表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
group by + group_concat()
通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作
group by + 集合函数
group by + having
5.分组
查询年龄在18到26岁之间的男同学，按照年龄从小到大排序select * from students where gender=1 and (age between 18 and 26) order by age;查询年龄在18到34岁之间的女同学，按照身高从高到矮排序select * from students where gender=2 and (age between 18 and 34) order by high desc;
目的：为了方便查看数据，可以对数据进行排序span style=\"font-size: inherit;\
6.排序
目的：获取部分行，当数据量过大时，在一页中查看数据非常麻烦 语法：select * from 表名 limit font color=\"#0076b3\
7.分页
连接查询：当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回。
语法：select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列；
内连接查询：查询的结果为两个表匹配到的数据
左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充优化：以小表驱动大表
将数据表名字互换位置，用left join完成
右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
mysql支持三种类型的连接查询。
8.连接查询
例如，查询最高的男生信息select * from students where gender=1 and high=(select max(high) from students where gender=1);查询出高于平均身高的信息select * from students where high>(select avg(high) from students);
列级子查询查询学生的班级号能够对应的学生信息select * from students where id in(select id from classes);select s.* from students as s left join classes as c on s.cls_id=c.id having c.id is not null;
9.子查询
自关联其实就是连结查询，需要两张表，只不过它的左表（主表）和右表（子表）都是自己。在做自连接查询的时候是自己链接自己，分别给主表和子表取别名，再付加条件执行。
在你填写收货信息的时候，会提示你选择省-市-区，当你选则江西省的后，市选项里不会出现广州市，只会显示江西省下的市，这样一个省级联动如何实现？如以下两种方法。查找广州市下的区也是同样的原理，但是如果想要查找县，乡，甚至是具体到某一个街道，那就需要更多的表，这个查询就变得很长，自关联就是把方法1中的3个表融合为一个表。方法二的优点：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大。
例如，查询广东省下的市 1.现在省表中拿到广东省的provinceid——select * from province where province='广东省'; 2.再去city表中找到provinceid对应的市——select * from city where provinceid='440000';或者 使用子查询select * from city where provinceid =(select provinceid from province where province='广东省');或者 使用内连接selecet * from city as c inner join province as p on p.provinceid=c.provinceid having p.province='广东省';
设计省信息表(province)idprovinceidprovince设计市信息表(city)idcityidctitleprovinceid-与省表中对应设计区信息表(areas)idatitlecityid-与市表中对应
No.1.多个表的连接查询
查询广东省下的市select * from site as s1 inner join site as s2 on s1.id=s2.pid having s1.name='广东省'; 查询广州市下的区selcect * from site as s1 inner join site as s2 on s1.id=s2.pid having s1.name='广州市';
设计表结构(site)id 从中国(1)开始pid 中国(0)/省(1-国id)/市(省id)/区(市id)name 省/市/区的名字--即，省的pid=国的id，市的pid=省的id，区的pid=市的id，区的id自增
No.2.一个表的自关联
10.自关联
5、MySQL查询
MySql外键必须使用存储引擎为innodb因程序很难100%保证数据的完整性，而外键在数据库服务器当机或者出现其他的问题时，能够最大限度的保证数据的一致性和完整性。设置外键约束的两个表之间会具有父子关系，即子表中外键的字段的取值范围由父表所决定设置外键一定程度上降低数据库的速度子表的外键字段的数据类型和父表要一致
外键的特点
语法：alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名);
例如，将students表中的cls_id与class表中的id相关联alter table students add constraint fk_cls_id foreign key(cls_id) references class(id);
添加外键约束
接上↑若想将class表中的信息删除，若id已使用则会因为students的外键关系而不被允许。
取消students表中的cls_id与class表中的id的关联关系alter table students drop foreign key fk_cls_id;
语法：alter table 表名 drop foreign key 外键名;
删除外键约束
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。
一个表可以有多个外键对子表students（外键所在的表）的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败对父表class的作用：对父表的主键字段进行删或改时，如果对应的主键在字表中被应用，操作会失败。
总结
语法：alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名) on delete 约束模式;
外键的定制作用：三种约束模式
6、MySQL外键
实际工作中不是用cmder，而是用python代码充当客户端
创建数据表
插入数据
1、数据准备
创建“商品分类”表
--通过goods_cates数据表来更新goods表update goods as g inner join goods_cates as c on g.cate_name=c.name set g.cate_name=c.id;这种情况下与外键有所不同，后期goods_cates表中的id的变化不会同步至goods表中。即再去改变goods_cates表中的id，goods表中的cate_name不会改变。update 表1 inner join 表2 on 条件 set xxx=xxx；
同步表数据
--将goods表中的cate_name与goods_cates表中的id相关联alter table goods modify cate_name int unsigned not null;alter table goods add constraint fk_cate_name foreign key(cate_name) references goods_cates(id);
建立外键
2、数据表的拆分
为了使python连接上数据库，你需要一个驱动，这个驱动是用于与数据库交互的库。pymqsql是一个使Python连接到MySQL的库，它是一个纯Python库。python是充当客户端的。
什么是PyMySQL
PyMySQL支持的python版本：python2python2.7python3python version >=3.4
环境要求
打开CMD/cmder窗口（命令提示符），运行以下其中一行语句：Python2.7 和 Python3： pip install PyMySQLPython2.7： pip2.7 install PyMySQLPython3： pip3 install PyMySQLpython2： pip install MySQLdb查看PyMySQL版本信息： pip3 show PyMySQL
在windows操作系统上安装
https://www.jianshu.com/p/d84cdb5e6273
Ubantu安装
PyMySQL的安装
安装PyMySQL
打开PyCharm，新建文件——选中文件夹，右键 -new -Python file 再输入名字即可导入数据库—— from pymysql import * 或者 import pymysql
1、开始
用于建立与数据库的连接 。创建对象：调用connect()方法
conn=connect（参数列表）参数host：连接的mysql主机，如果本机是'localhost'或'127.0.0.1'参数port：连接的mysql主机的端口，默认是3306参数database：使用的数据库的名称参数user：连接的用户名参数password：连接的密码参数charset：通信采用的编码方式，推荐使用utf8**使用变量conn来接收返回值，随便用abc也可以
connection对象方法
2、创建connection
cs=conn.cursor()用变量cs接收返回值
获取cursor对象：调用connection对象的cursor()方法
查询结果放在电脑内存中，所以不要一次性返回太多数据，会占用内存。
cursor对象方法
3、获取cursor
执行查询执行命令获取数据处理数据
异常处理？
4、SQL操作
cs.close()
5、关闭cursor
conn.close()
6、关闭connection
7、结束
Python操作MySQL步骤
3、Python操作MySQL
7、MySQL和Python交互
1、MySQL-封装DB类
8、Python操作MySQL
数据库MySQL
0 条评论
回复 删除
下一页
