6、说明:外连接查询(表名1:a 表名2:b)
select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
7、说明:在线视图查询(表名1:a )
select * from (SELECT a,b,c FROM a) T where t.a > 1;
9、说明:in 的使用方法
select * from table1 where a [not] in (‘值1’,’值2’,’值4’,’值6’)
12、说明:日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('minute',f开始时间,getdate())>5
13、说明:一条sql 语句搞定数据库分页
select top 10 b.* from (select top 20 主键字段,排序字段 from 表名 order by 排序字段 desc) a,表名 b where b.主键字段 = a.主键字段 order by a.排序字段
具体实现:
关于数据库分页:
declare @start int,@end int
@sql nvarchar(600)
set @sql=’select top’+str(@end-@start+1)+’+from T where rid not in(select top’+str(@str-1)+’Rid from T where Rid>-1)’
exec sp_executesql @sql
注意:在top后不能直接跟一个变量,所以在实际应用中只有这样的进行特殊的处理。Rid为一个标识列,如果top后还有具体的字段,这样做是非常有好处的。因为这样可以避免 top的字段如果是逻辑索引的,查询的结果后实际表中的不一致(逻辑索引中的数据有可能和数据表中的不一致,而查询时如果处在索引则首先查询索引)
14、说明:前10条记录
select top 10 * form table1 where 范围
17、说明:随机取出10条数据
select top 10 * from tablename order by newid()
alter table tablename
--添加一个自增列
add column_b int identity(1,1)
delete from tablename where column_b not in(
select max(column_b) from tablename group by column1,column2,...)
alter table tablename drop column column_b
20、说明:列出数据库里所有的表名
select name from sysobjects where type='U' // U代表用户
21、说明:列出表里的所有的列名
select name from syscolumns where id=object_id('TableName')
TRUNCATE TABLE table1
“where 1=1” 是表示选择全部 “where 1=2”全部不选,
如:
if @strWhere !=''
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where ' + @strWhere
end
else
begin
set @strSQL = 'select count(*) as Total from [' + @tblName + ']'
end
我们可以直接写成
错误!未找到目录项。
set @strSQL = 'select count(*) as Total from [' + @tblName + '] where 1=1 安定 '+ @strWhere 2、收缩数据库
--重建索引
DBCC REINDEX
DBCC INDEXDEFRAG
--收缩数据和日志
DBCC SHRINKDB
DBCC SHRINKFILE
3、压缩数据库
dbcc shrinkdatabase(dbname)
4、转移数据库给新用户以已存在用户权限
exec sp_change_users_login 'update_one','newname','oldname'
go
5、检查备份集
RESTORE VERIFYONLY from disk='E:\dvbbs.bak'
7、日志清除
SET NOCOUNT ON
DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE tablename -- 要操作的数据库名
SELECT @LogicalFileName = 'tablename_log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 1 -- 你想设定的日志文件的大小(M)
Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time has not expired
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name = @LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log') DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF
8、说明:更改某个表
exec sp_changeobjectowner 'tablename','dbo'
CREATE PROCEDURE dbo.User_ChangeObjectOwnerBatch
@OldOwner as NVARCHAR(128),
@NewOwner as NVARCHAR(128)
AS
DECLARE @Name as NVARCHAR(128)
DECLARE @Owner as NVARCHAR(128)
DECLARE @OwnerName as NVARCHAR(128)
DECLARE curObject CURSOR FOR
select 'Name' = name,
'Owner' = user_name(uid)
from sysobjects
where user_name(uid)=@OldOwner
order by name
OPEN curObject
FETCH NEXT FROM curObject INTO @Name, @Owner
WHILE(@@FETCH_STATUS=0)
BEGIN
if @Owner=@OldOwner
begin
set @OwnerName = @OldOwner + '.' + rtrim(@Name)
exec sp_changeobjectowner @OwnerName, @NewOwner
end
-- select @name,@NewOwner,@OldOwner
FETCH NEXT FROM curObject INTO @Name, @Owner
END
close curObject
deallocate curObject
GO
10、SQL SERVER中直接循环写入数据
declare @i int
set @i=1
while @i<30
begin
insert into test (userid) values(@i)
set @i=@i+1
end
案例:
有如下表,要求就裱中所有沒有及格的成績,在每次增長0.1的基礎上,使他們剛好及格:
Name score
Zhangshan 80
Lishi 59
Wangwu 50
Songquan 69
while((select min(score) from tb_table)<60)
begin
update tb_table set score =score*1.01
where score<60
if (select min(score) from tb_table)>60
break
else
continue
end
1.按姓氏笔画排序:
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as //从少到多
2.数据库加密:
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同
4.查看硬盘分区:
EXEC master..xp_fixeddrives
7.记录搜索:
开头到N条记录
Select Top N * From 表
-------------------------------
N到M条记录(要有主索引ID)
Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID Desc
----------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc
案例
例如1:一张表有一万多条记录,表的第一个字段 RecID 是自增长字段, 写一个SQL语句, 找出表的第31到第40个记录。
select top 10 recid from A where recid not in(select top 30 recid from A)
分析:如果这样写会产生某些问题,如果recid在表中存在逻辑索引。
select top 10 recid from A where……是从索引中查找,而后面的select top 30 recid from A则在数据表中查找,这样由于索引中的顺序有可能和数据表中的不一致,这样就导致查询到的不是本来的欲得到的数据。
解决方案
1, 用order by select top 30 recid from A order by ricid 如果该字段不是自增长,就会出现问题
2, 在那个子查询中也加条件:select top 30 recid from A where recid>-1
例2:查询表中的最后以条记录,并不知道这个表共有多少数据,以及表结构。
set @s = 'select top 1 * from T where pid not in (select top ' + str(@count-1) + ' pid from T)'
print @s exec sp_executesql @s
9:获取当前数据库中的所有用户表
select Name from sysobjects where xtype='u' and status>=0
10:获取某一个表的所有字段
select name from syscolumns where id=object_id('表名')
select name from syscolumns where id in (select id from sysobjects where type = 'u' and name = '表名')
两种方式的效果相同
11:查看与某一个表相关的视图、存储过程、函数
select a.* from sysobjects a, syscomments b where a.id = b.id and b.text like '%表名%'
12:查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype='P'
13:查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in(select sid from master..syslogins where name='sa')
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid <> 0x01
14:查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = '表名'
15:不同服务器数据库之间的数据操作
--创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
exec sp_addlinkedsrvlogin 'ITSV ', 'false ',null, '用户名 ', '密码 '
--查询示例
select * from ITSV.数据库名.dbo.表名
--导入示例
select * into 表 from ITSV.数据库名.dbo.表名
--以后不再使用时删除链接服务器
exec sp_dropserver 'ITSV ', 'droplogins '
--连接远程/局域网数据(openrowset/openquery/opendatasource)
--1、openrowset
--查询示例
select * from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--生成本地表
select * into 表 from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
--把本地表导入远程表
insert openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)
select *from 本地表
--更新本地表
update b
set b.列A=a.列A
from openrowset( 'SQLOLEDB ', 'sql服务器名 '; '用户名 '; '密码 ',数据库名.dbo.表名)as a inner join 本地表 b
on a.column1=b.column1
--openquery用法需要创建一个连接
--首先创建一个连接创建链接服务器
exec sp_addlinkedserver 'ITSV ', ' ', 'SQLOLEDB ', '远程服务器名或ip地址 '
--查询
select *
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
--把本地表导入远程表
insert openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ')
select * from 本地表
--更新本地表
update b
set b.列B=a.列B
FROM openquery(ITSV, 'SELECT * FROM 数据库.dbo.表名 ') as a
inner join 本地表 b on a.列A=b.列A
--3、opendatasource/openrowset
SELECT *
FROM opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ' ).test.dbo.roy_ta
--把本地表导入远程表
insert opendatasource( 'SQLOLEDB ', 'Data Source=ip/ServerName;User ID=登陆名;Password=密码 ').数据库.dbo.表名
select * from 本地表
SQL Server基本函数
SQL Server基本函数
1.字符串函数 长度与分析用
1,datalength(Char_expr) 返回字符串包含字符数,但不包含后面的空格
2,substring(expression,start,length) 取子串,字符串的下标是从“1”,start为起始位置,length为字符串长度,实际应用中以len(expression)取得其长度
3,right(char_expr,int_expr) 返回字符串右边第int_expr个字符,还用left于之相反
4,isnull( check_expression , replacement_value )如果check_expression為空,則返回replacement_value的值,不為空,就返回check_expression字符操作类
5,Sp_addtype 自定義數據類型
例如:EXEC sp_addtype birthday, datetime, 'NULL'
6,set nocount {on|off}
使返回的结果中不包含有关受 Transact-SQL 语句影响的行数的信息。如果存储过程中包含的一些语句并不返回许多实际的数据,则该设置由于大量减少了网络流量,因此可显著提高性能。SET NOCOUNT 设置是在执行或运行时设置,而不是在分析时设置。
SET NOCOUNT 为 ON 时,不返回计数(表示受 Transact-SQL 语句影响的行数)。 SET NOCOUNT 为 OFF 时,返回计数
常识
在SQL查询中:
from
后最多可以跟多少张表或视图:
256
在
SQL
语句中出现
Order by
,
查询时,
先排序,后取
在
SQL
中,一个字段的最大容量是
8000
,而对于
nvarchar(4000)
,
由于
nvarchar
是Unicode码。
SQLServer2000
同步复制技术实现步骤
一、 预备工作
1.
发布服务器,订阅服务器都创建一个同名的windows用户,并设置相同的密码,做为发布快照文件夹的有效访问用户
--
管理工具
--
计算机管理
--
用户和组
--
右键用户
--
新建用户
--
建立一个隶属于administrator组的登陆windows的用户(SynUser)
2.
在发布服务器上,新建一个共享目录,做为发布的快照文件的存放目录,操作:
我的电脑--D:\ 新建一个目录,名为: PUB
--
右键这个新建的目录
--
属性--共享
--
选择"共享该文件夹"
--
通过"权限"按纽来设置具体的用户权限,保证第一步中创建的用户(SynUser) 具有对该文件夹的所有权限
--
确定
3.
设置SQL代理(SQLSERVERAGENT)服务的启动用户(发布/订阅服务器均做此设置)
开始--程序--管理工具--服务
--
右键SQLSERVERAGENT
--
属性--登陆--选择"此账户"
--
输入或者选择第一步中创建的windows登录用户名(SynUser)
--"
密码"中输入该用户的密码
4.
设置SQL Server身份验证模式,解决连接时的权限问题(发布/订阅服务器均做此设置)
企业管理器
--
右键SQL实例--属性
--
安全性--身份验证
--
选择"SQL Server 和 Windows"
--
确定
5.
在发布服务器和订阅服务器上互相注册
企业管理器
--
右键SQL Server组
--
新建SQL Server注册...
--
下一步--可用的服务器中,输入你要注册的远程服务器名 --添加
--
下一步--连接使用,选择第二个"SQL Server身份验证"
--
下一步--输入用户名和密码(SynUser)
--
下一步--选择SQL Server组,也可以创建一个新组
--
下一步--完成
6.
对于只能用IP,不能用计算机名的,为其注册服务器别名(此步在实施中没用到)
(
在连接端配置,比如,在订阅服务器上配置的话,服务器名称中输入的是发布服务器的IP)
开始--程序--Microsoft SQL Server--客户端网络实用工具
--
别名--添加
--
网络库选择"tcp/ip"--服务器别名输入SQL服务器名
--
连接参数--服务器名称中输入SQL服务器ip地址
--
如果你修改了SQL的端口,取消选择"动态决定端口",并输入对应的端口号
二、 正式配置
1
、配置发布服务器
打开企业管理器,在发布服务器(B、C、D)上执行以下步骤:
(1)
从[工具]下拉菜单的[复制]子菜单中选择[配置发布、订阅服务器和分发]出现配置发布和分发向导
(2) [
下一步] 选择分发服务器 可以选择把发布服务器自己作为分发服务器或者其他sql的服务器(选择自己)
(3) [
下一步] 设置快照文件夹
采用默认\\servername\Pub
(4) [
下一步] 自定义配置
可以选择:是,让我设置分发数据库属性启用发布服务器或设置发布设置
否,使用下列默认设置(推荐)
(5) [
下一步] 设置分发数据库名称和位置 采用默认值
(6) [
下一步] 启用发布服务器 选择作为发布的服务器
(7) [下一步] 选择需要发布的数据库和发布类型
(8) [
下一步] 选择注册订阅服务器
(9) [
下一步] 完成配置
2
、创建出版物
发布服务器B、C、D上
(1)
从[工具]菜单的[复制]子菜单中选择[创建和管理发布]命令
(2)
选择要创建出版物的数据库,然后单击[创建发布]
(3)
在[创建发布向导]的提示对话框中单击[下一步]系统就会弹出一个对话框。对话框上的内容是复制的三个类型。我们现在选第一个也就是默认的快照发布(其他两个大家可以去看看帮助)
(4)
单击[下一步]系统要求指定可以订阅该发布的数据库服务器类型,
SQLSERVER
允许在不同的数据库如 orACLE或ACCESS之间进行数据复制。
但是在这里我们选择运行"SQL SERVER 2000"的数据库服务器
(5)
单击[下一步]系统就弹出一个定义文章的对话框也就是选择要出版的表
注意: 如果前面选择了事务发布 则再这一步中只能选择带有主键的表
(6)
选择发布名称和描述
(7)
自定义发布属性 向导提供的选择:
是 我将自定义数据筛选,启用匿名订阅和或其他自定义属性
否 根据指定方式创建发布 (建议采用自定义的方式)
(8)[
下一步] 选择筛选发布的方式
(9)[
下一步] 可以选择是否允许匿名订阅
1)
如果选择署名订阅,则需要在发布服务器上添加订阅服务器
方法: [工具]->[复制]->[配置发布、订阅服务器和分发的属性]->[订阅服务器] 中添加
否则在订阅服务器上请求订阅时会出现的提示:改发布不允许匿名订阅
如果仍然需要匿名订阅则用以下解决办法
[
企业管理器]->[复制]->[发布内容]->[属性]->[订阅选项] 选择允许匿名请求订阅
2)
如果选择匿名订阅,则配置订阅服务器时不会出现以上提示
(10)[
下一步] 设置快照 代理程序调度
(11)[
下一步] 完成配置
当完成出版物的创建后创建出版物的数据库也就变成了一个共享数据库
有数据
srv1.
库名..author有字段:id,name,phone,
srv2.
库名..author有字段:id,name,telphone,adress
要求:
srv1.
库名..author增加记录则srv1.库名..author记录增加
srv1.
库名..author的phone字段更新,则srv1.库名..author对应字段telphone更新
--*/
--
大致的处理步骤
--1.
在 srv1 上创建连接服务器,以便在 srv1 中操作 srv2,实现同步
exec sp_addlinkedserver 'srv2','','SQLOLEDB','srv2
的sql实例名或ip'
exec sp_addlinkedsrvlogin 'srv2','false',null,'
用户名','密码'
go
--2.
在 srv1 和 srv2 这两台电脑中,启动 msdtc(分布式事务处理服务),并且设置为自动启动
。我的电脑--控制面板--管理工具--服务--右键 Distributed Transaction Coordinator--属性--启动--并将启动类型设置为自动启动
go
--
然后创建一个作业定时调用上面的同步处理存储过程就行了
企业管理器
--
管理
--SQL Server
代理
--
右键作业
--
新建作业
--"
常规"项中输入作业名称
--"
步骤"项
--
新建
--"
步骤名"中输入步骤名
--"
类型"中选择"Transact-SQL 脚本(TSQL)"
--"
数据库"选择执行命令的数据库
--"
命令"中输入要执行的语句: exec p_process
--
确定
--"
调度"项
--
新建调度
--"
名称"中输入调度名称
--"
调度类型"中选择你的作业执行安排
--
如果选择"反复出现"
--
点"更改"来设置你的时间安排
然后将SQL Agent服务启动,并设置为自动启动,否则你的作业不会被执行
设置方法:
我的电脑--控制面板--管理工具--服务--右键 SQLSERVERAGENT--属性--启动类型--选择"自动启动"--确定.
--3.
实现同步处理的方法2,定时同步
--
在srv1中创建如下的同步处理存储过程
create proc p_process
as
--
更新修改过的数据
update b set name=i.name,telphone=i.telphone
from srv2.
库名.dbo.author b,author i
where b.id=i.id and
(b.name <> i.name or b.telphone <> i.telphone)
--
插入新增的数据
insert srv2.
库名.dbo.author(id,name,telphone)
select id,name,telphone from author i
where not exists(
select * from srv2.
库名.dbo.author where id=i.id)
--
删除已经删除的数据(如果需要的话)
delete b
from srv2.
库名.dbo.author b
where not exists(
select * from author where id=b.id)
go
create database cstp;
use cstp;
/*1:建立学生表*/
create table student (
学号 char(3) primary key,
姓名 char(4),
性别 char(2),
年龄 int,
班级 char(5)
);
insert into student values('108','曾华','男',19,'95033');
insert into student values('105','匡明','男',20,'95031');
insert into student values('107','王丽','女',20,'95033');
insert into student values('101','李军','男',19,'95033');
insert into student values('109','王芳','女',22,'95031');
insert into student values('103','陆君','男',20,'95031');
/*2:建立教师表*/
create table teacher(
教师号 char(3) primary key,
姓名 char(4),
性别 char(2),
年龄 int ,
级别 char(6),
专业 char(8)
);
insert into teacher values('804','李成','男',42,'副教授','计算机系');
insert into teacher values('856','张旭','男',35,'讲师','电子工程');
insert into teacher values('825','王萍','女',28,'助教','计算机系');
insert into teacher values('831','刘冰','女',25,'助教','电子工程');
/*3:建立课程表*/
create table course (
课程号 char(5) primary key,
课程名 char(10),
教师号 char(3),
foreign key(教师号) references teacher(教师号)
);
insert into course values('3-105','计算机导论','825');
insert into course values('3-245','操作系统','804');
insert into course values('6-166','数字电路','856');
insert into course values('9-888','高等数学','831');
/*4:建立选课表*/
create table sc(
学号 char(3),
课程号 char(5),
primary key(学号,课程号),
成绩 int,
foreign key(学号) references student(学号),
foreign key(课程号) references course(课程号)
);
insert into sc values('103','3-245',86);
insert into sc values('105','3-245',75);
insert into sc values('109','3-245',68);
insert into sc values('103','3-105',92);
insert into sc values('105','3-105',88);
insert into sc values('109','3-105',76);
insert into sc values('101','3-105',64);
insert into sc values('107','3-105',91);
insert into sc values('108','3-105',78);
insert into sc values('101','6-166',85);
insert into sc values('107','6-166',79);
insert into sc values('108','6-166',81);
/*5:所有表内容*/
select * from student;
select * from course;
select * from teacher;
select * from sc;
所有表信息如下:
student(学生)表
course(课程)表
teacher(教师)表
sc(选课)表
作业题:
1.查询选修课程'3-105'且成绩在60到80之间的所有记录。
注释:用于指定某个范围使用between and,也可以使用and连接符;
答案:
法1:select * from sc where 课程号='3-105' and 成绩 between 60 and 80;
法2:select * from sc where 课程号='3-105' and 成绩 > 60 and 成绩 < 80;
2.查询成绩为85、86或88的记录。
注释:用于制定某个集合使用 in 关键字,也可以使用 or 连接符;
答案:
法1:select * from sc where 成绩=85 or 成绩=86 or 成绩=88;
法2:select * from sc where 成绩 in(85,86,88);
3.查询'95031'班的学生人数。
注释:count(*)用于计算结果总数;
答案:
select count(班级) as 学生人数 from student where 班级='95031';
4.查询最低分大于70,且最高分小于90的学号列。
注释:having后面跟聚合函数:avg,min,max,count;having语句只能跟在:group by语句后面;
答案:
select 学号,min(成绩)as 最低分,max(成绩)as 最高分 from sc group by 学号 having min(成绩)>70 and max(成绩) < 90;
5.查询至少有5名学生选修并以3开头的课程的平均成绩。
注释:group by 语句置于where语句后面表示根据什么条件来分组;
答案:
select avg(成绩) as 平均成绩 from sc where 课程号 like '3%' group by 课程号 having count(课程号) >= 5;
6.查询平均分大于80分的学生的成绩表
注释:having后面跟聚合函数avg();
答案:
select * from sc group by 学号 having avg(成绩) > 80;
7.查询'95033'班每个学生所选课程的平均分。
注释:此题是根据 '95033'班学生的学号进行的分组,使用where语句限制group by语句的分组条件;
答案:
select 学号,avg(成绩) as 平均成绩 from sc where 学号 in (select 学号 from student where 班级 = '95033') group by 学号;
8.以选修 '3-105'为例,查询成绩高于'109'号同学的所有同学的记录。
注释:此题使用典型的嵌套查询,层层深入;
答案:
select * from student where 学号 in (select 学号 from sc where 课程号='3-105' and 成绩 >
(select 成绩 from sc where 学号='109' and 课程号='3-105'));
9.查询与学号为'108'的同学同岁的所有学生的学号、姓名和年龄。
注释:当查询的结果集返回只有一个时关键字in的作用等价于'='的作用,但是注意'='只能用与返回结果集只有一个,而 in 可以有多个结果;
答案:
法1:select 学号,姓名,年龄 from student where 年龄 = (select 年龄 from student where 学号='108');
法2:select 学号,姓名,年龄 from student where 年龄 in (select 年龄 from student where 学号='108');
10.查询'张旭'教师任课的课程号,选修其课程学生的学号和成绩。
注释:此题使用了表的连接:inner join,将多个表连接在一起组成一个新的表,标的连接的条件是必须存在相同的列;
答案:
法1:select 课程号 from course where 教师号=(select 教师号 from teacher where 姓名='张旭'); select 学号,成绩 from sc where 课程号 in(select 课程号 from course where 教师号 in
(select 教师号 from teacher where 姓名='张旭'));
法2:select teacher.姓名 as 教师姓名,course.课程号,student.姓名 as 学生姓名,student.学号,成绩 from teacher inner join (course inner join (sc inner join student on student.学号=sc.学号) on course.课程号=sc.课程号) on course.教师号=teacher.教师号 where teacher.姓名='张旭';
11.查询选修其课程的学生人数多于5人的教师姓名。
注册:此题采用嵌套查询和连接表两种方法,表现出了同样的效果;
答案:
法1:select 姓名 as 教师姓名 from teacher where 教师号 in (select 教师号 from course where 课程号 in (select 课程号 from sc group by 课程号 having count(*) > 5));
法2:select 姓名 as 教师姓名 from teacher inner join (course inner join sc on course.课程号=sc.课程号) on teacher.教师号=course.教师号 group by sc.课程号 having count(*) > 5;
12.查询'计算机系'与'电子工程系'不同职称的教师的姓名和职称。
注释:不同职称意味着分组后的返回结果集只有一个;
答案:
select 姓名 as 教师姓名,级别 as 职称 from teacher group by 级别 having count(*) = 1;
13.查询选修编号为'3-105'课程且成绩至少高于选修编号为'3-245'课程的同学的课程号、学号 、成绩并按成绩从高到低次序排列。
注释:any 表示任意一个;
答案:
select * from sc where 课程号='3-105' and 成绩 > any (select 成绩 from sc where 课程号='3-245') order by 成绩 desc;
14.查询选修编号为'3-105'课程且成绩高于选修编号为'3-245'课程的同学的课程号、学号 、成绩。
注释:all 表示所有;
答案:
select * from sc where 课程号='3-105' and 成绩 > all (select 成绩 from sc where 课程号='3-245') order by 成绩 desc;
15.列出所有教师和同学的姓名 、性别 、年龄。
注释:此题将所有表连接在一起:
答案:
select student.姓名 as 学生姓名,student.性别 as 学生性别,student.年龄 as 学生年龄,
teacher.姓名 as 教师姓名,teacher.性别 as 教师性别,teacher.年龄 as 教师年龄 from
student inner join (sc inner join (course inner join teacher on course.教师号=teacher.教师号)
on sc.课程号=course.课程号) on student.学号=sc.学号;
16.查询成绩比'3-105'课程的平均成绩低的学生的成绩表。
答案:
select * from sc where 成绩 < (select avg(成绩) from sc where 课程号='3-105') and 课程号='3-105';
17.查询成绩比该课程平均成绩低的学生的成绩表。
注释:此题是所有题中最难的,下面的方法很经典,请认真琢磨,先按照课程进行分组,查出小于各门课程平均成绩的所有成绩;
答案:
select * from sc where 成绩 < any (select avg(成绩) from sc group by 课程号) order by 课程号 asc;
18.列出所有任课教师的姓名和专业。
注释:姓名和专业涉及两个表course表和teacher表,只需连接这两个表就能解决此题了;
答案:
select teacher.姓名 as 教师姓名,专业 from teacher where 教师号 in (select 教师号 from course where 课程号 in (select 课程号 from sc group by 课程号));
19.列出所有未讲课教师的姓名和专业。
注释:解答同上题;
答案:
select teacher.姓名 as 教师姓名,专业 from teacher where 教师号 not in
(select 教师号 from course where 课程号 in (select 课程号 from sc group by 课程号));
20.列出至少有2名男生的班号。
注释:根据性别='男'这个条件来对student表进行分组就可以了;
答案:
select 班级 as 班号 from student where 性别='男' group by 班级 having count(*) >= 2;
21.查询不姓'王'的学生记录。
答案:
法1:select * from student where 姓名 not like '王%';
法2:select * from student where 姓名 not in (select 姓名 from student where 姓名like '王%');
22.查询每门课最高分的学生的学号、课程号、成绩。
注释:此题的技巧在于根据最高分来查成绩表;
答案:
select * from sc where 成绩 in (select max(成绩) from sc group by 课程号);
23.查询与'李军'同性别并同班的同学名字。
注释:嵌套查询;
答案:
select 姓名 from student where 性别=(select 性别 from student where 姓名='李军') and
班级=(select 班级 from student where 姓名='李军');
24.查询'男'教师及其所上的课程。
注释:先分析此题涉及的表;
答案:
select 姓名 as 教师名,课程名 as 课程 from teacher inner join course on teacher.教师号=course.教师号 where 性别='男';
25.查询选修'计算机导论'课程的'男'同学的成绩表。
注释:此题用嵌套查询和表连接两种方法进行解答;
答案:
法1:select * from sc where 课程号=(select 课程号 from course where 课程名='计算机导论') and 学号 in (select 学号 from student where 性别='男');
法2:select sc.学号,student.姓名 as 学生姓名,student.性别 as 学生性别,sc.课程号,sc.成绩,course.课程名 from student inner join (sc inner join course on sc.课程号=course.课程号)
on student.学号=sc.学号 where 性别='男' and 课程名='计算机导论';