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 )
