Mysql常见知识点

2.外键的使用需要满足下列的条件：（这里涉及到了InnoDB的概念）

     1. 两张表必须都是InnoDB表，并且它们没有临时表。

      注：InnoDB是数据库的引擎。MySQL常见引擎有两种：InnoDB和MyISAM，后者不支持外键。

      2. 建立外键关系的对应列必须具有相似的InnoDB内部数据类型。

      3. 建立外键关系的对应列必须建立了索引。

      4. 假如显式的给出了CONSTRAINT symbol，那symbol在数据库中必须是唯一的。假如没有显式的给出，InnoDB会自动的创建。

面试题：你的数据库用什么存储引擎？区别是？

      答案：常见的有MyISAM和InnoDB。

      MyISAM：不支持外键约束。不支持事务。对数据大批量导入时，它会边插入数据边建索引，所以为了提高执行效率，应该先禁用索引，在完全导入后再开启索引。

      InnoDB：支持外键约束，支持事务。对索引都是单独处理的，无需引用索引。

      CONSTRAINT symbol：可以给这个外键约束起一个名字，有了名字，以后找到它就很方便了。如果不加此参数的话，系统会自动分配一个名字。

      FOREIGN KEY：将从表中的字段1作为外键的字段。

      REFERENCES：映射到主表的字段2。

      ON DELETE后面的四个参数：代表的是当删除主表的记录时，所做的约定。

           RESTRICT(限制)：如果你想删除的那个主表，它的下面有对应从表的记录，此主表将无法删除。

           CASCADE（级联）：如果主表的记录删掉，则从表中相关联的记录都将被删掉。

           SET NULL：将外键设置为空。

           NO ACTION：什么都不做。

     注：一般是RESTRICT和CASCADE用的最多。

数据库索引的作用和优点缺点

 为什么要创建索引呢？这是因为，创建索引可以大大提高系统的性能。 

第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。 

第二，可以大大加快 数据的检索速度，这也是创建索引的最主要的原因。 

第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。 

第四，在使用分组和排序 子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。 

第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？这种想法固然有其合理性，然而也有其片面性。虽然,索引有许多优点,但是，为表中的每一个列都增加索引，是非常不明智的。这是因为，增加索引也有许多不利的一个方面。

第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。 

第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。 

第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。

（一）认识游标(cursor)

游标简单来说就是查询出来的数据索引，通过对游标的操作（第一个位置、最后一个位置、上一个位置、下一个位置）可以遍历出数据。

使用游标(cursor)的一个主要的原因就是把集合操作转换成单个记录处理方式。用SQL语言从数据库中检索数据后，结果放在内存的一块区域中，且结果往往是一个含有多个记录的集合。游标机制允许用户在SQL server内逐行地访问这些记录，按照用户自己的意愿来显示和处理这些记录。

在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联，因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

我们知道关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。由此可见，游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。 

在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECT INSERT 语句。但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？游标为我们提供了一种极为优秀的解决方案——那就是使用游标

就是一个可读的标识，用来标识数据取到什么地方了。

（二）游标特性

1,只读

2,不滚动

3,不敏感的

（三）使用游标

需要强调的是，游标必须在定义处理程序之前被定义，但变量必须在定义游标之前被定义，顺序就是变量定义-游标定义-处理程序。

1.定义游标

DECLARE cursor_name CURSOR FOR select_statement

这个语句声明一个游标。也可以在子程序中定义多个游标，一个块中的每一个游标必须命名唯一。声明游标后也是单条操作的。

 

2. 游标OPEN

OPEN cursor_name

这个语句打开先前声明的游标。

 

3. 游标FETCH

FETCH cursor_name INTO var_name [, var_name] ...

这个语句用指定的打开游标读取下一行（如果有下一行的话），并且前进游标指针至该行。

 

4. 游标CLOSE

CLOSE cursor_name

这个语句关闭先前打开的游标，注意，用完后必须关闭。

（四）示例

下面是一个存储过程，里面用到游标，逐条更新数据（批量更新数据）

 

BEGIN

 DECLARE  no_more_record INT DEFAULT 0;

 DECLARE  pID BIGINT(20);

 DECLARE  pValue DECIMAL(15,5);

 DECLARE  cur_record CURSOR FOR   SELECT colA, colB from tableABC;  /*首先这里对游标进行定义*/

 DECLARE  CONTINUE HANDLER FOR NOT FOUND  SET  no_more_record = 1; /*这个是个条件处理,针对NOT FOUND的条件,当没有记录时赋值为1*/

 

 

 OPEN  cur_record; /*接着使用OPEN打开游标*/

 FETCH  cur_record INTO pID, pValue; /*把第一行数据写入变量中,游标也随之指向了记录的第一行*/

 

 

 WHILE no_more_record != 1 DO

 INSERT  INTO testTable(ID, Value)

 VALUES  (pID, pValue);

 FETCH  cur_record INTO pID, pValue;

 

 END WHILE;

 CLOSE  cur_record;  /*用完后记得用CLOSE把资源释放掉*/

END

如果要清空表中的所有记录，可以使用下面的两种方法：

　      DELETE FROM table1

　      TRUNCATE TABLE table1

      其中第二条记录中的TABLE是可选的。

      如果要删除表中的部分记录，只能使用DELETE语句。

DELETE和TRUNCATE TABLE的最大区别是DELETE可以通过WHERE语句选择要删除的记录，但执行速度不快。

      truncate删除后不记录mysql日志，不可以恢复数据。delete的效果有点像将mysql表中所有记录一条一条删除到删完，而truncate相当于保留mysql表的结构，重新创建了这个表，所有的状态都相当于新表。而且还可以返回被删除的记录数。而TRUNCATE TABLE无法删除指定的记录，而且不能返回被删除的记录。但它执行得非常快。

      和标准的SQL语句不同，DELETE支持ORDER BY和LIMIT子句，通过这两个子句，我们可以更好地控制要删除的记录。如当我们只想删除WHERE子句过滤出来的记录的一部分，可以使用LIMIT，如果要删除后几条记录，可以通过ORDER BY和LIMIT配合使用。假设我们要删除users表中name等于"Mike"的前6条记录。可以使用如下的DELETE语句：

      DELETE FROM users WHERE name = 'Mike' LIMIT 6;

      一般MySQL并不确定删除的这6条记录是哪6条，为了更保险，我们可以使用ORDER BY对记录进行排序。

      DELETE FROM users WHERE name = 'Mike' ORDER BY id DESC LIMIT 6;

    SQL注入是一类危害极大的攻击形式。虽然危害很大，但是防御却远远没有XSS那么困难。

     SQL注入可以参见：https://en.wikipedia.org/wiki/SQL_injection

     SQL注入漏洞存在的原因，就是拼接 SQL 参数。也就是将用于输入的查询参数，直接拼接在 SQL 语句中，导致了SQL 注入漏洞。

1.演示下经典的SQL注入

      我们看到：select id,no from user where id=2;

      如果该语句是通过sql字符串拼接得到的，比如： String sql = "select id,no from user where id=" + id;

      其中的 id 是一个用户输入的参数，那么，如果用户输入的是 2， 那么上面看到查到了一条数据，如果用户输入的是 2 or 1=1 进行sql注入攻击。

      那么看到，上面的语句(select id,no from user where id=2 or 1=1;)将user表中的所有记录都查出来了。

     这就是典型的sql注入。

      再看一列：

     我们看到通过 sql 注入能够直接将表 sqlinject 删除掉！可见其危害！

2.sql注入的原因

      sql注入的原因，表面上说是因为拼接字符串，构成sql语句，没有使用sql语句预编译，绑定变量造成的。

      但是更深层次的原因是将用户输入的字符串，当成了“sql语句”来执行。

      比如上面的 String sql = "select id,no from user where id=" + id;

     我们希望用户输入的id的值，仅仅作为一个字符串字面值，传入数据库执行，但是当输入了：2 or 1=1 时，其中的 or 1=1 并没有作为 where id= 的字面值，而是作为了 sql语句 来执行的。所以其本质是将用户的输入的数据，作为了命令来执行。

3. sql注入的防御

      1> 基本上大家都知道 采用sql语句预编译和绑定变量，是防御sql注入的最佳方法。但是其中的深层次原因就不见得都理解了。

      String sql = "select id, no from user where id=?";

         PreparedStatement ps = conn.prepareStatement(sql);

         ps.setInt(1, id);

         ps.executeQuery();

      如上所示，就是典型的采用sql语句预编译和绑定变量。为什么这样就可以防止sql 注入呢？

      其原因就是：采用了PreparedStatement，就会将sql语句："select id, no from user where id=?" 预先编译好，也就是SQL引擎会预先进行语法分析，产生语法树，生成执行计划，也就是说，后面你输入的参数，无论你输入的是什么，都不会影响该sql语句的语法结构了，因为语法分析已经完成了，而语法分析主要是分析sql命令，比如 select ,from ,where ,and, or ,order by 等等。所以即使你后面输入了这些sql命令，也不会被当成sql命令来执行了，因为这些sql命令的执行，必须先得通过语法分析，生成执行计划，既然语法分析已经完成，已经预编译过了，那么后面输入的参数，是绝对不可能作为sql命令来执行的，只会被当做字符串字面值参数。所以sql语句预编译可以防御sql注入。

      2> 但是不是所有场景都能够采用 sql语句预编译，有一些场景必须的采用字符串拼接的方式，此时，我们严格检查参数的数据类型，还有可以使用一些安全函数，来方式sql注入。

      比如 String sql = "select id,no from user where id=" + id;

     在接收到用户输入的参数时，我们就严格检查 id，只能是int型。复杂情况可以使用正则表达式来判断。这样也是可以防止sql注入的。

      安全函数的使用，比如：  

      MySQLCodec codec = new MySQLCodec(Mode.STANDARD);

         name = ESAPI.encoder().encodeForSQL(codec, name);

        String sql = "select id,no from user where name=" + name;

      ESAPI.encoder().encodeForSQL(codec, name)

      该函数会将 name 中包含的一些特殊字符进行编码，这样 sql 引擎就不会将name中的字符串当成sql命令来进行语法分析了。

注

      实际项目中，一般我们都是采用各种的框架，比如ibatis,mybatis,hibernate等等。他们一般也默认就是sql预编译的。对于ibatis/mybatis，如果使用的是 #{name}形式的，那么就是sql预编译，使用 ${name} 就不是sql预编译的。

数据库事务的四大特性

原子性

　 事务的原子性指的是，事务中包含的程序作为数据库的逻辑工作单位，它所做的对数据修改操作要么全部执行，要么完全不执行。这种特性称为原子性。

　 事务的原子性要求，如果把一个事务可看作是一个程序，它要么完整的被执行，要么完全不执行。就是说事务的操纵序列或者完全应用到数据库或者完全不影响数据库。这种特性称为原子性。

  假如用户在一个事务内完成了对数据库的更新，这时所有的更新对外部世界必须是可见的，或者完全没有更新。前者称事务已提交，后者称事务撤消（或流产）。DBMS必须确保由成功提交的事务完成的所有操纵在数据库内有完全的反映，而失败的事务对数据库完全没有影响。

一致性

      事务的一致性指的是在一个事务执行之前和执行之后数据库都必须处于一致性状态。这种特性称为事务的一致性。假如数据库的状态满足所有的完整性约束，就说该数据库是一致的。

      一致性处理数据库中对所有语义约束的保护。例如，当数据库处于一致性状态S1时，对数据库执行一个事务，在事务执行期间假定数据库的状态是不一致的，当事务执行结束时，数据库处在一致性状态S2。

分离性

      分离性指并发的事务是相互隔离的。即一个事务内部的操作及正在操作的数据必须封锁起来，不被其它企图进行修改的事务看到。

      分离性是DBMS针对并发事务间的冲突提供的安全保证。DBMS可以通过加锁在并发执行的事务间提供不同级别的分离。假如并发交叉执行的事务没有任何控制，操纵相同的共享对象的多个并发事务的执行可能引起异常情况。

      DBMS可以在并发执行的事务间提供不同级别的分离。分离的级别和并发事务的吞吐量之间存在反比关系。较多事务的可分离性可能会带来较高的冲突和较多的事务流产。流产的事务要消耗资源，这些资源必须要重新被访问。因此，确保高分离级别的DBMS需要更多的开销。

持久性

　　持久性意味着当系统或介质发生故障时，确保已提交事务的更新不能丢失。即一旦一个事务提交，DBMS保证它对数据库中数据的改变应该是永久性的，耐得住任何系统故障。所以，持久性主要在于DBMS的恢复性能。持久性通过数据库备份和恢复来保证。

下面介绍mysql中模糊查询的四种用法： 

1%：

表示任意0个或多个字符。可匹配任意类型和长度的字符，有些情况下若是中文，请使用两个百分号（%%）表示。 

比如 SELECT * FROM [user] WHERE u_name LIKE '%三%' 

将会把u_name为“张三”，“张猫三”、“三脚猫”，“唐三藏”等等有“三”的记录全找出来。 

另外，如果需要找出u_name中既有“三”又有“猫”的记录，请使用and条件 

SELECT * FROM [user] WHERE u_name LIKE '%三%' AND u_name LIKE '%猫%' 

若使用 SELECT * FROM [user] WHERE u_name LIKE '%三%猫%' 

 

虽然能搜索出“三脚猫”，但不能搜索出符合条件的“张猫三”。 

2_：

表示任意单个字符。匹配单个任意字符，它常用来限制表达式的字符长度语句： 

比如 SELECT * FROM [user] WHERE u_name LIKE '_三_' 

只找出“唐三藏”这样u_name为三个字且中间一个字是“三”的； 

 

再比如 SELECT * FROM [user] WHERE u_name LIKE '三__'; 只找出“三脚猫”这样name为三个字且第一个字是“三”的； 

3[ ]：

表示括号内所列字符中的一个（类似正则表达式）。指定一个字符、字符串或范围，要求所匹配对象为它们中的任一个。 

比如 SELECT * FROM [user] WHERE u_name LIKE '[张李王]三' 将找出“张三”、“李三”、“王三”（而不是“张李王三”）； 

如 [ ] 内有一系列字符（01234、abcde之类的）则可略写为“0-4”、“a-e” 

SELECT * FROM [user] WHERE u_name LIKE '老[1-9]' 将找出“老1”、“老2”、……、“老9”； 

4[^ ] ：

表示不在括号所列之内的单个字符。其取值和 [] 相同，但它要求所匹配对象为指定字符以外的任一个字符。 

比如 SELECT * FROM [user] WHERE u_name LIKE '[^张李王]三' 将找出不姓“张”、“李”、“王”的“赵三”、“孙三”等； 

SELECT * FROM [user] WHERE u_name LIKE '老[^1-4]'; 将排除“老1”到“老4”，寻找“老5”、“老6”、…… 

 

5查询内容包含通配符时 

由于通配符的缘故，导致我们查询特殊字符“%”、“_”、“[”的语句无法正常实现，而把特殊字符用“[ ]”括起便可正常查询。据此我们写出以下函数： 

function sqlencode(str) str=replace(str,"';","';';") 

str=replace(str,"[","[[]") ';此句一定要在最先 str=replace(str,"_","[_]") str=replace(str,"%","[%]") sqlencode=str end function

在mysql默认order by 只对数字与日期类型可以排序，但对于varchar字符型类型排序好像没有用了，下面我来给各位同学介绍varchar类型排序问题如何解决。

今天在对国家电话号码表进行排序的时候发现了一个有趣的问题，我想让isdcode字段按照由小到大的顺序排序，于是乎我是这样写的

SELECT * FROM gb_country_isdcode ORDER BY isdcode asc

结果如下，发现竟然不是我想要的结果，asc排序是对的呀，于是乎我找呀找，找呀找，终于找到原因了；

isdcode是varcher类型的，如果排序的直接用asc显然是不行的，必须将他转换成int类型然后就可以正常排序了，只要isdcode + 0就可以了

于是乎这样写

SELECT * FROM gb_country_isdcode ORDER BY (isdcode+0) asc

好像是想要的那种数据比较大小的了呀。。可是为什么+0就好了呢？原来，+0后就转换INT类型排序了。这样就可以按照大小排序了。

如果不是电话而是汉字怎么办，汉字排序我们只要进行简单转换即可排序了。

在mysql中使用order by对存储了中文信息的字段，默认出来的结果并不是按汉字拼音的顺序来排序，要想按汉字的拼音来排序，需要把数据库的字符集设置为UTF8，然后在order by 时候强制把该字段信息转换成GBK，这样出来的结果就是按拼音顺序排序的。例如：

SELECT * FROM table_name ORDER BY CONVERT(column_name USING gbk);

在mysql中试了一下，结果很令人满意。

结论是：查询的时候，通过convert函数，把查询出来的数据使用的字符集gb2312编码就可以了，然后使用convert之后的中文排序。但是如果真的去把表中字段的字符集改成gb2312，又会涉及到很多编码的问题，页面传值啊，从数据库中存取啊，很麻烦。只要在查询的时候，指定一下字符集，并不是真的把物理字段改成gb2312，很简单。