【MySQL】SQL基础语法+运行原理

SQL基础语法+运行原理

基础语法部分基于公开网站sqlzoo的MySQL数据库引擎，必须使用英文界面练习代码（不同语言界面不同引擎可能导致数据库数据不同），并切换至Mysql引擎。
网址：https://sqlzoo.net/

• SQL查询语句语法结构和运行顺序
  语法结构：select–from–where–group by–having–order by–limit
  运行顺序：from–where–group by–having–order by–limit–select
  

• 运行原理
  from语句从数据库中调取复制一份表格
  where语句在复制的表格中筛选出符合条件的数据行
  group by语句在依据指定字段对筛选后的数据分区，将依据的字段去重分组
  having语句筛选满足条件的分组
  order by居于对筛选后的数据进行排序
  limit语句对排序后的数据限制显示的行
  select语句提取最后要显示的字段

1. select&from

1.1 标准语法

[ select字段名 ]：决定这一段查询最后展示的字段
[ from表名 ]：指定这段查询语句涉及的数据来源

#语法示例
SELECT name, continent, population FROM world

• 从单表中查询多列，在select后指定要查询的字段名称，多个字段名之间用英文逗号隔开，最后一个字段不需要加逗号；
• 查询结果的字段顺序按照select后的字段名顺序显示；
• SQL语句不区分大小写
• 一段标准的查询语句最后应当添加英文分号向数据库表明这一段查询语句已结束，但现在不写分号仍然能正常运行代码，甚至同时传递多段代码，依次输出多个查询结果；

#语法示例
SELECT * FROM world

• select后接*号表示从单表中查询所有的列，返回列的显示顺序为原始定义表的顺序

1.2 查询并修改列名

select 字段名 as 修改后字段名 from 表名

#语法示例
SELECT name as 国家名, continent 大洲, population 人口 FROM world

SELECT name 国家名, continent 大洲, population 人口 FROM world

• 仅修改查询表中的列名，不会修改原表；
• as可省略，写成字段名 别名即可（字段名与别名之间加空格），别名和下一个字段名之间为逗号

1.3 select中使用distinc去重

select中还可以加distinct对重复的行数据进行去重，保留某列中所有数据仅出现一次

select distinct 字段名 from 表名

#语法示例：列出world表中所有大洲（continent），且仅出现一次
SELECT DISTINCT continent FROM world

distinct可以对多列数据同时进行去重

#语法示例
SELECT DISTINCT name, continent FROM world

1.4 select中计算字段的运用

select提供直接计算功能，可直接使用字段名与运算符号进行计算后结果查询。在world表中仅有人口与GDP字段，使用GDP/POPULATION作为字段名可返回人均GDP。

#语法示例：查询国家名、gdp、人口及其人均gdp
SELECT name, gdp, population, gdp/population 人均GDP FROM world

2. where

2.1 标准语法

select字段名
from表名
[ where 表达式 ]：限定查询行必须满足的条件

where核心子句是可选项，使用该子句是为了通过表达式筛选出符合查询条件的行数据。多使用运算符对条件进行判断，常见运算符语法如下：

• =、>、<、>=、<=、<>、!=为比较运算符，用于判断表中的哪些数据符合条件；
• and ：一般用于组合两个及以上表达式，要求同时满足多个查询条件；
• or：一般用于组合两个及以上表达式，要求满足多个查询条件中的一个；
• not：一般和与其他连用例如not in，用于条件取反；
• and和not可以一起使用，但是默认and优先级高于or，可以使用括号来界定优先级；
• between and：用于查询两个值之间范围的值 （包含这两个值）；
• in：用于查询指定条件范围内的数据，一般为in (xxx,xxx,…)，用括号将条件括起来；
• is null，用于查询空值（NULL），空值不同于0，也不同于null字符串；

#语法示例：查询人口数至少2亿的国家名和人均gdp
SELECT name, gdp/population 人均GDP, population
FROM world
WHERE population >= 20000000

#语法示例：查询德国（Germany）的人口
SELECT population
FROM world
WHERE name = 'Germany'

• 判断符号后若跟的是字符串，需要使用单引号包裹，数字不需要

#语法示例：查询瑞典(Sweden)、挪威 (Norway) 和丹麦 (Denmark)的国家名和人口
SELECT population, name
FROM world
WHERE name in ('Germany', 'Sweden', 'Norway')

• in会筛选出字段值中所有与括号内数据相等的行

#语法示例：查询面积(area)在250000和300000之间的国家名和面积
SELECT area, name
FROM world
WHERE area between 250000 and 300000

2.2 模糊查询like

where子句的表达式中除了使用运算符来进行条件判断，还可以使用like操作符组合通配符进行模糊查询，标准使用语法为：where 字段名 like ‘通配符+字符’。通配符用来匹配值的一部分，跟在like后面进行数据过滤常用的通配符有 %和_，%用来匹配多个字符可以是零个、一个也可以是多个字符，_仅能用来匹配单个字符，多用来占位。

#语法示例：查询国家名中以C开头ia结尾的国家
SELECT name
FROM world
WHERE name like 'C%ia'

#语法示例：查询国家名中第二个字符为't'的国家
SELECT name
FROM world
WHERE name like '_t%'

#语法示例：查询国家名中含有两个o且被两个字符隔开的国家名
SELECT name
FROM world
WHERE name like '%o__o%'

2.3 多条件查询

使用and或者or逻辑运算符对多个条件进行组合筛选想要的数据

#语法示例：查询国家名中含有三个a且面积大于60万(600000)的国家及其面积
SELECT name, area
FROM world
WHERE name like '%a%a%a%' and area > 600000

#语法示例：查询国家名中含有三个a且面积大于60万(600000)的国家及其面积，或者人口大于13亿(1300000000)且面积大于500万(5000000)的国家及其面积
SELECT name, area
FROM world
WHERE name like '%a%a%a%' and area > 600000
or population > 1300000000 and area > 5000000

• and的运行优先级高于or；
• 若需要先运行or再运行and，可以使用括号来标记优先运行的部分

3. order by

select字段名
from表名
where 表达式
[ order by 字段名 asc|desc ]：规定查询出的结果集显示的顺序

order by核心子句是可选项，使用该子句是为了对被查询出的结果集，指定依据字段排序。asc指定该字段升序排序，desc为降序排序，不写则默认为升序排序。

#语法示例：查询姓名以Sir开头的获奖者(winner)，获奖年份(yr)和科目(subject)，查询结果按照年份从近到远排序，再按照姓名顺序升序排序
SELECT *
FROM nobel
WHERE winner like 'Sir%'
ORDER BY yr desc, winner

• order by后可加多个字段，按照所写字段的顺序一次作为排序依据

#语法示例：查询1984年所有获奖者的姓名和奖项科目。结果将诺贝尔化学奖和物理学奖排在最后，然后按照科目排序，再按照获奖者姓名排序
SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject in ('chemistry', 'physics'), subject, winner

• order by后使用in，会将in后的字段视为特殊字段，标识为1，其余为0，升序排序时将会放在最后

4. limit

select字段名
from表名
where 表达式
order by 字段名 asc|desc
[ limit [位置偏移量,]行数 ]：限制查询结果集显示的行数

limit子句是可选项，行数是子句中的必选参数，参数位置偏移量是可选参数。limit n表示返回查询结果的前n行；limit x,n表示从x+1行开始返回n行。

#语法示例：查询面积排名前三的国家
SELECT name
FROM world
ORDER BY area desc
LIMIT 3

#语法示例：查询人口数第4到第7的国家和人口
SELECT name
FROM world
ORDER BY population desc
LIMIT 3,4

• limit子句不同于其他核心子句，仅能在MySQL数据库中使用
• limit子句写在整段查询语句的最后一行

5. 聚合函数&group by

select字段名
from表名
where 表达式
[ group by 字段名 ]：规定依据哪个字段分组聚合
order by 字段名 asc|desc
limit [位置偏移量,]行数

聚合函数适用于需要获取数据的汇总信息，例如某字段行数、某字段平均值、某字段中最大最小数等，在SQL中使用group by进行操作，常见用法如下：

group by核心子句是可选项，使用该子句是为了依据相同字段值分组后进行聚合运算，常和聚合函数联用。

5.1 单独使用聚合函数

#语法示例：查询非洲总人口数
SELECT sum(population) 人口总数
FROM world
WHERE continent = 'Africa'

#语法示例：计算表格行数
SELECT count(*)
FROM world

• count(字段名)计算指定字段名下的总行数，但计算时将忽略空值的行；
• count(*)计算表中的总行数，不管某列是否有数值或者为空值；
• sum、avg、max、min函数必须之地那个字段进行聚合运算，无法使用*通配符，但这些指定字段名的聚合函数都会忽略空值行；
• 在不使用group by子句时只能使用简单常见聚合函数，不可以混合字段

5.2 单独使用group by

#语法示例：用group by 对大洲进行分组
SELECT continent
FROM world
GROUP BY continent

• group by子句具有数据去重的功能；
• distinct仅仅是返回不同的行，group by本质是先对指定的字段中相同的值分为一个区，再对字段进行去重

5.3 聚合函数和group by联用

#语法示例：查询每个大洲（continent）和大洲内的国家（name）数量
SELECT continent, count(name)
FROM world
GROUP BY continent

#语法示例：查询2013至2015年每年每个科目的获奖人数，结果按年份从大到小，人数从大到小排序
SELECT yr,subject, count(winner) 人数
FROM nobel
WHERE yr between 2013 and 2015
GROUP BY yr, subject
ORDER BY yr desc, count(winner) desc

• 当group by中有多个字段时，依据所写字段顺序一次对数据进行分区；
• 使用group by时，select只能使用聚合函数和group by引用过的字段

6. having

select 字段名
from 表名
where 表达式
group by 字段名
[having 表达式]：限定分组聚合后的查询行必须满足的条件
order by 字段名 asc|desc
limit [位置偏移量,]行数

having核心子句是可选项，使用该子句是为了对group by分组后的数据进行筛选。

#语法示例：查询总人口数量至少为1亿（100000000）的大洲
SELECT continent
FROM world
GROUP BY continent
HAVING sum(population) >= 100000000

• having子句不能脱离group by子句单独使用，其本质是对group by分组的筛选；
• having子句中只能使用聚合函数和group by作为分组依据的字段
• having的表达式与where表达式基本相同，但是having子句中可以使用聚合函数，where不可以
• 建议对行数据进行筛选时使用where子句，对含有聚合函数的筛选表达式使用having子句