正则函数理解与运行

创建表

DROP table `r`

 CREATE TABLE `R` (
 `NOO` INT NOT NULL,
 `NAMEO` varchar(10),
 `SEX` VARCHAR(10) ,
 `AGE` INT(10) ,
 `CLASS` VARCHAR(10),
 PRIMARY KEY (`NOO`));

SHOW TABLE `R`;
	 
INSERT INTO `R` (`NOO`,`NAMEO`,`SEX`,`AGE`,`CLASS`)
VALUES(25,'李明','男',21,'95031'),
(30,'郑和',null,null,'95031')

replace into R (NOO,NAMEO,SEX,AGE,CLASS)
VALUES (4,'张三','男',28,'23456'), 
(5,'王五','女',29,'45678')
replace into R (NOO,NAMEO,SEX,AGE,CLASS)
VALUES (7,'deeed','男',28,'23456')

 

 

1.REGEXP

SELECT * FROM r WHERE NAMEO REGEXP  '^张';

 SELECT * FROM test.r WHERE NAMEO REGEXP 'ee';

SELECT * FROM test.r WHERE NAMEO REGEXP '^.....$'; 

 

 SELECT * FROM test.r WHERE NAMEO REGEXP '和$';

 2.REPLACE

UPDATE r SET NAMEO=REPLACE(NAMEO,'王五','王小梦')

 SELECT REPLACE('王三','三','小虎')

 UPDATE r SET CLASS=REPLACE(CLASS,'95031','12345')

DELETE FROM `R` WHERE `NAMEO`LIKE'李%' 

 UPDATE `R` SET `CLASS`='95091' WHERE `CLASS`='12345'

 DELETE from `R` where `noO`=4

 

 

 UPDATE `R` SET `CLASS`='95092' WHERE `CLASS`='95091'

update `R` SET nameO='王华' where nameO='deeed' 

 ALTER table r DROP CLASS