SQL Second Lecture

Iv. Add, modify, delete

1. Insert a new record into the table

2. Update changes to existing records in the table

3. Delete Deletes existing records in the table

Five, pagination statements

The paging is based on the ordinal column of the record, and the "row_number ()" function gets the record ordinal.

SELECT * FROM (SELECT *, Row_number () over (order by Stuid) as row stuinfo) AA where row between 1 and 15

---The statement returns a 1~15 number record in the Stuinfo table in the stuid sorted record set AA.

SELECT * FROM (SELECT *, Row_number () over (order by Stuid) as row stuinfo) AA where row between and 30

---The statement returns a 16~30 number record in the Stuinfo table in the stuid sorted record set AA.

And so on, get paged data.

Vi. all database objects can be created, modified, and deleted directly by command

Create Creation (creation)

Alter Modify

DROP Removal

Vii. constraints

Maintain the integrity of the data. You can set a default value (for example: date) to limit the range (e.g. age range)

Eight, common functions

1, Getdate () gets the current date time

2, DateAdd (Mm,1,getdate ()) on the current date plus one months (on the specified date plus the number of units of the date time) to obtain a new date.

3, DateDiff (Yy,getdate (), 2020/12/31) Get the difference between two dates in the specified units.

Specified unit: DatePart abbreviation

Year yy, yyyy

Quarter QQ, Q

month mm, M

DayOfYear Dy, y

Day DD, D

Week wk, WW

Hour HH

Minute MI, n

Second SS, S

Millisecond MS

Microsecond MCs

nanosecond NS

4, IsNull (variable, null substituted value)

such as: IsNull (country, ' China ')---Replace with ' China ' when the Country field value is null

IX. case statements available in the query command

case when condition 1 then result 1 Case statement is equivalent to branch statement.

When condition 2 then result 2

....

When condition n then result n

Else Results n+1

End

X. Index

1. The primary key in the clustered index table is a clustered index, and only one clustered index can be in a table.

2. Nonclustered indexes There can be many nonclustered indexes in a table, and such indexes are also used with caution.

A non-clustered index is usually created for a large amount of data with a high frequency of queries.

* * Indexes are fragmented after they are used for a period of time and need to be re-organized periodically and rebuilt.

Xi. views

A view is actually a federated query that can be built in a GUI way.

12. Stored Procedures

Stored procedures provide methods that can be batched in the background.

13. Trigger (Trigger)

1. The INSERT trigger triggers the procedure when there is an insert operation in the table.

2. The UPDATE trigger triggers the process when there is an update operation in the table.

3. The DELETE trigger triggers the procedure when there is a delete operation in the table.

14, Backup, restore

1. Use the backup function provided by the database.

When restoring, create a database with the same name and overwrite the restore backup data on OK.

2, first separate the database, in the replication of the newly separated database as a backup.

When restoring, attaching the backed up database directly to SQL Server is OK.

* * Version compatibility issue: High version compatible with low version, but low version cannot use high version created database.

In the database properties of the High Board database--------------------the appropriate version builds the backup file

XV, import, export

1. Use the import and export functions provided by the database.

The active database is exported to the destination database, or the source database is imported into the destination database.

* * When importing and exporting, pay attention to the problem of disappearing the primary key.

16. Homework

Executes the prescribed command method at a specified time.

1. New Job

2. Step--Give the step name--and then write the command to execute the related job.

Automatic backup: Backup Database MyDB to Disk = ' D:\Test ' with Format, name= ' MyDB-20151023 '

* * The value given to name cannot be assigned to a function, such as the need to create an @ variable to pass when dynamic name is required.

3. Schedule--Give the program name--set frequency, duration--OK.

* * After the work has been built, follow the planned time frequency to execute the steps to make the order.

SQL Second Lecture