一.连接查询
1.内连接(join/inner join)
只有匹配时才显示
SELECT P.LastName, P.FirstName, O.OrderNo
FROM Persons P
INNER JOIN Orders O
ON P.Id_P = O.Id_P
ORDER BY P.LastName
2.左连接(LEFT JOIN)
这个好例子把table_name1所有行都显示
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
3.右连接(RIGHT JOIN)
这个好例子把table_name所有行都显示
SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
二.联合查询
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2
union会自动除去name1和name2中重复的部分,如果希望不除重用union all
三.子查询
1where子查询(把内查询当做条件)
查询所有分类为科技的文章标题
"select title from t2 where categori_id =
(select categori_id from t1 where name ='科技')
查询所有成绩都>80分的人名
select distinct name from t3 where name not in
(select name from t3 where score <= 80)
2from子查询(把内查询的结果(内存里的一张表)当作一张临时表)
3exists子查询就是对外层表进行循环,再对内表进行内层查询。和in ()差不多,但是它们还是有区别的。主要是看两个张表大小差的程度。若子查询表大则用exists(内层索引),子查询表小则用in(外层索引)
4.mysql操作符
LIMIT LIKE IN BETWEEN regexp(正则) ORDERBY DESC GROUPBY,