SQL复杂查询实战

数据库演练环境

增删改

insert into departments(dept_no, dept_name) values('d100','hogwarts');
update departments set dept_name='python_auto' where dept_no='d100';
delete from departments where dept_no='d100';

基本查询练习

  1. 查询所有员工的生日和性别,并且为查询的信息展示中文表头
  2. 查询所有女员工的名、姓和性别
  3. 显示年薪从高到低排序,第15位到第20位员工的编号和年薪

条件查询练习

  1. 查询年薪大于90000的员工编号和年薪,查询前3条
  2. 查询员工编号小于50000并且年薪大于150000的记录
  3. 查询名称中包含Senior并且员工编号在10144和10146之间的职级名称
  4. 以员工编号从小到大的顺序显示员工编号为10054、10133、10998或者入职日期不早于1999-03-30的员工信息

聚合查询练习

  1. 查询1986年入职的男员工和女员工的数量

表连接练习

  1. 取出客服部门的历任管理者
SELECT 
    first_name, last_name
FROM
    employees e
        JOIN
    (dept_manager dm, departments d) ON e.emp_no = dm.emp_no
        AND dm.dept_no = d.dept_no
WHERE
    dept_name = 'Customer Service';

select birth_date as 生日, gender 性别 from employees;
2.
select last_name, first_name, gender from employees where gender=‘F’;
3.
select emp_no, salary from salaries order by salary desc limit 5 offset 15;

1 Like

练习-20200418

1. 查询所有员工的生日和性别,并且为查询的信息展示中文表头

select birth_date as "生日", gender as "性别" from employees;

2. 查询所有女员工的名、姓和性别

select first_name as "名",last_name as "姓",gender as "性别" from employees where gender ="F";

3. 显示年薪从高到低排序,第15位到第20位员工的编号和年薪

select emp_no as "编号",salary as "年薪" from salaries order by salary desc limit 6 offset 14;
1 Like

–查询所有员工的生日和性别,并为查询的信息展示中文表头
SELECT a.birth_date AS ‘生日’, a.gender AS ‘性别’
FROM employees a
LIMIT 100
–查询所有女员工的名、姓和性别
SELECT a.first_name AS ‘名’,a.last_name AS ‘姓’, a.gender AS ‘性别’
FROM employees a
WHERE a.gender =‘F’
LIMIT 100
–显示年薪从高到低排序,第15位到第20位员工的编号和年薪
SELECT emp_no,salary
FROM salaries
ORDER BY salary DESC
LIMIT 6 OFFSET 14

基本查询练习

#查询所有员工的生日和性别,并且为查询的信息展示中文表头
select birth_date as 生日,gender as 性别 from employees;
# 查询所有女员工的名、姓和性别
select first_name as 名,last_name as 姓,gender as 性别 from employees where gender='F'
# 显示年薪从高到低排序,第15位到第20位员工的编号和年薪
select * from salaries order by salary desc limit 5 offset 14; 

select birth_date as `生日`,gender as `性别` from employees;
select first_name,last_name,gender from employees where gender='F';
select emp_no,salary from salaries where to_date='9999-01-01'  order by salary desc limit 6 offset 15;

select birth_date AS ‘生日’,gender AS ‘性别’ from employees ;
select First_name,last_name,gender from employees where gender=‘M’;

select emp_no,salary from salaries order by salary desc limit 5 offset 15;

SELECT E.birth_date as 生日, E.gender as 性别 from employees as E;
SELECT E.first_name as 名, E.last_name as 姓, E.gender as 性别 from employees as E WHERE E.gender=‘F’;
SELECT S.emp_no as 编号, S.salary as 年薪 from salaries as S ORDER BY S.salary DESC LIMIT 6 OFFSET 14;

条件查询

1.查询年薪大于90000的员工编号和年薪,查询前3条
SELECT S.emp_no AS 编号, S.salary AS 年薪 FROM salaries AS S WHERE S.salary>90000 LIMIT 3;

2.查询员工编号小于50000并且年薪大于150000的记录
SELECT * FROM salaries AS S WHERE S.emp_no<50000 AND S.salary>150000;

  1. 查询名称中包含Senior并且员工编号在10144和10146之间的职级名称
    SELECT T.title from titles AS T WHERE T.emp_no>=10144 AND T.emp_no<=10146 AND T.title LIKE ‘%Senior%’;

4.以员工编号从小到大的顺序显示员工编号为10054、10133、10998或者入职日期不早于1999-03-30的员工信息
SELECT * FROM employees as E WHERE E.emp_no in (10054,10133,10998) OR E.hire_date>=‘1999-03-30’ order by E.emp_no ASC;

聚合查询

SELECT E.gender as 性别, COUNT(*) AS 数量 FROM employees as E WHERE E.hire_date LIKE ‘1986%’ GROUP BY E.gender;

表连接查询

SELECT
	*
FROM
	employees AS E
JOIN dept_manager AS DM ON E.emp_no = DM.emp_no
JOIN departments AS D ON DM.dept_no = D.dept_no
WHERE
	D.dept_name = 'Customer Service'

基本查询练习

  1. 查询所有员工的生日和性别,并且为查询的信息展示中文表头
    select birth_date ‘生日’,gender ‘性别’ from employees.employees;
  2. 查询所有女员工的名、姓和性别
    select first_name,last_name,gender from employees.employees where gender=‘M’;
  3. 显示年薪从高到低排序,第15位到第20位员工的编号和年薪
    select emp_no,salary from employees.salaries order by salary desc limit 6 offset 15;
  1. 查询所有员工的生日和性别,并且为查询的信息展示中文表头
    SELECT birth_date 生日,gender 性别 FROM employees
  2. 查询所有女员工的名、姓和性别
    select last_name, first_name, gender from employees where gender=‘F’;
  3. 显示年薪从高到低排序,第15位到第20位员工的编号和年薪
    select emp_no, salary from salaries order by salary desc limit 6 offset 14;

1、select birth_date as ‘生日’,gender as ‘性别’ from employees.employees;
2、select last_name,first_name,gender from employees.employees where gender=‘F’;
3、select emp_no,salary from salaries order by salary desc limit 6 offset 14;

1 Like

1、查询所有员工的生日和性别,并且为查询的信息展示中文表头
image
2、查询所有女员工的名、姓和性别
image
3、显示年薪从高到低排序,第15位到第20位员工的编号和年薪
image

1 Like

基本查询练习作业:

  1. 查询所有员工的生日和性别,并且为查询的信息展示中文表头
SELECT birth_date AS '生日',gender AS '性别' FROM employees;
  1. 查询所有女员工的名、姓和性别
SELECT first_name,last_name,gender FROM employees WHERE gender = 'F';
  1. 显示年薪从高到低排序,第15位到第20位员工的编号和年薪
SELECT emp_no,salary FROM salaries ORDER BY salary DESC LIMIT 15 OFFSET 4;

SELECT birth_date as 生日 , gender as 性别 FROM employees ;

SELECT first_name , last_name , gender FROM employees e WHERE gender=‘F’ ORDER BY last_name desc;

SELECT emp_no , salary FROM salaries ORDER BY salary DESC LIMIT 6 OFFSET 14;

1 Like

1.查询所有员工的生日和性别,并且为查询的信息展示中文表头
image
2.查询所有女员工的名、姓和性别
image
3. 显示年薪从高到低排序,第15位到第20位员工的编号和年薪
image

练习-条件查询-20200418

1. 查询年薪大于90000的员工编号和年薪,查询前3条

select  emp_no,salary from salaries where salary > 90000 limit 3 offset 0;

2. 查询员工编号小于50000并且年薪大于150000的记录

select * from salaries where emp_no<50000 and salary > 150000;

3. 查询名称中包含Senior并且员工编号在10144和10146之间的职级名称

select emp_no,title from titles where title like '%Senior%' and emp_no between 10144 and 10146;

4. 以员工编号从小到大的顺序显示员工编号为10054、10133、10998或者入职日期不早于1999-03-30的员工信息

select * from employees where emp_no in (10054,10133,10998) or hire_date>"1999-03-30" order by emp_no;
1 Like

SELECT emp_no,salary FROM salaries WHERE salary > 90000 LIMIT 3;
SELECT * FROM salaries WHERE emp_no < 50000 AND salary > 150000;
SELECT * FROM titles WHERE title LIKE ‘%Senior%’ AND emp_no BETWEEN 10144 AND 10146;
SELECT * FROM employees WHERE emp_no in (‘10054’,‘10133’,‘10998’) OR hire_date >= 1999-03-30 ORDER BY emp_no;

1 Like
SELECT emp_no,salary FROM salaries WHERE salary>90000 LIMIT 3;
SELECT * FROM salaries WHERE emp_no<50000 AND salary>150000;
SELECT title FROM titles WHERE title LIKE '%Senior%' and emp_no BETWEEN 10144 AND 10146;
SELECT * FROM employees WHERE emp_no IN ('10054','10133','10998') OR hire_date>='1999-03-30' ORDER BY emp_no; 
1 Like

SELECT emp_no,salary FROM salaries WHERE salary>90000 LIMIT 3;

SELECT * FROM salaries s WHERE s.emp_no<50000 AND s.salary>150000;

SELECT t.title as 职级名称 FROM titles t WHERE t.emp_no BETWEEN 10144 and 10146 and t.title like ‘%Senior%’;

SELECT * FROM employees e WHERE e.emp_no in(10054,10133,10998) or e.hire_date >=‘1999-03-30’ ORDER BY e.emp_no asc;

1 Like

select emp_no from salaries where salary>90000 limit 0,3

select * from salaries where salary>150000 and emp_no<50000;

select * from employees where first_name like ‘%Senior%’ or last_name like ‘%Senior%’ and emp_no BETWEEN 10144 AND 10998;

select * from salaries where emp_no in (10054,10133,10998) or from_date>=‘1999-03-30’;

关闭