DDL.DCL,DML, DQL

DDL:数据库定义语言，比如创建，修改和删除数据库对象
DCL：数据库控制语句，权限方面
DML：数据库操纵语言
DQL：查询类语言

DDL语言：

创建库的方法：
1.create schema 库名；
2.create database 库名；

创建数据库指定字符集：
create database 库名 charset utf8;

选择大小写敏感：
create database 库名 charset utf8mb4 collate utf8mb4_bin;

建库规范：
1.库名不能大写
2.库名不能以数字开头
3.建库时必须加字符集
4.库名要和业务有关

删库：危险操作，生产中不要用此命令
drop database 库名；

修改库：
show create database 库名； 查看建库语句
alter database 库名 charset utf8; 修改字符集，但是在生产中一定要谨慎，修改后的字符集一定是原字符集的严格超级

创建表：
create table 表名 （
id INT NOT NULL PRIMARY KEY QUTO_INCREMENT COMMENT'学号',
name VARCHAR(255) NOT NULL COMMENT'姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT'年龄',
）ENGINE=INNODB CHARSET=UTF8 COMMENT'学生表';

建表规范：
1.表名小写
2.不能以数字开头
3.注意字符集和存储引擎
4.表名和业务有关
5.选择合适的数据类型
6.每个类都要有注释
7.每个列设置为非空，无法保证非空，用0代替

删除表：同样是在生产中不要用此命令
drop table 表名；

修改表：
show tables;查看所有表
desc 表名；查看列信息
show create table 列表；查看建表语句

添加列：默认在最后列添加
alter table 表名 add 列名 约束 ；

在某个列之后添加新列：
alter table 表名 add 列名 约束 after 想在哪个列之前添加的列名；

在第一列添加新列：
alter table 表名 add 列名 约束 first；

删除列：同样，生产中不要执行
alter table 表名 drop 列名；

修改列约束：所有属性必须重新写
alter table 表名 modify 列名 新的约束；

改名和属性：
alter table 表名 change 原列名 新列名 约束；
modify和change的区别在于modify值能修改属性，change可以都修改

DCL语言：

用户的所有权限是：
 SELECT,INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES,
 EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE 
超级管理员才有的权限是：
with grant option
一般在企业中创建用户给前四个权限就够用了，但是DELETE权限还需要根据情况授权

切记！！！！！！！！！！！！！！！！！！！！！！！
MySQL5.7中，可以不用提前创建用户，直接授权带创建用户，就像下面的操作，但是在8.0版本进行改进，必须先创建出用户在授权！！！！！！
用户的组成由名字+@'可连接网段'才是一个完整的用户！！！！！

单独授权用户：
grant SELECT,INSERT,UPDATE,DELETE on 库名.* to 用户名@'可连接网段' identified by "密码";

查看用户拥有哪些权限：
show grants for 用户名@'网段'；

收回权限：
revoke DELETE(就是权限名) on 用户名@'网段'；

DML语言：

对表中的数据进行增删改查 insert

标准的插入数据语法：
insert into 表名（列名1，列名2，列名3，列名4，列名5，列名6）
values
(数据1，数据2，数据3，数据4，数据5，数据6) 如
insert into 表名（id,name,age,sg,sfz,tel）
values
(1,张三，18，男，11000000000000000,13000000000) 

省事儿的语法，但是必须全部按顺序录入：
insert into 表名
values
( 1,张三，18，男，11000000000000000,13000000000 )

针对列录入：
insert into 表名（name,tel）
values
(李四，13200000000)

多行一起插入：
insert into 表名（name,tel）
values
(张三，13000000000)
(李四，12300000000）
(王五，13400000000)
(马六，13500000000)

update：修改，但是需谨慎！！！！！

修改整列的数据：
update 表名 set name='zhao4';
这样操作name列的所有的名字都被改成zhao4

针对某行修改，配合where使用;
update 表名 set name='zhao4' where id=2;
这样操作只把id为2的那一行的name改为zhao4

删除操作：谨慎！！！！！！

针对删除：
delete from 表名 where id=3;
删除id为3的行

全表删除：
delete from 表名；
truncate table 表名；
delete和truncate区别
delete:是逻辑性删除，逐行进行删除
truncate:属于ddl操作，对于标段中的数据页进行清空，速度快

生产中删除的操作太危险，甚至都不会给你删除的命令，也为了安全，我们做伪删除

添加状态列：默认值都设置为1
alter table 表名 add state(状态列名) TINYINT NOT NULL DEFAULT 1;
想要删除就把状态改为0
update 表名 set state=0 where id=6;
查询语句
select * from 表名 where state=1;

DQL语句：select

单独使用的情况：

查看系统参数：
select @@port;
select @@basedir;
select @@socket;
select @@server_id;

取函数值：
select NOW();
select DATABASE();
select USER();

from单表子句：
select 列名1，列名2, from 表名；
select * from 表名； 查看表中的所有列信息（不要对大表进行操作！！！！！！！） 

where单表子句：

比如数据库中有一张世界城市表(city)，列有属于哪个国家(countrycode)，省份(district)，名字(name)，人口(population).

看一下表结构：
db01 [world]>show create table city;
| city  | CREATE TABLE `city` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` char(35) NOT NULL DEFAULT '',
  `CountryCode` char(3) NOT NULL DEFAULT '',
  `District` char(20) NOT NULL DEFAULT '',
  `Population` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`ID`),
  KEY `CountryCode` (`CountryCode`),
  CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |

where配合等值查询

 查询所有中国的城市信息 
db01 [world]>select * from city where countrycode='CHN';

+------+---------------------+-------------+----------------+------------+

| ID   | Name                | CountryCode | District       | Population |

+------+---------------------+-------------+----------------+------------+

| 1890 | Shanghai            | CHN         | Shanghai       |    9696300 |

| 1891 | Peking              | CHN         | Peking         |    7472000 |

| 1892 | Chongqing           | CHN         | Chongqing      |    6351600 |

| 1893 | Tianjin             | CHN         | Tianjin        |    5286800 | 

查询辽宁省的城市信息
db01 [world]>select * from city where district='liaoning';

+------+------------+-------------+----------+------------+

| ID   | Name       | CountryCode | District | Population |

+------+------------+-------------+----------+------------+

| 1896 | Shenyang   | CHN         | Liaoning |    4265200 |

| 1902 | Dalian     | CHN         | Liaoning |    2697000 |

| 1918 | Anshan     | CHN         | Liaoning |    1200000 |

| 1919 | Fushun     | CHN         | Liaoning |    1200000 |

| 1933 | Benxi      | CHN         | Liaoning |     770000 |

| 1940 | Fuxin      | CHN         | Liaoning |     640000 |

查询你老家城市的信息
db01 [world]>select * from city where name='shenyang';
 +------+----------+-------------+----------+------------+
 | ID   | Name     | CountryCode | District | Population |
 +------+----------+-------------+----------+------------+
 | 1896 | Shenyang | CHN         | Liaoning |    4265200 |
 +------+----------+-------------+----------+------------+

where配合比较操作符

 查询世界上人口小于100的城市 
db01 [world]>SELECT * FROM city WHERE population < 100;

+------+-----------+-------------+----------+------------+

| ID   | Name      | CountryCode | District | Population |

+------+-----------+-------------+----------+------------+

| 2912 | Adamstown | PCN         | –        |         42 |

+------+-----------+-------------+----------+------------+

where配合逻辑运算符and or

查询中国人口数量大于500万的城市
db01 [world]>SELECT * FROM city WHERE countrycode='CHN' AND population>5000000;
 +------+-----------+-------------+-----------+------------+
 | ID   | Name      | CountryCode | District  | Population |
 +------+-----------+-------------+-----------+------------+
 | 1890 | Shanghai  | CHN         | Shanghai  |    9696300 |
 | 1891 | Peking    | CHN         | Peking    |    7472000 |
 | 1892 | Chongqing | CHN         | Chongqing |    6351600 |
 | 1893 | Tianjin   | CHN         | Tianjin   |    5286800 |
 +------+-----------+-------------+-----------+------------+
 查询人口数小于100和人口大于九百九十万的城市
db01 [world]>SELECT * FROM city WHERE population <100 OR population>9900000;
 +------+-----------------+-------------+-------------+------------+
 | ID   | Name            | CountryCode | District    | Population |
 +------+-----------------+-------------+-------------+------------+
 |  206 | São Paulo       | BRA         | São Paulo   |    9968485 |
 | 1024 | Mumbai (Bombay) | IND         | Maharashtra |   10500000 |
 | 2331 | Seoul           | KOR         | Seoul       |    9981619 |
 | 2912 | Adamstown       | PCN         | –           |         42 |
 +------+-----------------+-------------+-------------+------------+

where配合模糊查询

查询广字开头的省的城市
%不能写在前面，因为写在前面不走索引
db01 [world]>SELECT * FROM city WHERE district LIKE 'guang%';

+------+--------------------+-------------+-----------+------------+

| ID   | Name               | CountryCode | District  | Population |

+------+--------------------+-------------+-----------+------------+

| 1897 | Kanton [Guangzhou] | CHN         | Guangdong |    4256300 |

| 1920 | Nanning            | CHN         | Guangxi   |    1161800 |

| 1926 | Shenzhen           | CHN         | Guangdong |     950500 |

| 1942 | Liuzhou            | CHN         | Guangxi   |     610000 |

| 1943 | Shantou            | CHN         | Guangdong |     580000 |

| 1974 | Zhangjiang         | CHN         | Guangdong |     400997 |

| 1983 | Guilin             | CHN         | Guangxi   |     364130 |

| 1993 | Shaoguan           | CHN         | Guangdong |     350043 |

| 2002 | Chaozhou           | CHN         | Guangdong |     313469 |

| 2004 | Dongwan            | CHN         | Guangdong |     308669 |

| 2006 | Foshan             | CHN         | Guangdong |     303160 |

| 2018 | Zhongshan          | CHN         | Guangdong |     278829 |

| 2042 | Jiangmen           | CHN         | Guangdong |     230587 |

| 2052 | Yangjiang          | CHN         | Guangdong |     215196 |

| 2058 | Wuzhou             | CHN         | Guangxi   |     210452 |

where配合in语句

查询广东省和广西省的城市
db01 [world]>SELECT * FROM city WHERE district IN ('guangdong','guangxi');

+------+--------------------+-------------+-----------+------------+

| ID   | Name               | CountryCode | District  | Population |

+------+--------------------+-------------+-----------+------------+

| 1897 | Kanton [Guangzhou] | CHN         | Guangdong |    4256300 |

| 1920 | Nanning            | CHN         | Guangxi   |    1161800 |

| 1926 | Shenzhen           | CHN         | Guangdong |     950500 |

| 1942 | Liuzhou            | CHN         | Guangxi   |     610000 |

| 1943 | Shantou            | CHN         | Guangdong |     580000 |

| 1974 | Zhangjiang         | CHN         | Guangdong |     400997 |

where配合between and语句

查询世界上人口在100万至105万之间的城市
db01 [world]>SELECT * FROM city WHERE population BETWEEN 1000000 AND 1050000; 
 +------+-------------------+-------------+----------------+------------+
 | ID   | Name              | CountryCode | District       | Population |
 +------+-------------------+-------------+----------------+------------+
 |  457 | Birmingham        | GBR         | England        |    1013000 |
 | 1039 | Ludhiana          | IND         | Punjab         |    1042740 |
 | 1040 | Vadodara (Baroda) | IND         | Gujarat        |    1031346 |
 | 1041 | Kalyan            | IND         | Maharashtra    |    1014557 |
 | 1466 | Napoli            | ITA         | Campania       |    1002619 |
 | 1542 | Kitakyushu        | JPN         | Fukuoka        |    1016264 |
 | 1786 | Amman             | JOR         | Amman          |    1000000 |
 | 1810 | Montréal          | CAN         | Québec         |    1016376 |

group by单表子句+聚合函数

max 最大值
min 最小值
avg 平均值
sum 总和
count 个数
group_concat 列转行

查询city表每个国家的总人口数
看这个题的意思，最后展示出的肯定是第一列是国家，第二列相对应的是总人口数，所以select后面跟着的就是想要展示的列，group by跟着的，就是你前面的聚合函数，运算时以什么为分组，
每个国家的总人口数那么肯定是以国家为单位，如果说每个省的人口数，那么group by后面跟着的就是省

 查询city表每个国家的总人口数量 
db01 [world]>select countrycode,sum(population) from city group by countrycode;
 +-------------+-----------------+
 | countrycode | sum(population) |
 +-------------+-----------------+
 | ABW         |           29034 |
 | AFG         |         2332100 |
 | AGO         |         2561600 |
 | AIA         |            1556 |
 | ALB         |          270000 |
 | AND         |           21189 |
 | ANT         |            2345 |
 | ARE         |         1728336 |

查询中国各省的总人口数
db01 [world]>select district,sum(population) from city where countrycode='CHN' group by district;
 +----------------+-----------------+
 | district       | sum(population) |
 +----------------+-----------------+
 | Anhui          |         5141136 |
 | Chongqing      |         6351600 |
 | Fujian         |         3575650 |
 | Gansu          |         2462631 |
 | Guangdong      |         9510263 |
 | Guangxi        |         2925142 |
 | Guizhou        |         2512087 |
 | Hainan         |          557120 |

统计世界上每个国家的城市数量
db01 [world]>select countrycode,count(name) from city group by countrycode;
 +-------------+-------------+
 | countrycode | count(name) |
 +-------------+-------------+
 | ABW         |           1 |
 | CHN         |         363 |
 | USA         |         274 |

查询中国每个省的城市列表
db01 [world]>select district,group_concat(name) from 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   

统计中国各省的平均人数
db01 [world]>select district,avg(population) from city where countrycode='CHN' group by district;
 +----------------+-----------------+
 | district       | avg(population) |
 +----------------+-----------------+
 | Anhui          |     321321.0000 |
 | Chongqing      |    6351600.0000 |
 | Fujian         |     297970.8333 |
 | Gansu          |     351804.4286 |
 | Guangdong      |     475513.1500 |
 | Guangxi        |     325015.7778 |       

having单表子句

having的作用是返回的结果在进行判断过滤
注意！！！having条件是不走索引的，一般可以用临时表解决

统计中国每个省的总人口数，只打印总人口数小于100万的信息
db01 [world]>select district,sum(population) from city where countrycode='CHN'' group by 
district having sum(population) <1000000;
 +----------+-----------------+
 | district | sum(population) |
 +----------+-----------------+
 | Hainan   |          557120 |
 | Ningxia  |          802362 |
 | Qinghai  |          700200 |
 | Tibet    |          120000 |
 +----------+-----------------+

order by单表子句

order by 排序功能，默认就是从小到大排序，想要从大到小排序就加个desc就可以了

 中国城市信息，按人口数量从小到大排序 
db01 [world]>select * from city where countrycode='CHN' order by population;
 +------+---------------------+-------------+----------------+------------+
 | ID   | Name                | CountryCode | District       | Population |
 +------+---------------------+-------------+----------------+------------+
 | 2252 | Huangyan            | CHN         | Zhejiang       |      89288 |
 | 2251 | Pingyi              | CHN         | Shandong       |      89373 |
 | 2250 | Huaying             | CHN         | Sichuan        |      89400 |
 | 2249 | Junan               | CHN         | Shandong       |      90222 |
 | 2248 | Shaowu              | CHN         | Fujian         |      90286 |
 | 2247 | Xilin Hot           | CHN         | Inner Mongolia |      90646 |
 | 2246 | Linhai              | CHN         | Zhejiang       |      90870 |
 | 2245 | Putian              | CHN         | Fujian         |      91030 |
 | 2244 | Tumen               | CHN         | Jilin          |      91471 |
 | 2243 | Kaiyuan             | CHN         | Yunnan         |      91999 |
 | 2242 | Yuzhou              | CHN         | Henan          |      92889 |
 | 2241 | Bose                | CHN         | Guangxi        |      93009 |
 | 2240 | Enshi               | CHN         | Hubei          |      93056 |
 | 2239 | Emeishan            | CHN         | Sichuan        |      94000 |
 | 2238 | Jinzhou             | CHN         | Liaoning       |      95761 |
 | 2237 | Ya´an               | CHN         | Sichuan        |      95900 |
 | 2236 | Tong Xian           | CHN         | Peking         |      97168 |
 | 2235 | Zhangjiagang        | CHN         | Jiangsu        |      97994 |
 | 2234 | Jieyang             | CHN         | Guangdong      |      98531 |
 | 2233 | Xinzhou             | CHN         | Shanxi         |      98667 |
 | 2232 | Fuqing              | CHN         | Fujian         |      99193 |
 | 2231 | Pingliang           | CHN         | Gansu          |      99265 |
 | 2230 | Haining             | CHN         | Zhejiang       |     100478 |
 | 2229 | Kunshan             | CHN         | Jiangsu        |     102052 |

从打到小排序
db01 [world]>select * from city where countrycode='CHN' order by population desc;
 +------+---------------------+-------------+----------------+------------+
 | ID   | Name                | CountryCode | District       | Population |
 +------+---------------------+-------------+----------------+------------+
 | 1890 | Shanghai            | CHN         | Shanghai       |    9696300 |
 | 1891 | Peking              | CHN         | Peking         |    7472000 |
 | 1892 | Chongqing           | CHN         | Chongqing      |    6351600 |
 | 1893 | Tianjin             | CHN         | Tianjin        |    5286800 |
 | 1894 | Wuhan               | CHN         | Hubei          |    4344600 |
 | 1895 | Harbin              | CHN         | Heilongjiang   |    4289800 |
 | 1896 | Shenyang            | CHN         | Liaoning       |    4265200 |
 | 1897 | Kanton [Guangzhou]  | CHN         | Guangdong      |    4256300 |
 | 1898 | Chengdu             | CHN         | Sichuan        |    3361500 |
 | 1899 | Nanking [Nanjing]   | CHN         | Jiangsu        |    2870300 |
 | 1900 | Changchun           | CHN         | Jilin          |    2812000 |
 | 1901 | Xi´an               | CHN         | Shaanxi        |    2761400 |
 | 1902 | Dalian              | CHN         | Liaoning       |    2697000 |
 | 1903 | Qingdao             | CHN         | Shandong       |    2596000 |
 | 1904 | Jinan               | CHN         | Shandong       |    2278100 |
 | 1905 | Hangzhou            | CHN         | Zhejiang       |    2190500 |
 | 1906 | Zhengzhou           | CHN         | Henan          |    2107200 |

 统计中国，各省的总人口，找出大于500万的，并按总人口从大到小排序 
db01 [world]>select district,sum(population) from city where countrycode='CHN' group by 
district having sum(population)>5000000 order by sum(population) desc;
 +--------------+-----------------+
 | district     | sum(population) |
 +--------------+-----------------+
 | Liaoning     |        15079174 |
 | Shandong     |        12114416 |
 | Heilongjiang |        11628057 |
 | Jiangsu      |         9719860 |
 | Shanghai     |         9696300 |
 | Guangdong    |         9510263 |
 | Hubei        |         8547585 |
 | Jilin        |         7826824 |
 | Peking       |         7569168 |
 | Sichuan      |         7456867 |
 | Henan        |         6899010 |
 | Hebei        |         6458553 |
 | Chongqing    |         6351600 |
 | Zhejiang     |         5807384 |
 | Hunan        |         5439275 |  | Tianjin      |         5286800 |
 | Anhui        |         5141136 |
 +--------------+-----------------+

limit单表子句

limit显示几行，limit n，m
n是跳过多少航
m是显示几行
直接写limit 3，就是指跳过0行显示3行

统计中国，各省的总人口，找出大于500万的，并按总人口从大到小排序，只显示最多的前三名
db01 [world]>select district,sum(population) from city where countrycode='CHN' group by 
district having sum(population)>5000000 order by sum(population) desc limit 3;
 +--------------+-----------------+
 | district     | sum(population) |
 +--------------+-----------------+
 | Liaoning     |        15079174 |
 | Shandong     |        12114416 |
 | Heilongjiang |        11628057 |
 +--------------+-----------------+

union和union all联合查询

一般情况下，我们会将in语句或者or语句，改写成union all，来提升性能
union all 不去重
union 去重

SELECT * FROM city WHERE countrycode='CHN'
UNION ALL
SELECT * FROM city WHERE countrycode='USA'

SELECT * FROM city WHERE countrycode IN ('CHN','USA') 

多表联合查询from join

from 表1 join 表2
on 表1.列名=表2.列名

上边的都是在world库中的city表查询的，在增加一个country表
db01 [world]>show create table country;  
| CREATE TABLE country ( Code char(3) NOT NULL DEFAULT '', Name char(52) NOT NULL DEFAULT '', Continent enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia', Region char(26) NOT NULL DEFAULT '', SurfaceArea float(10,2) NOT NULL DEFAULT '0.00', IndepYear smallint(6) DEFAULT NULL, Population int(11) NOT NULL DEFAULT '0', LifeExpectancy float(3,1) DEFAULT NULL, GNP float(10,2) DEFAULT NULL, GNPOld float(10,2) DEFAULT NULL, LocalName char(45) NOT NULL DEFAULT '', GovernmentForm char(45) NOT NULL DEFAULT '', HeadOfState char(60) DEFAULT NULL, Capital int(11) DEFAULT NULL, Code2 char(2) NOT NULL DEFAULT '', PRIMARY KEY (Code) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

查看两表内容，找到关联列

db01 [world]>db01 [world]>select * from city limit 5;

+----+----------------+-------------+---------------+------------+

| 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 |

+----+----------------+-------------+---------------+------------+

5 rows in set (0.00 sec)

db01 [world]>select * from country limit 5;

+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+

| 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    |

+------+-------------+---------------+---------------------------+-------------+-----------+------------+----------------+---------+---------+-----------------------+----------------------------------------------+--------------------------+---------+-------+

可以看出city表中的countrycode和country表的code是一样的，所以就找他俩作为关联列

查询沈阳，显示国家，国家面积，沈阳人口
SELECT city.name,country.name,country.surfacearea,city.population 
FROM city JOIN country
ON city.countrycode=country.code
WHERE city.name='shenyang';
 +----------+-------+-------------+------------+
 | name     | name  | surfacearea | population |
 +----------+-------+-------------+------------+
 | Shenyang | China |  9572900.00 |    4265200 |
 +----------+-------+-------------+------------+
 1 row in set (0.01 sec)

别名

表别名和显示列别名

db01 [world]>SELECT a.name AS 城市名,
    -> b.name AS 国家名,
    -> b.surfacearea AS 国土面积,
    -> a.population AS 城市人口
    -> FROM city AS a JOIN country AS b
    -> ON a.countrycode=b.code
    -> WHERE a.name='shenyang';
+-----------+-----------+--------------+--------------+

| 城市名    | 国家名    | 国土面积     | 城市人口     |

+-----------+-----------+--------------+--------------+

| Shenyang  | China     |   9572900.00 |      4265200 |

+-----------+-----------+--------------+--------------+

1 row in set (0.01 sec)