MySQL 数据库管理系统(五）

一、使用数据处理函数
1.正则表达式_基本字符匹配

    select student_id,student_name,student_sex ,student_age from students where student_age regexp '.9' order by student_name;

2.正则表达式_OR匹配

     select student_id,student_name,student_sex ,student_age from students where   student_age regexp '19|21';

（使用|，表示搜索两个串之一）
3.正则表达式_OR匹配多个字符

    select student_id,student_name,student_sex ,student_age from students where   student_age regexp '[12]9'

‘[12]9’ 是[1|2]9的缩写，寻找年龄为19或者29的学生
4.正则表达式_匹配范围

      select student_id,student_name,student_sex ,student_age from students where   student_age regexp '[1-4]9'

[1-4]9=[1234]9
5.正则表达式_匹配特殊字符
" \-“表示匹配“-”,“\.”表示匹配”.",\f:表示换页，\n表示换行\
r表示回车\t表示制表\v表示纵向制表
二、创建计算字段
Concat（）函数 拼接两个列

       select Concat (student_id,'(',student_name,')') FROM students ORDER BY student_name;

Trim()：去掉串左右两边的空格
RTrim()去掉串右边的空格，
LTrim() 去掉串左边的空格
Soundex()匹配所有发音类似的列
执行算数计算

    select student_id,student_name,student_sex ,student_age,  student_id*student_age as expanded_age from students where   student_age =19;

三、组合聚集函数

     select count(*) as num_age,min(student_age) as age_min,max(student_age) as age_max,avg(student_age) as age_avg from students;

四，创建分组
1.group by

      select student_age ,count(*) as num_age from students group by student_age;

2.having 过滤分组

    select student_age ,count(*) as num_age from students group by student_age having student_age>=20;

总结：SELECT子句顺序
select --from–where–group by–having–order by --limit