条件与排序查询
①where子语句
一般格式:
select 字段 from 表名 where 条件
(1)字段值和固定值比较,例如:
select name,height from mess where name='李四'
(2)字段值在某个区间范围,例如:
select * from mess where height>1.60 and height<=1.8select * from mess where height>1.7 and name != '张山'
(3)使用某些特殊的日期函数,如year、month、day:
select * from mess where year(birthday)<1980 and month (birthday)<=10select * from mess where year(birthday) between 1983 and 1986
(4)使用某些特殊的时间函数,如hour、minute、second:
select * from time_list where second(shijian)=56;select * from time_list where minute(shijian)>15;
(5)用操作符like进行模式匹配,使用%代替0个或多个字符,用一个下画线_代替一个字符。例如,查询name有“林”字的记录:
select * from mess where name like '%林%'
②排序
用order by子语句对记录进行排序,例如:
select * from mess order by heightselect * from mess where name like '%林%' order by name
例子3查询mess表中姓张,身高大于1.65,出生的年份在2000年或2000之前,月份在7月之后的学生,并按出生日期排序(在运行例子3程序前,我们使用MySQL客户端管理工具又向mess表添加了一些记录)。程序代码如下所示(例子3中使用了例子2中的GetDBConnection类)。
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); } }}
程序运行效果如图所示