DQL data query language

Posted by timon on Wed, 17 Nov 2021 11:46:02 +0100

DQL data query language

1, SQL overview

1. What is SQL

* * SQL (Structured Query Language) * * is a "structured query language", which is an operation language for relational databases. It can be applied to all relational databases, such as MySQL, Oracle, SQL Server, etc.

Although SQL can be used in all relational databases, many databases have their own set of syntax. For example, the LIMIT paging statement in MySQL is a unique dialect of MySQL, which is not supported by other databases. Of course, Oracle or SQL Server also has its own syntax.

2. SQL syntax requirements

1). SQL statements can be written in one or more lines, ending with semicolons;

2) . spaces and indents can be used to enhance the readability of statements;

3) . the keywords are case insensitive, and it is recommended to use uppercase;

3. SQL classification

1). **DQL (Data Query Language): a Data Query Language used to query records (data)**

2) DML (Data Manipulation Language): Data Manipulation Language used to define insert / update / delete of database records (data)

3). DDL (Data Definition Language): Data Definition Language, which is used to define database objects: library, table, column, etc. create / drop / alter/desc

4). DCL (Data Control Language): Data Control Language used to define access rights and security levels

5). TCL (Transaction Control Language): transaction control language, transaction commit, transaction rollback, rollback, savepoint

2, DQL data query language

2.1 concept

DQL (Data Query Language): a Data Query Language used to query records (data) and perform * * select operations**

DQL is a data query language. When the database executes DQL statements, it will not change the data, but let the database send the result set to the client.

2.2 syntax

SELECT 

selection_list  /*Column name to query*/

FROM 

table_list   /*Table name to query*/

WHERE 

condition /*Line condition*/

GROUP BY 

grouping_columns /*Group results*/

HAVING 

condition /*Grouped row conditions*/

ORDER BY

sorting_columns /*Sort results*/

LIMIT 

offset_start, row_count /*Result qualification (paging)*/

2.3 preparation - create table structure

Execute the initialization script employee.sql to create and insert the following data.

Employee table: employees

Field name	Field type	explain
employee_id	int(6)	Employee No., non empty, primary key, self growth
first_name	varchar(20)	Employee last name
last_name	varchar(25)	Employee name
email	varchar(25)	mailbox
phone_number	varchar(20)	Telephone
job_id	varchar(10) string type	Type of work number, foreign key is associated with the primary key in the type of work table
salary	double(10,2) floating point type	a monthly salary
commission_pct	double(4,2) has 4 significant digits and 2 decimal places	Bonus rate
manager_id	int(6)	Leader number, derived from employee_id in this table
department_id	int(4)	Department number, the foreign key is associated with the primary key in the Department table
hiredate	Datetime datetime type	Entry time

Department table: departments

Field name	Field type	explain
department_id	int(4) integer	Department No., non empty, primary key, self growth
department_name	varchar(3) string type	Department name
manager_id	int(6)	Department leader No
location_id	int(4)	Location number, foreign key, primary key in associated area table

Type of work: jobs

Field name	Field type	explain
job_id	varchar(10) string type	Type of work No., non empty, primary key
job_title	varchar(35)	Name of work type
min_salary	int(6) integer	minimum wage
max_salary	int(6)	Maximum wage

Location table: locations

Field name	Field type	explain
location_id	int(11) integer	Location number, non empty, primary key, self growth
street_address	varchar(40) string type	Street address
postal_code	varchar(12)	Zip code
city	varchar(30)	city
state_province	varchar(25)	State / Province
country_id	varchar(2)	Country number

3, Basic query

3.1 syntax

select Query list from Table name;

among select The following query list can be composed of multiple parts separated by commas, for example:
select Field 1,Field 2,expression from surface;

3.2 execution sequence

from clause
select clause

3.3 query list

Fields, expressions, constants, functions, etc

3.4 cases

-- 1, Query constant
select 12;
-- 2, Query expression
select null+1;
select null+null;
-- 3, Query a single field
select first_name from employees;
-- 4, Query multiple fields
select first_name , last_name , department_id from employees;
-- 5, Query all fields
select * from employees;
-- 6, Query function (call the function to get the return value)
select concat(first_name , ' # ' , last_name) from employees;
-- 7, Alias query fields
#Method 1: use as keyword
select concat(first_name , ' & ' , last_name) as name from employees;
#Method 2: use spaces
select concat(first_name , ' $ ' , last_name) 'full name' from employees;
-- VIII+Role of
-- 1.Both operands are numeric and perform an addition operation
-- 2.If one of the operands is character and the other is numeric, the character data is forced to numeric,If it cannot be converted, it will be directly treated as 0
-- 3.One of the operands is null，The result is null
SELECT salary 'salary',commission_pct 'Commission rate', salary*commission_pct 'Commission' from employees;
-- IX distinct To remove duplicate records
#Demand: query the department number involved by the employee
SELECT distinct department_id from employees;
-- 10, View table structure
desc	employees;

4, Condition query

4.1 syntax

select  Query list   from  Table name   where Screening conditions;

4.2 execution sequence

from clause
where clause
select clause

4.3 cases

-- 1, Filter relational operators by relational expression:>  <  >=  <=  =  <>    Supplement: it can also be used!=,But not recommended
#Case 1: query employee information whose department number is not 100
select employee_id , first_name , salary ,department_id from employees where department_id <> 100;

#Case 2: query the name and salary of employees with salary < 15000
select  first_name,salary 
from employees
where salary < 15000;
-- 2, Filter logical operators by logical expression: and  or   not   Supplement: it can also be used&&  ||   !  ，But not recommended
#Case 1: query employee name, department number and e-mail address whose department number is not between 50-100
select  first_name,department_id ,email
from	employees
where department_id < 50 or department_id >100;

#Case 2: query employee information with bonus rate > 0.03 or employee number between 60-110
select first_name, department_id , employee_id ,commission_pct
from employees
where (commission_pct>0.03) or (employee_id >=60 and employee_id <=110);
-- Three, fuzzy query like/not like 
#Case 1: query employee information with character a in name
select first_name , salary from employees where first_name like '%a%';
#Case 2: query the employee information whose last character is e in the name
select first_name  from employees where first_name like '%e';
#Case 3: query the employee information whose first character is e in the name
select first_name  from employees where first_name like 'e%';
#Case 4: query the employee information whose name contains the third character x
select last_name  from employees where last_name like '__x%';
#Case 5: query the employee information with the second character in the name
-- \Slashes can escape the following_Underline represents here_Is an ordinary underline
select last_name  from employees where last_name like '_\_%';
-- escape The following characters represent this sql Escape symbols used in,On behalf of here_Is an ordinary underline
select last_name  from employees where last_name like '_#_%' escape '#';


-- 4, Query whether the value of a field belongs to the specified list  in/not in
#Case 1: query the employee name and department number whose department number is 30 / 50 / 90
select first_name , department_id
from employees
where department_id in(30,50,90);

#Case 2: query the employee information whose job number is not SH_CLERK or IT_PROG
select * from employees where job_id <> 'SH_CLERK' and job_id <> 'IT_PROG';
select * from employees where job_id not in('SH_CLERK' , 'IT_PROG');

-- 5, Judge whether the value of a field is within the specified range  between and/not between and
#Case 1: query the department number and employee name whose department number is between 30-90
select first_name , department_id
from employees
where department_id BETWEEN 30 and 90;

#Case 2: query the name, salary and annual salary of employees whose annual salary is not between 100000-200000
select first_name , commission_pct from employees;
select first_name , IFNULL(commission_pct,0) from employees;

select first_name , salary , salary*12 'Annual salary'
from employees
where salary*(1+IFNULL(commission_pct,0))*12 not BETWEEN 100000 and 200000;

-- 6, Query is null field  is null ， Query is not null field  is not null
#Case 1: query employee information without bonus
select first_name  from employees where commission_pct is null;

#Case 2: query employee information with bonus
select first_name  from employees where commission_pct is not null;

select * from employees where salary <> 9000.0;

4.4 IFNULL function with special usage

Because the types of salary and commission_pct columns are numeric, they can be calculated. If a field in salary or commission_pct is not numeric, an error will occur.

The value of many records in the commission_pct column is NULL. Because the result of adding anything to NULL is still NULL, the settlement result may be NULL.

Requirement: use the function IFNULL() to convert NULL to value 0:

#Ifnull (expression 1, expression 2)
/*
Expression 1: field or expression that may be null
 Expression 2: if expression 1 is null, the value displayed in the final result

Function: if expression 1 is null, expression 2 is displayed; otherwise, expression 1 is displayed
*/
SELECT commission_pct,IFNULL(commission_pct,0) FROM employees;

5, Sort query order by

5.1 syntax

select   Query list  from Table name  where Screening conditions  order by Sort list

5.2 execution sequence

from clause
where clause
select clause
order by clause

5.3 usage

1. A sorted list can be a single field, multiple fields, expressions, functions, number of columns, and combinations of the above

2. Ascending order, through asc, default behavior
Descending by desc

5.4 cases

-- 1, Sort by single field
#Case 1: ascending the employee information with employee No. > 120
select *
from employees
where  employee_id>120
order by salary;

#Case 2: employee information with employee No. > 120 in descending order of salary
select *
from employees
where  employee_id>120
order by salary desc;
-- 2, Sort by expression
#Case: for employees with bonuses, the annual salary is in descending order
select first_name , salary , commission_pct ,salary*12 'Annual salary'
from employees
where commission_pct is not null
order by salary*12 desc;


#Case: for employees with bonuses, the total income is in descending order
select first_name , salary , commission_pct , salary*(1+IFNULL(commission_pct,0))*12 'Total revenue'
from employees
where commission_pct is not null
order by salary*(1+IFNULL(commission_pct,0))*12 desc;

-- 3, Sort by alias
#Case: for employees with bonuses, the annual salary is in descending order
select first_name , salary , commission_pct , salary*12 'Annual salary'
from employees
where commission_pct is not null
order by 'Annual salary' desc;


-- 4, Sort by result of function
#Case: ascending by the number of characters and length of names
select first_name , char_length(first_name) 
from employees
order by char_length(first_name) ;

-- 5, Sort by multiple fields
#Case: query employee's name, salary and department number, first in ascending order of salary, and then in descending order of department number
select first_name , salary ,department_id 
from employees
order by salary , department_id desc;

-- 6, Sort by number of columns(No requirements)
#Cases: sorted by the second column
select * from employees order by 2;

6, Common functions

Function: similar to the "method" learned in java, in order to solve a problem, a series of commands are encapsulated together, and only the function name is exposed for external calls.

6.1 string function

1) 2. Common functions

String function name	Function description
CONCAT(S1,S2,...,Sn)	Connect S1,S2,..., Sn as a string
CONCAT(s, S1,S2,...,Sn)	The same as the CONCAT(s1,s2,...) function, but add s between each string
CHAR_LENGTH(s)	Returns the number of characters in the string s
LENGTH(s)	Returns the number of bytes of string s, which is related to the character set
INSERT(str, index , len, instr)	Replace the string str with the string instr from the index position and the substring len characters long
UPPER(s) or UCASE(s)	Converts all letters of the string s to uppercase letters
LOWER(s) or LCASE(s)	Converts all letters of the string s to lowercase
LEFT(s,n)	Returns the leftmost n characters of string s
RIGHT(s,n)	Returns the rightmost n characters of string s
LPAD(str, len, pad)	Fill the leftmost part of str with the string pad until the length of str is len characters
RPAD(str ,len, pad)	Fill the rightmost part of str with the string pad until the length of str is len characters
LTRIM(s)	Remove the space to the left of the string s
RTRIM(s)	Remove the space to the right of the string s
TRIM(s)	Remove the spaces at the beginning and end of the string s
TRIM([BOTH ]s1 FROM s)	Remove s1 from the beginning and end of string s
TRIM([LEADING]s1 FROM s)	Remove s1 at the beginning of string s
TRIM([TRAILING]s1 FROM s)	Remove s1 at the end of string s
REPEAT(str, n)	Returns the result of str repeated n times
REPLACE(str, a, b)	Replace all occurrences of string a in string str with string b
STRCMP(s1,s2)	Compare strings s1,s2
SUBSTRING(s,index,len)	Returns len characters from the index position of string s

2) . cases

#1, Character function
-- 1,CONCAT Splice character
#Concat (S1, S2,..., Sn) connects S1, S2,..., Sn as a string
select concat(first_name ,'&' ,last_name) as name from employees;
-- 2,LENGTH Get byte length
select length('abc');-- 3
select length("Hello");-- 6
-- 3,CHAR_LENGTH Get the number of characters
select CHAR_LENGTH('abc');-- 3
select CHAR_LENGTH('Hello'); -- 2

-- 4,SUBSTRING Intercept the substring. Note: the starting index starts from 1
#Substring (STR, pos, len) STR is the string to be intercepted. The initial subscript of pos is intercepted to the end by default
select first_name , substring(first_name,2,3) from employees;
#Substring (STR, pos, len) STR is the string to be intercepted. The initial subscript of pos is intercepted to the end by default
select first_name , substring(first_name,2) from employees;

-- 5,INSTR Gets the index of the first occurrence of a character
#INSERT(str,substr) queries the index position where the substring sunstr appears in the string str
select INSERT(first_name,'a') from employees;

-- 6,TRIM Remove the specified characters before and after. The default is to remove spaces
#Trim(s) removes the spaces at the beginning and end of the string s
select concat('###','	aaa		','###'),concat('###',trim('	aaa '),'###'); 

-- 7,LPAD/RPAD  padding-left /Right fill
select lpad('aaa',10,'#');

-- 8,UPPER/LOWER  Capitalize/Make lowercase
#Case: query the name in the employee table. The required format is: the first character of the last name is capitalized, other characters are lowercase, all characters of the first name are capitalized, and the last name and the first name are in uppercase_ Split, and finally alias "OUTPUT"
select upper(first_name) from employees;
select lower(first_name) from employees;

select first_name ,last_name,concat(upper(substring(last_name,1,1)),lower(substring(last_name,2)),'_',upper(first_name))'OUTPUT' from employees;

-- 9,STRCMP Compare two character sizes
select strcmp('aaa','bbb');

-- 10,LEFT/RIGHT  Intercept substring
select left('aaabbb',3);
select right('aaabbb',3);

6.2 mathematical functions

1) 2. Common functions

Mathematical function name	Function description
ABS(x)	Returns the absolute value of x
CEIL(x)	Returns the smallest integer value greater than x
FLOOR(x)	Returns the maximum integer value greater than x
MOD(x,y)	Returns the modulus of x/y
RAND(x)	Return random values from 0 to 1
ROUND(x,y)	Returns the value of the parameter x rounded to y decimal places
TRUNCATE(x,y)	Returns the result of truncating the number x to y decimal places
SQRT(x)	Returns the square root of x
POW(x,y)	Returns the y power of x

2) . cases

#2, Mathematical function
-- 1,ABS absolute value
select abs(-12),abs(0),abs(10);
-- 2,CEIL Round up return>=The minimum integer for this parameter
select ceil(12.3),ceil(12.7),ceil(-12.5);

-- 3,FLOOR Round down and return<=The maximum integer for this parameter
select floor(12.3),floor(12.7),floor(-12.5);

-- 4,ROUND rounding
select round(12.2),round(12.5),round(12.45,1);

-- 5,TRUNCATE truncation
select truncate(12.2,0),truncate(12.5,0),truncate(12.45,1);

-- 6,MOD Surplus
select mod(10.3);

6.3 date time function

1) 2. Common functions

Date time function	Function description
Current() or CURRENT_DATE()	Returns the current date
CURTIME() or CURRENT_TIME()	Returns the current time
NOW()	Returns the current system date and time
SYSDATE()
CURRENT_TIMESTAMP()
LOCALTIME()
LOCALTIMESTAMP()

YEAR(date)	Returns the year of the specified time
MONTH(date)	Returns the month of the specified time
DAY(date)	Returns the day of the specified time
HOUR(time)	Returns the hour of the specified time
MINUTE(time)	Returns the minutes of the specified time
SECOND(time)	Returns the second of the specified time

WEEK(date)	Returns the week ordinal of the year
WEEKOFYEAR(date)

DAYOFWEEK()	Returns the current day of the week. Note: Sunday is 1 and Monday is 2,... Saturday is 7
WEEKDAY(date)	Returns the day of the week for the specified time. Note that week 1 is 0 and week 2 is 1,... Sunday is 6
DAYNAME(date)	Returns the day of the week for the specified time: MONDAY,TUESDAY... SUNDAY

MONTHNAME(date)	Returns the month of the specified time: January,.....

DATEDIFF(date1,date2)	Returns the date interval from date1 to date2
TIMEDIFF(time1, time2)	Returns the interval between time1 - time2

DATE_ADD(datetime, INTERVALE expr , type)	Returns the date and time of the INTERVAL period different from the given date and time

DATE_FORMAT(datetime ,fmt)	Format the date datetime value according to the string fmt
STR_TO_DATE(str, fmt)	str is parsed according to the string fmt to a date

2) . cases

#3, Date function
-- 1,NOW  Current system time
select SYSDATE();
select now();

select YEAR(now()) as year; 
select MONTH(now()) as month;
select DAY(now()) as day;
select HOUR(now()) as hour; 
select MINUTE(now()) as minute; 
select SECOND(now()) as second; 


-- WEEKDAY(date)Returns the day of the week for the specified time. Note that week 1 is 0 and week 2 is 1,... Sunday is 6
select WEEKDAY(now()) as week; 
-- DAYOFWEEK()Returns the current day of the week. Note: Sunday is 1 and Monday is 2,... Saturday is 7
select DAYOFWEEK(now()) as week;
-- DAYNAME(date)Returns the day of the week for the specified time: MONDAY,TUESDAY.....SUNDAY
select DAYNAME(now());
-- MONTHNAME(date)Returns the month of the specified time: January，. . . . . 
select MONTHNAME(NOW());

-- WEEK(date)Returns the week ordinal of the year WEEKOFYEAR(date)
select WEEK(now()) , WEEKOFYEAR(now());


-- 2,CURDATE  Current system date
-- 3,CURTIME  Current system time
select CURDATE();
select CURTIME();
select CURRENT_DATE;
select CURRENT_TIME;
select CURRENT_TIMESTAMP;



-- 4,DATEDIFF(date1,date2)  return date1 - date2 Date interval
-- TIMEDIFF(time1, time2)return time1 - time2 Time interval
select DATEDIFF('2020-10-10',now());
select TIMEDIFF('16:24:01','16:24:24');
select TIMEDIFF('16:24:01',CURTIME());

-- 5,DATE_FORMAT(datetime ,fmt)  By string fmt format date datetime value
#Case: view the entry date of employee No. 100
select employee_id , hiredate ,DATE_FORMAT(hiredate,'%y-%m-%d %H:%i:%s week%w')from employees where employee_id = 100;


-- 6,STR_TO_DATE Resolves a string to a date type in the specified format
#Case: view the information of employees employed before June 1998
select employee_id,hiredate 
from employees
where hiredate < STR_TO_DATE('1998 June','%Y year-%m month');



-- 7,DATE_ADD(datetime, INTERVALE  expr  type)  Returns the difference between the given date and time INTERVAL Date time of the time period
/*
	Use DATE_ADD(NOW(),INTERVAL 1 MONTH) is used to modify the time
	The first parameter is the time to modify;
	The second parameter is fixed;
	The modified value of the third parameter: if a positive number is plus, a negative number is minus;
	The fourth parameter can be filled in YEAR,MONTH,DAY,HOUR,MINUTE,SECOND;
*/
select now() , DATE_ADD(now(),INTERVAL 1 MONTH);
select now() , DATE_ADD(now(),INTERVAL -1 DAY);
select now() , DATE_ADD(now(),INTERVAL 1 HOUR);
SELECT NOW() , DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);

(1)DATE_ADD(datetime,INTERVAL expr type) returns the date and time of the INTERVAL period different from the given date and time

SELECT DATE_ADD(NOW(), INTERVAL 1 YEAR);   

SELECT DATE_ADD(NOW(), INTERVAL -1 YEAR);  #Can be negative

SELECT DATE_ADD(NOW(), INTERVAL '1_1' YEAR_MONTH);  #Single quotation mark required

Expression type
YEAR	Added in years
MONTH
DAY
HOUR
MINUTE
SECOND
YEAR_MONTH	Added in year and month
DAY_HOUR
DAY_MINUTE
DAY_SECOND
HOUR_MINUTE
HOUR_SECOND
MINUTE_SECOND

(2)DATE_ Format (datetime, FMT) and STR_TO_DATE(str, fmt)

Formatter	explain	Formatter	explain
%Y	Four digits indicate the year	%y	Two digits for year
%M	Month name means month (January,...)	%m	Two digits represent the month (01, 02, 03...)
%b	Abbreviated month name (Jan., Feb.,...)	%c	Numbers represent months (1, 2, 3,...)
%D	The English suffix indicates the number of days in a month (1st,2nd,3rd,...)	%d	Two digits indicate the number of days in the month (01,02...)
%e	The number of days in a month (1,2,3,4,5...)
%H	Two digits represent decimals in 24-hour system (01,02...)	%h and% I	Two digits represent hours, 12 hour system (01,02...)
%k	Hours in digital form, 24-hour system (1,2,3)	%l	Digital form represents hour, 12 hour system (1,2,3,4...)
%i	Two digits represent minutes (00,01,02)	%S and% s	Two digits represent seconds (00,01,02...)
%W	Name of the week in the week (Sunday...)	%a	Abbreviation of week in a week (Sun., Mon.,Tues.,...)
%w	The number of days in the week (0=Sunday,1=Monday...)
%j	The number of days in the year is expressed in 3 digits (001002...)	%U	The number indicates the week of the year, (1,2,3.) where Sunday is the first day of the week
%u	The number indicates the week of the year, (1,2,3.) where Monday is the first day of the week
%T	24-hour system	%r	12 hour system
%p	AM or PM	%%	Represents%

6.4 process control function

1) 2. Common functions

Process control function	Function description
IF(value,t ,f)	If value is true, return t, otherwise return f
IFNULL(value1, value2)	If value1 is not empty, value1 is returned; otherwise, value2 is returned
CASE WHEN condition 1 THEN result1 WHEN condition 2 THEN result2... [ELSE resultn] END	Equivalent to Java if... else if
CASE expr WHEN constant value 1 THEN value 1 WHEN constant value 1 THEN value 1... [ELSE value n] END	Equivalent to Java switch

#for example
SELECT oid,`status`, 
CASE `status` 
WHEN 1 THEN 'Unpaid'
WHEN 2 THEN 'Paid'
WHEN 3 THEN 'Shipped'
WHEN 4 THEN 'Confirm receipt'
ELSE 'Invalid order'
END
FROM t_order;

2) . cases

#4, Process control function
-- 1,IF function
#Demand: if there is a bonus, the final bonus will be displayed; if not, 0 will be displayed

-- 2,CASE function
/*
   Case 1: similar to the switch statement, equivalence judgment can be realized
   CASE expression
   WHEN Value 1 THEN result 1
   WHEN Value 2 THEN result 2
   ...
   ELSE Result n
   END
*/
#Case: the department number is 30, and the salary is displayed as twice; The department number is 50, and the salary is displayed as 3 times; The department number is 60, and the salary is displayed as 4 times; Otherwise, it remains unchanged;
#Display department number, new salary, old salary
select department_id 'Department number',salary 'Old wages',
   case department_id
   	when 30 then salary*2
   	when 50 then salary*3
   	when 60 then salary*4
   	else salary
   end as 'newSalary'
   from employees;


/*
   Case 2: similar to multiple IF statements, interval judgment is realized
   CASE 
   WHEN Condition 1 THEN result 1
   WHEN Condition 2 THEN result 2
   ...
   ELSE Result n
   END
*/
#Case: if salary > 20000, level A is displayed; Salary > 15000, display level B; Salary > 10000, display level C; Otherwise, D is displayed
select 
   salary as 'wages',
   case
   	when salary>20000 then 'level A' 
   	when salary>15000 then 'level B' 
   	when salary>10000 then 'level C' 
   	else 'level D'
   end as 'level' 
from employees;

6.5 grouping (aggregation) function

Grouping function is often used to perform statistical calculation on a group of data and finally get a value, which is also called aggregation function or statistical function. Aggregation function is a function used for vertical operation.

1) 2. Common functions

COUNT(): counts the number of record rows whose specified column is not NULL;

MAX(): calculates the maximum value of the specified column. If the specified column is of string type, the string sorting operation is used;

MIN(): calculates the minimum value of the specified column. If the specified column is of string type, the string sorting operation is used;

SUM(): calculates the sum of the values of the specified column. If the specified column type is not a numeric type, the calculation result is 0;

AVG(): calculates the average value of the specified column. If the specified column type is not numeric, the calculation result is 0;

2) . cases

#5, Grouping (aggregation) function
-- Case: query the salary sum, average salary, minimum wage, maximum wage and the number of paid employees in the employee information table
select sum(salary) 'Wages and' , avg(salary) 'Average wage' ,
min(salary) 'minimum wage'  , max(salary) 'Maximum wage' , count(salary)
from employees;

-- count() function
#Add filter criteria
-- Case 1:query emp Number of records in the table
select count(*) from employees;

-- Case 2:query emp Number of people with Commission in the table
select count(commission_pct) from employees;

-- Case 3:query emp Number of persons with monthly salary greater than 2500 in the table
select count(first_name) from employees where salary>2500;

-- Case 4:Query the number of leaders
select count(manager_id) from employees;

#Other uses
-- Case 5:It is recommended to count the rows of the result set count(*). Demand: query the number of people in department No. 30 in the employee table
select count(*) from employees where department_id=30;

-- Case 6:collocation distinct Realize the statistics of de duplication. Demand: query the number of departments with employees
select count(distinct department_id) from employees;

-- SUM() Summation function
-- Case 7:Query monthly salary and salary of all employees
select sum(salary) from employees;

-- AVG() Mean value function
-- Case 8:Calculate the average salary of all employees
select AVG(salary) from employees;

-- MAX() and MIN()  Maximum and minimum functions
-- Case 9:Query maximum and minimum wages
select MAX(salary) from employees;
select AVG(salary) from employees;

7, Grouping query

When grouping query is required, the GROUP BY clause needs to be used, for example, to query the average salary of each department, which means that departments should be used for grouping.

7.1 syntax

select Query list

from Table name

where Screening conditions

group by Group list

having Filter after grouping

order by Sort list;

7.2 execution sequence

from  clause

where clause

group by clause

having clause

select clause

order by clause

7.3 usage

Query lists are often grouped functions and grouped fields

There are two types of filters in grouping query:

	Filtered base table	Keywords used	position
Filter before grouping	Original table	where	In front of group by
Filter after grouping	Grouped result set	having	Behind group by

Order: where - group by - having

Summary: 1. Grouping function conditions can only be placed after having. 2

2. In the select list, all columns not included in the group function should be included in the group by clause.

7.4 cases

-- 1)Simple grouping
#Case 1: query the average salary of employees of each type of work
select job_id ,avg(salary)
from employees
group by job_id;

#Case 2: query the number of subordinates of each leader
select manager_id ,count(*)
from employees
group by manager_id; 



-- 2)Filtering before grouping can be realized
#Case 1: query the maximum salary of each department with a character in the mailbox
select department_id,max(salary)
from employees
where email like '%a%'
group by department_id;

#Case 2: query the average salary of employees with bonus under each leader
select manager_id,avg(salary)
from employees
where commission_pct is not null
group by manager_id;



-- 3)Filtering after grouping can be realized
#Case 1: query which department has more than 5 employees
select	department_id,count(*)
from employees
group by department_id
having count(*)>5;

#Case 2: the maximum salary of employees with bonus in each type of work is more than 12000, and the type number and maximum salary
select job_id,max(salary)
from employees
where commission_pct is not null
group by job_id
having max(salary)>12000;

#Case 3: the minimum wage of each leader with leader number > 102 is greater than the minimum wage of 5000
select manager_id,min(salary)
from employees
where manager_id>102 
group by manager_id
having min(salary)>5000;


-- 4)Sorting can be realized
#Case: query the work type number and maximum salary of employees without bonus whose maximum salary is > 6000, in ascending order by the maximum salary
select job_id,max(salary) max
from employees
where commission_pct is not null
group by job_id
having max(salary)>6000
order by max;

-- 5)Group by multiple fields
#Case: query the minimum wage of each type of work and each department, in descending order. Tip: the type of work and department are the same, which is a group
select job_id,department_id,min(salary) min
from employees
group by job_id ,department_id
order by min desc;

8, Connection query

8.1 preparation - create table structure

Execute the initialization script person.sql, create and insert the following data.

Goddess Watch: girl

Field name	Field type	explain
id	int(11)	Girl number, primary key, non empty, self growth
name	varchar(50)	Name, not empty
sex	char(1)	Gender, default female
borndate	datetime	birthday
phone	varchar(11)	Mobile number, non empty
photo	blob	head portrait
boyfriend_id	int(11)	The number of the boyfriend. The foreign key is associated with the primary key in the boy table

Male God Watch: boy

Field name	Field type	explain
id	int(11)	Boy number, primary key, non empty, self growth
name	varchar(20)	full name

8.2 concept

Join query is also called multi table query. When the fields involved in the query statement come from multiple tables, join query will be used.

Direct multi table query will cause problems: Cartesian product. The number of records is that table 1 has m rows, table 2 has n rows, and the result = m*n rows

#View new table data
select count(*) from girl; -- 12
select count(*) from boy;  -- 6

#Cartesian product phenomenon
select girl.name 'female' , boy.name 'male' from girl , boy;  -- 72

Occurrence reason: there is no valid connection condition, and all rows in all tables are connected to each other

How to avoid: in order to avoid Cartesian sets, you can add valid connection conditions in WHERE

#Solution: add a valid connection condition
select girl.name 'female' , boy.name 'male' from girl , boy where girl.boyfriend_id = boy.id;

analysis:

So many table queries do not produce such results, so how to remove duplicate and unwanted records? Of course, it is through conditional filtering. Usually, there is an association relationship between multiple tables to be queried, so the Cartesian product is removed through the association relationship.

8.3 classification

Classification by age:

1). sql92 standard: only internal connection is supported. Internal connection: equivalent connection, non equivalent connection, self connection

2). sql99 standard [recommended]: support inner connection + outer connection (left outer and right outer) + cross connection

Classification by function:

Inner connection: equivalent connection, non equivalent connection, self connection

External connection: left external connection, right external connection, full external connection (not supported)

Cross connect: Cartesian product

Natural connection

8.4 SQL92 standard internal connection

1) . grammar

select Query list

from Table 1 aliases , Table 2 aliases

where Connection conditions and Screening conditions

group by Group list

having Filter after grouping

order by Sort list

2) 3. Execution sequence

from clause

where clause

and clause

group by clause

having clause

select clause

order by clause

3) . equivalent connection

usage

1) The result of multi table equivalent connection is the intersection of multi tables

2) N table connection, at least n-1 connection conditions are required

3) The order of multiple tables is not required

4) Aliasing tables is generally required

5) It can be used with all the clauses described above, such as sorting, grouping, filtering, etc

case

-- 1,Multi table equivalent query
#Case 1: query the goddess name and the corresponding male god name
select g.name 'goddess' ,b.name 'Male god'
from girl g , boy b
where g.boyfriend_id = b.id;


#Case 2: query employee name and corresponding department name
select e.first_name 'Employee name' ,d.department_name 'Department name'
from employees e , departments d
where e.department_id = d.department_id;


-- 2,Alias table
#Query employee name, type of work number and type of work name
select e.first_name 'Employee table' , j.job_id 'Type of work No' ,j.job_title 'Public name'
from employees e, jobs j
where e.job_id = j.job_id;


-- 3,The order of the two tables can be reversed
#Query employee name, type of work number and type of work name
select  e.first_name 'Employee name' ,e.job_id 'Type of work No' ,j.job_title 'Public name'
from jobs j , employees e
where e.job_id = j.job_id;

-- 4,You can add filtering
#Case 1: query employee name and department name with bonus
select e.first_name 'Employee name' , d.department_name 'Department name'
from employees e , departments d
where e.department_id = d.department_id and e.commission_pct is not null;

#Case 2: query the Department name and city name whose second character is o in the city name
select d.department_name 'Department name', l.city 'City name'
from departments d , locations l
where d.location_id = l.location_id and l.city like '_o%';

-- 5,Groups can be added
#Case 1: query the number of departments in each city
select l.city , count(d.department_id)
from departments d , locations l
where d.location_id = l.location_id 
group by l.city;

#Case 2: query the Department name, department leader number and minimum wage of each department with bonus
select d.department_name 'Department name' , d.manager_id 'Leader number' , min(salary) 'minimum wage'
from employees e , departments d
where e.department_id = d.department_id and e.commission_pct is not null
group by d.department_name;


-- 6,Can add sorting
#Case: query the name of each type of work and the number of employees in descending order by the number of employees
select j.job_title 'Type of work' , count(*) 'Number of employees'
from employees e , jobs j
where e.job_id = j.job_id
group by j.job_title
order by count(*) desc;

-- 7,Can three meter connection be realized?
#Case 1: query employee name, department name and city
select e.first_name 'Employee name', d.department_name 'Department name', l.city 'City'
from employees e , departments d , locations l
where e.department_id = d.department_id and d.location_id = l.location_id;

#Case 2: query the employee name, department name and city. The city name should start with s and be in descending order according to the Department name.
select e.first_name 'Employee name', d.department_name 'Department name', l.city 'City'
from employees e , departments d , locations l
where e.department_id = d.department_id and d.location_id = l.location_id and l.city like 's%'
order by d.department_name desc;

4) . non equivalent connection

Execute initialization script job_grades.sql, create and insert the following data.

#Case: query employee's salary and salary level
select e.first_name ,e.salary , g.grade_level
from employees e, job_grades g
where e.salary BETWEEN g.lowest_sal and g.highest_sal;

5) . self connection

#Case: query employee name and superior name
select	e.first_name 'staff' , m.first_name 'leader'
from employees e,employees m
where e.manager_id = m.employee_id;

8.5 SQL99 syntax

Differences between SQL92 and SQL99:

SQL99 uses the JOIN keyword instead of the previous comma, and separates the connection conditions and filter conditions to improve readability.

8.5.1 internal connection

1) . grammar

SELECT Query list

FROM Table name 1 alias [INNER] JOIN  Table name 2 alias

ON Connection conditions

WHERE Screening conditions

GROUP BY Group list

HAVING Filter after grouping

ORDER BY Sort list;

2) . equivalent connection

-- 1.Simple connection
#Case: query employee name and department name
select e.first_name 'staff',d.department_name 'Department name'
from employees e inner join departments d
on e.department_id = d.department_id;

-- 2.Add filter criteria
#Case: query the name of the Department with department number > 100 and the name of the city where it is located
select d.department_name 'Department name' , l.city 'City name'
from departments d JOIN locations l
on d.location_id = l.location_id;
where d.department_id >100;

-- 3.Add group
#Case: query the number of departments in each city
select l.city 'city' , count(d.department_id) 'Number of departments'
from departments d join locations l
on d.location_id = location_id
group by l.city;

-- 4.Add group+sort
#Case: query the Department names with more than 10 employees in the Department, in descending order by the number of employees
select d.department_name 'Department name' , count(e.employee_id) 'Number of employees'
from employees e join departments d
on e.department_id = d.department_id
group by d.department_name
having count(e.employee_id)>10
order by count(e.employee_id) desc;

3) . non equivalent connection

#Case: query the salary levels of employees with department numbers between 10-90 and group them by level
select e.salary ,e.department_id ,g.grade_level 'Wage scale'
from employees e join job_grades g
on e.salary BETWEEN g.lowest_sal and g.highest_sal
where e.department_id BETWEEN 10 and 90;

4) . self connection

#Case: query employee name and corresponding leader name
select e.first_name'staff' , m.first_name 'leader'
from employees e join employees m
on e.manager_id = m.employee_id;

8.5.2 external connection

1) . description

Query results are all records in the master table. If there are matching items in the slave table, the matching items will be displayed; If there is no match from the table, null is displayed

2) 2. Application scenarios

It is generally used to query records in the primary table but not in the secondary table

3) . usage

For left join, the left join is the main table;

For right join, the right join is the main table on the right;

Left / right outer join = result of inner join query + records in the main table but not in the slave table

Total external connection = result of internal connection query + records in Table 1 but not in Table 2 + records in Table 2 but not in Table 1

4) . grammar

select Query list

from Table 1 aliases

left | right | full [outer] join Table 2 aliases

on Connection conditions

where Screening conditions;

5) . left outer join

SELECT  FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

Note: OUTER can be omitted

Left join is to query the left table first (i.e. mainly the left table), and then query the right table. Those that meet the conditions are displayed in the right table, and those that do not meet the conditions are displayed as NULL.

6) right outer join

SELECT  FROM emp e RIGHT OUTER JOIN dept d ON e.deptno=d.deptno;

Right join is to query all records in the right table first, and then display the left table if it meets the conditions, and display NULL if it does not meet the conditions. For example, there are no employees in the 40 departments in the dept table, but in the right connection, if the dept table is the right table, the 40 departments will still be found, but the corresponding employee information is NULL.

Left / right outer join = result of inner join query + records in the main table but not in the slave table

#Case 1: query all goddess records and the corresponding male god names. If there is no corresponding male god, it will be null
-- Left connection
select g.name 'goddess' , b.name 'Male god'
from girl g left join boy b
on g.boyfriend_id = b.id;

-- Right connection
select  g.name 'goddess' , b.name 'Male god'
from boy b right join girl g
on b.id = g.boyfriend_id;


#Case 2: find out which goddess has no boyfriend
-- Left connection
select g.name 'goddess' , b.name 'Male god'
from girl g left join boy b
on g.boyfriend_id = b.id
where b.name is null;

-- Right connection
select  g.name 'goddess' , b.name 'Male god'
from boy b right join girl g
on b.id = g.boyfriend_id;


#Case 3: find out which God has no girlfriend
-- Left connection
select b.name 'Male god' ,g.name 'goddess'
from boy b left join girl g
on b.id = g.boyfriend_id
where g.name is null;

-- Right connection
select  b.name 'Male god' ,g.name 'goddess'
from girl g right join boy b
on  g.boyfriend_id = b.id
where g.name is null;


#Case 4: query which department has no employees, and display its department number and department name
select d.department_id , d.department_name , e.first_name 
from employees e right join departments d
on e.department_id = d.department_id
where e.first_name is null;


#Case 5: query which city whose city name contains the a character has no department, in descending order by city name
select l.city
from locations l left join departments d
on l.location_id = d.location_id 
where l.city like '%a%' and d.department_name is null
order by l.city desc;

7) . full outer join

Full join is to query all records in the table, and no duplication is allowed. Note: not supported here.

Total external connection = result of internal connection query + records in Table 1 but not in Table 2 + records in Table 2 but not in Table 1

#All external connections (not supported)
SELECT g.`name` , b.name 
FROM boy b 
FULL  OUTER JOIN girl  g  ON g.`boyfriend_id` = b.`id`;

8.5.3 CROSS JOIN (Cartesian product)

#Cross connect
SELECT g.`name` , b.name 
FROM boy b 
CROSS JOIN girl  g ;

8.5.4 NATURAL JOIN (not commonly used)

As we all know, join query will produce useless Cartesian product. We usually use the main foreign key relationship equation to divide it.

Natural connection does not require you to give the main foreign key equation, but it will automatically find this equation:

Columns with identical field names and types in the two connected tables are used as conditions. For example, deptno columns exist in both emp and dept tables, and the types are the same, so they will be found by natural connection!

Of course, natural connection has other ways to find conditions, but other ways may have problems!

SELECT  FROM emp NATURAL JOIN dept;  -- within

SELECT  FROM emp NATURAL LEFT JOIN dept; -- Left outer

SELECT  FROM emp NATURAL RIGHT JOIN dept; -- Right outer

8.5.5 connection query graph analysis

9, Subquery

9.1 concept

select statements that appear inside other statements are called subqueries or intra queries

Query statements nested with other select statements are called primary or external queries

Subqueries do not necessarily appear inside the select statement, but they often appear inside the select statement!

9.2 example

select first_name from employees where department_id  > (

   	 select department_id from departments   where  location_id=1700  ) ;

9.3 classification

Classify by the location where the sub query appears:

1. After select
Requirement: the result of subquery is single row and single column (scalar subquery)

2. Behind from
Requirement: the result of sub query can be multiple rows and columns

3. After where or having
Requirement: the result of sub query must be single column; Single line sub query; Multiline subquery

4. After exists
Requirement: sub query results must be single column (related sub query)

where or having is the key point:

Single line subquery

Features: the result set of sub query has only one row and one column

Multiline subquery

Features: the result set of sub query has multiple rows and one column

9.4 features

1. Subquery statements need to be placed in parentheses to improve the readability of the code

2. The sub query is executed before the main query. Generally speaking, the main query will use the results of the sub query

3. If a sub query is placed in a condition, generally speaking, the sub query needs to be placed on the right side of the condition

Example: where job_ ID > (subquery)

Cannot be written as: where (subquery) < job_ id

4. Single line operators corresponding to single line sub queries: > < > = < = < >

Multiline operators corresponding to multiline subqueries: in, any, all, not in

For example:

In: judge whether a field is in the specified list x in(10,30,50)

Any: judge whether the value of a field meets any of x > any (10,30,50) x > min () x = any (10,30,50) x in (10,30,50)

All: judge whether the value of a field meets all x > all (10,30,50) x > max ()

9.5 cases

-- 1.single-row subqueries 
#Case 1: whose salary is higher than Abel?
#First step
select salary from employees where last_name = 'Abel';
#Step 2
select last_name ,salary 
from employees 
where salary>(select salary from employees where last_name = 'Abel');


#Case 2: return job_ The ID is the same as that of employee 141. Salary is more than that of employee 143_ ID and salary
#First step
select job_id from employees where employee_id = 141;
#Step 2
select salary from employees where employee_id = 143;
#Step 3
select first_name ,employee_id ,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);


#Case 3: return the last of the employee with the lowest salary in the company_ name,job_ ID and salary
#First step
select min(salary) from employees;
#Step 2
select last_name ,job_id ,salary
from employees
where salary = (select min(salary) from employees);


#Case 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
#First step
select min(salary) from employees where department_id = 50;
#Step 2
select department_id , min(salary)
from employees
group by department_id
having min(salary) > (select min(salary) from employees where department_id = 50);

-- 2.Multiline subquery
#Case 1: return location_ The ID is the name of all employees in the 1400 or 1700 department
#First step
select department_id from departments where location_id in(1400,1700);
#Step 2
select first_name 
from employees e 
where department_id in(select department_id from departments where location_id in(1400,1700));

#Case 2: return to a job in another department_ ID is' it '_ Prog 'employee number, name and job of any employee with low salary in the Department_ ID and salary
#First step
select salary from employees where job_id = 'IT_PROG';
#Step 2
select	employee_id ,first_name , job_id ,salary  
from	employees 
where	salary < any(select salary from employees where job_id = 'IT_PROG')


#Case 3: return to a job in another department_ ID is' it '_ Prog 'employee number, name and job of all employees with low salary in the Department_ ID and salary
#First step
select salary from employees where job_id = 'IT_PROG';
#Step 2
select	employee_id ,first_name , job_id ,salary  
from	employees 
where	salary < all(select salary from employees where job_id = 'IT_PROG')



#Case 4: use the sub query to realize the names of employees whose city is Toroto and whose salary is > 10000
#First step
select location_id from locations where city='Toronto'; 
#Step 2
select department_id  
from	departments
where location_id =(select location_id from locations where city='Toronto');
#Step 3
select first_name,salary
from employees
where department_id 
in(select department_id  
from	departments
where location_id =(select location_id from locations where city='Toronto'))
and salary >10000;

-- 2, Put on select behind
#Case: query the number of employees whose department number is 50
#First step
select count(*) from employees where department_id = 50;
#Step 2
select(select count(*) from employees where department_id = 50);

-- 3, Put on from behind
#Case: query the average salary level of each department
#First step
select department_id ,avg(salary) from employees group by department_id;
#Step 2
select s.deptId , s.avg , g.grade_level
from 
	(select department_id deptId ,avg(salary) avg  from employees group by department_id) s,
	job_grades g
where s.avg between g.lowest_sal and g,highest_sal;


-- 4, Put on exists behind
#Case 1: query whether there is employee information named "Abel"
select * from employees where last_name='Abel';
-- Returning 1 means the value exists, and returning 0 means the value does not exist
select EXISTS(select * from employees where last_name='Abel') 'Yes no Abel staff';


#Case 2: query the boyfriend information without a girlfriend
select boyfriend_id from girl where boyfriend_id is not null;
select id from boy where id not in(select boyfriend_id from girl where boyfriend_id is not null);

-- exists
select id 
from boy 
where EXISTS(select boyfriend_id from girl where boyfriend_id is not null)

10, Paging query

10.1 application scenarios

When the data on the page is not fully displayed on one page, it needs to be displayed in pages

The sql command of paging query requests the database server - > the server responds to multiple pieces of data queried - > the foreground page

10.2 syntax

		select Query list
		
		from Table 1 aliases
		
		join Table 2 aliases
		
		on Connection conditions
		
		where Screening conditions
		
		group by grouping
		
		having Filter after grouping
		
		order by Sort list
		
		limit Start entry index,Number of entries displayed

LIMIT is used to LIMIT the starting row of query results and the number of rows displayed on each page.

MySQL limit keyword

Oracle rownum keyword

10.3 execution sequence

		from clause
		
		join clause
		
		on clause
		
		where clause
		
		group by clause
		
		having clause
		
		select clause
		
		order by clause
		
		limit clause

10.4 features

① if the start entry index is not written, it defaults to 0
② two parameters are supported after limit
Parameter 1: start entry index displayed
Parameter 2: number of entries

10.5 formula

If the number of pages to be displayed is page, the number of entries displayed on each page is size

	select *
	from employees
	limit (page-1)*size , size;

For example:
	page	     size=10
	 1      	 limit 0,10
	 2			 limit 10,10
	 3			 limit 20,10
	 4			 limit 30,10

10.6 cases

#Case 1: query the first 5 items in the employee information table
select * from employees limit 0,5;
select * from employees limit 5;


#Case 2: query the employee information in article 11-20
select * from employees limit 10,10;

#Case 3: query the name, salary, bonus and department number of the top three employees with the highest salary with bonus
select first_name ,salary ,commission_pct ,department_id
from employees
order by salary desc 
limit 3;



#Case 4: query the top 10 with the highest annual salary
select first_name ,salary ,commission_pct ,department_id
from employees
order by salary*12 desc 
limit 10;



#Case 5: using paging query to query the information of the five lowest paid employees with department No. 50 in the employee information table
select *
from employees
where department_id = 50
order by salary
limit 5;

11, Joint query

11.1 application scenarios

When the query results come from multiple tables, but there is no association between multiple tables, joint queries, also known as union queries, are often used

11.2 grammar

select Query list from Table 1  where Screening conditions  
union
select Query list from Table 2  where Screening conditions

11.3 features

1. The number of query columns of multiple query statements to be combined must be consistent, and the query type and field meaning should be consistent
2. Implementation of de duplication query in union
union all implements all queries, including duplicate items

11.4 cases

#Case 1: query the information of users aged > 20 in all countries
select * from chinese where age >20
union
select * from japanese where jage >20
union
select * from usa where uage >20;

#Case 2: query user names and ages in all countries
select name , age  from chinese 
union
select jname , jage  from japanese
union
select uname , uage  from usa ;



#Case 3: union automatic de duplication / union all can support duplicate items
select name , age  from chinese 
union all
select jname , jage  from japanese
union	all
select uname , uage  from usa ;

Summary: SQL execution order

Order of handwriting:

SELECT 

selection_list  /*Column name to query*/

FROM  

table1   /*Table name, alias*/

JOIN  

table2   /*Table name, alias*/

ON 

condition   /*Connection conditions*/

WHERE 

condition /*Screening conditions*/

GROUP BY 

grouping_columns /*Group results*/

HAVING 

condition /*Grouping conditions*/

ORDER BY

sorting_columns /*Sort results*/

LIMIT 

offset_start, row_count /*The result limits (paginates) the starting entry index and the number of entries displayed  */

Real execution order: with the update of Mysql version, its optimizer is constantly upgrading. The optimizer will dynamically adjust the execution order by analyzing the performance consumption caused by different execution orders.

The following is the frequently occurring query order:

		from  clause
		
		join  clause
		
		on  clause
		
		where  clause
		
		group by  clause
		
		having  clause
		
		select  clause
		
		distinct  clause
		
		order by  clause
		
		limit  clause

Topics: MySQL

Programmer Think

DQL data query language

DQL data query language

1, SQL overview

1. What is SQL

2. SQL syntax requirements

3. SQL classification

2, DQL data query language

2.1 concept

2.2 syntax

2.3 preparation - create table structure

3, Basic query

3.1 syntax

3.2 execution sequence

3.3 query list

3.4 cases

4, Condition query

4.1 syntax

4.2 execution sequence

4.3 cases

4.4 IFNULL function with special usage

5, Sort query order by

5.1 syntax

5.2 execution sequence

5.3 usage

5.4 cases

6, Common functions

6.1 string function

1) 2. Common functions

2) . cases

6.2 mathematical functions

1) 2. Common functions

2) . cases

6.3 date time function

1) 2. Common functions

2) . cases

6.4 process control function

1) 2. Common functions

2) . cases

6.5 grouping (aggregation) function

1) 2. Common functions

2) . cases

7, Grouping query

7.1 syntax

7.2 execution sequence

7.3 usage

7.4 cases

8, Connection query

8.1 preparation - create table structure

8.2 concept

8.3 classification

8.4 SQL92 standard internal connection

1) . grammar

2) 3. Execution sequence

3) . equivalent connection

usage

case

4) . non equivalent connection

5) . self connection

8.5 SQL99 syntax

8.5.1 internal connection

1) . grammar

2) . equivalent connection

3) . non equivalent connection

4) . self connection

8.5.2 external connection

1) . description

2) 2. Application scenarios

3) . usage

4) . grammar

5) . left outer join

6) right outer join

7) . full outer join

8.5.3 CROSS JOIN (Cartesian product)

8.5.4 NATURAL JOIN (not commonly used)

8.5.5 connection query graph analysis

9, Subquery

9.1 concept

9.2 example

9.3 classification