配置 SQL Server 数据库
EXEC sp_dboption 'pubs', 'read only', 'True'
此选项把数据库 "pubs" 设置为只读。
EXEC sp_dboption 'pubs' autoshrink true
此选项把符合条件的"pubs" 数据库文件设置为自动周期性收缩。
EXEC sp_dboption 'pubs' single user
此命令每次只允许一个用户访问数据库。
DBCC SHRINKDATABASE(PUBS, 10)
会减小"pubs"数据库中文件的大小,并允许有 10% 的可用空间。
使用 T-SQL 创建表
create table MyTable
(stdID int,stdName varchar(50))
自定义数据类型
EXEC sp_addtype City, 'nvarchar(15)', NULL
EXEC sp_addtype PostCode, 'nvarchar(6)', NULL
EXEC sp_addtype NCode, 'nvarchar(18)', NULL
删除自定义数据类型
EXEC sp_droptype city
使用 T-SQL 创建表
CREATE TABLE Categories
(CategoryID int IDENTITY(1,1),
CategoryName nvarchar(15) NOT NULL,
Description ntext NULL,
Picture image NULL)
实体完整性实现
主键约束
CREATE TABLE jobs(
Job_id smallint PRIMARY KEY,
job_desc varchar(50) NOT NULL)
唯一约束
CREATE TABLE jobs(
Job_id smallint UNIQUE)
标识列
CREATE TABLE jobs(
Job_id smallint IDENTIRY(2,1) PRIMARY KEY)
Uniqueidentifier数据类型和NEWID函数
CREATE TABLE Customer
(CustID uniqueidentifier NOT NULL DEFAULT NEWID(),
CustName char(30) NOT NULL)
INSERT Customer VALUES (NEWID(),'ASB')
引用完整性实现
ALTER TABLE dbo.Orders
ADD CONSTRAINT FK_Orders_Customers
FOREIGN KEY (CustomerID)
REFERENCES dbo.Customers(CustomerID)
更新表结构
ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
ALTER TABLE jobs ADD HIRE_DATE DATETIME
ALTER TABLE Doc_ED DROP COLUMN Column_B
ALTER TABLE Doc_ED WITH NOCHECK ADD CONSTRAINT Exd_Check CHECK (Column_a > 1)
在已有字段中增加标识约束时,必须先删除该字段,再新建字段。
alter table mytable drop column userid
alter table mytable add userid int identity(1,1)
删除表
DROP TABLE Airlines_Master
DEFAULT约束
USE Northwind
CREATE TABLE ABC
(ASD int Default 8, ASDE varchar(20) Default 'UNKNOWN')
USE Northwind
ALTER TABLE dbo.Customers ADD CONSTRAINT DF_contactname DEFAULT 'UNKNOWN' FOR ContactName
CHECK约束
CREATE TABLE ABCD
(ASD int CHECK(ASD <100) ,
ASDE varchar(80) Default 'UNKNOWN')
USE Northwind
ALTER TABLE Employees ADD CONSTRAINT CK_birthdate
CHECK (BirthDate > '01-01-1900' AND BirthDate < '01-01-2010')
PRIMARY KEY约束
CREATE TABLE AWC
(ASD int CHECK(ASD <100) PRIMARY KEY,
ASDE varchar(80) Default 'UNKNOWN')
USE Northwind
ALTER TABLE Customers ADD CONSTRAINT PK_Customers PRIMARY KEY (CustomerID)
UNIQUE约束
CREATE TABLE AAC
(ASD int CHECK(ASD <100) PRIMARY KEY,
ASDE int UNIQUE)
USE Northwind
ALTER TABLE Suppliers ADD CONSTRAINT U_CompanyName UNIQUE (CompanyName)
FOREIGN KEY约束
CREATE TABLE ACC
(ASD int CHECK(ASD <100) PRIMARY KEY,
ASDE int FOREIGN KEY REFERENCES AAC(ASD))
USE Northwind
ALTER TABLE Orders WITH NOCHECK ADD CONSTRAINT FK_Or_Cu
FOREIGN KEY (CustomerID)
REFERENCES Customers(CustomerID)
向表中插入数据
语法:INSERT [INTO] 表名 (字段列表)VALUES (值列表)
例:
1. 插入指定值的一条记录
INSERT INTO MyTable (PriKey, Description)
VALUES (123, 'A description of part 123.')
2. 从查询中获得数据(多条)
INSERT INTO MyTable (PriKey, Description)
SELECT ForeignKey, Description FROM SomeView
更新数据行
语法:UPDATE 表名 SET 字段名 = 新值,… WHERE 条件
例: UPDATE TITLES SET Price = Price + 0.25*Price Where Title_ID = 'TC777'
联接:根据表之间的逻辑关系搜索多表中的数据1. 内联接
SELECT … FROM 表1 INNER JOIN 表2 ON 表1.字段1=表2.字段1 WHERE 条件
例:UPDATE TITLES SET Price = Price + 10 From Titles INNER JOIN TitleAuthor
ON Titles.Title_IS = TitleAuthor.Title_ID WHERE Titles.Title = 'SuShi,AnyOne?'
2. 外联接左联接:左表与右表相联接的数据和左表的其他数据:
SELECT … FROM 表1 LEFT JOIN 表2 ON 表1.字段1=表2.字段1 WHERE 条件
例:UPDATE TITLES SET Price = Price + 10 From Titles LEFT OUTER JOIN TitleAuthor
ON Titles.Title_IS = TitleAuthor.Title_ID WHERE Titles.Title = 'SuShi,AnyOne?'
右联接:右表与左表相联接的数据和右表的其他数据:
SELECT … FROM 表1 RIGHT JOIN 表2 ON 表1.字段1=表2.字段1 WHERE 条件
例:UPDATE TITLES SET Price = Price + 10 From Titles RIGHT OUTER JOIN TitleAuthor
ON Titles.Title_IS = TitleAuthor.Title_ID WHERE Titles.Title = 'SuShi,AnyOne?'
全联接:右表与左表相联接的数据及右表和左表的其他数据:
SELECT … FROM 表1 FULL JOIN 表2 ON 表1.字段1=表2.字段1WHERE 条件
3. 自联接:表与其自身联接
SELECT … FROM 表1 as A JOIN 表1 as B ON A.字段1=B.字段2 WHERE 条件
删除数据
DELETE FROM 表 WHERE 条件 (速度慢)
TRUNCATE TABLE 表 (删除表中所有的记录,但不记日志,速度快)
查询数据
1. 数据库和所有者限定
SELECT * FROM Northwind.dbo.Shippers
2. 查询中使用常量和运算符
SELECT title_id +':'+ title + '->' + type As MyTitle FROM titles
3. 聚合函数
SELECT productid ,SUM(quantity) AS total_quantity FROM orderhist GROUP BY productid
显示所有type的值,并按type分组求平均值
SELECT type, AVG(price) FROM titles WHERE royalty = 10 GROUP BY ALL type
条件中带有聚合函数的,要用Having子句
SELECT productid, SUM(quantity) AS total_quantity FROM orderhist
GROUP BY productid HAVING SUM(quantity)>=30
4. 模糊查询
WHERE companyname LIKE '%Restaurant%'
WHERE fax IS NULL
WHERE country IN ('Japan', 'Italy')
WHERE unitprice BETWEEN 10 AND 20