A2-02-01. DML-Using MySQL SELECT Statement to Query Data
转载自：http://www.mysqltutorial.org/mysql-select-statement-query-data.aspx
Using MySQL SELECT Statement to Query Data
Summary: in this tutorial, you will learn how to use MySQL SELECT statement to query data from tables or views.
Introduction to MySQL SELECT statement
The SELECT statement allows you to get the data from tables or views. A table consists of rows and columns like a spreadsheet. Often, you want to see a subset rows, a subset of columns, or a combination of two. The result of the SELECT statement is called a result set that is a list of rows, each consisting of the same number of columns.
See the following employees table in the sample database. It has eight columns: employee number, last name, first name, extension, email, office code, reports to, job title and many rows.
The SELECT statement controls which columns and rows that you want to see. For example, if you are only interested in the first name, last name, and job title of all employees or you just want to view the information of every employee whose job title is the sales rep, the SELECT statement helps you to do this.
Let’s take look into the syntax of the SELECT statement:
|
1
2
3
4
5
6
7
8
9
10
11
|
SELECT
column_1, column_2, ...
FROM
table_1
[INNER | LEFT |RIGHT] JOIN table_2 ON conditions
WHERE
conditions
GROUP BY column_1
HAVING group_conditions
ORDER BY column_1
LIMIT offset, length;
|
The SELECT statement consists of several clauses as explained in the following list:
- SELECT followed by a list of comma-separated columns or an asterisk (*) to indicate that you want to return all columns.
- FROM specifies the table or view where you want to query the data.
- JOIN gets related data from other tables based on specific join conditions.
- WHERE clause filters row in the result set.
- GROUP BY clause groups a set of rows into groups and applies aggregate functions on each group.
- HAVING clause filters group based on groups defined by GROUP BY clause.
- ORDER BY clause specifies a list of columns for sorting.
- LIMIT constrains the number of returned rows.
The SELECT and FROM clauses are required in the statement. Other parts are optional.
You will learn about each clause in more detail in the subsequent tutorials. In this tutorial, we are going to focus on the basic form of the SELECT statement.
MySQL SELECT statement examples
The SELECT statement allows you to query partial data of a table by specifying a list of comma-separated columns in the SELECT clause. For instance, if you want to view only first name, last name, and job title of the employees, you use the following query:
|
1
2
3
4
|
SELECT
lastname, firstname, jobtitle
FROM
employees;
|
Even though the employees table has many columns, the SELECT statement just returns data of three columns of all rows in the table as highlighted in the picture below:
If you want to get data for all columns in the employees table, you can list all column names in the SELECT clause. Or you just use the asterisk (*) to indicate that you want to get data from all columns of the table like the following query:
|
1
|
SELECT * FROM employees;
|
Try It Out
It returns all columns and rows in the employees table.
You should use the asterisk (*) for testing only. In practical, you should list the columns that you want to get data explicitly because of the following reasons:
- The asterisk (*) returns data from the columns that you may not use. It produces unnecessary I/O disk and network traffic between the MySQL database server and application.
- If you explicit specify the columns, the result set is more predictable and easier to manage. Imagine when you use the asterisk(*) and someone changes the table by adding more columns, you will end up with a result set that is different from what you expected.
- Using asterisk (*) may expose sensitive information to unauthorized users.
In this tutorial, you’ve learned about the basic MySQL SELECT statement to query data from a table in MySQL.
