MySQL查询（待续）

建立索引

ALTER TABLE 表名字 ADD INDEX 索引名 (列名);
CREATE INDEX 索引名 ON 表名字 (列名);

建立视图

CREATE VIEW 视图名(列a,列b,列c) AS SELECT 列1,列2,列3 FROM 表名字;

按多个列排序

SELECT name, species, birth FROM pet ORDER BY species, birth DESC;
#注意DESC关键字仅适用于在它前面的列名(birth)；不影响species列的排序顺序
#birth是在species的升序基础上排序的

使用COUNT(*)和GROUP BY, 按多个列分组排序

SELECT species, sex, COUNT(*) FROM pet GROUP BY species, sex;
#按多个列分组，则以species优先分组

SELECT species, sex, COUNT(*) FROM pet
WHERE species = 'dog' OR species = 'cat'
GROUP BY species, sex;
#此外，可以对COUNT(*)限制条件

选出最大行

SELECT * FROM shop WHERE price=(SELECT MAX(price) FROM shop);
#使用子查询

SELECT * FROM shop ORDER BY price DESC LIMIT 1;
#降序排列且仅列出一行，不需子查询

SELECT @max_price:=MAX(price) FROM shop;
SELECT * FROM shop WHERE price=@max;
#使用用户变量

分组选出最大行

SELECT article, MAX(price) AS price FROM shop GROUP BY article;
#找出每个article对应的最大price

联接两个表查询

SELECT pet.name, (YEAR(date)-YEAR(birth)) - (RIGHT(date,5)<RIGHT(birth,5)) AS age, remark
FROM pet, event
WHERE pet.name = event.name AND event.type ='litter';

连接表自身查询

SELECT p1.name, p1.sex, p2.name, p2.sex, p1.species
FROM pet AS p1, pet AS p2
WHERE p1.species = p2.species AND p1.sex = 'f' AND p2.sex = 'm';

查询非空

death IS NOT NULL
#而非death != NULL，因为NULL是特殊的值，不能使用普通比较符来比较。

查询下个月的数据

select name,birth from pet where month(birth) = month(date_add((curdate()),interval 1 month));
select name,birth from pet where month(birth)=mod(month('1999-2-12'),12)+1;

经过日期计算

TIMESTAMPDIFF(interval,datetime_expr1,datetime_expr2) ;
#返回日期或日期时间表达式 datetime_expr2-datetime_expr1 之间的差值。其结果的单位由interval 参数给出。

计算每个月中某用户访问网页的天数

select year,month,bit_count(bit_or(1<<day)) as days from t1 group by year,month;

bit_count() 
#计算二进制数中包含1的个数

bit_or()
#按位或计算，bit_or(1<<day)相当于把day列每个数据右移N位后进行位或运算; 如day=10，则1右移10位