One, connection query--Query the name of the course name and grade
--Non-equivalent connection query
SELECT subjectname,gradename from ' subject ', Grade
--Equivalent connection query
SELECT Subjectname,gradename
From ' Subject ', Grade
WHERE subject.gradeid=grade. ' Gradeid '--outer joins left OUTER join to left table, no data in right table returns null
--Query course name, class number and grade name
SELECT Subjectname,gradename,classhour
From ' subject's left JOIN grade G
On s.gradeid=g. ' Gradeid '--Outer joins right outer joins with right table, no data in left table returns null
SELECT Subjectname,gradename,classhour
From ' subject ' right JOIN grade G
On s.gradeid=g. ' Gradeid '--Self connection query
CREATE TABLE IF not EXISTS teacher (
ID INT (10),--The teacher's own number
' Name ' VARCHAR (20),
PID INT (10)--tutor number for teacher
)--New teacher information
INSERT into teacher
VALUES
(1, ' Teacher 1 ', 5),
(2, ' Teacher 2 ', 5),
(3, ' Teacher 3 ', 5),
(4, ' Teacher 4 ', 3),
(5, ' Teacher 5 ', 3)--Check the name of the teacher and corresponding tutor
SELECT t1. ' Name ' as ' teacher name ', T2 ' name ' as ' tutor name '
From teacher T1,teacher T2
WHERE T2.id=t1.pidSecond, Order by--Query course name is database structure-1 of all exam scores and students ' names and viewed in descending order of grades
--ORDER by sort default is ascending ASC desc Descending
SELECT Subjectname,studentresult,studentname
From ' Subject ', result,student
WHERE
Student. ' Studentno ' =result. ' Studentno '
and
result. ' Subjectno ' =subject. ' Subjectno '
and
Subjectname= ' Database structure-1 '
ORDER by Studentresult DESCThird, limit--Query course name is database structure-1 of all exam scores and students ' names and viewed in descending order of grades
--ORDER by sort default is ascending ASC desc Descending
--Paging using limit begin,pagesize
--Begin from that data (current page-1) *pagesize
--The number of PageSize displayed per page
SELECT Subjectname,studentresult,studentname
From ' Subject ', result,student
WHERE
Student. ' Studentno ' =result. ' Studentno '
and
result. ' Subjectno ' =subject. ' Subjectno '
and
Subjectname= ' Database structure-1 '
ORDER by Studentresult DESC
LIMIT 10,5Four, sub-query--A subquery uses its own query result as a condition of another query statement
--Query course name is database structure-1 and score is not less than 80 students name and school number
SELECT Studentname,studentno
From student
WHERE Studentno in
(SELECT Studentno
From result
WHERE
subjectno= (SELECT subjectno from SUBJECT WHERE subjectname= ' database structure-1 ')
and studentresult>=80
)v. Statistical functions--Statistical functions
--count () sum () Avg () min () max ()
SELECT
COUNT (*) as total number of records,
Max (Studentresult) as highest score,
Min (studentresult) as the lowest score,
AVG (Studentresult) as average score,
SUM (Studentresult) as Total
From resultvi. Order of execution--Execution order
From table
WHERE condition
Group by group (column)
Conditions for having a group
Contents of the SELECT query
ORDER by---------code demo select Sex as Gender, count (sex) as Number of
From TB
WHERE sex is not NULL
GROUP by sex
ORDER by COUNT (sex) DESC SELECT sex as gender, count (sex) as Number of
From TB
WHERE Sex in (' Male ', ' female ')
GROUP by sex
ORDER by COUNT (sex) DESCVII. Transaction Isolation--Query MySQL default transaction isolation level
SELECT @ @tx_isolation
--Change the transaction isolation level
SET tx_isolation= ' read-committed '
--Open transaction
START transaction--rollback rollback--Commit Commit
Mysql (ii)
