第十一章 JDBC与MySQL数据库(7)——查询操作之条件与排序查询

select 字段 from 表名 where 条件
复制

select name,height from mess where name='李四'
复制

select * from mess where height>1.60 and height<=1.8
select * from mess where height>1.7 and name != '张山'
复制

select * from mess where year(birthday)<1980 and month (birthday)<=10
select * from mess where year(birthday) between 1983 and 1986
复制

select * from time_list where second(shijian)=56;
select * from time_list where minute(shijian)>15;
复制

select * from mess where name like '%林%'
复制

select * from mess order by height
select * from mess where name like '%林%' order by name
复制

import java.sql.*;
public class Example3 {
  public static void main(String[] args) {
    Connection con;
    Statement sql;
    ResultSet rs;
    con = GetDBConnection.connectDB("students","root","");
    if(con == null) return;
    String c1 = " year(birthday)<=2000 and month(birthday)>7";  //条件 1
    String c2 = " name Like '张_%'";                            //条件2
    String c3 = "height >1.65";                                //条件3
    String sqlstr =  "select * from mess where "+c1+" and "+c2+" and "+c3+"order by birthday";
    try {
      sql=con.createStatement();
      rs = sql.executeQuery(sqlstr);
      while (rs.next()) {
        String number = rs.getString(1);
        String name = rs.getString(2);
        Date date = rs.getDate(3);
        float h = rs.getFloat(4);
        System.out.printf("%s\t",number);
        System.out.printf("%s\t",name);
        System.out.printf("%s\t",date);
        System.out.printf("%.2f\n",h);
      }
      con.close();
    }
    catch (SQLException e) {
      System.out.println(e);
    }
  }
}
复制