Accumulate SQL _MySQL

Dynamic SQL at that time, she taught me the most amazing way to use SQL to generate SQL statements and finally execute the query. it is roughly to use sys. table to find the table name, generate the SQL statement and merge it, and finally execute exec. It seems to be useful. Assume that the database contains a bunch of tables with similar naming rules and structures.

At that time, she taught me the most amazing way to use SQL to generate SQL statements and finally execute the query. it is roughly to use sys. table to find the table name, generate the SQL statement, merge it, and execute exec. It seems to be useful. Assume that there are a bunch of tables with similar naming rules and structures in the database. to find the data of this batch of tables, consider using this method.

First, check the table name.

SELECT name FROM sys. tables WHERE name like '% table name mode %' AND type = 'u'

The wildcards mentioned above will not be mentioned. The next step is to splice the SQL statement for data query. It turns out that it can be used in this way. it seems that I was not enlightened or very honest in the past.

SELECT 'Union SELECT * from' + name FROM sys. tables WHERE name like '% table name mode %' AND type = 'u'

In this way, the query result is the final SQL semi-finished product. But I think we should try not to use *. here, the table structure is different, and the UNION will go wrong. Next we need to combine and intercept them. here we use two functions: for xml path ('') and stuff (), the order is to merge the above results into a string and then intercept it. The SQL statement is as follows:

DECLARE @ SQL varchar (max)

SET @ SQL = SELECT 'Union SELECT * from' + name FROM sys. tables WHERE name like '% table name mode %' AND type = 'U' for xml path ('')

Set @ SQL = stuff (@ SQL, 1, 7 ,'')

Select @ SQL

In this way, you can display the SQL statement. to execute the statement, you only need exec (@ SQL.

This statement can be used not only to query data, but also to batch delete tables of a certain type. However, the disadvantage is that the capacity of varchar (max) is limited, if the table or statement is too large and varchar (max) cannot be placed, the final SQL statement cannot be executed!

Associate subquery

In the previous company, Bing Ge taught us not to use subqueries, which slows down the query speed. However, this DBA taught me to use table subqueries, the original subquery is placed in the FORM clause. DBA teaches to put the subquery into the JOIN clause. when I mention that it affects efficiency, DBA says no, and I do not understand it. This statement has many application scenarios. let me give you an example.

If there is a two‑dimensional table, you need to query the results of each student to remove the highest and lowest scores of all tests throughout the semester. in this case, the associated subquery is suitable

SELECT a. * FROM Exam AS a LEFT JOIN

(SELECT [name], MAX (score) AS MaxScore, MIN (score) AS MinScore FROM Exam group by [name] having max (score) <> MIN (score) AS B

ON a. name = B. name AND a. score <> B. MaxScore AND a. score <> B. MinScore

WHERE B. name IS NOT NULL

The personal accumulation phase starts from below.

Remove duplicates

Removing duplicates involves subqueries, but subqueries are divided into subqueries associated with the FROM clause and directly in the WHERE clause. In common, several columns that are regarded as duplicate columns are first grouped to check their keys, and then the largest or smallest columns are retained in another query. The remaining DELETE operations are also Exam (id, name, score, subject) table as an example, only one score is required for a single subject, and the rest are removed.

Delete from Exam WHERE id not in (select min (id) FROM Exam group by name, subject)

In addition, the association deletion in the FROM clause is listed in the Union table deletion. if there is no primary key, or the combination of primary keys does not allow a value to uniquely identify this row, another method I have come up with is: create a temporary table # temptable, INSERT # temptable SELECT DESTINCT, and then delete the original table, finally, INSERT the data in the temporary table to the original table and delete the temporary table. I don't know if this is suitable for large data volumes.

Join table update

Table join update is just a basic SQL syntax, but the basic skills are not solid enough to record it.

UPDATE a SET a. value = B. monvalue WHERE table1 AS a inner join table2 as B on a. id = B. id WHERE .......

Here, by the way

UPDATE table1 SET value = (SELECT monvalue FROM table2 where id = table1.id)

And MySQL

UPDATE table1 AS a, table2 AS B SET a. value = B. monvalue WHERE a. id = B. id

Join table deletion

Take care of the deduplication data mentioned above, and directly go to the SQL

DELETE a FROM Exam AS a left join (select min (id) FROM Exam group by name, subject) AS B ON a. id = B. id WHERE B. id IS NULL

Case when

Case when actually has two formats. the simplest one is the case function form, as shown in the following example:

Case sex

When '1' THEN 'male'

WHEN '2' THEN 'female'

ELSE 'others' END

Another form is the case search function, as shown below:

Case when sex = '1' then''

When sex = '2' THEN 'female'

ELSE 'others' END

I personally think that case when achieves the effect of branch judgment in the query. The case function form will be described simply in the appearance syntax, however, in terms of the effect, the case function is suitable for determining branch values as discrete values. The case search function is suitable for determining conditions with a certain range or a wider degree of freedom, of course, this also contains the discrete value. However, when the case search function determines that a condition is met, it will block the following suitable conditions. The following statement can distinguish the grades of each score.

SELECT *, CASE

WHEN score> 90 THEN 'excellent'

WHEN score> 80 THEN 'good'

WHEN score> 60 THEN 'qualifying'

ELSE 'none' end from Exam

However, there are only two levels: Pass and unqualified.

SELECT *, CASE

WHEN score> 60 THEN 'qualifying'

WHEN score> 80 THEN 'good'

WHEN score> 90 THEN 'excellent'

ELSE 'none' end from Exam

I 've been writing case when... Statements such as is null always try to see the syntax without making any mistakes. after distinguishing the case function and the case search function, you will understand that the case when... Is null, not case... When is null.

Row-to-column

The following table structure is SubjectTest (id, name, subject, score ). If the result (student, Chinese, mathematics, and English) is displayed, you need to use the row-to-column conversion function.

Row-to-column conversion is useful in the case when statement above. it is probably to First Group records by name, then separate the scores from the group by case, and then use the aggregate function to find the greatest value or sum, statement.

Select name, MAX (CASE [subject] WHEN 'Chine' THEN [score] ELSE 0 END) AS 'China ',

MAX (CASE [subject] WHEN 'mate' THEN [score] ELSE 0 END) AS 'mate ',

MAX (CASE [subject] WHEN 'then [score] ELSE 0 END) AS 'English ',

SUM ([score]) AS 'total ',

AVG ([score]) AS 'average'

FROM SubjectTest

Group by [name]

In the past, when there were too many subjects, in addition to having to write a few more times, MAX could also generate statements in execution using the SQL code taught by the DBA sister, unfortunately, View does not support this, alas!

In SQL Server 2005 or later, there is another way of statement, directly on the statement

SELECT * FROM subjecttest variance (SUM (score) FOR [subject] IN (Chinese, mathematics, English)

I am surprised that all the results on the Internet are merged, but my results are like this.

If there is a row-to-column conversion, there will also be a column-to-row conversion, but this is not interesting, but we also talk about UNPIOVT.

Select name, 'China', as score FROM SubjectTest

UNION ALL

Select name, 'mat', Mathematics as score FROM SubjectTest

UNION ALL

Select name, 'English ', English as score FROM SubjectTest

SELECT * FROM subjecttest uncompare (score for [subject] IN (Chinese, mathematics, English)

Month year day function

These functions are used to calculate the corresponding part of the date, as the name suggests, which is the month, year, and number of days.

DECLARE @ testTimePoint DateTime

SET @ testTimePoint = '2017-10-23'

Select year (@ testTimePoint) as year, MONTH (@ testTimePoint) as month, DAY (@ testTimePoint) AS DAY

Cast function

The Cast function is used for data type conversion. There are three points to note when using the Cast function.

(1) the data types of the two expressions are identical .;

(2) two expressions can be implicitly converted.

(3) the data type must be explicitly converted.

As shown below, an error is reported.

Select cast ('12. 3' AS int)

Because '12. 3' can only be converted to a floating point, but not an integer, but the floating point can be converted to an integer, so that it can be converted successfully.

Select cast ('12. 3' AS Float) AS Int)