Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "introduction of MySQL-specific SQL sentences". Many people will encounter such a dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Create statement

If you want to copy the table structure information, the following statement is so concise and powerful that it copies the structure of the table test1.

Create table test like test1

Select into statement

The select into statement can quickly extract data from the table and quickly construct a table.

SELECT vale1, value2 into Table2 from Table1

It is required that the target table Table2 does not exist because the table Table2 is automatically created at insert time

Expressions in insert statements

There are plenty of insert statements in MySQL. For example, the following dynamic values, the way you set the expression, MySQL has its own taste.

INSERT INTO tbl_name (col1,col2) VALUES (15Cool 1mm 2)

-- ok

INSERT INTO tbl_name (col1,col2) VALUES (col2*2,15)

-- wrong

Insert statement

Among the unique insert statements supported by MySQL, the first one below is the statement format exported by mysqldump, which is relatively clear and much better than multiple SQL statements.

INSERT INTO table (a, b, c) VALUES (1min2, 3), (2, 3, 4)

The following statement looks special, and when you parse MySQL binlog, you will find that the insert statement looks like this.

INSERT INTO table SET axi1, baux2, cymbals 3

Delete

Drop statement

MySQL inside the drop statement is still quite characteristic, it will not be like Oracle can delete the index separately, delete is always associated with the table, so it is quite right with the characteristics of IOT.

If you delete an index directly, the following error will be thrown.

? Drop index ind_account_id2

? ERROR 1064 (42000):

You can use the following ways:

Drop index ind_account_id2 on t_user_login_record

Query OK, 0 rows affected (0.01 sec)

Records: 0 Duplicates: 0 Warnings: 0

Or write it in a more classic way:

Alter table t_user_login_record drop index account

Cascade deletion

Cascading deletion is supported in MySQL, and the following statement will cascade delete data, which is currently not supported by Oracle.

Delete A, B from A, B where A.id = B.aid

Change

Cascade update

Update A, B set A.a = A1, B.b = b1 where A.id = B.aid

Change modify

The usage of change,modify is similar, but it still has its own suitable usage scenarios. For example, with modifying the type or attribute of field b, change is also more suitable for modifying field names, and changing the data type modify is more direct.

ALTER TABLE t1 CHANGE b b BIGINT NOT NULL

ALTER TABLE t1 MODIFY b BIGINT NOT NULL

Replace statement

The replace statement in MySQL is very characteristic, which is comparable to the merge in Oracle. It supports two modes, data value and subquery.

Replace into x values (...)

Or

Replace into x select * from y

Renmae statement

This feature is likely to be ignored, but it is actually very useful, such as cleaning up a table, which is quick if you archive it to a historical database without cleaning up the data for the time being.

Rename table testsync.t_fund_info to test.t_user_login_record

Query OK, 0 rows affected (0.05 sec)

Check

Limit syntax

The use of limit is a classic use of MySQL, and it also supports a variety of modes.

For example, only the second result is returned

Select * from x

Limit 2

Return the results of articles 2 to 12

Or limit 2, 10

The wonderful use of order by

If the following data needs sorting

Select * from test order by name

+-+ +

| | id | name |

+-+ +

| | 1 | aa1 |

| | 3 | aa10 |

| | 4 | aa11 |

| | 2 | aa2 |

+-+ +

We want to display it in the order of aa1,aa2,aa10,aa11, and just write it this way.

Select * from test order by name+0

+-+ +

| | id | name |

+-+ +

| | 1 | aa1 |

| | 2 | aa2 |

| | 3 | aa10 |

| | 4 | aa11 |

+-+ +

Unique function

If you need to intercept a string, such as a number, there are many ways to do it.

For example, the following way is feasible.

Mysql > select replace ('123456 Globe, right (' 123456Grain journal 1),')

+-- +

| | replace ('123456 Globe, right (' 123456Grain Power1),'') |

| | +-+

| 123456

| | +-+

Or use cast to filter

> select cast ('123124aabc' as unsigned)

+-+

| | cast ('123124aabc' as unsigned) | |

+-+

| | 123124 |

+-+

1 row in set, 1 warning (0.01sec)

This is the end of the introduction of MySQL-specific SQL sentences. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.