mysql有数据库作业吗_数据库MySQL（课下作业）

作业要求

下载附件中的world.sql.zip, 参考http://www.cnblogs.com/rocedu/p/6371315.html#SECDB，导入world.sql,提交导入成功截图

编写程序，查询世界上超过“你学号前边七位并把最后一位家到最高位，最高位为0时置1”(比如学号20165201，超过3016520；学号20165208，超过1016520)的所有城市列表，提交运行结果截图

编写程序，查询世界上的所有中东国家的总人口

编写程序，查询世界上的平均寿命最长和最短的国家

具体步骤

1.下载world.sql.zip解压在localhost导入world.sql。

2.查询世界上超过5017530(我的学号为20175303，前七位为2017530，把最后一位3加到最前面，为5017530)的所有城市列表

代码为：

import java.sql.*;

/**

*InquiryCityList

*

* @author 20175303cxd

* @date 2019/5/4

*/

public class InquiryCityList {

public static void main(String[] args) throws SQLException {

Connection con;

Statement sql;

ResultSet rs;

String url = "jdbc:mysql://localhost:3306/world";

String user = "root";

String passwd = "";

con = DriverManager.getConnection(url, user, passwd);

if (con == null) {

return;

}

String sqlStr = "select*from city where population>5017530";

try {

sql = con.createStatement();

rs = sql.executeQuery(sqlStr);

while (rs.next()) {

int id = rs.getInt(1);

String name = rs.getString(2);

String countryCode = rs.getString(3);

String district = rs.getString(4);

int population = rs.getInt(5);

System.out.printf("%d\t", id);

System.out.printf("%s\t", name);

System.out.printf("%s\t", countryCode);

System.out.printf("%s\t", district);

System.out.printf("%d\n", population);

}

con.close();

} catch (SQLException e) {

System.out.println("Error:" + e);

}

}

}

3.查询世界上的所有中东国家的总人口

代码为：

import java.sql.*;

/**

*InquiryTotalPopulation

*

* @author 20175303cxd

* @date 2019/5/5

*/

public class InquiryTotalPopulation {

public static void main(String[] args) throws SQLException {

Connection con;

Statement sql;

ResultSet rs;

String url = "jdbc:mysql://localhost:3306/world";

String user = "root";

String passwd = "";

con = DriverManager.getConnection(url, user,passwd);

if (con == null) {

return;

}

String sqlStr = "select Name,Population from country where Region = 'Middle East'";

try {

sql = con.createStatement();

rs = sql.executeQuery(sqlStr);

int total = 0;

while (rs.next()) {

String name = rs.getString(1);

int population = rs.getInt(2);

System.out.printf("%s的人口为%d\n", name, population);

total += population;

}

System.out.println("中东国家的总人口为：" + total);

} catch (SQLException e) {

System.out.println("Error:" + e);

}

}

}

4.查询世界上的平均寿命最长和最短的国家

代码为：

import java.sql.*;

/**

* InquiryCountry

*

* @author 20175303cxd

* @date 2019/5/5

*/

public class InquiryCountry {

public static void main(String[] args) throws SQLException {

Connection con;

Statement sql;

ResultSet rs;

String url = "jdbc:mysql://localhost:3306/world";

String user = "root";

String passwd = "";

con = DriverManager.getConnection(url, user,passwd);

if (con == null) {

return;

}

String sqlStr = "select Name,LifeExpectancy from country order by LifeExpectancy";

try {

sql = con.createStatement();

rs = sql.executeQuery(sqlStr);

while (rs.next()) {

float life = rs.getInt(2);

String name = rs.getString(1);

rs.first();

while (life == 0) {

rs.next();

life = rs.getInt(2);

}

name = rs.getString(1);

System.out.println("世界上平均寿命最短的国家为：" + name);

rs.last();

name = rs.getString(1);

System.out.println("世界上平均寿命最长的国家为：" + name);

}

} catch (SQLException e) {

System.out.println("Error:" + e);

}

}

}