第4章 例题(子查询和group by子句)（The fourth chapter is the example (subquery and group by clause)）.docVIP

第4章 例题(子查询和group by子句)（The fourth chapter is the example (subquery and group by clause)） The syntax format of the select statement: Select [distinct] 1 field, 2 field, 3 field, n field | * from... [where expression for query criteria] [grop by packet fields] Conditions for adding data to each group after [having grouping [order by [asc|desc]] | sort field sort expression 4.2.1 select column 1, select the specified column In 4.5 cases Use xscj; Select name, professional name, total credits from, xs; 2, define column alias Example 4.6 defines the column alias (defined in the Mysql alias only in this format) Select student number as number, name as name, total credits as, mark, from, xs; When the column alias has spaces, both ends of the alias are enclosed in single quotes. Select student number as,Student, number, name as,Student, name, total credits as, mark, from, xs; If a column alias is specified as uppercase, no single quotation marks are added, and uppercase is used as follows. Select student number as NUMBER, name as NAME, total credits as, MARK, from, xs; 3, replace the data in the query results The format of the case expression: Case when condition 1 then expression 1 When condition 2 then expression 2 ... When condition n then expression n Else expression End Example 4.7 uses the case statement to replace the querys raw data. Select number, name, case When total credits is, null, then have not been selected yet When total credits 50 then failed When total credits =50, and, total credits, =52, then, qualified Else excellent End as class From XS where professional name = computer; 4 calculate column values In 4.8 cases Select student number, course number, grades *1.20 as, grades 120, from, xs_kc, where, student number =081101; 5. Eliminate repeated rows of results In 4.9 cases Select distinct professional name, total credits from, xs; 6, aggregation function (statistical summary function) Example 4.10 Select count (*) as student total from xs; Example 4.11 Se