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

        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)。这说明，使用不同运算符的成对比较在转换为标量表达式比较时，都有各自的转换规则。

4、范围比较

4.1 BETWEEN …AND…

BETWEEN …AND… 操作符可以测试列值是否在一个取值范围之内。

例1：找出1962—1964年之间出生的

root@TENNIS 09:53  mysql>SELECT playerno,birth_date   FROM PLAYERS WHERE birth_date BETWEEN '1962-01-01' ANND '1964-01-01';
+----------+------------+
| playerno | birth_date |
+----------+------------+
|        7 | 1963-05-11 |
|        8 | 1962-07-08 |
|       28 | 1963-06-22 |
|       44 | 1963-01-09 |
|       95 | 1963-05-14 |
|      100 | 1963-02-28 |
|      112 | 1963-10-01 |
+----------+------------+
7 rows in set (0.00 sec)

4.2 IN操作符

测试一个列值是否在一个值列表中。值列表中可以是字面量、列或标量子查询。

例2：对于那些有两局获胜或者两局输掉的所有比赛，得到比赛编号、获胜的局数和输掉的局数

root@TENNIS 09:55  mysql>SELECT  matchno,won,lost   FROM  MATCHES   WHERE  2  IN  (won, lost);
+---------+-----+------+
| matchno | won | lost |
+---------+-----+------+
|       2 |   2 |    3 |
|       4 |   3 |    2 |
|       9 |   3 |    2 |
|      10 |   3 |    2 |
|      11 |   2 |    3 |
+---------+-----+------+
5 rows in set (0.00 sec)

5、 LIKE操作符

like ‘匹配模式字符串’，实现模式匹配查询或者模糊查询：测试一个列值是否匹配给出的模式在‘匹配模式字符串’中，可以有两个具有特殊含义的通配字符：

–%：表示0个或者任意多个字符
–_：只表示一个任意字符

例3：找出名字以大写字母B开头的球员的名字和编号

root@TENNIS 09:58  mysql>SELECT  name, playerno   FROM  PLAYERS WHERE  NAME  LIKE  'B
B%';
+--------+----------+
| name   | playerno |
+--------+----------+
| Bishop |       39 |
| Baker  |       44 |
| Brown  |       57 |
| Bailey |      112 |
+--------+----------+
4 rows in set (0.00 sec)

如果转义字符指定反斜杠\，必须写两个：ESCAPE ‘\\‘.因为反斜杠\在进行模式匹配时，会被剥离一次（oracle中写一个反斜杠即可）。

select * from table_name wherecol_namelike ‘%\\\%‘，写成三个‘\’的原因是反斜杠会被语法分析程序剥离一次，在进行模式匹配时，又会被剥离一次，最后会剩下一个反斜杠接受匹配。

6、REGEXP操作符

MySQL特有的操作符。regexp是正则表达式（regular expression）的缩写。它比like功能更加强大。

例4：对于名字中有字母m、e、n中的一个，并且连续出现2次的球员的编号和名字

root@TENNIS 10:05  mysql>SELECT  playerno,name   FROM PLAYERS WHERE  name REGEXP '[meen][men]';
+----------+-----------+
| playerno | name      |
+----------+-----------+
|        6 | Parmenter |
|        8 | Newcastle |
|      100 | Parmenter |
+----------+-----------+
3 rows in set (0.00 sec)

7、使用逻辑操作符

and、or、not、 xor，用于在WHERE条件中把多个条件组合起来。

7.1 AND操作符

例5：得到1970年之后出生的每个男球员的编号、名字、性别和出生日期.

root@TENNIS 10:08  mysql>SELECT  playerno, name, sex, birth_date FROM PLAYERS WHERE birth_date  >  '1970-12-31'       AND  sex = 'M';
+----------+-------+-----+------------+
| playerno | name  | sex | birth_date |
+----------+-------+-----+------------+
|       57 | Brown | M   | 1971-08-17 |
+----------+-------+-----+------------+
1 row in set (0.00 sec)

7.2 OR操作符

例6：得到住在Plymouth或者Eltham的球员的编号、姓名和居住城市

root@TENNIS 10:10  mysql>SELECT playerno,name,town FROM PLAYERS WHERE town = 'Plymouuth' OR  town = 'Eltham';
+----------+---------+----------+
| playerno | name    | town     |
+----------+---------+----------+
|       27 | Collins | Eltham   |
|      104 | Moorman | Eltham   |
|      112 | Bailey  | Plymouth |
+----------+---------+----------+
3 rows in set (0.00 sec)

7.3 NOT操作符

用在一个条件的前面。经常和其它操作符一起使用。例如：not in、not between、not like、is not null。

例9：查找罚款金额不是25美元或者50美元的球员的编号

root@TENNIS 10:10  mysql>select * from PENALTIES;
+-----------+----------+--------------+--------+
| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |
+-----------+----------+--------------+--------+
|         1 |        6 | 1980-12-08   | 100.00 |
|         2 |       44 | 1981-05-05   |  75.00 |
|         3 |       27 | 1983-09-10   | 100.00 |
|         4 |      104 | 1984-12-08   |  50.00 |
|         5 |       44 | 1980-12-08   |  25.00 |
|         6 |        8 | 1980-12-08   |  25.00 |
|         7 |       44 | 1982-12-30   |  30.00 |
|         8 |       27 | 1984-11-12   |  75.00 |
+-----------+----------+--------------+--------+
8 rows in set (0.00 sec)

+----------+
| playerno |
+----------+
|        6 |
|       44 |
|       27 |
|       44 |
|       27 |
+----------+
5 rows in set (0.00 sec)

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

如果在where中同时用到了and和or运算符，先算and后算or，因为and优先级高。可以使用小括号来改变运算的顺序。

例10：得到那些住在Stratford或者出生于1963年的球员的编号、姓名、出生日期，但是不包括那些住在Stratford并且出生于1963年的球员

root@TENNIS 10:15  mysql>SELECT playerno, name, birth_date FROM PLAYERS WHERE (town=''Stratford' OR year(birth_date)='1963') AND NOT (town='Stratford' AND year(birth_datee)='1963');
+----------+-----------+------------+
| playerno | name      | birth_date |
+----------+-----------+------------+
|        2 | Everett   | 1948-09-01 |
|        6 | Parmenter | 1964-06-25 |
|       28 | Collins   | 1963-06-22 |
|       39 | Bishop    | 1956-10-29 |
|       44 | Baker     | 1963-01-09 |
|       57 | Brown     | 1971-08-17 |
|       83 | Hope      | 1956-11-11 |
|       95 | Miller    | 1963-05-14 |
|      112 | Bailey    | 1963-10-01 |
+----------+-----------+------------+
9 rows in set (0.00 sec)

7.5 XOR操作符

例11：得到那些住在Stratford或者出生于1963年的球员的编号、姓名、出生日期，但是不包括那些住在Stratford并且出生于1963年的球员

root@TENNIS 10:18  mysql>SELECT playerno,name,birth_date FROM PLAYERS WHERE (town='Sttratford') XOR (year(birth_date)='1963');
+----------+-----------+------------+
| playerno | name      | birth_date |
+----------+-----------+------------+
|        2 | Everett   | 1948-09-01 |
|        6 | Parmenter | 1964-06-25 |
|       28 | Collins   | 1963-06-22 |
|       39 | Bishop    | 1956-10-29 |
|       44 | Baker     | 1963-01-09 |
|       57 | Brown     | 1971-08-17 |
|       83 | Hope      | 1956-11-11 |
|       95 | Miller    | 1963-05-14 |
|      112 | Bailey    | 1963-10-01 |
+----------+-----------+------------+
9 rows in set (0.00 sec)

8、使用ORDER BY子句排序行

语法：

SELECT expr

FROM table

[WHERE condition(s)]

[ORDER BY {column, expr, numeric_position} [ASC|DESC]];

ASC：执行升序排序。默认值

DESC ：执行降序排序

ORDER BY子句一般在 SELECT 语句的最后面

例12：得到每笔罚款的球员编号和罚款额，结果以编号降序，罚款额升序排

root@TENNIS 10:24  mysql>SELECT playerno,amount FROM PENALTIES ORDER BY playerno DESC,amount ASC;
+----------+--------+
| playerno | amount |
+----------+--------+
|      104 |  50.00 |
|       44 |  25.00 |
|       44 |  30.00 |
|       44 |  75.00 |
|       27 |  75.00 |
|       27 | 100.00 |
|        8 |  25.00 |
|        6 | 100.00 |
+----------+--------+
8 rows in set (0.00 sec)

root@TENNIS 10:24  mysql>select * from PENALTIES;
+-----------+----------+--------------+--------+
| PAYMENTNO | PLAYERNO | PAYMENT_DATE | AMOUNT |
+-----------+----------+--------------+--------+
|         1 |        6 | 1980-12-08   | 100.00 |
|         2 |       44 | 1981-05-05   |  75.00 |
|         3 |       27 | 1983-09-10   | 100.00 |
|         4 |      104 | 1984-12-08   |  50.00 |
|         5 |       44 | 1980-12-08   |  25.00 |
|         6 |        8 | 1980-12-08   |  25.00 |
|         7 |       44 | 1982-12-30   |  30.00 |
|         8 |       27 | 1984-11-12   |  75.00 |
+-----------+----------+--------------+--------+
8 rows in set (0.00 sec)

9、用户变量

在MySQL中，我们可以定义变量，即用户变量（user-defined variable）。变量定义完毕后，就可以在SQL语句中使用它们了（任何可以使用标量表达式的地方），有两种定义用户变量的

方法：SET语句和SELECT语句。

SET语句语法：

set @变量名 {= | := } value [,@变量名 {= | := } value,…] ;

例13 ：定义用户变量 PI ，其初值为 3.1415926

SET @PI := 3.1415926;

注意：用户变量的数据类型派生自其值的数据类型要得到用户变量的值，使用SELECT语句

例14：得到用户变量PI的值

select @PI

例15：查询球员编号小于变量PI的所有球员的姓名、居住地和邮编

root@TENNIS 12:07  mysql>SET @PI := 3.1415926;
Query OK, 0 rows affected (0.00 sec)

root@TENNIS 12:16  mysql>select @PI;
+-----------+
| @PI       |
+-----------+
| 3.1415926 |
+-----------+
1 row in set (0.00 sec)

root@TENNIS 12:17  mysql>SELECT  name, town, postcode   FROM PLAYERS WHERE playerno < @PI;
+---------+-----------+----------+
| name    | town      | postcode |
+---------+-----------+----------+
| Everett | Stratford | 3575NH   |
+---------+-----------+----------+
1 row in set (0.00 sec)

例16：

SET @a = 1;

SET @a =5,@b = @a;

SELECT @b;

这里注意，变量b的值是1（a的老值）而不是5（a的新值）。因为MySQL首先先确定等号右边所有表达式的值（此时@a=1），然后再把值赋给变量（即5给@a，1给@b）

例17：创建用户变量playerno并赋值为7

root@TENNIS 12:17  mysql>SELECT  @playerno := 7;
+----------------+
| @playerno := 7 |
+----------------+
|              7 |
+----------------+
1 row in set (0.00 sec)

root@TENNIS 12:23  mysql>SELECT @name := 'tom', @town := 'Inglewood', @postcode :='1234ab';
+----------------+----------------------+-----------------------+
| @name := 'tom' | @town := 'Inglewood' | @postcode := '1234ab' |
+----------------+----------------------+-----------------------+
| tom            | Inglewood            | 1234ab                |
+----------------+----------------------+-----------------------+
1 row in set (0.00 sec)

root@TENNIS 12:24  mysql>SELECT @name = 'tom', @town = 'Inglewood',        @postcode = '1234ab';
+---------------+---------------------+----------------------+
| @name = 'tom' | @town = 'Inglewood' | @postcode = '1234ab' |
+---------------+---------------------+----------------------+
|             1 |                   1 |                    1 |
+---------------+---------------------+----------------------+
1 row in set (0.00 sec)

这种语法：

1、会返回一行

2、只能使用:=，不能使用=。=号这里被看做比较操作符。

原文链接：https://blog.csdn.net/cylcylccyyll/article/details/126315059

1、使用WHERE子句

2、字符集和校对规则