1.where字句,可以用and连接多个筛选条件,紧随from字句
SELECT last_name, job_id, employee_id
FROM employees
WHERE employee_id = 111 ;
2.字符和日期要包含在单引号中,字符大小写敏感,日期格式敏感,默认的日期格式是 DD-MON月-YY,也可以写成这样DD-MON月-YYYY,如以下例子
SELECT * FROM employees
WHERE last_name = 'Ernst';
SELECT last_name, hire_date, department_id FROM employees
WHERE hire_date = '6-2月-2000';
3.比较运算(>、<、=、<=、>=、<>),其中<>也可以写为!=,如以下例子
SELECT last_name, salary FROM employees
WHERE salary > 5000 AND salary < 8000 and salary !=7700;
4.其他比较运算,包含有between、in、like、escape、null
between关键字,查询某一区间内的数据
SELECT last_name, salary FROM employees
WHERE salary BETWEEN 5000 AND 8000;
in关键字,显示列表中的值
SELECT last_name, salary, manager_id FROM employees
WHERE manager_id IN (108, 147);
like关键字,查询类似的值,’_’代表一个字符,’%’代表零个字符或多个字符(任意个字符),例子:查询名字第三个字母为e的员工
SELECT last_name ROM employees
WHERE last_name LIKE '__e%';
escape(转义字符),回避特殊符号
SELECT job_id FROM employees
WHERE job_id LIKE ‘SH\_%‘ escape ‘\‘;
null关键字,使用is (not) null判断值是否为空
SELECT last_name, manager_id FROM employees
WHERE department_id IS NULL;
5.逻辑运算,包含有and、or、not
and关键字,把关系并在一起,而且关系要为真
SELECT employee_id, last_name, job_id, salary FROM employees
WHERE manager_id >=130 AND salary<8000;
or关键字,要求或关系为真,只要满足其中一个条件即可
SELECT employee_id, last_name, job_id, salary FROM employees
WHERE manager_id >=130 or salary<8000;
not关键字,逻辑关系为否,表示不在或不等
SELECT last_name, manager_id FROM employees
WHERE manager _id NOT IN (108,201);
6.order by字句,对查询出来的数据进行排序
升序asc(ascend),可以不写该关键字,因为order by默认为升序
SELECT last_name, job_id, department_id, hire_date FROM employees
ORDER BY hire_date ;
降序desc(descend),一般用简写
SELECT last_name, job_id, department_id, hire_date FROM employees
ORDER BY hire_date DESC ;
按别名进行排序
SELECT employee_id, last_name,hire_date as dates FROM employees ORDER BY dates;
多个列排序
SELECT last_name, manager_id, salary FROM employees
ORDER BY manager_id, salary DESC;
该查询语句首先通过manager_id进行升序排序,如果manager_id一样,再根据salary进行降序排序