SQL里的操作符

• 比较操作符
  • 等于（=）
  • 不等于（<>）
  • 大于（>）
  • 小于（<）
• 逻辑操作符
  • IS NULL
  • BETWEEN
  • EXISTS
  • IN
  • LIKE
  • UNIQUE
  • ALL和ANY
  • AND
  • OR
• 求反操作符
  • <>, != (NOT EQUAL)

SELECT * FROM PRODUCTS_TBL WHERE COST != 29.99;
SELECT * FROM PRODUCTS_TBL WHERE COST <>29.99;

• • NOT BETWEEN

SELECT * FROM PRODUCTS_TBL WHERE COST BETWEEN 5.95 AND 14.95;
SELECT * FROM PRODUCTS_TBL WHERE COST NOT BETWEEN 5.95 AND 14.95;

• • NOT IN

SELECT * FROM PRODUCTS_TBL WHERE PROD_ID IN('119','13','87','9');
SELECT * FROM PRODUCTS_TBL WHERE PROD_ID NOT IN('119','13','87','9');

• • NOT LIKE

SELECT * FROM PRODUCTS_TBL WHERE PROD_DESC LIKE 'L%';
SELECT * FROM PRODUCTS_TBL WHERE PROD_DESC NOT LIKE 'L%';

• • NOT EXISTS

SELECT MAX(COST) FROM PRODUCTS_TBL WHERE NOT EXISTS( SELECT COST FROM PRODUCTS_TBL WHERE COST > 100);
SELECT MAX(COST) FROM PRODUCTS_TBL WHERE EXISTS(SELECT COST FROM PRODUCTS_TBL WHERE COST > 100);

• • IS NOT NULL

SELECT EMP_ID, LAST_NAME, FIRST_NAME, PAGER FROM EMPLOYEE_TBL WHERE PAGER IS NOT NULL;
SELECT EMP_ID, LAST_NAME, FIRST_NAME, PAGER FROM EMPLOYEE_TBL WHERE PAGER IS NULL;

• • NOT EQUAL
• 算术操作符
  • +(加法）
  • -（减法）
  • *（乘法）
  • / （除法）