Count()函数
统计表中所有列的总数
mysql> select count(*) from City;
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
1 row in set (0.00 sec)
Count函数不统计空值NULL
mysql> create table test (
-> id int,
-> num int
-> );
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql>
mysql> insert into test values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into test set
-> id=4;
Query OK, 1 row affected (0.01 sec)
mysql>
mysql>
mysql> select * from test;
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | NULL |
+------+------+
4 rows in set (0.00 sec)
mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
| 4 |
+----------+
1 row in set (0.00 sec)
mysql> select count(id) from test;
+-----------+
| count(id) |
+-----------+
| 4 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(num) from test;
+------------+
| count(num) |
+------------+
| 3 |
+------------+
1 row in set (0.00 sec) Count函数不统计空值NULL
SUM（）函数
指定列求和
mysql> select sum(population) from City;
+-----------------+
| sum(population) |
+-----------------+
| 1429559884 |
+-----------------+
1 row in set (0.00 sec)
字符列求和
mysql> select sum(name) from City;
+-----------+
| sum(name) |
+-----------+
| 0 |
+-----------+
1 row in set, 4079 warnings (0.00 sec)
数据类型不匹配。做了全表扫描，并做了求和的运算，因此出现了下面的警告。
mysql> create table test ( id int, numint, index (id) );
Query OK, 0 rows affected (0.09 sec)
mysql> insert into test values(1,1),(2,2),(3,3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select count(id) from test;
+-----------+
| count(id) |
+-----------+
| 3 |
+-----------+
1 row in set (0.00 sec)
用到了索引
mysql> explain select count(id) fromtest\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: test
partitions: NULL
type: index
possible_keys: NULL
key: id
key_len: 5
ref: NULL
rows: 3
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
AVG（）函数
取出这一列的平均值
mysql> select avg(population) from City;
+-----------------+
| avg(population) |
+-----------------+
| 350468.2236 |
+-----------------+
1 row in set (0.00 sec)
MAX（）函数
取出这一列的最大值
mysql> select max(population) from City;
+-----------------+
| max(population) |
+-----------------+
| 10500000 |
+-----------------+
1 row in set (0.00 sec)
没有用到索引
mysql> explain select max(id) fromtest\G;
*************************** 1. row***************************
id: 1
select_type: SIMPLE
table: NULL
partitions: NULL
type: NULL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
filtered: NULL
Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
一个一个的比较，全表扫描。
MIN（）函数
mysql> select min(population) from City;
+-----------------+
| min(population) |
+-----------------+
| 42 |
+-----------------+
1 row in set (0.00 sec)
当表中的数据量非常大的时候使用MAX和MIN函数需要谨慎。因为都是进行全表扫描操作，比较耗费资源。
正则表达式查询
以特定字符开头
mysql> select * from City wherecountrycode like 'CHN' and district regexp '^z';
+------+----------+-------------+----------+------------+
| ID | Name | CountryCode |District | Population |
+------+----------+-------------+----------+------------+
| 1905 | Hangzhou | CHN | Zhejiang | 2190500 |
| 1915 | Ningbo | CHN | Zhejiang | 1371200 |
| 1973 | Wenzhou | CHN | Zhejiang | 401871 |
| 2049 | Huzhou | CHN | Zhejiang | 218071 |
| 2057 | Jiaxing | CHN | Zhejiang | 211526 |
| 2096 | Shaoxing | CHN | Zhejiang | 179818 |
| 2118 | Xiaoshan | CHN | Zhejiang | 162930 |
| 2128 | Rui麓an | CHN | Zhejiang | 156468 |
| 2129 | Zhoushan | CHN | Zhejiang | 156317 |
| 2143 | Jinhua | CHN | Zhejiang | 144280 |
| 2199 | Yuyao | CHN | Zhejiang | 114065 |
| 2205 | Quzhou | CHN | Zhejiang | 112373 |
| 2214 | Cixi | CHN | Zhejiang | 107329 |
| 2230 | Haining | CHN | Zhejiang | 100478 |
| 2246 | Linhai | CHN | Zhejiang | 90870 |
| 2252 | Huangyan | CHN | Zhejiang | 89288 |
+------+----------+-------------+----------+------------+
16 rows in set (0.00 sec)
‘^z’表示以Z开头的数据
以特定的字符结尾
mysql> select * from City wherecountrycode like 'CHN' and district regexp 'g$';
+------+---------------------+-------------+--------------+------------+
| ID | Name |CountryCode | District | Population |
+------+---------------------+-------------+--------------+------------+
| 1891 | Peking | CHN | Peking | 7472000 |
| 1892 | Chongqing | CHN | Chongqing | 6351600 |
| 1895 | Harbin | CHN | Heilongjiang | 4289800 |
| 1896 | Shenyang | CHN | Liaoning | 4265200 |
| 1897 | Kanton [Guangzhou] | CHN | Guangdong | 4256300 |
| 1902 | Dalian | CHN | Liaoning | 2697000 |
| 1903 | Qingdao | CHN | Shandong | 2596000 |
| 1904 | Jinan | CHN | Shandong | 2278100 |
| 1905 | Hangzhou | CHN | Zhejiang | 2190500 |
| 1915 | Ningbo | CHN | Zhejiang | 1371200 |
| 1917 | Urumt拧i [?r眉mqi] | CHN | Xinxiang | 1310100 |
| 1918 | Anshan | CHN | Liaoning | 1200000 |
| 1919 | Fushun | CHN | Liaoning | 1200000 |
| 1921 | Zibo | CHN | Shandong | 1140000 |
| 1922 | Qiqihar | CHN | Heilongjiang | 1070000 |
| 1926 | Shenzhen | CHN | Guangdong | 950500 |
| 1932 | Yichun | CHN | Heilongjiang | 800000 |
| 1933 | Benxi | CHN | Liaoning | 770000 |
| 1938 | Jixi | CHN | Heilongjiang | 683885 |
| 1939 | Daqing | CHN | Heilongjiang | 660000 |
| 1940 | Fuxin | CHN | Liaoning | 640000 |
| 1943 | Shantou | CHN | Guangdong | 580000 |
| 1944 | Jinzhou | CHN | Liaoning | 570000 |
| 1945 | Mudanjiang | CHN | Heilongjiang | 570000 |
| 1949 | Dandong | CHN | Liaoning | 520000 |
| 1950 | Hegang | CHN | Heilongjiang | 520000 |
| 1952 | Jiamusi | CHN | Heilongjiang | 493409 |
| 1953 | Liaoyang | CHN | Liaoning | 492559 |
| 1960 | Yantai | CHN | Shandong | 452127 |
| 1963 | Weifang | CHN | Shandong | 428522 |
| 1966 | Yingkou | CHN | Liaoning | 421589 |
| 1973 | Wenzhou | CHN | Zhejiang | 401871 |
| 1974 | Zhangjiang | CHN | Guangdong | 400997 |
| 1976 | Shuangyashan | CHN | Heilongjiang | 386081 |
| 1977 | Zaozhuang | CHN | Shandong | 380846 |
| 1985 | Panjin | CHN | Liaoning | 362773 |
| 1987 | Jinxi | CHN | Liaoning | 357052 |
| 1991 | Tai麓an | CHN | Shandong | 350696 |
| 1993 | Shaoguan | CHN | Guangdong | 350043 |
| 1997 | Linyi | CHN | Shandong | 324720 |
| 2001 | Tengzhou | CHN | Shandong | 315083 |
| 2002 | Chaozhou | CHN | Guangdong | 313469 |
| 2004 | Dongwan | CHN | Guangdong | 308669 |
| 2006 | Foshan | CHN | Guangdong | 303160 |
| 2010 | Shihezi | CHN | Xinxiang | 299676 |
| 2013 | Dongying | CHN | Shandong | 281728 |
| 2015 | Xintai | CHN | Shandong | 281248 |
| 2018 | Zhongshan | CHN | Guangdong | 278829 |
| 2020 | Tieli | CHN | Heilongjiang | 265683 |
| 2021 | Jining | CHN | Shandong | 265248 |
| 2029 | Tieling | CHN | Liaoning | 254842 |
| 2030 | Wafangdian | CHN | Liaoning | 251733 |
| 2033 | Laiwu | CHN | Shandong | 246833 |
| 2042 | Jiangmen | CHN | Guangdong | 230587 |
| 2044 | Suihua | CHN | Heilongjiang | 227881 |
| 2047 | Chaoyang | CHN | Liaoning | 222394 |
| 2049 | Huzhou | CHN | Zhejiang | 218071 |
| 2051 | Shangzi | CHN | Heilongjiang | 215373 |
| 2052 | Yangjiang | CHN | Guangdong | 215196 |
| 2053 | Qitaihe | CHN | Heilongjiang | 214957 |
| 2057 | Jiaxing | CHN | Zhejiang | 211526 |
| 2061 | Liaocheng | CHN | Shandong | 207844 |
| 2062 | Haicheng | CHN | Liaoning | 205560 |
| 2065 | Bei麓an | CHN | Heilongjiang | 204899 |
| 2067 | Laizhou | CHN | Shandong | 198664 |
| 2068 | Qaramay | CHN | Xinxiang | 197602 |
| 2069 | Acheng | CHN | Heilongjiang | 197595 |
| 2070 | Dezhou | CHN | Shandong | 195485 |
| 2072 | Zhaoqing | CHN | Guangdong | 194784 |
| 2073 | Beipiao | CHN | Liaoning | 194301 |
| 2081 | Heze | CHN | Shandong | 189293 |
| 2087 | Rizhao | CHN | Shandong | 185048 |
| 2095 | Zhaodong | CHN | Heilongjiang | 179976 |
| 2096 | Shaoxing | CHN | Zhejiang | 179818 |
| 2098 | Maoming | CHN | Guangdong | 178683 |
| 2100 | Ghulja | CHN | Xinxiang | 177193 |
| 2105 | Qashqar | CHN | Xinxiang | 174570 |
| 2114 | Zhuhai | CHN | Guangdong | 164747 |
| 2115 | Qingyuan | CHN | Guangdong | 164641 |
| 2116 | Aqsu | CHN | Xinxiang | 164092 |
| 2118 | Xiaoshan | CHN | Zhejiang | 162930 |
| 2121 | Hami | CHN | Xinxiang | 161315 |
| 2122 | Huizhou | CHN | Guangdong | 161023 |
| 2126 | Korla | CHN | Xinxiang | 159344 |
| 2128 | Rui麓an | CHN | Zhejiang | 156468 |
| 2129 | Zhoushan | CHN | Zhejiang | 156317 |
| 2130 | Liangcheng | CHN | Shandong | 156307 |
| 2131 | Jiaozhou | CHN | Shandong | 153364 |
| 2136 | Pingdu | CHN | Shandong | 150123 |
| 2138 | Longkou | CHN | Shandong | 148362 |
| 2143 | Jinhua | CHN | Zhejiang | 144280 |
| 2145 | Shuangcheng | CHN | Heilongjiang | 142659 |
| 2152 | Laiyang | CHN | Shandong | 137080 |
| 2155 | Anda | CHN | Heilongjiang | 136446 |
| 2159 | Wendeng | CHN | Shandong | 133910 |
| 2160 | Hailun | CHN | Heilongjiang | 133565 |
| 2161 | Binzhou | CHN | Shandong | 133555 |
| 2165 | Mishan | CHN | Heilongjiang | 132744 |
| 2167 | Changji | CHN | Xinxiang | 132260 |
| 2168 | Meixian | CHN | Guangdong | 132156 |
| 2170 | Tiefa | CHN | Liaoning | 131807 |
| 2174 | Weihai | CHN | Shandong | 128888 |
| 2176 | Qingzhou | CHN | Shandong | 128258 |
| 2181 | Kaiyuan | CHN | Liaoning | 124219 |
| 2182 | Linqing | CHN | Shandong | 123958 |
| 2188 | Jiaonan | CHN | Shandong | 121397 |
| 2190 | Heyuan | CHN | Guangdong | 120101 |
| 2194 | Kuytun | CHN | Xinxiang | 118553 |
| 2199 | Yuyao | CHN | Zhejiang | 114065 |
| 2205 | Quzhou | CHN | Zhejiang | 112373 |
| 2213 | Shanwei | CHN | Guangdong | 107847 |
| 2214 | Cixi | CHN | Zhejiang | 107329 |
| 2223 | Fujin | CHN | Heilongjiang | 103104 |
| 2227 | Xingcheng | CHN | Liaoning | 102384 |
| 2228 | Zhucheng | CHN | Shandong | 102134 |
| 2230 | Haining | CHN | Zhejiang | 100478 |
| 2234 | Jieyang | CHN | Guangdong | 98531 |
| 2236 | Tong Xian | CHN | Peking | 97168 |
| 2238 | Jinzhou | CHN | Liaoning | 95761 |
| 2246 | Linhai | CHN | Zhejiang | 90870 |
| 2249 | Junan | CHN | Shandong | 90222 |
| 2251 | Pingyi | CHN | Shandong | 89373 |
| 2252 | Huangyan | CHN | Zhejiang | 89288 |
+------+---------------------+-------------+--------------+------------+
123 rows in set (0.00 sec)
匹配任意单个字符
mysql> select * from City wherecountrycode regexp 'c.n' limit 20;
+------+-------------+-------------+------------------+------------+
| ID | Name | CountryCode |District | Population |
+------+-------------+-------------+------------------+------------+
| 1810 | Montr茅al | CAN | Qu茅bec | 1016376 |
| 1811 | Calgary | CAN | Alberta | 768082 |
| 1812 | Toronto | CAN | Ontario | 688275 |
| 1813 | North York | CAN | Ontario | 622632 |
| 1814 | Winnipeg | CAN | Manitoba | 618477 |
| 1815 | Edmonton | CAN | Alberta | 616306 |
| 1816 | Mississauga | CAN | Ontario | 608072 |
| 1817 | Scarborough | CAN | Ontario | 594501 |
| 1818 | Vancouver | CAN | British Colombia | 514008 |
| 1819 | Etobicoke | CAN | Ontario | 348845 |
| 1820 | London | CAN | Ontario | 339917 |
| 1821 | Hamilton | CAN | Ontario | 335614 |
| 1822 | Ottawa | CAN | Ontario | 335277 |
| 1823 | Laval | CAN | Qu茅bec | 330393 |
| 1824 | Surrey | CAN | British Colombia | 304477 |
| 1825 | Brampton | CAN | Ontario | 296711 |
| 1826 | Windsor | CAN | Ontario | 207588 |
| 1827 | Saskatoon | CAN | Saskatchewan | 193647 |
| 1828 | Kitchener | CAN | Ontario | 189959 |
| 1829 | Markham | CAN | Ontario | 189098 |
+------+-------------+-------------+------------------+------------+
20 rows in set (0.01 sec)
.在数据库中可以表示多个含义，在select后面表示数据库名和数据表名的连接符号。
匹配多个字符
*匹配零个或多个在它前面的字符。
+匹配它前面的字符一次或多次。
mysql> select district from City
-> where
-> district regexp '^sh*'
-> and
-> countrycode like 'chn';
+----------+
| district |
+----------+
| Shanghai |
| Sichuan |
| Shaanxi |
| Shandong |
| Shandong |
| Shanxi |
| Shandong |
| Shanxi |
| Shandong |
| Shandong |
| Sichuan |
| Sichuan |
| Shandong |
| Shanxi |
| Shaanxi |
| Shandong |
| Sichuan |
| Shaanxi |
| Shandong |
| Shanxi |
| Shandong |
| Shandong |
| Shandong |
| Shaanxi |
| Shandong |
| Sichuan |
| Sichuan |
| Sichuan |
| Shandong |
| Sichuan |
| Shandong |
| Shandong |
| Shandong |
| Shanxi |
| Shandong |
| Sichuan |
| Shanxi |
| Shandong |
| Sichuan |
| Sichuan |
| Sichuan |
| Sichuan |
| Sichuan |
| Shaanxi |
| Sichuan |
| Shandong |
| Shandong |
| Shandong |
| Shandong |
| Sichuan |
| Shaanxi |
| Shaanxi |
| Shandong |
| Shanxi |
| Sichuan |
| Shandong |
| Shandong |
| Shandong |
| Shandong |
| Shandong |
| Sichuan |
| Shandong |
| Shaanxi |
| Shanxi |
| Shandong |
| Shanxi |
| Sichuan |
| Sichuan |
| Shandong |
| Sichuan |
| Shandong |
+----------+
71 rows in set (0.01 sec)
mysql> select district from City wheredistrict regexp '^sh+' and countrycode like 'chn';
+----------+
| district |
+----------+
| Shanghai |
| Shaanxi |
| Shandong |
| Shandong |
| Shanxi |
| Shandong |
| Shanxi |
| Shandong |
| Shandong |
| Shandong |
| Shanxi |
| Shaanxi |
| Shandong |
| Shaanxi |
| Shandong |
| Shanxi |
| Shandong |
| Shandong |
| Shandong |
| Shaanxi |
| Shandong |
| Shandong |
| Shandong |
| Shandong |
| Shandong |
| Shanxi |
| Shandong |
| Shanxi |
| Shandong |
| Shaanxi |
| Shandong |
| Shandong |
| Shandong |
| Shandong |
| Shaanxi |
| Shaanxi |
| Shandong |
| Shanxi |
| Shandong |
| Shandong |
| Shandong |
| Shandong |
| Shandong |
| Shandong |
| Shaanxi |
| Shanxi |
| Shandong |
| Shanxi |
| Shandong |
| Shandong |
+----------+
50 rows in set (0.01 sec)
匹配指定的字符串
可以使用“|”分割两段字符来表示或。
mysql> select name,district from City
-> where
-> district regexp 'shan|guang'
-> and
-> countrycode like 'chn';
+--------------------+-----------+
| name | district |
+--------------------+-----------+
| Shanghai | Shanghai |
| Kanton [Guangzhou] | Guangdong |
| Qingdao | Shandong |
| Jinan | Shandong |
| Taiyuan | Shanxi |
| Nanning | Guangxi |
| Zibo | Shandong |
| Shenzhen | Guangdong |
| Datong | Shanxi |
| Liuzhou | Guangxi |
| Shantou | Guangdong |
| Yantai | Shandong |
| Weifang | Shandong |
| Zhangjiang | Guangdong |
| Zaozhuang | Shandong |
| Guilin | Guangxi |
| Yangquan | Shanxi |
| Tai麓an | Shandong |
| Shaoguan | Guangdong |
| Linyi | Shandong |
| Changzhi | Shanxi |
| Tengzhou | Shandong |
| Chaozhou | Guangdong |
| Dongwan | Guangdong |
| Foshan | Guangdong |
| Dongying | Shandong |
| Xintai | Shandong |
| Zhongshan | Guangdong |
| Jining | Shandong |
| Laiwu | Shandong |
| Jiangmen | Guangdong |
| Yangjiang | Guangdong |
| Wuzhou | Guangxi |
| Liaocheng | Shandong |
| Laizhou | Shandong |
| Dezhou | Shandong |
| Zhaoqing | Guangdong |
| Yuci | Shanxi |
| Heze | Shandong |
| Linfen | Shanxi |
| Rizhao | Shandong |
| Maoming | Guangdong |
| Zhuhai | Guangdong |
| Qingyuan | Guangdong |
| Huizhou | Guangdong |
| Liangcheng | Shandong |
| Jiaozhou | Shandong |
| Pingdu | Shandong |
| Longkou | Shandong |
| Yulin | Guangxi |
| Laiyang | Shandong |
| Jincheng | Shanxi |
| Wendeng | Shandong |
| Binzhou | Shandong |
| Meixian | Guangdong |
| Weihai | Shandong |
| Qingzhou | Shandong |
| Linqing | Shandong |
| Jiaonan | Shandong |
| Heyuan | Guangdong |
| Qinzhou | Guangxi |
| Guigang | Guangxi |
| Beihai | Guangxi |
| Yuncheng | Shanxi |
| Shanwei | Guangdong |
| Zhucheng | Shandong |
| Xinzhou | Shanxi |
| Jieyang | Guangdong |
| Bose | Guangxi |
| Junan | Shandong |
| Pingyi | Shandong |
+--------------------+-----------+
71 rows in set (0.02 sec)
匹配指定字符串中的任意一个
如果在[]中加入^符号，则表示非
带S或者带G
mysql> select district from City wheredistrict regexp '[SG]' and countrycode like 'CHN' limit 10;
+--------------+
| district |
+--------------+
| Shanghai |
| Peking |
| Chongqing |
| Heilongjiang |
| Liaoning |
| Guangdong |
| Sichuan |
| Jiangsu |
| Shaanxi |
| Liaoning |
+--------------+
10 rows in set (0.01 sec)
不带S或者不带G
mysql> select district from City wheredistrict regexp '[^SG]' and countrycode like 'CHN' limit 10;
+--------------+
| district |
+--------------+
| Shanghai |
| Peking |
| Chongqing |
| Tianjin |
| Hubei |
| Heilongjiang |
| Liaoning |
| Guangdong |
| Sichuan |
| Jiangsu |
+--------------+
10 rows in set (0.01 sec)
匹配指定字符串连续出现次数
如果写成h{2,} 或h{2}，则表示包含两个以上以上的h。
如果有H，出现1到2次
mysql> select name,district from Citywhere name regexp 'h{1,2}' and countrycode like 'CHN' limit 10;
+--------------------+--------------+
| name | district |
+--------------------+--------------+
| Shanghai | Shanghai |
| Chongqing | Chongqing |
| Wuhan | Hubei |
| Harbin | Heilongjiang |
| Shenyang | Liaoning |
| Kanton [Guangzhou] | Guangdong |
| Chengdu | Sichuan |
| Changchun | Jilin |
| Hangzhou | Zhejiang |
| Zhengzhou | Henan |
+--------------------+--------------+
10 rows in set (0.00 sec)