1、内连接查询
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关键字的子查询
SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
6、带ALL关键字的的子查询
SELECT num1 FROM tbl1 num1 > ALL (SELECT num2 FROM tbl2);
7、带EXISTS关键字的子查询
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关键字的查询
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、带比较运算符的子查询
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、合并查询结果
语法格式:
SELECT column,...FROM table1
UNION [ALL]
SELECT column,...FROM table2
UNION进行了去重，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;
拼接的字段数量要相同。
