Android SQLite SQL语句高级进阶

本篇博客使用《SQL必知必会》中的SQL知识讲解sql语句，并使用书中自带的SQLite数据库文件。

该数据库文件中共五张表：

本篇博客的sql语句均是操作的tysql数据库文件中的这五张表。

数据库文件下载

SELECT 语句：

SELECT prod_name FROM Products;--执行单列检索

prod_name---------------------8 inch teddy bear12 inch teddy bear18 inch teddy bearFish bean bag toyBird bean bag toyRabbit bean bag toyRaggedy AnnKing dollQueen doll

SELECT prod_id , prod_name FROM Products;--执行多列检索

prod_idprod_name----------------------------------BR018 inch teddy bearBR0212 inch teddy bearBR0318 inch teddy bearBNBG01Fish bean bag toyBNBG02Bird bean bag toyBNBG03Rabbit bean bag toyRGAN01Raggedy AnnRYL01King dollRYL02Queen doll

SELECT * FROM Products;--检索所有列

得到的结果是表中的所有数据，这里就不贴出来了。

SELECT DISTINCT vend_id From Products;--执行去重检索

vend_id---------BRS01DLL01FNG01

SELECT prod_name FROM Products LIMIT 5;--检索prod_name列的前五行数据

prod_name------------------8 inch teddy bear12 inch teddy bear18 inch teddy bearFish bean bag toyBird bean bag toy

SELECT prod_name FROM Products LIMIT 5 OFFSET 2;--检索prod_name列的五行数据从第三行开始--SELECT prod_name FROM Products LIMIT 2 , 5;   --检索prod_name列的五行数据从第三行开始

prod_name---------------------18 inch teddy bearFish bean bag toyBird bean bag toyRabbit bean bag toyRaggedy Ann

SELECT prod_name FROM Products ORDER BY prod_name ASC;--检索prod_name并按照prod_name升序排序

prod_name---------------------12 inch teddy bear18 inch teddy bear8 inch teddy bearBird bean bag toyFish bean bag toyKing dollQueen dollRabbit bean bag toyRaggedy Ann

SELECT prod_name , prod_price FROM Products ORDER BY prod_price DESC;--检索prod_name和prod_price并按照prod_price降序排序

prod_nameprod_price--------------  --------------------18 inch teddy bear11.99King doll9.49Queen doll9.4912 inch teddy bear8.998 inch teddy bear5.99Raggedy Ann4.99Fish bean bag toy3.49Bird bean bag toy3.49Rabbit bean bag toy3.49

SELECT prod_name ,prod_price FROM Products ORDER BY prod_price DESC , prod_name ASC;--检索prod_name和prod_price按照prod_price降序prod_name升序排序

prod_nameprod_price-------------  --------------------18 inch teddy bear11.99King doll9.49Queen doll9.4912 inch teddy bear8.998 inch teddy bear5.99Raggedy Ann4.99Bird bean bag toy3.49Fish bean bag toy3.49Rabbit bean bag toy3.49

WHERE子句操作符：

操作符说明-----------=等于<>不等于!=不等于<小于<=小于等于>大于>=大于等于BETWEEN两值之间IS NULL为NULL值

SELECT prod_price FROM Products WHERE prod_price > 5;--检索prod_price大于5的所有prod_price

prod_price------------5.998.9911.999.499.49

SELECT prod_name , prod_price FROM Products WHERE prod_price BETWEEN 3.0 AND 3.5;--检索prod_price大于3.0小于3.5的所有prod_name和prod_price

prod_nameprod_price------------------------Fish bean bag toy3.49Bird bean bag toy3.49Rabbit bean bag toy3.49

SELECT cust_name FROM Customers WHERE cust_email IS NULL;--检索cust_email为NULL值的cust_name

cust_name------------Kids PlaceThe Toy Store

SELECT cust_name , cust_email FROM Customers WHERE NOT cust_email IS NULL;--检索cust_email不为NULL值的cust_name和cust_email

cust_namecust_email------------------------Village [e-mail][e-mail][e-mail]

SELECT prod_name , prod_price FROM Products WHERE vend_id = 'DLL01' AND prod_price < 4;--检索vend_id为DLL01并且prod_price小于4的prod_name和prod_price

prod_nameprod_price-----------------------Fish bean bag toy3.49Bird bean bag toy3.49Rabbit bean bag toy3.49

SELECT vend_id , prod_price FROM Products WHERE vend_id = 'FNG01' OR prod_price = 5.99;--检索vend_id为FNG01或者prod_price等于5.99的vend_id和prod_price

vend_idprod_price-------------------BRS015.99FNG019.49FNG019.49

SELECT vend_id , prod_price FROM Products WHERE vend_id IN ('BRS01' , 'FNG01');--检索vend_id为BRS01或者FNG01的vend_id和prod_price--等同于SELECT vend_id , prod_price FROM Products WHERE vend_id = 'BRS01' OR vend_id = 'FNG01';

vend_idprod_price-------------------BRS015.99BRS018.99BRS0111.99FNG019.49FNG019.49

SELECT vend_id , prod_price FROM Products WHERE (vend_id = 'BRS01' OR vend_id = 'FNG01') AND prod_price > 9;--检索vend_id为BRS01或者FNG01并且prod_price大于9的vend_id和prod_price

vend_idprod_price----------------BRS0111.99FNG019.49FNG019.49

SQL 通配符

% 表示任何字符出现任意次数(可以匹配任何字符串)
_ 表示任何单个字符(可以匹配任何单个字符)

SELECT prod_name , prod_price FROM Products WHERE prod_name LIKE 'R%';--检索以R开头的prod_name及其对应的prod_price

prod_nameprod_price---------------------Rabbit bean bag toy3.49Raggedy Ann4.99

SELECT cust_name , cust_email FROM Customers WHERE cust_email LIKE '%';--检索所有cust_email不为NULL的cust_name和cust_email--%特性：可以匹配除NULL之外的所有字符串(包括空串)

cust_namecust_email----------------------Village [e-mail][e-mail][e-mail]

SELECT prod_name FROM Products WHERE prod_name LIKE '__ inch teddy bear';--检索所有以inch teddy bear结尾并在其前有两个任意字符的prod_name--_能且仅能匹配一个字符，不能多亦不能少。

prod_name------------12 inch teddy bear18 inch teddy bear

SELECT prod_name FROM Products WHERE prod_name LIKE '% inch teddy bear';--检索所有以inch teddy bear结尾的prod_name

prod_name------------8 inch teddy bear12 inch teddy bear18 inch teddy bear