【数据库原理及应用教程】第三章 SQL
最佳答案 问答题库588位专家为你答疑解惑
文章目录
- 重点难点
- 建立数据库
- 创建数据库
- 选择元组select
- 多表查询
- 子查询
- 追加元组 insert
- 嵌套子查询
- 元组删除命令 delete
- Update命令
- 结果计算
- 聚集函数
- 分组查询和分组过滤
- 分组过滤
- 修正与撤销数据库
- SQL-DDL撤销与修改
- 撤销基本表
- 指定数据库
- 关闭数据库
重点难点
- SQL-SELECT:In | Not In, Some, all, exists | not exists
- SQL-SELECT:聚集函数,Group By, Having
- 视图及其应用
建立数据库
学生选课数据库SCT
学生表:Student(SNo char(8), Sname char(10), Ssex char(2), Sage integer, DNo, char(2), Sclass char(6))
院系表:Dept(DNo char(2), Dname char(10), Dean char(10))
课程表:Course(CNo char(3), Cname char(12), Chourse integer, Credit float(1), TNo char(3))
教师表:Teacher(TNo char(3), Tname char(10), DNo char(2), Slaary float(2))
选课表:SC(SNo char(8), CNo char(3), Score float(1))
建立数据库包括:定义数据库和表(使用DDL),向表中追加元组(DML),两件事。
DDL:创建database,创建table,定义.约束.View.Index…;删除,drop;更改,Alter。
DML:insert, delete, select, update
DCL:授权/取消授权,Grant, Revoke
DDL通常由DBA使用,也可以DBA授权之后的程序
创建数据库
表达式:
Create database 数据库名
示例:创建SCT
Create database SCT
定义数据库的同时,也可以定义很多物理特性,这里指介绍最简单的定义。然后可以创建表。
表达式:
Create table 表名(列名 数据类型[primary key|unique][Not null][列名 数据类型],...)
定义表的时候,最少要有一列
- Primary Key每个表只能创建一个主键约束
- Unique唯一性约束(候选键),可以有多个
- Not null非空约束。该列不允许出现空值
- 其它
示例:定义学生表Student
Create Student(SNo char(10) not null, Sname char(8), Ssex char(2), Sage integer, DNo char(2), Sclass char(6));
选择元组select
表达式
Select 列名 (子句)From 表明 (子句)Where 检索条件 (子句)
此操作就相当于对一个关系,进行条件查找后再进行投影。
示例:检索学生表中所有学生的信息
Select * From Student;
示例:检索学生表中所有学生的姓名及年龄
Select Sname, SageFrom Student
示例:检索学生表中所有年龄大于等于19岁的学生的年龄及姓名
Select Sage, SnameFrom StudentWhere Sage >= 19;
关键:检索条件书写
not, and, or运算优先级
示例:检索教师表中所有工资少于1500元或者工资大于2000元 并且是03系的教师姓名
Select TnameFrom TeacherWhere (Salary < 1500 or Salary > 2000) and DNo = '03'; #注意 not, and, or 的运算优先级
示例:求或者学过001号课程,或者学过002号课程学生的学号
SelectFrom SCWhere CNo='001' or CNo = '002';
示例:求既学过001课程,又学过002课程学生的学号
Select SNoFrom SC as SC1, SC as SC2Where SC1.SNo=SC2.SNo and SC1.CNo = '001' and SC2.CNo = '002';
#下面的查询结果是空,也就是select语句书写有问题
Select SNo # 检索结果为空From SCWhere CNo='001' and CN0='002'; # 不可能既等于001,又等于002
结果唯一性问题
- 关系模型不允许出现重复的元组,但是现实的DMBS中,允许出现重复元组
- 在Table中,要求无重复元组是通过Primary Key或Unique保证
- 在检索结果中,要求无重复元组,通过DISTINCT保留字实现
示例:在选课表中,检索成绩大于80分的所有学号
Select DISTINCT SNoFrom SCWhere Score > 80;
结果排序问题
在select语句中增加order by子句实现排序功能,asc表示升序,desc表示降序
示例:按学号由小到大的顺序显示出所有学生的学号及姓名
Select SNo, SNameFrom StudentOrder By SNo asc, Sname asc
示例:检索002号课程大于80分的所有同学学号并按成绩由高到低排序
Select SNoFrom SCWhere Score > 80 and DNo = ='002'Order By Score desc;
模糊查询问题
%:匹配0个或多个字符
_:匹配任意单一字符
****:转义字符。比如只想检索百分号,而不是百分号当作通配符使用。
多表查询
多表查询通过连接运算完成,连接运算通过笛卡尔积完成。将连接条件放到Where子句中即可完成。
示例:按“001”号课程成绩由高到低顺序显示所有写上姓名(两表)
Select Student.SNameFrom SC, StudentWhere SC.SNo=Student.SNo and SC.CNo='001'Order By SC.CNo desc;
示例:按‘数据库’课程成绩由高到低顺序显示所有同学姓名(三表)
Select Student.SNameFrom Course, SC, StudentWhere Course.CNo=SC.CNo and SC.SNo=Student.SNo and Course.CName='数据库'Order By SC.Score;
对同一个表进行连接操作:对同一个表,(From子句中)使用不同的别名进行处理
示例:求有薪水差额的任意两位教师
Select T1.TName, T2.TNameFrom Teacher T1, Teacher T2Where T1.Salary > T2.Salary;
示例:求‘001’号课程有成绩差的任意两位同学
Select S1.SName, S2.SNameFrom SC as SC1, SC as SC2, Student as S1, Student as S2Where S1.SNo=SC1.SNo and S2.SNo=SC2.SNo and SC1.Score > SC2.Score
示例:求既学过’001’号课又学过‘002’号课的所有学生的学号
Select S1.SNoFrom SC as S1, SC as S2Where S1.SNo=S2.SNo and S1.CNO='001' and S2.CNo='002';
示例:列出没学过李明老师教授课程的所有同学姓名?
Select Student.SNameFrom Student, Teacher, Course, SCWhere Course.CNo = Teacher.CNo and Course.CNo=SC.CNo and Course.SNo = Student.SNo and (not Teacher.TName='李明')
子查询
为什么需要子查询?
1、集合成员资格查询:某一元素是否是某一个集合成员
2、集合之间比较:两个集合之间进行比较
3、集合基测试:测试集合是否为空、测试集合是否存在重复元组?
子查询:出现在Where子句中的Select语句被称为子查询。子查询返回一个集合。
三种类型子查询:In | Not In , ⊝-Some | ⊝-ALL, Exists | Not Exists
1、【In | Not In】 子查询
表达式 [not] in (子查询)
判断某一表达式的值是否在子查询的结果中
示例:列出张三、王三同学的所有信息
Select * From StudentWhere SName="张三" or SName ="王三";Where SName in ("张三", "王三");
示例:列出选修001号课程的学生的学号和姓名
Select SNo, SNameFrom StudentWhere SNo in (select S# From SC Where CNo = '0001');
示例:求既学过001号课程,又学过002号课程的学生学号
Select SNoFrom SCWhere CNo ='002' and SNo in # 依旧使用SNo进行连接查询(Select SNo From SC Where CNo == '002);
同一种查询多种书写方式:用户书写更灵活。
示例:列出没学过李明老师讲授课程的所有同学的姓名?
错误示范
Select SNameFrom StudentWhere DNo not in(Select DNo From Teacher Where TName = '李明')
正确示范
Select SName From StudentWhere SNo not in (Select SNo From SC, Student, TeacherWhere SC.SNo=Student.SNo and SC.TNo=Teacher.TNo and Teacher.name = '李明');
子查询分为内层查询和外层查询,同样受到定义域约束,将子查询分为两个部分:
(1)非相关子查询:内层查询中,不使用外层查询的变量
(2)相关子查询:内层查询需要依靠外层查询的变量(只能由外层查询向内层传递参数,不能内层向外层传递参数)
2、【⊝-some 和⊝-all】子查询
语法中,⊝表示比较运算符:<, >, >=, <=, =, <>
将表达式的值与子查询的结果进行比较:
(1)与子查询某一个(不是全部)比较下,满足⊝关系,为⊝-some。存在量词
(2)与子查询结果所有值进行比较,全部满足⊝关系,为⊝-all。全称量词
示例:找出工资最低的教师姓名
Select TNameFrom TeacherWhere Salary <= all(Select Salary From Teacher)
示例:找出001号课成绩不是最高的所有学生的学号
Select SNoFrom SCWhere CNo='001' and Score < some(Select Score From SC Where CN0='001);
示例:找出所有课程都不及格的学生姓名
Select SNameFrom StudentWhere SNo = all(. Where 60 > all (Select Score From SC Where SNo = Student.SNo)Select Student.SNo From SC,Student Where SC.SNo=Student.SNo and Score < 60);
示例:找出001号课程成绩最高的所有学生学号
Select SNoFrom SCWhere CNo='001' and Score >= all(Select Score From SC where CNo='001');
示例:找出98030101号同学成绩最低的课程号
Select CNoFrom SCWhere SNo='98030101' and Score <= all(Select Score From SC Where SNo='98030101');
示例:找出张三同学成绩最低的课程号
等价变换:
“表达式= some(子查询)” 和 “表达式 in(子查询)”
“表达式<> all(子查询)” 和 “表达式 not in(子查询)”
3、【Exists | Not Exists】
子查询结果中有无元组存在
追加元组 insert
表达式:
insert into 表明【列名...】values (值...)
【注意】需要值与列一一对应
示例:向Student表中追加元组
insert into Studentvalues('98030101', '张三', '男', '20', '03', '980301');
嵌套子查询
将values换成select…from…where子句
示例:新建立Table:St(SNo, SName),将以“伟”结尾的同学新增到该表中
Insert into St(SNo, SName)Select SNo, SNameFrom StudentWhere SName like '%伟';
【注意】新增元组时,DMBS会检查用户定义的完整性约束条件,如不符合完整性约束条件,则不会执行新增动作。
元组删除命令 delete
表达式:
Delete From 表名 [Where 条件表达式];
如果Where条件省略,则删除所有的元组
Select…From…Where句子很重要
1、当不写Where时,就是删除所有元组
2、Where满足条件的元组
示例:删除SC表中所有元组
Delete From SC;
示例:删除98030101号同学所选的所有课程
Delete From SC Where SNo = '98030101';
示例:【删除自动控制系的所有同学】:嵌套语句,属于子查询部分(属于后面内容)。
Delete From Student Where in(Select DNo From Dep Where Dname='自动控制');
【更复杂的条件删除语句】
示例:删除有四门不及格课程的所有同学
Delete From Student Where SNo in(Select SNo From SC Where Score<60Group by SNo Having Count(*)>=4);
- 此SELECT语句书法语法后面解释
- 当删除元组时,DBMS会检查用户定义的完整性约束条件,如不符合完整性约束条件,则不会执行删除动作
**Select…From…Where…**语句很重要
Update命令
元组更新Update命令:用指定要求的值更新指定表中满足指定条件的元组
Update 表名Set 列名 = 表达式 | (子查询)[[,列名=表达式|(子查询)]][Where 条件表达式];
如果Where 条件省略,则更新所有的元组
示例:将所有教师工资上调5%
Update TeacherSet Sal = Sal + Sal * 0.05;
示例:将所有计算机系教师工资上调10%
Update TeacherSet Sal = Sal + Sal*0.1Where DNo in(Select DNo From Department WHere Dname='计算机');
结果计算
示例:求有差额(差额>0)的任意两位教师的薪水差额
Select T1.TName, T2,TName, T1.Salary-T2.SalaryFrom Teacher as T1, Teacher as T2Where T1.Salary > T2.Salary;
示例:依据学生年龄求学生的出生年份,当前是2015年
Select 2015-SAgeFrom Student;
聚集函数
在Select子句中,使用聚集函数
Count:求个数
sum:求和
avg:求平均
max:求最大值
min:求最小值
示例:求所有教师工资的总额
Select Sum(Salary) From Teacher;
示例:求计算机系教师工资总额
Select sum(Salary)From TeacherWhere TDept = '计算机系';
如何使用一条语句,实现:求每一门课的平均成绩?求每一位学生的平均成绩?
分组查询和分组过滤
分组:将检索到的元组按照某一条件进行分类
在Where子句后面,增加Group By
分组条件可以是:列名1,列名2,…
示例:求每一学生的平均成绩
Select SNo, avg(Score)From SCgroup by SNo; # 根据学生进行分组
示例:求每一门课程的平均成绩
Select CNo, avg(Score)From SCgroup by CNo;
问:如何查找超过两门不及格的同学学号?
Select SNoFrom SCWhere Score < 60 and Count(*)>2 # 错误,因为where子句不能加聚合函数Group by SNo;
分组过滤
对集合(分组)的条件进行过滤。即:满足条件的集合留下,不满足条件的集合删除。
需要先分组,再过滤。所以只能在Group by子句后面。
Select SNoFrom SCWhere Score < 60Group by SNo Having Count(*)>2;
示例:求有10人以上不及格的课程号
Select CNoFrom SCWhere Score < 60Group by CNo having count(*)>10
每一分组检查满足与否的条件要用Having子句
每一行都检查是否满足条件用Where子句
语句执行:from where select group by having
修正与撤销数据库
SQL-DDL撤销与修改
**修正数据库:**修正数据库的定义,主要是修正表的定义
修正基本表的定义
Alter table tablename
ADD {columnname datatype,...}
drop 约束
modify {columnname datatype,...};
示例:在学生表Student(SNp, Sname, Ssex, Sage, DNo, Scalss)基础上,增加两列:Saddr, PID
Alter table Student ADD Saddr char(40), PID char(18);
示例:将上例表中Sname列的数据类型(原char(8))增加两个字符
Alter table Student Modify Sname char(10);
示例:删除学生姓名必须取名唯一值的约束
Alter table Student Drop Unique(Sname);
撤销基本表
drop table 表名;
【注意】
1、Delete...From...是删除表中的元组,并不删除表;Drop table 是撤销基本表,包括表的格式、表中所有元组、由该表导出的视图等相关所有内容。 2、只有Delete From,没有Delete table 3、是Drop tabledrop database 数据库名;
指定数据库
use 数据库名;
关闭数据库
close 数据库名;
99%的人还看了
相似问题
猜你感兴趣
版权申明
本文"【数据库原理及应用教程】第三章 SQL":http://eshow365.cn/6-38878-0.html 内容来自互联网,请自行判断内容的正确性。如有侵权请联系我们,立即删除!