Mysql must know will read the notes in chapters tenth and 11 using functions to process data _mysql

Introduction to MySQL

MySQL is an open source relational database management system (RDBMS), the MySQL database system uses the most commonly used database management language-Structured Query Language (SQL) for database management.

Stitching Fields

Data stored in database tables is generally not the format required by the application. Instead of retrieving the data and reformatting it in the client application or in the reporting program, we need to retrieve the transformed, calculated, or formatted data directly from the database.

Calculated fields (Fields = columns, but database columns are generally called columns, and fields are typically used in calculated fields) do not actually exist in a database table, which is created within a SELECT statement by the runtime.

Stitching concatenate to join values together to form a single value

In the MySQL SELECT statement, you can use the concat () function to stitch two columns.

If you create a two-column title: Generate a vendor report, you need to list the vendor's location in the vendor's name in a format such as name (location). This report requires a single value, while the data stored in the table contains two columns Vend_name and Vend_country. You also need to enclose the vend_country in parentheses.

The newly created column uses as to assign an alias

Remove whitespace

Ltrim () RTrim () Trim ()

Performing arithmetic calculations

For example, the list of items to store the price and quantity of items, but do not need to store the total price of each item (with the price multiplied by the number). For printing invoices, the total price of the item is required. That is, you need to add a column that is calculated from an existing column.

Text function

Left () string left-hand character
Length () string lengths
Locate () find a substring of a string
Lower () to lowercase
LTrim () Remove the left space
Right () returns the string's right-hand character
RTrim () Remove string right space
SOUNDEX () returns the string Soundex value
Upper () uppercase

Converts the selected text to uppercase

Select Upper (vend_name) from vendors;

Soundex () function: Converts any text to an algorithm that describes the alphanumeric pattern of its speech representation. (Speech match?) Compare pronunciation to comparison to subtitles

Date function

Date and Time functions

Adddate () Add a date-day or week

Addtime () Add a time

Curdate () returns the current date

Curtime () returns the current time

Date () Returns the day part of the date time

DateDiff () calculates two date difference

Date_add () highly flexible date operation function

Date_format () returns a formatted date or time series

Day () returns the number of days part of a date

DayOfWeek () for a date, returns the corresponding day of the week

Hour ()

Minute ()

Month ()

Now () Current date and time

Second ()

Time () part of the current date time

Year ()

Generally, applications do not use the format used to store dates and times, so date and time functions are always used to read, count, and process these values.

Date format for MySQL: Yyyy-mm-dd. Like 2005-09-01.

But such a where order_date = ' 2005-09-01 ' is unreliable. Because the data type of the Order_date store is datatime. This type stores date and time values. For example, the stored order_date value is 2005-09-01 11:30:05, then the WHERE order_date = ' 2005-09-01 ' will match the failure.

So the safest approach is the date () function, date (order_date) instructs MySQL to fetch the date portion of the column.

Select cust_id, order_num from
orders
where Date (order_date) = ' 2005-09-01 ';

Another example is to retrieve all the orders from September 2005.

Select cust_id, order_num from to
orders
where year (order_date) = Both the and Month (order_date) = 9;

Aggregate functions

We often need to summarize functions rather than actually retrieving them.

Examples of this type of retrieval are:

1. Determine the number of rows in the table

2. Get the group of rows in the table

3. Find the maximum, minimum, and average value of a table column (or some specific row of all rows)

aggregate function (aggregate function) functions that run on a row group to compute and return a single value.

AVG () returns the average of a column

COUNT () returns the number of rows in a column

Max () returns the maximum value of a column

MIN () returns the minimum value of a column

SUM () returns the value of a column

To ask for an average of a column

Copy Code code as follows:

Select AVG (prod_price) as Avg_pricefrom products;

-Count

Count the number of rows in a table using count (*) (whether null or not)

Use Count (column) to count rows with values for a particular column, ignoring null

Sum

Use SUM () to return the value of the specified column

The above is a small set to introduce the MySQL must know will read the tenth and 11 chapters of the use of functions to deal with data related knowledge, I hope to help you!