-
使用newdb3数据库
use newdb3;
having关键字
- where后面只能写普通字段条件， having后面写聚合函数条件
- having要和group by 结合使用，并且写在group by的后面
-
查询每个部门的平均工资，要求平均工资大于2000
select deptno,avg(sal) from emp group by deptno having avg(sal)>2000;
select deptno,avg(sal) a from emp group by deptno having a>2000;
-
查询每种工作的人数，只查询人数大于1的
select job,count(*) c from emp group by job having c>1;
-
查询每个部门的工资总和，只查询有领导的员工，并且要求工资总和高于5400
select deptno,sum(sal) s from emp where mgr is not null group by deptno having s>5400;
-
查询每个部门的平均工资,只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的部门
select deptno,avg(sal) a from emp
where sal between 1000 and 3000 group by deptno having a>=2000;
-
查询每种工作的人数要求人数大于1个,并且只查询1号部门和2号部门的员工, 按照人数降序排序
select job,count(*) c from emp where deptno in(1,2) group by job having c>1 order by c desc;
各个关键字的顺序
select 字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数
子查询(嵌套查询)
-
查询工资高于1号部门平均工资的员工信息
select avg(sal) from emp where deptno=1;
select * from emp where sal>2330;
-
合并上面两条SQL
select * from emp where sal>(select avg(sal) from emp where deptno=1);
-
-
查询工资最高的员工信息
select max(sal) from emp;
select * from emp where sal=(select max(sal) from emp);
-
查询工资高于2号部门最低工资的员工信息
select * from emp where sal>(select min(sal) from emp where deptno=2);
-
查询和孙悟空相同工作的其它员工信息
select job from emp where ename=“孙悟空”;
select * from emp where job=(select job from emp where ename=“孙悟空”) and ename!=“孙悟空”;
-
查询拿最低工资员工的同事们的信息(同事指相同部门)
-
得到最低工资
select min(sal) from emp;
-
得到最低工资的员工的部门编号
select deptno from emp where sal=(select min(sal) from emp);
-
得到上面部门编号的员工 并且排除掉最低工资的员工
select * from emp where deptno=(select deptno from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);
-
-
查询白骨精的部门信息(需要用到部门表dept)
select deptno from emp where ename=“白骨精”;
select * from dept where deptno=(select deptno from emp where ename=“白骨精”);
关联关系
-
创建表时,表和表之间存在的业务关系
-
有哪几种关系:
- 一对一: 有AB两张表,A表中一条数据对应B表中的一条数据,同时B表中的一条数据也对应A表中的一条数据.
- 一对多:有AB两张表,A表中一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的一条数据.
- 多对多:有AB两张表,A表中一条数据对应B表中的多条数据,同时B表中的一条数据也对应A表中的多条数据.
-
表和表之间如何建立关系
通过一个单独的字段指向另外一张表(或自己表)的主键, 这个字段称为外键
关联查询
- 同时查询多张存在关联关系的表时 使用的查询方式称为关联查询
- 关联查询的查询方式有三种: 1. 等值连接 2. 内连接 3. 外连接
关联查询之等值连接
- 格式: select * from A,B where A.x=B.x(关联关系) and A.age>30;
-
查询每个员工的姓名和对应的部门名
select ename,dname from emp e,dept d where e.deptno=d.deptno;
-
查询2号部门员工的姓名,工资和部门地址
select ename,sal,loc from emp e, dept d where e.deptno=d.deptno and e.deptno=2;
-
查询孙悟空和刘备的部门信息
select d.*
from emp e, dept d
where e.deptno=d.deptno
and ename in(“孙悟空”,“刘备”);
关联查询之内连接
-
等值连接和内连接的作用是一样的, 查询到的是两个表的交集数据
-
格式: select * from A join B on A.x=B.x(关联关系) where A.age>30;
-
查询每个员工的姓名和对应的部门名
select ename,dname from emp e join dept d on e.deptno=d.deptno;
-
查询1号部门有领导的员工姓名,工资,部门名和部门地址
select ename,sal,dname,loc
from emp e join dept d
on e.deptno=d.deptno
where e.deptno=1 and e.mgr is not null;
关联查询之外连接
-
查询的是一张表的全部和另外一张表的交集
-
格式: select * from A left/right join B on A.x=B.x(关联关系) where A.age>30;
-
准备一个员工表中的交集以外的数据
insert into emp (empno,ename) values(100,‘灭霸’);
-
查询所有员工的姓名和对应的部门名
select ename,dname
from emp e left join dept d
on e.deptno=d.deptno;
-
查询所有部门的名称,地址和对应的员工姓名
select dname,loc,ename
from emp e right join dept d
on e.deptno=d.deptno;
-
查询返回的结果集当做一张新表去使用
小试牛刀
Test1.各个部门薪水最高的员工所有信息
查询过程中出现问题 查询时没有把部门的条件带进去 只是按最高工资处理的
解决方法:
select * from
emp e,(select deptno,max(sal) msal from emp group by deptno) d
where e.sal=d.msal and
e.deptno=d.deptno;
关联查询总结
- 如果查询的数据是两个表的交集数据使用等值连接或内连接(推荐)
- 如果查询的是一张表的全部数据和另外一张表的交集数据则使用外连接
JDBC
-
Java DataBase Connectivity: Java数据库连接
-
学习JDBC主要学习的就是如何通过Java代码执行SQL语句
-
JDBC是Sun公司提供的一套 JAVA语言用于和各种数据库软件进行操作的API(Application Programma Interface 应用程序编程接口)
-
为什么使用JDBC?
如果没有JDBC接口, Java程序员每一种数据库都需要学习一套对应的方法,Sun公司为了避免出现这种情况,定义出了JDBC接口,JDBC里面是一堆的方法声明,各个数据库厂商根据方法声明写各自的实现类, 这样Java程序员就可以只学习JDBC里面的一套方法 就可以访问任何数据库,只要按照了JDBC的标准写的代码将来换了数据库 代码也不用改变.
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xi0TNw5l-1605011982475)(C:\Users\PC\AppData\Roaming\Typora\typora-user-images\image-20201110141507020.png)]
-
如何使用JDBC连接数据库
-
创建maven工程
-
将MySQL驱动(实际上就是MySQL数据库厂商提供的实现类) 复制到pom.xml中
<!-- 连接MySQL数据库的依赖 --> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.15</version> </dependency> -
创建Demo01.java 添加main方法
-
在main方法中添加以下代码:
//1. 注册驱动 告诉编译器使用的数据库是什么 Class.forName("com.mysql.cj.jdbc.Driver"); //2. 获取数据库连接 Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3?characterEncoding=utf8&useSSL=false&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true","root","root"); System.out.println("连接对象:"+conn); //3. 创建执行SQL语句的对象 Statement s = conn.createStatement(); //4. 执行SQL语句 String sql = "create table jdbct1(id int,name varchar(10))"; s.execute(sql); System.out.println("创建完成!"); //5. 关闭资源 conn.close();
-
Statement执行SQL语句的对象
- execute(sql); 可以执行任意SQL语句, 但是推荐执行DDL(数据库相关和表相关的SQL语句)
- executeUpdate(sql); 执行DML(包括增删改查)
- executeQuery(sql); 执行DQL(只包含查询)
小试牛刀
1. 查询人数最多的工作名称及人数
select job,count(*) from emp group by job order by count(*) desc limit 0,1;
2. 查询每种工作的平均工资取前三种
select job,avg(sal) from emp group by job order by avg(sal) desc limit 0,3;
3. 查询每种工作的人数,只查询3个人以内的工作
select job,count(*) from emp group by job having count(*)<3;
4. 查询最高工资的部门有多少人
//1.最高工资
select max(sal) from emp;
//2.最高工资的部门编号
select deptno from emp where sal=(select max(sal) from emp);
//3.部门编号对应的人数
select count(*) from emp where deptno=(select deptno from emp where sal=(select max(sal) from emp));
5. 查询工资高于2000的每个员工的姓名,工资和对应的部门名
select ename,sal,dname from emp e join dept d on e.deptno=d.deptno where e.sal>2000;
6. 查询所有的部门名和对应的员工姓名和工资
select dname,ename,sal from dept d left join emp e on d.deptno=e.deptno;
7. 查询平均工资最高的部门编号
select deptno from emp group by deptno order by avg(sal) desc limit 0,1;
8. 创建maven工程 08jdbc02 把MySQL依赖添加到pom.xml
Demo01.java 创建hero表 有id,name,type字段
create table hero(id int,name varchar(10),type varchar(10))
Demo02.java 插入 1,诸葛亮,法师 2,孙尚香,射手
insert into hero values(1,'诸葛亮','法师'),(2'孙尚香','射手');
Demo03.java 查询hero表的英雄名和类型 在控制台输出
select name,type from hero;
253
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
