SQL DDL 操作
什么是SQL DDL 操作?
DDL是SQL定义语言,它主要包括三个关键字:create ,alter , drop(数据库关键字不分大小写 ),主要操作对象 有数据库、表、索引、视图等。
语句说明:
创建数据库 create database
修改数据库 alter database
删除数据库 drop database
创建表 create table
修改表 alter table
删除表 drop table
创建索引 create index
删除索引 drop index
实验的目的
- 掌握基本的SELECT查询。
- 掌握复杂查询的实现
- 掌握聚合函数的应用。
- 掌握基本表的连接操作。
实验内容与要求
在实验1、2创建的S、SC、C、T四个基本表基础上,完成以下查询
1. 在四个基本表中完成以下查询
1)检索学习C3课程的所有学生姓名和年龄
2)检索学习由教授讲授的课程的学生学号和姓名
3)检索没选修任何课程的学生学号
4)检索年龄在18到20岁之间的女学生学习的课程号和课程名
5)检索学习了C1或C3课程的学生学号
6)检索Hu同学不学的课程号
7)检索成绩为空的课程对应的教师号和教师姓名
8)检索至少有S2和S4学习的课程号
9)检索选修了S3学习的全部课程的学生学号
10)检索每个学生的姓名和出生年份,并将出生年份列重命名为Birth_Year
2.在四个基本表中完成以下查询
1) 检索选修了课程的所有学生的学号,要求不显示重复结果
2) 检索LIU老师所授课程的每门课程的人数和平均成绩。
3) 统计各门课程的最高分。
4) 统计每门课程的学生选修人数,(超过3人的课程才统计),要求显示课程号和人数,查询结构按人数升序排序,若人数相同,按课程号降序排序。
5) 查询每个学生超过他自己选修课程平均成绩的学号及课程号。
6) 把低于所有课程总平均成绩的女同学的成绩提高3%
7) 分别用INNER JOIN 和LEFT OUTER JOIN 连接S和SC表
8) 检索姓名中第三个字母为“N”的学生的姓名和年龄。
9) 选择C1课程的前3名的学生学号和成绩。
10) 检索平均成绩大于85的学生学号。
3.Select 语句是否必须包含FROM和WHERE字句?或者至少应该包含哪个或哪些字句?请参考联机丛书并实验之。
实验主要步骤
在四个基本表中完成以下查询
1)检索学习C3课程的所有学生姓名和年龄
源码:
select S.SNAME,S.AGE
from S,SC
where S.S# = SC.S# and SC.C# = 'c3'
2)检索学习由教授讲授的课程的学生学号和姓名
源码:
--检索学习由教授讲授的课程的学生学号和姓名
select distinct S.S#,S.SNAME
from S,SC,C,T
where T.TITLE='教授' and T.T#=C.T# and C.C#=SC.C# and SC.S#=S.S#
3)检索没选修任何课程的学生学号
源码:
--检索没选修任何课程的学生学号
select S.S#
from S
where S.S# NOT IN (SELECT S# FROM SC)
4)检索年龄在18到20岁之间的女学生学习的课程号和课程名
源码:
--检索年龄在18到20岁之间的女学生学习的课程号和课程名
select distinct C.C#,C.CNAME
from C,SC
where C.C# = SC.C# and SC.S# in
(
select S# from S
where S.AGE between 18 and 20
)
5)检索学习了C1或C3课程的学生学号
源码:
--检索学习了c1或c3课程的学生学号
select distinct S.S#
from S,SC
where S.S# like SC.S# and (SC.C# = 'c1' or SC.C# = 'c3')
6)检索Hu同学不学的课程号
源码:
--检索Hu同学不学的课程号
select C.C#
from C
where C.C# not in
(
select C#
from SC,S
where SC.S# like S.S# and S.SNAME = 'HU'
)
7)检索成绩为空的课程对应的教师号和教师姓名
源码:
--检索成绩为空的课程对应的教师号和教师姓名
select T.T#,T.TNAME
from T
where T.T# in
(
select C.T#
from C,SC
where C.C# like SC.C# and SC.SCORE is null
)
8)检索至少有S2和S4学习的课程号
源码:
--检索至少有S2和S4学习的课程号
select distinct SC.C#
from SC
where SC.C# in
(
(
select SC.C#
from SC
where SC.S# like 's2'
)
intersect --交集
(
select SC.C#
from SC
where SC.S# like 's4'
)
)
9)检索选修了S3学习的全部课程的学生学号
源码:
--检索选修了S3学习的全部课程的学生学号
select distinct SC.S#
from SC
where SC.C# in
(
select SC.C#
from SC
where SC.S# like 's3'
)
10)检索每个学生的姓名和出生年份,并将出生年份列重命名为Birth_Year
源码:
--检索每个学生的姓名和出生年份,并将出生年份列重命名为Birth_Year
select S.SNAME,2018-S.AGE as Birth_Year from S
在四个基本表中完成以下查询
1)检索选修了课程的所有学生的学号,要求不显示重复结果
源码:
--检索选修了课程的所有学生的学号,要求不显示重复结果
select distinct SC.S# from SC
2)检索LIU老师所授课程的每门课程的人数和平均成绩
源码:
--检索LIU老师所授课程的每门课程的人数和平均成绩。
select C.C#,count(distinct SC.S#),avg(SC.SCORE)
from C,T,SC
where C.T# = T.T# and C.C# = SC.C# and TNAME = 'LIU'
group by C.C#
3)统计各门课程的最高分
源码:
--统计各门课程的最高分
select SC.C#,max(SC.SCORE)
from SC
group by SC.C#
4)统计每门课程的学生选修人数,(超过3人的课程才统计),要求显示课程号和人数,查询结构按人数升序排序,若人数相同,按课程号降序排序。
源码:
--统计每门课程的学生选修人数
--(超过3人的课程才统计),要求显示课程号和人数,查询结构按人数升序排序,若人数相同,按课程号降序排序。
select distinct C#,count(distinct S#) as 人数
from SC
group by C#
having count(S#)>3
order by count(distinct S#) asc,C#
5)查询每个学生超过他自己选修课程平均成绩的学号及课程号
源码:
--查询每个学生超过他自己选修课程平均成绩的学号及课程号。
select X.S#,X.C#
from SC as X
where X.SCORE >
(
select avg(Y.SCORE)
from SC as Y
where X.S# =Y.S#
)
6)把低于所有课程总平均成绩的女同学的成绩提高3%
此处的警告是因为存在空值,可在语句顶部添加 set ansi_warnings off;
源码:
--把低于所有课程总平均成绩的女同学的成绩提高3%
update SC set SC.SCORE=SC.SCORE*1.03
where SC.SCORE like
(
select SC.SCORE
from SC,S
where SC.S# like S.S# and S.sex = '女' and SC.SCORE <
(
select avg(SC.SCORE)
from SC
)
)
7)分别用INNER JOIN 和LEFT OUTER JOIN 连接S和SC表
源码:
--分别用 inner join 和 left outer join 连接 S 和 SC 表
select *from S inner join SC on S.S# = SC.S#
select *from S left outer join SC on S.S# = SC.S#
8)检索姓名中第三个字母为“N”的学生的姓名和年龄
源码:
--检索姓名中第三个字母为“N”的学生的姓名和年龄。
select S.SNAME,S.AGE
from S
where S.SNAME like '__N%'
9) 选择C1课程的前3名的学生学号和成绩
源码:
--选择C1课程的前3名的学生学号和成绩。
select top(3) SC.S#,SC.SCORE
from SC
where SC.C# like 'c1' order by SC.SCORE desc
10)检索平均成绩大于85的学生学号
源码:
--检索平均成绩大于85的学生学号。
select distinct X.S#
from SC as X
where 85 <= all
(
select avg(Y.SCORE)
from SC as Y
where Y.S# = X.S# group by Y.S#
)
select 语句是否必须包含from和where字句?或者至少应该包含哪个或哪些字句?请参考联机丛书并实验之。
- select column from table [where … ]
- select 语句必须包含from字句,但并非必须包含where条件选择字句。
- select 语句应该包含:
- select 关健字 必须
- column 字段名 必须。最少一个.全部则用*号代替。
- from 关健字 必须
- table 表名 必须
关键语法介绍:
-
like
语法格式是:
select * from 表名 where 字段名 like 对应值(子串)
它主要是针对字符型字段的,它的作用是在一个字符型字段列中检索包含对应子串的。 -
distinct
一般是用来去除查询结果中的重复记录的,而且这个语句在select、insert、delete和update中,只可以在select中使用。
具体的语法如下:
select distinct 表达式[,表达式…] from 表名 [where 条件]; -
intersect 和 except
(1)intersect 语句可以用来查两个表中的“公共”数据信息,即“交叉”数据信息。intersect 返回 intersect操作数左右两边的两个查询都返回的所有非重复值。
(2)except 语句则是用来查询上述“公共”信息“除外”才信息。except 从左查询中返回右查询没有找到的所有非重复值。
(3)与union联合查询语句一样,intersect 语句和 except 语句查询的两个表必须具有相同的结构,即他们的列必须相似,数目相等,顺序相同,且数据类型兼容。
(4)语法如下:
select 列名 from 表名1
intersect
select 列名 from 表名2
select 列名 from 表名1
except
select 列名 from 表名2 -
group by
group by语句从英文的字面意义上理解就是“根据(by)一定的规则进行分组(Group)”。它的作用是通过一定的规则将一个数据集划分成若干个小的区域,然后针对若干个小区域进行数据处理。 -
order by
order by 是用在where条件之后,用来对查询结果进行排序
语法如下:
order by 字段名 asc/desc
- asc 表示升序(默认为asc,可以省略)
- desc表示降序
order by 无法用于子查询,否则会报错:除非另外还指定了 top 或 for xml,否则,order by 子句在视图、内联函数、派生表、子查询和公用表表达式中无效。
如有错误,欢迎指正!