查询
1. 关键字大写
2. 根据逻辑换行
3. AND, OR这种放于行首,便于增删条件
4. 表简称,增加可读性
举个例子:
SELECT p.product_id
FROM Products AS p
WHERE p.low_fats = 'Y'
AND p.recyclable = 'Y'
;
5. NULL值无法与确定的值作比较,用is NULL或is not NULL判断
举个例子:
SELECT name FROM customer WHERE referee_id <> 2 OR referee_id IS NULL;
SELECT name FROM customer WHERE referee_id != 2 OR referee_id IS NULL;
select name from customer
where id not in (select id from customer where referee_id=2)
6. 主键索引+子查询
举个例子:注意最后一排还是写world而非w
SELECT w.name, w.population, w.area
FROM world as w
WHERE w.name IN
(SELECT w.name FROM world WHERE w.area >= 3000000 OR w.population >= 25000000);
7. DISTINCT 去重
8. ORDER BY DESC 降序;ASC 升序
9. length: 计算字段的长度,一个汉字算三个字符,一个数字或字母算一个字符;char_length:不管汉字还是数字或者是字母都算是一个字符
连接
LEFT JOIN: 以左表为准,返回左表所有行,返回右表中与左表匹配的行,如没有,则返回NULL;然后ON接条件
?几种连接的区别
自连接