mysql查询----修饰查询输出，like+通配符，regexp+正则

修饰查询输出:distinct, limit, order by,desc

select distinct id,age from students;	//限制重复
select distinct id,age from students limit 5; //限制行数
select distinct id,age from students limit 1,5;	// 选择固定的几行  start,len

select * from students order by id;
select * from students order by id,name desc limit 1;	//降序	desc只管前面的一个属性

where + 运算符/and/or/in/not in/exists/not exists

select * from students where id = 1;
select * from students where id <> 1;
select * from students where id != 1;
select * from students where id <= 1;
select * from students where id >= 1;
select * from students where id > 1;
select * from students where id < 1;
select * from students where id between 5 and 10;  // [5,10]
select * from students where remarks is null;	//!= <>  默认从不为null的行中选择

//and 与 or 与 in 与 not
select * from students where id > 1 and id < 5;
select * from students where id > 1 or name = 'aaa';
select * from students where id > 1 and id < 5 or name = 'aaa'; //and优先级高于or 可以用()明确优先级

select * from students where id in(1,3,4,5);
select * from students where id not in(1,3,4,5);

select * from students where id not > 1;

not 可以修饰 in exists
not in
not exists
EXISTS用于检查子查询是否至少会返回一行数据，
exists(where ------)该子查询实际上并不返回任何数据，而是返回值True或False

like+通配符 (需要全等)

select * from students where name like '%aa';	//长=n任意字符  	m ma mm 
select * from students where name like '_aa';	//长=1任意字符  ' ' 'm'

select * from students where name like '_aaa';	// name = aaa 无法匹配

regexp+正则（出现即可）

binary . | 匹配单个字符 匹配多个字符 定位

select * from students where name regexp 'aaa';
select * from students where name regexp '.aaa';
select * from students where name regexp binary 'aaa';	//区分大小
select * from students where name regexp 'aaa|bbb';	//或

匹配单个字符
[]
select * from students where name regexp '[1|2|3]aaa';	//'23aaa'可以匹配到
select * from students where name regexp '[123]aaa';	//上一行的简化版，所以[]只能选一个字符
select * from students where name regexp '[^123]aaa';	//一个^ 修饰括号
select * from students where name regexp '[1-z]aaa';	
select * from students where name regexp '[1-36-7]aaa';	//即[12367]
//因为[]都是单个字符，按照字符表顺序可以用[1-z]表示[1,z]内的所有单个元素。

特殊字符
\\f \\n \\r \\t \\v \\\ \\( \\)

预定义字符集  
格式类似  [:alnum:]
alnum alpha blank cntrl digit graph lower print punct space upper xdigit

匹配多个字符：都是右结合  表示满足左边条件的字符出现的个数（不要求相同） 一般在mysql中m不能超过255
* [0,+00]
+	[1,+00]
? [0,1]
{n} [n,n]
{n,} [n,+00]
{n,m} [n,m]

select * from students where name regexp '[:alumn:]{3}';
select * from students where name regexp '[[:alumn:]]{3}';	//效果同上
[:alumn:]的结果是单个字符，[单个字符]还是单个字符。
[[:digit:][:lower:]]   ===  [0-9a-z]

定位
^ 定位头   右结合
$  定位尾
[[:<:]] 词头
[[:>:]]  词尾