Preface:

The previous articles introduce the use of DML and DDL statements. This article will mainly describe the commonly used query grammar. In fact, MySQL official website gives many sample databases for your practical query. Below we take the most commonly used employee sample database as the standard, and introduce in detail their commonly used query grammar.

1. Employee sample database import

Official Document Employee Sample Database Introduction and Download Links:

https://dev.mysql.com/doc/employee/en/employees-installation.html

Similarly, for your convenience, I will share the database backup of the staff database with you. You can also download this data, and then decompress and import it into your local database.

If you have finished importing, you can see the following six tables. This is the table that we will use in our next practice of query grammar.

In order to let you have a better understanding of the sample database, the graph of the relationship between the tables in this database is given here.

Briefly introduce these six tables:

• Departments: Departments table, which records the Department numbers and names of nine departments.
• dept_emp: dept_emp: Departmental employee table, which records data of employees, employee ID and department id, start time and end time (Note: 9999-01-01 means still in the department).
• dept_manager: Departmental manager table, similar to the second table structure, records the period of each manager in each department.
• employees: Employee information sheet, record employee information, employee number emp_no is the only key value.
• Salaries: A salary scale that records the salaries of each employee for each period of time.
• Tiles: Title sheet, which records the title of each employee for each period of time.

2. Fuzzy Query

#Find employee information whose name begins with L
SELECT * FROM employees WHERE first_name LIKE 'L%'；

3. Sorting

#Sort by department number
mysql> SELECT * FROM departments ORDER BY dept_no;
+---------+--------------------+
| dept_no | dept_name          |
+---------+--------------------+
| d001    | Marketing          |
| d002    | Finance            |
| d003    | Human Resources    |
| d004    | Production         |
| d005    | Development        |
| d006    | Quality Management |
| d007    | Sales              |
| d008    | Research           |
| d009    | Customer Service   |
+---------+--------------------+
9 rows in set (0.00 sec)
//Summary:
order by Sort by default asc Arrange in ascending order
//Descending ordering can also be specified

4. Limit the number of rows

#Take the first five lines
mysql> SELECT * FROM employees LIMIT 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      | 1986-06-26 |
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      | 1985-11-21 |
|  10003 | 1959-12-03 | Parto      | Bamford   | M      | 1986-08-28 |
|  10004 | 1954-05-01 | Chirstian  | Koblick   | M      | 1986-12-01 |
|  10005 | 1955-01-21 | Kyoichi    | Maliniak  | M      | 1989-09-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM employees ORDER BY hire_date desc LIMIT 5;
+--------+------------+------------+-----------+--------+------------+
| emp_no | birth_date | first_name | last_name | gender | hire_date  |
+--------+------------+------------+-----------+--------+------------+
| 463807 | 1964-06-12 | Bikash     | Covnot    | M      | 2000-01-28 |
| 428377 | 1957-05-09 | Yucai      | Gerlach   | M      | 2000-01-23 |
| 499553 | 1954-05-06 | Hideyuki   | Delgrande | F      | 2000-01-22 |
| 222965 | 1959-08-07 | Volkmar    | Perko     | F      | 2000-01-13 |
|  47291 | 1960-09-09 | Ulf        | Flexer    | M      | 2000-01-12 |
+--------+------------+------------+-----------+--------+------------+
5 rows in set (0.11 sec)

//Summary:
limit Limit the number of lines before display, and order by Joint use

5. Aggregation function

#Find the total salary of an employee
SELECT SUM(salary) FROM salaries WHERE emp_no = 10001;
#Statistics the number of employees owned by different departments in history and rank them in descending order
mysql> SELECT dept_no, COUNT(*) AS emp_sum FROM dept_emp GROUP BY dept_no ORDER BY emp_sum DESC;
+---------+---------+
| dept_no | emp_sum |
+---------+---------+
| d005    |   85707 |
| d004    |   73485 |
| d007    |   52245 |
| d009    |   23580 |
| d008    |   21126 |
| d001    |   20211 |
| d006    |   20117 |
| d003    |   17786 |
| d002    |   17346 |
+---------+---------+
9 rows in set (0.07 sec)

6.JOIN

#You can try the following three statements for different execution results
SELECT *
FROM salaries INNER JOIN dept_emp
ON salaries.emp_no = dept_emp.emp_no
WHERE salaries.emp_no = 10010;

SELECT *
FROM salaries LEFT JOIN dept_emp
ON salaries.emp_no = dept_emp.emp_no
WHERE salaries.emp_no = 10010;

SELECT *
FROM salaries RIGHT JOIN dept_emp
ON salaries.emp_no = dept_emp.emp_no
WHERE salaries.emp_no = 10010;

//Summary:
a left join b  a All tables are used. b Table Matching a surface
LEFT JOIN Keywords will be listed from the left (a) Where all rows are returned, even in the right table (b) No matching rows in,Unmatched column usage NULL replace

a right join b  b All tables are used. a Table Matching b surface
RIGHT JOIN Keyword will be right table (b) Where all rows are returned, even in the left table (a) No matching rows in,Unmatched column usage NULL replace

inner join and join The effect is the same.
//When there is at least one match in the table, the INNER JOIN keyword returns rows

Summary:

It is recommended that you import this sample database locally. In fact, this database is a good material for practicing query grammar. For our daily study or work, the most frequently used should be query statements, I think there is no skill to write query SQL, only more practice can quickly write to meet the needs of the SQL.