SQL-based query statements

In SQL statements, query is the most commonly used operation. SQL can not only query data in tables, but also return the results of arithmetic operations and expressions. Next, let's learn about the basic query statements.

1. Basic SELECT statement

1. Query the specified field

Syntax format:

SELECT <字段名>,... FROM <表名>;

Multiple fields can be specified in the statement, and the results will be displayed based on the specified fields.

For example: query user ID, user name, nickname, and gender information in the users table:

SELECT user_id,user_name,nick_name,sex FROM users;

2. Query all fields

To view all fields in a table, you can use an asterisk "*". For example, the following statement queries all data in the users table:

SELECT * FROM users;

"*" represents all fields. When the database parses the statement, it will use the field names in the table for expansion. According to the actual situation, replace "*" with fields in user_id , user_name , nick_name , sex , mobile , email and other tables.

3. Set alias

Use the AS keyword to set an alias for a column.

SELECT user_id AS id , user_name AS user name, nick_name AS nickname, sex AS gender FROM users ;

4. Constant query

In the SELECT statement, you can write not only column names but also constants.

as follows:

SELECT 100;
SELECT 'user';

5. Expression query

SELECT 98%100;

6. Deduplication

You can use the DISTINCT keyword in the SELECT statement to remove duplicate records in the query results. For example, to remove duplicate data of user_name :

SELECT DISTINCT user_name FROM users;

Note: DISTINCT does not filter NULL values, that is, the returned results include NULL values;

When DISTINCT is applied to multiple columns, the application scope is all the fields that follow it, and DISTINCT can only be placed in front of all the fields, that is, before the first column name.

SELECT DISTINCT user_name,nick_name FROM users;

7. Conditional query

The SELECT statement uses the WHERE clause to query records that meet the specified conditions. The WHERE clause must follow the FROM clause.

SELECT <field name>,... FROM <table name> WHERE <conditional expression>;

7.1 Single condition query

Query users whose gender is male:

SELECT * FROM users WHERE sex='男';

Query users whose age is less than or equal to 24:

SELECT * FROM users WHERE age<=24;

Query users whose user id is not 3:

SELECT * FROM users WHERE NOT user_id=3;

The NOT operator is used in the third example. Adding NOT before a condition negates the condition and searches for records outside the condition.

7.2 Multiple Condition Query

Query users whose age is less than or equal to 24 or whose gender is male:

SELECT * FROM users WHERE age<=24 OR sex='男';

Query users whose age is less than or equal to 24 and whose gender is male:

SELECT * FROM users WHERE age<=24 AND sex='男';

The above query uses multiple conditions. If the conditions can be satisfied at the same time, use the AND operator. If only one condition can be satisfied, use OR operator.

7.3 Querying by specified range

Query users whose user ID is in the range (2,3,7,8):

SELECT * FROM users WHERE user_id IN (2,3,7,8);

IN specifies multiple values ​​in the WHERE clause. IN is followed by parentheses. There can be one or more values ​​in the parentheses. The values ​​are separated by commas and can be numbers or characters.

Query users whose user id is between 10 and 15:

SELECT * FROM users WHERE user_id BETWEEN 10 AND 15;

BETWEEN ... AND specifies a range of data between two values, which can be numbers, text, or dates.

7.4 Fuzzy Query

The LIKE keyword is used in SQL fuzzy query to perform pattern matching on the search string.

Syntax format:

Field name LIKE pattern

Matching mode:

• % : The percent sign matches zero, one, or more characters.
• - : The underscore symbol matches a single character

Find data where the user nickname contains tigeriaf :

SELECT * FROM users WHERE nick_name LIKE '%tigeriaf%';

This is the end of this article about SQL basic query statements. For more relevant SQL basic query statements, please search 123WORDPRESS.COM’s previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!