今日学习目标:
1、能够描述表与表的关系
2、能独立编写一对多的表关系SQL语句
3、能独立编写多对多的表关系SQL语句
4、能使用SQL进行多表查询
5、能使用JDBC完成单表增删改查操作
01 一对多建表原则
开启MySQL
中文乱码:
解决办法
声明外键约束
发现删除不了
注意事项:
1、主表不能删除,从表中已经引用的记录
但是可以通过先删除从表中引用的记录,在删除主表对应的外键
2、从表外键不能添加,主表中不存在的记录
02 多对多建表原则
03 一对一建表原则
04 商城的实体抽取和表关系分析
05 内连接和外连接区别
多表查询
1、交叉连接查询(基本不会使用-得到是两个表的乘积)
语法
select * from A,B;
2、内连接查询(使用的关键字inner join – inner可以省略)
隐式内连接:
select * from A,B where 条件;
显示内连接:
select * from A inner join B on 条件;
3、外连接查询(使用的关键字outer join – outer可以省略)
左外连接:
select * from A left join B on 条件;
右外连接:
乍一看觉得两者没有什么区别,这是因为数据仅仅拥有交集下:左外连接=内连接=右外连接
一旦当数据存在非交集,就会出现区别:
子查询
一条select语句结果作为另一条select语法一部分(查询条件、查询结果,表等)
JDBC工具类抽取方式一(测试根据id查询)
package cn.itcast.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
* 提供获取连接和释放资源的方法
* @author 小强
*
*/
public class JDBCUtils_V1 {
/**
* 获取连接
* @return
*/
public static Connection getConnection(){
Connection conn = null;
try {
//1.注册驱动
Class.forName("com.mysql.jdbc.Driver");
//2.获取连接对象
String url = "jdbc:mysql://localhost:3306/web08";
String username = "root";
String password = "123";
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
System.out.println(e);
}
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();
}
}
}
JDBC工具类抽取方式二(测试添加操作)
配置文件:
driver=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost:3306/web08?useUnicode=true&characterEncoing=utf8
username=root
password=123
package cn.itcast.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ResourceBundle;
/**
* 提供获取连接和释放资源的方法
* @author 小强
*
*/
public class JDBCUtils_V2 {
private static String driver;
private static String url;
private static String username;
private static String password;
/**
* 静态代码块加载配置文件信息
*/
static{
ResourceBundle bundle = ResourceBundle.getBundle("db");
driver = bundle.getString("driver");
url = bundle.getString("url");
username = bundle.getString("username");
password = bundle.getString("password");
}
/**
* 获取连接
* @return
*/
public static Connection getConnection(){
Connection conn = null;
try {
//1.注册驱动
Class.forName(driver);
//2.获取连接对象
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
System.out.println(e);
}
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();
}
}
}
package cn.itcast.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import cn.itcast.jdbc.JDBCUtils_V1;
import cn.itcast.jdbc.JDBCUtils_V2;
/**
* 测试工具类
* @author 小强
* @date 2019/2/22
* @version V1.0
*/
public class TestUtils {
/**
* 添加用户信息
*/
@Test
public void testAdd(){
Connection conn = null;
PreparedStatement pstmt = null;
try {
//1.获取连接
conn = JDBCUtils_V2.getConnection();
//2.编写sql语句
String sql = "insert into newuser values(null,?,?)";
//3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setString(1,"刘二");
pstmt.setString(2, "123");
//5.执行插入操作
int row = pstmt.executeUpdate();
if(row > 0){
System.out.println("添加成功");
System.out.println(sql);
}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 {
//获取连接
conn = JDBCUtils_V1.getConnection();
//2.编写sql语句
String sql = "select * from newuser where uid=?";
//3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setInt(1, 2);
//5.执行查询操作
rs = pstmt.executeQuery();
//7.对结果集进行处理
if(rs.next()){
System.out.println("恭喜您,"+rs.getString(2)+"登录成功!"+"\t"+"密码:"+rs.getString("upassword"));
System.out.println(sql);
}else{
System.out.println("账户或者密码错误!");
}
//释放资源就放在此处可以吗(不可以,一旦有出现故障,中断程序,但是像conn对象无法释放,造成阻塞)
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils_V1.release(conn, pstmt, rs);
}
}
}
JDBC工具类抽取方式三(删除操作)
package cn.itcast.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;
/**
* 提供获取连接和释放资源的方法
* @author 小强
*
*/
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) {
System.out.println(e);
}
}
/**
* 获取连接
* @return
*/
public static Connection getConnection(){
Connection conn = null;
try {
//1.注册驱动
Class.forName(driver);
//2.获取连接对象
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
System.out.println(e);
}
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();
}
}
}
package cn.itcast.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.junit.Test;
import cn.itcast.jdbc.JDBCUtils_V1;
import cn.itcast.jdbc.JDBCUtils_V2;
import cn.itcast.jdbc.JDBCUtils_V3;
/**
* 测试工具类
* @author 小强
* @date 2019/2/22
* @version V1.0
*/
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 newuser 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("更新成功");
System.out.println(sql);
}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 newuser where uid=?";
//3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setInt(1,5);
//5.执行删除操作
int row = pstmt.executeUpdate();
if(row > 0){
System.out.println("删除成功");
System.out.println(sql);
}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 newuser values(null,?,?)";
//3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setString(1,"刘二");
pstmt.setString(2, "123");
//5.执行插入操作
int row = pstmt.executeUpdate();
if(row > 0){
System.out.println("添加成功");
System.out.println(sql);
}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 {
//获取连接
conn = JDBCUtils_V1.getConnection();
//2.编写sql语句
String sql = "select * from newuser where uid=?";
//3.获取执行sql语句对象
pstmt = conn.prepareStatement(sql);
//4.设置参数
pstmt.setInt(1, 2);
//5.执行查询操作
rs = pstmt.executeQuery();
//7.对结果集进行处理
if(rs.next()){
System.out.println("恭喜您,"+rs.getString(2)+"登录成功!"+"\t"+"密码:"+rs.getString("upassword"));
System.out.println(sql);
}else{
System.out.println("账户或者密码错误!");
}
//释放资源就放在此处可以吗(不可以,一旦有出现故障,中断程序,但是像conn对象无法释放,造成阻塞)
} catch (SQLException e) {
e.printStackTrace();
}finally{
JDBCUtils_V1.release(conn, pstmt, rs);
}
}
}
JDBC工具类抽取方式三(修改操作)
建表语句:
分页操作:使用limit(参数1,参数2)
起始位置(参数1)=(第几页-1)*每页显示的条数(参数2)
1.分类表
create table category(
cid varchar(32) primary key,
cname varchar(100)
);
2.商品表
create table product(
pid varchar(32) primary key,
pname varchar(40),
price double,
category_id varchar(32)
);
alter table product add foreign key(category_id) references category(cid);
3.添加外键列
alter table product add category_id varchar(32);
4.添加约束
alter table product add constraint product_fk foreign key(category_id) references category(cid);
5.订单表
create table orders(
oid varchar(32) primary key,
totalprice double
);
6.订单项表
create table orderitem(
oid varchar(50),
pid varchar(50)
);
7.联合主键
alter table orderitem add primary key(oid,pid);
8.订单表和订单项表的主外键关系
alter table orderitem add constraint orderitem_orders_fk foreign key(oid) references orders(oid);
9.商品表和订单项表的主外键关系
alter table orderitem add constraint orderitem1_product_fk foreign key(pid) references product(pid);
insert into category(cid,cname) values('c001','家电');
insert into category(cid,cname) values('c002','服饰');
insert into category(cid,cname) values('c003','化妆品');
insert into product(pid,pname,price,category_id) values('p001','联想','5000','c001');
insert into product(pid,pname,price,category_id) values('p002','海尔','5000','c001');
insert into product(pid,pname,price,category_id) values('p003','雷神','5000','c001');
insert into product(pid,pname,price,category_id) values('p004','JACK JONES','800','c002');
insert into product(pid,pname,price,category_id) values('p005','真维斯','200','c002');
insert into product(pid,pname,price,category_id) values('p006','花花公子','440','c002');
insert into product(pid,pname,price,category_id) values('p007','劲霸','2000','c002');
insert into product(pid,pname,price,category_id) values('p008','香奈儿','800','c003');
insert into product(pid,pname,price,category_id) values('p009','相宜本草','200','c003');
声明外键约束
1.从orderitem到product的关系
alter table orderitem add foreign key(pid) references product(pid);
2.从orderitem到orders的关系