Learning notes: MySQL query

Time:2021-9-26

Preface: I spent two nights watching mysql. I didn’t expect that the effect was too poor. It’s better to follow the online class video and complete the exercise questions (if you don’t write notes on the exercise questions, you won’t record them), and then take notes.

1、 Basic query select statement
Syntax: select query list from table name;
The query list can be fields, constant values, expressions and functions in the table, and the query result is a virtual table.

SELECT last_name FROM employees;

#2. Query multiple fields in the table
SELECT first_name,last_name,phone_number,email FROM employees;

#3. Query all fields in the table
SELECT * FROM employees;

#4. Query constant
SELECT 10;

#5. Query expression
SELECT 1010*2;

#6. Query function
SELECT VERSION( );

#7. Alias
Select salary as ` income ` from employees;
SELECT first_ Name ` from employees;

#8. Weight removal
Select distinct salary as ` salary sample ` from employees;

#9. '+' plus sign: it is only used for operators, not for splicing characters
SELECT 1010 + 2023;

#10. Use concat to connect fields
SELECT CONCAT(first_name,last_name) FROM employees;

2、 Condition query
Syntax: select query list from table name
Where screening conditions;

Classification:
1. Filter by conditional expression
Operator: > < =! = < > =<=

2. Filter by logical expression
Operator: | & &!
You can also use or and not

3. Fuzzy query
like
between and
in
is null

#Query by conditional expression
#1. Query employee information with salary > 12000
SELECT * FROM employees WHERE salary >12000;

#2. Query the employee name and department number whose department number is not equal to 90
SELECT last_name,department_id FROM employees WHERE department_id !=90;

#Query by logical expression
#3. Query the name, salary and bonus of employees with salary of 10000 ~ 20000
SELECT last_name, salary,commission_pct FROM employees WHERE salary >10000 AND salary <20000;

#4. Query the information of employees whose department number is not 90 ~ 100 or whose salary is higher than 15000
SELECT * FROM employees WHERE department_id <90 OR department_id >110 OR salary >15000;
SELECT * FROM employees WHERE NOT(department_id BETWEEN 90 AND 110) OR salary >15000;

#Like query
#5. Query the employee information with a character in the employee name
SELECT * FROM employees WHERE last_name LIKE '%a%';

#6. Query the employee name and salary with the third character n and the fifth character L in the employee name
SELECT last_name,salary FROM employees WHERE last_name LIKE '__n_l%';

#7. The second character of employee name is_ Employee information for
SELECT * FROM employees WHERE last_name LIKE '_\_%';
SELECT * FROM employees WHERE last_name LIKE '_$_%' ESCAPE '$';

#Between and two critical values cannot be interchanged
#8. Query employee information with employee number between 100 and 120
SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120;

#in
#9. Query whether the employee's type of work is it_ PROG,AD_ VP,AD_ Employee name and type of work of one in prse
SELECT last_name, job_id FROM employees WHERE job_id IN('IT_PROG','AD_VP','AD_PRSE');

#is null
#10. Query employee name and bonus rate without bonus
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;

#Safety equals
SELECT last_name, commission_pct FROM employees WHERE commission_pct <=> NULL;

#11. Query employee name and bonus rate with bonus
SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NOT NULL;

3、 Sort query
Syntax:
Select query list from table name
Where filter criteria
Order by sort list ASE | DESC;

The order by clause is usually placed at the end of the query statement, except the limit clause
The order by clause supports single field, multiple fields, expressions, aliases, and functions

#1. Query employee information and sort by salary from low to high
SELECT * FROM employees 
ORDER BY salary ASC;

#2. Query the information of employees with department number > 90, and sort by employment time
SELECT * FROM employees
WHERE department_id >90
ORDER BY hiredate ASC;

#3. Display employee information and annual salary according to the level of annual salary
Select *, salary * 12 as' annual salary 'from employees
ORDER BY salary*12 ASC;

#4. Display employee name and salary by name length
Select concat (first_name, last_name) as' name ', salary from employees
ORDER BY LENGTH(CONCAT(first_name,last_name)) ASC;

#5. Query employee information, first sort by salary in ascending order, and then sort by employee number in descending order
SELECT * FROM employees
ORDER BY salary ASC,employee_id DESC;

4、 Common functions
Syntax:
Select function name (argument list) from table name;
Classification:

  1. Single-Row Functions
    For example: concat, length, ifnull
  2. Grouping function
#(1) Character function
#1. Length gets the number of bytes of the parameter value
Select length ('Chinese ');
SELECT LENGTH('English');

#2. Concat splicing string
SELECT CONCAT(first_name,' ',last_name) FROM employees;

#3.upper、lower
#Capitalize the last name and lowercase the first name, and then splice them
Select concat (upper (first_name), ', lower (last_name)) as' name' from employees;

#4. Substr and substring indexes start from the beginning
Select substr ('one two three four five ', 1,4);

#Capitalize the first character of the name and lower case the remaining characters, and then splice them together
SELECT CONCAT(UPPER(SUBSTR(last_name,1,1)),'_',LOWER(SUBSTR(last_name,2))) FROM employees;

#5. Instr returns the index of the first occurrence of the substring. If not, 0 is returned
Select instr ('Five mountains, Mount Tai ','mount Tai');

#6.trim
SELECT TRIM('    23232  ');
Select trim ('O 'from' oooo ';

#7. Lpad fills the left of the specified character to the specified length
Select lpad ('Chinese ', 9,' * ');

#8. Rpad fills the right of the specified character to the specified length
Select rpad ('English ', 10,' o ');

#9. Replace
Select replace ('Chinese * * * * ',' * ','O');

##(2) Mathematical function
#1. Round
SELECT ROUND(1.4);

#2. Ceil rounds up and returns > = the minimum integer of the parameter
SELECT CEIL(1.4);

#3. The floor is rounded down and returns < = the maximum integer of the parameter
SELECT FLOOR(1.5);

#4. Truncate and keep several decimal places
SELECT TRUNCATE(1.8888,0);

#5. Mod remainder
SELECT MOD(34,8);

##(3) Date function
#1. Now returns the current system date and time
SELECT NOW();

#2. Current date returns the current system date, excluding time
SELECT CURDATE();

#3. Curtime returns the time, excluding the date
SELECT CURTIME()

#4. Get the specified part, year, month, day, hour, minute and second
SELECT YEAR(NOW());

SELECT MONTH(NOW());

SELECT MONTHNAME(NOW());

SELECT MINUTE(NOW());

#5.str_ to_ Date converts characters to a date in the specified format
SELECT STR_TO_DATE('2021-4-13','%Y-%c-%d') out_put;

#Query employees whose employment date is April 3, 1992
SELECT * FROM employees WHERE hiredate = STR_TO_DATE('4-3 1992','%c-%d %Y');

#date_ Format converts the date to characters
SELECT DATE_ Format (now(), '% y year% C month% d');

#Query the name and employment date of the employee with bonus (mm / DD / yyyy)
SELECT CONCAT(first_name,' ',last_name),DATE_ Format (hiredate,% Y% C% d) entry date from employees
WHERE commission_pct IS NOT NULL;

##(4) Other functions
#1. Display MySQL version number
SELECT VERSION();

#2. Display the currently open database
SELECT DATABASE();

#3. Display the currently logged in user
SELECT USER();

##(5) Process control function
#If else effect
Select if (35 > 10, 'large', 'small');

Select concat (first_name, '' last_name), if (commission_pct, 'bonus',' no bonus') from employees;

#Case character or expression to judge
#When constant then the value or statement to display
#When constant then the value or statement to display
#...
#Else the value or statement to display
#end

#Query the salary of employees. Requirements:
#The department number is 30 and the displayed salary is 1.1 times
#The department number is 40 and the displayed salary is 1.2 times
#The department number is 50 and the displayed salary is 1.3 times
#In other departments, the displayed salary is 1 times

Select salary, Department_ ID department number,
CASE department_id
WHEN 30 THEN salary*1.1
WHEN 40 THEN salary*1.2
WHEN 50 THEN salary*1.3
ELSE salary
End as new salary
FROM employees;

#case 
#When condition1 then the value or expression to display
#When condition2 then the value or expression to display
#When condition3 then the value or expression to display
#Else the value or expression to display
#end

#Query the salary status of employees
/*If the salary is greater than 20000, Grade A is displayed
If the salary is greater than 15000, grade B is displayed
If the salary is greater than 10000, grade C is displayed
Otherwise, level D is displayed
*/
SELECT salary,
CASE salary
When salary > 20000 then 'grade a'
When salary > 15000 then 'grade B'
When salary > 10000 then 'grade C'
Else 'level d'
End as wage scale
FROM employees;



#Grouping function
/*Function: used for statistics, also known as aggregate function, statistical function or group function
Classification:
Sum, AVG average, Max max, count
*/

#1. Simple use
SELECT SUM(salary) FROM employees;
SELECT AVG(salary) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT MIN(salary) FROM employees;
SELECT COUNT(salary) FROM employees;

#2. Which parameter types are supported? Sum and AVG are generally used to handle numeric types. Max, min and count can handle any type
SELECT SUM(last_name) FROM employees;
SELECT MAX(last_name) FROM employees;
SELECT COUNT(last_name) FROM employees;

#3. Whether to ignore null value. The following grouping functions ignore null value
SELECT MAX(commission_pct),SUM(commission_pct),AVG(commission_pct) FROM employees;
SELECT COUNT(employee_id),COUNT(commission_pct) FROM employees;

#4. Use with distinct
SELECT SUM(DISTINCT(salary)) FROM employees;
SELECT SUM(salary) FROM employees;

#5. Detailed introduction to count function
/*Efficiency:
Under MyISAM engine, count (*) is efficient
Under the InnoDB engine, the efficiency of count (*) is almost the same as that of count (1), which is higher than that of count (field)
*/
SELECT COUNT(salary) FROM employees;
SELECT COUNT(*) FROM employees;
SELECT COUNT(1) FROM employees;

#6. Restrictions on the fields queried with the grouping function: the fields after group by are required
SELECT SUM(salary),employee_id FROM employees;

5、 Grouping query
Syntax:
Select grouping function and column (it is required to be the field after group by)
From table name
Where filter criteria
Group by list of groups
Order by clause

Note: the query list must be special. It is required to be the field after the grouping function and group by

There are two types of filter criteria in group query:
The filter data source before grouping is the original table, and the key in front of group by is where
Filter the grouped result set after group by having
The grouping function as the filter condition must be placed in the having clause.

The group by clause supports single field grouping, multiple field grouping, expression or function, and sorting can also be added

#Grouping query
#1. Query the maximum wage of each type of work
SELECT MAX(salary),job_id FROM employees
GROUP BY job_id;

#2. Query the number of departments in each location
SELECT COUNT(*),location_id FROM departments
GROUP BY location_id;

#3. Add filter criteria: query the average salary of each department with a character in the mailbox
SELECT AVG(salary),department_id FROM employees
WHERE email LIKE '%a%'
GROUP BY department_id;

#4. Query the maximum salary of employees under each leader with bonus
SELECT MAX(salary),manager_id FROM employees
WHERE commission_pct IS NOT NULL
GROUP BY manager_id;

#Add complex filter criteria
#5. Query which department has more than 2 employees
#Query the number of employees in each department
SELECT COUNT(*),department_id FROM employees GROUP BY department_id;
#Filter according to the above results and query which department has more than 2 employees
SELECT COUNT(*),department_id FROM employees GROUP BY department_id
HAVING COUNT(*) >2;

#6. Query the job number and maximum salary of employees with bonus > 12000
SELECT MAX(salary),job_id FROM employees
GROUP BY job_id
HAVING MAX(salary) >12000;

#7. Query the minimum wage of each leader with leader number > 102, the leader number with leader number > 5000 and the minimum wage
SELECT manager_id,MIN(salary) FROM employees
WHERE manager_id >102
GROUP BY manager_id
HAVING MIN(salary) >5000;

#Group by expression or function
#8. Query the number of employees in each group according to the length of employee name, and filter which employees are more than 5
SELECT COUNT(*),LENGTH(last_name) FROM employees
GROUP BY LENGTH(last_name)
HAVING COUNT(*) >5;

#Group by multiple fields
#9. Query the average salary of employees in each department and type of work
SELECT AVG(salary),department_id,job_id FROM employees
GROUP BY department_id,job_id;

#Add sort
#10. Query the average salary of employees in each department and type of work, and sort by the average salary
SELECT AVG(salary),department_id,job_id FROM employees
GROUP BY department_id,job_id
HAVING AVG(salary) >10000
ORDER BY AVG(salary) ASC;

6、 Multi table query
Classification by standard:
SQL92: only internal connection is supported, sql99

Classification by function:
Internal connection from external connection

Sql99 syntax:

Select query list
From table name connection type
Join table name
On connection condition
Where filter criteria
Group by group condition
Having filter criteria
Order by filter criteria

Classification:
Inner connection: Inner
External connection: left external connection, right external connection and full external connection
Cross join: cross join

(1) Internal connection:
Syntax: select query list
From table name
Inner join table name
On connection condition

Classification: equivalent connection, non equivalent connection, self connection

(2) External connection:
Application scenario: used to query records in one table but not in another.

characteristic:
The query results of external connections are all records in the main table,
If there is a matching value from the table, the matching value is displayed,
If there is no matching from the table, a null value is displayed

External connection query result = internal connection result + records in the main table but not in the slave table
Left outer join, left join, left main table
Right outer join. The main table is on the right of right join
Total external connection = internal connection result + records in the master table but not in the slave Table + records in the slave table but not in the master table

##Self connection

#1、 Inner connection
#Equivalent connection
#1. Query employee name and department name
SELECT last_name,department_name
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id;

#2. Query the employee name and type of work included E in the employee name (add filter)
SELECT last_name,job_title 
FROM employees e
INNER JOIN jobs j
ON e.`job_id`= j.`job_id`
WHERE e.last_name LIKE '%e%';

#3. Query the city name and department number with department number > 3 (add grouping and filtering)
SELECT COUNT(*),city
FROM departments d
INNER JOIN locations l
ON d.location_id  = l.location_id
GROUP BY city
HAVING COUNT(*) >3;

#4. Query the Department name and number of employees in that department whose number of employees is > 3, and sort them in descending order (add sorting)
SELECT COUNT(*),department_name
FROM employees e
INNER JOIN departments d
ON e.`department_id` = d.`department_id`
GROUP BY department_name
HAVING COUNT(*) >3
ORDER BY COUNT(*) DESC;

#5. Query employee name, department name and type of work in descending order by department name
SELECT last_name,department_name,job_title
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY department_name DESC;

##Non equivalent connection
#6. Query employee's salary level
SELECT last_name,salary,grade_level FROM employees e
INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal;

#7. Query the number of salary levels > 20 and descending by salary level
SELECT COUNT(*),grade_level FROM employees e
INNER JOIN job_grades j ON e.salary BETWEEN j.lowest_sal AND j.highest_sal
GROUP BY j.grade_level
HAVING COUNT(*) >20
ORDER BY  j.grade_level DESC;

##Self connection
#8. Query employee's name and superior's name
SELECT e.last_ Name employee name, m.last_ Name superior from employees e
INNER JOIN employees m ON e.manager_id = m.employee_id;

#9. Query the name of the employee, including the character K and the name of the superior
SELECT e.last_ Name employee name, m.last_ Name superior from employees e
INNER JOIN employees m ON e.manager_id = m.employee_id
WHERE e.last_name LIKE '%k%';

#2、 External connection
#Left outer connection
#1. Query goddess without boyfriend
SELECT be.name,b.boyname FROM beauty be
LEFT OUTER JOIN boys b ON be.boyfriend_id = b.id
WHERE b.id IS NULL;

#2. Query which department has no employees
SELECT department_name,employee_id FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id;
WHERE d.department_id IS NULL;

#Right outer connection
SELECT department_name FROM employees e
RIGHT OUTER JOIN departments d ON d.department_id = e.department_id;

#Not supported in external MySQL
SELECT be.*,bo.* FROM beauty be
FULL OUTER JOIN boys bo ON be.boyfriend_id = bo.id;

#Cross connect
SELECT be.*,bo.* FROM beauty be
CROSS  JOIN boys bo ;

7、 Subquery
Meaning: select statements that appear in other statements are called subqueries or intra queries
External query statements are called primary or external queries

Classification:
Location of sub query:
After select: only scalar subqueries are supported;
After from: support table sub query;
After where or having: scalar subquery or column subquery, row subquery;
After exist: related sub query

Divided by the number of rows and columns of the result set:
Scalar subquery (result set is row column)
Row subquery (one row with multiple columns)
Column subquery (one column and multiple rows)
Table subquery (multi row and multi column)

#1、 After where or having
/*
Scalar subquery, column subquery and row subquery are supported
characteristic:
Subqueries are generally placed in parentheses
Subqueries are generally placed on the right side of conditions
Scalar subquery: generally used with single line operator (> < =)
Column subquery: generally used with multi row operator (in any / some or)
*/

##Scalar subquery
#1. Whose salary is higher than Abel's?
SELECT salary FROM employees
WHERE last_name = 'Abel';

SELECT last_name,salary FROM employees
WHERE salary >(
	SELECT salary FROM employees
WHERE last_name = 'Abel');
 
#2. Query job_ The ID is the same as that of employee No. 141, and there are more employee names and jobs in salary than that of employee No. 143_ ID and salary
SELECT job_id FROM employees
WHERE employee_id = 141;

SELECT salary FROM employees
WHERE employee_id = 143;

SELECT last_name,job_id,salary FROM employees
WHERE job_id = (
		SELECT job_id FROM employees
		WHERE employee_id = 141) 
AND salary > (
		SELECT salary FROM employees
		WHERE employee_id = 143);

#3. Return the name and job of the employee with the lowest salary_ ID and salary
SELECT MIN(salary) FROM employees;

SELECT last_name,job_id,salary FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees);

#having
##4. Query the Department ID and the minimum wage of the Department whose minimum wage is greater than the minimum wage of No. 50 department
SELECT MIN(salary),department_id FROM employees
GROUP BY department_id
HAVING department_id = 50;

SELECT MIN(salary),department_id FROM employees
GROUP BY department_id
HAVING MIN(salary) > (
	SELECT MIN(salary) FROM employees
	GROUP BY department_id
	HAVING department_id = 50);

#Row subquery
#5. Query the information of the employee with the smallest employee number and the highest salary
SELECT MIN(employee_id) FROM employees;

SELECT MAX(salary) FROM employees;

SELECT * FROM employees
WHERE employee_id = (SELECT MIN(employee_id) FROM employees)
AND salary = (SELECT MAX(salary) FROM employees);

SELECT * FROM employees
WHERE (employee_id,salary) =(SELECT MIN(employee_id), MAX(salary) FROM employees);

#2、 After select: only scalar subqueries are supported)
#6. Query the number of employees in each department
SELECT COUNT(*),department_name FROM departments d
LEFT OUTER JOIN employees e ON d.department_id = e.department_id
GROUP BY department_name;

SELECT d.*,(
	SELECT COUNT(*) FROM employees e
	WHERE e.department_id = d.department_id)
FROM departments d;

#7. Query the Department name whose employee ID is 102
SELECT department_name FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id
WHERE employee_id = 102;

#3、 After from: column sub query. The results of the sub query are treated as a table. It is required to take an alias
#8. Query the salary grade of the average salary of each department
/*Possible error*/
SELECT AVG(salary),department_id,grade_level FROM employees e
INNER JOIN job_grades ON salary BETWEEN lowest_sal AND highest_sal
GROUP BY department_id;

SELECT ag.*, grade_level 
FROM (
	SELECT AVG(salary) s,department_id FROM employees
	GROUP BY department_id) ag
INNER JOIN job_grades ON ag.s BETWEEN lowest_sal AND highest_sal;

#4、 After exist
/*Syntax:
Exist (complete query statement)
Result: 1 or 0
*/
#9. Query the Department name with employees
SELECT department_name FROM departments d
WHERE EXISTS(
	SELECT * FROM employees e
	WHERE e.department_id = d.department_id);

SELECT department_name FROM departments d
INNER JOIN employees e ON d.department_id = e.department_id;

8、 Paging query
One page of data to be displayed is incomplete, and SQL requests need to be submitted in pages
Syntax:
Select query list
From table name
Inner join table name on join condition
Where filter criteria
Group by group condition
Filtering after having grouping
Order by sorted fields
limit offset,size

Offset the starting index of the entry to be displayed (the starting index starts from zero)
Size number of entries to display

Note: limit is placed at the end of the query statement
Formula: number of pages to display limit (page-1) * size, size

#Paging query
#Query the first five employee information
SELECT * FROM employees LIMIT 0,5;

#2. Query article 11-25 employee information
SELECT * FROM employees LIMIT 10,15;

#3. Information of employees with bonus and display the top 10 items with higher salary
SELECT * FROM employees WHERE commission_pct IS NOT NULL 
ORDER BY salary
LIMIT 0,10;

9、 Joint query
Union combines the results of multiple query statements into one result
Syntax:
Query statement 1
union
Query statement 2
union

Application scenario: when the query results come from multiple tables, and multiple tables have no direct connection relationship, but the query information is consistent
Features: the query list of multiple query statements is required to be consistent
The type and order of each column of multiple query statements should be consistent
Union keyword is de duplicated by default. If union all is used, duplicate items can be included

#Joint query
#Query employee information with department number > 90 or email containing a
SELECT * FROM employees WHERE department_id > 90
UNION
SELECT * FROM employees WHERE email LIKE '%a%';

Recommended Today

Supervisor

Supervisor [note] Supervisor – H view supervisor command help Supervisorctl – H view supervisorctl command help Supervisorctl help view the action command of supervisorctl Supervisorctl help any action to view the use of this action 1. Introduction Supervisor is a process control system. Generally speaking, it can monitor your process. If the process exits abnormally, […]