Niu Ke net SQL brush notes 1

1. Query data

Basic knowledge

Single table query

Single table query data (used when the data field queried can be obtained from a single table)
The specific mysql statements are summarized as follows

SELECT  (DISTINCT)field1,field2...
FROM  Table name
WHERE condition 
GROUP BY field
HAVING 
ORDER BY field DESC
LIMIT START,COUNT

  • WHERE condition can be =! = < or < = = between is null and or in
  • You can use wildcards to fuzzy match like
  • GROUP is set up according to the given fields. It is used to GROUP after WHERE filters the data of the complete table
  • Having filters after data grouping
  • The ORDER BY field is sorted in ascending ORDER BY default, and asc can be added to the display; if it is in descending order, DESC must be added
  • In the limit, START is the location where the data row starts to be retrieved (the default is 0), and COUNT is the number of retrieved data from the START location
    limit 5; the default is to retrieve 5 rows from the first row (0) of data
    *limit 3,4 retrieve data from 4-8 lines

Multi table join query

When the query fields are scattered in multiple tables, you need to join multiple tables to query; there are two ways to join the query, you must specify the join conditions, otherwise you use full join;

The following is internal connection (basic linked list query, only when data is matched, data will be displayed)

//Method 1: use from table 1, table 2 where..... This way
 SELECT field from table 1, table 2,.... 
where condition  
group by field
 order by field
limit  


//Method 2: Use Table 1 inner join table 2 on condition 1 and condition 2
 SELECT field
 FROM Table 1
 JOIN Table 2
ON
 Condition 1
AND
 Condition 2

The following is the external connection (according to the condition, the data in the table that does not match the data row will also be displayed)

SELECT field
 FROM Table 1
 LEFT JOIN table 2
ON 
Condition 1
AND 
Condition 2

=============

  1. Find all information about the latest employee

Keyword extraction:
Most:

  • You can use max, min and other functions. Here you need to use subquery or self connection
  • You can sort them and then limit them according to the limit

All information:

  • Can be used*
//Mode 1:
select * from employees 
order by hire_date desc 
limit 1;

//Mode 2: use the function max (subquery)
select * from employees 
WHERE 
hire_date =
(select max(hire_date) from employees);

2. Find all the information of the employee whose time rank is the third from the bottom

Key words: the third from the bottom is the same as the first question. Sort first, and then limit the result of sorting

 select * from employees 
 order by hire_date 
 desc limit 2,1;

3. Find the current salary details of the leaders of each department (to [date = '9999-01-01') and their corresponding department No

CREATE TABLE `dept_manager` (
`dept_no` char(4) NOT NULL,
`emp_no` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));


CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

Analysis:

  • According to the field information, it is obvious that two tables need to be linked for query
  • The query fields are mainly distributed in the slate table,
  • `Since there is a table of connection, we need to think about the conditions of connection
    • To date = '9999-01-01'; both tables have to date fields
    • The EMP ﹣ no in two tables should be equal
//Connection mode 1
select s.*,m.dept_no 
from salaries as s
 join dept_manager  as m 
on s.emp_no=m.emp_no
 and  m.to_date='9999-01-01' 
 and s.to_date='9999-01-01'; 

//Joint mode 2:
select s.*,m.dept_no 
from salaries as s
,dept_manager  as m 
where s.emp_no=m.emp_no
 and  m.to_date='9999-01-01' 
 and s.to_date='9999-01-01'; 

4. Find the last name, first name, and Dept no of all employees who have assigned departments

CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

Analysis:

  • The fields of the query are scattered in two tables, so the query needs to be joined
  • The condition of join query is
    *The EMP no field of the dept EMP table is equal to the EMP no field in the employees table
SELECT
    e.last_name,
    e.first_name,
    d.dept_no
 
FROM
    dept_emp AS d
 JOIN
    employees AS e
ON
    e.emp_no = d.emp_no;

5. Check the last name and first name of all employees and the corresponding department number Dept. No., including displaying the employees who are not assigned to a specific department

The difference between attention and the above is

  • Employees who have been promoted to match the assigned positions (no assignment mismatch)
  • This question matches all employees (whether or not they are assigned positions)
    Therefore, external connection is adopted
select e.last_name,e.first_name,d.dept_no      

from  employees AS e 

left join dept_emp AS d 

on

e.emp_no=d.emp_no;

6. Find the salary of all employees at the time of entry, give EMP no and salary, and reverse the order according to EMP No

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));

Analysis:

  • In the test data, salary.emp no is not unique (because the employee with the number of EMP no has the possibility of multiple salary increases, so there is more than one corresponding record in salary). Employees.emp no is unique, that is to say, the data of salary will be more than employees, so it is necessary to find the corresponding record salary.emp no in salary table first, and there are restrictions e.emp_no = s.emp_no
  • The key is how to determine the entry time
    • In employees, there is "hire" date, indicating the employee's employment information; in sakaries, there is "from" date, indicating the start date of the salary. You can determine the employment date by combining the two
  • Reverse EMP no
select   e.emp_no ,s.salary from  employees as e
 salaries 
join  s  
on e.emp_no=s.emp_no
AND 
e.hire_date = s.from_date 
order by 
e.emp_no desc;

2020/3/16

Published 181 original articles, won praise 19, visited 20000+
Private letter follow

Tags: MySQL

Posted on Mon, 16 Mar 2020 00:55:43 -0700 by Joopy