SQL review Summary (refer to Boyou)

 

Below are some simple summary and review of the SQL script. Most of them refer to Boyou, And I have rewritten it myself.

-- Create a database use ainfolianggoif exists (select * From sys. databases where name = 'ainfoliang ') begindrop database ainfoliang -- print '1' endgocreate database ainfoliangon (name = 'ainfoliang', filename = 'C: \ ainfoliang. MDF ', size = 5, -- the initial size of the database maxsize = 100, -- the maximum size of the database filegrowth = 10% -- the Automatically increasing percentage) log On (name = 'infoliang _ log', filename = 'C: \ ainfoliang_log.ldf ', size = 5, -- initial database size maxsize = 50, -- maximum number of filegrowth databases = 10% -- automatic increase Number of parts) -- create a table ------------------------------------------------ use ainfolianggoif exists (select * From sys. tables where name = 't1') Drop table t1gocreate table T1 (loginid int identity (1, 1) not null, loginname varchar (20), loginpwd varchar (30 )) if exists (select * From sys. tables where name = 't2') Drop table t2gocreate table T2 (userid int identity (1, 1) not null, username varchar (20), userpwd varchar (20 ))-- Add data insert T1 (loginname, loginpwd) values ('11', '11') to table T1 insert T1 (loginname, loginpwd) values ('22', '22 ') insert T1 (loginname, loginpwd) values ('33', '33') insert T1 (loginname, loginpwd) values ('44', '44') insert T1 (loginname, loginpwd) values ('55', '55') insert T1 (loginname, loginpwd) values ('66', '66') insert T1 (loginname, loginpwd) values ('77 ', '77') -- add data insert T2 (username, userpwd) values ('11', '11 ') Insert T2 (username, userpwd) values ('22', '22') insert T2 (username, userpwd) values ('33', '33 ') insert T2 (username, userpwd) values ('44', '44') insert T2 (username, userpwd) values ('55', '55') insert T2 (username, userpwd) values ('66 ', '66') insert T2 (username, userpwd) values ('77 ', '77 ') -- put it in a temporary table select * into # T2 from T2 -- temporary tables in the temporary table are tables whose names start with a well number. If the temporary table is not removed when the user disconnects, SQL Server automatically removes the temporary table. Temporary tables are not stored in the current database, instead, it is stored in the system database tempdb. alter table T2 add userper int -- add a column alter table T2 drop column userper -- delete a column select * From t1union all select * From T2 -- Two Sets table set -- trigger ---------------------------------------------------- use ainfolianggocreate trigger tri_t1on T1 -- used on that table (not multiple tables) for insert --- execute the following SQL statement as select * From t1select * From t2insert into T1 values ('99', '99') when executing the add operation ') -- When adding data to table T1, trigger is triggered -- view using ainfolianggocreate view vi_v1 asselect * From t1select * From vi_v1 -- execute view drop view vi_v1 -- delete View -- stored procedure using ainfolianggoif (exists (select * From sys. objects where name = 'proc _ 1 ')) drop procedure proc_1gocreate procedure proc_1as select * From t1goexec proc_1 -- execute the Stored Procedure drop procedure proc_1 -- delete the stored procedure -- use ainfolianggocreate procedure proc_2 (@ start int, @ end INT) as select * from T1 where loginid between @ start and @ endexec proc_2 @ start = 1, -- int @ end = 10 -- int
-- Use ainfolianggo alter procedure proc_3 (@ name varchar (20) = '% J % ') asselect * from T1 where loginname like @ nameexec proc_3 @ name = '% 8080' -- varchar (20) -- execute a stored procedure with a wildcard -- use ainfolianggocreate procedure proc_4 (@ loginid int, @ loginname varchar (20) Out, @ loginpwd varchar (20) Output) asselect * from T1 where loginid = @ loginid and loginname = @ loginname and loginpwd = @ loginpwddeclare @ loginid = 1 int, @ loginname varchar (30), @ loginpwd varchar (20) select @ loginid = 1; Set @ loginname = '11'; select @ loginpwd = '11'; Exec proc_4 @ loginid, @ loginname out, @ loginpwd output select @ loginname as DD, @ loginpwd as DDD -- paging into use ainfolianggocreate procedure proc_5 (@ start int, @ end INT) asselect * from (select row_number () over (order by loginid) as rowid, * From T1) as tempwhere temp. rowid between @ start and @ endexec proc_5 @ start = 2, -- int @ end = -- int -- pagination 2 ------------------------------ ---use ainfolianggocreate procedure proc_fen2 (@ pageindex int, -- start page @ pagesize int -- number of pages per page) as declare @ startrow int, @ endrow intset @ startrow = (@ pageindex-1) * @ pagesize + 1 -- get the data starting from that row set @ endrow = @ startrow + @ pagesize-1 -- get the data of the end row select * from (select row_number () over (order by loginid) rowid, * from T1) tempwhere temp. rowid between @ startrow and @ endrow exec proc_fen2 @ pageindex = 5, -- int @ pagesize = 2 -- int --- User-Defined Function-defined functions are divided into two types, one is scalar function, another type is table valued functions. Table functions can be divided into inline table valued functions and multi-sentence table valued functions. The 1 scalar function use ainfolianggoif (exists (select * From sys. objects where type = 'fn 'and name = 'fun _ 1') begindrop function fun_1endgo create function fun_1 (@ loginid INT) returns varchar (40) -- Return Value Type as begindeclare @ loginname varchar (40) Select @ loginname = loginname from T1 where loginid = @ loginidreturn @ loginname endselect DBO. fun_1 (1) -- execution function -- table value (Inline Function) ---------------------------------------------- use ainfolianggoif (exists (select * From sys. objects where type = 'fn 'and name = 'fun _ 3') begindrop function fun_3endgocreate function fun_3 (@ loginid INT) returns table asreturn (select * from T1 where loginid = @ loginid) Select DBO. fun_2 (1) -- table value (Multi-statement function) ------------------------------------------------ use ainfolianggoif (exists (select * From sys. objects where name = 'fun _ 4') Drop function fun_4gocreate function fun_4 (@ loginid INT) returns @ T1 table (A varchar (20), B varchar (20 )) as begininsert @ t1select loginname, loginpwd from DBO. t1 where loginid = @ loginidreturn endselect DBO. fun_4 (1)