SQL语法二

1.join语法
left join (左连接，以左表为主，右表匹配左表，匹配不到为null)
right join (右连接，以右表为主，左表匹配右表，匹配不到为null)
inner join (内连接，以两表共有的相匹配，匹配不到为null)

例1
创建testa表和testb表
create table testa(aid int,aname varchar(100));
create table testb(bid int,bname varchar(100),age int);

a表插入值
insert into testa values(1,‘tianming1’);
insert into testa values(2,‘si1’);
insert into testa values(3,‘ben’);
insert into testa values(4,‘doudou’);
insert into testa values(5,‘yan’);

b表插入值
insert into testb values(1,‘tianming2’,15);
insert into testb values(2,‘si2’,16);
insert into testb values(3,‘ben’,17);
insert into testb values(4,‘doudou’,18);

insert into testb values(7,‘feng’,19);
insert into testb values(8,‘ruoze’,20);
insert into testb values(9,‘jepson’,21);

左连接

select
a.aid,a.aname,
b.bid,b.bname
from
testa a left join testb b on a.aid=b.bid

aid aname bid bname
1 tianming 1 tianming
2 si 2 si
3 ben 3 ben
4 doudou 4 doudou
5 yan null null

left join: 以左表数据为主且 最全,右表去匹配（on后面的字段）左表,匹配不到就null

a<—b

右连接

select
a.aid,a.aname,
b.bid,b.bname
from
testa a right join testb b on a.aid=b.bid;

right join: 以右表数据为主且 最全,左表去匹配（on后面的字段）右表,匹配不到就null
a–>b

1 tianming 1 tianming
2 si 2 si
3 ben 3 ben
4 doudou 4 doudou
null 7 feng
null 8 ruoze
null 9 jepson

内连接

select
a.aid,a.aname,
b.bid,b.bname
from
testa a inner join testb b on a.aid=b.bid;

inner join : 两张表匹配的数据行才显示

1 tianming 1 tianming
2 si 2 si
3 ben 3 ben
4 doudou 4 doudou

---

根据id和name匹配
select
a.aid,a.aname,
b.bid,b.bname
from
testa a left join testb b
on a.aid=b.bid and a.aname=b.bname;

例2
create table emp (
empno numeric(4) not null,
ename varchar(10),
job varchar(9),
mgr numeric(4),
hiredate datetime,
sal numeric(7, 2),
comm numeric(7, 2),
deptno numeric(2)
);

create table dept (
deptno numeric(2),
dname varchar(14),
loc varchar(13)
);

create table salgrade (
grade numeric,
losal numeric,
hisal numeric
);

select

a.ename,a.deptno,b.dname,b.loc

from emp a
left join dept b on a.deptno=b.deptno;

CLARK 10 ACCOUNTING NEW YORK
KING 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
SMITH 20 RESEARCH DALLAS
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
ALLEN 30 SALES CHICAGO
WARD 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
BLAKE 30 SALES CHICAGO
TURNER 30 SALES CHICAGO
JAMES 30 SALES CHICAGO

我想要知道员工的所属部门是什么？在什么地方？所属的工资等级是什么？
select
a.ename,a.deptno,a.sal,IFNULL(a.comm, 0) comm,(a.sal+IFNULL(a.comm, 0)) as salcomm,
b.dname,b.loc
from emp a
left join dept b on a.deptno=b.deptno;
NULL值字段相加还是NULL,用函数IFNULL(a.comm, 0)****

CLARK 10 2450.00 ACCOUNTING NEW YORK
KING 10 5000.00 ACCOUNTING NEW YORK
MILLER 10 1300.00 ACCOUNTING NEW YORK
SMITH 20 800.00 RESEARCH DALLAS
JONES 20 2975.00 RESEARCH DALLAS
SCOTT 20 3000.00 RESEARCH DALLAS
ADAMS 20 1100.00 RESEARCH DALLAS
FORD 20 3000.00 null RESEARCH DALLAS
ALLEN 30 1600.00 300.00 1900.00 SALES CHICAGO
WARD 30 1250.00 500.00 1750.00 SALES CHICAGO
MARTIN 30 1250.00 1400.00 2650.00 SALES CHICAGO
BLAKE 30 2850.00 SALES CHICAGO
TURNER 30 1500.00 0.00 1500.00 SALES CHICAGO
JAMES 30 950.00 SALES CHICAGO

select

a.ename,a.deptno,(a.sal+IFNULL(a.comm, 0)) as salcomm ,
s.grade,
b.dname,b.loc

from emp a
left join dept b on a.deptno=b.deptno
left join salgrade s
on (a.sal+IFNULL(a.comm, 0)) between s.losal and s.hisal;

select * from salgrade;
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999

ALLEN 30 1900.00 3 SALES CHICAGO
WARD 30 1750.00 3 SALES CHICAGO
TURNER 30 1500.00 3 SALES CHICAGO
MARTIN 30 2650.00 4 SALES CHICAGO
CLARK 10 ACCOUNTING NEW YORK
KING 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
SMITH 20 RESEARCH DALLAS
JONES 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
BLAKE 30 SALES CHICAGO
JAMES 30 SALES CHICAGO

SMITH 20 800.00 1 RESEARCH DALLAS
ADAMS 20 1100.00 1 RESEARCH DALLAS
JAMES 30 950.00 1 SALES CHICAGO
MILLER 10 1300.00 2 ACCOUNTING NEW YORK
ALLEN 30 1900.00 3 SALES CHICAGO
WARD 30 1750.00 3 SALES CHICAGO
TURNER 30 1500.00 3 SALES CHICAGO
CLARK 10 2450.00 4 ACCOUNTING NEW YORK
JONES 20 2975.00 4 RESEARCH DALLAS
SCOTT 20 3000.00 4 RESEARCH DALLAS
FORD 20 3000.00 4 RESEARCH DALLAS
MARTIN 30 2650.00 4 SALES CHICAGO
BLAKE 30 2850.00 4 SALES CHICAGO
KING 10 5000.00 5 ACCOUNTING NEW YORK