内容回顾:
- 数据库相关SQL
-
查询所有 show databases;
-
创建 create database db1 charset=utf8/gbk;
-
查询信息 show create database db1;
-
删除 drop database db1;
-
使用 use db1;
- 表相关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 原名 新名 新类型;
- 数据相关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 条件;
- SQL语句分类
-
DDL: 数据定义语言 包括 数据库相关和表相关的SQL
-
DML: 数据操作语言 包括 增删改查
-
DQL: 数据查询语言 包括 select查询
-
TCL: 事务控制语言
-
DCL: 数据控制语言
- 去重distinct
- and or not
- between x and y 两者之间
- in(xxx,xxx)
- 模糊查询like %0或多个未知字符 _1个未知字符
- 排序 order by 字段名 asc/desc,字段名 asc/desc
- 分页 limit 跳过的条数,请求条数(每页条数)
- 别名 select name 名字 from emp;
- 聚合函数: 求平均值avg(), 最大值max(), 最小值min(),求和sum(),计数count(*)
分组查询 group by
- 格式: group by 分组的字段名
-
查询每个部门的平均工资
select dept_id,avg(sal) from emp group by dept_id;
-
查询每个部门的最高工资
select dept_id,max(sal) from emp group by dept_id;
-
查询每种工作的最高工资
select job,max(sal) from emp group by job;
-
查询每种工作的人数
select job,count(*) from emp group by job;
-
查询每个部门工资高于2000的人数
select dept_id,count(*) from emp where sal>2000 group by dept_id;
-
查询每个部门有领导的员工的人数
select dept_id,count(*) from emp where manager is not null group by dept_id;
having
-
where后面只能写普通字段的条件,不能包含聚合函数
-
having后面可以包含聚合函数的条件,需要和group by结合使用,写在group by的后面
- 查询每个部门的平均工资要求平均工资高于2000
select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000;
- 查询每种工作的人数,只查询人数大于1 的
select job,count() from emp group by job having count()>1;
select job,count(*) c from emp group by job having c>1;
- 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400
select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;
- 查询每个部门的平均工资, 只查询工资在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 跳过条数,请求条数;
子查询(嵌套查询)
- 查询工资大于2号部门平均工资的员工信息
select avg(sal) from emp where dept_id=2;
select * from emp where sal>(select avg(sal) from emp where dept_id=2);
- 查询工资高于程序员最高工资的员工信息
select max(sal) from emp where job=“程序员”;
select * from emp where sal>(select max(sal) from emp where job=“程序员”);
- 查询工资最高的员工信息
select max(sal) from emp;
select * from emp where sal=(select max(sal) from emp);
- 查询和孙悟空相同工作的员工信息
select job from emp where name=“孙悟空”;
select * from emp where job=(select job from emp where name=“孙悟空”) and name!=“孙悟空”;
- 查询拿最低工资员工的同事们的信息(同事指同一部门)
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 关联关系
- 查询工资高于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 关联关系
- 查询工资高于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);
- 查询所有员工姓名和对应的部门名
select e.name,d.name from emp e left join dept d on e.dept_id=d.id;
- 查询所有部门的名称,地点和对应的员工姓名和工资
select d.name,loc,e.name,sal
from emp e right join dept d on e.dept_id=d.id;
关联查询总结:
- 如果需要同时查询多张表的数据使用关联查询
- 关联查询包括:等值链接,内连接和外连接
- 等值链接和内连接查询的是两个表的交集数据, 推荐使用内连接
- 如果需要查询一张表的全部和另外一张表的交集时 使用外连接,只需要掌握左外即可,因为表的位置可以交换
如何查询多对多表中的数据
- 创建表:
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);
- 查询每个老师对应的学生
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;
- 查询苍老师的学生都有谁?
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=“苍老师”;
- 查询小明的老师是谁?
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文件 添加以下代码
//1.创建链接对象 异常抛出
Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/empdbcharacterEncoding=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("执行完成!");
执行SQL语句的对象Statement
-
execute(“sql”); 此方法可以执行任意SQL语句, 但建议执行DDL(数据库相关和表相关的SQL语句)
-
int row = executeUpdate(“sql”); 此方法用来执行增删改相关的SQL语句, 返回值表示生效的行数
ment s = conn.createStatement();
//3.执行SQL语句 execute执行
s.execute("create table jdbct1(age int)");
//4.关闭资源
conn.close();
System.out.println("执行完成!");
执行SQL语句的对象Statement
-
execute(“sql”); 此方法可以执行任意SQL语句, 但建议执行DDL(数据库相关和表相关的SQL语句)
-
int row = executeUpdate(“sql”); 此方法用来执行增删改相关的SQL语句, 返回值表示生效的行数
-
ResultSet rs = executeQuery(“sql”); 此方法用来执行查询相关的SQL语句,返回值ResultSet叫做结果集对象,查询到的数据都装在此对象中