第4章 例题(子查询和group by子句)（Chapter fourth examples (sub queries and group by clause)）.doc

第4章 例题(子查询和group by子句)（Chapter fourth examples (sub queries and group by clause)） The select statement syntax: Select [distinct] 1 field, 2 field, 3 field, n field | * from... [where query expression] [grop by group field] [having packet data for each condition increased [order by [asc|desc]] | sort field sort expression Select the column 4.2.1 1, select the specified column In 4.5 cases Use xscj; Select name, professional name, total credits from xs; 2, the definition of a column alias 4.6 definition column alias (defined alias only this kind of format in Mysql) The select code as number, the name as name, as mark from XS total credits; When a column alias alias box, with both ends of the single quotes. Select asStudent numberas school, the name ofStudent name as mark from XS, total credits; If the specified column alias for capital, do not need to add single quotes, upper or effect, as in the following example. The select code as NUMBER, the name as NAME, as MARK from XS total credits; 3, replace the data in the query result Case expression format: Case when 1 then 1. When 2 then 2. ... When n then n. The expression of else End The original data of 4.7 cases using the case statement to query the replacement. Select the student number, name, case When is null then is not the total credit course When 50 then total credits failed When =50 and total credits total credits =52 then qualified Else excellent The end as class From XS where professional name = computer; 4, calculate the column value In 4.8 cases Select school, courses, grades *1.20 as 120from xs_kc where record number: =081101; 5, eliminate duplicate rows in the result set In 4.9 cases Select distinct professional name, total credits from xs; 6, the aggregation function (statistical function) In 4.10 cases Select count (*) the total number of students from XS as; In 4.11 cases Select count (notes) the total number of students from XS as; In 4.12 cases Select count (total credits) as total credits 50 points