Mysql⼦查询、分组查询综合练习题
Mysql ⼦查询、分组查询语句的灵活运⽤练习题
#⼦查询
CREATE TABLE emp(
empno INT,
ename VARCHAR(50),
job VARCHAR(50),
mgr INT,
hiredate DATE,
sal DECIMAL(7,2), – 薪资
comm DECIMAL(7,2), – 奖⾦
deptno INT
) ;
INSERT INTO emp VALUES(7369,‘SMITH’,‘CLERK’,7902,‘1980-12-17’,800,NULL,20);
INSERT INTO emp VALUES(7499,‘ALLEN’,‘SALESMAN’,7698,‘1981-02-20’,1600,300,30); INSERT INTO emp VALUES(7521,‘WARD’,‘SALESMAN’,7698,‘1981-02-22’,1250,500,30); INSERT INTO emp VALUES(7566,‘JONES’,‘MANAGER’,7839,‘1981-04-02’,2975,NULL,20); INSERT INTO emp VALUES(7654,‘MARTIN’,‘SALESMAN’,7698,‘1981-09-28’,1250,1400,30); INSERT INTO emp VALUES(7698,‘BLAKE’,‘MANAGER’,7839,‘1981-05-01’,2850,NULL,30); INSERT INTO emp VALUES(7782,‘CLARK’,‘MANAGER’,7839,‘1981-06-09’,2450,NULL,10); INSERT INTO emp VALUES(7788,‘SCOTT’,‘ANALYST’,7566,‘1987-04-19’,3000,NULL,20); INSERT INTO emp VALUES(7839,‘KING’,‘PRESIDENT’,NULL,‘1981-11-17’,5000,NULL,10); INSERT INTO emp VALUES(7844,‘TURNER’,‘SALESMAN’,7698,‘1981-09-08’,1500,0,30); INSERT INTO emp VALUES(7876,‘ADAMS’,‘CLERK’,7788,‘1987-05-23’,1100,NULL,20);
INSERT INTO emp VALUES(7900,‘JAMES’,‘CLERK’,7698,‘1981-12-03’,950,NULL,30);
INSERT INTO emp VALUES(7902,‘FORD’,‘ANALYST’,7566,‘1981-12-03’,3000,NULL,20); INSERT INTO emp VALUES(7934,‘MILLER’,‘CLERK’,7782,‘1982-01-23’,1300,NULL,10);
CREATE TABLE dept(
deptno INT,
mysql面试题sql
dname VARCHAR(14),
loc VARCHAR(13)
);
INSERT INTO dept VALUES(10, ‘ACCOUNTING’, ‘NEW YORK’);
signals是什么意思
INSERT INTO dept VALUES(20, ‘RESEARCH’, ‘DALLAS’);
INSERT INTO dept VALUES(30, ‘SALES’, ‘CHICAGO’);
INSERT INTO dept VALUES(40, ‘OPERATIONS’, ‘BOSTON’);
SELECT * from emp ;
SELECT * from dept;
汇编语言程序讲解– 单⾏⼦查询(> < >= <= = <>)
– 查询出⾼于10号部门的平均⼯资的员⼯信息
select * from emp where sal>(select avg(sal) from emp where deptno=10);
– 多⾏⼦查询(in not in any all)
– 查询出⽐10号部门任何员⼯薪资⾼的员⼯信息
select * from emp where sal+comm>(select max(sal+comm) from emp where deptno=10)and deptno!=10;
– 多列⼦查询(实际使⽤较少)
– 和10号部门同名同⼯作的员⼯信息
select * from emp e where (ename,job)in (select ename,job from emp where deptno=10)and deptno!=10;
– select 后⾯接⼦查询
– 获取员⼯的名字和部门的名字
select ename,depto from emp;
select ename as 员⼯名字,(select dname from dept d where d.deptno=e.deptno)as 部门名称 from emp e;
– from 后⾯接⼦查询
– 查询emp表中经理信息
#1.查询出所有经理的ID
select distinct mgr from emp;
– 博客写的
select * from emp e,(select distinct mgr from emp) m ;
– ⾃⼰写的
postman要钱吗
select * from emp e,dept d where e.deptno=d.deptno and job=‘MANAGER’;
– where 后⾯接⼦查询
– 薪资⾼于10号部门平均⼯资的所有员⼯信息
select * from emp where sal>(select avg(sal) from emp where deptno=10);
– group by 后⾯接⼦查询
– 有哪些部门的平均⼯资⾼于30号部门的平均⼯资
select deptno 部门,avg(sal) 平均⼯资 from emp group by deptno having avg(sal)>(select avg(sal) from emp where deptno=30);
– ⼯资>JONES⼯资
select sal from emp where ename=‘jones’;
– 查询与SCOTT同⼀个部门的员⼯
select * from emp where deptno=(select deptno from emp where ename=‘SCOTT’);
– ⼯资⾼于30号部门所有⼈的员⼯信息
select * from emp where sal>(select max(sal) from emp where deptno=30);
– 查询⼯作和⼯资与MARTIN完全相同的员⼯信息
select * from emp where job=(select job from emp where ename=‘MARTIN’) and sal=(select sal from emp where ename=‘MARTIN’);
SELECT * FROM emp WHERE (job,sal) IN (SELECT job,sal FROM emp WHERE ename=‘MARTIN’);
– 有两个以上直接下属的员⼯信息
– 1.查出emp表中mgr信息
select mgr from emp;
– 2.分组统计mgr的信息
常见编程语言
select mgr,COUNT() from emp group by mgr having count()>2;
select * from emp e1 pno in ( from emp e2 group having count(*)>2);
聊雪峰java教程– 查询员⼯编号为7788的员⼯名称，员⼯⼯资，部门名称，部门地址
select ename ,sal,dname,loc from emp e,dept d where e.deptno=d.deptno and empno=7788;
– SQL查询的综合案例
– 1. 查询出⾼于本部门平均⼯资的员⼯信息
– 先查询每个部门的平均⼯资
select deptno,avg(sal) from emp group by deptno;
select * from emp e1 where e1.sal>(select avg(e2.sal) from emp e2 where e1.deptno=e2.deptno group by e2.deptno);
select a.* from emp a,(select deptno,avg(sal) as sal from emp group by deptno) b where a.deptno=b.deptno and a.sal>b.sal
– 2. 列出达拉斯加⼯作的⼈中,⽐纽约平均⼯资⾼的⼈
select * from emp where deptno=(select deptno from dept where loc=‘DALLAS’) and sal>(select avg(sal) from emp where deptno=(select deptno from dept where loc=‘NEW YORK’));
– 3. 查询7369员⼯编号，姓名，经理编号和经理姓名
,ame from emp e1 ,emp jingli =pno and empno=7369;
– 4. 查询出各个部门薪⽔最⾼的员⼯所有信息
select * from emp e1 where e1.sal = (select MAX(sal) from emp e2 where e1.deptno = e2.deptno group by deptno);
⾯试题
– ```sql
CREATE TABLE test(
NAME CHAR(20),
kecheng CHAR(20),
fenshu CHAR(20)
);
INSERT INTO test VALUES(‘张三’,‘语⽂’,81),
(‘张三’,‘数学’,75),
(‘李四’,‘语⽂’,76),
(‘李四’,‘数学’,90),
(‘王五’,‘语⽂’,81),
(‘王五’,‘数学’,82);
– 请⽤⼀条Sql语句查处分数⼤于80的学⽣
select name,fenshu from test where fenshu>80;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。