Blocking is a common occurrence in an SQL server context, but if you are new to the world of database management you might not know what this issue entails and perhaps even fear that it is a sign of serious underlying problems.To allay your fears and clear up the mystery, here is a brief overview of blocking and the steps you can take to tackle it.Image Source: PixabaySQL blocking explainedSQL blocking according to SentryOne is an offshoot of the way that concurrent databases operate. Because processes can be executed simultaneously, it is likely that at some point more than one process will n...

Yesterday, my friend Liu Bing asked me a question about how to select top 3 values for each person in a database table using SQL. I think this question is interesting and it deserves some thoughts. Here I record down how to solve this issue.Assume we have a table which has two columns, one column contains the names of some people and the other column contains some values related to each person. One person can have more than one value. Each value has a numeric type. The question is we want to select the top 3 values for each person from the table. If one person has less than 3 values, we select...

There are times where running IF statements inside a query can be useful. MySQL provides a simple way to do this through the use of IF and CASE statements.The IF statement takes three arguments; the conditional, the true value and the false value. False and true values may be static values or column values. For example:SELECT IF(score > 100, 100, score) AS scoreFROM exam_resultsthis will return the value in the score column limited to a maximum value of 100. IF statements can also be nested:SELECT IF(score > 100, 100, IF(score < 0, 0, score))FROM exam_resultsCASE statements (s...

In modern web development, it’s standard practice to make use of a database abstraction layer, typically an Object-Relational Mapper based on either the Active Record pattern or the Data Mapper pattern. There are several pros and cons to this which are fairly well established, so I’ll spare us all from enumerating them all right now.One established pro worth mentioning is that these systems typically provide a high level interface for fetching data, often removing the need to ‘hand write’ SQL for anything but the most complex of query.As good as this sounds it h...

The difference is subtle, but it is a big difference. The ON condition stipulates which rows will be returned in the join, while the WHERE condition acts as a filter on the rows that actually were returned.Simple example: Consider a student table, consisting of one row per student, with student id and student name. In a second table, a list of grades that students have received, with student_id, subject, and grade. Give me a list of all students, and show their grade in Math. This requires a LEFT OUTER JOIN, because you want all students, and you know that some of them didn't take Math. Here a...

整理了一下，希望对大家有用SQL语句大全  --语 句 功 能--数据操作SELECT --从数据库表中检索数据行和列INSERT --向数据库表添加新数据行DELETE --从数据库表中删除数据行UPDATE --更新数据库表中的数据--数据定义CREATE TABLE --创建一个数据库�...

SELECT MEMBER.Name, MEMBER.Address, ORGANIZER.phoneNo, TRAVEL.Tour_Name, TRAVEL.Start_Date, TRAVEL.End_DateFROM TRAVEL RIGHT JOIN ((MEMBER LEFT JOIN ORGANIZER ON MEMBER.Member_ID=ORGANIZER.memberID) LEFT JOIN PARTICIPATION ON MEMBER.Member_ID=PARTICIPATION.MemberID) ON TRAVEL.TravelID=PARTICIPATION.TravelID;...