©2020-2024 ioDraw All rights reserved
In daily development and data query , We need to join multiple tables equivalently or left and right to query related fields
Of course we are MySQL We need to be familiar with the whole framework of grammar knowledge , Only in this way can we operate our data more familiar
<> Table building
# according to E-R Figure create table DROP TABLE IF EXISTS wxw_data; CREATE TABLE wxw_data ( SN CHAR(20)
PRIMARY KEY, NAME VARCHAR(50) NOT NULL, PRICE DOUBLE, EDITOR varchar(50) ); DROP
TABLE IF EXISTS wxw_CANKAO; CREATE TABLE wxw_CANKAO ( JIAOCAI_SN CHAR(20),
COURSE_CODEchar(8), type varchar(50), PRIMARY KEY (JIAOCAI_SN,COURSE_CODE),
CONSTRAINT fk_jiaocai_sn FOREIGN KEY (JIAOCAI_SN) REFERENCES wxw_data(SN),
CONSTRAINT fk_course_code FOREIGN KEY (COURSE_CODE) REFERENCES tb_course(CODE) )
;
<> increase
USE XSXK; INSERT INTO wxw_data VALUES ('978-7-115-48910-4','MySQL Database technology and Application ',
42.00,' Zhang Suqing '); INSERT INTO wxw_data SELECT '978-7-115-48910-5','MySQL Database technology and Application ',
42.00,' Zhang Suqing '; # Insert your own information INSERT INTO tb_student SELECT '2019888888','tanguangyu',
' male ','2010-10-10','15902333748', ID from tb_major_class where COLLEGE=' School of mathematics and big data '
and grade=2019 and class=1 and major=' big data '; SELECT * FROM tb_student where ID=
'2019888888'; INSERT INTO tb_student SELECT '2018888888','tanguangyu',' male ',
'2010-10-10','15902333748', ID from tb_major_class where COLLEGE=' School of mathematics and big data ' and
grade=2018 and class=1 and major=' big data '; SELECT * FROM tb_student where ID=
'2018888888';
<> Delete
USE XSXK; DELETE FROM tb_student where ID='2018888888';
Delete a row of data under this condition
<> change
UPDATE tb_student SET GENDER=' female ' WHERE ID='2019888888';
-- Navigate to ID It's all right for this , Then change the gender to female , The basic operation is timely update Table name set Fields that need to be changed =' New field properties ' where Conditional orientation SELECT
* FROM tb_student where ID='2019888888';
<> check
# Find out which classes each teacher brings select CODE Class number , TEACHER_ID Teacher number from tb_course_class; # 1,
What are the fields returned SELECT t.NAME Teacher's name , cc.CODE Class number # 2, From which tables are the fields to be returned ( Temporary table , view , Table connection , Subquery result set ) Get from
FROM tb_teacher t, tb_course_class cc # 3, Determine from which rows the fields to be returned are obtained WHERE t.ID = cc.
TEACHER_ID; # How many classes did each teacher bring # 1, What are the fields returned SELECT t.NAME Teacher's name , COUNT(cc.CODE)
Number of classes # 2, From which tables are the fields to be returned ( Temporary table , view , Table connection , Subquery result set ) Get from FROM tb_teacher t, tb_course_class cc
# 3, Determine from which rows the fields to be returned are obtained WHERE t.ID = cc.TEACHER_ID # 4, What are the conditions for group aggregation GROUP BY t.NAME;
# How many classes did each teacher bring , Sort by quantity # 1, What are the fields returned SELECT t.NAME Teacher's name , COUNT(cc.CODE) Number of classes #
2, From which tables are the fields to be returned ( Temporary table , view , Table connection , Subquery result set ) Get from FROM tb_teacher t, tb_course_class cc # 3,
Determine from which rows the fields to be returned are obtained WHERE t.ID = cc.TEACHER_ID # 4, What are the conditions for group aggregation GROUP BY t.NAME # 5,
Sort the default positive order from small to large ORDER BY Number of classes DESC; # How many classes did each teacher bring , Sort by quantity , Filter greater than 5 A class teacher # 1, What are the fields returned
SELECT t.NAME Teacher's name , COUNT(cc.CODE) Number of classes # 2, From which tables are the fields to be returned ( Temporary table , view , Table connection , Subquery result set ) Get from
FROM tb_teacher t, tb_course_class cc # 3, Determine from which rows the fields to be returned are obtained WHERE t.ID = cc.
TEACHER_ID# 4, What are the conditions for group aggregation GROUP BY t.NAME # 5, Filter the query result set again HAVING Number of classes >5; #
How many classes did each teacher bring , Sort by quantity , Filter greater than 5 A class teacher # 1, What are the fields returned SELECT t.NAME Teacher's name , COUNT(cc.
CODE) Number of classes # 2, From which tables are the fields to be returned ( Temporary table , view , Table connection , Subquery result set ) Get from FROM tb_teacher t,
tb_course_class cc# 3, Determine from which rows the fields to be returned are obtained WHERE t.ID = cc.TEACHER_ID # 4, What are the conditions for group aggregation
GROUP BY t.NAME # 5, Filter the query result set again HAVING Number of classes >5 ORDER BY Number of classes LIMIT 1; # Subquery #
where Subquery in Clause # Which college does a course belong to SELECT DISTINCT COLLEGE FROM tb_major_class WHERE
IDIN (SELECT MAJOR_CLASS FROM tb_student WHERE ID IN (SELECT STUDENT_ID FROM
tb_electivesWHERE COURSE_CLASS_CODE IN (SELECT CODE FROM tb_course_class WHERE
COURSE_CODEIN ( SELECT CODE FROM tb_course WHERE NAME='Web Fundamentals of programming ' ) ) ) ); #
Table connection （ Internal connection ） # Which college does a course belong to SELECT DISTINCT mc.COLLEGE FROM tb_major_class mc,
tb_student s, tb_electives e, tb_course_class cc, tb_course c WHERE mc.ID = s.
MAJOR_CLASSand s.ID = e.STUDENT_ID and e.COURSE_CLASS_CODE = cc.CODE and cc.
COURSE_CODE= c.CODE and c.NAME like 'Web%'; # Which college does a course belong to SELECT DISTINCT c.
NAME curriculum ,mc.COLLEGE college FROM tb_major_class mc, tb_student s, tb_electives e,
tb_course_class cc, tb_course c WHERE mc.ID = s.MAJOR_CLASS and s.ID = e.
STUDENT_IDand e.COURSE_CLASS_CODE = cc.CODE and cc.COURSE_CODE = c.CODE; #
SELECT Generate a result set （ Two dimensional table ） SELECT 1,2; SELECT 'abc'; SELECT CURRENT_TIME(); # FROM
Scan a two-dimensional table structure （ Entity table , cursor , Query result set ） SELECT tb_course.NAME, CODE FROM tb_course; # FROM
Table connection （ New temporary table ） -- Cross connect （ Cartesian product ） A CROSS JOIN B => Number of columns =A+B Number of rows =A*B SELECT * from tb_course
,tb_teacher; SELECT * FROM tb_course CROSS JOIN tb_teacher; # Internal connection SELECT * from
tb_courseas c,tb_course_class as cc WHERE c.CODE=cc.COURSE_CODE; # Query the course name of the course class
# Check the course number corresponding to the course class SELECT COURSE_CODE,CODE FROM tb_course_class WHERE CODE=
'3SL1113A.09'; # Check the course name SELECT * FROM tb_course WHERE CODE = (SELECT COURSE_CODE
FROM tb_course_class WHERE CODE='3SL1113A.09'); SELECT a.*,b.NAME FROM (SELECT
COURSE_CODE,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a ,
tb_course bWHERE a.COURSE_CODE=b.CODE; SELECT a.*,b.NAME FROM (SELECT Teacher_id
,CODE FROM tb_course_class WHERE CODE='3SL1113A.09') as a INNER JOIN tb_teacher
bON a.teacher_id=b.ID; -- join condition SELECT cc.CODE,cc.COURSE_CODE,t.NAME FROM
tb_teacher tINNER JOIN tb_course_class cc ON t.ID=cc.teacher_id WHERE cc.CODE=
'3SL1113A.09'; SELECT cc.CODE,c.NAME,t.NAME from tb_course c, tb_teacher t,
tb_course_Class ccWHERE c.CODE=cc.COURSE_CODE and t.ID=cc.Teacher_id; SELECT cc.
CODE Class number ,c.NAME Course name ,t.NAME Teacher from tb_course_class cc JOIN tb_course c ON cc
.COURSE_CODE=c.CODE JOIN tb_teacher t ON cc.teacher_id=t.ID where cc.CODE=
'3SL1113A.09'; # External connection （ Left link , Right link ） # WHERE Conditional judgment => whether SELECT SELECT 'a' FROM
tb_courseWHERE 1=1; # query 2 Course information of credit SELECT * FROM tb_course WHERE CREDIT=2; #
Inquire about the grades of the students who failed SELECT * FROM tb_electives WHERE SCORE < 60; # LIKE ( I don't like it , It's like ） %
wildcard , Match any 0 One or more characters SELECT * FROM tb_course WHERE NAME like '% Programming '; # Query and ‘ I ’ Information of students with the same surname
SELECT * FROM tb_student WHERE NAME like ' Tan % fly '; # IN ==> OR OR OR SELECT * FROM
tb_studentWHERE NAME=' Tan Fei ' OR NAME=' Tan Hua ' OR NAME=' Tan Bei '; SELECT * FROM tb_student
WHERE NAME IN (' Tan Fei ',' Tan Hua ',' Tan Bei '); # Subquery # query 3SL1037A.01 Students with the highest class scores ID # 1. First you have to know the highest score
SELECT MAX(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.01'; #
2. Who is the highest score SELECT * FROM tb_electives WHERE SCORE=100 AND COURSE_CLASS_CODE=
'3SL1037A.01'; SELECT * FROM tb_electives WHERE COURSE_CLASS_CODE='3SL1037A.02'
ORDER BY SCORE ASC LIMIT 1; UPDATE tb_electives SET SCORE=100 WHERE
COURSE_CLASS_CODE='3SL1037A.01' and SCORE=99; SELECT * FROM tb_electives WHERE
SCOREIN (SELECT MIN(SCORE) FROM tb_electives WHERE COURSE_CLASS_CODE like
'3SL1037A.0%') AND COURSE_CLASS_CODE like '3SL1037A.0%'; # Lowest score for query SELECT MIN(
SCORE) FROM tb_electives ; # Query the lowest score by shift SELECT MIN(SCORE),COURSE_CLASS_CODE FROM
tb_electivesGROUP BY COURSE_CLASS_CODE; # Query the students with the lowest score by class SELECT MIN(SCORE),
COURSE_CLASS_CODEFROM tb_electives GROUP BY COURSE_CLASS_CODE; select t.
STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE from tb_electives t INNER JOIN (SELECT
MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY
COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms
.minscore ; select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME from
tb_electives tINNER JOIN (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM
tb_electivesGROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.
COURSE_CLASS_CODEand t.SCORE=ms.minscore LEFT JOIN tb_student s ON t.STUDENT_ID=
s.ID; select t.STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME Student name ,c.NAME Course name
from tb_electives t INNER JOIN (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE
FROM tb_electives GROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.
COURSE_CLASS_CODEand t.SCORE=ms.minscore LEFT JOIN tb_student s ON t.STUDENT_ID=
s.ID LEFT JOIN tb_course c ON instr(t.COURSE_CLASS_CODE,c.CODE); select t.
STUDENT_ID,t.COURSE_CLASS_CODE,t.SCORE,s.NAME Student name ,c.NAME Course name ,cc.TEACHER_ID from
tb_electives tINNER JOIN (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM
tb_electivesGROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.
COURSE_CLASS_CODEand t.SCORE=ms.minscore LEFT JOIN tb_student s ON t.STUDENT_ID=
s.ID LEFT JOIN tb_course c ON instr(t.COURSE_CLASS_CODE,c.CODE) LEFT JOIN
tb_course_class ccON t.COURSE_CLASS_CODE=cc.CODE; select t.STUDENT_ID,t.
COURSE_CLASS_CODE,t.SCORE,s.NAME Student name ,c.NAME Course name ,te.NAME Teacher's name ,te.TITLE Teacher title from
tb_electives tINNER JOIN (SELECT MIN(SCORE) minscore,COURSE_CLASS_CODE FROM
tb_electivesGROUP BY COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.
COURSE_CLASS_CODEand t.SCORE=ms.minscore LEFT JOIN tb_student s ON t.STUDENT_ID=
s.ID LEFT JOIN tb_course c ON instr(t.COURSE_CLASS_CODE,c.CODE) LEFT JOIN
tb_course_class ccON t.COURSE_CLASS_CODE=cc.CODE LEFT JOIN tb_teacher te ON cc.
TEACHER_ID=te.ID; select mc.COLLEGE college , concat(mc.MAJOR,mc.GRADE,'-',mc.CLASS)
as Professional class , t.STUDENT_ID Student number , s.NAME Student name , t.COURSE_CLASS_CODE Course class number , c.NAME Course name ,
t.SCORE fraction , te.NAME Teacher's name , te.TITLE Teacher title from tb_electives t INNER JOIN (SELECT
MIN(SCORE) minscore,COURSE_CLASS_CODE FROM tb_electives GROUP BY
COURSE_CLASS_CODE) ms ON t.COURSE_CLASS_CODE=ms.COURSE_CLASS_CODE and t.SCORE=ms
.minscore LEFT JOIN tb_student s ON t.STUDENT_ID=s.ID LEFT JOIN tb_course c ON
instr(t.COURSE_CLASS_CODE,c.CODE) LEFT JOIN tb_course_class cc ON t.
COURSE_CLASS_CODE=cc.CODE LEFT JOIN tb_teacher te ON cc.TEACHER_ID=te.ID LEFT
JOIN tb_major_class mc ON mc.ID=s.MAJOR_CLASS; # query ‘ I ’ Information of the professional class # Where can I find a professional class ：
tb_major_class # Known conditions ： I =》 School of mathematics and Physics , Big data ,2019 level , 3 class SELECT * FROM tb_major_class
WHERE COLLEGE=' School of mathematics and big data ' AND MAJOR=' big data ' AND GRADE=2019 AND CLASS=3; #
My major class number is 43 # How many people are there in my class # Statistical quantity COUNT() # people - Number of students , Where are the students looking ： tb_student SELECT COUNT(
*) AS Number of students , MAJOR_CLASS AS Class number FROM tb_student WHERE MAJOR_CLASS=43; #
Group statistics of the number of students in each class SELECT COUNT(*) AS Number of students , MAJOR_CLASS AS Class number FROM tb_student GROUP
BY MAJOR_CLASS; # Group statistics of the number of students in each class , Sort by number of students # major class Number of professional classes SELECT COUNT(*)
AS Number of students , MAJOR_CLASS AS Class number FROM tb_student GROUP BY MAJOR_CLASS ORDER BY Number of students
DESC; # course class Class size # Where are the students in the class looking ： tb_electives SELECT COUNT(*) as Number of students ,
COURSE_CLASS_CODEas Course class number FROM tb_electives GROUP BY COURSE_CLASS_CODE;
Every word
Learning is more than learning , But also learn to transfer and use , Finally internalized into their own knowledge points
Technology