数据库查询案例语句

员工表与部门表查询1

# 将两个表同时查询 字段相同的会自动改变一点例如在后面+'1' 出现的行数= 表1行数*表2行数
select * from emp,dept;

# 通过条件使其关联
select * from emp as e,dept as d where e.deptno = d.deptno and d.dname='sales';

# 显示部门号为10的部门名，员工名和工资
select d.dname,e.ename,e.sal from emp as e,dept as d where d.deptno=e.deptno and d.deptno=10;

# 显示员工名，工资，部门名称，按照部门号降序排列,按工资升序
select dept.deptno,dname,ename,sal from emp,dept where emp.deptno=dept.deptno order by dept.deptno desc,sal asc;

# 自连接/内连接、自关联-在同一张表上的连接查询
# 显示某个员工的上级姓名，比如显示ward的上级姓名
select ename from emp where empno=(select mgr from emp where ename='ward');

# 显示每个员工的姓名和他的上级姓名
select e1.ename as '员工',e2.ename as '上级' from emp as e1,emp as e2 where e1.mgr=e2.empno;

# 子查询-嵌入在其他查询语句中的查询，也叫嵌套查询
# 单行子查询--显示与smith同一部门的所有员工  ---单行子查询是指只返回一行数据的子查询语句
select ename from emp where deptno=(select deptno from emp where ename='smith');

# 多行子查询--查询和部门10相同的工作岗位的员工姓名，工资，部门编号 ---多行子查询是指返回多行数据的子查询
select job,ename,sal,deptno from emp where job in (select distinct job from emp where deptno=10);

# 在from中使用子查询--查询高于部门平均工资的员工姓名、薪水
# 注意：当在from子句使用子查询时，该子查询会被当作一个临时表来对待，当在from子句中使用子查询时，必须给子查询指定别名
select ename,sal from emp as e,(select avg(sal) as avg_sal,deptno from emp group by deptno) as t where e.deptno=t.deptno and e.sal>t.avg_sal;

# 分页查询 按入职时间顺序显示第1个到第5个  第一个参数是起始点，不包括自身 可以当成起始为0的索引 第二个参数为显示多少个
select * from emp order by hiredate limit 0,5;

# 每次查询5条记录，传一个当前页的信息
pageSize = 5, pageNow=1

select * from emp order by hiredate limit (pageNow-1)*pageSize ,pageSize;


# 左外连接，右外连接，等值连接
#·inner join(等值连接)：只返回两个表中联结字段相等的行；
#·left join(左联接)：返回包括左表中的所有记录和右表中联结字段相等的记录；未对应的数据使用null填充
#·right join(右联接)：返回包括右表中的所有记录和左表中联结字段相等的记录；未对应的数据使用null填充
# 显示所有员工的上级名称，没有上级的显示null1
select workdr.ename,boss.ename from emp as workdr left join emp as boss on workdr.mgr=boss.empno;

# 显示所有员工的上级名称，没有上级的不显示
select workdr.ename,boss.ename from emp as workdr inner join emp as boss on workdr.mgr=boss.empno;

员工表与部门表查询2

select * from emp;

select * from dept;

# 返回拥有员工的部门名、部门号,按部门号升序排列
select distinct dept.dname,dept.deptno from emp,dept where dept.deptno=emp.deptno order by dept.deptno;

# 返回工资比ward高的员工信息
select * from emp where sal>(select sal from emp where ename='ward');

# 4.返回员工的雇佣日期早于其经理雇佣日期的员工及其经理姓名
select e1.ename as '员工',e2.ename as '经理' from emp as e1 inner join emp as e2 on e1.mgr=e2.empno where e1.hiredate<e2.hiredate;

# 返回员工姓名及所在部门的名称
select ename,dname from emp inner join dept on emp.deptno=dept.deptno;

# 返回从事clerk工作的员工姓名和所在部门名称
select ename,dname from emp inner join dept on emp.deptno=dept.deptno where job='clerk';

# 返回部门号。部门名及部门最低工资and 员工名。

select e1.ename,e2.* from emp as e1 inner join 
(select emp.deptno,dname,min(sal) as minSal from emp inner join dept on emp.deptno=dept.deptno group by deptno) as e2 
on e1.deptno=e2.deptno where e1.sal in (e2.minSal);

# 返回与ward从事相同工作的员工
select * from emp where job=(select job from emp where ename='ward') and ename!='ward';

# 返回与30部门员工工资有相同的员工姓名与工资 all() 所有的 都可以比较
select ename,sal from emp as e1 where sal>all(select sal from emp as e2 where e2.deptno=30);

# 返回部门号、部门名、部门所在位置及每个部门的员工数  count(emp.ename) 一共有多少人 分组后才进行聚合
select dept.deptno,dept.dname,dept.address,count(emp.ename) as number from dept inner join emp where dept.deptno=emp.deptno group by emp.deptno;

# 返回员工工作及其从事此工作的最低工资.和拿该工资的人名、部门编号，部门名
select relust2.job,relust2.minSal,relust2.ename,relust2.deptno,dept.dname 
from dept inner join
(select result1.job,result1.minSal,ename,e1.deptno from emp as e1 inner join 
(select job,min(sal) as minSal,deptno,empno from emp group by job) as result1 
# 这里不能用部门号对称，因为部门号并不是唯一的,on结束后得到的部分会混乱
# 这里是员工表的内连接 这里使用sal而不是唯一的empno是因为 上面的临时表中
# minSal为唯一的，这样如果有重复的最少工资的人也都可以查出来
on e1.sal=result1.minSal) as relust2 
# 这里用部门号对应，因为在dept表中部门号是唯一的，是员工表的外键
# 这里是员工表与部门表的外连接
on dept.deptno=relust2.deptno;

员工与部门表查询3

select * from emp;

select * from dept;

# 显示部门30中的所有员工信息
select * from emp where deptno=30;

# 列出所有办事员(clerk)的姓名，编号和部门编号
select ename,empno,deptno from emp where job='clerk';

# 找出佣金(奖金)高于薪金的员工
select * from emp where comm>sal;

# 找出部门10中所有经理和部门20中所有办事员的详细资料
select * from emp where (deptno=10 and job='manager') or (deptno=20 and job='clerk');

# 找出部门10中所有经理(MANAGER)部门20中所有办事员(CLERK)以及既不是经理又不是办事员但其薪金大于或等于2000的所有员工的详细资料。
select * from emp where (deptno=10 and job='manager') or (deptno=20 and job='clerk') or (sal>=2000 and job not in('manager','clerk'));

# 找出收取佣金的员工的不同工作
select job from emp where comm is not null group by job;

# 找出不收取佣金或收取佣金低于100的员工
select * from emp where comm is null or comm<100;

# 找出在周三受雇的所有员工
# DAYNAME(date) 显示date为周几  
# DAYOFWEEK(date)  返回date所代表的一星期中的第几天(1~7) 周日为第一天
select ename,dayname(hiredate) as '周几' from emp where dayofweek(hiredate)=4;

# 找出于1981-05-03日到1984-03-04日之间受雇的员工
select ename,hiredate from emp where hiredate between '1981-05-03' and '1984-03-04';

# 以首字母大写的方式显示所有员工的姓名
# CONCAT(s1,s2..sn) 将s1,s2...,sn连接成字符串
# UPPER(str) / LOWER(str)返回将字符串str中所有字符转变为大写/小写后的结果
# LEFT(str,x)/RIGHT(str,x) 返回字符串str中最左/右边的x个字符
# LENGTH(s)返回字符串str中的字符数
select concat(upper(left(ename,1)),lower(right(ename,length(ename)-1))) as name from emp;

# 显示正好为5个字符长度的员工的姓名
select ename from emp where length(ename)=5;

# 显示不带有r的员工的姓名
select ename from emp where ename not like '%r%';

# 显示所有员工姓名的前三个字符
select left(ename,3) from emp;

# 显示名称第三个字母为a的所有员工的姓名
select ename from emp where ename like '__a%';

# 显示满10年服务年限的员工的姓名和受雇日期
# YEAR(date)   返回日期date的年份(1000~9999)
# CURDATE()或CURRENT_DATE() 返回当前的日期
select ename,hiredate from emp where (year(curdate())-year(hiredate))>=10;

# 显示员工的详细资料，按姓名排序
select * from emp order by ename;

# 显示员工姓名和受雇日期，按照服务年限，将最老的排前面
select ename,hiredate from emp order by hiredate asc;

# 显示所有员工的姓名、工作和薪金，按工作的降序排序，若工作相同则按薪金排序
select ename,job,sal from emp order by job desc,sal;

# 显示所有员工的姓名、加入公司的年份和月份，按受雇日期所在月排序,若月份相同则将最早年份的员工排在最前面
# MONTH(date) 
# 返回 date 中的月份，范围为 1 到 12： 
select ename,year(hiredate) as 'year',month(hiredate) as 'month' from emp order by month(hiredate),year(hiredate);

# 列出至少有一个员工的所有部门
select distinct dname from emp inner join dept on emp.deptno=dept.deptno;

# 列出薪金比'smith'多的所有员工
select * from emp where sal>(select sal from emp where ename='smith');

# 列出所有员工的姓名及其直接上级的姓名
select e1.ename as '员工',e2.ename as '上级' from emp as e1 inner join emp as e2 on e1.mgr=e2.empno;

# 列出受雇日期晚于其直接上级的员工
select e1.ename as '员工',e2.ename as '经理' from emp as e1 inner join emp as e2 on e1.mgr=e2.empno where e1.hiredate>e2.hiredate;

# 列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。
select d.dname,e.* from emp as e right join dept as d on e.deptno=d.deptno;

# 列出所有“clerk”(办事员)的姓名及其部门名称
select ename,dname from emp inner join dept on emp.deptno=dept.deptno where job='clerk';

# 列出最低薪金大于1500的各种工作。
select job from emp group by job having min(sal)>1500;

# 列出在部门“SALES”(销售部)工作的员工的姓名，假定不知道销售部的部门编号。
select ename from emp inner join dept on emp.deptno=dept.deptno where emp.deptno=(select deptno from dept where dname='sales');

# 列出薪金高于公司平均薪金的所有员工。并显示平均工资
select e.*,a.avgSal from emp as e inner join (select avg(sal) as avgSal from emp) as a on e.sal>a.avgSal;

# 列出与“SCOTT”从事相同工作的所有员工
select * from emp where job=(select job from emp where ename='scott') and ename!='scott';

# 列出工作岗位与部门30中工作岗位相同的员工的姓名和薪金和岗位
select ename,sal,job from emp where job in(select distinct job from emp where deptno=30);

# 列出薪金高于部门30最高薪金的员工姓名和薪金
select ename,sal from emp where sal>(select max(sal) from emp where deptno=30);

# 列出在每个部门工作的员工数量、平均工资
select count(empno) as '员工数量',avg(sal) as '平均工资' from emp group by deptno;

# 列出所有员工的姓名、部门名称和工资
select ename,dname,sal from emp inner join dept on emp.deptno=dept.deptno;

# 列出各种工作的最低工资
select job,min(sal) as '最低工资' from emp group by job;

# 列出MANAGER(经理)的最低薪金
select job,min(sal) as '最低工资' from emp where job='manager';

# 列出所有员工的年工资(工资加奖金)，按年薪从低到高排序
select ename,job,(sal+ifnull(comm,0))*12 as nx from emp order by nx;

学生表 成绩表 课程表关联查询

desc students;

insert into students (sname,age) values('依然',21),('诚哥',23),('miku',24),('岚',23);

select * from students;


desc subjects;

insert into subjects (subname) value ('语文'),('数学'),('英语');

select * from subjects;


CREATE TABLE `scores` (
  `stuid` int(11) DEFAULT NULL,
  `subid` int(11) DEFAULT NULL,
  `score` decimal(5,2) DEFAULT NULL,
  `isDelete` bit(1) DEFAULT b'0',
  KEY `stuid` (`stuid`),
  KEY `subid` (`subid`),
  CONSTRAINT `scores_ibfk_1` FOREIGN KEY (`stuid`) REFERENCES `students` (`sid`) on update cascade on delete cascade,
  CONSTRAINT `scores_ibfk_2` FOREIGN KEY (`subid`) REFERENCES `subjects` (`subid`) on update cascade on delete cascade
);

desc scores;

# 给成绩表添加数据
insert into scores (stuid,subid,score) values(1,1,107),(1,2,115),(1,3,40),(2,1,100),(2,2,103),(2,3,95),
(3,1,110),(3,2,113),(3,3,120),(4,1,110),(4,2,100),(4,3,90);

select * from scores;

# 三表关联查询 姓名 科目 成绩
select sname,subname,score from students inner join
 (select subname,stuid,score from subjects inner join scores on scores.subid=subjects.subid) as tab1 on students.sid=tab1.stuid;