数据库查询语言QL
数据库查询语言QL
一、简单查询
1 查询所有数据行和列
select * from stuInfo
2 查询部分行和列
select stuName,stuSex,stuAge
from stuInfo
where stuSex='男'
3 在查询中使用列名
select stuName as 姓名,stuSex as 性别,stuAge as 年纪
from stuInfo
where stuSex<>'男'
select姓名=stuName,性别=stuSex,年纪=stuAge
from stuInfo
where stuSex='男'
select stuName+','+stuSex+','+convert(char(8),stuAge) from stuInfo
select stuName+','+stuSex+','+cast(stuAge as char(8)) from stuInfo
4 查询空行
select * from stuInfo where stuName is null
5 在查询中使用常量
select姓名=stuName,性别=stuSex,'重庆市' as 籍贯
from stuInfo
where stuSex='男'
6 查询返回限制的行数
select top 2 stuName,stuSex from stuInfo
--显示符合条件的前%--
select top 40 percent stuName,stuSex from stuInfo
7 查询排序
select考号=ExamNo,学号=stuNo
from stuMarks
where writtenExam>60
order by ExamNo desc
8 在查询中使用聚合函数
select笔试总分=sum(writtenExam),机试总分=sum(labExam) from stuMarks
select笔试最高分=max(writtenExam),笔试最低分=min(writtenExam) from stuMarks
select笔试平均分=avg(writtenExam),机试平均分=avg(labExam) from stuMarks
select count(*) as 笔试及格人数from stuMarks where writtenExam>60
9 消除重复行
select distinct * from stuInfo
10 模糊查询
--用like进行模糊查询--
select * from stuInfo where stuName like '李%'
--用between在某个范围内查询--
select * from stuInfo where stuAge between 25 and 50
--用in在列举值内进行查询--
select * from stuInfo where stuName in('李文才','欧阳俊雄')
11 分组查询
--按性别分组查询--
select stuSex,(avg(labExam)+avg(writtenExam))/2 as 平均成绩
from score
group by stuSex
12 having进行分组筛选
--按性别分组查询,但只显示男生的平均成绩--
select stuSex,(avg(labExam)+avg(writtenExam))/2 as 平均成绩
from score
group by stuSex
having stuSex='男'
二、联接查询
1 内联接:显示符合条件的记录.
内联接是最典型和最常用的联接查询,它根据表中共同的列来进行匹配,特别是两个表存在主外键关系时通常用到内联接查询.内联接用"="或"<>"或inner join..on..来进行表之间的关联.
select a.stuName,a.stuSex,a.stuNo,b.ExamNo,b.writtenExam,b.labExam
from stuInfo as a,stuMarks as b
where a.stuNo=b.stuNo
select a.stuName,a.stuSex,a.stuNo,b.ExamNo,b.writtenExam,b.labExam
from stuInfo as a inner join stuMarks as b
on a.stuNo=b.stuNo
2 外联接:可以是左外联接右外联接或完整外联接
(1) 左外联接:显示左边表所有记录及右边表中符合条件的记录.
left join..on..
left outer join..on..
select a.stuName,a.stuSex,a.stuNo,b.ExamNo,b.writtenExam,b.labExam
from stuInfo as a left outer join stuMarks as b
on b.writtenExam>80
where a.stuNo=b.stuNo
(2) 右外联接:显示右边表所有记录及左边表中符合条件的记录.
right join..on..
right outer join..on..
select a.stuName,a.stuSex,a.stuNo,b.ExamNo,b.writtenExam,b.labExam
from stuInfo as a right outer join stuMarks as b
on b.labExam>80
where a.stuSex='男'
(3) 完整外联接:显示表中所有数据.包括符合条件还不符合条件的.
full join ..on..
full outer join..
select a.stuName,a.stuSex,a.stuNo,b.ExamNo,b.writtenExam,b.labExam
from stuInfo as a full outer join stuMarks as b
on a.stuSex='男'
where b.labExam>80
3 交叉联接:表中每一条记录与另一表中每一条记录搭配成新记录.不用on关健字.相当于两个表相乘.
cross join..
select a.stuName,a.stuSex,a.stuNo,b.ExamNo,b.writtenExam,b.labExam
from stuInfo as a cross join stuMarks as b
三、子查询
子查询是指一条select语句作为另一条select语句的一部分.外层的select语句叫父查询,内部的select语句叫子查询. SQL Servler执行时,先执行子查询部分,求出子查询部分的值,然后再执行整个父查询.它的执行效率比采用SQL变量实现的方案要高.子查询是作为where条件的一部分的,所以还可以和update、insert、delete一起使用.语法类似于select语句
.
1 简单子查询
--查询笔试成绩为90分的学员--
（1）采用表联接
select a.stuName
from stuInfo as a inner join stuMarks as b
on a.stuNo=b.stuNo
where b.writtenExam=90
（2）采用子查询
select stuName
from stuInfo
where stuNo=(select stuNo from stuMarks where writtenExam=90)
表联连:适合于查看多表数据.
子查询:适合于作为查询的筛选条件.
注意:
将子查询和比较运算(= >等)符联合使用时,必须保证子查询返回的值不能多于一个.
也就是说select stuNo from stuMarks where writtenExam=90返回的值只能有一个.
2 in和not in子查询
In和not in后面的子查询可以返回多个记录。
--查询参加考试的学员名单--
select stuName
from stuInfo
where stuNo in(select stuNo from stuMarks)
--查询没有参加考试的学员名单--
select stuName
from stuInfo
where stuNo not in(select stuNo from stuMarks)
3 exists和not exists子查询
exists是一个存在检测的子查询语句.从理论上讲,exists可以用为where的子查询.但一般用于if语句的存在检测.
--检测数据库--
if exists(select * from sysdatabasees where name='数据库')
--检测表视图存储过程触发器函数--
if exists(select * from sysobjects where name='表|视图|存储过程|触发器|函数')
--检测索引--
if exists(select name from sysindexes where name='索引')
若子查询结果非空,即记录条数条以上,则exists(子查询)将返回真(true),否则返回(false).
--笔试高于80分的加分,否则加分--
select * from stuMarks
if exists(select * from stuMarks where WrittenExam>80)
begin
print ' 笔试成绩高于分的,加分,加分后的成绩为:'
update stuMarks set WrittenExam=WrittenExam-2
select * from stuMarks
end
else
begin
print ' 笔试成绩低于分的,加分,加分后的成绩为:'
update stuMarks set WrittenExam=WrittenExam-5
select * from stuMarks
end
go
4 使用检索结果创建表
select [列名] [into|bulkcopy] 新表名from 源表名
若要创建永久表,必须设置into/bulkcopy;
若要创建临时表,则在表前面加#(局部临时表)或##(全局临时表).
select * into myTable from stuInfo
四、本节用到的测试数据库
use master
execute xp_cmdshell 'mkdir d:stu',no_output
go
/**//*建立数据库stuDB*/
if exists(select * from sysdatabases where name='stuDB')
drop database stuDB
create database stuDB
on primary
(
name='stuDB_data',
filename='d:stustuDB_data.mdf',
size=3mb,
maxsize=100mb,
filegrowth=2%
)
log on
(
name='stuDB_log',
filename='d:stustuDB_log.ldf',
size=1mb,
maxsize=50mb,
filegrowth=1
)
go
use stuDB
/**//*建立学生信息表stuInfo*/
if exists(select * from sysobjects where name='stuInfo')
drop table stuInfo
create table stuInfo
(
stuName varchar(10) not null,
stuNo varchar(10) not null,
stuSex varchar(4) not null,
stuAge int not null,
stuSeat int identity(1,1),
stuAddress text default('地址不详')
)
/**//*建立学生成绩表*/
if exists(select * from sysobjects where name='stuMarks')
drop table stuMarks
create table stuMarks
(
ExamNo varchar(10) not null,
stuNo varchar(10) not null,
writtenExam int,
labExam int
)
/**//*插入数据*/
insert into stuInfo values('张秋丽','s25301','男',18,'北京海淀')
insert into stuInfo values('李斯文','s25303','女',22,'河南洛阳')
insert into stuInfo values('李文才','s25302','男',31,default)
insert into stuInfo values('欧阳俊雄','s25304','男',28,'威武哈')
insert into stuMarks values('s271811','s25303',90,56)
insert into stuMarks values('s271813','s25302',58,90)
insert into stuMarks values('s271816','s25301',87,82)
insert into stuMarks values('s271819','s25304',66,48)
/**//*添加约束*/
alter table stuInfo add
constraint PK_stuNo primary key(stuNo),
constraint CK_stuNo check(stuNo like 's253[0-9][0-9]'),
constraint CK_stuSex check(stuSex in('男','女')),
constraint CK_stuAge check(stuAge between 15 and 40),
constraint CK_stuSeat check(stuSeat between 1 and 30)
alter table stuMarks add
constraint PK_ExamNo primary key(ExamNo),
constraint FK_stuNo foreign key(stuNo) references stuInfo(stuNo)
/**//*建立成绩信息表视图*/
create view score
as
select a.stuName,a.stuSex,a.stuNo,b.ExamNo,b.writtenExam,b.labExam
from stuInfo as a,stuMarks as b
where a.stuNo=b.stuNo
select * from stuInfo
select * from stuMarks
五、综合示例
/**//**//**//**//**//**//**//*--本次考试的原始数据--*/
--select * from stuInfo
--select * from stuMarks
/**//**//**//**//**//**//**//*-------------------统计考试缺考情况----------------------*/
select 应到人数=(select count(*) from stuInfo),
实到人数=(select count(*) from stuMarks),
缺考人数=((select count(*) from stuInfo))-((select count(*) from stuMarks))
/**//**//**//**//**//**//**//*-----统计考试通过情况,并将统计结果存放到新表newTable中---*/
if exists(select * from sysobjects where name='newTable')
drop table newTable
select stuName,stuInfo.stuNo,WrittenExam,labExam,
isPass=case
when WrittenExam>=60 and labExam>=60 then 1
else 0
end
into newTable from stuInfo left join stuMarks
on stuInfo.stuNo=stuMarks.stuNo
--select * from newTable
/**//**//**//**//**//**//**//*-------酌情加分,比较笔试和机试平均分,哪科低就加分-----*/
declare @avgWritten numeric(4,1),@avgLab numeric(4,1)
select @avgWritten=avg(WrittenExam) from newTable where WrittenExam is not null
select @avgLab=avg(LabExam) from newTable where LabExam is not null
if @avgWritten<@avgLab
while(1=1)--循环给笔试加分,不得超过分,
begin
update newTable set WrittenExam=WrittenExam+1
if(select max(WrittenExam) from newTable)>=97
break;
end
else
while(1=1)--循环给机试加分,不得超过分,
begin
update newTable set LabExam=LabExam+1;
if(select max(LabExam) from newTable)>=97
break;
end
--select * from newTable
/**//**//**//**//**//**//**//*---------因提分,所以要更新isPass(是否通过)列的数据--------*/
update newTable
set isPass=case
when WrittenExam>=60 and LabExam>=60 then 1
else 0
end
--select * from newTable
/**//**//**//**//**//**//**//*--------------------显示考试最终通过情况-------------------*/
select 姓名=stuName,学号=stuNo
,笔试成绩=case
when WrittenExam is null then '缺考'
else convert(varchar(5),WrittenExam)
end
,机试成绩=case
when LabExam is null then '缺考'
else convert(varchar(5),LabExam)
end
,是否通过=case
when isPass=1 then '是'
else '否'
end
from newTable
/**//**//**//**//**//**//**//*--------------------显示通过率及通过人数-------------------*/
select 总人数=count(*),通过人数=sum(isPass),
通过率=(convert(varchar(5),avg(isPass*100))+'%') from newTable
go
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/zerolsy/archive/2008/02/26/2123617.aspx
