MySQL基础(三)

一、高级查询

数据库主要是用于对数据进行存储,更新、删除、查询操作CURD。在这几种操作中,最重要的就是查询。

select 【all|distinct】 字段列表|*|字段名 【as】 别名 from 数据源 【as】 表别名 【where子句】【group by子句】【order by子句】【having子句】【limit子句】

1.select 选项

【all|distinct】

说明:
all (默认)在显示结果中包含重复的数据。
distinct 在显示结果中去掉重复的数据。
此处的重复是在显示结果中的整行来对比。

2.字段别名

在查行数据查询时,我们可以对查询出来的字段名,在显示时设置一个别名。

3.数据源

所谓的数据源就是数据的来源
数据源可以分为单表数据源,多表数据源、子查询数据源。
单表数据源: 数据的来源只来源于一个表
多表数据源: 数据的来源来源于多个表
子查询数据源: from后数据的来源是另一个select语句

1)、多表数据源

语法:select * from 表A,表B

说明:
多表数据源产生的结果是一个笛卡尔积
笛卡尔积的形式是两个表的字段数相加,记录数相乘
笛卡尔积,没有实际的意义,但以后所要讲的连接查询都是以笛卡尔积为基础的

2)、子查询数据源

表中的数据是存储在硬盘中的一个文件中。当我们在执行一条select语句时,是在内存中运行,得到的结果也是在内存中,这个结果也是一个表,只是这个表时临时的,既然是一个表,也而已放在from后,作为数据源

语法:select * from (select语句) 表名;

4.表别名

在一条select语句中引用一个表时,也可以为这个表起一个表别名,主要应用在连接查询中。

例:
select * from stu as s;表别名
select s.s_name,s.s_num from stu s;使用列别名引用字段

5.【where子句】

【where子句】【group by子句】【order by子句】【having子句】【limit子句】这些子句称为五子句,五子句可以随意的组合使用,但是组合中的顺序必须按语法的顺序书写。

1)、使用:
where子句,根据表达式,在将数据从硬盘中读取到内存时进行第1次筛选。

2)、原理:

提示:
如果省略where子句,表示所有的记录都匹配。

6.【group by子句】

语法:group by 字段1,字段2...

1)、作用:
group by是对where子句得到的结果进行,分组统计

2)、原理:

提示:
如果省略group by表示,相当于所有的记录为一个大组,但是要把所有的记录都返回

3)、统计函数
group by 重要是在统计上,MySQL对分组的数据提供了很多的统计函数

count(字段名|*)
对 group by 得到的每一组,求每一组的记录数。

count(字段名): 按指定的字段进行统计,如果某一条记录的被统计的字段是Null值,这条记录并不会参与统计。
示例:count(字段名)

count(*): 统计记录数,并不受Null值影响
示例:

max(字段名)
统计每一中的指定的字段的最大值。

示例:

示例:

min(字段名)
用于统计,每一组中指定字段的最小值。

示例:

avg(字段名)
用于统计每一组中指定字段的平均值

示例:

sum(字段名)
用于统计每一组中指定字段的和。

示例:

扩展:
所有的统计函数后都可以使用一个别名来表示。

4)、多字段分组
group by 字段1,字段2…
当 group by 后指定多个字段时,会进行多字段分组。

在多字段分组的同时,使用统计函数
示例:

分析:
在多字段进行分组时,最终的统计数据,是争对最小组的统计,如上例中是争对品牌所划分的组。

5)、回溯统计
with rollup
多字段进行分组,统计函数默认是应用在最小组上的,如果想对包含最小组(最小组之上)的大组进行同样的统计,那么 with rollup 就可以实现这个需求。

示例:

7.【having 子句】

作用:
where 是在将硬盘中的数据读取到内存时进行第1次筛选
group by 是针对 where 子句匹配到的记录进行分组统计
having 是争对 group by 分组统计得到的结果再进行第2次筛选。
示例:

扩展(了解):
统计函数,是不能出现在where子句中。
having可以代替where子句,但是强烈不建议这么用。

8.【order by 子句】

语法: order by 字段1 【asc|desc】,字段2 【asc|desc】...

说明:
order by 对 where 子句、group by子句、having子句得到的结果进行一个现实顺序上的控制。
asc 缺省的升序
desc 降序

扩展:

9.【limit 子句】

语法:limit 【offset,】 rows

说明:
limit是对 where 子句、group by 子句、having 子句、order by 子句得到的结果进行一个现实行数的一个限制。
offset 偏移量,每1条记录偏移量为0,第2条记录的偏移量为1,以此类推,如果省略表示0
rows 显示的行数

数据分页:
分页相关的因素:
每一页显示的记录数 rowsPerPage 认为设定
当前的页码数: curPage 用户所点击的页码
假设
rowsPerPage=3 表示每一页显示3条记录
curPage 1 2 3 4 5…

数据分页的公式:
select * from goods limit (curPage-1)*rowsPerPage,rowsPerPage;

二、联合查询

使用分表存储数据,联合查询

语法:
select 语句A
union 【all|distinct】
select 语句B

说明:
分表存储,联合查询。为什么要分表查询,为了解决查询1条记录更快,但是查询单条记录快了,如何解决所有呢,所以才就出现了联合查询语法。
【all|distinct】 union选项
all 表示所有
distinct (默认的)表示去重
在联合查询中的两条 select 语句,所查询出来的字段的个数必须一致。

联合查询还可以解决,对同一个表的不同部分进行不同的操作。
如果联合查询的 select 语句中没有 order by 子句,那么必须配合 limit 使用。

三、存储引擎(了解)

create table 表名(表的定义)【表选项】
【表选项】
charset 存储字符集
collate 校验集
engine 存储引擎

存储引擎:
汽车的引擎是用于驱动汽车运行的。而MySQL的存储引擎,用于驱动数据的从硬盘进行读或写的。

查看存储引擎:

语法:show engines;

MySIAM
不支持事务安全
不支持外键

如果是MyISAM存储引擎,那么在创建一个表时会创建三个文件。
.frm 表的结构文件
.MYD 存储的是表中的数据
.MYI 存储表的索引

InnoDB
支持事务安全
支持外键

如果使用innodb存储引擎,在创建一个表时,只会有结构文件,其他的数据文件与索引文件放在ibdata1中。

——–摘录自传智播客mysql基础视频