"SQL statements" useful SQL statements Project Database Learning Summary

Reprint Please specify source: http://blog.csdn.net/pearyangyang/article/details/41115491

These days to learn the company's system data flow. It mainly involves several tables of data.

However, the data in the table is millions, so it is assumed that there is no conditional SQL statement. Causes the query card to die, or even a memory overflow condition occurs.

The database used by the company is SQL Server. The SQL statements in the following columns are primarily for the SQL Server database, and the other databases are similar.

1, when I want to be familiar with the database table structure. I need to run a statement. This statement can view very much information about the database, such as column names. The field name. Field size, field type, etc...

SELECT * from information_schema.columns where table_name= ' table name '

2. And, of course, if you want to create a database table, you can run the following statements that call the stored procedure:

EXEC sp_help ' table name '

3, when I want to understand the database master, foreign key, I pass the following statement:

Query primary key: EXEC sp_pkeys @table_name = ' table name '

Query foreign Key:

Select primary key Column name = (selectname from syscolumns where colid=b.rkey and id = b.rkeyid), foreign key table name =object_name (B.fkeyid), foreign key Column name = (select n Ame from syscolumns wherecolid=b.fkey and id = B.fkeyid) from    Sysobjectsa join Sysforeignkeys b on a.id = B.constid Jo In sysobjects C on a.parent_obj =c.id where a.xtype = ' f ' and c.xtype= ' U ' and object_name (b.rkeyid) = ' table name '

4. Find the data in front of the database (able to analyze the design of the database tables and the details of the fields) using TOPkeyword. Because the amount of data is too large, so we can extract data from the first 50 or 100 data to find, detailed usage select Top100 * from table name, when looking for a field in the corresponding table how many kinds of time, can use group by to find.

Select property from table name GROUP by property

The ps:* can be replaced with the column name of the database to find. The following can also be added to query conditions and so on.

5. When we sometimes need to infer whether a field is empty, we need to use the IS null inference or IsNull () function. Commonly used when looking for or deleting. I have not found the difference in performance at the moment, but the use of the two is not the same, I think is null more concise, for example, the SELECT * from table name where name is null, and the IsNull () function is more flexible. Here are a few examples: when we have a user table, assuming that the user has not filled out their nickname at the beginning, we can give it a nickname nickename when we find it. Can be implemented using the following SQL statements.

If the user's nickname is empty. We are able to use Select Name,isnull (nickname, ' Csdn01 user ') from Csdninfo. The default is Csdn01 users.

6. It is best to select once before delete.  , the company has a big event recently, inventory table Warebalance was accidentally deleted, more than 30 W data suddenly no? I do not know the database has no rollback, even if the rollback after the data in real-time in the operation has caused the data in the database error? The reason is that when you delete the statement, you want to delete the Warebalancebak backup table. But because of the wrong operation. Causes the warebalance to be deleted, so we should make a query between deletions, assuming that the query might not cause the above error.

Lenkeyword (based on length lookup)

SELECT * from T_stu where len (sno) = 14 (Find student information for students with 14 digits)

7, when viewing the company field, very many fields are using subqueries, connection query. So it's not going to be able to check the relevant information (figure out the difference between Inner/left/right joins)

8, for example, a table field has the same, you want to query from these same data in one of the analysis, for example, there is a tb_a table

I want to find the unique data of name in the table field, so I can use distinctkeyword to check: SELECT distinct name from Tb_a;

watermark/2/text/ahr0cdovl2jsb2cuy3nkbi5uzxqvcgvhcnlhbmd5yw5n/font/5a6l5l2t/fontsize/400/fill/i0jbqkfcma==/ Dissolve/70/gravity/center ">

Let's say we look at some fields: SELECT distinct name,num from tb_a; We can see the results:

Summary: We'll look at the only conditions later. According to the relevant conditions and requirements to check. Get the results you want

9, the company database design thinking, because of the confidentiality of the relationship, so very much detail can not describe the narrative, but when we design the table, the relationship between the foreign key and the table and table relations must be clear. Sketch assist concept when necessary.

Others, and then gradually summed up the problems encountered.

"SQL statements" useful SQL statements Project Database Learning Summary