常用多表联查

测试使用mysql下库的表

// LEFT JOIN ... ON ...
SELECT
    * 
FROM
    admin a
    LEFT JOIN collect c ON a.id = c.id
    
// RIGHT JOIN ... ON ...
SELECT
    * 
FROM
    admin a
    RIGHT JOIN collect c ON a.id = c.id

// JOIN ... ON ...
SELECT
    * 
FROM
    admin a
    JOIN collect c ON a.id = c.id
    
// INNER JOIN ... ON ...
SELECT
    * 
FROM
    admin a
    INNER JOIN collect c ON a.id = c.id
    
// Result: 效果一样，可随意代替

// FULL JOIN ... ON ...不支持MYSQL在ORACLE下测试
SELECT
    * 
FROM
    AAAAAAA A
    FULL JOIN AAAAAAA2 B ON A.CTTYPE = B.CTTYPE

// IN  
SELECT
    * 
FROM
    admin a 
WHERE
    a.id IN ( SELECT id FROM collect)
    
// EXISTS  
SELECT
    * 
FROM
    admin a 
WHERE
    EXISTS ( SELECT id FROM collect WHERE a.id = id )
    
// Result: 效果一样，可随意代替
// 调优:当两个表大小不一时，IN后的外表应该用小表；EXISTS前的内表应该用小表

// ORDER BY ... xx(ASC/DESC)
SELECT
    * 
FROM
    collect 
ORDER BY
    song_id ASC,
    user_id DESC
// 当同时存在升序降序时按照代码中先后写的顺序

//  ... WHERE ... GROUP BY ...  ------主要用于有对象组的时候，如有count()...
SELECT
    id,
    user_id ,
    count(id)
FROM
    collect 
WHERE
    id < 50 
GROUP BY
    user_id
    
// ... GROUP BY ... HAVING ... 
SELECT
    id,
    user_id,
    count( id ) 
FROM
    collect 
GROUP BY
    user_id 
HAVING
    id < 50
    
// 两种写法皆可，查id其实是没有意义的，他显示的永远是第一个user_id的id的值，只有count( id )这个对象组才有意思，表示有多少个相同的user_id的数量
​
// ... GROUP BY ... A,B
SELECT
    user_id,
    id,
    count( * ) 
FROM
    collect 
GROUP BY
    user_id,
    id
意义： 表中 user_id 和 id 都相同的有几个