Database Basics Summary __ Database

Recently in the review of the final exam, the way to save these notes, but also convenient to view later, involved are some basic knowledge, Daniel don't spray ah, there are mistakes welcome advice ~ ~ ~

First of all, of course, have to remember some common words ah ... The following will appear in the vocabulary are probably summed up, convenient to view, save the full article to find (English good on their own skip it.) )。 Of course, not pure word translation, followed by the text may be the translation may also be the place he used.

-DBCC shrinkdatabase: Shrinking Database
-Constraint: Constraints (used when data integrity constraints are defined)
-Primary key: PRIMARY KEY constraint
-unique: Uniqueness constraint
- Clustered | Nonclustered: Clustered index or nonclustered index
-check: Check constraint
-default: Constraint
-Foreign key: FOREIGN KEY constraint
-on update Cascade on Delet Ecascade: Cascading updates and deletions in foreign key constraints
-sql:structured query Language Structured Query Language
-having: Filtering groups
-Exists: Presence
-Union: Union
-Truncate table: Empty tables (do not log operations, cannot be recovered)
-begin Transaction: Transaction start
-Commit Transaction: Transaction end, execution process successful
-Roll Back Transaction: Transaction rollback, execution error
-set implicit_transactions on: Start of implicit transaction
-Set implicit_transactions off: implicit transaction shutdown
-@ @Trancount: detecting nested layers of connection transactions
-sysobjects: basic information on new tables
-syscolumns: Main record new table column information
-sysindexes: Primary record to use for new table lock storage empty and primary keys, and other information.
-Excute: Execute
-@ @fetch_status: Get the last cursor data extraction operation result status, 0 for success,-1 for failure,-2 indicates that the row to be taken is not in the recordset and has been removed from the collection.
-PROCEDURE: Stored procedures

Database base and Design database system includes database, database management system, user of database and software and hardware of supporting database management system. The five commonly used objects in a database are tables, stores, views, triggers, indexes. Three relationships of database objects: one or one pairs, many pairs. Information is useful data and data is the manifestation of information. Information is characterized by: infinity, sharing, creativity. Pattern is a description of the logical structure and characteristics of all data in a database. Database level Three mode: internal mode (physical layer), conceptual mode (logical layer), external mode (user layer). The user structure of database system: Single user structure, master-slave structure, client-server mode structure and distributed structure. First paradigm: Eliminate duplicate fields in a relationship, and each field is the smallest logical storage unit. Second paradigm: All non-primary key fields depend entirely on the primary key, and there is not a partial dependent primary key on the Non-key field. Third paradigm: Remove transitive dependencies (do not include fields that can be computed or deduced).

database structure and management master data file (. mdf): The starting point of the database, which can point to other parts of the file in the database. Secondary database file (. ndf) transaction log file (. ldf): Contains all the log information required to recover the database. Shrink Database DBCC shrinkdatabase (database name, shrink size)

The storage principles and integrity of the

table Create management data type: value: BIGINT, INT, SMALLINT, TINYINT, decimal and numeric, FLOAT and real currency type: money , smallmoney character: CHAR, VARCHAR, TEXT date Time type: DATETIME, smalldatetime integrity constraint null value constraint: null | NOT NULL PRIMARY KEY constraint: Constraint Pk_ Schedule _ class number PRIMARY KEY (class number) Uniqueness constraint: Constraint constraint name unique [clustered | nonclustered] (column name) CHECK constraint: Cons Traint Ck_ Timetable _ Class number check (class number like ' s[0-9][0-9][0-9][0-9] ') Default constraint: Sex char (2) Default ' Male ' FOREIGN KEY constraint: Constraint fk_ class number Foreign key (Course number) References Schedule (Lesson number) on UPDATE cascade on DELETE Cascade (includes cascading deletes and modifications) modify the table structure: ALETR The specified property can be deleted after the constraint and index deletion associated with the property column. You cannot delete system tables and the user tables referenced by the FOREIGN KEY constraint by using the DROP TABLE statement, you must first delete the referenced foreign KEY constraint or referenced table.
~ ~ ~ ~ I can not tell which table to delete, which one can not be .... Here is an example to illustrate it, as follows: the creation of two tables, MAINTB and FOREIGNTB,FOREIGNTB in the school field is constrained by the foreign key, at this time we can not delete the MAINTB, and FOREIGNTB can be directly deleted, of course, If the FOREIGNTB is deleted, MAINTB can also be deleted.

Query processing and table data edit alias: Add ", eg: ' New name ' when the alias has a space." The DISTINCT keyword is scoped to the entire query list, not to a single column, so distinct is placed behind the select. Queries involving null values, with the EXP is [NOT] null, and not the ' = ' or '. = "or" <> "instead. If computed columns are used in a select and are required to be sorted by this computed column, there are three ways to represent them in the ORDER BY clause:
1. Computed column sequence number 2. Expression of computed column 3. The alias of a computed column

Select number, score, score +10 as new score from
student form
ORDER by 3        --The second way: to calculate the ordinal number of a column by
score +10--the second way: to evaluate the expression of a column
By new score-   -the third way: to compute the alias of a column

Grouped queries
The GROUP BY clause can group the query result set by the principle of equality of one or more columns.

--The number of selected courses in the course name level of a course Select class number
, Count (school number) from the selected timetable group by program number

The column name in the GROUP BY clause can only be a column name from the list in the FROM clause, not an alias.
The columns involved in the Select target column expression must be satisfied, either in the GROUP BY clause or in a statistical function.

--The number of students who are "s0601" in the top 5 of the inquiry number and who have enrolled in more than two courses, select from the selected curriculum where the like
' s0606% ' GROUP BY-number has count (*) >=2

The difference between where and having:
1. Different action objects (where-> table; having-> group);
2. There are differences in the composition of selection conditions, where conditions cannot directly contain statistical functions, and the columns involved in a having condition must either be in the GROUP BY clause or in a statistical function. Connection Query
Normal connection
Join on connection (Inner join)
Outer joins (left-outer join, right outer join, fully connected full outer join) subquery
Checks for subqueries result sets include:
Checks whether the given value is in the result set (in the Join subquery and parent query);
Check the size comparison of the elements in the given value and the result value (single value comparison; multi-value comparison);
Checks whether the result set is empty.

--Select the name of the student who took the course with the program number 010101 selected from the
student table as
where exists (SELECT * from Selection of the curriculum as
                s
                where E. School Number = S. Learning number and course number = ' 010101 '
             )

--***** The   characteristics of such inquiries:
--*****   1. The conditions of subqueries are often used in the tables involved in the parent query;
--*****   2. The SELECT clause of a subquery is generally written as a select *, without giving a specific column name;

Get first 8 records  desc descending  asc Ascending
Select Top 8 * newstab where type= ' notification notice ' ORDER by zhiding Desc,id desc;

Insert subquery Results
1. Use the Insert SELECT clause to create the table yourself
2. Use the SELECT INTO clause system to create the table itself (must be aliased when the target column is a computed column)

Delete data
You can use the delete from table name or the Truncate table statement to empty the target table (log operations are not logged faster than delete, and his deletion cannot be recovered)

An index index is a structure that sorts the values of one or more columns in a database.

Role:
By creating a unique index, you can guarantee the uniqueness of the data record.
Can greatly accelerate the speed of data retrieval.
You can speed up the connection between tables.
When retrieving data by using the ORDER BY clause and the GROUP BY clause, you can significantly reduce the time that is grouped and sorted in the query.
Indexes can improve system performance by using the query optimizer during the retrieval process.

Index classification
Clustered index: Logically orderly, physical also orderly, a table can only have 1;
Nonclustered index: Logical order, physical disorder, a table can only have 249;

Creation and deletion of indexes

--creation of index
[unique] [clustered|nonclustered] index
index name
on datasheet | view (field ASC|DESC)-
-Index Delete
drop Index Table name. Indexed name

Characteristics of Transaction transactions: atomicity, consistency, isolation, permanence. The basic unit of recovery and concurrency for transactions. Transactional concurrency Data problem: lost or overwritten, read dirty data, cannot read repeatedly, Phantom read. Transaction classifications: Display transactions, implicit transactions, automatic transaction mode (SQL Server default). Show transactions:

BEGIN TRANSACTION--                      statement start

commit transaction/commit work-       -transaction End execution successful
rollback transaction/rollback Work   --transaction End execution failed

An implicit transaction
All create statements, alert table, all drop statements, Truncate Table,grant,revork,insert,delete,update,select,open,fetch will restart a transaction.

Set implicit_transactions on--an implicit transaction turns on the
set implicit_transactions off--Implicit transaction shutdown

@ @Trancount detect how many layers of connection transactions are nested. Basic lock
Shared-Lock (S-lock): for read-only operations, which allow multiple transactions to read resource locks, but prevent other transactions from modifying the locked resource.
Exclusive lock (x Lock): The resource it locks can no longer be locked by another transaction, so other transactions cannot be read and modified.

Transact-SQL program Structure

A batch is a collection of SQL statements, a batch that ends with go, using the base rules for the batch:
1. All create statements should be made in a separate batch and cannot be used with other SQL statements in batches.
2. After modifying the table structure using ALTER TABLE, the newly defined columns cannot be used in the same batch.
When the 3.excute statement is the first statement in the batch, the Excute keyword can be omitted.
4. Batch command go and other SQL statements cannot be used on one line.

Conversion functions
Cast (< expression > as < Target data type > [(< length >)])
CONVERT (< Target data type > [(< length >)],< expression >[,style])

Case Statement Usage

Case < input expression >
    when < as expression 1> Then < result expression 1>
    when < as expression 2> Then < result expression 2>
    ................
    When < when expression n> Then < result expression n>
    {Else < result expression n+1>} End case Time
    < conditional expression 1> Then < The result expression 1> when
    < conditional expression 2> Then < result expression 2>
    ...
    . ....... When < conditional expression N> Then < result expression n>
    {Else < result expression n+1>} end

view planning and Operations
The view corresponds to the outer mode in level three mode, which stores the definition of the view and the information associated with the underlying table, without storing the corresponding data for the view, which is still stored in the underlying table of the exported view, so the view is called a virtual table.
Key benefits of using views in a database:
-Views can simplify the user's operation;
-View is the user can look at the same data from a multiple perspective;
-View provides a certain degree of logic independence for database reconstruction;
-Views can provide security for confidential data.

--Creating the syntax format for the view CREATE view
 view_name
 as select_statement-    -Defines the view's SELECT clause
 [with CHECK option]-- Forcing all data modification statements executed on the view must conform to the guidelines set by select_statement with
 encryption--indicating that SQL Server encrypts the text system table columns containing the CREATE VIEW statement to prevent views from being used as SQL A portion of server replication is published.

 --When you create a view, the name of the view exists in the sysobjects table, and the column information that is defined for the view is added to the syscolumns table, and the information about the view dependencies is stored in the
 --sysdepends table, and in addition, the CREATE view The text of the statement is added to the syscomments table.

 eg:
 Use teaching management
 IF exists (select table_name from information_schema.views where table_name= ' v_ view name ')
    drop view V_ view name C12/>go
 CREATE View v_ view name with
 encpyption
 as
 SELECT lesson number, class name, textbook from
 curriculum
 where the Department = ' Information College ' with
 CHECK OPTION
 Go

--Modify View
Alert View view_name as
select_statement
[with CHECK option]
--Use Alert view Change the view that is currently in use, and SQL Server will provide an exclusive schema lock for him.

--View rename
exec sp_rename ' v_oldname ', ' v_newname '

--Delete View
drop view view_name

When a view references more than one table, the data cannot be deleted with delete, and if you use Update, the updated column must belong to the same table as the insert.
Select_statement does not have aggregate functions in the selection list, and does not include top,group by,union or DISTINCT clauses.
There are no derived columns in the select_statement list.
An UPDATE or INSERT statement can only modify data in one of the referenced base tables.
When a view refers to only one table in the FROM clause, the DELETE statement can refer to an updatable view.

Cursors
Cursors provide a flexible means of manipulating data retrieved from a table, and, in essence, an identity is actually a mechanism for each fetch of a record in a demerit set that includes multiple data records.
SQL Server supports three kinds of cursors: Transact-SQL cursors, API server cursors, client cursors.

--Cursor Declaration
DECLARE < cursor name >  cursor   
[Local|global]
[Forward_only|scroll]                --forward_only forward-only cursor,
[Static|keyset|dynamic|fast_forwar]  --static static cursors, can not react to the user's change results dynamically dynamic cursors, at any time to reflect the user's change results
[read_only |scroll_locks|optimistic]
for <select query >


eg:
Declare student table _CUR1 cursor
for select school number, name From
    student table where professional = ' computer '

--Open cursor open
cursor name

--read cursor data
fetch [Next | prior | first | last |absolute{n| @nvar} | Relative {n| @nvar}  from]--next returns the top row of the result set-
                                                                                   Prior | A | Last |absolute{n| @nvar} | Relative {n| @nvar} only the scroll option is defined to use the
cursor name into the
@ variable 1,@ variable 2

--Close
the cursor close cursor name
--close statement closes the cursor, but does not release the data structure occupied by the cursor, the application can perform open open and fill cursors again

Eg:
Declare student table _CUR1 cursor for
Select school number, name, department from
    student table 
    Group

by number declare @snum char (5),
        @sname char (),
        @sdepa char-
-open cursor Open
Student table _cur1
--fetch next from student table with cursor first row data
_ Cur1 into @snum, @sname, @sdepa
--Displays teacher information line by row, and takes a row of data while
@ @fetch_status = 0
BEGIN
        select @snum, @ Sname, @sdepa
        fetch next from student table _cur1 into
        @snum, @sname, @sdepa
End
--closes the cursor, and you can reopen the close
Student Table _cur1
--release cursor
deallocate student table _cur1
go

Cursor positioning modification and deletion operations

--cursor location modification and deletion operations--syntax format:--cursor Positioning modify UPDATE statement: Update table name set clause where CURRENT of cursor name--Cursor positioning delete de Lete statement: Delete from table name set clause where CURRENT of cursor name--the modification or deletion that is made with where present is affected only by--eg: First look at each row in the student table, the number = "s060
109 "Recorded mobile phones to 13888320247 and changed the city to Tianjin declare @ School Number char (6), @ name char (10), @ phone char (11), @ Native char (d) DECLARE stu_up_cur cursor For select number, name, phone, origin from student form for update of phone, native open stu_up_cur cursor fetch NEXT from stu_up_cur cursor to @ School number, @ Name, @ phone, @ native while @ @fetch_status =0 begin SELECT @ School Number, @ name, @ phone, @ Native if @ School number = ' s060109 ' update student table Set phone = ' 1388832024 7 ', place of origin = ' Tianjin ' WHERE current of stu_up_cur cursor--modification or deletion using where present is only affecting the fetch next from Stu_up_cur cur Sor into @ School number, @ name, @ phone, @ Native end close Stu_up_cur cursor deallocate stu_up_cur cursor 

user-defined function design
There are three types of user-defined functions: Scalar-type functions (which return a single data of the type defined in the returns clause), inline table-valued functions (returning a return value as a table type), and multiple-statement table-valued functions (returns a table)
Actions that cannot be performed in a function include modifying a database table, manipulating a local cursor that is not on a function, sending an e-mail message, attempting to modify a directory, and generating a result set that is returned to a user.

--Create a scalar-valued user-defined function-
-syntax format create function
function_name return
scalar_return_data_type
begin
    function body return
    scalar_expression
end

--eg: Create a grade conversion scalar value function, realize hundred and excellent pass-through conversion use
teaching management
Go
Create function F_ fraction level (@ score float) return
char (at)
as
begin
    DECLARE @ rank char ()
    SELECT @ rank = case when
           @ score is NULL then ' has not participated in the exam ' when
           @ <60 then ' fail '
           .... End
           return  @ level 
End


-Call mode
select Number, DBO. F_ score level (score) as ' Grade ' from student table where learn number = ' s060606 '

--Create an inline user-defined function-
-syntax format create function
function_name return
table
begin return
    select_stmt
End

--eg: Create an inline function that returns the student information of the specified college use
teaching management go
create function f_ Student information (@ Faculty char) return
table
           as return (select number, gender from student table where the department of =@)


-Call mode
SELECT * from DBO. F_ Student Information (' Information college ')

--Create a multiple-statement table-valued Function-
-syntax format
create function function_name return
@return_variable table as
begin function Body
    return end

--eg: Create a multiple-statement table-valued function, returning to the execution of a certain year of teacher information use
teaching management go
Create Function F_ Teacher's schedule (@ Teacher name Char (20), @ Start of school year char (9)) return

@ Teacher Schedule Table (
        class name varchar,
        Place char (6),
        Selected number int
) as
begin
           Insert @ Teacher's Timetable
           Select Lesson Name, course location, selected number from
           teacher table T, start table O
           where T. Work number = O. Work and
           school year =@ The school year return end

Invocation Method
select * from DBO. F_ Teacher's timetable (' John ', ' 2015-2016 ')

Using the alert function command is equivalent to rebuilding a function with the same name
You cannot change the type of a function with alert function, that is, scalar-valued functions, table-valued functions, and multiple-statement functions cannot be converted to each other

Delete functions: Drop function Function_name

design of stored procedures and user stored procedures
A stored procedure is a set of SQL statements that completes a specific function and is compiled and stored in a database.
stored procedures in SQL Server are divided into two categories: system-supplied stored procedures and user-defined stored procedures.
System procedures are primarily stored in the master database and prefixed with SP_, which provides support for system administrators in managing SQL Server by obtaining information from system tables.
A user-defined stored procedure is a stored procedure created by a user and capable of accomplishing a particular function.
The benefits of stored procedures: Stored procedures allow standard-build programming. The stored procedure can achieve faster execution speed. Stored procedures can reduce network traffic. Stored procedures can be fully exploited as a security mechanism. Automates the tasks that need to be done beforehand.
Although the stored procedure has both parameters and return values, unlike the function, the return value of the stored procedure simply indicates whether the execution was successful, and he cannot be invoked directly as a function, and must have the EXEC reserved word before invoking the stored procedure name.

A stored procedure consists of three parts:
-All input parameters and output parameters passed to the caller.
-An action statement executed against the database, including statements that invoke other stored procedures.
-Returns the state value of the caller to indicate whether the call succeeded or failed.

--Create a stored procedure with parameters to achieve a specific course for a particular subject students selected courses and results of the query. Use
teaching management go to
if exists (select * from sysobjects where name= ' p_ student elective information ' and type= ' P ')
    BEGIN
        DROP proce Dure P_ Student Elective course information end go

CREATE PROCEDURE p_ Student Elective information (@ Professional char (20), @ class name char) as
if  @ Professional is null< C11/>begin
        print ' must specify professional '
    end
else
    begin
        Select S. School number, name, Major, Department, O. class, O. Class name, O. Grade
        From student table as s, schedule as E, start form as O, Schedule C
        where professional =@ Professional and class name =@ and S. =e. Course number and E. Course number =o. class number
    =c 0/>--Execute stored procedure
exec  p_ student elective information ' computer ', ' data structure '

Modify stored procedure alter
Delete stored procedure drop procedure stored procedure name

principle and application of trigger
Triggers can be viewed as a special kind of stored procedure that automatically triggers execution when a particular condition is met, and triggers are defined for UPDATE, INSERT, delete operations on the table, that is, when an update, insert, and delete operation occurs on the table.
The main function of a trigger is the ability to achieve complex referential integrity and data consistency that cannot be guaranteed by primary and foreign keys. Triggers also have many features: hardening constraints. Cascade Run.

A call to a stored procedure.

Types OF triggers:

After trigger: This type of trigger requires that only one action is performed [INSERT | UPDATE | DELETE, and after all constraints have been processed, triggers can be triggered and can only be defined on the table.

INSTEAD OF triggers: This class of triggers indicates that the defined action is not performed [INSERT | UPDATE | DELETE, and executes only the trigger itself. Can be either on the table or on the view.

Trigger principle:
Each trigger has two special tables: the Insert table and the Delete table, inserted and delete, with the following characteristics:

The tables are logical tables, both of which are managed by the system, stored in memory, not stored in the database, and are not allowed to be modified directly by the user. The structure of the two tables always has the same table structure as the table that the trigger acts on. These two tables are dynamically resident in memory, and when the trigger is finished, the tables are also deleted.

The tables are read-only and are readable only within triggers, that is, users cannot write to the tables, but can refer to the table's data in triggers.

The ability to insert a table:
Once an insert is performed on the table, all rows inserted into the table have a corresponding copy stored in the Insert table (inserted), where the Insert table stores the new rows of data inserted by the original table.
To delete a table's functionality:
Once a delete operation is performed on the table, all deleted rows are stored in the delete table (delete).

--insert Trigger
--in the university database, when the new student elective registration information is added to the selection timetable, the number of the course will be updated, and when the number exceeds the maximum number of people can be accommodated, to indicate the number of elective courses are full use
teaching management go
CREATE trigger T_ selection Schedule Insert trigger on
selected schedule for
insert
as
Begin
    DECLARE @ selected number int,@ Limit number int
    SELECT @ Selected number = Number of selected +1,@ = limited number of people selected from the Start
    table o,inserted i
    where O. Course number =i.
    if (@ Selected number > @ selected number)
    begin
        print ' The number of electives is full '
        ROLLBACK TRANSACTION End
    Update class
    set selected number =@ selected number from course
    Table o,inserted i
    where O. Course number =i.

--update Trigger
-the teacher's table and the person in charge must have foreign key association, when the person in charge of the work number modified, the person in charge of the content to be modified, using triggers to implement when a person in charge of the work number modified, cascade change in charge of use
teaching management go
create trigger T_ in charge change on
teacher table for
update as 
Begin
    declare @old_ work number char (