连接查询
交叉连接
交叉连接会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积。
交叉连接语法:
SELECT * FROM TABLE_A CROSS JOIN TABLE_B
SELECT * FROM TABLE_A JOIN TABLE_B
SELECT * FROM TABLE_A , TABLE_B
三种格式是等效的.
省略连接条件、连接条件无效、所有表中的所有行互相连接的情况下会产生笛卡儿积,为了避免笛卡尔集, 可以在 WHERE 加入有效的连接条件。
在表中有相同列时,在列名之前加上表名前缀以明确区分。
表的别名
AS 可以省略
使用别名可以简化查询。
使用表名前缀可以提高执行效率。
连接多个表
连接 n个表,至少需要 n-1个连接条件。
内连接:
合并具有同一列的两个以上的表的行, 结果集中不包含一个表与另一个表不匹配的行。
SELECT <列名>
FROM 表1 INNER JOIN 表2
ON 表1.列名 条件运算符 表2.列名
[WHERE 条件]
[ORDER BY 排序列]
其中“ON 表1.列名 条件运算符 表2.列名” 中,条件运算符常用的是:=、<>,>,<,>=,<=。
表1.列名和表2.列名,分别是两个表的公共列。
外连接
——左外联结 (LEFT JOIN)
——右外联结 (RIGHT JOIN)
外连接与普通连接的区别
普通连接操作只输出满足连接条件的元组
外连接操作以指定表为连接主体,将主体表中不满足连接条件的元组一并输出:
左外连接:
左外连接使用LEFT JOIN连接两表,连接时左表为主表,获取左表所有记录,左表中的每条记录必定出现在结果集中,而在右表中没有对应的记录,将以NULL值进行填充。
SELECT * FROM TABLE_A
LEFT JOIN TABLE_B
ON 连接条件
[WHERE 条件]
算符常用的是:=、<>,>,<,>=,<=。
右外连接:
右外连接与与外连接相似,不同的是右表为主表,获取右表所有记录,右表中的每条记录必定出现在结果集中,而在左表中没有对应的记录,将以NULL值进行填充。
SELECT * FROM TABLE_A
RIGHT JOIN TABLE_B
ON 连接条件
[WHERE 条件]
算符常用的是:=、<>,>,<,>=,<=。
内连接时,得到的结果是公有数据集C。
左外连接时,得到的是A1+C。
右外连接时,得到的是B1+C。
limit
限制由 SELECT 语句返回的数据数量,limit [offset,N]
offset可选,表示初始位置,默认为0
N,限制结果取N条 。 用法: limit [初始位置],取出条目
几个常用的端口:
web 的端口 80,FTP的端口是 21,HTTP通信用的端口号是80,mysql默认端口是3306,tomcat默认端口是8080。
JDBC
全称为:Java DataBase Connectivity(java数据库连接)。是访问数据库的一种技术
SUN公司为了简化、统一对数据库的操作,定义了一套jdbc接口,这套接口由数据库厂商去实现,这样,开发人员只需要学习jdbc接口,并通过jdbc加载具体的驱动,就可以操作数据库。简单的说,JDBC的意义在于在Java程序中执行SQL语句。
JDBC编程步骤:
1.加载注册驱动类Driver
Class.forName(“com.mysql.jdbc.Driver”);
2.建立连接
Connection connection = DriverManager.getConnection(
“jdbc:mysql://localhost:3306/test“,”root”,”root”);
3操作数据
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT A_NAME FROM A”);
while (rs.next()) {
String name = rs.getString("A_NAME");
System.out.println(“name is:”+ name);
}
4.释放资源
rs.close();
stmt.close();
connection.close();
注意添加try-catch-finally。
JDBC访问数据库的流程
加载注册驱动程序Driver;
通过DriverManager类获得表示数据库连接的Connection类对象;
通过Connection对象绑定要执行的语句,生成Statement类对象;
执行SQL语句,接收执行结果集ResultSet;
可选的对结果集ResultSet类对象的处理;
必要的关闭ResultSet、Statement和Connection
Connection
常用方法:
createStatement():创建向数据库发送sql的statement对象
prepareStatement(sql) :创建向数据库发送预编译sql的PreparedSatement对象
--- 存储过程
setAutoCommit(boolean autoCommit):设置事务是否自动提交。
commit() :在链接上提交事务。 ---与事务相关!!
rollback() :在此链接上回滚事务。
Statement
executeQuery(String sql) :用于向数据发送查询语句。
executeUpdate(String sql):用于向数据库发送insert、update或delete语句
execute(String sql):用于向数据库发送任意sql语句
Jdbc中的statement对象用于向数据库发送SQL语句,想完成对数据库的增删改查,只需要通过这个对象向数据库发送增删改查语句即可。
Statement对象的executeUpdate方法,用于向数据库发送增、删、改的sql语句,executeUpdate执行完后,将会返回一个整数(即增删改语句导致了数据库几行数据发生了变化)。
Statement.executeQuery方法用于向数据库发送查询语句,executeQuery方法返回代表查询结果的ResultSet对象。
SQL 注入是用户利用某些系统没有对输入数据进行充分检查,从而进行恶意破坏的行为。
1、statement存在sql注入攻击问题:例如登陆密码采用xxx' or '1'='1
2、对于防范 SQL 注入,可以采用PreparedStatement取代Statement。prepareCall(sql):创建执行存储过程的callableStatement对象。)
PreparedStatement是Statement的子接口,它的实例对象可以通过调用Connection.preparedStatement(sql)方法获得,相对于Statement对象而言:
PreperedStatement可以避免SQL注入的问题。
Statement会使数据库频繁编译SQL,可能造成数据库缓冲区溢出。PreparedStatement 可对SQL进行预编译,从而提高数据库的执行效率。
并且PreperedStatement对于sql中的参数,允许使用占位符的形式进行替换,简化sql语句的编写。
ResultSet (结果集)
Jdbc程序中的ResultSet用于代表Sql语句的执行结果。Resultset封装执行结果时,采用的类似于表格的方式。ResultSet 对象维护了一个指向表格数据行的游标cursor,初始的时候,游标在第一行之前,调用ResultSet.next() 方法,可以使游标指向具体的数据行,进而调用方法获取该行的数据。
ResultSet既然用于封装执行结果的,所以该对象提供的大部分方法都是用于获取数据的get方法
释放资源
Jdbc程序运行完后,切记要释放程序在运行过程中,创建的那些与数据库进行交互的对象,这些对象通常是ResultSet, Statement和Connection对象。
特别是Connection对象,它是非常稀有的资源,用完后必须马上释放,如果Connection不能及时、正确的关闭,极易导致系统宕机。Connection的使用原则是尽量晚创建,尽量早的释放。
为确保资源释放代码能运行,资源释放代码也一定要放在finally语句中。、
JavaEE模式-DAO 模式
Data Access Object 抽象并封装了对底层数据源的操作
封装对于数据源的操作
数据源可能是文件、数据库等任意存储方式
负责管理与数据源的连接
负责数据的存取(CRUD)
不包含任何业务