1.连接查询

1.1综合练习（之前学习的小练习）

查询工资在1000到2000之间有领导的员工信息 select * from emp where sal between 1000 and 2000 and manager is not null;查询有领导的员工工资按照降序排序取前三条数据 select * from emp where manager is not null order by sal desc limit 0,3;查询1号和3号部门出现了哪几种不同的工作 select distinct job from emp where dept_id in(1,3);查询出只有一个人的工作名称 select job from emp group by job having count(*)=1;查询每种工作的人数按照人数降序排序 select job,count(*) from emp group by job order by count(*) desc;查询人数最多的工作名称 select job from emp group by job order by count(*) desc limit 0,1;查询唐僧领导的信息 select * from emp where id=(select manager from emp where name='唐僧');查询比名字中包含飞员工工资高的员工信息 select * from emp where sal>(select sal from emp where name like "%飞%");

1.2关联关系

指创建表时,表和表之间存在的业务关系.包含以下几种关系:一对一: 有AB两张表, A表中的一条数据对应B表中的一条数据,同时B表中的一条数据也是对应A表中的一条数据一对多:有AB两张表, A表中的一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的一条数据多对多:有AB两张表, A表中的一条数据对应B表中的多条数据,同时B表中的一条数据也对应A表中的多条数据如果两张表之间存在业务关系,如何建立关系? 一对一: 在两张表中的任意一张表里面添加建立关系的字段,指向另外一张表的主键一对多: 一对多的两张表, 在"多"的表里面添加建立关系的字段,指向另外一张表的主键多对多: 需要创建一个单独的关系表,表里面至少两个字段指向另外两张表的主键 .

1.3关联查询

同时查询存在管理关系的多张表的数据时使用的查询方式包含三种查询方式: 等值连接内连接外连接

1.3.1关联查询之等值连接

格式: select * from A,B where A.x=B.x查询每个员工的姓名和对应的部门名 select e.name,d.namefrom emp e,dept d where e.dept_id=d.id;查询工资高于2000的员工姓名,工资和对应的部门名和地址 select e.name,sal,d.name,locfrom emp e,dept d where e.dept_id=d.id and sal>2000;

1.3.2关联查询之内连接

等值连接和内连接查询到的数据是一样的都是两个表的交集数据,只是书写格式不一样格式: select * from A join B on A.x=B.x查询每个员工的姓名和对应的部门名 select e.name,d.namefrom emp e join dept d on e.dept_id=d.id;查询工资高于2000的员工姓名,工资和对应的部门名和地址 select e.name,sal,d.name,locfrom emp e join dept d on e.dept_id=d.id where sal>2000;

1.3.3关联查询之外连接

外连接查询到的是一张表的全部和另外一张表的交集格式: select * from A left/right join B on A.x=B.x查询所有员工姓名和对应的部门信息 insert into emp(name,sal) values('灭霸',5);select e.name,d.*from emp e left join dept d on e.dept_id=d.id;查询所有部门名,部门地址和对应的员工姓名 select d.name,loc,e.namefrom emp e right join dept d on e.dept_id=d.id;

1.3.4关联查询总结:

如果查询的数据是来自多张表,则需要使用关联查询如果查询的是两个表的交集数据则使用等值连接或内连接(推荐)如果查询的是一张表的全部和另外一张表的交集则使用外连接

2.JDBC

作用: 通过Java代码执行SQL语句JavaDataBaseConnectivity: Java数据库链接,JDBC是Sun公司提供的一套用于Java语言和数据库软件进行连接的API(Application Programma Interface应用程序编程接口)Sun公司为了避免Java程序员每一种数据库软件都学习一套新的方法, 通过JDBC接口将方法名定义好, 各个数据库厂商根据JDBC里面的方法名写各自的实现类(jar包) , 这样Java程序员只需要掌握JDBC接口中方法的调用,将来就算是换了数据库软件, 代码是不需要改变的,这样的话就降低了Java程序员的学习成本

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

创建maven工程在工程的pom.xml文件中添加以下内容 然后刷新maven

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

检查是否出现了和mysql相关的jar包文件如果没有出现 ,检查pom.xml里面是否书写正确, 如果确定书写没问题,检查maven配置是否正确, 如果正确 则删除.m2文件夹下的repository文件夹然后回到工程中 再次刷新maven , 如果还未解决联系项目经理现场或远程解决在main文件夹中添加cn.tedu.Demo01.java文件添加main方法, 并在里面添加如下代码:

​​​​

package cn.tedu;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Demo01 {public static void main(String[] args) throws SQLException {//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语句s.execute("create table jdbct1(id int)");//4.关闭资源conn.close();System.out.println("执行完成!");}}

2.2SQL语句分类

DDL:数据定义语言,包含数据库相关和表相关的SQL语句DML:数据操作语言,包括增删改查DQL:数据查询语言,只包含查询TCL:事务控制语言, 包含和事务相关DCL:数据控制语言, 包含和用户管理和权限分配相关的SQL语句

2.3Statement执行SQL语句的对象

execute("sql"); 此方法可以执行任意SQL语句,推荐执行数据库相关和表相关的SQL语句(DDL数据定义语言)int row = executeUpdate("sql"); 此方法执行增删改相关SQL语句,返回值表示生效的行数ResultSet rs = executeQuery("sql"); 此方法执行查询相关SQL语句,返回值里面装着查询回来的数据

2.4综合练习

创建jdbc02工程, 在pom.xml文件中添加 MySQL相关的依赖信息 然后刷新maven

<?xml version="1.0" encoding="UTF-8"?><project xmlns="/POM/4.0.0"xmlns:xsi="/2001/XMLSchema-instance"xsi:schemaLocation="/POM/4.0.0 /xsd/maven-4.0.0.xsd"><parent><artifactId>t-jsd2204-3</artifactId><groupId>org.example</groupId><version>1.0-SNAPSHOT</version></parent><modelVersion>4.0.0</modelVersion><artifactId>jdbc02</artifactId><properties><piler.source>8</piler.source><piler.target>8</piler.target></properties><!--依赖标签--><dependencies><!-- 连接MySQL数据库的依赖 --><dependency><groupId>mysql</groupId><artifactId>mysql-connector-java</artifactId><version>8.0.15</version></dependency></dependencies></project>

创建cn.tedu.Demo01类文件 在里面创建一个 hero表, 有id,name两个字段

package cn.tedu;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Demo01 {public static void main(String[] args) throws SQLException {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","root");Statement s = conn.createStatement();s.execute("create table hero(id int primary key auto_increment,name varchar(20))");conn.close();System.out.println("创建完成!");}}

创建Demo02, 往hero表里面添加进去 刘备,关羽,张飞三条数据

package cn.tedu;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Demo02 {public static void main(String[] args) throws SQLException {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","root");Statement s = conn.createStatement();s.executeUpdate("insert into hero values(null,'刘备'),(null,'关羽'),(null,'张飞')");conn.close();System.out.println("添加完成!");}}

创建Demo03, 把刘备改成孙悟空

package cn.tedu;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Demo03 {public static void main(String[] args) throws SQLException {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","root");Statement s = conn.createStatement();s.executeUpdate("update hero set name='孙悟空' where name='刘备'");conn.close();System.out.println("修改完成!");}}

创建Demo04, 查询hero表里面所有名字在控制台输出

package cn.tedu;import java.sql.*;public class Demo04 {public static void main(String[] args) throws SQLException {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","root");Statement s = conn.createStatement();ResultSet rs = s.executeQuery("select name from hero");while(rs.next()){String name = rs.getString("name");System.out.println(name);}conn.close();}}

创建Demo05, 把孙悟空删除

package cn.tedu;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;import java.sql.Statement;public class Demo05 {public static void main(String[] args) throws SQLException {Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false","root","root");Statement s = conn.createStatement();s.executeUpdate("delete from hero where name='孙悟空'");conn.close();System.out.println("删除完成!");}}

2.5数据库连接池DBCP

DataBaseConnectionPool: 数据库连接池作用: 将连接重用,从而提高执行效率如何使用连接池?在pom.xml文件中再添加一个和连接池相关的依赖相关代码:

package cn.tedu;import com.alibaba.druid.pool.DruidDataSource;import java.sql.Connection;import java.sql.SQLException;public class Demo05 {public static void main(String[] args) throws SQLException {//创建连接池对象DruidDataSource dds = new DruidDataSource();//设置连接数据库的信息dds.setUsername("root");dds.setPassword("root");dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false");//设置初始连接数量 和 最大连接数量dds.setInitialSize(3);dds.setMaxActive(5);//从连接池中获取连接 异常抛出Connection conn = dds.getConnection();System.out.println("连接对象:"+conn);}}

封装了以后：

package cn.tedu;import com.alibaba.druid.pool.DruidDataSource;import java.sql.Connection;import java.sql.DriverManager;import java.sql.SQLException;public class DBUtils {private static DruidDataSource dds;static {//创建连接池对象dds = new DruidDataSource();//设置连接数据库的信息dds.setUsername("root");dds.setPassword("root");dds.setUrl("jdbc:mysql://localhost:3306/empdb?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false");//设置初始连接数量 和 最大连接数量dds.setInitialSize(3);dds.setMaxActive(5);}public static Connection getConn() throws SQLException {//从连接池中获取连接 异常抛出Connection conn = dds.getConnection();System.out.println("连接对象:"+conn);return conn;}}

2.6注册登录功能

创建用户表 use empdb;create table user(id int primary key auto_increment,username varchar(50),password varchar(50),nick varchar(50));select count(*) from user where username='tom' and password='asdf';

2.6.1注册案例

package cn.tedu;import java.sql.Connection;import java.sql.SQLException;import java.sql.Statement;import java.util.Scanner;public class Demo06 {public static void main(String[] args) {Scanner sc = new Scanner(System.in);System.out.println("请输入用户名");String username = sc.nextLine();System.out.println("请输入密码");String password = sc.nextLine();System.out.println("请输入昵称");String nick = sc.nextLine();//获取连接对象try (Connection conn = DBUtils.getConn()){Statement s = conn.createStatement();String sql = "insert into user values(null,'"+username+"','"+password+"','"+nick+"')";s.executeUpdate(sql);System.out.println("注册成功!");} catch (SQLException throwables) {throwables.printStackTrace();}}}

2.6.2登录案例

package cn.tedu;import javax.swing.plaf.nimbus.State;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import java.util.Scanner;public class Demo07 {public static void main(String[] args) {Scanner sc = new Scanner(System.in);System.out.println("请输入用户名");String username = sc.nextLine();System.out.println("请输入密码");String password = sc.nextLine();//获取连接try (Connection conn = DBUtils.getConn()){Statement s = conn.createStatement();String sql = "select count(*) from user where username='"+username+"' and password='"+password+"'";System.out.println(sql);ResultSet rs = s.executeQuery(sql);//让游标指向第一条数据rs.next();//从结果集对象中取出查询到的数量int count = rs.getInt(1);if (count>0){System.out.println("登录成功!");}else{System.out.println("用户名或密码错误!");}} catch (SQLException throwables) {throwables.printStackTrace();}}}

2.7SQL注入

往用户传值的地方传递进去了SQL语句导致原有SQL语句的逻辑发生改变,这个过程称为SQL注入, SQL注入漏洞是网站中非常低级的漏洞但是危害性很高. 注入内容: ' or '1'='1select count(*) from userwhere username='asdf' and password='' or '1'='1'需要使用PreparedStatement来实现，明天讲

如果觉得《JSD-2204-连接查询-JDBC-Day10》对你有帮助，请点赞、收藏，并留下你的观点哦！