MySQL addition, deletion, modification, and query statements

I. operation data (query)

1. Distinct

Syntax: select [distinct] column name [, column name]... from Table Name

Distinct is used to filter duplicate records. If distinct is followed by multiple columns, it filters out duplicates after multiple columns are merged.

2.

Syntax: Select column name as Alias from Table Name

Alias the database. The as can be omitted when the alias is initiated, without changing the value in the database.

3. Where

Syntax: Select column name from table name [where Condition Statement]

Conditional query of records in the database

4. Order

Syntax: Select column name from table name order by column name ASC | DESC;

The column name specified by order by can be the column name in the table or the alias after the SELECT statement. ASC is in ascending order, DESC is in descending order, and order by is at the end of the query statement.

5. Count, sum, AVG, max/min, group

Ii. Functions

1. Time Functions

Addtime (original time, added value) adds a period of time

Current_date () current date

Current_time () Current Time

Current_timestamp () The current timestamp 1970-2037 will change with your time

Date (time) returns the date part of the specified time.

Date_add (date, interval value-added type) adds a field on a specified date

Date_sub (date, interval Reduce Value Type) reduces a field on a specified date

Datediff (date 1, date 2) calculates the difference between two dates

Now () Current Time

Year | month | date | hour | minute | second (time) gets a field of the specified time

2. String Functions

Charset (string) returns the string Character Set

Concat (string 1 [, string 2]...) connection string

Instr (string, substring) to find the position where the substring appears. Note that the serial number starts from 1.

Ucase (string) converts string to uppercase

Lcase converts a string to lowercase.

Left (string, length) takes the specified length from the left side of the string

Length (string) calculates the string length

Replace (string, search string, replace string) Replace the specified string in the string with another string

Strcmp (string 1, string 2) compares two strings one by one. If the string is inclusive, the difference in length is returned.

Substring (string, start coordinate [, number]) captured from the string

Ltrim (string) removes the left blank

Rtrim (string) removes the right Blank

Trim (string) removes the left and right sides of the blank

3. mathematical functions

ABS (number) returns the absolute value

Bin (decimal number) converts decimal to binary

Hex (decimal number) converts decimal to hexadecimal

Conv (number, original hexadecimal, target hexadecimal) Conversion hexadecimal

Ceiling (decimal) rounded up

Floor (decimal) rounded down

Round (decimal)

Format (decimal, number of reserved digits) reserved decimal places

Least (value, value [, value]...) to obtain the minimum value

Greatest (value, value [, value]...) returns the maximum value.

MoD (number, number) Remainder

Rand () generates a random number, with 14 decimal places, 0 <=n <= 1

Iii. Table Constraints

1. unique constraint unique

Fields restricted by unique are unique in the whole table and cannot be repeated. Multiple null fields are not included.

2. Non-null constraint not null

The field specified by notnull cannot be blank.

3. primary key constraint primary key

Primarykey is used to identify a field. This field is not empty and unique.

4. Foreign key constraint foreign key

Foreign key restricts the value of a column by referring to another column

Iv. Table Design

Mainly include: single entity, one-to-many, multiple-to-one, one-to-one, multiple-to-many

5. Multi-Table query

1. Connection Query

In multi-table join queries, if there is no valid connection condition, all rows in the table are connected to each other to form a Cartesian set. To avoid Cartesian sets, you can add a valid connection condition after where.

2. Multi-table join

Join queries for multiple tables. One foreign key references another table, and the other references other tables.

3. Self-connection

Connect to the table by yourself. The foreign key of the current table references its primary key.