Stoker的Java学习之链接查询与JDBC基础

Java学习之DQL语句与JDBC基础

一.DQL语句

1.合并查询（把两张表的记录合并到一起)

UNION 取两张表交集（字段名数据类型相同）

SELECT * FROM a
UNION 
SELECT * FROM b;

UNION ALL把两张表合并,不会去除重复的

SELECT * FROM a
UNION ALL
SELECT * FROM b;

Stoker的Java学习之链接查询与JDBC基础

去除重复数据（99查询法通过两张表关联的字段相等来去除）

SELECT * FROM student,score
WHERE student.stuid=score.stuid;

查询学生姓名和学生的分数(利用学生表和分数表)

SELECT student.stuname,score.score FROM student,score
WHERE student.stuid=score.stuid;

三表查询

SELECT * FROM student s,score sc,course c 
WHERE s.stuid=sc.stuid
AND
sc.courseid=c.courseid;

查询学生的名字对应的分数和科目

SELECT s.stuname,sc.score,c.cname FROM student s,score sc,course c 
WHERE s.stuid=sc.stuid
AND
sc.courseid=c.courseid;

查询表中80分以上学生的姓名分数科目信息

SELECT s.stuname,sc.score,c.cname FROM student s,score sc,course c 
WHERE s.stuid=sc.stuid
AND
sc.courseid=c.courseid
AND sc.score>80;

2 . 链接查询（多表查询）
内链接外连接自然链接
1). 内链接表1 INNER（可以省略） JOIN 表2 ON 去除重复的条件.

SELECT * FROM student s
JOIN
score sc
ON s.stuid=sc.stuid;

三个表 (注意：on后面只能加去除重复的条件)

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). 外连接(OUTER JOIN ON)
左外链接(以left 左边那张表为主会输出这个表的全部数据)
右外链接与左外链接相反

SELECT * FROM student s
RIGHT  JOIN
score sc
ON s.stuid=sc.stuid;

3).自然链接(NATURAL JOIN)
自动匹配表中关联条件（字段名和类型相同）

SELECT * FROM student 
NATURAL JOIN score;

3 .子查询（嵌套查询）
Stoker的Java学习之链接查询与JDBC基础

查询工资高于JONES的员工信息
先查Jones工资
利用Jones工资当条件，查询工资高于JONES的员工信息

SELECT sal FROM emp WHERE ename='jones';
## 可以将两个SQL语句嵌套在一起
SELECT * FROM emp WHERE sal>(
SELECT sal FROM emp WHERE ename='jones'
);

查询与SCOTT同一个部门的员工。

SELECT * FROM emp WHERE deptno=(
SELECT deptno FROM emp WHERE ename='scott'
);

查询工资高于30号部门所有人的员工信息

SELECT MAX(sal) FROM emp WHERE deptno=30;
SELECT * FROM emp WHERE sal>(
SELECT MAX(sal) FROM emp WHERE deptno=30
);

查询工作和工资与MARTIN（马丁）完全相同的员工信息

SELECT job,sal FROM emp WHERE ename='MARTIN';
SELECT * FROM emp WHERE job=(
SELECT job FROM emp WHERE ename='MARTIN'
)AND sal=(
SELECT sal FROM emp WHERE ename='MARTIN'
);

查询有2个以上直接下属的员工信息
mgr这列一共出现几次这个人就有几个下属

SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2;
SELECT * FROM emp WHERE empno in(
SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(mgr)>=2
);

查询员工编号为7788的员工名称、员工工资、部门名称、部门地址

SELECT e.ename,e.sal,d.dname,d.loc FROM emp e,dept d WHERE e.deptno=d.deptno AND empno=7788;

自连接
求7369员工编号、姓名、经理编号和经理姓名

SELECT e1.empno,e1.ename,e2.empno,e2.ename FROM emp e1,emp e2 WHERE e1.empno=e2.mgr
AND e2.empno=7369;
;

求各个部门薪水最高的员工所有信息
错误方法

SELECT * FROM emp WHERE sal in (
SELECT MAX(sal) FROM emp GROUP BY deptno
);

正确方法
将查询完返回的数据当做一张新表来使用

SELECT deptno,MAX(sal) FROM emp GROUP BY deptno;
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(Java Database Connection)

JDBC是Java为链接数据库提供的一套规范（接口）
这套规范谁来实现？
这套规范是由数据库厂商来实现的.
我们只负责使用厂商提供好的实现完的方法(驱动程序).

链接数据库步骤
1.加载驱动（注册驱动类）
2.获取数据库链接(通过数据库账号密码)
3.通过数据库链接对象获取SQL语句的执行对象
4.使用sql执行对象执行SQL语句
5.接受执行SQL后结果集处理
6.关闭资源

1 . 注册驱动
点入Driver类的源码发现有个静态代码快
在静态代码快中已经注册了驱动相当于注册两次
这时不能重复注册
使用反射来加载驱动类
2. 获取链接
参数URL：数据库地址
jdbc:mysql://主键IP地址:数据库端口号/数据库名
jdbc:mysql://localhost:3306/myjdbc01
3.通过数据库链接对象获取SQL语句的执行对象createStatement()
4.使用sql执行对象执行SQL语句
statement.executeUpdate(sql) 执行DDL和DML 语句
statement.executeQuery(sql) 执行DQL语句
5.处理结果集resultSet.next()
6.关闭资源

sql注入问题

键盘输入账号密码
根据账户密码去数据库查询用户信息

public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入账号:");
		String username = scanner.nextLine();
		System.out.println("请输入密码: ");
		String pass = scanner.nextLine();
		
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/wljdbc01";
		String user = "root";
		String password = "123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		Statement statement = connection.createStatement();
		String sql = "select * from users where " + "username='"+ username +"' and password='"+ pass +"'";
		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注入的问题.
sql注入问题:因为SQL语句拼接，传入了SQL语句的关键字。这样做可以绕过数据库的安全检查,从而获取里面的数据
客户端利用JDBC-【Statement】的缺点，传入非法的参数，从而让JDBC返回不合法的值，我们将这种情况下，统称为SQL注入。

解决方法:使用PreparedStatement对象就可以解决。PreparedStatement对象预处理对象。允许使用占位符对SQL语句中的变量进行占位。对SQL语句进行预先编译。传入SQL语句的关键字，不会被当成关键字而是普通的字符串。包括：程序执行时动态为?符号设置值，安全检查，避免SQL注入问题，预处理能力 .
select * from user where username = ? and password = ?

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		Scanner scanner = new Scanner(System.in);
		System.out.println("请输入账号:");
		String username = scanner.nextLine();
		System.out.println("请输入密码: ");
		String pass = scanner.nextLine();
		
		Class.forName("com.mysql.jdbc.Driver");
		String url = "jdbc:mysql://localhost:3306/wljdbc01";
		String user = "root";
		String password = "123456";
		Connection connection = DriverManager.getConnection(url, user, password);
		// 获取执行SQL对象
		// prepareStatement 对SQL语句进行预编译
		// 需要使用占位符? 来替换传入的值
		String sql = "select * from users"
				+ " where username=? and password=?";
		PreparedStatement statement = connection.prepareStatement(sql);
		// 给占位符赋值
		// 参数1是?的索引 从1开始
		statement.setObject(1, username);
		statement.setObject(2, pass);
		// 执行查询
		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();
		
	}

Stoker的Java学习之链接查询与JDBC基础

Java学习之DQL语句与JDBC基础

一.DQL语句

二.JDBC(Java Database Connection)

相关推荐