DQL data query language 2

DQL data query language

multi-table query

Cartesian set

Generation conditions:
Omit connection conditions
Invalid connection condition
All rows in all tables are connected to each other

select * from t_mysql_beauty,t_mysql_boys

Equivalence / connection query

Querying data in multiple tables using joins
Write the connection condition in the WHERE clause.
When there are the same columns in the table, prefix the column name with the table name

Distinguish duplicate column names
Columns with the same column name in different tables can use the alias of the table
To differentiate.
If a table alias is used, you need to use the
Replace table name with table alias
Table aliases support a maximum of 32 characters in length, but the less recommended the
good

Alias of table
Use aliases to simplify queries.
Using table name prefixes can improve execution efficiency.

Connect multiple tables
Connect n tables, at least n-1 connection conditions are required.

join query

Meaning: also known as multi table query. When the query field comes from multiple tables, it will use join query

Example:
SELECT last_name,department_name
FROM t_mysql_employees,departments
WHERE t_mysql_employees.`department_id`=t_mysql_departments.`department_id`;

Alias table

Improve the conciseness of statements
Distinguish multiple fields with duplicate names

Example:
select b.`name`,s.boyName  from t_mysql_beauty b,t_mysql_boys s

Plus screening

Example:
SELECT department_name,city
FROM t_mysql_departments t_mysql_d,locations l
WHERE d.`location_id` = l.`location_id`
AND city LIKE '_o%';

Add group

SELECT COUNT(*) number,city
FROM t_mysql_departments d,t_mysql_locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY city;

Non equivalent connection

Example:
SELECT salary,grade_level
FROM t_mysql_employees e,t_mysql_job_grades g
WHERE salary BETWEEN g.`lowest_sal` AND g.`highest_sal`
AND g.`grade_level`='A';

Self connect

Example:
SELECT e.employee_id,e.last_name,m.employee_id,m.last_name
FROM t_mysql_employees e,t_mysql_employees m
WHERE e.`manager_id`=m.`employee_id`;

join link

Internal connection

Syntax: select query list from table 1 alias inner join table 2 alias on connection condition;

Example:
#Case 1. Query employee name and department name

SELECT last_name,department_name
FROM t_mysql_departments d
 JOIN  t_mysql_employees e
ON e.`department_id` = d.`department_id`;

Add filter

Example:
SELECT last_name,job_title
FROM t_mysql_employees e
INNER JOIN t_mysql_jobs j
ON e.`job_id`=  j.`job_id`
WHERE e.`last_name` LIKE '%e%';

Add group

SELECT city,COUNT(*) Number of departments
FROM t_mysql_departments d
INNER JOIN t_mysql_locations l
ON d.`location_id`=l.`location_id`
GROUP BY city
HAVING COUNT(*)>3;

Add sort

Example:
SELECT COUNT(*) number,department_name
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d
ON e.`department_id`=d.`department_id`
GROUP BY department_name
HAVING COUNT(*)>3
ORDER BY COUNT(*) DESC;

Three meter connection

SELECT last_name,department_name,job_title
FROM t_mysql_employees e
INNER JOIN t_mysql_departments d ON e.`department_id`=d.`department_id`
INNER JOIN t_mysql_jobs j ON e.`job_id` = j.`job_id`
ORDER BY department_name DESC;

Self connect

Example:
 SELECT e.last_name,m.last_name
 FROM t_mysql_employees e
 JOIN t_mysql_employees m
 ON e.`manager_id`= m.`employee_id`;

External connection

Application scenario: used to query records in one table and not in another
characteristic:
1. Query results of external connections are all records in the main table
If there is a match from the table, the matching value is displayed
Show null if there is no match from the table
External connection query result = internal connection result + records in the main table but not in the slave table
2. Left outer join, left join the main table on the left
right join the right outer join the right main table
3. The same effect can be achieved by exchanging the order of two tables outside the left and outside the right
4. Total external connection = result of internal connection + one in Table 1 but not in Table 2 + one in Table 2 but not in Table 1

Left outer connection

  SELECT d.*,e.employee_id
 FROM t_mysql_departments d
 LEFT OUTER JOIN t_mysql_employees e
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;

Right outer connection

SELECT d.*,e.employee_id
 FROM t_mysql_employees e
 RIGHT OUTER JOIN t_mysql_departments d
 ON d.`department_id` = e.`department_id`
 WHERE e.`employee_id` IS NULL;

Total external connection

Common functions

Character function

effect function result
To lowercase LOWER('SQL Course') sql course
Capitalize UPPER('SQL Course') SQL COURSE
Splicing CONCAT('Hello', 'World') HelloWorld
intercept SUBSTR('HelloWorld',1,5) Hello
length LENGTH('HelloWorld') 10
Index value for character INSTR('HelloWorld', 'W') 6
Second half of character truncation TRIM('H' FROM 'HelloWorld') elloWorld
Character substitution REPLACE('abcd','b','m') amcd

2. Digital function

effect function result
rounding ROUND(45.926, 2) 45.93
truncation TRUNC(45.926, 2) 45.92
To find redundancy MOD(1600, 300) 100

3. Date function

effect function result
Get current date now()
Convert characters in date format to date in specified format STR_TO_DATE('9-13-1999','%m-%d-%Y') 1999-09-13
Convert date to character DATE_FORMAT('2018 / 6 / 6 ','% Y% m% d ') June 6, 2018

Common functions

Concept: a java like method that encapsulates a set of logical statements in the method body and exposes the method name
Benefits: 1. Hidden implementation details 2. Improved code reusability
Call: select function name (argument list) [from table];
characteristic:
① What is the name (function name)
② What to do (function function function)
Classification:
1. Single line function
Such as concat, length, ifnull, etc
2. Grouping function
Function: used for statistics, also known as statistical function, aggregate function and group function

Subquery

meaning:
select statements that appear in other statements are called subqueries or inner queries
External query statement, called main query or external query

1, Behind where or having

Example: whose wage ratio Abel high?
1,query Abel 's salary
SELECT salary
FROM t_mysql_employees
WHERE last_name = 'Abel'

2,Query employee information to meet salary>①result
SELECT *
FROM t_mysql_employees
WHERE salary>(
	SELECT salary
	FROM t_mysql_employees
	WHERE last_name = 'Abel'
);

Column subquery (multiline subquery)

Example: Return location_id Is the name of all employees in a 1400 or 1700 Department
1,query location_id Is the department number of 1400 or 1700
SELECT DISTINCT department_id
FROM t_mysql_departments
WHERE location_id IN(1400,1700)

2,Query the employee's name and ask the department number to be①One of the lists
SELECT last_name
FROM t_mysql_employees
WHERE department_id  <>ALL(
	SELECT DISTINCT department_id
	FROM t_mysql_departments
	WHERE location_id IN(1400,1700)
);

Row subquery (one row, multiple columns or multiple rows, multiple columns in result set)

Example: query employee information with the lowest employee number and the highest salary
SELECT * 
FROM t_mysql_employees
WHERE (employee_id,salary)=(
	SELECT MIN(employee_id),MAX(salary)
	FROM t_mysql_employees
);

2, After select

Only scalar subqueries are supported

Example: query the number of employees in each department
SELECT d.*,(
	SELECT COUNT(*)
	FROM t_mysql_employees e
	WHERE e.department_id = d.`department_id`
 )Number
 FROM t_mysql_departments d;

3, from behind

Alias is required to use subquery results as a table

Example: query the wage grade of the average wage of each department
1,Query the average salary of each department
SELECT AVG(salary),department_id
FROM t_mysql_employees
GROUP BY department_id
SELECT * FROM t_mysql_job_grades;
2,Connect 1 result set and job_grades Table, filter average wage between lowest_sal and highest_sal

SELECT  ag_dep.*,g.`grade_level`
FROM (
	SELECT AVG(salary) ag,department_id
	FROM t_mysql_employees
	GROUP BY department_id
) ag_dep
INNER JOIN job_grades g
ON ag_dep.ag BETWEEN lowest_sal AND highest_sal;

4, After exists (related subqueries)

Syntax: exists (complete query statement)
Result: 1 or 0

Example: querying Department names with employees
SELECT department_name
FROM t_mysql_departments d
WHERE d.`department_id` IN(
	SELECT department_id
	FROM t_mysql_employees
)

Paging query

Application scenario: when the data to be displayed is incomplete on one page, you need to submit sql requests in pages
Syntax:
select query list
from table
[join type join table 2
on connection conditions
where filter criteria
group by group field
Screening after having grouping
order by field]
limit [offset,]size;
offset to display the starting index of the entry (starting from 0)
size number of entries to display
characteristic:
① Put the limit statement at the end of the query statement
② Formula
Number of pages to display page, number of entries per page size
select query list
from table
limit (page-1)*size,size;

Example: query the first five employee information
SELECT * FROM  t_mysql_employees LIMIT 0,5;
SELECT * FROM  t_mysql_employees LIMIT 5;

Keywords: SQL less Java

Added by nocniagenti on Thu, 18 Jun 2020 08:26:10 +0300

Popular Keywords

©2024 Programming VIP