**表数据:**
/*
SQLyog v10.2
MySQL - 5.1.62-community : Database - sience
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;
/*!40101 SET SQL_MODE=''*/;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*Table structure for table `dept` */
CREATE TABLE `dept` (
`id` varchar(36) NOT NULL,
`deptno` varchar(36) NOT NULL COMMENT '部门编号',
`dname` varchar(36) NOT NULL COMMENT '部门名称',
`loc` varchar(36) DEFAULT NULL COMMENT '地址',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `dept` */
insert into `dept`(`id`,`deptno`,`dname`,`loc`) values ('1','01','测试部','测试'),('2','02','开发部','开发'),('3','04','售前部','售前'),('4','05','运维部','运维');
/*Table structure for table `emp` */
CREATE TABLE `emp` (
`empno` varchar(36) NOT NULL COMMENT '员工编号',
`ename` varchar(36) NOT NULL COMMENT '员工姓名',
`job` varchar(20) NOT NULL COMMENT '职位',
`sal` varchar(20) NOT NULL COMMENT '工资',
`deptno` varchar(36) NOT NULL COMMENT '部门编号',
`mgr` varchar(20) NOT NULL COMMENT '上级领导编号',
`comm` varchar(20) DEFAULT NULL COMMENT '奖金',
`hiredate` datetime NOT NULL COMMENT '入职时间',
PRIMARY KEY (`empno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `emp` */
insert into `emp`(`empno`,`ename`,`job`,`sal`,`deptno`,`mgr`,`comm`,`hiredate`) values ('002','李四','开发','6000','02','002','20','2016-03-01 14:21:28'),('1','张三','测试','3000','01','001','20','2016-03-01 14:21:28'),('3','琳子','测试','4500','01','003','20','2017-03-01 14:35:07'),('4','糖衣','售前','5000','04','004','50','2016-07-11 14:36:03'),('5','古道','开发','7000','05','005','100','2015-03-01 14:36:50');
/*Table structure for table `salgrade` */
CREATE TABLE `salgrade` (
`id` varchar(36) NOT NULL,
`losal` varchar(36) DEFAULT NULL COMMENT '最低工资',
`hisal` varchar(36) DEFAULT NULL COMMENT '最高工资',
`grade` varchar(10) NOT NULL COMMENT '工资等级',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Data for the table `salgrade` */
insert into `salgrade`(`id`,`losal`,`hisal`,`grade`) values ('',NULL,NULL,''),('1','1000','3000','5'),('2','3100','6000','4'),('3','6100','8000','3'),('4','8100','9000','2');
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
/**
关联查询
*/
查询emp表中员工的编号,姓名以及所属部门的编号,名称
SELECT
emp.empno ‘员工编号’,
emp.ename ‘员工名称’,
dept.deptno ‘部门编号’,
dept.dname ‘部门名称’
FROM
dept,emp
WHERE emp.deptno = dept.deptno
查询dept表中部门的编号,名称,地址,以及该部门员工的编号,姓名,职位,工资
SELECT
d.deptno,
d.dname,
d.loc,
e.empno,
e.ename,
e.job,
e.sal
FROM
dept AS d,
emp AS e
WHERE d.deptno = e.deptno
查询emp表中工资在1000-3000之间的每一个员工的编号,姓名,工资以及所属部门的编号,名称,最后根据部门编号进行升序排列,如果部门编号一直,根据工资进行降序排序
SELECT
e.empno,
e.ename,
e.sal,
d.deptno,
d.dname
FROM
emp e,
dept d
WHERE e.deptno = d.deptno
AND e.sal BETWEEN 1000
AND 3000
ORDER BY deptno ASC,
e.sal DESC ;
/**
非等值连接查询
*/
#查询emp表中员工的编号,姓名,工资以及该工资等级(注意,此时要注意工资要在最低工资与最高工资之间)
SELECT
e.empno,
e.ename,
e.sal,
s.grade
FROM
emp e,
salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
#查询emp表中员工的姓名,职位,工资以及该工资的等级,该等级下的最高工资
SELECT
e.ename,
e.job,
e.sal,
s.grade,
s.hisal
FROM
emp e,
salgrade s
WHERE e.sal BETWEEN s.losal
AND s.hisal
#查询salgrade表中,工资的等级,最低工资,最高工资,以及该等级下,员工的所有的信息,最后根据工资的等级进行升序排列,如果工资的等级一直,根基该员工的工资进行升序排列
SELECT
s.*
FROM
salgrade s,
emp e
WHERE e.sal BETWEEN s.losal
AND s.hisal
ORDER BY s.grade ASC,
e.sal DESC ;
/**
*自连接====所需要查询的数据都来源于同一张表,而且二个列之间有关联关系,吧一张表当成二张表,通过关联条件连接起来进行查询插座。
领导也属于员工
*/
查询emp表中员工的编号,姓名,以及上级领导的编号,姓名【将emp看成二张表,员工表和领导表,员工表中领导的编号等于领导表中员工的编号】
SELECT
e.empno,
e.ename,
m.empno ‘上级领导编号’,
m.ename ‘上级领导姓名’
FROM
emp e,
emp m
WHERE e.mgr = m.empno
/**
内连接====inner join ..on
*/
查询emp表中员工的编号,姓名,职位,以及所属部门的编号,地址
SELECT
e.empno,
e.ename,
e.job,
d.deptno,
d.loc
FROM
emp e
INNER JOIN dept d
ON e.deptno = d.deptno
查询dept表中部门的编号,名称,以及该部门下的员工的所有信息
SELECT
d.deptno,
d.dname,
e.*
FROM
dept d
INNER JOIN emp e
ON d.deptno = e.deptno
查询emp表中员工的姓名,职位,工资,以及该工资的等级,最低工资,并且根据等级进行升序排列,如果等级一直,根据员工的工资进行升序排列
SELECT
e.ename,
e.job,
e.sal,
s.grade,
s.losal,
s.hisal
FROM
emp e
INNER JOIN salgrade s ON e.sal BETWEEN s.losal
AND s.hisal
ORDER BY s.hisal ASC,
e.sal ASC
/**
*左外连接查询=========以左边的表为主,查询左表的所有数据以及与之关联的右表中的数据,不管右边是否为空
格式:select …from 表1名称 left join 表二名称 on 条件(一般都是二表中共同的字段,也成关联字段)
*/
#查询emp 表中所有的信息以及员工所属的部门的编号
SELECT
e.*,
d.deptno
FROM
emp e
LEFT JOIN dept d
ON e.deptno = d.deptno
#查询emp表中所有员工的编号,姓名,职位,工资以及上级领导的编号,姓名,工资
SELECT
e.empno,
e.ename,
e.job,
m.empno,
m.ename,
m.sal
FROM
emp e
LEFT JOIN emp m
ON e.mgr = m.empno
/**
右外连接===以右边的表为主,不管左边是否为空
*/
#查询dept表中所有的部门的信息,以及对应部门下的员工的编号,姓名,职位
SELECT d.*,
e.empno,
e.ename,
e.job FROM dept d
RIGHT JOIN emp e
ON d.deptno = e.deptno
查询工资在1000到3000之间emp表中所有员工的编号,姓名,工资,入职时间,以及该工资的等级,最后根据等级进行升序排列,如果等级一直,根据工资进行将序排列
SELECT
e.*,
s.grade
FROM
emp e
RIGHT JOIN salgrade s
ON e.sal = s.grade
ORDER BY s.grade ASC,
e.sal ASC
#查询emp表中名字没有“古”的所有员工的编号,姓名,职位,工资以及上级领导的编号,姓名,职位,工资,最后根据员工的工资进行升序排列,如果工资一直,根基编号进行降序排列(右外)
SELECT
e.*,
m.empno,
m.ename,
m.job,
m.sal
FROM
emp m
RIGHT JOIN emp e
ON e.empno = m.mgr
WHERE e.ename NOT LIKE ‘%古%’
ORDER BY e.sal ASC,
m.empno
/**
全外连接
*/
#查询emp表中素有员工的编号,姓名,职位,以及dept表中所有部门的所有信息
SELECT
e.ename,
e.empno,
e.job,
d.*
FROM
emp e FULL
OUTER JOIN dept d
ON e.deptno = d.deptno