mysql

select * from bbs where artical like '[ALM]%';以ALM开头

[charlist][!charlist] ;

 

select * from bbs where artical in(book,image);

in 允许选多个

 

 select * from bbs where artical between 'book' and 'image';

在book 和image之间的所有行，mysql包括book和image;

 

select title as t from bbs;

将t作为title的别名;

 

select * from bbs,party where bbs,b_username=party,p_username;

多表查询;

 

select * from bbs inner join party on b_username=party.p_username;

内联

(inner) join:至少一个匹配，返回行。

left join:右边不匹配，返回左行。

right join:左边不匹配，返回右行。

full join: 只要有一个匹配，返回行。mysql不支持；

 

union 和 union all 两个表必须有相同的列，相同的数据类型，列的顺序也相同；union all 会列出所有的值，union只会列出不同的值

select name from E_China union select name from E_America;

 

 having 用来弥补where 的不足，where不能和合计函数一起用，例如group by

select customer sum(money) from order group by customer having sum(money)>2;

 

ucase()变为大写 lcase()变小写

select * from ucase(lastname) from name;

 

mid()从文本中提取字符

select mid(week,3)  from date;

 

len()函数返回文本字段的程度；

slect len(content) from message;

 

round()把数值字段舍入为制定位数的小数；

select round(price,0) from book；

 

format制定的字段进行格式化

select format(now(),'YYYY-MM-DD') as date from message;