SQL基本クエリステートメント

SQLは、テーブルのデータを問い合わせるだけでなく、算術演算や式の結果なども返します。基本的なクエリ文について学んでいきましょう。

I. 基本的なSELECTステートメント

1、指定されたフィールドに問い合わせる

構文形式です。

SELECT ,... FROM ;
Multiple fields can be specified in the statement, and the results will be displayed according to the specified fields.
For example.
To look up user id, username, 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 the fields in a table, you can use an asterisk "*" to indicate, for example, the following query
users
All data in the users table.
SELECT * FROM users;



"*" stands for all fields, the database will use the field names in the table for expansion when parsing the statement, replace the "*" with
user_id
The
user_name
, nick_name
, sex
, mobile
mobile
email
etc. fields.
3. Set aliases
Use the AS keyword to set aliases for columns.
SELECT user_id AS id
,
user_name AS 
User name,
nick_name AS 
Nickname,
sex AS 
sex 
FROM users
;
4. Query for constants
SELECT
statement can be written not only with column names but also with constants, the
as follows.
SELECT 100;
SELECT 'user';



5. Query of expressions
SELECT 98%100;



6. de-duplicate
SELECT
statement can use the
DISTINCT
keyword to remove duplicate rows from the query result, for example, to remove the
user_name
Duplicate data.
SELECT DISTINCT user_name FROM users;



Attention.
 DISTINCT is not filtered for NULL, i.e. the returned result contains NULL values.
When
DISTINCT
is applied to multiple columns, it is applied to all the fields that follow it, and
DISTINCT
can only be placed before all fields, i.e. before the first column name.
SELECT DISTINCT user_name,nick_name FROM users;



7. Conditional queries
SELECT
statement is passed through the
WHERE
clause to query for rows that match the specified criteria, the
WHERE
clause should be immediately followed by the
FROM
clause.
SELECT <field name>,... FROM <table name> WHERE <conditional expression>;



7.1 Single-Condition Queries
Query for users whose gender is male.


SELECT * FROM users WHERE sex='male';



Query for users whose age is less than or equal to 24.


SELECT * FROM users WHERE age<=24;



Query for users whose user id is not 3.


SELECT * FROM users WHERE NOT user_id=3;



In the third example, the
NOT
operator, prefixing the condition with the
NOT
to negate the condition and find records other than this condition.
7.2 Multi-criteria queries
Query for users whose age is less than or equal to 24 or whose gender is male.


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



Search for users whose age is less than or equal to 24 and whose gender is male.


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



The above uses a multi-conditional query, where the conditions can hold simultaneously
AND
operator, only one condition can be established with the
OR
operator.
7.3 Specifying a range query
Query for users whose user ids are in the range (2,3,7,8).


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



IN in
WHERE
Clause specifies multiple values, IN followed by parentheses, one or more values inside the parentheses, separated by commas, and the values can be numbers or characters.
Query for users with user ids 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 numeric, text or date.
7.4 Fuzzy queries
LIKE
keyword is used in
SQL
for pattern matching of search strings.
Syntax format.
Field name
 LIKE pattern
Matching pattern.
%
: percent sign matches zero, one or more characters
: underscore symbol to match a single character

Example.
Find user nicknames containing
tigeriaf
of the data:
SELECT * FROM users WHERE nick_name LIKE '%tigeriaf%';


This article about SQL basic query statement is introduced to this, more related SQL basic query statement content please search the previous articles of the Codedevlib or continue to browse the following related articles hope you will support the Codedevlib more!

tigeriaf

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

SELECT
statement can use the
DISTINCT
keyword to remove duplicate rows from the query result, for example, to remove the
user_name
Duplicate data.

DISTINCT

WHERE

Query for users whose age is less than or equal to 24.

SELECT * FROM users WHERE NOT user_id=3;

7.2 Multi-criteria queries

Search for users whose age is less than or equal to 24 and whose gender is male.

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

Query for users with user ids between 10 and 15.

SQL

%

Example.


Find user nicknames containing
tigeriaf
of the data:

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



This article about SQL basic query statement is introduced to this, more related SQL basic query statement content please search the previous articles of the BinaryDevelop or continue to browse the following related articles hope you will support the BinaryDevelop more!