[SQL statement] Easy-to-use SQL statements-suitable for unfamiliar databases and familiar with databases

[SQL statement] Easy-to-use SQL statements-suitable for unfamiliar databases and familiar with databases

Learn the data flow of the company system over the past few days. It mainly involves the data of several tables. However, there are millions of data records in the Table. Therefore, if SQL statements do not have any conditions, the query will become stuck and memory overflow may occur. The database used by the company is sqlserver, so the following SQL statements are mainly for sqlserver databases, while other databases are similar.

1. When I want to familiarize myself with the database table structure, I need to execute a statement that can view a lot of information about the database, such as the column name, field name, and field size, field type .......

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

2. Of course, if you want to create a database table, you can execute the following statements to call the stored procedure:

Exec sp_help 'table name'

3. When I want to know about the primary and Foreign keys of a database, I use the following statement:

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

Query Foreign keys:

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 name from syscolumns wherecolid = B. fkey AND id = B. fkeyid) from sysobjectsa join sysforeignkeys B on. id = B. constid join sysobjects c on. parent_obj = c. id where. xtype = 'F' AND c. xtype = 'U' and object_name (B. rkeyid) = 'table name'

4. Search for the data in front of the database (you can analyze the database table design and field details ).TOPKeyword. Because the data volume is too large, we can extract the first 50 or 100 pieces of data for data analysis. The specific use method is select top100 * from table name, you can use group by to find the number of fields in the corresponding table. Select attribute from table name group by attribute

Ps: * can be replaced by the name of the database column for search, followed by query conditions, and so on.

5. delete data that is null in the database. The where attribute value of the delete from table name is null. It is better to search for the data before deleting it ~

6. When viewing company fields, many fields use subqueries and connection queries, so we still can't look up the relevant information (understand the differences between inner/left/right join)

7. The company's database design thinking involves confidentiality, so many details cannot be described, but when we design a table, the association between foreign keys and the relationship between tables must be clarified. If necessary, you can draw a sketch to help design the concept.

Other problems.

(For reprinted articles on this site, please indicate the author and SourceCoderExtraordinary. Do not use it for any commercial purposes)