20170729上课笔记

函数在做计算时，会将空值列排出//count(*)时不会排出空值

count()

sum

avg

max

min

 

group by

 

 

select

from

where

group by

having

order by

关键字执行顺序排列

from--where--group by --having--order by--select

 

like %%或like %test 走的是全盘扫描

like test% 走的是索引 相对效率高

多表联合查询

内链接  只查询两表相关联列

select xx.xx ,xx.xx from table1,table2 where ..

select xx.xx,xx.xx from table1 join table1 on ....

外连接 不匹配列也会进行显示

分左外连接和右外连接

左外连接

select e.last_name, d.department_name

from employees e, departments d

where e.department_id=d.department_id(+);

 

select e.last_name, d.department_name

from employees e, left outer join departments d

on e.department_id=d.department_id

 

右外连接

select e.last_name, d.department_name

from employees e, departments d

where e.department_id（+）=d.department_id;

 

select e.last_name, d.department_name

from employees e right outer join departments d

on e.department_id=d.department_id;

 

完全外连接

select e.last_name, d.department_name

from employees e full outer join departments d

on e.department_id=d.department_id

 

多个表链接则

select e.last_name, d.department_name

from employees e right outer join departments d

on e.department_id=d.department_id

right outer join XXXX

on XXX=XXX

 

自然连接 //自动链接查询相同列

select xx.xx,xx.xx from table1 natural join tableb

转载于:https://www.cnblogs.com/guoxf/p/7258288.html