MySQL Self-study chapter (11)

Data query one, single-table query 1, inSelectstatement, use the*"indicates that all fields are queried

First of all:

Create a table because the test

CREATE TABLE student (ID int primary KEY not null,name varchar (), sex varchar (4), class varchar (10));

Secondly:

Insert data for the following query operation

INSERT into student (Id,name,sex,class) VALUES ("Zhang San" , ' male ', ' class 1 ') , ( "John Doe " , ' male ', ' class 2 '), ( ' Harry ', ' female ', ' class 3 ');

Again:

Querying all fields

SELECT * from student;

2, inSelectSpecify all field names in the same order as in the table

Select Name,id,class,sex from student;

The result of the query is that SQL is sorted by the field name specified in the SQL statement.

3, joinwherequery criteria for clauses

Select Id,name from student where class= ' class 1 ';

4, withinchQuery for Keywords

SELECT * from student where ID in (;

5, withbetween andthe scope of the query

SELECT * from student where ID between 1 and 3;

6, with likethe character matching query

(1) '%', matches any length of character, even including 0 characters

SELECT * from student where class like ' class % ';

(2) with ' _ ', can only match any one character at a time

SELECT * from student where class like ' _ class _ ';

7, query null value

Use the IS NULL clause to determine whether a field's contents are empty

Select name from student where ID is null;

The IS-not NULL clause acts in contrast to the is null , which determines that the contents of a field are not null values

Select name from student where ID is not null;

8, with andthe multi-criteria query

In the select query, you can increase the constraints of the query, which can make the results of the query more accurate. and you can add more restrictive conditions.

SELECT * FROM student where sex = ' male ' and class = ' class 1 ';

9, withORthe multi-criteria query

OR indicates that a record that satisfies one of these conditions can return

SELECT * FROM student where sex = ' female ' or id = 1;

10,DISTINCTkeyword makes the results of a query non-repeatable

Select distinct sex from student;

11, sorting the results of the query in a single column

Order by means that the default property is sorted in ascending order by a column, and can be sorted in reverse order using desc

SELECT * FROM student ORDER by id DESC;

12, multi-column sorting

SELECT * FROM student order by Id,class;

First , sort by the ascending ID , and then sort by the class value if you encounter a value with the same ID .

MySQL Self-study chapter (11)