Learn a little bit every day...

I learned a little about the database after work today. Show it out.

 

// Create a table and insert a table.
*********************************
Create Table SC
(
Sno char (10) Not null,
CNO char (2) null,/* course number field */
Score int null/* score field */
)
Go
Insert into SC (SNO, CNO, score) values ('2013', 'c3', 92)
Go
*********************************
Select SnO, AVG (score) as average score from SC
Group by SnO
*********************************
Create Table avgscore
(SNO char (10 ),
Avgscore smallint)
Go
Insert into avgscore
Select SnO, AVG (score)
From SC
Group by SnO

Note that in the returned set fields, these fields must be included inGroupThe statement is followed by the Group. It must be included in the aggregate function.
*********************************
Update avgscore set avgscore = avgscore-2

Select * From avgscore

Update avgscore set avgscore = avgscore-2
Where avgscore. SnO IN (
Select SnO from SC
Where SC. CNO = 'c4 ')
*********************************
Create Table T1
(Column_1 int,
Column_2 varchar (30 ))
Go
Insert T1 (column_2, column_1, wuzang) values ('false', 1, 'tianx')/* insert operation */

Update T1 set wuzang = 'wu'
Where wuzang = '?? '

Alter table T1 add wuzang char (5) null/* Add column */

Drop table t1/* Delete table */

Delete from T1 -- SQL two annotation methods:
Where column_1 = 7/* Delete (multiple lines can be commented here )*/
Select * from T1 -- dsdds (a single line comment can be added here)

*********************************
-- Two variables
-- 1) assign values to local variables and output them.
Declare @ myvar char (20)
Select @ myvar = 'ssfdmsm'
Select @ myvar
Go
-- 2) global variables: they often start with @ and are defined and maintained by the system.
For example
Select getdate () as 'current period and time ',
@ Connections as 'number of logins attempted'
*********************************
-- String concatenation operator + example:
Select 'abc' + 'def'
*********************************
-- Four types of functions
-- 1) dataset function: a dataset function can be referenced as a table in a Transact-SQL statement.
Exec sp_addmediaserver local
Select * From openquery (local, 'select * From customer ')
-- 2) Aggregate functions: Except the count function, Aggregate functions ignore null values.
-- Aggregate functions are often used together with the Group by clause of select statements.
-- Calculate the average number of vacation hours and total number of sick hours used by the Vice President of adventure works cycles.
-- For All retrieved rows, each aggregate function generates a separate summary value.
-- Program The list is as follows.
Use adventureworks;
Go
Select AVG (vacationhours) as 'average vacation hours ',
Sum (sickleavehours) as 'total sick leave Hours'
From HumanResources. Employee
Where title like 'Vice President %'
-- Window function:
-- Transact-SQL provides the following ranking functions:
Rank; dense_rank; ntile; row_number.
-- Scalar function: