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

This article mainly explains "what are the practical tips for SQL operation". Friends who are interested may wish to have a look. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what are the practical tips for SQL operation"!

1. Convert decimals to percentages

When writing SQL, we sometimes want to convert decimals to percentage display, which can be written like this:

SELECT CONVERT (VARCHAR (20), CONVERT (DECIMAL (18,2), ROUND (Aspir100.0) +'%'AS Rate

For example:

SELECT CONVERT (VARCHAR (20), CONVERT (DECIMAL (18,2), ROUND (2mm 100.0max 3,2)) +'%'AS Rate

Results:

Code interpretation:

ROUND (decimal to be rounded, rounded): it is rounded, but does not change the length of the number.

CONVERT (): the first CONVERT, which intercepts the number of decimal places after rounding, and controls the number of decimal places to 2 through DECIMAL (18Pol 2).

CONVERT (): the second convert, which converts the number rounded and truncated to a string type, followed by a percent sign to show the percentage complete.

Pay attention to two points:

The divisor is not 0

The divisor is converted to floating point first (here we use 100.0 to convert 2 to floating point).

2. Cannot be saved after modifying the table structure

We often encounter the problem that when we finish modifying the table structure, clicking Save will pop up a warning that changes are not allowed to be saved.

To solve this problem, we need to modify the relevant configuration.

Click "tools"-"options"-"designer" on the menu bar, uncheck "prevent saving changes that require re-creation of the table", and click OK to modify the table structure again. As shown below:

3. The query analyzer displays the line number

This is very useful for debugging SQL code, because the SQL error will tell you which line the error is in, and you can quickly lock the position by displaying the line number.

To display the line number or configure it in the options dialog box above, as follows:

Select [Line number] to display the number of lines of code.

4. String and date type conversion

Strings and date types can generally be converted to each other, mainly by using the CONVERT () function.

Convert a string to DATETIME format

SELECT CONVERT (DATETIME,'2018-06-26 09DV 54V 30.027')

Convert a date type to a string

SELECT CONVERT (VARCHAR (10), '2018-06-26 09 54 30.027)-- the last 120 is a parameter of the string display format. 5. Common string processing functions

CHARINDEX (SUBSTR,STR)

Returns the position where the substring SUBSTR first appears in the string STR, or 0 if the character SUBSTR does not exist in the string STR

SELECT CHARINDEX ('data', 'SQL database development')-result: 4

LEFT (STR, LENGTH)

Intercepting the STR,LENGTH from the left is the length of the intercept.

SELECT LEFT ('SQL database development', 6)-result: SQL database

RIGHT (STR, LENGTH)

Intercepting the STR,LENGTH from the right is the length of the intercept.

SELECT RIGHT ('SQL database development', 6)-results: l database development

SUBSTRING (STR,N, M)

Returns the string STR, starting with the Nth character and intercepting the M character.

SELECT SUBSTRING ('SQL database development', 4Power3)-result: database

REPLACE (STR, STR1, STR2)

Replace STR1 characters in the string STR with STR2 characters

SELECT REPLACE ('SQL database development', 'SQL',' sql')-result: sql database development

LEN (STR)

Calculate the length of the string STR

SELECT LEN ('SQL database development')-result: 8

REVERSE (STR)

Invert the string

SELECT REVERSE ('SQL database development')-results: issue database data LQS6, copy table data INSERT INTO TableName1 (field1, field2, field3) SELECT field4, field5, field6 FROM TableName2

Note: the column data types of the replicated table and the replicated table need to be the same.

7. Conversion of letter case

Change uppercase letters to lowercase letters

UPDATE TableName SET Field = LOWER (Field)

Convert lowercase letters to uppercase letters

UPDATE TableName SET Field = UPPER (Field) 8, delete tables / data

DELETE FROM TableName

Just delete some data from the table, and the table structure is still there.

DELETE can delete some data with a WHERE clause, such as DELETE FROM Student WHERE Age > 20

Automatic numbering does not revert to its original value.

TRUNCATE TABLE TableName

The TRUNCATE statement cannot follow the where condition, cannot be deleted according to the condition, and can only delete all data.

The automatic numbering is restored to the original value.

Using TRUNCATE to delete all data in a table is much more efficient than DELETE, because TRUNCATE operations are logged in a minimal manner.

TRUNCATE deletes data without triggering DELETE triggers.

DROP TABLE TableName

Delete the table itself, that is, all data and table structures (columns, constraints, views, keys) in the table are deleted.

At this point, I believe you have a deeper understanding of "what practical tips for SQL operation". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.