This is the 8th day of my participation in the November Gwen Challenge. Check out the event details: The last Gwen Challenge 2021.

SQL statement, query is the most used operation, SQL can not only query the table data, but also return the result of arithmetic operations, expressions, etc., next let’s look at the basic query statement.

Basic SELECT statement

Query a specified field

Syntax format:

SELECT <The field name>.FROM <The name of the table>;
Copy the code

Multiple fields can be specified in a statement, and the results are displayed according to the specified fields.

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

SELECT user_id,user_name,nick_name,sex FROM users;
Copy the code

Query all fields

To view all fields in a table, use an asterisk “*”. For example, query all data in the Users user table with the following statement:

SELECT * FROM users;
Copy the code

“*” indicates all fields. When parsing the statement, the database will use the field names in the table to expand the statement and change “*” to the fields in the user_id, user_name, Nick_name, sex, mobile, and email tables as required.

Set the alias

Columns can be aliased using the AS keyword.

SELECT user_id AS id,user_name ASUser name,nick_nameASNicknames, sexASgenderFROM users;
Copy the code

Constant query

The SELECT statement can write not only column names but also constants as follows:

SELECT 100;
SELECT 'users';
Copy the code

Query of expressions

SELECT 98%100;
Copy the code

duplicate removal

The DISTINCT keyword can be used in the SELECT statement to remove duplicate records from the query result. For example, to remove duplicate data from user_name:

SELECT DISTINCT user_name FROM users;
Copy the code

Note: NULL is not filtered by DISTINCT, that is, NULL values are returned.

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

SELECT DISTINCT user_name,nick_name FROM users;
Copy the code

Conditions of the query

The SELECT statement queries records that meet the specified criteria through the WHERE clause, which follows the FROM clause.

SELECT <The field name>.FROM <The name of the table> WHERE <Conditional expression>;
Copy the code

Single condition query

  • To query male users:
SELECT * FROM users WHERE sex='male';
Copy the code
  • Query the user whose age is 24 or younger:
SELECT * FROM users WHERE age< =24;
Copy the code
  • Query user id not 3;
SELECT * FROM users WHERE NOT user_id=3;
Copy the code

In the third example, we use the NOT operator, and prefacing the condition with NOT negates the condition and looks for records other than the condition.

Multi-condition query

  • Query the user whose age is 24 or younger or whose gender is male:
SELECT * FROM users WHERE age< =24 OR sex='male';
Copy the code
  • Query male users whose age is less than or equal to 24:
SELECT * FROM users WHERE age< =24 AND sex='male';
Copy the code

The above query uses multiple conditions, conditions can be both AND operator, condition can only be one OR operator.

Specified range query

  • Query users whose user ids are in range (2,3,7,8) :
SELECT * FROM users WHERE user_id IN (2.3.7.8);
Copy the code

IN specifies multiple values IN the WHERE clause. IN is followed by parentheses. The parentheses may contain one or more values separated by commas.

  • Query users whose ids are between 10 and 15:
SELECT * FROM users WHERE user_id BETWEEN 10 AND 15;
Copy the code

BETWEEN … AND specifies the range of data between two values, which can be numeric, text, or date.

Fuzzy query

The LIKE keyword is used in FUZZY SQL queries for pattern matching of search strings.

Syntax format:

The field nameLIKE pattern
Copy the code

Matching mode:

  • % : the percent sign matches zero, one, or more characters

  • – : Underscores match a single character

model meaning
LIKE ‘a%’ Match withAStart string, such as ABC and ab
LIKE ‘%y’ Match withyA string at the end, for example, aay, xy
LIKE ‘%mn% Matching containsmnFor example, amnb and LMN
LIKE ‘a_’ Match withaAt the beginning, only one character of data, such as ay, ab
LIKE ‘_y’ Match withyEnd, data preceded by only one character, such as ay, xy

Such as:

Find user nickname containing tigeriaf data:

SELECT * FROM users WHERE nick_name LIKE '%tigeriaf%';
Copy the code

Original is not easy, if small partners feel helpful, please click a “like” and then go ~

Finally, thank my girlfriend for her tolerance, understanding and support in work and life!