mysql 常用函数,存储过程,触发器等

数学函数

round(m,n) 四舍五入

rand() 随机函数

字符串函数

lower(),upper(),concat(),substr(),length(),
当给某列加上100时，如果有null值，则需要把null值先变为0才行，使用 ifnull函数
set comm = ifnull(comm,0) +100

日期函数

select SYSDATE();

select datediff(sysdate(),'1995-09-06') 返回天数

select date_add (sysdate(),interval 2 day);当前日期加2天 date_add 返回日期

last_day(sysdate()) 返回当月的年月日

多行函数

聚合函数

count(),avg() 不将null值计入

count(1) 表示统计行数,与count(*) 等价

分组函数
group by having

实操

修改列名和数据类型 alter table student change birthday birthday datetime;
跳过用户名密码登陆 [mysqld] skip-grant-tables
注意到NULL值是未知的，且占用空间，不走索引，DBA建议建表的时候最好设置字段是NOT NULL 来避免这种低效率的事情的发生。

4.自连接

查询员工及其上级姓名
select e.ename ,p.ename manager from emp e left join emp p on e.mgr = p.empno

查询超过所在(本)部门平均工资的员工信息

select * from emp e1 where e1.sal >(select AVG(sal) from emp e2 WHERE e1.deptno = e2.deptno);

以主句的结果作为子句的查询条件

select * from emp e1,(select deptno,avg(sal) avg from emp e2 group by deptno) e2 where e1.deptno=e2.deptno and e1.sal >e2.avg

=any 相当于 in()

<any 小于最大值 >all 大于最大值

exists

or 不用，会造成索引失效

union 去重 union 不去重

事务

RN
RC 防止脏读
RR 防止脏读和不可重复读（mysql默认）
SERIALIZABLE 最高级别

select @@tx_isolation; ### 查看事务隔离级别

日志

redo log其实保障的是事务的持久性和一致性，而undo log则保障了事务的原子性，锁机制保证事务的隔离性，innodb的行锁是基于索引列，如果where 后边的列没有加索引则会表锁。

存储过程

delimiter //
create procedure

begin

end;//

存储函数

区别:

存储函数必须有返回值，存储过程不必有返回值
存储函数只有入参，在sql中执行，不必调用；存储过程是单独使用，并需要
调用。

触发器

基于事件触发

修改emp表会触发备份表操作

根据员工编号修改对应的员工姓名

delimiter //
create trigger changeTrigger 
after UPDATE
on emp for each row 
BEGIN
 insert into emp_bak (ename,empno) values(new.ename,old.empno);
END;

update emp set ename='卓叔' where empno =7369

触发器的类型：

插入型使用before触发
删除型使用after触发

after型触发器失败时，sql会回滚

视图

create or replace view

as ... with check option;

推荐使用 check option, 可以保证基表数据不会更改。

使用视图的优点：安全，高效，可以定制数据

索引

btree索引和hash索引的区别

等值查询hash效率高;当是范围查询或排序时，btree索引效率高

当出现大量重复数据时，会有hash冲突，此时hash索引效率低

索引失效：索引列使用or, 函数，null, != 等会出现索引失效。