MySQL限制和排序数据 ---where、order by、between……and……

目录

1、使用WHERE子句 

2、字符集和校对规则 

3、比较操作符

4、范围比较

4.1  BETWEEN …AND…

5、 LIKE操作符 

6、REGEXP操作符

7、使用逻辑操作符 

7.1  AND操作符

7.2  OR操作符

7.3  NOT操作符

7.4  使用小括号改变运算的顺序

7.5  XOR操作符

8、 使用ORDER BY子句排序行 

9、用户变量 

---

1、使用WHERE子句 

        如果想限制查询返回的行，需要使用WHERE子句，WHERE子句跟在FROM子句后面，不能在WHERE子句中使用列别名。

2、字符集和校对规则 

        排序、比较 ，一般不进行设置，有默认。

        show character set  查看有哪些字符集。
    校对规则(COLLATION)：它是一组规则，负责决定某一字符集内的字符进行比较和排序的结果。
    每个字符集有一个或多个校对规则，并且每个校对规则只能属于一个字符集，每个字符集有一个默认校对规则。例如，utf8默认校对规则是utf8_general_ci，校对规则的命名约定：以字符集名开始，中间包括一个语言名，最后以后缀_ci、_cs或bin结束 。
    *_bin:  binary case sensitive collation，区分大小写 ，直接比较字符的编码
    *_cs:    case sensitive collation，区分大小写，相同字符靠近排列 
    *_ci:     case insensitive collation，不区分大小写 

3、比较操作符

         给定一个球员表：

root@TENNIS 09:42  mysql>select * from PLAYERS;
+----------+-----------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
| PLAYERNO | NAME      | INITIALS | BIRTH_DATE | SEX | JOINED | STREET         | HOUSENO | POSTCODE | TOWN      | PHONENO    | LEAGUENO |
+----------+-----------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
|        2 | Everett   | R        | 1948-09-01 | M   |   1975 | Stoney Road    | 43      | 3575NH   | Stratford | 070-237893 | 2411     |
|        6 | Parmenter | R        | 1964-06-25 | M   |   1977 | Haseltine Lane | 80      | 1234KK   | Stratford | 070-476537 | 8467     |
|        7 | Wise      | GWS      | 1963-05-11 | M   |   1981 | Edgecombe Way  | 39      | 9758VB   | Stratford | 070-347689 | NULL     |
|        8 | Newcastle | B        | 1962-07-08 | F   |   1980 | Station Road   | 4       | 6584WO   | Inglewood | 070-458458 | 2983     |
|       27 | Collins   | DD       | 1964-12-28 | F   |   1983 | Long Drive     | 804     | 8457DK   | Eltham    | 079-234857 | 2513     |
|       28 | Collins   | C        | 1963-06-22 | F   |   1983 | Old Main Road  | 10      | 1294QK   | Midhurst  | 010-659599 | NULL     |
|       39 | Bishop    | D        | 1956-10-29 | M   |   1980 | Eaton Square   | 78      | 9629CD   | Stratford | 070-393435 | NULL     |
|       44 | Baker     | E        | 1963-01-09 | M   |   1980 | Lewis Street   | 23      | 4444LJ   | Inglewood | 070-368753 | 1124     |
|       57 | Brown     | M        | 1971-08-17 | M   |   1985 | Edgecombe Way  | 16      | 4377CB   | Stratford | 070-473458 | 6409     |
|       83 | Hope      | PK       | 1956-11-11 | M   |   1982 | Magdalene Road | 16A     | 1812UP   | Stratford | 070-353548 | 1608     |
|       95 | Miller    | P        | 1963-05-14 | M   |   1972 | High Street    | 33A     | 5746OP   | Douglas   | 070-867564 | NULL     |
|      100 | Parmenter | P        | 1963-02-28 | M   |   1979 | Haseltine Lane | 80      | 6494SG   | Stratford | 070-494593 | 6524     |
|      104 | Moorman   | D        | 1970-05-10 | F   |   1984 | Stout Street   | 65      | 9437AO   | Eltham    | 079-987571 | 7060     |
|      112 | Bailey    | IP       | 1963-10-01 | F   |   1984 | Vixen Road     | 8       | 6392LK   | Plymouth  | 010-548745 | 1319     |
+----------+-----------+----------+------------+-----+--------+----------------+---------+----------+-----------+------------+----------+
14 rows in set (0.00 sec)

3.1  得到联盟会员号码为7060的球员的编号

root@TENNIS 09:43  mysql>SELECT  playerno   FROM  PLAYERS   WHERE  leagueno='7060';
+----------+
| playerno |
+----------+
|      104 |
+----------+
1 row in set (0.00 sec)

参考文章：

(1条消息) MySQL的Limit子句的使用，以及相关例题_忙碌且充实的博客-CSDN博客

3.2  找出获胜局数等于2并且输掉局数等于3的比赛的编号

root@TENNIS 09:47  mysql>select * from MATCHES;
+---------+--------+----------+-----+------+
| MATCHNO | TEAMNO | PLAYERNO | WON | LOST |
+---------+--------+----------+-----+------+
|       1 |      1 |        6 |   3 |    1 |
|       2 |      1 |        6 |   2 |    3 |
|       3 |      1 |        6 |   3 |    0 |
|       4 |      1 |       44 |   3 |    2 |
|       5 |      1 |       83 |   0 |    3 |
|       6 |      1 |        2 |   1 |    3 |
|       7 |      1 |       57 |   3 |    0 |
|       8 |      1 |        8 |   0 |    3 |
|       9 |      2 |       27 |   3 |    2 |
|      10 |      2 |      104 |   3 |    2 |
|      11 |      2 |      112 |   2 |    3 |
|      12 |      2 |      112 |   1 |    3 |
|      13 |      2 |        8 |   0 |    3 |
+---------+--------+----------+-----+------+
13 rows in set (0.00 sec)

root@TENNIS 09:47  mysql>SELECT  matchno   FROM  MATCHES   WHERE (won,lost) = (2,3);
+---------+
| matchno |
+---------+
|       2 |
|      11 |
+---------+

        MySQL在内部把条件重写为(won=2) and (lost=3)，注意：条件(2,4)>(1,3)并不等(2>1) and (4>3)，而是等于(2>1) or (2=1 and 4>3)。这说明，使用不同运算符的成对比较