T-SQL语句操作(一)

T-SQL是标准SQL语言的扩展,是SQL Server的核心,在SQL的的基础上添加了变量,运算符,函数和流程控制等新内容 SQL Server用于操作数据库的编程语言为Transaction-SQL，简称T-SQL。T-SQL与PL/SQL不同，并没有固定的程序结构。
T-SQL包括以下4个部分：

DDL：定义和管理数据库及其对象，例如create、alter和drop等。
DML：实现对数据库表各对象的操作，例如insert、update等。
DCL：数据控制语言，实现对数据库进行安全管理和权限管理等控制，例如grant、revoke、deny等。
附加的语言元素。T-SQL的附加语言元素，包括变量、运算符、函数、注释和流程控制语句等。

基本表的定义

语句格式：

CREATE TABLE  <表名>
	（<列名>  <数据类型>  | AS <表达式>  [<字段约束>]
		[, ……]                         
        [<记录约束>])

字段约束

[NOT NULL|NULL]：不允许或允许字段值为空。
[PRIMARY KEY [CLUSTERED|NON CLUSTERED]]：定义主键和聚簇
[REFERENCES <参照表> (<对应字段>)]：定义外部键和被参照表。
[DEFAULT <默认值>]：定义字段的默认值。
[CHECK(<条件表达式>)]：定义字段应满足的条件表达式。
[IDENTITY(<初始值>，<步长>)]：定义字段初始值和步长值。
[UNIQUE] ：定义不允许重复值。

记录约束格式： CONSTRAINT <约束名> <约束式> 约束式有以下几种

[PRIMARY KEY [CLUSTERED| NON CLUSTERED](〈列名组〉)]：定义主键和聚簇索引
[FOREIGN KEY(<外码>) REFERENCES <参照表> (<对应列>)]：定义外部键和被参照表
[CHECK(〈条件表达式〉)]：定义记录应满足的条件。
[UNIQUE(〈列名组〉)]：定义不允许重复值的字段组。

<列名> AS <表达式> 定义一个虚拟列，该列的值通过<表达式>计算而得，在表中不存放。例如：age AS YEAR(GETDATE())-YEAR(birthday)

定义数据表结构

利用CREATE TABLE语句定义数据表结构在stuinfo1数据库定义下列数据表的结构：

（1）定义学院表tab_institute

CREATE TABLE tab_institute(
instno char(4) NOT NULL PRIMARY KEY,
instname varchar(50) NULL,
address varchar(50) NULL)

（2）定义专业表tab_majoy

CREATE TABLE tab_majoy(
majoyno char(4) NOT NULL PRIMARY KEY,
majoyname varchar(50) NULL,
instno char(4) NULL REFERENCES tab_institute(instno))

（3）定义教师表tab_teacher

CREATE TABLE tab_teacher(
tno char(4) NOT NULL PRIMARY KEY,
tname varchar(50) NULL,
tsex char(2) NULL CHECK(tsex='男' or tsex='女'),
title varchar(10) NULL,
instno char(4) NULL REFERENCES tab_institute(instno))

（4）定义课程表tab_course

CREATE TABLE tab_course(
cno char(4) NOT NULL PRIMARY KEY,
cname varchar(50) NULL,
ctype varchar(10) NULL CHECK(ctype IN ('必修','限选','任选')),
credit tinyint NULL,
term tinyint NULL,
majoyno char(4) NULL REFERENCES tab_majoy(majoyno))

（5）定义学生表tab_student

CREATE TABLE tab_student(
sno char(4) NOT NULL PRIMARY KEY,
sname varchar(50) NULL,
ssex char(2) NULL CHECK(ssex='男' or ssex='女'),
birthday datetime NULL,
age AS YEAR(GETDATE())-YEAR(birthday),
class varchar(10) NULL,
grade tinyint NULL,
majoyno char(4) NULL REFERENCES tab_majoy(majoyno))

（6）定义选修（成绩）表tab_score

CREATE TABLE tab_score(
sno char(4) NOT NULL,
cno char(4) NOT NULL,
tno char(4) NOT NULL,
score smallint NULL,
CONSTRAINT PK_score_1 PRIMARY KEY(sno,cno,tno),
		CONSTRAINT FK_score_student FOREIGN KEY(sno) REFERENCES tab_student(sno),
CONSTRAINT FK_score_course FOREIGN KEY(cno) REFERENCES tab_course(cno),
CONSTRAINT FK_score_teacher FOREIGN KEY(tno) REFERENCES tab_teacher(tno))

基本表结构的修改

（1）修改字段的定义。 ALTER TABLE <表名> ALTER COLUMN <列名> <新类型> [NULL|NOT NULL] <约束定义> （2）增加字段和表约束规则。 ALTER TABLE <表名> ADD <列定义>|[<表约束定义>]… （3）删除字段或约束规则。 ALTER TABLE <表名> DROP [CONSTRAINT] <约束名>|COLUMN<列名> （4）使约束有效或无效。 ALTER TABLE <表名> CHECK|NOCHECK} CONSTRAINT {ALL|<约束名组>

修改数据表结构

（1）在tab_institue表中加一个“院长”字段（dean,varchar(10),NULL）

alter table tab_institute add DEAN VARCHAR(10) NULL
-- 删除院长字段
alter table tab_institute drop DEAN

（2）在tab_score表中对成绩字段score加一个CHECK约束（-5~~100），其中-1~~-5分别代表五级制成绩“优秀、良好、正等、及格、不及格”，0~100代表百分制成绩

alter table tab_score add check (score between -5 and 100)

（3）在tab_teacher表中对职称字段title加一个CHECK约束（助教，讲师，副教授，教授）

alter table tab_teacher add check (title='助教' or title='讲师' or title='副教授' or title='教授')

删除基本表

DROP TABLE <表名>

创建和管理索引

（1）创建索引 CREATE [UNIQUE] [CLUSTERED|NONCLUSTERED] INDEX <索引名> ON <表名> (<索引列组>) 例如：CREATE UNIQUE CLUSTERED INDEX index1 ON tab_student (sname) 就可以为表tab_student的sname列创建了一个唯一的聚簇索引。又如：CREATE INDEX index2 ON tab_student (majoyno, class) 就可以为表tab_student创建了一个专业和班级的符合索引。（2）删除索引 DROP INDEX <表名>.<索引名>

利用CREATE INDEX语句创建索引

（1）在tab_student表中创建一个“姓名”字段的非聚簇索引。

create INDEX index1 ON tab_student(sname)

（2）在tab_course表中创建一个“课程名”字段的唯一非聚簇索引。

create unique index index2 on tab_course(cname)

（3）在tab_course表中创建一个“学分”和“学期”字段的复合非聚簇索引。

CREATE INDEX idx_credit_semester ON tab_course (credit, term)

利用DROP INDEX 语句删除索引

drop index index2 on tab_course

数据插入语句

(1) 使用常量插入单个记录（元组）。格式为： INSERT INTO <表名>[(<列名1>[,<列名2>…)] VALUES (<常量1>[,<常量2>]…) 【例】插入一条学生记录。 INSERT INTO tab_student (sno,sname,ssex,birthday,class,grade ) VALUES (‘0101’，‘张三’，’男’，‘2000-1-1’，’1601’, 2016)

(2) 在表中插入子查询的结果集。格式为： INSERT INTO <表名>[(<列名1>[,<列名2>…)] <子查询> 【例】求每个学生的平均成绩，并将结果存入数据库中。 CREATE TABLE avg_score (sno CHAR(4)，avg_s SMALLINT) INSERT INTO avg_score SELECT sno，AVG (score) FROM tab_score GROUP BY sno

利用INSERT语句添加记录

（1）在tab_course表中添加2个课程记录，记录内容如下： C010，网络协议，任选，2，2，0201 C011，物联网，任选，2，2，0101

insert into tab_course values ('C010','网络协议','任选','2','2','0201'),('C011','物联网','任选','2','2','0101')

（2）在tab_score表中分别添加3个选修“网络协议”、“物联网”课程的成绩记录（学生可跨专业，任课教师分别为T005和T006，“网络协议”成绩为五级制，“物联网”成绩为百分制）

INSERT INTO tab_score (sno, cno, tno, score)
VALUES ('学生ID', '网络协议课程ID', 'T005', '-1');

INSERT INTO tab_score (sno, cno, tno, score)
VALUES ('学生ID', '物联网课程ID', 'T006', '百分制成绩');

数据修改语句

格式为： UPDATE <表名> SET <列名>=<表达式>[,<列名2>=<表达式2>][，…n][WHERE <条件>] 【例】将成绩表中全部学生的成绩加上10分。 UPDATE tab_score SET score=score+10 【例】将成绩表中的数据库课程的成绩加上10分。
UPDATE tab_score SET score= score+10 WHERE cno= (SELECT cno FROM tab_course WHERE cname= '数据库' )

利用UPDATE语句修改记录内容

（1）将tab_score表中 “网络协议” 成绩改成百分制，转换规则如下：优秀-95，良好-85，正等-75，及格-65，不及格-55 如：UPDATE tab_score SET score=95 where score= -1 AND cno=‘C010’

（2）将tab_score表中 “物联网” 成绩改成五级制，转换规则如下：优秀：100-90，良好：89-80，正等：79-70，及格：69-60，不及格：59-0 如：UPDATE tab_score SET score=-1 WHERE score BETWEEN 90 AND 100 AND cno=‘C011’

（3）将tab_score表中 “物联网” 成绩字段清空（值NULL）。

UPDATE tab_score SET score=NULL WHERE cno IN (select cno from tab_course WHERE cname='物联网')

数据删除语句

格式为： DELETE [FROM] <表名> [WHERE <条件>] 【例】将成绩表中的所有学生的成绩删除。
DELETE FROM tab_score 【例】将成绩表中的课程号为C001成绩删除。
DELETE FROM tab_score WHERE cno=‘C001’ 【例】将成绩表中的网络工程专业所有学生的成绩删除。
DELETE FROM tab_score WHERE sno IN (SELECT sno FROM tab_student WHERE majoyno=(SELECT majoyno FROM tab_majoy WHERE majoyname= ‘网络工程’ ))

利用DELETE语句删除记录

（1）将tab_score表中 “物联网” 成绩记录删除。

DELETE FROM tab_score WHERE cno IN (select cno from tab_course where cname='物联网')

数据查询语句

SELECT数据查询的语句格式：

	SELECT [<查询范围>] <查询列表> FROM <数据源>
		[WHERE <条件表达式>] [ORDER BY <排序列组>]

其中：

（1）<查询范围>::= [ALL | DISTINCT] [TOP n [PERCENT]]
（2）<查询列表>::= {* | <列名1> [[AS] <别名1>]} [,……n]
（3）<数据源>::= {\{<表名1>| <视图名1>} [[AS] <别名1>]} [,……n]
（4）<条件表达式>::=<关系表达式>|<逻辑表达式>
（5） <排序列组>::= {<列名> [ASC|DESC]} [, ……n]

单表查询——查询内容和查询条件都在同一个表中

1）查询tab_student表所有学生的记录

select * from tab_student

2）查询tab_student表中前4个学生的记录

SELECT TOP 4 * FROM tab_student;

3）查询tab_student表所有学生的学号和姓名

SELECT sname,sno FROM tab_student;

4）查询tab_student表专业号为“0101”的学生记录

select * from tab_student where majorno='0101';

5）查询tab_student表专业号为“0101”的学生记录，结果按年龄从小到大排序。

select * from tab_student where majorno='0101' order by grade ASC;

6）查询tab_student表专业号为“0101”的学生的学号，姓名和年龄，并将列名改成中文。

SELECT sno AS '学号', sname AS '姓名', age AS '年龄'
FROM tab_student
WHERE majorno = '0101';

7）查询tab_couse表中含有“数据”的课程记录。（查询条件中LIKE与通配符的用法）

select * from tab_course where cname like '%数据%';

百分号 %：用于匹配任意字符序列（包括空字符序列）。下划线 _：用于匹配任意单个字符。使用 LIKE 操作符结合通配符来进行模糊匹配

8）查询tab_student表专业号为“0101”、“0102”的学生记录（查询条件中IN的用法）

SELECT *
FROM tab_student
WHERE majorno IN ('0101', '0102');

IN 关键字用于指定一个值列表，以便在查询中匹配多个值

9）查询tab_score表中成绩为空的记录（查询条件中IS NULL的用法）

SELECT *
FROM tab_score
WHERE score IS NULL;

10）查询tab_score表中成绩为80-89的记录（查询条件中BETWEEN的用法）

SELECT *
FROM tab_score
WHERE score BETWEEN 80 AND 89;

连接查询——多表查询、多数据源查询

（1）通过WHERE子句表达连接条件连接条件的格式：[<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2> 例1 查询每个学生的信息及他们所选课程的信息。格式1：列名用<表名>限定

SELECT  tab_student.*, tab_score.* FROM tab_student, tab_score  WHERE tab_student.sno=tab_score.sno

格式2：列名用<表别名>限定

SELECT  a.*, b.* FROM tab_student AS  a, tab_score  AS b WHERE a.sno=b.sno

注意：有些版本只识别格式2，以下举例均采用格式2

例2 查询每个学生的学号、姓名、选修的课程名和成绩。 SELECT a.sno,sname,cname,score FROM tab_student AS a,tab_course AS b, tab_score AS c WHERE a.sno=c.sno AND b.cno=c.cno 例3 查询选修“数据结构” 且成绩90分以上（含）学生的学号、姓名、成绩。 SELECT a.sno,sname,score FROM tab_student a,tab_course b,tab_score c WHERE a.sno=c.sno AND b.cno=c.cno AND cname=‘数据结构’ AND score>=90 例4 查询选修教师“张三”授课的学生学号、姓名、成绩。 SELECT a.sno,sname,score FROM tab_student a,tab_teacher b,tab_score c WHERE a.sno=c.sno AND b.tno=c.tno AND tname=‘张三’

（2）通过FROM子句表示连接操作 FROM子句的语句格式：

     FROM <表名1> [INNER | {{LEFT | RIGHT | FULL} [OUTER]}]
          JOIN <表名2> ON [<表名1>.]<列名1> <比较运算符> [<表名2>.]<列名2>

其中 [INNER | {{LEFT | RIGHT | FULL} [OUTER]}]表示表1与表2的连接方式，默认值为INNER。
1）INNER（内连接）：查询结果中只有满足连接条件的记录。
2）LEFT [OUTER]（左连接）：查询结果中包含<表1>的所有记录，其中没有与<表2>匹配且又是<表2>字段的内容为NULL。
3）RIGHT [OUTER]（右连接）：查询结果中包含<表2>的所有记录，其中没有与<表1>匹配且又是<表1>字段的内容为NULL。
4）FULL [OUTER]（全连接）：查询结果中包含<表1> <表2>的所有记录，其中<表1> <表2>没有匹配且又是另一个表字段的内容为NULL。

例1 查询每个学生的信息及他们所选课程的信息。 SELECT a.*, b.* FROM tab_student AS a INNER JOIN tab_score AS b ON a.sno=b.sno 例2 查询每个学生的学号、姓名、选修的课程名和成绩。 SELECT a.sno, sname, cname, score FROM tab_student AS a INNER JOIN tab_score AS b ON a.sno = b.sno INNER JOIN tab_course AS c ON b.cno = c.cno 例3 查询选修“数据结构” 且成绩90分以上（含）学生的学号、姓名、成绩。 SELECT a.sno, sname, score FROM tab_student a JOIN tab_score b ON a.sno=b.sno JOIN tab_course c ON b.cno=c.cno AND cname=‘数据结构’ AND score>=90 例4 查询选修教师“张三”授课的学生学号、姓名、成绩。 SELECT a.sno, sname, score FROM tab_student a JOIN tab_score b ON a.sno=c.sno JOIN tab_teacher c ON b.tno=c.tno AND tname=‘张三’

连接查询的操作练习 1）查询全校的教师的工号、姓名、所在学院名

select a.tno,a.tname,b.instname from tab_teacher as a,tab_institute as b where a.instno=b.instno

2）查询授课教师的工号、姓名、讲授课程名

select a.tno,b.tname,a.cno,c.cname from tab_score as a,tab_teacher as b,tab_course as c where a.tno=b.tno and a.cno=c.cno

3）查询 “数据结构”课程的学生的学号、姓名、课程名、成绩、任课教师名

select a.sno,b.sname,c.cname,a.score,d.tname 
from tab_score as a,tab_student as b,tab_course as c,tab_teacher as d
where a.sno=b.sno and a.cno=c.cno and a.tno=d.tno and c.cname='数据结构'

4）查询“计算机学院”所有学生的学号、姓名、课程名、成绩、任课教师名

select s.sno,s.sname,c.cname,sc.score,t.tname
from tab_student s
join tab_score sc on s.sno=sc.sno
join tab_course c on sc.cno=c.cno
join tab_teacher t on sc.tno=t.tno
join tab_major m on s.majorno=m.majoyno
join tab_institute i on m.instno=i.instno
where i.instname='计算机学院'

5）查询所有授课的“教授”的工号、姓名和授课课程名

select sc.tno,t.tname,c.cname
from tab_score sc 
join tab_teacher t on sc.tno=t.tno
join tab_course c on sc.cno=c.cno
where t.title='教授'

嵌套查询——查询条件需要由另一个查询（子查询）结果确定

（1） <列名> <比较符> (SELECT <列名> FROM …) 的用法（子查询结果只有1条记录） 1）查询tab_student表中“网络工程”专业的学生的记录

SELECT * FROM tab_student WHERE majoyno=
  (SELECT majoyno FROM tab_majoy WHERE majoyname=‘网络工程’)

2）查询tab_score表中“C001”课程成绩低于平均分的学生记录

SELECT * FROM tab_score WHERE cno=‘C001’ AND score <
  (SELECT AVG(score) FROM tab_score WHERE cno=‘C001’)

（2） <列名> IN（SELECT <列名> FROM …）的用法（子查询结果有多条记录）如：查询tab_student表中计算机学院的学生的记录

SELECT * FROM tab_student WHERE majoyno IN
  (SELECT majoyno FROM tab_majoy WHERE instno=
           (SELECT instno FROM tab_institute WHERE instname=‘计算机学院’))

（3） <列名> <比较符>{ALL|ANY} (SELECT <列名> FROM …) 的用法（子查询结果有多条记录）例求其他专业中比网络工程某一学生年龄小的学生。

SELECT *  FROM tab_student WHERE birthday >ANY (SELECT birthday FROM tab_student WHERE majoyno=(SELECT majoyno FROM tab_majoy WHERE majoyname= ‘网络工程’)) AND majoyno != (SELECT majoyno FROM tab_majoy WHERE majoyname= ‘网络工程’)

嵌套查询的操作练习 1）查询“计算机学院”的教师记录

select * from tab_teacher where instno=(
select instno from tab_institute where instname='计算机学院')

2）查询“计算机学院”的课程记录

select * from tab_course where majoyno in(
select majoyno from tab_major where instno=(
select instno from tab_institute where instname='计算机学院'))

3）查询讲授“数据库”课程的教师记录

select * from tab_teacher where tno in (
select tno from tab_score where cno=(
select cno from tab_course where cname='数据库'))

4）查询选修“数据结构”课程的学生记录

select * from tab_student where sno in (
select sno from tab_score where cno=(
select cno from tab_course where cname='数据库'))

5）查询“C001”课程成绩第一名的学生的学号与姓名

select sno,sname from tab_student where sno=(
select TOP 1 sno from tab_score where cno='C001' order by score DESC)

这里和MySQL有点不同，T-SQL用的是 TOP 1来指定第一个，而不是limit 1

6）查询“C001”课程成绩高于“李四”的学生的学号与成绩

select sno,score from tab_score where cno='C001' and score > (
select score from tab_score where cno='C001' and sno=(
select sno from tab_student where sname='李四'))

7）查询所有授课的“教授”的工号、姓名和授课课程

select sc.tno,t.tname,c.cname
from tab_score sc
join tab_teacher t on t.tno = sc.tno
join tab_course c on c.cno = sc.cno