pg14-sql基础（一）-查询

条件过滤

SELECT *
FROM employees
WHERE employee_id = 100; -- 等于
/*
WHERE employee_id != 100; -- 不等于
WHERE employee_id <> 100; -- 不等于
WHERE hire_date > '2005-01-01'; -- 大于
WHERE hire_date < '2005-01-01'; -- 小于
WHERE hire_date >= '2005-01-01'; -- 大于等于
WHERE salary BETWEEN 10000 AND 12000; -- 之间
WHERE salary IN (10000,11000); -- 之间
*/

模糊查询

SELECT first_name
FROM employees
WHERE first_name LIKE 'S%';

SELECT first_name
FROM employees
WHERE first_name LIKE '%s'; -- 多字符后面一个s
-- SELECT 'xasds' LIKE '%s'; --返回true

SELECT first_name
FROM employees
WHERE first_name LIKE '%s%';
-- SELECT 'vgskl' LIKE '%s%'; -- 返回true

SELECT first_name
FROM employees
WHERE first_name LIKE 's_'; -- s后面单独一个字符
-- SELECT 'sa' LIKE 's_'; -- 返回true

-- 转义，查询包含25%的字段
SELECT 字段1 FROM 表名 WHERE 字段2 LIKE '%25\%'; --默认
SELECT 字段1 FROM 表名 WHERE 字段2 LIKE '%25#%%' ESCAPE #; --自定义

SELECT first_name
FROM employees
WHERE first_name NOT LIKE 'S%'; --区分大小写
-- ILIKE --不区分大小写

空值判断

SELECT * FROM employees WHERE manager_id IS NULL;-- null为缺失值
/*
SELECT * FROM employees WHERE manager_id NOTNULL;
*/

SELECT 1 IS DISTINCT FROM 2; --1与2不同？返回true
SELECT 1 IS DISTINCT FROM 1; --1与1不同？返回false
SELECT 1 IS NOT DISTINCT FROM 1; --双重否定是肯定，1与1相同？返回true
SELECT 1 IS NOT DISTINCT FROM 2; --1与2相同？返回false

SELECT 1 IS DISTINCT FROM null; --DISTINCT支持对空值判断，返回true

SELECT * FROM employees WHERE manager_id IS NOT DISTINCT FROM null; --manager_id为空值

运算符

SELECT *
FROM employees
-- WHERE first_name = 'Steven' AND last_name = 'King';
WHERE first_name = 'Steven' OR last_name = 'King';

SELECT 1=1 OR 1/0=1; --短路运算：返回true
SELECT 1=0 AND 2-1=1; --短路运算：返回fasle

/*
SELECT * FROM employees WHERE salary = 10000 OR salary = 12000 AND department_id = 80;
查找月薪在10000到12000并且部门编号为80的员工。AND优先级比OR高，所以条件应该为salary = 10000 OR (salary = 12000 AND department_id = 80)
*/
(salary = 10000 OR salary = 12000) AND department_id = 80

SELECT * FROM employees WHERE NOT first_name = 'Steven'; 
/*
NOT BETWEEN
NOT LIKE
NOT IN
*/