数据库04-子查询(嵌套查询)/数值计算+ - * / %/关联关系/关联查询/等值连接/内连接/外连接/JDBC/Statement执行SQL语句的对象/DBCP

1.查询工资高于2号部门平均工资的员工信息

select avg(sal) from emp where dept_id=2;

select * from emp where sal>(select avg(sal) from emp where dept_id=2);

2.查询工资高于程序员最高工资的员工信息

select max(sal) from emp where job='程序员';

select * from emp where sal>(select max(sal) from emp where job='程序员');

3.查询工资最高的员工信息

select * from emp where sal=(select max(sal) from emp);

4.查询和孙悟空相同工作的员工信息

select * from emp where job=(select job from emp where name='孙悟空') and name!="孙悟空";

5.查询拿最低工资的员工同事们的信息(同事指同一部门)

select min(sal) from emp;

select dept_id from emp where sal=(select min(sal) from emp);

select * from emp where dept_id=(select dept_id from emp where sal=(select min(sal) from emp)) and sal!=(select min(sal) from emp);

数值计算+ - * / %

1.查询每个员工的姓名,工资和年终奖(5个月的工资)

select name,sal,5*sal 年终奖 from emp;

2.给3号部门的员工每人涨薪5块钱

update emp set sal=sal+5 where dept_id=3;

关联关系

• 创建表的时候,表和表之间存在的业务关系.

• 有哪几种关系:

  • 一对一: 有AB两张表,A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据.

• 一对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条数据.

• 多对多:有AB两张表,A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条数据.

• 如果表之间存在关联关系,如何建立关系?

  • 一对一: 在两张表的任意一张表中添加建立关系的字段指向另外一张表的主键

  • 一对多: 在多的表中添加建立关系的字段指向另外一张表的主键

  • 多对多: 新建一张关系表,表中至少有两个字段用于建立关系, 指向另外两张表的主键

关联查询

• 同时从多张表中查询数据的方式称为关联查询

• 包括三种查询方式:

  • 等值连接

  • 内连接

  • 外连接

等值连接

• 格式: select 字段信息 from A,B where A.xxx=B.xxx(关联关系) and 其它条件

1.查询每个员工的姓名\工资和对应的部门名

select e.name,sal,d.name

from emp e,dept d

where e.dept_id=d.id;

2.查询工资高于2000的员工姓名,工资和对应的部门名和地址

select e.name,sal,d.name,loc

from emp e,dept d

where e.dept_id=d.id and sal>2000;

3.查询程序员的部门地址

select distinct loc

from emp e,dept d

where e.dept_id=d.id and job="程序员";

内连接

• 等值连接和内连接的作用一样,查询的都是两个表的"交集数据"(存在关系的数据)

• 格式: select 字段信息 from A join B on A.x=B.x where 其它条件

1.查询每个员工的姓名\工资和对应的部门名

select e.name,sal,d.name

from emp e join dept d on e.dept_id=d.id;

2.查询工资高于2000的员工姓名,工资和对应的部门名和地址

select e.name,sal,d.name,loc

from emp e join dept d on e.dept_id=d.id where sal>2000;

3.查询程序员的部门地址

select distinct loc

from emp e join dept d on e.dept_id=d.id where job="程序员";

外连接

• 外连接查询到的是一张表的全部和另外一张表的交集数据

• 格式: select 字段信息 from A left/right join B on A.x=B.x where 其它条件

insert into emp(name,sal) values("灭霸",5);

1.查询所有的员工姓名和对应的部门名

select e.name,sal,d.name

from emp e left join dept d on e.dept_id=d.id;

2.查询所有部门名和对应的员工姓名

select d.name,e.name

from emp e right join dept d on e.dept_id=d.id;

关联查询总结:

• 如果需要同时查询多张表的数据使用关联查询

• 如果查询到的是两张表的交集数据使用等值连接或内连接(推荐)

• 如果查询的是一张表的全部和另外一张表的交集则使用外连接

JDBC

• Java DataBase Connectivity: Java数据库连接

• JDBC是Sun公司提供的一套专门用于Java语言和数据库软件进行连接的API(Application Programma Interface) , Sun公司为了避免Java程序员每一种数据库软件都学习一套全选的方法, Sun公司通过JDBC接口将方法名固定好,各个数据库厂商根据此接口中的方法名写各自的实现类(驱动) , 这样的话对于Java程序员 不管连接什么数据库软件 方法名是一样的,即使某一天换了数据库软件 代码是不需要改变的,这样大大提高了开发效率.

• 如何通过JDBC和数据库软件进行连接并执行SQL语句

1. 创建Maven工程

2. 在pom.xml里面添加以下依赖

   <!--dependency依赖 dependencies多个依赖-->
    <dependencies>
        <!-- 连接MySQL数据库的依赖 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.15</version>
        </dependency>
    </dependencies>

1. 创建Demo01.java ,添加main方法,在方法中添加以下代码

//1. 获取数据库连接对象  异常抛出
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false",
                "root","root");
        System.out.println("连接对象:"+conn);
                //2. 创建执行SQL语句的对象
        Statement s = conn.createStatement();
        //3. 执行SQL语句 execute执行
        s.execute("drop table jdbct1");
        //4. 关闭资源
        conn.close();
        System.out.println("执行完成!");

Statement执行SQL语句的对象

• execute("sql"); 此方法可以执行任意SQL语句, 推荐执行数据库相关和表相关的SQL语句

• int row = executeUpdate("sql"); 此方法执行增删改 相关的SQL语句, 返回值表示生效的行数

• ResultSet rs = executeQuery("sql"); 执行查询相关的SQL语句,查询到的结果装在ResultSet对象中.

DBCP

• DataBaseConnectionPool数据库连接池

• 作用: 将连接重用,避免频繁的开关连接, 从而提高执行效率

• 如何使用DBCP?

  • 使用dbcp的依赖 赋值到工程的pom.xml里面

• 通过以下代码创建连接池,并从连接池对象中获取连接

//创建连接池对象
        DruidDataSource dds = new DruidDataSource();
        //设置连接数据库的信息
        dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false");
        dds.setUsername("root");
        dds.setPassword("root");
        //设置初始连接数量
        dds.setInitialSize(3);
        //设置最大连接数量
        dds.setMaxActive(5);
        //从连接池中获取连接对象  异常抛出
        Connection conn = dds.getConnection();
        System.out.println("连接对象:"+conn);