首页  思维导图  详情

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

2021-08-03 16:48:04   48  举报





AI智能生成

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

mysql

数据分析

大数据知识

数据库

知识脑图

作者其他创作

大纲/内容

1、数据库的介绍和环境搭建

1、数据库介绍

数据存储

传统记录数据的缺点：

不易保存
备份困难
查找不便

现代化手段——文件

对于数据容量较大的数据，不能够很好的满足，而且性能较差
不易扩展

数据库：

持久化存储
读写速度极高
保证数据的有效性（通过约束条件来保证）
对程序的支持性非常好，容易扩展

关系型数据库

关系型数据库，是指采用了关系模型来组织数据的数据库，其以行和列的形式存储数据，以便于用户理解，关系型数据库这一系列的行和列被称为表，一组表组成了数据库。

包括MySQL、Oracle、MsSqlserver等等

设计原则：

命名规范化
数据的一致性和完整性
减少数据冗余
范式理论（3NF）

优点：

复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
事务支持使得对于安全性能很高的数据访问要求得以实现。

非关系型数据库

非关系型数据库主要是基于“非关系模型”的数据库（由于关系型太大，所以一般用“非关系型”来表示其他类型的数据库）

类型：

列模型：存储的数据是一列列的。关系型数据库以一行作为一个记录，列模型数据库以一列为一个记录。（这种模型，数据即索引，IO很快，主要是一些分布式数据库）——Hbase
键值对模型：存储的数据是一个个“键值对”，比如name:liming,那么name这个键里面存的值就是liming——redis,MemcacheDB
文档类模型：以一个个文档来存储数据，有点类似“键值对”。——mongoDB

优点：

性能NOSQL是基于键值对的，可以想象成表中的主键和值的对应关系，而且不需要经过SQL层的解析，所以性能非常高。
可扩展性同样也是因为基于键值对，数据之间没有耦合性，所以非常容易水平扩展。

理解数据库

数据列：字段
数据行：记录
数据表：数据行的集合
数据库：数据表的集合
单元格：存储的数据
ID主键：能够唯一标识某个记录的
外键：字段所存储的数据是别的表的主键

MySQL

简介：
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更改功能

存数据——快、持久化存储、数据有效性、扩展好

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

图形化管理工具

PHPmyadmin
Navicat
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.数据库操作

数据库的连接——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不区分大小写

2.数据表操作

查看当前数据库中所有表——show tables;
创建表——create table 数据表名 (字段类型约束 [ ,字段类型约束]);
查看表结构——desc 数据库表名；
查看表的创建语句——show create table 表名字；
删除表——drop table 表名；【不可使用！！】
退出表——表不用退出，因为没有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子句];

查询所有字段
查询指定字段
使用 as 给字段起别名
可以通过 as 给表起别名
在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'......'
%表示任意个字符串
_表示一个字符串

查询姓名以“小”开始的学生信息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

注意：

不能用=
null(占用空间地址的)与""(空字符串,不占空间地址的)是不同的

查询姓名信息为空的男生信息select * from students where name is null and gender=1；

4.聚合函数

count(*)总数表示计算总行数，括号中写星与列名，结果是相同的
max(列)最大值表示求此列的最大值
min(列)最小值表示求此列的最小值
sum(列)求和表示求此列的和_如果求和字段是varchar 则为0；若字段内容是数字，则相加
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的值相同时，则按照列2排序，以此类推
默认按照列值从小到大排列（asc）
asc从小到大排列，即升序
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；
说明：

从start开始(从0开始)，获取count条数据【起始位置（从0开始），数据个数】
start,count不能写数学公式
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外键

外键的特点

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只有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;

总结

一个表可以有多个外键
对子表students（外键所在的表）的作用：子表在进行写操作的时候，如果外键字段在父表中找不到对应的匹配，操作就会失败
对父表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类