A single-table lookup based on the SQL statement

SqlSingle-table query of statementsA General Enquiry

1. View all records in the table and all fields (attributes)

Statement : SELECT * from student;

2. View only certain fields

Statement : Select Sname,sex from student;

3. Display the field name of the result of the query as a different name

Statement : Select Sname as ' name ', sage as ' age ' from student;

4. Add a column to the query result, and the value of this column is a constant

Statement:Select Sname,sex,' Guangzhou ' address from student;

5. Merge some fields and show them (I added a field to the table score, which has a value of +)

Statement : Select Sname, (sage+score) as ' age plus score ' from student;

Note that the data types of the merged fields must be the same.

6. Remove the duplicate records of the results of the query

Statement : SELECT distinct score from student;

Because the students ' grades are all thesame, after removing the duplicates, only one record is displayed.

Conditional query

In addition to querying by fields, we can also query by setting certain conditions to get the records we want.

Use the where keyword to set conditions;

1. Single condition Query

For information on students named Jax :

Statement:SELECT * from student where sname=' Jax ';

2. Multi-Criteria Query

For information on students whose name is JinXandage :

Statement: SELECT * from student where sname=' JinX ' and sage=18;

Check the age of the student named VNor EZ .

Statement:Select sage from student where sname=' VN ' or sname= ' EZ ';

3. Scope Query

Check the information of students aged between three and five years old.

Statement: SELECT * FROM student WHERE sage between and 20; --After the package before package

4. Search for empty sentences

There are two main empty sentences: one . determine if the NULL , two . determines whether an empty string

Determine if null: statement SELECT * from student where score is not null;

SELECT * FROM student where score null;

Determine if the string is empty : statement SELECT * from student where sex<>'; ------"<>" means "not equal to"

Select * from student where sex= ';

5. Fuzzy query

Using the like keyword,"%" stands for any number of characters, and "_" represents a placeholder.

Query for information about students whose names begin with J :

Statement:SELECT * from student where sname like ' j% ';

 

Find information about a student whose second letter is a in their name

Statement: SELECT * from student where sname like ' _a% ';

6. Paging Query

Paging queries are primarily used to view the information from article N to article M , which is typically used in conjunction with sort queries.

With the limit keyword, the first parameter represents the start of the entry, and the second parameter represents the number to display.

The default first record in the table has a parameter of 0.

Query the second to third section of the Student's Table for information:

Statement: SELECT * from student limit;

7. Post-Query ordering

After the query is sorted by a field.

Keyword:order BY, ASC: Ascending, desc: Descending

Sort in ascending order of age:

Statement:SELECT * FROM Student order by sage ASC;

8. Aggregate queries

If we need to sum up the results of the query, the average, the maximum minimum value, the number of statistics displayed, and so on, we need to use the aggregate query,

Keyword:sum,avg,Max,min,Count

Check all student ages and: Select sum (sage) from student;

Check the average age of all students:select AVG (SAGE) from student;

Check the age of the oldest student : select Max (sage) from student;

Check the age of the youngest student : select min (sage) from student;

Statistics of Secondary Students:select COUNT (sname) from student;

9. Group queries

You can display the results of a query in groups according to a certain condition.

Keyword:broup by

Group students according to gender :

Selete * FROM student group by sex;

--2.12 Group Filter Query

-- demand: To find out which areas are greater than or equal to 2 people

--1) query the situation of each area 2) Add condition

-- Expected Result: Guangzhou tianhe 2

-- Note: If groupby is used, filtering on a group basis uses the having keyword

SELECT Address,count (*) from student GROUP by address have COUNT (*) >=2;

A single-table lookup based on the SQL statement