sql入门讲座，包含PPT,SCRIPTS,C# DEMO

http://d.download.csdn.net/filedown/aHR0cDovL2RsMi5jc2RuLm5ldC9kb3duNC8yMDA3MDkyOC8yODE5NTI0NzUzOC5wcHQ=!254983

大家可以到我的资源里面去下载ppt，脚本和C#的代码

Section 1:  Introduce SQL background.

Section 2:  Introduce SQL foundation.

1) DML and Table:

Introduce Data Manipulation Language and how to manipulate table.

2) DDL and anther DB objects:

Introduce Data Definition Language and how to manipulate another DB objects.

 --To select the content of columns named "LastName" and "FirstName",-- from the database table called "Persons", use a SELECT statement like this:SELECT LastName,FirstName FROM Persons

Section 3:  Use Visual Studio to manipulate SQL.

 

--To select all columns from the "Persons" table,-- use a * symbol instead of column names, like this: SELECT * FROM Persons

--To select only DIFFERENT values from the column named "City"-- we use a SELECT DISTINCT statement like this:SELECT DISTINCT City FROM Persons

--Returns the number of selected rows

--To select only the persons living in the city "Sandnes",-- we add a WHERE clause to the SELECT statement: SELECT * FROM PersonsWHERE City='Sandnes'

--This is correct:SELECT * FROM Persons WHERE FirstName='Tove'--This is wrong:SELECT * FROM Persons WHERE FirstName=Tove

--This is correct:SELECT * FROM Persons WHERE Year>1965--This is wrong:SELECT * FROM Persons WHERE Year>'1965'

--The following SQL statement will return persons with first names that start with an 'O':SELECT * FROM PersonsWHERE FirstName LIKE 'O%'

--The following SQL statement will return persons with first names that end with an 'a':SELECT * FROM PersonsWHERE FirstName LIKE '%a'

--The following SQL statement will return persons with first names that contain the pattern 'la':SELECT * FROM PersonsWHERE FirstName LIKE '%la%' SELECT count(*) FROM Persons

--Insert a New RowINSERT Persons VALUES ('Hetland', 'Camilla', 'Hagabakka 24', 'Sandnes', 1982)

--Insert Data in Specified ColumnsINSERT Persons (LastName, Address)VALUES ('Rasmussen', 'Storgt 67')

--ResultSelect * from persons

--SyntaxUPDATE table_nameSET column_name = new_valueWHERE column_name = some_value

--We want to add a first name to the person with a last name of "Rasmussen":UPDATE Person SET FirstName = 'Nina'WHERE LastName = 'Rasmussen'

--We want to change the address and add the name of the city:UPDATE PersonSET Address = 'Stien 12', City = 'Stavanger'WHERE LastName = 'Rasmussen'

--SyntaxDELETE FROM table_nameWHERE column_name = some_value

--"Nina Rasmussen" is going to be deleted:DELETE FROM Person WHERE LastName = 'Rasmussen'

--We want to change the address and add the name of the city:UPDATE PersonSET Address = 'Stien 12', City = 'Stavanger'WHERE LastName = 'Rasmussen'

--Delete All RowsDELETE FROM table_name--or Truncate table table_name

--create viewCreate view vwPersonasselect LastName+' '+FirstName as Name,City,Yearfrom persons

--alter viewAlter view vwPersonasselect LastName+' '+FirstName as [Name],City,[Year],Addressfrom persons

--drop viewDrop view vwPerson

--ExampleSELECT * FROM vwPerson

--create stored procedureCreate PROCEDURE GetRecordByLastName (@Name varchar(50))asbegin select * from persons where LastName=@Nameend

--DropDrop PROCEDURE GetRecordByLastName

--ExampleGetRecordByLastName 'Svendson'

--Create TriggerCREATE TRIGGER reminder1ON Personsinstead of INSERT, UPDATE AS Begin RAISERROR ('Notify Customer Relations', 16, 10)End

--Drop TriggerDrop TRIGGER reminder1

--create functionCreate  FUNCTION GetAge(@Year int) RETURNS intASBEGIN RETURN(year(getdate())-@Year)END

--Exampleselect *,dbo.GetAge([year]) as Age from persons