MySQL Data processing functions and mysql Data Processing

MySQL Data processing functions and mysql Data Processing
Data processing functions sometimes process the data obtained from database tables. For example, replace lowercase letters with uppercase letters. This process can be performed on the client or the database. Database migration is more efficient. The database has corresponding data processing functions to process the data, but using special data processing functions in SQL will reduce the portability. Different DBMS systems have different data processing functions.
Most SQL statements support the following types of functions:

• Functions used to process text strings, such as deletion, filling, and case-sensitive conversion;
• A function used to perform arithmetic operations on numeric data;
• It is used to process Date and Time values and extract special components from these values, such as two functions with only difference in date;
• System functions that return the special information being used by the DBMS, such as user logon and version check;

Text processing functions

Example: convert a column of characters to uppercase characters

Common text processing functions

Function	Description
Left ()	Returns the character on the left of the string.
Length ()	Returns the length of a string.
Locate ()	Find a substring of a string
Lower ()	Convert string to lowercase
LTrim ()	Remove spaces on the left of the string
Right ()	Returns the character on the right of the string.
RTrim ()	Remove spaces on the right of the string
Soundex ()	Returns the SOUNDEX value of the string.
SubString ()	Returns the character of a substring.
Upper ()	Convert string to uppercase

The Soundex () function is used to find a string with similar pronunciation. For example, if a spelling error occurs, a name with similar pronunciation is recorded in the table. In this case, it is not efficient to search by using LIKE or other matches. It is much easier to use SoundEX.

In the following example, the name is Y. Lee, but the name stored in the table is Y. Lie. If you use the matching search, you can use the SoundEX () function:

Date and time processing functions common date and time processing functions

Function	Description
AddDate ()	Add a date (Days, weeks)
AddTime ()	Add a time (hour, minute)
CurDate ()	Returns the current date.
CurTime ()	Returns the current time.
Date ()	Returns the date part of the date and time.
DateDiff ()	Calculate the difference between two dates
Date_Add ()	Highly Flexible date computing functions
Date_Format	Returns a formatted date or string.
Day ()	Returns the number of days of a date.
DayOfWeek ()	Returns the day of the week for a date.
Hour ()	Returns the hour of a time.
Minute ()	Returns the minute of a time.
Month ()	Returns the month of a time.
Now ()	Returns the current date.
Second ()	Returns the second part of a time.
Time ()	Returns the time part of a date and time.
Year ()	Returns the year of a date.

Simple time comparison:

However, if there is time after order_date, the preceding SQL statement cannot match. Therefore, we can use the date function to compare only the date in the table, regardless of the time:

Search for orders for one month;

However, if you do not want to calculate the number of days in a month, you can do the following:

Numeric processing functions numeric processing functions only process numerical data. These functions are generally used for algebra, triangles, or ry operations. Common numeric processing functions are as follows:

Function	Description
Abs ()	Returns the absolute value of a number.
Cos ()	Returns the cosine of an angle.
Exp ()	Returns the exponential value of a number.
Mod ()	Returns the remainder of the division operation.
Pi ()	Returns the circumference rate.
Rand ()	Returns a random number.
Sin ()	Returns the sine of an angle.
Sqrt ()	Returns the square root of a number.
Tan ()	Returns the tangent of a number.

The following is an example of tangent:


What if mysql's string processing functions are richer than php's?

My opinion: because MYSQL does not support pattern matching and does not support regular expressions, the string processing function cannot be compared with PHP at all.

Generally, it is enough for a database to support REPLACE, SUBSTR (or excel lett, RIGHT, and MID, however, MYSQL's string functions are indeed very rich (in fact, MYSQL's date processing functions are also rich), including the following:

ASCII (str)
Returns the ASCII code value of the leftmost character of the str string. If str is a Null String, 0 is returned. If 'str' is NULL, return NULL.

ORD (str)
If the leftmost character of a string 'str' is a multi-byte character, use the format (first byte ASCII code) * 256 + (second byte ASCII code )) [* 256 + third byte ASCII code...] returns the ASCII code value of a character to return the multi-byte code. If the leftmost character is not a multi-byte character. Returns the same value as that returned by the ASCII () function.

CONV (N, from_base, to_base)
Convert numbers between different digit bases. Returns the string Number of number N, which is converted from from_base base to to_base base. If any parameter is NULL, NULL is returned. Parameter N is interpreted as an integer, but can be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is considered as a signed number. Otherwise, N is considered as an unsigned number. CONV works with 64-bit precision.

BIN (N)
Returns a string of the binary value N. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 2 ). If N is NULL, return NULL.

OCT (N)
Returns the representation of a string with an octal value N. Here N is a long integer, which is equivalent to CONV (N, 10, 8 ). If N is NULL, return NULL.

HEX (N)
Returns the representation of a hexadecimal value N string. Here N is a long integer (BIGINT) number, which is equivalent to CONV (N, 10, 16 ). If N is NULL, return NULL.

CHAR (N ,...)
CHAR () interprets the parameter as an integer and returns a string consisting of ASCII code characters of these integers. The NULL value is skipped.

CONCAT (str1, str2 ,...)
Returns a string from the parameter link. If any parameter is NULL, return NULL. There can be more than two parameters. A numeric parameter is converted to an equivalent string.

LENGTH (str)
OCTET_LENGTH (str)
CHAR_LENGTH (str)
CHARACTER_LENGTH (str)
Returns the length of the str string.

LOCATE (substr, str)
POSITION (substr IN str)
Returns the position of the substring substr In the first occurrence of the str. If the substring is not in the str, the return value is 0.

LOCATE (substr, str, pos)
Returns the position of the substring substr at the first occurrence of the substring, starting from the position pos. If substr is not in str, 0 is returned.

INSTR (str, substr)
Returns the position where the substring substr first appears in the str string. This is the same as LOCATE () in the form of two parameters, except that the parameters are reversed.

LPAD (str, len, padstr)
Returns the str string. The left side is filled with the string padstr until str is a string of len characters.

RPAD (str, len, padstr)
Returns the str string. Fill it with the string padstr on the right until it is a string of len characters.

LEFT (str, len)
Returns the leftmost len character of the str string.
... The remaining full text>

What should I do when the MYSQL database query results contain \ symbols?

The most effective means of management.

Database Design refers to constructing the optimal database mode for a given application environment, establishing a database and its application system, and effectively storing data to meet user information requirements and processing requirements.

Database Design phases:

A. Demand analysis stage: comprehensive application requirements of various users (real-world requirements ).

B. In the conceptual design stage: A Conceptual Model (information world model) independent of machines and various DBMS products is formed, which is described by a E-R diagram.

C. In the logic design stage: converts the E-R diagram to the data model supported by the specific database product, such as the relational model, to form the database logic model. Then, based on the user's processing requirements and security considerations, the necessary VIEW is created on the basis of the basic table to form an external data mode.

D. In the physical design stage: arranges physical storage and designs indexes based on the characteristics and processing needs of DBMS to form the internal database mode.

1. Demand analysis stage

Requirement collection and analysis: the data requirement described by the data dictionary is obtained (and the processing requirement described by the data flow diagram ).

Requirements Analysis focuses on investigating, collecting, and analyzing users' information requirements, handling requirements, and security and integrity requirements in data management.

Requirement Analysis Methods: Investigate the organization and organization, the business activities of each department, assist the user to clarify various requirements for the new system, and determine the boundaries of the new system.

Common survey methods include: follow-up operations, opening survey meetings, inviting special personnel to introduce, ask, and designing surveys. Users are requested to fill in and check records.

The methods for analyzing and expressing user requirements mainly include top-down and bottom-up methods. The top-down Structured Analysis method (SA) starts with the upper-level system organization and analyzes the system layer by layer, the data flow diagram and data dictionary are used to describe each layer.

The data flow chart shows the relationship between the data and the processing process. The Data in the system is described using the Data Dictionary (DD.

2. Conceptual Structure Design Stage

A conceptual model independent of a specific DBMS is formed through the synthesis, induction and abstraction of user requirements, which can be expressed by a E-R diagram.

The conceptual model is used for modeling the information world. The conceptual model does not depend on the data model supported by a DBMS. A conceptual model can be converted to a specific data model supported by a DBMS on a computer.

Concept model features:

(1) Strong semantic expression ability, which can easily and directly express various semantic knowledge in applications.

(2) It should be simple, clear, and easy to understand. It is a language for communication between users and database designers.

A common method for conceptual model design is the IDEF1X method, which applies the entity-contact method to a Semantic Modeling Technology in the semantic data model and is used to establish a system information model.

Author: Xiao Ling, source: Forum, responsible editor: Li shuqin

This article details the database design process, design skills, and summarizes the database naming rules ......

2.1 Step 1 -- initialize the project

In this phase, the task starts from the description of the purpose and scope, determines the modeling target, develops the modeling plan, organizes the modeling team, collects the source material, and develops constraints and specifications. Collecting source materials is the focus of this phase. Through investigation and observation results, business processes, input and output of the original system, various reports, and collection of raw data, a basic data table is formed.

2.2 Step 1 -- Define an object

Entity integrators have a common set of features and attributes that can identify most entities directly or indirectly from the collected source material-basic data tables. Mark the entities represented by the terms in the source material name table that indicate things and terms that end with "code", such as customer code, agent code, and product code, in this way, potential entities are preliminarily identified to form a preliminary entity table.

2.3 Step 2 -- Define contact

Only binary connections are allowed in the IDEF1X model. n-element connections must be defined as n binary connections. Based on actual business requirements and rules, the entity contact matrix is used to identify the binary relationship between entities. Then, the potential, Link name, and description of the connection relationship are determined based on the actual situation, and the link type is determined, is the identification link ...... remaining full text>