DQL query statement in Mysql

---------------- 1. query all columns -- Query all records (rows) in the student table select * from student

-- Conditional query select * from student where age> 19

------------------- 2. query the specified column -- query the name and gender of all select names, gender from student

-- Query the select name of the student of all ages> 19. The address is from student where age> 19.

/* Comparison operator ><>=<=! = <> Not equal to!> Not greater! <Not less than */--------------------- 3. Alias for the column -- Method 1 select name, address = hometown from student

-- Method 2 select name, address as hometown from student

----------------------- 4. Eliminate duplicates -- query the home select distinct addresses of the table from students

---------------------- 5. top n (First N queries) select top 3 * from student -- query the first 3 Records

----------------------- 6. Sort select * from student order by age asc -- sort by age in ascending order -- desc in descending order -- asc in ascending order

Select * from student order by age desc, number asc --- sort by age -- first sort by age, when the same age appears, sort the same students in ascending order

-- For example, in the student table, the age, name, select top 3 age, name, number from student order by age desc

----------------------------- 7, and (and), or (or) select * from student where age = 20 and name = 'zhang san'

-- For example, query if the gender is male or the specialized address is Wuhan select * from student where gender = 'male' or address = 'wuhan'

---------------------- 8, between... and (between...) -- for example: Query all persons aged between 20-30 select * from student where age between 20 and 30

----------------------- 9. usage of in: select * from student where age in (20, 19, 18)

--------------------------- 10. Use both top N and order

-- Example: select top 1 with ties * from students for the person with the largest age. -- if the with ties clause is added, all the students with the same age with the order by clause are displayed.

------------------------------- 11. Replace case with query results. query all person information. If the age is> = 40, the query result is displayed as "middle-aged person". If the age is between 30 and 39, show "Youth"-if the age is between 20 and 29, show "Teenagers"-if the age is less than 20, show "Teenagers"

Select student ID, name, gender, age = case when age> = 40 then 'middle-aged people' when age between 30 and 39 then' 'when age between 20 and 29 then' teenagers 'else' teenagers -- else indicates no when the preceding conditions are met, end All, address from student

--------------------------------- 12. Use the like clause to perform fuzzy search. Use the like clause in combination with the wildcard character. SQL server provides four wildcards. %: any character 2. _: represents a single arbitrary character 3. []: any character listed in square brackets. 4. [^]: represents any character not listed in square brackets.

-- Example: select * from student where name like 'Week % '-- % can replace any character

Select * from student where name like 'Week _ '-- _ indicates that one character can be replaced

-- For example, if the second word of the name contains 'heart' or '3', select * from student where name like '_ [Star, 3] _'

 

-- Nested query (generally, do not have more than three layers of nesting, that is, do not have more than three select statements) select * from student where age <(select age from student where name = 'zhang san ')

-- For example, query the select * from xs where age> (select top 1 age from xs where is located = 'Chinese' order by age desc) of all students older than all Chinese students)

/* Operator all some any */

/* All: specifies that the expression must be compared with each value in the subquery result set. true is returned only when the expression and each value meet the comparison relationship. Otherwise, false is returned;

Some and any: indicates that if the expression matches a value in the subquery result set, true is returned. Otherwise, false is returned.

*/

Select * from xs where age> all (select age from xs where region = 'Chinese ')

-------------------------------- Copy a table/* Pull all students from the computer system to create a separate table */

Create table xs_jisuanji -- create a new table (student ID int, name varchar (50), Gender char (10), age int)

Insert into xs_jisuanji -- query the content and copy the content to the select student ID, name, gender, age from xs where in the new table = 'computer 'H

 

/* COPY method 2 */-- create the select student ID, name, gender, age into xs_zhongwen from xs where = 'chine'

--- Cross-database table replication (the database name must be added before the table name) select * into test. dbo. xs from n2d09003

 

 

------------------------------------------ -- Aggregate Function

-- Calculate the total score of students in the select sum (score) as total score from xs

-- Calculate the highest score select max (score) as the highest score from xs

-- Calculate the percentile score select min (score) as the percentile score from xs

-- Average score select avg (score) as average score from xs

-- Count the number of select count (score) as students from xs

--------------------------------------- Classified summary group by -- Example 1

-- Query the majors in the student table in select distinct from xs

-- Group by: select hometown from N2D09003 group by hometown

-- Example 2: calculate the number of students in each region select hometown, count (*) as number of students from N2D09003 group by hometown -- sort by hometown for summary

-- [Example 3] calculate the number of boys and girls in each region select hometown, gender, count (*) as number from N2D09003 group by hometown, gender -- classify by hometown and gender

/* '[Note: 1: The column name after select must appear after group by] 2: group by and order by. The order by clause can contain aggregate functions. 3. You can use multiple field names as grouping fields after the group by keyword. In this way, the system groups the result set in more detail based on the order of these fields.

-- [Example 4] calculate the total number of people in each place, and sort the select hometown by number, count (*) as count from N2D09003 group by hometown order by Count desc -- here order by can be followed by an aggregate function (if needed)

Select * from xs order by max (AGE) dese -- the second error does not meet the usage requirements

---------------------------------------------------------------- 09.12.04

------------------------------------- Group... having -- function: After classification and summary, filter/* query the total number of professionals, and display the number of professionals with more than three students */select in the system, count (*) as Number of students from xs group by department having count (*)> 3 -- select the number of students> Major of 3

-------------------- Group by... with rollup select is located, count (*) as number of people from xs group by is located, gender with rollup -- after Classification summary, summarize again

Select department, gender, count (*) as number of people from xs group by department, gender with rollup -- after Classification summary, summarize again

------------------- Group .... with cube select Department, gender, count (*) as number of people from xs group by department, gender with cube-more detailed than rollup (resummarize by column after group)

---------------------------------------------- Link query/* Find the names and scores of students whose course number is 2 or higher than 80 */select name, xx. score from xs, xx where xs. student ID = xx. student ID -- two table link conditions and course number = 2 and xx. score> 80

-- Adds the table name. column name (you do not need to add the table name prefix before the column name, and only add the prefix when the two tables have the same column name) select xs. name, xx. score from xs, xx where xs. student ID = xx. student ID -- two table link conditions and xx. course No. = 2 and xx. score> 80

----------------------- Query Andy Lau's score -- Method 1 omitting the prefix select xx. Result from xx, xs where xx. Student ID = xs. Student ID and name = 'andy Lau'

-- Method 2 nested query select result from xx where student ID = (select student ID from xs where name = 'andy Lau ')

-- Method 3: inline query of select xx. Result from xx join xs on xs. Student ID = xx. Student ID -- two-table join condition where name = 'andy Lau '-- Other Restriction Conditions

-- Query Lin xinru's select name, course name, xx. Score

From xs join xx on xs. Student ID = xx. Student ID join kc on kc. course No. = xx. course no. and name = 'lin xinru 'and Course name = 'Gu China'

Select name, course name, xx. score from xs, xx, kc where xs. student ID = xx. student ID and xx. course No. = kc. course No. and name = 'lin xinru 'and Course name = 'ancient Chinese'

Select score from xx where course No. = (select course No. from kc where Course name = 'ancient Chinese ') and student no. = (select student no. from xs where name = 'lin xinru ')