mysql小知识

mysql中使用小技巧

日期格式化相关函数

1.FROM_UNIXTIME(starttime,'%Y-%m-%d')  查询的时候把字段(时间戳)根据格式转换。
2.date_format(now(),'%Y-%m-%d')                 当前日期按照格式来转换
3.使用日期来比较大小的时候一定要添加 ‘’，select * from table where date='2018-12-14'

变量说明

系统变量

1.查看所有的系统变量 show variables;
2.查看系统变量具体的值: select @@autocommit
3.设置变量
3.1 临时修改(当前回话) set name=value
3.2 全局修改 set global name=value ,只对新客户端生效，当前回话虽然修改了，要重启之后才可以生效。
3.3 如果想要修改了本次生效不能使用global方式。

会话变量(用户自定义)

1.赋值 set @name :=value
2.select field1,field2 from table into field3 ,field4 只看结果
3.select @name1 :=field1 from table;查看赋值过程

局部变量

1.声明方式 :declare name=value ,存在Begin 和End之间。

流程结构

基本用法

1.在select条件中

select * ,if(age>10,'为真','为假') as judge from table;  用于调试比较方便

2.用在复杂的函数(存储过程,触发器)

函数

1.定义说明:
mysql一旦见到语句结束符分号，就会自动开始执行，导致我们如果在end后面直接使用;自动执行所以要先替换之前的语句结束符。这里使用 //替换。

Create function 函数名(形参) returns 返回值类型
Begin
	//函数体
	Return 返回值数据;	//数据必须与结构中定义的返回值类型一致
End

举例  
delimiter //
create function addnum(a int,b int) returns int
begin
return a+b;
end//
delimiter ;

查看函数:
show function status like 'addnum%'; //查看函数相关信息
show create function 函数名字; //查看创建语句
调用:
select addnum(1,2);

2.注意
自定义函数是属于用户级别的：只有当前客户端对应的数据库中可以使用
可以在不同的数据库下看到对应的函数，但是不可以调用
函数因为必须规范返回值：那么在函数内部不能使用select指令：
select一旦执行就会得到一个结果（result set）：select 字段 into @变量;（唯一可用）

存储过程

简介:

存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中，经过第一次编译后再次调用不需要再次编译（效率比较高），用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象（针对SQL编程而言）。

对比函数

1、标识符不同。函数的标识符为FUNCTION，过程为：PROCEDURE。
2、函数中有返回值，且必须返回，而过程没有返回值。
3、过程无返回值类型，不能将结果直接赋值给变量；函数有返回值类型，调用时，除在select中，必须将返回值赋给变量。
4、函数可以在select语句中直接使用，而过程不能：函数是使用select调用，过程不是。

创建存储过程

delimiter //
CREATE PROCEDURE myproce()
BEGIN
业务逻辑
END//
delimiter ;

调用

   call myproce();

常见场景

表结构如下

实现:筛选出单个学生的最高成绩和最低成绩

\------------sql中自带的函数greatest();
select name,GREATEST(yw,sx,wy) m from gerder;
\-------------自己实现的
 select `name` ,max(m)  from (  
    select name, yw as m from gerder 
         union all   
      select name,sx as m from gerder  
          union all   
     select name,wy as m from gerder  
  ) u group by name;

实现:把一个表中关联了出现两次的字段根据字段展示出来

课程转移记录表

课程表

说明:图1中的id都是课程id。

 SELECT
   c.course_name,cc.course_name
 FROM
 	ad_change_class_record r,
 	ad_flow f,
 	ad_course c,
	ad_course cc
 WHERE
 	f.id = r.flow_id
 and
   c.id=r.new_course
 and
  cc.id=r.old_course

其实这种只需要把课程表关联两次就可以了,得到如下结果:

糟糕的子查询

EXPLAIN select * from rms_student_sell where id in (1,2,3)

EXPLAIN select * from rms_student_sell a where a.id in (select id from rms_student_sell b where b.id in (1,2,3))

mysql不会将select id from b where b.id in (1,2,3)这个话的内容查询出来，拼接成1,2,3这样的字符串放到in里。
如果用explain分析，看到一个DEPENDENT SUBQUERY的字眼。
mysql会将外层的结果先查询出来，然后逐条执行子查询，如果外层结果集很大，这个就会很慢