一、数据库表结构：
首先，有两个表：
分类表：category
create table category(
cid varchar(32) primary key,
cname varchar(100)
);
商品表：product
create table product(
pid varchar(32) primary key,
pname varchar(40),
price double,
category_id varchar(32)
);
其中：category为主表，"cid"为主键；product为从表，category_id为外键。
外键的特点：
- 从表外键的值是对主表主键的引用；
- 从表外键类型，必须与主表外键类型一致。
使用外键的目的：保证数据完整性。
1.表与表之间的关系：
- 一对多关系：从表（多方）创建一个字段，作为外键指向主表（一方）的主键；
- 多对多关系：创建第三张表，中间表至少两个字段，这两个字段分别作为外键指向各自的主键；
- 一对一：外键唯一、外键是主键、笛卡尔积。
2.创建表实现：
- 添加外键字段：
alter table product add category_id varchar(32);
-
添加约束：
alter table product add foreign key(category_id) references category(cid);
注意：
- 从表不能添加主表不存在的记录；
- 主表不能删除从表已引用的记录。
多对多关系的实现：
首先，有三个表：
- 商品表：product
create table product(
pid varchar(32) primary key,
pname varchar(40),
price double,
category_id varchar(32)
);
- 订单表: orders
create table orders(
oid varchar(32) primary key,
totalprice double
);
- 订单项表：orderitem
此表为建立商品表和订单表的关系而建立
create table orderitem(
oid varchar(50),
pid varchar(50)
);
添加订单表和订单项表的主外键关系：
alter table orderitem add constraint oreritem_orders_fk foreign key(oid) refereneces orders(oid);
添加商品表和订单项表的主外键关系：
alter table product add constraint orderitem_product_fk foreign key(pid) references orders(pid);
二、查询：
两个表：
1.交叉连接：
//基本不会使用，得到的是两个表的笛卡尔乘积
select * from A,B;
2.内连接：
- 隐式内连接：
select * from A,B where 条件；
- 显式内连接：
//inner可以省略
select * from A inner join B on 条件；
3.外链接：
- 左外连接：
//out可以省略
select * from A left out join B on 条件；
- 右外连接：
select * from A right out join B on 条件；
3.子查询：
例：查询“化妆品”分类上架商品详情：
select * from product where category_id=(select cid from category where name=‘化妆品’);
JDBC相关操作：
JDBCUtils_V3.java：
package com.zl.jdbc;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ResourceBundle;
/**
* 提供获取连接和释放资源的 方法
*
*/
public class JDBCUtils_V3 {
private static String driver;
private static String url;
private static String username;
private static String password;
/**
* 静态代码块加载配置文件信息
*/
static {
try {
// 1.通过当前类获取类加载器
ClassLoader classLoader = JDBCUtils_V3.class.getClassLoader();
// 2.通过类加载器的方法获得一个输入流
InputStream is = classLoader.getResourceAsStream("db.properties");
// 3.创建一个properties对象
Properties props = new Properties();
// 4.加载输入流
props.load(is);
// 5.获取相关参数的值
driver = props.getProperty("driver");
url = props.getProperty("url");
username = props.getProperty("username");
password = props.getProperty("password");
} catch (IOException e) {
e.printStackTrace();
}
}
/**
* 获取连接方法
*
* @return
*/
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
public static void release(Connection conn, PreparedStatement pstmt, ResultSet rs) {
if (rs != null) {
try {
rs.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (pstmt != null) {
try {
pstmt.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
TestUtils.java
package com.zl.jdbc.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import com.zl.jdbc.JDBCUtils_V1;
import com.zl.jdbc.JDBCUtils_V2;
import com.zl.jdbc.JDBCUtils_V3;
/**
* 测试工具类
*
*/
public class TestUtils {
/**
* 根据id更新用户信息方法
*/
@Test
public void testUpdateById() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1.获取连接
conn = JDBCUtils_V3.getConnection();
// 2.编写sql语句
String sql = "update tbl_user set upassword=? where uid=?";
// 3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setString(1, "999");
pstmt.setInt(2, 3);
// 5.执行更新操作
int row = pstmt.executeUpdate();
if (row > 0) {
System.out.println("更新成功!");
} else {
System.out.println("更新失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
// 6.释放资源
JDBCUtils_V3.release(conn, pstmt, null);
}
}
/**
* 根据id删除信息方法
*/
@Test
public void testDeleteById() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1.获取连接
conn = JDBCUtils_V3.getConnection();
// 2.编写sql语句
String sql = "delete from tbl_user where uid=?";
// 3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setInt(1, 4);
// 5.执行删除操作
int row = pstmt.executeUpdate();
if (row > 0) {
System.out.println("删除成功!");
} else {
System.out.println("删除失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
// 6.释放资源
JDBCUtils_V3.release(conn, pstmt, null);
}
}
/**
* 添加用户信息方法
*/
@Test
public void testAdd() {
Connection conn = null;
PreparedStatement pstmt = null;
try {
// 1.获取连接
conn = JDBCUtils_V2.getConnection();
// 2.编写sql语句
String sql = "insert into tbl_user values(null,?,?)";
// 3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setString(1, "lisi");
pstmt.setString(2, "hehe");
// 5.执行插入操作
int row = pstmt.executeUpdate();
if (row > 0) {
System.out.println("添加成功!");
} else {
System.out.println("添加失败!");
}
} catch (Exception e) {
throw new RuntimeException(e);
} finally {
// 6.释放资源
JDBCUtils_V2.release(conn, pstmt, null);
}
}
/**
* 根据id查询用户信息
*/
@Test
public void testFindUserById() {
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
// 1.获取连接
conn = JDBCUtils_V1.getConnection();
// 2.编写sql语句
String sql = "select * from tbl_user where uid=?";
// 3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
// 4.设置参数
pstmt.setInt(1, 2);
// 5.执行查询操作
rs = pstmt.executeQuery();
// 6.处理结果集
while (rs.next()) {
System.out.println(rs.getString(2) + "----" + rs.getString("upassword"));
}
} catch (SQLException e) {
e.printStackTrace();
} finally {
// 7.释放资源
JDBCUtils_V1.release(conn, pstmt, rs);
}
}
}
