AD

SQL statement using the Encyclopaedia (personal conclusion, with tested examples and explanations)

The following is a personal summary, if a better proposal, trouble writing. . . . (1000 words omitted). . .

------------------------------------------- Review First ----- -----------------------------------
------------------ Databases, tables, indexes, views the basic operation ----------------------- -----
Create a database ------------------- ------------------
create database Rev_1
on
primary
(
name = 'Rev_1',
filename = 'D: \ Rev_1.mdf',
size = 5MB,
maxsize = unlimited,
filegrowth = 10%
)
log on
(
name = 'Rev_log',
filename = 'D: \ Rev_1.l.ldf',
size = 3mb,
maxsize = unlimited,
filegrowth = 10%
) - Command completed successfully.

View database ------------------- -----------------
sp_helpdb master
- Master 6.50 MB sa 1 04 8 2003 Status = ONLINE, Updateability = READ_WRITE, UserAccess = MULTI_USER, Recovery = SIMPLE, Version = 611, Collation = Chinese_PRC_CI_AS, SQLSortOrder = 0, IsAutoCreateStatistics, IsAutoUpdateStatistics 90

Modify the database ------------------- -----------------
- With modify modify the database, you can only modify a property
alter database Rev_1
alter database name = 'Rev_2' - can not modify the above command, and command changes the database name

sp_renamedb 'Rev_1', 'Rev_2' - Database name 'Rev_2' has been set.

alter database Rev_1
modify file
(
name = 'Rev_1',
size = 3Mb - MODIFY FILE failed. The specified size is less than the current size.
)

alter database Rev_1
- Modify file
- Add file (...)-- additional data file
- Add log file (...)-- increase in the log file
- Remove file file_name - can not remove the primary data file, only use this command to remove the secondary data file
- Remove filegroup fg - file group 'fg' has been deleted.
- Add filegroup fg filegroup_name
(
name = 'Rev_1',
size = 6Mb
) - Can also modify other properties

alter database Rev_1
Delete the database ------------------- -------------------
drop database Rev_1 - can not delete the database "Rev_1", because the database currently in use.

Create table ------------------- -------------------
create table Rev_t1
(
rid int primary key identity (1,1) not null,
rname varchar (20) null constraint rdft default 'Joe Smith'
rage int null constraint rck check (rage> = 18 and rage <= 100),
rphone varchar (30) null constraint rck_1 check (rphone like '1% ')
)

------------------- ------------------- Records for the table to increase
insert into Rev_t1
(Rage, rphone) values (18, '123123 ')

------------------- ------------------- See the table records
select * from Rev_t1
select rname from Rev_t1

Delete table records ------------------- -------------------
delete from Rev_t1 where - rid =? - rname =? - rage =?

See table structure ------------------- -------------------
sp_help Rev_t1 - see table attributes

------------------- Modify table structure: increasing the column -------------------
alter table Rev_t1
add - column Note: the column does not need to increase keyword column
rdate smalldatetime null constraint dft_rdate default getdate ()

------------------- Modify table structure: Remove column -------------------
- Remove the default constraint columns
alter table Rev_t1
drop constraint dft_rdate, rdft

- Remove column check constraint
alter table Rev_t1
drop constraint rck, rck_1
------------------- Modify table structure: Modify column -------------------
alter table Rev_t1
alter column
rname varchar (40) null
------------------- Modify table structure: Delete table -------------------
drop table Rev_t1

------------------- Indexes, views -------------------

Index ---------------- ----------------
- Create Index -
create index in_Rev_t1
on Rev_t1 (rid, rname, rage, rphone, rdate)

- Create a unique clustered index -
create unique clustered index in_Rev_t2
on in_Rev_t1 (rid, rname)

- View Index -
sp_helpindex in_Rev_t1

- Use the Index -
select rname from Rev_t1
with (index (in_Rev_t1)) - Note: Use the Index query, not a direct query index, but to find data from the table,
- Used with with index and the field.
- Management Index
- Rename
sp_rename 'in_Rev_t1', 'in_Rev_t2' - can not rename the index of the order
sp_rename 'Rev_t1.in_Rev_t1', 'in_Rev_t2', 'index' - the original name before the index plus the table name

- Rebuild the index
alter index in_Rev_t1
on Rev_t1
rebuild

- Delete Index
drop index in_Rev_t1
on
Rev_t1

---------------- ---------------- View
- Create a view -
create view vw_Rev_t1
with encryption, schemabinding - encryption, binding
as
select rid, rname, rage, rphone, rdate from dbo.Rev_t1
- Select rid, rname, rage, rphone, rdate from Rev_t1 not be able to view 'vw_Rev_t1' bound to the structure because the name 'Rev_t1' is invalid for schema binding. Name must be composed of two parts, and the object can not reference itself.
- Select * from Rev_t1 the object bound to the framework does not allow using the syntax '*'.

- View View property
sp_help 'vw_Rev_t1'
sp_helptext 'vw_Rev_t1'

- Edit view -
- Rename
sp_rename 'vw_Rev_t1', 'vw_Rev_t2' - WARNING: Changing any part of the object name could break scripts and stored procedures.

- Edit View Properties
alter view vw_Rev_t1
as
select * from dbo.Rev_t1

- Use the View -
select * from vw_Rev_t1 - use the view query and use the index query is not the same

- Remove View -
drop view vw_Rev_t1

---------------- Function, custom type, declare a local variable, multi-table join queries ----------------
- T-SQL provides 4 types of functions: 1. Rowset function 2. Polymerization function 3. Scalar function 4. Ranking function

-------- -------- Function

---- ---- Aggregate Function

---- Rollup cube ----
select username from userinfo
group by username
with rollup

select userage from userinfo
group by userage
with cube

- AVG-SUM -
- Create xs table, and its operation -
create table xs
(
Student ID char (6) not null,
Name char (8) not null,
Professional name char (10) not null,
Sex bit not null,
Birth smalldatetime not null,
Total credits tinyint not null,
Notes text null,
Entry Date datetime constraint dateflt default getdate ()
)
alter table xs
modify datetime constraint datedflt default getdate ()

alter table xs modify [datetime] column default getdate ()

alter table xs
drop constraint dateflt

alter table xs
add addDate smalldatetime null
constraint addDateDflt
default getdate () with values

insert into xs
(Student ID,
Name,
Sex,
Total credits
Note) values (10, 'aa', 'true', 100, 'a')
Create xs_kc table ------------------- ------------------
create table xs_kc
(
Student ID char (6) null,
Name char (8) null,
Course char (10) null
)

select * from xs_temp
select avg (distinct total credits) as avg, sum = sum (all total credits) from xs_temp

- MIN - MAX -
select max (distinct total credits) as the maximum value without duplication, all minimum = min (all total credits) from xs_temp

select * from xs
select average = avg (total credits), Total = sum (total credits)
from xs
select min = min (distinct total credits), max = max (distinct total credits)
from xs

update xs
Total credits = 100 set
where name = 'wer' or name = 'sadf'

- COUNT -
select all columns and = count (all *), no repeated columns and = count (distinct *) from xs_temp - '*' syntax error near.
select all columns and = count (all), no repeated columns and = count (distinct) from xs_temp - ')' syntax error near.
select all columns and = count (all total credits), with no duplication of columns and = count (distinct total credits) from xs_temp

---- ---- String functions
select str (123.456,10,5) - 123.45600
select str (123.456,3,0) - 123
select str (123.456,3,2) - 123 - the total length of the middle of the parameter control, and the third parameter controls the number of decimal places

- LEFT-RIGHT -
select left ('123456 ', 5) - 12345
select right ('123456 ', 5) - 23456
select left ('just do it', 6)
select right ('live now', 5)

- LEN -
select len ('123456 ')
select len ('just do it and live now')
select len (select name from xs_temp where name = 'sadf') - Tips syntax error

- REVERSE -
select reverse ('123456 ') - 654321
select reverse ('just do it and live now')

- LOWER - UPPER -
select lower ('LOWER')
select upper ('upper')
select lower ('JUST')
select upper ('just')

- LTRIM RTRIM -
select ltrim ('ab cde')
select rtrim ('ab cde')
select ltrim ('just')
select rtrim ('just')
- SQL does not trim built-in function, that is, remove all spaces not directly a function of

- Substring -
select substring ('just do it', 4,3)

- Charindex -
select charindex = charindex ('i', 'just do it') - as 'charindex'

- Nchar -
select nchar ('43 ')

- Ascii -
select ascii ('4 ')

- Replace -
select replace ('just_do_it_and_', '', 'live_now')

---- ---- Mathematical functions
- ABS -
select abs (-123456) - take the absolute value

- PI -
select PI () - returns the constant value π

- FLOOR -
select floor (123.456) - taking the greatest integer not greater than 123.456

- SIN COS TAN COT -
select SIN (PI ())
select COS (PI ())
select TAN (PI ())
select COT (PI ())

- SQUARE POWER -
select square (3) - seek the square
select power (4,2) - 4 2 power demand

---- ---- Conversion functions
- CAST -
select cast ('123 .456 'as float)
select cast (date of birth as varchar (30)) as birsday from xs
- CONVERT -
select convert (varchar (30), 123.456)
select convert (varchar (30), time of birth) as birsday from xs

select convert (varchar (20), getdate (), 23)
select convert (varchar (20), getdate (), 24)

---- ---- Date and time functions
- GETDATE -
select getdate () - Returns the current millisecond time

- DATEADD DATEDIFF -
select dateadd (day, 10, getdate ())-- the current date plus 10 days

select datediff (month, '2005-7-30 ', getdate ())-- 2005 August 9 # number of months from a few days (regardless of the number of days scattered)

Create a user types -------- --------
- Create user-defined types -
create type mytype
from varchar (7)

- Delete user-defined types -
drop type mytype

- Through the system stored procedure to create user-defined types -
exec sp_addtype mytype2,
'Varchar (8)',
'Null'

- Delete user-defined types -
drop type mytype2

- Through the system stored procedure to delete user-defined types -
sp_droptype 'mytype2'

-------- -------- Local variables declared

declare @ str varchar (8), @ int1 int
set @ str = 'welcome'
select @ int1 = 7

print 'str:' + @ str
print 'int1:' + str (@ int1)

select * from xs

declare @ name varchar (20)
set @ name = '% s%'
select *
from xs
where name like @ name

select name = 'user', student number = 1, gender, professional name
from xs

declare @ mynumber int
set @ mynumber = 7-1 +7 / 4 * 7
select @ mynumber

-------- -------- Join query

- Create a temporary table -
select * into xs_temp from xs

- In connection -
select * from xs inner join xs_temp on xs. Student ID = xs_temp. Student ID

- External link -
select * from xs a full outer join xs_temp on xs. Student ID = xs_temp. Student ID

---------------- Creation, management and user login name ----------------
---- Log ----

- Create Login -
create login s_a with password = '1234 '
create login [ntc02 \ ZhouYuan] from windows
sp_addlogin s_aaa, '123 '

- Modify the registry -
- Modify the login name and password -
alter login s_a with name = s_aa, password = '321 '

- So login is available -
alter login s_aa disable
alter login s_aa enable

- View login information -
sp_helplogins s_aa

- To give login permission -
sp_addsrvrolemember s_a, 'sysadmin'

- Remove login -
drop login s_a
sp_droplogin s_aa

Users ---- ----
- Create User -
create user s_a - must be created under the premise of creating user login

create user s_a for login s_a

sp_adduser s_aaa

- Edit User -
alter user s_a with NAME = s_aa

- Delete user -
drop user s_aa

sp_dropuser s_aa

- Give users role or authority -
sp_addsrvrolemember s_a, 'sysadmin'

---------------- Create, manage Default, rule, check constraints ----------------
- Entity integrity
- Entity integrity defines a row the only entity for a specific table. Physical integrity of the adoption of the index, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY attribute table identifier compulsory primary key column or integrity.
- Domain Integrity
- Domain completeness refers to the effectiveness of specific items listed. You can enforce domain integrity constraints type (by using the data type), restrictions on the format (by using CHECK constraints and rules), or restrict the range of possible values (through the use of FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions and rules) .
- Referential integrity
- Referential integrity through FOREIGN KEY and CHECK constraints
- User-defined integrity
- User-defined integrity allows you to define integrity are not any other types of specific business rules.

---- Default ----
- Create a table to create a default value constraint -
create table dfttest
(
id smallint,
proccessid int default @ @ spid ,[email protected] output of the current user process ID
insertdate datetime default getdate (),
innum smallint default 30 * 4,
inname char (3),
flag char (5) default 'hello'
)

- Create a separate default constraint -
create default dftname as 'dft'

- Remove default value constraint -
drop default dftname

- To dfttest table inname column default binding constraint -
sp_bindefault dftname, 'dfttest.inname'

- To dfttest table inname unbound column constraint -
sp_unbindefault 'dfttest.inname'

- Test Constraints -
insert into dfttest (
id
) Values (1)

select * from dfttest

---- Rule ----
- Create Rules -
create rule ru_name as @ name like 'dft'
- Rule Name: ru_name the rules for the binding constraint for each column of the rules have to be a field and 'dft' the same

- See Rule Information -
sp_help ru_name

- Create the rule of text messaging -
sp_helptext ru_name

- Bind the rule to the table column inname on dfttest -
sp_bindrule ru_name, 'dfttest.inname'

- Lifting table dfttest binding rules out inname -
sp_unbindrule 'dfttest.inname'

- Delete Rule -
drop rule ru_name

- Truncate table
truncate table dfttest
- Delete all rows in the table, the speed faster than delete

---- Check ----
- Create table create a check constraint -
create table check_test
(
id smallint identity (1,1) check (id> 0 and id <10),
inname char (3),
flag char (5) default 'hello',
constraint check_name check (inname like '[a] [b] [c]')
)
- Test the above constraints -
drop table check_test

insert into check_test (inname) values ('abc')

- Table was created by modifying the table to create check constraint -
alter table check_test
add constraint check_name check (inname like 'abc%')

- By modifying the table to delete the specified table check constraint -
alter table check_test
drop constraint check_name

- Use with nocheck create the constraint can not verify the performance of some records -
alter table check_test with nocheck
add constraint check_name1 check (inname like 'cba')

- By removing the constraint name in the system to remove the constraint -
alter table check_test
drop constraint CK__check_test__id__740F363E

select * from check_test

---- ---- Unique constraint
- Unique constraint is the only constraint is mainly used for the sole constraint columns
- By modifying the table add constraint method to create a unique constraint -
alter table check_test with nocheck
add constraint unique_name unique (inname asc);

- Alter drop by the deletion of the unique table constraint -
alter table check_test
drop constraint unique_name

---- In addition to these constraints, there is a primary key constraint, foreign key constraint can reference inquiries by Li Wei in SQL2005

---------------------------- Stored procedures, custom functions, RAISERROR -------------- -----------------
----------------- ----------------- Stored procedure
- Accept input parameters and the format of the output parameters to the calling procedure or batch to return multiple values.
- Contains the implementation of the database operations (including calling other procedures) of the programming statements.
- Return to the calling procedure or batch status value to indicate success or failure (and failure).
- Create table Article -
create table Article
(
NickName varchar (30) not null,
Title varchar (30) not null,
article varchar (max) not null,
date smalldatetime null
)

- Create table Review -
create table Review
(
Floor tinyint identity (1,1) not null,
NickName varchar (30) not null,
Title varchar (30) not null,
Reviewor varchar (30) not null,
Review varchar (200) not null,
date smalldatetime null
)

- Create table Members -
create table Members
(
NickName varchar (30) not null,
Password varchar (30) not null,
[Name] varchar (30) not null,
Age tinyint null,
[E-mail] varchar (30) null,
Address varchar (100) null
)

- Create table Administrator -
create table Administrator
(
Nickname varchar (30) not null,
Password varchar (30) not null,
Super bit null
)

- Create a table view of all stored procedures up_selectArt_Rew -
create procedure up_selectArt_Rew
as
select * from article, review, members, administrator

- Implementation of the stored procedure up_selectArt_Rew -
up_selectArt_Rew

- Review the table to create the stored procedure to insert records up_insertRew -
create procedure up_insertRew
(
@ Nickname varchar (20),
@ Title varchar (50),
@ Reviewor varchar (20),
@ Review varchar (300)
)
as
insert into dbo.review
(
nickname, title, reviewor, review
)
values
(
@ Nickname, @ title, @ reviewor, @ review
)

- Use up_insertRew stored procedure -
exec up_insertRew @ nickname = 't', @ title = 't', @ reviewor = 't', @ review = 't'
go

- Remove up_insertRew stored procedure -
drop proc up_insertRew

- Show all the tables in the database -
sys.sp_tables

- Create output with output parameters of stored procedures select_name -
create procedure select_name
(
@ Article varchar (100) output,
@ Title varchar (40),
@ Name varchar (10)
)
as
Select article from article where title = @ title and Nickname = @ name

- Use stored procedures select_name -
declare @ art varchar (100)
exec select_name @ art output, @ title = 'first', @ name = 'zhou'
print @ art

- Delete stored procedure select_name -
drop proc select_name

- Set the automatic execution of stored procedures -
create procedure auto_print
as
print 'first auto print'

sp_procoption 'auto_print', 'startup', 'true'

auto_print

- Modify the stored procedure auto_print -

alter procedure auto_print
as
print 'altered auto print'

auto_print

- Encrypted stored procedures auto_print -

alter procedure auto_print
with encryption
as
print 'altered and encryption auto print'

sp_helptext auto_print
- Object 'auto_print' text has been encrypted.

- Recompile the stored procedure auto_print -
alter procedure auto_print
with recompile
as
print 'altered, encryption and recompiled auto print'

- Delete stored procedure auto_print -
if object_id ('auto_print', 'P') is not null
drop procedure auto_print

------------------ ------------------- Custom function
- A function with similar programming languages, Microsoft SQL Server 2005 user-defined function is to accept the parameters
- Perform actions (such as complex calculations) and operating results to the value in the form of return of routine.
- Return value can be a single scalar value or a result set.

- Scalar function -
- Create a custom function fc, the function has a varchar type parameter, a return value of type int,
- The function used to return xs_temp table to count the total number of professional name, returns 0 if not
create function fc (@ v varchar (10))
returns int - pay attention to returns int type, not a parameter
as
begin
declare @ vsum int;
select @ vsum = count (*) from xs_temp - note that not @ vsum = select count (*) from xs_temp
where the professional name = @ v ;
if (@ vsum is null)
set @ vsum = 0 - Note that setting a parameter value, use the set
return @ vsum
end
- Use a custom function fc -
declare @ f varchar (10)
set @ f = 'fe'
select dbo.fc (@ f) from xs_temp - Note to use select instead of using the function exec

- Inline table-valued function -
- Create a custom function f_table, the function has a parameter of type Int, a table type of return value
- This function is used to return all the data in the table xs_temp
create function f_table (@ f int)
returns table - Note: In the inline table-valued functions, the returns here is not the return
as
return (select * from xs_temp)

- Remove the custom function f_table -
drop function f_table

select function f_table () - Note: custom function here is not to use select function f_table ()
select * from dbo.f_table (3)

- Multi-statement table-valued function -
alter function f_table (@ f int)
returns int table
as
begin
declare @ count
return (select @ count = count (*) from xs_temp)
end
- The function to find a solution, not the implementation of

------------------------ RAISERROR instance ----------------------
- By creating a stored procedure p1 test RAISERROR -
create procedure p1 (@ stime varchar (30), @ etime varchar (30), @ vday varchar (30) out)
with encryption - encryption
as
if @ stime> @ etime
- Print 'start time is greater than the end of time' - NOTE: There raiserror place, there can be only a statement of its own, it is rather special
raiserror ('start time is greater than the end of time', 18,2)
else
select @ vday = datediff (day, convert (datetime, @ stime, 120), convert (datetime, @ etime, 120))

exec p1 @ stime = '20090101 ', - note that if here use '@ name = Value 'form after
@ Etime = '20100101 ', - all subsequent arguments on the need to '@ name = Value 'in the form of transfer.
@ Vday1 output
print @ vday1

declare @ vday1 varchar (30)
exec p1 '20090101 ',
'20100101 '
@ Vday1 output
print @ vday1

- Create function -
create function f1 (@ stime varchar (30), @ etime varchar (30))
returns varchar
as
begin
declare @ vday varchar (30);
if @ stime> @ etime
begin
select @ vday = @ etime - print 'start time is greater than the end of time'
- Raiserror ('start time is greater than the end of time', 18,2)
end
else
begin

select @ vday = @ stime - (datediff (day, convert (datetime, @ stime, 120), convert (datetime, @ etime, 120)))

end
return @ vday
end
- Message 443, Level 16, State 14, the process of f1, line 6
- Within the function of the 'PRINT' on with side effects or rely on the use of operator time is invalid.
- Message 443, Level 16, State 14, the process of f1, Line 9
- Within the function of 'RAISERROR' on with side effects or rely on the use of operator time is invalid.

- Test -
declare @ vaday varchar (30), @ stime varchar (30), @ etime varchar (30)
set @ stime = '20090101 '
set @ etime = '20100202 '
select @ vaday = datediff (day, convert (datetime, @ stime, 120), convert (datetime, @ etime, 120))
print @ vaday

---------------------------- DDL, DML triggers, cursors --------------- ----------------

------------- DDL, DML triggers ---------------
- DDL (Data Definition Language, Data Definition Language):
- Used to define the data structure, such as create, modify or delete database objects. Mainly CREATE, ALTER, and DROP statements beginning.

- Create trigger tr_1 -
create trigger tr_1
on database - Note: here can not write all server specified event type on the specified target object is invalid.
for create_table, alter_table, drop_table - Note: here is not the create table create_table
as - Note: Here to use as, rather than what not to write
print 'trigger disabled list operation'
rollback;

create table t_2
(
id int
)

drop table t_1 - Service ended in the trigger. Batch has been halted.

- Remove trigger tr_1 -
drop trigger tr_1 on all server - not on the trigger 'tr_1' implementation of the deletion, because it does not exist or you do not have the required permissions.
drop trigger tr_1 on database - command completed successfully.

- Create a trigger on the server tr_1 -
create trigger tr_1
on all server
for create_database, drop_database, alter_database - NOTE: for write as before
as
print 'trigger against the database operations'
rollback

create database db_1 - Service ended in the trigger. Batch processing aborted.

- Remove the server tr_1 -
drop trigger tr_1 on all server

select * from test

- Create prohibited to create, delete, modify the landing of the trigger and related operations -
create trigger tr_1
on database - Note: The specified event type on the specified target object is invalid. Can not use database, and use all server
for create_login, drop_login, alter_login
as
print 'trigger landing operation against'
rollback

create trigger tr_1
on all server
for create_login, drop_login, alter_login
as
print 'trigger landing operation against'
rollback transaction

create login lo_1 with password = '1 '- transaction ended in trigger. Batch has been halted.

drop trigger tr_1 on all server

- Modify the trigger -
alter trigger tr_1
on all server - NOTE: Modify the trigger, you can not modify this part of the
for create_database, alter_database, drop_database
as
print 'Trigger ban such work'
rollback

drop trigger tr_1 on all server

- Disable to disable and disarm the trigger -
disable trigger tr_1 on all server

enable trigger tr_1 on all server

--------------------------- DML trigger -------------------- ------------
------------------ For, after trigger ---------------------
select * from test
----------- ------- For trigger
create trigger tr_1
on test
after insert
as
insert into test (tname, tage) values ('ta', 12)

drop trigger tr_1

insert into test values ('ta', 13)

----------- ------- Update trigger
create trigger tr_1
on test
for update
as
if update (tname) - NOTE: update (which is written inside the column name)
begin - Note: If if contain a number of statements, then you need to use begin end
print 'test table can not change'
rollback transaction
end

update test set tname = 'china' where tage = 12 - Service ended in the trigger. Batch has been halted.

drop trigger tr_1

- Create a trigger in the view -
create view v_t as select * from test

create trigger tr_1
on v_t
for insert - can not be used for or after the trigger is created in view
as
if update (tname)
begin
insert into test values ('china', 14)
end

--------- -------- Instead of flip-flop
create trigger tr_1
on v_t
instead of insert
as
if update (tname)
begin
insert into test values ('china', 14)
end - the command has completed successfully.

insert into v_t values (1, 'japan', 15)

select * from v_t select * from test
- Results:
- 12 china 14
- 13 china 14
-
- 12 china 14
- 13 china 14

drop trigger tr_1

---------------------- Cursor-cursor -------------------

------- ------- Cursor operation
declare c_1 cursor
for - and not as used here for
select * from test

- Open cursor -
open c_1

- Get Data -
fetch next from c_1

- Close cursor -
close c_1

- The release of the cursor -
deallocate c_1

---------- ------------- Cursor using the example of

select * from test
- Declare variables
declare @ ti int, @ tn varchar (10), @ ta int
- Statement cursor
declare c_1 cursor
for
select * from test
- Open the cursor
open c_1
- Access to data
fetch next from c_1 into @ ti, @ tn, @ ta
- Output table test data after the next two rows of data
print @ tn
print @ ta
- Determine the next line of data exists
- 0 FETCH statement is successful.
- -1 FETCH statement failed or the line is not in the result set.
- -2 Extraction line does not exist.
while @ @ fetch_status = 0
begin
if @ tn = 'SQL'
update test set tname = 'china', tage = 15
fetch next from c_1 into @ ti, @ tn, @ ta
print @ tn
print @ ta
end

if @ @ fetch_status <> 0
print 'is finished'

close c_1

deallocate c_1

-------- Create a stored procedure and instances in which the use of cursor --------

create procedure p_1
as
declare c_1 cursor
for select * from test
open c_1
fetch next from c_1
while @ @ fetch_status = 0
begin
update test set tname = 'japan', tage = 154
fetch next from c_1
end
if @ @ fetch_status <> 0
print 'Done'
close c_1
deallocate c_1

p_1
标签: conclusion, databases, database name, proposal, personal summary, collation, indexes, file group, examples and explanations, command changes, 1000 words
分类: Database
时间: 2010-07-22

相关文章

  1. Classic SQL statement Encyclopaedia (transfer)

    Mssql statement following statement in part, can not be used in access. SQL Categories: DDL-Data Definition La ...
  2. uchome several data call sql statement

    uchome several data call sql statement (2009-05-20 14:32:08) reproduced Tags: topics for 1, call log with pict ...
  3. Optimizing Sql statement should be noted that the 4-point demand

    Optimizing Sql statement to note the following 4 points, of course, this is only my personal opinion, we want ...
  4. (Transfer) to optimize Sql statement should be noted that the 4-point demand

    Optimizing Sql statement to note the following 4 points, of course, this is only my personal opinion, we want ...
  5. SQL Statement: Group By Summary

    1. Group By statement Description: Group By statement from English to understand the literal meaning is " ...
  6. sql statement left join.inner join in on the difference and where

    table a (id, type): id type ---------------------------------- 11 21 32 table b (id, class): id class -------- ...
  7. Useful in SQL Server sql statement date

    Useful in SQL Server sql statement date 1 the first day of the month SELECT DATEADD (mm, DATEDIFF (mm, 0, getd ...
  8. sql statement left join.inner join in on the difference between the where

    table a (id, type): id type ---------------------------------- 11 21 32 table b (id, class): id class -------- ...
  9. Skills: dynamically created SQL statement

    MATT on his blog, has posted a dynamically created SQL statements Bo-wen. In the text, he referred to himself ...
  10. ID generation strategies and Save () whether to issue the sql statement of:

    ID generation strategies and Save () whether to issue the sql statement of: The primary key generation side of ...
  11. SQL statement used the classical

    1 - Note: the word of several high-level query operations 2A: UNION operator 3UNION operator results through a ...
  12. In the SQL statement optimization process, often used hint (change)

    In the SQL statement optimization process, often used hint, The following are the common Oracle SQL optimizati ...
  13. Rails detail (12) Sql statement

    Active Record is how to deal with SQL, we find ways to look at the: conditions parameter, when called like thi ...
  14. sql statement to update the same data, different data insert

    Execute a Sql statement, the same vendor_id, inventory_item_id to update, insert new data in different merge i ...
  15. ORACLE SQL statement Categories

    Oracle SQL statements can be divided into the following categories: Data manipulation language statements [Dat ...
  16. Classic SQL statement

    Classic SQL statement 1. Copy table (copy the data source table name: A (a, b, c) target table name: B) 2. Cop ...
  17. About 1 million records SQL statement optimization, need to remove all the duplicate data

    Need to get all the fields in Table A, b the same data as the SQL used to query a database of all duplicate re ...
  18. sql statement optimization

    Today, their ancestors left behind in optimizing a sql statement and found two problems. These issues are reso ...
  19. An SQL statement face questions on the group by

    An SQL statement face questions on the group by Table contents :2005-05-09 wins 2005-05-09 win 2005-05-09 Nega ...