SQL基本操作案例Word文档格式.docx
《SQL基本操作案例Word文档格式.docx》由会员分享,可在线阅读,更多相关《SQL基本操作案例Word文档格式.docx(20页珍藏版)》请在冰点文库上搜索。
表达式1>
][PRIMARYKEY|UNIQUE]
[REFERENCES<
表名2>
[TAG<
标识名1>
]]
字段名2>
小数位数
>
])…]…
[,PRIMARYKEY<
表达式2>
TAG<
标识名2>
|,UNIQUE<
表达式3>
标识名3>
[,FOREIGNKEY<
表达式4>
标识名4>
[NODUP]
[,REFERENCES<
表名3>
[TAG<
标识名5>
[,CHECK<
逻辑表达式2>
出错提示信息2>
]])
createtable学院;
(学院编号c
(2)primarykey,;
学院名c(16)notnull,;
院长c(6))
createtable专业;
(专业编号c(4)primarykey,;
专业名称c(20)notnull,;
专业类别c(10),;
学院编号c
(2)references学院tag学院编号)
createtable课程;
(课程编号c(6)primarykey,;
课程名c(20)notnull,;
课程类别c(10)notnull,;
学分n(3,1)notnullcheck学分>
0,;
学院编号c
(2)references学院tag学院编号notnull)
createtable教师;
(教师编号c(6)primarykey,;
姓名c(8)notnull,;
性别c
(2)notnullcheck(性别="
男"
or性别="
女"
),;
生日datenotnull,;
职称c(8),;
学院编号c
(2)references学院tag学院编号,;
基本工资ynotnullcheck基本工资>
=800)
createtable学生;
(学号c(10)primarykey,;
姓名c(10)notnull,;
性别c
(2)notnullcheck性别="
;
民族c(8),;
籍贯c(8),;
专业编号c(4)references专业tag专业编号,;
简历m,;
照片g)
createtable成绩;
(学号c(10)references学生,;
课程编号c(6)references课程,;
成绩n(5,1)check成绩>
=0and成绩<
=100,;
primarykey学号+课程编号tagcj)
2.表修改操作
例5—3
opendatabase学生管理.dbc
altertable教师;
add民族c(8)default"
汉族"
altercolumn民族c(10)notnull
dropcolumn民族
例5—4
altertable学院;
addforeignkey院长tag院长references教师tag教师编号
3.表删除操作
例5—5
opendatabasec:
\学生管理.dbcexclusive
droptable学院recycle
droptable成绩recycle
SQL的查询功能
【语法】SELECT[ALL|DISTINCT][TOP<
数值>
[PERCENT]]
[<
别名.>
]<
查询输出项>
[AS<
别名>
][,[<
别名.>
查询输
出项>
]…]
FROM[<
!
表名>
[[AS]<
[[INNER|LEFT[OUTER]|RIGHT[OUTER]|FULL[OUTER]JOIN
][ON<
连接条件>
…]
[[INTO<
目的地>
]|TOFILE<
文件名>
[ADDITIVE]
|TOPRINTER[PROMPT]|TOSCREEN]][NOCONSOLE][PLAIN]
[NOWAIT][WHERE<
[AND<
[AND|OR<
过滤条件>
…]]]
[GROUPBY<
分组项>
[,<
…[HAVING<
分组过滤条件>
[UNION[ALL]SELECT<
命令>
[ORDERBY<
排序项>
[ASC|DESC][,<
[ASC|DESC…]]
1.单表查询
例5—6
select*from学生管理!
学院
例5—7
select课程名,学分from课程
例5—8
selectdistinct专业编号from学生intotablexszy.dbf
usexszy.dbf
brow
例5—9
select*;
from教师;
tofilec:
\jsnl.txt;
orderby生日asc
例5—10
selecttop3*;
orderby基本工资desc
例5—11
from成绩;
where学号="
0511010001"
例5—12
select姓名,生日,民族,籍贯;
from学生;
where姓名like"
王%"
and性别="
例5—13
where职称in("
教授"
"
副教授"
研究员"
副研究员"
)
例5—14
select"
教师工资"
sum(基本工资),avg(基本工资),max(基本工资),min(基本工资);
from教师
as说明,;
sum(基本工资)as工资总额,avg(基本工资)as平均工资,;
max(基本工资)as最高工资,min(基本工资)as最低工资;
例5—15
sum(基本工资*1.1)as预期工资总额,avg(基本工资*1.1)as预期平均工资;
2.多表连接查询
【语法1】SELECT<
输出列1>
,…,<
输出列n>
FROM<
表1>
JOIN<
表2>
(SQL新版本)
ON<
连接条件1>
[JOIN<
表3>
连接条件2>
【语法2】SELECT<
,<
…
WHERE连接条件(SQL早期版本)
例5—16(内连接查询)
(SQL新版本写法)
select学生.*,专业.*;
from学生join专业;
on学生.专业编号=专业.专业编号
(SQL早期版本写法)
from学生,专业;
where学生.专业编号=专业.专业编号
例5—17(右外连接查询)
use专业
list
seledistinct专业编号from学生
select姓名,性别,生日,专业.*;
from学生rightouterjoin专业;
两表连接运算四种不同连接方式:
内部连接、左外连接、右外连接、全外连接
【内连接语法】SELECT输出列
FROM左表[INNER]JOIN右表
ON连接条件
内连接:
原表记录中严格满足连接条件的左表和右表的记录连接在一
起输出,内连接简称连接运算,命令中INNER可省略。
【外连接语法】SELECT输出列
FROM左表LEFT|RIGHT|FULL[OUTER]JOIN右表
ON连接条件
左外连接:
保留左表的不满足连接条件的记录,对应的右表输出字段
处填上NULL,命令中OUTER可省略。
右外连接:
保留右表的不满足连接条件的记录,对应的左表输出字段
全外连接:
保留左、右表中不满足连接条件的记录,在对应的另外一
边分别填上NULL,命令中OUTER可省略。
例5—18(左外连接查询)
use学生
list
seledistinct学号from成绩
select学生.*,成绩.*;
from学生leftjoin成绩;
on学生.学号=成绩.学号
例5—19(多表连接查询)
select课程名,姓名,专业名称,成绩;
from课程join成绩on课程.课程编号=成绩.课程编号;
join学生on成绩.学号=学生.学号;
join专业on学生.专业编号=专业.专业编号;
where课程名="
大学英语"
例5—20(自连接查询)
selectf1.起点,f1.终点as转机点,f2.终点;
from航班表asf1,航班表asf2;
wheref1.终点=f2.起点
例5—21(教师表连接2次查询)
selectj1.姓名,学院名,j2.姓名as院长;
from教师asj1join学院onj1.学院编号=学院.学院编号;
join教师asj2on学院.院长=j2.教师编号
笛卡儿积运算:
SELECT<
FROM表1,表2
(不写连接条件)
例5—16(笛卡儿积运算查询)
from学生,专业
3.嵌套子查询
SQL嵌套子查询的几种运算方式:
(1)<
字段>
比较运算符>
ALL(<
子查询>
(2)<
ANY|SOME(<
(3)<
[NOT]IN(<
(4)[NOT]EXISTS(<
SQL的嵌套子查询可以分为非相关子查询和相关子查询
◆非相关子查询包括以上
(1)
(2)(3)三种运算方式
◆相关子查询包括以上第(4)种运算方式
非相关子查询:
首先进行子查询,获得一个结果集合,然后再进行外部查询中的记录与子查询结果的比较,即先内后外的方式。
字段值按照比较运算符的要求,与子查询结果的每一个值比较,所有的比较都为.T.,结果为.T.,只要有一个不成立,结果就为.F.。
字段值与子查询结果的每一个值比较,只要有一个满足要求,结果就为.T.,全部比较都不成立,结果才为.F.。
注:
子查询的结果列的类型必须与参与比较的字段类型是可比的。
例5—22非相关子查询使用ALL运算符
from专业;
where专业.专业编号<
all(select专业编号from学生)
some(select专业编号from学生)
例5—23非相关子查询使用ALL运算符
select学号,姓名,专业名称;
from学生join专业on学生.专业编号=专业.专业编号;
where性别="
and生日<
all(select生日from学生where性别="
例5—24非相关子查询子查询结果唯一时,无须ANYSOMEALL
select学生.学号,姓名,课程编号,成绩;
from学生join成绩on学生.学号=成绩.学号;
where成绩=(selectmax(成绩)from成绩)
例5—25非相关子查询子查询结果唯一时,无须ANYSOMEALL
select学号,成绩;
where课程编号="
080001"
and成绩>
;
(select成绩from成绩;
where学号="
0508040004"
and课程编号="
集合运算符IN的作用相当于数学上的运算符:
∈(属于)
使用子查询的格式:
[NOT]IN(<
首先由子查询求出一个结果集合(一个值或一列值),在外部查询中的字段值如果属于其中的一个,则符合条件,否则不符合要求。
NOTIN的意思是不属于。
说明:
通过分析可知:
◆IN与SOME的功能相同
◆NOTIN与<
ALL的功能相同
因此很多查询可以有不止一种的实现方法;
需要注意运算符两边的数据类型要可比。
例5—26非相关子查询使用NOTIN和IN运算符
where学号notin(select学号from成绩where成绩<
80);
and学号in(select学号from成绩)
相关子查询:
对于外部查询中与EXISTS子查询有关的表的记录,逐条带入子查询中进行运算,如果结果不为空,这条记录就符合查询要求;
如果子查询结果为空,则该条记录不符合查询要求。
由于查询过程是针对外部查询的记录值再去进行子查询,子查询的结果与外部查询的表有关,因此称为相关子查询,即是从外到内的过程。
带NOT运算的判别结果与不带NOT运算的判别结果相反。
由于EXISTS的运算是检验子查询结果是否为空,因此该运算的格式是:
[NOT]EXISTS(子查询)
运算符前面不需要字段名,子查询的输出列也无须指明具体的字段。
虽然在标准SQL中,子查询可以多层嵌套,但在VFP中只允许有一层子查询。
例5—27
select姓名,性别,专业编号;
whereexists(select*from成绩where成绩.学号=学生.学号;
and课程编号="
=60)
select*from成绩join学生on成绩.学号=学生.学号;
=60
例5—28
selectc1.学号;
from成绩asc1;
wherec1.课程编号="
and;
exists(select*from成绩asc2;
wherec2.学号=c1.学号and;
c2.课程编号="
050002"
andc1.成绩>
c2.成绩)
select*from成绩asc1,成绩asc2;
c1.课程编号="
andc2.课程编号="
c2.成绩
4.分组统计查询
SQL的分组统计以及HAVING子句的使用方式:
(1)设定一个分组依据字段(组),按该字段(组)值相等的原则进行分组,具有相同值的记录作为一组。
用GROUPBY<
[,…]子句指定。
(2)在输出列中指定统计函数,分别对每一组按照统计函数所在的字段进行统计,得到各组的统计数据。
输出列中的非统计字段必须出现在分组子句中。
(3)如果要对统计计算结果进行筛选,将筛选条件放在HAVING<
条件>
子句中。
HAVING<
子句可与GROUP子句联用,对统计的结果进行筛选;
另外HAVING中不能使用子查询。
例5—29
select学号,avg(成绩)as平均成绩;
groupby学号
select成绩.学号,姓名,avg(成绩)as平均成绩;
from成绩join学生on成绩.学号=学生.学号;
groupby成绩.学号,姓名;
havingavg(成绩)>
=85
例5—30
统计表中的行数
selectcount(*);
分组统计
selectcount(教师编号);
groupby学院编号
统计表中某字段不重复的行数
selectcount(distinct学院编号);
例5—31
select学院名,性别,count(性别)as人数;
from学院join专业on学院.学院编号=专业.学院编号;
join学生on专业.专业编号=学生.专业编号;
groupby学院.学院编号,学院名,性别
where学院名="
信息学院"
groupby学院.学院编号,学院名,性别
补充题1:
统计软件工程学院表中各班的平均身高和平均体重。
selectdistinct专业班级,avg(身高)as平均身高,avg(体重)as平均体重from软件工程学院groupby专业班级
补充题2:
统计软件工程学院表中各民族的人数。
selectdistinct民族,count(*)from软件工程学院groupby民族
5.查询合并
并运算格式:
查询1>
UNION<
查询2>
查询3>
…
并运算时,前后查询的输出列要对应,并非一定要完全相同,但二者列数相同,对应字段类型要一致。
数据来源可以是相同的表,也可以是不同的表。
例5—32相同表的并查询
select教师.*,学院名;
from教师join学院on教师.学院编号=学院.学院编号;
and职称="
union;
工商管理学院"
select专业班级,姓名,爱好;
from软工1班;
where爱好like"
%篮球%"
%游戏%"
or爱好like"
SQL的更新功能
1.SQL的插入功能
【语法1】INSERTINTO<
表>
[(<
字段1>
字段2>
,…])]
VALUES(<
,…])
插入一条或多条记录到表尾
计算出表达式的值,插入到表中作为一条新记录。
如果省略字段名表,则表达式的个数必须与字段数相同,按字段顺序将各表达式的值依次赋予各字段,数据类型必须一致。
若指明了要赋值的字段表,则将表达式的值依次赋予列出的各字段,没有列出的字段取各字段的默认值。
【语法2】INSERTINTO<
FROMARRAY<
数组名>
|FROMMEMVAR
插入字段的各字段值事先已放在数组元素或同名内存变量中。
在指定的表中插入新记录,表不需要事先打开。
例5—33
insertinto学生(学号,姓名,性别,生日,民族,籍贯,专业编号);
values("
0602010112"
张三"
{^1989/01/24},"
汉"
湖北省"
0201"
插入部分字段值,字段名顺序可以改变,但必须与字段值相对应。
insertinto学生(姓名,性别,学号,生日);
{^1989/01/24})
2.SQL的删除功能
分为逻辑删除和物理删除(PACK)
可以同时删除多行
参照完整性设置
【语法】DELETEFROM[<
数据库名!
[WHERE<
[AND|OR<
…]]
例5—34
deletefrom学生;
0611010005"
deletefrom学院;
where学院编号="
02"
例5—35
where学号notin(selectdistinct学号from成绩)
3.SQL的修改功能
【语法】UADATE[<
数据库名!
SET<
=<
[WHERE<
修改指定表中指定字段的值,可以同时修改多行。
例5—36
update教师set基本工资=基本工资+基本工资*0.05
update教师set基本工资=基本工资*1.05
VFP修改功能:
p88
【语法】REPLACE
WITH<
[ADDITIVE]
[ADDITEVE]]…
[FOR|WHILE<
[<
范围>
][IN<
工作区号>
|<
use教师
replaceall基本工资with基本工资+基本工资*0.05
查询设计器
创建查询文件:
Createquery[<
查询文件名>
|?
编辑查询文件:
Modifyquery<
Modifycommand<
查询文件名.qpr>
执行查询文件: