MySql笔记

• select语句

-- 使用逗号和空格将last_name和job_id连接起来
SELECT CONCAT_WS(', ',last_name,job_id) JOb
FROM employees
-- CONCAT_WS(separator,str1,str2,...) 
-- 是CONCAT()的特殊形式。第一个参数是其它参数的分隔符。分隔符的位置放在要连接的两个字符串之间。分隔符可以
-- 是一个字符串，也可以是其它参数。

-- 显示在1998年2月20日和1998年5月1日之间聘用的
-- 员工姓名(last_name)，职务标识(job_id)和起始日期(hire_date)
-- 对起始日期升序
SELECT last_name, job_id, hire_date 
FROM employees 
WHERE hire_date 
BETWEEN '1998-2-20' AND '1998-5-1'
ORDER BY hire_date

-- 显示可以赚取佣金的所有员工的姓氏(last_name)，薪金(salary)和佣金(commission_pct)
-- 按薪金和佣金的降序对数据排序
SELECT last_name, salary, commission_pct
FROM employees
WHERE commission_pct IS NOT NULL
ORDER BY salary DESC, commission_pct DESC

-- 显示当前日期
 SELECT NOW()
 -- 或者
 SELECT SYSDATE()
 --
 SELECT YEAR(NOW()),MONTH(NOW()),DAY(NOW()),HOUR((NOW()))

• 查询字段为null用0替代

SELECT IFNULL(字段名,0) FROM 表名

• 实现Oracle的DECODE()函数

DECODE()的语法：DECODE(value,if1,then1,if2,then2,if3,then3,…,else)，表示如果value 等于if1时，DECODE函数的结果返回then1,…,如果不等于任何一个if值，则返回else。

MySql支持IF()语句 IF(expr1,expr2,expr3)
如果expr1是TRUE(expr1<>;0且expr1<>;NULL)，那么IF()返回expr2，否则它返回expr3。IF()返回一个数字或字符串值

-- 根据job_id列的值返回所有员工的级别
SELECT job_id,IF(job_id='ad_pres','A',
IF(job_id='st_man','B',
IF(job_id='it_prog','C',
IF(job_id='sa_rep','D',
IF(job_id='st_clerk','E','0'))))) Grade
FROM employees

• 用CASE完成上述功能

SELECT job_id,
CASE WHEN job_id='ad_pres' THEN 'A'
     WHEN job_id='st_man' THEN 'B'
     WHEN job_id='it_prog' THEN 'C'
     WHEN job_id='sa_rep' THEN 'D'
     WHEN job_id='st_clerk' THEN 'E'
     ELSE '0'
     END Grade
FROM employees

**

• 聚合函数

**

-- 显示每个职务(job_id)类型的最低，最高，总计和平均薪金(保留整数)
SELECT job_id,
       MAX(salary) 'Maximum',
       MIN(salary) 'Minmun',
       SUM(salary) 'Sum',
       ROUND(AVG(salary),0) 'Average'
FROM employees 
GROUP BY job_id       

-- 确定经理(manager_id)人数而不列出他们
SELECT COUNT(DISTINCT manager_id) 'Manager Count'
FROM employees

• 高级任务

-- 显示经理编号以及该经理所管员工的最低薪金，
-- 排除最低薪金不超过6000的所有组。
-- 按薪金降序对输出排序
SELECT manager_id, MIN(salary) 'salary'
FROM employees
GROUP BY manager_id
HAVING MIN(salary) <= 6000
ORDER BY 2 DESC

-- 显示每个部门名称(department_name)，地点(location_id)，员工人数及该部门所有员工的平均薪金
-- 平均薪金舍入到小数点后两位
SELECT d.department_name 'Name', d.location_id 'Location',
       COUNT(*) 'Number of people', ROUND(AVG(salary),2) 'Salary'
FROM departments d, employees e
WHERE e.department_id = d.department_id
GROUP BY d.department_name ,d.location_id

-- 显示员工总数以及其中在1995，1996，1997年的聘用员工数
SELECT COUNT(*) 'total',
       SUM(IF(YEAR(hire_date) = '1995',1,0)) '1995',
       SUM(IF(YEAR(hire_date) = '1996',1,0)) '1996',
       SUM(IF(YEAR(hire_date) = '1997',1,0)) '1997'
FROM employees

-- 矩阵查询 
-- 显示部门20,50,80的职务，该职务的总薪水
SELECT job_id,
       SUM(IF(department_id=20,salary,0)) 'dept_20',
       SUM(IF(department_id=50,salary,0)) 'dept_50',
       SUM(IF(department_id=80,salary,0)) 'dept_80'
FROM employees
WHERE department_id IN (20,50,80)
GROUP BY job_id

• 多表查询

-- 显示赚取佣金(ommission_pct)的所有员工的姓氏(last_name),部门名称(department_name),
-- 地点标识(location_id)和城市(city)
-- 跨越employees,departments,locations三张表
SELECT e.last_name, 
       d.department_name, d.location_id, 
       l.city
FROM employees e, departments d, locations l 
WHERE e.department_id = d.department_id
AND   d.location_id = l.location_id
AND   e.commission_pct IS NOT null

-- 显示员工姓氏(last_name)，员工编号(employee_id)，以及他们经理的姓氏(last_name)和经理编号
SELECT e.last_name "Employee", e.employee_id "Emp#",
       m.last_name "Manager", m .manager_id 'Mgr#'
 FROM employees e, employees m
 WHERE e.manager_id = m.employee_id

-- 显示在员工Davies之后招聘的所有员工的姓氏和聘用日期

 SELECT e.last_name, e.hire_date
  FROM employees e, employees d
  WHERE d.last_name = 'Davies'
  AND e.hire_date > d.hire_date

-- 显示在其经理之前聘用的所有的员工的姓氏和聘用日期
-- 以及经理的姓氏和聘用日期
SELECT e.last_name "Employee" , e.hire_date "Emp_hired#",
       m.last_name "Manager", m.hire_date "Mgr_hired#"
FROM employees e, employees m
WHERE m.employee_id = e.manager_id
AND  e.hire_date < m.hire_date

• 子查询

-- 显示和Zlokety在同一个部门的所有员工的姓氏和聘用日期
--结果集中不包括Zlotykey
SELECT last_name, hire_date
FROM employees
WHERE department_id = (
  SELECT department_id
  FROM employees
  WHERE last_name = 'Zlotkey')
AND last_name <> 'Zlotkey'

-- 显示薪金超过平均薪金的所有员工的员工编号和姓氏
 SELECT employee_id, last_name
 FROM employees
 WHERE salary > (SELECT AVG(salary) FROM employees)

 -- 显示行政部门(Executive)中每位员工的部门编号，姓氏和职务标识
 SELECT department_id, last_name, job_id
 FROM employees
 WHERE department_id IN
       (SELECT department_id 
       FROM departments
       WHERE department_name = 'Executive')

-- 显示所有员工的标号，姓氏和薪金
-- 条件：他们的薪金超过平均薪金并且他所工作的部门里有员工的姓氏中包含'u'
SELECT employee_id, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
AND department_id IN 
    (SELECT department_id FROM employees WHERE last_name LIKE '%u%')