【MySQL】基础学习全解（三）

参考1：廖雪峰老师 的 mysql教程

参考2：实验楼 的 mysql教程

目录

1、事务

1.1  定义

1.2  特性（ACID）

1.3  隔离级别

1.4  隔离级别详解

2、函数

2.1  字符串类型

2.2  常用函数

2.3  语句

2.4  例子  

3、视图与索引

3.1  视图

3.2  索引 

4、触发器与存储过程

4.1  触发器：

4.2  存储过程 

 

 

1、事务

1.1  定义

       把多条语句作为一个整体进行操作的功能，可以确保该事务范围内的所有操作都可以全部成功或者全部失败。如果事务失败，那么效果就和没有执行这些SQL一样，不会对数据库数据有任何改动。

• 显示事务：多条SQL语句作为一个事务执行，使用BEGIN开启一个事务，使用COMMIT提交一个事务
• 隐式事务：对于单条SQL语句，数据库系统自动将其作为一个事务执行

1.2  特性（ACID）

1、原子性(Atomicity)：事务中的全部操作在数据库中是不可分割的，要么全部完成，要么均不执行。

2、一致性(Consistency)：几个并行执行的事务，其执行结果必须与按某一顺序串行执行的结果相一致。

3、隔离性(Isolation)：事务的执行不受其他事务的干扰，事务执行的中间结果对其他事务必须是透明的。

4、持久性(Durability)：对于任意已提交事务，系统必须保证该事务对数据库的改变不被丢失，即使数据库出现故障。     

1.3  隔离级别

   

1.4  隔离级别详解

• Read Uncommitted

这个隔离级别会出现脏读的情况：我改了个数据，不过还没确定，而你却把还没确定的给读取了。我如果决定不这么改，你读到的就是脏数据。

• Read committed

这个隔离级别会出现不可重复读的情况：我一直在读一个东西，你中间插一脚改了一下，我两次读到的就不一样了。

• Repeatable Read

这个隔离级别会出现幻读的情况：我准备找一个数据，结果显示没有，然后想更新一下，结果还真能更新，在进行查找竟然找到了（原来是有人插入了符合我查找条件的数据）。

• Serializable（使用InnoDB默认隔离级别）

 

2、函数

2.1  字符串类型

   

2.2  常用函数

• 数字

       RAND( )：取随机值

       ABS( )：取绝对值

       CEILING( )：获取比当前数大的最小整数

       FLOOR( )：获取比当前数小的最大整数

       POWER(3,4)

       ROUND(1.54,1 )：四舍五入到小数点后一位

       SIGN( )：判断正负数

• 字符串

       ISNULL(字段名, ' ')：将null值替换为指定字符串值

       LTRIM(RTRIM(' A '))：去左右空格

       LOWER(UPPER(' A '))：转换大小写

       REPLACE(' ABC ', ' A ' , ' C ')：替换

       STUFF(' ABCDE ', 2 , 2 , ' ABC ')：’AABCDE‘

       SUBSTRING(' ABCDE ', 3 , 2)：去字符串 'CD'

2.3  语句

• if...else语句

       例子：

               declare @LoginID int, @rtn int;

               if 

                    @ LoginID == 3  then  set @ rtn = 3;

                    elseif  @ LoginID == 2  then set @ rtn = 2;

                    else set @ rtn = 1;

               endif;

 

• while语句

       例子：

               declare @n int;

               while  @ n > 0  do 

                     select  n;

                     set  @ n = @ n -1 ;

               endwhile;

2.4  例子  

1、例子一

      

2、例子二:有关时间的函数

要想确定每个宠物有多大，可以使用函数TIMESTAMPDIFF()计算当前日期的年和出生日期之间的差

也可以按照直接使用语句(YEAR(CURDATE())-YEAR(birth))计算，

其中函数CURDATE()是计算当前的日期。如果当前日期的日历年比出生日期早，则减去一年。以下代码是查询每个宠物的出生日期、当前日期和年龄（以年作为计算单位），其中关键字age是年龄这个计算结果的标签。

mysql> SELECT name, birth, CURDATE(),
    -> TIMESTAMPDIFF(YEAR,birth,CURDATE()) AS age
    -> FROM pet;


# 比较这两个查询语句的结果

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5)) AS age  
    -> FROM pet;

此处，YEAR() 提取日期的年数，RIGHT() 提取日期最右面5个字符的 MM-DD (月份和日期)部分。MM-DD 值的表达式部分的值一般为 1 或 0，如果 CURDATE() 的年比 birth 的年早，则年份应减去 1。整个表达式看起来有些难懂，使用 age 来使输出的列标记更有意义。

尽管查询可行，但整体输出结果缺乏秩序，如果以某个顺序排列行，那么会使得浏览结果变得更加轻松。添加 ORDER BY name 子句则能够实现按照名字进行排序输出。

mysql> SELECT name, birth, CURDATE(),
    -> (YEAR(CURDATE())-YEAR(birth))
    -> - (RIGHT(CURDATE(),5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet ORDER BY name;

 

可以使用一个类似的查询来确定已经死亡动物的死亡年龄。你通过检查death值是否为NULL来确定是哪些动物已经死亡，然后对于那些非NULL值的动物，需要计算出death和birth值之间的差来知道他们在这个世界上所存在的时间：

mysql> SELECT name, birth, death,
    -> (YEAR(death)-YEAR(birth)) - (RIGHT(death,5)<RIGHT(birth,5))
    -> AS age
    -> FROM pet WHERE death IS NOT NULL ORDER BY age;

查询使用death IS NOT NULL而非death != NULL，因为NULL是特殊的值，不能使用普通比较符来比较。

如果你想要知道哪个动物下个月过生日怎么办？对于这类计算，年和天是无关的，你只需要提取birth列的月份部分。MySQL提供几个日期方面的提取函数，例如YEAR()、MONTH()和DAYOFMONTH()。在这里MONTH()是我们需要的函数。为了观察它的实现原理，可以运行以下简单的查询显示birth和MONTH(birth)的值：

mysql> SELECT name, birth, MONTH(birth) FROM pet;

找出下个月生日的动物也很简单。假定当前月是4月，那么月值是4，你需要找在5月出生的动物，方法是：

mysql> SELECT name, birth FROM pet WHERE MONTH(birth) = 5;

如果当前月份是12月，就有点复杂了。你不能只把1加到月份数(12)上并寻找在13月出生的动物，因为没有这样的月份。相反，你应寻找在1月出生的动物。

你甚至可以编写查询，不管当前月份是什么它都能执行。因此不必在查询中使用一个特定的月份，DATE_ADD()允许在一个给定的日期上加上时间间隔。如果在NOW()值上加上一个月，然后用MONTH()提取月份，产生生日所在月份：

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));

完成该任务的另一个方法是加1得出当前月份的下一个月(在使用取模函数MOD()后，如果月份当前值是12，则“返回”到值0)：

mysql> SELECT name, birth FROM pet
    -> WHERE MONTH(birth) = MOD(MONTH(CURDATE()), 12) + 1;

注意，MONTH返回在1和12之间的一个数字，且MOD(something,12)返回在0和11之间的一个数字，因此必须在MOD()后加1，否则我们将从11月(11)跳到1月(1)。

3、例子三

使用BIT_COUNT函数计算每个月中某用户访问网页的天数：

CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL,
    day INT(2) UNSIGNED ZEROFILL);
INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2),
    (2000,2,23),(2000,2,23);

上述建立的表中有用户访问网页的年月日，可以使用以下语句查询每个月的访问天数：

3、视图与索引

3.1  视图

视图是从一个或多个表中导出来的表，是一种虚拟存在的表。它就像一个窗口，通过这个窗口可以看到系统专门提供的数据，这样，用户可以不用看到整个数据库中的数据，而只关心对自己有用的数据。

注意理解视图是虚拟的表：

• 数据库中只存放了视图的定义，而没有存放视图中的数据，这些数据存放在原来的表中；
• 使用视图查询数据时，数据库系统会从原来的表中取出对应的数据；
• 视图中的数据依赖于原来表中的数据，一旦表中数据发生改变，显示在视图中的数据也会发生改变；
• 在使用视图的时候，可以把它当作一张表。

     

3.2  索引 

比如有一个用户表，它拥有用户名(username)和个人签名(note)两个字段。其中用户名具有唯一性，并且格式具有较强的限制，我们给用户名加上一个唯一索引；个性签名格式多变，而且允许不同用户使用重复的签名，不加任何索引。

这时候，如果你要查找某一用户，使用语句 select * from user where username=? 和 select * from user where note=? 性能是有很大差距的，对建立了索引的用户名进行条件查询会比没有索引的个性签名条件查询快几倍，在数据量大的时候，这个差距只会更大。

一些字段不适合创建索引，比如性别，这个字段存在大量的重复记录无法享受索引带来的速度加成，甚至会拖累数据库，导致数据冗余和额外的 CPU 开销。

      # show index from 表名

   

 

4、触发器与存储过程

4.1  触发器：

      作用：实现对表操作记录的监听

     

     

4.2  存储过程