Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to write DQL data query sentences in MySQL. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Catalogue

I. basic query

2. Conditional inquiry

Third, sort query

IV. Common functions

5. Group query

VI. Connection query

7. Subquery

8. Paging query

IX. Joint inquiry

Sample data preparation

Before experimenting with DQL query statements, prepare the corresponding data as samples for query use

Using SQLyog to import the SQL script, you can see the prepared sample table:

This sample is four tables managed by employees of a multinational company. the following figure introduces the fields of each table:

I. basic query

Syntax: select query list from table name

Features:

The query list can be: fields in the table, constant values, expressions, functions.

The result of the query is a virtual table.

Execution order: from > select (find the table first, then start the query)

Note: `is an emphasis. When a field in a table conflicts with a keyword, you can put an emphasis on both sides of the field to indicate that it is a field, not a keyword (such as `name`). [basic query] # Select sample library USER myemployees;# 1. Query a single field in the table SELECT last_name FROM employees;# 2. 0. Query multiple fields in the table SELECT last_name,salary,email FROM employees;# 3. 0. Query all the fields in the table SELECT * FROM employees;# 4. Query the constant value SELECT 'Tom';# 5. Query expression SELECT 7% 6 position # 6. Query function SELECT VERSION (); # 7. Aliases ("aliases" caused by double quotes are recommended in mysql) / * advantages: easy to understand; when connecting a query, if the field to be queried has duplicate names, you can use aliases to distinguish * / # mode 1, use asSELECT 7% 6 AS results; SELECT last_name AS surname, first_name AS first name FROM employees;# mode 2, use spaces SELECT 7% 6 results; SELECT last_name surname, first_name name FROM employees # query the employee's name, department, nianxinSELECT last_name,department_id,salary*12* (1+IFNULL (commission_pct,0)) AS annual salary FROM employees; # 8. The function of deduplicating SELECT DISTINCT department_id FROM employees;# 9.+ sign / * select 13: 21; both operands are numeric, and one of them is a character, then the character is converted to a numeric select'13; if the conversion is successful, the addition operation select 'hello'+1; conversion fails, and the character is converted to 0select null+10 As long as one of them is null, the result is to supplement the ifnull function for null: SELECT IFNULL (commission_pct,0) AS bonus rate, commission_pct FROM employees;mysql is not used to concatenate the + sign, but the concat function * / SELECT CONCAT (last_name,first_name) AS "name" FROM employees; basic query summary description 1. Query a single field in the table select field 1 from table; 2. Query multiple fields in the table select field 1, field 2. Field n from table; 3. Query all fields in the table select * from table; 4. Query constant value select 'constant value;' 5. Query expression select value 1 expression value 2 value 6. Query function select f (); 7. Alias as8. Remove duplicate distinct9. Splicing uses the concat function instead of "+" concat (last_name,first_name)

After learning the basic query, try to complete the following exercises

Answer:

1. Correct

two。 Correct

3. Quotation marks should be used in English

4.DESC departments;;SELECT * FROM departments

5.SELECT CONCAT (first_name,',',last_name,',',IFNULL (email,0)) AS "out_put" FROM employees

2. Conditional inquiry

Syntax: select query list from table name where filter

Execution order: from > where > select (navigate to the table first, then start filtering, and finally query)

Classification:

(1) filter by conditional operator

The conditional operators are: >

< = >

= 12000 employees SELECT * FROM employees WHERE salary > 12000; # 2. Query the employee name and department number SELECT department_name, department_id FROM departments WHERE department_id90 if the department number is not equal to 90 -(2) filter # 1 by logical expression. Query the name, salary and bonus rate of employees whose salary is between 10000 and 20000 SELECT last_name,salary,commission_pct FROM employees WHERE salary > = 10000 AND salary=90 AND department_id15000 -(3) Fuzzy query # (1) like keyword # 1. Query the information of an employee whose name contains the character a SELECT * FROM employees WHERE last_name LIKE'% employee # 2. Query the employee name with n in the third character and l in the fifth character and the salary SELECT last_name,salary FROM employees WHERE last_name LIKE'_ naughl% employee # 3. Query the employee name with the second character _ in the employee name (escape) SELECT last_name FROM employees WHERE last_name LIKE'_ $% 'ESCAPE' $'; # (2) between...and keyword # 1. Query employee information SELECT * FROM employees WHERE employee_id BETWEEN 100 AND 120 (3) in keyword # 1. The job number for querying employees is the employee name and job number SELECT last_name,job_id FROM employees WHERE job_id IN ('IT_PROG','AD_VP','AD_PRES') in IT_PROG, AD_VP and AD_PRES; # (4) is null# 1. Querying employee names without bonuses and bonus rate SELECT last_name,commission_pct FROM employees WHERE commission_pct IS NULL;# is null can only be used to judge null values. Security equals can be used to judge both null values and the readability of the normal value # is null. It is recommended to use is nullSELECT last_name,commission_pct FROM employees WHERE commission_pct NULL; conditional query summary instructions (1) filter by conditional operator >

< = >

= where > select > order by (order by is usually placed at the end of the query statement, except for the limit clause (which will be discussed later)

[sort query] # 1. Query employee information and ask for salary order from high to low SELECT * FROM employees ORDER BY salary DESC;# 2. Query the employee information of department number > = 90, and sort [add filter criteria] SELECT * FROM employees WHERE department_id > = 90 ORDER BY hiredate ASC;# 3 by entry time. Display employee's information and annual salary according to the level of annual salary [add expression sort] SELECT *, salary*12* (1+IFNULL (commission_pct,0)) AS annual salary FROM employees ORDER BY salary*12* (1+IFNULL (commission_pct,0)) DESC;SELECT *, salary*12* (1+IFNULL (commission_pct,0)) AS annual salary FROM employees ORDER BY annual salary DESC; # ORDER BY support alias # 4. According to the length of the name, display the employee's name and salary [sorted by function] SELECT LENGTH (last_name) AS byte length, last_name,salary FROM employees ORDER BY byte length DESC;# 5. To inquire about employee information, first by salary ascending order, and then by employee number descending order SELECT * FROM employees ORDER BY salary ASC, employee_id DESC

After learning the sorting query, try to complete the following exercises

Answer:

1. SELECT last_name,department_id,salary*12* (1+IFNULL (commission_pct,0)) AS Annual salary FROM employees ORDER BY Annual salary DESC,last_name ASC

2 、 SELECT last_name,salary FROM employees WHERE salary NOT BETWEEN 8000 AND 17000 ORDER BY salary DESC

3. SELECT * FROM employees WHERE email LIKE'% e% 'ORDER BY LENGTH (email) DESC,department_id ASC

Sort query summary description ascending order by asc descending order by desc

IV. Common functions

Call syntax: select function name (argument list) from table

Concept: similar to the method in Java, a set of logical statements are encapsulated in the method body to expose the interface.

Benefits:

1. Hides the implementation details

two。 Improve code reusability

Classification: divided into one-line functions and grouping functions. The one-line function is divided into character function, mathematical function, date function, system function and flow control function. Grouping functions are used to do statistical functions, also known as statistical functions, aggregate functions, group functions.

One-line function description (1) character function parameter type is character type number of bytes to get parameter value select length ('string') splicing concat (field 1, field 2) case conversion upper, lower intercept substr (index,end) find instr (main string, substring) clear spaces on both sides trim (a from'aaaa string 1aa') fill lpad ('string 1aa') around (' string 1', left fill n Fill character'a'), lpad ('string 1', right fill n, fill character'a') replace replace ('string 1', 'replacement string', 'new string') (2) Mathematical function parameter type is numeric rounding round (decimal, reserved digits) intercepts truncate (decimal) Number of reserved bits) rounding up ceil (values rounded up) and rounding down floor (values rounded downwards) minus mod (nmemm) The result is positive or negative and the remainder n is the same random number rand (); returns the decimal between 0-1 (3) the function parameter returns the current full date select now (); returns the current year, month, day, select curdate (); returns the current hour, minute and second select curtime (); intercepts the specified part of select YEAR (now ()) as year, MONTH (now ()) as month, DAY (now ()) as day String → date STR_TO_DATE ('2020-7-7% YMY% MMI% d') date → string DATE_FORMAT (NOW (),'% Y year% m month% d day') returns the number of days between the two dates datediff (date 1, date 2) (4) the system function comes with the system to view the current version of select version (); view the current database select database (); view the current user select user () Automatically encrypt password ('character'); or md5 ('character'); (5) flow control function analogy Javaifif (bonus IS NULL,' no bonus', 'bonus')

Group function description sum summation avg average max maximum min minimum count count [one-line function] # (1) character function-[parameter type is character] # 1.length gets the number of bytes of parameter value SELECT LENGTH ('john'); SELECT LENGTH (' Zhang Sanfeng'); SHOW VARIABLES LIKE'% char%' # View character set # 2.concat splicing string SELECT CONCAT (last_name,'_',first_name) name from employees # 3.upper, lower case conversion SELECT UPPER ('tom'); SELECT LOWER (' TOM') # change the last name to uppercase and lowercase, and then concatenate the SELECT CONCAT (UPPER (last_name), LOWER (first_name)) name FROM employees;# 4.substr splicing function # the index in mysql starts SELECT SUBSTR ('if you lose a business, what's your name? don't worry', 7) AS out_put;SELECT SUBSTR ('if you lose a business, what's your name? don't worry', 1Pol 3) AS out_put # case: the first character in the name is capitalized, other characters are lowercase, concatenated with _ and displayed with SELECT CONCAT (UPPER (SUBSTR (last_name,1,1)),'_', LOWER (SUBSTR (last_name,2) oup_put FROM employees; # 5.instr character search function # returns the starting index of the substring in the main string, but does not return zero select INSTR ('Fan Dust A Liang','A Liang') AS out_put # 6.trim clears spaces function # removes spaces on both sides of characters SELECT LENGTH (TRIM ('dust') AS out_put;SELECT TRIM ('a 'FROM' aaaaaa aaa dust aaaa') AS out_put;# 7.lpad left fill function # implements left filling with specified characters to fill the specified length SELECT LPAD ('dust', 10 heroines') AS out_put # 8.rpad right fill function # fill the specified length SELECT RPAD with the specified characters ('Fan Dust', 10 minutes') AS out_put;# 9.replace replacement function SELECT REPLACE ('my idol is Lu Xun','Lu Xun', 'Zhou Dongyu') AS oup_put -# (2) Mathematical function-[parameter type For Numeric] # 1.round rounding function SELECT ROUND (1.65) SELECT ROUND (1.567 SELECT ROUND); # 2.ceil takes the entire function up # returns the smallest integer > = this parameter SELECT CEIL (1.00); # 3.floor takes the entire function down # returns 5 scores' greater than', 'less than'); SELECT last_name,commission_pct, IF (commission_pct IS NULL,' has no bonus', 'bonus') AS out_put FROM employees # 2.case function / * method 1: similar to switch-case in Java: case: query employee's salary, require department number = 30, display salary 1.1x department number = 40, show 1.2x department number = 50, show 1.3times salary in other departments, display salary as original wage * / SELECT salary original wage, department_id CASE department_idWHEN 30 THEN salary*1.1WHEN 40 THEN salary*1.2WHEN 50 THEN salary*1.3ELSE salaryEND AS New salary FROM employees / * method 2: similar to multiple if in Java: case: query employee's salary wage > 20000, show A salary > 15000, show B salary > 10000, show C level otherwise, display D level * / SELECT salary,CASEWHEN salary > 20000 THEN 'A'WHEN salary > 15000 THEN' B'WHEN salary > 10000 THEN 'C'ELSE' D'END AS wage grade FROM employees [grouping function] / * SUM summation AVG average MAX maximum MIN minimum COUNT calculated number * / # Comprehensive use of SELECT SUM (salary) "and", AVG (salary) "average", MAX (salary) "maximum", MIN (salary) "minimum", COUNT (salary) "total" FROM employees / * characteristics of grouping functions: 1.sum and avg are generally used to deal with numerical types Max, min, count can handle any type of 2. Grouping functions ignore null values and can be matched with distinct to remove duplicates. The field queried with the grouping function requires that the field after group by 4.count function is often used to count rows. Use count (*) or count (1) or count (constant) efficiency problem: under MYISAM storage engine, count (*) efficiency is high. Under INNODB storage engine, count (*) and count (1) are almost as efficient, but higher than count (field) * /

After learning one-line functions, try to complete the following exercises

Answer:

1. SELECT NOW ()

2. SELECT employee_id,last_name,salary*1.2 "new salary" FROM employees

3. SELECT LENGTH (last_name) "length", SUBSTR (last_name,1) "first character", last_name FROM employees ORDER BY first character

4. SELECT CONCAT (last_name,' earns'), salary,' monthly but wants', salary*3 AS "Dream Salary" FROM employees WHERE salary=24000

5 、

SELECT last_name,job_id AS job

CASE job_id

WHEN 'AD_PRES' THEN' A'

WHEN 'ST_MAN' THEN' B'

WHEN 'IT_PROG' THEN' C'

WHEN 'SA_PRE' THEN' D'

WHEN 'ST_CLERK' THEN' E'

END AS "Grade"

FROM employees

WHERE job_id = "AD_PRES"

After learning the grouping function, try to complete the following exercises:

Answer:

1.SELECT ROUND (MAX (salary), 2) "maximum", ROUND (MIN (salary), 2) "minimum", ROUND (AVG (salary), 2) "average", ROUND (SUM (salary), 2) "sum" FROM employees

2.SELECT DATEDIFF (MAX (hiredate), MIN (hiredate)) "DIFFERNCE" FROM employees

3.SELECT COUNT (*) AS number of employees FROM employees WHERE department_id=90

5. Group query

Syntax:

Select grouping function, query list (required to appear after group by)

From table

[where filter criteria]

List of group by groups

[filtering after grouping of having]

[order by clause]

Execution order: from > where > group by > having > select > order by

Classification filtering source location keyword grouping before filtering the original table group bywhere grouping and filtering the result set group byhaving after the grouping

Note:

1. The query list must be a field that appears after the grouping function and group by.

two。 The condition of the grouping function must be placed in the having clause.

3. Priority can be given to pre-packet filtering.

The 4.group by clause supports a single field grouping, multiple field grouping (multiple fields are separated by commas and no order requirement), expressions, or functions.

5. You can add sorting (sort at the end of the entire grouping query)

6. Aliases are not generally used after group by and having.

# 1. Query the maximum wage of each job SELECT MAX (salary) "maximum wage", job_id "type of work" FROM employees GROUP BY job_id;# 2. Query the number of departments in each location SELECT COUNT (*) "number of departments", location_id "location id" FROM departments GROUP BY location_id;# 3. Query mailbox contains a character, average salary of each department SELECT AVG (salary) "average wage", department_id "department id" FROM employees WHERE email LIKE'% a% 'GROUP BY department_id;# 4. Inquire about the maximum wage of employees with bonuses under each leader SELECT MAX (salary) "maximum wage", manager_id "leadership number" FROM employees WHERE NOT ISNULL (commission_pct) GROUP BY manager_id;# 5. Query the number of employees in which department > ideas: query the number of employees in each department, and then according to the results of which department's number of employees > 2SELECT COUNT (*), department_id FROM employees GROUP BY department_id HAVING COUNT (*) > 2 position # 6. Check the maximum wage number and maximum wage of employees with bonus for each type of work > 12000, SELECT MAX (salary) "maximum wage", job_id "type of work" FROM employees WHERE NOT ISNULL (commission_pct) GROUP BY job_id HAVING MAX (salary) > 12000 position # 7. Inquire which is the minimum wage > 5000 for each employee under the leadership number > 102and its minimum wage SELECT MIN (salary) "minimum wage", manager_id "leadership number" FROM employees WHERE manager_id > 102BW (salary) > 5000th # 8. Group according to the length of employees' names, query the number of employees in each group, screen which SELECT COUNT (*) "number of employees" > 5, LENGTH (last_name) "name length" FROM employees GROUP BY LENGTH (last_name) HAVING COUNT (*) > 5th # 9. Query the average salary of employees in each department for each type of work SELECT AVG (salary) "average wage", department_id "department", job_id "type of work" FROM employees GROUP BY department_id,job_id; # 10. Query the average wages of employees of each type of work in each department, and display SELECT AVG (salary) "average wage", department_id "department", job_id "type of work" FROM employees GROUP BY department_id,job_id ORDER BY AVG (salary) DESC according to the average wage.

After learning the group query, try to complete the following exercises

Answer:

1. SELECT MAX (salary), MIN (salary), AVG (salary), SUM (salary), job_id FROM employees GROUP BY job_id ORDER BY job_id ASC

2. SELECT MAX (salary)-MIN (salary) "DIFFERENCE" FROM employees

3. SELECT MIN (salary), manager_id FROM employees WHERE NOT ISNULL (manager_id) GROUP BY manager_id HAVING MIN (salary) > = 6000

4. SELECT department_id,COUNT (*), AVG (salary) FROM employees GROUP BY department_name ORDER BY AVG (salary) DESC

5. SELECT COUNT (*) "number", job_id FROM employees GROUP BY job_id

VI. Connection query

Concept: the fields of the query come from multiple tables.

Classification: installation age can be divided into sql92 and sq99, according to the function is divided into inner join (intersection), outer join (one table has, another table does not), cross-connection; among them, sql92 only supports inner connection, sql99 supports all except full connection.

Note:

1. If an alias is given to the table, the fields of the query cannot be qualified with the original table name.

two。 When it comes to multi-table query, aliasing the table can effectively improve the conciseness of the statement and facilitate the distinction of multiple fields with duplicate names.

Join queries fall into the following three categories:

(1) Internal connection inner

Equivalent connection

Non-equivalent connection

Self-connection

(2) external connection

Left outside connection left outer

Right external connection right outer

Full external connection full outer

(3) Cross-connect cross

[sql92 Standard] # 1. Equivalent link # query the name of the goddess and the corresponding boyfriend name # SELECT NAME,boyName FROM boys,beauty WHERE beauty.boyfriend_id=boys.id;# 1. Query the employee name and the corresponding department name SELECT last_name "employee name" and department_name "department name" FROM employees,departments WHERE employees.department_id=departments.department_id;# 2. Query the employee name, job number, and job name SELECT last_name,e.job_id,job_title FROM employees edepartment jobs j WHERE e.`job _ id` = j.`job _ id`; # 3. Query the name of the employee with bonus, the name of the department, SELECT last_name,department_name,commission_pct FROM employees eGrad WHERE e.`department _ id` = d.`department _ id`AND e.`commission _ pct` IS NOT NULL;# equivalent connection + filter # 4. Query the department name of the city with the second character o and the city name of SELECT department_name "department name", city "city name" FROM departments ddepartment WHERE d.`location _ id` = l.`location _ id` AND city LIKE'_ o% name; # equivalent connection + grouping # 5. Query the number of departments in each city SELECT COUNT (*) "number of departments", city "city" FROM departments d WHERE d.`location _ id` = l.`location _ id` GROUP BY city;# 6. Query the name of each department with bonus, the leader number of the department, the minimum wage of this department SELECT department_name,d.manager_id,MIN (salary) FROM departments d department WHERE d.`department _ id` = e.`department _ id` AND commission_pct IS NOT NULL GROUP BY department_name,d.manager_id;# 7. Query the name of each type of work, the number of employees, and by the number of employees descending order SELECT job_title,COUNT (*) FROM employees eForce jobs j WHERE e.`job _ id` = j.`job _ id` GOUP BY job_title ORDER BY COUNT (*) DESC;# 8. Support three-table join # query employee name, department name, city SELECT last_name,department_name,city FROM employees eGrades.`department _ id` = d.`department _ id`AND d.`location _ id` = l.`location _ id`; # (2) non-equivalent join / * execute the following statement first to create a new job_ table in the myemployees database. CREATE TABLE job_grades (grade_level VARCHAR (3), lowest_sal INT, highest_sal INT); INSERT INTO job_gradesVALUES ('Atoll, 1000, 2999); INSERT INTO job_gradesVALUES (' Bond, 3000, 5999); INSERT INTO job_gradesVALUES ('Cure, 6000, 9999); INSERT INTO job_gradesVALUES (' Dust, 10000, 14999); INSERT INTO job_gradesVALUES ('Emission, 15000, 24999); INSERT INTO job_gradesVALUES (' Flying, 25000,40000); * / # 1. Query the salary and salary grade of the employee SELECT salary,grade_level FROM employees eJM jobless grades j WHERE salary BETWEEN j.`lowest _ sal`AND j.`highest _ sal`; # (3) self-connect # 1. Query the employee name and the name of its superior. SELECT e.employee_id "employee id", e.last_name "employee name", m.employee_id "Manager id", m.last_name "Manager name" FROM employees eGramManager WHERE e.`manager _ id` = m.`employee _ id`

After learning the sql92 standard connection query, try to complete the following exercises

Answer:

1 、

SELECT last_name,d.department_id,department_name

FROM employees e,departments d

WHERE e.department_id=d.department_id

2 、

SELECT e.job_id,d.location_id FROM employees e,departments d

WHERE d.department_id=e.department_id

AND e.department_id=90

3 、

SELECT last_name, department_name, l.location_id, city

FROM employees e,departments d,locations l

WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND e.commission_pct IS NOT NULL

4 、

SELECT last_name, job_id, d.department_id, department_name

FROM employees e,departments d,locations l

WHERE e.department_id=d.department_id AND d.location_id=l.location_id AND l.cityless Torontos

5 、

SELECT department_name,job_title,MIN (salary)

FROM departments d,employees e,jobs j

WHERE d.department_id=e.department_id AND e.job_id = j.job_id

GROUP BY job_title,department_name

6 、

SELECT COUNT (), country_id FROM departments drecoveryl

WHERE d.location_id=l.location_id

GROUP BY country_id

HAVING COUNT () > 2

7 、

SELECT e1.last_name "employees", e1.employee_id "Emp#", e2.last_name "manager", e2.employee_id "Mgr#"

FROM employees e1,employees e2

WHERE e1.manager_id=e2.employee_id AND e1. Lastworthy nameplates Kochhar'

[sql99 Standard] # (1) equivalent connection # 1. Query the employee name, the department name is SELECT last_name,department_name FROM employees eINNER JOIN departments dON e.departmentaccounid.departmentaccounident # 2. Query name contains e employee name and job name (add filter) SELECT last_name,job_titleFROM employees eINNER JOIN jobs jON e.job_id=j.job_idWHERE last_name LIKE'% e%'OR job_title LIKE'% e% candidate # 3. Query the number of cities with more than 3 departments and the number of departments (grouping + screening) SELECT city,COUNT (*) "number of departments" FROM departments dINNER JOIN locations lON d.location_id=l.location_idGROUP BY cityHAVING COUNT (*) > 3 * 4. Query the department name and the number of employees of which department has more than 3 employees, and descend (sort) SELECT department_name "department name", COUNT (*) "number of employees" FROM departments dINNER JOIN employees eON d.department_id=e.department_idGROUP BY department_nameHAVING COUNT (*) > 3ORDER BY COUNT (*) DESC;# 5. Query employee name, department name, job name, and sort SELECT last_name "employee name", department_name "department name", job_title "job name" FROM employees eINNER JOIN departments d ON d.department_id=e.department_idINNER JOIN jobs j ON e.job_id=j.job_idORDER BY department_name by department name; # (2) non-equivalent connection # query employee salary level SELECT salary,grade_levelFROM employees eJOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_sal # query the number of each salary grade > 20, and arrange SELECT COUNT (*), grade_levelFROM employees eJOIN job_grades j ON e.`salary` BETWEEN j.lowest_sal AND j.highest_salGROUP BY grade_levelHAVING COUNT (*) > 20ORDER BY grade_level DESC according to the descending order of the wage grade # (3) self-connect # query employee's name, superior's name SELECT e1.last_name "employee's name", e2.last_name 's "superior's name" FROM employees e1JOIN employees e2 ON e1.manager_id=e2.employee_id -# II. External connection # 1. Query the name of the goddess whose boyfriend is not in the male god table # left outer connection SELECT NAMEFROM beauty LEFT OUTER JOIN boys ON beauty.boyfriend_id=boys.idWHERE boys.id IS NULL;# right outer connection SELECT NAMEFROM boys RIGHT OUTER JOIN beauty ON beauty.boyfriend_id=boys.idWHERE boys.id IS NULL;# 2. Query the department that has no employees, SELECT. Departmentationnameamino. EmployeeSecretidFROMFROM departments d LEFT JOIN employees e ON d.department_id=e.department_idWHERE e.manager_id IS NULL;SELECT * FROM employees WHERE employee_id=100;# 3. Full external connection (not supported) # full external connection is the combination of USE girls;SELECT b.complete external connection bo.boyfriend from beauty bFULL JOIN boys boON b.boyFriendship roomidconnectivity bo.idente. Cross-connect # uses the Cartesian product implemented in the 99 standard, using cross instead of 92, SELECT b.

After learning the sql99 standard connection query, try to complete the following exercises

Answer:

I.

SELECT b.name _ Bo.

FROM beauty b LEFT JOIN boys bo

ON b.boyfriend_id=bo.id

WHERE b.id > 3

II.

SELECT city "city", department_name "city name"

FROM departments d RIGHT JOIN locations l

ON d.location_id=l.location_id

WHERE d.department_id IS NULL

3.

SELECT d.departmentaccountname.*

FROM departments d LEFT JOIN employees e

ON d.department_id=e.department_id

WHERE d.department_name IN ('SAL','IT')

7. Subquery

Meaning: the select statement that appears in other statements is called subquery or inner query; the external query statement is called main query or external query.

Classify by the location where the subquery appears:

After select (only scalar quantum queries are supported)

After from (table subquery is supported)

After where or having (support for scalar, row, column subqueries) ☆☆☆

After exists (also called related subquery, which supports table subquery)

Sort by the number of rows and rows of the result set:

Scalar quantum query (result set has only one row and one column)

Column subquery (result set has only one column with multiple rows)

Row subquery (result set has one row and multiple columns)

Table subquery (result set is usually multi-row and multi-column)

[subquery after where and having] (support scalar, row, column subquery) # 1. A single scalar quantum query # queries the name of an employee whose salary is higher than Abel's SELECT last_name,salary FROM employees WHERE salary > (SELECT salary FROM employees WHERE last_name='Abel'); # 2. Multiple Quantum query # returns the name, job_id and salary of employees whose job_id is the same as 141and whose salary is more than employee 143s. SELECT last_name,job_id,salary FROM employeesWHERE job_id= (SELECT job_id FROM employeesWHERE employee_id=141) AND salary > (SELECT salary FROM employeesWHERE employee_id=143); # 3. Scalar Quantum query + grouping function # returns the last_name, job_id and salarySELECT last_name,job_id,salary FROM employees WHERE salary= (SELECT MIN (salary) FROM employees) of the employee with the lowest salary; # 4. Standard quantum query + having clause # query the minimum wage department id and its minimum wage SELECT department_id,MIN (salary) FROM employees GROUP BY department_idHAVING MIN (salary) > (SELECT MIN (salary) FROM employees WHERE department_id=50); # 5. Column subquery (multi-row subquery) # returns the names of all employees in departments where location_id is 1400 or 1700. Select last_name FROM employeesWHERE department_id IN (SELECT DISTINCT department_id FROM departments WHERE location_id IN (1400 last_name FROM employeesWHERE department_id IN)); # returns the job number, name, job_id, and salarySELECT employee_id,last_name,job_id,salary FROM employeesWHERE salarya.ag of employees in other jobs who are paid less than any one of job_id 's' IT_PROG' jobs.

4. Idea: ① queries the department of the employee whose name contains the letter u. ② queries the job number and name of any one of the employees in ①.

SELECT employee_id "employee number", last_name "name"

FROM employees

WHERE department_id IN (

SELECT DISTINCT department_id

FROM employees

WHERE last_name LIKE'% u%'

);

5. Idea: ① query location_id=1700 department ② query the employee number of employees working in ① department

SELECT employee_id

FROM employees

WHERE department_id IN (

SELECT DISTINCT department_id

FROM departments

WHERE location_id=1700

);

6. Idea: ① looks up the employee number named K_ing, ② queries the name and salary of manager_id= ①

SELECT last_name "employee name", salary "salary"

FROM employees

WHERE manager_id IN (

SELECT employee_id

FROM employees

WHERE last_name='K_ing'

);

7. Idea: ① query maximum wage ② query salary = ① surname. Name

SELECT CONCAT (first_name,last_name) "first name."

FROM employees

WHERE salary= (SELECT MAX (salary) FROM employees)

If you don't find it enjoyable, you can continue to challenge the following subquery classic cases:

Answer:

1. Idea: ① query minimum wage ② query wage = ① last_name, salary

SELECT last_name, salary

FROM employees

WHERE salary= (SELECT MIN (salary) FROM employees)

2. Idea 1: ① query the average wage of each department; ② query the minimum average wage on the ① result; ③ query which department's average wage = ②; ④ query department information

SELECT d.*

FROM departments d

WHERE d.departmentroomid= (

SELECT department_id

FROM employees

GROUP BY department_id

HAVING AVG (salary) = (

SELECT MIN (ag)

FROM (

SELECT AVG (salary) ag,department_id

FROM employees

GROUP BY department_id

) a

)

);

Idea 2: ① uses sorting to find the department number of the minimum average wage, ②, to query department information.

SELECT * FROM departments

WHERE department_id= (

SELECT department_id

FROM employees

GROUP BY department_id

ORDER BY AVG (salary) ASC

LIMIT 1

);

3. Idea: ① inquires the average wage of each department; ② queries the department number of the minimum average wage.

SELECT d.*, ag

FROM departments d

JOIN (

SELECT AVG (salary) ag,department_id

FROM employees

GROUP BY department_id

ORDER BY AVG (salary) ASC

LIMIT 1

) a

ON d.department_id=a.department_id

4. Idea: ① query job's job_id; ② with the highest average salary, query job information

SELECT *

FROM jobs

WHERE job_id= (

SELECT job_id

FROM employees

GROUP BY job_id

ORDER BY AVG (salary) DESC

LIMIT 1

);

5. Idea: ① inquires the average salary of the company; ② queries the average salary of each department; finally, the average salary in ② > ①

SELECT AVG (salary), department_id

FROM employees

GROUP BY department_id

HAVING AVG (salary) > (

SELECT AVG (salary)

FROM employees

);

6. Idea: ① query has the employee number of manager; details of ② query number in ①

SELECT * FROM employees

WHERE employee_id IN (SELECT DISTINCT manager_id FROM employees)

7. Idea: ① inquires which department has the lowest highest wage in each department. Id; ② inquires the minimum wage of ① department.

SELECT MIN (salary) FROM employees WHERE department_id= (

SELECT department_id

FROM employees

GROUP BY department_id

ORDER BY MAX (salary) ASC

LIMIT 1

);

8. Idea: ① queries the department number with the highest average salary; ② connects employees and departments, and the filter condition is ①.

SELECT last_name, d.department_id, email,salary

FROM employees e

INNER JOIN departments d ON d.manager_id=e.employee_id

WHERE d.departmentroomid= (

SELECT department_id

FROM employees

GROUP BY department_id

ORDER BY AVG (salary) DESC

LIMIT 1

)

8. Paging query

Application scenario: when the data to be displayed is incomplete in one page, the sql request needs to be submitted in pages.

Syntax:

Select query list

From table

[table 2 of join type join

On connection condition

Where filter condition

Group by grouping field

Filtering after grouping of having

Order by]

Limit offset,size

Offset: the starting index starting from 0. If omitted, it starts from the first by default.

Size: number of entries to display

Execution order: from > join > on > where > group by > having > select > order by > limit (limit syntax and execution are last)

Limit paging formula:

Select * from table limit (page-1) * size,size; (the number of pages to be displayed is page and the number of entries per page is size)

# 1. Inquire about the first five items of employee information SELECT * FROM employees LIMIT 5. Inquire about articles 11 to 25 SELECT * FROM employees LIMIT 10 minutes 3. Information about employees with bonuses and showing the top 10 SELECT * FROM employeesWHERE commission_pct IS NOT NULLORDER BY salaryLIMIT 10 with higher salaries

IX. Joint inquiry

Definition: merges the results of multiple query statements into one result.

Syntax: query statement 1 union query statement 2 unin... Query statement n

Application scenario: when the results to be queried come from multiple tables that do not have a join relationship, but the query information is the same, it is most suitable to use union.

Note:

The number of query columns required for multiple query statements is the same.

The type and order of each column queried by multiple query statements are required to be the same.

Union deduplicates by default, and can be turned off if union all is used.

In most cases, union is more efficient than or.

# 1. Query department number > 90 or the mailbox contains employee information SELECT * FROM employees WHERE department_id > 90 OR email LIKE'% a% select * FROM employees WHERE department_id > 90 UNION SELECT * FROM employees WHERE email LIKE'% a%' This is the end of the article on "how to write DQL data query sentences in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.