Python自动化训练营2期——SQL复杂查询实战-课程贴

课后调查表

单表查询

基本查询语法

基本查询:SELECT * FROM 表名;
字段查询:SELECT 字段名 FROM 表名;
条件查询:SELECT * FROM 表名 WHERE 条件;
排序:SELECT * FROM 表名 ORDER BY 字段名 DESC;
分页:SELECT * FROM 表名 LIMIT 10 OFFSET 0;
去重:SELECT DISTINCT 字段名 FROM 表名;

limit

  • limit m offset n:m-每一页显示的条数,n-起始行的索引
  • limit n, m:n-起始行索引,m-每一页显示的条数
  • limit m:从0开始显示,显示m条数据

基本查询练习

– 查询部门表中的信息
SELECT
*
FROM
departments;
– 查询都有哪些部门
select dept_name from departments;
– 查询部门编号为d003的部门信息
select * from departments where dept_no=‘d003’;
– 查询员工的编号和入职日期,按照员工入职日期从晚到早排序
select emp_no, hire_date from employees order by hire_date DESC;
– 分页查看员工信息,每页展示10条
select * from employees limit 10 offset 0;
select * from employees limit 20, 10;
select * from employees limit 10;
– 查看公司中员工的姓氏
select distinct last_name from employees;

条件查询

– 查询出生日期晚于1965-01-01的员工编号、姓名和生日
SELECT
emp_no, first_name, last_name, birth_date
FROM
employees
WHERE
birth_date > ‘1965-01-01’;

– 查询名字中包含’fai’的员工的信息
select * from employees where first_name like ‘%fai%’;
– 查询名字中’fa’开头的名字长度为3位的员工信息
select * from employees where first_name like ‘fa_’;

– 查询年薪介于70000到70003之间的员工编号和年薪
SELECT
emp_no, salary
FROM
salaries
WHERE
salary BETWEEN 70000 AND 70003;
– 查询名字在Berni和Bezalel之间的员工编号和名字
SELECT
emp_no, first_name
FROM
employees
WHERE
first_name BETWEEN ‘Berni’ AND ‘Bezalel’;

– 查询入职日期为1995-01-27和1995-03-20日的员工信息
SELECT
*
FROM
employees
WHERE
hire_date IN (‘1995-01-27’ , ‘1995-03-20’);

– 查询名字为Lillian并且姓氏为Haddadi的员工信息
SELECT
*
FROM
employees
WHERE
first_name = ‘Lillian’
AND last_name = ‘Haddadi’;
– 查询名字为Lillian或者姓氏为Terkki的员工信息
SELECT
*
FROM
employees
WHERE
first_name = ‘Lillian’
OR last_name = ‘Terkki’;
– 查询名字不为Lillian并且性别不是女的员工信息
SELECT
*
FROM
employees
WHERE
first_name != ‘Lillian’
AND NOT gender = ‘F’;

聚合查询

– 查询职级名称为Senior Engineer的员工数量
select count(*) from titles where title=‘Senior Engineer’;
– 查询员工编号为10002的员工的最高年薪
select max(salary) from salaries where emp_no = 10002;
– 查询员工编号为10002的员工的最低年薪
select min(salary) from salaries where emp_no = 10002;
– 查询员工编号为10002的员工的薪水总和
select sum(salary) from salaries where emp_no = 10002;
– 查询员工编号为10002的员工的平均年薪
select avg(salary) from salaries where emp_no = 10002;
– 查询每个员工的薪资和
select emp_no, sum(salary) from salaries group by emp_no;
– 查询员工编号小于10010的,薪资和小于400000的员工的薪资和
SELECT
emp_no, SUM(salary)
FROM
salaries
WHERE
emp_no < 10010
GROUP BY emp_no
HAVING SUM(salary) < 400000;

基础查询语法总结

select <列名>
from <表名>
where <查询条件表达式>
group by <分组的列名>
having <分组以后查询条件表达式>
order by <排序的列名> [ASC/DESC]
limit [开始的行数], <查询条数>

多表查询

表连接

– 查询员工编号为10001的员工的生日,姓名,部门编号和部门名称
SELECT
birth_date, first_name, last_name, de.dept_no, dept_name
FROM
employees em
INNER JOIN
dept_emp de ON de.emp_no = em.emp_no
INNER JOIN
departments ds ON ds.dept_no = de.dept_no
WHERE
de.emp_no = 10001;

– 取出客服部门的历任管理者
SELECT
*
FROM
employees e
INNER JOIN
dept_manager dm ON e.emp_no = dm.emp_no
INNER JOIN
departments d ON d.dept_no = dm.dept_no
WHERE
d.dept_name = ‘Customer Service’;

SELECT
*
FROM
employees e
INNER JOIN
(dept_manager dm, departments d) ON e.emp_no = dm.emp_no
AND dm.dept_no = d.dept_no
WHERE
d.dept_name = ‘Customer Service’;

– 查询员工编号为10001、10002、10003的员工的生日,姓名,部门编号,部门名称
SELECT
birth_date, first_name, last_name, d.dept_no, dept_name
FROM
employees e
LEFT JOIN
(dept_emp de, departments d) ON e.emp_no = de.emp_no
AND de.dept_no = d.dept_no
WHERE
e.emp_no IN (10001 , 10002, 10003);

子查询

– 查询生日为1959-12-03,入职日期为1986-08-28的员工所在的部门编号
SELECT
dept_no
FROM
dept_emp
WHERE
emp_no = (SELECT
emp_no
FROM
employees
WHERE
birth_date = ‘1959-12-03’
AND hire_date = ‘1986-08-28’);
– 查询1988年的年薪为62102的员工的姓名
SELECT
first_name, last_name
FROM
employees
WHERE
emp_no IN (SELECT
emp_no
FROM
salaries
WHERE
salary = 62102
AND from_date LIKE ‘1988%’);

查询思路

  1. 查询结果是什么,对应的是哪些表
  2. 查询条件是什么,查询条件里的字段都在哪些表里

课堂 ppt

课后作业

关闭