数据库 day03

内容回顾:

1. 数据库相关SQL

• 查询所有 show databases;

• 创建 create database db1 charset=utf8/gbk;

• 查询信息 show create database db1;

• 删除 drop database db1;

• 使用 use db1;

1. 表相关SQL

• 创建 create table t1(name varchar(20)) charset=utf8/gbk;

• 查询所有 show tables;

• 查询表信息 show create table t1;

• 表字段 desc t1;

• 删除表 drop table t1;

• 修改表名 rename table t1 to t2;

• 添加字段 alter table t1 add age int first/ after xxx;

• 删除字段 alter table t1 drop age;

• 修改字段 alter table t1 change 原名 新名 新类型;

1. 数据相关SQL

• 插入数据 insert into t1(字段1名,字段2名) values(值1,值2),(值1,值2),(值1,值2);

• 查询数据 select 字段信息 from 表名 where 条件;

• 修改数据 update 表名 set xxx=xxx,xxx=xxx where 条件;

• 删除数据 delete from 表名 where 条件;

1. SQL语句分类

• DDL: 数据定义语言 包括 数据库相关和表相关的SQL

• DML: 数据操作语言 包括 增删改查

• DQL: 数据查询语言 包括 select查询

• TCL: 事务控制语言

• DCL: 数据控制语言

1. 去重distinct

1. and or not

1. between x and y 两者之间

1. in(xxx,xxx)

1. 模糊查询like %0或多个未知字符 _1个未知字符

1. 排序 order by 字段名 asc/desc,字段名 asc/desc

1. 分页 limit 跳过的条数,请求条数(每页条数)

1. 别名 select name 名字 from emp;

1. 聚合函数: 求平均值avg(), 最大值max(), 最小值min(),求和sum(),计数count(*)

分组查询 group by

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

1. 查询每个部门的平均工资

select dept_id,avg(sal) from emp group by dept_id;

1. 查询每个部门的最高工资

select dept_id,max(sal) from emp group by dept_id;

1. 查询每种工作的最高工资

select job,max(sal) from emp group by job;

1. 查询每种工作的人数

select job,count(*) from emp group by job;

1. 查询每个部门工资高于2000的人数

select dept_id,count(*) from emp where sal>2000 group by dept_id;

1. 查询每个部门有领导的员工的人数

select dept_id,count(*) from emp where manager is not null group by dept_id;

having

• where后面只能写普通字段的条件,不能包含聚合函数

• having后面可以包含聚合函数的条件,需要和group by结合使用,写在group by的后面

1. 查询每个部门的平均工资要求平均工资高于2000

select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;

1. 查询每种工作的人数,只查询人数大于1 的

select job,count(*) from emp group by job having count(*)>1;

select job,count(*) c from emp group by job having c>1;

1. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于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;

各个关键字的书写顺序

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);

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

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

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

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

select max(sal) from emp;

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

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

select job from emp where name="孙悟空";

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

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

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);

关联关系

• 指创建的表和表之间存在的业务关系

• 有哪几种关系?

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

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

 

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

 

• 表和表之间如何建立关系 ,通过外键字段建立关系

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

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

• 多对多: 需要创建一个单独的关系表,里面至少包含两个字段分别指向另外两个表的主键.

关联查询

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

• 关联查询包括: 等值链接, 内连接和外连接

关联查询之等值链接

• 格式: 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;

关联查询之内连接

• 格式: 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;

关联查询之外连接

• 等值链接和内连接查询到的都是两张表的交集数据

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

• 格式: 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;

1. 查询所有部门的名称,地点和对应的员工姓名和工资

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

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

关联查询总结:

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

1. 关联查询包括:等值链接,内连接和外连接

1. 等值链接和内连接查询的是两个表的交集数据, 推荐使用内连接

1. 如果需要查询一张表的全部和另外一张表的交集时 使用外连接,只需要掌握左外即可,因为表的位置可以交换

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

• 创建表:

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;

1. 查询苍老师的学生都有谁?

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="苍老师";

1. 查询小明的老师是谁?

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="小明";

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文件 添加以下代码

 

执行SQL语句的对象Statement

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

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

• ResultSet rs = executeQuery("sql"); 此方法用来执行查询相关的SQL语句,返回值ResultSet叫做结果集对象,查询到的数据都装在此对象中