Database Experience Summary

Summary of database Experience 1. SQL statements for querying 21st to 30th pieces of data in the testtable (Table Primary Key is ID) table are as follows:

SQL Server:

Select top 10 * From testtable where id not in (select top 20 ID from testtable)

ORACLE:

Select * from (select T. *, rownum rowid from testtable t) Where rowid between 21 and 30

2. Delete duplicate data in the database

Note: (the distinct keyword can only filter data with the same columns in the table)

SQL Server and Oracle:

<1> Create a table (testtable_temp) with the same structure as the current operation table (testtable) as a temporary table.

Create a temporary table:

The SQL Server statement is as follows:

Create Table testtable_temp

(

Id int,

Name nvarchar (50)

)

The statement in Oracle is as follows:

Create Table testtable_temp

(

ID number,

Name nvarchar2 (50)

)

<2> Create an index for the temporary table (testtable_temp) (in SQL server2005 ):

Remember to check the columns in the "General" column and the columns with the index key (if all columns are added, the effect will be the same as that with distinct) and the two option boxes in "options.

<3> load the data in the testtable table to the temporary table testtable_temp (the duplicate data is automatically filtered out because the index is set)

The statement is as follows:

Insert into testtable_temp select * From testtable (the temporary table is the data we want)

<4> delete data from the testtable table

The statement is as follows:

Delete testtable

<5> import data from temporary tables to testtable

The statement is as follows:

Insert into testtable select * From testtable_temp

<6> Delete the temporary table testtable_temp

The statement is as follows:

Drop table testtable_temp

 

3. Randomly retrieve 10 data records

Sqlserver:

Select top 10 * From test_table order by newid ()

ORACLE:

Select * From test_table where rownum <10 order by sys_guid ()

4. Differences between inner join and left/right join.

SQL Server and Oracle:

For example, table

Aid	Othera
1	2
2	3

Table B

Bid	Otherb
2	3
3	4

Inner join:

Select * from a inner join B on A. Aid = B. Bid

Evaluate the aid = bid link set in A and B

Aid	Othera	Bid	Otherb
2	3	2	3

Left join:

Select * from a left join B on A. ID = B. ID

Evaluate the link set of aid = bid in A and B, and add

For a set that matches B, A is still the same, and B's field is blank.

Aid	Othera	Bid	Otherb
1	2	Nul	Nul
2	3	2	3

Right join:

Select * from a rRight join B on A. Aid = B. Bid

Evaluate the link set of aid = bid in A and B, and add

The set that matches a. B is still the same, and the field of A is empty.

Aid	Othera	Bid	Otherb
2	3	2	3
Null	Null	3	4

 

5. Differences between clustered and non-clustered indexes in sqlserver

<1> Definition

Clustered index: physical storage is sorted by index, just like the dictionary body. We can find our records in alphabetical order of the body. (Person familiar with dictionary search)

Non-clustered index: physical storage is not sorted by index, just like the text search directory on the left side. The page numbers of words under each partial directory are not sequential.

<2> comparison

Clustered index: the data insertion speed is slow (the time is spent on the sorting of physical storage, that is, the location must be located first and then inserted). The data query speed is faster than that of non-clustered data.

<3> understanding

Clustered Index

Each table can have only one primary key. The default primary key is a clustered index, but it is often not suitable for business purposes. You must set the clustered index:

2. The most frequently used field to narrow the query scope;

2. You need to sort the most frequently used fields.

Non-clustered Index

You can create multiple

<4> Environment

Action Description	UseClustered Index	UseNon-clustered Index
Columns are sorted by group.	Ying	Ying
Returns data within a certain range.	Ying	Should not
One or few different values	Should not	Should not
Different decimal values	Ying	Should not
Different values of large numbers	Should not	Ying
Frequently updated Columns	Should not	Ying
Foreign key column	Ying	Ying
Primary Key Column	Ying	Ying
Frequently modify index Columns	Should not	Ying