目录
第一题
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包
