Introduce how to use the SELECT statement to retrieve one or more data columns from a table
Chapter Two : SELECT statement
SQL statements can be given on one line or divided into many It is easier to debug by dividing it into multiple lines.
Multiple SQL statements must be separated by semicolons. Most DBMSs do not need to add a semicolon after a single SQL statement. However, a specific DBMS may have to add a semicolon after a single SQL statement.
SQL statements are not case sensitive
Example: kedou_jifen.jf_adjust
Retrieve a single column:
SELECT member_id FROM jf_adjust; —“Retrieve the name column from the city table
Search multiple columns:
To retrieve multiple columns from a table, use the same SELECT statement. The only difference is that multiple column names must be given after the SELECT keyword, and the column names must be separated by commas.
When selecting multiple columns, be sure to add a comma between the column names, and after the last column name No, if a comma is added after the last column name, an error will occur.
SELECT member_id,member_name,type
FROM jf_adjust;
SQL statements generally return raw, unformatted data. Data formatting is a presentation problem, not a retrieval problem.
So “representation” is generally specified in the application that displays the data.
In general, the actual retrieved data is rarely used (there is no format provided by the application)
Retrieve all columns
In addition to specifying the required columns (as mentioned above, one or more columns ), the SELECT statement can also retrieve all the columns without having to list them one by one.
This can be achieved by using the asterisk (*) wildcard in the actual column name.
SELECT * FROM jf_adjust;
Given a wildcard, all columns in the table are returned. The order of the columns is generally the physical order in which the columns appear in the table definition.
But SQL data rarely does this (usually, the data is returned to an application that is formatted or represented as needed )
Chapter 3: Sorting and Retrieving Data
Introduce how to use the ORDER BY clause of the SELECT statement to sort the retrieved data as needed.
3.1 Sorting data
SELECT member_id
FROM jf_adjust;
This SQL statement search returns a single column of a database table, and the output is in no particular order .
The retrieved data is not displayed in a purely random manner. If the data is not sorted, it will generally be displayed in the order in which it appears in the underlying table.
This can be the order in which the data was initially added to the table. However, if the data is updated or deleted later, this sequence will be affected by the DBMS reuse and reclaim storage space.
Therefore, you cannot (and should not) rely on this sort order if you don’t explicitly control it.
Relational database design theory believes that if the sort order is not clearly specified, it should not be assumed that the order of the retrieved data is significance.
Clause: SQL statement consists of clauses, some clauses are required, and some are optional. A clause usually consists of a keyword plus the provided data.
For example: FROM clause of SELECT statement
In order to clearly sort the data retrieved by the SELECT statement, you can use the ORDER BY clause
ORDER BY clause takes one or more column names, and sorts the output accordingly.
SELECT member_name FROM jf_adjust ORDER BY member_name;
This statement instructs the DBMS software to respond to member_name column sorts data in alphabetical order, except for the ORDER BY clause, which is the same as the previous statement.
The position of the ORDER BY clause: When specifying an ORDER BY clause, you should ensure that it is the last clause in the SELECT statement. If the order of the clauses is incorrect, an error message will appear.
Sort by non-selected columns: usually the columns used in the ORDER BY clause will be selected for display Columns, but in fact, this is not necessarily the case. It is completely legal to sort data with non-retrieved columns.e
3.2 Sort by multiple columns
It is often necessary to sort data by more than one column.
For example, if you want to display a list of employees, you may want to sort by first and last name ( Sort by last name first, and then sort by first name in each last name).
This is useful if multiple employees have the same last name.
In order to sort by multiple columns, simply specify the column name, between the column names Separate by commas (just like you do when selecting multiple columns)
SELECT order_no,member_name,expend_date FROM expend_goods ORDER BY member_name, expend_date;
< /div>
This line of code retrieves three columns and sorts the results by two of them —— first by member name , And then sort by date of consumption.
It is important to understand that when sorting by multiple columns, the sorting order is exactly as specified.
For the output in the above example, expend_date is only pressed on the order when multiple lines contain the same member name The consumption date is sorted.
If all values in the member_name column If they are unique, they will not be sorted by expend_date.
3.3 Sort by column position
In addition to specifying the sort order by column name, ORDER BY also supports sorting by relative column position.
SELECT order_no,member_name,expend_date FROM expend_goods ORDER BY 2, 3;
The output of this line of code is the same as the query above. The difference lies in the ORDER BY clause. The SELECT list specifies the relative position of the selected column instead of the column name.
ORDER BY 2 means to sort by the second column member_name of SELECT order_no, member_name, and expend_date in the SELECT list.
ORDER BY 2, 3 means first Sort by member_name, then press expend_date.
The main advantage of this technique is that there is no need to re-enter column names. The disadvantages are:
Firstly, not clearly giving the column name increases the possibility of incorrectly sorting by column name.
Secondly, it is easy to sort the data incorrectly when making changes to the SELECT list
Finally, if the column to be sorted is not in the SELECT list, obviously this technique cannot be used
< span style="font-family:'Microsoft YaHei';">
3.4 Specify sort Direction
Data sorting is not limited to ascending sorting (from A to Z). This is just the default sort order. You can also use the ORDER BY clause to sort in descending order (from Z to A).
In order to sort in descending order, the DESC keyword must be specified.
SELECT order_no,member_name,expend_date FROM expend_goods ORDER BY expend_date DESC;< /span>
This SQL statement is sorted in descending order of consumption date
< div>
Multi-column sorting in descending order:
SELECT order_no,member_name,expend_date FROM expend_goods ORDER BY expend_date DESC,member_name;
The DESC keyword is only applied to the column name directly in front of it. Only the expend_date column is sorted in descending order, while the member_name column (within the same date) is still sorted in the standard ascending order
Sort in descending order on multiple columns: If you want to sort in descending order on multiple columns, you must specify the DESC keyword for each column.
DESC is the abbreviation of DESCENDING, and both keywords can be used.
The opposite of DESC is ASC (or ASCENDING), which can be specified when sorting in ascending order. But in fact, ASC is not very useful, because ascending order is the default
< div> Case-sensitive and sort order:
In the dictionary sort order, A is considered the same as a, which is the default behavior of most database management systems.
But many DBMSs allow database administrators to change this behavior when needed (if your database contains a large number of foreign language characters , You may have to do this)
If you really need to change this sort order urgently, use a simple ORDER BY clause Can’t do it. You must ask the database administrator for help.
Remember: the ORDER BY clause must be the last clause in the SELECT statement.
Chapter 4: Filtering data
Introduce how to use the WHERE clause in the SELECT statement to specify search conditions
< /span>
4.1 Use WHERE clause
To retrieve only the required data, you need to formulate search conditions, which are also called filter conditions.
In the SELECT statement, the data is filtered according to the search criteria specified in the WHERE clause. The WHERE clause is given after the table name (FROM clause).
SELECT order_no,member_name,expend_date FROM expend_goods WHERE member_name=’lvyuping’;
SQL filtering and application filtering: data can also be filtered at the application layer. The SQL SELECT statement retrieves more data than actually needed for the client application, and then the client code loops the returned data,
to extract the required rows. This realization allows the client application to process the database will greatly affect the performance of the application, and make the created application completely non-scalable.
In addition, if the data is filtered on the client, the server has to send excess data over the network, which will cause the network Waste of bandwidth.
When using ORDER BY and WHERE clauses at the same time, ORDER BY should be placed after WHERE, otherwise an error will occur.
4.2 WHERE clause operator
The WHERE child of SQL The sentence supports all the conditional operators listed below:
Check a single value:
