查询排序

顺序

SELECT * FROM `website` ORDER BY `id` ASC;
SELECT * FROM `runoob_tbl` ORDER BY `runoob_submission_date` ASC;

倒序

SELECT * FROM `website` ORDER BY `id` DESC;
SELECT * FROM `runoob_tbl` ORDER BY `runoob_submission_date` DESC;

去重

单字段去重

SELECT DISTINCT `runoob_submission_date` FROM `runoob_tbl`;

分组去重

SELECT * FROM `runoob_tbl` GROUP BY `runoob_submission_date`;
SELECT * FROM `runoob_tbl` GROUP BY `runoob_submission_date` ORDER BY `runoob_submission_date` ASC;

计数

计数（不含null）

SELECT COUNT(`runoob_submission_date`) AS 'total' FROM `runoob_tbl`;

去重计数（不含null）

SELECT COUNT(DISTINCT `runoob_submission_date`) AS 'total' FROM `runoob_tbl`;

分组计数（含null）

SELECT `runoob_submission_date` AS 'date', COUNT(*) as 'count' FROM `runoob_tbl` GROUP BY `runoob_submission_date`;

表数据总条目数

SELECT COUNT(*) AS 'total' FROM `runoob_tbl`;

计数（高数量级）
百万级计数参考方法

条件查询

WHERE条件子句

SELECT * FROM `runoob_tbl` WHERE `runoob_id` = 1;
SELECT * FROM `runoob_tbl` WHERE `runoob_author` LIKE '%.com%';
SELECT * FROM `runoob_tbl` WHERE `runoob_submission_date` < "2015-05-02 00:00:00";

组合查询

不带重复的组合查询

SELECT `country` FROM `website`
UNION
SELECT `country` FROM `apps`
ORDER BY `country`;

带重复的组合查询

SELECT `country` FROM `website`
UNION ALL
SELECT `country` FROM `apps`
ORDER BY `country`;

联合查询

SELECT 
    a.teacher_name,
    a.teacher_grader,
    c.course_name,
    b.class_name
FROM
    t_teacher a
        INNER JOIN
    t_class b
        INNER JOIN
    t_course c
        INNER JOIN
    t_teacher_class z ON z.teacher_id = a.id
        AND z.class_id = b.id
        AND a.course_id = c.id
WHERE
    a.teacher_name LIKE '%董%';