2022-8-21 第一组 (≥▽≤) 周测试

目录

第一题

SELECT stu.`姓名`,stu.`学号` FROM student stu
 LEFT JOIN SC sc ON sc.`学号` = stu.`学号` 
LEFT JOIN Course c ON c.`课程编号` = sc.`课程编号`
 WHERE c.`课程名称` = '计算机原理';

SELECT c.`课程名称` FROM student stu 
LEFT JOIN SC sc ON sc.`学号` = stu.`学号`
 LEFT JOIN Course c ON c.`课程编号` = sc.`课程编号`
 WHERE stu.`姓名` = '周星驰';

SELECT stu.`姓名`,stu.`学号` FROM student stu
 LEFT JOIN SC sc ON sc.`学号` = stu.`学号`
 LEFT JOIN Course c ON c.`课程编号` = sc.`课程编号`
 GROUP BY stu.`学号`
 HAVING COUNT(sc.`课程编号`) = 5;

第二题

第一步

CREATE TABLE `bank`(
    `id` INT  PRIMARY KEY auto_increment,
    `no` VARCHAR(30) UNIQUE,
    `password` VARCHAR(30) ,
    `name` VARCHAR(30),
    `money` DECIMAL 
)

第二步

package exam1;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class CreateBankAccount {
    public static void main(String[] args) {
        Scanner scanner =new Scanner(System.in);
        System.out.println("请输入开户的卡号");
        String next = scanner.next();
        System.out.println("请输入开户的名字");
        String next1 = scanner.next();
        System.out.println("请输入开户的密码");
        String next2 = scanner.next();
        System.out.println("请输入开户的钱数");
        double v = scanner.nextDouble();
        new CreateBankAccount().insert(next,next2,next1,new BigDecimal(v));
    }

    public void insert(String no, String password, String name, BigDecimal money) {
        Connection connection = null;
        PreparedStatement statement =null;
        try {
            String sql="insert  into  bank values(null ,?,?,?,? ) ";
            connection= BaseDao.DATA_SOURCE.getConnection();
            statement=connection.prepareStatement(sql);
            statement.setString(1,no);
            statement.setString(2,password);
            statement.setString(3,name);
            statement.setBigDecimal(4,money);
            int i = statement.executeUpdate();
            if (i!=0){
                System.out.println("开户成功");
            }else {
                System.out.println("开户失败");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            BaseDao.release(null,statement,null);
        }
    }
}

第三步

package exam1;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.Scanner;

public class BankAccountTransfer {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入你的卡号");
        String next2 = scanner.next();
        System.out.println("请输入收款的账户");
        String next = scanner.next();
        System.out.println("请输入收款人姓名");
        String next1 = scanner.next();
        System.out.println("请输入转账金额");
        double v = scanner.nextDouble();
        insert(next2,next,next1,new BigDecimal(v));
    }
    public static  void insert(String no1,String no, String name, BigDecimal money){
        Connection connection =null;
        PreparedStatement statement =null;
        try {
            String sql1="update bank set money=money-? where `no` =? ";
            String sql2="update bank set money=money+? where `no` =? and  `name` =?";
            connection=BaseDao.DATA_SOURCE.getConnection();
            connection.setAutoCommit(false);
            statement=connection.prepareStatement(sql1);
            statement.setBigDecimal(1,money);
            statement.setString(2,no1);
            statement.executeUpdate();

            statement=connection.prepareStatement(sql2);
            statement.setBigDecimal(1,money);
            statement.setString(2,no);
            statement.setString(3,name);
            statement.executeUpdate();
            connection.commit();
            System.out.println("转账成功");
        } catch (SQLException e) {
            try {
                System.out.println("转账失败");
                connection.rollback();
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            BaseDao.release(null,statement,null);
        }
    }
}

工具类

package exam1;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import javax.sql.DataSource;
import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Objects;
import java.util.Properties;

public class BaseDao {

    public static final DataSource DATA_SOURCE;

    static {
        Properties properties = new Properties();
        try {
            properties.load(BaseDao.class.getClassLoader().getResourceAsStream("druid.properties"));
            DATA_SOURCE = DruidDataSourceFactory.createDataSource(properties);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }

    public static void release(Connection conn, Statement stmt, ResultSet rs) {
        if (Objects.nonNull(stmt)) {
            try {
                stmt.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        }

配置文件

  • druid.properties
druid.driver=com.mysql.cj.jdbc.Driver
druid.url=jdbc:mysql://localhost:3306/night?useSSL=false&serverTimezone=Asia/Shanghai
druid.username=root
druid.password=3306

导入jar包

posted @ 2022-08-22 13:55  (≧∇≦)(≧∇≦)(≧∇≦)  阅读(21)  评论(0)    收藏  举报