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 Persons
WHERE 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 Persons
WHERE FirstName LIKE 'O%'

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

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

SELECT count(*) FROM Persons

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

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

--Result
Select * from persons

--Syntax
UPDATE table_name
SET column_name = new_value
WHERE 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 Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'

--Syntax
DELETE FROM table_name
WHERE 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 Person
SET Address = 'Stien 12', City = 'Stavanger'
WHERE LastName = 'Rasmussen'

--Delete All Rows
DELETE FROM table_name
--or
Truncate table table_name

--create view
Create view vwPerson
as
select LastName+' '+FirstName as Name,City,Year
from persons

--alter view
Alter view vwPerson
as
select LastName+' '+FirstName as [Name],City,[Year],Address
from persons

--drop view
Drop view vwPerson

--Example
SELECT * FROM vwPerson

--create stored procedure
Create PROCEDURE GetRecordByLastName (@Name varchar(50))
as
begin
 select * from persons where LastName=@Name
end

--Drop
Drop PROCEDURE GetRecordByLastName

--Example
GetRecordByLastName 'Svendson'

--Create Trigger
CREATE TRIGGER reminder1
ON Persons
instead of INSERT, UPDATE
AS
Begin
 RAISERROR ('Notify Customer Relations', 16, 10)
End

--Drop Trigger
Drop TRIGGER reminder1

--create function
Create  FUNCTION GetAge(@Year int)
 RETURNS int
AS
BEGIN
 RETURN(year(getdate())-@Year)
END

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

发表于 @ 2007年09月28日 20:06:00|评论(loading...)|编辑