WEB09_MySQL多表&JDBC

待我称王封你为后i 2022-03-17 03:38 217阅读 0赞

## 今日学习目标： ##

1、能够描述表与表的关系

2、能独立编写一对多的表关系SQL语句

3、能独立编写多对多的表关系SQL语句

4、能使用SQL进行多表查询

5、能使用JDBC完成单表增删改查操作  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70]

## 01 一对多建表原则 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 1]  
开启MySQL  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 2]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 4]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 5]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 6]  
中文乱码：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 7]  
解决办法  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 8]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 9]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 10]  
声明外键约束  
![在这里插入图片描述][20190222113737293.png]  
发现删除不了  
![在这里插入图片描述][20190222113936158.png]  
注意事项：  
1、主表不能删除,从表中已经引用的记录  
![在这里插入图片描述][20190222114508683.png]  
但是可以通过先删除从表中引用的记录，在删除主表对应的外键  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 11]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 12]  
2、从表外键不能添加，主表中不存在的记录  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 13]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 14]  
![在这里插入图片描述][2019022221053116.png]

## 02 多对多建表原则 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 15]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 16]  
![在这里插入图片描述][20190222210418268.png]  
![在这里插入图片描述][20190222210431337.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 17]

## 03 一对一建表原则 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 18]

## 04 商城的实体抽取和表关系分析 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 19]

## 05 内连接和外连接区别 ##

### 多表查询 ###

![在这里插入图片描述][20190222213251914.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 20]

#### 1、交叉连接查询（基本不会使用-得到是两个表的乘积） ####

语法  
select \* from A,B;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 21]

#### 2、内连接查询（使用的关键字inner join – inner可以省略） ####

隐式内连接：  
select \* from A,B where 条件；  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 22]  
显示内连接：  
select \* from A inner join B on 条件;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 23]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 24]

#### 3、外连接查询（使用的关键字outer join – outer可以省略） ####

左外连接：  
select \* from A left join B on 条件;  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 25]  
右外连接：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 26]  
乍一看觉得两者没有什么区别，这是因为数据仅仅拥有交集下：左外连接=内连接=右外连接  
一旦当数据存在非交集，就会出现区别：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 27]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 28]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 29]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 30]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 31]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 32]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 33]

#### 子查询 ####

一条select语句结果作为另一条select语法一部分（查询条件、查询结果，表等）  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 34]

## 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();
    			}
    
    	}
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 35]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 36]

## JDBC工具类抽取方式二(测试添加操作) ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 37]  
![在这里插入图片描述][2019022220465720.png]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 38]  
配置文件：

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();
    			}
    
    	}
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 39]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 40]

## JDBC工具类抽取方式三(删除操作) ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 41]

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);
    		}		
    	}
    }

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 42]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 43]

## JDBC工具类抽取方式三(修改操作) ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 44]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 45]

## 建表语句： ##

分页操作：使用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的关系

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70]: /images/20220317/5b719188ae624373ac0b3fb54b419e75.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 1]: /images/20220317/d8fbd845ca9c4e5bb571c54b5c27befa.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 2]: /images/20220317/623f4a00793c40a98d66bb86af4d732a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 3]: /images/20220317/1c1c457cd1e1472295b4ba33b2b1d1f3.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 4]: /images/20220317/3bda92e6d29647bba8242a47e4d28529.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 5]: /images/20220317/fb57ceaae4504830b7163ab156b0be14.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 6]: /images/20220317/ad20c7413ea54aff86f9b26628760580.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 7]: /images/20220317/330ab8c7a3bf4b858c390ae5284ce651.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 8]: /images/20220317/ebe9e6d75aae42beb30f08a097ff78cb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 9]: /images/20220317/fc8bf3f888514ced8016916a3d847a51.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 10]: /images/20220317/dca7d5abdbcc4cb3bf16c0c6580ab042.png
[20190222113737293.png]: /images/20220317/67ebca510677469aa0203079636287ab.png
[20190222113936158.png]: /images/20220317/550ca41a816c4de89705214fd928cf37.png
[20190222114508683.png]: /images/20220317/54624dd24171424d91daeecbc96b5990.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 11]: /images/20220317/4ab69be6418347dab9d8df3e8904301f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 12]: /images/20220317/4a428fc8606e418f9a91458dedefa940.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 13]: /images/20220317/77fa31166a6948beb2ff1bc231d5d2ab.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 14]: /images/20220317/0ddcfdaf28a14eddbabc57566821f184.png
[2019022221053116.png]: /images/20220317/f5f996a2fa1c40f98bef594b26e63b1b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 15]: /images/20220317/3a31d0fa6c6e4bbd96a0c533f13262ea.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 16]: /images/20220317/26f05b499c97407c983f3d179f470529.png
[20190222210418268.png]: /images/20220317/3f21a6a5f32f4a6b92ce526ff6aea715.png
[20190222210431337.png]: /images/20220317/b8523d8720934c08947f0a03882172f8.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 17]: /images/20220317/4d32b0a048c94d7780f57ad4ec846666.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 18]: /images/20220317/d503dad4a3674dd3b178ee576be173f1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 19]: /images/20220317/c50c8812ee1349b79fe2dab0cee5b32a.png
[20190222213251914.png]: /images/20220317/55254b87fd444e50a867534a5b3476ca.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 20]: /images/20220317/41750702ed9d4d50a6570a616794a215.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 21]: /images/20220317/424bdae92177451dab51bef31f39a74d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 22]: /images/20220317/1175e7cbe5964db4addd3d43c132792f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 23]: /images/20220317/52c5bc608fda408dbd0b32642d1d739a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 24]: /images/20220317/a66533fdb8374987aca51f53f4d37853.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 25]: /images/20220317/f4c09b946d90400e8d4eeb6fece1b548.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 26]: /images/20220317/1572ddd0d0fe4d05879d4cd9e129f1d9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 27]: /images/20220317/637dfcfbab82462d8eafda97387e3e19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 28]: /images/20220317/c90a4e3071a7456bb7f69e144044abbc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 29]: /images/20220317/18cb8e0bce7f4c07a384973f23999771.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 30]: /images/20220317/ab3ef92998284efd87f30cc44811b5bd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 31]: /images/20220317/41180955e37a452e816fad7fa8d19603.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 32]: /images/20220317/1ec19cb4884544d1a2281be11c351f6d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 33]: /images/20220317/425551fbe20f48c584a81d4cca2ca62c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 34]: /images/20220317/d7c601bb404d4910b48f4523b675e557.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 35]: /images/20220317/a68546846bd04ec498fc3938bcd7de25.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 36]: /images/20220317/f3e9da7ad75a460383e5bf315868fb9b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 37]: /images/20220317/d281cf5b594c46cfbc8059d1fedf0955.png
[2019022220465720.png]: /images/20220317/326f522f3a404d0f8cd0d5b04f6f14dd.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 38]: /images/20220317/52ea6e6c5a754112bcc9833e817ebe67.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 39]: /images/20220317/750f0846ab6a4440a12cfeb4c926bf3b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 40]: /images/20220317/22f68c97bba74c55baff3a65ab53b5ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 41]: /images/20220317/156dc149c5d042f5a702cb0a5eb66ae9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 42]: /images/20220317/d561b253174948518c9505cc759af956.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 43]: /images/20220317/ccad727fbcc1445b9a1023142a936eb9.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 44]: /images/20220317/e749730ff42f4fa98293f4701bfa382a.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dlaXhpbl80MDgwNzI0Nw_size_16_color_FFFFFF_t_70 45]: /images/20220317/144b22aa203241ce9a48dd0b363f776e.png