Advanced query Statement ____ Mysql

MySQL advanced query

Advanced Query

Keyword Write order keyword execution order
Select: Projection Results 1 5

From: Navigate to Table 2 1

Where: First filter before grouping 3 2

GROUP BY: Group 4 3

Having: Second filter after grouping 5 4

ORDER BY: Sort 6 6

Limit: Last

---pagination *
Objective: In order to speed up the query (retrieval) rate of the website to the data

--sql Server:
-1. Skip the previous few and take the remaining data
Double Top Double ORDER BY
Select top data volume per page * from table where column not in
(
Select top the amount of data to skip column from table
)
-----------------------------
---------------------------------------------------------
-2.row_nubmer () over (ORDER by) (2005 support later)
SELECT * FROM
(
Select *,row_number () over (order by primary key column) as myID from table
) as Temp
Where myID between start number and amount per page of data

--mysql:

SELECT < Field Name List >from < table name or view >[where < query criteria >][group by < grouped field names >][order by < column names > [ASC or DESC]] [LIMIT [position offset,] number of rows];

--Temporary table
Temporal tables are primarily used to make a subset of large data volumes and improve query efficiency. Speed up data access
Temporary tables exist in the system database
SQL Sever:
exists in system database tempdb
#表名: Local Temp table:
Valid only for the current session
# #表名: Global temp table
All session Sharing

Mysql:
Break destroy in session
All temporary tables are serving the current connection
Temporary tables are only visible at the current connection, and when the connection is closed, MySQL automatically deletes the table and frees all space. Therefore, a temporary table with the same name can be created in a different connection, and the operation belongs to the temporary table of this connection.
The syntax for creating a temporary table is similar to creating a table syntax, where you add keyword temporary, such as:
CREATE temporary table name (...)
Show CREATE table can view temporary tables;

--pseudo-table
Dual we call it a pseudo-table!

It's a device in MySQL.

SELECT *;
SELECT * from dual;

SELECT * from dual; Error

The from dual must be used in Oracle;
SELECT * from dual; The right
SELECT *; Error

Dual is a table with only one row!
can only query! Dual can not be added to the deletion!

--and parallel drop TABLE IF EXISTS ' testa '; CREATE TABLE ' testa ' (' name ' varchar () default NULL, ' subject ' varchar () default NULL, ' score ' double default null ) Engine=innodb DEFAULT Charset=utf8; Insert INTO ' testa ' (' name ', ' Subject ', ' score ') VALUES (' Zhang San ', ' language ', ' 80 '), (' John Doe ', ' language ', ' 90 '), (' Harry ', ' language ', ' 70 '), (' Zhang San ', ' math ', 60), ( ' John Doe ', ' mathematics ', 98, (' Harry ', ' mathematics ', 100);--requires scores and subjects in a column display according to name group Select ' Name ' as name, Group_concat (' Subject ', ': ', score) as score fr    OM testagroup by ' name '; ---query all students with grade number 1, sort by number in ascending order select * from Studentwhere gradeid=1order by Studentno ASC; --Display the first 4 records select * FROM Studentwhere gradeid=1order by Studentno asclimit 0,4;--per page 4, showing page 2nd, starting from the 5th record 4 data select * FROM S Tudentwhere Gradeid=1order by Studentno Asclimit

SQL99 Standard:
(1) is a rule that operates on all relational databases
(2) is the fourth generation language
(3) is a structured query language s
(4) only if a legitimate order is issued, a corresponding result is displayed.

<>: Not equal to (SQL99 standard)

--Sub-query
Correlation and nesting
Correlated subqueries: Execution mechanism
The inner query cannot be executed separately, and it needs to be combined with external queries. The outer and inner layers are executed in parallel.
Nested subqueries: Inner queries can be executed separately. The result of the inner layer as the outer condition
Note: Not all subqueries perform an inner query first

Subqueries can be applied to any location

All table joins can be replaced with subqueries, but where you can use subqueries, you may not be able to connect using tables
Example: Limitations: Unable to use table join scenario::: When the query condition is <>

(Conclusion: Subqueries have a wider application range)
Another query is included in one query, in general, the subquery will be expanded with (), and the results of the search within the parentheses will exist as a condition for the outer query.

Comparison operators can project only one column In,not In,not exists and exists can project multiple columns

--the result of a query as a field of another query, a condition or a table (a subquery can be applied to any location)! select  Studentname from  student--  only through the student table to find out the student corresponding grade name--  01. First, the student Wu song corresponding   grade number select    gradeid  from student where  studentname= ' Wu Song '--  02. Based on grade number   pick   grade name Select Gradename from grade WHERE gradeid=??? SELECT gradename from grade WHERE gradeid= (select    gradeid  from student where  studentname= ' Wu Song ')  --query grade number is 1 or 2 of   all students list  select * from student WHERE Gradeid in ()--Query grade name is   freshman or sophomore student information &NB sp;--There is no   grade in the student table   But there is a grade number  --01. According to the   grade name, check out the number  select Gradeid from grade WHERE Gradename in (' Freshman ', ' sophomore ');  --02. Query Student information based on ID select  * from student where Gradeid in (SELECT Gradeid from grade where Gradename I N (' Freshman ', ' sophomore ')   --the highest and lowest score of the students who attended the most recent advanced math-1 Exam scores-01. No account names found in the score table are numbered only! By name number select  subjectno  from ' subject '   wheresubjectname= ' higher mathematics-1 '--02. Query The last advanced math-1Exam Time select  MAX (examdate)   from Resultwhere subjectno= (select  subjectno  from ' subject '   Wheresubjectname= ' Advanced math-1 ')--  all recent exam results select *  from Resultwhere examdate= ' 2013-11-11 16:00:00 '--03. Start to get the highest and lowest select  max (Studentresult) as highest score,        min (studentresult) as Min. from  resultwhere subjectno= (select  subjectno  from ' subject '   wheresubjectname= ' advanced math-1 ') and Examdate= (select  MAX (examdate)   from Resultwhere subjectno= (select  subjectno  from ' subject '   Wheresubjectname= ' Advanced mathematics-1 ')  

--Query for Advanced mathematics--1 test scores are 60 points of student information-01. Get account Number according to   account name Select subjectno  from  ' subject ' WHERE subjectname= ' higher mathematics -1 '--02. All student numbers are queried by number select Studentno from result WHERE subjectno= (select subjectno  from  ' Subject ' where Sub Jectname= ' Advanced Mathematics-1 ') and studentresult=60; --score =60--03. Query Student Information SELECT * from  studentwhere  studentno in ( Select Studentno from result WHERE subjectno= (select subjectno  from  ' Subject ' where subjectname= ' higher mathematics-1 ') and STUDENTRESULT=60)--use in to replace a subquery statement equal to (=)! --In the back of the subquery can return more than one record! --  not in: is not within a range--query not enrolled in "Advanced math-1" The most recent exam on the student list-01. Get account number by   account name Select subjectno  from  ' su Bject ' WHERE subjectname= ' higher mathematics-1 '--02. Get the most recent exam time SELECT MAX (examdate) from Resultwhere subjectno= (select Subjectno   from  ' subject ' WHERE subjectname= ' higher mathematics-1 ')--03. Query for student numbers not enrolled select Studentno,studentname from Studentwhere Studentno not in (select  studentno  from  resultwhere subjectno= (SELECT subjectno  from  ' subject ' WHERE subjectname= ' higher mathematics-1 ') and examdate= (select MAX (examdate) from Resultwhere subjectno= (select subjectno  from  ' subject ' WHERE subjectname= ' higher mathematics-1 '))    

-Use of exists (check subquery)
--01. For detecting tables, databases, etc.
--02. Check to see if data is returned in the subquery! Check that the subquery does not return any data!
Value returns TRUE or false!
1.Exists usage scenarios?
To determine whether a database object exists
1.1
if exists XXXX
1.2
where exists (sub-query)

SELECT * from Student where EXISTS (select NULL) SELECT * from Student where EXISTS (select 9*9) SELECT * from Student where E Xists (select Studentname from Student) SELECT * FROM student where EXISTS (select Studentname from student where Studentnam E= ' Zhang San ') SELECT * from Student WHERE studentname in (SELECT studentname from Student)  --in  effect equals =anyselect * FRO M Student WHERE studentname =any (SELECT studentname from Student)--  All is greater than the maximum value in a subquery statement    > (+/-)      >3select * from Studentwhere studentno>all (SELECT studentno from student WHERE Studentno in (1003,1004, 1005))--  any is greater than the minimum value in the subquery statement    > (all in a)    >1select * from Studentwhere Studentno>any ( Select Studentno from Student WHERE Studentno in (1003,1004,1005))--  some and any function as SELECT * FROM Studentwhere STUDENTN O>some (SELECT studentno from student WHERE Studentno in (1003,1004,1005))--check "Advanced math-1" for the most recent exam results-if you have more than 80 marks, Show scores ranked in the top 5 student number and score--  do not use exists-- 01. Query the "Advanced Math-1" course number select Subjectno from ' Subject ' WHERE subjectname= ' higher mathematics-1 '--02. Query Recent Exam scores select MAX (Examdate) from Resultwhere subjectno= (SELECT subjectno from ' subject ' WHERE subjectname= ' higher mathematics-1 ')--03. On the basis of 02, the conditional score is greater than 80SELECT * from the result WHERE examdate= (select MAX (examdate) from Resultwhere subjectno= (select Subjectno F ROM ' subject ' WHERE subjectname= ' higher mathematics-1 ') and studentresult>80--04. Optimize Select Studentno,studentresult from result where examdate= (select MAX (examdate) from Resultwhere subjectno= (select Subjectno from ' Subject ' WHERE subjectname= ' Advanced Mathematics-1 ')) and Studentresult>80order by Studentresult Desclimit 0,5 

--Using Exists
--Check the last exam results of the "Advanced Math-1" course
--If you have more than 80 points, show the student number and score ranked in the top 5

--01. Query the "Advanced Math-1" course number select Subjectno from ' Subject ' WHERE subjectname= ' higher mathematics-1 '--02. Query Recent Exam scores select MAX (Examdate) From Resultwhere subjectno= (select Subjectno from ' Subject ' WHERE subjectname= ' higher mathematics-1 ')--03. Inquiry Number and score SELECT Studentno, Studentresult from Resultwhere EXISTS (SELECT * FROM Resultwhere subjectno= (select Subjectno from ' Subject ' WHERE Subjectna Me= ' Advanced math-1 ') and examdate= (select MAX (examdate) from Resultwhere subjectno= (select Subjectno from ' Subject ' WHERE Subjectname= ' Advanced Mathematics-1 ') and studentresult>80) and subjectno= (SELECT subjectno from ' subject ' WHERE subjectname= ' Advanced Math-1 ') and examdate= (select MAX (examdate) from Resultwhere subjectno= (select Subjectno from ' Subject ' WHERE subjectname = ' Advanced math-1 ') ORDER by Studentresult Desclimit 0,5

--NOT EXISTS

--Check the last exam results of the "Advanced Math-1" course
-If all fail the exam (60 pass), think this exam is difficult, the calculation of the average score plus 5 points

--01. Query the "Advanced Math-1" course number select Subjectno from ' Subject ' WHERE subjectname= ' higher mathematics-1 '--02. Query Recent Exam scores select MAX (Examdate) From Resultwhere subjectno= (SELECT subjectno from ' subject ' WHERE subjectname= ' higher mathematics-1 ')  --03. Query scores greater than 60   Back to select Studentresult from Resultwhere studentresult>60and subjectno= (select Subjectno from ' Subject ' WHERE Subjectname= ' Advanced math-1 ') and examdate= (select MAX (examdate) from Resultwhere subjectno= (select Subjectno from ' Subject ' WHERE subjectname= ' Higher mathematics-1 ')  --04. If all fail the exam, the average score of the test is 5 points select AVG (studentresult) +5  from Resultwhere not EXISTS (select Studentresult from Resultwhere Studentresult>60and subjectno= (select Subjectno from ' Subject ' WHERE subjectname= ' higher mathematics-1 ') and examdate= (select MAX ( examdate) from Resultwhere subjectno= (SELECT subjectno from ' subject ' WHERE subjectname= ' higher mathematics-1 '))) and subjectno= ( Select Subjectno from ' Subject ' WHERE subjectname= ' higher mathematics-1 ') and examdate= (select MAX (examdate) from Resultwhere Subjectno = (SELECT subjectno from ' subject 'Where subjectname= ' higher mathematics-1 ')  --if there is a grade name is a sophomore student, the name of the grade is a freshman of all student information--  01. First check out the corresponding grade number select Gradeid from Grad e  where gradename= ' freshman ' SELECT Gradeid from grade  where Gradename= ' sophomore '--  02. Is there a   grade name in the student table is sophomore Student SELECT * from  student  where  gradeid= (select Gradeid from grade  WHERE gradename= ' sophomore ')--03. If The name of the grade is all student information for freshman SELECT * FROM studentwhere  EXISTS (SELECT * from  student  where  gradeid= (select Gr Adeid from grade  where Gradename= ' sophomore ') and gradeid= (SELECT Gradeid from grade  where gradename= ' freshman ')

--Considerations for using sub-queries
--01. Any place where expressions are allowed can use subqueries
--02. Columns that appear only in the subquery but do not appear in the parent query cannot contain the columns in the result set!

SQL optimization

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

exists only returns TRUE or false. Do not return result sets
In returns the result set

--Query The student information of the surname Li   % represents 0 or more characters   _ represents a character SELECT * from student WHERE studentname like ' li% ' select * FROM student W Here studentname like ' li _ '--use in to complete the above code select * from student where studentname in (select Studentname from student where St Udentname like ' li% ')--  in (Multiple data--"return result set)--use EXISTS to replace select * FROM student WHERE EXISTS (select Studentname from Stude NT) and studentname like ' li% '--exists (there is no data)  --statistics each course is divided by the number of Group by  column name Group Select SUBJECTNO,AVG ( Studentresult)   from Resultgroup by  subjectno--for courses with an average of more than 60 course numbers and an average of select Subjectno,avg (Studentresult) & nbsp From Resultgroup by  subjectnohaving avg (studentresult) >60  --Conditions after grouping--statistics on the average number of each course in descending order select Subjectno,avg (Studentresult)   from Resultgroup by  Subjectnoorder by AVG (studentresult) desc--If the results are the same   Sort by course number in ascending order select Subjectno,avg (studentresult)   from Resultgroup by  Subjectnoorder by AVG (Studentresult) Desc,subjectno--grouping statistics of men and women in each grade select  GradEid grade number, sex sex, count (sex) number from Studentgroup by Gradeid,sex 

--Create TABLE IF not  exists   examtest ( id  INT (2) Not null, sex VARCHAR (20))--Also new increase Strip data insert INTO examtest  VALUES (1, ' Male '), (2, ' Male '), (3, ' Female '), (4,null);  SELECT sex as ' gender ', count (sex) as ' number ' from Examtestwhere sex are not nullgroup by Sexorder by COUNT (sex) DESC SELECT sex as ' Gender ', count (sex) as ' number ' from Examtestgroup by sexhaving sex are not nullorder by COUNT (sex) DESC SELECT sex as ' gender ', COUNT ( Sex) as ' number ' from examtestwhere  sex in (' Male ', ' female ') GROUP by Sexorder (sex) DESC  --creating Tables create  Table IF not EXISTS mytable (' name ' VARCHAR (Ten) Not null,class  INT (4) Not null,sorce  DOUBLE not NULL)--insert data in To MyTable VALUES (' Little black 1 ', 1,88), (' Little Black 2 ', 1,80), (' Little Black 3 ', 1,68), (' Little black 4 ', 1,70), (' Little black 5 ', 1,98), (' Little black 6 ', 1,90), (' Little white 1 ', 2,88), (' Small white 2 ', 2,80), (' Small white 3 ', 2,68), (' Small white 4 ', 2,70), (' Small white 5 ', 2,98), (' Small white 6 ', 2,90)--  find the top three of the score in the table select * FROM Mytableorder by Sorce Desclimit 0,3--  find top three of each class    select * FROM MyTable t1where (select COUNT (1) FROM mytable t2where   t1. ' Sorce ' <t2. ' Sorce ' and t1.class=t2. ' class ') <3order by Class,sorce desc  

The result set in the inner link:
Cartesian product: A product of two table records!
The Cartesian product, also called Descartes, was proposed by a man called Descartes.
In simple words, the result of multiplying the two sets.
The Cartesian product is defined as the Cartesian products of two sets X and Y in mathematics (Cartesian product), also called direct product, expressed as XXY, the first object is a member of X and the second object is one of all possible ordered pairs of Y [1].

Table in connection
On two tables which column establishes an association relationship
(All tables connected similarly)

INNER JOIN: Find a common row by matching the public columns in two tables!

Left OUTER join: Left table, no data in right table returns null

Right outer join: With the right table, no data in the left table returns null

Implicit INNER join: Find a common row by matching the public columns in two tables!

Self-linking using a table as multiple tables The key is to use aliases

--Output student name and corresponding grade name within the link select Studentname,gradename from student INNER JOIN gradeon student. ' Gradeid ' =grade. ' Gradeid '-- Implicit internal connection Select Studentname,gradename from Student,gradewhere student. ' Gradeid ' =grade. ' Gradeid '--the student's name and grade name are 1 of the examination course number. and subject names and grades 01.  SELECT S.studentname,gradename,subjectname, Studentresult from student sinner joins grade G on (S.gradeid=g.gradeid) INNER Join ' subject ' B on (g.gradeid=b.gradeid) INNER join result R on (B.SUBJECTNO=R.SUBJECTNO) and S.studentno=r.studentnoand b . subjectno=1

02.

SELECT studentname,gradename,subjectname, Studentresult fromstudent s,grade g, ' subject ' B,result rwhere s.gradeID= G.gradeidand g.gradeid=b.gradeidand s.studentno=r.studentnoand b.subjectno=r.subjectnoand b.subjectNo=1--Query columns  Not in the same table! You must use a connection query! Establish an association relationship! --temporary table only the current connection is visible with the connection closed automatically delete--temporary table additions and deletions will not affect the real table CREATE temporary table mystudent (SELECT * FROM student) SELECT * F ROM mystudent Delete from mystudent--Data deletion of temporary table SELECT * FROM student--does not affect the true table

Self-connect

--self-linking using a table as multiple tables The key is to use the alias SELECT * from teacher--Query the name of the teacher 3 and the name of the corresponding tutor--T1 teacher T2 Tutor's tutor number = = Mentor's Number SELECT t1. ' Name ' as a,t2. ' Name ' as tutor name from teacher T1,teacher T2 WHERE T1. ' Name ' = ' Teacher 3 ' and T2.id=t1.tid

Advanced query Statement ____ Mysql