美文网首页
MySQL基础(一)—查询语句

MySQL基础(一)—查询语句

作者: 初来乍到_freman | 来源:发表于2019-02-28 20:26 被阅读0次

    MySQL基础(一)——查询语句

    1.导入示例数据库

    2.SQL是说明?MySQL是说明?

    3.查询语句 SELECT FROM

    语句解释:

    去重语句;

    前N个语句:

    CASE...END语句:

    4.筛选语句 WHERE

    语句解释: 运算符/通配符/操作符:

    5.分组语句 GROUP BY

    聚集函数: 语句解释: HAVING子句:

    6.排序语句 ORDER BY

    语句解释: 正序、逆序:

    7.函数

    时间函数: 数值函数: 字符串函数:

    8.SQL注释

    9.SQL代码规范

    [SQL编程格式的优化建议] [https://zhuanlan.zhihu.com/p/27466166](https://zhuanlan.zhihu.com/p/27466166)      [SQL Style Guide] [https://www.sqlstyle.guide/](https://www.sqlstyle.guide/)
    

    由于时间问题,参考了知乎专栏

    1. 导入示例数据库

    教程 MySQL导入示例数据库 - MySQL教程™,在上面链接中有示例数据库的下载链接,

    Navicat自带导入功能,如下截图所示:

    image

    右键local,点击运行SQL文件,按步骤操作,成功会显示如下界面:

    image

    2. 查询语句 SELECT FROM

    SELECT是最常用的SQL语句,没有之一,它的用途是从一个或多个表中检索信息,使用SELECT检索信息,至少要给出两条信息:

    1、想选择什么信息;

    2、从哪里选择信息;

    实例

    查询一列的数据

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n39" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT contactLastName FROM customers --contactLastName是列标签,customers是表名</pre>

    运行结果如下所示,有122条,如下所示:

    image

    利用distinct语句去重

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n43" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT DISTINCT contactLastName FROM customers -- contactLastName是列标签,customers是表名</pre>

    该查询语句在contactLastName加上distinct,只会保留唯一值,重复的会去掉,可以看到结果从122条变成108条,如下所示:

    image

    利用limit取前N个语句

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="text" contenteditable="true" cid="n47" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT contactLastName FROM customers LIMIT 10 -- contactLastName是列标签,customers是表名</pre>

    limit后面的数字可以为任意正整数,只要小于表的行数,结果如下:

    image

    利用CASE...END判断语句

    详细参考

    mysql操作查询结果case when then else end用法举例www.cnblogs.com

    4. 筛选语句 WHERE

    WHERE 子句配合 SELECT 语句,表示有条件的筛选信息,类似程序语言中的if条件。

    where语句常结合运算符、通配符操作,运算符常见有算术运算符、逻辑运算符、比较运算符等。

    详细参考:

    MySQL(六)之MySQL常用操作符 - 苦水润喉 - 博客园www.cnblogs.com!图标

    通配符

    image

    算术运算符

    “+”、“-”、“*”、“/”、“%”分别对应加减乘除求余。

    比较运算符

    “==”等于、“<=>”安全的等于、“<>(!=)”不等于、“<=”小于等于、“>=”大于等于、“IS NULL”判断一个值是否为NULL、“IS NOT NULL”判断一个值是否不为NULL。

    逻辑运算符

    NOT或者! 逻辑非

    AND或者&& 逻辑与

    OR或者|| 逻辑或

    XOR 逻辑异或

    实例

    利用where语句筛选

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n72" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT contactLastName FROM customers WHERE country = "USA" -- contactLastName是列标签,customers是表名</pre>

    选取国籍是USA的contactLastName,有36条信息:

    image

    where语句加上AND,多个条件同时满足

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n76" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT contactLastName FROM customers WHERE country = "USA" AND salesRepEmployeeNumber > 1200-- contactLastName是列标签,customers是表名</pre>

    选取国籍是USA、salesRepEmployeeNumber > 12的contactLastName,有18条信息:

    image

    where语句加上OR、IS NULL

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n80" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT contactLastName FROM customers WHERE country = "France" OR state IS NULL-- contactLastName是列标签,customers是表名</pre>

    只要满足一个条件就行,73条数据,如下:

    image

    5. 分组语句 GROUP BY

    GROUP BY 语句根据一个或多个列对结果集进行分组,可以理解成Excel上的透视表。HAVING子句常常配合GROUP BY使用,和where功能相似,where对行进行筛选,而HAVING则是对分组进行筛序。

    MySQL中提供的聚合函数可以用来统计、求和、求最值等等。

    分类:

    COUNT:统计行数量 SUM:获取单个列的合计值 AVG:计算某个列的平均值 MAX:计算列的最大值 MIN:计算列的最小值

    实例

    按国家分类,并统计出现的次数

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n90" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT country,count(*) as num FROM customers GROUP BY country</pre>

    结果如下,有27个国家:

    image

    利用having语句,统计出现次数大于3的国家

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n94" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT country,count() as num FROM customers GROUP BY country HAVING count() > 3</pre>

    筛选之后,只有8条信息,结果如下:

    image

    6. 排序语句 ORDER BY

    ORDER BY子句来设定你想按哪个字段哪种方式来进行排序,再返回搜索结果,常常配合ASC升序、DSEC降序使用。

    实例

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n100" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT country,count() as num
    FROM customers
    WHERE creditLimit > 0
    GROUP BY country HAVING count(
    ) > 3
    ORDER BY country DESC ,count(*) ASC</pre>

    GROUP BY语句常常在WHERE之后,ORDER BY之前,country是降序,国家计数是升序,结果如下:

    image

    \7. 函数

    时间函数太多,只选部分函数

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n105" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT NOW();-- 获得当前日期
    SELECT TIME('2017-05-15 10:37:14.123456');-- 获取时间
    SELECT YEAR('2017-05-15 10:37:14.123456');-- 获取年份
    SELECT MONTH('2017-05-15 10:37:14.123456');-- 获取月份
    SELECT DAY('2017-05-15 10:37:14.123456');-- 获取日
    SELECT HOUR('2017-05-15 10:37:14.123456');-- 获取时
    SELECT FROM_UNIXTIME(1494815834);-- 将时间戳转为具体时间</pre>

    数值函数

    image

    字符串函数

    image

    这一节实例相对简单,不做具体说明。

    \8. SQL注释

    “--”之后的文本属于注释。

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n113" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT NOW();-- 获得当前日期</pre>

    “//”从/开始,到/结束,//之间的任何内容都是注释

    \9. SQL代码规范

    SQL编程格式的优化建议(https://zhuanlan.zhihu.com/p/27466166)

    SQL Style Guide(http://link.zhihu.com/?target=https%3A//www.sqlstyle.guide/)

    题目一:

    数据表如下如图所示,查找重复的电子邮箱

    image

    利用HAVING的筛选分组功能

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n122" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT Email as NUM FROM email GROUP BY Email HAVING count(Email) > 1</pre>

    输出:

    image

    题目二:

    如果一个国家的面积超过300万平方公里,或者(人口超过2500万并且gdp超过2000万),那么这个国家就是大国家。

    编写一个SQL查询,输出表中所有大国家的名称、人口和面积。

    image

    利用where和运算符做筛选

    <pre spellcheck="false" class="md-fences md-end-block ty-contain-cm modeLoaded" lang="mysql" contenteditable="true" cid="n130" mdtype="fences" style="box-sizing: border-box; overflow: visible; font-family: Monaco, Consolas, "Andale Mono", "DejaVu Sans Mono", monospace; margin-top: 0px; margin-bottom: 20px; background-image: inherit; background-position: inherit; background-size: inherit; background-repeat: inherit; background-attachment: inherit; background-origin: inherit; background-clip: inherit; background-color: rgb(54, 59, 64); font-size: 0.9rem; display: block; break-inside: avoid; text-align: left; white-space: normal; position: relative !important; padding: 10px 30px; border: 1px solid; width: inherit; color: rgb(184, 191, 198); font-style: normal; font-variant-ligatures: normal; font-variant-caps: normal; font-weight: 400; letter-spacing: normal; orphans: 2; text-indent: 0px; text-transform: none; widows: 2; word-spacing: 0px; -webkit-text-stroke-width: 0px; text-decoration-style: initial; text-decoration-color: initial;">SELECT name,population, area FROM world WHERE area > 3000000 OR (population > 25000000 AND gdp > 20000000)</pre>

    结果如下,和预期一致:

    相关文章

      网友评论

          本文标题:MySQL基础(一)—查询语句

          本文链接:https://www.haomeiwen.com/subject/pszkuqtx.html