#SQL
#连接查询 多表查询
SELECT
NAME,
`boyName`
FROM
`beauty`,
`boys`
WHERE `beauty`.`boyfriend_id` = `boys`.`id` ;
#等值连接
#查询员工名和对应的部门名
SELECT `last_name`,`department_name`
FROM `employees`,`departments`
WHERE `employees`.`department_id` = `departments`.`department_id`;
#查询员工名,工种号,工种名,利用别名让语句简洁
SELECT `last_name`,e.`job_id`,`job_title`
FROM `employees` AS e,`jobs` AS j
WHERE e.`job_id` = j.`job_id`;
#加入筛选
#有奖金的员工名,部门名
SELECT
`last_name`,
`department_name`,
`commission_pct`
FROM
`employees` AS e,
`departments` AS d
WHERE e.`department_id` = d.`department_id`
AND e.`commission_pct` IS NOT NULL ;
#查询城市名中第二个字符为o的部门名和城市名
SELECT `department_name`,`city`
FROM `departments` AS d ,`locations` AS l
WHERE d.`location_id` = l.`location_id` AND `city` LIKE '_o%';
#查询每个城市的部门个数
SELECT COUNT(*),`city`
FROM `departments` AS d,`locations` AS l
WHERE d.`location_id` = l.`location_id`
GROUP BY `city`;
#查询有奖金的每个部门的部门名和部门领导编号和该部门的最低工资
SELECT d.`manager_id`,`department_name`,MIN(`salary`)
FROM `employees` AS e,`departments` AS d
WHERE e.`department_id` = d.`department_id`
AND `commission_pct` IS NOT NULL
GROUP BY `department_name`,d.`manager_id`;
#添加排序
#查询每个工种的工种名和员工个数,并按员工个数降序
SELECT `job_title`,COUNT(*) 员工个数
FROM `employees` AS e,`jobs` AS j
WHERE e.`job_id` = j.`job_id`
GROUP BY `job_title`
ORDER BY COUNT(*) DESC;
#三表连接
#查询员工名,部门名和所在城市
SELECT `last_name`,`department_name`,`city`
FROM `employees` e,`departments` d ,`locations` l
WHERE e.`department_id` = d.`department_id`
AND d.`location_id` = l.`location_id`;
#非等值连接
#查询员工的工资和工资级别
SELECT `salary`,`grade_level`
FROM `employees` e,`job_grades` j
WHERE `salary` BETWEEN `lowest_sal` AND `highest_sal`
ORDER BY `salary` DESC;
#自连接,自己连接自己
#查询员工名和上级的名字
SELECT e.`employee_id`,e.`last_name`,m.`employee_id`,m.`last_name`
FROM `employees` e, `employees` m
WHERE e.`manager_id` = m.`department_id`;