WHERe <表达式> <操作符> (子查询)
其中，操作符可以是比较运算符（<”、“<=”、“>”、“>=”、“！=”等）和 IN、NOT IN、EXISTS、NOT EXISTS 、 ANY（SOME）等关键字。
1、单行子查询 （就是查询结果是一个值用比较运算符）：单行子查询是指子查询的返回结果只有一行数据。当主查询语句的条件语句中引用子查询结果时可用单行比较符号（＝, >, <, >=, <=, <>）来进行比较。 例： select ename,deptno,sal from emp where deptno=(select deptno from dept where loc='NEW YORK')； 2、多行子查询（就是查询结果是多个值用in，all，any）：多行子查询即是子查询的返回结果是多行数据。当主查询语句的条件语句中引用子查询结果时必须用多行比较符号（IN，ALL,ANY）来进行比较。其中，IN的含义是匹配子查询结果中的任一个值即可（"IN" 操作符，能够测试某个值是否在一个列表中），ALL则必须要符合子查询的所有值才可，ANY要符合子查询结果的任何一个值即可。
注意：ALL和ANY 操作符不能单独使用，而只能与单行比较符（=、>、< 、>= 、<= 、<>）结合使用。
1）IN | NOT IN
当表达式与子查询返回的结果集中的某个值相等时，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回值正好相反。2）EXISTS | NOT EXISTS
用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 TRUE，否则返回 FALSE；若使用关键字 NOT，则返回的值正好相反。
1. 使用IN进行子查询
使用in进行子查询，这个我们在日常写sql的时候是经常遇到的。in的意思就是指定的一个值是否在这个集合中，如何在就返回TRUE；否则就返回FALSE了。
in是“=any”的别名，在使用“=any”的地方，我们都可以使用“in”来进行替换。
有了in，肯定就有了not in；not in并不是和<>any是同样的意思，not in和<>all是一个意思。
2. ANY进行子查询
any关键词的意思是“对于子查询返回的列中的任何一个数值，如果比较结果为TRUE，就返回TRUE”。
好比“10 >any(11, 20, 2, 30)”，由于10>2，所以，该该判断会返回TRUE；只要10与集合中的任意一个进行比较，得到TRUE时，就会返回TRUE。
any的意思比较好明白，直译就是任意一个，只要条件满足任意的一个，就返回TRUE。
any和all的区别：
any关键字表示满足其中的任意一个条件，使用any关键字时，只要满足内层查询语句结果的的任意一个，就可以通过该条件来执行外层查询语句。
all和any刚好是相反的，all关键字表示满足所有结果，使用all关键字，要满足内层查询语句的所有结果，才可以通过该条件来执行外层查询语句。
3. 使用SOME进行子查询
some是any的别名，用的比较少。
4. 使用ALL进行子查询
all必须与比较操作符一起使用。all的意思是“对于子查询返回的列中的所有值，如果比较结果为TRUE，则返回TRUE”。
好比“10 >all(2, 4, 5, 1)”，由于10大于集合中的所有值，所以这条判断就返回TRUE；而如果为“10 >all(20, 3, 2, 1, 4)”，这样的话，由于10小于20，所以该判断就会返回FALSE。
<>all的同义词是not in，表示不等于集合中的所有值，这个很容易和<>any搞混，平时多留点心就好了。
5. 带exists的子查询
exists: 是否存在的意思, exists子查询就是用来判断某些条件是否满足(跨表), exists是接在where之后 exists返回的结果只有0和1（也就是true和false）.
例：如果存在成绩大于90的人则列出整个表的记录
1，事务是什么？ 事务是一组SQL语句（包含一个或者多个SQL语句），要么全部执行成功，要么全部执行失败。通常一个事务对应一个完整的业务(例如银行账户转账业务，该业务就是一个最小的工作单元)。
事务是MySQL区别于NoSQL的重要特征，是保证关系型数据库数据一致性的关键技术。事务可看作是对数据库操作的基本执行单元，可能包含一个或者多个SQL语句。这些语句在执行时，要么都执行，要么都不执行。
事务中一些概念：
事务（Transaction）：一批操作（一组DML）
开启事务：Start Transaction
事务的提交：COMMIT
事务的回滚：ROLLBACK
事务的关闭：CLOSE
禁用或启用事务的自动提交模式：SET AUTOCOMMIT
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
默认情况下一个SQL语句为一个事务。
关于事务的回滚需要注意：只能回滚insert、delete和update语句，不能回滚select（回滚select没有任何意义），对于create、drop、alter这些无法回滚事务只对DML有效果。
注意：rollback，或者commit后事务就结束了。
如何使用： （1）在执行sql语句之前，我们要开启事务 start transaction; （2）正常执行我们的sql语句 （3）当sql语句执行完毕，存在两种情况： 1，全都成功，我们要将sql语句对数据库造成的影响提交到数据库中，committ 2，某些sql语句失败，我们执行rollback（回滚），将对数据库操作赶紧撤销
● 原子性（Atomicity）
整个事务中的所有操作，必须作为一个单元全部完成（或全部取消）。
事务是最小单元，不可再分，要么全部执行成功，要么全部失败回滚。
● 一致性（Consistency）
在事务开始之前与结束之后，数据库都保持一致状态。
一致性是指事务必须使数据库从一个一致的状态变到另外一个一致的状态，也就是执行事务之前和之后的状态都必须处于一致 的状态。不一致性包含三点：脏读，不可重复读，幻读
● 隔离性(Isolation)
一个事务不会影响其他事务的运行。
隔离性是指当多个用户并发访问数据库时，比如操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所 干扰，多个并发事务之间要相互隔离
● 持久性(Durability)
在事务完成以后，该事务对数据库所作的更改将持久地保存在数据库之中，并不会被回滚。
DBMS（数据库管理系统）对数据的修改是永久性的。
当数据库出现有多个事务同时执行时（也就是并发情况下），就可能出现，，等问题。
（1）脏读：当前事务中读到其他事务未提交的数据，也就是脏数据。
以上图为例，事务A在读取文章的阅读量时，读取到了事务B为提交的数据。如果事务B最后没有顺利提交，导致事务回滚，那么实际上阅读量并没有修改成功，而事务A却是读到的修改后的值，显然不合情理。
（2）不可重复读：在事务A中先后两次读取同一个数据，但是两次读取的结果不一样。脏读与不可重复读的区别在于：前者读到的是其他事务未提交的数据，后者读到的是其他事务已提交的数据。
以上图为例，事务A在先后读取文章阅读量的数据时，结果却不一样。说明事务A在执行的过程中，阅读量的值被其它事务给修改了。这样使得数据的查询结果不再可靠，同样也不合实际。
（3）幻读：在事务A中按照某个条件先后两次查询数据库，两次查询结果的行数不同，这种现象称为幻读。不可重复读与幻读的区别可以通俗的理解为：前者是数据变了，后者是数据的行数变了。
以上图为例，当对0<阅读量<100的文章进行查询时，先查到了一个结果，后来查询到了两个结果。这表明同一个事务的查询结果数不一，行数不一致。这样的问题使得在根据某些条件对数据筛选的时候，前后筛选结果不具有可靠性。
当数据库出现有多个事务同时执行时，就可能出现，，等问题，隔离级别就是为了解决这些问题的。隔离的越严实，效率就越低，并发越低，安全性越高。
脏读：一个事务处理过程里读取了另一个未提交的事务中的数据
可重复读：一个事务在它运行期间，两次查找相同的表，出现了不同的数据
幻读：在一个事务中读取到了别的事务插入的数据，导致前后不一致
A事务读取了B事务已经提交的新增数据。和不可重复读的区别，这里是新增，不可重复读是更改（或删除）。 这两种情况对策是不一样的，对于不可重复读，只需要采取行级锁防止该记录数据被更改或删除，然而对于幻读必 须加表级锁，防止在这个表中新增一条数据。
读未提交：（read uncommitted）,可能发生脏读、不可重复读和幻读
读提交：（read committed）可能发生不可重复读和幻读
可重复读（repeatable read）可能发生幻读
串行化（serializable ）都不可能发生
-
读未提交（read uncommitted，RU） 一个事务还未提交时，它做的变更就可以被别的事务看到。
-
读提交（read committed，RC） 事务提交以后，它做的变更才能被其它事务看到。但是在这个事务未提交之前，数据库中发生的变更，这个事务也能看见。
-
可重复度（repeatable read，RR） 事务总是只能看见在启动的那个时刻，数据库的状态。事务未提交之前做的变更，其它事务看不见。事务执行期间，数据库中已经发生的变更，这个事务也看不见。只能看见事务刚启动时刻，数据库的状态。
-
串行化（serializable） 事务对某一行的操作会加锁，“写”会加“写锁”，“读”会加“读锁”，在锁释放掉之前，其它的事务都无法都这一行的记录进行操作。必须等之前的事务执行完毕，释放锁。后面的事务又会重新加锁。
- 显式启动： 或 。配套的提交语句是 ，回滚语句是 。
- 也可以使用 ，此时不需要显式启动，比如执行了一个语句就直接启动了事务，但是需要再执行一条来提交；
因此建议设置，此时需要用来开启事务，如果觉得多了一次交互，比较麻烦的话，也可以使用，表示提交当前事务，并且再启动一个新的事务，这样就只有一次了。
事务之间的隔离，是通过锁机制实现的。当一个事务需要对数据库中的某行数据进行修改时，需要先给数据加锁；加了锁的数据，其它事务是不运行操作的，只能等待当前事务提交或回滚将锁释放。
锁机制并不是一个陌生的概念，在许多场景中都会利用到不同实现的锁对数据进行保护和同步。而在MySQL中，根据不同的划分标准，还可将锁分为不同的种类。
按照粒度划分：行锁、表锁、页锁
按照使用方式划分：共享锁、排它锁
按照思想划分：悲观锁、乐观锁
锁机制的知识点很多，由于篇幅不好全部展开讲。这里对按照粒度划分的锁进行简单介绍。
粒度：指数据仓库的数据单位中保存数据的细化或综合程度的级别。细化程度越高，粒度级就越小；相反，细化程度越低，粒度级就越大。
MySQL按照锁的粒度划分可以分为行锁、表锁和页锁。
行锁：粒度最小的锁，表示只针对当前操作的行进行加锁；
表锁：粒度最大的锁，表示当前的操作对整张表加锁；
页锁：粒度介于行级锁和表级锁中间的一种锁，表示对页进行加锁。
这三种锁是在不同层次上对数据进行锁定，由于粒度的不同，其带来的好处和劣势也不一而同。
表锁在操作数据时会锁定整张表，因而并发性能较差；
行锁则只锁定需要操作的数据，并发性能好。但是由于加锁本身需要消耗资源(获得锁、检查锁、释放锁等都需要消耗资源)，因此在锁定数据较多情况下使用表锁可以节省大量资源。
MySQL中不同的存储引擎能够支持的锁也是不一样的。MyIsam只支持表锁，而InnoDB同时支持表锁和行锁，且出于性能考虑，绝大多数情况下使用的都是行锁。
了解完MySQL的基本架构，大体上能够对MySQL的执行流程有了比较清晰的认知。接下来我将在讲述MySQL事务之前，先为大家介绍以下日志系统，以方便之后更好的理解事务的特性和实现。
MySQL日志系统是数据库的重要组件，用于记录数据库的更新和修改。若数据库发生故障，可通过不同日志记录恢复数据库的原来数据。因此实际上日志系统直接决定着MySQL运行的鲁棒性和稳健性。
MySQL的日志有很多种，如二进制日志（binlog）、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种日志：redo log（重做日志）和undo log（回滚日志）。这里将重点针对InnoDB引擎，对重做日志、回滚日志和二进制日志这三种进行分析。
重做日志（redo log） 重做日志（redo log）是InnoDB引擎层的日志，用来记录事务操作引起数据的变化，记录的是数据页的物理修改。
重做日记的作用其实很好理解，我打个比方。数据库中数据的修改就好比你写的论文，万一哪天论文丢了怎么呢？以防这种不幸的发生，我们可以在写论文的时候，每一次修改都拿个小本本记录一下，记录什么时间对某一页进行了怎么样的修改。这就是重做日志。
InnoDB引擎对数据的更新，是先将更新记录写入redo log日志，然后会在系统空闲的时候或者是按照设定的更新策略再将日志中的内容更新到磁盘之中。这就是所谓的预写式技术（Write Ahead logging）。这种技术可以大大减少IO操作的频率，提升数据刷新的效率。
脏数据刷盘
值得注意的是，redo log日志的大小是固定的，为了能够持续不断的对更新记录进行写入，在redo log日志中设置了两个标志位置，checkpoint和write_pos，分别表示记录擦除的位置和记录写入的位置。redo log日志的数据写入示意图可见下图。
主从复制的概念很简单，就是从原来的数据库复制一个完全一样的数据库，原来的数据库称作主数据库，复制的数据库称为从数据库。从数据库会与主数据库进行数据同步，保持二者的数据一致性。
主从复制的原理实际上就是通过bin log日志实现的。bin log日志中保存了数据库中所有SQL语句，通过对bin log日志中SQL的复制，然后再进行语句的执行即可实现从数据库与主数据库的同步。
主从复制的过程可见下图。主从复制的过程主要是靠三个线程进行的，一个运行在主服务器中的发送线程，用于发送binlog日志到从服务器。两外两个运行在从服务器上的I/O线程和SQL线程。I/O线程用于读取主服务器发送过来的binlog日志内容，并拷贝到本地的中继日志中。SQL线程用于读取中继日志中关于数据更新的SQL语句并执行，从而实现主从库的数据一致。
之所以需要实现主从复制，实际上是由实际应用场景所决定的。主从复制能够带来的好处有：
1 通过复制实现数据的异地备份，当主数据库故障时，可切换从数据库，避免数据丢失。
2 可实现架构的扩展，当业务量越来越大，I/O访问频率过高时，采用多库的存储，可以降低磁盘I/O访问的频率，提高单个机器的I/O性能。
3 可实现读写分离，使数据库能支持更大的并发。
4 实现服务器的负载均衡，通过在主服务器和从服务器之间切分处理客户查询的负荷。
视图是从一个或多个表中查询出来的数据保存为视图，是一种虚拟存在的表。视图和表很相似，它也包含行和列，所以可以直接对它进行查询操作。
视图是一张虚表，所谓虚表，就是说这张表里面没有数据，他的数据是从别的基础表中获取到的。视图在被定义时，就确定了和基础表的联系，比如视图的某个字段是来自基础表A，另外的几个字段是来自基础表B；所以说视图是基于基础表的。
视图也是一张表，对于基础表的所有基础操作（增删改查），视图也适用。当然，当你操作视图的时候，数据库通过中间过程的处理，实际上是操作的基础表。
对于查询操作，视图一般都没问题；不过删除、新增和修改操作，一般会有限制，你得仔细考虑代码的正确性，举个例子，比如视图中的字段来自两个基础表，但是并不包含两个基础表的所有非空字段，当你执行插入操作时，肯定是会报错的。
注意:
数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中。
使用视图查询数据时，数据库系统会从原来的表中取出对应的数据。
视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变。
重复利用SQL语句
简化SQL查询，快速取数据
只用知道表的部分结构
保护数据，根据特定授权(分配用户一定权限,安全性)
更改数据格式，视图可返回与底层表的格式不同的数据表,展现出来。
视图有以下好处：
●访问数据变得简单
●可被用来对不同用户显示不同的表的内容
用来协助适配表的结构以适应前端现有的应用程序。
视图作用：
●提高检索效率
●隐藏表的实现细节【面向视图检索】
优点:
视图可以让用户或者程序开发人员只看到他们所需要的数据，而不需要把表中的所有信息与字段暴露出来，这样增强了数据的安全性。
视图的作用就是缓存数据，可以这么理解，我把一条sql的查询结果装在了一个视图里，我下次再查的时候，就不用走sql了，直接取这个视图里就可以了，神奇的是我可以修改这个视图里的数据，就等于修改了原表数据。
它的优点有很多，什么简单、安全、简化操作、数据独立。。。。
- 表要占用磁盘空间，视图不需要
- 视图不能添加索引
- 使用视图可以简化查询
- 视图可以提高安全性
定义的语法结构 完整版：CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION]
语法解读：
1）OR REPLACE：表示替换已有视图，如果该视图不存在，则CREATE OR REPLACE VIEW与CREATE VIEW相同。
2）ALGORITHM：表示视图选择算法，默认算法是UNDEFINED(未定义的)：MySQL自动选择要使用的算法 ；merge合并；temptable临时表，一般该参数不显式指定。
3）DEFINER：指出谁是视图的创建者或定义者，如果不指定该选项，则创建视图的用户就是定义者。
4）SQL SECURITY：SQL安全性，默认为DEFINER。
5）select_statement：表示select语句，可以从基表或其他视图中进行选择。
6）WITH CHECK OPTION：表示视图在更新时保证约束，默认是CASCADED。
其实我们日常创建视图时，无需指定每个参数，一般情况下，建议这样创建视图：
精简版：CREATE VIEW 视图名 AS 查询语句;
create view 视图名称 as select * from 表明 where 条件;
如：create view v_t1 as select * from t1 where id>4 and id<11;
查看命令 SELECt * FROM 视图名;
1. 简介
1）索引就好比一本书的目录，它能让你更快的找到自己想要的内容。2）让获取的数据更有目的性，从而提高数据库检索数据的性能。
索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
例如：有一张person表，其中有2W条记录，记录着2W个人的信息。有一个Phone的字段记录每个人的电话号码，现在想要查询出电话号码为xxxx的人的信息。
如果没有索引，那么将从表中第一条记录一条条往下遍历，直到找到该条信息为止。
如果有了索引，那么会将该Phone字段，通过一定的方法进行存储，好让查询该字段上的信息时，能够快速找到对应的数据，而不必在遍历2W条数据了。
举例说明索引：如果把数据库中的某一张看成一本书，那么索引就像是书的目录，可以通过目录快速查找书中指定内容的位置，对于数据库表来说，可以通过索引快速查找表中的数据。
除此之外，索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的。
2. 索引的原理
索引的目的在于提高查询效率，与我们查阅图书所用的目录是一个道理：先定位到章，然后定位到该章下的一个小节，然后找到页数。相似的例子还有：查字典，查火车车次，飞机航班等
本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂的多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段......这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的。而数据库实现比较复杂，一方面数据是保存在磁盘上的，另外一方面为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
3. 索引的优点
建立索引的目的是加快对表中记录的查找或排序！
① 建立索引的列可以保证行的唯一性，生成唯一的rowId
② 建立索引可以有效缩短数据的检索时间
③ 建立索引可以加快表与表之间的连接
④ 为用来排序或者是分组的字段添加索引可以加快分组和排序顺序
4. 索引的缺点
① 创建索引和维护索引需要时间成本，这个成本随着数据量的增加而加大
② 创建索引和维护索引需要空间成本，每一条索引都要占据数据库的物理存储空间，数据量越大，占用空间也越大（数据表占据的是数据库的数据空间）
③ 会降低表的增删改的效率，因为每次增删改索引需要进行动态维护，导致时间变长
数据库中表的数据量较大的情况下，对于查询响应时间不能满足业务需求，可以合理的使用索引提升查询效率。
1)BTREE:B+树索引 ( Btree B+tree B*tree) 2)HASH：HASH索引 (hash key) 3)FULLTEXT：全文索引 4)RTREE：R树索引
Mysql索引主要有两种结构：B+Tree索引和Hash索引，也就是用树或者Hash值来存储该字段。我们平常所说的索引，如果没有特别指明，一般都是指B树结构组织的索引(B+Tree索引)。
#1. 索引的功能就是加速查找 #2. mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能
索引分类
1.普通索引index :加速查找
2.唯一索引
主键索引：primary key ：加速查找+约束（不为空且唯一）
唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
-primary key(id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name):联合普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用
注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
MyISAM和InnoDB存储引擎：只支持BTREE索引， 也就是说默认使用BTREE，不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引
MySQL 提供了三种创建索引的方法：
1) 使用 CREATE INDEX 语句
可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。 语法格式：CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
语法说明如下：
- ：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。
- ：指定要创建索引的表名。
- ：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERe 子句里经常出现的列作为索引列。
- ：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。
- ：可选项。指定索引按照升序来排列，指定索引按照降序来排列，默认为。
CREATE INDEX indexName ON table_name (column_name)
2) 使用 CREATE TABLE 语句
create table t_dept(
no int not null primary key,
name varchar(20) null,
sex varchar(2) null,
info varchar(20) null,
unique index index_no(no)
）
3) 使用 ALTER TABLE 语句
语法如下：
以上就是本篇文章【mysql（二）】的全部内容了，欢迎阅览 ！ 文章地址：http://sicmodule.glev.cn/news/9720.html 资讯 企业新闻 行情 企业黄页 同类资讯 首页 网站地图 返回首页 歌乐夫资讯移动站 http://sicmodule.glev.cn/mobile/ , 查看更多