select birth_date 生日, gender 性别 from employees;
select first_name, last_name, gender from employees where gender = 'F';
select birth_date from employees order by birth_date desc;
select * from employees limit 10 offset 20;
select max(birth_date) from employees;
select count(gender), gender from employees group by gender;
select count(first_name) c, first_name from employees group by first_name order by c ;
select * from employees order by birth_date limit 10 offset 0 ;
DDS
基本查询
聚合查询
连接查询
内连接
SELECT
birth_date, first_name, last_name, dept_emp.dept_no, dept_name
FROM
employees
JOIN
dept_emp ON dept_emp.emp_no = employees.emp_no
JOIN
departments ON departments.dept_no = dept_emp.dept_no
WHERE
employees.emp_no = 10001;
左连接
SELECT
birth_date,
first_name,
last_name,
dept_emp.dept_no,
dept_name
FROM
employees
LEFT JOIN
dept_emp ON dept_emp.emp_no = employees.emp_no
LEFT JOIN
departments ON departments.dept_no = dept_emp.dept_no
WHERE
employees.emp_no in (10001, 10002, 10003);
SELECT
birth_date,
first_name,
last_name,
dept_emp.dept_no,
dept_name
FROM
employees FULL
JOIN
(dept_emp, departments) ON dept_emp.emp_no = employees.emp_no
AND departments.dept_no = dept_emp.dept_no
WHERE
employees.emp_no IN (10001 , 10002, 10003);
给出各部门历任领导的记录并附上部门名字
SELECT
e.first_name,
e.last_name,
d.dept_name
FROM
dept_manager dm
JOIN departments d ON dm.dept_no = d.dept_no
JOIN employees e ON dm.emp_no = e.emp_no;
*计算各个部门的平均工资并排序，给出平均工资、部门名称
SELECT
AVG( s.salary ) salary,
d.dept_name
FROM
departments d
JOIN dept_emp de ON d.dept_no = de.dept_no
JOIN salaries s ON s.emp_no = de.emp_no
GROUP BY
dept_name
ORDER BY
salary;
1.给出各部门历任领导的记录并附上部门名字：
A.
select es.*,ds.dept_name from employees es,dept_manager dr,departments ds where es.emp_no=dr.emp_no and dr.dept_no=ds.dept_no;
B.
select es.*,ds.dept_name from employees es inner join dept_manager dr on es.emp_no=dr.emp_no inner join departments ds on dr.dept_no=ds.dept_no;
2.计算各个部门的平均工资并排序，给出平均工资、部门名称:
A.
select avg(ss.salary)dept_salary_avg,ds.dept_name from salaries ss,dept_emp dp,departments ds where ss.emp_no= dp.emp_no and dp.dept_no=ds.dept_no group by ds.dept_no order by dept_salary_avg;
B.
select avg(ss.salary)dept_salary_avg,ds.dept_name from salaries ss join dept_emp dp on ss.emp_no=dp.emp_no join departments ds on dp.dept_no=ds.dept_no group by ds.dept_no order by dept_salary_avg desc;
计算各个部门的平均工资并排序，给出平均工资、部门名称
SELECT
avg( salary ),
dept_name
FROM
salaries
JOIN dept_emp ON salaries.emp_no = dept_emp.emp_no
JOIN departments ON dept_emp.dept_no = departments.dept_no
GROUP BY
dept_emp.dept_no
ORDER BY
avg( salary ) DESC;
±--------------±-------------------+
| avg( salary ) | dept_name |
±--------------±-------------------+
| 80667.6058 | Sales |
| 71913.2000 | Marketing |
| 70489.3649 | Finance |
| 59665.1817 | Research |
| 59605.4825 | Production |
| 59478.9012 | Development |
| 58770.3665 | Customer Service |
| 57251.2719 | Quality Management |
| 55574.8794 | Human Resources |
±--------------±-------------------+
9 rows in set (2.70 sec)
select m.emp_no,e.first_name,e.last_name,d.dept_name from dept_manager m LEFT JOIN departments d ON m.dept_no = d.dept_no LEFT JOIN employees e ON m.emp_no = e.emp_no;
2、计算各个部门的平均工资并排序，给出平均工资、部门名称
select d.dept_name "部门名称",AVG(s.salary) "平均工资" from salaries s LEFT JOIN dept_emp e ON s.emp_no = e.emp_no LEFT JOIN departments d ON e.dept_no = d.dept_no GROUP BY d.dept_name;
SELECT
dept_manager.dept_no,
dept_name,
employees.emp_no,
first_name,
last_name,
dept_manager.from_date,
dept_manager.to_date
FROM
departments
JOIN (dept_manager, employees) ON employees.emp_no = dept_manager.emp_no
AND departments.dept_no = dept_manager.dept_no
ORDER BY
dept_manager.dept_no;
计算各个部门的平均工资并排序，给出平均工资、部门名称
SELECT
dept_name,
AVG(salary)
FROM
salaries
JOIN (dept_emp,departments) ON dept_emp.emp_no = salaries.emp_no
AND departments.dept_no = dept_emp.dept_no
GROUP BY
dept_emp.dept_no
ORDER BY
AVG(salary) DESC;
SELECT
dept_manager.dept_no,
dept_name,
employees.emp_no,
first_name,
last_name,
dept_manager.from_date,
dept_manager.to_date
FROM departments
JOIN (dept_manager, employees) ON employees.emp_no = dept_manager.emp_no
AND departments.dept_no = dept_manager.dept_no
ORDER BY dept_manager.dept_no;
计算各个部门的平均工资并排序，给出平均工资、部门名称
SELECT AVG(salary), dept_name
FROM salaries
JOIN (dept_emp, departments) ON dept_emp.emp_no = salaries.emp_no
AND departments.dept_no = dept_emp.dept_no
GROUP BY dept_emp.dept_no
ORDER BY AVG(salary) DESC;
给出各部门历任领导的记录并附上部门名字
SELECT e.first_name,e.last_name ,d.dept_name from dept_manager as m
LEFT JOIN employees as e on m.emp_no = e.emp_no
LEFT JOIN departments as d on m.dept_no = d.dept_no
计算各个部门的平均工资并排序，给出平均工资、部门名称
SELECT AVG(s.salary) as salary ,de.dept_name FROM salaries as s
left JOIN dept_emp as d on s.emp_no = d.emp_no
LEFT JOIN departments as de on d.dept_no = de.dept_no GROUP BY de.dept_no ORDER BY salary
