[Oldboy-django] [2 deep Django]mysql query statement--native SQL

#1Add (a total of three ways) # Inserts a single record insert into T1 (name,...) VALUES ('LZP',..); Note: T1 (name,...) Must contain all non-empty columns (except for the self-increment column) # Insert multiple record insert into T1 (name,age) VALUES ('LZP', A),('Alex', -); # Copy copy (copy data from T2 table to T1) insert into T1 (name,age) valuesSelectName,age fromt2;#2Change Update T1SetName='b', age= - whereName='LZP'; Update T1SetName='C' whereName='Alex';#3Check (emphasis)Select[...] fromTB [...] From the previous [] extension:SelectName asCNAME, age fromtb1; # aliasSelectName, age,1  fromtb1; # Multiple display of a column, the value of which is a constantSelectName, age, variable fromtb1; # Displays a column, the value of which is a variable, the definition of the variable is implemented from the previous [] extension:1 where        SelectName, age fromTb1whereIdinch（2,4,6) # id =2or id=4, or id=6Collection2,4,6)        SelectName, age fromTb1whereID <>3# <> Not equal to (less than < or greater than >)SelectName, age fromTb1whereID notinch(2,4,6)        SelectName, age fromTb1whereID between5and A; # IDinch[5, A] #闭区间集合 [5, A]        SelectName, age fromTb1whereIdinch(SelectId fromTB2); # wildcard Characters%(multiple arbitrary characters), underscore _ (an arbitrary character)SelectName, age fromTb1whereName like"a%"# Start with a name note: This cannot be allowedwhereID =1and id=2, it must be null to find it.2paging limit [start], count; Start is starting from 0, the default value=0; count indicates the number of records viewedSelectName, age fromTB1 limitTen; #查看第一条数据到第10条数据;SelectName, age fromTB1 limitTen,Ten; #查看第11条数据到第20条数据 Simple paging: page=int(Input ("'). Strip ()) Start= (page-1)*Ten                  SelectName, age fromTB1 limit Start,TenLimitTenoffset= -; equivalent3Order by sort order BY column name [ASC|DESC] # column names are integersSelectName, age fromtb1 ORDER by ID desc;# sort from large to small by IDSelectName, age fromTB1 ORDER BY id DESC limit0,2; # Multi-column sort order BY cow_1 desc, cow_2 desc # Note is not an order by cow_1, the order by Cow_2 first in descending order of cow_1, if Cow_1 has the same record, The same records for Cow_1 are sorted in descending order of cow_24GROUP BY +aggregate function GROUP BY part_id: it can be understood that the same part_id is folded into a record (how to fold?). Go to weight (preferably without distinct) for the same record, it can be based on the function count (), Max (), Min (), SUM (), AVG () into one record these functions are called aggregate functions example1: User table, Department table to see how many people are in each departmentSelectPART_ID, COUNT (id), max (ID) fromtb1 GROUP by part_id; 5Grouping GROUP BY + aggregate function +two brush selection (having) the result of the aggregate function two times brush, must be used with havingSelectPART_ID, COUNT (ID) fromTB1 GROUP BY PART_ID have count (ID) >1# Brush selected departments with more than 1 departments6The even table operation selects all the tables and then tells them the relationshipSelect* fromUser, Departmentwhereuser.part_id =Department.id recommended: Left Join ... on (previous version performance is better, the current version of both performance is consistent) #将所有表select out;Select* fromTb1 left joins TB2 # on tb1.part_id=Tb2.id Tell them the relationship between the tablesSelect* fromUser left JOIN department on user.part_id =department.id7even table operation left join and right join, INNER join differenceSelect* fromTb1 LEFT Join TB2whereTB1 will show all, the table on the left will show all, and the inner join will be equivalent to the entire row that would be implemented by the leftmost join table, and then the whole line will be hidden (not shown) .8Multiple table ConnectionsSelect* fromTb1 left Join TB2where... # You can only tell the relationship between TB1 and TB2 at this time. Left JOIN TB3where... # At this point you can tell the relationship between TB1 and tb3, the relationship between TB2 and Tb3 (at this point, the TB1,TB2,TB3 has been linked in) when the table is encountered, if the column name is the same,*can become a table name. Selecttb1.id, tb2.id, Tb3.id fromTb1 left Join TB2where .. Left JOIN TB3where .. Example: http://Images2015.cnblogs.com/blog/425762/201608/425762-20160803224643778-2071849037.pnghttp//www.cnblogs.com/wupeiqi/articles/5729934.htmlstudent, class, teacher, course, score table: Student Table: Foreign key Class table class table: Curriculum: foreign key teacher into Performance table: Student ID, course ID, score, foreign key student table, foreign key curriculum #9The temporary table will query the resulting data as a temporary table, stored in memory, but not written to the hard disk. Syntax: (Select* fromTb1whereAge > -) asB; SelectSid from（Select* fromTb1whereAge > -) asB; #TenAdd display columns (if:SelectScore fromTb1whereName="Yuwen"can only be a value)SelectID, (SelectScore fromTb1whereName="Yuwen") fromTB2; TB1 and TB2 are not the same table, and the added display column is not the same row of data for the same table # OneAdd display ColumnsSelectID, (SelectScore fromTb1 asS2whereS2.id= s1.id) asScore fromtb1 asS1 # outermost loop, one line of data # inside the loop, a column and a column in each row # Aconditional Statements CaseWhen min (num) <TenThen0 Else 1End asC # -the ternary operation of MySQLif(True,1,0# Calculation course average score from high to show, show classroom teacher avg (if(IsNull (Score.num),0, Score.num)) # because when it's empty, it's impossible to calculateSelectscore.course_id, Course.cname, Teacher.tname, AVG (if(IsNull (Score.num),0, Score.num)) asAverage fromscore left JOIN course on score.course_id=Course.cid left join teacher on course.teacher_id=Teacher.tid GROUP BY score.course_id order by average DESC; # You can use average at this time, but you can't use average in select -Double LoopSelect* from            (Selecta.student_id, a.course_id, A.num, (SelectB.num fromScore asBwhereb.course_id = a.course_id ORDER BY b.num DESC limit0,1) asfrist_s, (SelectB.num fromScore asBwhereb.course_id = a.course_id ORDER BY b.num DESC limit1,1) assecond_s fromScore asA) asCwhereC.num >=c.second_s # theMulti-table operation, directly with the table, it will be better to understand and set the where # -# query did not learn the name of teacher Li Ping class students, ID---did not learn Li Ping any of the students of a class before the selection of the students who chose Li Ping any class, # met not, must be in the top layer of not, in the student table notinch(Learn the teacher course student's ID, as well as the group filter out duplicates) # First to find out what the teacher Li Ping taught lessons;--SelectStudent.sid,student.sname fromStudentwhereStudent.sid notinch (        --Selectstudent_id fromscore--wherescore.course_idinch(SelectCid fromCourse left join teacher on Course.teacher_id=teacher.tidwhereTeacher.tname='Mr. Li Ping')        --GROUP by student_id); # -  whereGROUP BY has: Advanced line where one brush selection, then group having brush selection # -union up and down table (Union Auto-weight, union All does not go heavy)SelectID, name fromTB1 UnionSelectSid, Sname Frou stb1;

[Oldboy-django] [2 deep Django]mysql query statement--native SQL