SQL语法及用法总结
学生选课数据库SCT:
学生:学号S#,姓名Sname,性别Ssex,年龄Sage,所属系别D#,班级Sclass
Student(S# char(8)),Sname char(10),Ssex char(2),Sage integer,D# char(2),Sclass char(6))
院系:系别D#,系名Dname,系主任Dean
Dept(D# char(2),Dname char(10),Dean char(10))
课程:课号C#,课名Cname,教师编号T#,学时Chours,学分Credit
Course(C# char(3),Cname char(12),Chours integer,Credit float(1),T# char(3))
教师:教师编号T#,教师名Tname,所属院系D#,工资Salary
Teacher(T# char(3),Tname char(10),D# char(2),Salary float(2))
选课:学号S#,课程C#,成绩Score
SC(S# char(8),C# char(3),Score float(1))
建立数据库
定义数据库和表(使用DDL),向表中追加元组(使用DML)
DDL:Data Definition Language
1.创建数据库(DB)–create database
2.创建DB中的Table(定义关系模式)–Create Table
创建Database
数据库(Dstabase)是若干具有相互关联关系的Table/Relation的集合
数据库可以看作是一个集中存放若干个Table的大型文件
create database 的简单语法形式:
create database 数据库名;
创建Table
create table 简单语法形式:
Create table 表名(列名 数据类型【Primary key|Unique】【Not null】【,列名 数据类型 【Not null】,…】);
Eg.定义学生表 Student
create table Student (S# char(8) not null,Sname char(10),Ssex char(2),Sage integer,D# char(2),Sclass char(6));
DML:Data Manipulation Language
1.向Table中追加新的元组:Insert
2.修改Table中某些元组中的某些属性的值:Updata
3.删除Table中的某些元组Delete
4.对Table中的数据进行 各种条件的检索:Select
向表中追加元组
insert into 简单的语法形式:
insert into 表名【(列名【,列名】…】
values(值【,值】,…);
注意:
1.values后面值的排序,须与into子句后面的列名排列一致
2.若表名后的所有列名省略,则values后的值的排序,须与该表存储中的列排列一致。
eg.追加学生表中的元组
insert into Course
values(‘001’,‘数据库’,40,6,‘001’);
insert into Course(Cname ,C#,Credit,Chours,T#)
values(‘001’,‘数据库’,6,40,‘001’);
Select的简洁语法形式:
Select 列名【【,列名】…】
From 表名
【Where 检索条件】;
语义;从表名所给出的表中,查询出满足检索条件的元组,并按给定的列名及顺序进行投影显示。
eg.检索教师表中所有工资少于1500或者工资大于2000元并且是03系的教师姓名
select Tname
from Teacher
where Salary<1500 or Salary>2000 and D#=‘03’;
select Tname
from Teacher
where (Salary<1500 or Salary>2000) and D#=‘03’;
eg.求或者学过001号课程,或者学过002号课程的学生的学号
select S#
from SC
where C#=‘001’ or C#=‘002’;
结果唯一性问题
关系模型不允许出现重复元组,但现实DBMS,却允许出现重复元组,但也允许无重复元组。
在Table中要求无重复元组是通过Primary key 或 Unique 来保证的;
而在检索结果中要求无重复元组,是通过DISTINCT保留字的使用来实现的。
eg.在选课表中,检索成绩大于80分的所有学号
select DISTINCT S#
From SC
Where Score>80;
结果排序问题:
DBMS可以对检索结果进行排序,可以升序排列,也可以降序排列。
select 语句中结果排序是通过增加order by 子句实现的
order by 列名【asc|desc】
意义为检索结果按指定列名进行排序,若后面跟asc或省略,则为升序;若后跟desc,则为降序。
EG.按学号由小到大的顺序显示出所有学生的学号及姓名
select S#,Sname
from Student
order by S#;
EG.检索002号课大于80分的所有同学学号并按成绩由高到低的顺序显示
select S#
from SC
where C#=‘002’ and Score>80
order by Score desc;
模糊查询:
比如检索姓张的学生,检索张某某;这类问题,Select 语句是通过在检索条件中引用运算符like来表示的
含有like运算符的表达式
列名【not】like“字符串”
找出匹配给定字符串,其中给定字符串中可以出现%,_等匹配符。
匹配规则:
% 匹配零个或多个字符
_ 匹配任意单个字符
\ 转义字符,用于去掉一些特殊字符的特定含义,使其被作为普通字符对待,如“%”去匹配字符%,用 \ 去匹配字符
EG.检索所有姓张的学生学号及姓名
select S#,Sname
from Student
where Sname like “张%”;
Θ
−
链
接
之
等
值
连
接
\Theta -链接之等值连接
Θ−链接之等值连接
EG.按’001’号课成绩由高到底顺序显示所有学生的姓名(二表链接)
select Sname
from Student,SC
where SC.S#=Student.S# and SC.C#=‘001’
order by Score DESC;
多表连接时,如两个表的属性名相同,则需采用表名.属性名方式来限定该属性是属于哪个表
EG.按’数据库’课成绩由高到低顺序显示所有同学姓名(三表连接)
select Sname
from Student,SC,Course
where SC.S#=Student.S# and SC.C#=Coures and Course.Cname=‘数据库’
order by Score DESC;
重名处理
连接运算涉及到重名的问题,如两个表中的属性重名,连接的两个表重名(同一表的连接)等,因此需要使用别名以便区别
select 中采用别名的方式
Select 列名 as 列别名【【,列名 as 列别名】…】
From 表名 1 as 表别名 1,表名 2 as 表别名 2,…
Where 检索条件;
上述定义中的 as 可以省略
当定义了别名后,在检索条件中可以使用别名来限定属性
∗ ∗ Θ − 链 接 之 不 等 值 连 接 ∗ ∗ **\Theta -链接之不等值连接** ∗∗Θ−链接之不等值连接∗∗
EG.求有薪水差额的任意两位教师
Select T1.Tname as Teacher1,T2.Tname as Teacher2
from Teacher T1,Teacher T2
Where T1.Salary>T2.Salary;
EG.求年龄有差异的任意两位同学的姓名
Select S1.Sname as Student1,S2.Sname as Student2
from Student S1,Student S2
where S1.Sage>S2.Sage;
EG.求’001’号课程有成绩差的任意两位同学
select sc1.S#,sc2.S#
from SC sc1,SC sc2
where sc1.C#=sc2.C# and C#=‘001’;
EG.求既学过"001"号课又学过"002"号课的所有学生的学号
select sc1.S#
from SC sc1,SC sc2
where sc1.S#=sc2.S# and sc1.C#=‘001’ and sc2.C#=‘002’ ;
批元组新增:
新建Table:St(S#,Sname),将检索到的满足条件的同学新增到该表中
Insert into St(S#,Sname)
select S#,Sname From Student
where Sname like ‘%伟’;
Insert into St(S#,Sname)
Select S#,Sname From Student
Order By Sname;
EG.新建Table:St(S#,Sname,avgScore),将检索到的同学的平均成绩新增到该表中
insert into St(S#,Sname,avgScore)
select S#,Sname,Avg(Score) From Student,SC
Where Student.S#=SC.S#
Group by Student.S#;
元组删除Delete命令:删除满足指定条件的元组
Delete From 表名 【Where 条件表达式】;
如果Where条件省略,则删除所有的元组。
EG.删除SC表中所有元组
Delete from SC;
EG.删除自动控制系的所有同学
Delete from SC where D# in
(Select D# From Dept Where Dname=‘自动控制’ );
EG.删除有四门不及格课程的所有同学
Delete From Student Where S# in
(Select S# From SC where Score<60
Group by S# Having Count(*)>=4);
元组更新Update命令:用指定要求的值更新指定表中满足指定条件的元组的指定列的值
Update 表名
Set 列名=表达式 | (子查询)
【【,列名=表达式 | (子查询)】…】
【Where 条件表达式】;
如果Where 条件省略,则更新所有的元组。
EG.将所有教师工资上调5%
update Teacher
set salary=salary*1.05;
EG.将所有计算机系的教师工资上调5%
update Teacher
set salary=salary*1.1
where D# in (Select D# From Dept Where Dname = ‘计算机’);
EG.当某同学001号课的成绩低于该课程的平均成绩时,将该同学该门课成绩提高5%
update SC
set Score = Score*1.05
where C# =‘001’ and Score<some (Select SVG(Score) From SC Where C#=‘001’);
修正数据库:修正数据库的定义,主要是修正表的定义
修正基本表的定义
alter table tablename
【add{colname datatype,…}】 增加新列
【drop{完整性约束名}】 删除完整性约束
【modify{colname datatype,…}】 修改列定义
SQL语言表达复杂查询 (NOT)IN 子查询
子查询 :出现在Where子句中的Select语句被称为子查询,子查询返回了一个集合,可以通过与这个集合的比较来确定另一个查询集合。
集合成员资格:某一元素是否是某一个集合的成员
集合之间的比较:某一个集合是否包含另一个集合等
集合基数的测试:测试集合是否为空
测试集合是否存在重复元祖
(NOT)IN 子查询
基本语法:
表达式【not】in (子查询)
语法中,表达式的最简单形式就是列名或常数。
语义:判断某一表达式的值是否在子查询的结果中。
EG.列出张三,王三同学的所有信息
select * from Student
where Same in (“张三”,“王三”);
等价于
select * from Student
where Sname=“张三” or Sname=“王三”;
EG.列出选修了001号课程的学生的学号和姓名
select S#,Sname From Student
where S# in (select S# from SC where C#=“001”);
EG.及学过001号课程,又学过002号课程的学生的学号。
select S# from SC
where C#=“001” and S# in (select S#From SC WHERE C#=“002”);
EG.列出没学过李明老师讲授课程的所有同学的姓名
select Sname
from Student
where S# not in (select S#
from Teacher T,SC,Course C
where T.name=“李明” AND SC.S#=C.S# AND T.T#=C.T#);
非相关子查询:内层查询独立进行,没有涉及任何外层查询相关信息的子查询
外层向内层传递参量需要使用外层的表名或表别名来限定
EG.求学过001号课程的同学的姓名
Select Sname
from Student Stu
where S# in(select S#
from SC
where Stu.S#=SC.S# and C#=“001” );
注意:相关子查询只能由外层向内层传递参数,而不能反之;这也称为变量的作用域原则。
Θ
s
o
m
e
/
Θ
a
l
l
子
查
询
\Theta some/ \Theta all子查询
Θsome/Θall子查询
基本语法:
表
达
式
Θ
s
o
m
e
(
子
查
询
)
表达式 \Theta some(子查询)
表达式Θsome(子查询)
表 达 式 Θ a l l ( 子 查 询 ) 表达式 \Theta all(子查询) 表达式Θall(子查询)
语法中
Θ
是
比
较
运
算
符
:
<
,
>
,
>
=
,
<
=
,
=
,
<
>
.
\Theta是比较运算符:<,>,>=,<=,=,<>.
Θ是比较运算符:<,>,>=,<=,=,<>.
语义:将表达式的值与子查询的结果进行比较。
1.如果表达式的值至少与子查询结果的某一个值相比较满足
Θ
\Theta
Θ
关系,则
表
达
式
Θ
s
o
m
e
(
子
查
询
)
表达式 \Theta some(子查询)
表达式Θsome(子查询)
的结果便为真;
2.如果表达式的值与子查询结果的所有值相比较独满足
Θ
\Theta
Θ
关系,则
表
达
式
Θ
a
l
l
(
子
查
询
)
表达式 \Theta all(子查询)
表达式Θall(子查询)
的结果便为真;
EG.找出工资最低的教师姓名
select Tname
from Teacher
where Salary<=all(select salary from Teacher);
EG.找出001号课成绩不是最高的所有学生的学号
select S#
from SC
where C#="001 and Score<some(select Score from SC where C#=“001”);
EG.找出所有课程都不及格的学生姓名(相关子查询)
select Sname
from Student
where 60>all(select Score
from SC
where S#=Student.S# );
EG.找出001号课成绩最高的所有学生的学号
select S#
from C#=“001” and Score>=all(Select Score From Where C#=“001”);
EG.找出217589529号同学成绩最低的课程号
select C# from SC
where S#=“217589529” and Score <=all(Select Score From SC where S#=“217589529”);
EG.找出张三同学成绩最低的课程号
select C# from SC,Student S
where Sname =“张三” and S.S#=SC.S# AND Score <all(Select Score from SC where S#=S.S#);
(NOT)EXISTS
基本语法:
{not}Exists(子查询) 检索 ( 所有的,全部的,至少)
语义:子查询结果中有无元祖存在
EG.检索选修了赵三老师主讲课程的所有同学的姓名。
select DISTINCT Sname FROM Student
where exists(Select * From SC,Course,Teacher where SC.C#=Course and SC.S#=Student.S# and Course.T#=Teacher.T# and Tname=“赵三”);
或者:
select DISTINCT Sname From Student,SC,Course,Teacher
where SC.C#=Course.C# AND SC.S#=Student.S# AND Coures.T#=Teacher.T# and Tname=‘赵三’;
EG.检索学过001号教师主讲的所有课程的所有同学的姓名
Select Sname from Student
where not exists
(Select * from Course
where Course.T#=‘001’ and not exists (select * from SC
where S#=Student.S# and C#=Course.C#));
语义:不存在有一门001号教师主讲的课程该同学没学过。 牛逼
EG.列出没学过李明老师讲授任何一门课的所有同学的姓名。
Select Sname from Student
where not exists
(Select * from Teacher,Course,SC,Teacher
where Tname=‘李明’ and S#=Student.S# andSC.C#=Course.C#));
EG.列出至少学过98030101号同学学过所有课程的同学的学号。
Select DISTINC S# from SC SC1
where not exists #不存在 有一门课 该同学没学过
(Select * from SC SC2
where SC2.S#=‘98030101’ and not exists (select * from SC
where S#=SC1.S# and C#=SC2.C#));
利用SQL语言进行结果计算与聚集计算
select-from-where 语句中,select 子句后面不仅是列名,还可以是一些计算表达式或聚集函数,表明在投影的同时直接进行一些运算
Select 列名 | expr |agfunc(列名) 【【,列名 | expr |agfunc(列名) 】…】
From 表名1【,表名2…】
【where 检索条件】;
expr 可以是常量,列名,或由常量,列名,特殊函数及算数运算符构成的算数运算式
agfunc()是一些聚集函数
EG.求有差额(差额>0)的任意两位教师的薪水差额
select T1.Tname as TR1,T2.Tname as TR2,T1.Salary-T2.Salary
from Teacher T1,Teacher T2
where T1.Salary>T2.Salary;
EG.依据学生年龄求出学生的出生年份,当前是2015年。
select S.S#,S.Sname,2015-S.Sage+1 as Syear
from Student S;
聚集函数
SQL提供五个作用在简单列值集合上的内置聚集函数agfunc,分别是:COUNT ,SUM,AVG,MAX,MIN
求个数 求和 求平均 求最大 求最小
EG.求教师的工资总额
select Sum(Salary) From Teacher;
EG.求计算机教师的工资总额。
select SUM(salary) from Teacher T,Dept
where T.D#=Dept.D# and Dept.Dname=“计算机”;
EG.求数据库课程的平均成绩。
select AVG(Score)
from SC,Course C
where C.C#=SC.C# and C.Cname=“数据库”;
分组查询
SQL可以将检索到的元祖按照某一条件进行分类,具有相同条件值的元祖划分到一个组或一个集合中,同时处理多个组或集合的聚集运算。
分组的基本语法:
Select 列名 | expr |agfunc(列名) 【【,列名 | expr |agfunc(列名) 】…】
From 表名1【,表名2…】
【where 检索条件】
【Group by 分组条件】; 分组条件可以是 列名1,列名2,、、
EG.求每一个学生的平均成绩
Select S#,AVG(Score)
from SC
Group by S#;
EG.求每一门课的平均成绩
Select C#,AVG(Score)
from SC
Group by C#;
聚集函数是不允许用于Where字句中;where子句是对每一个元祖进行条件过滤,而不是对集合进行条件过滤
分组过滤:
若要对集合(即分组)进行条件过滤,即满足条件的集合/分组留下,不满足条件的集合/分组剔除。
Having子句,又称分组过滤子句。需要有Group by 子句支持,换句话说,没有Group by 子句,便不能有Having子句。
Select 列名 | expr |agfunc(列名) 【【,列名 | expr |agfunc(列名) 】…】
From 表名1【,表名2…】
【where 检索条件】
【Group by 分组条件【Having 分组过滤条件】】;
EG.求不及格课程超过两门的同学的学号
select S#
from SC
where Score<60
Group by Having Count(*)>2;
EG.求有10人以上不及格的课程号
Select C# from SC
where Score<60
Group by C# Having Count(*)>10;
Having子句与Where子句表达条件的区别
Where子句:是对每一个元组进行检查.
Having子句:是对每一个分组进行检查.
EG.求两门以上不及格课程同学的学号及其平均成绩
Select S#,AVG(Score)
from SC
where S# in (Select S# from SC
where Score<60
Group by S# Having Count(*)>2);
Group by S#;
SQL语言:并UNION,交INTERSECT,差EXCEPT
基本语法形式:
子查询{Union [ALL] | Intersect[ALL] | Except [ALL] 子查询}
通常情况下自动删除重复元祖,不带ALL。若要保留重复的元组,则要带ALL。
EG.求学过002号课的同学或学过003号课的同学学号
Select S# from SC Where C# =‘002’
UNION
Select S# From SC Where C#=‘003’;
或者如下改写;
Select S# From SC Where C#=‘003’ OR C# =‘002’;
EG.求学过002号课的同学又学过003号课的同学学号
Select S# from SC Where C# =‘002’
intersect
Select S# From SC Where C#=‘003’;
EG.假定所有学生都有选课,求没学过002号课程的学生学号
select DISTINCT S# from SC
EXCEPT
Select S# from SC where C#=‘002’;