文章目录
分析表
查询是sql语句的重点和难点。这里导入了两个数据库分别是world.sql和company.sql是mysql的官方数据集，本文主要对world.sql执行操作。学习资源在此：。。。。。。（突然找不到了，等下导入）
--world库里有city、country、countrylanguage三张表
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| |
| country |
| |
| countrylanguage |
+-----------------+
--使用desc查看它们的表结构：
--country表结构：字段含义分别为：3字母的国家代码、国家名字、所处大洲、所处区域、国土面积、独立时间、国家人口、平均寿命、国民生产总值、旧的国民生产总值、当地对国家的称谓、政府组织形式、国家领导人、首都、2字母的国家代码
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
| Code | char(3) | NO | PRI | | |
| Name | char(52) | NO | | | |
| Continent | enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') | NO | | Asia | |
| Region | char(26) | NO | | | |
| SurfaceArea | float(10,2) | NO | | 0.00 | |
| IndepYear | smallint(6) | YES | | NULL | |
| Population | int(11) | NO | | 0 | |
| LifeExpectancy | float(3,1) | YES | | NULL | |
| GNP | float(10,2) | YES | | NULL | |
| GNPOld | float(10,2) | YES | | NULL | |
| LocalName | char(45) | NO | | | |
| GovernmentForm | char(45) | NO | | | |
| HeadOfState | char(60) | YES | | NULL | |
| Capital | int(11) | YES | | NULL | |
| Code2 | char(2) | NO | | | |
+----------------+---------------------------------------------------------------------------------------+------+-----+---------+-------+
--country表内容：
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+-------------------------------------+---------+-------+
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+----------------------------------------------+---------------+---------------------------+-------------+-----------+------------+----------------+------------+------------+----------------------------------------------+----------------------------------------------+-------------------------------------+---------+-------+
| ABW | Aruba | North America | Caribbean | 193.00 | NULL | 103000 | 78.4 | 828.00 | 793.00 | Aruba | Nonmetropolitan Territory of The Netherlands | Beatrix | 129 | AW |
| AFG | Afghanistan | Asia | Southern and Central Asia | 652090.00 | 1919 | 22720000 | 45.9 | 5976.00 | NULL | Afganistan/Afqanestan | Islamic Emirate | Mohammad Omar | 1 | AF |
| AGO | Angola | Africa | Central Africa | 1246700.00 | 1975 | 12878000 | 38.3 | 6648.00 | 7984.00 | Angola | Republic | José Eduardo dos Santos | 56 | AO |
| AIA | Anguilla | North America | Caribbean | 96.00 | NULL | 8000 | 76.1 | 63.20 | NULL | Anguilla | Dependent Territory of the UK | Elisabeth II | 62 | AI |
| ALB | Albania | Europe | Southern Europe | 28748.00 | 1912 | 3401200 | 71.6 | 3205.00 | 2500.00 | Shqip?ria | Republic | Rexhep Mejdani | 34 | AL |
| AND | Andorra | Europe | Southern Europe | 468.00 | 1278 | 78000 | 83.5 | 1630.00 | NULL | Andorra | Parliamentary Coprincipality | | 55 | AD |
| ANT | Netherlands Antilles | North America | Caribbean | 800.00 | NULL | 217000 | 74.7 | 1941.00 | NULL | Nederlandse Antillen | Nonmetropolitan Territory of The Netherlands | Beatrix | 33 | AN |
| ARE | United Arab Emirates | Asia | Middle East | 83600.00 | 1971 | 2441000 | 74.1 | 37966.00 | 36846.00 | Al-Imarat al-?Arabiya al-Muttahida | Emirate Federation | Zayid bin Sultan al-Nahayan | 65 | AE |
| ARG | Argentina | South America | South America | 2780400.00 | 1816 | 37032000 | 75.1 | 340238.00 | 323310.00 | Argentina | Federal Republic | Fernando de la Rúa | 69 | AR |
| ARM | Armenia | Asia | Middle East | 29800.00 | 1991 | 3520000 | 66.4 | 1813.00 | 1627.00 | Hajastan | Republic | Robert Kot?arjan | 126 | AM |
| ASM | American Samoa | Oceania | Polynesia | 199.00 | NULL | 68000 | 75.1 | 334.00 | NULL | Amerika Samoa | US Territory | George W. Bush | 54 | AS |
| ATA | Antarctica | Antarctica | Antarctica | 13120000.00 | NULL | 0 | NULL | 0.00 | NULL | – | Co-administrated | | NULL | AQ |
| ATF | French Southern territories | Antarctica | Antarctica | 7780.00 | NULL | 0 | NULL | 0.00 | NULL | Terres australes fran?aises | Nonmetropolitan Territory of France | Jacques Chirac | NULL | TF |
| ATG | Antigua and Barbuda | North America | Caribbean | 442.00 | 1981 | 68000 | 70.5 | 612.00 | 584.00 | Antigua and Barbuda | Constitutional Monarchy | Elisabeth II | 63 | AG |
| AUS | Australia | Oceania | Australia and New Zealand | 7741220.00 | 1901 | 18886000 | 79.8 | 351182.00 | 392911.00 | Australia | Constitutional Monarchy, Federation | Elisabeth II | 135 | AU |
| AUT | Austria | Europe | Western Europe | 83859.00 | 1918 | 8091800 | 77.7 | 211860.00 | 206025.00 | ?sterreich | Federal Republic | Thomas Klestil | 1523 | AT |
| AZE | Azerbaijan | Asia | Middle East | 86600.00 | 1991 | 7734000 | 62.9 | 4127.00 | 4100.00 | Az?rbaycan | Federal Republic | Heyd?r ?liyev | 144 | AZ |
--共有239个国家
`239 rows in set (0.01 sec)`
--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 | |
+-------------+----------+------+-----+---------+----------------+
--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 |
| 6 | Rotterdam | NLD | Zuid-Holland | 593321 |
| 7 | Haag | NLD | Zuid-Holland | 440900 |
| 8 | Utrecht | NLD | Utrecht | 234323 |
| 9 | Eindhoven | NLD | Noord-Brabant | 201843 |
| 10 | Tilburg | NLD | Noord-Brabant | 193238 |
| 11 | Groningen | NLD | Groningen | 172701 |
| 12 | Breda | NLD | Noord-Brabant | 160398 |
| 13 | Apeldoorn | NLD | Gelderland | 153491 |
| 14 | Nijmegen | NLD | Gelderland | 152463 |
| 15 | Enschede | NLD | Overijssel | 149544 |
| 16 | Haarlem | NLD | Noord-Holland | 148772 |
| 17 | Almere | NLD | Flevoland | 142465 |
| 18 | Arnhem | NLD | Gelderland | 138020 |
| 19 | Zaanstad | NLD | Noord-Holland | 135621 |
| 20 | ?s-Hertogenbosch | NLD | Noord-Brabant | 129170 |
| 21 | Amersfoort | NLD | Utrecht | 126270 |
| 22 | Maastricht | NLD | Limburg | 122087 |
| 23 | Dordrecht | NLD | Zuid-Holland | 119811 |
| 24 | Leiden | NLD | Zuid-Holland | 117196 |
| 25 | Haarlemmermeer | NLD | Noord-Holland | 110722 |
| 26 | Zoetermeer | NLD | Zuid-Holland | 110214 |
| 27 | Emmen | NLD | Drenthe | 105853 |
| 28 | Zwolle | NLD | Overijssel | 105819 |
| 29 | Ede | NLD | Gelderland | 101574 |
| 30 | Delft | NLD | Zuid-Holland | 95268 |
| 31 | Heerlen | NLD | Limburg | 95052 |
| 32 | Alkmaar | NLD | Noord-Holland | 92713 |
| 33 | Willemstad | ANT | Cura?ao | 2345 |
| 34 | Tirana | ALB | Tirana | 270000 |
| 35 | Alger | DZA | Alger | 2168000 |
| 36 | Oran | DZA | Oran | 609823 |
| 37 | Constantine | DZA | Constantine | 443727 |
| 38 | Annaba | DZA | Annaba | 222518 |
| 39 | Batna | DZA | Batna | 183377 |
| 40 | Sétif | DZA | Sétif | 179055 |
| 41 | Sidi Bel Abbès | DZA | Sidi Bel Abbès | 153106 |
| 42 | Skikda | DZA | Skikda | 128747 |
| 43 | Biskra | DZA | Biskra | 128281 |
| 44 | Blida (el-Boulaida) | DZA | Blida | 127284 |
| 45 | Béja?a | DZA | Béja?a | 117162 |
| 46 | Mostaganem | DZA | Mostaganem | 115212 |
| 47 | Tébessa | DZA | Tébessa | 112007 |
| 48 | Tlemcen (Tilimsen) | DZA | Tlemcen | 110242 |
| 49 | Béchar | DZA | Béchar | 107311 |
| 50 | Tiaret | DZA | Tiaret | 100118 |
| 51 | Ech-Chleff (el-Asnam) | DZA | Chlef | 96794 |
| 52 | Gharda?a | DZA | Gharda?a | 89415 |
| 53 | Tafuna | ASM | Tutuila | 5200 |
| 54 | Fagatogo | ASM | Tutuila | 2323 |
| 55 | Andorra la Vella | AND | Andorra la Vella | 21189 |
| 56 | Luanda | AGO | Luanda | 2022000 |
--共4079条记录
+----------+
| count(*) |
+----------+
| 4079 |
+----------+
--countrylanguage表结构：字段含义分别为：国家代码、语言、是否为官方语言、使用这个语言的人口占比
+-------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------+------+-----+---------+-------+
| CountryCode | char(3) | NO | PRI | | |
| Language | char(30) | NO | PRI | | |
| IsOfficial | enum('T','F') | NO | | F | |
| Percentage | float(4,1) | NO | | 0.0 | |
+-------------+---------------+------+-----+---------+-------+
--countrylanguage表内容
+-------------+---------------------------+------------+------------+
| CountryCode | Language | IsOfficial | Percentage |
+-------------+---------------------------+------------+------------+
| ABW | Dutch | T | 5.3 |
| ABW | English | F | 9.5 |
| ABW | Papiamento | F | 76.7 |
| ABW | Spanish | F | 7.4 |
| AFG | Balochi | F | 0.9 |
| AFG | Dari | T | 32.1 |
| AFG | Pashto | T | 52.4 |
| AFG | Turkmenian | F | 1.9 |
| AFG | Uzbek | F | 8.8 |
| AGO | Ambo | F | 2.4 |
| AGO | Chokwe | F | 4.2 |
| AGO | Kongo | F | 13.2 |
| AGO | Luchazi | F | 2.4 |
| AGO | Luimbe-nganguela | F | 5.4 |
| AGO | Luvale | F | 3.6 |
| AGO | Mbundu | F | 21.6 |
| AGO | Nyaneka-nkhumbi | F | 5.4 |
| AGO | Ovimbundu | F | 37.2 |
| AIA | English | T | 0.0 |
| ALB | Albaniana | T | 97.9 |
| ALB | Greek | F | 1.8 |
| ALB | Macedonian | F | 0.1 |
| AND | Catalan | T | 32.3 |
| AND | French | F | 6.2 |
| AND | Portuguese | F | 10.8 |
| AND | Spanish | F | 44.6 |
| ANT | Dutch | T | 0.0 |
| ANT | English | F | 7.8 |
| ANT | Papiamento | T | 86.2 |
| ARE | Arabic | T | 42.0 |
| ARE | Hindi | F | 0.0 |
| ARG | Indian Languages | F | 0.3 |
| ARG | Italian | F | 1.7 |
| ARG | Spanish | T | 96.8 |
| ARM | Armenian | T | 93.4 |
| ARM | Azerbaijani | F | 2.6 |
| ASM | English | T | 3.1 |
| ASM | Samoan | T | 90.6 |
| ASM | Tongan | F | 3.1 |
| ATG | Creole English | F | 95.7 |
| ATG | English | T | 0.0 |
| AUS | Arabic | F | 1.0 |
| AUS | Canton Chinese | F | 1.1 |
| AUS | English | T | 81.2 |
| AUS | German | F | 0.6 |
| AUS | Greek | F | 1.6 |
| AUS | Italian | F | 2.2 |
| AUS | Serbo-Croatian | F | 0.6 |
| AUS | Vietnamese | F | 0.8 |
| AUT | Czech | F | 0.2 |
| AUT | German | T | 92.0 |
| AUT | Hungarian | F | 0.4 |
| AUT | Polish | F | 0.2 |
| AUT | Romanian | F | 0.2 |
| AUT | Serbo-Croatian | F | 2.2 |
| AUT | Slovene | F | 0.4 |
| AUT | Turkish | F | 1.5 |
| AZE | Armenian | F | 2.0 |
| AZE | Azerbaijani | T | 89.0 |
| AZE | Lezgian | F | 2.3 |
| AZE | Russian | F | 3.0 |
| BDI | French | T | 0.0 |
| BDI | Kirundi | T | 98.1 |
| BDI | Swahili | F | 0.0 |
| BEL | Arabic | F | 1.6 |
| BEL | Dutch | T | 59.2 |
| BEL | French | T | 32.6 |
| BEL | German | T | 1.0 |
| BEL | Italian | F | 2.4 |
| BEL | Turkish | F | 0.9 |
| BEN | Adja | F | 11.1 |
| BEN | Aizo | F | 8.7 |
| BEN | Bariba | F | 8.7 |
| BEN | Fon | F | 39.8 |
| BEN | Ful | F | 5.6 |
| BEN | Joruba | F | 12.2 |
| BEN | Somba | F | 6.7 |
| BFA | Busansi | F | 3.5 |
| BFA | Dagara | F | 3.1 |
| BFA | Dyula | F | 2.6 |
| BFA | Ful | F | 9.7 |
| BFA | Gurma | F | 5.7 |
| BFA | Mossi | F | 50.2 |
。。。。。。
--共984条数据
984 rows in set (0.00 sec)
SQL注意 :
SQL 语言大小写不敏感。
SQL 可以写在一行或者多行
关键字不能被缩写也不能分行
各子句一般要分行写。
使用缩进提高语句的可读性。
一、普通单表查询
1.查询表中所有数据
--列出country表中的所有记录
select * from country;
2.查询表中特定字段的记录(列过滤)
select
code,
name,
population
from
country;
--给列起别名, 可以省略as关键字, 别名中如果有特殊符号, 可以使用""包围.
--起别名方便又保证可读性
select
population as pop,
name "国家 名称",
code
from
country;
超级重点！！！！！！关键字的执行顺序！！！！！
执行顺序 : 先from, 再where 最后select
就是from获取基表，where过滤不满足条件的行形成虚拟表，select从虚拟表中截取想要查看的字段再拼接
3.不注意关键字执行顺序会导致的问题
这是错误示例！！！！！
-- 错误!! where中不可以使用列的别名, 因为此时虚表的列还没有生成好.
SELECT
employee_id empId,
last_name name,
job_id job,
department_id deptId
FROM
employees
WHERE
deptId = 90 ;
小练习：查询人口总数大于1亿的国家的名称和大洲及人口, 给人口列起别名.
select
name,
continent,
population countryPop
from
country
where
population > 100000000;
+--------------------+---------------+------------+
| name | continent | countryPop |
+--------------------+---------------+------------+
| Bangladesh | Asia | 129155000 |
| Brazil | South America | 170115000 |
| China | Asia | 1277558000 |
| Indonesia | Asia | 212107000 |
| India | Asia | 1013662000 |
| Japan | Asia | 126714000 |
| Nigeria | Africa | 111506000 |
| Pakistan | Asia | 156483000 |
| Russian Federation | Europe | 146934000 |
| United States | North America | 278357000 |
+--------------------+---------------+------------+
10 rows in set (0.00 sec)
3.between、in、like、%、_在where后的使用
--Between a and b 且a和b都包含
--查询eployees表中工资在2500和3500之间的记录
SELECT last_name, salary
FROM employees
WHERE salary BETWEEN 2500 AND 3500; -- 相当于 salary >= 2500 and salary <= 3500
SELECT employee_id, last_name, salary, manager_id
FROM employees
WHERE manager_id IN (100, 101, 201); -- 相当于 manager_id = 101 or manager_id = 100 or manager_id = 201
--% 表示任意个任意字符
select
code,
name,
continent
from
country
where
name like '%na%'; --模糊查询
--_ 表示一个任意字符
select
code,
name,
continent
from
country
where
name like '__na%'; //模糊查询，比%更准确
4.关键字null的注意事项
--查询哪些国家没有首都
只要有null参与比较运算, 结果一定是false！！！！！！
--错误!!
select
name,
continent,
capital
from
country
where
capital = null;
--运行结果，不会显示正确的结果
Empty set (0.00 sec)
正确示例：
--查询哪些国家没有首都
select
name,
continent,
capital
from
country
where
capital is null;
--运行结果
+----------------------------------------------+------------+---------+
| name | continent | capital |
+----------------------------------------------+------------+---------+
| Antarctica | Antarctica | NULL |
| French Southern territories | Antarctica | NULL |
| Bouvet Island | Antarctica | NULL |
| Heard Island and McDonald Islands | Antarctica | NULL |
| British Indian Ocean Territory | Africa | NULL |
| South Georgia and the South Sandwich Islands | Antarctica | NULL |
| United States Minor Outlying Islands | Oceania | NULL |
+----------------------------------------------+------------+---------+
7 rows in set (0.00 sec)
--查询哪些国家有首都
select
name,
continent,
capital
from
country
where
capital is not null;
小练习：查询所有亚洲国家 (人口大于1亿或者尚未独立)的国家, 的代码,名称和大洲及人口
select
name,
code,
continent,
population,
indepYear
from
country
where
continent = 'asia'
and
(
population > 100000000
or
indepYear is null
);
--分析：1.from：获取country基表 2.where：行过滤，选取continent字段值为asia亚洲并且人口大于1亿或者isdeoYear为null的记录，生成虚拟表。3.从虚拟表中选取字段为，，，，拼接显示
5.去重distinct
--distinct 去重, 要求列真的有重复的
select
distinct
region,
continent//这里是按照两个的组合值来去重
from
country ;
--查询中国有哪些不同的省份.
select
distinct
district
from
city
where
countrycode = 'chn';
6.排序desc和asc
order by 可以排序, 只是给结果集虚表排序！！！！
默认是升充(asc)
降序必须指定(desc)
order by 可以使用列的别名.
select
distinct district dis
from
city
where
countrycode = 'chn' //先from获取表，然后where过滤行，然后select过滤列（生成虚表），然后distinct过滤行，最后order by排序
order by
dis desc;
order by
SELECT
last_name,
department_id,
salary
FROM
employees
ORDER BY
department_id,
salary DESC; //列1, 列2 先以列1排序, 再在【相同的列1数据中】, 再依据列2再微排.
+-------------+---------------+----------+
| last_name | department_id | salary |
+-------------+---------------+----------+
| Grant | NULL | 7000.00 |
| Whalen | 10 | 4400.00 |
| Hartstein | 20 | 13000.00 |
| Fay | 20 | 6000.00 |
| Raphaely | 30 | 11000.00 |
| Khoo | 30 | 3100.00 |
| Baida | 30 | 2900.00 |
| Tobias | 30 | 2800.00 |
| Himuro | 30 | 2600.00 |
| Colmenares | 30 | 2500.00 |
| Mavris | 40 | 6500.00 |
| Fripp | 50 | 8200.00 |
| Weiss | 50 | 8000.00 |
| Kaufling | 50 | 7900.00 |
| Vollman | 50 | 6500.00 |
| Mourgos | 50 | 5800.00 |
| Sarchand | 50 | 4200.00 |
| Bull | 50 | 4100.00 |
。。。。。。
超级重点！！！！！执行顺序！！！
步骤 :
- from 基表 2) where 过滤哪些行 3) select 选择哪些列 4) order by 以哪些列为排序依据.
--查看最富有的国家
select
code,
name,
GNP
from
country
order by
GNP desc;
二.多表查询(表联接)
create table city2
select * from city where name='london';//基于查询建表
create table country2
select * from country where code in('gbr', 'can');
mysql> select * from city2;
+------+--------+-------------+----------+------------+
| ID | Name | CountryCode | District | Population |
+------+--------+-------------+----------+------------+
| 456 | London | GBR | England | 7285000 |
| 1820 | London | CAN | Ontario | 339917 |
+------+--------+-------------+----------+------------+
2 rows in set (0.00 sec)
mysql> select * from country2;
+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+----------------+-------------------------------------+--------------+---------+-------+
| Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+----------------+-------------------------------------+--------------+---------+-------+
| CAN | Canada | North America | North America | 9970610.00 | 1867 | 31147000 | 79.4 | 598862.00 | 625626.00 | Canada | Constitutional Monarchy, Federation | Elisabeth II | 1822 | CA |
| GBR | United Kingdom | Europe | British Islands | 242900.00 | 1066 | 59623400 | 77.7 | 1378330.00 | 1296830.00 | United Kingdom | Constitutional Monarchy | Elisabeth II | 456 | GB |
+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+----------------+-------------------------------------+--------------+---------+-------+
--表内联接，生成笛卡尔集
select
*
from
city2,
country2;
--笛卡尔集中的数据绝大多数都是垃圾, 必须使用行过滤.
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+----------------+-------------------------------------+--------------+---------+-------+
| ID | Name | CountryCode | District | Population | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+----------------+-------------------------------------+--------------+---------+-------+
| 456 | London | GBR | England | 7285000 | CAN | Canada | North America | North America | 9970610.00 | 1867 | 31147000 | 79.4 | 598862.00 | 625626.00 | Canada | Constitutional Monarchy, Federation | Elisabeth II | 1822 | CA |
| 1820 | London | CAN | Ontario | 339917 | CAN | Canada | North America | North America | 9970610.00 | 1867 | 31147000 | 79.4 | 598862.00 | 625626.00 | Canada | Constitutional Monarchy, Federation | Elisabeth II | 1822 | CA |
| 456 | London | GBR | England | 7285000 | GBR | United Kingdom | Europe | British Islands | 242900.00 | 1066 | 59623400 | 77.7 | 1378330.00 | 1296830.00 | United Kingdom | Constitutional Monarchy | Elisabeth II | 456 | GB |
| 1820 | London | CAN | Ontario | 339917 | GBR | United Kingdom | Europe | British Islands | 242900.00 | 1066 | 59623400 | 77.7 | 1378330.00 | 1296830.00 | United Kingdom | Constitutional Monarchy | Elisabeth II | 456 | GB |
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+----------------+-------------------------------------+--------------+---------+-------+
select
*
from
city2,
country2--两表内连接
where
countrycode = code;--只要这两个值相等的行，就过滤了垃圾记录
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+----------------+-------------------------------------+--------------+---------+-------+
| ID | Name | CountryCode | District | Population | Code | Name | Continent | Region | SurfaceArea | IndepYear | Population | LifeExpectancy | GNP | GNPOld | LocalName | GovernmentForm | HeadOfState | Capital | Code2 |
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+----------------+-------------------------------------+--------------+---------+-------+
| 1820 | London | CAN | Ontario | 339917 | CAN | Canada | North America | North America | 9970610.00 | 1867 | 31147000 | 79.4 | 598862.00 | 625626.00 | Canada | Constitutional Monarchy, Federation | Elisabeth II | 1822 | CA |
| 456 | London | GBR | England | 7285000 | GBR | United Kingdom | Europe | British Islands | 242900.00 | 1066 | 59623400 | 77.7 | 1378330.00 | 1296830.00 | United Kingdom | Constitutional Monarchy | Elisabeth II | 456 | GB |
+------+--------+-------------+----------+------------+------+----------------+---------------+-----------------+-------------+-----------+------------+----------------+------------+------------+----------------+-------------------------------------+--------------+---------+-------+
内联接(不分左右)
--SQL99标准, 用内联代替逗号联接.
select
ci.name cityName,
ci.population cityPop,
co.name countryName,
co.population countryPop,
co.continent
from
city2 ci
inner join --内联接
country2 co
on
ci.countrycode = co.code -- 联接条件(可以看成最重要的行过滤条件)
where
ci.population > 5000000 -- 普通过滤
--有join就必须有on！！！
select
co.name countryName,
ci.name capitalName,
co.capital
from
country2 co
join
city2 ci
on
co.capital = ci.id ;
--有的国家没有人口没有首都（值为null），但却那条记录会被过滤掉
外联接
内联接的逻辑是从笛尔集中取出来的是满足联接条件的记录. 有可能会导致某张表的数据不完整（因为如果null值会导致结果false，那么连接后的一整条记录都会被过滤掉，但有时我们不希望被过滤，而是保留显示null值）.内连接没有左右之分
select
co.name countryName,
ci.name capitalName,
co.capital
from
country2 co
left outer join -- 左外联接, 保证左表数据完整，保证左表数据完整
city2 ci
on
co.capital = ci.id ;
select
co.name countryName,
ci.name capitalName,
co.capital
from
country2 co
right outer join -- 右外联接, 保证右表数据完整
city2 ci
on
co.capital = ci.id ;
--外联时可以省略outer关键字
select
co.name countryName,
ci.name capitalName,
co.capital
from
country2 co
left join
city2 ci
on
co.capital = ci.id ;
函数
1.单行函数
单行函数 -- 作用于结果集中的每一条记录的.
select
upper(name),
now(), //系统函数
concat(continent, code2) //拼接
from
country;
--查询国家表中的数据, 把国家名称,大洲, 国家代码 连接起来, 中间使用'=>'连接.
--concat(concat(concat(concat(name, '=>'), continent), '=>'), code)
select
concat(name, '=>', continent, '=>', code)
from
country;
2.组函数
组函数 – 作用于一组数据（group by）, 最终针对一组只有一个结果. 也称为统计处理。
avg() 平均
max() 最大
min() 最小
count() 计数
sum()求和
select
avg(population),
max(surfacearea)
from
country;
select
count(name)
from
country;
select
count(capital), -- 如果有null值, 所有的组函数都会忽略之
min(capital) --也会忽略null值
from
country;
总结
关于group by 分组以及查询的高难度运用的内容在后续文章中
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
