Summary of SQL statements

Common keywords in SQL statements and their explanations are as follows:1Select selects data from a table in the database and two keywords: select from a table in the (from) database. Syntax for select"Field name" from "Table name". 2DISTINCT The above select keyword and add a DISTINCT can be removed from the selected field of repetition, thus completing the table/The function of what different values are in the field. Syntax for select DISTINCT"Field name" from "Table name". 3where this keyword can help us selectively capture data rather than take it all out. Syntax for select"Field name" from "Table name" WHERE "condition" 4the where directive in and or above can be used to conditionally select data from a table. This condition may be simple (like the previous page example), or it may be complex. A complex condition is a connection of two or more simple conditions through and OR OR. The syntax is: SELECT"Field name" from "Table name" WHERE "simple Condition" {[And|or] "Simple condition"}+5In SQL, the in command is used in two cases, and this page describes one of them: the one that is related to where. In this usage, we have already known at least one of the values we need, and we put these known values in this clause. The syntax is: SELECT"Field name" from table name "WHERE" column name "in (' Value one ', ' Value two '), ...) 6betweenin This instruction allows us to grab the values in the database according to one or several discontinuous (discrete) values, while between allows us to use a range (range) to grab the values in the database, with the syntax: SELECT "Field name" from "Table name" WHERE "field name" between ' Value one ' and ' value two ' 7Likelike is another directive that will be used in the WHERE clause. Basically, like allows us to find the information we need based on a pattern. The syntax is: SELECT"Field name" from table name "WHERE" field name "Like {schema}8ORDER by we often need to be able to make a systematic display of the captured data. This may be by small toward large (ascending) or by large toward small (descending). In this case, we can use order by as an order to achieve our purpose. The syntax is: SELECT"Field name" from "Table name [WHERE" condition "] ORDER by" field name [ASC, DESC] 9function functions allow us to perform operations on the rows or columns in which the number's form exists, including AVG (average), count (count), Max (max), min (minimum), SUM (summation). The syntax is: SELECT"Function name" ("Field name") from "Table name" 10count this keyword can help me we count how many data are selected, the syntax is: SELECT COUNT ("Field name") from "table name" 11The group Bygroup by statement is used to combine aggregate functions to group result sets based on one or more columns. The syntax is: SELECT"Field 1", SUM ("Field 2") from "table name" GROUP by "Field 1" 12having this keyword can help us set the conditions for the value that the function produces. The syntax is: SELECT"Field 1", SUM ("Field 2") from "table name" GROUP by "Field 1"Having (function condition)13Alias We can specify an alias for the column name and table name via alias, the syntax is: SELECT"Table Aliases". Field 1 "field alias" from "Table name" "Table alias"Here is an example, through which we should be able to master the use of the above keyword method. Student (s#,sname,sage,ssex) Student Table Course (c#,cname,t#) Timetable SC (s#,c#,score) score table teacher (t#,tname) Teacher Table questions:1, the query "001" Course than "002"study number of all students with high course performance; Select A.s#from (select S#,score from SC where C #= ' 001A, (select S#,score from SC where C #= ' 002) Bwhere A.score>b.score and a.s#=b.s#;2, the number of students with average scores greater than 60 points and average scores; Select S#,AVG (Score) from Scgroup by s# have avg (score)>60;3, query All students of the student number, name, number of courses selected, total; select Student.s#,student.sname,count (SC. C #), SUM (score) from Student to Outer join SC on student.s#=SC. S#group by Student.s#,sname4, the number of teachers who queried the surname "Li"; select COUNT (Distinct (tname)) from Teacherwhere tname like ' Lee%';5, inquiry did not learn the "cotyledons" teacher class of the student's school number, name; Select Student.s#,student.snamefrom studentwhere s# not in (SC. s#) from Sc,course,teacher where sc.c#=course.c# and teacher.t#=course.t# and Teacher.tname=' cotyledons ');6, the inquiry learned "001" and also learned the number "002The student's number and name of the course; Select Student.s#,student.snamefrom student,scwhere student.s#=sc. s# and SC. C#= ' 001′and exists (Select * from SC as sc_2 where SC_2.S#=SC. s# and sc_2.c#= ' 002);7, inquiring about the students ' school numbers and names of all the classes taught by "cotyledons" teachers; select S#,snamefrom studentwhere s# in (select S#from SC, Course, Teacherwhere sc.c#=course.c# and teacher.t#=course.t# and Teacher.tname= ' cotyledons ' GROUP by s# have count (SC. C #) = (select count (C #) from Course,teacher where teacher.t#=course.t# and tname=' cotyledons '));8, check the student's number and name of all the students whose course scores are less than 60 points; Select S#,snamefrom Studentwhere s# not in (select student.s# from STUDENT,SC where s.s#=sc. s# and Score>60);9, the inquiry did not learn all classes of the students of the school number, name; Select Student.s#,student.snamefrom student,scwhere student.s#=SC. S#group by Student.s#,student.sname have count (C #)<(select count (C #) from Course);10, the inquiry has at least one course and the study number is "1001"Students learn the same student's number and name; Select S#,snamefrom student,scwhere student.s#=sc. s# and C # in (select C # from SC where s#= ' 1001 ');11, delete the SC table record of "cotyledons" teacher class; Delect Scfrom course, Teacherwhere course.c#=sc. C # and course.t#= teacher.t# and tname= ' cotyledons ';12, query the highest and lowest scores for each section: Show as follows: Course ID, highest score, Min. Select l.c# Course id,l.score highest score, R.score min. from SC L, SC rwhere l.c#=R.c#andl.score=(SELECT MAX (il.score) from SC il,student imwhere IL. C #= L.c# and IM. s#=IL. S#group by IL. C #) Andr.score=(SELECT MIN (ir.score) from SC irwhere IR. C #=R.c#group by IR. C #);13, query average students ' scores and their rankings select1 +(select COUNT (distinct average score) from (select S#,avg (score) average score from Scgroup by s#) T1where average score>T2. Average score) rank, s# student number, average score from (SELECT S#,avg (score) average score from SC GROUP by s#) T2order by average score desc;14, check the records of the top three grades of each section: (regardless of the performance of the situation) SELECT t1. s# as student id,t1. C # as course Id,score as fraction from SC t1where score in (SELECT TOP3scorefrom scwhere t1. C #=C#order by score DESC) ORDER by T1. C #;15, query the top two select T1 for each of the best results. s# as student id,t1. C # as course Id,score as fraction from SC t1where score in (SELECT TOP2scorefrom scwhere t1. C #=C#order by score DESC) ORDER by T1. C #;

Summary of SQL statements