1.字符函数
案例:
select concat('imooc',',','MySQL');
create table test(
first_name varchar(20) default null,
last_name varchar(20) default null
);
select concat(first_name,last_name) as fullname from test;
select concat_ws('--','A','B');
select format(12560.7534,2);
select left('MySQL',2);
select replace('??My??SQL???','?','');
select substring('MySQL',1,3);
select 'MySQL' like 'M%';
select * from test where first_name like '%1%%' escape '1';(1后面的%不需要去匹配)
2.数值运算符与函数
select 14 between 1 and 22;
select * from test where first_name is null;
3.日期时间函数
select date_add('2014-3-12',interval 365 day);
select date_add('2014-3-12',interval 1 year);
select datediff('2013-3-12','2014-3-12');
select date_format('2013-3-12','%m/%d/%Y');
4.信息函数
alter table test add id smallint unsigned key auto_increment first;
insert test(first_name,last_name) values ('11','22');
select last_insert_id();
5.聚合函数
select avg(id) from test;
select avg(goods_price) as avg_price from tdb_goods;
6.加密函数
7.自定义函数
创建不带参数的自定义函数:
create function f1() returns varchar(30)
return date_format(now(),'%Y/%m/%d, %H:%i:%s');
select f1();
创建带参数的自定义函数:
create function f2(num1 smallint unsigned,num2 smallint unsigned)
returns float(10,2) unsigned
return (num1+num2)/2;
select f2(3,4);
创建具有复合结构函数的自定义函数
delimiter //
create function adduser(first_name varchar(20),last_name varchar(20))
returns int unsigned
begin
insert test(first_name,last_name) values (first_name,last_name);
return last_insert_id();
end
//
select adduser('zhu','heng');
delimiter ;
函数体如果为复合结构则使用begin…end语句