作业要求：

1.下载附件中的world.sql.zip, 参考http://www.cnblogs.com/rocedu/p/6371315.html#SECDB，导入world.sql,提交导入成功截图2.编写程序，查询世界上超过“你学号前边七位并把最后一位家到最高位，最高位为0时置1”（比如学号20165201，超过3016520；学号20165208，超过1016520）的所有城市列表，提交运行结果截图3.编写程序，查询世界上的所有中东国家的总人口4.编写程序，查询世界上的平均寿命最长和最短的国家

相关问题

GetDBCnnection类由于系统版本不同，改为

import java.sql.*;public class GetDBConnection {    public static Connection connectDB(String DBName,String id,String p) {        Connection con = null;        String                uri = "jdbc:mysql://localhost:3306/"+DBName+"?serverTimezone=GMT%2B8&characterEncoding=utf-8";        try{  Class.forName("com.mysql.cj.jdbc.Driver");//加载JDBC-MySQL驱动        }        catch(Exception e){}        try{            con = DriverManager.getConnection(uri,id,p); //连接代码        }        catch(SQLException e){}        return con;    }}

步骤

一、world.sql

1.下载相关附件并解压。

2.在数据库单击右键，运行sql文件，选择sql文件，点击开始。

3.导入完成后，重新打开连接会有显示。

二、编写程序，查询世界上超过“你学号前边七位并把最后一位家到最高位，最高位为0时置1”

1.学号为20175225，查询为超过7017522

2.实验代码

import java.sql.*;public class MySQL1 {    public static void main(String[] args) {        Connection con;        Statement sql;        ResultSet rs;        con = GetDBConnection.connectDB("world", "root", "");        if (con == null) {            return;        }        String sqlStr = "select*from city where population>7017522";        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.相关截图

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

1.相关代码

import java.sql.*;public class MySQL2 {    public static void main(String[] args) {        Connection con;        Statement sql;        ResultSet rs;        con = GetDBConnection.connectDB("world","root","");        if(con == null) {            return;        }        String sqlStr = "select * from country where Region = 'Middle East'";        try {            sql = con.createStatement();            rs = sql.executeQuery(sqlStr);            long totalpopulation = 0;            while(rs.next()) {                int Population = rs.getInt(7);                totalpopulation +=Population;            }            System.out.println("中东国家的总人口为"+totalpopulation);            con.close();        }        catch (SQLException e) {            System.out.println(e);        }    }}

2.截图

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

1.相关代码

import java.sql.*;public class MySQL3 {    public static void main(String[] args) {        Connection con;        Statement sql;        ResultSet rs;        con = GetDBConnection.connectDB("world","root","");        if(con == null) {            return;        }        String sqlStr = "select * from country order by LifeExpectancy";        try {            sql = con.createStatement();            rs = sql.executeQuery(sqlStr);            rs.first();            String highcountry,lowcountry;            float number1 = rs.getInt(8);            while(number1 == 0) {                rs.next();                number1 = rs.getInt(8);            }            lowcountry = rs.getString(2);            System.out.println("世界上平均寿命最短的国家为："+lowcountry+" 寿命为"+number1);            rs.last();            float number2 = rs.getInt(8);            highcountry = rs.getString(2);            System.out.println("世界上平均寿命最长的国家为："+highcountry+" 寿命为"+number2);            con.close();        }        catch (SQLException e) {            System.out.println(e);        }    }}

2.截图