完成结果

要求 1 ：导入world.sql

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

截图：

要求 2 ：CityWanna.java

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

截图：

CityWanna.java

import java.sql.*;

import java.util.Scanner;

/**

 * @author 10542

 */

public class CityWanna {

    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 password = "";

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

        if (con == null) {

            return;

        }

        //输入学号20175223得：5017522

        //magicNumber[] 替换魔法值

        int [] magicNumber = new int[]{10,1000000};

        int studentId ,frist ,last;

        System.out.println ("Input your student's id:");

        Scanner reader = new Scanner (System.in);

        studentId = reader.nextInt ();

        frist = studentId/10;

        last = studentId%10;

        frist = frist + last*1000000;

        if (frist/magicNumber[1]==magicNumber[0]) {

            frist=(frist-10000000)+1000000;

        }

        else if (frist/magicNumber[1]>magicNumber[0]) {

            frist=frist-10000000;

        }

        System.out.println ("Result:" +frist);

        try {

            //Statement sql = con.createStatement(); -> 向数据库发送SQL查询语句

            sql = con.createStatement();

            //ResultSet rs = sql.executeQuery(sqlStr); -> 处理查询结果

            rs = sql.executeQuery("select*from city where population>"+Integer.toString (frist));

            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 ：CountryWanna.java

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

截图：

CountryWanna.java

import java.sql.*;

/**

 * @author 10542

 */

public class CountryWanna {

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

        Connection con;

        Statement sql;

        ResultSet rs;

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

        String user = "root";

        String password = "";

        con = DriverManager.getConnection(uri, user,password);

        if (con == null) {

            return;

        }

        try {

            sql = con.createStatement();

            rs = sql.executeQuery("select Name,Population from country where Region = 'Middle East'");

            int allPopulation = 0;

            while (rs.next()) {

                String name = rs.getString(1);

                int population = rs.getInt(2);

                System.out.printf("The population of %s is %d\n", name, population);

                allPopulation = allPopulation + population;

            }

            System.out.println("The population of Middle East" + allPopulation);

        } catch (SQLException e) {

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

        }

    }

}

要求 4 ：LifeWanna.java

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

截图：

LifeWanna.java

import java.sql.*;

/**

 * @author 10542

 */

public class LifeWanna {

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

        Connection con;

        Statement sql;

        ResultSet rs;

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

        String user = "root";

        String password = "";

        con = DriverManager.getConnection(uri, user,password);

        if (con == null) {

            return;

        }

        try {

            sql = con.createStatement();

            rs = sql.executeQuery("select Name,LifeExpectancy from country order by LifeExpectancy");

            /**

             * rs.next() 跳读取下一行信息

             * 若有，返回true，继续循环

             * 若无，返回false，停止循环

             */

            while (rs.next()) {

                float life = rs.getInt(2);

                String name;

                //获取第一条数据的信息

                rs.first();

                while (life == 0) {

                    //获取下一条数据的信息

                    rs.next();

                    life = rs.getInt(2);

                }

                name = rs.getString(1);

                System.out.println("The shortest life expectancy in the world:" + name);

                System.out.println ("LifeExpectancy is:" + rs.getInt (2));

                //获取最后一条数据的信息

                rs.last();

                name = rs.getString(1);

                System.out.println("The longest life expectancy in the world:" + name);

                System.out.println ("LifeExpectancy is:" + rs.getInt (2));

            }

        } catch (SQLException e) {

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

        }

    }

}

过程中问题及解决

1. XAMPP无法启用 MySQL 程序。

问题 1 解决方法：

在安装xampp之前电脑上装过mysql，然后默认启动的是以前的mysql。

修改注册表：[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\MySQL] 的 ImagePath 修改成新的xampp中位置 <xampp>\mysql\bin\mysqld MySQL 。

巴特西

20175223 MySQL