SQL 查询

1.拼接---MySQL使用concat,oracle使用||，SQL server使用+  
#MySQL
select NAME,AGE,concat( NAME, '-' , AGE) as truename from test_employee;
select concat( NAME,AGE) as truename from test_employee;

#oracle
select NAME||'('||AGE||')' as truename from test_employee;

#SQL server
select NAME+'('+AGE+')' as truename from test_employee;

2。计算 加+，减-，乘*，除/
select a.AGE/a.SALARY as truename from test_employee a;
select a.AGE+a.SALARY as truename from test_employee a;
select a.AGE*a.SALARY as truename from test_employee a;
select a.AGE-a.SALARY as truename from test_employee a;

#
select LENGTH(a.AGE) as truename from test_employee a;

select sum(a.AGE) as truename from test_employee a;

select avg(a.AGE) as truename from test_employee a;

3.分组
select a.name ,COUNT(*) as truename from test_employee a GROUP BY a.name;


#having 和where 都是用来筛选用的  
having 是筛选组  而where是筛选记录
他们有各自的区别
1》当分组筛选的时候 用having
2》其它情况用where
用having就一定要和group by连用，
用group by不一有having （它只是一个筛选条件用的）

where在分组前过滤，having在分组后过滤。
select a.name ,COUNT(*) as truename from test_employee a GROUP BY a.name HAVING COUNT(*)>2;

4.作为子查询的select语句只能查询单个列，企图返回多个列会有错误
select *  from test_employee a where a.NAME in (SELECT name from test_student b  WHERE b.name='test');

select *  from test_employee a where a.NAME in ('test','scott');

select *  from test_employee a where a.NAME in (SELECT name from test_student b  WHERE b.sex='0');

5.

#条件相等的查询为内部连接

内连接: 只连接匹配的行

select *  from test_employee a  INNER JOIN test_student b on a.id =b.id;

#左连接

左外连接: 包含左边表的全部行（不管右边的表中是否存在与它们匹配的行），以及右边表中全部匹配的行

select *  from test_employee a  left JOIN test_student b on a.id =b.id;

#右连接

右外连接: 包含右边表的全部行（不管左边的表中是否存在与它们匹配的行），以及左边表中全部匹配的行

select *  from test_student a  RIGHT  JOIN test_employee b on a.id =b.id;

#全连接

全外连接: 包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行。
交叉连接  生成笛卡尔积－它不使用任何匹配或者选取条件，而是直接将一个数据源中的每个行与另一个数据源的每个行都一一匹配

select *  from  test_employee   full  JOIN test_student  ;