SQL的增删改查、多表关联、分组、嵌套查询等。
JDBC连接数据库,查询数据,有连接池概念(有接口,实现类实现接口)
SQL
SQL的增删改查
查询数据:select * from 表名
插入数据:insert into 表名(列名,列名) values(数据,数据)
修改数据:update 表名 set 列名 = 数据
删除指定数据:delete from 表名 where 条件
删除整表数据:truncate table 表名
SQL的多表联查
注意!若需要使用多个表来进行连接查询,注意连接使用的是什么连接方式,关键的不能为null的数据需要使用内连接,否则多表连接条件就会出现 on 列名 = null 的情况,就会出错。
内连接
select * from 表1 join 表2 on 表1.id = 表2.id where 条件
select * from 表1, 表2 where 条件两张表都需要满足on的条件
外连接:
左外连接:select * from 表1 left join 表2 on 表1.id = 表2.id where 条件以左表为主,显示左表的全部行,显示右表满足on条件的数据,显示的总表中若右表没有数据则显示为null
右外连接:select * from 表1 right join 表2 on 表1.id = 表2.id where 条件
以右表为主,显示右表的全部行,显示左表满足on条件的数据,显示的总表中若左表没有数据则显示为null
全外连接:
oracle:select * from 表1 full join 表2 on 表1.id = 表2.id where 条件
MySQL:select * from 表1 left join 表2 on 表1.id = 表2.id where 条件 union select * from 表1 right join 表2 on 表1.id = 表2.id where 条件 【左外连接 union 右外连接】显示左右表的全部行,另一边没有数据则显示为null
union会将结果去重,union all不会将结果去重。交叉连接
select * from 表1 cross join 表2 on 表1.id = 表2.id where 条件显示左表所有行,每一行与右表的所有行合并
SQL的分组查询
select 列名 from 表名 group by 列名 having 条件
注意:查询的字段必须在group by子句或者聚合函数中
比如:select 字段1, count(字段2) from 表名 group by 字段2; √
select 字段1, count(字段2), 字段3 from 表名 group by 字段2; ×
where子句不能包含聚合函数,having子句可以包含聚合函数
比如:having sum(列名) > 10 √
where sum(列名) > 10 ×
SQL的嵌套查询
嵌套查询 = 子查询:嵌入在其他SQL语句中的select语句。
总结:带比较运算符的子查询(简单子查询)、带有in的子查询、带有any/all的子查询、带有exists的子查询、作为临时表的子查询。
- 带有比较运算符的子查询(单行子查询)
>,<,=,>=,<=,!=,<>
select * from 表名 where 列名 =
(select 列名 from 表名 where 条件)
- 带有in的子查询(多行子查询)
select * from 表名 where 列名 in
(select distinct 列名 from 表名 where 条件)
- 带有any(some)/all的子查询
select * from 表名 where 列名 比较运算符 any(子查询)
any = some,有些系统用some。
select * from 表名 where 列名 比较运算符 all(子查询)
举例:select * from 表名 where 工资 > ALL(select 工资 from 表名 where 部门id = 2 )
→ 查询工资大于2号部门的最高工资的所有记录> all 表示父查询的条件需要大于子查询的所有结果
> any 表示父查询的条件需要大于子查询的某个结果
= any 相当于 in, != any 相当于 not in
- 带有exists的子查询
select * from 表名 where exists
(select 列名 from 表名 where 条件)
- 作为临时表的子查询
select * from (select 列名 from 表名 where 条件) as 临时表名 where 条件
- 多列子查询
select * from 表名 where (列1, 列2) =
(select 列1, 列2 from 表名 where 条件)
>>> 查询指定列相同的结果
JDBC连接数据库
数据库连接池
数据库连接池:负责分配、管理、释放数据库连接,允许应用程序重复使用一个现有的数据库连接,而不是重新建立一个。
原理:在系统初始化的时候,将数据库连接作为对象存储在内存中,当用户需要访问数据库时,如果连接池中有空闲连接对象,则将连接分配给用户使用;如果没有空闲连接对象,则查看当前连接数是否达到最大连接数,如果没有达到最大连接数,则创建连接对象给用户使用;若已达到最大连接数,则将请求加入到等待队列进行等待,如果超过最大等待时间就抛出异常给用户。如果连接对象的使用超过了连接的最大使用次数,则该连接会被关闭。可以通过设置连接池的参数来控制连接池中的初始连接数、连接的上下限数以及每个连接的最大使用次数、最大空闲时间等等。也可以通过数据库连接池的管理机制来监视数据库连接的数量、使用情况等。
数据库连接池:DBCP、C3P0、Druid…
Druid 数据库连接池
Druid配置参数:
initialSize:初始连接数
maxActive:最大连接数
maxWait:最大等待时间
src目录下druid.properties:
url=jdbc:postgresql://localhost:5432/test
username=postgres
password=***
driverClassName=org.postgresql.Driver
initialSize=10
maxActive=20
maxWait=9000
Java程序:
public class JdbcCConnection {
public static void main(String[] args) {
Properties properties = new Properties();
try {
properties.load(JdbcCConnection.class.getClassLoader().getResourceAsStream("druid.properties"));
DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
Connection connection = dataSource.getConnection();
Statement statement = connection.createStatement();
statement.executeUpdate("insert into employee values(4,24,'李琳')");
} catch (IOException e) {
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}
}
}
JDBC操作PostgreSQL
postgresql.jar 包下载:https://jdbc.postgresql.org/download/postgresql-42.3.4.jar
public class JdbcConnection { //java程序
public static void main(String[] args) {
String username = "postgres";
String password = "";
String url = "jdbc:postgresql://localhost:5432/test";
try {
//引入数据库驱动postgresql.jar
//注册驱动程序
Class.forName("org.postgresql.Driver");
//通过驱动程序管理器获取连接对象
Connection connection = DriverManager.getConnection(url, username, password);
//执行SQL语句
Statement statement = connection.createStatement();
// statement.executeUpdate("insert into employee values(1,22,"张三")");
ResultSet resultSet = statement.executeQuery("select * from employee");
//获取结果集
resultSet.next();
System.out.println(resultSet.getInt(1));
System.out.println(resultSet.getInt(2));
System.out.println(resultSet.getString(3));
} catch (ClassNotFoundException | SQLException e) {
e.printStackTrace();
}
}
}