笔记区
多表连接查询
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