实验二、交互式SQL

实验二、交互式SQL

一、实验目的：

1. 熟练掌握表的创建、修改和删除方法； 2. 熟悉索引的创建和删除； 3. 熟悉视图的创建和删除；

4. 熟悉数据的各种更新（增、删改）操作；

5. 熟悉各种数据查询（单表查询、连接查询、嵌套查询和集合查询） 二、实验环境

SQL Server 三、实验学时

2学时

四、实验准备知识 1．表的定义 1）表的创建

CREATE TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name

( }

[ ] [ ,...n ] )

/*指定分区方案和存储表的文件组*/

/*指定存储text、ntext和image类型数据的文件组*/

/*指定表的约束*/

{

/*列的定义*/

| column_name AS computed_column_expression [PERSISTED [NOT NULL]]

/*定义计算列*/

[ ON { partition_scheme_name ( partition_column_name ) | filegroup | \"default\" } ] [ { TEXTIMAGE_ON { filegroup | \"default\" } ] [ ; ]

说明：

database_name是数据库名，schema_name是新表所属架构的名称，table_name是表名， 表的标识按照对象命名规则。如果省略数据库名则默认在当前数据库中创建表；如果省略架构名， 则默认是“dbo”。

::= column_name data_type

/*指定列名、类型*/

/*指定排序规则*/

[ COLLATE collation_name ] [ NULL | NOT NULL ] [

[ CONSTRAINT constraint_name ]

/*指定是否为空*/

[ DEFAULT constant_expression ] /*指定默认值*/

| [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] ]/*指定列为标识列*/ ]

[ ROWGUIDCOL ]

/*指定列为全局标识符列*/ /*指定列的约束*

[ [ ...n ] ]

2）表的修改

修改表结构可以使用“ALTER TABLE”语句。 语法格式：

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name { }

[ ALTER COLUMN column_name } ]

| [ WITH { CHECK | NOCHECK } ] ADD {

| column_name AS computed_column_expression [PERSISTED [NOT NULL]] |

/*添加列*/

/*修改已有列的属性*/

{ new_data_type [ ( precision [ , scale ] ) ] [ COLLATE < collation_name > ] [ NULL | NOT NULL ]

| {ADD | DROP } [ ROWGUIDCOL | PERSISTED ]

} [ ,...n ] | DROP {

[ CONSTRAINT ] constraint_name [ WITH ( [ ,...n ] ) ] | COLUMN column_name

/*删除列*/

} [ ,...n ]

| [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT

{ ALL | constraint_name [ ,...n ] }

| { ENABLE | DISABLE } TRIGGER

{ ALL | trigger_name [ ,...n ] }

| SWITCH [ PARTITION source_partition_number_expression ]

TO [ schema_name. ] target_table

[ PARTITION target_partition_number_expression ]

2. 数据操作 1）插入数据

插入记录使用INSERT语句。 语法格式：

[ WITH [ ,...n ] ]

INSERT [ TOP ( expression ) [ PERCENT ] ] [INTO] { }

{ [ (column_list)]

/*列列表*/

/*指定列值的子句*/

[

]

/*OUTPUT子句*/

table_name | view_name

/*表名*/ /*视图名*/

/*可以是OPENQUERY 或 OPENROWSET 函数*/

/*指定临时结果集，在SELECT语句中介绍*/

| rowset_function_limited

[WITH ([„n])] /*指定表提示，可省略*/

{ VALUES

({DEFAULT | NULL |expression} [,„n]) /*列值的构成形式*/

}

| derived_table | exectute_statement }

/*结果集*/

/*有效的EXECTUTE语句*/

| DEFAULT VALUES /*所有列均取默认值*/

2）删除数据

使用DELETE语句删除数据 语法格式：

[ WITH [ ,...n ] ] DELETE [ TOP ( expression ) [ PERCENT ] ] [FROM]

{ table_name }

[ FROM {}[,„n]] [

]

[ WHERE {

}]

/*使用优化程序*/

/*从table_source删除数据*/

/*OUTPUT子句*/

/*指定条件*/

/*从表中删除数据*/

/*从视图删除数据*/

/*可以是OPENQUERY或OPENROWSET 函数*/

| view_name

| rowset_function_limited

[WITH ( < table_hint_limited > [ ...n ] )] /*指定表提示，可省略*/

|{ [CURRENT OF { {[GLOBAL] cursor_name} | cursor_variable_name}]}

/*有关游标的说明，见第4章*/

[OPTION ([,„n])]

3）修改数据

在T-SQL中，UPDATE语句可以用来修改表中的数据行。 语法格式：

[ WITH [ ,...n ] ] UPDATE [ TOP ( expression ) [ PERCENT ] ]

{ table_name WITH ( < table_hint_limited > [ ...n ] )/*修改表数据*/ }

SET { }

[OPTION([,„n])]

/*使用优化程序*/

/*赋予新值*/

{ column_name = { expression | DEFAULT | NULL } /*为列重新指定值*/

| @variable = expression

/*指定变量的新值*/ /*指定列和变量的新值*/

| @variable = column = expression { [ FROM {}[,„n]] [ WHERE ] }

| [ WHERE CURRENT OF

/*有关游标的说明，见第4章*/

{ {[GLOBAL] cursor_name} | cursor_variable_name}]

/*指定条件*/

| view_name

/*修改视图数据*/

| rowset_function_limited

/*可以是OPENQUERY或OPENROWSET函数*/

}[,„n]

4）数据查询

/*指定临时命名的结果集*/

语法格式：

[ WITH ] SELECT [ ALL | DISTINCT ]

[ TOP expression [ PERCENT ] [ WITH TIES ] ]

/*指定要选择的列及其限定*/

/*INTO子句，指定结果存入新表*/ /*FROM子句，指定表或视图*/ /*WHERE子句，指定查询条件*/

[ INTO new_table ]

[ FROM table_source ]

[ WHERE search_condition ]

[ GROUP BY group_by_expression] /*GROUP BY子句，指定分组表达式*/

[ HAVING search_condition] /*HAVING子句，指定分组统计条件*/

[ ORDER BY order_expression [ ASC | DESC ] ] /*ORDER子句，指定排序表达式和顺序*/

通过SELECT语句的项组成结果表的列。

3. 视图 1）视图的创建 语法格式：

CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] [ WITH [ ,...n ] ] AS select_statement [ ; ] [ WITH CHECK OPTION ]

其中：

::= { [ ENCRYPTION ] [ SCHEMABINDING ] [ VIEW_METADATA ] }

一般情况下，如果视图为下列格式，则称其为分区视图：

CREATE VIEW view_name AS

SELECT FROM T1 UNION ALL

SELECT FROM T2 UNION ALL ...

SELECT FROM Tn

2）视图的修改

语法格式：

ALTER VIEW [ schema_name . ] view_name [ ( column [ ,...n ] ) ]

[ WITH [ ,...n ] ] AS select_statement [ ; ] [ WITH CHECK OPTION ]

其中，view_attribute、select_statement等参数与CREATE VIEW语句中含义相同。 3）视图的删除 DROP VIEW [ schema_name . ] view_name [ ...,n ] [ ; ]

其中view_name是视图名，使用DROP VIEW可删除一个或多个视图。 4. 索引 1）概述 （1）聚集索引

聚集索引将数据行的键值在表内排序并存储对应的数据记录，使得数据表物理顺序与索引顺序一致。SQL Server 2005是按B树（BTREE）方式组织聚集索引的，B树方式构建为包含了多个节点的一棵树。顶部的节点构成了索引的开始点，叫做根。每个节点中含有索引列的几个值，一个节点中的每个值又都指向另一个节点或者指向表中的一行，一个节点中的值必须是有序排列的。指向一行的一个节点叫做叶子页。叶子页本身也是相互连接的，一个叶子页有一个指针指向下一组。这样，表中的每

一行都会在索引中有一个对应值。查询的时候就可以根据索引值直接找到所在的行。

聚集索引中B树的叶节点存放数据页信息。聚集索引在索引的叶级保存数据。这意味着不论聚集索引里有表的哪个（或哪些）字段，这些字段都会按顺序被保存在表中。由于存在这种排序，所以每个表只会有一个聚集索引。

由于数据记录按聚集索引键的次序存储，因此聚集索引对查找记录很有效。 （2）非聚集索引

非聚集索引完全独立于数据行的结构。SQL Server 2005也是按B树方式组织非聚集索引的，与聚集索引不同之处在于：非聚集索引B树的叶节点不存放数据页信息，而是存放非聚集索引的键值，并且每个键值项都有指针指向包含该键值的数据行。

在非聚集索引内，从索引行指向数据行的指针称为行定位器。行定位器的结构取决于数据页的存储方式是堆集还是聚集。对于堆集，行定位器是指向行的指针。对于有聚集索引的表，行定位器是聚集索引键。只有在表上创建聚集索引时，表内的行才按特定顺序存储，这些行按聚集索引键顺序存储。如果一个表只有非聚集索引，它的数据行将按无序的堆集方式存储。

一个表中最多只能有一个聚集索引，但可有一个或多个非聚集索引。当在 SQL Server 2005上创建索引时，可指定是按升序还是降序存储键。

如果在一个表中既要创建聚集索引，又要创建非聚集索引时，应先创建聚集索引，然后再创建非聚集索引，因为创建聚集索引时将改变数据记录的物理存放顺序。 2）定义 使用CREATE INDEX命令可以为表创建索引。 语法格式：

CREATE [ UNIQUE ]

/*指定索引是否唯一*/

/*索引的组织方式*/

/*索引名称*/

[ CLUSTERED | NONCLUSTERED ]

INDEX index_name

ON {[ database_name. [ schema_name ] . | schema_name. ] table_or_view_name}

( column [ ASC | DESC ] [ ,...n ] )

/*索引定义的依据*/

/*索引选项*/

/*指定索引文件所在的文件组*/

[ INCLUDE ( column_name [ ,...n ] ) ]

[ WITH ( [ ,...n ] ) ] [ ON { partition_scheme_name ( column_name ) ] [ ; ] 其中：

::= {

PAD_INDEX = { ON | OFF } | FILLFACTOR = fillfactor | SORT_IN_TEMPDB = { ON | OFF } | IGNORE_DUP_KEY = { ON | OFF } | STATISTICS_NORECOMPUTE = { ON | OFF } | DROP_EXISTING = { ON | OFF } | ONLINE = { ON | OFF }

| ALLOW_ROW_LOCKS = { ON | OFF } | ALLOW_PAGE_LOCKS = { ON | OFF } | MAXDOP = max_degree_of_parallelism }

| filegroup_name | default }

/*指定分区方案*/

五、实验内容：

（一）数据定义： 1.创建和打开数据库

create database stu go use stu go

2.创建数据库表 1）创建学生表S create table s( go

2）创建课程表C create table c( go

3）创建学生选课表SC create table sc( go

sno char(10) not null, cno char(5) not null, grade tinyint,

primary key(sno,cno),

foreign key(sno) references s(sno), foreign key(cno) references c(cno),

constraint q1 check(grade>=0 and grade<=100)) cno char(5) not null, cname varchar(20), cpno char(5), ccredit tinyint, primary key(cno))

sno char(10) not null primary key, sname varchar(20), ssex char(2), sage int,

sdept varchar(20))

3. 修改数据库表 1）增加属性列：为学生表S增加入学日期sdate （1）在增加入学日期sdate属性列前

（2）增加入学日期sdate属性列前

alter table s add sdate date go

注意：SQL Server2000只有日期时间数据类型：datetime，date是SQL Server2008的数据类型。 2）删除属性列sdate：

alter table s drop column sdate go

3）修改属性列：修改学生表S的属性列sage，将数据类型修改为tinyint。 （1）修改前

（2）执行修改命令：alter table s alter column sage tinyint

4）删除学生选课表属性列的完整性约束q1 （1）删除前：

（2）执行删除约束命令：

alter table sc drop q1 go

（二）数据操纵 1. 录入数据

1）录入“计科教育”的学生信息

说明：若是SQL SERVER2005及以前的版本，需要逐行输入。如：

insert into dbo.s(sno,sname,ssex,sage,sdept) values('1150310001','徐涛','女',21,'计科教育') insert into dbo.s(sno,sname,ssex,sage,sdept) values ('1150310002','马慧晶','女',22,'计科教育') 若是SQL SERVER2008及以后的版本，采用多行输入： insert into dbo.s(sno,sname,ssex,sage,sdept) values

('1150310001','徐涛','女',21,'计科教育'), ('1150310003','郭洁','女',21,'计科教育'), ('1150310005','李嫣','女',19,'计科教育'), ('1150310006','吉鑫','男',22,'计科教育'),

('1150310002','马慧晶','女',22,'计科教育'), ('1150310004','裴玉林','女',20,'计科教育'),

('1150310007','张翠侠','男',23,'计科教育'), ('1150310009','王灿灿','女',21,'计科教育'), ('1150310010','贾贝贝','女',22,'计科教育'), ('1150310011','储佳丽','女',21,'计科教育'), ('1150310012','魏兰','女',22,'计科教育'), ('1150310013','肖恒','男',23,'计科教育'), ('1150310014','徐尚','男',21,'计科教育'),

('1150310015','揭红梅','女',22,'计科教育'), ('1150310016','杨丽雯','女',21,'计科教育'), ('1150310017','耿健','男',20,'计科教育'),

('1150310019','崔亚超','女',21,'计科教育'), ('1150310020','刘雪丽','女',21,'计科教育'), ('1150310021','唐梦兰','女',20,'计科教育'), ('1150310022','刘林林','女',19,'计科教育'), ('1150310023','汪梦婷','女',21,'计科教育'), ('1150310024','梁渊','男',22,'计科教育'),

('1150310025','黎良月','女',21,'计科教育'), ('1150310026','向芫梅','女',20,'计科教育'), ('1150310028','罗静思','女',22,'计科教育'), ('1150310029','马丽','女',21,'计科教育'), ('1150310031','田梦','女',22,'计科教育'), ('1150310032','宋珮','女',21,'计科教育'), ('1150310034','杨杰','男',22,'计科教育'), ('1150310036','盛勐','男',20,'计科教育'),

('1150310030','胡金强','男',22,'计科教育'),

('1150310033','陈松松','男',21,'计科教育'), ('1150310035','伍志飞','男',23,'计科教育'), ('1150310037','张俊佳','男',22,'计科教育'), ('1150310038','郑容容','女',21,'计科教育'), ('1150310039','孙亮','男',22,'计科教育'),

('1150310040','杜晓文','女',21,'计科教育'),

('1150310041','胡小星','女',21,'计科教育'), ('1150310042','刘晶晶','女',21,'计科教育'), ('1150310043','李中俊','男',20,'计科教育'), ('1150310044','邱莉','女',19,'计科教育'),

('1150310046','武增强','男',22,'计科教育'), ('1150310047','张丽君','女',21,'计科教育'), ('1150310048','鲍安琪','女',21,'计科教育')

2）录入“计科网络”的学生信息

insert into dbo.s(sno,sname,ssex,sage,sdept) values

('1150312003','陈腾','男',21,'计科网络'), ('1150312004','于露','女',22,'计科网络'), ('1150312005','唐健','男',23,'计科网络'),

('1150312006','王能森','男',21,'计科网络'), ('1150312007','李文鹏','男',19,'计科网络'), ('1150312008','陈勉','男','20','计科网络'), ('1150312009','汪佳文','男',22,'计科网络'), ('1150312010','张佩瑶','女',21,'计科网络'), ('1150312011','喻超','男',21,'计科网络'), ('1150312012','周磊','男',20,'计科网络'),

('1150312013','张良诚','男',21,'计科网络'), ('1150312014','邹晓婉','女',21,'计科网络'), ('1150312015','李凌锋','男',22,'计科网络'), ('1150312016','杨海勇','男',23,'计科网络'), ('1150312017','彭柯霖','男','20','计科网络'), ('1150312018','陈冲','男',21,'计科网络'), ('1150312019','刘健','男',22,'计科网络'), ('1150312020','吴锦涛','男',21,'计科网络'), ('1150312021','徐鹏飞','男',22,'计科网络'), ('1150312022','程蓉','女',23,'计科网络'), ('1150312023','黄小龙','男',21,'计科网络'), ('1150312024','肖伟军','男',20,'计科网络'), ('1150312025','谌绪武','男',21,'计科网络'), ('1150312026','操旭明','男',20,'计科网络'), ('1150312027','徐燃','男',19,'计科网络'), ('1150312028','黄鹏程','男',22,'计科网络'), ('1150312029','王俊华','女',20,'计科网络'), ('1150312030','丁媛','女',21,'计科网络'), ('1150312031','汪盼','女',21,'计科网络'), ('1150312032','吴阳威','男',22,'计科网络'), ('1150312033','鲁远方','男',22,'计科网络'), ('1150312034','张超','男',23,'计科网络'), ('1150312035','张庚','男',21,'计科网络'),

('1150312036','郝红','女',20,'计科网络'), ('1150312037','郑宇航','男',21,'计科网络'), ('1150312038','李鹏','男',22,'计科网络'), ('1150312039','纪奥','男',21,'计科网络'), ('1150312040','张钟洲','男',20,'计科网络'), ('1150312041','黄婷婷','女',21,'计科网络'), ('1150312042','赵静','女',21,'计科网络'), ('1150312043','程璐璐','女',22,'计科网络'), ('1150312044','汪松','男',20,'计科网络'), ('1150312045','蒋善伟','男',19,'计科网络'), ('1150312046','李跃超','女',21,'计科网络'), ('1150330028','董波','男',22,'计科网络')

3）录入“数字媒体”的学生信息 insert into dbo.s(sno,sname,ssex,sage,sdept)

values

('1150320001','李宁','女',20,'数字媒体'), ('1150320004','余萍','女',22,'数字媒体'), ('1150320006','林欣','女',19,'数字媒体'), ('1150320007','赵莎','女',20,'数字媒体'),

('1150320002','董丹丹','女',21,'数字媒体'), ('1150320005','裴佳瑞','女',23,'数字媒体'),

('1150320008','孟建明','男',21,'数字媒体'), ('1150320009','游雅欣','女',22,'数字媒体'), ('1150320010','蔡雯雯','女',21,'数字媒体'), ('1150320011','张家政','男',20,'数字媒体'), ('1150320012','余丹纯','女',21,'数字媒体'), ('1150320014','郝伟','男',20,'数字媒体'), ('1150320016','陈月','女',21,'数字媒体'), ('1150320018','李慧','女',20,'数字媒体'), ('1150320019','白米雪','女',22,'数字媒体'), ('1150320020','刘佳','女',21,'数字媒体'), ('1150320021','吴雪','女',20,'数字媒体'), ('1150320022','蔡瑶','女',21,'数字媒体'), ('1150320023','卢晓晗','女',22,'数字媒体'), ('1150320024','秦杏杏','女',21,'数字媒体'), ('1150320026','黄容','女',20,'数字媒体'), ('1150320027','郭茜','女',20,'数字媒体'), ('1150320028','陈雅格','女',21,'数字媒体'), ('1150320029','史法阳','女',22,'数字媒体'), ('1150320030','罗昕瑜','女',21,'数字媒体'), ('1150320031','应晓芳','女',20,'数字媒体'),

('1150320015','王亚兰','女',20,'数字媒体'), ('1150320017','欧阳可维','女',21,'数字媒体'),

('1150320032','刘佳梦','女',21,'数字媒体'), ('1150320033','毕放','女',20,'数字媒体'), ('1150320034','王贝','女',20,'数字媒体'), ('1150320035','何威','男',22,'数字媒体'), ('1150320036','孙倩','女',21,'数字媒体'), ('1150320037','张笑程','女',21,'数字媒体')

4）录入课程表c的课程信息

录入课程信息

insert into dbo.c(cno,cname,cpno,ccredit) values

('00001','计算机导论','',2), ('00002','高级语言程序设计','',2), ('00003','离算数学','',3), ('00004','数据结构','00002',3), ('00005','c#','00002',2),

('00006','面向对象程序设计','00005',2), ('00007','数据库原理','00004',3), ('00008','操作系统','',3),

('00009','数据库新技术','00007',2), ('00010','嵌入式技术','',2),

('00011','算法设计与分析','00004',2), ('00012','nosql','',2)

5）通过程序为学生选课表输入学生选课信息

（1）创建学生选课表sc11 create table sc11( sno char(10), cno char(5), grade tinyint)

go

（2）录入学生选课信息 declare @sno char(10)

declare s_cursor cursor local for select open s_cursor

fetch s_cursor into @sno

declare @s_row tinyint,@n tinyint set @s_row=123 set @n=1

while @n<=@s_row begin

insert dbo.sc11(sno,cno)

select @sno, cno from stu_12.dbo.c fetch s_cursor into @sno set @n=@n+1 end

from dbo.ssno close s_cursor deallocate s_cursor

2. 修改数据和删除数据（略） （三）数据查询 1. 单表查询

（1）查询表中的若干列 1）查询部分属性列

select sno,sname,ssex from s go

go

或

select * from s go

2）查询全部属性列

select sno,sname,ssex,sage,sdept from s

go

3）查询经过计算的值：学生的出生年份。 在查询中使用了算术运算，系统函数

select sno,sname,YEAR(sysdatetime())-sage from s

4）查询中使用列别名和字符串

select sno,sname,'同学的出生年份是：',YEAR(GETDATE())-sage as 出生年份 from s

go

（2）查询表中的若干元组

1）消除取值重复的行 （a）没有消除重复的行 select sno from sc11 go

（b）消除重复的行

select distinct sno from sc11 go

2）查询满足条件的元组 （a）比较大小 select * from s where sage<22

（b）确定范围 select * from s

where sage between 20 and 23 go

（c）字符串匹配

select * from s where sname like '郝红' go

select * from s where sname like '李%' go

select * from s where sname like '李_' go

（3）排序

select * from s order by sname go

select * from s order by sname desc go

（4）分组查询

select sdept,COUNT(sno) from s group by sdept go

select sdept,avg(sage) from s group by sdept go

2. 连接查询

（1）为学生选课表生成成绩 1）创建学生选课表sc12 create table sc12( go

2）将学生选课表sc11中的选课信息插入到学生选课表sc12，并随机生成相应的选课成绩 declare @sno char(10),@cno char(5) declare @grdae int

declare sc11_cur cursor for select sno,cno from dbo.sc11 open sc11_cur

fetch next from sc11_cur into @sno,@cno set @grdae=cast( floor(rand()*50) as int)+50 declare @n int,@sc_row int set @n=1

set @sc_row=1476 while @n<=@sc_row begin

insert into dbo.sc12(sno,cno,grade) sno char(10), cno char(5), grade int)

values(@sno,@cno,@grdae)

fetch next from sc11_cur into @sno,@cno set @grdae=cast( floor(rand()*50) as int)+50 set @n=@n+1

end

close sc11_cur deallocate sc11_cur select @n

--3)—6)可以一起执行

--3）创建临时表，其结构与学生成绩表sc12一致 CREATE TABLE #TEMP12(

--4）从学生表中随机产生300行数据插入到临时表中 INSERT INTO #TEMP12

SELECT top 300 sno,cno,grade FROM sc12

ORDER BY NEWID()

--5）从学生成绩表sc12中删除这随机的300行数据 DELETE r FROM sc12 r

INNER JOIN #TEMP12 t

ON t.sno = r.sno and t.cno=r.cno

--6）删除临时表

TRUNCATE TABLE #TEMP12 DROP TABLE #TEMP12

（2）排序查询 select sno,grade from sc12

where cno='00003' order by grade desc

sno char(10), cno char(5), grade int)

（3）使用聚合函数查询

1）查询学生的选课人数

select COUNT(distinct sno) from sc12

2）查询学生的选课人次数

select COUNT( sno) from sc12

（4）自然连接：查询选了课程的学生情况和成绩情况 select s.*,cno,grade

from s inner join sc12 on s.sno=sc12.sno

（5）外连接查询

1）插入数据：产生没有选课的学生信息

insert into dbo.s(sno,sname,ssex,sage,sdept) values

('1050330001','刘炼','男',23,'信管') , ('1050330002','丁蕾','女',22,'信管') , ('1050330003','陈勇','男',24,'信管') , ('1050330004','余霞','女',21,'信管'), ('1050330007','付强','男',23,'信管'),

('1050330005','胡海燕','女',22,'信管'), ('1050330008','何楠楠','女',22,'信管'), ('1050330009','李晓飞','女',23,'信管'), ('1050330011','蔡盼盼','女',23,'信管'), ('1050330012','赵刚','男',24,'信管'),

('1050330013','涂四超','男',22,'信管'), ('1050330014','陈昌钦','男',21,'信管'), ('1050330015','王爱玲','女',21,'信管'), ('1050330016','王俊','女',21,'信管'), ('1050330017','刘晶','女',23,'信管'), ('1050330018','吴棚','男',23,'信管'), ('1050330019','胡国丰','男',22,'信管'), ('1050330021','叶梦竹','女',21,'信管'),

('1050330022','庄健','男',23,'信管'), ('1050330023','张志琪','女',22,'信管'), ('1050330024','邱珊珊','女',22,'信管'), ('1050330025','张凤','女',23,'信管'), ('1050330026','王杰','男',24,'信管'), ('1050330027','张婷','女',24,'信管'), ('1050330028','吴丹','女',23,'信管'), ('1050330029','弓兴宇','男',22,'信管'), ('1050330031','张美佳','女',23,'信管') （5）左外连接：查询学生信息及选课信息 select s.*,cno,grade

from s left outer join sc12 on s.sno=sc12.sno

在本例中，受参照完整性的制约，左外连接与全外连接等价，右外连接与自然连接等价。 （6）自身连接查询：求每门课的间接先行课 select c1.cno,c2.cpno from c as c1,c as c2 where c1.cpno=c2.cno

（7）复合连接条件查询：查询选修了“00002”课程，且至少有一门课程在85分以上的所有学生信息

select s.*,sc12.grade

from s,sc12 where s.sno=sc12.sno and sc12.cno='00002' and sc12.grade>85

3.嵌套查询

（1） 通过in谓词实现嵌套查询

1） 查询与‘徐涛’在同一个系的学生信息

select stu_12.dbo.s.* from dbo.s

where sdept in ( select sdept from dbo.s where sname='徐涛')

2） 查询选修了‘数据库原理’的学生学号和姓名 select sno,sname from s

where sno in ( select sno from sc12

where cno in ( select cno from c where cname='数据库原理'))

（2）通过比较运算符实现子查询：查询其他系中比‘计科教育’某一学生年龄小的学生姓名和年龄 1）比较运算符与any、all谓词实现子查询 select sname,sage,sdept from s

where sage< any (select sage from s where sdept='计科教育') and sdept<>'计科教育'

2) 聚集函数实现实现子查询 select sname,sage,sdept from s

where sage< (select max(sage) from s where sdept='计科教育') and sdept<>'计科教育'

3）两者执行时间的比较

（3）带有exists谓词的子查询

1）查询所有选修了‘00001’课程的学生信息 select *

from s where exists (select * from sc12 where cno='00001' and sno=s.sno)

2）查询所有选修了‘00001’课程的学生信息 select *

from s where not exists (select * from sc12 where cno='00001' and sno=s.sno)

显然，两者人数之和是学生人数总数 3） 查询选修了全部课程的学生信息 select * from s

where not exists (select * from c where not exists (

select * from sc12 where sno=s.sno and cno=c.cno))

4）查询至少选修了学号为‘1150310001’选修全部课程的学生号码 select distinct sno from sc12 scx

where not exists ( select * from sc12 scy where scy.sno='1150310001' and

not exists ( select * from sc12 scz where scz.sno=scx.sno and

scz.cno=scy.cno))