基本查询语句
MySQL冲数据表中查询数据的基本语句是SELECT语句，SELECT语句基本格式是：
SELECT
{* | <字段列表>}
[
FROM <表1>,<表2>,...
[WHERE <表达式>
[GROUP BY <group by definition>]
[HAVING <expression> [{<operator> <expression>}...]]
[ORDER BY <order by definition>]
[LIMIT [<offset>,] <row count>]
]
SELECT [字段1,字段2,字段3]
FROM [表或视图]
WHERE [查询条件];
其中，各条子句的含义如下：
1. {* | <字段列表>}包含星号匹配符（表示全部）和字段列表，表示查询的字段。
其中，字段列表至少包含一个字段名称，如果要查询多个字段，则用逗号分隔，
最后一个字段不用逗号。
2. FROM <表1>,<表2>,...，表示表1和表2是查询数据的来源，可以是单个或者多个。
3. WHERE <表达式> ，WHERE子句是可选项，如果选择该项，将限定查询行必须满足的查询条件。
4. GROUP BY <字段>，该子句告诉MySQL如何显示查询出来的数据，并按照指定的字段分组。
5. [ORDER BY <order by definition>]，盖兹矩告诉MySQL安神摸样的顺序显示查询出来的数据，
可以进行的排序有升序（ASC），降序（DESC）。
6. [LIMIT [<offset>,] <row count>]，该子句告诉MySQL每次查询出来的数据条数。
SELECT的可选参数比较多，我们一步一步来慢慢学，一个一个试。
a. 从单个表中获取数据
- 首先建立数据表
mysql> desc authors;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| au_id | varchar(11) | YES | | NULL | |
| au_lname | varchar(40) | YES | | NULL | |
| au_fname | varchar(20) | YES | | NULL | |
| phone | char(12) | YES | | NULL | |
| address | varchar(40) | YES | | NULL | |
| city | varchar(20) | YES | | NULL | |
| state | char(2) | YES | | NULL | |
| zip | char(5) | YES | | NULL | |
| contract | tinyint(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
9 rows in set (0.00 sec)
- 然后输入数据
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
| au_id | au_lname | au_fname | phone | address | city | state | zip | contract |
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
| 172-32-1176 | White | Johnson | 408 496-7223 | 10932 Bigge Rd. | Menlo Park | CA | 94025 | 1 |
| 213-46-8915 | Green | Marjorie | 415 986-7020 | 309 63rd St. #411 | Oakland | CA | 94618 | 1 |
| 238-95-7766 | Carson | Cheryl | 415 548-7723 | 589 Darwin Ln. | Berkeley | CA | 94705 | 1 |
| 267-41-2394 | O'Leary | Michael | 408 286-2428 | 22 Cleveland Av. #14 | San Jose | CA | 95128 | 1 |
| 274-80-9391 | Straight | Dean | 415 834-2919 | 5420 College Av. | Oakland | CA | 94609 | 1 |
| 341-22-1782 | Smith | Meander | 913 843-0462 | 10 Mississippi Dr. | Lawrence | KS | 66044 | 0 |
| 409-56-7008 | Bennet | Abraham | 415 658-9932 | 6223 Bateman St. | Berkeley | CA | 94705 | 1 |
| 427-17-2319 | Dull | Ann | 415 836-7128 | 3410 Blonde St. | Palo Alto | CA | 94301 | 1 |
| 472-27-2349 | Gringlesby | Burt | 707 938-6445 | PO Box 792 | Covelo | CA | 95428 | 1 |
| 486-29-1786 | Locksley | Charlene | 415 585-4620 | 18 Broadway Av. | San Francisco | CA | 94130 | 1 |
| 527-72-3246 | Greene | Morningstar | 615 297-2723 | 22 Graybar House Rd. | Nashville | TN | 37215 | 0 |
| 648-92-1872 | Blotchet-Halls | Reginald | 503 745-6402 | 55 Hillsdale Bl. | Corvallis | OR | 97330 | 1 |
| 672-71-3249 | Yokomoto | Akiko | 415 935-4228 | 3 Silver Ct. | Walnut Creek | CA | 94595 | 1 |
| 712-45-1867 | del Castillo | Innes | 615 996-8275 | 2286 Cram Pl. #86 | Ann Arbor | MI | 48105 | 1 |
| 722-51-5454 | DeFrance | Michel | 219 547-9982 | 3 Balding Pl. | Gary | IN | 46403 | 1 |
| 724-08-9931 | Stringer | Dirk | 415 843-2991 | 5420 Telegraph Av. | Oakland | CA | 94609 | 0 |
| 724-80-9391 | MacFeather | Stearns | 415 354-7128 | 44 Upland Hts. | Oakland | CA | 94612 | 1 |
| 756-30-7391 | Karsen | Livia | 415 534-9219 | 5720 McAuley St. | Oakland | CA | 94609 | 1 |
| 807-91-6654 | Panteley | Sylvia | 301 946-8853 | 1956 Arlington Pl. | Rockville | MD | 20853 | 1 |
| 846-92-7186 | Hunter | Sheryl | 415 836-7128 | 3410 Blonde St. | Palo Alto | CA | 94301 | 1 |
| 893-72-1158 | McBadden | Heather | 707 448-4982 | 301 Putnam | Vacaville | CA | 95688 | 0 |
| 899-46-2035 | Ringer | Anne | 801 826-0752 | 67 Seventh Av. | Salt Lake City | UT | 84152 | 1 |
| 998-72-3567 | Ringer | Albert | 801 826-0752 | 67 Seventh Av. | Salt Lake City | UT | 84152 | 1 |
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
23 rows in set (0.01 sec)
- 使用SELECT语句查询au_id字段的数据：
mysql> SELECT au_id, au_lname, au_fname FROM authors;
+-------------+----------------+-------------+
| au_id | au_lname | au_fname |
+-------------+----------------+-------------+
| 172-32-1176 | White | Johnson |
| 213-46-8915 | Green | Marjorie |
| 238-95-7766 | Carson | Cheryl |
| 267-41-2394 | O'Leary | Michael |
| 274-80-9391 | Straight | Dean |
| 341-22-1782 | Smith | Meander |
| 409-56-7008 | Bennet | Abraham |
| 427-17-2319 | Dull | Ann |
| 472-27-2349 | Gringlesby | Burt |
| 486-29-1786 | Locksley | Charlene |
| 527-72-3246 | Greene | Morningstar |
| 648-92-1872 | Blotchet-Halls | Reginald |
| 672-71-3249 | Yokomoto | Akiko |
| 712-45-1867 | del Castillo | Innes |
| 722-51-5454 | DeFrance | Michel |
| 724-08-9931 | Stringer | Dirk |
| 724-80-9391 | MacFeather | Stearns |
| 756-30-7391 | Karsen | Livia |
| 807-91-6654 | Panteley | Sylvia |
| 846-92-7186 | Hunter | Sheryl |
| 893-72-1158 | McBadden | Heather |
| 899-46-2035 | Ringer | Anne |
| 998-72-3567 | Ringer | Albert |
+-------------+----------------+-------------+
23 rows in set (0.00 sec)
单表查询
1. 查询所有字段
- 在SELECT中使用星号(*)通配符查询所有字段
语法格式：SELECT * FROM 表名
使用星号（*）通配符时，将返回所有列，列按照定义表的时候的顺序显示。
例子：
mysql> SELECT * FROM authors;
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
| au_id | au_lname | au_fname | phone | address | city | state | zip | contract |
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
| 172-32-1176 | White | Johnson | 408 496-7223 | 10932 Bigge Rd. | Menlo Park | CA | 94025 | 1 |
| 213-46-8915 | Green | Marjorie | 415 986-7020 | 309 63rd St. #411 | Oakland | CA | 94618 | 1 |
| 238-95-7766 | Carson | Cheryl | 415 548-7723 | 589 Darwin Ln. | Berkeley | CA | 94705 | 1 |
| 267-41-2394 | O'Leary | Michael | 408 286-2428 | 22 Cleveland Av. #14 | San Jose | CA | 95128 | 1 |
| 274-80-9391 | Straight | Dean | 415 834-2919 | 5420 College Av. | Oakland | CA | 94609 | 1 |
| 341-22-1782 | Smith | Meander | 913 843-0462 | 10 Mississippi Dr. | Lawrence | KS | 66044 | 0 |
| 409-56-7008 | Bennet | Abraham | 415 658-9932 | 6223 Bateman St. | Berkeley | CA | 94705 | 1 |
| 427-17-2319 | Dull | Ann | 415 836-7128 | 3410 Blonde St. | Palo Alto | CA | 94301 | 1 |
| 472-27-2349 | Gringlesby | Burt | 707 938-6445 | PO Box 792 | Covelo | CA | 95428 | 1 |
| 486-29-1786 | Locksley | Charlene | 415 585-4620 | 18 Broadway Av. | San Francisco | CA | 94130 | 1 |
| 527-72-3246 | Greene | Morningstar | 615 297-2723 | 22 Graybar House Rd. | Nashville | TN | 37215 | 0 |
| 648-92-1872 | Blotchet-Halls | Reginald | 503 745-6402 | 55 Hillsdale Bl. | Corvallis | OR | 97330 | 1 |
| 672-71-3249 | Yokomoto | Akiko | 415 935-4228 | 3 Silver Ct. | Walnut Creek | CA | 94595 | 1 |
| 712-45-1867 | del Castillo | Innes | 615 996-8275 | 2286 Cram Pl. #86 | Ann Arbor | MI | 48105 | 1 |
| 722-51-5454 | DeFrance | Michel | 219 547-9982 | 3 Balding Pl. | Gary | IN | 46403 | 1 |
| 724-08-9931 | Stringer | Dirk | 415 843-2991 | 5420 Telegraph Av. | Oakland | CA | 94609 | 0 |
| 724-80-9391 | MacFeather | Stearns | 415 354-7128 | 44 Upland Hts. | Oakland | CA | 94612 | 1 |
| 756-30-7391 | Karsen | Livia | 415 534-9219 | 5720 McAuley St. | Oakland | CA | 94609 | 1 |
| 807-91-6654 | Panteley | Sylvia | 301 946-8853 | 1956 Arlington Pl. | Rockville | MD | 20853 | 1 |
| 846-92-7186 | Hunter | Sheryl | 415 836-7128 | 3410 Blonde St. | Palo Alto | CA | 94301 | 1 |
| 893-72-1158 | McBadden | Heather | 707 448-4982 | 301 Putnam | Vacaville | CA | 95688 | 0 |
| 899-46-2035 | Ringer | Anne | 801 826-0752 | 67 Seventh Av. | Salt Lake City | UT | 84152 | 1 |
| 998-72-3567 | Ringer | Albert | 801 826-0752 | 67 Seventh Av. | Salt Lake City | UT | 84152 | 1 |
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
23 rows in set (0.00 sec)
- 在SELECT语句中指定所有字段
mysql> SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract FROM authors;
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
| au_id | au_lname | au_fname | phone | address | city | state | zip | contract |
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
| 172-32-1176 | White | Johnson | 408 496-7223 | 10932 Bigge Rd. | Menlo Park | CA | 94025 | 1 |
| 213-46-8915 | Green | Marjorie | 415 986-7020 | 309 63rd St. #411 | Oakland | CA | 94618 | 1 |
| 238-95-7766 | Carson | Cheryl | 415 548-7723 | 589 Darwin Ln. | Berkeley | CA | 94705 | 1 |
| 267-41-2394 | O'Leary | Michael | 408 286-2428 | 22 Cleveland Av. #14 | San Jose | CA | 95128 | 1 |
| 274-80-9391 | Straight | Dean | 415 834-2919 | 5420 College Av. | Oakland | CA | 94609 | 1 |
| 341-22-1782 | Smith | Meander | 913 843-0462 | 10 Mississippi Dr. | Lawrence | KS | 66044 | 0 |
| 409-56-7008 | Bennet | Abraham | 415 658-9932 | 6223 Bateman St. | Berkeley | CA | 94705 | 1 |
| 427-17-2319 | Dull | Ann | 415 836-7128 | 3410 Blonde St. | Palo Alto | CA | 94301 | 1 |
| 472-27-2349 | Gringlesby | Burt | 707 938-6445 | PO Box 792 | Covelo | CA | 95428 | 1 |
| 486-29-1786 | Locksley | Charlene | 415 585-4620 | 18 Broadway Av. | San Francisco | CA | 94130 | 1 |
| 527-72-3246 | Greene | Morningstar | 615 297-2723 | 22 Graybar House Rd. | Nashville | TN | 37215 | 0 |
| 648-92-1872 | Blotchet-Halls | Reginald | 503 745-6402 | 55 Hillsdale Bl. | Corvallis | OR | 97330 | 1 |
| 672-71-3249 | Yokomoto | Akiko | 415 935-4228 | 3 Silver Ct. | Walnut Creek | CA | 94595 | 1 |
| 712-45-1867 | del Castillo | Innes | 615 996-8275 | 2286 Cram Pl. #86 | Ann Arbor | MI | 48105 | 1 |
| 722-51-5454 | DeFrance | Michel | 219 547-9982 | 3 Balding Pl. | Gary | IN | 46403 | 1 |
| 724-08-9931 | Stringer | Dirk | 415 843-2991 | 5420 Telegraph Av. | Oakland | CA | 94609 | 0 |
| 724-80-9391 | MacFeather | Stearns | 415 354-7128 | 44 Upland Hts. | Oakland | CA | 94612 | 1 |
| 756-30-7391 | Karsen | Livia | 415 534-9219 | 5720 McAuley St. | Oakland | CA | 94609 | 1 |
| 807-91-6654 | Panteley | Sylvia | 301 946-8853 | 1956 Arlington Pl. | Rockville | MD | 20853 | 1 |
| 846-92-7186 | Hunter | Sheryl | 415 836-7128 | 3410 Blonde St. | Palo Alto | CA | 94301 | 1 |
| 893-72-1158 | McBadden | Heather | 707 448-4982 | 301 Putnam | Vacaville | CA | 95688 | 0 |
| 899-46-2035 | Ringer | Anne | 801 826-0752 | 67 Seventh Av. | Salt Lake City | UT | 84152 | 1 |
| 998-72-3567 | Ringer | Albert | 801 826-0752 | 67 Seventh Av. | Salt Lake City | UT | 84152 | 1 |
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
23 rows in set (0.00 sec)
2. 查询指定字段
- 查询单个字段
语法格式：
SELECT <字段> FROM <表名>;
mysql> SELECT au_id FROM authors;
+-------------+
| au_id |
+-------------+
| 172-32-1176 |
| 213-46-8915 |
| 238-95-7766 |
| 267-41-2394 |
| 274-80-9391 |
| 341-22-1782 |
| 409-56-7008 |
| 427-17-2319 |
| 472-27-2349 |
| 486-29-1786 |
| 527-72-3246 |
| 648-92-1872 |
| 672-71-3249 |
| 712-45-1867 |
| 722-51-5454 |
| 724-08-9931 |
| 724-80-9391 |
| 756-30-7391 |
| 807-91-6654 |
| 846-92-7186 |
| 893-72-1158 |
| 899-46-2035 |
| 998-72-3567 |
+-------------+
23 rows in set (0.00 sec)
- 查询多个字段
语法格式：
SELECT 字段名1, 字段名2, 字段名3, ... , 字段名n FROM 表名;
mysql> SELECT au_id, au_lname, au_fname FROM authors;
+-------------+----------------+-------------+
| au_id | au_lname | au_fname |
+-------------+----------------+-------------+
| 172-32-1176 | White | Johnson |
| 213-46-8915 | Green | Marjorie |
| 238-95-7766 | Carson | Cheryl |
| 267-41-2394 | O'Leary | Michael |
| 274-80-9391 | Straight | Dean |
| 341-22-1782 | Smith | Meander |
| 409-56-7008 | Bennet | Abraham |
| 427-17-2319 | Dull | Ann |
| 472-27-2349 | Gringlesby | Burt |
| 486-29-1786 | Locksley | Charlene |
| 527-72-3246 | Greene | Morningstar |
| 648-92-1872 | Blotchet-Halls | Reginald |
| 672-71-3249 | Yokomoto | Akiko |
| 712-45-1867 | del Castillo | Innes |
| 722-51-5454 | DeFrance | Michel |
| 724-08-9931 | Stringer | Dirk |
| 724-80-9391 | MacFeather | Stearns |
| 756-30-7391 | Karsen | Livia |
| 807-91-6654 | Panteley | Sylvia |
| 846-92-7186 | Hunter | Sheryl |
| 893-72-1158 | McBadden | Heather |
| 899-46-2035 | Ringer | Anne |
| 998-72-3567 | Ringer | Albert |
+-------------+----------------+-------------+
23 rows in set (0.00 sec)
3. 查询指定记录
在SELECT语句中，通过WHERE字句可以对数据进行筛选过滤。
语法格式：
SELECT 字段名1, 字段名2, ... , 字段名n
FROM 表名
WHERE 查询条件
//筛选出au_id="172-32-1176"的记录
mysql> SELECT au_id, au_lname
-> FROM authors
-> WHERE au_id="172-32-1176";
+-------------+----------+
| au_id | au_lname |
+-------------+----------+
| 172-32-1176 | White |
+-------------+----------+
1 row in set (0.00 sec)
//筛选出au_lname="Karsen"的记录
mysql> SELECT au_id, au_lname
-> FROM authors
-> WHERE au_lname="Karsen";
+-------------+----------+
| au_id | au_lname |
+-------------+----------+
| 756-30-7391 | Karsen |
+-------------+----------+
1 row in set (0.00 sec)
//筛选出contract>0的记录
mysql> SELECT au_id, au_lname, au_fname, contract
-> FROM authors
-> WHERE contract>0;
+-------------+----------------+----------+----------+
| au_id | au_lname | au_fname | contract |
+-------------+----------------+----------+----------+
| 172-32-1176 | White | Johnson | 1 |
| 213-46-8915 | Green | Marjorie | 1 |
| 238-95-7766 | Carson | Cheryl | 1 |
| 267-41-2394 | O'Leary | Michael | 1 |
| 274-80-9391 | Straight | Dean | 1 |
| 409-56-7008 | Bennet | Abraham | 1 |
| 427-17-2319 | Dull | Ann | 1 |
| 472-27-2349 | Gringlesby | Burt | 1 |
| 486-29-1786 | Locksley | Charlene | 1 |
| 648-92-1872 | Blotchet-Halls | Reginald | 1 |
| 672-71-3249 | Yokomoto | Akiko | 1 |
| 712-45-1867 | del Castillo | Innes | 1 |
| 722-51-5454 | DeFrance | Michel | 1 |
| 724-80-9391 | MacFeather | Stearns | 1 |
| 756-30-7391 | Karsen | Livia | 1 |
| 807-91-6654 | Panteley | Sylvia | 1 |
| 846-92-7186 | Hunter | Sheryl | 1 |
| 899-46-2035 | Ringer | Anne | 1 |
| 998-72-3567 | Ringer | Albert | 1 |
+-------------+----------------+----------+----------+
19 rows in set (0.00 sec)
4. 带IN关键字的查询
IN操作符是用来查询满足指定范围的条件的记录，使用IN操作符，将所有的检索条件用括号括起来，检索条件之间使用逗号分隔，并且只要满足条件范围内的一个值即为匹配项。
语法格式：
SELECT 条件名1, 条件名2, ...,条件名n
FROM 表名
WHERE au_id IN (条件1, 条件2, ..., 条件n);
mysql> SELECT au_id, au_lname, au_fname
-> FROM authors
-> WHERE au_id IN ("172-32-1176", "23-46-8915");
+-------------+----------+----------+
| au_id | au_lname | au_fname |
+-------------+----------+----------+
| 172-32-1176 | White | Johnson |
+-------------+----------+----------+
1 row in set (0.01 sec)
//还可以使用NOT IN
mysql> SELECT au_id, au_lname, au_fname
-> FROM authors
-> WHERE au_id NOT IN ("172-32-1176", "23-46-8915");
+-------------+----------------+-------------+
| au_id | au_lname | au_fname |
+-------------+----------------+-------------+
| 213-46-8915 | Green | Marjorie |
| 238-95-7766 | Carson | Cheryl |
| 267-41-2394 | O'Leary | Michael |
| 274-80-9391 | Straight | Dean |
| 341-22-1782 | Smith | Meander |
| 409-56-7008 | Bennet | Abraham |
| 427-17-2319 | Dull | Ann |
| 472-27-2349 | Gringlesby | Burt |
| 486-29-1786 | Locksley | Charlene |
| 527-72-3246 | Greene | Morningstar |
| 648-92-1872 | Blotchet-Halls | Reginald |
| 672-71-3249 | Yokomoto | Akiko |
| 712-45-1867 | del Castillo | Innes |
| 722-51-5454 | DeFrance | Michel |
| 724-08-9931 | Stringer | Dirk |
| 724-80-9391 | MacFeather | Stearns |
| 756-30-7391 | Karsen | Livia |
| 807-91-6654 | Panteley | Sylvia |
| 846-92-7186 | Hunter | Sheryl |
| 893-72-1158 | McBadden | Heather |
| 899-46-2035 | Ringer | Anne |
| 998-72-3567 | Ringer | Albert |
+-------------+----------------+-------------+
22 rows in set (0.00 sec)
5. 带 BETWEEN AND 的范围查询
BETWEEN AND 用来查询有个范围的值，该操作符需要两个参数，及范围的开始值和结束值，如果字段值满足指定范围的查询条件，则这些记录被返回。
//查询contract从0到1的au_id、au_lname、au_fname
mysql> SELECT au_id, au_lname, au_fname, contract
-> FROM authors
-> WHERE contract BETWEEN 0 AND 1;
+-------------+----------------+-------------+----------+
| au_id | au_lname | au_fname | contract |
+-------------+----------------+-------------+----------+
| 172-32-1176 | White | Johnson | 1 |
| 213-46-8915 | Green | Marjorie | 1 |
| 238-95-7766 | Carson | Cheryl | 1 |
| 267-41-2394 | O'Leary | Michael | 1 |
| 274-80-9391 | Straight | Dean | 1 |
| 341-22-1782 | Smith | Meander | 0 |
| 409-56-7008 | Bennet | Abraham | 1 |
| 427-17-2319 | Dull | Ann | 1 |
| 472-27-2349 | Gringlesby | Burt | 1 |
| 486-29-1786 | Locksley | Charlene | 1 |
| 527-72-3246 | Greene | Morningstar | 0 |
| 648-92-1872 | Blotchet-Halls | Reginald | 1 |
| 672-71-3249 | Yokomoto | Akiko | 1 |
| 712-45-1867 | del Castillo | Innes | 1 |
| 722-51-5454 | DeFrance | Michel | 1 |
| 724-08-9931 | Stringer | Dirk | 0 |
| 724-80-9391 | MacFeather | Stearns | 1 |
| 756-30-7391 | Karsen | Livia | 1 |
| 807-91-6654 | Panteley | Sylvia | 1 |
| 846-92-7186 | Hunter | Sheryl | 1 |
| 893-72-1158 | McBadden | Heather | 0 |
| 899-46-2035 | Ringer | Anne | 1 |
| 998-72-3567 | Ringer | Albert | 1 |
+-------------+----------------+-------------+----------+
23 rows in set (0.00 sec)
6. 带LIKE的字符匹配查询
- 百分比通配符‘%’，匹配任意长度的字符，甚至包括零字符。
//%可以在任意位置，在不同位置
mysql> SELECT au_id, au_lname, au_fname FROM authors WHERE au_id LIKE '7%';
+-------------+--------------+----------+
| au_id | au_lname | au_fname |
+-------------+--------------+----------+
| 712-45-1867 | del Castillo | Innes |
| 722-51-5454 | DeFrance | Michel |
| 724-08-9931 | Stringer | Dirk |
| 724-80-9391 | MacFeather | Stearns |
| 756-30-7391 | Karsen | Livia |
+-------------+--------------+----------+
5 rows in set (0.00 sec)
mysql> SELECT au_id, au_lname, au_fname FROM authors WHERE au_id LIKE '%7';
+-------------+--------------+----------+
| au_id | au_lname | au_fname |
+-------------+--------------+----------+
| 712-45-1867 | del Castillo | Innes |
| 998-72-3567 | Ringer | Albert |
+-------------+--------------+----------+
2 rows in set (0.00 sec)
mysql> SELECT au_id, au_lname, au_fname FROM authors WHERE au_id LIKE '7%7';
+-------------+--------------+----------+
| au_id | au_lname | au_fname |
+-------------+--------------+----------+
| 712-45-1867 | del Castillo | Innes |
+-------------+--------------+----------+
1 row in set (0.00 sec)
- 下划线通配符‘_’，一次只能任意匹配一个字符。
mysql> SELECT au_id, au_lname, au_fname FROM authors WHERE au_lname LIKE 'D_______';
+-------------+----------+----------+
| au_id | au_lname | au_fname |
+-------------+----------+----------+
| 722-51-5454 | DeFrance | Michel |
+-------------+----------+----------+
1 row in set (0.01 sec)
mysql> SELECT au_id, au_lname, au_fname FROM authors WHERE au_lname LIKE '_____r';
+-------------+----------+----------+
| au_id | au_lname | au_fname |
+-------------+----------+----------+
| 846-92-7186 | Hunter | Sheryl |
| 899-46-2035 | Ringer | Anne |
| 998-72-3567 | Ringer | Albert |
+-------------+----------+----------+
3 rows in set (0.00 sec)
7. 查询空值
空值，不同于0和空字符串，他一般表示数据未知、不适用或将在未来填入数据。
语法结构：SELECT 字段1, 字段2 FROM 表名1, 表名2 WHERE 字段名 IS NULL;
mysql> SELECT au_id, au_fname, au_lname FROM authors WHERE au_id IS NULL;
Empty set (0.00 sec)
//若要判断不是空值，则可以加上NOT
mysql> SELECT au_id, au_lname, au_fname FROM authors WHERE au_lname IS NOT NULL;
+-------------+----------------+-------------+
| au_id | au_lname | au_fname |
+-------------+----------------+-------------+
| 172-32-1176 | White | Johnson |
| 213-46-8915 | Green | Marjorie |
| 238-95-7766 | Carson | Cheryl |
| 267-41-2394 | O'Leary | Michael |
| 274-80-9391 | Straight | Dean |
| 341-22-1782 | Smith | Meander |
| 409-56-7008 | Bennet | Abraham |
| 427-17-2319 | Dull | Ann |
| 472-27-2349 | Gringlesby | Burt |
| 486-29-1786 | Locksley | Charlene |
| 527-72-3246 | Greene | Morningstar |
| 648-92-1872 | Blotchet-Halls | Reginald |
| 672-71-3249 | Yokomoto | Akiko |
| 712-45-1867 | del Castillo | Innes |
| 722-51-5454 | DeFrance | Michel |
| 724-08-9931 | Stringer | Dirk |
| 724-80-9391 | MacFeather | Stearns |
| 756-30-7391 | Karsen | Livia |
| 807-91-6654 | Panteley | Sylvia |
| 846-92-7186 | Hunter | Sheryl |
| 893-72-1158 | McBadden | Heather |
| 899-46-2035 | Ringer | Anne |
| 998-72-3567 | Ringer | Albert |
+-------------+----------------+-------------+
23 rows in set (0.00 sec)
8. 带AND的多条件查询
当限制条件不只有两个时，可以添加多个AND。
语法结构：SELECT 字段1, 字段2 FROM 表名1, 表名2 WHERE 条件1 AND 条件2 AND 条件3;
mysql> SELECT au_id, au_lname, au_fname FROM authors WHERE au_id LIKE '8%';
+-------------+----------+----------+
| au_id | au_lname | au_fname |
+-------------+----------+----------+
| 807-91-6654 | Panteley | Sylvia |
| 846-92-7186 | Hunter | Sheryl |
| 893-72-1158 | McBadden | Heather |
| 899-46-2035 | Ringer | Anne |
+-------------+----------+----------+
4 rows in set (0.00 sec)
mysql> SELECT au_id, au_lname, au_fname FROM authors WHERE au_id LIKE '8%' AND au_fname LIKE 'S%';
+-------------+----------+----------+
| au_id | au_lname | au_fname |
+-------------+----------+----------+
| 807-91-6654 | Panteley | Sylvia |
| 846-92-7186 | Hunter | Sheryl |
+-------------+----------+----------+
2 rows in set (0.00 sec)
mysql> SELECT au_id, au_lname, au_fname FROM authors WHERE au_id LIKE '8%' AND au_fname LIKE 'S%' AND au_lname LIKE 'P%';
+-------------+----------+----------+
| au_id | au_lname | au_fname |
+-------------+----------+----------+
| 807-91-6654 | Panteley | Sylvia |
+-------------+----------+----------+
1 row in set (0.00 sec)
9. 带OR的多条件查询
语法结构：SELECT 字段1, 字段2 FROM 表名1, 表名2 WHERE 条件1 OR 条件2 OR 条件3;
mysql> SELECT au_id, au_lname, au_fname, state FROM authors WHERE state='CA' OR state='ks';
+-------------+------------+----------+-------+
| au_id | au_lname | au_fname | state |
+-------------+------------+----------+-------+
| 172-32-1176 | White | Johnson | CA |
| 213-46-8915 | Green | Marjorie | CA |
| 238-95-7766 | Carson | Cheryl | CA |
| 267-41-2394 | O'Leary | Michael | CA |
| 274-80-9391 | Straight | Dean | CA |
| 341-22-1782 | Smith | Meander | KS |
| 409-56-7008 | Bennet | Abraham | CA |
| 427-17-2319 | Dull | Ann | CA |
| 472-27-2349 | Gringlesby | Burt | CA |
| 486-29-1786 | Locksley | Charlene | CA |
| 672-71-3249 | Yokomoto | Akiko | CA |
| 724-08-9931 | Stringer | Dirk | CA |
| 724-80-9391 | MacFeather | Stearns | CA |
| 756-30-7391 | Karsen | Livia | CA |
| 846-92-7186 | Hunter | Sheryl | CA |
| 893-72-1158 | McBadden | Heather | CA |
+-------------+------------+----------+-------+
16 rows in set (0.00 sec)
//于IN有相同的作用，但是IN的更简洁，更快，还可以用更复杂的嵌套查询
mysql> SELECT au_id, au_lname, au_fname, state FROM authors WHERE state IN ('CA','KS');
+-------------+------------+----------+-------+
| au_id | au_lname | au_fname | state |
+-------------+------------+----------+-------+
| 172-32-1176 | White | Johnson | CA |
| 213-46-8915 | Green | Marjorie | CA |
| 238-95-7766 | Carson | Cheryl | CA |
| 267-41-2394 | O'Leary | Michael | CA |
| 274-80-9391 | Straight | Dean | CA |
| 341-22-1782 | Smith | Meander | KS |
| 409-56-7008 | Bennet | Abraham | CA |
| 427-17-2319 | Dull | Ann | CA |
| 472-27-2349 | Gringlesby | Burt | CA |
| 486-29-1786 | Locksley | Charlene | CA |
| 672-71-3249 | Yokomoto | Akiko | CA |
| 724-08-9931 | Stringer | Dirk | CA |
| 724-80-9391 | MacFeather | Stearns | CA |
| 756-30-7391 | Karsen | Livia | CA |
| 846-92-7186 | Hunter | Sheryl | CA |
| 893-72-1158 | McBadden | Heather | CA |
+-------------+------------+----------+-------+
16 rows in set (0.00 sec)
10. 查询结果不重复
使用DISTINCT关键字
语法结构：SELECT DISTINCT 字段名 FROM 表名;
mysql> SELECT state FROM authors;
+-------+
| state |
+-------+
| CA |
| CA |
| CA |
| CA |
| CA |
| KS |
| CA |
| CA |
| CA |
| CA |
| TN |
| OR |
| CA |
| MI |
| IN |
| CA |
| CA |
| CA |
| MD |
| CA |
| CA |
| UT |
| UT |
+-------+
23 rows in set (0.00 sec)
mysql> SELECT DISTINCT state FROM authors;
+-------+
| state |
+-------+
| CA |
| KS |
| TN |
| OR |
| MI |
| IN |
| MD |
| UT |
+-------+
8 rows in set (0.00 sec)
11. 对查询的结果升序
使用关键字ORDER BY子句对查询的结果进行排序。
- 单列排序
语法结构：SELECT 字段名 FROM 表名 ORDER BY 表名1, 表名2 ASC(默认为正序，ASC也可以不写);
mysql> SELECT au_id FROM authors ORDER BY au_id;
+-------------+
| au_id |
+-------------+
| 172-32-1176 |
| 213-46-8915 |
| 238-95-7766 |
| 267-41-2394 |
| 274-80-9391 |
| 341-22-1782 |
| 409-56-7008 |
| 427-17-2319 |
| 472-27-2349 |
| 486-29-1786 |
| 527-72-3246 |
| 648-92-1872 |
| 672-71-3249 |
| 712-45-1867 |
| 722-51-5454 |
| 724-08-9931 |
| 724-80-9391 |
| 756-30-7391 |
| 807-91-6654 |
| 846-92-7186 |
| 893-72-1158 |
| 899-46-2035 |
| 998-72-3567 |
+-------------+
23 rows in set (0.00 sec)
- 多列排序
对多列排序时，权重最大的是第一列，其次是第二列，以此类推，因此，只有第一列相同时，才会根据第二列来排序。
mysql> SELECT au_id, au_lname, au_fname FROM authors ORDER BY au_id, au_fname, au_lname;
+-------------+----------------+-------------+
| au_id | au_lname | au_fname |
+-------------+----------------+-------------+
| 172-32-1176 | White | Johnson |
| 213-46-8915 | Green | Marjorie |
| 238-95-7766 | Carson | Cheryl |
| 267-41-2394 | O'Leary | Michael |
| 274-80-9391 | Straight | Dean |
| 341-22-1782 | Smith | Meander |
| 409-56-7008 | Bennet | Abraham |
| 427-17-2319 | Dull | Ann |
| 472-27-2349 | Gringlesby | Burt |
| 486-29-1786 | Locksley | Charlene |
| 527-72-3246 | Greene | Morningstar |
| 648-92-1872 | Blotchet-Halls | Reginald |
| 672-71-3249 | Yokomoto | Akiko |
| 712-45-1867 | del Castillo | Innes |
| 722-51-5454 | DeFrance | Michel |
| 724-08-9931 | Stringer | Dirk |
| 724-80-9391 | MacFeather | Stearns |
| 756-30-7391 | Karsen | Livia |
| 807-91-6654 | Panteley | Sylvia |
| 846-92-7186 | Hunter | Sheryl |
| 893-72-1158 | McBadden | Heather |
| 899-46-2035 | Ringer | Anne |
| 998-72-3567 | Ringer | Albert |
+-------------+----------------+-------------+
23 rows in set (0.00 sec)
- 指定方向排序
使用逆序，对多列进行逆序时，要在每一列后加DESC，只在最后一列加DESC就只对最后一列进行逆序排列。
mysql> SELECT au_id FROM authors ORDER BY au_id DESC;
+-------------+
| au_id |
+-------------+
| 998-72-3567 |
| 899-46-2035 |
| 893-72-1158 |
| 846-92-7186 |
| 807-91-6654 |
| 756-30-7391 |
| 724-80-9391 |
| 724-08-9931 |
| 722-51-5454 |
| 712-45-1867 |
| 672-71-3249 |
| 648-92-1872 |
| 527-72-3246 |
| 486-29-1786 |
| 472-27-2349 |
| 427-17-2319 |
| 409-56-7008 |
| 341-22-1782 |
| 274-80-9391 |
| 267-41-2394 |
| 238-95-7766 |
| 213-46-8915 |
| 172-32-1176 |
+-------------+
23 rows in set (0.00 sec)
//还可以以au_id逆序，在以au_fname正序进行排序
mysql> SELECT au_id, au_fname FROM authors ORDER BY au_id DESC, au_fname;
+-------------+-------------+
| au_id | au_fname |
+-------------+-------------+
| 998-72-3567 | Albert |
| 899-46-2035 | Anne |
| 893-72-1158 | Heather |
| 846-92-7186 | Sheryl |
| 807-91-6654 | Sylvia |
| 756-30-7391 | Livia |
| 724-80-9391 | Stearns |
| 724-08-9931 | Dirk |
| 722-51-5454 | Michel |
| 712-45-1867 | Innes |
| 672-71-3249 | Akiko |
| 648-92-1872 | Reginald |
| 527-72-3246 | Morningstar |
| 486-29-1786 | Charlene |
| 472-27-2349 | Burt |
| 427-17-2319 | Ann |
| 409-56-7008 | Abraham |
| 341-22-1782 | Meander |
| 274-80-9391 | Dean |
| 267-41-2394 | Michael |
| 238-95-7766 | Cheryl |
| 213-46-8915 | Marjorie |
| 172-32-1176 | Johnson |
+-------------+-------------+
23 rows in set (0.00 sec)
12. 分组查询
分组查询是按照一个或多个字段你进行分组，使用GROUYP BY关键字对数据进行分组。
语法结构：SELECT 字段1, 字段2 AS 字段名3 FROM 表名1, 表名2 GROUP BY 字段名 HAVING 条件1, 条件2;
- 创建分组
语法结构：SELECT 字段1, 字段2 AS 字段名3 FROM 表名1, 表名2 GROUP BY 字段名;
//COUNT(*) AS StorStor由COUNT(*)计算得出，COUNT()函数是计算总数，计算对象就是GROUP BY 后面字段，如6380有2个，而7067有4个，GROUP BY stor_id表示子句按照stor_id排序并对数据进行分组且GROUP BY后面只有一个字段。
mysql> SELECT stor_id , ord_date, COUNT(*) AS Stor FROM sales GROUP BY stor_id;
+---------+---------------------+------+
| stor_id | ord_date | Stor |
+---------+---------------------+------+
| 6380 | 1994-09-14 00:00:00 | 2 |
| 7066 | 1993-05-24 00:00:00 | 2 |
| 7067 | 1994-09-14 00:00:00 | 4 |
| 7131 | 1994-09-14 00:00:00 | 6 |
| 7896 | 1993-10-28 00:00:00 | 3 |
| 8042 | 1994-09-14 00:00:00 | 4 |
+---------+---------------------+------+
6 rows in set (0.00 sec)
mysql> SELECT stor_id , ord_date, COUNT(*) AS Stor FROM sales GROUP BY ord_date;
+---------+---------------------+------+
| stor_id | ord_date | Stor |
+---------+---------------------+------+
| 6380 | 1994-09-14 00:00:00 | 6 |
| 6380 | 1994-09-13 00:00:00 | 2 |
| 7066 | 1993-05-24 00:00:00 | 1 |
| 7067 | 1992-06-15 00:00:00 | 3 |
| 7131 | 1993-05-29 00:00:00 | 4 |
| 7896 | 1993-10-28 00:00:00 | 1 |
| 7896 | 1993-12-12 00:00:00 | 1 |
| 7896 | 1993-02-21 00:00:00 | 1 |
| 8042 | 1993-03-11 00:00:00 | 1 |
| 8042 | 1993-05-22 00:00:00 | 1 |
+---------+---------------------+------+
10 rows in set (0.00 sec)
mysql> SELECT stor_id , ord_date, COUNT(*) AS Stor FROM sales GROUP BY ord_date, sales;
ERROR 1054 (42S22): Unknown column 'sales' in 'group statement'
//使用GROUP_CONCAT()函数，将分组中的各个字段显示出来
mysql> SELECT stor_id ,ord_num, GROUP_CONCAT(title_id) AS Stor FROM sales GROUP BY stor_id;
+---------+----------+-------------------------------------------+
| stor_id | ord_num | Stor |
+---------+----------+-------------------------------------------+
| 6380 | 6871 | BU1032,PS2091 |
| 7066 | A2976 | PC8888,PS2091 |
| 7067 | D4482 | PS2091,TC3218,TC4203,TC7777 |
| 7131 | N914008 | PS2091,MC3021,PS1372,PS2106,PS3333,PS7777 |
| 7896 | QQ2299 | BU7832,MC2222,BU2075 |
| 8042 | 423LL922 | MC3021,BU1032,BU1111,PC1035 |
+---------+----------+-------------------------------------------+
6 rows in set (0.00 sec)
- 使用HAVING过滤分组
HAVING 和 WHERE 之间的区别是HAVING过滤的是分组后的数据，而WHERE过滤的是分组前的数据。
mysql> SELECT stor_id, ord_num, GROUP_CONCAT(title_id) AS Stor
-> FROM sales
-> GROUP BY stor_id HAVING stor_id LIKE '7%';
+---------+---------+-------------------------------------------+
| stor_id | ord_num | Stor |
+---------+---------+-------------------------------------------+
| 7066 | A2976 | PC8888,PS2091 |
| 7067 | D4482 | PS2091,TC3218,TC4203,TC7777 |
| 7131 | N914008 | PS2091,MC3021,PS1372,PS2106,PS3333,PS7777 |
| 7896 | QQ2299 | BU7832,MC2222,BU2075 |
+---------+---------+-------------------------------------------+
4 rows in set (0.00 sec)
- 在GROUP BY 子句中使用WITH ROLLUP
使用WITH ROLLUP关键字，在所有记录后面添加一条记录，该记录是查询出的记录的总和，及统计记录的数量。
mysql> SELECT stor_id, ord_num, GROUP_CONCAT(title_id) AS Stor
-> FROM sales
-> GROUP BY stor_id WITH ROLLUP;
+---------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| stor_id | ord_num | Stor |
+---------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
| 6380 | 6871 | BU1032,PS2091 |
| 7066 | A2976 | PC8888,PS2091 |
| 7067 | D4482 | PS2091,TC3218,TC4203,TC7777 |
| 7131 | N914008 | PS2091,MC3021,PS1372,PS2106,PS3333,PS7777 |
| 7896 | QQ2299 | BU7832,MC2222,BU2075 |
| 8042 | 423LL922 | MC3021,BU1032,BU1111,PC1035 |
| NULL | 423LL922 | BU1032,PS2091,PC8888,PS2091,PS2091,TC3218,TC4203,TC7777,PS2091,MC3021,PS1372,PS2106,PS3333,PS7777,BU7832,MC2222,BU2075,MC3021,BU1032,BU1111,PC1035 |
+---------+----------+----------------------------------------------------------------------------------------------------------------------------------------------------+
7 rows in set (0.06 sec)
mysql> SELECT stor_id, ord_num, COUNT(title_id) AS Stor
-> FROM sales
-> GROUP BY stor_id WITH ROLLUP;
+---------+----------+------+
| stor_id | ord_num | Stor |
+---------+----------+------+
| 6380 | 6871 | 2 |
| 7066 | A2976 | 2 |
| 7067 | D4482 | 4 |
| 7131 | N914008 | 6 |
| 7896 | QQ2299 | 3 |
| 8042 | 423LL922 | 4 |
| NULL | 423LL922 | 21 |
+---------+----------+------+
7 rows in set (0.00 sec)
- 多字段分组
还是权重，第一个的权重最大，先按第一个排，第一个相同，再按第二个排。
mysql> SELECT stor_id, ord_num, title_id
-> FROM sales
-> GROUP BY stor_id, title_id;
+---------+----------+----------+
| stor_id | ord_num | title_id |
+---------+----------+----------+
| 6380 | 6871 | BU1032 |
| 6380 | 722a | PS2091 |
| 7066 | A2976 | PC8888 |
| 7066 | QA7442.3 | PS2091 |
| 7067 | D4482 | PS2091 |
| 7067 | P2121 | TC3218 |
| 7067 | P2121 | TC4203 |
| 7067 | P2121 | TC7777 |
| 7131 | N914008 | PS2091 |
| 7131 | N914014 | MC3021 |
| 7131 | P3087a | PS1372 |
| 7131 | P3087a | PS2106 |
| 7131 | P3087a | PS3333 |
| 7131 | P3087a | PS7777 |
| 7896 | QQ2299 | BU7832 |
| 7896 | TQ456 | MC2222 |
| 7896 | X999 | BU2075 |
| 8042 | 423LL922 | MC3021 |
| 8042 | 423LL930 | BU1032 |
| 8042 | P723 | BU1111 |
| 8042 | QA879.1 | PC1035 |
+---------+----------+----------+
21 rows in set (0.00 sec)
- GROUP BY 和 ORDER BY 一起使用
GROUP BY 的排序优先级低于 ORDER BY 且 ORDER BY于WITH ROLLUP 冲突，不能一起使用。
mysql> SELECT stor_id, ord_num, title_id
-> FROM sales
-> GROUP BY stor_id, title_id
-> ORDER BY ord_num;
+---------+----------+----------+
| stor_id | ord_num | title_id |
+---------+----------+----------+
| 8042 | 423LL922 | MC3021 |
| 8042 | 423LL930 | BU1032 |
| 6380 | 6871 | BU1032 |
| 6380 | 722a | PS2091 |
| 7066 | A2976 | PC8888 |
| 7067 | D4482 | PS2091 |
| 7131 | N914008 | PS2091 |
| 7131 | N914014 | MC3021 |
| 7067 | P2121 | TC3218 |
| 7067 | P2121 | TC4203 |
| 7067 | P2121 | TC7777 |
| 7131 | P3087a | PS1372 |
| 7131 | P3087a | PS2106 |
| 7131 | P3087a | PS3333 |
| 7131 | P3087a | PS7777 |
| 8042 | P723 | BU1111 |
| 7066 | QA7442.3 | PS2091 |
| 8042 | QA879.1 | PC1035 |
| 7896 | QQ2299 | BU7832 |
| 7896 | TQ456 | MC2222 |
| 7896 | X999 | BU2075 |
+---------+----------+----------+
21 rows in set (0.00 sec)
mysql> SELECT stor_id, ord_num, title_id
-> FROM sales
-> GROUP BY stor_id, title_id
-> ORDER BY ord_num DESC;
+---------+----------+----------+
| stor_id | ord_num | title_id |
+---------+----------+----------+
| 7896 | X999 | BU2075 |
| 7896 | TQ456 | MC2222 |
| 7896 | QQ2299 | BU7832 |
| 8042 | QA879.1 | PC1035 |
| 7066 | QA7442.3 | PS2091 |
| 8042 | P723 | BU1111 |
| 7131 | P3087a | PS1372 |
| 7131 | P3087a | PS2106 |
| 7131 | P3087a | PS3333 |
| 7131 | P3087a | PS7777 |
| 7067 | P2121 | TC3218 |
| 7067 | P2121 | TC4203 |
| 7067 | P2121 | TC7777 |
| 7131 | N914014 | MC3021 |
| 7131 | N914008 | PS2091 |
| 7067 | D4482 | PS2091 |
| 7066 | A2976 | PC8888 |
| 6380 | 722a | PS2091 |
| 6380 | 6871 | BU1032 |
| 8042 | 423LL930 | BU1032 |
| 8042 | 423LL922 | MC3021 |
+---------+----------+----------+
21 rows in set (0.00 sec)
13. 使用LIMIT限制查询结果的数量
语法格式：LIMIT [位置偏移量,] 行数
mysql> SELECT * FROM authors;
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
| au_id | au_lname | au_fname | phone | address | city | state | zip | contract |
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
| 172-32-1176 | White | Johnson | 408 496-7223 | 10932 Bigge Rd. | Menlo Park | CA | 94025 | 1 |
| 213-46-8915 | Green | Marjorie | 415 986-7020 | 309 63rd St. #411 | Oakland | CA | 94618 | 1 |
| 238-95-7766 | Carson | Cheryl | 415 548-7723 | 589 Darwin Ln. | Berkeley | CA | 94705 | 1 |
| 267-41-2394 | O'Leary | Michael | 408 286-2428 | 22 Cleveland Av. #14 | San Jose | CA | 95128 | 1 |
| 274-80-9391 | Straight | Dean | 415 834-2919 | 5420 College Av. | Oakland | CA | 94609 | 1 |
| 341-22-1782 | Smith | Meander | 913 843-0462 | 10 Mississippi Dr. | Lawrence | KS | 66044 | 0 |
| 409-56-7008 | Bennet | Abraham | 415 658-9932 | 6223 Bateman St. | Berkeley | CA | 94705 | 1 |
| 427-17-2319 | Dull | Ann | 415 836-7128 | 3410 Blonde St. | Palo Alto | CA | 94301 | 1 |
| 472-27-2349 | Gringlesby | Burt | 707 938-6445 | PO Box 792 | Covelo | CA | 95428 | 1 |
| 486-29-1786 | Locksley | Charlene | 415 585-4620 | 18 Broadway Av. | San Francisco | CA | 94130 | 1 |
| 527-72-3246 | Greene | Morningstar | 615 297-2723 | 22 Graybar House Rd. | Nashville | TN | 37215 | 0 |
| 648-92-1872 | Blotchet-Halls | Reginald | 503 745-6402 | 55 Hillsdale Bl. | Corvallis | OR | 97330 | 1 |
| 672-71-3249 | Yokomoto | Akiko | 415 935-4228 | 3 Silver Ct. | Walnut Creek | CA | 94595 | 1 |
| 712-45-1867 | del Castillo | Innes | 615 996-8275 | 2286 Cram Pl. #86 | Ann Arbor | MI | 48105 | 1 |
| 722-51-5454 | DeFrance | Michel | 219 547-9982 | 3 Balding Pl. | Gary | IN | 46403 | 1 |
| 724-08-9931 | Stringer | Dirk | 415 843-2991 | 5420 Telegraph Av. | Oakland | CA | 94609 | 0 |
| 724-80-9391 | MacFeather | Stearns | 415 354-7128 | 44 Upland Hts. | Oakland | CA | 94612 | 1 |
| 756-30-7391 | Karsen | Livia | 415 534-9219 | 5720 McAuley St. | Oakland | CA | 94609 | 1 |
| 807-91-6654 | Panteley | Sylvia | 301 946-8853 | 1956 Arlington Pl. | Rockville | MD | 20853 | 1 |
| 846-92-7186 | Hunter | Sheryl | 415 836-7128 | 3410 Blonde St. | Palo Alto | CA | 94301 | 1 |
| 893-72-1158 | McBadden | Heather | 707 448-4982 | 301 Putnam | Vacaville | CA | 95688 | 0 |
| 899-46-2035 | Ringer | Anne | 801 826-0752 | 67 Seventh Av. | Salt Lake City | UT | 84152 | 1 |
| 998-72-3567 | Ringer | Albert | 801 826-0752 | 67 Seventh Av. | Salt Lake City | UT | 84152 | 1 |
+-------------+----------------+-------------+--------------+----------------------+----------------+-------+-------+----------+
23 rows in set (0.00 sec)
//偏移量为0，从第一行开始，行数为4，则为1-4行
mysql> SELECT * FROM authors LIMIT 4;
+-------------+----------+----------+--------------+----------------------+------------+-------+-------+----------+
| au_id | au_lname | au_fname | phone | address | city | state | zip | contract |
+-------------+----------+----------+--------------+----------------------+------------+-------+-------+----------+
| 172-32-1176 | White | Johnson | 408 496-7223 | 10932 Bigge Rd. | Menlo Park | CA | 94025 | 1 |
| 213-46-8915 | Green | Marjorie | 415 986-7020 | 309 63rd St. #411 | Oakland | CA | 94618 | 1 |
| 238-95-7766 | Carson | Cheryl | 415 548-7723 | 589 Darwin Ln. | Berkeley | CA | 94705 | 1 |
| 267-41-2394 | O'Leary | Michael | 408 286-2428 | 22 Cleveland Av. #14 | San Jose | CA | 95128 | 1 |
+-------------+----------+----------+--------------+----------------------+------------+-------+-------+----------+
4 rows in set (0.01 sec)
//偏移量为4，从第五行开始，行数为3，则为5-7行
mysql> SELECT * FROM authors LIMIT 4, 3;
+-------------+----------+----------+--------------+--------------------+----------+-------+-------+----------+
| au_id | au_lname | au_fname | phone | address | city | state | zip | contract |
+-------------+----------+----------+--------------+--------------------+----------+-------+-------+----------+
| 274-80-9391 | Straight | Dean | 415 834-2919 | 5420 College Av. | Oakland | CA | 94609 | 1 |
| 341-22-1782 | Smith | Meander | 913 843-0462 | 10 Mississippi Dr. | Lawrence | KS | 66044 | 0 |
| 409-56-7008 | Bennet | Abraham | 415 658-9932 | 6223 Bateman St. | Berkeley | CA | 94705 | 1 |
+-------------+----------+----------+--------------+--------------------+----------+-------+-------+----------+
3 rows in set (0.00 sec)
使用集合函数查询
| 函数 | 作用 |
|---|---|
| AVG() | 返回某列的平均值 |
| COUNT() | 返回某列的行数 |
| MAX() | 返回某列的最大值 |
| MIN() | 返回某列的最小值 |
| SUM() | 返回某列值的和 |
- COUNT()函数
函数统计数据表中包含的记录行的总数，或者根据查询结果返回列中包含的数据行数。其使用方法有两种： - COUNT(*)计算表中总的行数，不管某列是否有数值或者为空值。
- COUNT(字段名)计算指定列下总的行数，计算时将忽略空值的行。
mysql> SELECT COUNT(*) AS Total FROM authors;
+-------+
| Total |
+-------+
| 23 |
+-------+
1 row in set (0.00 sec)
//说明au_id中没有空值
mysql> SELECT COUNT(au_id) AS Total FROM authors;
+-------+
| Total |
+-------+
| 23 |
+-------+
1 row in set (0.00 sec)
- SUM()函数
SUM()函数十一个求总和的函数，返回指定列值的总和。
mysql> SELECT SUM(contract) AS items_total FROM authors WHERE contract = 1;
+-------------+
| items_total |
+-------------+
| 19 |
+-------------+
1 row in set (0.00 sec)
剩下的函数使用以上面两种函数相同，就不接着举例子了
连接查询
1. 内连接查询
内连接（INNER JOIN）使用比较运算符进行表间某些列数据的比较操作，并列出这些表中与连接条件相匹配的数据行，组合成新的纪录，也就是说，在内连接查询中，只有满足条件的记录才能出现在结构关系中。
mysql> SELECT * FROM sales;
+---------+----------+---------------------+------+------------+----------+
| stor_id | ord_num | ord_date | qty | payterms | title_id |
+---------+----------+---------------------+------+------------+----------+
| 6380 | 6871 | 1994-09-14 00:00:00 | 5 | Net 60 | BU1032 |
| 6380 | 722a | 1994-09-13 00:00:00 | 3 | Net 60 | PS2091 |
| 7066 | A2976 | 1993-05-24 00:00:00 | 50 | Net 30 | PC8888 |
| 7066 | QA7442.3 | 1994-09-13 00:00:00 | 75 | ON invoice | PS2091 |
| 7067 | D4482 | 1994-09-14 00:00:00 | 10 | Net 60 | PS2091 |
| 7067 | P2121 | 1992-06-15 00:00:00 | 40 | Net 30 | TC3218 |
| 7067 | P2121 | 1992-06-15 00:00:00 | 20 | Net 30 | TC4203 |
| 7067 | P2121 | 1992-06-15 00:00:00 | 20 | Net 30 | TC7777 |
| 7131 | N914008 | 1994-09-14 00:00:00 | 20 | Net 30 | PS2091 |
| 7131 | N914014 | 1994-09-14 00:00:00 | 25 | Net 30 | MC3021 |
| 7131 | P3087a | 1993-05-29 00:00:00 | 20 | Net 60 | PS1372 |
| 7131 | P3087a | 1993-05-29 00:00:00 | 25 | Net 60 | PS2106 |
| 7131 | P3087a | 1993-05-29 00:00:00 | 15 | Net 60 | PS3333 |
| 7131 | P3087a | 1993-05-29 00:00:00 | 25 | Net 60 | PS7777 |
| 7896 | QQ2299 | 1993-10-28 00:00:00 | 15 | Net 60 | BU7832 |
| 7896 | TQ456 | 1993-12-12 00:00:00 | 10 | Net 60 | MC2222 |
| 7896 | X999 | 1993-02-21 00:00:00 | 35 | ON invoice | BU2075 |
| 8042 | 423LL922 | 1994-09-14 00:00:00 | 15 | ON invoice | MC3021 |
| 8042 | 423LL930 | 1994-09-14 00:00:00 | 10 | ON invoice | BU1032 |
| 8042 | P723 | 1993-03-11 00:00:00 | 25 | Net 30 | BU1111 |
| 8042 | QA879.1 | 1993-05-22 00:00:00 | 30 | Net 30 | PC1035 |
+---------+----------+---------------------+------+------------+----------+
21 rows in set (0.00 sec)
mysql> SELECT * FROM stores;
+---------+--------------------------------------+---------------------+-----------+-------+-------+
| stor_id | stor_name | stor_address | city | state | zip |
+---------+--------------------------------------+---------------------+-----------+-------+-------+
| 6380 | Eric the Read Books | 788 Catamaugus Ave. | Seattle | WA | 98056 |
| 7066 | Barnum's | 567 Pasadena Ave. | Tustin | CA | 92789 |
| 7067 | News & Brews | 577 First St. | Los Gatos | CA | 96745 |
| 7131 | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way | Remulade | WA | 98014 |
| 7896 | Fricative Bookshop | 89 Madison St. | Fremont | CA | 90019 |
| 8042 | Bookbeat | 679 Carson St. | Portland | OR | 89076 |
+---------+--------------------------------------+---------------------+-----------+-------+-------+
6 rows in set (0.00 sec)
//由于stor_id 在两个表中都有，因此我们要指明那个表中的，但是两个表的stor_id都行，他们都是一样的。
mysql> SELECT sales.stor_id, ord_num, stor_name, stor_address
-> FROM sales, stores
-> WHERE sales.stor_id = stores.stor_id;
+---------+----------+--------------------------------------+---------------------+
| stor_id | ord_num | stor_name | stor_address |
+---------+----------+--------------------------------------+---------------------+
| 6380 | 6871 | Eric the Read Books | 788 Catamaugus Ave. |
| 6380 | 722a | Eric the Read Books | 788 Catamaugus Ave. |
| 7066 | A2976 | Barnum's | 567 Pasadena Ave. |
| 7066 | QA7442.3 | Barnum's | 567 Pasadena Ave. |
| 7067 | D4482 | News & Brews | 577 First St. |
| 7067 | P2121 | News & Brews | 577 First St. |
| 7067 | P2121 | News & Brews | 577 First St. |
| 7067 | P2121 | News & Brews | 577 First St. |
| 7131 | N914008 | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | N914014 | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | P3087a | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | P3087a | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | P3087a | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | P3087a | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7896 | QQ2299 | Fricative Bookshop | 89 Madison St. |
| 7896 | TQ456 | Fricative Bookshop | 89 Madison St. |
| 7896 | X999 | Fricative Bookshop | 89 Madison St. |
| 8042 | 423LL922 | Bookbeat | 679 Carson St. |
| 8042 | 423LL930 | Bookbeat | 679 Carson St. |
| 8042 | P723 | Bookbeat | 679 Carson St. |
| 8042 | QA879.1 | Bookbeat | 679 Carson St. |
+---------+----------+--------------------------------------+---------------------+
21 rows in set (0.01 sec)
接下来我们使用INNER JOIN的内连接查询方式,使用内连接是条件语句不能用WHERE了，要用ON语句，在内连接查询中ON比WHERE更准确和更效率。
mysql> SELECT sales.stor_id, ord_num, stor_name, stor_address
-> FROM sales INNER JOIN stores
-> ON sales.stor_id = stores.stor_id;
+---------+----------+--------------------------------------+---------------------+
| stor_id | ord_num | stor_name | stor_address |
+---------+----------+--------------------------------------+---------------------+
| 6380 | 6871 | Eric the Read Books | 788 Catamaugus Ave. |
| 6380 | 722a | Eric the Read Books | 788 Catamaugus Ave. |
| 7066 | A2976 | Barnum's | 567 Pasadena Ave. |
| 7066 | QA7442.3 | Barnum's | 567 Pasadena Ave. |
| 7067 | D4482 | News & Brews | 577 First St. |
| 7067 | P2121 | News & Brews | 577 First St. |
| 7067 | P2121 | News & Brews | 577 First St. |
| 7067 | P2121 | News & Brews | 577 First St. |
| 7131 | N914008 | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | N914014 | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | P3087a | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | P3087a | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | P3087a | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7131 | P3087a | Doc-U-Mat: Quality Laundry and Books | 24-A Avogadro Way |
| 7896 | QQ2299 | Fricative Bookshop | 89 Madison St. |
| 7896 | TQ456 | Fricative Bookshop | 89 Madison St. |
| 7896 | X999 | Fricative Bookshop | 89 Madison St. |
| 8042 | 423LL922 | Bookbeat | 679 Carson St. |
| 8042 | 423LL930 | Bookbeat | 679 Carson St. |
| 8042 | P723 | Bookbeat | 679 Carson St. |
| 8042 | QA879.1 | Bookbeat | 679 Carson St. |
+---------+----------+--------------------------------------+---------------------+
21 rows in set (0.00 sec)
2. 外连接查询
不仅想要满足条件的记录，而且我想要其中一个表或多个表的所有记录，即使他不满足条件，，而内连接查询，只能查询符合条件的记录。
- LEFT JOIN（左连接）：返回包括左表中的所有记录和右表中连接字段相等的记录（也就是满足条件的记录）
- RIGHT JOIN（左连接）：返回包括右表中的所有记录和左表中连接字段相等的记录（也就是满足条件的记录）
- 左连接
//左连接
//有NULL的地方就是没有满足条件的记录
mysql> SELECT titles.title_id, price , titleauthor.title_id
-> FROM titles LEFT OUTER JOIN titleauthor
-> ON titles.title_id = titleauthor.title_id;
+----------+-------+----------+
| title_id | price | title_id |
+----------+-------+----------+
| PS3333 | 20 | PS3333 |
| BU1032 | 20 | BU1032 |
| BU2075 | 3 | BU2075 |
| PC1035 | 23 | PC1035 |
| BU1111 | 12 | BU1111 |
| TC7777 | 15 | TC7777 |
| BU7832 | 20 | BU7832 |
| BU1032 | 20 | BU1032 |
| PC8888 | 20 | PC8888 |
| TC7777 | 15 | TC7777 |
| PC9999 | NULL | PC9999 |
| PS7777 | 8 | PS7777 |
| TC4203 | 12 | TC4203 |
| TC7777 | 15 | TC7777 |
| MC2222 | 20 | MC2222 |
| MC3021 | 3 | MC3021 |
| BU1111 | 12 | BU1111 |
| PS1372 | 22 | PS1372 |
| PS1372 | 22 | PS1372 |
| TC3218 | 21 | TC3218 |
| PC8888 | 20 | PC8888 |
| MC3021 | 3 | MC3021 |
| PS2091 | 11 | PS2091 |
| PS2091 | 11 | PS2091 |
| PS2106 | 7 | PS2106 |
| MC3026 | NULL | NULL |
+----------+-------+----------+
26 rows in set (0.00 sec)
- 右连接
//右连接
mysql> SELECT titles.title_id, price , titleauthor.title_id
-> FROM titleauthor RIGHT OUTER JOIN titles
-> ON titles.title_id = titleauthor.title_id;
+----------+-------+----------+
| title_id | price | title_id |
+----------+-------+----------+
| PS3333 | 20 | PS3333 |
| BU1032 | 20 | BU1032 |
| BU2075 | 3 | BU2075 |
| PC1035 | 23 | PC1035 |
| BU1111 | 12 | BU1111 |
| TC7777 | 15 | TC7777 |
| BU7832 | 20 | BU7832 |
| BU1032 | 20 | BU1032 |
| PC8888 | 20 | PC8888 |
| TC7777 | 15 | TC7777 |
| PC9999 | NULL | PC9999 |
| PS7777 | 8 | PS7777 |
| TC4203 | 12 | TC4203 |
| TC7777 | 15 | TC7777 |
| MC2222 | 20 | MC2222 |
| MC3021 | 3 | MC3021 |
| BU1111 | 12 | BU1111 |
| PS1372 | 22 | PS1372 |
| PS1372 | 22 | PS1372 |
| TC3218 | 21 | TC3218 |
| PC8888 | 20 | PC8888 |
| MC3021 | 3 | MC3021 |
| PS2091 | 11 | PS2091 |
| PS2091 | 11 | PS2091 |
| PS2106 | 7 | PS2106 |
| MC3026 | NULL | NULL |
+----------+-------+----------+
26 rows in set (0.00 sec)
- 复合条件连接
mysql> SELECT titles.title_id, price , titleauthor.title_id
-> FROM titleauthor RIGHT OUTER JOIN titles
-> ON titles.title_id = titleauthor.title_id AND titles.title_id = 'PS3333' OR titles.title_id = 'BU1111';
+----------+-------+----------+
| title_id | price | title_id |
+----------+-------+----------+
| BU1111 | 12 | PS3333 |
| PS3333 | 20 | PS3333 |
| BU1111 | 12 | BU1032 |
| BU1111 | 12 | BU2075 |
| BU1111 | 12 | PC1035 |
| BU1111 | 12 | BU1111 |
| BU1111 | 12 | TC7777 |
| BU1111 | 12 | BU7832 |
| BU1111 | 12 | BU1032 |
| BU1111 | 12 | PC8888 |
| BU1111 | 12 | TC7777 |
| BU1111 | 12 | PC9999 |
| BU1111 | 12 | PS7777 |
| BU1111 | 12 | TC4203 |
| BU1111 | 12 | TC7777 |
| BU1111 | 12 | MC2222 |
| BU1111 | 12 | MC3021 |
| BU1111 | 12 | BU1111 |
| BU1111 | 12 | PS1372 |
| BU1111 | 12 | PS1372 |
| BU1111 | 12 | TC3218 |
| BU1111 | 12 | PC8888 |
| BU1111 | 12 | MC3021 |
| BU1111 | 12 | PS2091 |
| BU1111 | 12 | PS2091 |
| BU1111 | 12 | PS2106 |
| BU1032 | 20 | NULL |
| BU2075 | 3 | NULL |
| BU7832 | 20 | NULL |
| MC2222 | 20 | NULL |
| MC3021 | 3 | NULL |
| MC3026 | NULL | NULL |
| PC1035 | 23 | NULL |
| PC8888 | 20 | NULL |
| PC9999 | NULL | NULL |
| PS1372 | 22 | NULL |
| PS2091 | 11 | NULL |
| PS2106 | 7 | NULL |
| PS7777 | 8 | NULL |
| TC3218 | 21 | NULL |
| TC4203 | 12 | NULL |
| TC7777 | 15 | NULL |
+----------+-------+----------+
42 rows in set (0.00 sec)
mysql> SELECT titles.title_id, price , titleauthor.title_id
-> FROM titleauthor RIGHT OUTER JOIN titles
-> ON titles.title_id = titleauthor.title_id AND titles.title_id = 'PS3333' OR titles.title_id = 'BU1111'
-> ORDER BY price;
+----------+-------+----------+
| title_id | price | title_id |
+----------+-------+----------+
| MC3026 | NULL | NULL |
| PC9999 | NULL | NULL |
| MC3021 | 3 | NULL |
| BU2075 | 3 | NULL |
| PS2106 | 7 | NULL |
| PS7777 | 8 | NULL |
| PS2091 | 11 | NULL |
| BU1111 | 12 | BU1111 |
| BU1111 | 12 | TC4203 |
| BU1111 | 12 | PC8888 |
| BU1111 | 12 | TC7777 |
| BU1111 | 12 | TC7777 |
| BU1111 | 12 | MC3021 |
| BU1111 | 12 | BU7832 |
| BU1111 | 12 | MC2222 |
| BU1111 | 12 | PS2091 |
| BU1111 | 12 | PS3333 |
| BU1111 | 12 | BU1032 |
| BU1111 | 12 | MC3021 |
| BU1111 | 12 | PS2091 |
| TC4203 | 12 | NULL |
| BU1111 | 12 | PC8888 |
| BU1111 | 12 | BU1111 |
| BU1111 | 12 | PS2106 |
| BU1111 | 12 | BU1032 |
| BU1111 | 12 | TC7777 |
| BU1111 | 12 | PS1372 |
| BU1111 | 12 | BU2075 |
| BU1111 | 12 | PC9999 |
| BU1111 | 12 | PS1372 |
| BU1111 | 12 | PC1035 |
| BU1111 | 12 | PS7777 |
| BU1111 | 12 | TC3218 |
| TC7777 | 15 | NULL |
| MC2222 | 20 | NULL |
| PS3333 | 20 | PS3333 |
| PC8888 | 20 | NULL |
| BU1032 | 20 | NULL |
| BU7832 | 20 | NULL |
| TC3218 | 21 | NULL |
| PS1372 | 22 | NULL |
| PC1035 | 23 | NULL |
+----------+-------+----------+
42 rows in set (0.00 sec)
子查询
1. 带ANY、SOME关键字的子查询
ANY和SOME关键字是同义词，表示满足其中任一条件，他们就允许创建一个表达式对子查询的返回值列表进行比较，只要满足内层子查询中的任何一个比较条件，就返回一个结果作为外层查询的条件
mysql> SELECT * FROM tbl1;
+------+
| num1 |
+------+
| 1 |
| 5 |
| 13 |
| 27 |
| 6 |
| 14 |
| 11 |
| 20 |
+------+
8 rows in set (0.00 sec)
mysql> SELECT * FROM tbl2;
+------+
| num2 |
+------+
| 60 |
| 15 |
| 70 |
| 25 |
+------+
4 rows in set (0.00 sec)
mysql> SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2);
+------+
| num1 |
+------+
| 27 |
| 20 |
+------+
2 rows in set (0.00 sec)
2. 带ALL关键字的子查询
与ANY不同，ALL从字面上就能看出，必须要满足内层查询全部条件。
mysql> SELECT num2 FROM tbl2 WHERE num2 > ALL (SELECT num1 FROM tbl1);
+------+
| num2 |
+------+
| 60 |
| 70 |
+------+
2 rows in set (0.00 sec)
3. 带EXISTS关键字的子查询
EXISTS关键字后面的参数是一个任意的子查询，系统对子查询进行运算以判断它是否返回行，如果至少返回一行，那么EXISTS的结果为true，此时外层查询语句将进行查询；如果子查询没有返回任何行，那么EXISTS返回的结果是false，此时外层的语句将不进行查询。
同时，NOT EXISTS也是可以使用的，效果与EXISTS相反。
mysql> SELECT * FROM titles WHERE EXISTS
-> (SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2));
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
| MC2222 | Silicon Valley Gastronomic Treats | mod_cook | 0877 | 20 | 0 | 12 | 2032 | Favorite recipes for quick, easy, and elegant meals. | 1991-06-09 00:00:00 |
| MC3021 | The Gourmet Microwave | mod_cook | 0877 | 3 | 15000 | 24 | 22246 | Traditional French gourmet recipes adapted for modern microwave cooking. | 1991-06-18 00:00:00 |
| MC3026 | The Psychology of Computer Cooking | UNDECIDED | 0877 | NULL | NULL | NULL | NULL | NULL | 2004-12-13 16:11:37 |
| PC1035 | But Is It User Friendly? | popular_comp | 1389 | 23 | 7000 | 16 | 8780 | A survey of software for the naive user, focusing on the 'friendliness' of each. | 1991-06-30 00:00:00 |
| PC8888 | Secrets of Silicon Valley | popular_comp | 1389 | 20 | 8000 | 10 | 4095 | Muckraking reporting on the world's largest computer hardware and software manufacturers. | 1994-06-12 00:00:00 |
| PC9999 | Net Etiquette | popular_comp | 1389 | NULL | NULL | NULL | NULL | A must-read for computer conferencing. | 2004-12-13 16:11:37 |
| PS1372 | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | psychology | 0877 | 22 | 7000 | 10 | 375 | A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. | 1991-10-21 00:00:00 |
| PS2091 | Is Anger the Enemy? | psychology | 0736 | 11 | 2275 | 12 | 2045 | Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. | 1991-06-15 00:00:00 |
| PS2106 | Life Without Fear | psychology | 0736 | 7 | 6000 | 10 | 111 | New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately. | 1991-10-05 00:00:00 |
| PS3333 | Prolonged Data Deprivation: Four Case Studies | psychology | 0736 | 20 | 2000 | 10 | 4072 | What happens when the data runs dry? Searching evaluations of information-shortage effects. | 1991-06-12 00:00:00 |
| PS7777 | Emotional Security: A New Algorithm | psychology | 0736 | 8 | 4000 | 10 | 3336 | Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized. | 1991-06-12 00:00:00 |
| TC3218 | Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean | trad_cook | 0877 | 21 | 7000 | 10 | 375 | Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend. | 1991-10-21 00:00:00 |
| TC4203 | Fifty Years in Buckingham Palace Kitchens | trad_cook | 0877 | 12 | 4000 | 14 | 15096 | More anecdotes from the Queen's favorite cook describing life among English royalty. Recipes, techniques, tender vignettes. | 1991-06-12 00:00:00 |
| TC7777 | Sushi, Anyone? | trad_cook | 0877 | 15 | 8000 | 10 | 4095 | Detailed instructions on how to make authentic Japanese sushi in your spare time. | 1991-06-12 00:00:00 |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
18 rows in set (0.00 sec)
mysql> SELECT * FROM titles WHERE EXISTS
-> (SELECT num1 FROM tbl1 WHERE num1 > ANY (SELECT num2 FROM tbl2))
-> AND price >5;
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
| MC2222 | Silicon Valley Gastronomic Treats | mod_cook | 0877 | 20 | 0 | 12 | 2032 | Favorite recipes for quick, easy, and elegant meals. | 1991-06-09 00:00:00 |
| PC1035 | But Is It User Friendly? | popular_comp | 1389 | 23 | 7000 | 16 | 8780 | A survey of software for the naive user, focusing on the 'friendliness' of each. | 1991-06-30 00:00:00 |
| PC8888 | Secrets of Silicon Valley | popular_comp | 1389 | 20 | 8000 | 10 | 4095 | Muckraking reporting on the world's largest computer hardware and software manufacturers. | 1994-06-12 00:00:00 |
| PS1372 | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | psychology | 0877 | 22 | 7000 | 10 | 375 | A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. | 1991-10-21 00:00:00 |
| PS2091 | Is Anger the Enemy? | psychology | 0736 | 11 | 2275 | 12 | 2045 | Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. | 1991-06-15 00:00:00 |
| PS2106 | Life Without Fear | psychology | 0736 | 7 | 6000 | 10 | 111 | New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately. | 1991-10-05 00:00:00 |
| PS3333 | Prolonged Data Deprivation: Four Case Studies | psychology | 0736 | 20 | 2000 | 10 | 4072 | What happens when the data runs dry? Searching evaluations of information-shortage effects. | 1991-06-12 00:00:00 |
| PS7777 | Emotional Security: A New Algorithm | psychology | 0736 | 8 | 4000 | 10 | 3336 | Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized. | 1991-06-12 00:00:00 |
| TC3218 | Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean | trad_cook | 0877 | 21 | 7000 | 10 | 375 | Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend. | 1991-10-21 00:00:00 |
| TC4203 | Fifty Years in Buckingham Palace Kitchens | trad_cook | 0877 | 12 | 4000 | 14 | 15096 | More anecdotes from the Queen's favorite cook describing life among English royalty. Recipes, techniques, tender vignettes. | 1991-06-12 00:00:00 |
| TC7777 | Sushi, Anyone? | trad_cook | 0877 | 15 | 8000 | 10 | 4095 | Detailed instructions on how to make authentic Japanese sushi in your spare time. | 1991-06-12 00:00:00 |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
14 rows in set (0.00 sec)
我们从上面的例子可以看出EXISTS类似一个IF语句，就是判断是否执行外层查询语句的IF条件，与外层的查询条件没有直接关系，且第二个例子说明了只有EXISTS后面的语句是属于IF条件块的，而AND后的多条件不是。
4. 带IN关键字的子查询
IN关键字进行子查询时，实际上是执行两次操作，一次时内部查询语句，仅仅返回一个数据列，然后外层查询语句对这个数据列进行比较操作。
mysql> SELECT title_id FROM titles WHERE price > 20 IN
-> (SELECT title_id FROM titles WHERE type IN ('popular_comp', 'psychology', 'trad_cook'));
+----------+
| title_id |
+----------+
| BU1032 |
| BU1111 |
| BU2075 |
| BU7832 |
| MC2222 |
| MC3021 |
| PC1035 |
| PC8888 |
| PS1372 |
| PS2091 |
| PS2106 |
| PS3333 |
| PS7777 |
| TC3218 |
| TC4203 |
| TC7777 |
+----------+
16 rows in set, 11 warnings (0.00 sec)
//这步过程其实是两次执行，分别为下面两步
mysql> SELECT type FROM titles WHERE price > 20;
+--------------+
| type |
+--------------+
| popular_comp |
| psychology |
| trad_cook |
+--------------+
3 rows in set (0.00 sec)
mysql> SELECT title_id FROM titles WHERE type IN ('popular_comp', 'psychology', 'trad_cook');
+----------+
| title_id |
+----------+
| PC1035 |
| PC8888 |
| PC9999 |
| PS1372 |
| PS2091 |
| PS2106 |
| PS3333 |
| PS7777 |
| TC3218 |
| TC4203 |
| TC7777 |
+----------+
11 rows in set (0.00 sec)
当然也有NOT IN，与IN相反，就不举例子了。
5.带比较运算符的子查询
使用如 >, <, >=, <=, !=, = 等运算符进行计算
//首先找到在titleauthor的au_id = '172-32-1176' 的title_id ，然后通过这个外键查找到titles的title_id price。
mysql> SELECT title_id, price FROM titles
-> WHERE title_id =
-> (SELECT ta.title_id FROM titleauthor AS ta WHERE ta.au_id = '172-32-1176');
+----------+-------+
| title_id | price |
+----------+-------+
| PS3333 | 20 |
+----------+-------+
1 row in set (0.00 sec)
合并查询结果
使用UNION关键字，将他们的结果组合成单个结果集，但是合并的两个或多个表对应的列数和数据类型必须相同。
UNION ALL 加上ALL就是把全部记录都显示，而UNION是会删去重复行。
语法结构：SELECT col1, col2, ... FROM table1
` UNION [ALL]
SELECT col1, col2, ... FROM table2;
mysql> SELECT title_id, price FROM titles
-> WHERE title_id =
-> (SELECT ta.title_id FROM titleauthor AS ta WHERE ta.au_id = '172-32-1176')
-> UNION ALL
-> SELECT title_id, price FROM titles
-> WHERE title_id =
-> (SELECT ta.title_id FROM titleauthor AS ta WHERE ta.au_id = '238-95-7766');
+----------+-------+
| title_id | price |
+----------+-------+
| PS3333 | 20 |
| PC1035 | 23 |
+----------+-------+
2 rows in set (0.00 sec)
为表和字段取别名
在嵌套查询语句中有些表的名字很长，我们就可以在查询语句中为它取别名，在嵌套语句中使用会方便些，要注意不能与其它名字重复。
1.为表取别名
语法结构：表名 AS 表别名
mysql> SELECT * FROM titleauthor AS ta WHERE ta.au_ord = 1;
+-------------+----------+--------+------------+
| au_id | title_id | au_ord | royaltyper |
+-------------+----------+--------+------------+
| 172-32-1176 | PS3333 | 1 | 100 |
| 213-46-8915 | BU2075 | 1 | 100 |
| 238-95-7766 | PC1035 | 1 | 100 |
| 274-80-9391 | BU7832 | 1 | 100 |
| 409-56-7008 | BU1032 | 1 | 60 |
| 427-17-2319 | PC8888 | 1 | 50 |
| 486-29-1786 | PC9999 | 1 | 100 |
| 486-29-1786 | PS7777 | 1 | 100 |
| 648-92-1872 | TC4203 | 1 | 100 |
| 672-71-3249 | TC7777 | 1 | 40 |
| 712-45-1867 | MC2222 | 1 | 100 |
| 722-51-5454 | MC3021 | 1 | 75 |
| 724-80-9391 | BU1111 | 1 | 60 |
| 756-30-7391 | PS1372 | 1 | 75 |
| 807-91-6654 | TC3218 | 1 | 100 |
| 998-72-3567 | PS2091 | 1 | 50 |
| 998-72-3567 | PS2106 | 1 | 100 |
+-------------+----------+--------+------------+
17 rows in set (0.00 sec)
2.为字段取别名
有时列明是简写，会出现误解，因此我们可以取别名，这与为表取别名不同，它是为了在显示结果是更清晰，不容易产生误解为目的的。
语法结构：列名 AS 列别名
//错误的，不能直接使用别名，别名是为了显示而添加的。
mysql> SELECT ta.au_ord AS ao, ta.au_id AS ai
-> FROM titleauthor AS ta
-> WHERE ta.ao = 1 AND ai IN ('172-32-1176','213-46-8915','238-95-7766');
ERROR 1054 (42S22): Unknown column 'ta.ao' in 'where clause'
mysql> SELECT ta.au_ord AS author_ordinary, ta.au_id AS author_id
-> FROM titleauthor AS ta
-> WHERE ta.au_ord = 1 AND au_id IN ('172-32-1176','213-46-8915','238-95-7766');
+-----------------+-------------+
| author_ordinary | author_id |
+-----------------+-------------+
| 1 | 172-32-1176 |
| 1 | 213-46-8915 |
| 1 | 238-95-7766 |
+-----------------+-------------+
3 rows in set (0.00 sec)
使用正则表达式查询
正则表达式常用字符匹配列表，如下图：
该图片引自： https://www.modb.pro/db/13129
1. 查询以特定字符或字符串开头的记录
mysql> SELECT * FROM titles WHERE title REGEXP '^T';
+----------+-------------------------------------+-----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-------------------------------------+-----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| MC3021 | The Gourmet Microwave | mod_cook | 0877 | 3 | 15000 | 24 | 22246 | Traditional French gourmet recipes adapted for modern microwave cooking. | 1991-06-18 00:00:00 |
| MC3026 | The Psychology of Computer Cooking | UNDECIDED | 0877 | NULL | NULL | NULL | NULL | NULL | 2004-12-13 16:11:37 |
+----------+-------------------------------------+-----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------+---------------------+
3 rows in set (0.01 sec)
mysql> SELECT * FROM titles WHERE title REGEXP '^The B';
+----------+-------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
+----------+-------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------+---------------------+
1 row in set (0.00 sec)
2. 查询以特定字符或字符串结尾的记录
mysql> SELECT * FROM titles WHERE title REGEXP 's$';
+----------+-----------------------------------------------------------------+------------+--------+-------+---------+---------+-----------+-----------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-----------------------------------------------------------------+------------+--------+-------+---------+---------+-----------+-----------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
| MC2222 | Silicon Valley Gastronomic Treats | mod_cook | 0877 | 20 | 0 | 12 | 2032 | Favorite recipes for quick, easy, and elegant meals. | 1991-06-09 00:00:00 |
| PS1372 | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | psychology | 0877 | 22 | 7000 | 10 | 375 | A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. | 1991-10-21 00:00:00 |
| PS3333 | Prolonged Data Deprivation: Four Case Studies | psychology | 0736 | 20 | 2000 | 10 | 4072 | What happens when the data runs dry? Searching evaluations of information-shortage effects. | 1991-06-12 00:00:00 |
| TC4203 | Fifty Years in Buckingham Palace Kitchens | trad_cook | 0877 | 12 | 4000 | 14 | 15096 | More anecdotes from the Queen's favorite cook describing life among English royalty. Recipes, techniques, tender vignettes. | 1991-06-12 00:00:00 |
+----------+-----------------------------------------------------------------+------------+--------+-------+---------+---------+-----------+-----------------------------------------------------------------------------------------------------------------------------+---------------------+
6 rows in set (0.00 sec)
mysql> SELECT * FROM titles WHERE title REGEXP 'ss!$';
+----------+---------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+---------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
+----------+---------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
1 row in set (0.00 sec)
3. 用符号"."来代替字符串中的任意一个字符
mysql> SELECT * FROM titles WHERE title_id REGEXP 'BU....';
+----------+------------------------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+------------------------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
+----------+------------------------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM titles WHERE title_id REGEXP 'BU...2';
+----------+-------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
+----------+-------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------+---------------------+
2 rows in set (0.00 sec)
4. 使用"*“和”+"来匹配多个字符
"*"星号匹配前面的字符任意多次，可以是零次
"+“加号匹配前面的字符至少一次，最少一次，不可为零次
mysql> SELECT * FROM titles WHERE title_id REGEXP '^B*';
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
| MC2222 | Silicon Valley Gastronomic Treats | mod_cook | 0877 | 20 | 0 | 12 | 2032 | Favorite recipes for quick, easy, and elegant meals. | 1991-06-09 00:00:00 |
| MC3021 | The Gourmet Microwave | mod_cook | 0877 | 3 | 15000 | 24 | 22246 | Traditional French gourmet recipes adapted for modern microwave cooking. | 1991-06-18 00:00:00 |
| MC3026 | The Psychology of Computer Cooking | UNDECIDED | 0877 | NULL | NULL | NULL | NULL | NULL | 2004-12-13 16:11:37 |
| PC1035 | But Is It User Friendly? | popular_comp | 1389 | 23 | 7000 | 16 | 8780 | A survey of software for the naive user, focusing on the 'friendliness' of each. | 1991-06-30 00:00:00 |
| PC8888 | Secrets of Silicon Valley | popular_comp | 1389 | 20 | 8000 | 10 | 4095 | Muckraking reporting on the world's largest computer hardware and software manufacturers. | 1994-06-12 00:00:00 |
| PC9999 | Net Etiquette | popular_comp | 1389 | NULL | NULL | NULL | NULL | A must-read for computer conferencing. | 2004-12-13 16:11:37 |
| PS1372 | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | psychology | 0877 | 22 | 7000 | 10 | 375 | A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. | 1991-10-21 00:00:00 |
| PS2091 | Is Anger the Enemy? | psychology | 0736 | 11 | 2275 | 12 | 2045 | Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. | 1991-06-15 00:00:00 |
| PS2106 | Life Without Fear | psychology | 0736 | 7 | 6000 | 10 | 111 | New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately. | 1991-10-05 00:00:00 |
| PS3333 | Prolonged Data Deprivation: Four Case Studies | psychology | 0736 | 20 | 2000 | 10 | 4072 | What happens when the data runs dry? Searching evaluations of information-shortage effects. | 1991-06-12 00:00:00 |
| PS7777 | Emotional Security: A New Algorithm | psychology | 0736 | 8 | 4000 | 10 | 3336 | Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized. | 1991-06-12 00:00:00 |
| TC3218 | Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean | trad_cook | 0877 | 21 | 7000 | 10 | 375 | Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend. | 1991-10-21 00:00:00 |
| TC4203 | Fifty Years in Buckingham Palace Kitchens | trad_cook | 0877 | 12 | 4000 | 14 | 15096 | More anecdotes from the Queen's favorite cook describing life among English royalty. Recipes, techniques, tender vignettes. | 1991-06-12 00:00:00 |
| TC7777 | Sushi, Anyone? | trad_cook | 0877 | 15 | 8000 | 10 | 4095 | Detailed instructions on how to make authentic Japanese sushi in your spare time. | 1991-06-12 00:00:00 |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
18 rows in set (0.00 sec)
mysql> SELECT * FROM titles WHERE title_id REGEXP '^B+';
+----------+------------------------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+------------------------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
+----------+------------------------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
4 rows in set (0.00 sec)
5. 匹配指定字符串
正则表达式可以匹配指定字符串，只要这个字符串在查询的文中即可，如要匹配多个字符串，多个字符串之间使用分隔符" | “隔开
在之前我们学过LIKE运算符也可以匹配，但是再养LIKE不行，它要加上”%“和”_"表示任意个字符和一个字符
mysql> SELECT * FROM titles WHERE title_id REGEXP 'BU';
+----------+------------------------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+------------------------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
+----------+------------------------------------------------------+----------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------+---------------------+
4 rows in set (0.00 sec)
mysql> SELECT * FROM titles WHERE title_id REGEXP 'BU|PS';
+----------+-----------------------------------------------------------------+------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-----------------------------------------------------------------+------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
| PS1372 | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | psychology | 0877 | 22 | 7000 | 10 | 375 | A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. | 1991-10-21 00:00:00 |
| PS2091 | Is Anger the Enemy? | psychology | 0736 | 11 | 2275 | 12 | 2045 | Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. | 1991-06-15 00:00:00 |
| PS2106 | Life Without Fear | psychology | 0736 | 7 | 6000 | 10 | 111 | New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately. | 1991-10-05 00:00:00 |
| PS3333 | Prolonged Data Deprivation: Four Case Studies | psychology | 0736 | 20 | 2000 | 10 | 4072 | What happens when the data runs dry? Searching evaluations of information-shortage effects. | 1991-06-12 00:00:00 |
| PS7777 | Emotional Security: A New Algorithm | psychology | 0736 | 8 | 4000 | 10 | 3336 | Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized. | 1991-06-12 00:00:00 |
+----------+-----------------------------------------------------------------+------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
9 rows in set (0.00 sec)
mysql> SELECT * FROM titles WHERE title_id LIKE 'BU';
Empty set (0.00 sec)
6. 匹配指定字符中的任意一个
方括号"[]"指定一个字符集合，只匹配其中的一个字符，即为所在查找的文本，且数字也是匹配一个。
mysql> SELECT * FROM titles WHERE title_id REGEXP '[BPS]';
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
| PC1035 | But Is It User Friendly? | popular_comp | 1389 | 23 | 7000 | 16 | 8780 | A survey of software for the naive user, focusing on the 'friendliness' of each. | 1991-06-30 00:00:00 |
| PC8888 | Secrets of Silicon Valley | popular_comp | 1389 | 20 | 8000 | 10 | 4095 | Muckraking reporting on the world's largest computer hardware and software manufacturers. | 1994-06-12 00:00:00 |
| PC9999 | Net Etiquette | popular_comp | 1389 | NULL | NULL | NULL | NULL | A must-read for computer conferencing. | 2004-12-13 16:11:37 |
| PS1372 | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | psychology | 0877 | 22 | 7000 | 10 | 375 | A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. | 1991-10-21 00:00:00 |
| PS2091 | Is Anger the Enemy? | psychology | 0736 | 11 | 2275 | 12 | 2045 | Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. | 1991-06-15 00:00:00 |
| PS2106 | Life Without Fear | psychology | 0736 | 7 | 6000 | 10 | 111 | New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately. | 1991-10-05 00:00:00 |
| PS3333 | Prolonged Data Deprivation: Four Case Studies | psychology | 0736 | 20 | 2000 | 10 | 4072 | What happens when the data runs dry? Searching evaluations of information-shortage effects. | 1991-06-12 00:00:00 |
| PS7777 | Emotional Security: A New Algorithm | psychology | 0736 | 8 | 4000 | 10 | 3336 | Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized. | 1991-06-12 00:00:00 |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
12 rows in set (0.00 sec)
7. 匹配指定字符以外的字符
"[^字符集合]"匹配不在指定集合内的任何字符
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU1032 | The Busy Executive's Database Guide | business | 1389 | 20 | 5000 | 10 | 4095 | An overview of available database systems with emphasis on common business applications. Illustrated. | 1991-06-12 00:00:00 |
| BU1111 | Cooking with Computers: Surreptitious Balance Sheets | business | 1389 | 12 | 5000 | 10 | 3876 | Helpful hints on how to use your electronic resources to the best advantage. | 1991-06-09 00:00:00 |
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
| MC2222 | Silicon Valley Gastronomic Treats | mod_cook | 0877 | 20 | 0 | 12 | 2032 | Favorite recipes for quick, easy, and elegant meals. | 1991-06-09 00:00:00 |
| MC3021 | The Gourmet Microwave | mod_cook | 0877 | 3 | 15000 | 24 | 22246 | Traditional French gourmet recipes adapted for modern microwave cooking. | 1991-06-18 00:00:00 |
| MC3026 | The Psychology of Computer Cooking | UNDECIDED | 0877 | NULL | NULL | NULL | NULL | NULL | 2004-12-13 16:11:37 |
| PC1035 | But Is It User Friendly? | popular_comp | 1389 | 23 | 7000 | 16 | 8780 | A survey of software for the naive user, focusing on the 'friendliness' of each. | 1991-06-30 00:00:00 |
| PC8888 | Secrets of Silicon Valley | popular_comp | 1389 | 20 | 8000 | 10 | 4095 | Muckraking reporting on the world's largest computer hardware and software manufacturers. | 1994-06-12 00:00:00 |
| PC9999 | Net Etiquette | popular_comp | 1389 | NULL | NULL | NULL | NULL | A must-read for computer conferencing. | 2004-12-13 16:11:37 |
| PS1372 | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | psychology | 0877 | 22 | 7000 | 10 | 375 | A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. | 1991-10-21 00:00:00 |
| PS2091 | Is Anger the Enemy? | psychology | 0736 | 11 | 2275 | 12 | 2045 | Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. | 1991-06-15 00:00:00 |
| PS2106 | Life Without Fear | psychology | 0736 | 7 | 6000 | 10 | 111 | New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately. | 1991-10-05 00:00:00 |
| PS3333 | Prolonged Data Deprivation: Four Case Studies | psychology | 0736 | 20 | 2000 | 10 | 4072 | What happens when the data runs dry? Searching evaluations of information-shortage effects. | 1991-06-12 00:00:00 |
| PS7777 | Emotional Security: A New Algorithm | psychology | 0736 | 8 | 4000 | 10 | 3336 | Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized. | 1991-06-12 00:00:00 |
| TC3218 | Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean | trad_cook | 0877 | 21 | 7000 | 10 | 375 | Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend. | 1991-10-21 00:00:00 |
| TC4203 | Fifty Years in Buckingham Palace Kitchens | trad_cook | 0877 | 12 | 4000 | 14 | 15096 | More anecdotes from the Queen's favorite cook describing life among English royalty. Recipes, techniques, tender vignettes. | 1991-06-12 00:00:00 |
| TC7777 | Sushi, Anyone? | trad_cook | 0877 | 15 | 8000 | 10 | 4095 | Detailed instructions on how to make authentic Japanese sushi in your spare time. | 1991-06-12 00:00:00 |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
18 rows in set (0.00 sec)
mysql> SELECT * FROM titles WHERE title_id REGEXP '[^BU1032]';
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU2075 | You Can Combat Computer Stress! | business | 0736 | 3 | 10125 | 24 | 18722 | The latest medical and psychological techniques for living with the electronic office. Easy-to-understand explanations. | 1991-06-30 00:00:00 |
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
| MC2222 | Silicon Valley Gastronomic Treats | mod_cook | 0877 | 20 | 0 | 12 | 2032 | Favorite recipes for quick, easy, and elegant meals. | 1991-06-09 00:00:00 |
| MC3021 | The Gourmet Microwave | mod_cook | 0877 | 3 | 15000 | 24 | 22246 | Traditional French gourmet recipes adapted for modern microwave cooking. | 1991-06-18 00:00:00 |
| MC3026 | The Psychology of Computer Cooking | UNDECIDED | 0877 | NULL | NULL | NULL | NULL | NULL | 2004-12-13 16:11:37 |
| PC1035 | But Is It User Friendly? | popular_comp | 1389 | 23 | 7000 | 16 | 8780 | A survey of software for the naive user, focusing on the 'friendliness' of each. | 1991-06-30 00:00:00 |
| PC8888 | Secrets of Silicon Valley | popular_comp | 1389 | 20 | 8000 | 10 | 4095 | Muckraking reporting on the world's largest computer hardware and software manufacturers. | 1994-06-12 00:00:00 |
| PC9999 | Net Etiquette | popular_comp | 1389 | NULL | NULL | NULL | NULL | A must-read for computer conferencing. | 2004-12-13 16:11:37 |
| PS1372 | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | psychology | 0877 | 22 | 7000 | 10 | 375 | A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. | 1991-10-21 00:00:00 |
| PS2091 | Is Anger the Enemy? | psychology | 0736 | 11 | 2275 | 12 | 2045 | Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. | 1991-06-15 00:00:00 |
| PS2106 | Life Without Fear | psychology | 0736 | 7 | 6000 | 10 | 111 | New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately. | 1991-10-05 00:00:00 |
| PS3333 | Prolonged Data Deprivation: Four Case Studies | psychology | 0736 | 20 | 2000 | 10 | 4072 | What happens when the data runs dry? Searching evaluations of information-shortage effects. | 1991-06-12 00:00:00 |
| PS7777 | Emotional Security: A New Algorithm | psychology | 0736 | 8 | 4000 | 10 | 3336 | Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized. | 1991-06-12 00:00:00 |
| TC3218 | Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean | trad_cook | 0877 | 21 | 7000 | 10 | 375 | Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend. | 1991-10-21 00:00:00 |
| TC4203 | Fifty Years in Buckingham Palace Kitchens | trad_cook | 0877 | 12 | 4000 | 14 | 15096 | More anecdotes from the Queen's favorite cook describing life among English royalty. Recipes, techniques, tender vignettes. | 1991-06-12 00:00:00 |
| TC7777 | Sushi, Anyone? | trad_cook | 0877 | 15 | 8000 | 10 | 4095 | Detailed instructions on how to make authentic Japanese sushi in your spare time. | 1991-06-12 00:00:00 |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
16 rows in set (0.00 sec)
mysql> SELECT * FROM titles WHERE title_id REGEXP '[^BU1032BU2075]';
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
| BU7832 | Straight Talk About Computers | business | 1389 | 20 | 5000 | 10 | 4095 | Annotated analysis of what computers can do for you: a no-hype guide for the critical user. | 1991-06-22 00:00:00 |
| MC2222 | Silicon Valley Gastronomic Treats | mod_cook | 0877 | 20 | 0 | 12 | 2032 | Favorite recipes for quick, easy, and elegant meals. | 1991-06-09 00:00:00 |
| MC3021 | The Gourmet Microwave | mod_cook | 0877 | 3 | 15000 | 24 | 22246 | Traditional French gourmet recipes adapted for modern microwave cooking. | 1991-06-18 00:00:00 |
| MC3026 | The Psychology of Computer Cooking | UNDECIDED | 0877 | NULL | NULL | NULL | NULL | NULL | 2004-12-13 16:11:37 |
| PC1035 | But Is It User Friendly? | popular_comp | 1389 | 23 | 7000 | 16 | 8780 | A survey of software for the naive user, focusing on the 'friendliness' of each. | 1991-06-30 00:00:00 |
| PC8888 | Secrets of Silicon Valley | popular_comp | 1389 | 20 | 8000 | 10 | 4095 | Muckraking reporting on the world's largest computer hardware and software manufacturers. | 1994-06-12 00:00:00 |
| PC9999 | Net Etiquette | popular_comp | 1389 | NULL | NULL | NULL | NULL | A must-read for computer conferencing. | 2004-12-13 16:11:37 |
| PS1372 | Computer Phobic AND Non-Phobic Individuals: Behavior Variations | psychology | 0877 | 22 | 7000 | 10 | 375 | A must for the specialist, this book examines the difference between those who hate and fear computers and those who don't. | 1991-10-21 00:00:00 |
| PS2091 | Is Anger the Enemy? | psychology | 0736 | 11 | 2275 | 12 | 2045 | Carefully researched study of the effects of strong emotions on the body. Metabolic charts included. | 1991-06-15 00:00:00 |
| PS2106 | Life Without Fear | psychology | 0736 | 7 | 6000 | 10 | 111 | New exercise, meditation, and nutritional techniques that can reduce the shock of daily interactions. Popular audience. Sample menus included, exercise video available separately. | 1991-10-05 00:00:00 |
| PS3333 | Prolonged Data Deprivation: Four Case Studies | psychology | 0736 | 20 | 2000 | 10 | 4072 | What happens when the data runs dry? Searching evaluations of information-shortage effects. | 1991-06-12 00:00:00 |
| PS7777 | Emotional Security: A New Algorithm | psychology | 0736 | 8 | 4000 | 10 | 3336 | Protecting yourself and your loved ones from undue emotional stress in the modern world. Use of computer and nutritional aids emphasized. | 1991-06-12 00:00:00 |
| TC3218 | Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean | trad_cook | 0877 | 21 | 7000 | 10 | 375 | Profusely illustrated in color, this makes a wonderful gift book for a cuisine-oriented friend. | 1991-10-21 00:00:00 |
| TC4203 | Fifty Years in Buckingham Palace Kitchens | trad_cook | 0877 | 12 | 4000 | 14 | 15096 | More anecdotes from the Queen's favorite cook describing life among English royalty. Recipes, techniques, tender vignettes. | 1991-06-12 00:00:00 |
| TC7777 | Sushi, Anyone? | trad_cook | 0877 | 15 | 8000 | 10 | 4095 | Detailed instructions on how to make authentic Japanese sushi in your spare time. | 1991-06-12 00:00:00 |
+----------+-----------------------------------------------------------------+--------------+--------+-------+---------+---------+-----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------+
15 rows in set (0.00 sec)
8. 使用{n,}或者{n,m}来指定字符串连续出现的次数
“字符串{n,}”，表示至少匹配n次前面的字符
“字符串{n,m}”，表示至少匹配前面的字符n次，最多不能超过m次
mysql> SELECT * FROM titles WHERE title_id REGEXP 'PC9{4,}';
+----------+---------------+--------------+--------+-------+---------+---------+-----------+----------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+---------------+--------------+--------+-------+---------+---------+-----------+----------------------------------------+---------------------+
| PC9999 | Net Etiquette | popular_comp | 1389 | NULL | NULL | NULL | NULL | A must-read for computer conferencing. | 2004-12-13 16:11:37 |
+----------+---------------+--------------+--------+-------+---------+---------+-----------+----------------------------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM titles WHERE title_id REGEXP 'PC9{5,}';
Empty set (0.00 sec)
mysql> SELECT * FROM titles WHERE title_id REGEXP 'PC9{4,5}';
+----------+---------------+--------------+--------+-------+---------+---------+-----------+----------------------------------------+---------------------+
| title_id | title | type | pub_id | price | advance | royalty | ytd_sales | notes | pubdate |
+----------+---------------+--------------+--------+-------+---------+---------+-----------+----------------------------------------+---------------------+
| PC9999 | Net Etiquette | popular_comp | 1389 | NULL | NULL | NULL | NULL | A must-read for computer conferencing. | 2004-12-13 16:11:37 |
+----------+---------------+--------------+--------+-------+---------+---------+-----------+----------------------------------------+---------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM titles WHERE title_id REGEXP 'PC9{5,6}';
Empty set (0.00 sec)
到此MySQL的基本查询数据的方法都写过一篇了。
11万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
