-
多表sql查询求和
2017-07-06 09:57:49昨天由于需要统计某一时间段内的数据,需要两张关联表的数据和, ...表A的sql查询结果 select count(*) from 表A a where a.task_type = 2 and a.create_time >= to_date('2017-01-30', 'yyyy-MM-dd') and a展开全文昨天由于需要统计某一时间段内的数据,需要两张关联表的数据和,
表A的sql查询结果
select count(*)
from 表A a
where a.task_type = 2
and a.create_time >= to_date('2017-01-30', 'yyyy-MM-dd')
and a.create_time < to_date('2017-07-07', 'yyyy-MM-dd')表B的sql查询结果
select count(*)
from 表B b
where b.task_type = 2
and b.create_time >= to_date('2017-01-30', 'yyyy-MM-dd')
and b.create_time < to_date('2017-07-07', 'yyyy-MM-dd')求两条sql之和,使用union all函数
select count(*)
from (select *
from 表A A
where a.task_type = 2
and a.create_time >= to_date('2017-01-30', 'yyyy-MM-dd')
and a.create_time < to_date('2017-07-07', 'yyyy-MM-dd')
union all
select *
from 表B b
where b.task_type = 2
and b.create_time >= to_date('2017-01-30', 'yyyy-MM-dd')
and b.create_time < to_date('2017-07-07', 'yyyy-MM-dd'));此处UNION ALL 这个指令的目的也是要将两个 SQL 语句的结果合并在一起。 UNION ALL 和UNION 不同之处在于UNION ALL 会将每一个符合条件的资料都列出来，无论资料值有无重复
-
MySQL多表SQL查询
2016-05-17 13:28:1701.创建数据库表结构并插入...[sql] view plain copy DROP DATABASE exam; CREATE DATABASE exam; USE exam; /*创建部门表*/ CREATE TABLE dept( deptno INT PRIMARY KEY, dnam展开全文01.创建数据库表结构并插入数据
如下代码，导入sql不产生乱码
DROP DATABASE exam; CREATE DATABASE exam; USE exam; /*创建部门表*/ CREATE TABLE dept( deptno INT PRIMARY KEY, dname VARCHAR(50), loc VARCHAR(50) ); /*创建雇员表*/ CREATE TABLE emp( empno INT PRIMARY KEY, ename VARCHAR(50), job VARCHAR(50), mgr INT, hiredate DATE, sal DECIMAL(7,2), COMM DECIMAL(7,2), deptno INT, CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno) ); /*创建工资等级表*/ CREATE TABLE salgrade( grade INT PRIMARY KEY, losal INT, hisal INT ); /*创建学生表*/ CREATE TABLE stu( sid INT PRIMARY KEY, sname VARCHAR(50), age INT, gander VARCHAR(10), province VARCHAR(50), tuition INT ); /*插入dept表数据*/ INSERT INTO dept VALUES (10, 'jiao yan bu', 'bei jing'); INSERT INTO dept VALUES (20, 'xue gong bu', 'shang hai'); INSERT INTO dept VALUES (30, 'xiao shou bu', 'guang zhou'); INSERT INTO dept VALUES (40, 'cai wu bu', 'wu han'); /*插入emp表数据*/ INSERT INTO emp VALUES (1009, 'a niu', 'dong shi zhang', NULL, '2001-11-17', 50000, NULL, 10); INSERT INTO emp VALUES (1004, 'liu bei', 'jing li', 1009, '2001-04-02', 29750, NULL, 20); INSERT INTO emp VALUES (1006, 'guan yu', 'jing li', 1009, '2001-05-01', 28500, NULL, 30); INSERT INTO emp VALUES (1007, 'zhang fei', 'jing li', 1009, '2001-09-01', 24500, NULL, 10); INSERT INTO emp VALUES (1008, 'zhu ge liang', 'fen xi shi', 1004, '2007-04-19', 30000, NULL, 20); INSERT INTO emp VALUES (1013, 'pang', 'fen xi shi', 1004, '2001-12-03', 30000, NULL, 20); INSERT INTO emp VALUES (1002, 'dai', 'xiao shou yuan', 1006, '2001-02-20', 16000, 3000, 30); INSERT INTO emp VALUES (1003, 'tian zheng', 'xiao shou yuan', 1006, '2001-02-22', 12500, 5000, 30); INSERT INTO emp VALUES (1005, 'xie xun', 'xiao shou yuan', 1006, '2001-09-28', 12500, 14000, 30); INSERT INTO emp VALUES (1010, 'wei yi xiao', 'xiao shou yuan', 1006, '2001-09-08', 15000, 0, 30); INSERT INTO emp VALUES (1012, 'cheng pu', 'wen yuan', 1006, '2001-12-03', 9500, NULL, 30); INSERT INTO emp VALUES (1014, 'huang gai', 'wen yuan', 1007, '2002-01-23', 13000, NULL, 10); INSERT INTO emp VALUES (1011, 'zhou tai', 'wen yuan', 1008, '2007-05-23', 11000, NULL, 20); INSERT INTO emp VALUES (1001, 'gan nin', 'wen yuan', 1013, '2000-12-17', 8000, NULL, 20); /*插入salgrade表数据*/ INSERT INTO salgrade VALUES (1, 7000, 12000); INSERT INTO salgrade VALUES (2, 12010, 14000); INSERT INTO salgrade VALUES (3, 14010, 20000); INSERT INTO salgrade VALUES (4, 20010, 30000); INSERT INTO salgrade VALUES (5, 30010, 99990); /*插入stu表数据*/ INSERT INTO `stu` VALUES ('1', '001', '23', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('2', '002', '25', 'nan', 'liao ning', '2500'); INSERT INTO `stu` VALUES ('3', '003', '22', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('4', '004', '25', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('5', '005', '23', 'nv', 'bei jing', '1000'); INSERT INTO `stu` VALUES ('6', '006', '22', 'nv', 'shan dong', '2500'); INSERT INTO `stu` VALUES ('7', '007', '21', 'nv', 'bei jing', '1600'); INSERT INTO `stu` VALUES ('8', '008', '23', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('9', '009', '23', 'nv', 'guang zhou', '2500'); INSERT INTO `stu` VALUES ('10', '010', '18', 'nan', 'shan xi', '3500'); INSERT INTO `stu` VALUES ('11', '011', '23', 'nan', 'hu bei', '4500'); INSERT INTO `stu` VALUES ('12', '011', '24', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('13', '011', '24', 'nan', 'liao ning', '2500'); INSERT INTO `stu` VALUES ('14', '011', '22', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('15', '011', '25', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('16', '011', '23', 'nv', 'bei jing', '1000'); INSERT INTO `stu` VALUES ('17', '011', '22', 'nv', 'shan dong', '2500'); INSERT INTO `stu` VALUES ('18', '011', '21', 'nv', 'bei jing', '1600'); INSERT INTO `stu` VALUES ('19', '011', '23', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('20', '011', '23', 'nv', 'guang zhou', '2500'); INSERT INTO `stu` VALUES ('21', '011', '18', 'nan', 'shan xi', '3500'); INSERT INTO `stu` VALUES ('22', '011', '23', 'nan', 'hu bei', '4500'); INSERT INTO `stu` VALUES ('23', '011', '23', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('24', '011', '25', 'nan', 'liao ning', '2500'); INSERT INTO `stu` VALUES ('25', '011', '22', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('26', '011', '25', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('27', '011', '23', 'nv', 'bei jing', '1000'); INSERT INTO `stu` VALUES ('28', '011', '22', 'nv', 'shan dong', '2500'); INSERT INTO `stu` VALUES ('29', '011', '21', 'nv', 'bei jing', '1600'); INSERT INTO `stu` VALUES ('30', '011', '23', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('31', '011', '23', 'nv', 'guang zhou', '2500'); INSERT INTO `stu` VALUES ('32', '011', '18', 'nan', 'shan xi', '3500'); INSERT INTO `stu` VALUES ('33', '033', '23', 'nan', 'hu bei', '4500'); INSERT INTO `stu` VALUES ('34', '034', '23', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('35', '035', '25', 'nan', 'liao ning', '2500'); INSERT INTO `stu` VALUES ('36', '036', '22', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('37', '037', '25', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('38', '038', '23', 'nv', 'bei jing', '1000'); INSERT INTO `stu` VALUES ('39', '039', '22', 'nv', 'shan dong', '2500'); INSERT INTO `stu` VALUES ('40', '040', '21', 'nv', 'bei jing', '1600'); INSERT INTO `stu` VALUES ('41', '041', '23', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('42', '042', '23', 'nv', 'guang zhou', '2500'); INSERT INTO `stu` VALUES ('43', '043', '18', 'nan', 'shan xi', '3500'); INSERT INTO `stu` VALUES ('44', '044', '23', 'nan', 'hu bei', '4500'); INSERT INTO `stu` VALUES ('45', '045', '23', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('46', '046', '25', 'nan', 'liao ning', '2500'); INSERT INTO `stu` VALUES ('47', '047', '22', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('48', '048', '25', 'nan', 'bei jing', '1500'); INSERT INTO `stu` VALUES ('49', '049', '23', 'nv', 'bei jing', '1000'); INSERT INTO `stu` VALUES ('50', '050', '22', 'nv', 'shan dong', '2500'); INSERT INTO `stu` VALUES ('51', '051', '21', 'nv', 'bei jing', '1600'); INSERT INTO `stu` VALUES ('52', '052', '23', 'nan', 'bei jing', '3500'); INSERT INTO `stu` VALUES ('53', '053', '23', 'nv', 'guang zhou', '2500'); INSERT INTO `stu` VALUES ('54', '054', '18', 'nan', 'shan xi', '3500'); INSERT INTO `stu` VALUES ('55', '055', '23', 'nan', 'hu bei', '4500'); /* select * from emp; select * from dept; select * from salgrade; select * from stu; */
如下代码，mysql编码格式为GBD2312
- DROP DATABASE exam;
- CREATE DATABASE exam;
- USE exam;
- /*创建部门表*/
- CREATE TABLE dept(
- deptno INT PRIMARY KEY,
- dname VARCHAR(50),
- loc VARCHAR(50)
- );
- /*创建雇员表*/
- CREATE TABLE emp(
- empno INTPRIMARY KEY,
- ename VARCHAR(50),
- job VARCHAR(50),
- mgr INT,
- hiredate DATE,
- sal DECIMAL(7,2),
- COMM DECIMAL(7,2),
- deptno INT,
- CONSTRAINT fk_emp FOREIGN KEY(mgr) REFERENCES emp(empno)
- );
- /*创建工资等级表*/
- CREATE TABLE salgrade(
- grade INTPRIMARY KEY,
- losal INT,
- hisal INT
- );
- /*创建学生表*/
- CREATE TABLE stu(
- sid INTPRIMARY KEY,
- sname VARCHAR(50),
- age INT,
- gander VARCHAR(10),
- province VARCHAR(50),
- tuition INT
- );
- /*插入dept表数据*/
- INSERT INTO dept VALUES (10, '教研部', '北京');
- INSERT INTO dept VALUES (20, '学工部', '上海');
- INSERT INTO dept VALUES (30, '销售部', '广州');
- INSERT INTO dept VALUES (40, '财务部', '武汉');
- /*插入emp表数据*/
- INSERT INTO emp VALUES (1009, '曾阿牛', '董事长', NULL, '2001-11-17', 50000, NULL, 10);
- INSERT INTO emp VALUES (1004, '刘备', '经理', 1009, '2001-04-02', 29750, NULL, 20);
- INSERT INTO emp VALUES (1006, '关羽', '经理', 1009, '2001-05-01', 28500, NULL, 30);
- INSERT INTO emp VALUES (1007, '张飞', '经理', 1009, '2001-09-01', 24500, NULL, 10);
- INSERT INTO emp VALUES (1008, '诸葛亮', '分析师', 1004, '2007-04-19', 30000, NULL, 20);
- INSERT INTO emp VALUES (1013, '庞统', '分析师', 1004, '2001-12-03', 30000, NULL, 20);
- INSERT INTO emp VALUES (1002, '黛绮丝', '销售员', 1006, '2001-02-20', 16000, 3000, 30);
- INSERT INTO emp VALUES (1003, '殷天正', '销售员', 1006, '2001-02-22', 12500, 5000, 30);
- INSERT INTO emp VALUES (1005, '谢逊', '销售员', 1006, '2001-09-28', 12500, 14000, 30);
- INSERT INTO emp VALUES (1010, '韦一笑', '销售员', 1006, '2001-09-08', 15000, 0, 30);
- INSERT INTO emp VALUES (1012, '程普', '文员', 1006, '2001-12-03', 9500, NULL, 30);
- INSERT INTO emp VALUES (1014, '黄盖', '文员', 1007, '2002-01-23', 13000, NULL, 10);
- INSERT INTO emp VALUES (1011, '周泰', '文员', 1008, '2007-05-23', 11000, NULL, 20);
- INSERT INTO emp VALUES (1001, '甘宁', '文员', 1013, '2000-12-17', 8000, NULL, 20);
- /*插入salgrade表数据*/
- INSERT INTO salgrade VALUES (1, 7000, 12000);
- INSERT INTO salgrade VALUES (2, 12010, 14000);
- INSERT INTO salgrade VALUES (3, 14010, 20000);
- INSERT INTO salgrade VALUES (4, 20010, 30000);
- INSERT INTO salgrade VALUES (5, 30010, 99990);
- /*插入stu表数据*/
- INSERT INTO `stu` VALUES ('1', '王永', '23', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('2', '张雷', '25', '男', '辽宁', '2500');
- INSERT INTO `stu` VALUES ('3', '李强', '22', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('4', '宋永合', '25', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('5', '叙美丽', '23', '女', '北京', '1000');
- INSERT INTO `stu` VALUES ('6', '陈宁', '22', '女', '山东', '2500');
- INSERT INTO `stu` VALUES ('7', '王丽', '21', '女', '北京', '1600');
- INSERT INTO `stu` VALUES ('8', '李永', '23', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('9', '张玲', '23', '女', '广州', '2500');
- INSERT INTO `stu` VALUES ('10', '啊历', '18', '男', '山西', '3500');
- INSERT INTO `stu` VALUES ('11', '王刚', '23', '男', '湖北', '4500');
- INSERT INTO `stu` VALUES ('12', '陈永', '24', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('13', '李雷', '24', '男', '辽宁', '2500');
- INSERT INTO `stu` VALUES ('14', '李沿', '22', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('15', '王小明', '25', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('16', '王小丽', '23', '女', '北京', '1000');
- INSERT INTO `stu` VALUES ('17', '唐宁', '22', '女', '山东', '2500');
- INSERT INTO `stu` VALUES ('18', '唐丽', '21', '女', '北京', '1600');
- INSERT INTO `stu` VALUES ('19', '啊永', '23', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('20', '唐玲', '23', '女', '广州', '2500');
- INSERT INTO `stu` VALUES ('21', '叙刚', '18', '男', '山西', '3500');
- INSERT INTO `stu` VALUES ('22', '王累', '23', '男', '湖北', '4500');
- INSERT INTO `stu` VALUES ('23', '赵安', '23', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('24', '关雷', '25', '男', '辽宁', '2500');
- INSERT INTO `stu` VALUES ('25', '李字', '22', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('26', '叙安国', '25', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('27', '陈浩难', '23', '女', '北京', '1000');
- INSERT INTO `stu` VALUES ('28', '陈明', '22', '女', '山东', '2500');
- INSERT INTO `stu` VALUES ('29', '孙丽', '21', '女', '北京', '1600');
- INSERT INTO `stu` VALUES ('30', '李治国', '23', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('31', '张娜', '23', '女', '广州', '2500');
- INSERT INTO `stu` VALUES ('32', '安强', '18', '男', '山西', '3500');
- INSERT INTO `stu` VALUES ('33', '王欢', '23', '男', '湖北', '4500');
- INSERT INTO `stu` VALUES ('34', '周天乐', '23', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('35', '关雷', '25', '男', '辽宁', '2500');
- INSERT INTO `stu` VALUES ('36', '吴强', '22', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('37', '吴合国', '25', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('38', '正小和', '23', '女', '北京', '1000');
- INSERT INTO `stu` VALUES ('39', '吴丽', '22', '女', '山东', '2500');
- INSERT INTO `stu` VALUES ('40', '冯含', '21', '女', '北京', '1600');
- INSERT INTO `stu` VALUES ('41', '陈冬', '23', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('42', '关玲', '23', '女', '广州', '2500');
- INSERT INTO `stu` VALUES ('43', '包利', '18', '男', '山西', '3500');
- INSERT INTO `stu` VALUES ('44', '威刚', '23', '男', '湖北', '4500');
- INSERT INTO `stu` VALUES ('45', '李永', '23', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('46', '张关雷', '25', '男', '辽宁', '2500');
- INSERT INTO `stu` VALUES ('47', '送小强', '22', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('48', '关动林', '25', '男', '北京', '1500');
- INSERT INTO `stu` VALUES ('49', '苏小哑', '23', '女', '北京', '1000');
- INSERT INTO `stu` VALUES ('50', '赵宁', '22', '女', '山东', '2500');
- INSERT INTO `stu` VALUES ('51', '陈丽', '21', '女', '北京', '1600');
- INSERT INTO `stu` VALUES ('52', '钱小刚', '23', '男', '北京', '3500');
- INSERT INTO `stu` VALUES ('53', '艾林', '23', '女', '广州', '2500');
- INSERT INTO `stu` VALUES ('54', '郭林', '18', '男', '山西', '3500');
- INSERT INTO `stu` VALUES ('55', '周制强', '23', '男', '湖北', '4500');
- /*
- select * from emp;
- select * from dept;
- select * from salgrade;
- select * from stu;
- */
02.SQL多表查询/*1. 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。*/
/*
列：部门编号、部门名称、部门位置、部门人数(分组)
列：dept、emp(部门人数没有员工表不行)
条件：没有
分组条件：人数>1
部门编号、部门名称、部门位置在dept表中都有，只有部门人数需要使用emp表，使用deptno来分组得到。
我们让dept和（emp的分组查询），这两张表进行连接查询
*/
# 解答
- SELECT d.*,z.cnt FROM dept d,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) z WHERE d.deptno=z.deptno;
# 答案
- SELECT
- z.*,d.dname,d.loc
- FROM dept d, (SELECT deptno, COUNT(*) cnt FROM emp GROUP BY deptno) z
- WHERE z.deptno=d.deptno;
/**************************************************/
/*2. 列出薪金比关羽高的所有员工。*/
/*
列：所有
表：emp
条件：sal>关羽的sal，其中关羽的sal需要子查询
*/
# 解答
- SELECT e.* FROM emp e WHERE e.sal>(SELECT sal FROM emp WHERE ename='关羽');
# 答案
- SELECT *
- FROM emp e
- WHERE e.sal > (SELECT sal FROM emp WHERE ename='关羽')
/**************************************************/
/*3. 列出所有员工的姓名及其直接上级的姓名。*/
/*
列：员工名、领导名
表：emp、emp
条件：领导.empno=员工.mgr
emp表中存在自身关联，即empno和mgr的关系。
我们需要让emp和emp表连接查询。因为要求是查询所有员工的姓名，所以不能用内连接，因为曾阿牛是BOSS，没有上级，内连接是查询不到它的。
*/
# 解答
/* 内连接 */
- SELECT e.*,m.ename FROM emp e, emp m WHERE e.mgr=m.empno;
/* 左外连接 */
- SELECT e.*,IFNULL(m.ename,'BOSS') leader FROM emp e
- LEFT JOIN emp m ON e.mgr=m.empno
/* 右外连接 */
- SELECT e.*,m.ename leader FROM emp e
- RIGHT JOIN emp m ON e.mgr=m.empno
# 答案
/* 左外连接 */
- SELECT e.ename, IFNULL(m.ename, 'BOSS') AS lead
- FROM emp e
- LEFT JOIN emp m ON e.mgr=m.empno;
/**************************************************/
/*4. 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。*/
/*
列：编号、姓名、部门名称
表：emp、dept
条件：hiredate < 领导.hiredate
emp表需要查。部门名称在dept表中，所以也需要查。领导的hiredate需要查，这说明需要两个emp和一个dept连接查询
即三个表连接查询
*/
# 解答
/* 内连接 */
- SELECT e.empno,e.ename,d.dname
- FROM emp e,emp m,dept d
- WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.hiredate<m.hiredate
- ORDER BY d.dname ASC, e.empno ASC;
/* 左外连接 */
- SELECT e.empno,e.ename,d.dname
- FROM emp e
- LEFT JOIN emp m ON e.mgr=m.empno
- LEFT JOIN dept d ON e.deptno=d.deptno
- WHERE e.hiredate<m.hiredate
/* 右外连接 */
- SELECT e.empno,e.ename,d.dname
- FROM emp e
- RIGHT JOIN emp m ON e.mgr=m.empno
- RIGHT JOIN dept d ON e.deptno=d.deptno
- WHERE e.hiredate<m.hiredate
# 答案
- SELECT e.empno, e.ename, d.dname
- FROM emp e
- LEFT JOIN emp m ON e.mgr=m.empno
- LEFT JOIN dept d ON e.deptno=d.deptno
- WHERE e.hiredate<m.hiredate;
/**************************************************/
/*5. 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。*/
/*
列：员工表所有列、部门名称
表：emp, dept
要求列出没有员工的部门，这说明需要以部门表为主表使用外连接
*/
# 解答
/* 左外连接 */
- SELECT d.dname,e.*
- FROM dept d
- LEFT JOIN emp e ON d.deptno=e.deptno
# 答案
- SELECT e.*, d.dname
- FROM emp e
- RIGHT JOIN dept d ON e.deptno=d.deptno;
/**************************************************/
/*6. 列出所有文员的姓名及其部门名称，部门的人数。*/
/*
列：姓名、部门名称、部门人数
表：emp emp dept
条件：job=文员
分组：emp以deptno得到部门人数
连接：emp连接emp分组，再连接dept
*/
# 解答
/* 内连接 */
- SELECT e.ename,e.job,d.dname,c.cnt
- FROM emp e,dept d,(SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) c
- WHERE e.deptno=d.deptno AND e.deptno=c.deptno AND e.job='文员';
/* 左外连接 */
- SELECT e.ename,e.job,d.dname,c.cnt
- FROM emp e
- LEFT JOIN dept d ON e.deptno=d.deptno
- LEFT JOIN (SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) c ON e.deptno=c.deptno
- WHERE e.job='文员';
/* 右外连接 */
- SELECT e.ename,e.job,d.dname,c.cnt
- FROM emp e
- RIGHT JOIN dept d ON e.deptno=d.deptno
- RIGHT JOIN (SELECT deptno,COUNT(*) cnt FROM emp GROUP BY deptno) c ON e.deptno=c.deptno
- WHERE e.job='文员'
- ORDER BY c.cnt ASC;
/**************************************************/
/*7. 列出最低薪金大于15000的各种工作及从事此工作的员工人数。*/
/*
列：工作，该工作人数
表：emp
分组：使用job分组
分组条件：min(sal)>15000
*/
# 解答
/* 内连接 */
- SELECT e.job,c.cnt
- FROM emp e, (SELECT job,COUNT(*) cnt, MIN(sal) minsal FROM emp GROUP BY job) c
- WHERE e.job = c.job AND c.minsal>15000;
/* 左外连接 */
- SELECT e.job,c.cnt
- FROM emp e
- LEFT JOIN (SELECT job, COUNT(*) cnt, MIN(sal) minsal FROM emp GROUP BY job) c ON e.job=c.job
- WHERE c.minsal>15000;
/* 右外连接 */
- SELECT DISTINCT e.job,c.cnt
- FROM emp e
- RIGHT JOIN (SELECT job, COUNT(*) cnt, MIN(sal) minsal FROM emp GROUP BY job) c ON e.job=c.job
- WHERE c.minsal>15000;
# 答案
- SELECT job, COUNT(*)
- FROM emp e
- GROUP BY job
- HAVING MIN(sal) > 15000;
# 练习
- SELECT job, COUNT(*)
- FROM emp e
- GROUP BY job
- HAVING MIN(sal) > 15000;
/**************************************************/
/*8. 列出在销售部工作的员工的姓名，假定不知道销售部的部门编号。*/
/*
列：姓名
表：emp, dept
条件：所在部门名称为销售部，这需要通过部门名称查询为部门编号，作为条件
*/
/**************************************************/
# 解答
/* 内连接 */
- EXPLAIN SELECT e.ename
- FROM emp e,dept d
- WHERE e.deptno=d.deptno AND d.dname='销售部';
/* 左外连接 */
- EXPLAIN SELECT e.ename
- FROM emp e
- LEFT OUTER JOIN dept d ON e.deptno=d.deptno
- WHERE d.dname='销售部';
/* 右外连接 */
- EXPLAIN SELECT e.ename
- FROM emp e
- RIGHT OUTER JOIN dept d ON e.deptno=d.deptno
- WHERE d.dname='销售部';
/* 子查询 */
- EXPLAIN SELECT e.ename
- FROM emp e
- WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');
# 答案
- SELECT e.ename
- FROM emp e
- WHERE e.deptno = (SELECT deptno FROM dept WHERE dname='销售部');
/*9. 列出薪金高于公司平均薪金的所有员工信息，所在部门名称，上级领导，工资等级。*/
/*
列：员工所有信息(员工表)，部门名称(部门表)，上级领导(员工表)，工资等级(等级表)
表：emp, dept, emp, salgrade
条件：sal>平均工资，子查询
所有员工，说明需要左外
*/
# 解答
/* 内连接 */
- SELECT e.*,d.dname,m.ename,s.grade
- FROM emp e,dept d,emp m,salgrade s
- WHERE e.deptno=d.deptno AND e.mgr=m.empno AND e.sal BETWEEN s.losal AND s.hisal AND e.sal>(SELECT AVG(sal) avgsal FROM emp )
- ORDER BY s.grade;
/* 左外连接 */
- SELECT e.*,d.dname,m.ename,s.grade
- FROM emp e
- LEFT OUTER JOIN dept d ON e.deptno=d.deptno
- LEFT OUTER JOIN emp m ON e.mgr=m.empno
- LEFT OUTER JOIN salgrade s ON e.sal> s.losal AND e.sal<s.hisal
- WHERE e.sal>(SELECT AVG(sal) avgsal FROM emp)
- ORDER BY s.grade;
/* 右外连接 */
- SELECT e.*,d.dname,m.ename,s.grade
- FROM emp e
- RIGHT JOIN dept d ON e.deptno=d.deptno
- RIGHT OUTER JOIN emp m ON e.mgr=m.empno
- RIGHT OUTER JOIN salgrade s ON e.sal> s.losal AND e.sal<s.hisal
- WHERE e.sal>(SELECT AVG(sal) avgsal FROM emp)
- ORDER BY s.grade;
/* 子查询 */
# 内连接、左外连接、右外连接在where条件中都包含了子查询
# 答案
- SELECT e.*, d.dname, m.ename, s.grade
- FROM emp e
- NATURAL LEFT JOIN dept d
- LEFT JOIN emp m ON m.empno=e.mgr
- LEFT JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
- WHERE e.sal > (SELECT AVG(sal) FROM emp);
/**************************************************/
/*10.列出与庞统从事相同工作的所有员工及部门名称。*/
/*
列：员工表所有列，部门表名称
表：emp, dept
条件：job=庞统的工作，需要子查询，与部门表连接得到部门名称
*/
# 解答
/* 内连接 */
- SELECT e.*,d.dname
- FROM emp e, dept d
- WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');
/* 左外连接 */
- SELECT e.*,d.dname
- FROM emp e
- LEFT JOIN dept d ON e.deptno=d.deptno
- WHERE e.job=(SELECT job FROM emp WHERE ename='庞统');
/* 右外连接 */
- SELECT e.*,d.dname
- FROM emp e
- RIGHT JOIN dept d ON e.deptno=d.deptno
- WHERE e.job=(SELECT job FROM emp WHERE ename='庞统');
/* 子查询 */
# 内连接、左外连接、右外连接在where条件部分都包含了子查询
# 答案
- SELECT e.*, d.dname
- FROM emp e, dept d
- WHERE e.deptno=d.deptno AND e.job=(SELECT job FROM emp WHERE ename='庞统');
/**************************************************/
/*11.列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金、部门名称。*/
/*
列：姓名、薪金、部门名称(需要连接查询)
表：emp, dept
条件：sal > all(30部门薪金)，需要子查询
*/
# 解答
/* 内连接 */
- SELECT e.ename,e.sal,d.dname
- FROM emp e,dept d
- WHERE e.deptno=d.deptno AND e.sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)
- ORDER BY e.sal;
/* 左外连接 */
- SELECT e.ename,e.sal,d.dname
- FROM emp e
- LEFT JOIN dept d ON e.deptno=d.deptno
- WHERE e.sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)
- ORDER BY e.sal;
/* 右外连接 */
- SELECT e.ename,e.sal,d.dname
- FROM emp e
- RIGHT JOIN dept d ON e.deptno=d.deptno
- WHERE e.sal>(SELECT MAX(sal) maxsal FROM emp WHERE deptno=30)
- ORDER BY e.sal;
/* 子查询 */
# 内连接、左外连接、右外连接在where条件中都存在子查询
# 答案
- SELECT e.ename, e.sal, d.dname
- FROM emp e, dept d
- WHERE e.deptno=d.deptno AND sal > ALL(SELECT sal FROM emp WHERE deptno=30)
/**************************************************/
/*12.列出在每个部门工作的员工数量、平均工资。*/
/*
列：部门名称, 部门员工数，部门平均工资
表：emp, dept
分组：deptno
*/
# 解答
/* 内连接 */
- SELECT d.dname,c.cnt,c.avgsal
- FROM dept d,(SELECT deptno,COUNT(*) cnt,AVG(sal) avgsal FROM emp GROUP BY deptno) c
- WHERE d.deptno=c.deptno;
/* 左外连接 */
- SELECT d.dname,c.cnt,c.avgsal
- FROM dept d
- LEFT JOIN (SELECT deptno,COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) c ON d.deptno=c.deptno
/* 右外连接 */
- SELECT d.dname,c.cnt,c.avgsal
- FROM dept d
- RIGHT JOIN (SELECT deptno,COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) c ON d.deptno=c.deptno
/* 子查询 */
# 内连接、左外连接、右外连接中在条件where部分都包含了子查询
# 答案
- SELECT d.dname, e.cnt, e.avgsal
- FROM (SELECT deptno, COUNT(*) cnt, AVG(sal) avgsal FROM emp GROUP BY deptno) e, dept d
- WHERE e.deptno=d.deptno;
-
hibernate 原生多表sql查询 返回非映射实体类
2018-05-09 11:51:55Session session = this.getSession(); List<实体类> list = session.createSQLQuery(hql).setResultTransformer(Transformers.aliasToBean(实体类.class)).setParameter(参数).list();...展开全文Session session = this.getSession(); List<实体类> list = session.createSQLQuery(hql).setResultTransformer(Transformers.aliasToBean(实体类.class)).setParameter(参数).list(); releaseSession(session); return list;实体类和字段名称一一对应 可以用别名
-
SQL多表查询
2019-06-05 22:29:09多表查询的话，SQL提供了JOIN关键字来连接多张表的查询（即连接的是SELECT结果集）基本分为以下几种： JOIN / INNER JOIN-也称作内连接，是最为常用的一种JOIN，其效果是在表中存在一个匹配即返回该条数据 等值连接...展开全文SQL 提供了
JOIN关键字来连接多张表的查询（即连接的是SELECT结果集）基本分为以下几种：-
内连接：JOIN / INNER JOIN，是最为常用的一种连接，其效果是只返回条件匹配的那条数据
- 等值连接：ON 子句中使用了运算符“=”，且连接的两个字段最好是主键
例如select student.name, avg(sc.score) as avg_score from student join score on student.id = sc.id group by name;
name avg_score 赵雷 89.66667 钱电 70.00000 孙风 80.00000 李云 33.33333 - 不等连接：ON 子句中使用了不等于运算符，例如"<>" "<=>"等（可以搜下 SQL 比较运算符）
- 自连接：一张表连接自身。例如
select * from student a, student b where a.sex = b.sex and a.sex = '男';相当于做了次笛卡尔积运算，就不全部展示了
id name sex id name sex 1 赵雷 男 1 赵雷 男 2 钱电 男 1 赵雷 男 3 孙风 男 1 赵雷 男 4 李云 男 1 赵雷 男 - 等值连接：ON 子句中使用了运算符“=”，且连接的两个字段最好是主键
-
外连接：除了返回条件匹配的数据，那些不满足的数据也会返回。根据连接表的顺序可分为：
- 左（外）连接：LEFT JOIN / LEFT OUTER JOIN
以左表为基表连接右表，在右表中若没有匹配到基表中的数据则返回 null
例如select stu.name, avg(sc.score) as avg_score from student stu left join sc on sc.id = stu.id group by stu.name;
name avg_score 赵雷 89.66667 钱电 70.00000 孙风 80.00000 李云 33.33333 张三 null 李四 null - 右（外）连接：RIGHT JOIN / RIGHT OUTER JOIN
以右表为基表连接左表，在左表中若没有右匹配到基表中的数据则返回 null
- 左（外）连接：LEFT JOIN / LEFT OUTER JOIN
-
全（外）连接：FULL JOIN / FULL OUTER JOIN
- 等同于 LEFT JOIN 结果集 UNION RIGHT JOIN 结果集
-
交叉连接：CROSS JOIN （可用","代替）
- 等同于笛卡尔积 （所以不常用）
select * from table1 CROSS JOIN table2;<=>select * from table1, table2;
-
-
SQL语法（五） 多表联合查询
2018-09-09 09:00:01前言 当需要获取的数据分布在多张中，考虑使用联合查询，本章将学习两种查询方式（sql92/sql99） ... --表名以逗号隔开实现多表查询 --SQL99方式 --使用cross join 关键字 2.等值连接筛选&不等... -
sql多表的查询介绍
2020-09-23 19:28:15sql多表的查询介绍 （1）初始化数据 （2）实际项目中，数据保存于多个表，如果需要的数据来自多个表，就要使用多表查询 （3）查询的分类 》1:交叉连接查询(了解) 》2:内连接查询 隐式内连接，显式内连接*** 》3:外... -
sql多表查询
2014-08-18 22:36:14SQL多表连接查询（详细实例） 来源：新客网 作者：joeleo博客 发表于：2012-07-09 09:22 点击： 本文主要列举两张和三张表来讲述多表连接查询。 新建两张表： 表1：student 截图如下： 表2：course 截图如下... -
SQL多表连接查询
2015-11-06 17:16:49SQL多表连接查询（详细实例） 来源：新客网 作者：joeleo博客 发表于：2012-07-09 09:22 点击：68349 本文主要列举两张和三张表来讲述多表连接查询。 新建两张表： 表1：student 截图如下： 表2：course ... -
SQL语句多表联合查询
2017-03-18 12:25:57本周之前，对于数据库的使用仅限于简单的增删改查（insert、delete、updata、select），而这一周由于工作需要，对一些sql函数、sql语句多表联合查询、T-SQL语句基本语法、存储过程编写等有了简单的学习，现将一周所... -
SQLSERVER多表联查，多表分页查询
2017-10-16 18:01:59FULL OUTER JOIN 详解 注意：多表联查中，必然会用到这些sql关键字，如果不清楚它们的区别，那就没办法写出我们项目需要的查询条件。 多表联查 注意： 能够实现多表联查的前提是这些表一定有某些字段关联相等 两... -
SQL查询语句多表联查、三表关联查询
2020-08-10 15:27:46三表只查询左表有数据的值，右表没有的数据会显示出NULL值 语法 left---join---on 示例： select s.*,c.*,d.* from 表名 s left join 表名 c on c.id = s.sid left join 表名 d on d.id = s.sid select s.*,c.... -
SQL视图 多表链接查询
2019-01-22 22:44:52SQL视图 是数据对象 可以看做虚拟表式储存对象 是建立在基本表之上的表一般用于多表链接查询 外链接： 1.左外链接 ：以左表为基准进行查询，左表数据会全部显示出来右表则显示于左表匹配的相应字段的数据，不匹配... -
SQL单表查询与多表查询23例
2018-03-25 20:24:34多表查询 以MySQL为背景，.sql文件下载地址：员工关系表.sql 单表查询 /*1. 查询出部门编号为30的所有员工*/ SELECT * FROM emp WHERE deptno=30; /*2. 查询所有销售员的姓名、编号和部门编号。*/ ... -
SQL 多表连接查询
2018-05-30 10:24:55本文就不详细去说明表连接的概念，举个例子让大家理解表连接的实际用法学生表课程表选课表查询课号＇２０１８０３＇的学生学号，姓名，成绩，并且成绩按降序排列select student .student_id ,student .student_name ... -
SQL多表联合查询
2018-04-28 17:11:08注：所有的下划线+斜体语句都是非必须语句多表联合检索：SQL语句：Select 用逗号隔开的一排列名From 表名1，表名2, …Where 检索条件+连接条件相当于语句如果没有检索条件+连接条件，同理于笛卡尔积θ连接之等值连接... -
ORACLE SQL 多表查询
2019-04-07 21:52:00ORACLE SQL 多表查询 开发工具与关键技术：Oracle sql*plus 与 PLSQL Developer 作者：蒋紫晨 撰写时间：2019年04月07日 外连接 例子1、如果别人要的数据是那个部门没有员工？ Select * from departments d , ... -
sqlserver 多表联合查询
2019-04-03 16:53:16数据库查询时，对单表的查询很难满足需求，通常都需要多表联合查询。多表连接大致分为内连接和外连接。 内连接和外连接的区别主要体现在返回数据上，内连接返回的是符合连接条件和查询条件的记录，外连接返回的... -
sql多表连接查询
2018-05-19 17:22:11初始表环境如下：表1：student表2：course1.外链接： 又分为：左连接、右连接、完全外连接 左连接：select * from student left join course on student.ID=course.ID 查询结果为： 右连接：select * from ... -
用户表-角色表-权限表多表关联查询sql语句
2019-11-18 23:34:00在企业系统中经常会使用到给用户分配权限的情况，往往在用户信息表和...那么，查询用户权限的多表查询sql语句长什么样呢？ select * from pe_role_user ru inner join pe_role_module rm on ru.role_id=rm.`role_id... -
多表查询&动态sql
2019-06-25 22:06:14Mybatis多表查询及动态SQL 多表查询 建立两个表的关联查询 数据库里有两个表book,booktype 。book表有如下字段：id，bname,btype,author,author_gender,price,description。booktype表有如下字段：id，tname。 创建... -
SQL（三）|多表查询之查询3个表
2019-05-13 00:08:36连接查询查询3个表SQL -
数据库SQL语句查询及多表查询
2017-05-17 22:54:09数据库SQL语句查询的使用以及多表查询的方式 (可以通过浏览器打开图片或者下载图片来观看)
收藏数
28,873
精华内容
11,549
-
Docker从入门到精通
-
【论文阅读基础知识整理】CNN经典结构的总结
-
第三章 C语言 PTA循环控制——作业-答案.html
-
占据主动！刘强东微博营销之道.pdf
-
营销葵花宝典.txt
-
数据研究必备：国内40个免费数据源.pdf
-
光伏并网matlab仿真模型.zip
-
社交运营数据挖掘.ppt
-
在 Linux 上构建企业级 DNS 域名解析服务
-
鸿蒙系统Harmonyos源码架构分析-第1期第2课
-
java 二维数组打印杨辉三角
-
2021-02-28
-
apache-jmeter-3.1.7z
-
力扣7. 整数反转
-
ROSE-HA-V8.9+Win2008+SQL2008双机配置详细指南(图文).pdf
-
2021-02-28
-
DHCP 动态主机配置服务（在Linux环境下，配置单网段或跨网段提）
-
合同证明正版一元付费
-
MySQL 管理利器 mysql-utilities
-
git拉下来的项目文件夹没有图标