SQL连接查询

笔记区

         多表连接查询           


1，等值连接
select *from table1,table2 
where table1.t1no=table2.t2no;

2,非等值

笛卡尔积
特点：查询个数为乘积
作用：模拟大量数据

交叉连接

自连接
内连接：把符合条件的查出来(以上都是内查询)

外连接(可以把不存在员工的部门和员工也显示出来，即显示不满足条件的)


左外连接：table1 left outer join table2 on +连接条件//只能将表一中不符合条件的也查出来

右外连接：table1 right outer  join table2 on +连接条件//只能将表一中不符合条件的也查出来.

查所有部门下的员工：
select *from dept left outer join emp on emp.deptno=dept.deptno;
单行子查询操作符：= > ,< ,>= ,<=   只能返回一行值
多行子查询操作符：< all ,> all,=all,< any,> any ,=any,in。返回结果可以有多个。


分页查询
select * from emplimit start,length;
start 默认从0开始.
自增auto_increment 主键可以设为NULL自动生成1号。

代码区

select ename,loc from emp e,dept d where e.deptno = d.deptno;
select ename,loc from emp e,dept d where e.deptno = d.deptno and loc = 'DALLAS';
SELECT * FROM EMP ,salgrade WHERE sal >= losal and sal <=hisal;
<=>
select *from EMP ,salgrade where sal between losal and hisal;

select * from emp,dept,salgrade 
where sal >= losal and sal <=hisal and emp.deptno = dept.deptno and loc = 'DALLAS';              ///三表查询。

select * from emp,dept;
select w.ename,w.mgr,m.empno,m.ename from emp w,emp m where w.mgr = m.empno;//自身连接
select * from dept d,emp e where e.deptno = d.deptno;

select * from dept left outer join emp on emp.deptno = dept.deptno
select * from emp where sal >all (select sal from emp where deptno = 30);

select * from emp where sal in (select sal from emp where deptno = 30);//返回多个值

select * from emp LIMIT 8,4;
select * from emp order by sal desc limit 3;

CREATE TABLE student(	id INT PRIMARY KEY AUTO_INCREMENT,sage INT ,sname VARCHAR(32) NOT NULL,gender VARCHAR(10) NOT NULL,	score DOUBLE,birthday DATE);
INSERT INTO student (id,sage,sname,gender,score,birthday) VALUES(null,22,'zhangsan','male',98.99,'1999-09-09');

update emp set sal = sal+500 where deptno = 10