MySQL实验作业_MySQL课下作业

MySQL

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

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

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

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

实验代码

主程序

package MySQL;

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;

}

}

getCity

package MySQL;

import java.sql.*;

public class getCity{

public static void main(String[] args) {

Connection con;

Statement sql;

ResultSet rs;

con = GetDBConnection.connectDB("world","root","");

if(con == null) return;

try {

sql=con.createStatement();

rs = sql.executeQuery("SELECT * FROM city");

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);

if(Population>1017520) {

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(e);

}

}

}

getyear

package MySQL;

import java.sql.*;

public class getyear {

public static void main(String[] args) {

Connection con;

Statement sql;

ResultSet rs;

con = GetDBConnection.connectDB("world","root","ldc990430");

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);

}

}

}

getPeople

package MySQL;

import java.sql.*;

public class getPeople {

public static void main(String[] args) {

Connection con;

Statement sql;

ResultSet rs;

con = GetDBConnection.connectDB("world","root","ldc990430");

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);

}

}

}

实验截图

连接主程序

城市

寿命

总人口

代码链接