1-1,普通选择语句:SELECT name FROM Products ; 在Products表中检索name的列。一般与where连用。
如:SELECT name FROM Products WHERE id = 1;,在Products表中检索列为id的的name,id =1的有几条数据,就显示几条数据。
1-2,WHERE子句操作:WHERE还可以用其他操作符,比如‘>='(大于等于),‘<=’(小于等于),'IS NULL'(不为空)等
SELECT prod_name, prod_price
FROM Products
WHERE prod_price < 10; 从Products表中,检索prod_price小于10的prod_name和prod_price
我们看下一个,BETWEEN子句操作:
SELECT prod_name, prod_price
FROM Products
WHERE prod_price BETWEEN 5 AND 10; 从Products表中,检索prod_price在5和10之间的prod_name和prod_price
NOT NULL子句操作:
SELECT prod_name, prod_price
FROM Products
WHERE prod_price IS NOT NULL; 从Products表中,检索prod_price不是空的prod_name和prod_price
where的子句操作还有很多,可以自己去了解
1-3,SELECT排序检索
SELECT prod_name
FROM Products
ORDER BY prod_name WHERE prod -price = 50; 老样子,这是排序操作,按升序排列proe_price为50的数据
降序:
SELECT prod_name
FROM Products
ORDER BY prod_name DESC WHERE prod -price = 50;
多列排序:
SELECT prod_id, prod_price, prod_name
FROM Products
ORDER BY prod_price DESC, prod_name ;这里就prod_price降序,prod_name 正常多列排序
2-1,高级数据过滤--WHERE子句--OR
SELECT prod_name, prod_price
FROM Products
WHERE vend_id = 'DLL01' OR vend_id = ‘BRS01’; 这个是OR的运用,条件vend_id = 'DLL01' ,或者vend_id = ‘BRS01’,这要是满足其一条件的语句都会筛选出来。还有AND就是两个条件都要满足才行。
2-2,高级数据过滤--WHERE子句--IN
SELECT prod_name, prod_price
FROM Products
WHERE vend_id IN ( 'DLL01', 'BRS01' )
ORDER BY prod_name; 选择vend_id在'DLL01'或'BRS01'里的prod_name, prod_price字段,且按prod_name排序
2-3,高级数据过滤--WHERE子句--NOT
SELECT prod_name
FROM Products
WHERE NOT vend_id = 'DLL01'
ORDER BY prod_name; 如上,筛选vend_id不是'DLL01'的数据,并排序