mysql函数

一、函数及存储过程

1、函数

-- 直接返回查询语句的结果
delimiter $$
create function aa() returns int
begin 
	return (select 列标题 from author where 条件);
end $$
delimiter ;

select aa();

-- 通过变量进行返回
delimiter $$
create function a1() returns int
BEGIN
	declare num int;
	set num=(select age from author where a_name='小川');
	return num;
end $$
delimiter ;

select a1();

-- 删除函数
drop function a1;

-- 函数参数
delimiter $$
create function a2(str char(5)) returns int
BEGIN
	declare num int;
	set num=(select age from author where a_name=str);
	return num;
end $$
delimiter ;

select a2('阿宸');
select a2('老舍');

时间函数

时间数据 由 年月日 时分秒组成
年：year
月:  month
日:  day
时：hour
分：minute
秒：second
year()：获得日期时间的年份
month():获得日期时间的月份
day():获得日期时间的天数
hour():获得日期时间的小时 
minute():获得日期时间的分钟
second():获得日期时间的秒钟

日期：date 包含年月日
时间：time 包含时分秒
date(): 获得日期时间的 日期部分
time()：获得日期时间的 时间部分

now():获得当前时间(你计算机的时间)的年月日时分秒
	select now();
curdate(): 获得当前的日期
curtime(): 获得当前的时间

​

date_add(列名,INTERVAL 数量 单位)
addDate(列名, 天数):加指定的天数 天数可以是负数 如果是负数 则是减去指定的天数
	select stujointime, addDate(stujointime, 1) from studentinfo;
	interval
addDate(列名,INTERVAL 数量 单位):
	select stujointime, addDate(stujointime,interval 1 hour) from studentinfo;
	# 如果是存在多个单位的  数量应该要使用引号引起来 数值之间要隔开
	select stujointime, addDate(stujointime,interval "1 1" year_month) from studentinfo;
	# addDate(stujointime,interval "1 1" year_month)：添加一年零一个月
单位:
	YEAR，
	MONTH，
	DAY，
	HOUR，
	MINUTE，
	SECOND，
	WEEK，
	YEAR_MONTH，
	DAY_HOUR，
	DAY_MINUTE，
	DAY_SECOND，
	HOUR_MINUTE，
	HOUR_SECOND，
	MINUTE_SECOND
	week：周

dateDiff(时间1, 时间2): 求两个时间的差值 时间1是大的时间 时间2是小的时间 差值是天数
	 select datediff(now(), StuJoinTime) from studentinfo;
date_fomat(列名, 格式)
	select date_format(stujointime, '%b %d %Y %h:%i %p') from studentinfo;
	select DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p');
	格式：
	%a	缩写星期名
	%b	缩写月名
	%c	月，数值
	%D	带有英文前缀的月中的天
	%d	月的天，数值(00-31)
	%e	月的天，数值(0-31)
	%f	微秒
	%H	小时 (00-23)
	%h	小时 (01-12)
	%I	小时 (01-12)
	%i	分钟，数值(00-59)
	%j	年的天 (001-366)
	%k	小时 (0-23)
	%l	小时 (1-12)
	%M	月名
	%m	月，数值(00-12)
	%p	AM 或 PM
	%r	时间，12-小时（hh:mm:ss AM 或 PM）
	%S	秒(00-59)
	%s	秒(00-59)
	%T	时间, 24-小时 (hh:mm:ss)
	%U	周 (00-53) 星期日是一周的第一天
	%u	周 (00-53) 星期一是一周的第一天
	%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
	%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
	%W	星期名
	%w	周的天 （0=星期日, 6=星期六）
	%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
	%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
	%Y	年，4 位
	%y	年，2 位
	
	实例
	下面的脚本使用 DATE_FORMAT() 函数来显示不同的格式。我们使用 NOW() 来获得当前的日期/时间：
	
	DATE_FORMAT(NOW(),'%b %d %Y %h:%i %p')
	DATE_FORMAT(NOW(),'%m-%d-%Y')
	DATE_FORMAT(NOW(),'%d %b %y')
	DATE_FORMAT(NOW(),'%d %b %Y %T:%f')

dayofweek(列名)：查询当前时间是星期几  返回的是1-7的数字
	# 国外一周的第一天是星期天  最后一天是星期6
	如果是1 就是星期天 如果是7就是星期6
	select dayofweek(now());

# 时间可以比大小~~~  
	"2022-1-2" > "2022-1-1"

2、存储过程

存储过程是一个sql语句的集合

delimiter $$
create procedure b()
begin 
	select * from author where age<30;
	select a_name from author where age>30;
end $$
delimiter ;

call b();

-- in 是接受常量以及变量的
-- out 只能接受变量
-- 在sql中定义一个变量
set @变量名=值
select @变量名

delimiter $$
create procedure b()
begin 
	select * from author where age<30;
	select a_name from author where age>30;
end $$
delimiter ;

call b();


set @name='星奕';
select @name;

delimiter $$
create procedure c(in num int)
begin 
	select * from author where age<num;
	select a_name from author where age>num;
end $$
delimiter ;

-- 使用常量传参
call c(30);

-- 通过变量传参
set @number=90;
call c(@number);

二、触发器

在数据中设置一段静态的代码 ， 这个代码并不会自己执行也不行手动调用，是当某一个操作触发到这个设置的条件的时候，就会自动的执行。

delimiter $$
create trigger 触发器名称 触发时间 触发条件 on 表名 for each row
begin
	触发事件
end $$
delimiter ;

-- 触发时间（有两个）：before在触发条件之前 ；after 在触发条件之后
-- 触发条件 ：insert、 update、 delete
-- new 新数据  insert  update
-- old 旧数据  update  delete


-- 增加学生信息 ， 对应班级人数自动增加
delimiter $$
create trigger tri after insert on student for each row
begin
	-- 1、获的新增学生对应班级的人数
	declare num int;
	set num=(select class_num from class where class_id=new.class_id);
	-- 对应班级人数+1
	update class set class_num=num+1 where class_id=new.class_id;
end $$
delimiter ;


insert into student values(20,'星奕',2201,'男');
-- 下面会触发两次
insert into student values
(22,'小谦',2202,'男'),
(23,'小奕',2203,'男');


-- 修改学生班级，对应班级人数触发操作
delimiter $$
create trigger tri_update after update on student for each row
begin
	-- 1、获的学生对应班级的人数
	declare num_old int;
	declare num_new int;
	-- 获取到班级人数
	set num_new=(select class_num from class where class_id=new.class_id);
	set num_old=(select class_num from class where class_id=old.class_id);
	-- 对应班级人数+1/-1
	update class set class_num=num_new+1 where class_id=new.class_id;
	update class set class_num=num_old-1 where class_id=old.class_id;
end $$
delimiter ;

update student set class_id=2201 where name='小奕';

三、视图

视图：视图是一个虚拟表 ， 其内容由查询结果定义。

视图的结果与真实的表结构是一样的，视图最好只做查询操作。

视图可以简化高频，复杂的查询语句操作。

-- 创建视图
create view 视图名称 as select查询语句;

-- 删除视图
drop view 视图名称;
-- 如果视图不存在不会报错
drop view if exists 视图名称;

-- 查看数据库所有视图
show full tables in 数据库名称 where table_type like 'VIEW';

select name ,graden from student;

create view s_name as select name ,graden from student;
create view s_x as select id ,name ,class_id from student;

show full tables in class5 where table_type like 'VIEW';

select * from s_name;

四、索引

索引：数据库的性能调优 ； 提升数据库的工作效率

1、主键索引

2、普通索引

3、唯一索引

使用索引：索引可以在一定情况下加快查询的数据

当表的查询大于修改，删除的操作的，可以创建索引

当表查询操作很少，表的数据很少，不建议创建索引

-- 在创建表的时候创建索引
create table 表名(
	字段名 数据类型，
    字段名 数据类型，
    index 索引名(字段名)，
    unique 索引名(字段名)
);

-- 已有表创建普通索引
create index 索引名 on 表名(字段名);
-- 已有表创建唯一索引
create unique index 索引名 on 表名(字段名);

-- 删除索引
drop index 索引名 on 表名;

-- 查询索引
show keys from 表名;


create table t1(
	id int ,
	index i(id)
);
show keys from t1;

create table t2(
	num int,
	name char(5)
);

create unique index number on t2(num);

show keys from t2;

五、事务、存储引擎

1、事务

事务主要是处理数据的增删改查操作。确保数据在操作过程中是安全。

-- 开启事务
begin;

-- 提交事务
commit;

-- 回滚,回滚到上一条操作语句
rollback;

-- 开启事务
begin;

insert into student values(30, '武则天', 2201 ,'女');

-- 提交数据
commit;

update student set name='星奕' where id=20;

commit;
update student set name='xy' where id=20;
update student set name='星奕' where id=20;
rollback;

事务的好处：事务的四大特性：原子性、一致性、隔离性、持久性、简称为ACID

事务隔离级别

• 隔离级别

  • 读未提交：read uncommitted

    事物A和事物B，事物A未提交的数据，事物B可以读取到
    这里读取到的数据叫做“脏数据”
    这种隔离级别最低，这种级别一般是在理论上存在，数据库隔离级别一般都高于该级别

  • 读已提交：read committed

    事物A和事物B，事物A提交的数据，事物B才能读取到
    这种隔离级别高于读未提交
    换句话说，对方事物提交之后的数据，我当前事物才能读取到
    这种级别可以避免“脏数据”
    这种隔离级别会导致“不可重复读取”

  • 可重复读：repeatable read

    事务A和事务B，事务A提交之后的数据，事务B读取不到
    事务B是可重复读取数据
    这种隔离级别高于读已提交
    MySQL默认级别
    虽然可以达到可重复读取，但是会导致“幻像读”

  • 串行化：serializable

    事务A和事务B，事务A在操作数据库时，事务B只能排队等待
    这种隔离级别很少使用，吞吐量太低，用户体验差
    这种级别可以避免“幻像读”，每一次读取的都是数据库中真实存在数据，事务A与事务B串行，而不并发

2、存储引擎

就是MySQL是如何存储数据的

-- 查看存储引擎
show engines;

InnoDB -- 默认的存储引擎，比较平衡的读写效率都是可以的
MyISAM -- 注重查询，对于些的效率不好