我自己總結(jié)兩表查詢的語法：select * from 表名1 a inner join 表名2 b on a.兩表共有的列名=b.兩表共有的列名

create table ptype1(

p_typeid int primary key identity(1,1),

p_typename nvarchar(20) not null

)

insert into ptype1 values('管理員')

insert into ptype1 values('普通用戶')

insert into ptype1 values('會(huì)員用戶')

create table person1(

p_id int primary key identity(1,1),

p_name nvarchar(50) not null,

p_sex nvarchar(30) check(p_sex='男' or p_sex='女'),

p_typeid int

foreign key(p_typeid) references ptype1(p_typeid)

)

insert into person1 values('張三','男',1)

insert into person1 values('李四','男',3)

insert into person1 values('王五','女',2)

insert into person1 values('馬六','男',2)

insert into person1 values('張大飛','男',1)

insert into person1 values('張大壯','女',3)

select * from ptype1

select * from person1

select * from person1 a inner join ptype1 b on a.p_typeid=b.p_typeid where 1=1;

drop table ptype1

drop table person1

package yongHuZSGC;

import java.sql.*;

public class DBUtils {

static Connection con=null;

static Statement sta=null;

static ResultSet res=null;

//在靜態(tài)代碼塊中執(zhí)行

static{

try {

Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");

} catch (ClassNotFoundException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

//封裝鏈接數(shù)據(jù)庫的方法

public static Connection getCon(){

if(con==null){

try {

con=DriverManager.getConnection

("jdbc:sqlserver://localhost;databaseName=yonghu","qqq","123");

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

}

return con;

}

//查詢的方法

public static ResultSet Select(String sql){

con=getCon();//建立數(shù)據(jù)庫鏈接

try {

sta=con.createStatement();

res=sta.executeQuery(sql);

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return res;

}

//增刪改查的方法

// 返回int類型的數(shù)據(jù)

public static boolean ZSG(String sql){

con=getCon();//建立數(shù)據(jù)庫鏈接

boolean b=false;

try {

sta=con.createStatement();

int num=sta.executeUpdate(sql);

//0就是沒有執(zhí)行成功，大于0 就成功了

if(num>0){

b=true;

}

} catch (SQLException e) {

// TODO Auto-generated catch block

e.printStackTrace();

}

return b;

}

}

package yongHuZSGC;

public class mains {

public static void main(String[] args) {

new ZSGC();

}

}

package yongHuZSGC;

import java.awt.Color;

import java.awt.event.*;

import java.sql.*;

import java.util.*;

import javax.swing.*;

import javax.swing.table.DefaultTableModel;

class shiJian implements MouseListener, ActionListener {

public ZSGC jieShouchuangTi = null;

public shiJian(ZSGC chuangTi) {

this.jieShouchuangTi = chuangTi;

}

@Override

public void actionPerformed(ActionEvent arg0) {

String huoQuDaoDeWenZi = arg0.getActionCommand();

if (huoQuDaoDeWenZi.equals("查詢")) {

String pid = jieShouchuangTi.txt_pid.getText();

String pName = jieShouchuangTi.txt_pname.getText();

jieShouchuangTi.tushu_load(pid, pName);

} else if (huoQuDaoDeWenZi.equals("增加人員")) {

String pName = jieShouchuangTi.txt_pName.getText();

int pTypeId = jieShouchuangTi.arr_type.get(jieShouchuangTi.com_type.getSelectedIndex());

String pSex = "男";

if (jieShouchuangTi.rb2.isSelected()) {

pSex= "女";

}

String sql = "insert into person1(p_name,p_typeid,"

+ "p_sex) " + "values('" + pName + "',"

+ pTypeId + ",'" + pSex + "')";

if (DBUtils.ZSG(sql)) {

jieShouchuangTi.tushu_load(null, null);

JOptionPane.showMessageDialog(null, "人員增加成功!");

} else {

JOptionPane.showMessageDialog(null, "增加失敗，請(qǐng)重試");

}

} else if (huoQuDaoDeWenZi.equals("修改人員")) {

String pid = jieShouchuangTi.txt_Pid.getText();

String pName = jieShouchuangTi.txt_pName.getText();

int pTypeNameId = jieShouchuangTi.arr_type

.get(jieShouchuangTi.com_type.getSelectedIndex());

String pSex = "男";

if (jieShouchuangTi.rb2.isSelected()) {

pSex= "女";

}

String sql = "update person1 set? p_name='" + pName

+ "',p_typeid=" + pTypeNameId

+ ",p_sex='" + pSex + "' where p_id='" + pid

+ "'";

if (DBUtils.ZSG(sql)) {

jieShouchuangTi.tushu_load(null, null);

JOptionPane.showMessageDialog(null, "修改成功!");

} else {

JOptionPane.showMessageDialog(null, "修改失敗，請(qǐng)重試");

}

} else if (huoQuDaoDeWenZi.equals("清空")) {

jieShouchuangTi.txt_Pid.setText("");

jieShouchuangTi.txt_pName.setText("");

String pSex = "男";

if (jieShouchuangTi.rb2.isSelected()) {

pSex= "女";

}

jieShouchuangTi.com_type.setSelectedIndex(1);

}

}

@Override

public void mouseClicked(MouseEvent arg0) {

if (arg0.getClickCount() == 2) {

int row = jieShouchuangTi.jt1.getSelectedRow();

jieShouchuangTi.txt_Pid.setText(jieShouchuangTi.jt1.getValueAt(row, 0).toString());

jieShouchuangTi.txt_pName.setText(jieShouchuangTi.jt1.getValueAt(row, 1).toString());

if (jieShouchuangTi.jt1.getValueAt(row, 2).equals("男")) {

jieShouchuangTi.rb1.setSelected(true);

}

else if(jieShouchuangTi.jt1.getValueAt(row, 2).equals("女")){

jieShouchuangTi.rb2.setSelected(true);

}

String type = jieShouchuangTi.jt1.getValueAt(row, 3).toString();

jieShouchuangTi.com_type.setSelectedItem(type);

} else if (arg0.isMetaDown()) {

int num = JOptionPane.showConfirmDialog(null, "確定要?jiǎng)h除這條信息嗎?");

if (num == 0) {

int row = jieShouchuangTi.jt1.getSelectedRow();

String pId = jieShouchuangTi.jt1.getValueAt(row, 0).toString();

String sql = "delete person1 where p_id='" + pId + "'";

if (DBUtils.ZSG(sql)) {

jieShouchuangTi.tushu_load(null, null);

JOptionPane.showMessageDialog(null, "人員刪除成功!");

} else {

JOptionPane.showMessageDialog(null, "刪除失敗，請(qǐng)重試");

}

}

}

}

@Override

public void mouseEntered(MouseEvent arg0) {

}

@Override

public void mouseExited(MouseEvent arg0) {

}

@Override

public void mousePressed(MouseEvent arg0) {

}

@Override

public void mouseReleased(MouseEvent arg0) {

}

}

public class ZSGC extends JFrame {

public static ArrayList<Integer> arr_type = null;

public static JButton btn_select, btn_insert, btn_update,

btn_qingkong = null;

public static JComboBox com_type = null;

public static DefaultTableModel dtm = null;

public static JPanel jp1, jp2, jp3 = null;

public static JTable jt1 = null;

public static JLabel lbPid, lbPname = null;

public static JLabel lbPId, lbPName,? lb_Ptypename,

lb_pSex = null;

public static JTextField txt_pid, txt_pname = null;

public static JTextField txt_Pid, txt_pName

= null;

public static JRadioButton rb1,rb2;?

public ZSGC() {

this.setTitle("人員管理");

this.setLayout(null);

this.setSize(710, 550);

this.setLocationRelativeTo(null);

jp2 = new JPanel();

jp2.setBorder(BorderFactory.createLineBorder(Color.gray));

jp2.setLayout(null);

jp2.setBounds(470, 10, 200, 150);

lbPid = new JLabel("人員編號(hào):");

lbPname = new JLabel("人員姓名:");

lbPid.setBounds(10, 10, 70, 30);

lbPname.setBounds(10, 50, 70, 30);

txt_pid = new JTextField();

txt_pname = new JTextField();

txt_pid.setBounds(80, 10, 100, 30);

txt_pname.setBounds(85, 50, 100, 30);

btn_select = new JButton("查詢");

btn_select.setBounds(30, 90, 100, 30);

jp2.add(lbPid);

jp2.add(lbPname);

jp2.add(txt_pid);

jp2.add(txt_pname);

jp2.add(btn_select);

jp3 = new JPanel();

jp3.setBorder(BorderFactory.createLineBorder(Color.gray));

jp3.setLayout(null);

jp3.setBounds(470, 170, 200, 300);

lbPId = new JLabel("人員編號(hào):");

lbPName = new JLabel("人員姓名:");

lb_Ptypename = new JLabel("人員類型:");

lb_pSex = new JLabel("人員性別:");

lbPId.setBounds(10, 10, 70, 30);

lbPName.setBounds(10, 50, 70, 30);

lb_Ptypename.setBounds(10, 130, 70, 30);

lb_pSex.setBounds(10, 170, 70, 30);

jp3.add(lbPId);

jp3.add(lbPName);

jp3.add(lb_Ptypename);

jp3.add(lb_pSex);

txt_Pid = new JTextField();

txt_Pid.setEditable(false);

txt_pName = new JTextField();

rb1 = new JRadioButton("男", true);

rb2 = new JRadioButton("女");

jp3.add(rb1);

jp3.add(rb2);

ButtonGroup bg = new ButtonGroup();

bg.add(rb1);

bg.add(rb2);

rb1.setBounds(80, 170, 50, 30);

rb2.setBounds(140, 170, 50, 30);

txt_Pid.setBounds(80, 10, 100, 30);

txt_pName.setBounds(80, 50, 100, 30);

com_type = new JComboBox();

String sql = "select * from ptype1";

ResultSet res_type = DBUtils.Select(sql);

arr_type = new ArrayList<Integer>();

try {

while (res_type.next()) {

com_type.addItem(res_type.getObject(2));

arr_type.add(res_type.getInt(1));

}

} catch (SQLException e) {

e.printStackTrace();

}

com_type.setBounds(80, 130, 100, 30);

btn_insert = new JButton("增加人員");

btn_insert.setBounds(10, 210, 80, 30);

btn_update = new JButton("修改人員");

btn_update.setBounds(95, 210, 80, 30);

btn_qingkong = new JButton("清空");

btn_qingkong.setBounds(10, 250, 160, 30);

btn_select.addActionListener(new shiJian(this));

btn_insert.addActionListener(new shiJian(this));

btn_update.addActionListener(new shiJian(this));

btn_qingkong.addActionListener(new shiJian(this));

jp3.add(txt_Pid);

jp3.add(txt_pName);

jp3.add(com_type);

jp3.add(btn_insert);

jp3.add(btn_update);

jp3.add(btn_qingkong);

this.add(jp2);

this.add(jp3);

this.setVisible(true);

tushu_load(null, null);

}

public void tushu_load(String tid, String tname) {

if (jp1 != null) {

this.remove(jp1);

}

String sql = "select * from person1 a inner join ptype1 b on a.p_typeid=b.p_typeid where 1=1";

if (tid != null && tid.length() > 0) {

sql += " and p_id='" + tid + "'";

}

if (tname != null && tname.length() > 0) {

sql += " and p_name like'%" + tname + "%'";

}

Vector<Object> v_head = new Vector<Object>();

v_head.add("人員編號(hào)");

v_head.add("人員姓名");

v_head.add("人員性別");

v_head.add("人員類型");

Vector<Vector<Object>> v_body = new Vector<Vector<Object>>();

ResultSet res = DBUtils.Select(sql);

try {

while (res.next()) {

Vector<Object> v = new Vector<Object>();

v.add(res.getInt("p_id"));

v.add(res.getString("P_name"));

v.add(res.getString("P_sex"));

v.add(res.getString("P_typename"));

v_body.add(v);

}

} catch (SQLException e) {

e.printStackTrace();

}

dtm = new DefaultTableModel(v_body, v_head) {

@Override

public boolean isCellEditable(int a, int b) {

return false;

}

};

jt1 = new JTable(dtm);

jt1.addMouseListener(new shiJian(this));

JScrollPane jsp = new JScrollPane(jt1);

jsp.setBounds(0, 0, 450, 500);

jp1 = new JPanel();

jp1.setLayout(null);

jp1.setBounds(10, 10, 450, 500);

jp1.add(jsp);

this.add(jp1);

}

}