数据库(二)

磕代码

已于 2023-09-11 08:39:51 修改

阅读量281

点赞数 1

分类专栏：数据库第三阶段文章标签：数据库

于 2023-09-11 08:39:35 首次发布

本文链接：https://blog.csdn.net/m0_69480146/article/details/132799243

版权

数据库第三阶段专栏收录该内容

2 篇文章 0 订阅

订阅专栏

数据库(二)

9.4

JDBC

Java DataBase Connectivity

JDBC，是Java程序用于连接不同类型数据库的一套规范

实际是，用Java定义的一套连接数据库时的接口的集合，不同的数据库对其进行了实现

核心接口

Connection
- 用于设置要连接的数据库的信息
  - 数据库地址
  - 用户名
  - 密码
PreparedStatement
- 预处理结果，用于执行SQL语句
Result
- 结果集，用于保存执行了查询后的结果集

JDBC具体使用

导入驱动包并添加依赖关系

项目中新建一个目录，可以命名为lib，将要连接的驱动包保存在其中

驱动包jar

将驱动包保存其中后，右键点击add as libray，添加依赖关系

查询

核心方法

Class.forName(“class文件权限定名”)
- 加载某个class文件
- Class.forName(“com.mysql.cj.jdbc.Driver”)
Connection conn = DriverManager.getConnection(url, username, password)
- 连接指定数据，返回连接成功的对象Connection
- url = “jdbc:mysql://localhost:3306/数据库?serverTimezone=Asia/Shanghai”;
PreparedStatement pst = conn.prepareStatement(sql)
- 预处理sql语句，返回处理后的对象
ResultSet res = pst.executeQuery()
执行查询
res.next() 返回boolean类型值
- 表示下一行是否有数据
res.get数据类型(字段名或字段顺序)
读取查询到的某个字段的值
- res.getInt(“id”);
- res.getString(2);

//1.加载连接数据库的驱动文件
Class.forName("com.mysql.cj.jdbc.Driver");
//2.定义连接信息(地址、账号、密码)
String url = "jdbc:mysql://localhost:3306/db_game?serverTimezone=Asia/Shanghai";
String username = "root";
String password = "root";
//连接指定数据库,返回值为连接对象
Connection conn = DriverManager.getConnection(url, username, password);
//3.使用上一步返回的连接对象conn执行指定的sql语句，得到sql预处理对象
String sql = "select * from hero";
PreparedStatement pst = conn.prepareStatement(sql);
//4.使用上一步返回的预处理对象pst执行查询的方法,得到查询后的结果集
ResultSet res = pst.executeQuery();
//5.循环读取查询到的结果集
while (res.next()) {
    //取数据。get数据类型(字段名) 或 get数据类型(字段顺序) 用于获取读取到的一条记录中指定字段的值
    int id=res.getInt("id");
    String name = res.getString(2);
    String sex = res.getString(3);
    double price = res.getDouble(4);
    Date createDate = res.getDate(5);
    String position = res.getString(6);
    //将读取出的数据赋值给一个Hero对象的属性
    Hero hero = new Hero(id, name, sex, position, price, createDate);
    System.out.println(hero);
}
//6.释放资源
res.close();
pst.close();
conn.close();

更新(增加、删除、修改)

Class.forName(“class文件权限定名”)
- 加载某个class文件
- Class.forName(“com.mysql.cj.jdbc.Driver”)
Connection conn = DriverManager.getConnection(url, username, password)
- 连接指定数据，返回连接成功的对象Connection
- url = “jdbc:mysql://localhost:3306/数据库?serverTimezone=Asia/Shanghai”;
PreparedStatement pst = conn.prepareStatement(sql)
- 预处理sql语句，返回处理后的对象
int i = pst.executeUpdate()
- 执行更新，返回受影响的行数
pst.set数据类型(问号顺序,值)
给sql中指定的问号赋值

//1.加载驱动
Class.forName("com.mysql.cj.jdbc.Driver");
//2.连接数据库
String url="jdbc:mysql://localhost:3306/db_game?severTimezone=Asia/Shanghai";
Connection conn = DriverManager.getConnection(url, "root", "root");
//3.定义sql对其预处理。使用?表示sql中的参数
String sql="insert into hero values(null,?,default,5000,now(),null)";
PreparedStatement pst = conn.prepareStatement(sql);
//4.使用预处理对象pst对?赋值 调用 set数据类型(index,param) 方法给第index个?赋值param
pst.setString(1,"xxx");
//5.调用数据更新的方法，返回值是受影响的行数
int i = pst.executeUpdate();
if(i!=0){
    System.out.println("操作成功");
}else{
    System.out.println("操作失败");
}
//6.释放资源
sc.close();
pst.close();
conn.close();

应用

用户表

用户编号用户名密码

注册，保证不重复

1.查询当前要注册的信息是否存在

查询：select * from 用户表 where 用户名=‘admin’

2.若存在(能查询出数据)无法注册；若不存在(不能查询出数据)才能注册

注册即添加：insert into 用户表 values(‘admin’,‘123123’)

登录 admin 123123

如果能通过给定的用户名和密码查询到数据，说明输入正确

如果查询结果为空，说明用户名或密码有误

select * from 用户表 where 用户名=‘admin’ and 密码=‘123123’

SQL注入

利用sql语句拼接后，导致原本sql失去本意的漏洞

-- 使用姓名和手机登录 如果能查询出结果则进入系统
-- 如果有一个条件不满足，查询结果为空
select * from employee where emp_name='aw' and emp_phone='123'
-- 王茂鑫 18523473566
select * from employee where emp_name='王茂鑫' and emp_phone='18523473566'

-- "' or 1=1 -- "
select * from employee where emp_name='' or 1=1 -- ' and emp_phone=''

’ or 1=1 – 若将这个字符串作为用户名输入时，会导致sql语句异常，查询出全部数据

package login;
import java.sql.*;
/*
* 注册登录流程
* 注册：
* 1.查询当前要注册的用户名是否存在
* 存在则无法注册
* 2.不存在，执行添加
* 登录：
* 使用用户名和密码查询，如果查询结果为空，说明用户名或密码错误
* */
public class Test {
    public static void main(String[] args) throws SQLException, ClassNotFoundException
    {
        //register("xxxx", "sdfsdf");
        login("' or 1=1 -- ","sdfsdf");
    }
    /*
* 注册的方法
* */
    public static void register(String username, String password) throws
        ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/db_game?
            serverTimezone=Asia/Shanghai";
            Connection conn = DriverManager.getConnection(url, "root", "root");
        //核心sql1:查询用户名是否存在
        String sql = "select * from userinfo where username = ?";
        PreparedStatement pst = conn.prepareStatement(sql);
        pst.setString(1, username);
        ResultSet res = pst.executeQuery();
        //如果查询到数据，提前结束
        if (res.next()) { //true==true true false==true false
            System.out.println("该用户已存在");
            return;
        }
        //核心sql2:添加用户
        String sql2 = "insert into userinfo values(null,?,?)";
        pst = conn.prepareStatement(sql2);
        pst.setString(1, username);
        pst.setString(2, password);
        if (pst.executeUpdate() > 0) {
            System.out.println("注册成功");
        }
        res.close();
        pst.close();
        conn.close();
    }
    public static void login(String username, String password) throws SQLException,
    ClassNotFoundException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        String url = "jdbc:mysql://localhost:3306/db_game?
            serverTimezone=Asia/Shanghai";
            Connection conn = DriverManager.getConnection(url, "root", "root");
        //登录核心sql:根据用户名密码查询
        String sql = "select * from userinfo where username =? and password=?";
        PreparedStatement pst = conn.prepareStatement(sql);
        /*pst.setString(1, username);
pst.setString(2, password);*/
        ResultSet res = pst.executeQuery();
        if (res.next()) {
            System.out.println("登录成功");
        } else {
            System.out.println("用户名或密码错误");
        }
        res.close();
        pst.close();
        conn.close();
    }
}

简化JDBC

DBUtil

定义数据库工具类

package com.hqyj.jdbc_plus.util;

import java.sql.*;

public class DBUtil {

    /*
     * 数据库工具类
     * 用于提取JDBC连接数据库时的公共代码
     * //公共代码：
     * //1.加载驱动
     * //2.获取连接
     * //3.释放资源
     * */

    //静态代码块，在类加载时就执行，只执行一次
    static {
        try{
            //加载数据库驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动异常"+e);
        }
    }

    //定义常量保存数据库的地址、用户名、密码
    private static final String URL="jdbc:mysql://localhost:3306/db_emp?serverTimezone=Asia/Shanghai";
    private static final String NAME="root";
    private static final String PWD="root";

    //获取连接数据库对象
    public static Connection getConnection(){
        Connection conn =null;
        try {
            conn=DriverManager.getConnection(URL, NAME, PWD);
        } catch (SQLException e) {
            System.out.println("获取连接异常"+e);
        }
        return conn;
    }

    //释放资源
    public static void release(Connection conn, PreparedStatement pst, ResultSet res){
        try {
            //判断非空，防止空指针异常
            if(res!=null){
                res.close();
            }
            if (pst!=null){
                pst.close();
            }
            if(conn!=null){
                conn.close();
            }
        }catch (SQLException e){
            System.out.println("关闭异常"+e);
        }
    }
}

存储数据表字段

数据表字段类似与类中的属性

package com.hqyj.jdbc_plus.entity;

import java.util.Date;
/*
* 用于保存Employee表中数据的实体类
* entity包表示实体包
* 属性表示表中的字段
* 全参与无参构造方法
* get()/set()方法
* toString()方法
* */
public class Employee {
    private int empNo;
    private String empName;
    private String empPhone;
    private Date joinDate;
    private String dept;
    private double salary;
    private String email;

    //无参构造
    public Employee() {
    }
    //全参构造
    public Employee(int empNo, String empName, String empPhone, Date joinDate, String dept, double salary, String email) {
        this.empNo = empNo;
        this.empName = empName;
        this.empPhone = empPhone;
        this.joinDate = joinDate;
        this.dept = dept;
        this.salary = salary;
        this.email = email;
    }

    @Override
    public String toString() {
        return "Employee{" +
            "empNo=" + empNo +
            ", empName='" + empName + '\'' +
            ", empPhone='" + empPhone + '\'' +
            ", joinDate=" + joinDate +
            ", dept='" + dept + '\'' +
            ", salary=" + salary +
            ", email='" + email + '\'' +
            '}';
    }

    public int getEmpNo() {
        return empNo;
    }

    public void setEmpNo(int empNo) {
        this.empNo = empNo;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public String getEmpPhone() {
        return empPhone;
    }

    public void setEmpPhone(String empPhone) {
        this.empPhone = empPhone;
    }

    public Date getJoinDate() {
        return joinDate;
    }

    public void setJoinDate(Date joinDate) {
        this.joinDate = joinDate;
    }

    public String getDept() {
        return dept;
    }

    public void setDept(String dept) {
        this.dept = dept;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }
}

数据库访问层

package com.hqyj.jdbc_plus.dao;

import com.hqyj.jdbc_plus.entity.Employee;
import com.hqyj.jdbc_plus.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/*
* dao层表示数据库访问层
* 该层中的类都是用于操作数据库
* 类名通常命名为:实体类Dao
* */
public class EmployeeDao {
    //定义操作数据库时所需要的接口
    Connection conn;
    PreparedStatement pst;
    ResultSet res;
    //查询所有，返回所有Employee对象集合
    public List<Employee> queryAll(){
        //创建集合用于最终的返回值
        List<Employee> list = new ArrayList<>();
        try {
            //加载驱动、连接
            conn= DBUtil.getConnection();
            //构造sql语句，对其预处理
            pst=conn.prepareStatement("select * from employee");
            res=pst.executeQuery();
            while (res.next()){
                int id=res.getInt(1);
                String name=res.getString(2);
                String phone=res.getString(3);
                Date date=res.getDate(4);
                String dept=res.getString(5);
                double salary=res.getDouble(6);
                String email=res.getString(7);
                //读取到的数据保存在一个对象里
                Employee emp=new Employee(id,name,phone,date,dept,salary,email);
                //将读取到的数据对象保存到集合中
                list.add(emp);
            }
        }catch (SQLException e){
            System.out.println("查询所有异常"+e);
        }finally {
            //释放资源
            DBUtil.release(conn,pst,res);
        }
        return list;
    }
    //根据编号查询员工
    public Employee queryId(int id){
        Employee emp=null;
        //加载与连接
        try {
            conn=DBUtil.getConnection();
            pst=conn.prepareStatement("select * from employee where emp_no=?");
            pst.setInt(1,id);
            res=pst.executeQuery();
            if(res.next()){
                emp=new Employee(res.getInt(1),res.getString(2),res.getString(3),res.getDate(4),res.getString(5),res.getDouble(6),res.getString(7));
            }
        }catch (SQLException e){
            System.out.println("查询id异常"+e);
        }finally {
            DBUtil.release(conn,pst,res);
        }
        return emp;
    }



    //添加新员工
    public boolean insertEmp(int id,String name,String phone,Date date,String dept, double salary,String email){
        conn=DBUtil.getConnection();
        try {
            pst=conn.prepareStatement("insert into employee values (?,?,?,?,?,?,?)");
            if (id==-1){
                pst.setInt(1,0);
            }else {
                pst.setInt(1,id);
            }
            pst.setString(2,name);
            pst.setString(3,phone);
            pst.setDate(4,new java.sql.Date(date.getTime()));
            pst.setString(5,dept);
            pst.setDouble(6,salary);
            pst.setString(7,email);
            return pst.executeUpdate()>0;
        }catch (SQLException e){
            System.out.println("添加员工异常"+e);
        }finally {
            DBUtil.release(conn,pst,res);
        }
        return false;
    }
    //修改员工信息
    public boolean updateSalary(int empNo,double salary){
        try {
            conn=DBUtil.getConnection();
            pst=conn.prepareStatement("update employee set salary=? where emp_no=?");
            pst.setDouble(1,salary);
            pst.setDouble(2,empNo);
            int i=pst.executeUpdate();
            return pst.executeUpdate()>0;
        }catch (SQLException e){
            System.out.println("修改薪资异常"+e);
        }finally {
            DBUtil.release(conn,pst,res);
        }
        return false;
    }
    //删除员工
    public boolean delectEmp(int id){
        try {
            conn=DBUtil.getConnection();
            if(id==-1){
                pst=conn.prepareStatement("truncate table employee");
                pst.executeUpdate();
                return true;
            }else {
                pst=conn.prepareStatement("delete from employee where emp_no=? ");
                pst.setInt(1,id);
                return pst.executeUpdate()>0;
            }
        }catch (SQLException e){
            System.out.println("删除员工异常"+e);
        }
        finally {
            DBUtil.release(conn,pst,res);
        }
        return false;
    }
}

用法

package com.hqyj.jdbc_plus.service;

import com.hqyj.jdbc_plus.dao.EmployeeDao;
import com.hqyj.jdbc_plus.entity.Employee;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Scanner;

public class Main {
    public static void main(String[] args) {
        EmployeeDao employeeDao = new EmployeeDao();
        Scanner sc = new Scanner(System.in);
        while (true){
            System.out.println("1.查询所有员工");
            System.out.println("2.根据编号查询员工");
            System.out.println("3.添加员工");
            System.out.println("4.修改员工");
            System.out.println("5.删除员工");
            System.out.println("6.退出员工");
            System.out.println("请输入操作编号");
            switch (sc.nextInt()){
                case 1:
                    List<Employee> list=employeeDao.queryAll();
                    list.forEach((i)->{
                        System.out.println(i.getEmpNo()+"\t"+i.getEmpName()+"\t"+i.getEmpPhone()+"\t"+i.getDept()+"\t"+i.getJoinDate()+"\t"+i.getSalary()+"\t"+i.getEmail());
                    });
                    break;
                case 2:
                    System.out.println("请输入要查询的id");
                    int id=sc.nextInt();
                    Employee emp=employeeDao.queryId(id);
                    if(emp!=null){
                        System.out.println(emp.getEmpNo()+"\t"+emp.getEmpName()+"\t"+emp.getEmpPhone()+"\t"+emp.getDept()+"\t"+emp.getJoinDate()+"\t"+emp.getSalary()+"\t"+emp.getEmail());
                    }
                    else {
                        System.out.println("该"+id+"员工未查询到");
                    }
                    break;
                case 3:
                    System.out.println("请输入要添加的员工编号,自动自增输入-1或输入指定id");
                    int id3=sc.nextInt();
                    System.out.println("请输入要添加的员工名");
                    String name=sc.next();
                    System.out.println("请输入要添加的员工电话号码");
                    String phone=sc.next();
                    System.out.println("请输入要添加的员工入职日期，自动生成当前日期null或自定义日期");
                    Date date;
                    String d=sc.next();
                    if(d.equals("null")){
                        date=new Date();
                    }else {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        System.out.println("输入自定义日期，格式yyyy-MM-dd");
                        while (true){
                            try {
                                date=sdf.parse(d);
                                break;
                            } catch (ParseException e) {
                                System.out.println("输入日期格式不规范"+e+"请重输入");
                                d=sc.next();
                            }
                        }
                    }

                    System.out.println("请输入要添加的员工部门");
                    String dept=sc.next();
                    System.out.println("请输入要添加的员工薪资");
                    double salary=sc.nextDouble();
                    System.out.println("请输入要添加的员工邮箱");
                    String email=sc.next();

                    if(employeeDao.insertEmp(id3,name,phone,date,dept,salary,email)){
                        System.out.println("添加员工成功");
                    }else {
                        System.out.println("操作失败");
                    }

                    break;
                case 4:
                    System.out.println("修改员工");
                    System.out.println("请输入要修改的id");
                    int id4=sc.nextInt();
                    System.out.println("请输入工资");
                    double salary4=sc.nextDouble();
                    if (employeeDao.updateSalary(id4,salary4)){
                        System.out.println("更新成功");
                    }
                    else {
                        System.out.println("该"+id4+"员工不存在,更新失败");
                    }
                    break;
                case 5:
                    System.out.println("请选择删除员工(全部输入-1;指定输入删除的id)");
                    int id5=sc.nextInt();
                    if (employeeDao.delectEmp(id5)){
                        System.out.println("删除成功");
                    }
                    else {
                        System.out.println("该"+id5+"员工不存在,删除失败");
                    }

                    break;
                case 6:
                    System.out.println("程序结束");
                    System.exit(0);
                    break;
                default:
                    System.out.println("输入操作序号无法识别");
            }
        }

    }
}

IDEA的一些便于操作的选择

忽略大小写，均字符提示

自动导包与删包

安装翻译插件

翻译插件

选择微软翻译

9.5

连接查询

表1 inner/left/right join 表2 on 表1.字段=表2.字段

笛卡尔积、交叉连接

将两张表中的数据两两组合，得到的结果就是交叉连接的结果，也称为笛卡尔积

集合A：{a,b}；集合B：{1,2,3}；

集合A x 集合B = {a1,a2,a3,b1,b2,b3}

select * from 表1**,**表2;
select * from 表1 cross join 表2;
select * from 表1 inner join 表2;

-- 交叉连接
SELECT * FROM book_type,book_info;

SELECT * from book_type CROSS JOIN book_info;

SELECT * from book_type INNER JOIN book_info;

以上三种方式都能将两张表中的数据互相组合，其中有很多无效数据

内连接

在交叉连接的基础上，筛选出相关联的数据

select * from 表1**,**表2 where 表1.字段 = 表2.字段;
select * from 表1 cross join 表2 on 表1.字段 = 表2.字段;
select * from 表1 inner join 表2 on 表1.字段 = 表2.字段;

-- 查询所有图书详情和类型名
select * from 图书详情表 t1,图书类型表 t2 where t1.类型编号=t2.类型编号;
select * from 图书详情表 t1 inner join 图书类型表 t2 on t1.类型编号=t2.类型编号;


-- 内连接
SELECT * FROM book_type,book_info WHERE book_type.type_id=book_info.type_id;

-- 为表重命名

SELECT * FROM book_type bt,book_info bi WHERE bt.type_id=bi.type_id;

-- 使用cross join、inner join 进行内连接使用on 
SELECT * FROM book_type bt CROSS JOIN book_info bi on bt.type_id=bi.type_id;

SELECT * from book_type bt INNER JOIN book_info bi on bt.type_id=bi.type_id ;

外连接

左连接 left join

-- 左连接
SELECT * from book_type bt left join book_info bi on bt.type_id=bi.type_id;

-- 查询没有图书的类型
SELECT type_name from book_type bt left JOIN book_info bi on bt.type_id=bi.type_id  where book_id is null;

右连接 right join

-- 右连接
SELECT * from book_info bi RIGHT JOIN book_type bt on bt.type_id=bi.type_id;

-- 查询每个类型下的图书平均价格和类型名，显示没有图书的类型
SELECT type_name,AVG(book_price) avg FROM book_info bi RIGHT JOIN book_type bt on bt.type_id=bi.type_id GROUP BY bt.type_id;

表的复用

可以在一个查询中，将一张表使用多次。一定要将表重命名

表复用

-- 表复用

-- 查询书名相同、作者不同的图书；显示图书的编号、名称、作者
SELECT t1.book_id,t1.book_name,t1.book_author from book_info t1,book_info t2 where t1.book_name=t2.book_name and t1.book_author!=t2.book_author;

-- 查询书名相同、作者不同的图书；显示图书的编号、名称、作者、类型名
SELECT t1.book_id,t1.book_name,t1.book_author,type_name from book_info t1,book_info t2,book_type bt where t1.book_name=t2.book_name and t1.book_author!=t2.book_author and t1.type_id=bt.type_id;

嵌套查询

将查询出的结果继续使用在另一个查询语句中

-- 查询价格最低的图书

-- 1.查询最低价格 2.将查询的结果看做整体作为查询的条件
select * from book_info where book_price = (select min(book_price) from book_info)

-- 按类型分组，查询每组中价格大于平均价格的图书
-- 1.查询每组的平均价格 2.类型编号相同，价格大于均价
select * from book_info bi,(select type_id , avg(book_price) avg from book_info group by type_id) temp where book_price> avg and bi. type_id=temp.type_id

数据库设计

绘图软件

实体关系模型

实体Entity：一张表就是一个实体

关系Relationship：实体与实体之间的关系

实体关系模型，也称为ER模型

用图形表示ER模型时，这个图称为ER图

ER图

用矩形表示实体
用椭圆形表示实体的属性
用菱形表示实体之间的关系
用直线连接各个图形

一对一

实体A与实体B之间唯一对应

如一个国家有一个领导人；一个人对应一个配偶

一对一

在数据库中创建表的过程

方式一：使用一张表实现

国家实体的某个属性和领导人实体的某个属性需要添加唯一约束

一张表实现

方式二：使用两张表实现(推荐)

各自保存实体的属性，选择其中一张表添加额外的一列，关联另一张表的主键，且这一列还要添加唯一约束

领导表

create table leader(
    id int not null primary key auto_increment,
    name varchar(50) not null,
    birthday date ,
    sex char(1) not null
)

国家表

create table country(
    id int not null primary key auto_increment,
    name varchar(50) not null,
    poplulation bigint not null,
    area double not null,
    leader_id int not null unique,
    foreign key leader_id references leader(id)
)

方式三：使用三张表实现

两张表用于保存两个实体的信息，第三张表用于表示对应关系

使用三张表实现

一对多/多对一

一对多：一个实体A对应多个实体B，一个实体B不能对应多个实体A

如：一个人可以有多辆车，一辆车不能被多个人拥有

多对一：多个实体B对应一个实体A，多个实体A不能对应一个实体B

如：多个学员对应一个训练他们的教官，多个教官不能对应一个学员

一对多/多对一

在数据库中创建表的过程

create table coach(
    id int not null primary key auto_increment,
    name varchar(20) not null,
    level varchar(20) not null
)

创建从表

create table student(
    id int not null primary key auto_increment,
    phone varchar(20) not null,
    name varchar(20) not null,
    coach_id int not null,
    foreign key coach_id references coach(id)
)

多对多

一个实体A可以对应多个实体B，一个实体B也可以对应多个实体A

如：一个学生可以学习多门课程，一门课程可以对应多个学习它的学生

如：一个医生对应多个病人，一个病人可以看多个医生

多对多

在数据库中创建表的过程

创建学生表

create table student(
    id int not null primary key auto_increment,
    name varchar(20) not null,
    phone varchar(20)
)

课程表

create table course(
    id varchar(10) not null primary key ,
    name varchar(20) not null,
    credit int not null
)

成绩表

create table score(
    id int not null primary key auto_increment
    s_id int not null,
    c_id varchar(20) not null,
    cj int not null,
    foreign key s_id references student(id),
    foreign key c_id references course(id)
)

总结

一对一：创建各自的实体表

在任意一张表中添加另一张表的主键字段，将其设置为唯一

一对多/多对一：先创建主表(一)，再创建从表(多)

在从表中添加主表的主键字段，外键可选添加

多对多：创建各自的实体表，再创建第三张表：“关系表”

在关系表中添加两个实体表中的主键字段，外键可选添加

练习

医院信息管理系统

科室表

create table dept(
    id int not null primary key auto_increment,
    name varchar(20) not null,
    phone varchar(20) not null
)

医生表

CREATE TABLE doctor(
    id int not null PREPARE key auto_increment,
    name VARCHAR(20) not null,
    phone VARCHAR(20) not null,
    dept_id int not null,
    FOREIGN KEY(dept_id) REFERENCES dept(id);
)

病人表

CREATE TABLE patient(
    id int not null PRIMARY key auto_increment,
    name VARCHAR(20) not null,
    phone VARCHAR(20) not null,
    age int not null,
    address VARCHAR(20)
)

诊断记录表

CREATE TABLE diagnosis(
    id int not null PRIMARY key auto_increment,
    dr_id int not null,
    p_id int not null,
    diagnosis_time dateime not null,
    diagnosis_result text not null,
    foreign key dr_id REFERENCES doctor(id),
    foreign key p_id references patient(id)
)

数据库设计规范

数据库设计的规范，简称为范式NF

范式分为第一范式1NF，第二范式2NF，第三范式3NF，BC范式BCNF，第四范式4NF，第五范式5NF共六种

这六种范式的级别越高(第一到第五越来越高)，表示数据库设计的结构越规范

每一个高等级的范式都包含了低等级的范式

通常设计数据库时，只需满足3NF即可

如：有该原始表

黄色背景称为联合主键
由学号和科目一起区分每一条记录
暂时这张表不满足任何范式

原始表

当前表如果要做增删改查的操作，会涉及到的问题

如果要加入一个新的系别，就要添加学生信息、系主任
如果要删除"刘定宇"，他所占的系别也会被删除
如果将"王海"的系别改为会计，相应的系主任也要修改
当前表中有大量冗余数据

第一范式1NF

数据表中的每一列都是不可分割的原子项

关系型数据库中，起码要满足1NF，才能创建表

上表中的"联系方式"列，可以分为"手机"和"QQ"两列，不满足原子性，不满足1NF

根据1NF修改：

根据1NF修改

第二范式2NF

在满足1NF的基础上，消除部分依赖

对于联合主键而言，每一个非主属性字段都需要完全依赖于主属性，而不是只依赖其中的一部分
在上图中，无法用学号当主键
学号和科目组合为联合主键
联合主键才能得到分数
除了学号和科目外，其他字段都是非主属性字段
- 分数完全依赖于联合主键
- 其他字段部分依赖于联合主键，所以对其进行拆分

消除部分依赖

第三范式3NF

在满足2NF的基础上，消除传递依赖

在上图中，系主任是通过学号–>系别–>系主任获取，系主任传递依赖于学号，消除这个传递依赖

**在满足2NF的基础上，消除传递依赖**

最终根据实体关系模型进行优化，体现对应关系

最终优化

名词解释

主键/主码/主属性
- 用于区分每条记录的一个字段
- 通常选择能唯一确定且几乎不会更改的字段作为主键
- 如果没有，自定义一个id列作为主键
联合主键
- 若一个字段不能唯一区分每条记录，且需要多个字段一起才能区分，这些字段组成了联合主键
完全依赖
- 如果能通过A和B得到C，A和B都不能单独得到C时，称为C完全依赖于A和B
  
  如：(学号+科目)联合主键–>分数，分数完全依赖于联合主键，其他字段完全依赖于学号
部分依赖
- 通过A和B可以得到C，单独通过A或B也能得到C，称为C部分依赖于A和B
  
  如：(学号+科目)联合主键–>姓名，其实只通过学号也能得到姓名，称为姓名部分依赖于联合主键
传递依赖
- 如果通过A得到B，通过B得到C，称为C传递依赖于A
  
  如：学号–>系–>系主任。其实只通过系也能得到系主任，称为系主任传递依赖于学号

连接查询练习


-- 创建学生信息库 studb;
drop database if EXISTS studb;
create database studb;
use studb;

-- 学生表student
drop table if EXISTS student;
create table student(
    stu_id int not null primary key auto_increment comment '学号',
    stu_name varchar(20) not null comment '姓名',
    stu_sex char(1) comment '性别'
);


-- 课程表course
drop table if EXISTS course;
create table course(
    c_id varchar(20) not null primary key comment '课程号',
    c_name varchar(20) not null comment '课程名',
    c_redit int not null comment '学分'
);



-- 成绩表score
drop table if EXISTS score;
create table score(
    s_no int not null primary key auto_increment comment '成绩编号',
    stu_id int not null comment '学号',
    c_id varchar(20) not null comment '课程号',
    cj int not null comment '成绩',
    foreign key(stu_id) REFERENCES student(stu_id),
    FOREIGN key(c_id) REFERENCES course(c_id)
);




insert into student values(1001,'张晓红','女');
insert into student values(null,'张伟','男');
insert into student values(null,'肖怀伟','男');
insert into student values(null,'卢宇鹏','男');
insert into student values(null,'白思琪','女');
insert into student values(null,'黄鹏','男');
insert into student values(null,'吕思源','女');

insert into course values('c9001','高等数学',8);
insert into course values('c9002','大学英语',8);
insert into course values('c9003','思修',8);
insert into course values('c9004','大学体育',4);


insert into score values(null,'1001','c9003','88');
insert into score values(null,'1001','c9001','79');
insert into score values(null,'1002','c9001','84');
insert into score values(null,'1002','c9003','68');
insert into score values(null,'1003','c9002','78');
insert into score values(null,'1003','c9003','90');
insert into score values(null,'1003','c9004','69');
insert into score values(null,'1004','c9003','55');
insert into score values(null,'1004','c9004','54');
insert into score values(null,'1005','c9003','68');
insert into score values(null,'1005','c9004','74');
insert into score values(null,'1005','c9002','72');

select * from course;
select * from student;
select * from score;



-- 查询每位学生的学号、姓名、所学课程名，考试成绩,对字段重命名
SELECT st.stu_id 学号,stu_name 姓名,c_name 所学课程名,cj 考试成绩 from student st,score sc,course cs where st.stu_id=sc.stu_id and sc.c_id=cs.c_id;

-- 查询所有女生的学号、姓名、学习的课程名,成绩
SELECT st.stu_id 学号,stu_name 姓名,c_name 课程名,cj 成绩 from (SELECT * from student where stu_sex='女') st,score sc,course cs where st.stu_id=sc.stu_id and sc.c_id=cs.c_id;

SELECT st.stu_id 学号,stu_name 姓名,c_name 课程名,cj 成绩 from (SELECT * from student where stu_sex='女') st INNER JOIN score sc on st.stu_id=sc.stu_id INNER JOIN course cs on sc.c_id=cs.c_id;

SELECT st.stu_id 学号,stu_name 姓名,c_name 课程名,cj 成绩 from student st INNER JOIN score sc on st.stu_id=sc.stu_id INNER JOIN course cs on sc.c_id=cs.c_id  where stu_sex='女';

-- 查询参加了’高等数学‘考试的学生学号、姓名、成绩
SELECT st.stu_id 学号,stu_name 姓名,cj 考试成绩 from student st,score sc,(SELECT * from course where c_name='高等数学') cs where st.stu_id=sc.stu_id and sc.c_id=cs.c_id;


SELECT st.stu_id 学号,stu_name 姓名,cj 考试成绩 from student st INNER JOIN score sc on st.stu_id=sc.stu_id INNER JOIN (SELECT * from course where c_name='高等数学') cs on sc.c_id=cs.c_id;

-- 查询没有参加考试的学生
SELECT st.stu_id 学号,stu_name 姓名 from student st LEFT JOIN score sc on st.stu_id=sc.stu_id where cj is null;

-- 查询每门课程的平均成绩、总成绩、最高分、最低分
SELECT c_name 课程名,avg(cj) 平均成绩,sum(cj) 总成绩,max(cj) 最高分, min(cj) 最低分 from score sc right JOIN course cs on sc.c_id=cs.c_id GROUP BY cs.c_id;

-- 查询每个学生的学号、姓名、总分、平均分
select st.stu_id 学号,stu_name 姓名,sum(cj) 总分,avg(cj) 平均分 from student st LEFT JOIN score sc on st.stu_id=sc.stu_id GROUP BY st.stu_id;

-- 查询姓“张”的同学的学号、姓名、考试课程、成绩
SELECT st.stu_id 学号,stu_name 姓名,c_name 课程名,cj 成绩 from student st INNER JOIN score sc on st.stu_id=sc.stu_id INNER JOIN course cs on sc.c_id=cs.c_id  where stu_name LIKE '张%';

-- 查询没有及格(60分以下)的学生及其课程名、成绩
SELECT stu_name 姓名,c_name 课程名,cj 成绩 from student st INNER JOIN score sc on st.stu_id=sc.stu_id INNER JOIN course cs on sc.c_id=cs.c_id  where cj<60;

-- 查询男生和女生的平均成绩
select stu_sex 性别,avg(cj) 平均分 from student st LEFT JOIN score sc on st.stu_id=sc.stu_id GROUP BY st.stu_sex;

-- 查询总分大于160分的同学的学号、姓名、总分
select st.stu_id 学号,stu_name 姓名,sum(cj) 总分 from student st INNER JOIN score sc on st.stu_id=sc.stu_id GROUP BY st.stu_id HAVING sum(cj)>160;

-- 查询平均分大于80的课程名、平均分
SELECT c_name 课程名,avg(cj) 平均分 from score sc INNER JOIN course cs on sc.c_id=cs.c_id GROUP BY cs.c_id HAVING avg(cj)>80;

-- 查询参加了3门考试的同学的学号、姓名、课程名、成绩,按成绩降序

SELECT st1.stu_id 学号,stu_name 姓名,c_name 课程名,cj 成绩 from (SELECT sc.stu_id,stu_name from student st,score sc where st.stu_id=sc.stu_id GROUP BY st.stu_id HAVING count(sc.c_id)=3) st1 INNER JOIN score sc1 on st1.stu_id=sc1.stu_id INNER JOIN course cs1 on sc1.c_id=cs1.c_id ORDER BY st1.stu_id,cj desc; 

SELECT st.stu_id 学号,stu_name 姓名,c_name 课程名,cj 成绩 from student st,score sc,course cs where st.stu_id=sc.stu_id and sc.c_id=cs.c_id and st.stu_id in (SELECT stu_id from score GROUP BY stu_id HAVING count(s_no)=3) ORDER BY st.stu_id,cj desc ;



-- 添加教师表teacher
create table teacher(
    t_id varchar(20) not null primary key,
    t_name varchar(20) not null
);

-- 添加授课表
create  table teach(
    id int not null primary key auto_increment,
    t_id varchar(20) not null,
    c_id varchar(20) not null
);


-- INSERT
insert into teacher values('t001','吴彦祖');
insert into teacher values('t002','易烊千玺');
insert into teacher values('t003','刘德华');
insert into teacher values('t005','李宇春');
insert into teacher values('t004','邓超');


insert into teach() values(0,'t001','c9001');
insert into teach() values(0,'t001','c9002');
insert into teach() values(0,'t001','c9004');
insert into teach() values(0,'t002','c9003');
insert into teach() values(0,'t002','c9001');
insert into teach() values(0,'t003','c9002');
insert into teach() values(0,'t003','c9003');
insert into teach() values(0,'t003','c9004');
insert into teach() values(0,'t005','c9001');
insert into teach() values(0,'t005','c9002');


SELECT * from teacher;
SELECT * from teach;
select * from course;
select * from student;
select * from score;


-- 查询每个教师的姓名及其所教课程
SELECT t_name 教师姓名,c_name 所教课程 from teacher tr 
INNER JOIN teach th on tr.t_id=th.t_id 
INNER JOIN course cs on th.c_id=cs.c_id;


SELECT t_name 教师姓名,c_name 所教课程 from teacher tr 
LEFT JOIN teach th on tr.t_id=th.t_id
LEFT JOIN course cs on th.c_id=cs.c_id;


-- 查询每个教师所教课程数量
SELECT t_name 教师姓名,count(c_id) 所教课程数量 from teacher tr 
INNER JOIN teach th on tr.t_id=th.t_id 
GROUP BY tr.t_id;

SELECT t_name 教师姓名,count(c_id) 所教课程数量 from teacher tr 
left JOIN teach th on tr.t_id=th.t_id 
GROUP BY tr.t_id;

-- 查询没有上课的教师姓名
SELECT t_name 教师姓名 from teacher tr 
LEFT JOIN teach th on tr.t_id=th.t_id 
WHERE c_id is null;

-- 查询授课3门的教师的姓名及其所授课程
SELECT t_name 教师姓名,c_name 所教课程 from teacher tr 
INNER JOIN teach th on tr.t_id=th.t_id 
INNER JOIN course cs on th.c_id=cs.c_id 
where tr.t_id in(SELECT t_id from teach th GROUP BY t_id HAVING count(t_id)=3);


SELECT t_name 教师姓名,c_name 所教课程 from teacher tr ,(SELECT t_id from teach th GROUP BY t_id HAVING count(t_id)=3) tmp,teach th,course cs where tr.t_id=tmp.t_id and tmp.t_id=th.t_id and th.c_id=cs.c_id;

-- 查询每个教师所带学生数量，不显示没有学生的教师
SELECT t_name 教师,count(DISTINCT sc.stu_id) 所带学生数量 from teacher tr INNER JOIN teach th on tr.t_id=th.t_id 
INNER JOIN score sc on th.c_id=sc.c_id 
GROUP BY th.t_id;


SELECT count(DISTINCT sc.stu_id) 所带学生数量 from teach th 
INNER JOIN score sc on th.c_id=sc.c_id 
GROUP BY th.t_id;


-- 查询每个教师所教课程的平均分
SELECT t_name 教师,c_name 课程名, avg 平均分 from (SELECT c_id,avg(cj) avg from score GROUP BY c_id) s1 
INNER JOIN course cs on cs.c_id=s1.c_id 
INNER JOIN teach th on th.c_id=cs.c_id INNER JOIN teacher tr on tr.t_id=th.t_id ORDER BY tr.t_id,avg DESC;


SELECT t_name 教师,c_name 课程名, avg 平均分 
from (SELECT c_id,avg(cj) avg from score GROUP BY c_id) s1 
RIGHT JOIN course cs on cs.c_id=s1.c_id  
RIGHT JOIN teach th on th.c_id=cs.c_id 
RIGHT JOIN teacher tr on tr.t_id=th.t_id 
ORDER BY tr.t_id,avg DESC;


-- 对一个老师所教的所有课程求平均值
SELECT t_name 教师,avg(cj) 平均分 from  score sc 
RIGHT JOIN teach th on th.c_id=sc.c_id 
RIGHT JOIN teacher tr on tr.t_id=th.t_id 
GROUP BY tr.t_id;



-- 查询每个同学的姓名、课程名、每门成绩、按成绩降序
SELECT stu_name 姓名,c_name 课程名,cj 成绩 from student st 
INNER JOIN score sc on st.stu_id=sc.stu_id 
INNER JOIN course cs on sc.c_id=cs.c_id 
ORDER BY st.stu_id,cj DESC; 


-- -- 查询每个同学的姓名、课程名、每门成绩、要包含没有考试的学生,按成绩降序
SELECT stu_name 姓名,c_name 课程名,cj 成绩 from student st 
LEFT JOIN score sc on st.stu_id=sc.stu_id 
LEFT JOIN course cs on sc.c_id=cs.c_id 
ORDER BY st.stu_id,cj DESC; 

-- 查询平均分最高的课程名及其授课教师

SELECT cs.c_name 课程名称,tr.t_name 授课教师 from 
(SELECT c_id,avg(cj) avg from score GROUP BY c_id) s1 
INNER JOIN course cs on cs.c_id=s1.c_id 
INNER JOIN teach th on th.c_id=cs.c_id 
INNER JOIN teacher tr on tr.t_id=th.t_id 
where avg=(SELECT max(avg) from (SELECT avg(cj) avg from score GROUP BY c_id) s);

-- 视图
-- 将某个查询得到结果临时保存为一张表
 create view myview as SELECT c_id,avg(cj) avg from score GROUP BY c_id
 
 SELECT cs.c_name 课程名称,tr.t_name 授课教师 from myview s1 
 INNER JOIN course cs on cs.c_id=s1.c_id 
 INNER JOIN teach th on th.c_id=cs.c_id 
 INNER JOIN teacher tr on tr.t_id=th.t_id 
 where avg=(SELECT max(avg) from myview);

总体应用-美术馆票务管理系统数据库设计

组织结构图

美术馆票务管理系统

E-R图

数据库具体设计

展厅表venues

-- 展馆表
create table venues(
venues_id int not null primary key auto_increment comment '展馆编号',
venues_name varchar(20) not null comment '展馆名称',
venues_price double not null comment '展馆门票价格'
)

作品展表arts

-- 作品展表
create table arts(
arts_id int not null primary key auto_increment comment '作品编号',
arts_topic varchar(50) not null comment '作品主题',
arts_author varchar(50) not null comment '作品作者',
arts_notes text comment '作品描述',
arts_poster varchar(50) comment '作品海报'
)

展览时间表show_time

-- 展映时刻表
create table show_time(
id int not null primary key auto_increment comment '编号',
exhibition_time datetime not null comment '展映时间',
total_tickets int not null comment '总票数',
remaining_tickets int not null comment '余票',
venues_id int not null comment '展馆编号',
arts_id int not null comment '作品编号'
)

用户表userinfo

-- 用户信息表
create table userinfo(
user_id int not null primary key auto_increment comment '用户编号',
user_phone varchar(50) not null comment '登录电话',
user_password varchar(50) not null comment '登录密码',
user_nickname varchar(50) comment '昵称'
)

订单表orders

-- 订单表
create table orders(
order_id int not null primary key auto_increment comment '订单编号',
show_time_id int not null comment '展映时间编号',
user_id int not null comment '用户编号',
tickets_num int comment '买票数量',
cost double comment '费用',
order_time datetime comment '下单时间',
order_state int default 0 comment '订单状态 0未使用 1已使用 2申请退票 3退票成功'
)

实际功能对应的SQL语句

-- 假设管理员已经登录
-- 添加一些展馆信息
insert into venues values
(null,'A馆',100),
(null,'B馆',80),
(null,'C馆',80),
(null,'D馆',60)
-- 有作品展想要在B馆进行展览
-- 添加一条作品展的信息
INSERT INTO arts values(9001,'xxx摄影展','xxx','记录美好生活','sdfsdf.jpg');
-- B馆进行展览
-- 添加一条展映记录
insert into show_time values(null,'2023-09-08 14:00:00',500,500,2,9001);
-- 查询当前美术馆中可以观看的展览
-- 查询展览的名称、内容、余票、价格、地点、时间
select
arts_topic,arts_notes,arts_poster,remaining_tickets,venues_price,venues_name,exhibition
_time
select *
from arts a,venues v,show_time s where a.arts_id=s.arts_id and v.venues_id=s.venues_id
and exhibition_time>now()
-- 用户登录系统
-- 注册 13895522678
select * from userinfo where user_phone = '13895522678';
insert into userinfo values(null,'13895522678','123123',concat('游
客',right('13895522678',4)))
-- 登录
select * from userinfo where user_phone='13895522678' and user_password='123123'
-- 订票
insert into orders values(null,952701,1,2,160,now(),default);
update show_time set remaining_tickets=remaining_tickets-2;
-- 查询订单
-- 查询作品展的信息、场馆信息、订单信息
select * from orders o,show_time s,arts a,venues v
where o.show_time_id=s.id and s.arts_id=a.arts_id and s.venues_id=v.venues_id

视图View

视图，可当做数据库中的一个临时表，保存一些较为复杂的查询后的结果

可直接通过视图查询数据，不需要再次编写重复的sql语句

视图还能隐藏一些查询细节，定制查询数据

SQL语句	用法
create view 视图名 as 查询的sql语句;	创建视图
select * from 视图名;	使用视图
drop view 视图名;	删除视图

-- 查询平均分最高的课程名及其授课教师

-- 视图
-- 将某个查询得到结果临时保存为一张表
 create view myview as SELECT c_id,avg(cj) avg from score GROUP BY c_id

 SELECT cs.c_name 课程名称,tr.t_name 授课教师 from myview s1 
 INNER JOIN course cs on cs.c_id=s1.c_id 
 INNER JOIN teach th on th.c_id=cs.c_id 
 INNER JOIN teacher tr on tr.t_id=th.t_id 
 where avg=(SELECT max(avg) from myview);

表的行列转换

行转列

-- 查询每个同学的姓名、课程名、每门成绩、要包含没有考试的学生

CREATE VIEW stu_score 
as SELECT st.stu_id,stu_name,c_name,cj from student st 
LEFT JOIN score sc on st.stu_id=sc.stu_id
LEFT JOIN course cs on sc.c_id=cs.c_id ; 

-- 行列转换
-- 将stu_score表中数据，输出为"学号、姓名、课程1、课程2...格式"
-- 统计函数造为一行

select stu_id,stu_name,
sum(if(c_name='思修',cj,null)) as '思修',
avg(if(c_name='高等数学',cj,null)) as '高等数学',
max(if(c_name='大学英语',cj,0)) as '大学英语',
min(if(c_name='大学体育',cj,null)) as '大学体育'
from stu_score group by stu_id

-- 如果一门课程一个学生只能有一个成绩，sum()、avg()、max()、min()都可以使用
-- 如果要统计学生对于课程的考试次数，使用count()
-- if(表达式,表达式成立时的结果,表达式不成立时的结果)

事务

事务是由一组sql语句组成的最小执行单元，这些sql之间一般都互相依赖

如A给B转账

update 表 set money = money - 200 where id=A
update 表 set money = money + 200 where id=B

以上两句sql组成了一个转账的事务，一个事务要么全部执行，要么全部不执行

事务的特性ACID

Atomicity 原子性

事务是最小的执行单元，要么全部执行，要么全部不执行
Consistency 一致性

事务执行前后，必须让所有数据保持一致状态(数据总量守恒)
Isolation 隔离性

多个事务并发执行时，应该互相隔离，互不影响
Durability 持久性

事务一旦提交，对数据的改变是永久的

事务的使用

提交commit 与 回滚rollback

mysql中的事务默认是，自动提交的

如果关闭了事务自动提交，在执行某个事务过程中，若出了错误，可使用rollback进行回滚，让数据回到事务执行之前的状态

查询事务自动提交开启状态：select @@autocommit
- 1，表示开启了自动提交
- 0，表示关闭了自动提交
关闭事务自动提交：set @@autocommit=0
手动开启事务：start transaction

手动提交/回滚事务

关闭事务自动提交：set @@autocommit=0
手动开启事务：start transaction
事务要执行的sql
没有commit提交之前，可以使用rollback回滚
如果没有问题，使用commit提交，一旦提交，无法rollback

一旦commit提交，无法rollback回滚

-- 查看事务自动提交状态
select @@autocommit
-- 设置设置不自动提交
set @@autocommit =0

-- 开启事务
start TRANSACTION;
-- 1006向1008转账200
-- 扣除1006的200
update employee set salary = salary - 200 where emp_no=1006;
-- 模拟出错
jkghkjh;
-- 1008增加200
update employee set salary = salary + 200 where emp_no=1008;

-- 先运行以上三条，若出错单独运行回滚语句，则返回数据回滚
-- 回滚
rollback;
-- 提交
commit;

事务并发可能出现的问题

在同一时刻同时执行多个事务时，称为事务并发

事务并发时有可能会出现以下问题：

问题	描述
脏读	事务A读取到了事务B未提交的数据
不可重复读	事务A中如果要读取两次数据，在这期间，事务B对数据进行了修改并提交，导致事务A读取两次的情况不一致。
幻读	事务A读取id为1-10之间的数据，假如只有2和5的数据，在读取期间，事务B添加了一条id为3的数据，导致事务A多读取到了事务B中的数据。

事务隔离级别

隔离级别	能否出现脏读	能否出现不可重复读	能否出现幻读
Read Uncommitted RU未提交读	会	会	会
Read Committed 已提交读RC(Oracle默认)	不会	会	会
Repeatable Read 可重复读RR(MySQL默认)	不会	不会	会
Serializable 可序列化	不会	不会	不会

查看事务隔离级别

select @@transaction_isolation

设置事务隔离级别

set [session|global] transatcion isolation level [Read Uncommitted|Read Committed|Repeatable Read|Serializable]

触发器trigger

若在更新某张表之前before或之后after，自动执行另一组sql时，可以使用触发器实现

表A是客户表，表B是操作日志表

对表A进行更新的操作时，将这次的操作记录保存到表B中

慎用触发器

因为触发器对于表中的每一行都生效，数据多时，触发器也会执行很多次，效率不高

创建触发器

-- 语法
-- create trigger 触发器名
-- 触发时机(before/after) 触发操作(update/insert/delete) on 表 for each row
-- begin
-- 满足触发条件后执行的内容
-- end

create trigger mytrigger
after insert on userinfo for each row
begin
insert into log values(null,'执行了添加用户的操作',now());
end

使用触发器

触发器创建成功后，无需手动调用，在执行满足触发器的条件的操作后，自动执行触发器

-- 执行这句话时，会自动在log表中添加一条记录
insert into userinfo values(null,'a','b')

删除触发器

drop trigger 触发器名;

存储过程procedure

类似于Java中的方法，定义一组用于完成特定功能的sql语句

定义存储过程后，通过调用存储过程名，就可以执行定义时的内容，存储过程还可以有参数

调用存储过程

-- 调用无参数的存储过程
call 存储过程名();

-- 调用有参数的存储过程
call 存储过程名('实参');

定义存储过程

-- 参数类型分为输入型和输出型
create procedure 存储过程名([参数类型 参数名 参数数据类型]) 
begin
sql语句;
end

定义无参数的存储过程

create procedure 存储过程名()
begin
sql语句;
end

-- 定义无参数的存储过程,查询每本图书的所有信息
CREATE PROCEDURE myproc1 ()
BEGIN
SELECT
*
FROM
book_info bi
INNER JOIN book_type bt ON bi.type_id = bt.type_id;
END

-- 调用存储过程
call myproc1();

定义输入型参数的存储过程

create procedure 存储过程名(in 形参名 数据类型)
begin
sql语句;
end

-- 根据图书类型名，查询该类型下的所有图书
CREATE PROCEDURE myproc2 (IN lx VARCHAR ( 20 ))
BEGIN
SELECT
*
FROM
book_info bi,
book_type bt
WHERE
bi.type_id = bt.type_id
AND type_name = lx;
END

-- 调用
call myproc2('漫画');

定义输出型参数的存储过程

调用输出型参数，使用@变量，接收存储过程的调用结果

create procedure 存储过程名(out 形参名 数据类型)
begin
sql语句;
end
-- 根据作者查询其出版图书数量
drop PROCEDURE myproc3;
CREATE PROCEDURE myproc3 (OUT book_count INT,IN zz VARCHAR ( 20 ))
BEGIN
SELECT
-- 将查询到的结果使用into赋值给输出型参数book_count中
count( book_id ) INTO book_count
FROM
book_info
WHERE
book_author = zz;
END
-- 调用输出型参数，使用@变量，接受存储过程的调用结果
call myproc3(@x,'金庸');
select @x;

删除存储过程

drop procedure 存储过程名;

drop PROCEDURE myproc3;

MySQL编程

在定义的存储过程中，可以使用变量、流程控制语句等

变量

定义变量
```
declare 变量名 数据类型
```

给变量赋值

-- 方法一
select 值 into 变量
-- 方法二
select 字段 into 变量 from 表

读取变量值
```
select 变量
```

定义变量

declare 变量名数据类型

create procedure 存储过程名()
begin
-- declare 变量名 数据类型;
declare num int;
declare name varchar(20);
end

给变量赋值

create procedure 存储过程名()
begin
-- declare 变量名 数据类型;
declare num int;
declare name varchar(20);
-- 给num和name赋值
-- select 值/字段 into 变量 [from 表];
select 123 into num;
select book_name into name from book_info where id=123;
end

读取变量的值

create procedure 存储过程名()
begin
-- declare 变量名 数据类型;
declare num int;
declare name varchar(20);
-- 给num和name赋值
-- select 值/字段 into 变量 [from 表];
select 123 into num;
select book_name into name from book_info where id=123;
-- select 变量名
select num;
select name;
end

练习

-- 创建存储过程，查询所有图书库存总数，保存到变量中
CREATE PROCEDURE myproc4 ()
BEGIN
-- 定义变量
DECLARE num INT;
-- 查询,将结果赋值给变量
SELECT
sum( book_num ) INTO num
FROM
book_info;
-- 读取变量的值
SELECT
num;
END
call myproc4()

条件语句

单分支if语句

if 条件 then 满足条件时执行的sql;
end if;

双分支if语句

if 条件 then 满足条件时执行的sql;
else 不满足条件时执行的sql;
end if;

case语句

case 变量
when 值 then
变量为该值时执行的sql语句;
when 值 then
变量为该值时执行的sql语句;
else
没有任何值满足时执行的sql语句;
end case;

单分支if语句

if 条件
then
满足条件时执行的sql；
end if;

-- 根据作者查询图书库存，如果不足100，输出'库存不足'
create PROCEDURE myproc5(in zz varchar(20))
begin

-- 定义变量保存最终的结果
declare num int;
-- 核心sql
select sum(book_num) into num from book_info where book_author=zz;
-- 判断
if num<100
then
select '库存不足';
end if;
end
call myproc5('鸟山明')

双分支if语句

if 条件
then
满足条件时执行的sql；
else
不满足条件时执行的sql；
end if;
-- 根据图书类型查询图书数量，如果不足5，输出'不足5中'，如果足够，输出详情
create PROCEDURE myproc6(in lx varchar(20))
begin
-- 定义变量保存图书数量
declare num int;
-- 执行查询
SELECT
count( book_id ) INTO num
FROM
book_info bi,
book_type bt
WHERE
bi.type_id = bt.type_id
AND type_name = lx;
-- 双分支判断
if num<5
then
select '不足5种';
else
SELECT
*
FROM
book_info bi,
book_type bt
WHERE
bi.type_id = bt.type_id
AND type_name = lx;
end if;
end

case语句

case 变量
when 值 then
变量为该值时执行的sql语句;
when 值 then
变量为该值时执行的sql语句;
else
没有任何值满足时执行的sql语句;
end case;
-- 输入类型编号，输出对应的类型名称，没有输出不存在
create procedure myproc7(in id int)
begin
declare type varchar(20) ;
select type_name into type from book_type where type_id=id;
case type
when '小说' then
select concat(id,'--',type);
when '漫画' then
select concat(id,'--',type);
when '杂志' then
select concat(id,'--',type);
when '传记' then
select concat(id,'--',type);
when '教材' then
select concat(id,'--',type);
else
select concat(id,'不存在');
end case;
end
call myproc7(4)
call myproc7(9)

循环语句

while循环

while 循环条件 do
循环体;
end while;

repeat循环

repeat
循环体;
unitl 条件 end repeat;

loop循环

循环名:loop
循环体;
if 循环条件 then
leave 循环名;
end if;
end loop 循环名;

while循环

while 循环条件 do
循环体;
end while;
-- 添加50本图书
create PROCEDURE myproc8()
begin
-- 定义循环变量
declare n int;
-- 循环变量定义初始值
select 1 into n;
-- 循环条件
while n<=50 do
-- 循环内容
insert into book_info values(null,concat('书名',n),'作者',now(),50,1,100);
-- 更新循环变量
set n = n+1;
end while;
end
call myproc8()

repeat循环

repeat
循环体;
unitl 条件 end repeat;
-- 添加50本图书
create PROCEDURE myproc9()
begin
-- 定义循环变量
declare n int;
-- 循环变量定义初始值
select 51 into n;
-- 循环条件
repeat
-- 循环内容
insert into book_info values(null,concat('书名',n),'作者',now(),50,1,100);
-- 更新循环变量
set n = n+1;
until n=100 end repeat;
end
call myproc9()

loop循环

循环名:loop
循环体;
if 循环条件 then
leave 循环名;
end if;
end loop 循环名;
-- 添加50本图书
create PROCEDURE myproc10()
begin
-- 定义循环变量
declare n int;
-- 循环变量定义初始值
select 100 into n;
-- 循环条件
test:loop
-- 循环内容
insert into book_info values(null,concat('书名',n),'作者',now(),50,1,100);
-- 更新循环变量
set n = n+1;
if n=151 then
leave test;
end if;
end loop test;
end
call myproc10()

综合练习-“简易商场系统”

until工具包

//DBUntil
package com.shopping.jdbc_plus.util;

import java.sql.*;

public class DBUtil {
    /*
     * 数据库工具类
     * 用于提取JDBC连接数据库时的公共代码
     * //公共代码：
     * //1.加载驱动
     * //2.获取连接
     * //3.释放资源
     * */

    //加载驱动，一次即可，放入静态代码块
    //Class.forname("com.mysql.cj.jdbc.Driver")
    static {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
        } catch (ClassNotFoundException e) {
            System.out.println("加载驱动异常"+e);
        }
    }
    //获取连接数据库
    //url、username,password
//url="jdbc:mysql://localhost:3306/数据库名?serverTimezone=Asia/Shanghai"
    static final String URL="jdbc:mysql://localhost:3306/db_shopping?serverTimezone=Asia/Shanghai";
    static final String USERNAME="root";
    static final String PASSWORD="root";

    public static Connection getConnection(){
        Connection conn=null;
        try {
            conn= DriverManager.getConnection(URL,USERNAME,PASSWORD);
        } catch (SQLException e) {
            System.out.println("连接数据库异常"+e);
        }
        return conn;
    }
    //释放资源
    public static void release(Connection conn, PreparedStatement pst, ResultSet rst){
        try {
            if(conn!=null)
                conn.close();
            if(pst!=null)
                conn.close();
            if(rst!=null)
                conn.close();
        }catch (SQLException e) {
            System.out.println("释放资源异常"+e);
        }
    }

}

entity

AdminInfo

package com.shopping.jdbc_plus.entity;

public class AdminInfo {
    private int aId;
    private String aUsername;
    private String aPassword;

    public AdminInfo() {
    }

    public AdminInfo(int aId, String aUsername, String aPassword) {
        this.aId = aId;
        this.aUsername = aUsername;
        this.aPassword = aPassword;
    }

    @Override
    public String toString() {
        return "admin{" +
            "aId=" + aId +
            ", aUsername='" + aUsername + '\'' +
            ", aPassword='" + aPassword + '\'' +
            '}';
    }

    public int getaId() {
        return aId;
    }

    public void setaId(int aId) {
        this.aId = aId;
    }

    public String getaUsername() {
        return aUsername;
    }

    public void setaUsername(String aUsername) {
        this.aUsername = aUsername;
    }

    public String getaPassword() {
        return aPassword;
    }

    public void setaPassword(String aPassword) {
        this.aPassword = aPassword;
    }
}

ClientInfo

package com.shopping.jdbc_plus.entity;

public class ClientInfo {
    private int cId;
    private String cPhone;
    private String cPassword;
    private String cName;
    private double cAmount;
    private String cAddress;

    public ClientInfo() {
    }

    public ClientInfo(int cId, String cPhone, String cPassword, String cName, double cAmount, String cAddress) {
        this.cId = cId;
        this.cPhone = cPhone;
        this.cPassword = cPassword;
        this.cName = cName;
        this.cAmount = cAmount;
        this.cAddress = cAddress;
    }

    @Override
    public String toString() {
        return "client{" +
            "cId=" + cId +
            ", cPhone='" + cPhone + '\'' +
            ", cPassword='" + cPassword + '\'' +
            ", cName='" + cName + '\'' +
            ", cAmount=" + cAmount +
            ", cAddress='" + cAddress + '\'' +
            '}';
    }

    public int getcId() {
        return cId;
    }

    public void setcId(int cId) {
        this.cId = cId;
    }

    public String getcPhone() {
        return cPhone;
    }

    public void setcPhone(String cPhone) {
        this.cPhone = cPhone;
    }

    public String getcPassword() {
        return cPassword;
    }

    public void setcPassword(String cPassword) {
        this.cPassword = cPassword;
    }

    public String getcName() {
        return cName;
    }

    public void setcName(String cName) {
        this.cName = cName;
    }

    public double getcAmount() {
        return cAmount;
    }

    public void setcAmount(double cAmount) {
        this.cAmount = cAmount;
    }

    public String getcAddress() {
        return cAddress;
    }

    public void setcAddress(String cAddress) {
        this.cAddress = cAddress;
    }
}

ProductInfo

package com.shopping.jdbc_plus.entity;

public class ProductInfo {
    private int pId;
    private String pName;
    private double pPrice;
    private int pNum;

    public ProductInfo() {
    }

    public ProductInfo(int pId, String pName, double pPrice, int pNum) {
        this.pId = pId;
        this.pName = pName;
        this.pPrice = pPrice;
        this.pNum = pNum;
    }

    @Override
    public String toString() {
        return "client{" +
            "pId=" + pId +
            ", pName='" + pName + '\'' +
            ", pPrice=" + pPrice +
            ", pNum=" + pNum +
            '}';
    }

    public int getpId() {
        return pId;
    }

    public void setpId(int pId) {
        this.pId = pId;
    }

    public String getpName() {
        return pName;
    }

    public void setpName(String pName) {
        this.pName = pName;
    }

    public double getpPrice() {
        return pPrice;
    }

    public void setpPrice(double pPrice) {
        this.pPrice = pPrice;
    }

    public int getpNum() {
        return pNum;
    }

    public void setpNum(int pNum) {
        this.pNum = pNum;
    }
}

dao

AdminInfoDao

package com.shopping.jdbc_plus.dao;

import com.shopping.jdbc_plus.entity.AdminInfo;
import com.shopping.jdbc_plus.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/*
 * dao层表示数据库访问层
 * 该层中的类都是用于操作数据库
 * 类名通常命名为:实体类Dao
 * */
public class AdminInfoDao {
    //定义操作数据库时所需要的接口
    private Connection conn;
    private PreparedStatement pst;
    private ResultSet rst;
    //一个方法、加载驱动、连接、构造sql语句，对其预处理

    //验证登录
    public AdminInfo loginAdmin(AdminInfo ad){
        //加载驱动、连接数据
        conn= DBUtil.getConnection();
        //构造sql语句
        try {
            pst= conn.prepareStatement("select * from admin where a_username=? and a_password=?");
            pst.setString(1,ad.getaUsername());
            pst.setString(2,ad.getaPassword());
            rst=pst.executeQuery();
            if(rst.next()){
                ad.setaId(rst.getInt(1));
                return ad;
            }
        } catch (SQLException e) {
            System.out.println("管理员验证登录异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return null;
    }
}

ClientInfoDao

package com.shopping.jdbc_plus.dao;

import com.shopping.jdbc_plus.entity.ClientInfo;
import com.shopping.jdbc_plus.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class ClientInfoDao {
    Connection conn;
    PreparedStatement pst;
    ResultSet rst;

    //用户查询
    public ClientInfo findByPhone(ClientInfo c){
        conn= DBUtil.getConnection();
        try {
            pst= conn.prepareStatement("select * from client where c_phone=?");
            pst.setString(1,c.getcName());
            ResultSet rst= pst.executeQuery();
            if(rst.next()){
                c.setcId(rst.getInt(1));
                c.setcPhone(rst.getString(2));
                c.setcPassword(rst.getString(3));
                c.setcName(rst.getString(4));
                c.setcAmount(rst.getDouble(5));
                c.setcAddress(rst.getString(6));
                return c;
            }
        } catch (SQLException e) {
            System.out.println("用户查询异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return null;
    }
    //用户注册
    public boolean signClient(ClientInfo c){
        conn= DBUtil.getConnection();
        try {
            pst= conn.prepareStatement("insert into client values (null,?,?,concat('用户',REPLACE(?,SUBSTR(?,4,4),'****')),default ,default)");
            pst.setString(1,c.getcPhone());
            pst.setString(2,c.getcPassword());
            pst.setString(3,c.getcPhone());
            pst.setString(4,c.getcPhone());
            return pst.executeUpdate()>0;
        } catch (SQLException e) {
            System.out.println("用户查询注册异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return false;
    }

    //用户登录
    public ClientInfo loginClient(ClientInfo c){
        conn= DBUtil.getConnection();
        try {
            pst= conn.prepareStatement("select * from client where c_phone=? and c_password=? ");
            pst.setString(1,c.getcPhone());
            pst.setString(2,c.getcPassword());
            ResultSet rst= pst.executeQuery();
            if (rst.next()){
                c.setcId(rst.getInt(1));
                c.setcName(rst.getString(4));
                c.setcAmount(rst.getDouble(5));
                c.setcAddress(rst.getString(6));
                return c;
            }
        } catch (SQLException e) {
            System.out.println("用户登录异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return null;
    }

    //用户修改字段
    public boolean updateClient(ClientInfo c){
        conn= DBUtil.getConnection();
        try {
            pst= conn.prepareStatement("update client set c_phone=?,c_password=?,c_name=?,c_amount=?,c_address=? where c_id=? ");
            pst.setString(1,c.getcPhone());
            pst.setString(2,c.getcPassword());
            pst.setString(3,c.getcName());
            pst.setDouble(4,c.getcAmount());
            pst.setString(5,c.getcAddress());
            pst.setInt(6,c.getcId());
            return pst.executeUpdate()>0;
        } catch (SQLException e) {
            System.out.println("用户修改异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return false;
    }
}

ProductInfoDao

package com.shopping.jdbc_plus.dao;

import com.shopping.jdbc_plus.entity.ProductInfo;
import com.shopping.jdbc_plus.util.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductInfoDao {
    Connection conn;
    PreparedStatement pst;
    ResultSet rst;

    //查询所有商品
    public List<ProductInfo> QueryAll(){
        List<ProductInfo> list=new ArrayList<>();
        conn= DBUtil.getConnection();
        try {
            pst= conn.prepareStatement("select * from product");
            ResultSet rst= pst.executeQuery();
            while (rst.next()){
                ProductInfo p=new ProductInfo(rst.getInt(1),rst.getString(2),rst.getDouble(3),rst.getInt(4));
                list.add(p);
            }
        } catch (SQLException e) {
            System.out.println("用户查询异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return list;
    }

    //利用id查询商品
    public ProductInfo findById(ProductInfo p){
        conn= DBUtil.getConnection();
        try {
            pst= conn.prepareStatement("select * from product where p_id=?");
            pst.setInt(1,p.getpId());
            ResultSet rst= pst.executeQuery();
            if(rst.next()){
                p.setpId(rst.getInt(1));
                p.setpName(rst.getString(2));
                p.setpPrice(rst.getDouble(3));
                p.setpNum(rst.getInt(4));
                return p;
            }
        } catch (SQLException e) {
            System.out.println("用户查询id找商品异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return null;
    }

    //增加商品
    public boolean addProduct(ProductInfo p){
        conn= DBUtil.getConnection();
        try {
            pst= conn.prepareStatement("insert into product values(null,?,?,?)");
            pst.setString(1,p.getpName());
            pst.setDouble(2,p.getpPrice());
            pst.setInt(3,p.getpNum());
            return pst.executeUpdate()>0;
        } catch (SQLException e) {
            System.out.println("用户增加商品异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return false;
    }

    //根据id修改商品
    public boolean updateProduct(ProductInfo p){
        conn= DBUtil.getConnection();
        try {
            pst= conn.prepareStatement("update product set p_name=?,p_price=?,p_num=? where p_id=? ");
            pst.setString(1,p.getpName());
            pst.setDouble(2,p.getpPrice());
            pst.setInt(3,p.getpNum());
            pst.setInt(4,p.getpId());
            return pst.executeUpdate()>0;
        } catch (SQLException e) {
            System.out.println("用户修改商品异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return false;
    }

    //根据id删除商品
    public boolean delProduct(ProductInfo p){
        conn= DBUtil.getConnection();
        try {
            pst= conn.prepareStatement("delete from product where p_id=? ");
            pst.setInt(1,p.getpId());
            return pst.executeUpdate()>0;
        } catch (SQLException e) {
            System.out.println("用户删除商品异常"+e);
        }finally {
            DBUtil.release(conn,pst,rst);
        }
        return false;
    }
}

service

Main

package com.shopping.jdbc_plus.service;

import com.shopping.jdbc_plus.dao.AdminInfoDao;
import com.shopping.jdbc_plus.dao.ClientInfoDao;
import com.shopping.jdbc_plus.dao.ProductInfoDao;
import com.shopping.jdbc_plus.entity.AdminInfo;
import com.shopping.jdbc_plus.entity.ClientInfo;
import com.shopping.jdbc_plus.entity.ProductInfo;

import java.util.List;
import java.util.Scanner;

public class Main {
    Scanner scan=new Scanner(System.in);
    AdminInfoDao ad=new AdminInfoDao();
    ClientInfoDao cl=new ClientInfoDao();
    ProductInfoDao pd=new ProductInfoDao();
    void loginMenu() {
        System.out.println("请选择登录方式：1为管理员,2为用户，3为退出");
        int i = scan.nextInt();
        switch (i) {
            case 1:
                AdminInfo a=new AdminInfo();

                //错误
                /*a.setaUsername("root");
                a.setaPassword("12345");*/

                //正确
                a.setaUsername("root");
                a.setaPassword("123456");
                if(ad.loginAdmin(a)!=null){
                    System.out.println("登录成功");
                    adminMenu();
                }else
                    System.out.println("用户名或密码错误");
                break;
            case 2:
                cLoginMenu();

                break;
            case 3:
                System.exit(0);
            default:
                System.out.println("方式不可识别，请重新选择");
        }
        loginMenu();
    }
    void adminMenu(){
        ProductInfo p;
        System.out.println("管理员操作菜单:1为查看所有商品，2为添加新商品，3为修改某个商品，4为删除某个商品 5为返回");
        int i = scan.nextInt();
        switch (i){
            case 1:
                List<ProductInfo> list=pd.QueryAll();
                System.out.println("查看所有商品");
                list.forEach(a->System.out.println(a));
                break;
            case 2:
                p=new ProductInfo();
                p.setpName("螺蛳粉");
                p.setpPrice(12.4);
                p.setpNum(215);
                if(pd.addProduct(p))
                    System.out.println("添加新产品成功");
                else
                    System.out.println("添加新产品失败");
                break;
            case 3:
                p=new ProductInfo();
                p.setpId(5);
                if(pd.findById(p)!=null){
                    p.setpNum(1000);
                    p.setpPrice(50);
                    if(pd.updateProduct(p))
                        System.out.println("商品修改成功");
                    else
                        System.out.println("商品修改失败");
                }
                else
                    System.out.println("该商品不存在，无法修改");
                break;
            case 4:
                p=new ProductInfo();
                p.setpId(7);
                if(pd.findById(p)!=null){
                    if(pd.delProduct(p))
                        System.out.println("商品删除成功");
                    else
                        System.out.println("商品删除失败");
                }
                else
                    System.out.println("该商品不存在，无法删除");
                break;
            case 5:
                loginMenu();
                break;
        }
        adminMenu();
    }
    void cLoginMenu(){
        ClientInfo c;
        System.out.println("请选择操作：1为用户登录,2为用户注册，3为返回");
        int i = scan.nextInt();
        switch (i){
            case 1:
                c=new ClientInfo();
                //错误
                /*c.setcPhone("11165432112");
                c.setcPassword("12345675");*/

                //正确
                c.setcPhone("98765432112");
                c.setcPassword("12345678");
                if(cl.loginClient(c)!=null){
                    System.out.println("登录成功");
                    clientMenu(c);
                }
                else
                    System.out.println("用户名或密码错误");
                break;
            case 2:
                c=new ClientInfo();
                //错误
                /*c.setcPhone("98765432112");
                c.setcPassword("12345678");*/
                //正确
                c.setcPhone("21323432112");
                c.setcPassword("12345678");

                if (cl.findByPhone(c)==null){
                    if (cl.signClient(c)){
                        System.out.println("注册成功");
                        if(cl.loginClient(c)!=null){
                            System.out.println("自动登录！！");
                            clientMenu(c);
                        }
                    }
                }else {
                    System.out.println("该用户已注册");
                }
                break;
            case 3:
                loginMenu();
                break;
        }
        cLoginMenu();
    }
    void clientMenu(ClientInfo c){
        ProductInfo p;
        System.out.println("客户操作菜单:1为查看所有商品，2为查看某个商品，3为购买某个商品，4为充值余额，5为查看个人信息，6为返回");
        int i = scan.nextInt();
        switch (i){
            case 1:
                System.out.println("");
                List<ProductInfo> list=pd.QueryAll();
                System.out.println("查看所有商品");
                list.forEach(a->System.out.println(a));
                break;
            case 2:
                p=new ProductInfo();
                p.setpId(12);
                if(pd.findById(p)!=null)
                    System.out.println(p);
                else
                    System.out.println("该商品未查找到");
                break;
            case 3:
                System.out.println("购买");
                int count=10;
                p=new ProductInfo();
                p.setpId(11);
                if(pd.findById(p)==null)
                    System.out.println("该商品不存在");
                else if(p.getpNum()<count)
                    System.out.println("该商品库存不足");
                else if(c.getcAmount()<count*p.getpPrice())
                    System.out.println("该用户余额不足");
                else {
                    p.setpNum(p.getpNum()-count);
                    c.setcAmount(c.getcAmount()-count*p.getpPrice());
                    if(pd.updateProduct(p)&&cl.updateClient(c)){
                        System.out.println("商品名：" + p.getpName());
                        System.out.println("购买数量：" + count);
                        System.out.println("订单金额：" + count * p.getpPrice());
                        System.out.println("购买成功");
                    }
                    else
                        System.out.println("购买失败");

                }
                break;
            case 4:
                c.setcAmount(c.getcAmount()+5000);
                if(cl.updateClient(c))
                    System.out.println("充值成功");
                else
                    System.out.println("充值失败");
                break;
            case 5:
                System.out.println(c);
                break;
            case 6:
                cLoginMenu();
                break;
        }
        clientMenu(c);
    }

    public static void main(String[] args) {
        Main m=new Main();
        m.loginMenu();
    }
}

112");
c.setcPassword(“12345678”);

            if (cl.findByPhone(c)==null){
                if (cl.signClient(c)){
                    System.out.println("注册成功");
                    if(cl.loginClient(c)!=null){
                        System.out.println("自动登录！！");
                        clientMenu(c);
                    }
                }
            }else {
                System.out.println("该用户已注册");
            }
            break;
        case 3:
            loginMenu();
            break;
    }
    cLoginMenu();
}
void clientMenu(ClientInfo c){
    ProductInfo p;
    System.out.println("客户操作菜单:1为查看所有商品，2为查看某个商品，3为购买某个商品，4为充值余额，5为查看个人信息，6为返回");
    int i = scan.nextInt();
    switch (i){
        case 1:
            System.out.println("");
            List<ProductInfo> list=pd.QueryAll();
            System.out.println("查看所有商品");
            list.forEach(a->System.out.println(a));
            break;
        case 2:
            p=new ProductInfo();
            p.setpId(12);
            if(pd.findById(p)!=null)
                System.out.println(p);
            else
                System.out.println("该商品未查找到");
            break;
        case 3:
            System.out.println("购买");
            int count=10;
            p=new ProductInfo();
            p.setpId(11);
            if(pd.findById(p)==null)
                System.out.println("该商品不存在");
            else if(p.getpNum()<count)
                System.out.println("该商品库存不足");
            else if(c.getcAmount()<count*p.getpPrice())
                System.out.println("该用户余额不足");
            else {
                p.setpNum(p.getpNum()-count);
                c.setcAmount(c.getcAmount()-count*p.getpPrice());
                if(pd.updateProduct(p)&&cl.updateClient(c)){
                    System.out.println("商品名：" + p.getpName());
                    System.out.println("购买数量：" + count);
                    System.out.println("订单金额：" + count * p.getpPrice());
                    System.out.println("购买成功");
                }
                else
                    System.out.println("购买失败");

            }
            break;
        case 4:
            c.setcAmount(c.getcAmount()+5000);
            if(cl.updateClient(c))
                System.out.println("充值成功");
            else
                System.out.println("充值失败");
            break;
        case 5:
            System.out.println(c);
            break;
        case 6:
            cLoginMenu();
            break;
    }
    clientMenu(c);
}

public static void main(String[] args) {
    Main m=new Main();
    m.loginMenu();
}

}