mysql零碎知识点（持续更新）

（以下练习数据库资源可以在mysql练习题中找到，直接运行即可）

1.NULL值处理

mysql不支持布尔类型的值存储的，当给一个字段定义成BOOL类型的时候，mysql会自动将值转换成tinyint类型的，只有两个值1/0，1表示true，0表示false。当用where关键字做条件判断的时候可以用0表示false,用不为0的数字表示true.

mysql中的NULL值与任何值比较和运算返回值都为NULL。

判断某条数据某个字段是否为NULL可以用 IS 关键字：

IS NULL：当列的值为NULL时，返回1(true)。

IS NOT NULL：当列的值不为NULL时，返回1(true).（值包括任何值，0也是一种值）

<=>：值1<=>值2，当值1，值2都是NULL时返回1(true)。（值1=值2，当值1，值2都是NULL时返回NULL）

ifnull（值，默认值）：当值为NULL，选择默认值代替当前值。NULL值时没有办法做运算的当我们需要某个字段做运算的时候可以使用ifnull函数。比如：SELECT 1+IFNULL(NULL,0);，结果为1。

0='null'/0='NULL'：这个结果为1（true）。^_^  （黑技术）。

2.UNION 

unoin是联合的意思，即将结果合并在一起，它可以将查询到的多个结果合并到一起。

语法：

          查询语句1 

           union [ALL/DISTINCT]

           查询语句2

            union [ALL/DISTINCT]

            查询语句3

            、、、

ALL和DISTINCT都是可选项，默认是DISTINCT.

DISTINCT：意思是有区别的，结果集是不重复的。

ALL：意思是所有，不论重复不重复都会显示。

注意事项：

①.所有查询结果的字段数量要一致，即查询结果的列数要一样多。

例子：

SELECT * FROM EMP
            UNION
            SELECT * FROM DEPT;

错误代码： 1222
           The used SELECT statements have a different number of columns

②.如果查询结果列数一样多，最后显示结果的列名将是第一条查询结果的列名。

SELECT DEPTNO FROM EMP
           UNION
           SELECT DNAME FROM DEPT
           UNION
           SELECT JOB FROM EMP;

结果：

             ③.如果需要显示重复的数据可以添加ALL关键字

SELECT DEPTNO FROM EMP
UNION ALL
SELECT JOB FROM EMP;

结果：

3.mysql结合正则表达式

mysql中的正则表达式一般都是和查询条件结合起来

语法：REGEXP 正则表达式

① 选择员工表中所有名字带有s的员工信息

SELECT * FROM emp WHERE ENAME REGEXP 's';

②.选择员工编号以7开头，并以部门分组，分组后的结果中1981年入职的员工

SELECT * FROM EMP WHERE EMPNO REGEXP '^7'
GROUP BY DEPTNO HAVING HIREDATE REGEXP '1981';

③.由于效率问题，能不能正则就不用正则

 4.mysql中的事物（transaction）

mysql中的SQL语句默认都是直接提交的。即当一句可执行DML的SQL语句执行完毕，那么数据库就会对这条所影响到的数据进行持久化的更改。

我们在mysql数据库工具中（SQLyog,Nvacat）中，选中一批SQL语句（DDL,DML,DCL,即对数据库进行修改的语句）进行执行时，看起来是进行一次批处理，实际上是依次执行语句，每执行一条语句都要从数据库获得连接，执行完毕后，提交执行结果，持久化到数据库。这样在连接断开的过程中浪费了许多时间，这个时候可以用事务处理，真正的进行批处理。

另外如果我们需要保证数据的原子性一致性（即这些语句要么全部成功，要么全部失败，执行前后的数据在整体上是一致的），必须要用事物处理。最经典的例子就是银行转账操作，A转账给B 100元，如果我们没有加事物，执行流程是这样的：A-100 commit;，B+100  commit;，如果在中间出了些差错（代码出错，客户端机器坏掉。。。），那么A-100提交后，B并没有提交。那个这种情况是当然不允许的，谁能愿意平白无故受损失，这个时候必须要用到事物了，开启事物的过程是这样的：A-100;B+100;commit;当中间出错的时候就不提交进行数据库的持久化，回滚到执行这些操作之前，一切操作都当做是失败，当没有出错的时候，才进行数据的持久化。

mysql中的事物主要用于处理操作量大、复杂度高和需要进行原子操作的数据。

①.注意事项：

•          在mysql中只有使用了Innodb数据库引擎的数据库和表才支持事物。
•          事物主要用来数据的完整性，即保证SQL要不全部成功,要不全部失败。
•          事物主要用来管理 insert、update、delete语句

②.事物具备以下四个特性（ACID）：

• 原子性（Atomicity）：即实务中的操作要么全部成功，要么全部失败。中间发证错误，就回滚（Rollback）到事物开启之前的状态。
• 一致性（Consistency）：事物操作前后要满足数据的完整性，要必须符合我们所有预设的规则。比如转账：一方减少多少，另一方就要对应的增加多少，两个账户的资金总数和转账之前是一致的。
• 隔离性（Isolation，又称独立性）：数据库允许并发事物对同一数据进行读写和修改，隔离性就是保证数据在事物并发时的准确性。根据在事物并发时、不同情况下读取数据的结果将隔离性分为四个等级，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
• 持久性（Durability）：当事物成功执行完毕后，对数据的修改是持久性的，即保存到了硬件磁盘中。

③.事物并发问题：

• 脏读：当事物A修改表中数据，事物B读取这条数据，之后事物A回滚操作，事物B的操作就属于脏读，读取的数据也叫做脏数据

-- 开启事物
BEGIN;
-- 更新数据
UPDATE EMP SET DEPTNO=10 WHERE EMPNO=7369;
-- 读取数据
SELECT DEPTNO FROM EMP WHERE EMPNO=7369;
-- 回滚操作
ROLLBACK;

再来看看真正的数据：

SELECT DEPTNO FROM EMP WHERE EMPNO=7369;

• 不可重复读：事物A第一次读取数据，事物B修改数据，之后事物A有读取数据，事物A两次读取的数据不一致，就叫做不可重复读。

-- 开启事物
BEGIN;
-- 第一次读取数据
SELECT DEPTNO FROM EMP WHERE EMPNO=7369;

结果：

-- 更新数据
UPDATE EMP SET DEPTNO=10 WHERE EMPNO=7369;
-- 第二次读取数据
SELECT DEPTNO FROM EMP WHERE EMPNO=7369;

结果：

-- 回滚操作
ROLLBACK;

• 幻读：事物A对表中所有数据进行处理，事物B添加一条数据，事物A执行完毕看到还有数据没有进行处理（明明都处理了，为什么还有数据没处理，像是幻觉一样），这个时候称为幻读

-- 开启事物
BEGIN;
-- 给每个员工添加性别字段，都设置为男性
ALTER TABLE EMP ADD SEX VARCHAR(3);
UPDATE EMP SET SEX='男';

SELECT * FROM EMP;

这个时候数据是：

-- 另一个事物插入一条数据
INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) 
VALUES(1,'new','new','000','2000-00-00',100,100,10);
SELECT * FROM EMP;

这个时候的结果：

ROLLBACK;

 ④.隔离等级与事务并发问题

mysql的默认隔离等级是：

规避了脏读可不可重复读的问题。

隔离等级                                                          脏读           不可重复读         幻读

读未提交（Read uncommitted）                      √                       √                    √           （可能出现任何并发问题）

读提交（read committed）                                ×                      √                    √              （只读取提交数据）

可重复读（repeatable read）                            ×                       ×                    √               （对行数据加锁）

串行化（Serializable）                                      ×                        ×                    ×                （对表加锁）

⑤事物操作：

• 设置默认提交状态：

SET AUTOCOMMIT=0 ：默认不提交

SET AUTOCOMMIT=1：默认提交

• 开启事物：

 BEGIN/START TRANSACTION

• 回滚事务：

ROLLBACK：回滚到事物开启之前

SAVEPOINT identifier ：创建事物保存点

RELEASE SAVEPOINT identifie ： 删除事物保存点

ROLLBACK TO identifier ：回滚到指定保存点

• 提交事务：

COMMIT

5.导出数据

①导出查询结果INTO OUTFILE：

SELECT * FROM EMP INTO OUTFILE 'f:\emp.txt';

结果：

记事本打开是一行数据，用高级文本编辑器打开结果如下：

这绝不是想要的结果。

2.导出SQL可执行文件（备份）：

在命令行下输入：

mysqldump -uroot -p test > test.sql

结果：

里面数据：

里面数据包括了数据库中的所有数据，建表、插入数据、表关系语句等。

导入这些数据（恢复）

mysql -uroot -p test < test.sql 

 。。。