1、內(nèi)連接查詢
SELECT suppliers.s_id, s_name, f_price FROM tb_name INNER JOIN tb_name1 ON frutis.s_id = suppliers.s_id;
2块促、外連接-左連接
SELECT customers.c_id, orders.o_num FROM customers LEFT OUTER JOIN orders ON customers.c_id=orders.c_id;
3荣堰、外連接-右連接
SELECT customers.c_id, orders.o_num FROM customers RIGHT OUTER JOIN orders ON customers.c_id=orders.c_id;
4、符合條件連接查詢
SELECT suppliers.s_id, s_name, f_price FROM tb_name INNER JOIN tb_name1 ON frutis.s_id = suppliers.s_id AND frutis.c_id=10001;
SELECT suppliers.s_id, s_name, f_price FROM tb_name INNER JOIN tb_name1 ON frutis.s_id = suppliers.s_id ORDER BY suppliers.s_id;
5竭翠、帶ANY振坚、SOME關(guān)鍵字的子查詢
SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
6、帶ALL關(guān)鍵字的的子查詢
SELECT num1 FROM tbl1 num1 > ALL (SELECT num2 FROM tbl2);
7斋扰、帶EXISTS關(guān)鍵字的子查詢
SELECT * FROM tb_name WHERE EXISTS (SELECT s_name FROM tb_name1 WHERE s_id = 107);
SELECT * FROM tb_name WHERE f_price >10.20 AND EXISTS (SELECT s_name FROM tb_name1 WHERE s_id = 107);
SELECT * FROM tb_name WHERE NOT EXISTS (SELECT s_name FROM tb_name1 WHERE s_id = 107);
8渡八、帶IN關(guān)鍵字的查詢
SELECT c_id FROM tb_name WHERE o_num IN (SELECT o_num FROM tb_name2 WHERE f_id = 'c0');
SELECT c_id FROM tb_name WHERE o_num IN (1,23);
SELECT c_id FROM tb_name WHERE o_num NOT IN (SELECT o_num FROM tb_name2 WHERE f_id = 'c0');
9、帶比較運(yùn)算符的子查詢
SELECT s_id, f_name FROM tb_name WHERE s_id = (SELECT s1.s_id FROM tb_name2 AS s1 WHERE s1.s_city = 'Tianjin');
10传货、合并查詢結(jié)果
語法格式:
SELECT column,...FROM table1
UNION [ALL]
SELECT column,...FROM table2
UNION進(jìn)行了去重屎鳍，UNION ALL則未去重(效率高于去重)
select user_id,user_nickname,user_status from yy_user where user_status = 1
UNION
select user_id,user_nickname,user_status from yy_user where user_id > 3;
拼接的字段數(shù)量要相同。
