本文详细介绍了MySQL中的各种函数，包括数值型、字符串、日期时间、聚合及流程控制函数，并重点讲解了加密函数如PASSWORD和MD5的使用。接着探讨了MySQL窗口函数的应用，如row_number和lead/lag。接着，文章阐述了数据库锁的作用、类型和并发操作，以及InnoDB和MyISAM引擎的锁粒度。最后，讨论了索引对查询效率的影响，包括不同类型的锁以及如何在MySQL中创建和删除索引。
函数
MySQL 数值型函数
| [ABS] | 求绝对值 |
|---|---|
| [SQRT] | 求二次方根 |
| [MOD] | 求余数 |
| [CEIL 和 CEILING] | 两个函数功能相同，都是返回不小于参数的最小整数，即向上取整 |
| [FLOOR] | 向下取整，返回值转化为一个BIGINT |
| [RAND] | 生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列 |
| [ROUND] | 对所传参数进行四舍五入 |
| [SIGN] | 返回参数的符号 |
| [POW 和 POWER] | 两个函数的功能相同，都是所传参数的次方的结果值 |
| [SIN] | 求正弦值 |
| [ASIN] | 求反正弦值，与函数 SIN 互为反函数 |
| [COS] | 求余弦值 |
| [ACOS] | 求反余弦值，与函数 COS 互为反函数 |
| [TAN] | 求正切值 |
| [ATAN] | 求反正切值，与函数 TAN 互为反函数 |
| [COT] | 求余切值 |
MySQL 字符串函数
| 函数名称 | 作 用 |
|---|---|
| [LENGTH] | 计算字符串长度函数，返回字符串的字节长度 |
| [CONCAT] | 合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个 |
| [INSERT] | 替换字符串函数 |
| [LOWER] | 将字符串中的字母转换为小写 |
| [UPPER] | 将字符串中的字母转换为大写 |
| [LEFT] | 从左侧字截取符串，返回字符串左边的若干个字符 |
| [RIGHT] | 从右侧字截取符串，返回字符串右边的若干个字符 |
| [TRIM] | 删除字符串左右两侧的空格 |
| [REPLACE] | 字符串替换函数，返回替换后的新字符串 |
| [SUBSTRING] | 截取字符串，返回从指定位置开始的指定长度的字符换 |
| [REVERSE] | 字符串反转（逆序）函数，返回与原始字符串顺序相反的字符串 |
REVERSE(str):返回颠倒字符顺序的字符串str。
select REVERSE('abc');
CONCAT(str1,str2,...):返回来自于参数连结的字符串
LENGTH(str):返回字符串str的长度。
INSTR(str,substr):返回子串substr在字符串str中的第一个出现的位置。
select INSTR('foobarbar', 'bar');
LEFT(str,len):返回字符串str的最左面len个字符。
select LEFT('foobarbar', 5);
RIGHT(str,len):返回字符串str的最右面len个字符。
select RIGHT('foobarbar', 4);
SUBSTRING(str,pos):从字符串str的起始位置pos返回一个子串。
select SUBSTRING('Quadratically',5);
TRIM(str):返回字符串str，所有前缀或后缀被删除了。
select TRIM(' bar ');
LTRIM(str):返回删除了其前置空格字符的字符串str。
select LTRIM(' barbar');
RTRIM(str):返回删除了其拖后空格字符的字符串str。
select RTRIM(‘barbar ’);
REPLACE(str,from_str,to_str):返回字符串str，其字符串from_str的所有出现由字符串to_str代替。
select REPLACE('www.mysql.com', 'w', 'Ww');
INSERT(str,pos,len,newstr):返回字符串str，在位置pos起始的子串且len个字符长的子串由字符串newstr代替。
select INSERT(‘whatareyou', 5, 3, ‘is');
MySQL 日期和时间函数
| 函数名称 | 作 用 |
|---|---|
| [CURDATE 和 CURRENT_DATE] | 两个函数作用相同，返回当前系统的日期值 |
| [CURTIME 和 CURRENT_TIME] | 两个函数作用相同，返回当前系统的时间值 |
| [NOW 和 SYSDATE] | 两个函数作用相同，返回当前系统的日期和时间值 |
| [UNIX_TIMESTAMP] | 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数 |
| [FROM_UNIXTIME] | 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数 |
| [MONTH] | 获取指定日期中的月份 |
| [MONTHNAME] | 获取指定日期中的月份英文名称 |
| [DAYNAME] | 获取指定曰期对应的星期几的英文名称 |
| [DAYOFWEEK] | 获取指定日期对应的一周的索引位置值 |
| [WEEK] | 获取指定日期是一年中的第几周，返回值的范围是否为 0〜52 或 1〜53 |
| [DAYOFYEAR] | 获取指定曰期是一年中的第几天，返回值范围是1~366 |
| [DAYOFMONTH] | 获取指定日期是一个月中是第几天，返回值范围是1~31 |
| [YEAR] | 获取年份，返回值范围是 1970〜2069 |
| [TIME_TO_SEC] | 将时间参数转换为秒数 |
| [SEC_TO_TIME] | 将秒数转换为时间，与TIME_TO_SEC 互为反函数 |
| [DATE_ADD 和 ADDDATE] | 两个函数功能相同，都是向日期添加指定的时间间隔 |
| [DATE_SUB 和 SUBDATE] | 两个函数功能相同，都是向日期减去指定的时间间隔 |
| [ADDTIME] | 时间加法运算，在原始时间上添加指定的时间 |
| [SUBTIME] | 时间减法运算，在原始时间上减去指定的时间 |
| [DATEDIFF] | 获取两个日期之间间隔，返回参数 1 减去参数 2 的值 |
| [DATE_FORMAT] | 格式化指定的日期，根据参数返回指定格式的值 |
| [WEEKDAY] | 获取指定日期在一周内的对应的工作日索引 |
DAYOFWEEK(date):返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)。
select DAYOFWEEK('1998-02-03');
select DAYOFWEEK(now());
WEEKDAY(date):返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
select WEEKDAY('1997-11-05');
DAYOFMONTH(date):返回date的月份中的日期，在1到31范围内。
select DAYOFMONTH('1998-02-03');
DAYOFYEAR(date):返回date在一年中的日数, 在1到366范围内。
select DAYOFYEAR('1998-02-03');
MONTH(date):返回date的月份，范围1到12。
select MONTH('1998-02-03');
DAYNAME(date):返回date的星期名字。
select DAYNAME("1998-02-05");
MONTHNAME(date) :返回date的月份名字。
select MONTHNAME("1998-02-05");
QUARTER(date):返回date一年中的季度，范围1到4。
select QUARTER('98-04-01');
YEAR(date):返回date的年份，范围在1000到9999。
select YEAR('98-02-03');
HOUR(time):返回time的小时，范围是0到23。
select HOUR('10:05:03');
MINUTE(time):返回time的分钟，范围是0到59。
select MINUTE('98-02-03 10:05:03');
SECOND(time):回来time的秒数，范围是0到59。
select SECOND('10:05:03');
DATE_ADD(date,INTERVAL expr type) ,进行日期增加的操作，可以精确到秒
DATE_SUB(date,INTERVAL expr type) ，进行日期减少的操作，可以精确到秒
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL 1 SECOND);
SELECT DATE_ADD("1997-12-31 23:59:59", INTERVAL "1:1" MINUTE_SECOND);
CURRENT_DATE:以‘YYYY-MM-DD’或YYYYMMDD格式返回今天日期值，取决于函数在一个字符串还是数字上下文被使用。
select CURDATE();
CURRENT_TIME:以‘HH:MM:SS’或HHMMSS格式返回当前时间值
select CURTIME();
NOW():以‘YYYY-MM-DD HH:MM:SS’或YYYYMMDDHHMMSS格式返回当前的日期和时间
select NOW();
MySQL 聚合函数
| 函数名称 | 作用 |
|---|---|
| [MAX] | 查询指定列的最大值 |
| [MIN] | 查询指定列的最小值 |
| [COUNT] | 统计查询结果的行数 |
| [SUM] | 求和，返回指定列的总和 |
| [AVG] | 求平均值，返回指定列数据的平均值 |
MySQL 流程控制函数
| 函数名称 | 作用 |
|---|---|
| [IF] | 判断，流程控制 |
| [IFNULL] | 判断是否为空 |
| [CASE] | 搜索语句 |
CASE value WHEN [compare-value] THEN result [WHEN [compare-value] THEN result ...] [ELSE result] END CASE WHEN [condition] THEN result [WHEN [condition] THEN result ...] [ELSE result] END
IF(expr1,expr2,expr3)
如果 expr1 是TRUE (expr1 <> 0 and expr1 <> NULL)，则 IF()的返回值为expr2; 否则返回值则为 expr3。IF() 的返回值为数字值或字符串值
加密函数
-
加密函数PASSWORD(str)
PASSWORD(str)函数可以对字符串str进行加密。一般情况下，PASSWORD(str)函数主要是用来给用户的密码加密的。 -
加密函数MD5(str)
MD5(str)函数可以对字符串str进行加密。MD5(str)函数主要对普通的数据进行加密。下面使用MD5(str)函数为字符串“abcd”加密。
-
ENCODE(str,pswd_str)函数可以使用字符串pswd_str来加密字符串str。加密的结果是一个二进制数，必须使用BLOB类型的字段来保存它。
解密函数：
DECODE(crypt_str,pswd_str)函数可以使用字符串pswd_str来为crypt_str解密
MySQL窗口函数简介
-
序号函数：row_number() / rank() / dense_rank()
-
lead,lag
窗口函数如何使用
函数名（[expr]） over子句
可以在OVER子句中分割结果
partition by：分组order by：排序
create table tb_score(id int primary key auto_increment,stu_no varchar(10),course varchar(50),score decimal(4,1)，key idx_stuNo_course(stu_no,course));
锁
-
作用
避免资源挣用的机制
资源征用：多个任务使用同一个资源，对资源产生征用
在数据库中，数据就是资源，不同的客户端对记录的CRUD操作就是任务
解决方式：在某个任务使用某个资源的时候，进行标记，其他任务不能同时使用，等待或者放弃
-
流程
加锁 -->成功–>使用资源–>释放锁
加锁–>失败–>进操作队列–>重复加锁
-
锁类型
- 共享锁，读锁，S-lock，都能读取表中的记录 但是不能写
- 排他锁，独占锁，写锁，X-lock：
-
锁粒度
不同的锁导致并发操作结果不一样
-
表级：table-level，锁定整张表
-
行级: row-level ,锁定表中的某些行的记录
Innodb ,实现了表级和行级锁
MyISAM 实现表级锁
-
-
语法
-
表锁（Innodb 和 MyISAM 引擎都支持）
加锁：lock tables 表名1,表名2 read|write
释放锁：unlock tables
-
行锁（Innodb 支持，在InnoDB事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，等到事务结束时才释放）
共享级： select * from 表 where id=1 lock in share mode;
排他级： select * from 表 条件 for update
-
索引
作用
帮助mysql提高查询效率
索引存储在磁盘中
索引就是空间换时间
语法
- 创建表时
-
CREATE TABLE s1(
id INT ,
NAME CHAR(20),
age INT,
email VARCHAR(30),
PRIMARY KEY(id,age)
);
inforamtion varchar(10000) ,
INDEX(email)
-
在已存在的表上创建
ALTER TABLE 表名 ADD [UNIQUE | FULLTEXT | SPATIAL ] INDEX 索引名 (字段名[(长度)] [ASC |DESC]) ; -
删除索引
DROP INDEX 索引名 ON 表名字;
使用
创建表时
CREATE TABLE s1(
id INT ,
NAME CHAR(20),
age INT,
email VARCHAR(30),
PRIMARY KEY(id,age)
);
inforamtion varchar(10000) ,
INDEX(email)
在创建表后添加索引
create index name on s1(name); #添加普通索引
create unique age on s1(age);添加唯一索引
alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
删除索引
DROP INDEX NAME ON s1; #删除普通索引
DROP INDEX email ON s1; #删除唯一索引，就和普通索引一样，不用在index前加unique来删，直接就可以删了
alter table s1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)
ALTER TABLE s1 ADD FULLTEXT (`information`)
CREATE FULLTEXT INDEX information ON `s1` (`information`)
案例
DELIMITER $$ #声明存储过程的结束符号为$$
CREATE PROCEDURE auto_insert1()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE(i<3000000)DO
INSERT INTO s1 VALUES(i,CONCAT('egon',i),i,CONCAT('egon',i,'@oldboy'));
SET i=i+1;
END WHILE;
END$$ #$$结束
DELIMITER ; #重新声明分号为结束符号
CALL auto_insert1()
select * from s1 where id=333;
## 使用索引之后
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
