SQL：

select ts_shuliang,jnum from tushu t join jieyue j on t.ts_id=j.ts_id where j.ts_id='1'

j.ts_id='1'和j.ts_id=1等價

select * from jieyue a inner join yonghu b on a.yh_id=b.yh_id inner join tushu c on a.ts_id=c.ts_id where yh_uname='1';

select ts_shuliang,jnum from tushu t join jieyue j on t.ts_id=j.ts_id where j.ts_id='1'

j.ts_id='1'和j.ts_id=1等價

select *from yonghu

drop table yonghu

create table yonghu(

? yh_ID int primary key identity(1,1),

? yh_Uname varchar(30) not null,

? yh_Pwd varchar(30) not null,

? yh_Name varchar(30) not null,

? yh_Age int,

? yh_Sex varchar(20),

? yh_Phone varchar(100),

? yh_Address varchar(200),

? yh_chubanshe varchar(500),

? ?yh_Q varchar(500),

? ? yh_A varchar(500),

yh_type nvarchar(20) check(yh_type='普通用戶' or yh_type='管理員用戶'),

);

select * from yh_type

drop table yh_type

create table yh_Type(

yh_typeid int primary key identity(1,1),

yh_typename varchar(100) not null

);

insert into? yh_Type values('普通用戶');

insert into? yh_Type values('管理員用戶');

insert into yonghu values('1','11','詩書畫唱1',21,'男','19999999999','江西','陽光帥氣的男孩子','你愛好多嗎？','多','普通用戶');

insert into yonghu values('2','22','詩書畫唱2',22,'男','29999999999','北京','陽光帥氣的男孩子','你看過的書多嗎？','多','普通用戶');

insert into yonghu values('3','33','詩書畫唱3',23,'男','39999999999','上海','陽光帥氣的男孩子','你會的技能多嗎？','多','普通用戶');

insert into yonghu values('4','44','詩書畫唱4',24,'男','49999999999','湖南','陽光帥氣的男孩子','你喜歡的動漫嗎？','多','普通用戶');

insert into yonghu values('5','55','詩書畫唱5',25,'男','59999999999','浙江','陽光帥氣的男孩子','你喜歡的歌曲多嗎？','多','管理員用戶');

drop table ts_Type

create table ts_Type(

ts_leixingid int primary key identity(1,1),

ts_leixing varchar(100) not null

);

insert into ts_Type values('奇幻');

insert into ts_Type values('愛情');

insert into ts_Type values('日常');

insert into ts_Type values('科普');

drop table tushu

create table tushu(

? ts_ID int primary key identity(1,1),

? ts_Name varchar(100) not null,

? ts_shuliang? int,

--ts_leixing varchar(100) not null,

? ts_chubanshe varchar(300),

? ts_leixingid int

);

--insert into tushu values('《奧秘》',12,'奇幻','人民出版社',1);

--insert into tushu values('《愛你》',2,'愛情','人民出版社',2);

--insert into tushu values('《詩書》',4,'日常','人民出版社',3);

--insert into tushu values('《科普》',3,'科普','人民出版社',4);

insert into tushu values('《奧秘》',12,'人民出版社',1);

insert into tushu values('《愛你》',2,'人民出版社',2);

insert into tushu values('《詩書》',4,'人民出版社',3);

insert into tushu values('《科普》',3,'人民出版社',4);

drop table yonghu, tushu,dingdanxiangqing,ts_Type

drop table tushu

select * from yonghu

select * from yonghu where yh_name='1' and yh_pwd='1' and yh_type='管理員用戶'

select * from tushu

select * from ts_Type

select * from dingdan,dingdanxiangqing

select * from dingdanxiangqing

drop table dingdan

create table dingdan(

? dingdan_ID int primary key identity(1,1),

? yh_ID int,

? dingdanxiangqing_ID int

)

insert into dingdan values(1,1);

insert into dingdan values(1,2);

insert into dingdan values(1,3);

insert into dingdan values(2,4);

insert into dingdan values(2,5);

insert into dingdan values(2,6);

insert into dingdan values(3,7);

create table dingdanxiangqing(

? dingdanxiangqing_ID int primary key identity(1,1),

? sp_ID int,

? sp_num int

)

insert into dingdanxiangqing values(1,3);

insert into dingdanxiangqing values(2,4);

insert into dingdanxiangqing values(1,5);

insert into dingdanxiangqing values(3,5);

insert into dingdanxiangqing values(4,2);

insert into dingdanxiangqing values(5,1);

insert into dingdanxiangqing values(3,3);

insert into dingdanxiangqing values(4,3);

select * from yonghu;

select * from shangpin;

select * from sp_Type;

drop table jieyue

create table jieyue(

jid int primary key identity(1,1),

jnum int,

jday int,

yh_id int,

ts_id int

--ALTER TABLE 表名1 add constraint 自己取的約束名 foreign key（字段) references 表名2（字段）?

)

insert into jieyue values(3,3,1,1)

insert into jieyue values(2,2,2,2)

insert into jieyue values(4,5,3,3)

insert into jieyue values(5,1,1,4)

--alter table tushu add constraint tushuAndts_type foreign key(yh_leixing) references typetushu (ts_leixing)

--alter table jieyue add constraint jieyueAndyonghu foreign key (yh_id) references users(yh_id)

--alter table jieyue add constraint jieyueAndtushu foreign key(tid) references tushu

select * from tushu a inner join ts_type b on a.ts_leixingid=b.ts_leixingid

select * from ts_type

select * from jieyue a inner join yonghu b on a.yh_id=b.yh_id inner join tushu c on a.ts_id=c.ts_id

select * from jieyue a inner join yonghu b on a.yh_id=b.yh_id inner join tushu c on a.ts_id=c.ts_id

update? yonghu set yh_type='管理員用戶' where yh_id<4

Java：

1：

package denglu;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import javax.swing.JButton;

import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JOptionPane;

import javax.swing.JPasswordField;

public class chongzhimimajiemian extends JFrame {

static JButton JButton_QuRen = null;

static JLabel JLabel1, JLabel2 = null;

static JPasswordField JPasswordField_qslmm1, JPasswordField_qslmm2 = null;

public chongzhimimajiemian() {

this.setLayout(null);

this.setTitle("重置密碼頁面");

this.setSize(500, 500);

this.setLocationRelativeTo(null);

this.setVisible(true);

JLabel1 = new JLabel("請輸入密碼");

JLabel2 = new JLabel("請再次輸入密碼");

JLabel1.setBounds(100, 100, 100, 30);

this.add(JLabel1);

JLabel2 = new JLabel("請再次輸入密碼");

JLabel2.setBounds(100, 140, 100, 30);

this.add(JLabel2);

JButton_QuRen = new JButton("確認修改");

JButton_QuRen.setBounds(100, 290, 150, 30);

this.add(JButton_QuRen);

JButton_QuRen.addActionListener(new shijian_queRenXiuGai(this));

JPasswordField_qslmm1 = new JPasswordField();

JPasswordField_qslmm2 = new JPasswordField();

JPasswordField_qslmm1.setBounds(280, 100, 100, 30);

JPasswordField_qslmm2.setBounds(280, 140, 100, 30);

this.add(JPasswordField_qslmm1);

this.add(JPasswordField_qslmm2);

}

}

class shijian_queRenXiuGai implements ActionListener {

static chongzhimimajiemian chuangKou = null;

public shijian_queRenXiuGai(chongzhimimajiemian chuanzhi) {

this.chuangKou = chuanzhi;

}

@Override

public void actionPerformed(ActionEvent arg0) {

if (arg0.getActionCommand().equals("確認修改")) {

JOptionPane.showMessageDialog(null, "點擊了修改密碼按鈕");

String pwd1 = chuangKou.JPasswordField_qslmm1.getText();

String pwd2 = chuangKou.JPasswordField_qslmm2.getText();

if (pwd1.equals(pwd2)) {

String uname = gongjvClass.uname;

String sql = "update yonghu set yh_pwd='" + pwd1

+ "' where yh_uname= '" + uname + "'";

if (DBUtils.ZSG(sql)) {

JOptionPane.showMessageDialog(null, "密碼重置成功");

} else {

JOptionPane.showMessageDialog(null, "出現(xiàn)了未知的錯誤，請重試!");

}

} else {

JOptionPane.showMessageDialog(null, "兩次密碼輸入不一致");

return;

}

}

}

}

2：

package denglu;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

public class DBUtils {

private static Connection con = null;

private static ResultSet res = null;

private static Statement sta = null;

static {

try {

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

} catch (ClassNotFoundException e) {

e.printStackTrace();

}

}

public static Connection getCon() {

if (con == null) {

try {

con = DriverManager.getConnection(

"jdbc:sqlserver://DESKTOP-49FTFSP;"

+ "databaseName=yonghu", "sa", "1234abcd");

} catch (SQLException e) {

e.printStackTrace();

}

}

return con;

}

public static ResultSet Select(String sql) {

con = getCon();

try {

sta = con.createStatement();

res = sta.executeQuery(sql);

} catch (SQLException e) {

e.printStackTrace();

}

return res;

}

public static boolean ZSG(String sql) {

boolean b = false;

con = getCon();

try {

sta = con.createStatement();

int num = sta.executeUpdate(sql);

if (num > 0) {

b = true;

}

} catch (SQLException e) {

e.printStackTrace();

}

return b;

}

}