MySQL advanced query,

MySQL advanced query,

 

Advanced Query

Keyword writing sequence keyword execution sequence
Select: Projection result 1 5

From: locate Table 2 1

Where: first filter 3 2 before grouping

Group by: group 4 3

Having: The second filter after grouping 5 4

Order by: Sort 6 6

Limit: Last

--- Paging *
Objective: To speed up Data Query (retrieval) of a website

-- SQL server:
-1. Skip the first few items and retrieve the remaining data items.
Double top double order
Select top data volume per page * from table where column not in
(
Select top data volume column to be skipped from table
)
-----------------------------
---------------------------------------------------------
-2. row_nubmer () over (order by) (supported after 2005)
Select * from
(
Select *, row_number () over (order by primary key column) as myid from table
) As temp
Where myid between start number and data volume per page

-- Mysql:
SELECT <field name list>
FROM <Table name or View>
[WHERE <query condition>]
[Group by <GROUP field Name>]
[Order by <sort column Name> [ASC or DESC]
[LIMIT [location offset,] number of rows];

-- Temporary table
Temporary tables are mainly used to make a subset of large data tables to improve query efficiency. Accelerate Data Access
Temporary tables exist in the system database
SQL Sever:
Stored in System database tempdb
# Table Name: local temporary table:
Valid only for the current session
# Table Name: Global temporary table
Share all sessions

MySQL:
Session disconnection and destruction
All temporary tables serve the current connection.
Temporary tables are only visible in the current connection. When the connection is closed, Mysql automatically deletes the table and releases all space. Therefore, you can create temporary tables with the same name in different connections and operate on temporary tables of the current connection.
The syntax for creating a TEMPORARY table is similar to that for creating a table. The difference is that the keyword TEMPORARY is added, for example:
Create temporary table name (.... )
Show create table allows you to view temporary tables;

-- Pseudo table
Dual is called a pseudo table!

It is a decoration in mysql

Select *;
Select * from dual;

Select * from dual; Error

From dual; must be used in oracle;
Select * from dual; correct
Select *; Error

Dual is a table with only one row and one column!
Query only! You cannot add, delete, or modify dual!

 

-- Parallel

Drop table if exists 'testa ';

Create table 'testa '(
'Name' varchar (20) default null,
'Subobject' varchar (20) default null,
'Score 'double DEFAULT NULL
) ENGINE = InnoDB default charset = utf8;

 

Insert into 'testa '('name', 'subobject', 'score') values ('zhang san', 'China', 80), ('Li si', 'China ', 90), ('wang 5', 'China', 70), ('zhang san', 'mat', 60), ('Li si', 'mat', 98 ), ('wang wu', 'mat', 100 );

-- Scores and subjects must be displayed in one column and grouped by name
SELECT
'Name' AS name,
GROUP_CONCAT ('subobject', ':', score) AS score
FROM testa
Group by 'name ';

 

-- Query the student information of all grades numbered 1 in ascending order
SELECT * FROM student
WHERE GradeID = 1
Order by Studentno ASC;

-- Display the first four records
SELECT * FROM student
WHERE GradeID = 1
Order by Studentno ASC
LIMIT 0, 4;
-- Four records on each page, with 2nd pages displayed, that is, four data records are displayed starting from 5th records.
SELECT * FROM student
WHERE GradeID = 1
Order by Studentno ASC
LIMIT 4, 4

SQL99 standard:
(1) Rules for operating all relational databases
(2) the fourth generation of Language
(3) Structured Query Language s
(4) The corresponding results are displayed only by issuing valid and reasonable commands.

<>: Not equal to (SQL99 Standard)
 

-- Subquery
Correlation and nesting
Related subqueries: execution mechanism
Internal queries cannot be executed independently and must be combined with external queries. The outer and inner layers are executed in parallel. |
Nested subquery: the inner layer query can be executed separately. The result of the inner layer is used as the condition of the outer layer.
Note: not all subqueries execute the inner layer query first.

Subqueries can be applied to any location

All table connections can be replaced by subqueries, but table connections may not be used where subqueries can be used.
Example: restriction: the table cannot be connected. Scenario: When the query condition is <>

(Conclusion: subquery has a wider application scope)
A query contains another query. Generally, a subquery is expanded with (), and the search results in parentheses exist as conditions for outer queries.

Comparison operators can only project one column of in, not in, not exists, and exists.

-- Regard the result of one query as the field, condition or table of another query (subquery can be applied to any position )!

SELECT studentName FROM student

-- The student's grade name can only be queried through the student table

-- 01. First query the grade number corresponding to the student Wu Song
SELECT GradeID FROM student WHERE studentName = 'wusong'

-- 02. Get the grade name based on the grade number
SELECT gradeName FROM grade WHERE GradeID = ???

SELECT gradeName FROM grade WHERE GradeID
= (SELECT GradeID FROM student WHERE studentName = 'wusong ')

-- Query the list of all students whose grade numbers are 1 or 2
SELECT * FROM student WHERE gradeId IN (1, 2)

-- Query Information of all students whose grades are freshman or sophomore.
-- The student table has no grade name but a grade number.
-- 01. query the number based on the grade name
 
SELECT gradeID FROM grade WHERE gradeName IN ('freshman ', 'sophomore ');
-- 02. query student information by id
SELECT * FROM student WHERE
GradeID
IN (SELECT gradeID FROM grade WHERE gradeName IN ('freshman ', 'sophomore '))
 

-- Query the highest score and lowest score of the student who took the last advanced mathematics-1 Test

-- 01. No subject name in the "tables" table. Only the number is displayed! Number based on name

SELECT SubjectNo FROM 'subobject' WHERE
SubjectName = 'advanced mathematics-1'

-- 02. query the last time of the advanced mathematics-1 Test

Select max (ExamDate) FROM result
WHERE
SubjectNo = (SELECT SubjectNo FROM 'subobject' WHERE
SubjectName = 'advanced mathematics-1 ')

-- All recent exam scores
SELECT * FROM result
WHERE ExamDate = '2017-11-11 16:00:00'

-- 03. start to get the highest score and lowest score
Select max (studentResult) AS highest score,
MIN (studentResult) AS minute score
FROM result
WHERE SubjectNo = (SELECT SubjectNo FROM 'subobject' WHERE
SubjectName = 'advanced mathematics-1 ')
AND ExamDate =
(Select max (ExamDate) FROM result
WHERE
SubjectNo = (SELECT SubjectNo FROM 'subobject' WHERE
SubjectName = 'advanced mathematics-1 '))

 

-- Query the information of students whose score is 60 in advanced mathematics-1.

-- 01. Obtain the account number based on the account name
SELECT SubjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'

-- 02. query all student numbers by number
SELECT studentNo FROM result
WHERE SubjectNo = (SELECT SubjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')
AND StudentResult = 60; -- score = 60

-- 03. query Student Information
SELECT * FROM student
WHERE studentNo IN
(SELECT studentNo FROM result
WHERE SubjectNo = (SELECT SubjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')
AND StudentResult = 60)

-- Replace the subquery statement equal to (=) with in!
-- The subquery after in can return multiple records!

-- Not in: not within a certain range

-- Query the list of students not taking the last exam of the advanced mathematics-1 course
-- 01. Obtain the account number based on the account name

SELECT SubjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'

-- 02. Obtain the last exam time
Select max (ExamDate) FROM result
WHERE SubjectNo =
(SELECT SubjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')

-- 03. query the number of students not involved
SELECT studentNo, StudentName FROM student
WHERE studentNo NOT IN
(
SELECT StudentNo FROM result
WHERE SubjectNo =
(SELECT SubjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')
AND ExamDate =
(Select max (ExamDate) FROM result
WHERE SubjectNo =
(SELECT SubjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 '))
)

 

 

 

-Exists (check subquery) Usage
-- 01. Used to check whether tables, databases, and so on exist
-- 02. Check whether data is returned in the subquery! Check that the subquery does not return any data!
Returns true or false!
1. Use Cases of Exists?
Determine whether a database object exists
1.1
If exists XXXX
1.2
Where exists (subquery)

SELECT * FROM Student where exists (select null)
SELECT * FROM Student where exists (SELECT 9*9)
SELECT * FROM Student where exists (SELECT StudentName FROM student)

SELECT * FROM Student
Where exists (SELECT studentName FROM Student WHERE studentName = 'zhang san ')

SELECT * FROM Student WHERE studentName IN (SELECT studentName FROM Student)

-- In is equivalent to = any
SELECT * FROM Student WHERE
StudentName = ANY (SELECT studentName FROM Student)

-- All is greater than the maximum value in the subquery Statement> (1, 2, 3)> 3
SELECT * FROM student
WHERE studentNo> ALL
(SELECT studentNo FROM student WHERE studentNo IN (1003,1004, 1005 ))

-- Any is greater than the minimum value in the subquery Statement> (1, 2, 3)> 1
SELECT * FROM student
WHERE studentNo> ANY
(SELECT studentNo FROM student WHERE studentNo IN (1003,1004, 1005 ))

-- Some and any functions are the same.
SELECT * FROM student
WHERE studentNo> SOME
(SELECT studentNo FROM student WHERE studentNo IN (1003,1004, 1005 ))

-- Check the last exam score of the advanced mathematics-1 course
-- If the score is higher than 80, the student ID and score of the top 5 are displayed.

-- Do not use exists

-- 01. query the number of the course "advanced mathematics-1"
SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'

-- 02. query recent test scores
Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')

-- 03. Add a score greater than 80 on the basis of 02
SELECT * FROM result
WHERE ExamDate =
(Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 '))
AND StudentResult> 80

-- 04. Optimization
SELECT studentNo, StudentResult FROM result
WHERE ExamDate =
(Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 '))
AND StudentResult> 80
Order by StudentResult DESC
LIMIT 0, 5

 

-- Use exists
-- Check the last exam score of the advanced mathematics-1 course
-- If the score is higher than 80, the student ID and score of the top 5 are displayed.

-- 01. query the number of the course "advanced mathematics-1"
SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'

-- 02. query recent test scores
Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')

-- 03. query student ID and score
SELECT StudentNo, StudentResult FROM result
WHERE EXISTS
(
SELECT * FROM result
WHERE subjectNo = (
SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'
)
AND ExamDate = (
Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')
)
AND StudentResult> 80
)
AND subjectNo = (
SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'
)
AND ExamDate = (
Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')
)
Order by StudentResult DESC
LIMIT 0, 5

 

-- Not exists

-- Check the last exam score of the advanced mathematics-1 course
-- If all the tests fail (60 points pass), it is difficult to take this test. The average score for this test is 5 points.

-- 01. query the number of the course "advanced mathematics-1"
SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'

-- 02. query recent test scores
Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')

 

-- 03. The query score is greater than 60.
SELECT StudentResult FROM result
WHERE StudentResult> 60
AND SubjectNo = (
SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'
)
AND ExamDate = (
Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')
)

 

-- 04. If all the tests fail, the average score is 5 points.
Select avg (StudentResult) + 5 FROM result
WHERE NOT EXISTS
(
SELECT StudentResult FROM result
WHERE StudentResult> 60
AND SubjectNo = (
SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'
)
AND ExamDate = (
Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')
)
)
AND SubjectNo = (
SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1'
)
AND ExamDate = (
Select max (ExamDate) FROM result
WHERE SubjectNo = (SELECT subjectNo FROM 'subobject'
WHERE SubjectName = 'advanced mathematics-1 ')
)

 

-- If a grade name is a sophomore, all the students whose grade name is freshman are queried.

-- 01. First query the corresponding grade number
SELECT GradeId FROM grade WHERE GradeName = 'freshman'
SELECT GradeId FROM grade WHERE GradeName = 'sophomore'

-- 02. Is there any student whose grade name is sophomore in the student table?
SELECT * FROM student WHERE gradeID = (
SELECT GradeId FROM grade WHERE GradeName = 'sophomore'
)

-- 03. If any student whose grade name is freshman is queried
SELECT * FROM student
WHERE EXISTS
(
SELECT * FROM student WHERE gradeID = (
SELECT GradeId FROM grade WHERE GradeName = 'sophomore'
)
)
AND GradeId = (
SELECT GradeId FROM grade WHERE GradeName = 'freshman'
)

 

-- Notes for using subqueries
-- 01. subqueries can be used wherever expressions are allowed
-- 02. Only columns in the subquery but not in the parent query can be included in the result set!

SQL Optimization

Use exists instead of in
Use not exists instead of not in

Exists only returns true or false. No result set is returned.
In returned result set

-- Querying student information % with the surname Li represents 0 or multiple characters _ represents one character
SELECT * FROM student WHERE StudentName LIKE 'Lee %'
SELECT * FROM student WHERE StudentName LIKE 'Lee _'

-- Use in to complete the above Code
SELECT * FROM student WHERE StudentName IN (
SELECT studentName FROM student WHERE StudentName LIKE 'Li % ')
-- In (multiple data records -- return result set)

-- Replace with exists
SELECT * FROM student where exists (
SELECT studentName FROM student)
AND StudentName LIKE 'Li %'
-- Exists (with or without data)

 

-- Count the average score of each course in group by column GROUP
SELECT subjectno, AVG (studentresult) FROM result
Group by subjectno

-- Query the number and average score of the course with an average score greater than 60

SELECT subjectno, AVG (studentresult) FROM result
Group by subjectno
Having avg (studentresult)> 60 -- Condition After grouping

-- Calculate the average score of each course in descending order
SELECT subjectno, AVG (studentresult) FROM result
Group by subjectno
Order by avg (studentresult) DESC

-- If the scores are the same, sort by course number in ascending order.
SELECT subjectno, AVG (studentresult) FROM result
Group by subjectno
Order by avg (studentresult) DESC, subjectno

-- Group statistics on the number of men and women in each grade

SELECT gradeid grade number, sex gender, COUNT (sex)
FROM student
Group by gradeid, sex

-- Create a table
Create table if not exists examTest (
Id INT (2) not null,
Sex VARCHAR (20)
)

-- Add multiple data records at the same time
Insert into examTest VALUES (1, 'male'), (2, 'male'), (3, 'female '), (4, NULL );

SELECT sex AS 'gender', COUNT (sex) AS ''FROM examTest
WHERE sex IS NOT NULL
Group by sex
Order by count (sex) DESC

SELECT sex AS 'gender', COUNT (sex) AS ''FROM examTest
Group by sex
HAVING sex IS NOT NULL
Order by count (sex) DESC

SELECT sex AS 'gender', COUNT (sex) AS ''FROM examTest
WHERE sex IN ('male', 'female ')
Group by sex
Order by count (sex) DESC

 

-- Create a table
Create table if not exists mytable (
'Name' VARCHAR (10) not null,
Class INT (4) not null,
Sorce DOUBLE NOT NULL
)
-- Insert data
Insert into mytable
VALUES
('Blacklist 1',), ('blacklist 2 ),
('Blacklist 3',), ('blacklist 4 ),
('Black 5',), ('black 6 ),
('White 1',), ('White 2 ),
('White 3',), ('White 4 ),
('White 5', 2,98), ('White 6', 2,90)

-- Find the top three scores in the table
SELECT * FROM mytable
Order by sorce DESC
LIMIT 0, 3

-- Find the top three in each class
SELECT * FROM mytable t1
WHERE
(
Select count (1) FROM mytable t2
WHERE t1. 'sorce '<t2. 'sorce'
AND t1.class = t2. 'class'
) <3
Order by class, sorce DESC

 

 

Result set in the link:
Cartesian product: the product of two table records!
Cartesian product is also called the Cartesian product. It is proposed by a person named Cartesian.
Simply put, it is the result of multiplying two sets.
Cartesian product refers to the Cartesian product of two sets of X and Y in mathematics, also known as straight product, expressed as x y, the first object is a member of X, and the second object is a member of all possible ordered pairs of Y [1].

Table connection in progress
On which columns are used to establish an association between two tables?
(All table connections are the same)

Internal join: You can find public rows by matching the public columns in two tables!

Left Outer Join: Take the left table as the standard. If no data exists in the right table, null is returned.

Right outer join: The right table prevails. If no data in the left table is returned, null is returned.

Implicit inner join: Find public rows by matching public columns in two tables!
 
The key to using a self-join table as multiple tables is to use aliases.

-- Output the Student name and corresponding grade name connection
SELECT StudentName, GradeName FROM student inner join grade
ON student. 'gradeid' = grade. 'gradeid'

-- Implicit inner join
SELECT StudentName, GradeName FROM student, grade
WHERE student. 'gradeid' = grade. 'gradeid'

-- Query the name of the student whose exam course number is 1, the name of the grade, the name of the subject, and the score.
01.
SELECT s. StudentName, GradeName, SubjectName, studentResult FROM student s
Inner join grade g ON (s. gradeID = g. gradeID)
Inner join 'subobject' B ON (g. gradeID = B. gradeID)
Inner join result r ON (B. subjectNo = r. subjectNo)
AND s. studentNo = r. studentNo
AND B. subjectNo = 1

02.
SELECT StudentName, GradeName, SubjectName, studentResult FROM
Student s, grade g, 'subobject' B, result r
WHERE s. gradeID = g. gradeID
AND g. gradeID = B. gradeID
AND s. studentNo = r. studentNo
AND B. subjectNo = r. subjectNo
AND B. subjectNo = 1

-- The queried column is not in the same table! Connection query is required! Establish Association!

-- The temporary table is automatically deleted only when the current connection is visible and the connection is closed.
-- Adding, deleting, and modifying temporary tables does not affect real tables.
Create temporary table myStudent
(SELECT * FROM student)

SELECT * FROM myStudent
Delete from mystudent -- DELETE temporary table data
SELECT * FROM student -- does not affect the real table

Self-connection

-- The key to using a self-join table as multiple tables is to use aliases.
SELECT * FROM teacher
-- Query the name of instructor 3 and the name of the corresponding tutor.
-- T1 instructor t2 instructor's mentor id = tutor's ID
SELECT t1. 'name' AS a, t2. 'name' AS instructor name FROM teacher t1, teacher t2
WHERE t1. 'name' = 'instructor 3'
AND t2.id = t1.tid