Shulou(Shulou.com)06/01 Report--

4. Views, indexes, and transactions

View is a virtual table or query table derived from one or more data tables (basic tables). It is an important mechanism for relational database systems to provide users with multiple perspectives to observe the data in the database.

The benefits of the view: it can simplify the operation of the user; the view can provide security protection for confidential data.

When you create a view, the name of the view exists in the sysobjects table. Information about the columns defined in the view is added to the syscolumns table, and information about view dependencies is added to the sysdepends table. In addition, the text of the create view statement is added to the syscomments table.

When inserting data into a table through a view, this is not allowed if the list of insert statements contains columns that are not selected in the view and columns that are not allowed to be null values.

Create a view: create view view_employee as select emp_id,fname,lname from employee

Use View: select * from view_employee

Modify the view: alter view view_employee as select emp_id,fname,job_id from employee where job_id > 10

Delete View: drop veiw view_employee

View structure: exec sp_help view_employee

View view definition information: exec sp_helptext 'view_employee'

An index provides a way to quickly access the data rows of a table based on the values of one or more columns. The index provides the logical order in the table.

A clustered index sorts and stores rows of data within a table based on their key values. When a data table establishes a clustered index with a column key, the data rows in the table are stored in the sort order of that column (clustered index key). There can be only one clustered index per table.

A nonclustered index has a structure that is completely independent of data rows, and multiple nonclustered indexes can be established in a table.

Create a clustered index: create clustered index studid_ind on stud (studid)

Create a nonclustered index: create unique index studfullname_ind on stud (fname desc,lname)

Delete index: drop index stud.studid_ind

Check the index on the stud table: exec sp_helpindex stud

A transaction is a mechanism, an operation sequence, which contains a set of database operation commands, and all commands as a whole submit or revoke operation requests to the system.

The characteristics of transactions: Atomicity, Consistenty, Isolation, Durability.

Transaction classification: show transactions, implicit transactions, auto-commit transactions.

Creation, use, modification, and deletion of views, indexes, and transactions

5.Transact-SQL programming

Global variables: defined and maintained by the system, with names beginning with the @ @ character

Local variables: defined and assigned by the user, whose names begin with the @ character

Output statement: print

Logic control statements: begin...end; break; case; continue; goto; if...else; return; while

Common functions: rowset function, aggregate function, scalar function

Conversion function: convert (dt,e,s), cast ()

Mathematical functions: absolute value abs (n), rounding ceiling (n) up, rounding floor (n) down, specifying power power (nMague y), rounding round (ncentree length), finding the symbol sign (n), square root sqrt (n)

Date and time functions: dateadd (datepart,num,date), datediff (datepart,date1,date2), datename (datepart,date), datepart (datepart,date), getdate (), year (date), month (date), day (date)

String functions: lower (e), upper (e), left (eMagience I), right (eMagneur I), replace (s1meme s2Power3), repeat a specified number of times with 3 instead of 2Magi replicate (eMaginI) in 1, stuff (s1Magint startMagery S2) replaces the specified position in 1 with 2, substring (expression,start,length)

Metadata functions: db_id ('database_name'), db_name (datebase_id), object_id (' obj_name'), object_name (obj_id), col_length ('table','column'), col_name (table_id,col_id)

Aggregate functions: avg (expr), count (expr), count (*), max (expr), min (expr), sum (expr)

Select au_lname,au_fname,contory =

Case state

When 'ut' then' utah'

When 'ca' then' california'

Else 'world'

End,city from authors order by state desc

While (select avg (price) from titles) 50 break

Else continue

End

Print 'price is too high'

Begin

Insert into jobs values (`axiajie 80234)

If @ @ error0 print 'data insertion failed'

Else goto M

End

M:print 'data inserted successfully'

This article is reproduced. If you have any questions, please contact us at https://3pomelos.1688.com/.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.