SQL 学习笔记-SELECT

选择指定的 columns

/* choose all columns */SELECT * FROM table /* choose specific columns */SELECT col1, col2 FROM table1

DISTINCT: 选择 coulmns 中所有的 unique value

/* choose unique values in the specifc columns */SELECT DISTINCT col1, col2 FROM table1

WHERE: 有条件的搜寻我们要的 columns

/* Use conditions to choose what I want */SELECT col1, col2 FROM table1 WHERE conditions;/* Example */SELECT first_name, last_name FROM customer WHERE first_name='Jamie' AND last_name='Waugh';

conditions 支援基本运算子及逻辑判断

COUNT: 回传 column 中 row 的数量

/* 显示 table 有几个 row */SELECT COUNT(*) FROM tabel1/* 显示 col1 有几个 row */SELECT COUNT(col1) FROM table1/* 显示 unique 值有多少 */SELECT COUNT(DISTINCT(col1)) FROM table1

LIMIT: 限制回传 row 的数量,方便查看

/* 只显示 5 个 row 就好 */SELECT * FROM table1 LIMIT 5;

ORDER BY: 排序我们所选的 row

/* 根据 first_name 来做递升的排序 */SELECT first_name, last_name FROM customer ORDER BY first_name ASC;/* 根据 first_name 来做递减的排序 */SELECT first_name, last_name FROM customer ORDER BY first_name DESC;/* 可以多重 columns 排序, *//* 先对 fisrt_name 递减排序, 再对 last_name 递升排序 */SELECT first_name, last_name FROM customerORDER BY first_name DESC,last_name ASC;/* PostgreSQL 特有 *//* 可以依据没被 SELECT 的 columns 来进行排序 *//* 其他 SQL engine 如 MySQL 就必须一定要 SELCET*/SELECT first_name FROM customer ORDER BY last_name ASC;

BETWEEN: 选取特定範围值

/* 挑选值介于 8 和 9 之间 */SELECT customer_id,amount FROM paymentWHERE amount BETWEEN 8 AND 9;/* 挑选值不在 8 和 9 之间 */SELECT customer_id,amount FROM paymentWHERE amount NOT　BETWEEN 8 AND 9;/* 时间也可以做挑选, 日期介于 17 号和 18 号之间 */SELECT customer_id,payment_date from paymentWHERE payment_date BETWEEN '2007-02-17' AND '2007-02-18';

IN: 选取所指定的数值群(可不必连续)

/* 只选取 customer id 为 1,3,7 的 row */SELECT customer_id,rental_id,return_date FROM rentalWHERE customer_id in (1,3,7)ORDER BY return_date DESC;/* 选取 customer id 为 1,3,7 之外的 row */SELECT customer_id,rental_id,return_date FROM rentalWHERE customer_id NOT in (1,3,7)ORDER BY return_date DESC;

LIKE: 寻找相似字的匹配

/* 寻找 first_name 中以 Jan 开头的 row */SELECT first_name,last_name FROM customerWHERE first_name LIKE 'Jan%';/* 寻找中间有 er 的名字 */SELECT first_name,last_name FROM customerWHERE first_name LIKE '%er%';/* 寻找不要有 y 结尾的名字 */SELECT first_name,last_name FROM customerWHERE first_name NOT LIKE '%y';/* 寻找 first_name 中以任意 单字 开头, *//* 接着为 an, 且以任意 字串 结尾的 row */SELECT first_name,last_name FROM customerWHERE first_name LIKE '_an%';/* 寻找有 er 的名字, 不管大小写, */SELECT first_name,last_name FROM customerWHERE first_name ILIKE '%er%';