MySQL面试题：SQL语句的基本语法

　　参考文章：数据库系统学习

　　参考文章：非关系型数据库讲解

　　参考文章：mysql学习（详细）

　　参考文章：函数依赖

　　参考文章1：范式

　　参考文章2：范式

　　人工管理阶段：在计算机出现之前，人们主要利用纸张和计算工具（如算盘和计算尺）来进行数据的记录和计算，依靠大脑来管理和利用数据。文件系统阶段：将数据存储在计算机的磁盘上。这些数据都以文件的形式存储，然后通过文件系统来管理这些文件。

　　数据库系统阶段：相对于文件系统来说，数据库系统实现了数据结构化。在文件系统中，独立文件内部的数据一般是有结构的，但文件之间不存在联系，因此整体来说是没有结构的。 数据库系统虽然也常常分成许多单独的数据文件，但是它更注意同一数据库中各数据文件之间的相互联系。

　　 关系型数据库是由多张能互相连接的表组成的数据库。非关系型数据库通常指数据以对象的形式存储在数据库中，而对象之间的关系通过每个对象自身的属性来决定。相信大家对关系型数据库已经非常了解，下面将以四大非关系型数据库讲解，来让我们看看两者区别 简述：目前对于非关系型数据库主要有四种数据存储类型：键值对存储（key-value），文档存储（document store），基于列的数据库（column-oriented），还有就是图形数据库（graph database）。每一种都会解决相应的问题，这些问题是关系型数据库所不能解决的。而在实际应用中都会将这几种情况结合起来实现相应的功能。

　　在开始介绍NoSQL数据库之前，我们先来回顾一下关系型数据库，这样我们可以对非关系数据库和关系型数据库做一个深入的比较。

　　例如：如果你想存储一个人的信息和这个人的爱好这样的数据，你可以创建两个表：一个用来存储这个人的信息，另一个表用来存储这个人的爱好。正如你在图一中看到的，你必须有一张额外的映射表，这张表将人的信息表和爱好表建立其对应的关系。这是因为他们的关系是多对多的关系，一个人可以有多个爱好，并且多个人可能会有相同的爱好。

　　

　　这里你就需要注意了，这种请款下你已经有一点违反关系型数据库严格遵循的标准化了，因为爱好是有重复的。

　　在基于行的数据库中进行查找的时候，每次都会对每一行进行遍历，不管某一列数据是否是你需要的都会进行遍历。假如你只需要生日是九月的人的数据，基于行的数据库会对这张表从上到下从左至右遍历一遍，正像下图看到的那样，最后再返回你需要的那些数据。对特定列的数据进行索引能有效的提高查找速度，但是索引每一列同样会带来额外的负载，并且数据库同样也是会遍历所有的列来取得要查找的数据。基于列的数据库会将每一列分开单独存放，当查找一个数量较小的列的时候其查找速度是很快的。

　　

　　说了这么多，那应该在什么时候使用基于行的数据库，在什么时候使用基于列的数据库呢？在基于列的数据库中要想增加一列新的数据是很容易的，因为现有的那些列是不会受新增列的影响的。但是要想增加一整条记录就需要适应所有的表，防止各个表的数据之间对应关系出现错误。因此这使得基于行的数据库在事务处理的时候要优胜于基于列的数据库，因为它很好的实现了数据的实时更新。 键值对中存储的数据的类型是不受限制的，可以是一个字符串，也可以是一个数字，甚至是由一系列的键值对封装成的对象等

　　

　　文档存储是基于键值对存储的，其结构较之于键值对存储更为复杂，可以说在键值对的基础上更深入了一步。

　　

　　现在剩下的是最后一个NoSQL数据库存储类型，也是最复杂的一个，主要使用一种高效的方式来存储各个实体之间的关系。当数据之间是紧密联系的，例如社会关系、科学论文的引文抑或是资本资产定价模型等等，使用图形数据库时最好的选择。图形或者网络数据有两部分组成：

　　Node-：实体本身，在一个社会关系中可以认为是一个人。Edge-：实体之间的关系。这个关系可以用一条线来表示，这条线有它自己的属性。这条线可以有方向，箭头可以表明谁是谁的上级。 如果给予足够的关系和实体类型，图形会变得非常的复杂，其发杂程度简直难以置信。

　　

　　DDL(Data Definition Language)数据定义语言

　　用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等DML(Data Manipulation Language)数据操作语言

　　用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等DQL(Data Query Language)数据查询语言

　　用来查询数据库中表的记录(数据)。关键字：select, where 等DCL(Data Control Language)数据控制语言(了解)

　　用来定义数据库的访问权限和安全级别，及创建用户。关键字：GRANT， REVOKE 等TCL(Transition Language) :事务控制语言，用来管理事务 ’alian’@'localhost’ ：表示只允许本机登录’alian’@’%’ ：表示任意地址登录’alian’@'192.168.0.100’ ：表示只允许ip为192.168.0.100的地址登录’alian’@‘192.168..’ ：表示只允许ip为192.168网段的地址登录 或者

　　或者

　　数据库名.表名

　　. 表示任意库的任意表（不建议）mysql.* 表示mysql库的任意表mysql.user 表示mysql库的user表 用户名@地址

　　’alian’@'localhost’ ：表示只允许本机登录’alian’@’%’ ：表示任意地址登录’alian’@'192.168.0.100’ ：表示只允许ip为192.168.0.100的地址登录’alian’@‘192.168..’ ：表示只允许ip为192.168网段的地址登录 实例

　　创建

　　查询

　　修改

　　删除

　　创建

　　查询

　　修改

　　删除

　　添加

　　删除

　　修改

　　3.1.1选择表中的若干列 3.1.2 选择表中的若干元组

　　3.1.3 ORDER BY 3.1.4 聚合函数 聚合函数的计算，排序null值

　　WHERE子句中是不能用聚集函数作为条件表达式

　　3.1.5 GROUP BY where 在分组之前进行限定，如果不满足条件，则不参与分组。having在分组之后进行限定，如果不满足结果，则不会被查询出来where 后不可以跟聚合函数having可以进行聚合函数的判断。 3.2.1 等值连接 SQL语句 执行结果

　　执行过程 嵌套循环法（NESTED-LOOP） 首先在表1中找到第一个元组，然后从头开始扫描表2，逐一查找满足连接件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。

　　表2全部查找完后，再找表1中第二个元组，然后再从头开始扫描表2，逐一查找满足连接条件的元组，找到后就将表1中的第二个元组与该元组拼接起来，形成结果表中一个元组。

　　重复上述操作，直到表1中的全部元组都处理完毕 排序合并法（SORT-MERGE） 常用于=连接首先按连接属性对表1和表2排序，对表1的第一个元组，从头开始扫描表2，顺序查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。当遇到表2中第一条大于表1连接字段值的元组时，对表2的查询不再继续

　　找到表1的第二条元组，然后从刚才的中断点处继续顺序扫描表2，查找满足连接条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组。直接遇到表2中大于表1连接字段值的元组时，对表2的查询不再继续

　　重复上述操作，直到表1或表2中的全部元组都处理完毕为止 索引连接（INDEX-JOIN） 对表2按连接字段建立索引对表1中的每个元组，依次根据其连接字段值查询表2的索引，从中找到满足条件的元组，找到后就将表1中的第一个元组与该元组拼接起来，形成结果表中一个元组 3.2.2 非等值连接 执行过程:

　　先从SC中挑选出Cno='2’并且Grade>90的元组形成一个中间关系，再和Student中满足连接条件的元组进行连接得到最终的结果关系 3.2.3 自身连接 自身连接：一个表与其自己进行连接需要给表起别名以示区别由于所有属性名都是同名属性，因此必须使用别名前缀

　　3.2.4 外连接 外连接与普通连接的区别 普通连接操作只输出满足连接条件的元组外连接操作以指定表为连接主体，将主体表中不满足连接条件的元组一并输出左外连接：列出左边关系中所有的元组右外连接：列出右边关系中所有的元组 SQL语句 执行结果

　　 3.3.1 嵌套查询概述 一个SELECT-FROM-WHERE语句称为一个查询块将一个查询块嵌套在另一个查询块的WHERE子句或HAVING短语的条件中的查询称为嵌套查询子查询不能使用ORDER BY子句 SQL语句 3.3.2 不相关子查询与相关子查询（非常重要，关乎子查询） 子查询的查询条件不依赖于父查询 由里向外 逐层处理。即每个子查询在上一级查询处理之前求解，子查询的结果用于建立其父查询的查找条件。 子查询的查询条件依赖于父查询 首先取外层查询中表的第一个元组，根据它与内层查询相关的属性值处理内层查询，若WHERE子句返回值为真，则取此元组放入结果表然后再取外层表的下一个元组重复这一过程，直至外层表全部检查完为止 3.3.3 带有IN谓词的子查询 3.3.4 带有比较运算符的子查询 3.3.5 带有ANY（SOME）或ALL谓词的子查询 3.3.6 带有EXISTS谓词的子查询 带有EXISTS谓词的子查询不返回任何数据，只产生逻辑真值“true”或逻辑假值“false”。

　　若内层查询结果非空，则外层的WHERE子句返回真值若内层查询结果为空，则外层的WHERE子句返回假值 集合操作的种类：并操作UNION、交操作INTERSECT、差操作EXCEPT集合操作限制：参加集合操作的各查询结果的列数必须相同;对应项的数据类型也必须相同 派生表：子查询不仅可以出现在WHERE子句中，还可以出现在FROM子句中，这时子查询生成的临时派生表（Derived Table）成为主查询的查询对象 主键约束：primary key

　　非空约束：not null

　　唯一约束：unique

　　外键约束：foreign key

　　默认值 ：Default

　　在创建数据表时添加约束。

　　下面语句执行成功之后，在表 tb_emp6 上添加了名称为 fk_emp_dept1 的外键约束，外键名称为 deptId，其依赖于表 tb_dept1 的主键 id。

　　在修改表时添加外键约束

　　删除外键约束

　　候选码： 若关系中的某一属性组的值能唯一地标识一个元组，而其子集不能，则称该属性组为候选码。主码： 若一个关系中有多个候选码，则选定其中一个为主码。主属性： 所有候选码的属性称为主属性。不包含在任何候选码中的属性称为非主属性或非码属性。 有一个关系模式S(Sno,Sname,Sage)：如果知道了一个学生的学号Sno，那我就能确定他的姓名Sname和年龄Sage。

　　如果我想知道某位学生的某一门课的成绩Grade，那我必须得同时知道他的学号Sno和课程号Cno。

　　但如果我只知道一部分信息，比如他的Sno或者Cno可以吗？答案是不行的！此时称Y[Grade]完全依赖于X[Sno,Cno]。

　　

　　如果我想知道某位学生的姓名Sname，那我知道他的学号Sno就可以了。也就是说Y[Sname]只函数依赖于X[Sno,Cno]中的子集x[Sno]，此时称Y部分函数依赖于X。

　　

　　有一个关系模式S(Sno,Sdept,Mname)

　　如果我知道了一个学生的学号Sno，那我就能知道他所在的系Sdept。(因为理论上一个学生只属于一个系)

　　如果我知道了某一个系Sdept，那么我就能知道这个系的系主任的姓名Mname。(一个系只有一个正的系主任，别杠，你赢了。)

　　也就是说，我知道了一个学生的学号Sno，其实我就知道了他所在系的系主任的姓名Mname。但这个过程中，他们是不存在直接函数依赖的，我需要通过系名称Sdept作为一个桥梁去把二者联系起来的。

　　

　　定义： 一个关系，至少存在三个属性（A、B、C），才能存在这种关系。对于每一个A值，有一组确定的B值和C值，并且这组B的值独立于这组C的值。

　　实例： 比如我们建立课程教师和教材的模型，我们规定，每门课程有对应的一组教师，每门课程也有对应的一组教材，一门课程使用的教程和教师没有关系。

　　

　　定义： 所有属性都不可再分，即数据项不可分

　　理解： 第一范式强调数据表的原子性，是其他范式的基础

　　

　　上表将商品这一数据项又划分为名称和数量两个数据项，故不符合第一范式关系。改正之后如下图所示：

　　

　　概念： 若某关系R属于第一范式，且每一个非主属性完全函数依赖于任何一个候选码，则关系R属于第二范式。

　　理解 ： 第二范式是指每个表必须有一个（有且仅有一个）数据项作为关键字或主键（primary key），其他数据项与关键字或者主键一一对应，即其他数据项完全依赖于关键字或主键。由此可知单主属性的关系均属于第二范式。

　　概念： 非主属性既不传递依赖于码，也不部分依赖于码。

　　理解 ：第三范式要求在满足第二范式的基础上，任何非主属性不依赖于其他非主属性，即在第二范式的基础上，消除了传递依赖。

　　理解： 根据定义我们可以得到结论，一个满足BC范式的关系模式有：

　　所有非主属性对每一个码都是完全函数依赖；所有主属性对每一个不包含它的码也是完全函数依赖；没有任何属性完全函数依赖于非码的任何一组属性。 实例： 比如我们有一个学生导师表，其中包含字段：学生ID，专业，导师，专业GPA，这其中学生ID和专业是联合主键。

　　

　　这个表的设计满足三范式，有主键，不存在主键的部分依赖，不存在非主键的传递依赖。但是这里存在另一个依赖关系，“专业”函数依赖于“导师”，也就是说每个导师只做一个专业方面的导师，只要知道了是哪个导师，我们自然就知道是哪个专业的了。

　　所以这个表的部分主键依赖于非主键部分，那么我们可以进行以下的调整，拆分成2个表：

　　

　　实例： 这样我们首先肯定有三个实体表，分别表示课程，教师和教材。现在我们要建立这三个对象的关系，于是我们建立的关系表，定义如下：

　　

　　这个表除了主键，就没有其他字段了，所以肯定满足BC范式，但是却存在多值依赖导致的异常。

　　假如我们下学期想采用一本新的英版高数教材，但是还没确定具体哪个老师来教，那么我们就无法在这个表中维护Course高数和Book英版高数教材的的关系。

　　解决办法是我们把这个多值依赖的表拆解成2个表，分别建立关系。这是我们拆分后的表：

　　

　　除了第四范式外，我们还有更高级的第五范式和域键范式（DKNF），第五范式处理的是无损连接问题，这个范式基本没有实际意义，因为无损连接很少出现，而且难以察觉。而域键范式试图定义一个终极范式，该范式考虑所有的依赖和约束类型，但是实用价值也是最小的，只存在理论研究中。

　　参考文章：数据类型

　　

　　

　　

　　YEAR 类型是一个单字节类型，用于表示年，在存储时只需要 1 个字节。可以使用各种格式指定 YEAR，如下所示：

　　以 4 位字符串或者 4 位数字格式表示的 YEAR，范围为 ‘1901’～’2155’。输入格式为 ‘YYYY’ 或者 YYYY，例如，输入 ‘2010’ 或 2010，插入数据库的值均为 2010。

　　以 2 位字符串格式表示的 YEAR，范围为 ‘00’ 到 ‘99’。‘00’～’69’ 和 ‘70’～’99’ 范围的值分别被转换为 2000～2069 和 1970～1999 范围的 YEAR 值。‘0’ 与 ‘00’ 的作用相同。插入超过取值范围的值将被转换为 2000。

　　以 2 位数字表示的 YEAR，范围为 1～99。1～99 和 70～99 范围的值分别被转换为 2001～2069 和 1970～1999 范围的 YEAR 值。注意，在这里 0 值将被转换为 0000，而不是 2000。

　　提示：两位整数范围与两位字符串范围稍有不同。例如，插入 3000 年，读者可能会使用数字格式的 0 表示 YEAR，实际上，插入数据库的值为 0000，而不是所希望的 3000。只有使用字符串格式的 ‘0’ 或 ‘00’，才可以被正确解释为 3000，非法 YEAR值将被转换为 0000。

　　可以使用各种格式指定 TIME 值，如下所示。

　　‘D HH：MM：SS’ 格式的字符串。还可以使用这些“非严格”的语法：‘HH：MM：SS’、‘HH：MM’、‘D HH’ 或 ‘SS’。这里的 D 表示日，可以取 0～34 之间的值。在插入数据库时，D 被转换为小时保存，格式为 “D*24+HH”。

　　‘HHMMSS’ 格式、没有间隔符的字符串或者 HHMMSS 格式的数值，假定是有意义的时间。例如，‘101112’ 被理解为’10：11：12’，但是 ‘106112’ 是不合法的（它有一个没有意义的分钟部分），在存储时将变为 00：00：00。

　　提示：为 TIME 列分配简写值时应注意：如果没有冒号，MySQL 解释值时，假定最右边的两位表示秒。（MySQL 解释 TIME 值为过去的时间而不是当前的时间）。例如，读者可能认为 ‘1112’ 和 1112 表示 11：12：00（即 11 点过 12 分钟），但MySQL 将它们解释为 00：11：12（即 11 分 12 秒）。同样 ‘12’ 和 12 被解释为00：00：12。相反，TIME 值中如果使用冒号则肯定被看作当天的时间，也就是说，‘11：12’ 表示 11：12：00，而不是 00：11：12。

　　在给 DATE 类型的字段赋值时，可以使用字符串类型或者数字类型的数据插入，只要符合 DATE 的日期格式即可。如下所示：

　　以 ‘YYYY-MM-DD’ 或者 ‘YYYYMMDD’ 字符中格式表示的日期，取值范围为 ‘1000-01-01’～’9999-12-3’。例如，输入 ‘2015-12-31’ 或者 ‘20151231’，插入数据库的日期为2015-12-31。

　　以 ‘YY-MM-DD’ 或者 ‘YYMMDD’ 字符串格式表示日期，在这里YY表示两位的年值。MySQL 解释两位年值的规则：‘00～69’ 范围的年值转换为 ‘20002069’，'7099’ 范围的年值转换为 ‘1970～1999’。例如，输入 ‘15-12-31’，插入数据库的日期为 2015-12-31；输入 ‘991231’，插入数据库的日期为 1999-12-31。

　　以 YYMMDD 数字格式表示的日期，与前面相似，00~69 范围的年值转换为 2000～2069，80～99 范围的年值转换为 1980～1999。例如，输入 151231，插入数据库的日期为 2015-12-31，输入 991231，插入数据库的日期为 1999-12-31。

　　使用 CURRENT_DATE 或者 NOW()，插入当前系统日期。

　　提示：MySQL 允许“不严格”语法：任何标点符号都可以用作日期部分之间的间隔符。例如，‘98-11-31’、‘98.11.31’、‘98/11/31’和’98@11@31’ 是等价的，这些值也可以正确地插入数据库。

　　如下所示。

　　以 ‘YYYY-MM-DD HH：MM：SS’ 或者 ‘YYYYMMDDHHMMSS’ 字符串格式表示的日期，取值范围为 ‘1000-01-01 00：00：00’～’9999-12-3 23：59：59’。例如，输入 ‘2014-12-31 05：05：05’ 或者 '20141231050505’，插入数据库的 DATETIME 值都为 2014-12-31 05：05：05。

　　以 ‘YY-MM-DD HH：MM：SS’ 或者 ‘YYMMDDHHMMSS’ 字符串格式表示的日期，在这里 YY 表示两位的年值。与前面相同，‘00～79’ 范围的年值转换为 ‘2000～2079’，‘80～99’ 范围的年值转换为 ‘1980～1999’。例如，输入 ‘14-12-31 05：05：05’，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

　　以 YYYYMMDDHHMMSS 或者 YYMMDDHHMMSS 数字格式表示的日期和时间。例如，输入 20141231050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05；输入 140505050505，插入数据库的 DATETIME 为 2014-12-31 05：05：05。

　　提示：MySQL 允许“不严格”语法：任何标点符号都可用作日期部分或时间部分之间的间隔符。例如，‘98-12-31 11：30：45’、‘98.12.31 11+30+35’、‘98/12/31 113045’ 和 ‘98@12@31 113045’ 是等价的，这些值都可以正确地插入数据库。

　　TIMESTAMP 的显示格式与 DATETIME 相同，显示宽度固定在 19 个字符，日期格式为 YYYY-MM-DD HH：MM：SS，在存储时需要 4 个字节。但是 TIMESTAMP 列的取值范围小于 DATETIME 的取值范围，为 '1970-01-01 00：00：01’UTC～’2038-01-19 03：14：07’UTC。在插入数据时，要保证在合法的取值范围内。

　　TIMESTAMP 也有一个 DATETIME 不具备的属性。默认情况下，当插入一条记录但并没有指定 TIMESTAMP 这个列值时，MySQL 会把 TIMESTAMP 列设为当前的时间。因此当需要插入记录和当前时间时，使用 TIMESTAMP 是方便的，另外 TIMESTAMP 在空间上比 DATETIME 更有效。

　　提示：协调世界时（英：Coordinated Universal Time，法：Temps Universel Coordonné）又称为世界统一时间、世界标准时间、国际协调时间。英文（CUT）和法文（TUC）的缩写不同，作为妥协，简称 UTC。

　　TIMESTAMP 与 DATETIME 除了存储字节和支持的范围不同外，还有一个最大的区别是：

　　DATETIME 在存储日期数据时，按实际输入的格式存储，即输入什么就存储什么，与时区无关；

　　而 TIMESTAMP 值的存储是以 UTC（世界标准时间）格式保存的，存储时对当前时区进行转换，检索时再转换回当前时区。即查询时，根据当前时区的不同，显示的时间值是不同的。

　　提示：如果为一个 DATETIME 或 TIMESTAMP 对象分配一个 DATE 值，结果值的时间部分被设置为 ‘00：00：00’，因此 DATE 值未包含时间信息。如果为一个 DATE 对象分配一个 DATETIME 或 TIMESTAMP 值，结果值的时间部分被删除，因此DATE 值未包含时间信息。

　　

　　今天写mysql的时候发现了一个问题，在查询的时候不小心把int类型的id加上引号查询了，但是也能查询出来，

　　于是查询了很多资料，最后得出的结论是，mysql会自动转换：当你类型为int 但传入的值为varchar 他会把前几个’数字挑出来’

　　转换为int 类型来进行查询

　　如果你的 aid = 1 你查询语句是SELECT * FROM 表名 WHERE aid=‘1dasd’;

　　在编译的时候，编译器会把 1 挑出来进行转换，在进行查询，

　　如果你的 aid = 1 你查询语句是SELECT * FROM 表名 WHERE aid=‘dasd’;

　　第一个不为数字，所以无法转换，查询出来的结果就为空(不会报错)，

　　如果你的 aid = 1 你查询语句是SELECT * FROM 表名 WHERE aid=‘123da’;

　　在编译的时候，编译器会把 123 挑出来进行转换，在进行查询，但查询结果为空（找不到这个对应的id）；

　　如果你的 aid = 1 你查询语句是SELECT * FROM 表名 WHERE aid=‘da1sd’;

　　查询结果为空，因为编译期是从第一个开始查看是否为数字，如果不是不会往后查；

　　规则一 空值与另一个值（包括另一个空值）的算术运算的结果为空值空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN。

　　有UNKNOWN后，传统二值（TRUE，FALSE）逻辑就扩展成了三值逻辑空值与另一个值（包括另一个空值）的逻辑运算

　　概述

　　虚表，是从一个或几个基本表（或视图）导出的表只存放视图的定义，不存放视图对应的数据基表中的数据发生变化，从视图中查询出的数据也随之改变