1.distinct
distinct A,B 用于取出不同的组合记录。
取出不同的雇员
select distinct employee from test_ljb;
取出雇员和薪水的不同组合。
select distinct t.employee, t.salary from test_ljb t;
2.between a and b [a,b]
等价于数学中的[a,b],闭区间,包括边界。
取出薪水在1000-1500的薪水。
select t.salary from test_ljb t where 1=1 and t.salary between 1000 and 1500;
等价于
select t.salary from test_ljb t where 1=1 and t.salary >=1000 and t.salary <=1500;
3.in(’ ‘,’ ‘)
选取处于in表达式里的数据。
select t.employee, t.salary from test_ljb t where t.salary in (1000,1500,2000);
4.is(is not)
select t.* from test_ljb t where t.bonus is null;
select t.* from test_ljb t where t.bonus is not null;
5.like及通配符、转移字符
%
:任意多个任意字符 (>=0)
_
:一个任意字符 (= 1)
选取名字中包含a的雇员
select t.employee from test_ljb t where t.employee like '%a%';
选取第二个字母为a的雇员
select t.employee from test_ljb t where t.employee like '_a%';
选取姓名中包含’%’的雇员
select t.employee from test_ljb t where t.employee like '%\%%' escape '\';
escape用于指定转义字符,当然我们可以指定其他的转移字符。如:
select t.employee from test_ljb t where t.employee like '%*%%' escape '*';