Shulou(Shulou.com)06/01 Report--

Here's a tip for quickly remembering SQL commands:

Keeping the following special statements in mind, the SQL command will not be a problem

String problem

In standard SQL, strings use single quotation marks.

If the string itself includes single quotes, use two single quotes (note that they are not double quotes, and double quotes in the string do not need to be escaped)

1. Select1. The statement [remember] select * from userwhere username='xiaomin'order by user_id DESC limit 1 offset 2 or limit 2 Magazine 1 ordered order by user_id DESC': in reverse order according to user_id. If there is no DESC, it means to arrange 'limit': set the number of records returned' offset': set the number of offsets when querying. Default is 0. two。 Difficult point

Question:

Why do the two sentences LIMIT 2 OFFSET 1 LIMIT 1 express the same meaning?

Answer:

Remember three points: 1. Indexing starts at 0 in the computer. But the numbers after LIMIT and OFFSET only indicate the number, which has nothing to do with the index. two。 Generally related to the offset parameters (offset 3 rows), such a description does not include the number described, that is, 3 lines of data is no longer in the "field of view", offset to the "visual field", can not be seen, naturally does not include. 3. Generally related to index parameters (intercept index 3 to index 5), such a description includes index 2, does not include index 5LIMIT 2, which means offset 2 rows of data, get 1 data. That is, the third row of data is obtained, LIMIT 1 OFFSET 2: 1 data is obtained, but 2 rows of data are offset, that is, the third row of data is also obtained.

Digression:

Both Java and Javascript have a method for string interception: substring (), which takes the value of the index as a parameter. According to the above description, the following results are not difficult to come out.

/ / indicates the starting and ending index values of "0123456789" .substring (3); / 3456789 "0123456789" .substring (3); / 34 II, insert insert into user (name,age) values ('song',18) 3, updateupdate user set age=18 where id=3 3, deletedelete from user where id=4 4, multi-table query 1. Join. On... (internal connection, or equivalent connection)

Join two tables and return only the data that meets the criteria.

User table and permission table: select user.name, permission.type from userjoin permission on permission.id = user.id2. Left join... On... (left link)

Get all the records in the left table, even if there are no matching records in the right table, and record the fields in the right table that do not match as null.

User and permission: select user.name, permission.type from userleft join permission on permission.id = user.id

Of course, the above can only meet the daily development needs, but for the SQL sentence which is not easy to remember, have you learned a better memory method?

I hope this article will be helpful to 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.