Click on “SQL database development”, \

Set it as “top or star mark” to deliver dry goods in the first time

SQL in the use of the process, often encounter some strange strange small problems, today to sum up a few common problems how to deal with.

1. Convert decimals to percentages

When writing SQL, we sometimes want to convert decimals to hundreds of fractions, so we can write:

SELECT CONVERT (
 VARCHAR(20),CONVERT ( DECIMAL (18.2),ROUND(A*100.0/B, 2))) +The '%' AS Rate
Copy the code

Tip: You can swipe the code left or right

Such as:

SELECT CONVERT (
 VARCHAR(20),CONVERT (DECIMAL (18.2),ROUND(2*100.0/3.2))) +The '%' AS Rate
Copy the code

Results:

Code: \

ROUND: ROUND the number, but does not change the length of the number. \

CONVERT(): the first CONVERT, which takes rounded DECIMAL places and controls DECIMAL places to 2 through DECIMAL(18,2)

CONVERT(): The second CONVERT, which converts rounded decimal numbers to a string type followed by a percent sign to complete the percentage display

Two points to note:

  • The dividend is not zero
  • Divisor is converted to float first (here we use 100.0 to convert 2 to float).

 

2. The table structure cannot be saved after modification

We often run into the problem that when we modify the table structure, clicking Save will bring up a warning that the changes are not allowed to be saved. \

To fix this, we need to modify the configuration.

Click [Tools] – [Options] – [Designer] on the menu bar, uncheck [prevent to save the changes required to recreate the table], click OK back to modify the table structure can be normally modified. The diagram below:

 

3. The query parser displays the line number

This is very useful for debugging SQL code, because THE SQL error will prompt you to report the location of the error in the line, display the line number can quickly lock the location. \

To display the line number is again configured in the options dialog box above, as follows:

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

String and date type conversion \

String and date types can generally be converted to each other, primarily using the CONVERT() function. \

Convert the string to DATETIME format,

SELECT CONVERT(DATETIME,'the 2018-06-26 09:54:30. 027');
Copy the code

Converts the date type to a string

SELECT CONVERT(VARCHAR(10),'the 2018-06-26 09:54:30. 027'.120) At the end120Is an argument to the string display formatCopy the code

5, the common string processing function \

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 ') -- Results:4
Copy the code

LEFT(STR, LENGTH)

STR is truncated from the left, with LENGTH being the truncated LENGTH;

SELECT LEFT('SQL Database Development '.6) -- Result: SQL databaseCopy the code

RIGHT(STR, LENGTH)

STR is truncated from the right, where LENGTH is the truncated LENGTH;

SELECT RIGHT('SQL Database Development '.6) -- Result: L database developmentCopy the code

SUBSTRING(STR,N ,M)

Returns the string STR starting at the NTH character, truncated by M characters;

SELECT SUBSTRING('SQL Database Development '.4.3) -- Result: databaseCopy the code

REPLACE(STR, STR1, STR2)

Replace STR1 characters in string STR with STR2 characters;

SELECT REPLACE('SQL Database Development '.'SQL'.'sql'Result: SQL database developmentCopy the code

LEN(STR)

Calculates the length of string STR;

SELECT LEN('SQL Database Development ') -- Results:8
Copy the code

REVERSE(STR)

Invert the string;

SELECT REVERSE('SQL Database Development ') -- Result: issued database data LQSCopy the code

Select * from table_name

INSERT INTO TableName1 (field1, field2, field3)
SELECT field4, field5, field6 FROM TableName2
Copy the code

Note: The column data type of the replicated table and the column data type of the replicated table need to be the same

7, letter case conversion \

Change the uppercase letter to lowercase

UPDATE TableName SET Field = LOWER (Field)
Copy the code

Convert lowercase letters to uppercase letters

UPDATE TableName SET Field = UPPER(Field)
Copy the code

 \

Delete table/data \

DELETE FROM TableName

  • Delete some data from the table, the table structure is still there.
  • DELETE can be used with a WHERE clause to DELETE part of the data, for example DELETE FROM Student WHERE Age > 20
  • Automatic numbering is not restored to the initial value.

 

TRUNCATE TABLE TableName

  • The TRUNCATE statement cannot follow A WHERE condition and cannot be deleted according to the condition. Only all data can be deleted.
  • Automatic number restored to the initial value.
  • Using TRUNCATE to DELETE all data in a table is more efficient than DELETE because the TRUNCATE operation records logs in a minimal manner.
  • TRUNCATE Deletes data and does not trigger the DELETE trigger.

 

DROP TABLE  TableName

  • Delete the table itself, that is, the data in the table and the table structure (columns, constraints, views, keys) are all deleted.
I am my brother Yue, and finally I would like to share with you the PDF electronic version of MY SQL two-piece set: "SQL Basics 2nd Edition" and "SQL Advanced 2nd Edition". There are various grammar explanations, a large number of examples and annotations, and so on, very easy to understand, convenient for everyone to follow together to practice. There is a need for readers can download learning, in the following public number "data front" (not this number) back keyword: SQL, on the line data front -- End -- back keyword:1024, to obtain a carefully organized technical dry goods background reply keywords: into the group, take you into the master like clouds of communication group. Goodbye, the father of anti-virus software, Wang Jiangmin! Another programmer, arrested! My computer is not connected to the Internet, so it is secure. Hacker: You have a fan... CentOS is dead, "free" RHEL without a set of simple and useful SQL scripts, there is always your needCopy the code