MySQL statement test--data query

MySQL statement test--data query

3.4 Data Query One, single table query/*1. Select several columns in the table, the order of the columns, and the column names in the statement from left to right in the order of the Select Target expression from table name; *//* (1) Select Column name from schema name. Table name; */select Sno,sname From zyl.student;/* (2) to query all columns available * Instead of */select * from student;/* (3) query computed values (target expression: Column name/expression/string constant/function, etc.) */select sname,2014- Sage from Student;select Sname, ' Years of Birth: ', 2014-sage,lower (sdept) from student;/* (4) specifying aliases to change the title of the query result select column name/Expression/Word The character string constant/function alias from the schema name. Table name; */select Sname, ' Year of Birth: ' Birth,2014-sage birthday,lower (sdept) department from Student;/*2.          Select a number of tuples in a table select ALL/DISTINCT (default to all) column name/expression/string constant/function from schema name. Table name; *//* (1) Eliminate duplicate rows */select sdept from student; /* There are many duplicates at this time */select distinct sdept from student; /*distinct removed duplicates *//* (2) query the tuple that satisfies the condition, which can be achieved by the WHERE statement *//*① comparison size =,,, <, >=, <=,! =, <&gt,!>,!<*/select Sname,sage from student where Sage=20;/*② determines the range, especially the data is numeric between ... and ... , not between ... and ... */select sname,sdept,sage from student where sage between and 20;/*③ determine collection in, not In*/select sname,sdept,sage from Studen t where sdept In (' CS ');/*④ character matches like, not like% (Percent semicolon) represents any single character */select sname,sdept,sage from student where sname like ' sheet% ';/* The string you want to query has a% or _ in itself and does not need to be escaped */insert into student (sno,sname,ssex,sage,sdept) VALUES (' 20162300 ', ' Lou _java ', ' male ', ' + ', ' is               ') Select Sno,sname from student where sname like ' Lou \_java ';    /* Run successfully */select Sno,sname from student where sname like ' Lou \_java ' escape ' \ '; /* error *//*⑤ involves control of the query is null, was not null*/insert to student (sno,sname,ssex,sdept,sage) VALUES (' 20160000 ', ' ', ' ', ' ', ' 0 ');           Select Sno,sname,sdept,sage from student where sname is null; /* You cannot find 20160000*/insert into student (' 20160001 ', ' ', ', ' 0 ') at this time, and select Sno,sname,sdept,           Sage from student where sname is null; /* At this time can find 20160001, show sname for Null*//*⑥ multiple conditions query and, or, Not*/select sno,sname,sdept,sage from student where sdept= ' CS ' and sage <20;select sname,sdept,sage from student where sdept in (' CS ', ' 43 '); with select Sname,sdept,sage from student where sdept= ' CS ' or sdept= ' 43 ';Price/*3.order BY clause, ASC ascending, desc descending *//*4. Aggregate function Count: Count distinct/all column Name: Counts the number of values in a column sum distinct/all column name: Calculates the sum of the values of a column avg Distinct/all Column Name: Calculates the average of a column of values max distinct/all column name: The maximum value of a column min distinct/all column name: The minimum value of a column value *//*5.group by clause groups The results of a query by one or more columns of values. The values are equal to a set of select column names/expressions/String constants/functions from the table name group By column name having conditional conditional expressions; Where statements cannot be used with group BY, the WHERE clause cannot use aggregate functions as conditional expressions, with having instead of * /Two, connection query three, nested query four, set query five, based on derived table query

MySQL statement test--data query