SQL Server Common Syntax points

If exists (select name from sysobjects where name = ' Stuinfo ')
drop table Stuinfo
Go
CREATE TABLE Stuinfo
(
Stuname varchar () NOT NULL,--learner name
Stuno Char (6) NOT NULL,--school number
Stusex char (2) NOT null,--sex
Stuage int NOT NULL,--age
Stuid Numeric (18,0) NOT null,--Social Security number, numeric (18,0) represents 18 digits, and the number of decimal digits is 0
Stuseat smallint identity (,--) seat number: auto-numbering (identity column), starting from 1
STUADDR text--Address
)
Go

ALTER TABLE STUINFO add constraint Pk_stuid primary key (STUID);
ALTER TABLE STUINFO add constraint uq_stuseat unique (stuseat);
ALTER TABLE stuinfo add constraint ck_stusex check (stusex in (' Male ', ' female '));
---------------------------------------------------------------------------------------------------------
If exists (select name from sysobjects where name= ' Stumarks ')
drop table Stumarks
Go
Create table Stumarks
( Br>examno Char (7) NOT NULL,--test number
Stuno char (6) NOT NULL,--study number
Writtenexam int not null,--written test result
Labexam int not null- -Machine test results
)
Go

ALTER TABLE Stumarks ADD constraint Pk_examno primary key (EXAMNO);
ALTER TABLE Stumarks add constraint Uq_stuno unique (Stuno);
---------------------------------------------------------------------------------------------------------
SELECT * from Stuinfo;
INSERT into stuinfo values (' Zhang Qiuli ', ' s25301 ', ' female ', 18,412724198603151234, ' Beijing Haidian ');
INSERT into stuinfo values (' Lis Wen ', ' s25303 ', ' Male ', 22,412724198603161234, ' Henan Luoyang ');
INSERT into stuinfo values (' Force Mogaoyizhang ', ' s25302 ', ' Male ', 31,412724198603171234, ' Shaanxi Xian ');
INSERT into stuinfo values (' Ouyangjun ', ' s25304 ', ' Male ', 28,412724198603181234, ' Kashgar, Xinjiang ');
INSERT into stuinfo values (' Dan Ouyang ', ' s25305 ', ' female ', 23,412724198603191234, ' Xinjiang Kuche ');

SELECT * from Stumarks;
INSERT into Stumarks values (' s27811 ', ' s25303 ', 80,58);
INSERT into Stumarks values (' s27813 ', ' s25302 ', 50,90);
INSERT into Stumarks values (' s27815 ', ' s25301 ', 65,0);
INSERT into Stumarks values (' s27816 ', ' s25304 ', 77,82);
INSERT into Stumarks values (' s27817 ', ' s25305 ', 70,92);

---------------------------------------------------------------------------------------------------------
--local variable declaration/assignment/query
DECLARE @stuName varchar (20)--statement
Set @stuName = ' Force Mogaoyizhang '--assignment @stuname
SELECT * from Stuinfo where [email protected];--query
DECLARE @stuSeat int
Select @stuSeat = Stuseat from stuinfo where [email protected]--assignment @stuseat
SELECT * from Stuinfo where (stuseat = @stuSeat + 1) or (stuseat = @stuSeat-1)--Query
Go
---------------------------------------------------------------------------------------------------------
--if else
Case 1: If the average score of the written test is greater than 70 good, otherwise poor
DECLARE @avgWrt float
Select @avgWrt =avg (writtenexam) from Stumarks
if (@avgWrt > 70)
Begin
print ' excellent score, average score: ' + CONVERT (varchar, @avgWrt)
Select Top 3 * from Stumarks ORDER BY writtenexam Desc
End
Else
Begin
print ' Poor grades, average score: ' + CONVERT (varchar, @avgWrt)
Select Top 3 * from Stumarks ORDER by writtenexam ASC
End
---------------------------------------------------------------------------------------------------------
--Case 2: If the average score of the machine test is greater than 40 good, otherwise poor
DECLARE @avgLab float
Select @avgLab =avg (labexam) from Stumarks
if (@avgLab > 40)
Begin
print ' excellent score, average score: ' + CONVERT (varchar, @avgLab)
Select Max (labexam) from Stumarks
End
Else
Begin
print ' Poor grades, average score: ' + CONVERT (varchar, @avgLab)
Select min (labexam) from Stumarks
End
Go
---------------------------------------------------------------------------------------------------------
--while Loop statement, you can use break to exit the loop of the layer, continue exit when the secondary loop
SELECT * FROM Stumarks where labexam<60
--case 1:labexam is less than 60 points, the cycle is added to 60 points, plus 2 points each time
DECLARE @n int
while (1 = 1)
Begin
Select @n=count (*) from Stumarks where Labexam < 60
if (@n > 0)
Begin
Update Stumarks Set labexam = Labexam + 2 where Labexam < 60
End
Else
Begin
Break
End
End
SELECT * FROM Stumarks

--update Stumarks set labexam=50 where labexam=60
----------------------------------------------------------- ----------------------------------------------
Select * from Stumarks where writtenexam<60
-- Case 2:writtenexam less than 60, loop plus 60 points, add 2 points each time
declare @m int
while (1=1)
Begin
Select @m=count (*) from Stumarks Where Writtenexam <
if (@m > 0)
Begin
Update stumarks Set writtenexam = Writtenexam + 2 where Writtenexam <
End
Else
break;
End
Select * from Stumarks

---------------------------------------------------------------------------------------------------------
--case Multi-branch statement
--select col1,col2 =
--case
--when col2 = Condition1 Then
--when col2 = condition2 Then
--else
--end
--from tabname
Select stuno,writtenexam =
Case
When Writtenexam >= and then ' A '
when Writtenexam between and "B"
when writtenexam between and "C"
when writtenexam between 6 9 Then ' D '
Else ' E '
End
from Stumarks
Select * from Stumarks
----------------------------------------- ----------------------------------------------------------------
Select stuname,stuage =
Case
When Stuage >=, "old"
when stuage between and "strong"
when stuage between and "Young"
else ' baby '
End
from Stuinfo
Select * from Stuinfo

---------------------------------------------------------------------------------------------------------
If exists (select name from sysobjects where name = ' Bankcounter ')
drop table Bankcounter
Go
CREATE TABLE Bankcounter
(
Cstname varchar (+) NOT null,--customer name
Countno Numeric (21,0) NOT null,--account
Curmoney Money,--Account Cash balances
Time_stamp datetime--time Stamp
)
Go
ALTER TABLE Bankcounter ADD constraint Pk_countno primary key (COUNTNO);
ALTER TABLE Bankcounter add constraint Ck_curmoney check (Curmoney > 0);
Go
INSERT into bankcounter values (' Zhang San ', 400006666668888889999,1000, ' 2012-08-09 ');
INSERT into bankcounter values (' John Doe ', 400006666668888889998,1001, ' 2013-08-06 ');
Go
SELECT * FROM Bankcounter
---------------------------------------------------------------------------------------------------------
--Transaction processing: a mechanism, a sequence of operations that executes a set of database operations instructions as a whole and commits or revokes together to the system, i.e.
--This set of instructions is either all executed or not, and any error will be immediately rolled back/undone all operations.
--begin transaction--Start Transaction
--commit transaction--COMMIT Transaction
--rollback transaction--rollback (undo) transaction

--Case: In this case, Zhang San transfer 1000 to John Doe, but because the check constraint Curmoney must be greater than 0, error, so the cash value of update (Zhang San) has not changed,
--but John Doe's cash has risen by 1000.
Use Studentdemodb
Go
Update Bankcounter Set Curmoney = curMoney-1000 where cstname= ' Zhang San ';
Update Bankcounter Set Curmoney = Curmoney + where cstname= ' John Doe ';
Go
--Restore data
SELECT * FROM Bankcounter
Update Bankcounter Set Curmoney = curMoney-1000 where cstname= ' John Doe ';

Transaction processing for-------------
BEGIN TRANSACTION
Use Studentdemodb
Go
Declare @errorT int--define local variable record error count
Set @errorT = 0--initialization
Update Bankcounter Set Curmoney = curMoney-1000 where cstname= ' Zhang San ';
Set @errorT = @errorT + @ @error
Update Bankcounter Set Curmoney = Curmoney + where cstname= ' John Doe ';
Set @errorT = @errorT + @ @error

if (@errorT > 0)
Begin
print ' Error code # ' + CONVERT (varchar, @errorT) + '! ‘
ROLLBACK TRANSACTION
End
Else
Begin
Commit TRANSACTION
End
Go
SELECT * FROM Bankcounter
---------------------------------------------------------------------------------------------------------
BEGIN TRANSACTION
DECLARE @error int
Set @error =0
Update Bankcounter Set Curmoney = curMoney-800 where cstname = ' Zhang San '
Set @error = @error + @ @ERROR
Update Bankcounter Set Curmoney = Curmoney + where cstname = ' John Doe '
Set @error = @error + @ @ERROR

if (@error > 0)
Begin
ROLLBACK TRANSACTION
End
Else
Begin
Commit TRANSACTION
End
Go
SELECT * FROM Bankcounter
Go

---------------------------------------------------------------------------------------------------------
--Index
--create [Unique] [clustered|nonclustered] Index Index_name
--on table_name (column)
--[with
--fillfactor = X
--]
--Description
--unique: Performs a unique index, optional
--clustered/nonclustered: Specify a clustered or nonclustered index, optional
--fillfactor: Represents a fill factor, setting a value of 0-100 that indicates the percentage of space that the index page fills up

--Case 1: The Writtenexam column in the score table Stumark is often queried, creating an index now to speed up the query. Because grades can be repeated,
--so the index can only create nonclustered indexes.
Use Studentdemodb
Go
If exists (select name from sysindexes where name = ' Ix_stumarks_writtenexam ')
Drop INDEX Stumarks.ix_stumarks_writtenexam
Go
Create nonclustered index Ix_stumarks_writtenexam
On Stumarks (Writtenexam)
With fillfactor=30
Go

---------------------------------------------------------------------------------------------------------
--View
--create View View_name
--as
--<select Statement >
--Case: view of Stuinfo/stumarks
If exists (select name from sysobjects where name= ' view_stumarksinfo ')
Drop View View_stumarksinfo
Go
CREATE VIEW View_stumarksinfo
As
Select A.stuname,a.stuno,b.examno,a.stusex,a.stuage,b.writtenexam,b.labexam
From Stuinfo a,stumarks b where A.stuno = B.stuno
Go

SELECT * FROM View_stumarksinfo
---------------------------------------------------------------------------------------------------------
--Stored procedures

--System stored procedures
EXEC sp_databases--lists all of the databases on the server
EXEC sp_helpdb--lists information for all databases
EXEC sp_helpdb studentdemodb--listing information for the specified database
exec sp_renamedb ' A ', ' B '--Change the name of data A to B
EXEC sp_tables--lists the list of objects that can be queried in the current environment

Exec sp_columns stuinfo--lists information for all columns of the specified table
Exec sp_help--Lists all object information in the current environment (table name/constraint name/view)
Exec sp_help stuinfo--returns all information for the specified table (table name/Field/constraint/)
EXEC sp_helpconstraint stuinfo--returns constraint information for the specified table
exec sp_helpindex stuinfo--returns the index information for the specified table
EXEC sp_stored_procedures--returns all stored procedures in the current environment
exec sp_helptext ' view_stumarksinfo '--View view/ Default value/(unencrypted | user-defined stored procedure)/Trigger statement text
----------------------------------------------------------------------------- ----------------------------
--Create a stored procedure without parameters
--create proc[edure] procedure_name
--[
--{@ parameter 1 data type}[= default value] [Output],--where the parameters section is optional
--,--where the parameter part is optional
--{@ parameter n data type}[= default][output]--parameter part optional
--]
--as
--< SELECT statement;

--Case study: see the average score for this exam, and the list of exam participants who failed (Writtenexam < or Labexam < 60)
Use Studentdemodb
Go
If exists (select name from sysobjects where name = ' Proc_avg_nopass_nopara ')
--drop proc Proc_avg_nopass_nopara
drop procedure proc_avg_nopass_nopara--is equivalent to the drop proc Proc_avg_nopass above
Go
CREATE PROCEDURE Proc_avg_nopass_nopara
As
Select AVG (writtenexam) as ' written average ', avg (Labexam) as ' pilot average ' from Stumarks
Select A.stuname,a.stuno,b.examno,a.stusex,a.stuage,b.writtenexam,b.labexam
From Stuinfo a,stumarks b where A.stuno = B.stuno and (B.writtenexam < or B.labexam < 60)
Go

EXEC Proc_avg_nopass_nopara
Go

SELECT * FROM Stumarks
Update Stumarks set writtenexam=55 where stuno= ' s25303 '

Go
---------------------------------------------------------------------------------------------------------
--Create a stored procedure with input parameters
--create Proc[edure] Procedure_name
--[
--{@ Parameter 1 data type}[= default][output],--where the parameter part is optional, there is an output parameter, otherwise the input parameter
--,--where the parameter part is optional, there is an output parameter, otherwise the input parameter
--{@ parameter n data type}[= default value][output]--where the parameter part is optional, there is output parameter, otherwise the input parameter
--]
--as
--<select Statement >

--Case Study: Check the average score for this exam, and the list of exam participants who failed (Writtenexam < custom parameters or Labexam < custom parameters)
Use Studentdemodb
Go
If exists (select name from sysobjects where name = ' Proc_avg_nopass_para ')
drop proc Proc_avg_nopass_para
Go
Create proc Proc_avg_nopass_para
@written int,
@lab int
As
Select AVG (writtenexam) as ' written average ', avg (Labexam) as ' pilot average ' from Stumarks
Select A.stuname,a.stuno,b.examno,a.stusex,a.stuage,b.writtenexam,b.labexam
From Stuinfo a,stumarks b where A.stuno = B.stuno and (B.writtenexam < @written or B.labexam < @lab)
Go

EXEC Proc_avg_nopass_para 55,65
Go

---------------------------------------------------------------------------------------------------------
--Default value (condition) stored procedure
--create Proc[edure] Procedure_name
--[
--{@ Parameter 1 data type}[= default][output],--where the parameter part is optional, there is an output parameter, otherwise the input parameter
--,--where the parameter part is optional, there is an output parameter, otherwise the input parameter
--{@ parameter n data type}[= default value][output]--where the parameter part is optional, there is output parameter, otherwise the input parameter
--]
--as
--<select Statement >

-Case: Check the average score for this exam, and the list of exam participants that failed (writtenexam/labexam default <60)
Use Studentdemodb
Go
if exists (select Name from sysobjects where name = ' Proc_avg_nopass_default ')
drop proc Proc_avg_nopass_default
Go
Create proc Proc_avg_nopass_default
@writePass int=60,--default
@labPass int=60--default value
as
Select AVG (writtenexam) as ' Written average score ', AVG (Labexam) as ' pilot average ' from Stumarks
Select A.stuname,a.stuno,b.examno,a.stusex,a.stuage,b.writtenexam , B.labexam
from Stuinfo a,stumarks b where A.stuno = B.stuno and (B.writtenexam < @writePass or B.labexam < @labP )
Go
exec proc_avg_nopass_default
Go

---------------------------------------------------------------------------------------------------------
--Create a stored procedure with output parameters
--create proc[edure] procedure_name
--[
--{@ parameter 1 data type}[= default][output],--where the parameters section is optional, There is an output parameter, otherwise the input parameter
--,--where the parameter part is optional, there is an output parameter, otherwise the input parameter
--{@ parameter n data type}[= default][output]--where the parameters section is optional, Output parameter is indicated, otherwise the input parameter
--]
--as
--<select statement;

--Case: Check the average score of this exam, and fail to pass (writtenexam/labexam default <60 not pass) the exam trainee list, also want to return to the number of failed
use Studentdemodb
Go
if exists (select name from sysobjects where name = ' Proc_avg_nopass_output ')
drop proc Proc_avg_nopass_output
Go
Create proc proc_avg_nopass_output
@noPassNum int output,
@wPass int=60,
@lPass int=60
as
Select AVG (writtenexam) as ' Test Average ', AVG (Labexam) as ' pilot average ' from Stumarks
Select A.stuname,a.stuno,b.examno,a.stusex,a.stuage,b.writtenexam,b.labexam
from Stuinfo a,stuMarks b where a.stuNo = B.stuno and (B.writtenexam < @wPass or B.labexam < @lPass)
Select @noPassNum = count (c.stuname) from (select A.S Tuname,a.stuno,b.examno,a.stusex,a.stuage,b.writtenexam,b.labexam
from Stuinfo a,stumarks b where a.stuNo = B.stuno and (B.writtenexam < @wPass or B.labexam < @lPass)) C
Go

/* Call the stored procedure proc_avg_nopass_output*/
Declare @sum int--declares a local variable to receive the value of the parameter output from the stored procedure
EXEC proc_avg_nopass_output @sum output--output parameters, this time @wpass, @lPass use default values
Print convert (varchar, @sum)
Go
DECLARE @sum int
EXEC proc_avg_nopass_output @sum output,66--at this time @wpass=66,[email protected] Use default values
Go
DECLARE @sum int
EXEC proc_avg_nopass_output @sum output,66,100--at this time @wpass=66, @lPass =100
Go

SQL Server Common Syntax points