--1、If函数:if-else的效果SELECTIF(10>5,'大','小');--案例:查询某些员工是否有奖金,有输出1,没有输出2SELECT last_name,commission_pct,IF(commission_pct ISNULL,'没奖金,呵呵','有奖金,哈哈')AS 备注 FROM employees
--2、Case函数 Switch效果SELECT salary 原始工资,department_id,CASE department_id
WHEN30THEN salary*1.1WHEN40THEN salary*1.2WHEN50THEN salary*1.3ELSE0ENDAS 新工资
FROM employees;--案例:查询工资>20000显示A,>15000显示B,>10000显示C,ELSE DSELECT salary,CASEWHEN salary>20000THEN'A'WHEN salary>15000THEN'B'WHEN salary>11000THEN'C'ELSE'D'ENDAS 工资级别
FROM employees
6、多表连接
/*-----------------------------------------------连接查询-----------------------------------*/--1、等值连接--查询员工名和他所对应的部门名SELECT CONCAT(last_name,'_',first_name) 姓名,department_name 部门名
FROM employees,departments
WHERE employees.department_id = departments.department_id
--2、非等值连接SELECT salary 工资,grade_level 等级
FROM employees,job_grades
WHERE employees.salary BETWEEN job_grades.lowest_sal AND job_grades.highest_sal
ORDERBY grade_level
AND job_grades.grade_level='A'--3、自连接,把一张表当成两张表看(一般用于三级分类,子分类)SELECT e.employee_id,e.first_name,m.employee_id,m.first_name
FROM employees e,employees m
WHERE e.employee_id = m.manager_id