A. 使用 SELECT 检索行和列 下例显示三个代码示例。第一个代码示例从 pubs 数据库内的 authors 表中返回所有行(没有指定 WHERE 子句)和所有列(使用 *)。 USE pubs SELECT * FROM authors ORDER BY au_lname ASC, au_fname ASC B. 将 DISTINCT 与 SELECT 一起使用 下例使用 DISTINCT 防止检索重复的作者 ID 号: USE pubs SELECT DISTINCT au_id FROM authors ORDER BY au_id c. 使用 SELECT INTO 创建表 第一个示例在tempdb 中创建一个名为 #coffeetabletitles 的临时表。为使用该表,始终用下面显示的精确名称(包括井号 (#))引用它。 USE pubs DROP TABLE #coffeetabletitles GO SET NOCOUNT ON SELECT * INTO #coffeetabletitles FROM titles WHERE price < $20 SET NOCOUNT OFF SELECT name FROM tempdb..sysobjects WHERE name LIKE '#c%' d. 使用 GROUP BY 下例在数据库内查找各出版商的本年度截止到现在的销售总额。USE pubs SELECT pub_id, SUM(ytd_sales) AS total FROM titles GROUP BY pub_id ORDER BY pub_id c. 使用 UNION 查询提示 下例显示使用 MERGE UNION 查询提示。USE pubs SELECT * FROM authors a1 OPTION (MERGE UNION) SELECT * FROM authors a2H. 对计算列使用表达式 下例显示如何使用表达式 ((low + high)/2) 计算 myavg 计算列。CREATE TABLE mytable ( low int, high int, myavg AS (low + high)/2 )F. 完整的表定义 下例显示 pubs 数据库中所创建的三个表(jobs、employee 和 publishers)的完整表定义,其中包含所有的约束定义。/* ************************** jobs table ************************** */ CREATE TABLE jobs ( job_id smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, job_desc varchar(50) NOT NULL DEFAULT 'New Position - title not formalized yet', min_lvl tinyint NOT NULL CHECK (min_lvl >= 10), max_lvl tinyint NOT NULL CHECK (max_lvl <= 250) )/* ************************* employee table ************************* */ CREATE TABLE employee ( emp_id empid CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED CONSTRAINT CK_emp_id CHECK (emp_id LIKE '[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'), /* Each employee ID consists of three characters that represent the employee's initials, followed by a five digit number ranging from 10000 through 99999 and then the employee's gender (M or F). A (hyphen) - is acceptable for the middle initial. */ fname varchar(20) NOT NULL, minit char(1) NULL, lname varchar(30) NOT NULL, job_id smallint NOT NULL DEFAULT 1 /* Entry job_id for new hires. */ REFERENCES jobs(job_id), job_lvl tinyint DEFAULT 10, /* Entry job_lvl for new hires. */ pub_id char(4) NOT NULL DEFAULT ('9952') REFERENCES publishers(pub_id), /* By default, the Parent Company Publisher is the company to whom each employee reports. */ hire_date datetime NOT NULL DEFAULT (getdate()) /* By default, the current system date is entered. */ )/* ***************** publishers table ******************** */ CREATE TABLE publishers ( pub_id char(4) NOT NULL CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756') OR pub_id LIKE '99[0-9][0-9]'), pub_name varchar(40) NULL, city varchar(20) NULL, state char(2) NULL, country varchar(30) NULL DEFAULT('USA') )
下例显示三个代码示例。第一个代码示例从 pubs 数据库内的 authors 表中返回所有行(没有指定 WHERE 子句)和所有列(使用 *)。
USE pubs
SELECT *
FROM authors
ORDER BY au_lname ASC, au_fname ASC
B. 将 DISTINCT 与 SELECT 一起使用
下例使用 DISTINCT 防止检索重复的作者 ID 号:
USE pubs
SELECT DISTINCT au_id
FROM authors
ORDER BY au_id
c. 使用 SELECT INTO 创建表
第一个示例在tempdb 中创建一个名为 #coffeetabletitles 的临时表。为使用该表,始终用下面显示的精确名称(包括井号 (#))引用它。 USE pubs
DROP TABLE #coffeetabletitles
GO
SET NOCOUNT ON
SELECT * INTO #coffeetabletitles
FROM titles
WHERE price < $20
SET NOCOUNT OFF
SELECT name
FROM tempdb..sysobjects
WHERE name LIKE '#c%'
d. 使用 GROUP BY
下例在数据库内查找各出版商的本年度截止到现在的销售总额。USE pubs
SELECT pub_id, SUM(ytd_sales) AS total
FROM titles
GROUP BY pub_id
ORDER BY pub_id
c. 使用 UNION 查询提示
下例显示使用 MERGE UNION 查询提示。USE pubs
SELECT *
FROM authors a1
OPTION (MERGE UNION)
SELECT *
FROM authors a2H. 对计算列使用表达式
下例显示如何使用表达式 ((low + high)/2) 计算 myavg 计算列。CREATE TABLE mytable
(
low int,
high int,
myavg AS (low + high)/2
)F. 完整的表定义
下例显示 pubs 数据库中所创建的三个表(jobs、employee 和 publishers)的完整表定义,其中包含所有的约束定义。/* ************************** jobs table ************************** */
CREATE TABLE jobs
(
job_id smallint
IDENTITY(1,1)
PRIMARY KEY CLUSTERED,
job_desc varchar(50) NOT NULL
DEFAULT 'New Position - title not formalized yet',
min_lvl tinyint NOT NULL
CHECK (min_lvl >= 10),
max_lvl tinyint NOT NULL
CHECK (max_lvl <= 250)
)/* ************************* employee table ************************* */
CREATE TABLE employee
(
emp_id empid
CONSTRAINT PK_emp_id PRIMARY KEY NONCLUSTERED
CONSTRAINT CK_emp_id CHECK (emp_id LIKE
'[A-Z][A-Z][A-Z][1-9][0-9][0-9][0-9][0-9][FM]' or
emp_id LIKE '[A-Z]-[A-Z][1-9][0-9][0-9][0-9][0-9][FM]'),
/* Each employee ID consists of three characters that
represent the employee's initials, followed by a five
digit number ranging from 10000 through 99999 and then the
employee's gender (M or F). A (hyphen) - is acceptable
for the middle initial. */
fname varchar(20) NOT NULL,
minit char(1) NULL,
lname varchar(30) NOT NULL,
job_id smallint NOT NULL
DEFAULT 1
/* Entry job_id for new hires. */
REFERENCES jobs(job_id),
job_lvl tinyint
DEFAULT 10,
/* Entry job_lvl for new hires. */
pub_id char(4) NOT NULL
DEFAULT ('9952')
REFERENCES publishers(pub_id),
/* By default, the Parent Company Publisher is the company
to whom each employee reports. */
hire_date datetime NOT NULL
DEFAULT (getdate())
/* By default, the current system date is entered. */
)/* ***************** publishers table ******************** */
CREATE TABLE publishers
(
pub_id char(4) NOT NULL
CONSTRAINT UPKCL_pubind PRIMARY KEY CLUSTERED
CHECK (pub_id IN ('1389', '0736', '0877', '1622', '1756')
OR pub_id LIKE '99[0-9][0-9]'),
pub_name varchar(40) NULL,
city varchar(20) NULL,
state char(2) NULL,
country varchar(30) NULL
DEFAULT('USA')
)