In this tutorial, you will learn how to query data from a single table using SQL SELECT statements.
1. Introduction to SQL SELECT statement
To query the data in a table, use the SQL SELECT statement. The select statement contains columns for selection, Select row，Grouping data，Connection table And the syntax for performing simple calculations.
The SELECT statement is one of the most complex commands in SQL, so in this tutorial, we will only cover the basics.
The following describes the basic syntax of a SELECT statement that retrieves data from a single table.
SELECT
column1, column2, column3, ...
FROM
table_name;
In this syntax, specify that the data in the query SELECT clause is a comma separated list of columns, and specify the table name in the FROM clause. When evaluating a SELECT statement, the database system first evaluates the FROM clause and then the SELECT clause.
Semicolon (;) Is not part of the query. Typically, database systems use semicolons to separate two SQL queries. For more information, see SQL syntax. For more tutorials, visit http://www.manongzj.com
If you want to query the data in all columns of the table, you can use the asterisk (*) operator instead of the column list, as shown below.
SELECT
*
FROM
table_name;
Note that SQL is not case sensitive. Therefore, the database system handles both select and select in the same way. However, in order to make the SQL statement more readable, we will use identifiers such as uppercase letters (such as select and FROM) and lowercase letters (such as table name and column name) of SQL keywords.
In addition to the SELECT and FROM clauses, a SELECT statement can contain many other clauses, such as-
- WHERE - used to filter data according to specified criteria
- ORDER BY - used to sort the result set
- LIMIT - used to LIMIT the rows returned
- JOIN - used to query data from multiple related tables
- GROUP BY - used to group data according to one or more columns
- HAVING - used to filter groups
You will learn about the use of these clauses in subsequent tutorials.
2. SQL SELECT statement example
We will use Sample database The employees table in.
3. SQL SELECT - query all columns example
To query all columns in a table, use an asterisk (*) instead of specifying each column. For example, the following statement retrieves all data from the employees table:
SELECT
*
FROM
employees;
Execute the above example code and get the following results-
+-------------+------------+-----------+---------------------------+----------------+------------+--------+--------+------------+---------------+ | employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | manager_id | department_id | +-------------+------------+-----------+---------------------------+----------------+------------+--------+--------+------------+---------------+ | 100 | Steven | Lee | steven.lee@yiibai.com | 0532-86011111 | 1987-06-17 | 4 | 24000 | NULL | 9 | | 101 | Neena | Wong | neena.wong@kaops.com | 0551-4243311 | 1989-09-21 | 5 | 17000 | 100 | 9 | | 102 | Lex | Liang | lex.liang@kaops.com | 0571-87622362 | 1993-01-13 | 5 | 17000 | 100 | 9 | | 103 | Alexander | Lee | alexander.lee@kaops.com | 020-95105105 | 1990-01-03 | 9 | 9000 | 102 | 6 | ... ... | 201 | Michael | Zhou | michael.zhou@yiibai.com | 010-67237328 | 1996-02-17 | 10 | 13000 | 100 | 2 | | 202 | Pat | Zhou | pat.zhou@yiibai.com | 0755-28114518 | 1997-08-17 | 11 | 6000 | 201 | 2 | | 203 | Susan | Zhou | susan.zhou@yiibai.com | 0755-83587526 | 1994-06-07 | 8 | 6500 | 101 | 4 | | 204 | Hermann | Wu | hermann.wu@yiibai.com | 0513-83512816 | 1994-06-07 | 12 | 10000 | 101 | 7 | | 205 | Shelley | Wu | shelley.wu@yiibai.com | 0898-31686222 | 1994-06-07 | 2 | 12000 | 101 | 11 | | 206 | William | Wu | william.wu@yiibai.com | 022-26144822 | 1994-06-07 | 1 | 8300 | 205 | 11 | +-------------+------------+-----------+---------------------------+----------------+------------+--------+--------+------------+---------------+ 40 rows in set
Using the asterisk (*) operator can only easily query data interactively through SQL client applications. However, if you use the asterisk (*) operator in embedded statements in your application, there may be some potential problems.
First, because the structure of the table will evolve to meet new business needs, for example, you can add new columns and delete existing columns. If the asterisk (*) operator is used and the application code is not changed to use with the new table structure, the application may not function properly.
Another problem with the asterisk (*) operator is that it may increase unnecessary data transferred between the database server and the application, because the application may only need part of the data of the table.
4. SQL SELECT - query specific columns
The SELECT statement is used to accurately specify the columns to retrieve data in any order. It does not have to be arranged in the order defined in the table.
For example, if you want to view the employee ID, first name, last name and employment date of all employees, use the following query:
SELECT
employee_id,
first_name,
last_name,
hire_date
FROM
employees;
Note that the result set contains only the four columns specified in the SELECT clause. As shown below-
+-------------+------------+-----------+------------+ | employee_id | first_name | last_name | hire_date | +-------------+------------+-----------+------------+ | 100 | Steven | Lee | 1987-06-17 | | 101 | Neena | Wong | 1989-09-21 | | 102 | Lex | Liang | 1993-01-13 | | 103 | Alexander | Lee | 1990-01-03 | ... ... | 202 | Pat | Zhou | 1997-08-17 | | 203 | Susan | Zhou | 1994-06-07 | | 204 | Hermann | Wu | 1994-06-07 | | 205 | Shelley | Wu | 1994-06-07 | | 206 | William | Wu | 1994-06-07 | +-------------+------------+-----------+------------+ 40 rows in set
5. SQL SELECT - perform simple calculations
As mentioned earlier, the SELECT statement is used to perform simple calculations. For example, the following query uses FLOOR(), DATEDIFF(), and current_ The date function calculates the employee's year of service. To calculate the service year, divide the result of the DATEDIFF() function by 365. The FLOOR() function returns the largest integer less than or equal to the result of a numeric expression. YoS is the of the following expression Column alias , used to display user-friendly titles in the returned result set.
FLOOR(DATEDIFF(NOW(), hire_date) / 365)
The complete wording is as follows-
SELECT
employee_id,
first_name,
last_name,
FLOOR(DATEDIFF(NOW(), hire_date) / 365) AS YoS
FROM
employees;
Execute the above query statement and get the following results-
+-------------+------------+-----------+-----+ | employee_id | first_name | last_name | YoS | +-------------+------------+-----------+-----+ | 100 | Steven | Lee | 31 | | 101 | Neena | Wong | 29 | | 102 | Lex | Liang | 26 | | 103 | Alexander | Lee | 29 | | 104 | Bruce | Wong | 27 | | 105 | David | Liang | 21 | | 106 | Valli | Chen | 20 | | 107 | Diana | Chen | 19 | | 108 | Nancy | Chen | 24 | ... ... | 203 | Susan | Zhou | 24 | | 204 | Hermann | Wu | 24 | | 205 | Shelley | Wu | 24 | | 206 | William | Wu | 24 | +-------------+------------+-----------+-----+ 40 rows in set
This query applies to MySQL. If you are using SQL Server, you can use the following queries:
SELECT
employee_id,
first_name,
last_name,
DATEDIFF(year, hire_date,CURRENT_TIMESTAMP)
FROM
employees;
Through the above examples and learning, you should know how to query data from a single table using SQL SELECT statement.