数据库查询语言QL

最新推荐文章于 2022-12-05 16:48:00 发布

zerolsy

最新推荐文章于 2022-12-05 16:48:00 发布

阅读量1.6k

点赞数

文章标签：数据库语言 insert null join table

本文链接：https://blog.csdn.net/zerolsy/article/details/2123617

版权

数据库查询语言 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

4 使用检索结果创建表

select [ 列名 ] [into|bulkcopy] 新表名 from 源表名

若要创建永久表 , 必须设置 into/bulkcopy;

若要创建临时表 , 则在表前面加 #( 局部临时表 ) 或 ##( 全局临时表 ).

select * into myTable from stuInfo

四、本节用到的测试数据库

use master

execute xp_cmdshell ' mkdir d:stu ' ,no_output

/*建立数据库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

)

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

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

zerolsy

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
数据库查询语言QL

数据库查询语言QL一、简单查询1 查询所有数据行和列select * from stuInfo 2 查询部分行和列select stuName,stuSex,stuAge from stuInfowhere stuSex=男 3 在查询中使用列名select stuName as 姓名,stuSex as 性别,stuAge a
复制链接

扫一扫