SQLite 基础13

sqlite> SELECT * FROM COMPANY WHERE AGE <= 22 and SALARY >= 40000;
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0   
sqlite> SELECT * FROM COMPANY WHERE AGE <= 22 or SALARY >= 40000;
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
4           Mark        25          Rich-Mond   65000.0   
5           David       27          Texas       85000.0   
6           Kim         22          South-Hall  45000.0   
sqlite> SELECT * FROM COMPANY WHERE AGE IS NOT NULL;
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0   
2           Allen       25          Texas       15000.0   
3           Teddy       23          Norway      20000.0   
4           Mark        25          Rich-Mond   65000.0   
5           David       27          Texas       85000.0   
6           Kim         22          South-Hall  45000.0   
7           James       24          Houston     10000.0   
sqlite> SELECT * FROM COMPANY WHERE AGE is null;
sqlite> SELECT * FROM COMPANY WHERE NAME LIKE 'P%';     
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0   
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'T%';
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Ki*';
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
6           Kim         22          South-Hall  45000.0   
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'Te%';
sqlite> SELECT * FROM COMPANY WHERE NAME GLOB 'T*'; 
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
3           Teddy       23          Norway      20000.0   
sqlite> SELECT * FROM COMPANY WHERE AGE IN ( 24,32);
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0   
7           James       24          Houston     10000.0   
sqlite> SELECT * FROM COMPANY WHERE AGE BETWEEN 25 and 32;
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0   
2           Allen       25          Texas       15000.0   
4           Mark        25          Rich-Mond   65000.0   
5           David       27          Texas       85000.0   
sqlite> SELECT AGE FROM COMPANY WHERE EXISTS (SELECT AGE FROM COMPANY WHERE SALARY > 65000);
age       
----------
32        
25        
23        
25        
27        
22        
24        
sqlite>  SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY > 15000);  
sqlite> SELECT * FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY <15000); 
id          name        age         address     salary    
----------  ----------  ----------  ----------  ----------
1           Paul        32          California  20000.0   
2           Allen       25          Texas       15000.0   
4           Mark        25          Rich-Mond   65000.0   
5           David       27          Texas       85000.0   
sqlite>