SQL select查询回顾总结:
select (distinct | all)col1_name col1_subname,col2_name col2_subname,...
from table1_name table1_subname,table2_name table2_subname, (select col1,col2 from table_name ) as table_name(col_name1,col_name1)
where col_name
1. 数字对比,不含通配符&,_的字符对比:= , > , < ,>=,<=,!=...
2.字符对比: like,not like %(任意多个任意字符),_(单个任意字符)
3.集合:in not in(集合)
4.范围:between and not between and
5.空:is null is not null
6.逻辑:and or not
7.where条件中不能包含聚集函数(count(distinct|all col) max() min() avg() sum())
group by col_name having conditions(having 相当于where ,区别是条件中可以有聚集函数)
order by desc|asc (SQL默认升序)
连接查询:
1.等值连接 :所有表的属性列均显示(重复列名带table_name.col_name)
2.自然连接:去掉重复属性列
1.左外连接,列出左边表(table1)中所有元组 :select ... from table1 left outer join table2 on (conditions)
2.右外连接相反
嵌套查询:
1.子查询中不能排序
2.可以同一张表嵌套查询
起别名:空格作为间隔
当所查信息在表中比较少时,建立索引查询,提高查询效率(create unique|cluster index index_name on talble_name(col_name))
----------------------------------------------------------------------------------总结自(王珊 萨师煊 《数据库系统概论》第5版)