SELECT语句

SELECT语句

SELECT * FROM table1 //最简单

条件类型

SELECT * FROM table1 WHERE cState = 'California'  //条件语句

SELECT cName, vAddress, FROM table1 WHERE age > 8 //条件语句int类型

SELECT Title, Ytd_Sales FROM Titles WHERE Ytd_Sales IS NULL //IS NULL

 

自定义列名类型

SELECT 'Publishers Identity' = Pub_Id, 'Publishers Name' = Pub_Name FROM table1

SELECT Pub_Id 'Publishers Identity', Pub_Name 'Publishers Name' FROM table1

 

模糊查找

SELECT Title, type FROM table1 WHERE type LIKE 'bus%' //模糊查找bus开头的

SELECT Title_Id, Price FROM table1 WHERE Title_Id LIKE 'p[sc]%' //以p开头第二个字为s或c

SELECT Title_Id, Price FROM table1 WHERE Title_Id LIKE '[p-z]%' //以p至z开头

SELECT Title_Id, Price FROM table1 WHERE Title_Id LIKE 'p[^s]%' //以p开头第二个字不为s

SELECT cNewspaperName, vContactPerson FROM Newspaper WHERE cNewspapername LIKE '% Texas Times%' 

//包含Texas Times

 

排序

SELECT cPositionCode, vDescription FROM Position ORDER BY vDescription ASC

//以vDescription排升序DESC为降序

SELECT TOP 3 * FROM ExternalCandidate WHERE dTestDate >= ' 3/1/2001 ' AND dTestDate <= '3/31/2001' ORDER BY score DESC //TOP3:前三个 在指定日期这间 以score排降序

 

计算

SELECT AVG(Price) 'Average Price' FROM Titles //计算Price的平均值,显示为Average Price

SELECT SUM(DISTINCT Advance) 'Sum'FROM Titles //计算不重复Advance的总值显示为Sum

SELECT MIN(Ytd_Sales) 'Min Sales' FROM Titles // Ytd_Sales最小值显示为Min Sales

SELECT COUNT(Price) 'price'FROM Titles //显示Price的个数

SELECT COUNT(DISTINCT Price) 'price'FROM Titles //显示Price种类的个数

 

分组

SELECT Type, MIN(Price) 'Minimum', MAX(Price) 'Maximum' FROM Titles WHERE Price > 10 GROUP BY Type

// GROUP BY Type:以Type分组,求到每组的各个值

SELECT Type, AVG(Price) FROM Titles WHERE Price > 10 GROUP BY Type HAVING AVG(Price) > 18

//以Type分组,显示Type组名,Price平均值, Price > 10,而且分组后, Price平均值大于18的行

SELECT Type, SUM(Advance) FROM Titles WHERE Type IN ('business', 'mod_cook', 'trad_cook') GROUP BY ALL Type

//显示Type在()里面,以Type分级Advance的总数

 

转换

SELECT 'Newspaper' = UPPER (cNewspaperName) FROM Newspaper  //显示为大写,lower为小写

SELECT cName, SUBSTRING(vAddress,1,10) 'Address', cPhone FROM RecruitmentAgencies//提取字符串

SELECT GETDATE()//显示当前时间

 

连表

SELECT cName FROM ContractRecruiter cr JOIN ExternalCandidate ec ON cr.cCity = ec.cCity WHERE vFirstName = 'Linda' AND vLastName = 'Lewis' //连表

 

嵌套语句

SELECT cName FROM ContractRecruiter WHERE cCity = ( SELECT cCity FROM ExternalCandidate WHERE vFirstName = 'Linda' AND vLastName = 'Lewis' )//SELECT语句关联

SELECT Au_Id FROM TitleAuthor WHERE Title_Id IN ( SELECT Title_Id FROM Sales ) //SELECT语句关联

SELECT Au_Id FROM TitleAuthor WHERE Title_Id NOT IN ( SELECT Title_Id FROM Sales ) //SELECT关联

SELECT Pub_Name FROM Publishers WHERE EXISTS ( SELECT * FROM UserMaster WHERE Login = 'geecy' AND Password = 'geecy' )

SELECT Title, Price FROM Titles WHERE Price > ( SELECT Avg(Price) FROM Titles )

 

建表

创建DATABASE

CREATE DATABASE Jessup

 

create table Users

(

UserID varchar(15) not null Primary Key, //主键

UserPassword varchar(30) not null, //varchar与char相比,char不满时会用空格填充,varchar则不会

Role varchar(25) not null constraint checkrole CHECK (Role in ('Saler', 'Administrator','SuperAdministrator'))

//CHECK约束

)

create table Products

(

ProID varchar(15) not null Primary Key,

ProName varchar(20) not null,

ProDesc varchar(50) not null,

ProType varchar(20) not null,

ProPrice money not null,

ProQuantity int not null

)

create table Projects

(

ProjectID varchar(15) not null Primary Key,

ProjectDesc varchar(50) not null,

ProID varchar(15) not null foreign key references Products(ProID), //外键约束Products表的ProID

ProjectDiscount float(20) not null,

PresentID varchar(15) not null,

PresentPrice money not null,

ProjectStartDate varchar(20) not null,

ProjectEndDate varchar(20) not null

)

 

ALTER TABLE Employee DROP CONSTRAINT pkEmployeeCode //删除约束

ALTER TABLE Employee

ADD CONSTRAINT pkEmployeeCode PRIMARY KEY CLUSTERED (cEmployeeCode) //添加约束

DROP TABLE Users //删除表

 

增删改

INSERT INTO AnnualSalary VALUES('000020',12000,2002) //增加

UPDATE PreferredCandidate SET cCountryCode = '002' where Name = ‘Bob’ //修改

DELETE FROM Sales WHERE QtySale = 20 //删除

 

存储过程

CREATE PROCEDURE prcDisplayToys  //创建存储过程 prcDisplayToys为过程名

AS

  SELECT vToyName,vToyDescription,mToyRate //存储过程主体

  FROM Toys

 

EXECUTE prcDisplayToys   //执行存储过程

 

CREATE PROCEDURE prcAddBrand  //创建带参数的存储过程

    @BrandId char(3),                //参数

    @BrandName char(20)

AS

  INSERT INTO ToyBrand

  VALUES(@BrandID,@BrandName)

EXEC prcAddBrand '009','Fun World'  //执行带参数的存储过程

CREATE PROCEDURE prcOrder(@OrderNo char(6), @CartId char(6)) //创建过程 带两个参数

AS

BEGIN

BEGIN TRANSACTION

INSERT INTO OrderDetail(cOrderNo,cToyId,siQty)

SELECT @OrderNo,cToyId,siQty FROM ShoppingCart WHERE cCartId = @CartId

 

UPDATE Toys

SET siToyQoh = siToyQoh - siQty

FROM Toys t

JOIN OrderDetail od ON t.cToyId = od.cToyId

WHERE cOrderNo = @OrderNo

COMMIT TRANSACTION

END

 

prcOrder '000001','000001'

触发

CREATE TRIGGER trgInsertRequisition    //创建触发

ON Requisition                        //表名

FOR INSERT                         //插入触发

AS

BEGIN

    DECLARE @VacancyReported int

    DECLARE @ActualVacancy int

    SELECT @ActualVacancy = iBudgetedStrength - iCurrentStrength

    FROM Position

    JOIN Inserted ON Position.cPositionCode = Inserted.cPositionCode

    SELECT @VacancyReported = Inserted.siNoOfVacancy

    FROM Inserted

    IF(@VacancyReported > @Actualvacancy)

    BEGIN

        PRINT 'The actual vacancies are less than the vacancies reported. Hence, cannot insert.'

              ROLLBACK TRANSACTION

    END    

END

 

CREATE TRIGGER trgDeleteTitle

ON Titles

FOR DELETE                       //删除触发

AS

BEGIN

DELETE TitleAuthor

FROM TitleAuthor ta

JOIN Deleted d ON ta.Title_Id = d.Title_Id

END

 

CREATE TRIGGER trgUpdateTitleAuthor

ON TitleAuthor

FOR UPDATE                      //更新触发

AS

BEGIN

IF (SELECT COUNT(*) FROM Titles t

   JOIN Inserted I ON t.Title_Id = i.Title_Id ) = 0

BEGIN

    PRINT ‘Invalid title ID’

    ROLLBACK TRANSACTION

END

IF (SELECT COUNT(*) FROM Author a

   JOIN Inserted I ON a.Au_Id = i.Au_Id ) = 0

BEGIN

    PRINT ‘Invalid Author ID’

    ROLLBACK TRANSACTION

END

END

 

DROP TRIGGER trgDeleteContractRecruiter //删除触发

ALTER TRIGGER trgDeleteCountry

ON Country

INSTEAD OF DELETE

AS

BEGIN

    PRINT 'do nothing.'

END

 

字段类型

字段类型	描述
bit	0或1的整型数字
int	从-2^31(-2,147,483,648)到2^31(2,147,483,647)的整型数字
smallint	从-2^15(-32,768)到2^15(32,767)的整型数字
tinyint	从0到255的整型数字
decimal	从-10^38到10^38-1的定精度与有效位数的数字
numeric	decimal的同义词
money	从-2^63(-922,337,203,685,477.5808)到2^63-1(922,337,203,685,477.5807)的货币数据，最小货币单位千分之十
smallmoney	从-214,748.3648到214,748.3647的货币数据，最小货币单位千分之十
float	从-1.79E+308到1.79E+308可变精度的数字
real	从-3.04E+38到3.04E+38可变精度的数字
datetime	从 1753 年 1 月 1 日 到9999年12日31的日期和时间数据，最小时间单位为百分之三秒或3.33毫秒
smalldatetime	从 1900 年 1 月 1 日 到 2079 年 6 月 6 日 的日期和时间数据，最小时间单位为分钟
timestamp	时间戳，一个数据库宽度的唯一数字
uniqueidentifier	全球唯一标识符GUID
char	定长非Unicode的字符型数据，最大长度为8000
varchar	变长非Unicode的字符型数据，最大长度为8000
text	变长非Unicode的字符型数据，最大长度为2^31-1( 2G )
nchar	定长Unicode的字符型数据，最大长度为8000
nvarchar	变长Unicode的字符型数据，最大长度为8000
ntext	变长Unicode的字符型数据，最大长度为2^31-1( 2G )
binary	定长二进制数据，最大长度为8000
varbinary	变长二进制数据，最大长度为8000
image	变长二进制数据，最大长度为2^31-1( 2G )