数据库内容
java源代码 （代码实现的是查询成绩为 的人员信息 至于其他功能的 代码中有注释） 注意 在eclipse里运行程序的时候 要工程里插入jar包 否则运行异常！ import java sql *import java io *class database_manage { public Connection conn = null public ResultSet rs = null private String DatabaseDriver = " mysql jdbc Driver" // DataSource 数据源名称DSN private String DatabaseConnStr = "jdbc:mysql://localhost: /people_manage?useUnicode=true&characterEncoding=utf " + " root root" public void setDatabaseDriver(String Driver) { this DatabaseDriver = Driver } public String getDatabaseDriver() { return (this DatabaseDriver) } public void setDatabaseConnStr(String ConnStr) { this DatabaseConnStr = ConnStr } public String getDatabaseConnStr() { return (this DatabaseConnStr) } public database_manage() {// 构造函数连接数据库 try { Class forName(DatabaseDriver)} catch (java lang ClassNotFoundException e) { System err println("加载驱动器有错误:" + e getMessage()) System out print("执行插入有错误:" + e getMessage())// 输出到客户端 } } public ResultSet query(String sql) {// 查询数据库 rs = nulltry { conn = DriverManager getConnection( "jdbc:mysql://localhost: /people_manage?useUnicode=true&characterEncoding=utf " "root" "root") Statement stmt = conn createStatement() rs = stmt executeQuery(sql)} catch (SQLException ex) { System err println("执行查询有错误:" + ex getMessage()) System out print("执行查询有错误:" + ex getMessage())// 输出到客户端 }return rs
} public int update_database(String sql) {// 更新或插入数据库 int num = try { conn = DriverManager getConnection( "jdbc:mysql://localhost: /people_manage?useUnicode=true&characterEncoding=utf " "root" "root") Statement stmt = conn createStatement() num = stmt executeUpdate(sql)} catch (SQLException ex) { System err println("执行插入有错误:" + ex getMessage()) System out print("执行插入有错误:" + ex getMessage())// 输出到客户端 } CloseDataBase()return num } public void CloseDataBase() {// 关闭数据库 try { conn close()} catch (Exception end) { System err println("执行关闭Connection对象有错误 " + end getMessage()) System out print("执行执行关闭Connection对象有错误 有错误:" + end getMessage())// 输出到客户端 } } } class people { private String uid private String name private String banji private int score public people() { } public people(String uid String name String banji) { this uid = uidthis name = namethis banji = banji } public people(String uid String name String banji int score) { this uid = uidthis name = namethis banji = banjithis score = score } public String getUid() { return uid } public void setUid(String uid) { this uid = uid } public String getName() { return name } public void setName(String name) { this name = name } public String getBanji() { return banji } public void setBanji(String banji) { this banji = banji } public int getScore() { return score } public void setScore(int score) { this score = score } } public class manage { private people[] people_array // 对象数组 public void add_people(String uid String name) { String sql = "insert people (uid name) values ( " + uid + " " + name + " )"// sql插入语句 // String sql = "insert people (uid name) values ( uid tom )" database_manage db_obj = new database_manage()db_obj update_database(sql) } public void update_people(String uid String name) { String sql = "update people set name= " + name + " where uid= " + uid + " "database_manage db_obj = new database_manage()db_obj update_database(sql) } public void delete_people(String uid) { String sql = "delete from people where uid= " + uid + " " database_manage db_obj = new database_manage() db_obj update_database(sql) } public people query_people(String uid) { database_manage db_obj = new database_manage()// String adminid=nullString uid_new name banjiuid_new = nullname = nullbanji = nullString sql_query = "select * from people where uid= " + uid + " " try { ResultSet rs = db_obj query(sql_query) if (rs next()) { uid_new = rs getString("uid")name = rs getString("name")banji = rs getString("banji") } } catch (Exception e) { e getMessage()} people new_people = new people(uid_new name banji)return new_people } public people[] query_people_byscore(int score) { database_manage db_obj = new database_manage()String uid_new name banjiuid_new = nullname = nullbanji = nullint score_new =String sql_query = "select * from people where score=" + score// sql查询语句
try {
ResultSet rs = db_obj query(sql_query)// 查询后 返回结果集
int num =
ResultSet rs_new = rs
while (rs_new next()) {// 统计结果集中学生个数
num++
}
// System out println(num)
people_array = new people[num]
int i =
rs beforeFirst()// 返回结果集的开始
while (rs next()) {
uid_new = rs getString("uid")
name = rs getString("name")
banji = rs getString("banji")
score_new = rs getInt("score")
people_array [i] = new people(uid_new name banji score_new)
i++
}
} catch (Exception e) {
e getMessage()
}
return people_array
}
public static void main(String args[]) {
/*
* people new_people=new people()
*
* manage mr=new manage()//mr add_people(" " "小明")插入一个学生的信息
*
* new_people=mr query_people(" ")//查询uid= 的学生信息 返回对象 System out
* println(""+new_people getName()+" "+new_people getBanji())
* mr update_people(" " "小王")更新一个学生的信息
*
* new_people=mr query_people(" ")//更新后查询
*
* System out println(""+new_people getName()+" "+new_people getBanji(
* ))
*/
manage mr = new manage()
// mr delete_people(" ")删除uid= 的学生信息
people[] people_array// 声明对象数组
people_array = mr query_people_byscore( )// 返回成绩为一百的学生类数组 后输出
int num =
num = people_array length
for (int i = i <numi++) {
System out println(people_array[i] getUid() + " "
+ people_array[i] getName() + " "
+ people_array[i] getBanji() + " "
+ people_array[i] getScore())
}
}
}
lishixinzhi/Article/program/Java/JSP/201311/20030
其实我也没有啥好办法，我甚至推荐你使用楼上说的方法，直接select * 将数据全部查出后，在service中用java处理数据更加方便。
如果要强行使用sql查出来，我这里写了这么一段：
首先因为是同一张表，根据不同条件将结果拼接在一起，我能想到的就是用left join，我按照不同条件将数据分成如下几段：
a段：姓名段，作为left join的主表，只有姓名；
b段：吃了早餐段；
c段：没吃早餐段；
d段：吃了晚餐段；
e段：没吃晚餐段；
f段：吃了饭段；
g段：没吃饭段。
除了a段以外，其他段都是根据自身条件
SELECT
name,
count(*) count,
sum(weight) sum，
然后依次左连接将所有段通过姓名连接到一起，最终组成的sql语句如下：
SELECT
a.name 姓名,
ifnull(b.count, 0) 吃了早餐的次数,
ifnull(b.sum, 0) 吃了早餐的重量,
ifnull(c.count, 0) 没吃早餐的次数,
ifnull(c.sum, 0) 没吃早餐的重量,
ifnull(d.count, 0) 吃了晚餐的次数,
ifnull(d.sum, 0) 吃了晚餐的重量,
ifnull(e.count, 0) 没吃晚餐的次数,
ifnull(e.sum, 0) 没吃晚餐的重量,
ifnull(f.count, 0) 吃了饭的次数,
ifnull(f.sum, 0) 吃了饭的重量,
ifnull(g.count, 0) 没吃饭的次数,
ifnull(g.sum, 0) 没吃饭的重量
FROM
(SELECT DISTINCT name
FROM T) a LEFT JOIN
(SELECT
name,
count(*) count,
sum(weight) sum
FROM T
WHERE type = 0 AND status = 0
GROUP BY name) b ON a.name = b.name
LEFT JOIN
(SELECT
name,
count(*) count,
sum(weight) sum
FROM T
WHERE type = 0 AND status = 1
GROUP BY name) c ON a.name = c.name
LEFT JOIN
(SELECT
name,
count(*) count,
sum(weight) sum
FROM T
WHERE type = 1 AND status = 0
GROUP BY name) d ON a.name = d.name
LEFT JOIN
(SELECT
name,
count(*) count,
sum(weight) sum
FROM T
WHERE type = 1 AND status = 1
GROUP BY name) e ON a.name = e.name
LEFT JOIN
(SELECT
name,
count(*) count,
sum(weight) sum
FROM T
WHERE status = 0
GROUP BY name) f ON a.name = f.name
LEFT JOIN
(SELECT
name,
count(*) count,
sum(weight) sum
FROM T
WHERE status = 1
GROUP BY name) g ON a.name = g.name
T表结构为：
（因为以name字段进行连接，type和status作为条件，建议以该三个字段作为索引）
T表测试数据为：
运行结果为：
你好，很高兴回答你的问题。Mysql的delete语句可以用limit来限制删除的条数。
类似这样:
delete from 表名 where 条件 limit 条数。
如果有帮助到你，请点击采纳。
