SQL BASICS (II)

Advanced SQL Server database operations
(1) Batch Processing
(2) variables
(3) Logical Control
(4) Functions
(5) Advanced Query

*/

(1) Batch Processing
Compile Multiple SQL statements as a whole, generate an execution plan, and then execute!
The key to understanding batch processing lies in "Compilation". For a batch processing composed of multiple statements,
If a syntax error occurs during compilation, compilation will fail!

Create Table t
(
A int,
B INT
)

-- Comment
-- If the multi-line comment contains the batch identifier go
-- During compilationCodeIt is split into multiple parts by go for batch compilation.
-- The multi-line comment mark will be separated, leading to compilation errors
-- The following statements are three very classic batch processing statements.
-- You can guess how many records will be added!
/*
Insert into T values (1, 1)
Go
*/
Insert into T values (2, 2)
Go
/*
Insert into T values (3, 3)
*/
Go

-- Query. Several records are added.
Select * from t

Truncate table t

(2) variables

-- Global variables
Global variables in SQL Server are defined and maintained by the system. You can only read them!

-- View the SQL Server version
Print @ version

-- Server name
Print @ servername

-- System error number
Insert into T values ('A', 'A ')
Print @ Error

Insert into T values ('A', 'A ')
If @ error = 245
Print 'error'

-- SQL Server version Language Information
Print @ Language

-- The first day of a week starts from the day of the week
Print @ datefirst

-- Time-consuming accumulation of CPU Command Execution
Print @ cpu_busy

-- Obtain the value of the recently added ID column
Create Table TT
(
A int identity (3, 10 ),
B INT
)
Insert into TT (B) values (1)
Print @ identity
Select * from TT

-- Local variable
Local variables are defined by users and can only be called and accessed in the same batch.

Declare @ intage tinyint
Set @ intage = 12
Print @ intage

Declare @ strname varchar (12)
Select @ strname = 'state'
Print @ strname
Select au_lname, @ strname from authors

(3) Logical Control

-- If condition judgment
Declare @ I int
Set @ I = 12
If (@ I> 10)
Begin --{
Print 'dadadada! '
Print 'dadadada! '
End --}
Else
Begin
Print 'xiaoxiao! '
Print 'xiaoxiao! '
End

-- While loop control
Declare @ I int;
Set @ I = 12;
Print @ I
Return;
While (@ I <18)
Begin
Print @ I;
Set @ I = @ I + 1;
If @ I <17
Continue;
If @ I> 15
Break;
End;

-- Case Branch judgment
Select au_lname, state, 'ut' from authors where State = 'ut'
Select au_lname, state, 'Mississippi 'from authors where State = 'mi'
Select au_lname, state, 'ky' from authors where State = 'K'

Select au_lname, state,
Case state
When 'ut' then 'ut'
When 'mi' then 'Mississippi'
When 'ks 'then 'ky'
When 'CA' then 'galilia'
Else state
End
From authors

(4.1) system functions

-- Obtains the ASC code of the first character from the left of the specified string.
Print ASCII ('abcdef ')
-- Obtain the corresponding characters based on the given ASC code
Print char (65)
-- Get the length of a given string
Print Len ('abcdef ')
-- Case-sensitive Conversion
Print lower ('abcdef ')
Print upper ('abcdef ')
-- Remove space
Print ltrim ('abcd DFD df ')
Print rtrim ('abcd DFD df ')
-- Returns the absolute value.
Print ABS (-12)
-- Power
-- 2nd power of 3
Print power (3, 2)
Print power (3, 3)
-- Random Number
-- Random number between 0 and 1000
Print rand () * 1000
-- Obtain the circumference Rate
Print Pi ()

-- Obtain the system time
Print getdate ()

-- Get the time three days ago
Print dateadd (day,-3, getdate ())
-- Get the time in 3 days
Print dateadd (day, 3, getdate ())
-- Get the time from 3 years ago
Print dateadd (year,-3, getdate ())
-- Get the time after 3 years
Print dateadd (year, 3, getdate ())

-- Get the time after January 1, March
Print dateadd (month, 3, getdate ())
-- Get the time after 9 hours
Print dateadd (hour, 9, getdate ())
-- Get the time after 9 minutes
Print dateadd (minute, 9, getdate ())

-- Obtain the number of years separated by a specified time
Print datediff (year, '2017-01-01 ', '2017-01-01 ')
-- Get the number of months between the specified time
Print datediff (month, '2017-01-01 ', '2017-01-01 ')
-- Obtains the number of days between specified time intervals.
Print datediff (day, '2017-01-01 ', '2017-01-01 ')

-- String Merging
Print 'abc' + 'def'

Print 'abcder'

Print 'abc' + '123'
Print 'abc' + 456

-- Type conversion
Print 'abc' + convert (varchar (10), 456)

Select title_id, type, price from titles
-- The string connection must be of the same type (the following statements may fail to be executed)
-- Type conversion
Select title_id + Type + price from titles
-- Correct
Select title_id + Type + convert (varchar (10), price) from titles

Print '000000' + convert (varchar (3), 123)
Print '000000' + '000000'

Print convert (varchar (12), '2017-09-01 ', 2005)

-- Get a specific part of the specified time
Print year (getdate ())
Print month (getdate ())
Print Day (getdate ())

-- Get a specific part of the specified time
Print datepart (year, getdate ())
Print datepart (month, getdate ())
Print datepart (day, getdate ())
Print datepart (HH, getdate ())
Print datepart (MI, getdate ())
Print datepart (SS, getdate ())
Print datepart (MS, getdate ())

-- Obtain the specified interval
-- Returns the number of date and time boundaries across two specified dates.
Print datediff (year, '2017-01-01 ', '2017-08-08 ')
Print datediff (month, '2017-01-01 ', '2017-08-08 ')
Print datediff (day, '2017-01-01 ', '2017-08-08 ')
Print datediff (hour, '2017-01-01 ', '2017-08-08 ')
Print datediff (MI, '2017-01-01 ', '2017-08-08 ')
Print datediff (SS, '2017-01-01 ', '2017-08-08 ')

-- Returns a new datetime value based on a specified date.
Print dateadd (year, 5, getdate ())
Print dateadd (month, 5, getdate ())
Print dateadd (Day, 5, getdate ())
Print dateadd (hour, 5, getdate ())
Print dateadd (MI, 5, getdate ())
Print dateadd (SS, 5, getdate ())

-- Others
Print host_id ()
Print host_name ()
Print db_id ('pubs ')
Print db_name (5)

-- Use system functions as default Constraints
Drop table ttt

Create Table ttt
(
Stu_name varchar (12 ),
Stu_birthday datetime default (getdate ())
)

Alter table ttt
Add constraint df_ttt_stu_birthday default (getdate () for stu_birthday

Insert into TTT values ('aniu ', '2017-04-01 ')
Insert into TTT values ('aniu ', getdate ())

Insert into TTT values ('azhu ', default)

Sp_help ttt

Select * From ttt

 

(4.2) User-Defined Functions

Select title_id
From titles
Where type = 'business'

Select stuff (title_id, 1, 3, 'Abb '), Type
From titles
Where type = 'business'

Select count (title_id) from titles where type = 'business'
Select title_id from titles where type = 'business'

Select *, count (DBO. titleauthor. title_id)
From DBO. Authors inner join
DBO. titleauthor on DBO. Authors. au_id = DBO. titleauthor. au_id

Select au_id, count (title_id)
From titleauthor
Group by au_id

Select DBO. Authors. au_id, count (DBO. titleauthor. title_id) as 'number of works'
From DBO. Authors left Outer Join
DBO. titleauthor on DBO. Authors. au_id = DBO. titleauthor. au_id
Group by DBO. Authors. au_id
Order by 'number of works'

-- User-Defined Function Introduction

-- Subquery
-- Count the number of works of each author
-- Input the author number in the parent query to the subquery.
-- Use the aggregate function count as the query condition to count the number of entries
Select au_lname,
(Select count (title_id)
From titleauthor as Ta
Where ta. au_id = A. au_id
) As titlecount
From authors as
Order by titlecount

-- Whether a function can be defined
-- Use the author number as a parameter to count the number of his/her works and return it
Select au_id, au_lname, DBO. gettitlecountbyauid (au_id) as titlecount
From authors
Order by titlecount

Create proc pro_caltitlecount
As
Select au_id, au_lname, DBO. gettitlecountbyauid (au_id) as titlecount
From authors
Order by titlecount
Go

Execute pro_caltitlecount

-- Function Definition Format in VB
Function gettitlecountbyauid (au_id as string) as integer

.......

Gettitlecountbyauid =?
End Function

Dim strname string
Declare @ au_id varchar (12)

-- Obtain the number of corresponding works based on the specified author number.
Create Function gettitlecountbyauid (@ au_id varchar (12 ))
Returns int
Begin
Return (select count (title_id)
From titleauthor
Where au_id = @ au_id)
End

 

Select * from sales

Select * from sales where title_id = 'bu1032'

Select sum (qty) from sales where title_id = 'bu1032'

Select title_id, sum (qty) from sales
Group by title_id

Select title_id, title, DBO. gettotalsalebytitleid (title_id) as totalsales
From titles
Order by totalsales

Create Function gettotalsalebytitleid (@ TID varchar (24 ))
Returns int
Begin
Return (select sum (qty) from sales where title_id = @ tid)
End

Select top 10 title_id, title, DBO. gettotalsalebytitleid (title_id) as totalsales
From titles
Order by totalsales DESC

 

Create Function getpaixu (@ ID varchar (20 ))
Returns int
Begin
Return (select count (totalsales)
From titles
Where toalsales> (
Select totalsales
From titles
Where title_id = @ ID ))
End

Select DBO. getpaixu ('pc1035') from titles

Select count (title_id) + 1
From titles
Where DBO. gettotalsalebytitleid (title_id)> DBO. gettotalsalebytitleid ('pc1035 ')

Drop function getrankbytitleid

Alter function getrankbytitleid (@ TID varchar (24 ))
Returns int
Begin
Return (select count (title_id) + 1
From titles
Where DBO. gettotalsalebytitleid (title_id)> DBO. gettotalsalebytitleid (@ tid ))
End

Select title_id, title,
DBO. gettotalsalebytitleid (title_id) as totalsales,
DBO. getrankbytitleid (title_id) as totalrank
From titles
Order by totalsales DESC

Sp_help titles
Sp_helptext getrankbytitleid
Sp_helptext
Sp_helptext xp_cmdshell

 

Select * from [Order Details]
Select * from [Order Details] Where productid = 23
Select sum (Quantity) from [Order Details] Where productid = 23

--
Create Function gettotalsalebypid (@ PID varchar (12 ))
Returns int
Begin
Return (select sum (Quantity) from [Order Details] Where productid = @ PID)
End

Select * from products

Select productid, productname, DBO. gettotalsalebypid (productid)
From Products

 

Select P. pub_name, T .*
From titles as t join publishers as P on T. pub_id = P. pub_id
Where p. pub_name = 'hello'

Select * from publishers

Select P. pub_name, T .*
From titles as t join publishers as P on T. pub_id = P. pub_id
Where p. pub_name = 'algodata infosystem'

Alter procedure gettitlesbypub (@ pubname varchar (36 ))
With Encryption
As
Select P. pub_name, T .*
From titles as t join publishers as P on T. pub_id = P. pub_id
Where p. pub_name = @ pubname
Go

Sp_helptext gettitlesbypub

Execute gettitlesbypub 'hello'
Execute gettitlesbypub 'algodata infosystem'

Alter procedure testnull
As
Print 'Hello world! '
Go

Execute testnull

Alter function testnullfun ()
Returns int
With Encryption
Begin
Return (1)
End

Sp_helptext testnullfun

(5) Advanced Query

(6) Stored Procedure

Xp_cmdshell 'dir *.*'

Xp_mongoshell 'net start iisadmin'

Use northwind
Go

Create Function largeordershippers (@ freightparm money)
Returns @ ordershippertab table
(
Shipperid int,
Shippername nvarchar (80 ),
Orderid int,
Shippeddate datetime,
Freight money
)
As
Begin
Insert @ ordershippertab
Select S. shipperid, S. companyName,
O. orderid, O. shippeddate, O. Freight
From shippers as s inner join orders as O
On S. shipperid = O. shipvia
Where o. Freight> @ freightparm
Return
End

Select * From largeordershippers ($500)