20175223 MySQL

目錄

• 完成結果
  • 要求 1 ：導入world.sql
  • 要求 2 ：CityWanna.java
    • CityWanna.java
  • 要求 3 ：CountryWanna.java
    • CountryWanna.java
  • 要求 4 ：LifeWanna.java
    • LifeWanna.java
• 過程中問題及解決
  • 1. XAMPP無法啟用 MySQL 程序。

目錄

完成結果

要求 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 。