MySQL SQL介绍(2)
一、MySQL SQL介绍
1.1.1 select 单表查询
- select 查询语句的使用
1. select
1、作用
获取MySQL中的数据行
单独使用select
select @@xxxx;获取参数信息。
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3306 |
+--------+
1 row in set (0.00 sec
mysql> show variables like ‘%innodb%‘;
+------------------------------------------+------------------------+
| Variable_name | Value |
+------------------------------------------+------------------------+
| ignore_builtin_innodb | OFF |
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | ON |
| innodb_adaptive_hash_index_parts | 8 |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_chunk_size | 134217728 |
| innodb_buffer_pool_dump_at_shutdown | ON |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_dump_pct | 25 |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 1 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | ON |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 134217728 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | crc32 |
| innodb_checksums | ON |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_deadlock_detect | ON |
| innodb_default_row_format | dynamic |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Barracuda |
| innodb_file_per_table | ON |
| innodb_fill_factor | 100 |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_flush_neighbors | 1 |
| innodb_flush_sync | ON |
| innodb_flushing_avg_loops | 30 |
| innodb_force_load_corrupted | OFF |
| innodb_force_recovery | 0 |
| innodb_ft_aux_table | |
| innodb_ft_cache_size | 8000000 |
| innodb_ft_enable_diag_print | OFF |
| innodb_ft_enable_stopword | ON |
| innodb_ft_max_token_size | 84 |
| innodb_ft_min_token_size | 3 |
| innodb_ft_num_word_optimize | 2000 |
| innodb_ft_result_cache_limit | 2000000000 |
| innodb_ft_server_stopword_table | |
| innodb_ft_sort_pll_degree | 2 |
| innodb_ft_total_cache_size | 640000000 |
| innodb_ft_user_stopword_table | |
| innodb_io_capacity | 200 |
| innodb_io_capacity_max | 2000 |
| innodb_large_prefix | ON |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 16777216 |
| innodb_log_checksums | ON |
| innodb_log_compressed_pages | ON |
| innodb_log_file_size | 50331648 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | ./ |
| innodb_log_write_ahead_size | 8192 |
| innodb_lru_scan_depth | 1024 |
| innodb_max_dirty_pages_pct | 75.000000 |
| innodb_max_dirty_pages_pct_lwm | 0.000000 |
| innodb_max_purge_lag | 0 |
| innodb_max_purge_lag_delay | 0 |
| innodb_max_undo_log_size | 1073741824 |
| innodb_monitor_disable | |
| innodb_monitor_enable | |
| innodb_monitor_reset | |
| innodb_monitor_reset_all | |
| innodb_numa_interleave | OFF |
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
| innodb_online_alter_log_max_size | 134217728 |
| innodb_open_files | 2000 |
| innodb_optimize_fulltext_only | OFF |
| innodb_page_cleaners | 1 |
| innodb_page_size | 16384 |
| innodb_print_all_deadlocks | OFF |
| innodb_purge_batch_size | 300 |
| innodb_purge_rseg_truncate_frequency | 128 |
| innodb_purge_threads | 4 |
| innodb_random_read_ahead | OFF |
| innodb_read_ahead_threshold | 56 |
| innodb_read_io_threads | 4 |
| innodb_read_only | OFF |
| innodb_replication_delay | 0 |
| innodb_rollback_on_timeout | OFF |
| innodb_rollback_segments | 128 |
| innodb_sort_buffer_size | 1048576 |
| innodb_spin_wait_delay | 6 |
| innodb_stats_auto_recalc | ON |
| innodb_stats_include_delete_marked | OFF |
| innodb_stats_method | nulls_equal |
| innodb_stats_on_metadata | OFF |
| innodb_stats_persistent | ON |
| innodb_stats_persistent_sample_pages | 20 |
| innodb_stats_sample_pages | 8 |
| innodb_stats_transient_sample_pages | 8 |
| innodb_status_output | OFF |
| innodb_status_output_locks | OFF |
| innodb_strict_mode | ON |
| innodb_support_xa | ON |
| innodb_sync_array_size | 1 |
| innodb_sync_spin_loops | 30 |
| innodb_table_locks | ON |
| innodb_temp_data_file_path | ibtmp1:12M:autoextend |
| innodb_thread_concurrency | 0 |
| innodb_thread_sleep_delay | 10000 |
| innodb_tmpdir | |
| innodb_undo_directory | ./ |
| innodb_undo_log_truncate | OFF |
| innodb_undo_logs | 128 |
| innodb_undo_tablespaces | 0 |
| innodb_use_native_aio | ON |
| innodb_version | 5.7.26 |
| innodb_write_io_threads | 4 |
+------------------------------------------+------------------------+
134 rows in set (0.00 sec)
2、select 函数();
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| mysql |
+------------+
1 row in set (0.00 sec)
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2020-06-06 13:20:07 |
+---------------------+
1 row in set (0.00 sec)
mysql> select version();
+------------+
| version() |
+------------+
| 5.7.26-log |
+------------+
1 row in set (0.03 sec)
3、SQL92标准的使用语法
select语法执行顺序（单表）
select开始 ---->
from子句 --->
where子句--->
group by子句--->
select后执行条件--->
having子句 ---->
order by ---->
limit
- select 应用查询列子
mysql> use world
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
- 查询city表中的所有数据(from)
mysql> select * from city; #生产中较少使用，适合数据行少的表（基本没有）
+------+------------------------------------+-------------+------------------------+------------+
| ID | Name | CountryCode | District | Population |
+------+------------------------------------+-------------+------------------------+------------+
| 1 | Kabul | AFG | Kabol | 1780000 |
| 2 | Qandahar | AFG | Qandahar | 237500 |
| 3 | Herat | AFG | Herat | 186800 |
| 4 | Mazar-e-Sharif | AFG | Balkh | 127800 |
| 5 | Amsterdam | NLD | Noord-Holland | 731200 |
#查询name和population的所有值
mysql> desc city;
+-------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+----------------+
| ID | int(11) | NO | PRI | NULL | auto_increment |
| Name | char(35) | NO | | | |
| CountryCode | char(3) | NO | MUL | | |
| District | char(20) | NO | | | |
| Population | int(11) | NO | | 0 | |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> select name ,Population from city; #数据也很多，也不推荐
+------------------------------------+------------+
| name | Population |
+------------------------------------+------------+
| Kabul | 1780000 |
| Qandahar | 237500 |
| Herat | 186800 |
| Mazar-e-Sharif | 127800 |
| Amsterdam | 731200 |
- where 条件
#查询中国的城市信息
mysql> select name ,CountryCode from world.city where CountryCode=‘CHN‘ limit 10; #数据太多 ，我只取前10 下同
+--------------------+-------------+
| name | CountryCode |
+--------------------+-------------+
| Shanghai | CHN |
| Peking | CHN |
| Chongqing | CHN |
| Tianjin | CHN |
| Wuhan | CHN |
| Harbin | CHN |
| Shenyang | CHN |
| Kanton [Guangzhou] | CHN |
| Chengdu | CHN |
| Nanking [Nanjing] | CHN |
+--------------------+-------------+
10 rows in set (0.00 sec)
#查询美国的城市信息
mysql> select name ,CountryCode from world.city where CountryCode=‘USA‘ limit 10;
+--------------+-------------+
| name | CountryCode |
+--------------+-------------+
| New York | USA |
| Los Angeles | USA |
| Chicago | USA |
| Houston | USA |
| Philadelphia | USA |
| Phoenix | USA |
| San Diego | USA |
| Dallas | USA |
| San Antonio | USA |
| Detroit | USA |
+--------------+-------------+
10 rows in set (0.00 sec)
#查询一下世界上人口小于100人的城市
mysql> select name ,countrycode, population from world.city where population < 100;
+-----------+-------------+------------+
| name | countrycode | population |
+-----------+-------------+------------+
| Adamstown | PCN | 42 |
+-----------+-------------+------------+
1 row in set (0.00 sec)
#查询世界上人口大于10000000的城市
mysql> select name ,countrycode, population from world.city where population > 1000000 limit 10;
+--------------+-------------+------------+
| name | countrycode | population |
+--------------+-------------+------------+
| Kabul | AFG | 1780000 |
| Alger | DZA | 2168000 |
| Luanda | AGO | 2022000 |
| Buenos Aires | ARG | 2982146 |
| La Matanza | ARG | 1266461 |
| Córdoba | ARG | 1157507 |
| Yerevan | ARM | 1248700 |
| Sydney | AUS | 3276207 |
| Melbourne | AUS | 2865329 |
| Brisbane | AUS | 1291117 |
+--------------+-------------+------------+
10 rows in set (0.00 sec)
- 条件模糊查询 (where like)
#查询国家代号是C开头的城市
mysql> select name ,countrycode from world.city where countrycode like "C%" limit 10;
+-------------+-------------+
| name | countrycode |
+-------------+-------------+
| Bangui | CAF |
| Montréal | CAN |
| Calgary | CAN |
| Toronto | CAN |
| North York | CAN |
| Winnipeg | CAN |
| Edmonton | CAN |
| Mississauga | CAN |
| Scarborough | CAN |
| Vancouver | CAN |
+-------------+-------------+
10 rows in set (0.00 sec)
#注意：like 语句在MySQL中，不要出现%在前面的情况。因为效率很低，不走索引。
#这种前后都带% 都放在ElasticSearch、MongoDB等数据库里。
- 链接逻辑符号（and or）
#查询城市人口在10w到20w之间的城市
mysql> select name ,countrycode, population from world.city where population>=100000 and population<=200000 limit 10;
+----------------+-------------+------------+
| name | countrycode | population |
+----------------+-------------+------------+
| Herat | AFG | 186800 |
| Mazar-e-Sharif | AFG | 127800 |
| Tilburg | NLD | 193238 |
| Groningen | NLD | 172701 |
| Breda | NLD | 160398 |
| Apeldoorn | NLD | 153491 |
| Nijmegen | NLD | 152463 |
| Enschede | NLD | 149544 |
| Haarlem | NLD | 148772 |
| Almere | NLD | 142465 |
+----------------+-------------+------------+
10 rows in set (0.00 sec)
mysql> select name ,countrycode, population from world.city where population between 100000 and 200000 limit 10;
+----------------+-------------+------------+
| name | countrycode | population |
+----------------+-------------+------------+
| Herat | AFG | 186800 |
| Mazar-e-Sharif | AFG | 127800 |
| Tilburg | NLD | 193238 |
| Groningen | NLD | 172701 |
| Breda | NLD | 160398 |
| Apeldoorn | NLD | 153491 |
| Nijmegen | NLD | 152463 |
| Enschede | NLD | 149544 |
| Haarlem | NLD | 148772 |
| Almere | NLD | 142465 |
+----------------+-------------+------------+
10 rows in set (0.00 sec)
#查询一下中国或美国的城市信息
#第一种方法（性能最差）
mysql> select name, countrycode from world.city where countrycode=‘USA‘ or countrycode=‘CHN‘ limit 10;
+--------------------+-------------+
| name | countrycode |
+--------------------+-------------+
| Shanghai | CHN |
| Peking | CHN |
| Chongqing | CHN |
| Tianjin | CHN |
| Wuhan | CHN |
| Harbin | CHN |
| Shenyang | CHN |
| Kanton [Guangzhou] | CHN |
| Chengdu | CHN |
| Nanking [Nanjing] | CHN |
+--------------------+-------------+
10 rows in set (0.00 sec)
#第二种方法
mysql> select name, countrycode from world.city where countrycode in (‘USA‘ ,‘CHN‘) limit 10;
+--------------------+-------------+
| name | countrycode |
+--------------------+-------------+
| Shanghai | CHN |
| Peking | CHN |
| Chongqing | CHN |
| Tianjin | CHN |
| Wuhan | CHN |
| Harbin | CHN |
| Shenyang | CHN |
| Kanton [Guangzhou] | CHN |
| Chengdu | CHN |
| Nanking [Nanjing] | CHN |
+--------------------+-------------+
10 rows in set (0.00 sec)
#第三种方法（性能最优）
mysql> select name, countrycode from world.city where countrycode=‘USA‘
-> union all
-> select name, countrycode from world.city where countrycode=‘CHN‘ limit 10;
+--------------+-------------+
| name | countrycode |
+--------------+-------------+
| New York | USA |
| Los Angeles | USA |
| Chicago | USA |
| Houston | USA |
| Philadelphia | USA |
| Phoenix | USA |
| San Diego | USA |
| Dallas | USA |
| San Antonio | USA |
| Detroit | USA |
+--------------+-------------+
10 rows in set (0.00 sec)
8.group by 聚合函数应用(统计)
常用聚合函数：
AVG()
COUNT()
SUM()
MAX()
MIN()
GROUP_CONCAT()
#统计每个国家的总人口
mysql> select countrycode,sum(population) from world.city group by countrycode limit 10;
+-------------+-----------------+
| countrycode | sum(population) |
+-------------+-----------------+
| ABW | 29034 |
| AFG | 2332100 |
| AGO | 2561600 |
| AIA | 1556 |
| ALB | 270000 |
| AND | 21189 |
| ANT | 2345 |
| ARE | 1728336 |
| ARG | 19996563 |
| ARM | 1633100 |
+-------------+-----------------+
10 rows in set (0.00 sec)
#统计每个国家的城市个数
1、拿什么站队
GROUP BY countrycode
2、拿什么统计
城市id，name
3、统计的是什么？
COUNT(id) COUNT(NAME)
mysql> select countrycode,count(id) from world.city group by countrycode limit 10;
+-------------+-----------+
| countrycode | count(id) |
+-------------+-----------+
| ABW | 1 |
| AFG | 4 |
| AGO | 5 |
| AIA | 2 |
| ALB | 1 |
| AND | 1 |
| ANT | 1 |
| ARE | 5 |
| ARG | 57 |
| ARM | 3 |
+-------------+-----------+
10 rows in set (0.00 sec)
mysql> select countrycode,count(name) from world.city group by countrycode limit 10;
+-------------+-------------+
| countrycode | count(name) |
+-------------+-------------+
| ABW | 1 |
| AFG | 4 |
| AGO | 5 |
| AIA | 2 |
| ALB | 1 |
| AND | 1 |
| ANT | 1 |
| ARE | 5 |
| ARG | 57 |
| ARM | 3 |
+-------------+-------------+
10 rows in set (0.00 sec)
mysql> select countrycode, group_concat(district) from world.city group by countrycode limit 10\G;
*************************** 1. row ***************************
countrycode: ABW
group_concat(district): –
*************************** 2. row ***************************
countrycode: AFG
group_concat(district): Kabol,Qandahar,Herat,Balkh
*************************** 3. row ***************************
countrycode: AGO
group_concat(district): Luanda,Huambo,Benguela,Benguela,Namibe
*************************** 4. row ***************************
countrycode: AIA
group_concat(district): –,–
*************************** 5. row ***************************
countrycode: ALB
group_concat(district): Tirana
*************************** 6. row ***************************
countrycode: AND
group_concat(district): Andorra la Vella
*************************** 7. row ***************************
countrycode: ANT
group_concat(district): Cura?ao
*************************** 8. row ***************************
countrycode: ARE
group_concat(district): Dubai,Abu Dhabi,Sharja,Abu Dhabi,Ajman
*************************** 9. row ***************************
countrycode: ARG
group_concat(district): Distrito Federal,Buenos Aires,Córdoba,Santa Fé,Buenos Aires,Buenos Aires,Buenos Aires,Buenos Aires,Buenos Aires,Tucumán,Buenos Aires,Buenos Aires,Buenos Aires,Salta,Buenos Aires,Santa Fé,Buenos Aires,Buenos Aires,Buenos Aires,Buenos Aires,Buenos Aires,Buenos Aires,Buenos Aires,Buenos Aires,Buenos Aires,Corrientes,Buenos Aires,Buenos Aires,Buenos Aires,Chaco,Buenos Aires,Entre Rios,Mendoza,Misiones,Mendoza,Santiago del Estero,Jujuy,Buenos Aires,Neuquén,Buenos Aires,Buenos Aires,Formosa,Mendoza,La Rioja,Catamarca,Córdoba,Chubut,Mendoza,Buenos Aires,San Juan,Buenos Aires,Entre Rios,Buenos Aires,San Luis,Buenos Aires,Mendoza,Buenos Aires
*************************** 10. row ***************************
countrycode: ARM
group_concat(district): Yerevan,?irak,Lori
10 rows in set (0.00 sec)
#统计中国每个省的城市名列表
mysql> select district,group_concat(name) from world.city where countrycode=‘CHN‘ group by district;
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| district | group_concat(name) |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Anhui | Hefei,Huainan,Bengbu,Wuhu,Huaibei,Ma′anshan,Anqing,Tongling,Fuyang,Suzhou,Liu′an,Chuzhou,Chaohu,Xuangzhou,Bozhou,Huangshan |
| Chongqing | Chongqing |
| Fujian | Fuzhou,Amoy [Xiamen],Nanping,Quanzhou,Zhangzhou,Sanming,Longyan,Yong′an,Fu′an,Fuqing,Putian,Shaowu |
| Gansu | Lanzhou,Tianshui,Baiyin,Wuwei,Yumen,Jinchang,Pingliang |
| Guangdong | Kanton [Guangzhou],Shenzhen,Shantou,Zhangjiang,Shaoguan,Chaozhou,Dongwan,Foshan,Zhongshan,Jiangmen,Yangjiang,Zhaoqing,Maoming,Zhuhai,Qingyuan,Huizhou,Meixian,Heyuan,Shanwei,Jieyang |
| Guangxi | Nanning,Liuzhou,Guilin,Wuzhou,Yulin,Qinzhou,Guigang,Beihai,Bose |
| Guizhou | Guiyang,Liupanshui,Zunyi,Anshun,Duyun,Kaili |
| Hainan | Haikou,Sanya |
| Hebei | Shijiazhuang,Tangshan,Handan,Zhangjiakou,Baoding,Qinhuangdao,Xingtai,Chengde,Cangzhou,Langfang,Renqiu,Hengshui |
| Heilongjiang | Harbin,Qiqihar,Yichun,Jixi,Daqing,Mudanjiang,Hegang,Jiamusi,Shuangyashan,Tieli,Suihua,Shangzi,Qitaihe,Bei′an,Acheng,Zhaodong,Shuangcheng,Anda,Hailun,Mishan,Fujin |
| Henan | Zhengzhou,Luoyang,Kaifeng,Xinxiang,Anyang,Pingdingshan,Jiaozuo,Nanyang,Hebi,Xuchang,Xinyang,Puyang,Shangqiu,Zhoukou,Luohe,Zhumadian,Sanmenxia,Yuzhou |
| Hubei | Wuhan,Huangshi,Xiangfan,Yichang,Shashi,Shiyan,Xiantao,Qianjiang,Honghu,Ezhou,Tianmen,Xiaogan,Zaoyang,Jinmen,Suizhou,Xianning,Laohekou,Puqi,Shishou,Danjiangkou,Guangshui,Enshi |
| Hunan | Changsha,Hengyang,Xiangtan,Zhuzhou,Yueyang,Changde,Shaoyang,Yiyang,Chenzhou,Lengshuijiang,Leiyang,Loudi,Huaihua,Lianyuan,Hongjiang,Zixing,Liling,Yuanjiang |
| Inner * | Baotou,Hohhot,Yakeshi,Chifeng,Wuhai,Tongliao,Hailar,Jining,Ulanhot,Linhe,Zalantun,Manzhouli,Xilin Hot |
| Jiangsu | Nanking [Nanjing],Wuxi,Xuzhou,Suzhou,Changzhou,Zhenjiang,Lianyungang,Nantong,Yangzhou,Yancheng,Huaiyin,Jiangyin,Yixing,Dongtai,Changshu,Danyang,Xinghua,Taizhou,Huai′an,Qidong,Liyang,Yizheng,Suqian,Kunshan,Zhangjiagang |
| Jiangxi | Nanchang,Pingxiang,Jiujiang,Jingdezhen,Ganzhou,Fengcheng,Xinyu,Yichun,Ji′an,Shangrao,Linchuan |
| Jilin | Changchun,Jilin,Hunjiang,Liaoyuan,Tonghua,Siping,Dunhua,Yanji,Gongziling,Baicheng,Meihekou,Fuyu,Jiutai,Jiaohe,Huadian,Taonan,Longjing,Da′an,Yushu,Tumen |
| Liaoning | Shenyang,Dalian,Anshan,Fushun,Benxi,Fuxin,Jinzhou,Dandong,Liaoyang,Yingkou,Panjin,Jinxi,Tieling,Wafangdian,Chaoyang,Haicheng,Beipiao,Tiefa,Kaiyuan,Xingcheng,Jinzhou |
| Ningxia | Yinchuan,Shizuishan |
| Peking | Peking,Tong Xian |
| Qinghai | Xining |
| Shaanxi | Xi′an,Xianyang,Baoji,Tongchuan,Hanzhong,Ankang,Weinan,Yan′an |
| Shandong | Qingdao,Jinan,Zibo,Yantai,Weifang,Zaozhuang,Tai′an,Linyi,Tengzhou,Dongying,Xintai,Jining,Laiwu,Liaocheng,Laizhou,Dezhou,Heze,Rizhao,Liangcheng,Jiaozhou,Pingdu,Longkou,Laiyang,Wendeng,Binzhou,Weihai,Qingzhou,Linqing,Jiaonan,Zhucheng,Junan,Pingyi |
| Shanghai | Shanghai |
| Shanxi | Taiyuan,Datong,Yangquan,Changzhi,Yuci,Linfen,Jincheng,Yuncheng,Xinzhou |
| Sichuan | Chengdu,Panzhihua,Zigong,Leshan,Mianyang,Luzhou,Neijiang,Yibin,Daxian,Deyang,Guangyuan,Nanchong,Jiangyou,Fuling,Wanxian,Suining,Xichang,Dujiangyan,Ya′an,Emeishan,Huaying |
| Tianjin | Tianjin |
| Tibet | Lhasa |
| Xinxiang | Urumt?i [ürümqi],Shihezi,Qaramay,Ghulja,Qashqar,Aqsu,Hami,Korla,Changji,Kuytun |
| Yunnan | Kunming,Gejiu,Qujing,Dali,Kaiyuan |
| Zhejiang | Hangzhou,Ningbo,Wenzhou,Huzhou,Jiaxing,Shaoxing,Xiaoshan,Rui′an,Zhoushan,Jinhua,Yuyao,Quzhou,Cixi,Haining,Linhai,Huangyan |
+----------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
31 rows in set (0.00 sec)
#统计一下中国,每个省的总人口数
mysql> select countrycode,district,sum(population) from world.city where countrycode=‘CHN‘ group by district;
+-------------+----------------+-----------------+
| countrycode | district | sum(population) |
+-------------+----------------+-----------------+
| CHN | Anhui | 5141136 |
| CHN | Chongqing | 6351600 |
| CHN | Fujian | 3575650 |
| CHN | Gansu | 2462631 |
| CHN | Guangdong | 9510263 |
| CHN | Guangxi | 2925142 |
| CHN | Guizhou | 2512087 |
| CHN | Hainan | 557120 |
| CHN | Hebei | 6458553 |
| CHN | Heilongjiang | 11628057 |
| CHN | Henan | 6899010 |
| CHN | Hubei | 8547585 |
| CHN | Hunan | 5439275 |
| CHN | Inner * | 4121479 |
| CHN | Jiangsu | 9719860 |
| CHN | Jiangxi | 3831558 |
| CHN | Jilin | 7826824 |
| CHN | Liaoning | 15079174 |
| CHN | Ningxia | 802362 |
| CHN | Peking | 7569168 |
| CHN | Qinghai | 700200 |
| CHN | Shaanxi | 4297493 |
| CHN | Shandong | 12114416 |
| CHN | Shanghai | 9696300 |
| CHN | Shanxi | 4169899 |
| CHN | Sichuan | 7456867 |
| CHN | Tianjin | 5286800 |
| CHN | Tibet | 120000 |
| CHN | Xinxiang | 2894705 |
| CHN | Yunnan | 2451016 |
| CHN | Zhejiang | 5807384 |
+-------------+----------------+-----------------+
31 rows in set (0.00 sec)
#统计一下中国,每个省的平均人口
mysql> select countrycode,district,avg(population) from world.city where countrycode=‘CHN‘ group by district;
+-------------+----------------+-----------------+
| countrycode | district | avg(population) |
+-------------+----------------+-----------------+
| CHN | Anhui | 321321.0000 |
| CHN | Chongqing | 6351600.0000 |
| CHN | Fujian | 297970.8333 |
| CHN | Gansu | 351804.4286 |
| CHN | Guangdong | 475513.1500 |
| CHN | Guangxi | 325015.7778 |
| CHN | Guizhou | 418681.1667 |
| CHN | Hainan | 278560.0000 |
| CHN | Hebei | 538212.7500 |
| CHN | Heilongjiang | 553717.0000 |
| CHN | Henan | 383278.3333 |
| CHN | Hubei | 388526.5909 |
| CHN | Hunan | 302181.9444 |
| CHN | Inner * | 317036.8462 |
| CHN | Jiangsu | 388794.4000 |
| CHN | Jiangxi | 348323.4545 |
| CHN | Jilin | 391341.2000 |
| CHN | Liaoning | 718055.9048 |
| CHN | Ningxia | 401181.0000 |
| CHN | Peking | 3784584.0000 |
| CHN | Qinghai | 700200.0000 |
| CHN | Shaanxi | 537186.6250 |
| CHN | Shandong | 378575.5000 |
| CHN | Shanghai | 9696300.0000 |
| CHN | Shanxi | 463322.1111 |
| CHN | Sichuan | 355088.9048 |
| CHN | Tianjin | 5286800.0000 |
| CHN | Tibet | 120000.0000 |
| CHN | Xinxiang | 289470.5000 |
| CHN | Yunnan | 490203.2000 |
| CHN | Zhejiang | 362961.5000 |
+-------------+----------------+-----------------+
31 rows in set (0.00 sec)
- having (过滤)
#统计中国，每个省的总人口大于1000w的省及人口数
mysql> select countrycode,district,sum(population)
-> from world.city
-> where countrycode=‘CHN‘
-> group by district
-> having sum(population) >10000000; #不走索引的
+-------------+--------------+-----------------+
| countrycode | district | sum(population) |
+-------------+--------------+-----------------+
| CHN | Heilongjiang | 11628057 |
| CHN | Liaoning | 15079174 |
| CHN | Shandong | 12114416 |
+-------------+--------------+-----------------+
3 rows in set (0.00 sec)
#说明: having后的条件是不走索引的，可以进行一些优化手段处理。
10 . order by （正序） +desc（倒序）
#正序
mysql> select countrycode,district,sum(population)
-> from world.city
-> where countrycode=‘CHN‘
-> group by district
-> order by sum(population);
+-------------+----------------+-----------------+
| countrycode | district | sum(population) |
+-------------+----------------+-----------------+
| CHN | Tibet | 120000 |
| CHN | Hainan | 557120 |
| CHN | Qinghai | 700200 |
| CHN | Ningxia | 802362 |
| CHN | Yunnan | 2451016 |
| CHN | Gansu | 2462631 |
| CHN | Guizhou | 2512087 |
| CHN | Xinxiang | 2894705 |
| CHN | Guangxi | 2925142 |
| CHN | Fujian | 3575650 |
| CHN | Jiangxi | 3831558 |
| CHN | Inner * | 4121479 |
| CHN | Shanxi | 4169899 |
| CHN | Shaanxi | 4297493 |
| CHN | Anhui | 5141136 |
| CHN | Tianjin | 5286800 |
| CHN | Hunan | 5439275 |
| CHN | Zhejiang | 5807384 |
| CHN | Chongqing | 6351600 |
| CHN | Hebei | 6458553 |
| CHN | Henan | 6899010 |
| CHN | Sichuan | 7456867 |
| CHN | Peking | 7569168 |
| CHN | Jilin | 7826824 |
| CHN | Hubei | 8547585 |
| CHN | Guangdong | 9510263 |
| CHN | Shanghai | 9696300 |
| CHN | Jiangsu | 9719860 |
| CHN | Heilongjiang | 11628057 |
| CHN | Shandong | 12114416 |
| CHN | Liaoning | 15079174 |
+-------------+----------------+-----------------+
31 rows in set (0.00 sec)
#倒序
mysql> select countrycode,district,sum(population)
-> from world.city
-> where countrycode=‘CHN‘
-> group by district
-> order by sum(population) desc;
+-------------+----------------+-----------------+
| countrycode | district | sum(population) |
+-------------+----------------+-----------------+
| CHN | Liaoning | 15079174 |
| CHN | Shandong | 12114416 |
| CHN | Heilongjiang | 11628057 |
| CHN | Jiangsu | 9719860 |
| CHN | Shanghai | 9696300 |
| CHN | Guangdong | 9510263 |
| CHN | Hubei | 8547585 |
| CHN | Jilin | 7826824 |
| CHN | Peking | 7569168 |
| CHN | Sichuan | 7456867 |
| CHN | Henan | 6899010 |
| CHN | Hebei | 6458553 |
| CHN | Chongqing | 6351600 |
| CHN | Zhejiang | 5807384 |
| CHN | Hunan | 5439275 |
| CHN | Tianjin | 5286800 |
| CHN | Anhui | 5141136 |
| CHN | Shaanxi | 4297493 |
| CHN | Shanxi | 4169899 |
| CHN | Inner * | 4121479 |
| CHN | Jiangxi | 3831558 |
| CHN | Fujian | 3575650 |
| CHN | Guangxi | 2925142 |
| CHN | Xinxiang | 2894705 |
| CHN | Guizhou | 2512087 |
| CHN | Gansu | 2462631 |
| CHN | Yunnan | 2451016 |
| CHN | Ningxia | 802362 |
| CHN | Qinghai | 700200 |
| CHN | Hainan | 557120 |
| CHN | Tibet | 120000 |
+-------------+----------------+-----------------+
31 rows in set (0.00 sec)
#查询中国所有的城市，并以人口数降序输出
mysql> select countrycode,name,population
-> from world.city
-> where countrycode=‘CHN‘
-> order by population desc limit 10;
+-------------+--------------------+------------+
| countrycode | name | population |
+-------------+--------------------+------------+
| CHN | Shanghai | 9696300 |
| CHN | Peking | 7472000 |
| CHN | Chongqing | 6351600 |
| CHN | Tianjin | 5286800 |
| CHN | Wuhan | 4344600 |
| CHN | Harbin | 4289800 |
| CHN | Shenyang | 4265200 |
| CHN | Kanton [Guangzhou] | 4256300 |
| CHN | Chengdu | 3361500 |
| CHN | Nanking [Nanjing] | 2870300 |
+-------------+--------------------+------------+
10 rows in set (0.00 sec)
- limit m,n 分页(限制) 配合order by 使用
#查询中国所有的城市，并以输出6-10行
mysql> select countrycode,name,population from world.city where countrycode=‘CHN‘ order by population desc limit 5,5;
+-------------+--------------------+------------+
| countrycode | name | population |
+-------------+--------------------+------------+
| CHN | Harbin | 4289800 |
| CHN | Shenyang | 4265200 |
| CHN | Kanton [Guangzhou] | 4256300 |
| CHN | Chengdu | 3361500 |
| CHN | Nanking [Nanjing] | 2870300 |
+-------------+--------------------+------------+
5 rows in set (0.00 sec)
#limit 5,5 意思是前5行不用显示，从第6行开始显示5行（6.7.8.9.10）
#显示6-8行数数据
mysql> select countrycode,name,population from world.city where countrycode=‘CHN‘ order by population desc limit 5,3;
+-------------+--------------------+------------+
| countrycode | name | population |
+-------------+--------------------+------------+
| CHN | Harbin | 4289800 |
| CHN | Shenyang | 4265200 |
| CHN | Kanton [Guangzhou] | 4256300 |
+-------------+--------------------+------------+
3 rows in set (0.00 sec)