SQL之过滤排序语法

基本语法：

select .... from tab_name where conditions....;

SELECT * from tab_name where id = 10;

注意事项：

1）字符和日期要包含在单引号中，因为字符对大小写敏感，日期对格式敏感；

2）比较运算：>,>=,<,<=,=,<>,between..and..,in,not in,like,is null等

select * from tab_name where salary >= 1000;

select * from tab_name where salary between 1000 and 2000;

select * from tab_name where salary in(100,200,300);

select * from tab_name where name like 'j%';--------%代表0个或多个字符，_代表一个字符

select * from tab_name where name is null;

3）逻辑运算：and，or，not；

select * from tab_name where salary = 1000 and name = 'jack';---要求和条件为真。

select * from tab_name where salary = 1000 or name = 'jack';---要求或条件为真。

select * from tab_name where salary not in(1000,2000);

PS：运算优先级

优先级

      1  算术运算符

2  连接符

  3  比较符

  4  IS [NOT] NULL, LIKE, [NOT] IN

  5  [NOT] BETWEEN

  6  NOT

  7  AND

  8  OR

也可以用括号改变优先级。

select * from tab_name where salary = 1000 or salary = 2000 and name = 'jack';

等同于select * from tab_name where salary = 1000 or （salary = 2000 and name = 'jack'）;

可以用括号控制优先级：

select * from tab_name where （salary = 1000 or salary = 2000） and name = 'jack';

排序：order by

select salary from tab_name order by salary;

排序默认是升序排列，可以用ASC--升序排列，DESC--降序排列控制排序顺序。

order by 一定要放在sql语句的最后面。

也可按照别名或者多行排序

select　salary,name from tab_name order by salary,name desc;

select name v_name from tab_name order by v_name;

也可以使用不在select中的列进行排序

select name from tab_name order by salary desc;