MySQL 数据库锁定机制
MySQL 数据库锁定机制简介
●行级锁定（row-level）
行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。
●表级锁定（table-level）
和行级锁定相反，表级别的锁定是MySQL 各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现
锁定资源争用的概率也会最高，致使并大度大打折扣。
●页级锁定（page-level）
页级锁定是MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。
在数据库实现资源锁定的过程中，随着锁定资源颗粒度的减小，锁定相同数据量的数据所需要消耗的内存数量是越来越多的，实现算法也会越来越复杂。不过，随着锁定资源颗粒度的减小，应用程序的访问请求遇到锁等待的可能性也会随之降低，系统整体并发度也随之提升。
在MySQL 数据库中，使用表级锁定的主要是MyISAM，Memory，CSV 等一些非事务性存储引擎，而使用行级锁定的主要是Innodb 存储引擎和NDB Cluster 存储引擎，页级锁定主要是BerkeleyDB 存储引擎的锁定方式。MySQL 的如此的锁定机制主要是由于其最初的历史所决定的。在最初，MySQL 希望设计一种完全独立于各种存储引擎的锁定机制，而且在早期的MySQL 数
据库中，MySQL 的存储引擎（MyISAM 和Momery）的设计是建立在“任何表在同一时刻都只允许单个线程对其访问（包括读）”这样的假设之上。但是，随着MySQL 的不断完善，系统的不断改进，在MySQL3.23 版本开发的时候，MySQL 开发人员不得不修正之前
的假设。因为他们发现一个线程正在读某个表的时候，另一个线程是可以对该表进行insert 操作的，只不过只能INSERT 到数据文件的最尾部。这也就是从MySQL 从3.23 版本开始提供的我们所说的Concurrent Insert。
当出现Concurrent Insert 之后，MySQL 的开发人员不得不修改之前系统中的锁定实现功能，但是仅仅只是增加了对Concurrent Insert 的支持，并没有改动整体架构。可是在不久之后，随着BerkeleyDB存储引擎的引入，之前的锁定机制遇到了更大的挑战。因为BerkeleyDB存储引擎并没有MyISAM 和Memory存储引
擎同一时刻只允许单一线程访问某一个表的限制，而是将这个单线程访问限制的颗粒度缩小到了单个page，这又一次迫使MySQL 开发人员不得不再一次修改锁定机制的实现。
由于新的存储引擎的引入，导致锁定机制不能满足要求，让MySQL 的人意识到已经不可能实现一种完全独立的满足各种存储引擎要求的锁定实现机制。如果因为锁定机制的拙劣实现而导致存储引擎的整体性能的下降，肯定会严重打击存储引擎提供者的积极性，这是MySQL 公司非常不愿意看到的，因为这完全不符合MySQL 的战略发展思路。所以工程师们不得不放弃了最初的设计初衷，在锁定实现机制中作出修改，允许存储引擎自己改变MySQL 通过接口传入的锁定类型而自行决定该怎样锁定数据。
各种锁机制分析
在整体了解了MySQL 锁定机制之后，这一节我们将详细分析MySQL 自身提供的表锁定机制和其他储引擎实自身实现的行锁定机制，并通过MyISAM存储引擎和Innodb存储引擎实例演示。
表级锁定
MySQL 的表级锁定主要分为两种类型，一种是读锁定，
另一种是写锁定。在MySQL 中，主要通过四个队列来维护这两种锁定：两个存放当前正在锁定中的读和写锁定信息，另外两个存放等待中的读写锁定信息，如下：
?Current read-lock queue (lock->read)
?Pending read-lock queue (lock->read_wait) ?Current write-lock queue (lock->write)
?Pending write-lock queue (lock->write_wait)
当前持有读锁的所有线程的相关信息都能够在Current read-lock queue 中找到，队列中的信息按照获取到锁的时间依序存放。而正在等待锁定资源的信息则存放在Pending read-lock queue 里面，另外两个存放写锁信息的队列也按照上面相同规则来存放信息。虽然对于我们这些使用者来说MySQL 展现出来的锁定（表锁定）只有读锁定和写锁定这两种类型，但是在MySQL 内部实现中却有多达11 种锁定类型，由系统中一个枚举量（thr_lock_type）定义
读锁定
一个新的客户端请求在申请获取读锁定资源的时候，需要满足两个条件：
1、请求锁定的资源当前没有被写锁定；
2、写锁定等待队列（Pending write-lock queue）中没有更高优先级的写锁定等待；
如果满足了上面两个条件之后，该请求会被立即通过，并将相关的信息存入Current read-lock
queue 中，而如果上面两个条件中任何一个没有满足，都会被迫进入等待队列Pending read-lock queue中等
待资源的释放。
写锁定
当客户端请求写锁定的时候，MySQL 首先检查在Current write-lock queue 是否已经有锁定相同资源的信息存在。如果Current write-lock queue 没有，则再检查Pending write-lock queue，如果在Pending write-lock queue 中找到了，自己也需要进入等待队列并暂停自身线程等待锁定资源。反之，如果Pending write-lock queue 为空，则再检测Current read-lock queue，如果有锁定存在，则同样需要进入Pending write-lock queue 等待。当然，也可能遇到以下这两种特殊情况：
1. 请求锁定的类型为WRITE_DELAYED;
2. 请求锁定的类型为WRITE_CONCURRENT_INSERT 或者是TL_WRITE_ALLOW_WRITE，同时
Current read lock 是READ_NO_INSERT 的锁定类型。当遇到这两种特殊情况的时候，写锁定会立即获得而进入Current write-lock queue 中
如果刚开始第一次检测就Current write-lock queue 中已经存在了锁定相同资源的写锁定存在，那么就只能
进入等待队列等待相应资源锁定的释放了。
读请求和写等待队列中的写锁请求的优先级规则主要为以下规则决定：
1. 除了READ_HIGH_PRIORITY 的读锁定之外，Pending write-lock queue 中的WRITE 写锁定能够阻塞所有其他的读锁定；
2. READ_HIGH_PRIORITY 读锁定的请求能够阻塞所有Pending write-lock queue 中的写锁定；
3. 除了WRITE 写锁定之外，Pending write-lock queue 中的其他任何写锁定都比读锁定的优先级低。写锁定出现在Current write-lock queue 之后，会阻塞除了以下情况下的所有其他锁定的请求：
1. 在某些存储引擎的允许下，可以允许一个WRITE_CONCURRENT_INSERT 写锁定请求
2. 写锁定为WRITE_ALLOW_WRITE 的时候，允许除了WRITE_ONLY 之外的所有读和写锁定请求
3. 写锁定为WRITE_ALLOW_READ 的时候，允许除了READ_NO_INSERT 之外的所有读锁定请求
4. 写锁定为WRITE_DELAYED 的时候，允许除了READ_NO_INSERT 之外的所有读锁定请求
5. 写锁定为WRITE_CONCURRENT_INSERT 的时候，允许除了READ_NO_INSERT 之外的所有读锁定请求
随着MySQL 存储引擎的不断发展，目前MySQL 自身提供的锁定机制已经没有办法满足需求了，很多存储引擎都在MySQL 所提供的锁定机制之上做了存储引擎自己的扩展和改造。
MyISAM存储引擎基本上可以说是对MySQL 所提供的锁定机制所实现的表级锁定依赖最大的一种存储引擎了，虽然MyISAM存储引擎自己并没有在自身增加其他的锁定机制，但是为了更好的支持相关特性，MySQL 在原有锁定机制的基础上为了支持其Concurrent Insert 的特性而进行了相应的实现改造。
而其他几种支持事务的存储存储引擎，如Innodb，NDB Cluster 以及Berkeley DB 存储引擎则是让MySQL 将锁定的处理直接交给存储引擎自己来处理，在MySQL
中仅持有WRITE_ALLOW_WRITE 类型的锁定。
由于MyISAM存储引擎使用的锁定机制完全是由MySQL 提供的表级锁定实现，所以下面我们将以MyISAM存储引擎作为示例存储引擎，来实例演示表级锁定的一些基本特性。由于，为了让示例更加直观，我将使用显示给表加锁来演示：
行级锁定
行级锁定不是MySQL 自己实现的锁定方式，而是由其他存储引擎自己所实现的，如广为大家所知的Innodb存储引擎，以及MySQL 的分布式存储引擎NDB Cluster 等都是实现了行级锁定。
Innodb锁定模式及实现机制
考虑到行级锁定君由各个存储引擎自行实现，而且具
体实现也各有差别，而Innodb是目前事务型存储引擎中使用最为广泛的存储引擎，所以这里我们就主要分析一下Innodb的锁定特性。
总的来说，Innodb的锁定机制和Oracle 数据库有不少相似之处。Innodb的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，Innodb也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种
当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添
加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以，可以说Innodb的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和
意向排他锁（IX），我们可以通过以下表格来总结上面这四种所的共存逻辑关系：
虽然Innodb的锁定机制和Oracle 有不少相近的地方，但是两者的实现确是截然不同的。总的来说就是Oracle 锁定数据是通过需要锁定的某行记录所在的物理block 上的事务槽上表级锁定信息，而Innodb的锁定则是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。Innodb的这种锁定实现方式被称为“NEXT-KEY locking”（间隙锁），因为Query 执行过程中通过过范围查找的华，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。而Innodb给出的解释是为了组织幻读的出现，所以他们选择的间隙锁来实现锁定。
除了间隙锁给Innodb带来性能的负面影响之外，通过索引实现锁定的方式还存在其他几个较大的性能隐患●当Query 无法利用索引的时候，Innodb会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
●当Quuery使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该Query 的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
●当Query 在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定
Innodb各事务隔离级别下锁定及死锁
Innodb实现的在ISO／ANSI SQL92 规范中所定义的Read UnCommited，Read Commited，Repeatable Read 和Serializable这四种事务隔离级别。同时，为了保证数据在事务中的一致性，实现了多版本数据访问。
行级锁定肯定会带来死锁问题，Innodb也不可能例外。至于死锁的产生过程我们就不在这里详细描述了，在后面的锁定示例中会通过一个实际的例子为大家爱展示死锁的产生过程。这里我们主要介绍一下，在Innodb 中当系检测到死锁产生之后是如何来处理的。
在Innodb的事务管理和锁定机制中，有专门检测死锁的机制，会在系统中产生死锁之后的很短时间内就检测到该死锁的存在。当Innodb检测到系统中产生了死锁之后，Innodb会通过相应的判断来选这产生死锁的
两个事务中较小的事务来回滚，而让另外一个较大的事务成功完成。那Innodb是以什么来为标准判定事务的大小的呢？MySQL 官方手册中也提到了这个问题，实际上在Innodb发现死锁之后，会计算出两个事务各自插入、更新或者删除的数据量来判定两个事务的大小。也就是说哪个事务所改变的记录条数越多，在死锁中就越不会被回滚掉。但是有一点需要注意的就是，当产生死锁的场景中涉及到不止Innodb存储引擎的时候，Innodb是没办法检测到该死锁的，这时候就只能通过锁定超时限制来解决该死
锁了。另外，死锁的产生过程的示例将在本节最后的Innodb锁定示例中演示。
Innodb锁定机制示例
Order by 2013年4月19日星期五 09:39 校对规则，决定排序关系。 按照字段值进行排序 Order by 字段升序|降序(asc|desc) 默认升序，asc。 允许多字段排序， 指的是，先按照第一个字段排序，如果说不能区分，才使用第二个字段。以此类推。 注意，如果是分组，则应该使用对分组字段进行排序的groupby语法。 limit 2013年4月19日星期五 09:50 限制获得的记录数量： limit的语法： Limit offset，row_count Offset 偏移量,从0开始。可以省略，默认为0. Row_count总记录数，如果数量大于余下的记录数， 则获取所有余下的即可：
select * from teacher_class limit 3, 4; select * from teacher_class limit 5；select * from teacher_class limit 5, 100;
distinct 2013年4月19日星期五 10:17 去除重复记录： 重复的记录，指的是，字段值都相同的记录， select days from teacher_class ; select distinct days from teacher_class ; select days, begin_date from teacher_class ; select distinct days, begin_date from teacher_class ; 相对的是all，表示所有。默认就是all行为。
Union 2013年4月19日星期五 10:26 联合查询： 将多条select语句的结果，合并到一起。称之为联合操作。 获得2个班代课最多的老师： select t_name, days from teacher_class where c_name='php0115' order by days desc limit 1; select t_name, days from teacher_class where c_name='php0228' order by days desc limit 1;
Mysl面试大全 1.Mysql中有哪几种锁？ 1.表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 2.行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 3. 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。 2.Mysql中有哪些不同的表格？ 共有5种类型的表格： MyISAM Heap Merge INNODB ISAM 3.简述在MySQL数据库中MyISAM和InnoDB的区别 MyISAM： 不支持事务，但是每次查询都是原子的； 支持表级锁，即每次操作是对整个表加锁； 存储表的总行数； 一个MYISAM表有三个文件：索引文件、表结构文件、数据文件； 采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。 InnoDb： 支持ACID的事务，支持事务的四种隔离级别； 支持行级锁及外键约束：因此可以支持写并发； 不存储总行数； 一个InnoDb引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为2G），受操作系统文件大小的限制； 主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持B+树结构，文件的大调整。 4.Mysql中InnoDB支持的四种事务隔离级别名称，以及逐级之间的区别？ SQL标准定义的四个隔离级别为： read uncommited ：读到未提交数据 read committed：脏读，不可重复读 repeatable read：可重读 serializable ：串行事物
ACCESS数据库锁定问题 问题1 单位网站突然有的时候不能打开网页。重启电脑后问题解决。说是CONN.ASP第6行错误。同时生成一个.LDB文件。在网上查了下说是数据库没有关闭或锁定了。请问如何关闭啊，下边是CONN.ASP代码： <% starttime=timer() StrSQL="DBQ="+server.mappath("admin/data/news30000.mdb")+";DRIVER={Microsoft Access Driver (*.mdb)};" 'connstr="driver={SQL Server};server=(local);database=master;uid=sa;pwd=;" set conn=server.createobject("ADODB.CONNECTION") conn.open StrSQL（第6行） %> 答： 如果是ACCESS数据库，应该是并发访问造成的问题。因为ACCESS没有行锁。所以你有个一个用户在网上改一条数据，另外一个用户再上来访问相同一条数据时就给锁了。重启动后所有的连接全断开了，所以就没问题了。如果有可能最好不用ACCESS做后台数据库。 ---------------------------------- 问题2 我的数据库老是被锁住，网页打不开，请高手帮忙！！我的conn.asp是：<% scadb=mydata&"datahotel/#@@##feel.mdb" 'mydata 为各文件中设置的路径，请不要改动 connstr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(""&scadb&"") On Error Resume Next Set conn = Server.CreateObject("ADODB.Connection") conn.open connstr If Err Then err.Clear Set Conn = Nothing Response.Write "
一.数值类型 Mysql支持所有标准SQL中的数值类型，其中包括严格数据类型 (INTEGER,SMALLINT,DECIMAL,NUMBERIC)，以及近似数值数据类型(FLOAT,REAL,DOUBLE PRESISION),并在此基础上进行扩展。 扩展后增加了TINYINT,MEDIUMINT,BIGINT这3种长度不同的整形，并增加了BIT类型，用来存放位数据。 整数类型字节范围（有符号）范围（无符 号）用途 TINYINT 1字节(-128，127) (0， 255) 小整数值 SMALLINT 2字节 (-32 768，32 767) (0，65 535) 大整数值 MEDIUMINT 3字节(-8 388 608，8 388 607) (0，16 777 215) 大整数值 INT或INTEGER 4字节 (-2 147 483 648，2 147 483 647) (0，4 294 967 295) 大整数值 BIGINT 8字节 (-9 233 372 036 854 775 808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615) 极大整数值 FLOAT 4字节 (-3.402 823 466 E+38，1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38) 0，(1.175 494 351 E-38，3.402 823 466 E+38) 单精度浮点数值 DOUBLE 8字节 (1.797 693 134 862 315 7 E+308，2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308) 双精度浮点数值DECIMAL 对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2 依赖于M和D的值依赖于M和D的值小数值 INT 类型: 在 MySQL 中支持的 5 个主要整数类型是 TINYINT，SMALLINT，MEDIUMINT，INT 和BIGINT。这些类型在很大程度上是相同的，只有它们存储的值的大小是不相同的。MySQL 以一个可选的显示宽度指示器的形式对 SQL 标准进行扩展，这样当从数据库检索一个值时，可以把这个值加长到指定的长度。例如，指定一个字段的类型为 INT(6)，
前言 作为一名编程人员，对MySQL一定不会陌生，尤其是互联网行业，对MySQL的使用是比较多的。对于求职者来说，MySQL又是面试中一定会问到的重点，很多人拥有大厂梦，却因为MySQL败下阵来。实际上，MySQL并不难，今天这份最全的MySQL总结，助你向大厂“开炮”，面试不再被MySQL难倒。 注意：关于MySQL的内容整理，包括了面试题、学习笔记、使用文档以及Xmind思维图几个部分，需要高清完整版的请转发+关注，然后私信回复“666”获得免费领取方式 01、MySQL 面试题集合总结 1.1 MySQL 面试题（基础部分）： ?drop、truncate、delete区别 ?数据库三范式是什么? ?union和union all有什么不同? ?char、varchar2、varchar有什么区别？ ?合并查询有哪些？ ?SQL语句执行顺序 ?null的含义 ?MySQL、SqlServer、oracle写出字符存储、字符串转时间 ?update语句可以修改结果集中的数据吗？ ?B树和B+树的区别 ?你建过索引吗? 建索引的原则 ?索引的类型, 如主键索引 ?查看SQL执行计划
?有十万条数据, 写SQL语句查询其中某字段较大值的几条数据 ?子查询与关联查询的区别 ?MySQL InnoDB、Mysaim的特点？ ?乐观锁和悲观锁的区别？？ ?行锁和表锁的区别？ ?数据库隔离级别是什么？有什么作用？ ?MySQL主备同步的基本原理。 ?如何优化数据库性能（索引、分库分表、批量操作、分页算法、升级硬盘SSD、业务优化、主从部署） ?SQL什么情况下不会使用索引（不包含，不等于，函数） ?一般在什么字段上建索引（过滤数据最多的字段） ?MySQL，B+索引实现，行锁实现，SQL优化 ?如何解决高并发减库存问题 ?数据库事务的几种粒度 1.2 MySQL 面试题（实战部分）： ?数据库三范式，根据秒杀场景设计数据表 ?数据库的主从复制 ?死锁怎么解决 ?mysql并发情况下怎么解决（通过事务、隔离级别、锁） ?触发器的作用？ ?什么是存储过程？用什么来调用？ ?存储过程的优缺点？
数据库锁表与解锁 一、mysql 锁定表:LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…] 解锁表:UNLOCK TABLES 例子: LOCK TABLES table1 WRITE ,table2 READ 、、、更多表枷锁; 说明:1、READ 锁代表其她用户只能读不能其她操作 2、WRITE锁代表:其她用户不能任何操作(包括读) 查瞧那些表被锁:show OPEN TABLES where In_use > 0; 全局加锁:FLUSH TABLES WITH READ LOCK(这个命令就是全局读锁定,执行了命令之后所有库所有表都被锁定只读。解锁也就是:UNLOCK TABLES ) 二、oracle --行级锁定(同样对 mysql起作用) 通过 :select * from tableName t for update 或 select * from tableName t where id =1 for update 前者锁定整个表,后者多顶 id=1的一行数据(有主键,并且指定主键=值的只 锁定指定行) 说明:通过 select 、、、 for update 后其她用户只能读不能其她操作,锁定者通过 commit或 rollback命令自动解锁,或使用本文的解锁方式
(will)！ --表级锁定 lock table
昨天作业 2013年4月20日星期六 09:56 class as h on m.host_id =h.id left join
select 2013年4月20日星期六10:06
生成的文件格式： 默认的，采用行来区分记录，而采用制表符，来 区分字段。 为了满足某种特别的需求，会采用不同的分割方式。 支持，在导出数据时，设置记录，与字段的分割符。 通过如下的选项： fields：设置字段选项 Lines: 设置行选项（记录选项） 先看默认值： 字段：fields terminated by '\t' enclosed by '' escaped by '\\‘ 记录：lines terminated by '\n' starting by '' 可以自己设定： select * into outfile 'e:/amp/three' fields terminated by ',' lines terminated by '\n' starting by 'start:' from teacher_class where t_name = '韩信'; 字段包裹 select * into outfile 'e:/amp/four' fields terminated by '\t' enclosed by 'x' lines terminated by '\n' starting by 'start:' from teacher_class where t_name = '韩信'; 注意： 常规的，所有的记录，应该通过行来显示例外是保存二进制数据：
1、MySQL 中有哪几种锁？ 1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。 2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。 2、MySQL 中有哪些不同的表格？ 共有 5 种类型的表格： 1、MyISAM2、Heap 3、Merge 4、INNODB 5、MISAM 3、简述在MySQL 数据库中 MyISAM 和InnoDB 的区别 MyISAM： 不支持事务，但是每次查询都是原子的；支持表级锁，即每次操作是对整个表加锁；存储表的总行数； 一个 MYISAM 表有三个文件：索引文件、表结构文件、数据文件； 采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致，但是辅索引不用保证唯一性。 InnoDb： 支持 ACID 的事务，支持事务的四种隔离级别；支持行级锁及外键约束：因此可以支持写并发；不存储总行数： 一个 InnoDb 引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制， 一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为 2G），受操作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持 B+树结构，文件的大调整。 4、MySQL 中InnoDB 支持的四种事务隔离级别名称，以及逐 级之间的区别？SQL 标准定义的四个隔离级别为： 1、read uncommited ：读到未提交数据 2、read committed：脏读，不可重复读 3、repeatable read：可重读 4、serializable ：串行事物 5、CHAR 和VARCHAR 的区别？ 1、CHAR 和 VARCHAR 类型在存储和检索方面有所不同 2、CHAR 列长度固定为创建表时声明的长度，长度值范围是 1 到 255 当 CHAR 值被存储时，它们被用空格填充到特定长度，检索 CHAR 值时需删除尾随空格。 6、主键和候选键有什么区别？ 表格的每一行都由主键唯一标识,一个表只有一个主键。 主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键引用。 7、myisamchk 是用来做什么的？ 它用来压缩 MyISAM 表，这减少了磁盘或内存使用。 MyISAM Static 和 MyISAM Dynamic 有什么区别？ 在 MyISAM Static 上的所有字段有固定宽度。动态 MyISAM 表将具有像 TEXT， BLOB 等字段，以适应不同长度的数据类型。 MyISAM Static 在受损情况下更容易恢复。 8、如果一个表有一列定义为TIMESTAMP，将发生什么？ 每当行被更改时，时间戳字段将获取当前时间戳。列设置为 AUTO INCREMENT 时，如果在表中达到最大值，会发生什么情况？它会停止递增，任何进一步的插入都将产生错误，因为密钥已被使用。怎样才能找出最后一次插入时分配了哪个自动增量？LAST_INSERT_ID 将返回由 Auto_increment 分配的最后一个值，并且不需要指定表名称。 9、你怎么看到为表格定义的所有索引？ 索引是通过以下方式为表格定义的： SHOW INDEX FROM ; 10、LIKE 声明中的％和是什么意思？％对应于 0 个或更多字符，只是 LIKE 语句中的一个字符。 如何在 Unix 和 MySQL 时间戳之间进行转换？UNIX_TIMESTAMP 是从 MySQL 时间戳转换为 Unix 时间戳的命令FROM_UNIXTIME 是从 Unix 时间戳转换为 MySQL 时间戳的命令 11、列对比运算符是什么？
我们在操作数据库的时候，有时候会由于操作不当引起数据库表被锁定，这么我们经常不知所措，不知怎么给这些表解锁，在pl/sql Developer工具的的菜单“tools”里面的“sessions”可以查询现在存在的会话，但是我们很难找到那个会话被锁定了，想找到所以被锁的会话就 更难了，下面这叫查询语句可以查询出所以被锁的会话。如下： SELECT https://www.doczj.com/doc/5c462056.html,ername, m.SID,sn.SERIAL#, m.TYPE, DECODE (m.lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive', lmode, LTRIM (TO_CHAR (lmode, '990')) ) lmode, DECODE (m.request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row Excl.', 4, 'Share', 5, 'S/Row Excl.',
6, 'Exclusive', request, LTRIM (TO_CHAR (m.request, '990')) ) request, m.id1, m.id2 FROM v$session sn, v$lock m WHERE (sn.SID = m.SID AND m.request != 0) OR ( sn.SID = m.SID AND m.request = 0 AND lmode != 4 AND (id1, id2) IN ( SELECT s.id1, s.id2 FROM v$lock s WHERE request != 0 AND s.id1 = m.id1 AND s.id2 = m.id2) )ORDER BY id1, id2, m.request; 通过以上查询知道了sid和SERIAL#就可以开杀了 alter system kill session 'sid,SERIAL#';
MySQL数据类型和常用字段属性总结●日期和时间数据类型 ●数值数据类型 整型
上面定义的都是有符号的，当然了，也可以加上unsigned关键字，定义成无符号的类型，那么对应的取值范围就要翻翻了，比如： tinyint unsigned的取值范围为0~255。 ●浮点型 我在MySQL中建立了一个表，有一列为float(5, 3)；做了以下试验： 1.插入123.45678，最后查询得到的结果为99.999； 2.插入12 3.456，最后查询结果为99.999； 3.插入12.34567，最后查询结果为12.346； 所以，在使用浮点型的时候，还是要注意陷阱的，要以插入数据库中的实际结果为准。 ●字符串数据类型
1.char（n）和varchar（n）中括号中n代表字符的个数，并不代表字节个数，所以当使用了中文的时候(UTF8)意味着可以插入m个中文，但是实际会占用m*3个字节。 2.同时char和varchar最大的区别就在于char不管实际value都会占用n个字符的空间，而varchar只会占用实际字符应该占用的空间+1，并且实际空间+1<=n。 3.超过char和varchar的n设置后，字符串会被截断。 4.char的上限为255字节，varchar的上限65535字节，text的上限为65535。 5.char在存储的时候会截断尾部的空格，varchar和text不会。 6.varchar会使用1-3个字节来存储长度，text不会。 1.enum(“member1″, “member2″, … “member65535″) enum数据类型就是定义了一种枚举，最多包含65535个不同的成员。当定义了一个enum的列时，该列的值限制为列定义中声明的值。如果列声明包含NULL属性，则NULL将被认为是一个有效值，并且是默认值。如果声明了NOT NULL，则列表的第一个成员是默认值。
题目一 问题描述： 为管理岗位业务培训信息，建立3个表: S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄C (C#,CN ) C#,CN 分别代表课程编号、课程名称 SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名 --实现代码： SELECT SN,S# FROM S WHERE [S#] IN( SELECT [S#] FROM C,SC WHERE C.[C#]=SC.[C#] AND CN=N\'税收基础\') 2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位 --实现代码： SELECT S.SN,S.SD FROM S,SC WHERE S.[S#]=SC.[S#] AND SC.[C#]=\'C2\' 3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位 --实现代码： SELECT SN,SD FROM S WHERE [S#] NOT IN(
SELECT [S#] FROM SC WHERE [C#]=\'C5\') 4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位--实现代码： SELECT SN,SD FROM S WHERE [S#] IN( SELECT [S#] FROM SC RIGHT JOIN C ON SC.[C#]=C.[C#] GROUP BY [S#] HAVING COUNT(*)=COUNT([S#])) 5. 查询选修了课程的学员人数 --实现代码： SELECT 学员人数=COUNT(DISTINCT [S#]) FROM SC 6. 查询选修课程超过5门的学员学号和所属单位 --实现代码： SELECT SN,SD FROM S WHERE [S#] IN( SELECT [S#] FROM SC GROUP BY [S#] HAVING COUNT(DISTINCT [C#])>5) 题目二 问题描述：
如何对行表数据库加锁 1如何锁一个表的某一行 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT*FROM table ROWLOCK WHERE id =1 2锁定数据库的一个表 SELECT*FROM table WITH (HOLDLOCK) 加锁语句： sybase: update表set col1=col1 where1=0 ; MSSQL: select col1 from表(tablockx) where1=0 ; oracle: LOCK TABLE表IN EXCLUSIVE MODE ； 加锁后其它人不可操作，直到加锁用户解锁，用commit或rollback解锁 几个例子帮助大家加深印象 设table1(A,B,C) A B C a1 b1 c1 a2 b2 c2 a3 b3 c3 1）排它锁 新建两个连接 在第一个连接中执行以下语句 begin tran update table1 set A='aa' where B='b2' waitfor delay '00:00:30'--等待30秒 commit tran 在第二个连接中执行以下语句 begin tran select*from table1 where B='b2' commit tran
若同时执行上述两个语句，则select查询必须等待update执行完毕才能执行即要等待30秒 2）共享锁 在第一个连接中执行以下语句 begin tran select*from table1 holdlock-holdlock人为加锁 where B='b2' waitfor delay '00:00:30'--等待30秒 commit tran 在第二个连接中执行以下语句 begin tran select A,C from table1 where B='b2' update table1 set A='aa' where B='b2' commit tran 若同时执行上述两个语句，则第二个连接中的select查询可以执行 而update必须等待第一个事务释放共享锁转为排它锁后才能执行即要等待30秒 3）死锁 增设table2(D,E) D E d1 e1 d2 e2 在第一个连接中执行以下语句 begin tran update table1 set A='aa' where B='b2' waitfor delay '00:00:30' update table2 set D='d5' where E='e1' commit tran 在第二个连接中执行以下语句 begin tran update table2 set D='d5' where E='e1'
1. MYSQL 5数据类型,长度范围 1.1数值类型 MySQL支持所有标准SQL数值数据类型.这些类型包括严格数值数据类型(INTEGER,SMALLINT,DECIMAL和NUMERIC),以及近似数值数据类型 (FLOAT,REAL 和DOUBLE PRECISION).关键字INT是INTEGER的同义词,关键字DEC是DECIMAL 的同义词. BIT 数据类型保存位字段值,并且支持MyISAM,MEMORY,InnoDB和BDB表.作为SQL标准的扩展,MySQL也支持整数类型 TINYINT,MEDIUMINT和BIGINT.下面的表显示了需要的每个整数类型的存储和范围. MySQL还支持选择在该类型关键字后面的括号内指定整数值的显示宽度(例 如,INT(4)).该可选显示宽度规定用于显示宽度小于指定的列宽度的值时从左侧填满宽度. 显示宽度并不限制可以在列内保存的值的范围,也不限制超过列的指定宽度的值的显示. 当结合可选扩展属性ZEROFILL使用时, 默认补充的空格用零代替.例如,对于声明为INT(5) ZEROFILL的列,值4检索为00004.请注意如果在整数列保存超过显示宽度的一个值,当MySQL为复杂联接生成临时表时会遇到问题,因为在这些情况下MySQL相信数据适合原列宽度. 所有整数类型可以有一个可选(非标准)属性UNSIGNED.当你想要在列内只允许 非负数和该列需要较大的上限数值范围时可以使用无符号值. 浮点和定点类型也可以为UNSIGNED.同数类型,该属性防止负值保存到列中.然而,与整数类型不同的是,列值的上范围保持不变. 如果为一个数值列指定ZEROFILL,MySQL自动为该列添加UNSIGNED属性. 对于浮点列类型,在MySQL中单精度值使用4个字节,双精度值使用8个字节. FLOAT类型用于表示近似数值数据类型.SQL标准允许在关键字FLOAT后面的括号内选择用位指定精度(但不能为指数范围).MySQL还支持可选的只用于确定存储大小的精度规定.0到23的精度对应FLOAT 列的4字节单精度.24到53的精度对应DOUBLE列的8字节双精度. MySQL允许使用非标准语法:FLOAT(M,D)或 REAL(M,D)或DOUBLE PRECISION(M,D).这里,"(M,D)"表示该值一共显示M位整数,其中D位位于小数点后面.例如,定义为FLOAT(7,4)的一个列可以显示为-999.9999.MySQL保存值时进行四舍五入,因此如果在FLOAT(7,4)列内插入999.00009,近似结果是999.0001.
数据库常见的join方式有三种：inner join、left outter join、right outter join（还有一种full join，因不常用）。这三种连接方式都是将两个以上的表通过on条件语句拼成一个大表。以下是它们共同点： 1、关于左右表的概念。左表指的是在SQL语句中排在left join左边的表，右表 值得是排在left join右边的表。 2、在拼成的大表中，左表排在左边，右表排在右边。 3、On条件语句最好用=号对两表相应的主外键进行连接。当然，也可以用其他 操作符，如>,<来连接两个表的任一个字段，此时的关系将非常复杂，连接后的记录数也随之而变得不确定。如果在一些特殊的场合中需要用到这种方式，必须通过简单的实例加以确认，否则，连接结果很可能不是我们所想要的。 4、On条件语句不能省略。 5、可以连锁使用join，每次使用join都令另一表与当前的表或连接的结果相连 接。 Inner join 目的：将两表中符合on条件的所有记录都找出来。 规律： 1、拼出的大表记录不会增加。 2、如果左边与右边的关系是一对多的关系，在选出的任一记录中，假若右表有 多个记录与其对应，那么，连接后的左表，主键将不再唯一。 3、典型应用：将存在多关系的引用表放在左表，将存在一关系的被引用表放在 右表，通过=号将主外键进行连接，通过对右表设定过滤条件，选出相应的且主键唯一的左表记录。 Left outter join 目的：将左表的所有记录列出，右表中只要符合on条件的，与左表记录相拼合，不符合条件的，填以null值。 规律： 1.选出所有符合条件的左表，如果左表与右表的关系是一对一关系，则拼成的 大表记录不会改变。 2.如果左表与右表的关系是多对一的关系，则拼成的大表记录也不会改变。 3.如果左表与右表的关系是一对多的关系，则拼成的大表记录会增加。对于每 一具有一对多关系的左表记录，如果左表1：N与右表对应，那么会多出N-1条记录。例如，如果左表第一条记录1：3对应右表，多出2条记录。如果左表第二条记录1：2对应于右表，则再多出1条记录。这样，总共多出3条记录。其他类推。 4.如果左边与右表的关系是一对多的关系，在选出的任一记录中，假若右表有 多个记录与其对应，那么，连接后的左表，主键将不再唯一。 5.如果左表与右表的关系是一对多的关系，对于左表任一记录，如果右表没有 记录与其对应，则全部填以null值。 典型应用：将存在多关系的应用放在左表，将存在一关系的被引用表放在右表，通过对右表设定过滤条件，选出相应的且主键唯一的左表记录。 right outter join
1、MySQL的复制原理以及流程 基本原理流程，3个线程以及之间的关联； 1.主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中； 2.从：io线程——在使用startslave之后，负责从master上拉取binlog内容，放进自己的relaylog中； 3.从：sql执行线程——执行relaylog中的语句； 2、MySQL中myisam与innodb的区别，至少5点 (1)、问5点不同； 1>.InnoDB支持事物，而MyISAM不支持事物 2>.InnoDB支持行级锁，而MyISAM支持表级锁 3>.InnoDB支持MVCC,而MyISAM不支持 4>.InnoDB支持外键，而MyISAM不支持 5>.InnoDB不支持全文索引，而MyISAM支持。 (2)、innodb引擎的4大特性 插入缓冲（insertbuffer),二次写(doublewrite),自适应哈希索引(ahi),预读(readahead) (3)、2者selectcount(*)哪个更快，为什么 myisam更快，因为myisam内部维护了一个计数器，可以直接调取。 3、MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义 (1)、varchar与char的区别 char是一种固定长度的类型，varchar则是一种可变长度的类型
(2)、varchar(50)中50的涵义 最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，因为orderbycol采用fixed_length计算col长度(memory引擎也一样) (3)、int（20）中20的涵义 是指显示字符的长度 但要加参数的，最大为255，比如它是记录行数的id,插入10笔资料，它就显示00000000001~~~00000000010，当字符的位数超过11,它也只显示11位，如果你没有加那个让它未满11位就前面加0的参数，它不会在前面加0 20表示最大显示宽度为20，但仍占4字节存储，存储范围不变； (4)、mysql为什么这么设计 对大多数应用没有意义，只是规定一些工具用来显示字符的个数；int(1)和int(20)存储和计算均一样； 4、问了innodb的事务与日志的实现方式 (1)、有多少种日志； 错误日志：记录出错信息，也记录一些警告信息或者正确的信息。 查询日志：记录所有对数据库请求的信息，不论这些请求是否得到了正确的执行。慢查询日志：设置一个阈值，将运行时间超过该值的所有SQL语句都记录到慢查询的日志文件中。 二进制日志：记录对数据库执行更改的所有操作。 中继日志： 事务日志：
Mysql数据库学习总结 数据库的基本操作:创建删除查瞧 Create database school; 用于创建数据库,并且数据库的名字不可以更改 Show create database; show databases; 用来查瞧创建数据库的语句 Drop database; 用于删除数据库 表的基本操作: Create table; 用于创建表,table后面加表名称 Create table student{ Id int; Name varchar(10); Sex Boolean; } Show tables; 用于显示数据库中的所有表 Describe student; 这里显示了字段、数据类型、就是否为空、主外键、默认值与额外信息Show create table; 显示创建表时的详细信息 Drop table student; 删除表的操作 完整性约束
就是对字段进行限制,从而该字段达到我们期望的效果 设置表的主键:主键能够标识表中的每条信息的唯一性。(primary key) 创建主键的目的在于快速查找到表中的某一条信息 多字段主键:由多个属性组合而成 例如:primary key(id,course_id); 设置表的外键; 设置表的外键的作用在于建立与父表的联系 比如表A中的id就是外键,表B中的id就是主键 那么就可以称表B为父表,表A为子表 比如表B中id为123的学生删除后,表A中id为123的记录也随着消失这样做的目的在于保证表的完整性。 设置表的非空约束: 设置表中的字段不为空 设置表的唯一性约束 唯一性约束指表中该字段的值不能重复出现,也就就是给表中某个字段加上unique 设置表的属性值自动增加: auto_increment 主要用于为表中插入的新纪录自动生成唯一ID 一个表中只能由一个字段使用此约束,并且该字段必须为主键的一部分,约束的值ibixu 就是整型值。 设置表中属性的默认值 在表中插入一体哦新的记录时,如果没有为该字段赋值,那么数据库系统就会为该字段附上一条默认值。 修改表 修改表需要用到alter table 修改表名:
MySQL左连接、右连接和内连接详解 以MySql为例。在MySQL数据库中建立两张数据表，并分别插入一些数据。 示例脚本如下： 1.drop table table1; 2.CREATE TABLE `andrew`.`table1` 3.( 4.`name` VARCHAR(32) NOT NULL, 5.`city` VARCHAR(32) NOT NULL 6.) 7.ENGINE = MyISAM; 8.insert into TABLE1(name, city) values ('Person A', 'BJ'); 9.insert into TABLE1(name, city) values ('Person B', 'BJ'); 10.insert into TABLE1(name, city) values ('Person C', 'SH'); 11.insert into TABLE1(name, city) values ('Person D', 'SZ'); https://www.doczj.com/doc/5c462056.html,mit; 13.drop table table2; 14.CREATE TABLE `andrew`.`table2` 15.( 16.`name` VARCHAR(32) NOT NULL, 17.`city` VARCHAR(32) NOT NULL 18.) 19.ENGINE = MyISAM; 20.insert into TABLE2(name, city) values ('Person W', 'BJ'); 21.insert into TABLE2(name, city) values ('Person X', 'SH'); 22.insert into TABLE2(name, city) values ('Person Y', 'SH'); 23.insert into TABLE2(name, city) values ('Person Z', 'NJ'); https://www.doczj.com/doc/5c462056.html,mit; 1. MySQL外连接–左连接结果 table1居左，故谓之左连接。这种情况下，以table1为主，即table1中的所有记录均会被列出。有一下三种情况： a. 对于table1中的每一条记录对应的城市如果在table2中也恰好存在而且刚好只有一条，那么就会在 返回的结果中形成一条新的记录。如上面Person A和Person B对应的情况。
《数据库原理及应用》习题集参考答案 一、简答题 1、什么是数据库管理系统？ 一种负责数据库的建立、操作、管理和维护的软件系统。 2、数据库系统有哪几种模式？分别用来描述什么？ （1）外模式 是用户的数据视图，用来描述数据的局部逻辑结构，是模式的子集。（2）模式 是所有用户的公共数据视图，用来描述数据库中全体数据的全局逻辑结构和特征。 （3）内模式 又称存储模式，描述数据的物理结构及存储方式 3、什么是事务？事务有哪些特征？ 答：所谓事务是用户定义的一个数据库操作序列，这些操作要么全做要么全不做，是一个不可分割的工作单位。 事务的特征：原子性、一致性、隔离性、持续性。 4、POWER BUILDER中事务对象有何作用？ 答：PowerBuider的事务对象是应用程序与数据库之间进行通信的桥梁，在应用程序初启时，系统自动创一个为SQLCA(SQL Communication Area, SQL通讯区)的全局事务对象，该对象在应用程序的任何地方都可以访问 应用程序与数据库的所有通信都需要通过事务对象来完成，除了直接使用系统的缺省事务对SQLCA外，开发人员也可以创建自己的事务对象。 5、SQL SERVER中INSERTED表和DELETED表有何用？ 答：触发器中用到两种特殊的表：删除表和插入表触发器中使用名为“deleted"和“inserted"来参照这些表；删除表存储受DELTE和UPDATE语句影响的行的副本当执行DELETE或UPDATE语句时，行从触发器表中删除并传递到删除表中。删除表和触发器表通常没有共有的行。 插入表存储受INSERT和UPDA TE语句影响的行的副本当执行一NSERT 或UPDA T语句时，新行同时增加到插入表和触发器表中。插入表中的行是触发器表中新行的副本可使用删除表和插入表中的行来参照相关表中的行，或测试被删除或插入行中的值。