mysql数据库进阶_MYSQL数据库进阶操作

一，基础强化

where语句的作用: 使用where子句对表中的数据筛选，结果为true的行会出现在结果集中.

1，as关键字

在使用SQL语句显示结果的时候，往往在屏幕显示的字段名并不具备良好的可读性，此时可以使用 as 给字段起一个别名。

1)使用 as 给字段起别名

select id as 序号, name as 名字, gender as 性别 from students;

2)可以通过 as 给表起别名

-- 如果是单表查询 可以省略表明

select id, name, gender from students;

-- 表名.字段名

select students.id,students.name,students.gender from students;

-- 可以通过 as 给表起别名

select s.id,s.name,s.gender from students as s;

2,消除重复行(distinct)

distinct可以消除重复的行。

select distinct 列1,... from 表名;

例：

select gender from students; -- 看到了很多重复数据 想要对其中重复数据行进行去重操作可以使用distinct

select distinct gender from students;

二，条件查询

select * from 表名 where 条件;

例：

select * from students where id=1;

where后面支持多种运算符，进行条件的处理

比较运算符

逻辑运算符

模糊查询

范围查询

空判断

1，比较运算符

等于: =

大于: >

大于等于: >=

小于: <

小于等于: <=

不等于: !=

例：

select * from students where is_delete=0;

2，逻辑运算符

and

or

not

例：

select * from students where not id > 3 and gender=0;  #由于优先级，not否定的是and之前的内容，可用()改变

3，模糊查询

1)like

%表示任意多个任意字符

_表示一个任意字符

例：

select * from students where name like '黄%' or name like '%靖';

2)rlike(正则表达式)

例：

select * from students where name rlike '^周.*伦$' ;

4，范围查询

范围查询分为连续范围查询和非连续范围查询

1.)in 表示在一个非连续的范围内

例：

select * from students where id in(1,3,8);

2.) between ... and ...表示在一个连续的范围内

例：

select * from students where id between 3 and 8;   # 包括3和8

注意:

1，between A and B在匹配数据的时候匹配的范围空间是 [A,B]

2，select * from students where id not (between 3 and 8) ; 是不对的！ not between是一个整体.

5，空判断

例：

select * from students where height is null/Null/NULL;

例：

select * from students where height is not null and gender=1

注意：

1. null与 '' 是不同的

2.判断是否为空用：  is null

6，优先级

优先级由高到低的顺序为：小括号，not，比较运算符，逻辑运算符

and比or先运算，如果同时出现并希望先算or，需要结合()使用

三，排序

排序查询语法：

select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...]

语法说明:

将行数据按照列1进行排序，如果某些行 列1 的值相同时，则按照 列2 排序，以此类推

asc从小到大排列，即升序

desc从大到小排序，即降序

默认按照列值从小到大排列(即asc关键字)

例：

select * from students order by age desc,height desc;

四，聚合函数

聚合函数aggregation function又称为组函数。默认情况下 聚合函数会对当前所在表当做一个组进行统计。

聚合函数有以下几个特点:

每个组函数接收一个参数(字段名或者表达式)

统计结果中默认忽略字段为NULL的记录 要想列值为NULL的行也参与组函数的计算，必须使用IFNULL函数对NULL值做转换。

不允许出现嵌套 比如sum(max(xx))

1，总数

count(*) 表示计算总行数，括号中写星与列名，结果是相同的。

例：

select count(*)  as 男性人数 from students where gender = 1;

2，最大值

max(列) 表示求此列的最大值

例：

select max(id) from students where gender=2;

3，最小值

min(列) 表示求此列的最小值

例：

select min(id) from students where is_delete=0;

4，求和

sum(列) 表示求此列的和

例：

select round(sum(age)/count(*),1) from students where gender=1;

5， 平均值

avg(列) 表示求此列的平均值

例：

select round(avg(id),2) from students where is_delete=0 and gender=2; # round(原数值,保留小数的位数)

五，分组

所谓的分组就是将一个“数据集”划分成若干个“小区域”，然后针对若干个“小区域”进行数据处理。

1，group by分组

使用特点

group by的含义

:将查询结果按照1个或多个字段进行分组，字段值相同的为一组

group by可用于单个字段分组，也可用于多个字段分组

select * from students;

+----+-----------+------+--------+--------+--------+-----------+

| id | name | age | height | gender | cls_id | is_delete |

+----+-----------+------+--------+--------+--------+-----------+

| 1 | 小明 | 18 | 180.00 | 女 | 1 | |

| 2 | 小月月 | 18 | 180.00 | 女 | 2 | |

| 3 | 彭于晏 | 29 | 185.00 | 男 | 1 | |

| 4 | 刘德华 | 59 | 175.00 | 男 | 2 | |

| 5 | 黄蓉 | 38 | 160.00 | 女 | 1 | |

| 6 | 凤姐 | 28 | 150.00 | 保密 | 2 | |

| 7 | 王祖贤 | 18 | 172.00 | 女 | 1 | |

| 8 | 周杰伦 | 36 | NULL | 男 | 1 | |

| 9 | 程坤 | 27 | 181.00 | 男 | 2 | |

| 10 | 刘亦菲 | 25 | 166.00 | 女 | 2 | |

| 11 | 金星 | 33 | 162.00 | 中性 | 3 | |

| 12 | 静香 | 12 | 180.00 | 女 | 4 | |

| 13 | 周杰 | 34 | 176.00 | 女 | 5 | |

| 14 | 郭靖 | 12 | 170.00 | 男 | 4 | |

+----+-----------+------+--------+--------+--------+-----------+

select gender from students group by gender; +--------+

| gender |

+--------+

| 男 |

| 女 |

| 中性 |

| 保密 |

+--------+

根据gender字段来分组，gender字段的全部值有4个'男','女','中性','保密'，所以分为了4组

在现在MySQL默认情况下, select 后的字段只能出现在以下两种情况:

在group by后出现过

在聚合函数中出现

2，group by + group_concat()

group_concat(字段名)根据分组结果，使用group_concat()来放置每一个分组中某字段的集合

select gender from students group by gender;

+--------+

| gender |

+--------+

| 男 |

| 女 |

| 中性 |

| 保密 |

+--------+

select gender,group_concat(name[,id]) from students group by gender;+--------+-----------------------------------------------------------+

| gender | group_concat(name) |

+--------+-----------------------------------------------------------+

| 男 | 彭于晏,刘德华,周杰伦,程坤,郭靖 |

| 女 | 小明,小月月,黄蓉,王祖贤,刘亦菲,静香,周杰 |

| 中性 | 金星 |

| 保密 | 凤姐 |

+--------+-----------------------------------------------------------+

select gender,group_concat(id) from students group by gender;

+--------+------------------+

| gender | group_concat(id) |

+--------+------------------+

| 男 | 3,4,8,9,14 |

| 女 | 1,2,5,7,10,12,13 |

| 中性 | 11 |

| 保密 | 6 |

+--------+------------------+

3， group by + 聚合函数

通过group_concat()的启发，我们既然可以统计出每个分组的某字段的值的集合，那么我们也可以通过集合函数来对这个值的集合做一些操作

聚合函数在和group by结合使用的时候 统计的对象是每一个分组。

select gender,group_concat(age) from students group by gender;

+--------+----------------------+

| gender | group_concat(age) |

+--------+----------------------+

| 男 | 29,59,36,27,12 |

| 女 | 18,18,38,18,25,12,34 |

| 中性 | 33 |

| 保密 | 28 |

+--------+----------------------+

分别统计性别为男/女的人年龄平均值

select gender,avg(age) from students group by gender;

+--------+----------+

| gender | avg(age) |

+--------+----------+

| 男 | 32.6000 |

| 女 | 23.2857 |

| 中性 | 33.0000 |

| 保密 | 28.0000 |

+--------+----------+

分别统计性别为男/女的人的个数

select gender,count(*) from students group by gender;

+--------+----------+

| gender | count(*) |

+--------+----------+

| 男 | 5 |

| 女 | 7 |

| 中性 | 1 |

| 保密 | 1 |

+--------+----------+

4，group by + having

having 条件表达式：用来过滤分组结果

having作用和where类似，但having只能用于group by 而where是用来过滤表数据，

select gender,count(*) from students group by gender having count(*)>2;

+--------+----------+

| gender | count(*) |

+--------+----------+

| 男 | 5 |

| 女 | 7 |

+--------+----------+

5，group by + with rollup

with rollup的作用是：在最后新增一行，来记录当前表中该字段对应的操作结果，一般是汇总结果。

select gender,count(*) from students group by gender with rollup;

+--------+----------+

| gender | count(*) |

+--------+----------+

| 男 | 5

| 女 | 7 |

| 中性 | 1 |

| 保密 | 1 |

| NULL | 14 |

+--------+----------+

select gender,group_concat(age) from students group by gender with rollup;

+--------+-------------------------------------------+

| gender | group_concat(age) |

+--------+-------------------------------------------+

| 男 | 29,59,36,27,12 |

| 女 | 18,18,38,18,25,12,34 |

| 中性 | 33 |

| 保密 | 28 |

| NULL | 29,59,36,27,12,18,18,38,18,25,12,34,33,28 |

+--------+-------------------------------------------+

6，总结

group by 关键字能根据1个或多个字段对数据进行分组

group_concat函数作用就是将每个分组中的每个成员的指定个字段拼接在一行中显示

聚合函数在和 group by 结合使用时, 统计的对象是每个分组

having 是对分组结果进行条件过滤

with rollup在分组结果最后新增一行完成汇总显示。

六，分页

为什么需要分页?

接着百度给用户提供数据的例子。当排序经过数据分析之后，根据关联度和点击量等属性排序后，所有的数据的大小对于用户来讲是个天文数字并且用户也不一定需要这么大量的数据，所以这个时候就有一个想法能不能把这么多数据分成一页一页的数据，而 根据用户的需要将数据分为一页一页地传输给用户的技术就是分页

1， 语法

select * from 表名 limit start=0,count

说明

从start开始，获取count条数据

start默认值为0

也就是当用户需要获取数据的前n条的时候可以直接写上 xxx limit n;

例：

select * from students where gender=1 limit 0,3;

select * from students where gender = 2 order by height desc limit 0,2;

2，关于分页的推导公式

已知：每页显示m条数据，当前显示第n页

求总页数：此段逻辑后面会在python项目中实现

查询总条数p1

使用p1除以m得到p2

如果整除则p2为总数页

如果不整除则p2+1为总页数

获取第n页的数据的SQL语句求解思路

第n页前有n-1页

所在第n页前已经显示的数据的总量是(n-1)*m

由于数据的下标从0开始 所以第n页前所有的网页的下标是0,1,...,(n-1)*m-1

所以第n页的数据起始下标是(n-1)*m

获取第n页数据的SQL语句

select * from students where is_delete=0 limit (n-1)*m,

# 注意：在sql语句中limit后不可以直接加公式

3，总结

使用limit限制数据显示数量

limit后 参数一：数据查询的起始下标。；参数二：显示查询数据的数量。

七，连接查询

当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集进行汇总显示。

mysql支持三种类型的连接查询，分别为：内连接查询、右(外)连接查询、左(外)连接查询

1，内连接查询：查询的结果为两个表匹配到的数据

select * from 表1 inner或left或right join 表2 on 表1.列 运算符 表2.列 having...

2，右(外)连接查询：查询的结果为两个表匹配到的数据和右表特有的数据，对于左表中不存在的数据使用null填充

select * from 表1 inner或left或right join 表2 on 表1.列 运算符 表2.列 having...

3，左(外)连接查询：查询的结果为两个表匹配到的数据和左表特有的数据，对于右表中不存在的数据使用null填充

select * from 表1 inner或left或right join 表2 on 表1.列 运算符 表2.列 having...

例：

select s.cls_id ,s.*,c.name from students as s left join classes as c on s.cls_id=c.id order by cls_id ;

例：使用右连接查询班级表与学生表

select * from students as s left join classes as c on s.cls_id=c.id having c.id is null order by age limit 2;

4，总结

连接的目的主要将多张的相关数据汇总一个结果集中

连接分为内连接, 左连接, 右连接

on 关键字用于 设置多表连接操作的条件

内连接 inner join 的结果是表与表之间满足连接条件的数据

外连接 outer join 是在内连接的基础上添加了外部数据，外部数据来自于左表(右表数据位置对应填充NULL) 则是左连接；外部数据来自于右表(左表数据位置对应填充NULL)则是右连接。

注意: 能够使用连接的前提是 多表之间有字段上的关联。

八，自关联

当需要将多张"表"的相关数据汇总一个结果集中, 并且多张"表"的数据来自于同一张表.

自连接就是一种特殊的连接查询方式

需要对同一张表起多个不同的别名才能进行自连接查询

例：

select city.* from areas as city inner join areas as province on city.pid=province.aid where province.atitle='山西省';

九，子查询

在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句,外部那个select语句则称为主查询.

主查询和子查询的关系：

子查询是嵌入到主查询中

子查询是辅助主查询的,要么充当条件,要么充当数据源

子查询是可以独立存在的语句,是一条完整的 select 语句

先执行子查询语句

总结

SELECT select_expr [,select_expr,...] [

FROM tb_name

[JOIN 表名]

[ON 连接条件]

[WHERE 条件判断]

[GROUP BY {col_name | postion} [ASC | DESC], ...]

[HAVING WHERE 条件判断]

[ORDER BY {col_name|expr|postion} [ASC | DESC], ...]

[ LIMIT {[offset,]rowcount | row_count OFFSET offset}] ]