SQL语法及用法总结

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 可以省略

​ 当定义了别名后，在检索条件中可以使用别名来限定属性

$$\ast \ast \Theta -链接之不等值连接\ast \ast$$

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” );

注意：相关子查询只能由外层向内层传递参数，而不能反之；这也称为变量的作用域原则。

$$\Theta some/\Theta all子查询$$
基本语法：

​
$$表达式\Theta some(子查询)$$

$$表达式\Theta all(子查询)$$

语法中

​
$$\Theta 是比较运算符：<,>,>=,<=,=,<>.$$
语义：将表达式的值与子查询的结果进行比较。

1.如果表达式的值至少与子查询结果的某一个值相比较满足
$$\Theta$$
关系，则
$$表达式\Theta some(子查询)$$
的结果便为真；

2.如果表达式的值与子查询结果的所有值相比较独满足
$$\Theta$$
关系，则
$$表达式\Theta 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’;