原列名 as 新列名
select first_name as name, salary as sal from employees;
查询 first_name表示为name,salary表示为sal
1.5多列合并
--将first_name和last_name合并展示select first_name||last_name from employees;select employee_id,first_name||last_name as name from employees;
1.6 结果去重
selectdistinct manager_id from employees;
查询 去重 manager_id 来自 employees表;
2,条件查询 关键字where
2.1比较查询 >,<,>=,<=,=
select*from employees where salary>10000;
查询 所有 来自 employees 条件 salary>10000;
2.2 逻辑运算 and,or,not
--查询工资为11000和24000的员工所有信息select*from employees where salary=11000or salary=24000;--查询last_name为King并且工资为24000的所有信息(sql语言不区分大小写,但是数据区分)select*from employees where last_name='King'and salary=24000;--查询工资不是11000的员工select*from employees where salary!=11000;select*from employees wherenot salary=11000;--不建议使用 !=
2.3 特殊谓词
2.3.1 in / not in
--查询工资为11000和24000的员工select*from employees where salary in(11000,24000);--查询工资不是11000和24000的员工select*from employees where salary notin(11000,24000);
2.3.2 between…and… 相当于>=和<= 闭区间
--查询工资在11000和24000之间所有的员工(闭区间)select*from employees where salary>=11000and salary<=24000;select*from employees where salary between11000and24000;
2.3.3 like 模糊查询
--'%' 表示不定长度通配,'_'表示一个字符的通配--查询first_name包含L的员工select*from employees where first_name like'%L%';--查询last_name的长度大于4的员工select*from emloyees where last_name like'____%'--上面的语句中'____'是4个'_'加上'%'所以会找到所以大于4的last_name
2.3.4 is null / is not null
--查询commission_pct为空值的所有信息select*from employees where commission_pct isnull;--查询manager_id不为空值的所有信息select*from employees where manager_id isnotnull;
2.3.5 条件控制
--将工资大于20000的分级为A,15000以上的分级为B,其余为Cselect employee_id,first_name,salary,casewhen salary>=20000then'A'when salary>=15000then'B'else'C'endas lvl
from employees;