澳门皇冠金沙网站-澳门皇冠844网站

热门关键词: 澳门皇冠金沙网站,澳门皇冠844网站

mysql基础整理,mysql必知必会

第六章《过滤数据》

(一)几个数据库相关的概念

一、汇总数据

P35

1.数据库

工作中经常需要汇总数据而不是将它们全部检索出来(实际数据本身:返回实际数据是对时间和处理资源的浪费),这种类型的检索有以下特点:

  1. select prod_name,prod_price from products where prod_price=2.5;

数据库: 保存有组织数据的容器。

确定表中的行数(或者满足某个条件或包含某个特定值的行数)

2.select prod_name,prod_price from products where prod_price='2.5';

数据的所有存储、检索、管理和处理实际上是有数据库软件DBMS完成的。

获得表中行组的和

#两个语句得到的结果一样,因为指定的是数值。

我们通过数据库软件DBMS来创建和操纵容器。

找出表列(或所有行或某些特定的行)的最大值、最小值和平均值

图片 1

2.表

聚集函数(aggregate function):运行在行组上,计算和返回单个值的函数(MySQL还支持一些列的标准偏差聚集函数);

P36

某种特定类型数据的结构化清单。表名是唯一的,用来标识自己。

SQL聚集函数如下表:

select prod_name,prod_price from products where prod_name='fuses'; #当指定的是文字时候,则要用引号(‘ ’)。

表具有一些特性,定义了数据在表中如何的存储,存储什么样的数据,数据如何分解,各部分信息如何命名等。描述这组信息叫做模式(schema),它是关于数据库和表的布局及特性信息。

图片 2

图片 3

3.列和数据类型

 

select prod_name,prod_price from products where prod_price <10; #检索prod_price小于或等于10(<=不是=<)的,读取prod_name,prod_price列。

列:表中的一个字段

1、avg()函数

图片 4

数据类型:每个列都有相应的数据类型

avg()通过对表中行数计数并计算特定列值之和,求得该列的平均值;avg()可用来返回所有列平均值,也可用来返回特定列的平均值;

 select prod_name,prod_price from products where prod_price <10; #检索prod_price小于10的,读取prod_name,prod_price列。

4.行

select avg(prod_price) as avg_price from pfoducts;

图片 5

表中的一条记录

此select语句返回值avg_price,它包含producs表中所有产品的平均价格,avg_price是一个别名;

P37 不匹配检查(不等于某个值)

5.主键

avg()也可以用来确定特定列或行的平均值,例子如下:

  1. select vend_id,prod_name from products where vend_id <>1003; #vend_id不等于1003# 

一列或者一组列,能够唯一区分表中的每个行。

select avg_(prod_price) as avg_price from products where vend_id = 1003;

2.1. select vend_id,prod_name from products where vend_id !=1003; #vend_id不等于1003# 1和2的表达的意思是一样的# 

习惯上:不更新主键列中的值,不重用主键列的值,不在主键列中使用可能更改的值。

这条SQL语句包含了where子语句,仅过滤出vend_id为1003的产品,avg_price中返回该供应商的产品的平均值;

图片 6

6.外键

PS:avg()只能用来确定特定数值列的平均值,而且列名必须作为函数参数给出,为了获得多个列的平均值,必须使用多个avg()函数{avg()函数忽略列值为NULL的行};

P38

外键为某个表的一列,它包含另一个表的主键值。

 

 select prod_name,prod_price from products where prod_price between 5 and 10; #检索prod_price在范围5到10之内的。注意:数值加上引号与否并不会影响结果#

(二)mysql使用方法

2、count()函数

图片 7

1.mysql是什么?

count()函数进行计数,可利用count()确定表中行的数目或符合特定条件的行的数目;

select prod_name from products where prod_price is null ; #表示检索prod_price是空值的#返回没有结果,因为prod_price没有空值#

mysql是一种DBMS,即是一种数据库软件。它是一种基于客户机-服务器的数据库。

count()函数有两种使用方式:

图片 8

2.mysql的优点:

使用count(*)对表中行的数目进行计数,不管表列中包含的是空值(null)还是非空值;

P39

(1)因为开源,成本低

使用count(column)对特定列中具有值的行进行计数,忽略null值;

select cust_id from customers where cust_email is null ;#检索cust_email是空值#

(2)执行速度快,性能好

select count(*) as num_cust from customers;

图片 9

(3)可信赖

这条SQL语句利用count(*)对customers表中所有行计数,计数值在num_cust中返回;

 

(4)易于安装和使用

select count(cust_email) as cum_cust from customers;

3.连接好数据库后,就可以访问数据库并做操作,其中use是用来选择数据库的,show是用来查看mysql数据库、表、每部信息的。

这条SQL语句使用count(cust_email)对cust_email列中有值的行进行计数;

(1)use database name;

PS:如果指定列名,则指定列的值为空的行被count()函数忽略,但如果count()函数中用的是星号(*),则不忽略; 

      表示切换使用哪个数据库。

 

      用use打开数据库,才能读取其中的数据。

3、max()函数

(2)show database;

max()返回指定列中的最大值,max()要求指定列名,例子如下:

      返回数据库的列表

select max(prod_price) as max_price from products;

(3)show tables;

这条SQL语句中国返回products表中price列的最大值;

      获取一个数据库内的表的列表

PS:MySQL允许max()用来返回任意列中的最大值,包括返回文本列的最大值;但用于文本数据时,如果数据按相应的列排序,则max()返回最后一行(max()函数忽略列值为null的行)

(4)show columns from customers;

 

      显示每列的信息,后面用的比较多的是 desc customers;

4、min()函数

(三)用select检索数据

min()返回指定列的最小值,min()也要求指定列名,例子如下:

1.select:

select min(prod_price) as min_price from products;

用途:从表中检索一个或者多个数据列。

这条SQL语句中min()返回products表中price列最小值;

select语句中需要体现两种信息:选什么,从什么地方选。

PS:MySQL允许min()用来返回任意列中的最小值,包括返回文本列的最小值;但用于文本数据时,如果数据按相应的列排序,则min()返回最前面的行(min()函数忽略列值为null的行)

2.检索单列

 

例如:select prod_name from products;

5、sum()函数

解释:从products表中检索出来prod_name列

sum()函数用来返回指定列值的和(总计);例子如下:

注意:返回结果是未排序的。

select sum(quantity) as items_ordered from orderitems where order_num = 20005;

3.检索多列

函数sum()返回orderitems中所有quantity列的值之和,where子句保证只统计某个指定列的数值;

例如:select prod_id,prod_name,prod_price from products;

PS:利用标准的算数操作符,所有聚集函数都可用来执行多个列上的计算(sum()函数忽略列值为null的行)

4.检索所有列

 

select * from product;

6、distinct与聚集函数

优点:不明确列名的时候使用。

MySQL5.0.3以及之后的版本,聚集函数和distinct可以搭配使用,比如:

5.distinct关键字

对所有的行执行计算,指定all参数或不给参数(all是默认所有行为,不需要指定,如果不指定distinct,则假定为all);

作用:指示mysql只返回不同的值的行

只包含不同的值,指定distinct参数;

例如:select distinct vend_id from products;

如果指定列名,则distinct只能用于count();distinct不能用于count(*),因此不允许使用count(distinct);distinct必须使用列名,不能用于计算或者表达式;

使用:它必须放在列名前面

select avg(distinct prod_price) as avg_price from products where vend_id = 1003;

6.limit

这条SQL语句中,使用avg()函数返回vend列中vend_id=1003的对应的price平均价格,因为使用了distinct参数,因此平均值只考虑不同的值(唯一值)

作用:返回结果的前几行

 

例如:select prod_name from products limit 5;

7、组合聚集函数

如果是 select prod_name from products limit 5,5;则表示从行5开始,检索5行.

select语句可以包含多个聚集函数,比如:

注意:检索出来第一行是行0。如果行数不够,能返回多少就返回多少。

select count(*) as num_items,

7.完全限定的表名

          min(prod_price) as price_min,

select products.prod_name from product;

          max(prod_price) as price_max,

(四)用where进行数据过滤

          avg(prod_price) as price_avg

1、where子句

from products;

作用:指定搜索条件,因为一般数据库表都包含大量的数据,很少我们需要所有的行,通常会根据特定需要来提取数据的子集。where语句就是来指定搜索条件(过滤条件)

这条SQL语句,使用了单条select语句执行4个聚集计算,返回四个值(products表中items的数目、price的最高、最低以及平均值)

位置:放在from之后,order by之前

PS:在指定别名以包含某个聚集函数的结果时,不应该使用表中实际的列名;这样便于使用SQL更加容易和理解,以及排除方便排除错误。

例子:select prod_name,prod_price from products where prod_price = 2.50

 

解释:这里采用了相等测试,只返回prod_price为2.5的行,还可以有等于、不等于、小于、小于等、大于、大于等、between操作符。

二、分组数据

扩展:根据这些操作符,可以做单个值匹配(=)、不匹配检查(!=或者<>)、范围值检查(between)、空值检查(is null)

1、group by创建分组

例子1:between用法,它需要两个值。

在MySQL中,分组是在select语句中的group by子句中建立的,比如:

select prod_name,prod_price from products where prod_price between 5 and 10;

select vend-id,count(*) as num_prods from products group by vend_id;

例子2:空值检查

这条SQL语句指定了2个列,group by指示MySQL按照vend_id排序并且分组(如果使用group by,则不必指定要计算的每个组)

select cust_id from customers where cust_email is null;

group by子句指示指示MySQL分组数据,然后都每个组而不是整个结果集进行聚集;关于group by使用,请注意以下规则:

2.组合where子句

group by子句可以包含任意数目的列(使得对分组进行嵌套,为数据分组提供更细致的控制);

目的:为了进行更强的过滤控制,mysql允许给出多个where自居,以逻辑操作符and或者or的方式使用。

如果在group by子句中嵌套分组,数据将在最后规定的分组上进行汇总,即:建立分组时,指定的所有列都一起计算(所以不能从个别列取回数据);

and例子:

group by子句中列出的每个列都必须是检索列或有效的表达式(但不能是聚集函数),如果在select中使用表达式,则必须在group by子句中指定相同的表达式(不能使用别名);

select prod_id,prod_price,prod_name

除了聚集计算语句外,select中每个列都必须在group by子句中给出;

from products

如果分组列中具有null值,则null将作为一个分组返回(如果列中有多行null值,他们将分为一组);

where vend_id =1003 and prod_price <=10;

group by子句必须出现在where子句之后,order by子句之前;

解释:必须同时满足两个条件

PS:使用with rollup关键字,可以得到每个分组以及每个分组汇总级别(针对每个分组)的值。

or例子:

 

select prod_id,prod_price,prod_name

2、having过滤分组

from products

where子句都可以用having代替,区别在于where过滤行,having过滤分组;having支持所有的where操作符,比如:

where vend_id =1003 or vend_id =1002;

select cust_id,count(*) as orders from orders group by cust_id having count(*) >= 2;

解释:满足任意一个条件即可

这条SQL语句中的having子句过滤count(*)>=2(2个以上的分组)的那些分组;

注意:计算次序用圆括号界定,要不容易混淆。

having和where的区别:

3、in操作

where在数据分组前进行过滤,having在数据分组后进行过滤;where排除的行不包括在分组中(这可能会改变计算值,从而影响having子句中基于这些值过滤掉的分组)

作用:指定条件范围

having和where可以同时使用,比如:

例子:select prod_name,prod_price

select vend_id, count(*) as num_prods from products where prod_price>=10 group by vend_id having count(*)>= 2;

from products

这条SQL语句中,where子句过滤掉所有prod_price至少为10的行,然后按照vend_id分组数据;having子句过滤技术为2或2以上的分组;

where vend_id in (1002,1003);

 

解释:检索供应商1002和1003制造的所有产品。in操作符后面跟着的是合法值得清单。

3、分组和排序

另一种写法:

group by和order by的区别:

select prod_name,prod_price

图片 10

from products

order by的重要性:一般使用group by子句时,应该也给出order by子句,这是保证数据正确性的唯一方法(千万不要依赖group by排序数据)。

where vend_id = 1002 or vend_id = 1003;

 

那么为什么使用in操作符呢,优点是什么呢?

4、select子句顺序

(1)清楚只管

图片 11

(2)计算次序容易理解

 

(3)in执行比or执行的快

(4)在in中可以包含其他的select语句

4、not操作符:

作用:where子句中用来否定后跟条件的关键字。

例如:select prod_name,prod_price

from products

where vend_id not in (1002,1003);

解释:检索除了1002和1003之外的所有。

(五)通配符过滤

1、应用场景

  之前说的数据过滤都是对已知值进行过滤的,比如说匹配一个值或者多个值,大于某个值或者是小于某个值,或者是检查某个范围的值。

  但是如果我要搜索产品名中包含anvil的所有产品呢,这时候通配符就可以大显身手了,我们可以利用通配符搜索模式,找出产品名中任何位置出现anvil的产品。

2、什么是通配符

概念:用来匹配值得一部分的特殊字符

如何使用:为了在搜索子句中使用通配符,必须使用like操作符。

3、有哪些通配符以及如何使用呢?

(一)百分号通配符%

表示:任何字符出现任意次数,也可以是0次

例子:

(1)找到以jet开头的产品,接受jet后面为任意多个字符

select prod_id,prod_name

from products

where prod_name like 'jet%';

(2)匹配任何位置包含anvil,不论在之前还是之后出现什么字符。

select prod_id,prod_name

from products

where prod_name like '%anvil%';

(3)找到以s起头、以e结尾的所有产品:

select prod_name

from products

where prod_name like 's%e';

注意:

(1)可以用‘**%’的形式进行尾空格处理,也可以用trim函数进行处理

(2)%通配符不能匹配null

(二)下划线通配符_

表示:下划线只匹配单个字符而不是多个字符

这也是与%的区别,这里就不举例赘述了。

4、小结:

通配符是一种非常有用的搜索工具,但是不能过度使用,否则搜索时间会很长。

(六)正则表达式匹配

1、啥是正则表达式

正则表达式的作用是匹配文本,将一个正则表达式与一个文本串进行比较,mysql用where子句对正则表达式提供了初步的支持,允许指定正则表达式,过滤select检索出的数据。

2、like与regexp的区别

举个例子来看两者的差别:

(一)like统配符

select prod_name

from products

where prod_name like '1000'

order by prod_name;

结果:不返回数据

(二)正则表达式

select prod_name

from products

where prod_name regexp '1000'

order by prod_name;

结果:返回一行

原因:like匹配的是整个列,只有使用通配符的时候才会返回。而regexp是在列值中匹配,如果被匹配的文本在列值中出现regexp将会找到他,相应的行将被返回。

3、有哪几种匹配呢?

(一)基本字符匹配

例1:检索列prod_name包含文本1000的所有行

select prod_name

from products

where prod_name regexp '1000'

order by  prod_name;

注意:regexp后所跟的东西作为正则表达式处理。

例2:检索列prod_name包含000的所有行

select prod_name

from products

where prod_name regexp '.000'

order by  prod_name;

注意:.是正则表达式语言中的一个特殊的字符,它表示的匹配任意一个字符,所以1000和2000都符合条件。

(二)or匹配

使用:当我想搜索两个串之一时,使用|

例子:匹配prod_name为1000或者2000的情况

select prod_name

from products

where prod_name regexp '1000|2000'

order by prod_name;

(三)匹配几个字符之一

表示:匹配任何一个单一字符,当想匹配特定字符的时候,可通过制定一组用[]括起来的字符来完成。

例1:

select prod_name

from products

where prod_name regexp '[123] Ton'

order by prod_name;

解释:正则表达式是[123] Ton,[123]定义了一组字符,即匹配1或者2或者3,这么看,其实[ ]是另一种形式or语句,也可以看做是[1|2|3]的缩写。

例2:

本文由澳门皇冠金沙网站发布于数据库研究,转载请注明出处:mysql基础整理,mysql必知必会