数据库 III

1.分组查询 group by

 格式: group by 分组的字段名

1. 查询每个部门的平均工资
   select dept_id,avg(sal) from emp group by dept_id;

2. 查询每个部门的最高工资
   select dept_id,max(sal) from emp group by dept_id;

3. 查询每种工作的最高工资
   select job,max(sal) from emp group by job;

4. 查询每种工作的人数
   select job,count(*) from emp group by job;

5. 查询每个部门工资高于2000的人数
   select dept_id,count(*) from emp where sal>2000 group by dept_id;

6. 查询每个部门有领导的员工的人数
   select dept_id,count(*) from emp where manager is not null group by dept_id;

2.having

 where后面只能写普通字段的条件,不能包含聚合函数
 having后面可以包含聚合函数的条件,需要和group by结合使用,写在group by的后面

1. 查询每个部门的平均工资要求平均工资高于2000
   select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;

2. 查询每种工作的人数,只查询人数大于1 的
   select job,count() from emp group by job having count()>1;
   select job,count(*) c from emp group by job having c>1;

3. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400
   select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;

4. 查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的
   select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a>=2000;

3.各个关键字的书写顺序

select 查询的字段信息 from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 limit 跳过条数,请求条数;

子查询(嵌套查询)

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 max(sal) from emp;
   select * from emp where sal=(select max(sal) from emp);

4. 查询和孙悟空相同工作的员工信息
   select job from emp where name=“孙悟空”;
   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);

4.关联关系

 指创建的表和表之间存在的业务关系
 有哪几种关系?
 一对一: 有AB两张表,A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据

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

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

 表和表之间如何建立关系 ,通过外键字段建立关系
 一对一: 在任意表中添加一个建立关系的字段指向另外一张表的主键 .
 一对多: 在多的表中添加建立关系的字段(外键) 指向另外一张表的主键 .
 多对多: 需要创建一个单独的关系表,里面至少包含两个字段分别指向另外两个表的主键.

5.关联查询

 同时查询多张表数据的查询方式称为关联查询
 关联查询包括: 等值链接, 内连接和外连接

6。关联查询之等值链接

 格式: select * from A,B where 关联关系

1. 查询工资高于2000的员工姓名和对应的部门名
   select e.name,d.name,sal
   from emp e,dept d where e.dept_id=d.id and sal>2000;

7.关联查询之内连接

 格式: select * from A join B on 关联关系

1. 查询工资高于2000的员工姓名和对应的部门名
   select e.name,d.name,sal from emp e join dept d on e.dept_id=d.id where sal>2000;

8.关联查询之外连接

 等值链接和内连接查询到的都是两张表的交集数据
 外连接查询的是一张表的全部和另外一张表的交集数据
 格式: select * from A left/right join B on 关联关系
insert into emp(name,sal) values(“灭霸”,5);

1. 查询所有员工姓名和对应的部门名
   select e.name,d.name from emp e left join dept d on e.dept_id=d.id;

2. 查询所有部门的名称,地点和对应的员工姓名和工资
   select d.name,loc,e.name,sal
   from emp e right join dept d on e.dept_id=d.id;

9.关联查询总结:

1. 如果需要同时查询多张表的数据使用关联查询
2. 关联查询包括:等值链接,内连接和外连接
3. 等值链接和内连接查询的是两个表的交集数据, 推荐使用内连接
4. 如果需要查询一张表的全部和另外一张表的交集时 使用外连接,只需要掌握左外即可,因为表的位置可以交换

10.如何查询多对多表中的数据

 创建表:
create table student(id int primary key auto_increment,name varchar(50))charset=utf8;
create table teacher(id int primary key auto_increment,name varchar(50))charset=utf8;
create table t_s(tid int,sid int);

 插入数据
insert into teacher values(null,“苍老师”),(null,“传奇哥”);
insert into student values(null,“小明”),(null,“小红”),(null,“小绿”),(null,“小狗”),(null,“小黄”);
insert into t_s values(1,1),(1,5),(1,4),(2,2),(2,3),(2,1),(2,5);

1. 查询每个老师对应的学生
   select t.name,s.name
   from teacher t join t_s ts on t.id=ts.tid
   join student s on s.id=ts.sid;

2. 查询苍老师的学生都有谁?
   select s.name
   from teacher t join t_s ts on t.id=ts.tid
   join student s on s.id=ts.sid
   where t.name=“苍老师”;

3. 查询小明的老师是谁?
   select t.name
   from teacher t join t_s ts on t.id=ts.tid
   join student s on s.id=ts.sid
   where s.name=“小明”;

11.JDBC

 JavaDataBaseConnectivity: Java数据库链接
 学习JDBC主要学习的就是如何通过Java语言和MySQL数据库进行链接并执行SQL语句.
 JDBC是Sun公司提供的一套专门用于Java语言和数据库进行链接的API(Application Programma Interface应用程序编程接口)
 为什么使用JDBC?
Sun公司为了避免Java程序员,每一种数据库软件都学习一套全新的方法,通过JDBC接口将方法名定义好, 让各个数据库厂商根据此接口中的方法名写各自的实现类(就是一个jar文件, 称为数据库的驱动) ,这样Java程序员只需要掌握JDBC接口中方法的调用,即可访问任何数据库软件.
 如何通过JDBC链接数据库并执行SQL语句
 创建Maven工程
 在工程的pom.xml文件中 添加MySQL驱动的依赖坐标
 创建cn.tedu.Demo01.java文件 添加以下代码

//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("create table jdbct1(age int)");
//4.关闭资源
conn.close();
System.out.println("执行完成!");

12.执行SQL语句的对象Statement

 execute(“sql”); 此方法可以执行任意SQL语句, 但建议执行DDL(数据库相关和表相关的SQL语句)
 int row = executeUpdate(“sql”); 此方法用来执行增删改相关的SQL语句, 返回值表示生效的行数
 ResultSet rs = executeQuery(“sql”); 此方法用来执行查询相关的SQL语句,返回值ResultSet叫做结果集对象,查询到的数据都装在此对象中

我是将军；我一直都在，。！