首页 思维导图 详情

MySQL数据库知识解析_思维脑图

2021-11-16 23:36:56 1 举报
AI智能生成
MySQL数据库知识解析_思维脑图
MySQL优化
技术知识体系
计算机类
程序员
数据库
作者其他创作
大纲/内容
1、数据库的介绍和环境搭建
1、数据库介绍
数据存储
传统记录数据的缺点：
  • 不易保存
  • 备份困难
  • 查找不便
现代化手段——文件
  • 对于数据容量较大的数据，不能够很好的满足，而且性能较差
  • 不易扩展
数据库：
  • 持久化存储
  • 读写速度极高
  • 保证数据的有效性（通过约束条件来保证）
  • 对程序的支持性非常好，容易扩展
关系型数据库
关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。
包括MySQL、Oracle、MsSqlserver等等
设计原则：
  1. 命名规范化
  2. 数据的一致性和完整性
  3. 减少数据冗余
  4. 范式理论（3NF）
优点：
  1. 复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
  2. 事务支持使得对于安全性能很高的数据访问要求得以实现。
非关系型数据库
非关系型数据库主要是基于“非关系模型”的数据库（由于关系型太大，所以一般用“非关系型”来表示其他类型的数据库）
类型：
  • 列模型：存储的数据是一列列的。关系型数据库以一行作为一个记录，列模型数据库以一列为一个记录。（这种模型，数据即索引，IO很快，主要是一些分布式数据库）——Hbase
  • 键值对模型：存储的数据是一个个“键值对”，比如name:liming,那么name这个键里面存的值就是liming——redis,MemcacheDB
  • 文档类模型：以一个个文档来存储数据，有点类似“键值对”。——mongoDB
优点：
  1. 性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
  2. 可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。
理解数据库
  • 数据列：字段
  • 数据行：记录
  • 数据表：数据行的集合
  • 数据库：数据表的集合
  • 单元格：存储的数据
  • ID主键：能够唯一标识某个记录的
  • 外键：字段所存储的数据是别的表的主键
MySQL
简介：
MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，后被Sun公司收购，Sun公司后来又被Oracle公司收购，目前属于Oracle旗下产品。
特点：
  1. 使用C和C++编写，并使用了多种编译器进行测试，保证源代码的可移植性
  2. 支持多种操作系统，如Linux、Windows、AIX等
  3. 为多种编程语言提供了API，如C、C++、Python、Java等
  4. 支持多线程，充分利用CPU资源
  5. 优化的SQL查询算法，有效提高查询速度
  6. 提高多语言支持，常见的编码如GB2312、BIG5、UTF8
  7. 提供TCP/IP、ODBC和JDBC等多种数据库连接途径
  8. 提供用于管理、检查优化数据库操作的管理工具
  9. 大型的数据库，可以处理上千万条记录
  10. 支持多种储存引擎
  11. MySQL使用标准的SQL数据语言形式
  12. MySQL是可以定制的，采用了GPL协议，你可以修改源代码来开发自己的MySQL系统
  13. MySQL软件采用了双授权政策，分为商业版和社区版，由于其体积小、速度快、总体拥有成本低，尤其是开放源代码这一特点，一般中小型网站的开发都选择MySQL作为网站数据库
  14. 复制全局事务标识
  15. 复制无崩溃从机
  16. 复制多线程从机
  17. 在线DDL更改功能
存数据——快、持久化存储、数据有效性、扩展好
2、环境搭建
MySQL的安装和配置
分为客户端和服务端，安装的是服务端。
然后通过cmd/cmder连接、操作。（充当了MySQL的客户端）
都是命令行的方式来操作数据库。（重点）
直接安装（不建议用）
  • 下载地址：www.mysql.com/downloads
    MySQL社区（GPL）下载  —》适用于Windows的MySQL Installer  —》出来俩，下载哪个都可以，大的更全面  —》得到.msi文件
  • 安装：https://jingyan.baidu.com/article/0aa223751ed91188cc0d643f.html
集成安装
  • phpstudy——官网下载，安装和QQ很像，可以开启MySQL服务
  • xampp
图形化管理工具
  1. PHPmyadmin
  2. Navicat
  3. SQLyog——操作数据库
SQLyog
  • 打开SQLyog
连接到我的SQL主机
新建-保存的连接-命名即可
我的SQL主机地址：127.0.0.1或localhost
用户名：root（默认，不修改）
密码：root（默认，不修改）
端口：3306（默认，不修改）
  • 打开后右键创建数据库
基字符集：utf8
数据库排序规则：utf8_general_ci
  • 打开数据库右键创建表
引擎：InnoDB
字符集：utf8
核对：utf8_general_ci
一般工作时不会接触到图形化管理工具，原因：安全、权限。所以用于辅助学习
选中语句后按F9执行；注释符号为--
建议搭配：phpstudy+SQLyog+cmder（比cmd好用）
cmder的安装和配置
  • 下载地址：http://cmder.net/
  • 设置环境变量，CMDER_HOME=cmder.exe所在目录，并在path中增加%CMDER_HOME%。
    右击我的电脑->属性->（左侧）高级系统设置->（下侧）环境变量->系统变量的新建（变量名是：CMDER_HOME，变量值可以浏览目录找到cmder.exe所在目录）
    同时编辑用户变量的path，添加%CMDER_HOME%
  • 这样可以在运行中输入cmder，打开cmder了
phpstudy下载安装完成后，设置环境变量
需要将MySQL文件夹下的bin文件路径（即。。phpStudy\PHPTutorial\MySQL\bin）添加到环境变量中才能使用cmd/cmder操作。
右击我的电脑->属性->（左侧）高级系统设置->（下侧）环境变量->系统变量的新建
为什么要配置环境变量？
https://blog.csdn.net/qq_42707118/article/details/85337235
2、数据类型及约束
1.SQL介绍&常见的数据类型
SQL
结构化查询语言(Structured Query Language)简称SQL，是一种特殊目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。
当前关系型数据库都支持使用SQL语言进行操作，也就是说可以通过SQL操作Oracle，SQL server，MySQL等关系型数据库。
SQL语句主要分为
  • DQL：（data query language）数据查询语言，用于对数据进行查询，如select
  • DML：（data manipulation language）数据操作语言，对数据进行增加、修改、删除，如insert、update、delete
  • DDL：（ data definition language）数据定义语言，进行数据库、表的管理等，如create、drop
重点是数据的crud（增删改查），必须熟练编写DQL、DML，能够编写DDL完成数据库、表的操作。
数据完整性
在表中为了更加准确的存储数据，保证数据的正确有效，可以在创建表的时候，为表添加一些强制性的验证，包括数据字段的类型、约束
常见的数据类型
1.数值类型（12）
整数类型（6）：int=integer
tinyint, smallint, mediumint, int/integer, bigint
定点数（2）：decimal和numeric
  • decimal(M,D)其中M表示十进制数字总的个数，D表示小数点后面数字的位数
    如decimal(5,2)表示共存5位数，小数占2位，即XXX.XX
浮点数（3）：float，double和real
BIT（1）：bit
  • BIT数据类型可用来保存位字段值。BIT(M)类型允许存储M位值。M范围为1~64，默认为1。
    BIT其实就是存入二进制的值，类似010110。
    如果存入一个BIT类型的值，位数少于M值，则左补0.
2.字符串类型（14）
char和varchar类型（2）
char和varchar类型声明的长度表示你想要保存的最大字符数
  • char表示固定长度的字符串，如char(3)，如果填充'ab'时会补一个空格为'ab '；
  • varchar表示可变长度的字符串，如varchar(3)，填充'ab'时就会存储'ab'；
通常情况下varchar更好，存储空间需要的更小
binary和varbinary类型（2）
binary和varbinary类型类似于char和varchar类型，但是不同的是，它们存储的不是字符字符串，而是二进制串。所以它们没有字符集，并且排序和比较基于列值字节的数值值。
blob和text类型（8）
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列不能有默认值。
enum（1）
enum是一个字符串对象，其值来自表创建时在列规定中显式枚举的一列值。
   如size enum('x-small','small',medium','large','x-large')
如果输入的不是枚举值，可以插入空字符串""或NULL或第1个元素：
  • 如果你将一个非法值插入ENUM(也就是说，允许的值列之外的字符串)，将插入空字符串以作为特殊错误值。该字符串与“普通”空字符串不同，该字符串有数值值0。
  • 如果将ENUM列声明为允许NULL，NULL值则为该列的一个有效值，并且默认值为NULL。
  • 如果ENUM列被声明为NOT NULL，其默认值为允许的值列的第1个元素。
set类型（1）
set是一个字符串对象，可以有零或多个值，其值来自表创建时规定的允许的一列值。
   
例如，指定为set('one', 'two') NOT NULL的列可以有下面的任何值：
  • ' '
  • 'one'
  • 'two'
  • 'one,two'
SET值按数字顺序排序。NULL值排在非NULL SET值的前面。
3.日期时间类型（5）
date，datetime，timestamp类型（3）
  • 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。
time类型（1）
  MySQL以'HH:MM:SS'格式检索和显示time值(或对于大的小时值采用'HHH:MM:SS'格式)。
  • 范围可以从'-838:59:59'到'838:59:59'。
小时部分会因此大的原因是TIME类型不仅可以用于表示一天的时间(必须小于24小时)，还可能为某个事件过去的时间或两个事件之间的时间间隔(可以大于24小时，或者甚至为负)。
year类型（1）
year类型是一个单字节类型用于表示年。
MySQL以YYYY格式检索和显示YEAR值。范围是1901到2155。
4.几何类型（8）
对于图片、音频、视频等文件，不存储在数据库中，而是上传到某个服务器上，然后在表中存储这个文件的保存路径
更全的数据类型参考https://blog.csdn.net/anxpp/article/details/51284106
各种数据类型占用的存储
数值类型
取够用的就行，尽量减小存储空间
字符串
日期时间类型
2.数据库约束
  • 主键primary key：物理上存储的顺序，必须非空。只有主键才有自增属性
  • 非空not null：此字段不允许null
  • 唯一unique：此字段的值不允许重复
  • 无符号unsigned：非负数
  • 默认default：当不填写时使用默认值（在索引中设置）
  • 自增auto_increment：插入新数据时，插入数据时无需设置此列，默认将自增，表中只有一个自增，中间项被删除后仍按原顺序增加
  • 外键foreign key：对关系字段进行约束，当为关系字段填写值时，会到关联的表中查询此值是否存在，如果存在则填写成功，如果不存在则填写失败并抛出异常
3、数据库与数据表的基本操作
1.数据库操作
  1. 数据库的连接——mysql -uroot -p或mysql -u root -proot（不推荐，不安全）连接后可直接看到数据库的版本
  2. 显示数据库版本——select version();
  3. 查看所有数据库——show databases;
  4. 显示时间——show now();
  5. 退出数据库——exit 或 quit
  1. 创建数据库——create database 数据库名 charset=utf8；
  2. 使用数据库——use 数据库名；
  3. 查看创建数据库的命令——show create database 数据库名；
  4. 查看当前使用的数据库——select database();
  5. 删除数据库——drop database 数据库名；【不可使用！！】
sql语句最后需要有分号；结尾    MySQL不区分大小写
2.数据表操作
  1. 查看当前数据库中所有表——show tables;
  2. 创建表——create table 数据表名 (字段 类型 约束 [ ,字段 类型 约束]); 
  3. 查看表结构——desc 数据库表名；
  4. 查看表的创建语句——show create table 表名字；
  5. 删除表——drop table 表名；【不可使用！！】
  6. 退出表——表不用退出，因为没有use,同一个库中的table不能同名
创建students表（id，name，age，high，gender，cls_id）
create table students(
id int primary key not null auto_increment,
name varchar(30),
age tinyint unsigned default18,
high decimal(5,2),
gender enum('男','女') default '保密',
cls_id tinyint
);
  • 修改表-添加字段——alter table 表名 add 列名 类型及约束；
  • 修改表-修改字段：不重命名版——alter table 表名 modify 列名 类型及约束；
  • 修改表-修改字段：重命名版——alter table 表名 change 原名 新名 类型及约束；
  • 修改表-删除字段——alter table 表名 drop 列名；
4、数据的基本操作：增删改查（curd）
新增
全列插入——insert into 表名 values(......)；
......表示按列名顺序输入信息，数据之间用逗号，隔开，中文加上引号
例如
  • 向students表中插入一个学生信息insert into students values(0,'shell',20,181,'男',2)；
主键字段用0/null/default来占位
--若插入的是别的函数得到的结果集，则不需要使用values()
insert into 表名(列名)【结果集】；
insert into 表名(字段) select 字段名 from 表名 ......;
枚举类型插入：下标是从1开始的
比如
  • 说向students表中插入一个学生信息insert into students values(null,'shell',20,181,1,2)；
此时gender的插入没有用'男'，而是用了数字1
部分插入——insert into 表名(列1,......) values(值1,......);
例如：
insert into students(`name`,`gender`) values('shell',1)
注意前面列名用的是````,后面数据信息中的字符串用的是''''，``的作用是防止转译。
想要部分插入成功，【非空】且【无默认值】约束的列名必须要有信息输入
多行插入——insert into 表名 values(......),(......),(......);
例如：
insert into students values(0,'shell',20,181,'男',2),(0,'shell',20,181,1,2),(0,'666',20,181,'男',2);
修改
语法：update 表名 set 字段1=新值1，字段2=新值2... where 条件;
不加where条件时，全部都会发生改变！
例如：
  • 将名字为shell的改为python
update students set name='python' where name='shell';
  • 将id=3的姓名及年龄做修改
update students set name='none',age=19 where id=3;
删除
物理删除——delete from 表名 where 条件；
不加where条件时，该表内的数据将会被全部删掉
例如：
  • 将students表中id=3的记录删掉delete from students where id=3；
删除的是数据，几乎不这么操作！
逻辑删除
由于直接删除无法找回原来的数据记录，故添加字段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；
5、MySQL查询
1.基本语法
  • 查询完整语法：
select 去重选项 字段列表 [as 字段别名] from 数据源 [where子句] [group by 子句] [having子句] [order by 子句] [limit子句];

  1. 查询所有字段
  2. 查询指定字段
  3. 使用 as 给字段起别名
  4. 可以通过 as 给表起别名
  5. 在select后面列前使用distinct可以消除重复的行
2.简单查询
查询所有列——select * from 表名；
很少这么做，数据量过大
查询指定列——select 列1,列2,... from 表名;
例如，select name,age,gender from students
去重复字段的查询——select distinct 列1,列2... from 表名；
例如，
  • 查询students表中所有的不同的名字select distinct name from students；
distinct去重，当查询多列时，只有当所查找的列名1,列名2....内容都相同才会只显示一条（即整个查询的行是否重复）
使用as为查询的结果的列或表指定别名
当一些字段名称或表名称太长或过于复杂可以为其起别名，一般两个表联合查询时才会给表取别名。
例如，将查询的结果中name显示为姓名
select name as 姓名,age,gender from students where name='shell';（条件中依旧是原名称 name）
3.条件查询
基本语法
语法：
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，一般通过加括号的形式解决，且可读性更高。
模糊查询
like 
like'......'
%表示任意个字符串
_表示一个字符串
  • 查询姓名以“小”开始的学生信息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的使用效率低。
rlike
用法与like相同，但rlike支持正则表达式
正则表达式：正则表达式通常被用来检索、替换那些符合某个模式(规则)的文本。
.     匹配任意单个字符串
*    匹配0个或多个前一个得到的字符
[]    匹配任意一个[]内的字符，[ab]*可以匹配空字符串、a、b、或由任意个a和b组成的字符串。
^    匹配开头，如^s匹配以s开头的字符串
$    匹配结尾，如s$匹配以s结尾的字符串
{n}   匹配前一个字符反复n次
例如
查询姓名以周开始的学生信息select * from students where name rlike"^周.*";
查询姓名以周开始、伦结尾的学生信息select * from students where name rlike"^周.*伦$";
范围查询
  • in表示在一个非连续范围内
  • not in不非连续的范围之内
  • between...and...表示在一个连续的范围内
  • not between...and...表示不在一个连续的范围内
  • 查询年龄为18、34的姓名select name,age from students where age in(18,34);
  • 查询姓名不是shell、python的学生信息 select * from students where name not in('shell','python');
  • 查询id是2到5的男生信息select * from students where id between 2 and 5 and gender='男';
（超出边界不会报错，会返回当前到信息的终点）
  • 查询年龄不在18到34的男生信息select * from students where (age not between 18 and 34) and gender=1;
空判断
  • 判空is null
  • 判非空is not null
注意：
  1. 不能用=
  2. null(占用空间地址的)与""(空字符串,不占空间地址的)是不同的
查询姓名信息为空的男生信息select * from students where name is null and gender=1；
4.聚合函数
  1. count(*)总数  表示计算总行数，括号中写星与列名，结果是相同的
  2. max(列)最大值  表示求此列的最大值
  3. min(列)最小值  表示求此列的最小值
  4. sum(列)求和  表示求此列的和_如果求和字段是varchar 则为0；若字段内容是数字，则相加
  5. avg(列)平均值  表示求此列的平均值_默认保留四位小数
    round（123.45,1）四舍五入，保留一位小数
  • 查询学生总数select count(*) as 总人数 from students;
    查询男生有多少人select count(*) as 男生人数 from students where gender=1;
  • 查询女生最大的年龄select max(age) from students where gender=2;
    查询最大年龄的女生姓名 select id from students where max(age) and gender=2;
    需要用到子查询select name from students where gender=2 and age=(select max(age) from students where gender=2);
  • 查询未删除学生的最小编号 select min(id) from students where is_delete=0;
  • 查询男生的年龄和select sum(age) from students where gender=1;
  • 查询未删除女生年龄的平均值select avg(age) from students where gender=2 and is_delete=0;
  • 计算平均年龄，保留两位小数select round(avg(age),2) from students;或者select round(sum(age)/count(*),2) from students;
5.分组
group by
group by的含义：将查询结果按照1个或多个字段进行分组，字段值相同的为一组
group by可用于单个字段分组，也可用于多个字段分组
语法
select ...字段1 from 表名 group by 字段；
...字段1一般与聚合函数共同使用，且字段1是真正能区分这个组的字段。
例如：
  • 按照性别分组，查询所有性别select gender from students group by gender；
  • 计算男生和女生的人数select gender as 性别,count(*) from students group by gender;
  • 男女同学的最大年龄select gender as 性别,max(age) from students group by gender;
group by + group_concat()
group_concat(字段名)可以作为一个输出字段来使用 
表示分组之后，根据分组结果，使用group_concat()来放置每一组的某字段的值的集合
  • 查询同性别学生的姓名select gender as 性别,group_concat(name) from students group by gender;
  • 查询同性别学生的姓名,年龄select gender as 性别,group_concat('姓名',name,'_','年龄',age) from students group by gender; 
'姓名','_','年龄'目的是间隔开两个字段的信息或加以标识，用别的符号/蚊子等也可，字段前后的可以加。（相当于python中的字符串拼接）
group by + 集合函数
通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作
group by + having
  • 查询人数大于5的性别的人数select gender,count(*) from students group by gender having count(*)>5;
  • 查询人数大于5的性别的人姓名select gender,count(*),group_concat(name) from students group by gender having count(*)>5;
  • 查询平均年龄超过18岁的性别及姓名select gender,group_concat(name),avg(age) from students group by gender having avg(age)>18;
6.排序
目的：为了方便查看数据，可以对数据进行排序
语法：select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...];
说明：
  1. 将行数据按照列1进行排序，如果某些行列1的值相同时，则按照列2排序，以此类推
  2. 默认按照列值从小到大排列（asc）
  3. asc从小到大排列，即升序
  4. desc从大到小排序，即降序
  • 查询年龄在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;
order by 多个字段
例如，
查询年龄在18到28岁之间的女性，年龄从大到小排序，如果年龄相同的情况下按照身高从小到大排序
select * from students where gender=2 and (age between 18 and 28) order by age desc,high;
按照年龄从小到大，身高从低到高排序
select * from students order by age asc,high asc;
7.分页
目的：获取部分行，当数据量过大时，在一页中查看数据非常麻烦 
语法：select * from 表名 limit start,count；
说明：
  1. 从start开始(从0开始)，获取count条数据【起始位置（从0开始），数据个数】
  2. start,count不能写数学公式
  3. limit 只能写到SQL语句最后，只限制显示的条数
查询前5个数据select * from students limit5;
查询id 6-10（包含）的数据select * from students limit 5,5;
制作分页——limit (第N页-1)*每页显示的个数，每页显示的个数
每页显示2个，第1个页面select * from students limit 0,2;
每页显示2个，第2个页面select * from students limit 2,2;
每页显示2个，第3个页面select * from students limit 4,2;
每页显示2个，第4个页面select * from students limit 6,2;
8.连接查询
连接查询：当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集，再选择合适的列返回。
语法：select * from 表1 inner或left或right join 表2 on 表1.列 = 表2.列；
mysql支持三种类型的连接查询。
内连接查询：查询的结果为两个表匹配到的数据
例如，
  • 查询有能够对应班级的学生以及班级信息(含classes表中的id等)
select * from students inner join classes on students.cls_id=classes.id;
  • 查询有能够对应班级的学生以及班级信息，显示学生的所有信息，只显示班级名称(不含classes表中的id等) 
select s.*,c.name from students as s inner join classes as c on s.cls_id=c.id;
  • 查询有能够对应班级的学生的姓名班级
select students.name,classes.name from students inner join classes on students.cls_id=classes.id;
  --给数据表重命名select s.name,c.name from students as s inner join classes as c on s.cls_id=c.id;
  • 查询有能够对应班级的学生以及班级信息，并按班级进行排序
select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name;
  --接上，当同一个班级时，按照id从小到大排序
     select c.name,s.* from students as s inner join classes as c on s.cls_id=c.id order by c.name,s.id asc;
左连接查询：查询的结果为两个表匹配到的数据，左表特有的数据，对于右表中不存在的数据使用null填充
优化：以小表驱动大表
查询每位学生对应的班级信息
select students.name,classes.name from students left join classes on students.cls_id=classes.id;
查询没有对应班级信息的学生信息，并按id排序
select * from students as s left join classes as c on s.cls_id=c.id having c.id is null order by s.id;
右连接查询：查询的结果为两个表匹配到的数据，右表特有的数据，对于左表中不存在的数据使用null填充
将数据表名字互换位置，用left join完成
9.子查询
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句。
例如，
  • 查询最高的男生信息
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;
10.自关联
自关联其实就是连结查询，需要两张表，只不过它的左表（主表）和右表（子表）都是自己。在做自连接查询的时候是自己链接自己，分别给主表和子表取别名，再付加条件执行。
在你填写收货信息的时候，会提示你选择省-市-区，当你选则江西省的后，市选项里不会出现广州市，只会显示江西省下的市，这样一个省级联动如何实现？
如以下两种方法。
查找广州市下的区也是同样的原理，但是如果想要查找县，乡，甚至是具体到某一个街道，那就需要更多的表，这个查询就变得很长，自关联就是把方法1中的3个表融合为一个表。
方法二的优点：存储的都是地区信息，而且每种信息的数据量有限，没必要增加一个新表，或者将来还要存储区、乡镇信息，都增加新表的开销太大。
No.1.多个表的连接查询
  • 设计省信息表(province)
id
provinceid
province
  • 设计市信息表(city)
id
cityid
ctitle
provinceid-与省表中对应
  • 设计区信息表(areas)
id
atitle
cityid-与市表中对应
例如，
查询广东省下的市
     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='广东省';
No.2.一个表的自关联
  • 设计表结构(site)
id 从中国(1)开始
pid 中国(0)/省(1-国id)/市(省id)/区(市id)
name 省/市/区的名字
--即，省的pid=国的id，市的pid=省的id，区的pid=市的id，区的id自增
查询广东省下的市
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='广州市';
6、MySQL外键
外键的特点
  1. MySql外键必须使用存储引擎为innodb
  2. 因程序很难100%保证数据的完整性，而外键在数据库服务器当机或者出现其他的问题时，能够最大限度的保证数据的一致性和完整性。
  3. 设置外键约束的两个表之间会具有父子关系，即子表中外键的字段的取值范围由父表所决定
  4. 设置外键一定程度上降低数据库的速度
  5. 子表的外键字段的数据类型和父表要一致
添加外键约束
语法：
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只有1,2,3 则students表中的cls_id插入其他数据（比如4）【 insert into students values(0,'orange',18,156,'男',4,0)；】会失败，因为字段cls_id不允许存在4，若想成功插入，需要先给class表中加入id为4的数据。【 insert into class values(4,'四班'); 】
  • --当class表中的id有1,2,3 students表中的cls_id有1,2 那么class表中的id1,2不允许修改，而3可以修改。
删除外键约束
接上↑
若想将class表中的信息删除，若id已使用则会因为students的外键关系而不被允许。
语法：
alter table 表名 drop foreign key 外键名;
  • 取消students表中的cls_id与class表中的id的关联关系
alter table students drop foreign key fk_cls_id;
总结
  1. 一个表可以有多个外键
  2. 对子表students（外键所在的表）的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败
  3. 对父表class的作用：对父表的主键字段进行删或改时，如果对应的主键在字表中被应用，操作会失败。
主表（父表）：对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表。
从表（子表）：对于两个具有关联关系的表而言，相关联字段中外键所在的表就是从表。
外键的定制作用：三种约束模式
语法：
alter table 表名 add constraint 外键名字 foreign key(外键字段名) references 外表表名(主键字段名) on delete 约束模式;
  • district：严格模式(默认), 父表不能删除或更新一个被子表引用的记录。
  • cascade：级联模式, 父表操作后，子表关联的数据也跟着一起操作。
  • set null：置空模式,前提外键字段允许为NULL, 父表操作后，子表对应的字段被置空。
7、MySQL和Python交互
1、数据准备
实际工作中不是用cmder，而是用python代码充当客户端
创建数据表
--创建“京东”数据库
create database jd charset=utf8;
--使用“京东”数据库
use jd;
--创建一个商品goods数据表
create table goods(
id int unsigned primary key auto_increment not null,
name varchar(150) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10,3) not null default 0,
is_show tinyint not null default 1,
is_saleoff tinyint not null default 0);
插入数据
--向goods表中插入数据
insert into goods values(0,'r510vc 15.6英寸笔记本','笔记本','华硕','3399',default,default);
insert into goods values(0,'y400n 14.0英寸笔记本电脑','笔记本','联想','4999',default,default);
insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);
insert into goods values(0,'x550cc 15.6英寸笔记本','笔记本','华硕','2799',default,default);
insert into goods values(0,'x240 超极本','超级本','联想','4880',default,default);
insert into goods values(0,'u330p 13.3英寸超极本','超级本','联想','4299',default,default);
insert into goods values(0,'svp13226scb 触控超极本','超级本','索尼','7999',default,default);
insert into goods values(0,'ipad mini 7.9英寸平板电脑','平板电脑','苹果','1998',default,default);
insert into goods values(0,'ipad air 9.7英寸平板电脑','平板电脑','苹果','3388',default,default);
insert into goods values(0,'ipad mini 配备 retina 显示屏','平板电脑','苹果','2788',default,default);
insert into goods values(0,'ideacentre c340 20英寸一体电脑 ','台式机','联想','3499',default,default);
insert into goods values(0,'vostro 3800-r1206 台式电脑','台式机','戴尔','2899',default,default);
insert into goods values(0,'imac me086ch/a 21.5英寸一体电脑','台式机','苹果','9188',default,default);
insert into goods values(0,'at7-7414lp 台式电脑 linux ）','台式机','宏碁','3699',default,default);
insert into goods values(0,'z220sff f4f06pa工作站','服务器/工作站','惠普','4288',default,default);
insert into goods values(0,'poweredge ii服务器','服务器/工作站','戴尔','5388',default,default);
insert into goods values(0,'mac pro专业级台式电脑','服务器/工作站','苹果','28888',default,default);
insert into goods values(0,'hmz-t3w 头戴显示设备','笔记本配件','索尼','6999',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
insert into goods values(0,'x3250 m4机架式服务器','服务器/工作站','ibm','6888',default,default);
insert into goods values(0,'商务双肩背包','笔记本配件','索尼','99',default,default);
2、数据表的拆分
创建“商品分类”表
--创建“goods_cates”表
create table goods_cates(
id int unsigned primary key auto_increment not null,
name varchar(40) not null);
--查询goods表中的商品种类
select cate_name from goods group by cate_name;
--将分组结果写入“goods_cates”数据表
insert into goods_cates(name) select cate_name from goods group by cate_name;--注意，此处没有使用values()
insert into 表名(字段) select 字段名 from 表名 ......;
同步表数据
--通过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);
3、Python操作MySQL
安装PyMySQL
什么是PyMySQL
为了使python连接上数据库，你需要一个驱动，这个驱动是用于与数据库交互的库。
pymqsql是一个使Python连接到MySQL的库，它是一个纯Python库。
python是充当客户端的。
环境要求
PyMySQL支持的python版本：
  • python2
  • python2.7
  • python3
  • python version >=3.4
PyMySQL的安装
在windows操作系统上安装
打开CMD/cmder窗口（命令提示符），运行以下其中一行语句：
Python2.7 和 Python3： pip install PyMySQL
Python2.7： pip2.7 install PyMySQL
Python3： pip3 install PyMySQL
python2： pip install MySQLdb
查看PyMySQL版本信息： pip3 show PyMySQL
Ubantu安装
https://www.jianshu.com/p/d84cdb5e6273
Python操作MySQL步骤
1、开始
  • 打开PyCharm，新建文件——选中文件夹，右键 -new  -Python file  再输入名字即可
  • 导入数据库—— from pymysql import *  或者 import pymysql
2、创建connection
用于建立与数据库的连接 。
创建对象：调用connect()方法
conn=connect（参数列表）
参数host：连接的mysql主机，如果本机是'localhost'或'127.0.0.1'
参数port：连接的mysql主机的端口，默认是3306
参数database：使用的数据库的名称
参数user：连接的用户名
参数password：连接的密码
参数charset：通信采用的编码方式，推荐使用utf8
**使用变量conn来接收返回值，随便用abc也可以
import pymysql
conn = pymysql.connect(host = 'localhost',port=3306,database='demo1',user='root',password = 'root',charset = 'utf8')
from pymysql import *
conn = connect(host = 'localhost',port=3306,database='demo1',user='root',password = 'root',charset = 'utf8')
connection对象方法
close()关闭连接
commit()提交
cursor()返回Cursor对象,用于执行sql语句并获得结果
3、获取cursor
用于执行sql语句,使用频度最高的语句为select、insert、update、delete.
获取cursor对象：调用connection对象的cursor()方法
cs=conn.cursor()
用变量cs接收返回值
cursor对象方法
close()关闭 先关闭游标,在关闭链接
execute(operation [, parameters ])执行语句，返回受影响的行数，主要用于执行insert、update、delete语句，也可以执行create、alter、drop等语句
fetchone()执行查询语句时，获取查询结果集的第一个行数据，返回一个元组
fetchmany(n)执行查询语句时，获取查询结果集的n行数据，一行构成一个元组，再将这些元组装入一个元组返回
fetchall()执行查询时，获取结果集的所有行，一行构成一个元组，再将这些元组装入一个元组返回
查询结果放在电脑内存中，所以不要一次性返回太多数据，会占用内存。
4、SQL操作
执行查询
执行命令
获取数据
处理数据
异常处理？
5、关闭cursor
cs.close()
6、关闭connection
conn.close()
7、结束
8、Python操作MySQL
1、MySQL-封装DB类
评论
0 条评论
下一页
为你推荐
查看更多
MySQL
MySQL
数据库设计
数据库设计
数据库(MySQL)
数据库(MySQL)
Mysql面试知识点_数据库
Mysql面试知识点_数据库
mysql数据库项目式教程
mysql数据库项目式教程
Mysql数据库知识图谱
Mysql数据库知识图谱
MySQL 数据库项目式教程
MySQL 数据库项目式教程
数据库模型
数据库模型
MySQL数据库知识解析_思维脑图
MySQL数据库知识解析_思维脑图
mysql
mysql