完整复习(包含语法)--MYSQL正则表达式

正则表达式完整复习(包含语法)

– 正则表达式数据为前一章MyBingSchool库内的
use MyBingSchool;
– 学生信息表
SELECT * FROM Student;
– 学科表
select * from Cuosre;
– 分数表
select * from Sorce;
– 教师表
select * from Teacher;

– 正则表达式 操作案例

– ^ 匹配输入字符串的开始位置 以1开始的数据

select s_brith as '出生年月' from student WHERE s_brith REGEXP '^1'  ORDER BY s_brith ;

– ^ 匹配输入字符串的结束位置 以9开始的数据

-- select s_sroce as '分数' from Sorce WHERE s_sroce REGEXP '^9'  ORDER BY s_sroce ;

select s_sroce as '分数' from Sorce WHERE s_sroce REGEXP '9$'  ORDER BY s_sroce ;

select s_name as  '姓名' from student WHERE s_name REGEXP '四$'  ORDER BY s_name;

select s_name as  '姓名' from student WHERE s_name REGEXP '^[张]|二&'  ORDER BY s_name;

– .匹配任意字符 .9这里匹配任意包含9的数据

select s_brith as '出生年月' from student WHERE s_brith REGEXP '.9'  ORDER BY s_brith;

– .4这里匹配任意包含4的数据

select s_brith as '出生年月' from student WHERE s_brith REGEXP '.4'  ORDER BY s_brith;

– |与‘or’类似 或者 匹配所以

select s_name as  '姓名' from student WHERE s_name REGEXP '张三|王二|李四'  ORDER BY s_name;

– 查找表中带有199 1996 1998的数据 ‘’=LIKE 具有模糊查询条件作用

select s_brith as '出生年月' from student WHERE s_brith REGEXP '199'  ORDER BY s_brith ;

select s_brith as '出生年月' from student WHERE s_brith REGEXP '1996'  ORDER BY s_brith ;

select s_brith as '出生年月' from student WHERE s_brith REGEXP '1998'  ORDER BY s_brith ;

– 组合[]匹配条件 |或者

select s_brith as '出生年月' from student WHERE s_brith REGEXP '199[6|8]'  ORDER BY s_brith ;

– * 匹配前面的子表达式零次或多次 * 等价于{0,}

select s_brith as '出生年月' from student WHERE s_brith REGEXP '1*'  ORDER BY s_brith ;

– 匹配前面的子表达式一次或多次 + 等价于 {1,}

select s_brith as '出生年月' from student WHERE s_brith REGEXP '1+'  ORDER BY s_brith ;

– 组合^ +使用

select s_sroce as '分数' from Sorce WHERE s_sroce REGEXP '^9+'  ORDER BY s_sroce ;

– {n} n 是一个非负整数。匹配确定的 n 次 9{2}

select s_brith as '出生年月' from student WHERE s_brith REGEXP '9{2}'  ORDER BY s_brith ;

– {n,m} m 和 n 均为非负整数 其中n <= m 最少匹配 n 次且最多匹配 m 次
– 插入数据方便演示{n,m}

INSERT INTO student VALUES (DEFAULT,'刘涛','女','1999-01-11');

– 没有设置自增不能使用default
– 根据名字修改插入数据

UPDATE student set s_id='06' WHERE s_name='刘涛';

INSERT INTO student VALUES ('07','王涛','女','1999-02-22');

INSERT INTO student VALUES ('08','萧萧','女','1888-08-18');

select s_brith as '出生年月' from student WHERE s_brith REGEXP '9{1,3}'  ORDER BY s_brith ;

select s_brith as '出生年月' from student WHERE s_brith REGEXP '9{2,3}'  ORDER BY s_brith ;