Mysql基础1

检索不同的行：

select vend_id
from products;

/*返回不同的值*/
select distinct vend_id
from products;

限制结果：limit

/*limit 5 指示sql返回不多于5行*/
select vend_id from products limit 5;

/*指定要检索的开始行和行数*/
select prod_name from products limit 5,5;

 检索排序：order by

/*从小到大*/
select prod_name from products  order by prod_name;

/*按多个列排序*/
select prod_id,prod_price from products
order by prod_id,prod_price;

/*指定排序方向，DESC降序排序，ASC为升序默认*/
select prod_id,prod_price
from products
order by prod_id DESC,prod_price;/*指定id降序，price升序*/

/*order by 和 limit一起使用，可得到最大，最小值*/
select prod_price
from products
order by prod_price DESC
limit 1;
/*from ,order by,limit的顺序不能改变*/

 where子句：

/*范围值between*/
select prod_name,prod_price
from products
where prod_price between 5 and 10;
/*空值检测*/
select cust_id
from customers
where cust_email is null;

通配符的应用：

 

/*通配符%  可以匹配多个字符*/
select prod_id,prod_name
from products
where prod_name like 'jet%';/*区分大小写*/

where prod_name like '%';/*不能匹配null*/

/*通配符_  可以匹配单个字符*/
select prod_id,prod_name
from products
where prod_name like '_ ton anvil';

 

使用MySql正则表达式：

/*基本字符匹配*/
select prod_name
from products
where prod_name regexp '.000' /*.表示任意一个字符*/
order by prod_name;

/*这个不会返回任何值，因为没有通配符，匹配到也无返回结果*/
select prod_name
from products
where prod_name like '1000'
order by prod_name;

/*会返回结果*/
select prod_name
from products
where prod_name like '1000'
order by prod_name;

/*进行OR匹配*/
select prod_name
from products
where prod_name regexp '1000|2000|3000';

/*匹配几个字符之一*/
select prod_name
from products
where prod_name regexp '[123] ton';

where prod_name regexp '[^123] ton';

 

/*匹配范围*/
select prod_name
from products
where prod_name regexp '[1-5][a-z] ton'
order by prod_name;

/*匹配特殊符号*/
select prod_name
from venders
where prod_name regexp '\\.'  /*匹配特殊符号要用\\做前导*/
order by vend_name;

\\f  换页
\\n 换行
\\r 回车
\\t 制表
\\v 纵向制表
/*两个反斜杠一个Mysql自己解释用，另一个正则表达式库解释用*/