sql的distinct过滤单个列_数据库知识总结——(二)检索&过滤数据

一.select语句

这里选用的数据库和表来自书上所创建的products表，为了方便大家学习，在此将表结构整理如下：

products表

//（一）检索单列
Select prod_name from products; //从products表中检索名为prod_name的列。
//此处是未排序的数据，因此返回表中所有行，这些数据没有过滤也没有排序。

//（二）检索多列
Select prod_id, prod_name, prod_price From products; //从products表中检索多列。

//（三）检索所有列
Select * from products; //表示返回表中所有列。但可能导致顺序的变化。

//（四）检索不同的行
// DISTINCT关键字得到不同值的列表。
Select DISTINCT vend_id from products;//得到表中值不同的供应商ID，只返回4行。

//（五）限制结果
// limit限制检索结果
Select prod_name from products limit 5; //检索单个列，取前5行。

//取下一个5行，第一个为开始位置，第二个为检索行数。即从第5行开始的5行。
Select prod_name from products limit 5，5; 

//（六）使用完全限定的表名
//有一些情景需完全限定名，以便在遇到的时候知道它的作用。
Select products.prod_name from products;

二.检索排序数据

//Order By子句用来根据排序需要检索出数据。

//按prod_name的顺序对商品进行排序。其中order by指示以字母顺序排序的数据。
Select prod_name from products order by prod_name;

//（一）按多列排序
//检索三个列，其中两个列对结果进行排序——首先是价格，其次名称。
Select prod_id,prod_price,prod_name 
from products 
order by prod_price,prod_name;

//（二）指定排序方向
//为了降序排序，必须指定desc关键字。DESC只应用到直接位于前面的列名
//若想在多个列上进行降序，必须对每个列指定DESC关键字。
//与DESC相反的关键字是ASC，可在升序时可指定。不过升序是MySQL默认的。

//按照产品价格降序检索，显示商品ID，商品价格，商品名。
Select prod_id, prod_price, prod_name 
from products 
order by prod_price DESC;

//先按产品价格降序排序，再按产品名排序（默认升序）检索，显示商品ID，商品价格，商品名。
Select prod_id, prod_price, prod_name 
from products 
order by prod_price DESC,prod_name;

//使用order by和limit能够找到一列中的最大或最小值。
// Order by子句应位于from子句后，若使用limit，应位于order by后。

//检索最贵的商品。
Select prod_price 
from products 
order by prod_price desc limit 1;

三.过滤数据

（1）where子句

数据库通常只会根据特定操作或需求提取表数据的子集，检索所需的数据需指定搜索条件，也称过滤条件。根据where字句中指定的搜索条件进行过滤，where子句的操作符有 =等于； <>、!= 不等于； <=、< 小于等于、小于； >=、> 大于等于、大于； BETWEEN介于两个值之间。

//（一）匹配检查
//where子句在表名后给出，order by子句应位于where之后。
//检索并返回商品价格为2.5的商品名和商品价格。
Select prod_name,prod_price 
from products 
where prod_price=2.50;

//检索并返回商品名为fuses的商品名和商品价格。
//何时用引号：单引号用来限定字符串。如果将值与串类型的列比较，则需要限定引号；数值列比较则无需引号。
Select prod_name, prod_price 
from products 
where prod_name=’fuses’;

//检索并返回商品价格小于10的商品名和商品价格。
Select prod_name,prod_price 
from products 
where prod_price < 10; 

//检索并按商品价格降序返回商品价格小于等于10的商品名和商品价格。
Select prod_name,prod_price 
from products 
where prod_price <= 10 order by prod_price;

//（二）不匹配检查
//检索并返回供应商ID不是1003的供应商ID和商品名称。
Select vend_id, prod_name 
from products 
where vend_id <> 1003;

//（三）范围值检查
// Between语法需要两个值，即范围的开始值与结束值，中间用AND分割。
// Bewteen匹配的值包括指定的开始值和结束值。

// 检索并返回商品价格在5到10之间的商品名和商品价格。
Select prod_name,prod_price 
from products 
where prod_price between 5 and 10;

//（四）空值检查
// NULL与字段包含0，空字符串或仅仅包含空格不同。
// 在过滤数据时，一定要验证返回的数据中确实给出了被过滤的列具有null的。

//检索并返回没有价格的产品。因为表中没有这样的行，所以没有返回数据。
Select prod_name 
from products 
where prod_price is NULL;

//检索顾客表中没有电子邮件地址的顾客id。
Select cust_id 
from customers 
where cust_email is null; 

（2）组合where子句

为了进行更强的过滤控制，MySQL允许给出多个where子句，可以两种方式使用：以AND子句方式或OR子句方式使用。

//操作符：用来联结或改变where子句中子句的关键字。

//（一）AND操作符：用来指示检索满足所有给定条件的行。
//检索并返回供应商ID为1003且价格小于等于10的所有商品ID、商品价格和商品名。
Select prod_id,prod_price,prod_name 
from products 
where vend_id=1003 and prod_price <= 10;

//（二）OR操作符：用来指示检索匹配任一条件的行。
//检索并返回由任一个指定供应商制造的的商品价格和商品名。
Select prod_name, prod_price 
from products 
where vend_id=1002 or vend_id=1003;

//这里需注意计算次序，SQL语言在处理or之前，优先处理and操作符。
//用括号分组相应操作符，括号具有比AND,OR高的计算次序。
//任何时候使用具有AND和OR的where子句，都应该使用圆括号明确分组操作符。

/检索并返回价格在10以上且由1002或1003制造的商品名和商品价格。
Select prod_name,prod_price 
from products 
where (vend_id=1002 or vend_id=1003) and prod_price >= 10;


//（三）IN操作符：In操作符用来指定条件范围，范围中每个条件都可以进行匹配。
// In后跟由逗号分隔的合法值清单，且必须括在圆括号中。

// 检索并返回1002和1003制造的商品名和商品价格。
Select prod_name，prod_price 
from products 
where vend_id in (1002,1003) order by prod_name; 

// 使用In操作符的好处：
//（1）In的语法更清楚且直观
//（2）计算次序更容易管理
//（3）In一般比or清单执行更快
//（4）In可以包含其他select语句


//（四）NOT操作符:否定它之后所跟的任何条件，是where子句中用来否定后跟条件的关键字。
// MySQL支持not对in,between和exists的子句取反。

//检索并按商品名称排序返回不是1002和1003供应的商品名称和价格。
Select prod_name,prod_price 
from products 
where vend_id not in (1002,1003) order by prod_name;

（3）用通配符进行过滤

前面介绍的所有操作符都是针对已知值进行过滤的。不管是匹配一个还是多个值，测试大于还是小于已知值，或者检查某个范围的值，其共同点是过滤中使用的值都是已知的。但是如何检索商品名称中带有jet或者anvil的所有商品？这里必须使用通配符。利用通配符可创建比较特定数据的搜索模式。

// 通配符：用来匹配值的一部分的特殊字符。
// 搜索模式：由字面值，通配符或二者组合构成的搜索条件。
// like操作符：为在搜索子句中使用通配符，必须使用like操作符。
// like指示MySQL后跟的搜索模式利用通配符匹配而不是直接相等匹配进行比较。

//通配符搜索的处理要比其他搜索所花时间更长，因此使用通配符的技巧有：
//（1）不要过度使用通配符，若其他操作符能达到相同目的，应使用其他操作符。
//（2）不要把通配符用在搜索模式的开始处，搜索起来最慢。
//（3）注意通配符放置的位置。


//（一）百分号（%）通配符：%表示任何字符出现任意次数。
//通配符可以在搜索模式中任意位置使用，并使用多个通配符。

//检索任意以jet开头的词，其中搜索模式为jet%,搜索模式是可以区分大小写的
Select prod_id,prod_name 
from products 
where prod_name like 'jet%';

//搜索模式%anvil%匹配任何位置包含文本anvil的值，无论前后是什么字符。
Select prod_id,prod_name 
from products 
where prod_name like '%anvil%';

//检索以s开头e结尾的所有产品名。
Select prod_name 
from products 
where prod_name like 's%e';


//（二）下划线（_）通配符：_只匹配单个字符而非多个。

//这里不返回值.5，因为搜索模式要求匹配两个通配符而不是一个。
Select prod_name 
from products 
where prod_name like '_ ton anvil';

//希望三个全部匹配则用%通配符。
Select prod_name 
from products 
where prod_name like '% ton anvil';

参考书籍:《MySQL必知必会》