My SQL and Java Developer’s Guide phần 10

Khi sử dụng các nhà điều hành bình đẳng vào các lĩnh vực cột, một giá trị NULL sẽ tàn phá với các truy vấn. Nếu các giá trị cột có thể là NULL, sử dụng nhà điều hành này. Ví dụ: mysql chọn đăng nhập từ đăng nhập closedate = null; Tập hợp rỗng (0,00 giây) mysql SELECT đăng nhập TỪ đăng nhập WHERE closedate | Comparison Functions Operators 373 The less than and greater than operators work on both numeric and alphanumeric operands they return true or false based on their obvious function. When using the equality operator on column fields a NULL value will play havoc with queries. If column values might be NULL use this operator. For example mysql select login from login where closedate null Empty set sec mysql SELECT login FROM login WHERE closedate null ------- login ------- johnd bobs ------- 2 rows in set sec BETWEEN min AND max Many queries will require an expression to bring back results in a range of values. For example WHERE salary 10000 and salary 100000. The operator BETWEEN min AND max is a convenience operator for this range type query. Example mysql SELECT login salary FROM login WHERE salary BETWEEN 10000 AND 100000 ---------- ----- login salary ---------- ----- janed 91000 jaysong 42000 Mattm 46000 bobs 24000 ---------- ----- 4 rows in set sec COALESCEỢứt text If you want to retrieve a list of the row in a database where a NULL exists in a column but you would like to have a more pleasing message appear than the string null then COALESCE can be used. The string text will be displayed for all NULL values in list otherwise the current column value is displayed. 374 MySQL Functions and Operators The COALESCE function returns either NULL or value when a NULL is found in list. Example mysql SELECT login opendate COALESCE closedate Closedate is NULL as WARNING FROM login ---------- --------------------- -------------------- login opendate WARNING ---------- --------------------- -------------------- johnd 2002-10-10 00 00 00 Closedate is NULL janed 0000-00-00 00 00 00 0000-00-00 00 00 00 timd 0000-00-00 00 00 00 0000-00-00 00 00 00 jamesr 0000-00-00 00 00 00 0000-00-00 00 00 00 jaysong 0000-00-00 00 00 00 0000-00-00 00 00 00 Mattm 0000-00-00 00 00 00 0000-00-00 00 00 00 bobs 2003-01-10 09 51 27 Closedate is NULL ---------- --------------------- .