外键
添加外键
alter table 从表 add [constraint] [外键名字] foreign key(从表外键字段名) references 主表(主表主键);
alter table product add foreign key(category_id) references category(cid);
删除外键
alter table 从表 drop foreign key 外键名字;
建表原则
1.一对多:再多的一方创建一个字段作为外键指向一的一方的主键
2.多对多:创建第三张表,至少两个字段,这两个字段作为外键指向一的一方的主键
3.一对一:唯一外键(unique)指向从表主键 主键对应
多表查询
1.交叉连接查询得到的是两个表的乘积:select * from a,b;
2.内连接查询 关键字inner join (inner 可省略)
隐式内连接:select * from a inner join b on 条件;
显示内连接:select * from a,b where 条件;
3.外连接查询 关键字 outer join (outer可省略)
左外连接:select * from a left outer join on 条件;
右外连接:select * from a right outrt jin on条件;
4.内连接和外连接的区别
内连接:两个表的交集
左外连接:左边全部和两表交集
右外连接:右表全部和两表交集
JDBC工具类
1.
public static Connection getConnection() {
Connection conn = null;
try {
Class.forName("com.mysql.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/web08", "root", "root");
} 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();
}
}
}
2.
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 {
Class.forName(driver);
conn = DriverManager.getConnection(url, username, password);
} catch (Exception e) {
e.printStackTrace();
}
return conn;
}
3.
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;
}