mysql-3 检索数据(1)

2018-06-17 23:12:15来源:未知 阅读 ()

新老客户大回馈,云服务器低至5折

SELECT 语句

SELECT检索表数据,必须至少给出两条信息--------想选择什么,以及从什么地方选择。

检索一个列 

SELECT prod_name FROM products;

上述语句利用SELECT语句从products表中检索一个名为prod_name的列

检索多个列

SELECT prod_id,prod_name,prod_price FROM products;

检索所有的列

SELECT  * FROM products;

检索不同的行

SELECT vend_id FROM products;

select返回了14行,但是表中只有4个供应商,因为products表中列出了14个产品,如何检索有不同值的列表

SELECT DISTINCT vend_id FROM products;

限制结果

SELECT prod_name FROM products LIMIT 5;

 SELECT prod_name FROM products LIMIT 5 , 5;

从第五行开始输出5行

第一个数位开始位置,第二个数为检索的行数

 

SELECT prod_name FROM products LIMIT 4 OFFSET 3;

从第三行开始,输出四行

 

使用完全限定输出表名

SELECT products.prod_name FROM products;

 

排序检索数据

排序数据

SELECT prod_name FROM products ORDER BY prod_name;

mysql对prod_name列以字母顺序排序

按多个排序

SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price , prod_name;

首先按照价格然后按照名称排序     作用,当价格相同的时候,会再按照名称来排序。

如果prod_price列中的数据所有值都是唯一的,则不会按照prod_name来排序

制定排序方向

ORDER BY默认是按照升序的方式排列,如果进行降序排列,就必须标出 DESC;

SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC;

SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC, prod_name;

先按照价格从高到底排序,价格相同的再按照名称升序排列。

与DESC相反的是ASC,ASC没有多大作用,因为默认就是ASC升序排列。

 

SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 2;

找出两个最贵的物品的值

 

过滤数据

使用where子句

SELECT prod_name ,prod_price FROM products WHERE prod_price = 2.50;

查询价格等于2.50的商品名字和价格

      操作符           说明
      =           等于
     <>          不等于
     !=          不等于
      <           小于
      >           大于
      <=           小于等于
      >=           大于等于
     BETWEEN           在制定的两个值之间


检查单个值

SELECT prod_name ,prod_price FROM products WHERE prod_name = 'fuses';

SELECT prod_name,prod_price FROM products WHERE prod_price <10;

 SELECT vend_id , prod_name FROM products WHERE vend_id <> 1003;

查询供应商不是1003制造的所有产品

 SELECT prod_name , prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

查询价格在5和之间的商品名称和价格

 

数据过滤

组合where子句

SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1003 AND prod_price <=10;

SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1002 OR vend_id = 1003;

计算次序

SELECT prod_name , prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >=10;

从上面的结果来看,有两个值的结果小于10,显然返回的结果未按照预期的进行过滤,为什么会这样呢,原因是在于计算的次序,SQL在处理OR操作符前,优先处理AND操作符,当SQL看到上述where子句时,它理解为由供应商1003制造的任何价格都为10美元以上,包含10美元,或者由供应商1002提供的产品,不管价格。换句话说,由于AND的计算次序中优先级更高,操作符被错误的组合了。正确的操作方法:

SELECT prod_name , prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10;

 

IN 操作符

 IN操作符来制定条件范围,范围中的每个条件都可以进行匹配。

SELECT prod_name , prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;

IN和OR的用法有点类似,上面也可以这样写:

SELECT prod_name , prod_price FROM products WHERE vend_id=1002 OR vend_id=1003 ORDER BY prod_name;

 

NOT操作符

where子句中的not操作符的用法只有一个,那就是否定后它之后所有的任何条件

SELECT prod_name , prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

 

 

上面用到的所有查询命令

SELECT prod_name FROM products;
SELECT prod_id,prod_name,prod_price FROM products;
SELECT * FROM products;
SELECT vend_id FROM products;
SELECT DISTINCT vend_id FROM products;
SELECT prod_name FROM products LIMIT 5;
SELECT prod_name FROM products LIMIT 5 , 5;
SELECT prod_name FROM products LIMIT 1 , 1;
SELECT prod_name FROM products LIMIT 4 OFFSET 3;
SELECT products.prod_name FROM products;
SELECT prod_name FROM products ORDER BY prod_name;
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price , prod_name;
SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC;
SELECT prod_id , prod_price ,prod_name FROM products ORDER BY prod_price DESC, prod_name;
SELECT prod_price FROM products ORDER BY prod_price DESC LIMIT 2;
SELECT prod_name ,prod_price FROM products WHERE prod_price = 2.50;
SELECT prod_name ,prod_price FROM products WHERE prod_name = 'fuses';
SELECT prod_name,prod_price FROM products WHERE prod_price <10;
SELECT vend_id , prod_name FROM products WHERE vend_id <> 1003;
SELECT prod_name , prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1003 AND prod_price <=10;
SELECT prod_id , prod_price ,prod_name FROM products WHERE vend_id =1002 OR vend_id = 1003;
SELECT prod_name , prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >=10;
SELECT prod_name , prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >=10;
SELECT prod_name , prod_price FROM products WHERE vend_id IN (1002,1003) ORDER BY prod_name;
SELECT prod_name , prod_price FROM products WHERE vend_id=1002 OR vend_id=1003 ORDER BY prod_name;
SELECT prod_name , prod_price FROM products WHERE vend_id NOT IN (1002,1003) ORDER BY prod_name;

 

标签:

版权申明:本站文章部分自网络,如有侵权,请联系:west999com@outlook.com
特别注意:本站所有转载文章言论不代表本站观点,本站所提供的摄影照片,插画,设计作品,如需使用,请与原作者联系,版权归原作者所有

上一篇:mysql数据库学习目录

下一篇:前端学数据库之子查询