Database basic query and advanced query

First, the basic query:

Select* fromFruit--Check AllSelectName,source fromFruit--Check specific columnsSelectIds' Code ', Name' name ', Price' Price ', Source' origin '  fromFruit--Modify column namesSelect* fromFruitwhereids='K006'Select* fromFruitwherePrice=2.4and source=' Yantai '--check the specified line according to criteriaSelect* fromFruitwherePrice between2.0and4.0--check the designated line by ScopeSelect* fromFruitwhereNumbersinch( -, the, -)--check the specified line, discreteSelectDistinct Numbers fromFruit--go to re-querySelect* fromNewsSelect* fromNewswhereTitle like'% Account'--fuzzy Query, check the end of the accountSelect* fromNewswhereTitle like'Road Fly%'--fuzzy Query, check the beginning of the road to flySelect* fromNewswhereTitle like'% Road Fly%'--fuzzy query, to include the road flySelect* fromNewswhereTitle like'% Foreign Currency goods _'--fuzzy Query, check the foreign currency goods after only one characterSelect* fromFruit ORDER BY Numbers ASC--Follow the numbers column in ascending order, if you do not add ASC by default in ascending rowSelect* fromFruit ORDER BY Numbers Desc--sort in descending order of numbers columnsSelect* fromFruit ORDER BY Numbers,price--Follow the numbers row first, then the price rowSelectCOUNT (*) fromFruit--returns how many data are in the fruit tableSelectAVG (Numbers) fromFruit--returns the average of a columnSelectSUM (Numbers) fromFruit--returns all data for a column andSelectMAX (Numbers) fromFruit--returns the maximum value in a columnSelectMIN (Numbers) fromFruit--returns the minimum value in a columnSelect*, (price*0.8) as ' Price after discount '  fromFruit--add a column not in the database, this is the price column after 80 percentSelectNumbers, COUNT (*) fromFruit GROUP BY Numbers--find out the number of members in the group based on a column groupingSelectNumbers, COUNT (*) fromFruit GROUP BY Numbers have COUNT (*) >1--based on a column grouping, the number of members in the group is calculated, returning the number of members greater than 1

Second, advanced query:

--Connection QuerySelect* fromInfo,nation--To form a Cartesian productSelect* fromInfo,nationwhereNation.code=info.nation--join on Inner connectionSelect* fromInfo Join Nation on info.nation =Nation.code--find out which students have a number of examsSelectStudent.sname,course.cname,score.degree fromStudent join score on Score.sno=student.sno join course on course.cno =Score.cno--right connection, the right table must show full, if the table on the left does not have the corresponding information, then fill the empty valueSelect* fromInfo right Join Nation on info.nation=Nation.code--left connection, the left table must show all, if there is no information corresponding to the table on the right, then fill the empty valueSelect* fromInfo left join Nation on info.nation=Nation.code--Fully connected, both sides of the table are displayed completelySelect* fromInfo full join Nation on info.nation=Nation.code--Federated Query, federated display of two or more identical tables for isolated structuresSelectCode,name frominfounionSelectInfocode,name fromFamily--------Sub-query--------------------The result of the subquery is treated as a condition of the parent querySelect* fromInfo--regardless of the subquery, the subquery execution is independent, and the parent query is irrelevant (nothing to the parent query)Select* fromInfowhereYear (Birthday) =(SelectYear (Birthday) fromInfowhereCode='p005')--related sub-querySelect* fromTeacher--teachers ' information of different titles of computer department and Electronic Engineering departmentSelect* fromTeacher T1wheredepart='Computer Department'And not exists (Select* fromTeacher T2wheredepart='Department of Electronic Engineering'and t1.prof =t2.prof) UnionSelect* fromTeacher T1wheredepart='Department of Electronic Engineering'And not exists (Select* fromTeacher T2wheredepart='Computer Department'and t1.prof =t2.prof)--inquire about other student information other than the highest score for each course. Select* fromscoreSelect* fromScorewhereDegree notinch(SelectMAX (degree) fromScore GROUP by CNO)--ErrorSelect* fromScore S1whereDegree notinch(SelectMAX (degree) fromScore S2 GROUP by CNO have s1.cno =s2.cno)--Select* fromScorewhereDegree notinch( the, the)--page OutSelect* fromCarSelectTop5* fromCar--first 5 data, page oneSelectTop5* fromCarwhereCode notinch(SelectTop5Code fromCar)--data on the second pageSelectTop5* fromCarwhereCode notinch(SelectTopTenCode fromCar)--data on page ThreeSelectTop5* fromCarwhereCode notinch(SelectTop (5*2) Code fromCar)SelectCeiling (COUNT (*)/5) fromCar--Find total PagesSelect* fromCarwhereConditional limit skips a few data, takes several data--mysql inside the pagination

Database basic query and advanced query