SQL Common SQL-to-digest

Common SQL-to-digest

Think about your usual SQL, and make a record, the goal is to achieve through this page to find their own needs of SQL , continue to add shortcomings, please remind the correct

First I created two libraries, two tables per library. (at work, each company better have its own database model, the product can also see, model tools generally with PD (power designer) or something, easy to use and easy to standardize, suggest Meng new learning)

·

The second library crtest2 is to copy the first crtset copy of the table to the right--"script--" Open the window copy of Library 2 (recommended to glance at the script, the execution of the script is unsuccessful, the topmost database is changed to the target database)

1. Adding and deleting changes

Check: The main skills of programmers there are many ways to talk about here

Select field name from Table name (* represents all fields of a table, in fact, do not recommend the specific development of all, which field to check which, * number of efficiency is too slow, consumption performance, optimization part)

Check three words of the name of the person, and the output of name= Yang Yongxin for Lightning King, this hand is to use case when (encounter condition query do not worry, step by step, first to ensure the correctness of data logic, write well, then to optimize SQL) (Beautify SQL with Navicat Premium, SQL Format,sql Beautifier and so on, these three pro-test easy to use)

Case WHEN: Then : Else: End as if ... else .... Just use it.

Check the amount of data, not recommended with COUNT (*), with count (field name), no field name constraint with Count (1) to check, execution speed is very different

even table query, more than two tables above the table query, be sure to remember the big table in front of the small table after. In fact, inner join,left join use more, here to make a distinction, when exactly when with the inner, when with left

There are many online, too official to say, easy to fan. I'll talk about it in a popular place.

Left JOIN: Two tables associated with the right side is eldest brother, everything to eldest brother, Big Brother table data unconditional all return, other table with eldest brother corresponding data, eldest brother also all want (so generally, left than inner data volume more)

INNER JOIN: Internal Union Two tables is the bow to each other humility, you have I have data to be, take common ground

Syntax: SELECT * from a table left join B table on a.id = b.ID is simple

cross-Library query is also the same, here I check crtest and crtest2 two libraries of the Helloman table of the joint query, very simple, look at

Go heavy

SQL de-weight general use Distinct,group by these two methods, introduce

distinct: SELECT DISTINCT (field name) from table name where condition

groupby: Select field from table name Group BY field having condition

With GROUP BY, you use the having syntax instead of where (I tend to group by because it's not just about looking up, deleting the weight, conditional nesting can be written together, more functions.) ）

Row_number () function

This row_number () is a bit of a performance when the database executes, but its tasks are mostly used to improve performance. Strange, look down.

You can help him as an ingredient group function or an analytic function.

Just contact. NET. You are familiar with Gridview,repeater these controls. We use paging when we have a lot of data, but it's usually a page that is directly bound to a data source, called a false paging, or a single select from a database. But with Row_number () can be made into real page, each time only the PageSize bar data. Improves performance speed.

Syntax: Row_number () Over (Order by field name Desc)

See, there's more than one sort of field in where a real page is OK

Let's say we want to check out a store for a period of time, the 88th person to consume customer information. Hey, one step words can also be written, not row_number () function is really not good to write. Using Row_number () is much better.

Back to our test sheet on the above we check the age in 15-25, four characters, ranked second highest martial arts masters (martial arts rankings according to their own CID to calculate, sword dust first, because less data we can see from above the answer, is Sekiya brewery), SQL is as follows:

Group first, then look for a simple

Fuzzy query

Very simple, one can see here to say that we often use the wildcard character for the percent, but there are some like ' _ A_ ', such as ' [A]b ' is also a wildcard, LZ think the keyword Like is finished. You know, that's fine.

Mention, the left and right side with the %, is on both sides of the fuzzy horse, only on the left is the left side of the match, the same side, a look to know

Time-Date function

Convert () date () You can go here and see the learning http://www.w3school.com.cn/sql/func_convert.asp.

Well, I'm not going to write it here.

Data format conversion, timestamp

Time stamp: Linux time is better database storage Time fields are time-stamped, type int, security, small footprint, simple view timestamp https://tool.lu/timestamp/

method: CONVERT (VARCHAR), DATEADD (S, ttime + 8 * 3600, ' 1970-01-01 '), as times (here I have added a ttime field in the test table, with To save the time stamp, take out the time to use this function to convert to times, the accuracy of their own settings)

Convert () in SQL convert () function in addition to the conversion time can also be converted in other formats, the most common is money, money,decimal type, and if the database is a varchar type, and then you throw in the int, Dateteime or something like that. CONVERT () it's simple.

Format: CONVERT (the type to be transferred, the data to be transferred) For example: Convert (VARCHAR (), Tid) tid is int type to VARCHAR, very simple

When it comes to skilled use of the IsNULL () function, subtraction will have to wrap a layer of IsNULL () function outside.

Format:IsNull (data, default) for example IsNull (sum,0) as Sum

First write here, almost enough, there is time LZ will add, summed up to write complex SQL does not necessarily need to be those novel or too complex functions, according to a little bit of logic, step to check, slowly improve their SQL.

2. Delete, update

Write Delete UPDATE statement be sure to write where, develop good habits

Update table name set field name = ' ... ' WHERE (1=1)

Delete from table name where (1=1)

3. Increase

Insert into table name (field name) values (add data)

Next, will write an article, the thousand level of the database of the bulk INSERT, I understand the new introduction of the stored procedures, triggers, SQL things and the data on the table is too large optimization solution, very simple, big guy please ignore.

Slowly discovering the fun of programming with a simple two-sentence code

SQL Common SQL-to-digest