mysql 基础（学习mysql必看）

Mysql基础

Mysql是一种cs架构的系统。

其中s是：存放mysql数据库的机器，上面运行着mysqld服务，用来监听客户端的访问，并把结果返回给客户端

c是：客户端机器，用来访问mysql服务程序，提交自己的查询，并获取结果

 

Mysql的命令行工具不仅提供了mysql这个客户端用来和服务程序交互查询，还有mysqldump客户端用来把结果导出到文件里；还有mysqladmin用来检查数据库的工作状态，执行数据库管理的相关操作；mysql还提供了一个客户端开发库用来自定义编程解决业务问题，这个开发库可以使用c直接操作，也可以使用别的语言的编程接口。

 

Mysql的GUI工具有mysql workbench等

 

Mysql有很多妙用，比如，使用group by + with rollup 来对groupby后的结果汇总得出有价值的信息；也有一些坑，比如，语句中调用函数，不能在函数名和括号中间加入空格；使用group by指定某个列（这个列是由汇总函数计算出的）排序时，要给这个列起一个别名等。

下面就讲讲一些mysql的基础使用方式和理论。

 

不同客户端拥有不同的sql模式，这一模式可以使用sql语句改变。不同的sql模式会让服务器用不同的规则去处理客户端的sql。

 

Mysql的大小写问题在于所使用的内容和MySQL服务器所安装的宿主机的操作系统，因为mysql的表和视图都是基于操作系统文件系统的文件来表示，所以如果文件系统不分大小写则mysql也同样不分大小写，window和mac不分，而unix区分大小写。另外别名默认是区分大小写的。

 

对于字符集和排序规则的设置分为好多个层次，可以在服务器、数据库、表、列、字符串常量这些不同层面上设置不同的字符集和排序规则，但是不能在一列的不同行设置不同的字符集或是一个常量的不同字符。服务器的字符集和排序规则是在编译时构建好了的，可以修改系统变量character-set-server和collation-server来指定。在创建数据库和表的时候可以使用character set XXX和collate XXX来指定字符集和排序规则，当同时制定的时候需要保证两者兼容，兼容的意思是排序规则的首个字符集名为制定的字符集。确定当前可用的字符集和排序规则分别使用show character set （like）和show collation （like），他们支持模糊查询，确定当前使用的字符集和排序规则可以使用show variables （like）。

 

数据库的操作有些需要注意的地方，比如在创建数据库的时候mysql服务器在数据目录下创建一个同数据库名的目录，用来存放这个数据库的内容，并且在该目录下创建一个db.opt文件来存储这个数据库的默认定义，比如这个数据库的字符集、排序规则等。有时候drop数据库删除执行失败，是因为drop只能删除表、存储过程等数据库内容，不能删除其它内容，因此残留文件会导致drop失败，这时候需要去服务器中手动删除。

 

 

使用show engines可以查看mysql可用的存储引擎，不同的存储引擎有不同的管理表的方式，比如inndb会把所有表的定义文件放到一个共享的表空间。

 

一般创建表的时候如果指定的engine用不了，则会默认使用默认引擎，也就是innodb，如果要达到使用不了就报错而不使用默认引擎的效果，则要用no_engine_substitution 的sql模式。 另外可以使用show create table （name）来查看表定义。表的创建最好加上if not exists，如果使用这个语句mysql不会比较两个表的结构，只会比较表明，如果结构不同则仍会执行。

 

Mysql的分区表是把表内容分散到不同物理存储位置上，从而提高效率，因为这样可以通过io并行缩短访问时间，减少单个表的数据量。分区可以依据范围、列、主键散列值分。在建表的时候使用partition关键字。

 

对于enum和set以外的字符串类型字段，可以创建前缀索引，就是依据这个字段前几个字符或者几个字节创建索引，前缀索引可能会降低性能也可能提升性能。前缀索引的创建语法是用字段名加（）里的数字表示前几个字符或前几个字节建立索引，对于字符型（如char）是字符，对于字节类型（如binary）为字节。有时候前缀索引是必要的，例如字段为text或是blob类型，它们数据过长。索引的限制有明确规定，这一点不同存储引擎不一样。一个表用了什么索引可以通过show index查看。可以对char、varchar、binary、varbinary、text、blob类型创建前缀索引。

 

获取mysql元数据的三种方式分别是，在mysql客户端中使用show命令；使用select查询information_schema表；在命令行使用mysqlshow命令工具。

 

Mysql的子查询如果返回了一行，则可以使用元组（字段一，字段二..）的形式相等，子查询往往要改写成连接，常用join和left join。

 

事务在连接意外断掉时自动回滚，如果又突然重新连接，那么将设置事务为自动提交状态。如果一个事务很长常用savepoint命令来记录一些断点以便回滚。事务隔离等级可以通过set transaction设置，super权限可以修改global的隔离等级使所有客户端等级改变，客户端可以使用set session transaction和set transaction分别设置自己会话的事务隔离属性和下一个事务的隔离等级。

 

FULLTEXT搜索涉及的列只能是char、varchar或者text，指定的搜索字符串的配置可以更改，例如最小长度，fulltext索引也是忽略了一些系统词和常见词，常见是指一半的列都包括的。使用fulltext索引必须要与列对应，比如要搜索a1、a2、a1和a2，那么就要创建a1、a2的索引以及a1和a2的复合索引。Fulltext的默认搜索模式是自然语言模式，也就是查出包含搜索字符的行，所查出的行是依照相关性升序，只要在against后面使用as relevance就可以显示相关度了。Fulltext还有两种模式是布尔模式和查询扩展模式，它们规则都彼此不同，由于不是默认模式，所以需要显示定义。

 

innodb中的序列从一开始编号，每张表只有唯一的序列列，而且这个列是主键，序列的值总是为最新生成的序列号值，当一个表中的一行被删掉或是插入该序列所在的事务回滚，那么这个序列号在当前表中不可重用。序列号的最大值总是有限制的，这取决于序列列的类型，如果是tinyint则序列号最大值是127，unsigned tinyint 为255,如果超出最大值则报错键重复。当表被使用delete from table；和truncate table；清空后，该表序列号是会被重置的，如果想要保留这个表的序列号在清空表后不被重置，那么就要用delete from table where true；这个命令，也就是说如果delete后面where条件为true的话，当前被删除的行的序列号信息会被保留。Last_insert_id()这个函数是客户端独有的，他永远获取当前客户端插入的最新序列值，不会被其它客户端影响。

 

列的数据类型的选取需要搞清楚提供数据的人的想法，再来确定选取的类型，mysql中数字的处理效率普遍高于字符串，所以尽量使用数字的类型，数字是否有小数、是否允许误差、是否允许为负、取值范围等等都要搞清楚，针对不同的情况来选择，日期也同样是否要精确到时分秒等等，往往选取符合要求的类型的最省范围类型，而且如果可以的话尽量用enum和set替代字符串类型，因为他们在mysql中被用作数字型处理，如果表有变动则使用alter重构。

 

Innodb使用的是聚簇索引，主键值和数据行放在了一起。其它索引都有二级索引，也就是主键值和二级索引放在一起，二级索引再去指向数据行。如果已经添加了一个复合索引，（a，b，c）那么就没必要在添加一个a的索引，但是有一种索引列外，就是FULLTEXT索引。Mysql的查询优化程序对于查询会率先测试列，尽可能地排除不需要检索的行，被测试的列是带索引的。我们可以使用一些操作来帮助优化程序更好地使用索引，比如分析表是生成键值分布情况的统计数据，可以帮助优化程序评估索引效率，就像分析表后再使用explain会获得更加精准的row的分析；尽可能比较相同类型的列，如int和bigint以及char（10）和char（12）都是不相同的类型；当索引列参与函数或表达式计算的时候则不走索引，所以要把索引列单独放在表达式的一边；尽量不要在like的第一个字符使用通配符，而且正则表达式匹配也不会被查询优化；索引列不要使用隐式转换，否则不走索引。

 

Mysql有些数据类型会产生很大的表碎片，像char、text、blob等，虽然这些类型可以用来存储一个定制的结构数据但是会增加从磁盘读取的开销以及空间的浪费，解决表碎片的方式可以是使用mysqldump转储表，再利用转储文件重建它。

 

数据的加载也有一些方式优化，最基本的是与磁盘连接的次数越少越好，所以大数据量加载时尽量使用批量操作。Load data比insert的效率要高一些，而且load data不使用local时效率更高，因为local需要客户端把资源文件通过网络传给服务器再执行加载，而不使用local是服务器加载本地资源，其次写入数据和查询数据在innodb中是排斥的，为了减少这种冲突，可以考虑使用辅助表，辅助表用来执行数据的加载，而辅助表里的数据定期插入到主表中，这样写入和读取操作就会分成两个表，大大减少了冲突。但是这种情况只适用于不太着急使用辅助表里面的数据的情况，如果要第一时间使用刚刚写入的数据那么此方法则不适用。

 

Innodb引擎使用了行级的锁，而其他引擎使用了表级的锁，所以innodb对并发的支持更好，但是行级的锁有时候会发生死锁情况，对此mysql只能中止其中一个事务来解决。

 

 

 

 

 

 

使用php的pdo来编写mysql程序：

Pdo是php连接数据库的一种方式，使用new pdo()来获取数据库的连接，如果失败则抛出异常。Pdo操作中如果有异常抛出则是默认结束脚本，因此需要显示指定异常的处理。通过pdo对象的setAttribute（pdo的错误模型，pdo异常）来指定当pdo操作失败时要抛出异常，抛出的异常使用try catch捕获处理。对于一个页面的附加功能可以使用空catch来忽略它抛出的异常，也就是说只处理主功能的异常。e的getcode和getmessage方法是异常对象信息，而dbh的errorcode和errorinfo是数据库连接句柄的信息。不过句柄的信息只能在仅仅使用唯一一个连接的前提下使用。

使用包含文件有许多的好处，比如防止敏感信息明文泄露或者模板式编程。

使用isnull、isset和===都可以检测null值。

Pdo的sql操作分成预处理prepare和执行execute两个方法。很多时候需要使用prepare来初始化带有占位符的语句，因为有时候sql中的一些值会带有特殊符号，而占位符可以解决这些特殊字符；此外prepare也可以优化一些操作，比如批量插入语句预先prepare一次sql语句，然后执行多次execute，就不用每次插入都prepare了。

 

Mysql组件中服务器端的mysqld_safe用于启动和监控服务器，服务意外中止时会重启。当在一台服务器上启动多个服务器实例时，可以使用mysqld_multi管理。客户端有mysql，用来和服务器交互sql；mysqladmin可以用来关闭服务器、检查服务器配置并且监控；mysqldump用来备份数据库或者把数据库拷贝到另一个服务器上；mysqlcheck和myisamchk用来分析、优化、修复表。

 

Mysql的数据目录：

不同的数据库系统会有不同管理数据库的方式，mysqld所管理的数据库组的所有信息都存在于数据目录中。数据目录的位置可以在mysql的配置文件里查找，为datadir变量的值，而且也可以使用mysql的sql查找，使用show variables liike ‘datadir’。数据目录在mysqld启动时可以通过--datadir指定。数据目录里存有进程文件、数据库信息、日志状态文件和安全文件等。

Mysql服务器启动之后会打开请求它维护的日志，然后监听各种类型的网络连接，并对外提供一个通向数据目录的网络接口，每一个客户端需要和mysql服务器建立连接，传送sql。

对于innodb的表，mysql会在他们创建的时候都会生成一个frm文件用来表示表结构，而表内容默认是存入系统表空间里；而还有一种表示方式是使用ibd文件来表示表的内容，不过就算使用ibd这种方式表示内容，系统表空间中的数据字典也是会存储表的内容的；此外myisam引擎的表有三个文件，而memory引擎的表只有一个frm文件。视图都会有一个frm文件。一个表关联多个触发器，那么这些触发器将会集中存入以这个表命名的trg文件中，一个触发器也会关联多个表，这样会为每一个表会生成一个以该表命名的trn文件。

因为对于innodb表，被创建的时候innodb引擎会在系统表空间里维护一个数据字典条目，顺便将该表内容存储在哪里，所以如果删除包含innodb的数据库或是单独删除innodb表，必须使用drop database或者drop table，这样才能更新数据字典里的内容，否则信息删除不彻底而占用空间。

 

Mysql服务器级别有一些状态和日志文件。Mysql服务器启动的时候会把进程id写入hostname.pid文件中，因此可以通过检测这个文件来判断mysql服务器是否在运行，以及他的进程id，mysql服务关闭的时候也是通过检测该文件来对响应进程id发送终止命令；错误日志包含了服务器出现问题的具体原因；常规查询日志包含与服务器的常规操作信息；二进制日志还包含了除了select之外的语句信息，所以二进制文件也可用来恢复数据库到发生崩溃前的那一刻的状态；二进制日志索引文件会列出服务器当前维护的二进制日志文件。

 

Mysql在安装过程中会创建mysql和test两个数据库，mysql存放用户客户端对服务器访问的授权表，test用于测试。Mysql安装后会默认提供root和匿名账户，通常需要使用set password和 update来修改user中的密码，来使得账户安全，但是set password修改之后服务器会自动重新读取权限表所以不需使用flush privileges来刷新权限通知服务器。删除权限使用drop user ‘name’@’localhost’，也不用手动刷新权限。当在一个计算机上安装第二个mysql服务，那么可能会使用mysql -u root --skip-password来显式指定无密码登陆新服务，解决已经安装的mysql服务的选项文件里的默认root密码的影响。

 

Window下运行mysql服务器，有两种方式，一个是命令行使用mysqld启动，用mysqldadmin -p -u root shutdown停止。另一个是用windows服务的方式运行，可以 把mysqld安装成一个window服务，当window启动时也启动mysqld。Mysql服务器有多个程序，每个程序使用的选项组不同，选项组都存在mysql的配置文件中，比如mysqld使用了[mysqld] [server]两个选项组，mysqld_safe 使用了[mysqld][server][mysqld_safe]，mysql.server使用了[mysqld][server][mysql_server][mysql.server]。如果window下设置了开机自动启动mysqld，那么关机时也不需要手动关闭mysqld，window服务管理器会自动提示mysqld停止，而手动启动mysqld则需要手动停止mysqld。

 

当无法连接myhsql服务时还要对其控制一般有两种情况，一个是忘了密码，需要重新设定密码，这种情况要先正常关闭或者强行关闭mysqld，然后使用--skip-grant-tables选项重启，获得root连接然后马上flush privilege，这是因为使用上述选项获取连接适用于所有用户，导致了数据库及其不安全，所以使用flush privilege再切换到输入密码访问，然后重新修改密码，最后重启mysqld就可以；还有一种情况就是mysql.sock文件无意间被删除，则不能使用socket连接，这样就要使用--protocol=tcp来显示指定使用tcp连接，或者使用ip地址连接而不用域名连接（ip地址指定了使用tcp连接），或者可以重新指定一个sock文件。

 

Show variables和show status分别可以查看mysql的系统变量和状态变量，系统变量有会话值和全局值，但是有些系统变量不都是同时拥有这两个值，或许有一个，对于两个值都有的变量，系统会用全局值来初始化会话值，客户端可以改变自己的会话值满足一些需求而super权限的客户端可以改变全局值，一般修改完系统变量后可以查看系统的状态来确定修改是否生效。Show varaibles显示的是会话层变量，加上global则可以查看全局层变量。状态值只有服务器端可以修改，对于客户端为只读，同样也有全局值和会话值。

 

Show engines 可以查看当前可用的存储引擎，可以在选项文件里使用default_storage_engine变量指定默认存储引擎，也可以使用set在运行时改。上面提到的innodb每个表对应两个文件的配置可以使用innodb_file_per_table变量。Innodb系统表空间可以由常规文件构成也可以是设备文件。Innodb_buffer_pool_size变量控制缓冲池大小，如果有多余内存可以将其分配大一点，以减少对磁盘上文件的访问。Innodb_log_buffer_size，innodb引擎正在事务没有完成以前，事务的操作日志会记录在log缓存中，而不会写入磁盘，配置该变量可以支持更大的事务操作日志的缓存，最大可为8mb。Innodb_log_group_home_dir为日志的存储目录，所有的日志文件会存储在这个目录下，默认为数据目录。Innodb_log_file_size和innodb_log_files_in_group分别是日志文件大小和日志文件数量的限制，日志文件的总大小为两者乘积，最大不超过4gb。关于已经被创建的日志，还要改变大小，就要确保所有事务结束后，重启服务器来创建新文件。

 

服务器的调整有许多系统变量，back_log是在处理当前连接时，排队等待连接的最大请求数，在客户端连接很慢时，增大该变量。Max_connections是服务器最大客户端并发连接数，在适当时机调大，在状态变量中体现为max_used_connections。Table_open_cache是已经打开的表的信息的缓存，服务器在打开一个表的时候尽量要使得这个表保持被打开的状态，因为如果再有命令访问到这个表就要减少频繁的打开文件的操作，所以表信息的缓存依赖这个变量的大小，默认大小是400，可能过小，适当调大，它的状态变量体现在opened_tables，如果这个值趋于稳定或者增加缓慢则表示cache值正好，如果显著增加则需要调大cache。如果调大table_open_cache也要同时调大table_definition_cache。由于操作系统对单个进程的文件操作符数量有限制，而当过多地打开表文件时，可能会突破这个限制，所以需要调大open_files_limit来增大文件操作符限制，如果还是达不到需求，那么就要配置操作系统，或者开启多个mysql服务来处理。当客户端向服务器发送的数据包过大（比如sql信息过大），可能会导致cs间通讯有问题，这样要调大max_allowed_packet来增加数据包缓存得大小，默认为1mb，最大是1gb。还有read_buffer_size、sort_buffer_size、join_buffer_size等，这些缓存并不是一开始就分配好的，而是当某个客户端执行了对应的读取、排序、连接的sql操作时才分配，而对他们的调整要逐步调整，不要一下子调的过大，并且调整后要使用对应的操作来评估。

 

其实与mysql性能联系最紧密的莫过于存储引擎的缓存了，说得更细一点就是缓冲表信息的缓存，这个东西在不同存储引擎中是不同的，在innodb中叫做缓冲池。缓冲池一开始是空的，当有sql查询表信息时，先从缓存读取，如果缺失，则是从磁盘中读取，再把当前访问的信息放入缓存中以便下次读取。如果缓存已满，则默认使用lru（最近最少使用）算法来确定要丢弃的缓存块，如果一个缓存块中的信息被修改过，也就是经过了写入数据的操作，那么在它被丢弃前会批量写入磁盘。也就是说，缓冲池不仅仅是提高了读取信息的性能，也可以提高修改信息的性能。Innodb把它的缓冲池分成新子列表和旧子列表，分别表示频繁操作的表和不频繁操作的表，其实缓冲池的列表就是一块内存，新旧的界限只不过是通过指针来控制，默认新子列表占总列表大小的3/8，刚被加入到缓存里的信息的默认策略是插入到新子列表和旧子列表的中间。有两个变量innodb_buffer_pool_size和innodb_buffer_pool_instances，是表示缓冲池的总大小和实例个数，当总大小大于1gb，而实例设置成多于一个的时候，会实例多个小的缓冲快，相当于分布式的缓冲块，这样不仅提高了缓存的性能，也方便了对缓存的锁机制，因为每一个小的缓存块可以独立处理信息，其次就是innodb_old_blocks_pct设置旧子列表占总大小的的百分比，还有innodb_old_blocks_time设置当旧列表中一个缓存块信息被访问后多少毫秒才能移动到新子列表中，有时候防止一次性访问表操作而丢弃了所有的新子列表缓存块的情况，可以设置这个值大于零，或者有时候为了把一些访问频率高的表尽量放到新子列表，可以先设置成0，然后访问这些高频表，再设置成大于零的值。

 

查询缓存是否被支持可以查看have_query_cache变量，对于支持查询缓存的服务器，有三个值作为query_cache_type的值，其中值表示不缓存查询结果。Query_cache_size和query_cache_limit分别表示查询缓存的大小和被缓存结果集的最大值。查询缓存使用时需要注意不能使用过大的cache size因为会在比对缓存时耽误大量时间，而且查询缓存是不支持多核操作的，当服务器是多核系统时，两个处理器会竞争一个查询缓存的操作处理，检查这个问题需要使用show processlist命令，如果发现state有多个waiting for query cache lock 则表示要考虑禁用查询缓存了，另外在一个写数据频繁的服务中也往往禁用查询缓存，因为缓存的表信息被修改时，其所有缓存则作废。

 

对于硬件的优化尽量使用内存，磁盘可考虑固态硬盘，使用多处理器，最后就是把服务的不同部分分散到不同设备上存储，因为这样可以提高并行的性能，但是在同一个设备的不同分区则是不算的，因为对于设备的资源竞争根本是对设备的物理资源（磁头）的竞争。

 

服务器的日志非常重要，可以用来诊断服务，提高性能，复制和恢复数据库。服务器启动的时候依据选项文件的配置来判断是否启用各种日志，一般日志有出错日志，包括了服务器启动和关闭以及服务器出错、异常；普通查询日志统计收到的sql语句，连接方、连接地用于诊断调试；满日志查询统计查询时间长于某个配置值的sql；二进制和二进制索引日志用来记录所有的修改sql语句，他不是以文本形式记录日志而是二进制格式的修改事件，用于数据库恢复（恢复过程一般是先备份数据库结构，然后使用mysqlbinlog将二进制日志转为文本形式，然后输入到备份后的数据库中，使其恢复到崩溃前那一刻的数据库状态）；中继日志和中继索引，它的格式同二进制日志一样，适用于从复制服务上接受主服务器需要做出修改的修改事件。除了这些日志还有引擎单独创建的日志，比如innodb创建的日志用于恢复崩溃，这个日志无法控制其被创建，但是可以通过innodb_log_group_home_dir来指定其位置。

 

Window下的出错日志会默认写到数据目录的HOSTNAME.err中，如果要用另外的名字或者位置要在[mysqld]组配置。普通查询日志中的sql顺序是按照mysqld收到的sql顺序写入的，但是不一定是执行完的顺序，使用普通查询日志可以考虑把它写到文件或数据日志表或者同时都写入，写入的目标由log_output系统变量指定，如果是写入日志表，则是写到mysql数据库的general_log和slow_log，日志表为可读，但是可以使用truncate table来清空日志表。慢日志的写入目标与普通查询日志一样。慢日志的超时值为系统变量long_query_time，单位是秒，小数单位是微秒，min_examined_row_limit变量表示至少这个查询语句要查多少行数才会被记录，默认是0。如果服务器启动时带有--log-slow-admin-statements则它会把执行慢的管理语句记录下来。Log_queries_not_using_indexes可以记录没用索引的查询语句，如果有大量语句被记录可以在5.6.5版本后使用log_throttle_queries_not_using_indexes决定每分钟记录的不带索引的语句上限数。二进制日志不会记录select语句，也不会记录实际没改变表数据的修改语句，不同于普通查询日志的写入顺序，因为二进制日志要用于数据恢复所以里面的修改事件顺序是按照执行完毕顺序来写入的。Max_binlog_size控制二进制文件的大小，超过此值则会重新创建第二个日志文件以此类推。二进制日志有三种记录格式选择，基于行、语句、和混合，可以使用binlog_format控制，默认是基于语句。

 

如何窃取mysql服务器上的数据呢？不是所有客户端都能访问服务器的，因为服务器有对数据访问权限的控制，只有权限表里的用户可以访问。因此想要窃取信息，只能绕过权限认证这一过程。首先在被窃取的mysql服务器上安装另一个mysql服务器，使用不同的端口号、套接字文件和数据目录；其次运行mysql_install_db，初始化数据目录，这样可以让你以root身份进入自己的服务器，然后创建一个数据库准备放入窃取的表，将所要窃取的表复制放入这个新建的数据库，然后启动自己的服务器，便可以在新数据库里访问表信息了，最后在自己的服务器上设置用户权限，把用户暴漏给别人，这样别人就可以通过你的服务器间接访问目标数据。如何保护mysql安装、套接字文件、选项文件等值得一看。

mysql语句的执行顺序：

From+ 后续操作+ select+distinct+ order by

 

 

 

mysql实战

1. limit和offset的区别

Mysql是从0开始的序列，但是实际结果集的开始位置是1，也就是说limit当后接一个参数时，表示从0开始，取条数据，这个从0开始注意是不包括0.

limit 2，2表示从第三条记录开始取两条数据，而limit 2 offset 2表示从第二条记录开始取第二条记录。

 

2、 sql中内部sql可以使用外部的sql变量，这一点至关重要，使用的同时需要理清思路。

 

3、外层查询的字段在子查询中一定要查询到，也就是说select后的属性确保存在才能在外层查询中查询而不报出编译错误。

 

4、关于groupby错误兼容问题

显然groupby的sql是先确定了groupby后的字段，再从中确定所查字段，因此groupby后的字段必须包括所有所查字段，否则查询的时候会有错误，这个错误被mysql兼容而不会报出，这样一来多余的行则会默认强行选择每一组的第一行的字段值。

 

1. union、union all的语法问题

并操作中如果有一个子句使用了小括号，则其它必须都是用小括号；子句中不能还有order by ，如果含有，则必须使用小括号把子句括起来；如果并操作不使用括号，那么最后一个子句后面的操作都是语法上默认作用于整个结果集的；并操作中就算子句套上了小括号，也不会执行order by，只有子句带有order by和limit时才会执行排序操作

 

1. 有时候需要取逻辑的反向操作

是否存在问题可以转成个数统计，好处是不需要逻辑处理，只需比较数量；而且当分组不方便时，可以使用两次查询同一张表实现分组效果

 

d.*,e1.*

from employee e1 join department d on e1.DepartmentId=d.Id

where 3>(

select count(distinct e2.Salary)

from employee e2

where e1.Salary<e2.Salary

and e1.DepartmentId = e2.DepartmentId

)

 

1. 同表查询的使用巧妙

比如：

等。

1. 关于mysql中相近函数的选择问题

对于一个功能的实现，有时候Mysql中会提供多种函数来帮助功能的实现，比如：

分别使用了data_sub和 datediff，由于date_sub返回string类型，与RecordDate（Date）做计算难免要类型转换，所以性能就不如下面的datediff=1，因为datediff返回是int类型不需类型转换则可跟1计算，上下两句性能大概差400ms。

 

1. 关于count(表达式)问题

Count括号中有表达式时，如果该行满足情况且不为空则统计行数，因此如果列中无该值则要设置成null。

 

1. enum问题

select * from Test where t=4 

如果上述语句要查枚举字段值为4的行，t字段是enum，那么结果则不正确，因为所查则是enum索引为4的行，enum索引从0开始，正确写法应该是：

select * from Test where t=’4’

 

1. 当有多个字段同时需要相同not in操作时，可以考虑换成exist，因为效率比较是in、exists、not exists都远高于not in。

 

一点关于sql的查询思想的问题

以下的括号一和括号二的语句都可以实现，将一个表中相邻两行的记录交换的功能，如果最后一行是一个奇数行那么最后一行就不交换。

 

（1）SELECT

    s1.id, COALESCE(s2.student, s1.student) AS student

FROM

    seat s1

        LEFT JOIN

    seat s2 ON ((s1.id + 1) ^ 1) - 1 = s2.id

ORDER BY s1.id;

 

（2）

select CASE

WHEN id%2=1 and id=(select max(s.id) from seat s) THEN id

WHEN id%2=1 THEN id+1

ELSE id-1

    END as id, student

from seat

order by id

 

然后先说一下括号二的语句，这里只说一点就是这个语句很好地表现了，sql的select 查询是以一行记录为基础的，也就是说从seat表中先查出一行记录，比如 id=1 student=tom，然后开始依据你的sql开始逐个获取字段，比如这时id=1，符合第二个when，那么这个id的字段最后的值便是id+1也就是2，最后该字段起个别名为id就像语句中写的那样，然后开始获取student值，注意虽然刚才查询的id最终为2，但是并不是说这里的student我就要获取id为2哪一行的记录了，这也就是我为什么分析括号二这条sql的目的，也很好地理解了select的查询是以行为基础的，当前行的记录仍然是id=1 student=tom ，只不过我们自己修改了这个id的实际值，所以最后查询出来的，这一行的记录应该是 id=2，student=tom。所以我们对sql的操作，只不过是自己修改一行的实际值罢了。

 

再来说一下括号一的语句，其中Coalesce()函数十分有用，用来返回传入参数中第一个不为null的值，可以传入多个参数。那么我分析括号一的sql的原因不是它拥有更好的性能，而是这个sql非常有创意，它不同于常规思路，就是括号二，这个功能正常来说大部分人都会使用case when，但是这条sql使用了left join 和一个经典的异或操作来取代了case when，他的思路是反正是要考虑最后一条语句是否为奇数，那么索性就不考虑了，直接把两个同表做连接，但是这个连接的条件非常关键，为了能使相邻的两行记录互为一行，这个连接条件便是取了id（这是肯定的），而且通过（（id+1）^1）-1的方式连接，具体分析这一步，首先这个人知道一个规律，就是偶数与1异或计算，则相当于加一，奇数与1异或计算，则相当于减一，那么这个规律恰恰就是类似这个功能的思想，好比相邻两行记录，第一行和第二行，我所要的是第一行换成第二行，第二行换成第一行，由此看来这个目标与异或的操作很相似。所以这里必然有一个异或操作，而且操作数是1；再说为什么在异或操作之前有一个加一呢？是因为如果不加一，第一行是奇数便会减一而导致行号为0；然后最后再把多加的这个1减掉，便有了这个连接的条件。其次这条语句也很好地使用了left join的特点，防止了原来的表因为最后一项是奇数项而没有自己的偶数项而被舍去的情况，所以结果则是最后一个奇数项的连接的部分是null，最后一步则是使用上述的coalesce函数来取交换后的结果集，这个函数的参数注意是要先取连接的表，而非驱动表，这一点处理的也特别到位。