开发一个简单的数据库应用程序。
要求如下：
使用附件中的SQL语句初始化数据库，完成一个控制台应用（也可用SWING应用或Web应用）程序的开发，该程序应该能够提供如下功能：
1. 用户通过菜单可以选择不同操作功能。
2. 用户可以查询所有课程信息。
3. 用户可以新增课程信息。
4. 用户可以修改现有的课程信息。
5. 用户可以删除指定课程的信息。
6. 用户可以查询指定某门课程的选课情况。
参考资料：
基于Java的GUI界面+SQL Server数据库课程信息管理系统
GetDBConnection.java
import java.sql.*;
public class GetDBConnection {
public static Connection connectDB(String DBName,String id,String p) {
Connection con = null;
String
uri = "jdbc:mysql://localhost:3308/"+DBName+"?useSSL=true&characterEncoding=utf-8";
try{ Class.forName("com.mysql.jdbc.Driver");//加载JDBC-MySQL驱动
}
catch(Exception e){}
try{
con = DriverManager.getConnection(uri,id,p); //连接代码
}
catch(SQLException e){}
return con;
}
}
LoginWindow.java
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionListener;
import java.awt.event.ActionEvent;
public class LoginWindow extends JFrame{
//标签
private JLabel lable1;
private JLabel lable2;
//文本框
private JTextField text1;
private JTextField text2;
//按钮
private JButton bt1;
private JButton bt2;
//构造函数
public LoginWindow()
{
this.init();
this.addComponent();
this.addListener();
}
public void init()
{
this.setSize(500,400);
this.setVisible(true);
this.setTitle("登录界面");
this.setLayout(null);
this.setLocation(700, 300);
this.getContentPane().setBackground(Color.orange);
}
private void addComponent()
{
lable1 = new JLabel("用户名");
lable1.setSize(100,70);
lable1.setLocation(100,80);
this.add(lable1);
lable2 = new JLabel("密 码");
lable2.setSize(100,70);
lable2.setLocation(100,130);
this.add(lable2);
text1 = new JTextField();
text1.setSize(150,30);
text1.setLocation(160,100);
this.add(text1);
text2 = new JTextField();
text2.setSize(150,30);
text2.setLocation(160,150);
this.add(text2);
bt1 = new JButton("登录");
bt1.setSize(70,30);
bt1.setLocation(140,195);
this.add(bt1);
bt2 = new JButton("退出");
bt2.setSize(70,30);
bt2.setLocation(250,195);
this.add(bt2);
this.setBackground(Color.GRAY);
//设置单击关闭按钮时的默认操作
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
}
private void addListener()
{
bt1.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
if(text1.getText().equals("abc")&&text2.getText().equals("123"))
{
new Menu();
dispose();
}
else
{
JOptionPane.showMessageDialog(null, "登陆密码错误");
}
}
});
bt2.addActionListener(new ActionListener()
{
public void actionPerformed(ActionEvent e)
{
dispose();
}
});
}
public static void main(String[] args) {
new LoginWindow();
}
}
Menu.java
import java.awt.Color;
import java.awt.Font;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.*;
public class Menu extends JFrame implements ActionListener {
JButton bt1;
JButton bt2;
JButton bt3;
JButton bt4;
JButton bt5;
JPanel panel;
JPanel panel2;
JLabel label1;
JLabel label2;
Menu(){
this.setSize(900, 700);// 设置尺寸
this.setTitle("学生课程管理系统");
this.setLayout(null);
this.setLocation(400,200);// 设置坐标
this.getContentPane().setBackground(Color.orange);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
bt1=new JButton("查询课程");
bt1.setSize(150, 50);
bt1.setLocation(150, 400);
bt1.addActionListener(new queryActionPerformed());
bt1.setActionCommand("查询课程");
bt2=new JButton("删除课程");
bt2.setSize(150, 50);
bt2.setLocation(150, 500);
bt2.addActionListener(this);
bt2.setActionCommand("删除课程");
bt3=new JButton("添加课程");
bt3.setSize(150, 50);
bt3.setLocation(550, 400);
bt3.addActionListener(this);
bt3.setActionCommand("添加课程");
bt4=new JButton("修改课程");
bt4.setSize(150, 50);
bt4.setLocation(550, 500);
bt4.addActionListener(this);
bt4.setActionCommand("修改课程");
bt5=new JButton("查找新增课程");
bt5.setSize(150, 50);
bt5.setLocation(350, 450);
bt5.addActionListener(this);
bt5.setActionCommand("查找新增课程");
this.add(bt1);
this.add(bt2);
this.add(bt3);
this.add(bt4);
this.add(bt5);
panel=new JPanel(); // 创建内容面板
panel.setLocation(100, 20);
panel.setLayout(null);
this.add(panel);
panel2=new JPanel();
panel2.setSize(650,350);
panel2.setLocation(100, 20);
panel2.setLayout(null);
panel2.setBackground(Color.white);
label1=new JLabel(); // 创建一个标签
label2=new JLabel();
label1.setText("WELCOME");
label2.setText("欢迎登陆课程管理系统");
label1.setLocation(270,20);
label1.setSize(500, 200);
label2.setLocation(165,60);
label2.setSize(500, 200);
panel2.add(label1);
panel2.add(label2);
label1.setFont( (new Font("仿宋",Font.BOLD,30)));
label2.setFont( (new Font("仿宋",Font.BOLD,30)));
this.add(panel2);
panel2.setVisible(true);
this.setVisible(true);
}
public class queryActionPerformed implements ActionListener
{
public void actionPerformed(ActionEvent e)
{
new FindCourse().setVisible(true);
}
}
@Override
public void actionPerformed(ActionEvent e) {
JButton bt=(JButton )e.getSource();
//移除上一个面板
if(bt!=null)
{
this.remove(panel2);
this.remove(panel);
}
if(bt.getText().equals("查询课程"))
{
/*panel=new FindCourse();
panel.setLocation(100, 20);
this.add(panel);
this.repaint();*/
bt1=new JButton("查询课程");
bt1.addActionListener(new queryActionPerformed());
}
else{
if(bt.getText().equals("添加课程"))
{
panel=new AddCourse();
panel.setLocation(100, 20);
this.add(panel);
this.repaint();
}
else{
if(bt.getText().equals("删除课程"))
{
panel=new DeleteCourse();
panel.setLocation(100, 20);
this.add(panel);
this.repaint();
}
else{
if(bt.getText().equals("修改课程"))
{
panel=new UpdateCourse();
panel.setLocation(100, 20);
this.add(panel);
this.repaint();
}
else{
if(bt.getText().equals("查找新增课程"))
{
panel=new FindNewCourse();
panel.setLocation(100, 20);
this.add(panel);
this.repaint();
}
}
}
}
}
}
}
AddCourse.java
import java.awt.Color;
import java.awt.Font;
import java.sql.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.*;
public class AddCourse extends JPanel implements ActionListener{
JLabel cname;
JLabel cno;
JLabel cpno;
JLabel credit;
JTextField cnametext;
JTextField cnotext;
JTextField cpnotext;
JTextField credittext;
JButton Addbt;
public AddCourse() {
this.setSize(650,350);
this.setLocation(100, 20);
this.setLayout(null);
this.setBackground(Color.WHITE);
cname=new JLabel("需添加的课程名称");
cname.setSize(200,30);
cname.setLocation(60, 20);
this.add(cname);
cnametext=new JTextField();
cnametext.setSize(120,30);
cnametext.setLocation(180, 20);
this.add(cnametext);
cno=new JLabel("需添加的课程号");
cno.setSize(200,30);
cno.setLocation(60, 60);
this.add(cno);
cnotext=new JTextField();
cnotext.setSize(120,30);
cnotext.setLocation(180, 60);
this.add(cnotext);
cpno=new JLabel("需添加的先修课程号");
cpno.setSize(200,30);
cpno.setLocation(60, 100);
this.add(cpno);
cpnotext=new JTextField();
cpnotext.setSize(120,30);
cpnotext.setLocation(180, 100);
this.add(cpnotext);
credit=new JLabel("需添加的课程学分");
credit.setSize(200,30);
credit.setLocation(60, 140);
this.add(credit);
credittext=new JTextField();
credittext.setSize(120,30);
credittext.setLocation(180, 140);
this.add(credittext);
this.setVisible(true);
Addbt=new JButton("添加");
Addbt.setSize(80,30);
Addbt.setLocation(180, 200);
this.add(Addbt);
Addbt.addActionListener(this);
}
@Override
public void actionPerformed(ActionEvent e) {
String addcname=cnametext.getText();
String addcno=cnotext.getText();
String addcpno=cpnotext.getText();
String addcredit=credittext.getText();
Connection con;
Statement sql;
con = GetDBConnection.connectDB("EDUC","root","");
if(con == null ) return;
try {
sql=con.createStatement();
//创建SQL语句执行对象
String strSQL="insert into course values('"+addcno+"','"+addcname+"','"+addcpno+"','"+addcredit+"')";
String strSQL1="(Select* from course where cname='"+addcname+"' )";
if(!addcname.trim().equals("")&&!addcno.trim().equals("")&&!addcpno.trim().equals("")&&!addcredit.trim().equals(""))
{
ResultSet rs1=sql.executeQuery(strSQL1);
if(rs1.next())
{
JOptionPane.showMessageDialog(null,"该课程已存在"); }
else {
int rs=sql.executeUpdate(strSQL);
if(rs==1) {
JOptionPane.showMessageDialog(null,"课程添加成功");
}
else{
JOptionPane.showMessageDialog(null,"课程添加失败");
}
}
}
else
{
JOptionPane.showMessageDialog(null,"课程信息输入有误，请重新输入！");
}
con.close();
//关闭数据库连接
}
catch (SQLException ex) {
System.out.println("数据库连接或者是数据库操作失败");
}
}
}
FindNewCourse.java
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class FindNewCourse extends JPanel implements ActionListener{
JLabel cname;
JLabel cno;
JLabel cpno;
JLabel credit;
JTextField cnametext;
JTextField cnotext;
JTextField cpnotext;
JTextField credittext;
JButton Findbt;
JLabel Inputlabel;
JTextField Inputtext;
public FindNewCourse() {
this.setSize(650,350);
this.setLocation(100, 20);
this.setLayout(null);
this.setBackground(Color.WHITE);
Inputlabel=new JLabel("请输入需要查找的课程名称");
Inputlabel.setSize(300,50);
Inputlabel.setLocation(320, 45);
this.add(Inputlabel);
Inputtext=new JTextField();
Inputtext.setSize(160,40);
Inputtext.setLocation(480, 45);
this.add(Inputtext);
cname=new JLabel("课程名称");
cname.setSize(200,30);
cname.setLocation(60, 60);
this.add(cname);
cnametext=new JTextField();
cnametext.setSize(120,30);
cnametext.setLocation(180, 60);
this.add(cnametext);
cno=new JLabel("课程号");
cno.setSize(200,30);
cno.setLocation(60, 20);
this.add(cno);
cnotext=new JTextField();
cnotext.setSize(120,30);
cnotext.setLocation(180, 20);
this.add(cnotext);
cpno=new JLabel("先修课程号");
cpno.setSize(200,30);
cpno.setLocation(60, 100);
this.add(cpno);
cpnotext=new JTextField();
cpnotext.setSize(120,30);
cpnotext.setLocation(180, 100);
this.add(cpnotext);
credit=new JLabel("课程学分");
credit.setSize(200,30);
credit.setLocation(60, 140);
this.add(credit);
credittext=new JTextField();
credittext.setSize(120,30);
credittext.setLocation(180, 140);
this.add(credittext);
this.setVisible(true);
Findbt=new JButton("查找");
Findbt.setSize(80,30);
Findbt.setLocation(400, 100);
this.add(Findbt);
Findbt.addActionListener(this);
}
@Override
public void actionPerformed(ActionEvent e) {
String inputName=Inputtext.getText();
Connection con;
Statement sql;
con = GetDBConnection.connectDB("EDUC","root","");
if(con == null ) return;
try {
sql=con.createStatement();
//创建SQL语句执行对象
String strSQL="(Select* from course where cname='"+inputName+"' )";
ResultSet rs=sql.executeQuery(strSQL);
if(rs.next())
{
cnotext.setText(rs.getString(1));
cnametext.setText(rs.getString(2));
cpnotext.setText(rs.getString(3));
credittext.setText(rs.getString(4));
}
else
{
JOptionPane.showMessageDialog(null, "您查询的课程不存在，请重新输入");
}
con.close();
//关闭数据库连接
}
catch (SQLException ex) {
System.out.println("数据库连接或者是数据库操作失败");
}
}
}
UpdateCourse.java
import javax.swing.*;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class UpdateCourse extends JPanel implements ActionListener {
JLabel Inputlabel;
JTextField Inputtext;
JLabel cname;
JLabel cno;
JLabel cpno;
JLabel credit;
JTextField cnametext;
JTextField cnotext;
JTextField cpnotext;
JTextField credittext;
JButton Updatebt;
public UpdateCourse() {
this.setSize(650,350);
this.setLocation(100, 20);
this.setLayout(null);
this.setBackground(Color.white);
cname=new JLabel("需修改的课程名称");
cname.setSize(200,30);
cname.setLocation(60, 20);
this.add(cname);
cnametext=new JTextField();
cnametext.setSize(120,30);
cnametext.setLocation(180, 20);
this.add(cnametext);
cno=new JLabel("需修改的课程号");
cno.setSize(200,30);
cno.setLocation(60, 60);
this.add(cno);
cnotext=new JTextField();
cnotext.setSize(120,30);
cnotext.setLocation(180, 60);
this.add(cnotext);
cpno=new JLabel("需修改的先修课程号");
cpno.setSize(200,30);
cpno.setLocation(60, 100);
this.add(cpno);
cpnotext=new JTextField();
cpnotext.setSize(120,30);
cpnotext.setLocation(180, 100);
this.add(cpnotext);
credit=new JLabel("需修改的课程学分");
credit.setSize(200,30);
credit.setLocation(60, 140);
this.add(credit);
credittext=new JTextField();
credittext.setSize(120,30);
credittext.setLocation(180, 140);
this.add(credittext);
this.setVisible(true);
Updatebt=new JButton("修改");
Updatebt.setSize(80,30);
Updatebt.setLocation(180, 200);
this.add(Updatebt);
Updatebt.addActionListener(this);
}
@Override
public void actionPerformed(ActionEvent e) {
String updatecname=cnametext.getText();
String updatecpno=cpnotext.getText();
String updatecno=cnotext.getText();
String updatecredit=credittext.getText();
Connection con;
Statement sql;
con = GetDBConnection.connectDB("EDUC","root","");
if(con == null ) return;
try {
sql=con.createStatement();
//创建SQL语句执行对象
String strSQL1="update course set cpno='"+updatecpno+"' where cname='"+updatecname+"'";
String strSQL2="update course set cno='"+updatecno+"' where cname='"+updatecname+"'";
String strSQL3="update course set credit='"+updatecredit+"' where cname='"+updatecname+"'";
int rs1=sql.executeUpdate(strSQL1);
int rs2=sql.executeUpdate(strSQL2);
int rs3=sql.executeUpdate(strSQL3);
if(rs1==1&&rs2==1&&rs3==1) {
JOptionPane.showMessageDialog(null,"课程修改成功");
}
else{
JOptionPane.showMessageDialog(null,"课程修改失败");
}
con.close();
//关闭数据库连接
}
catch (SQLException ex) {
System.out.println("数据库连接或者是数据库操作失败");
}
}
}
通过查找新增课程按钮，检查修改课程的信息是否有误：
DeleteCourse.java
import java.awt.Color;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.swing.*;
public class DeleteCourse extends JPanel implements ActionListener{
JLabel cname;
JTextField cnametext;
JButton Delbt;
JTable table;
public DeleteCourse() {
this.setSize(650,350);
this.setLocation(100, 20);
this.setLayout(null);
this.setBackground(Color.WHITE);
cname=new JLabel("请输入需要删除的课程名称");
cname.setSize(200,50);
cname.setLocation(50, 50);
this.add(cname);
cnametext=new JTextField();
cnametext.setSize(160,40);
cnametext.setLocation(220, 50);
this.add(cnametext);
Delbt=new JButton("确认删除");
Delbt.setSize(90,38);
Delbt.setLocation(200, 150);
this.add(Delbt);
Delbt.addActionListener(this);
/*Object[] columnTitle= {"课程号","课程名称","先修课程号","课程学分"};
//表格行对象数据
Object[][] tableData= {
new Object[] {"1","数据库","5","4分"},
new Object[] {"2","数学","null","2分"},
new Object[] {"3","信息系统","1","4分"},
new Object[] {"4","操作系统","6","3分"},
new Object[] {"5","数据结构","7","2分"},
new Object[] {"6","数据处理","null","4分"},
new Object[] {"7","PASCAL语言","6","4分"},
new Object[] {"8","大学英语","null","4分"},
new Object[] {"9","计算机网络","null","4分"},
new Object[] {"10","人工智能","null","2分"},
};
//创建表格
JTable table=new JTable(tableData,columnTitle);
JScrollPane scrollpane=new JScrollPane(table);
scrollpane.setSize(550,250);
scrollpane.setLocation(60, 20);
this.add(scrollpane);
this.setVisible(true);*/
}
@Override
public void actionPerformed(ActionEvent e) {
String delName=cnametext.getText();
Connection con;
Statement sql;
con = GetDBConnection.connectDB("EDUC","root","");
if(con == null ) return;
try {
sql=con.createStatement();
//创建SQL语句执行对象
String strSQL="delete from course where cname='"+delName+"' ";
int rs=sql.executeUpdate(strSQL);
if(rs==1) {
JOptionPane.showMessageDialog(null,"课程删除成功");
}
else{
JOptionPane.showMessageDialog(null,"课程删除失败");
}
con.close();
//关闭数据库连接
}
catch (SQLException ex) {
System.out.println("数据库连接或者是数据库操作失败");
}
}
}
通过点击查找新增课程按钮，检查课程是否删除成功：
FindCourse.java
import java.awt.*;
import javax.swing.event.*;
import java.awt.event.*;
import javax.swing.*;
import java.sql.*;
public class FindCourse extends JFrame{
JTextArea show;
JButton button1,button2;
JComboBox comoBox;
static Statement sql;
static{
try{
Class.forName("com.mysql.jdbc.Driver");
Connection con=GetDBConnection.connectDB("EDUC","root","");
sql=con.createStatement();
}
catch(Exception e){}
}
@SuppressWarnings("unchecked")
public FindCourse()
{
show=new JTextArea(5,10);
button1=new JButton("查询所有课程信息");
button1.addActionListener(new FindAllCourse());
Container container=getContentPane();
container.setLayout(new BorderLayout());
JPanel panel=new JPanel();
JPanel mainpanel=new JPanel();
String items[]={"请选择","数据库","数学","信息系统","操作系统","数据结构","数据处理","PASCAL语言","大学英语","计算机网络","人工智能"};
comoBox=new JComboBox(items);
button2=new JButton("按课程名称查询");
button2.addActionListener(new FindCourseByCname());
panel.add(button2);
panel.add(comoBox);
panel.setVisible(true);
mainpanel.add(panel);
mainpanel.setBackground(Color.orange);
panel=new JPanel();
panel.add(button1);
panel.setBackground(Color.orange);
container.add(mainpanel,BorderLayout.NORTH);
container.add(panel,BorderLayout.SOUTH);
container.add(new JScrollPane(show),BorderLayout.CENTER);
setTitle("查询课程");
setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
setSize(750,400);
Toolkit kit = Toolkit.getDefaultToolkit();
Dimension screenSize = kit.getScreenSize();//获取屏幕尺寸对象
int screenWidth = screenSize.width/2;//水平位置
int screenHeight = screenSize.height/2;//垂直位置
int height = this.getHeight();
int width = this.getWidth();
setLocation(screenWidth-width/2, screenHeight-height/2);//屏幕宽度(高度)减去窗口宽度(高度)，然后在除以2，就是居中了
setVisible(true);
}
class FindAllCourse implements ActionListener
{
public void actionPerformed(ActionEvent e)
{
try
{
String sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc"
+ " where student.sno = sc.sno AND sc.cno = course.cno ";
ResultSet rs=sql.executeQuery(sqlStr);
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
while(rs.next())
{
show.append(rs.getString(1)+" "+"\t");
show.append(rs.getString(2)+" "+"\t");
show.append(rs.getString(3)+" "+"\t");
show.append(rs.getString(4)+" "+"\t");
show.append(rs.getString(5)+" "+"\t");
show.append(rs.getString(6)+" "+"\t");
show.append(rs.getString(7)+"\n");
}
}
catch(Exception ee){}
}
}
class FindCourseByCname implements ActionListener
{
public void actionPerformed(ActionEvent e)
{
try
{
String sqlStr="";
String course=comoBox.getSelectedItem().toString();
if(course.equals("数据库"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc"
+ " where student.sno = sc.sno AND sc.cno = course.cno AND cname='数据库'";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
else if(course.equals("数学"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc "
+ "where student.sno = sc.sno AND sc.cno = course.cno AND cname = '数学'";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
else if(course.equals("信息系统"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc "
+ "where student.sno = sc.sno AND sc.cno = course.cno AND cname = 信息系统' ";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
else if(course.equals("操作系统"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc "
+ "where student.sno = sc.sno AND sc.cno = course.cno AND cname = '操作系统' ";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
else if(course.equals("数据结构"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc "
+ "where student.sno = sc.sno AND sc.cno = course.cno AND cname = '数据结构' ";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
else if(course.equals("数据处理"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc "
+ "where student.sno = sc.sno AND sc.cno = course.cno AND cname = '数据处理' ";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
else if(course.equals("PASCAL语言"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc "
+ "where student.sno = sc.sno AND sc.cno = course.cno AND cname = 'PASCAL语言' ";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
else if(course.equals("大学英语"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc "
+ "where student.sno = sc.sno AND sc.cno = course.cno AND cname = '大学英语' ";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
else if(course.equals("计算机网络"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc "
+ "where student.sno = sc.sno AND sc.cno = course.cno AND cname = '计算机网络' ";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
else if(course.equals("人工智能"))
{
sqlStr="select student.sno,student.sname,course.cno,course.cname,course.cpno,course.credit,sc.grade from student,course,sc "
+ "where student.sno = sc.sno AND sc.cno = course.cno AND cname = '人工智能' ";
show.setText("");
show.append("学号\t姓名\t课程号\t课程名\t先修课程号\t课程学分\t成绩"+"\n");
}
ResultSet rs=sql.executeQuery(sqlStr);
while(rs.next())
{
show.append(rs.getString(1)+" "+"\t");
show.append(rs.getString(2)+" "+"\t");
show.append(rs.getString(3)+" "+"\t");
show.append(rs.getString(4)+" "+"\t");
show.append(rs.getString(5)+" "+"\t");
show.append(rs.getString(6)+" "+"\t");
show.append(rs.getString(7)+"\n ");
}
}
catch(Exception ee){}
}
}
}
按课程名称查询：
查询所有课程信息：
数据库文件
create table student
(
sno char(9) primary key,
sname char(8) not null,
ssex char(2),
sage int,
sdept char(8)
)
insert into student values('200515001', '赵菁菁', '女', 23, 'CS');
insert into student values('200515002', '李勇', '男', 20, 'CS');
insert into student values('200515003', '张力', '男', 19, 'CS');
insert into student values('200515004', '张衡', '男', 18, 'IS');
insert into student values('200515005', '张向东', '男', 20, 'IS');
insert into student values('200515006', '张向丽', '女', 20, 'IS');
insert into student values('200515007', '王芳', '女', 20, 'CS');
insert into student values('200515008', '王民生', '男', 25, 'MA');
insert into student values('200515009', '王小民', '女', 18, 'MA');
insert into student values('200515010', '李晨', '女', 22, 'MA');
insert into student values('200515011', '张毅', '男', 20, 'WM');
insert into student values('200515012', '杨磊', '女', 20, 'EN');
insert into student values('200515013', '李晨', '女', 19, 'MA');
insert into student values('200515014', '张丰毅', '男', 22, 'CS');
insert into student values('200515015', '李蕾', '女', 21, 'EN');
insert into student values('200515016', '刘社', '男', 21, 'CM');
insert into student values('200515017', '刘星耀', '男', 18, 'CM');
insert into student values('200515018', '李贵', '男', 19, 'EN');
insert into student values('200515019', '林自许', '男', 20, 'WM');
insert into student values('200515020', '马翔', '男', 21, '');
insert into student values('200515021', '刘峰', '男', 25, 'CS');
insert into student values('200515022', '牛站强', '男', 22, '');
insert into student values('200515023', '李婷婷', '女', 18, '');
insert into student values('200515024', '严丽', '女', 20, '');
insert into student values('200515025', '朱小鸥', '女', 30, 'WM');
create table course
(
cno int primary key,
cname nchar(10) not null,
cpno int,
credit int
);
insert into course values(1, '数据库', 5, 4);
insert into course values(2, '数学', null ,2);
insert into course values(3, '信息系统', 1, 4);
insert into course values(4, '操作系统', 6, 3);
insert into course values(5, '数据结构', 7, 4);
insert into course values(6, '数据处理', null ,2);
insert into course values(7, 'PASCAL语言', 6, 4);
insert into course values(8, '大学英语', null ,4);
insert into course values(9, '计算机网络', null ,4);
insert into course values(10, '人工智能', null ,2);
create table sc
(
sno nchar(9),
cno int,
grade int,
primary key(sno,cno)
);
insert into sc values('200515001', 1, 75);
insert into sc values('200515002', 1, 85);
insert into sc values('200515002', 3, 53);
insert into sc values('200515003', 1, 86);
insert into sc values('200515004', 1, 74);
insert into sc values('200515005', 1, 58);
insert into sc values('200515006', 1, 84);
insert into sc values('200515004', 2, 46);
insert into sc values('200515005', 2, 89);
insert into sc values('200515006', 2, 65);
insert into sc values('200515008', 2, 72);
insert into sc values('200515009', 2, 76);
insert into sc values('200515010', 2, 96);
insert into sc values('200515010', 8, 86);
insert into sc values('200515011', 8, 62);
insert into sc values('200515015', 8, 0);
insert into sc values('200515018', 8, 58);
insert into sc values('200515001', 4, 62);
insert into sc values('200515002', 4, 85);
insert into sc values('200515021', 9, 54);
insert into sc values('200515001', 5, 58);
insert into sc values('200515021', 6, 58);
insert into sc values('200515001', 7, 70);
insert into sc values('200515005', 10, 65);
insert into sc values('200515016', 8, Null);
insert into sc values('200515017', 8, Null);
版权声明：本文为weixin_44711037原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。