day16-mysql多表查詢原生jdbc編程題代碼版

1、創(chuàng)建數(shù)據(jù)庫語句建表語句表數(shù)據(jù)：題目一：通過 JDBC，將寶馬廠商下的中型車（寶馬 3 系），以 28.3W 的價格，添加到數(shù)據(jù)庫表car 中：publicvoid method01()try Class.forName(com.jdbc.Driver); Connection conn =DriverManager.getConnection(jdbc:/localhost:3306/day31, root, root); PreparedSement ps= conn.prepareSement(INSERTO car(cname,company, grade, price) VALUES

2、(?,?,?,?);ps.setString(1, 寶馬3系);ps.setString(2, 寶馬);ps.setString(3, 中型車); ps.setDouble(4, 28.3);addCount = ps.executeUpdate(); System.out.prln(添加汽車個數(shù): +addCount); catch (Exception e) e.prStackTrace();CREATETABLE car(idPRIMARYKEY AUTO_INCREMENT, #IDcname VARCHAR(20), #車型名稱 company VARCHAR(20), #廠商 gr

3、ade VARCHAR(20), #級別 price DOUBLE #價格(萬元);CREATE DATABASE day31;USE day31;題目二：通過 JDBC，查詢級別為緊湊型車的所有汽車信息，并把信息顯示在控制臺中要求使用：PreparedS：ement題目三：通過 JDBC，查詢級別為包含SUV的所有汽車信息，并把信息顯示在控制臺中要求使用：PreparedSement：publicvoid method03()try Class.forName(com.jdbc.Driver); Connection conn =DriverManager.getConnection(jdb

4、c:/localhost:3306/day31, root, root);PreparedSement ps= conn.prepareSement(SELECT * FROM car WHERE grade LIKE ?);publicvoid method02()try Class.forName(com.jdbc.Driver); Connection conn =DriverManager.getConnection(jdbc:/localhost:3306/day31, root, root);PreparedSement ps= conn.prepareSement(SELECT

5、* FROM car WHEREgrade=?);ps.setString(1, 緊湊型車); ResultSet rs = ps.executeQuery(); while(rs.next()id = rs.get(id);String cname = rs.getString(cname); String company = rs.getString(company); String grade = rs.getString(grade); doubleprice = rs.getDouble(price);System.out.prln(id +t+ cname +t+ company

6、+t+ grade +t+price); catch (Exception e) e.prStackTrace();題目四：通過 JDBC，查詢價格大于 10W 的所有汽車信息，并把信息顯示在控制臺中要求使用：PreparedSement：publicvoid method04()try Class.forName(com.jdbc.Driver); Connection conn =DriverManager.getConnection(jdbc:/localhost:3306/day31, root, root);PreparedSement ps= conn.prepareSement(

7、SELECT * FROM car WHEREprice ?);ps.setDouble(1, 10);ResultSet rs = ps.executeQuery();while(rs.next()id = rs.get(id);String cname = rs.getString(cname); String company = rs.getString(company); String grade = rs.getString(grade); doubleprice = rs.getDouble(price);System.out.prln(id +t+ cname +t+ compa

8、ny +t+ grade +t+price); catch (Exception e) e.prStackTrace();ps.setString(1, %SUV%); ResultSet rs = ps.executeQuery(); while(rs.next()id = rs.get(id);String cname = rs.getString(cname); String company = rs.getString(company); String grade = rs.getString(grade); doubleprice = rs.getDouble(price);Syst

9、em.out.prln(id +t+ cname +t+ company +t+ grade +t+price); catch (Exception e) e.prStackTrace();題目五：通過 JDBC，查詢廠商為奔馳、寶馬、奧迪的所有汽車信息，并把信息顯示在控制臺中要求使用：PreparedS：ement題目六：通過 JDBC，查詢價格在 10W 到 20W 之間，同時級別為緊湊型 SUV、緊湊型車的所有汽車信息，并把信息顯示在控制臺中要求使用：PreparedSement：publicvoid method06()try Class.forName(com.jdbc.Driver

10、);publicvoid method05()try Class.forName(com.jdbc.Driver); Connection conn =DriverManager.getConnection(jdbc:/localhost:3306/day31, root, root);PreparedSement ps= conn.prepareSement(SELECT * FROM car WHERE company IN(?,?,?);ps.setString(1, 奔馳);ps.setString(2, 寶馬);ps.setString(3, 奧迪); ResultSet rs =

11、ps.executeQuery(); while(rs.next()id = rs.get(id);String cname = rs.getString(cname); String company = rs.getString(company); String grade = rs.getString(grade); doubleprice = rs.getDouble(price);System.out.prln(id +t+ cname +t+ company +t+ grade +t+price); catch (Exception e) e.prStackTrace();題目七：通

12、過 JDBC，查詢價格在 15W 以下的所有汽車信息，并按照價格的從低到高，把信息顯示在控制臺中要求使用：PreparedSement：Testpublicvoid method07()try Class.forName(com.jdbc.Driver); Connection conn =DriverManager.getConnection(jdbc:/localhost:3306/day31, root, root);PreparedSementps= conn.prepareSement(SELECT * FROM car WHERE price ? ORDER BY price;);

13、ps.setDouble(1, 15);ResultSet rs = ps.executeQuery();while(rs.next()id = rs.get(id);String cname = rs.getString(cname); String company = rs.getString(company);Connection conn =DriverManager.getConnection(jdbc:/localhost:3306/day31, root, root);PreparedSement ps= conn.prepareSement(SELECT * FROM car

14、WHERE grade LIKE ? AND price BETN ?AND ?);ps.setString(1, 緊湊型%); ps.setDouble(2, 10);ps.setDouble(3, 20);ResultSet rs = ps.executeQuery();while(rs.next()id = rs.get(id);String cname = rs.getString(cname); String company = rs.getString(company); String grade = rs.getString(grade); doubleprice = rs.ge

15、tDouble(price);System.out.prln(id +t+ cname +t+ company +t+ grade +t+price); catch (Exception e) e.prStackTrace();題目八：通過 JDBC，查詢價格在 20W 以下,級別為緊湊型 SUV的汽車數(shù)量，把數(shù)量顯示在控制臺中要求使用：PreparedS：ement題目九：通過 JDBC，查詢每種級別下，價格最低的汽車信息，把信息(級別、制臺中要求使用：PreparedSement：格)顯示在控publicvoid method08()try Class.forName(com.jdbc.D

16、river); Connection conn =DriverManager.getConnection(jdbc:/localhost:3306/day31, root, root); PreparedSement ps= conn.prepareSement(SELECT COUNT(*) FROM carWHERE price ? GROUP BY company); ps.setDouble(1, 10);ResultSet rs = ps.executeQuery();while(rs.next()String company = rs.getString(company); count = rs.get(2);System.out.prln(company +t+ count); catch (Exception e) e.prStackTrace();publicvoid method09()try Class.forName(com.jdbc.Driver); Connection conn =DriverManager.getConnection(jdbc:/localhost:3306/mydb, root, root); PreparedSement