#查询员工表中涉及到的所有的部门编号(去重)SELECTDISTINCT department_id FROM employees;#查询员工姓和名连接成一个字段,并显示为姓名SELECT
CONCAT(last_name, first_name)AS 姓名
FROM
employees;#查询多个值,一个为0,则全部为NULL,利用IFNULL函数来避免SELECT
IFNULL(commission_pct,0)AS 奖金率
FROM
employees;
条件查询
/*
语法:
select
查询列表
from
表名
where
筛选条件; (ture or false)
*/#查询工资>12000的员工信息SELECT*FROM
employees
WHERE
salary >12000;#查询部门编号不等于90号的员工名和部门编号SELECT
last_name,
department_id
FROM
employees
WHERE
department_id <>90;#查询部门编号不是在90到110之间,或者工资高于15000的员工信息SELECT*FROM
employees
WHERENOT(department_id >=90AND department_id <=110)OR salary >15000;
模糊查询
#查询员工名中包含字符a的员工信息SELECT*FROM
employees
WHERE
last_name LIKE'%a%';#查询员工名中第三个字符为n,第五个字符为l的员工名和工资SELECT*FROM
employees
WHERE
last_name LIKE'__n_l%';#查询员工名中第二个字符为_的员工名SELECT
last_name
FROM
employees
WHERE
last_name LIKE'_\_%';/*
这里的\是转义字符
或者 last_name LIKE '_$_%' ESCAPE'$';
*/#查询员工编号在100到120之间的员工信息SELECT*FROM
employees
WHERE
employee_id BETWEEN100AND120;/* 包含临界值,且必须左边小,右边大 */#查询员工的工种编号是IT_PROT、AD_VP、AD_PRES中的一个员工名和工种编号SELECT
last_name,
job_id
FROM
employees
WHERE
job_id IN('IT_PROT','AD_VP','AD_PRES');
基础查询#查询员工表中涉及到的所有的部门编号(去重)SELECT DISTINCT department_id FROM employees;#查询员工名和姓连接成一个字段,并显示为姓名SELECT CONCAT(last_name, first_name) AS 姓名FROM employees;#查询多个值,一个为0,则全部为NULL,利用IFNULL函数来避免SELECT IFNULL(commission_pct, 0) AS 奖金率FROM employ.