一.数据库合并查询
补充:合并查询(把两张表的记录合并在一起显示)
## union取两张表的交集(字段名和数据类型相同)
## union all是显示两张表的全部
SELECT
*
FROM
A
UNION ## ALL
SELECT
*
FROM
B;
运行结果:
含all的运行结果:
1.笛卡尔积
两张表一起查,出现大量重复数据,出现笛卡尔积
即如下:
a(a,b,c) 和 b(0,1,2)的笛卡尔积
结果是:
(a,0) (a,1) (a,2)
(b,0) (b,1) (b,2)
(c,0) (c,1) (c,2)
SELECT * FROM A,B;
表A:表B:
结果图:
2.去除重复数据(99查询法 通过两张表的关联字段相同 来去除)
表score:
表student:
表course:
查询两表信息:
SELECT
*
FROM
score,
student
WHERE
score.stuid = student.stuid;
3.起别名,多表查询时,不一定非要有外键
SELECT
sc.score,
s.stuname
FROM
student s,
score sc
WHERE
s.stuid = sc.stuid;
4.连接查询(多表查询)
- 内连接 外连接 自然连接
-
- 内连接 表1 inner(可省略) join 表2 on 去除重复的条件
- 注意 on 后边只能加去除重复的条件
查询表中80分以上学生的 姓名 分数 科目信息:
SELECT
*
FROM
student s
JOIN score sc ON s.stuid = sc.stuid
JOIN course c ON sc.courseid = c.courseid
WHERE
sc.score > 80;
运行结果:
- 2.外连接
- 左外连接 (left outer[可省略] join) (以left左边那张表为主,会输出这张表的全部数据)
- 右外连接 (right outer[可省略] join) (以right右边那张表为主,会输出这张表的全部数据)
SELECT
*
FROM
student s
LEFT JOIN score sc ON s.stuid = sc.stuid;
运行结果:
5.自然连接(natural join)
自动匹配表中关联条件(字段名和类型相同才可以)
SELECT
*
FROM
student
NATURAL JOIN score;
运行结果:
6.子查询(嵌套查询)
可以将两个sql语句 嵌套在一起
## 查询工资高于JONES的员工信息
## 步骤:1.先查JONES的工资;2.利用JONES的工资当条件 查询高于他的员工信息;
SELECT
*
FROM
emp
WHERE
sal > (
SELECT
sal
FROM
emp
WHERE
ename = 'JONES'
);
运行结果图:
7.自连接 把自己当做一个新表使用
求7369员工编号、姓名、经理编号和经理姓名
## 把两个表,一个当做员工表,一个当做管理者表
SELECT e1.empno,e1.ename,e2.ename FROM emp e1,emp e2
WHERE e1.empno = e2.mgr AND e2.empno = 7369;
8. 将查询完 返回的数据 当做一张新表 来使用
求各个部门薪水最高的员工所有信息
1.错误写法:
## (in 是取符合这几个值的信息,但是也有可能其他部门的员工工资也在里面,但不是最高的)
SELECT * FROM emp WHERE sal in (
SELECT MAX(sal) FROM emp GROUP BY deptno
);
2.正确写法:
## 将查询完 返回的数据 当做一张新表 来使用
SELECT * FROM emp e1,(
SELECT deptno,MAX(sal) msal FROM emp GROUP BY deptno
) e2 WHERE e1.deptno = e2.deptno AND e1.sal = e2.msal;
二.JDBC(数据库连接)
- JDBC(Java DataBase Connection)
- 是Java为连接数据库提供的一套规范(接口)
- 这套规范的实现和我们无关,是数据库厂商来实现
- 咱们只负责 使用厂商提供好的实现完的方法(驱动程序)
- 连接数据库步骤:
- 在此之前要先导入jar包
- 1.加载驱动(注册驱动类)
- 2.获取数据库将连接(通过数据库账号和密码)
- 3.通过数据库连接对象,获取sql语句的执行对象
- 4.使用sql语句执行对象 执行sql语句
- 5.接收执行sql后结果及处理
- 6.关闭资源
表sort:
表users:
- 插入一条数据, 代码如下:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import com.mysql.jdbc.Driver;
public class Kll {
public static void main(String[] args) throws Exception {
// 注册驱动
// 点入driver类的源码中,发现有个静态代码块
// 在静态代码块中 系统已经为你注册了驱动
// 这时不能重复注册
// DriverManager.registerDriver(new Driver());
// 使用反射来加载驱动类
// com.mysql.jdbc.Driver
Class<?> c = Class.forName("com.mysql.jdbc.Driver");
// 获取连接
// 参数url:数据库地址
// jdbc:mysql://主机ip地址:数据库端口号/数据库名
// jdbc:mysql://localhost:3306/klljdbc01
String url = "jdbc:mysql://localhost:3306/testdb1";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
// 通过数据库连接对象,获取sql语句的执行对象
Statement statement = connection.createStatement();
// 使用sql语句执行对象 执行sql语句
// statement.executeUpdate(sql) 执行DDL DML语句
// statement.executeQuery(sql) 执行DQL语句
// 插入一条数据
String sql = "insert into sort"
+ "(sname,sprice,sdesc) values"
+"('毛毯','30','好用方便包邮')";
int update = statement.executeUpdate(sql);
System.out.println("受影响的行数" + update);
// 关闭资源
// 关闭连接对象
connection.close();
// 关闭sql语句执行对象
statement.close();
}
}
2.查询全表 sort 打印数据
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class Demo02 {
public static void main(String[] args) throws Exception {
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 获取连接
String url = "jdbc:mysql://localhost:3306/klljdbc01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
// 获取执行sql对象
Statement statement = connection.createStatement();
// 执行sql
// 使用*查找 列的索引是数据库表字段的顺序
// 如果没有使用* 手写字段 那么索引的顺序就是你写的顺序
// 默认列索引从1开始
String string = "select * from sort";
ResultSet set = statement.executeQuery(string);
// 处理结果集
while (set.next()) {
// 获取值
//int i = set.getInt(1);
//String str = set.getString(2);
int i = set.getInt("sid");
String str = set.getString("sname");
System.out.println(i + " " + str);
}
// 关闭
connection.close();
statement.close();
set.close();
}
}
3.sql注入
需求:
- 键盘输入账号密码
- 根据账户密码去数据库查询用户信息
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Demo03 {
public static void main(String[] args) throws Exception {
System.out.println("请输入账号:");
Scanner sc = new Scanner(System.in);
String uName = sc.nextLine();
System.out.println("请输入密码:");
String uPassword = sc.nextLine();
//
Class.forName("com.mysql.jdbc.Driver");
String url = "jdbc:mysql://localhost:3306/klljdbc01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
// 创建连接
Statement statement = connection.createStatement();
String sql = "select * from users where username = '" + uName + "' and password = '" + uPassword + "'";
ResultSet resultSet = statement.executeQuery(sql);
while (resultSet.next()) {
System.out.println(resultSet.getInt("id"));
System.out.println(resultSet.getString("username"));
System.out.println(resultSet.getString("password"));
}
connection.close();
statement.close();
resultSet.close();
}
}
sql 注入 是指 带有(‘or’ 1=1)类的输入出现,会把原来的sql语句的条件改变,是输出的结果超出预期.
4.解决sql注入的方法
prepareStatement方法 对sql语句进行预编译.
sql 语句需要使用占位符 ? 来替换传入的值.
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;
public class Demo04 {
public static void main(String[] args) throws Exception {
System.out.println("请输入账号:");
Scanner sc = new Scanner(System.in);
String uName = sc.nextLine();
System.out.println("请输入密码:");
String uPassword = sc.nextLine();
// 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 获取连接
String url = "jdbc:mysql://localhost:3306/klljdbc01";
String user = "root";
String password = "123456";
Connection connection = DriverManager.getConnection(url, user, password);
// 获取执行sql对象
// prepareStatement 对sql语句进行预编译
// sql 语句需要使用占位符 ? 来替换传入的值
String sql = "select * from users where username = ? and password = ?";
PreparedStatement statement = connection.prepareStatement(sql);
// 给占位符赋值
// 参数1是问号的索引,从1开始
statement.setObject(1, uName);
statement.setObject(2, uPassword);
// 执行查询
ResultSet resultSet = statement.executeQuery();
while (resultSet.next()) {
System.out.println(resultSet.getInt("id"));
System.out.println(resultSet.getString("username"));
System.out.println(resultSet.getString("password"));
}
// 关闭
connection.close();
statement.close();
resultSet.close();
}
}