聚合函数
create database MyTestdb;
use MyTestdb;
create table emp(
emp_id int primary key auto_increment comment '编号',
emp_name char(20) not null default '' comment '姓名',
salary decimal(10,2) not null default 0 comment '工资',
department char(20) not null default '' comment '部门'
);
insert into emp(emp_name,salary,department)
values('张晶晶',5000,'财务部'),('王飞飞',5800,'财务部'),('赵刚',6200,'财务部'),('刘小贝',5700,'人事部'),
('王大鹏',6700,'人事部'),('张小斐',5200,'人事部'),('刘云云',7500,'销售部'),('刘云鹏',7200,'销售部'),
('刘云鹏',7800,'销售部');
select group_concat(emp_name) from emp;
select group_concat(emp_name separator ';') from emp;
select department,group_concat(emp_name separator ';') from emp group by department;
select department,group_concat(emp_name order by salary desc separator ';') from emp group by department;
数学函数
select abs(-10);
select abs(表达式或字段) from 表;
select ceil(1.1);
select ceil(1.0);
select floor(1.1);
select floor(1.9);
select greatest(1,2,3);
select least(1,2,3);
select mod(5,2);
select pow(2,3);
select rand();
select floor(rand()*100);
select round(3.5415);
select round(3.5415,3);
select truncate(3.1415,3);
字符串函数
select char_length('hello');
select char_length('你好吗');
select length('hello');
select length('你好吗');
select concat('hello','world');
select concat_ws('-','hello','world');
select field('aaa','aaa','bbb','ccc');
select field('bbb','aaa','bbb','ccc');
select ltrim(' aaa');
select rtrim('aaa ');
select trim(' aaa ');
select mid("helloworld",2,3);
select position('abc' in 'habcelloabcworld');
select replace('aaahelloaaaworld','aaa','bbb');
select reverse('hello');