DQL query statement in Mysql

Welcome to the Linux community forum and interact with 2 million technical staff to access Mysql's DQL query statement 1. query all columns-query all records of the student table (ROW) select * from student -- Query with conditions select * from student where age 192, query specified column -- query the name and gender of all person select name, sex

Welcome to the Linux community forum and interact with 2 million technical staff> go to Mysql's DQL query statement 1. query all columns-query all records of the student table (rows) select * from student -- Query with conditions select * from student where age 19 2. query the specified column -- query the name and gender of the owner select name, sex

Welcome to the Linux community forum and interact with 2 million technicians>

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 the owner select name, 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! <不小于 *< p>

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 (query the first N records) select top 3 * from student -- query the first three 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 = 'James'

-- 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, 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 years old, "middle-aged" is displayed. -- 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. Fuzzy search uses the like clause for fuzzy search. 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 functions

-- 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

[1] [2]