011.t_sql syntax (SQL instance)

--t_sql:transaction _sql (SQL SERVER Proprietary)
--pl_sql: (Unique to ORACLE)

--Role:
--standard sql+ programming (logic) =t_sql

--T_sql Grammar Basics:
--Variables:
--Global variables (System variables) to understand
[email protected]@, System definition, maintenance, assignment
--read-only (use)

--Local variables: User-defined variables (* * * * *)
--@, defined by the user, assigned value, using

--1 Declaration Declare
--c# int i=9
--declare @i int=10
--declare @a varchar (+), @j int

--2 Assignment Value
DECLARE @i int =0
DECLARE @s1 varchar, @j int
Set @i=85--, @j=10 Set1 to assign values to 1 variables
Select @s1 = ' Jack ', @i=10--can assign values to multiple variables
--Output (test)
--Print @s1 + ' score is ' + cast (@i as varchar (10))
Select @s1 + ' results are ' + CAST (@i as varchar (10))
--How to assign a query result to a variable
--Judging employee's average salary, if >2000 output ' wage standard '
-Otherwise the output ' wages are too low '
DECLARE @avgSla money=0--average salary
--Assigned value
--NOTE: Subquery query results not more than 1, or error
--set @avgSla = (select AVG (sal) from EMP)
Select @avgSla =avg (sal) from EMP
--If the query exceeds 1 values, the last value will be assigned to the variable without error
if (@avgSla >=2000)
print ' salary standard '
Else
print ' wages too low '

--known department name (variable), query the Department staff to display the Department's employee information.

declare @em nvarchar (20)
Set @em = ' SALES '
Print @em + ' Department employee information is as follows: '
Select EMPNO as employee number, ename as name, job as work, MGR as superior number, HireDate as entry time, SAL as payroll from emp,dept
where EMP. Deptno=dept. DEPTNO and [email protected]


Control statements:
If statement

IF (condition)
BEGIN
--only 1 statements, begin end can be ignored
END

While statement

while (condition)
BEGIN
--Loop body
End
The number between the--eg:1-100, which can be divisible by 3 of the number of the and, output and.

declare @num int =1, @sum int=0
while (@num <=100)
Begin
if (@num%3=0)
Begin
Set @[email protected]
End
Set @num +=1
End
print ' 1-100 can be divisible by 3 of the number of the and is: ' +cast (@sum as varchar (10))

--eg: Raise wages, cycle up, until all employees pay more than 2000, each up 200 yuan. The company system has a salary ceiling: the employee's salary limit is 5000 yuan
SELECT * FROM EMP

DECLARE @sal int= (select MIN (sal) from EMP)
while (@sal <=2000)
Begin
Update EMP Set sal+=200
where sal+200<=5000
End
Select Sal from EMP

--case End Statement (interview question)
Nesting in select,update to implement functionality
Statement: Case
When condition 1 then result 1
When condition 2 then result 2
...
else other results
End

--eg
Select Sname,sex=case
When sex=1 and then ' male '
When sex=0 then ' woman '
End from Info

--Interview questions:
Change the 1 of the sex column in the info table to 0 and 0 to 1.
Update info Set Sex=case
When Sex=1 then 0
When sex=0 then 1
End
SELECT * FROM Info

Use practice
CREATE TABLE TEMP
(
SJ DATETIME,
RE VARCHAR (10)
)
INSERT into TEMP VALUES
(' 2015-5-9 ', ' win '),
(' 2015-5-9 ', ' negative '),
(' 2015-5-10 ', ' win '),
(' 2015-5-10 ', ' negative '),
(' 2015-5-10 ', ' negative ')

SELECT * from TEMP

SELECT Sj,sum (case when re= ' wins ' then 1 ELSE 0 END) as ' wins ',
SUM (case when re= ' wins ' then 0 ELSE 1 END) as ' negative '
From TEMP
GROUP by SJ

CREATE TABLE KECHENGTB
(
Techid int,
[Week] nvarchar (20),
IsClass nvarchar (10)
)
INSERT INTO KECHENGTB values
(1, ' 2 ', ' there '),
(1, ' 3 ', ' there '),
(2, ' 1 ', ' there '),
(3, ' 2 ', ' there '),
(1, ' 2 ', ' have ')

Select Techid as teacher number,
SUM (case if week=1 then 1 else null end) as Monday,
SUM (case if week=2 then 1 else null end) as Tuesday,
SUM (case if week=3 then 1 else null end) as Wednesday
From KECHENGTB
GROUP BY Techid

011.t_sql syntax (SQL instance)