Mysql数据库笔记

关键字说明

数据库：database
数据库系统：DBS(Database System)是一种虚拟系统，将多种内容关联起来的称呼DBS = DBMS+DB
DBMS:Database Management System，数据库管理系统，专门管理数据库
DBA:Database Administrator，数据库管理员

SQL

SQL: Structured Query Language ，结构化查询语言（数据以查询为主，99%是在进行查询操作）

SQL分为三个部分：
DDL: Data Definition Language,数据定义语言,用来维护存储数据的结构(数据库,表),代表指令: create, drop, alter等
DML: Data Manipulation Language,数据操作语言,用来对数据进行操作(数据表中的内容),代表指令: insert, delete,update等:其中DML内部又单独进行了一个分类: DQL(Data Query Language:数据查询语言,如select)
DCL: Data Control Language,数据控制语言,主要是负责权限管理(用户),代表指令 grant,revoke等
SQL是关系型数据库的操作指令, SQL是一种约束,但不强制(类似W3C):不同的数据库产品 (如Oracle,mysql)可能内部会有一些细微的区别

Mysql数据库

Mysql数据库是一种c/s结构的软件：客户端/服务器，若想访问服务器必须通过客户端（服务器一直运行，客户端在需要的时候运行）

交互方式

1. 客户单连接认证：连接服务器，认证身份 mysql -hPup
2. 发送SQL指令
3. 服务器接收SQL指令，处理SQL指令，返回操作结果
4. 客户端接收结果，显示结果
5. 断开连接（服务器释放资源）

Mysql服务器对象

mysql服务器内部对象分为四层：系统（DBMS）-> 数据库（DB）-> 数据表（Table）-> 字段（field）

SQL基本操作

基本操作：CURD

将SQL的基本操作根据操作对象进行分类，分为三类：库操作，表操作（字段），数据操作

库操作

对数据库的增删改查

新增数据库

create database 数据库名 [库选项];
库选项：用来约束数据库，有两个选项：
字符集设定：charset/character set 具体字符集（数据存储的编码格式）：常用字符集：GBK/UTF8
校对集设定：collate 具体校对集（数据比较的规则）

创建数据库
create database mydatabase charset utf8;
如果创建关键字名称的数据库，需要使用反引号（esc键下面的键）
create database ·database· charset utf8;
如果要创建带有中文的数据库名，需要提前告诉服务器使用的编码格式，但是建议不要用
set names gbk; （编码格式以当前命令窗口的编码格式为准）
create database 中国 charset utf8;

当创建数据库的sql命令执行以后，发生了什么？
1. 在数据库系统中增加对应的数据库信息
2. 在保存数据的文件夹（data目录）下，创建一个对应数据库名字的文件夹
中文数据库的文件夹名会以特定字母格式存储，无法分辨
3. 数据库文件夹中会创建一个opt文件（保存了库选项，校对集依赖于字符集，修改字符集，校对集跟着修改）

查看数据库

1. 查看所有数据库
show databases;
2. 查看指定部分的数据库（模糊查询）
show database like ‘pattern’; — pattern是匹配模式
%：匹配多个字符
_：匹配单个字符
如果数据库名中含有_，则_需要被转义（\_）
3. 查看数据库创建语句
show create database mydatabase;
系统在执行sql语句时会先进行优化，系统存储的是优化后的结果

修改数据库

数据库名字不可以修改，数据库的修改仅限于库选项（字符集和校对集，校对集依赖字符集）
alter database 数据库名 [库选项];
charset/character set [=] 字符集;

删除数据库

drop database mydatabase;

当删除数据库语句执行后发生了什么？
1. 数据库内信息被删除
2. 数据库文件夹被级联删除

所以不要随意删除数据库，删除操作不可逆，应先进行备份操作后进行删除

表操作

创建数据表

ERROR 1046：No datebase selected — 没有选中数据库
解决方法：
1. 显式的指定数据库 create table 数据库.表名();
2. 隐式的指定所属的数据库 use 数据库名;

当创建数据库指令执行后：
1. 指定数据库下存在对应的表
2.在数据库对应文件夹下产生对应表的结构文件（与存储引擎有关）
frm文件：结构文件

查看数据表

数据库能查看的方式，表都可以查看

修改数据表

1.修改表本身

1. 修改表名： rename table oldname to newname;
2. 修改表选项：字符集，校对集和存储引擎 alter table 表名 表选项 [=] 值;

2.修改表字段

删除数据表

drop table 表名,表名,表名; — 可以一次性删除多张表

删除指令执行后：
1. 在表空间中，没有指定的表（数据也没有了）
2.在数据库库对应的文件夹下，表对应的文件（与存储引擎有关）也会被删除

数据操作

增加数据

1.给全表字段插入数据，不需要指定字段列表：要求数据的值出现的顺序与表中设计的字段出现的顺序一致：凡是非数值数据，都需要使用引号（建议单引号）包裹
insert into 表名 values(值列表)[,(值列表)];
— 可以一次性插入多条记录

2.给部分字段插入数据，需要选定字段列表：字段列表出现的顺序与字段的顺序无关；但是值列表的顺序必须与字段的顺序一致
insert into 表名 (字段列表) value (值列表)[,(值列表)]

查看数据

1.查看所有数据
select */字段列表 from 表名 [where 条件]

2.查看指定字段指定条件的数据
select 字段列表 from 表名 where 字段名=值;

更新数据

update 表名 set 字段 = 值 [where 条件]
— 更新不一定会成功，比如：如果没有要更新的数据；所以需要查看执行后受影响的行数

删除数据

delete from 表名 [where 条件]

中文字符集问题

error 1366 : incorrect string value:’\xd5\xc6′ for column ‘name’ at row 1
原因：控制台使用的字符集编码与数据库使用的字符集编码不匹配

查看服务器支持的所有字符集
show character set;
查看服务器默认的对外识别字符集
show variables like ‘character_set%’;

解决方法：
更改服务器默认的接收字符集
set character_set_client=gbk;
更改服务器默认的给外部数据的字符集
set character_set_results=gbk;

set 变量 = 值; — 这种修改方式只是会话级别（本次连接有效，关闭失效）
可以使用快捷方式：set names 字符集;
— set names gbk; == character_set_client,set character_set_results,set character_set_connection
— connection 连接层：是字符集转变的中间层，统一后效率更高，不统一也可以。

数据类型

数据类型：对数据进行统一的分类，从系统的角度出发是为了能够更好的管理：更好的利用有限的空间

列类型

数值型

整数型
1. tinyint:迷你整型，占用1字节，范围：256
2. smallint:小整型，占用2字节，范围65536
3. mediumint:中整型，占用3字节
4. int:标准整型，占用4字节
5. bigint大整型，占用8字节

sql中的数值类型全部默认是有符号的：分正负
需要使用无符号类型时，需要给类型限定：字段类型 unsigned;

显示表结构时，字段类型后面的数字叫做：显示宽度：数据最终显示的位数（负号包含一位）
显示宽度：没有特别的意义，只是默认告诉用户可以显示的形式，用户可以进行控制，但是不会改变数据本身的大小
显示宽度的意义在于：当数据不够显示宽度的的时候，自动让数据变成对应的显示宽度，通常需要搭配前导‘0’来增加宽度，但不改变数据大小；
零填充（显示宽度）的意义在于：保证数据格式
前导‘0’：zerofill（零填充）,使用时自动添加无符号类型
使用时给类型限定：字段类型 zerofill;

小数型

带有小数点或者范围超出整型的数值类型

*浮点型

浮点型是一种精度型数据，小数点浮动，精度有限，超出指定范围会丢失精度

1.float:单精度，占用4个字节，精度范围在7为左右
2.double:双精度，占用8个字节，精度范围在15位左右

创建浮点数表：浮点的使用方式：
1.直接float表示没有小数部分；
2.float(M,D) M:总长度 D:小数部分长度 则整数部分：M-D
插入数据时：
1.可以使用小数或者使用科学计数法（10e10）
2.整型部分不能超出长度，小数部分可以超出长度（系统自动四舍五入）
*浮点数超出精度范围进行四舍五入，由于系统进位导致的超出长度是被允许的

*定点型

decimal:小数点固定，精度固定，不会丢失精度

绝对保证整数部分不会四舍五入（不会丢失精度），小数部分可能会（理论上来说不会）

小数部分超出设置的精度范围（D），是可以的，但是会提示警告
由于系统进位导致的超出范围会报错
error 1264 : out of range value for column …

查看警告：show warnings;

时间日期类型

1.datatime:YYYY-mm-dd HH:ii:ss ，范围：1000-9999
有0值：0000-00-00 00:00:00
2.date:日期，与datatime中的date部分一样
3.time:时间（段），指定某个区间时间，-time – +time
4.timestamp:时间戳，只是从1970年开始的， YYYY-mm-dd HH:ii:ss 格式与datetime完全一致
5.year年份，两种形式year(2)和year(4):1901-2156

timestamp特性：不能为空，有默认值，自动更新
只要当前所在行的记录被更新，该字段会自动更新成系统当前时间

插入数据：
时间time可以是负数/很大的负数，使用负数时，表示的是从过去到现在的时间段
带有空格表示过去2天，系统会自动转换，即-（48+11）=-59
使用year可以用2位/4位， 2位使用时注意，69表示2069，70表示1970，即数据范围在1970-2069

字符串类型

1.char：定长字符串（磁盘在定义结构时，确定了最终数据的存储长度，以后不能改变）
char(L):L代表length，可以存储的长度，单位为字符，最大长度255

2.varchar：变长字符串（在分配空间时，按照最大空间分配，但是实际用了多少，要根据具体数据来确定）
varchar(L)：L代表length，最大长度65536，一般会多出1-2个字节（1个字节表示255，2个字节刚好够），来确定实际存储的长度
实际上如果字符长度超过255，则不用char/varchar，使用text

如何让选择定长和变长字符串？
定长的磁盘空间比较浪费，但是效率高，变长磁盘空间比较节省，但是效率低
如果数据长度基本一样，则可以使用定长
如果数据长度变化较大，则可以使用变长

3.text：文本类型，如果数据量非常大，超过255则使用，一般来存储文字（二进制数据存储路径）

4.blob：文本类型，如果数据量非常大，超过255则使用，一般来存储二进制（一般不使用）

5.enum：枚举，只能存储事先规定好的所有可能出现的数据中的一个
enum(‘元素1′,’元素2′,’元素3’)
枚举使用作用1：统一数据格式
枚举使用作用2：节省空间（mysql系统在存储数据时会进行自动转换，比如把字符串自动转换为0，所以枚举在使用时，会自动存储数字，不会存储字符）
如何证明枚举存储的是数值：将数据取出进行+0运算，如果为0则说明是字符串，否则为其他值
经验证枚举内部存储数值，按照元素出现的顺序以1开始，进行编码
枚举的存储原理：枚举在进行数据规范的时候（定义的时候），系统会自动建立一个数字与枚举元素的对应关系（放进日志中），在进行插入数据或者数据提取时，自动进行转换
因为枚举存储的是数值，所以插入数据可以直接插入数值

6.set：集合，和枚举类似，存储的是数值，而不是字符串，集合是多选
插入数据时可以用多个字符串组合也可以直接插入数值：insert into 表名 values (‘元素1,元素2，元素3…’);
集合中的数值是二进制转换之后的结果，因为集合中的元素是二进制位对应（0为未选中，1为选中），然后反向排列：例

集合内部使用整型来进行存储数值，与整型的空间对应，能够规范数据和节省空间

mysql记录长度

mysql中规定，任何一条记录长度不能超过65535个字节，即，cvarchar永远也达不到理论值
varchar的实际存储长度达到多少，需要看他的字符集编码

utf8:21845*3=65535
gbk:32767*2=65534
但是因为varchar还需要2个字节存储长度
utf8:21845*3+2=65535+2=65537
gbk:32767*2+2=65534+2=65536
都超出了长度，所以实际长度：
utf8:21844*3+2=65532+2=65534
gbk:32766*2+2=65532+2=65534
utf8:21844
gbk:32766

如果想要使用完整的所有长度，即：65534+1
理论上继续创建一个tinyint字段
但是实际上，如果有任何一个字段设置默认可以为NULL，系统会为默认为NULL保留一个字节
所以，要使用完整长度，需要让所有字段都不允许为NULL
即创建数据表时需要设置：
字段名 字段类型 not null

如果存储大量字符，则可以使用text类型
因为，text类型不占用记录长度，额外进行存储，但是text本身也属于记录的一部分，即也需要占用一定的记录长度：10字节（保存数据地址和长度）

列属性

列属性：真正约束字段的是数据类型，但是数据类型的约束很单一，需要有一些额外的约束，来更加保证数据的合法性

列属性：
NULL/NOT NULL,default,Primary key,unique key,auto_increment,comment

空属性

NULL（默认）/NOT NULL

虽然默认为空，但是在实际开发中，应该保证所有的数据都不应该为空：空数据没有意义，空数据没有办法参与运算

列描述

comment：描述，没有实际含义，专门用来描述字段，会根据创建语句保存
comment ‘具体描述’
使用show create table 表名; 进行查看

默认值

default：增加数据时默认自动填写
插入数据：
insert into 表名 (字段名) values(‘未指定默认值的字段值’);
insert into 表名 values(‘未指定默认值的字段值’,default);

主键

primary key主要的键，一张表只能有一个字段可以使用对应的键，用来唯一的约束该字段里面的数据，不能重复，这种称之为主键

一张表最多能能有一个主键，主键默认不能为空

增加主键

1.在创建表的时候之间添加primary key字段（优点：非常直接，缺点：只能使用一个字段作为主键）
2.在创建表的时候，在所有的字段之后，使用primary key（主键字段列表）来创建主键（如果有多个字段作为主键，可以是复合主键）
复合主键：两个主键字段的数据同时不重复，即有一个重复也可以

create table [if not exists] 表名(
字段名 数据类型,
字段名 数据类型,
primary key(字段名,字段名)
)[表选项];

主键只能有一个，所以多字段主键是一个复合主键

3.当表创建好之后，再次额外追加主键，前提是表中字段数据是不重复的
可以通过修改表属性
alter table 表名 modify 字段名 数据类型 [属性][位置]
也可以直接追加主键
alert table 表名 add primary key(字段列表);

更新主键/删除主键

不能更新主键，所以只能先删除再增加
删除主键：alert table 表名 drop primary key;

唯一键

一张表往往有很多字段需要具有唯一性，数据不能重复，但是一张表中只能有一个主键
所以需要使用唯一键（unique key）解决一张表中有多个字段需要唯一性约束

唯一键和主键的本质差不多，但是区别在于：
唯一键默认允许自动为空，而且可以多个为空（空字段不参与唯一性比较）但是不为空的唯一键和主键性质相同

增加唯一键

1.在创建表的时候之间添加unique/unique key字段
2.在创建表的时候，在所有的字段之后，使用unique key（唯一键字段列表）来创建唯一键（如果有多个字段作为唯一键，可以是复合唯一键）
3.当表创建好之后，再次额外追加主键，前提是表中字段数据是不重复的
可以通过修改表属性
alter table 表名 modify 字段名 数据类型 [属性][位置]
也可以直接追加唯一键
alert table 表名 add unique key(字段列表);

创建唯一键后查询时key字段会显示pri，但是他不是主键，不为空的唯一键和主键性质相同，但这不是它显示pri的原因，如果该表没有主键，只有唯一键，系统会自动把它当作主键而去显示pri
如果该表有主键的时候再去增加唯一键，则唯一键显示uni

更新唯一键/删除唯一键

更新唯一键：先删除后增加，但是唯一键可以有多个，所以可以不删除

删除唯一键： alert table 表名 drop index 索引名;
查看索引名：查看表创建语句，在unique key字段之后，字段名前

自增长

auto_increment
1.任何字段需要使用自增长，前提是它是一个索引（key一栏有值）
2.自增长字段必须是整数
3.一张表最多只能有一个自增长

当自增长的字段被给定值为NULL/默认值时，触发自增长
如果自增长的字段被给定了一个有效值时，自增长失效，下一次触发自增长时，从最大值开始
通过查看表创建语句，可以查看自增长下一次使用的值

修改自增长

修改自增长字段必须先删除现有自增长，然后再添加

修改下一次自增长的值，只能修改为比当前字段最大值大的值，小时不生效
alter table 表名 表选项 [=] 值;

因为所有的系统变量都是由系统内部变量控制的，所以可以进行查看/修改（修改是对于整个数据库而不是一张表，并且修改是会话级别）
show variable like ‘auto_increment%’
set auto_increment_increment = 数值; — 步长（修改步长时会导致在原数值基础上+1，然后在自增长）
set auto_increment_offset = 数值; — 起始值

删除自增长

删除自增长以修改表选项的方式删除，注意，有主键时，不需要在写主键选项

索引

几乎所有的索引都是建立在字段之上

索引：系统根据某种算法，将已有的数据（未来可能新增的数据）单独建立一个文件夹：文件能够实现快速匹配，并且能快速找到对应表中的记录

索引的意义：
1.提升查询数据的效率
2.约束数据的有效性（唯一性等）

增加索引的前提：索引本身会产生索引文件（有时候可能此数据比数据文件大），会非常消耗磁盘空间
如果某个字段经常作为查询的条件使用，那么可以使用索引（一定会想办法增加）
如果某个字段需要进行数据的有效性约束，那也可以使用索引（主键，唯一键）

mysql中提供了多种索引：
1.主键索引（primary key）
2.唯一索引（unique key）
3.全文索引（fulltext）
针对文章内部的关键字进行索引
它最大的问题在于：如何确定关键字，英文很容易（单词与单词之间有空格），中文很难（没有空格，中文可以随意组成：使用sphinx进行中文分词技术）
4.普通索引（index）：可能不具有唯一性但是经常作为查询条件

关系

将实体与实体的关系，反应到最终数据表的设计上来，将关系分成三种:一对一，一对多，多对一，多对多，所有的关系都是指表与表的关系

一对一

一对一：一张表的一条记录一定只能与另一张表的一条记录进行对应，反之亦然

如果一张表中的字段存在常用与不常用的字段时，每次查询所有的数据而又不需要全部的信息时影响效率

解决方案：将常用的和不常用的信息分成两张表，这时需要将常用信息表和不常用信息表可以对应，使用一个具有唯一确定的记录字段共同链接两张表

一个常用表中的一条记录，永远只能在一张不常用表中匹配一条记录，反过来，一个不常用表中的一条记录，在常用表中也只能匹配一条记录。

一对多

一对多：一张表中的的一条记录可以对应另外一张表的多条记录，反之，另外一张表的一条记录，只能对应一张表的一条记录，这种关系就是一对多/多对一

在第二张表中增加一个字段指向第一张表，因为第二张表只能匹配到第一张表的一条记录

多对多

一张表中的多条记录能够对应另一张表的多条记录，同时另一张表的多条记录可以对应一张表的多条记录

这时，不管在哪张表中增加字段都会出现一个字段需要保存多个数据，而且是与其他表有关系的字段，不符合表的设计规范
通过增加一张新表，专门维护两张表之间的关系

范式

normal format，是一种离散数学中的知识，是为了解决一种数据的存储与优化的问题，保证数据的存储之后，范式通过关系寻找出来的数据，坚决不再重复存储
它的目标是为了减少数据的冗余存储

范式：是一种分层结构的规范，分为六层：每一层都比上一层更加严格：若要满足下一层范式，必须满足上一层范式

六层范式：1NF,2NF,3NF,4NF,5NF,6NF
1NF是最底层（要求最低），6NF最高层（要求最高）

mysql属于关系型数据库：有空间浪费：也是致力于节省空间：与范式所有解决问题不谋而合，在设计数据库时，会利用范式来指导设计
但是数据库不单要解决空间问题，还要保证效率问题，范式只为解决空间问题，所以数据库的设计又不可能完全按照范式的要求实现
一般情况下，只有前三种范式需要满足

范式在数据库的设计中具有指导意义，但是不是强制规范

1NF

第一范式：在存储数据时，如果表中的字段，再取出使用之前还需要进行额外的数据处理（拆分），那么这张表不满足第一范式
第一范式要求字段的数据具有原子性：不可拆分

2NF

要满足第二范式，必须满足第一范式

在数据表的设计的过程中，如果有复合主键（多字段主键），且表中有字段并不是由整个主键来确定，而是依赖主键中的某个字段（主键的部分）：存在字段依赖主键的部分的问题，称之为部分依赖
第二范式就是要解决表设计不允许出现部分依赖

一张表中的一个字段没有办法作为独立主键，需要结合另一个字段才能作为主键，即复合主键，如果出现第三个字段和第四个字段只依赖于第一第二其中一个字段的时候，这时称为部分依赖，不符合第二范式

解决方案：
1. 将相互依赖的字段单独成表
2.取消复合主键，使用逻辑主键

3NF

要满足第三范式必须满足第一第二范式

理论上讲，一张表中所有字段都应该直接依赖主键（逻辑主键：代表业务主键），如果表设计中存在一个字段，并不直接依赖主键，而是通过非主键字段依赖，最终实现依赖主键，把这种不是直接依赖主键，而是依赖非主键字段的依赖关系称之为传递依赖
第三范式要解决传递依赖的问题

解决方法：
1.将存在传递依赖的字段和依赖字段本身单独成表，在需要对应信息的时候，使用对应实体表的主键加进来

逆规范化

有时候在设计表的时候，如果一张表中有几个字段是需要从另外的表中去获取信息，理论上讲的确可以获取到想要的数据，但是效率低一点，会刻意的在某些表中不去保存另外表的主键（逻辑主键），而是直接保存想要的数据信息，这样一来，在查询数据的时候，一张表可以直接提供数据，而不需要多表查询（效率低），但是会导致数据冗余增加。

逆范式化：磁盘利用率与效率的对抗

数据高级操作

新增数据

基本语法：
insert into 表名 [(字段列表)] values(值列表);

在数据插入的时候，假设主键对应的值已经存在，插入一定会失败

主键冲突

当主键存在冲突的时候（duplicate key）可以选择性的进行处理：
更新操作：
insert into 表名 [(字段列表：包含主键)] values(值列表) on duplicate key update 字段 = 新值;
替换操作： — 先尝试插入，出现冲突时，先删除，在插入，没有冲突时，直接插入
replace into 表名 [(字段列表：包含主键)] values(值列表);

蠕虫复制

从已有的数据中去获取数据，然后将数据进行新增操作，使数据成倍的增加

create table 表名 like 数据库.表名; — 只复制表结构

insert into 表名[(字段列表)] select 字段列表/* from 数据表名; — 复制数据（可以从其他表复制，也可以从自己本身复制）

蠕虫复制的意义：
1.从已有的表中拷贝数据
2.可以迅速让表中的数据膨胀到一定的数量级；用以测试表的压力以及效率

更新数据

基本语法
update 表名 set 字段 = 值 [where 条件];

高级语法 — 限制更新数量
update 表名 set 字段 = 值 [where 条件][limit 更新数量];

删除数据

与更新类似，可以通过limit来限制数量

基本语法
delete from 表名 [where 条件][limit 数量];

当删除的表中存在主键自增长，当删除后，自增长不会还原，数据的删除不会改变表结构，只能删除后重建表
truncate 表名; — 先删除，后重建该表，即重置自增长

查询数据

基本语法
select * from 表名 [where 条件];

完整用法
select [select 选项] 字段列表[字段别名]/* from 数据源 [where 条件][group by 子句] [having 子句] [order by 子句] [limit 子句];

sleect 选项

sleect：select对查出来的结果的处理方式
All：默认，保留所有结果
Distinct：去除重复（所有字段都相同）

字段别名

当数据查询出来时，字段名不能满足需要，需要对字段重命名时

字段列表 [as] 字段别名

数据源

数据的来源，关系型数据库的数据来源都是数据表，本质上，只要保证数据类似二维表，都可以作为数据源

数据源分为多种：单表数据源，多表数据源，查询语句

单表数据源
select * from 表名;

多表数据源
select * from 表名,表名….;
从一张表中取出一条记录，去匹配另一张表中的所有记录且全部保留（记录数，字段数）
将这种结果称为：笛卡尔积（交叉连接）
但是笛卡尔积没什么用，而且耗费太多资源

子查询
数据的来源是一条查询语句（查询语句的结果属二维表）
select * from (子查询) as 表名;

where子句

用来判断数据，筛选数据
返回结果：0/1；0:false ; 1:true

判断条件：
比较运算符： > , < , >= , <= , != , <> , = , like , between and , in/not in
逻辑运算符：&&(and) , ||(or) , !(not)

where的原理：它是唯一一个直接从磁盘获取数据的时候就开始判断的条件
从磁盘取出一条数据，如果成立则保存，不成立则放弃

例：selet * from 表名 where id = 1 || id = 2 || id = 3;
select * from 表名 where id in (1,2,3);
这两种方式结果相同

select * from 表名 where id > 1 && id <3;
select * from 表名 where id > 1 and id <3;
select * from 表名 where id between 1 and 3;
between 是闭区间，而且左边的值必须小于或等于右边的值

group by

根据某个字段进行分组（相同的放一组，不同的放不同的组）

基本语法：select * from 表名 group by 字段名;

分组是为了统计数据（按分组字段进行统计）

sql提供了一系列的统计函数
count()：统计分组后的记录数：每组有多少记录
max()：统计分组后字段最大的值
min()：统计分组后的最小值
avg()：统计平均值
sum()：统计和

例：select 字段名,count(*),max(字段名),min(字段名)，avg(字段名) from 表名 group by 要统计的字段名;
count:统计要统计的字段名各总有多少条（其中NULL不进行统计），max该字段最大值，avg：该字段平均值（浮点4位，通过count(*)算记录数）

分组会自动对分组的结果进行排序（根据分组字段，默认升序）
group by [asc|desc]

多字段分组

用法：
select 字段名,count(*),max(字段名),min(字段名)，avg(字段名) from 表名 group by 字段1,字段2;
先按照字段1排序，再按照字段2排序

通过group_concat(字段)函数可以把分组的结果中的某个字段进行字符串连接而显示出来

回溯统计

with rollup

任何一个分组后都有一个小组，最后都需要对上级进行分组和汇报统计，根据当前分组的字段，这就是回溯统计，回溯统计的时候会讲分组字段置NULL

select 字段名,count(*),max(字段名),min(字段名)，avg(字段名) from 表名 group by 字段1 with rollup ;

多字段回溯统计

select 字段名,count(*),max(字段名),min(字段名)，avg(字段名) from 表名 group by 字段1,字段2 with rollup;

多字段回溯：考虑第一层会有一次回溯；第二次要看第一层分组的组数，组数是多少，回溯就是多少，再加上第一次回溯

having 子句

与where子句相似，进行条件判断；where针对磁盘数据进行判断，having对进入到内存后的数据进行处理（分组统计的结果或者统计函数都只有having能做）
having 能做where能做的几乎所有事
但是where因为在进入内存时进行条件判断所以效率更高

having使用时可以使用别名，避免重新统计浪费资源

例：
select id,count(*) from 表名 group by id having count(*) >= 1;
查询分组后id大于一的记录数

order by 子句

对某一字段进行升序或者降序排序，依赖于校对集

order by 字段名 [asc/desc]
asc默认升序，desc降序

多字段排序
order by 字段名 [asc],字段名 [desc];

limit 子句

是一种限制结果的语句，限制数量

1.只用来限制长度（数据量）：limit 数据量;
2.限制起始位置，限制数量：limit 起始位置,数据量; — 常用于数据分页，为用户节省时间，提高服务器的相应效率，减少资源浪费，offset = (页码 – 1) * length ;

连接查询

表1 join 表2

在用户查询数据的时候，需要显示的数据来自于多张表
将多张表（可以大于两张）进行记录的连接
记录数有可能变化，字段数一定会增加（至少两张表合并）

内连接

select * from 表1 [inner] join 表2 on 表1.字段=表2.字段;
on表示连接条件，条件字段代表相同的业务含义
从表1中取出每一条记录，去表2中与所有的记录进行匹配，匹配必须是某个条件在表1中与表2中相同最终才会保留，否则不保留

表或者字段都可以使用as 指定别名

内连接可以没有连接条件，结果为笛卡尔积

可以使用where代替on进行运算，通常不用where，where没有on效率更高（where所有数据匹配，on选择数据匹配）

外连接

以某张表为主，取出所有记录，每条记录与另一张表进行连接，不管能不能匹配上条件，最终都会保留，不能匹配时，其他表的字段都置空（NULL）

连接方式分为两种
lift join :以表1为主表
right join :以表2为主表

基本语法：
select * from 表1 lift/right join 表2 on 表1.字段=表2.字段; 必须指定条件

自然连接

natural join 自动匹配连接条件，系统自动以字段名作为匹配模式（同名字段作为条件，多个同名都作为条件）
连接之后自动合并同名字段

自然内连接

表1 natural join 表2

自然外连接

select * from 表1 natural lift/right join 表2 on 表1.字段=表2.字段;

交叉连接

表1 cross join 表2 ====== from 表1,表2
从一张表中循环取出每一条记录，每条记录都去另一张表进行匹配（没有条件匹配），而连接本身字段就会增加（保留），最终形成的结果叫：笛卡尔积

笛卡尔积没有意义：应该尽量去避免
存在的意义：保证连接的完整性

外键

foreign key :外面的键（键不再自己表中）；如果一张表中有一个字段（非主键）只想另外一张表的主键，那么该字段称之为外键

外键条件

外键要存在，必须保证表的存储引擎是innodb（默认的存储引擎），如果不是，那么外键可以创建成功但是没有约束效果

外键字段的字段类型（列类型）必须与父表的主键类型完全一致

一张表中的外键名字不能重复

增加外键的字段（数据已经存在），必须保证数据与父表主键要求对应

外键作用

外键默认有两个作用：一个对父表，一个队子表（外键字段所在的表）

对子表约束：

子表数据进行写操作的时候，如果对应的外键字段在父表找不到对应的匹配，那么操作会失败（约束子表数据操作）

对父表约束：

父表数据进行写操作（删和改都必须涉及到主键本身），如果对应的主键在子表中已经被数据所引用

增加外键

可以在创建表的时候增加或者在创建表之后增加（但要考虑数据的问题）
一张表可以有多个外键

修改外键&删除外键

外键不能修改，只能先删除后新增

删除外键

alter table 表名 drop foreign key 外键名;

一张表中可以有多个外键，但是名字不能相同
删除外键之后通过desc查看表结构不能反映出是否删除，必须通过查看表创建语句进行查看

外键约束

所谓的外键约束，就是指外键的作用，之前所说的作用是默认作用，另外可以对外键的需求进行定制化操作

外键约束有三种约束模式（都是针对父表的约束）：

district：严格模式（默认的），父表不能删除或者更新一个已经被子表数据引用的记录
cascade：级联模式，父表的操作，对应子表关联的数据也跟着操作
set null：置空模式，父表的操作之后，对应子表的数据（外键字段）被置空

通常的一个合理做法（约束模式），删除的时候，子表置空，更新的时候，子表级联操作
foreign key (外键字段) references 父表(主键字段) on delete 模式 update 模式;

删除置空前提：外键字段允许为空（如果不满足条件，外键无法创建）