MYSQL基础知识总结(二)

五、运算符和函数

5.1、字符函数

名称	描述	说明
CONCAT()	字符连接	CONCAT(str1,str2,...);SELECT CONCAT(first,second) AS name FROM user
CONCAT_WS()	使用指定的分隔符进行连接	CONCAT_WS(str1,str2,...);SELECT CONCAT(‘-’，'imooc','MYSQL')
FORMAT()	数字格式化	FORMAT(NUMBER,n);保留n为小数,返回字符型数字
LOWER()	转换成小写字母
UPPER()	转换成大写字母
LEFT()	获取左侧字符	LEFT(str,n)
RIGHT()	获取右侧字符
LENGTH()	获取字符串长度
LIRIM()	删除前导空格
RTRIM()	删除后续空格
TRIM()	删除前导和后续空格
SUBSTRING()	字符串截取	位置可以为负值，但是长度不能为负值
[NOT]LIKE	模式匹配	like+通配符进行过滤,“%”任意字符出现任意次数,"_"匹配单个字符, "[JM]"以J或M开头的,"[^JM]"不易J或M开头的,[]指定范围的单个字符
REPLACE()	字符串替换

5.2、数值运算符及函数

名称	描述	说明
CEIL()	进一取整	向上取整，ceil(2.99)=3
DIV	整数除法
FLOOR()	舍一取整
MOD	取余数（取模）
POWER()	幂运算
ROUND()	四舍五入	ROUND(NUM,n),n为保留几位小数
TRUNCATE()	数字截取	TRUNCATE(NUM,n),注意TRUNCATE(125.89,0)=125,TRUNCATE(125.89,-1)=120, TRUNCATE(125.89,-2)=100,TRUNCATE(125.89,-3)=0

5.3、比较远算符及函数

名称	描述	说明
[NOT]BETWEEN...AND...	[不]在范围之内
[NOT]IN()	[不]在列出值范围内
IS [NOT] NULL	[不]为空

5.4、日期时间函数

名称	描述	说明
NOW()	当前日期和时间
CURDATE()	当前日期
CURTIME()	当前时间
DATE_ADD()	日期变化	SELECT DATE_ADD('2014-3-12',INTERVAL 1 YEAR),这个输出为2015-03-12
DATEDIFF()	日期差值
DATE_FORMAT()	日期格式化

5.5、信息函数

名称	描述
COUNECTION_ID()	连接ID
DATABASE()	当前数据库
LAST_INSERT_ID()	最后插入记录的ID号。注意：不管一次插入几条记录，返回的都是最后一条的ID号
USER()	当前用户
VERSION()	版本信息

5.6、聚合函数

名称	描述	说明
AVG()	平均数，NULL 值不包括在计算中	SELECT customers FROM Orders WHERE orderPrice>(SELECT AVG(orderPrice) FROM Orders)
COUNT()	计数,NULL值不计入	SELECT COUNT(*) FROM tbl_name//返回表中的记录数 SELECT COUNT(DISTINCT column_name) FROM tbl_name;//返回指定列不通知的数目
MAX()	最大值
MIN()	最小值
SUM()	求和

5.7、加密函数

名称	描述	说明
MD5()	信息摘要算法	SELECT MD5('KCY')
PASSWORD()	密码算法	SET PASSWORD = PASSWORD('root);将用户的密码修改成root

5.8、自定义函数

创建自定义函数：

              CREATE FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL} rountine_body

例如：1)编写一个无参函数，改写日期的格式

               CREATE FUNCTION f1() RETURNS VARCHAR(30) RETURNS DATE_FORMAT(NOW(),%Y年%m月%d日 %H点%i分%s秒");

               调用：SELECT f1();

            2)编写一个有参函数来计算均值

               CREATE FUNCTION f2(num1 SMALLINT UNSIGNED,num2 SMALLINT UNSIGNED) RETURNS float(10,2)unsigned return (num1+num2)/2;

               调用：SELECT f2(10,15);

六、MYSQL中的存储引擎

6.1、存储引擎就是以不同的存储机制、索引、锁方式等技术将数据存储在文件或者内存中；

          常见的存储引擎有MyISAM、InnoDB、Menory、Archive等
6.2、并发控制，多个连接对数据库进行修改时保证数据的一致性和完整性，MySQL提供两个级别的并发控制分别为服务器级和存储引擎级。
6.3、锁，实现并发控制的一种方法。 
         1)、锁分为共享锁（读个用户可同时进行读操作）和排他锁（只有一个用户进行写操作）
         2)、锁的粒度表级锁（开销较小）和行级索（开销较大）。    
6.4、事务，是恢复和并发控制的基本单位；
         1)、数据库事务的四特性ACID如下：
           Atomic 原子性，整个数据库事务是不可分割的工作单位
           Consistency 一致性，数据库事务不能破坏关系数据的完整性以及业务逻辑上的一致性
           Isolation 隔离性，在并发环境中，当不同的事务同时操纵相同的数据时，每个事务都有各自的完整数据空间
           Durability 持久性，指的是只要事务成功结束，它对数据库所做的更新就必须永久保存下来
         2)、事务的隔离级别
           Serializable：可避免脏读、不可重复读、虚读情况的发生。
           Repeatable read：可避免脏读、不可重复读情况的发生。（可重复读，是 mysql 默认的事务隔离级别）
           Read committed：可避免脏读情况发生。（读取已提交的数据）
           Read uncommitted：最低级别，以上情况均无法保证。（读取到了未提交的数据）
         3)、实际的开发过程用到事务开启、回滚、关闭等操作，保证数据的正确性。
 6.5、索引，在存储引擎级别实现的，对一行或者多行记录进行排序的结构，包含普通索引、唯一索引、主键索引、组合索引等，索引的创建同表的创建 

                      create [unique]index on t_name(字段);
6.6、不同存储引擎之间的对比
        1）、MyISAM：存储限制为256TB,支持索引，表锁，可进行数据压缩
        2）、InnoDB: 存储限制为64TB, 支持事务，支持索引，行锁，支持外键
        3）、Memory: 存在内存中，支持索引，表锁
        4）、Archive：无存储限制，行锁，支持数据压缩