select avg(sal) from emp where deptno=1;
select * from emp where sal>2325;
select * from emp where sal>(select avg(sal) from emp where deptno=1);
select * from emp where sal=(select max(sal) from emp);
select * from emp where sal>(select min(sal) from emp where deptno=2);
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);
select deptno from emp where ename='白骨精';
select * from dept where deptno=(select deptno from emp where ename='白骨精');
select distinct deptno from emp;
- 通过查询到的部门编号查询部门信息
select * from dept where deptno in(select distinct deptno from emp);
select e.ename,e.sal,d.dname
from emp e,dept d
where e.deptno=d.deptno
and e.sal>2000;
2. 查询不是程序员的员工姓名,工作和部门所在地
select e.ename,e.job,d.loc
from emp e,dept d
where e.deptno=d.deptno
and e.job!='程序员';
select e.ename,e.sal,d.dname
from emp e join dept d
on e.deptno=d.deptno
where e.sal>2000;
2. 查询不是程序员的员工姓名,工作和部门所在地
select e.ename,e.job,d.loc
from emp e join dept d
on e.deptno=d.deptno
where e.job!='程序员';
select d.dname,e.ename
from emp e join dept d
on e.deptno=d.deptno;
insert into emp(empno,ename) values(20,'灭霸');
select e.ename,d.dname
from emp e left join dept d
on e.deptno=d.deptno;
###如何使用JDBC
<!-- 连接MySQL数据库的依赖 -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.15</version>
</dependency>
//1. 注册驱动 通知编译器使用的是什么数据库 抛出异常
//Class.forName("com.mysql.cj.jdbc.Driver");
//2. 获取数据库连接 参数介绍:1.url连接地址 2.用户名 3.密码(自己mysql密码)
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 jdbct2(name varchar(10),age int)";
s.execute(sql);//执行SQL
System.out.println("执行成功!");
//5. 关闭资源
conn.close();