Common operations in SQL Server

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26-27--28 29---30 31--32 33 34 35 36 37 38-39 40 41 42 45 46

Create DATABASE personnel_training_dbgouse personnel_training_dbgo CREATE TABLE dept (deptid Int primary key identity (1,1 ,   deptname varchar () NOT NULL, remark varchar. NOT NULL) go SELECT * FROM dept inserts into Dept VALUES (' --1. Create a stored procedure   query created proc proc_dept@deptname varchar asselect * FROM dept where Deptname= @deptnamego- Row stored procedures Exec proc_dept ' Department of Education '    --2. Stored procedure query create proc proc_dept2@deptname varchar as EXEC (' SELECT * FROM dept where deptname= ' + @deptname + '] goproc_dept2 ' teaching department '      --Stored procedure add create proc Proc_add_dept@deptname varchar, @remark varchar asinsert into dept values (@deptname, @remark) goexec proc_add_dept ' faculty ', ' You know '    --Stored procedure Delete create proc Proc_del_deptid@deptid varchar as    exec (' delete from dept where DeptID (' + @deptid + ') ') Goproc_del_deptid ' 1,2 '    --Query CREATE proc proc_like_query_dept@deptname varchar (20), @ Remark varchar as if @deptname <> ' and @remark <> '   begin   select * FROM dept where Deptname= @deptname and remark= @remark   end      else if @remark!= ' and @deptname = '    begin   select * FROM dept where remark= @re Mark end Else if @deptname!= ' and @remark = '     begin   select * FROM dept where Deptname= @deptn Ame End else    begin   SELECT * to dept  end goexec proc_like_query_dept ', '   &NB Sp --With output parameter create proc Proc_set@deptid int Outputas Select @deptid = deptid from dept where Deptname= ' academic department ' and remark= ' This can't be a little bit. Think ' go--defines output parameter variable declare @deptid INT--executes output parameter multiple followed by comma-separated exec proc_set @deptid output--output deptidprint ' obtained deptid is: ' + CONVERT (varchar, @deptid)  --stored procedure with return value CREATE proc Proc_returnas declare @deptcount INT--query total number of bars SELECT @ Deptcount=count (*) from Dept return @deptcountgodeclare @deptcount intexec @deptcount =proc_return print ' Total number is: ' +convert (varchar, @deptcount)  --Conditional query Create proc Proc_query_like@like varchar as--dynamic Constructed SQL statement declare @sql varchar (1024) Set @sql = ' SELECT * FROM dept where 1=1 ' Set @sql = @sql + ' and deptname like '% ' + @like + '% '   set @sql = @sql + ' or remark like '% ' + @like + '% '   print @sql   EXEC (@sql) goproc_query_like '--create View/*1, view view        (1) g             View is a dataset of one or more tables, a virtual table, which means that it is not the    of storing data The        mode exists, and the stored SQL language is a query.         (2) Why use view             On the one hand: views can hide some data, restricting users to access only specific columns in the table.            on the other hand: encapsulate complex SQL statements for easy understanding and querying.         (3) How to create a view              syntax:             The CREATE VIEW view name               as                SQL statements              go          (4) Delete View                if exists (select * from sysobjects where name= ' view name ')                     Drop View View name                Go          (5) Advantages p115             perspective: Users can see only what they are interested in, but not the other data in the referenced table, improving the security of the data.             Simplification: The view hides the associated query between the table and tables, and can be done with just one simple query view statement.             Customizing data: Views allow different users to see different or identical data in a way that is not possible.             Merge split Data:             Security: You can grant access as a table. is a security mechanism.          (6) Note Item:              A view can contain only one SQL statement. That is, it is not possible to get multiple result sets with one view.              view can also be added, deleted, modified, check the operation of the modified Data base table synchronization Update. */create  View View_deptasselect * from Deptgoselect * from view_dept-index/* 2, Index index    &nbsp The indexing mechanism is;        to provide the efficiency of query data.              Life Example: a catalogue of various books.          (1) g               Dependencies and table creation provide a way to orchestrate data in a table. is a separate physical data structure.               usually a table of data is stored on two pages:                data is stored in the data page                 Index stored in index page          (2) type   4               is divided into four categories: primary key index                        Clustered Index    clustered                      Nonclustered Indexes   nonclustered                       Unique index    unique                   PRIMARY KEY index: A primary key index is automatically created when a primary key is defined in a table, and is a special type of unique index.                          require each valueare the only ones.               Clustered index: Index based on data sorting and storage in tables                          For example: The body part of a dictionary.               nonclustered index: similar to the process of checking Chinese characters according to the radical radicals. The              clustered index affects the actual physical sort of data, with only one;  in a single table            nonclustered indexes do not affect the actual physical ordering of data, and can be multiple in a single table.                  Unique index: can be implemented by a unique constraint, either a clustered index or a nonclustered index.          (3) How to create an index              Grammar:               Create [unique|clustered|nonclustered] index  index name                on  table name (column name)                   with fillfactor= array [0-100]                  Note: If the preceding keyword is not written, the default is to create a nonclustered index.           (4) Delete index                 if exists (SELECT * from sysindexes where name= ' index name ')                      Drop Index Table name. Index Name                go           (5) Call index                  select * FROM table name with (index= index name) [where ...]              (6) definition index is usually in the table of the data when  &Nbsp;         (7) Advantages and disadvantages p122                 Advantages: Greatly provide query speed                  disadvantage: Indexes need to occupy physical space, and creating and maintaining indexes takes time.     *   Create nonclustered index Index_depton dept (deptname) with Fillfactor=50go select * FROM dept With (index=index_dept)--Delete index drop dept.index_dept