Database SQL Server connection query variable selection loop DELAY statement

Use Lianxi
Go
--1, Supplier Table gong
--(Gcode supplier number
--Gname supplier name
--Gtel supplier Telephone)
CREATE TABLE Gong
(
Gcode int PRIMARY KEY,
Gname varchar (20),
Gtel varchar (20),
)
Go
Insert into Gong values (101, ' Zibo business ', ' 0533-123456 ')
Insert into Gong values (102, ' Jinan business ', ' 0531-234567 ')
Insert into Gong values (103, ' Qingdao business ', ' 0532-345678 ')
Select *from Gong
--2, Warehouse table Cang
--(Ccode Item No.
--Name of the CNAME item
--Cshu number of goods
--Cprice commodity price
--CG goods supplier number)
CREATE TABLE Cang
(
Ccode int PRIMARY KEY,
CNAME varchar (20),
Cshu int,
Cprice Decimal (18,2),
CG INT,
)
Go
INSERT into Cang values (1001, ' Coca-Cola ', 100, ' 6 ', 101)
INSERT into Cang values (1002, ' Fanta ', 110, ' 5 ', 101)
INSERT into Cang values (1003, ' Sprite ', 120, ' 4 ', 101)

INSERT into Cang values (1004, ' Dragon ', 200, ' 3 ', 102)
INSERT into Cang values (1005, ' instant noodles ', 210, ' 2 ', 102)
INSERT into Cang values (1006, ' tofu ', 220, ' 1 ', 102)

INSERT into Cang values (1007, ' Yida ', 300, ' 7 ', 103)
INSERT into Cang values (1008, ' green Arrows ', 310, ' 8 ', 103)
INSERT into Cang values (1009, ' clear ', 320, ' 9 ', 103)
Select *from Cang
--3, Store table men
--(Mcode Item No.
--Mname Item Name
--Mshu number of goods
--Mprice goods price)

CREATE TABLE Men
(
Mcode int,
Mname varchar (20),
Mshu int,
Mprice Decimal (18,2)
)
Go
Insert into men values (1001, ' Coca-Cola ', 10, ' 6 ')
Insert into men values (1002, ' Fanta ', 20, ' 5 ')
Insert into men values (1003, ' Sprite ', 30, ' 4 ')

Insert into men values (1004, ' Dragon ', 40, ' 3 ')
Insert into men values (1005, ' instant noodles ', 50, ' 2 ')
Insert into men values (1006, ' tofu ', 60, ' 1 ')

Insert into men values (1007, ' Yida ', 70, ' 7 ')
Insert into men values (1008, ' green Arrows ', 80, ' 8 ')
Insert into men values (1009, ' Clear ', 90, ' 9 ')
Select *from Men

--Local variables
DECLARE @bianhao INT--Declaring variables
--set @bianhao = 123--Variable assignment
Select @bianhao =CG from Cang where Cname= ' Coca-Cola '-variable assignment
--print @bianhao-The value of the output local variable
Select @bianhao-The value of the output local variable

--Global variables (System variables)
SELECT @ @servername-server name
SELECT @ @rowcount-gives the number of rows affected by the most recent select
SELECT @ @datefirst-system constant, returns a value

--SELECT statement
DECLARE @a int
Select @a=mshu from men where Mname = ' Coca-Cola '
If @a<11
print ' need to add Coca-Cola '
Else
print ' Coca-Cola adequacy '

If (select Mshu from men where Mname= ' Coca-Cola ') <10
print ' need to add Coca-Cola '
Else
print ' Coca-Cola adequacy '

--Inventory less than 200, find out the name of the product and the supplier's information
Select Cang.cname, Gong.gname,gtel from Cang
Join Gong on GONG.GCODE=CANG.CG where cshu<200

--Looping statements
DECLARE @s int
DECLARE @i int
Set @i=1
Set @s=0
While @i<=10
Begin
Set @[email Protected][email protected]
Set @[email protected]+1
End
Print @s

--Delay to the specified time
Begin
waitfor time ' 15:14:00 '
Select *from Gong
End
--How long is the time delay
Begin
WAITFOR DELAY ' 00:00:10 '
Select *from Gong
End

Database SQL Server connection query variable selection loop DELAY statement