SQL 基础语法

一、SQL 概述

功能概述

  • SQL 语言是集 DDL、DML 和 DCL 于一体的数据库语言
  • SQL 语言主要由以下 9 个单词引导的操作语句来构成，但每一种语句都能表达复杂的操作请求
  • DDL 语句引导词：Create（建立）, Alter （修改）, Drop（撤消）
    • 模式的定义和删除，包括定义 Database, Table, View, Index, 完整性约束条件等，也包括定义对象 (RowType 行对象，Type 列对象）
  • DML 语句引导词：Insert , Delete, Update, Select
    • 各种方式的更新与检索操作，如直接输入记录，从其他 Table（由 SubQuery 建立）输入
    • 各种复杂条件的检索，如连接查找，模糊查找，分组查找，嵌套查找等
    • 各种聚集操作，求平均、求和、…等，分组聚集，分组过滤等
  • DCL 语句引导词：Grant, Revoke
    • 安全性控制：授权和撤消授权

1. 创建数据库

1
create database 数据库名;

后面的示例中的数据库有以下关系模式：

  • 学生：学号 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) )

创建表

1
2
Create table 表名( 列名 数据类型 [Primary key |Unique] [Not null]
[, 列名 数据类型 [Not null] , … ]) ;
  • Primary key: 主键约束。每个表只能创建一个主键约束。
  • Unique: 唯一性约束（即候选键）。可以有多个唯一性约束。
  • Not null: 非空约束。是指该列是否允许出现空值。
  • 语法中的数据类型在 SQL 标准中有定义。

向表中追加元组

1
2
insert into 表名[ (列名 [, 列名 ]… ]
values (值 [, 值] , …) ;
  • values 后面值的排列，须与 into 子句后面的列名排列一致
  • 若表名后的所有列名省略， 则 values 后的值的排列，须与该表存储中的列名排列一致

2. 简单单表查询

1
2
3
Select 列名 [[, 列名] … ]
From 表名
[ Where 检索条件 ];
  • 检索条件的逻辑运算符用 and, or, not 来表示，注意运算符的优先次序及括弧的使用。

例 1：检索教师表中所有工资少于 1500 元或者工资大于 2000 元并且是 03 系的教师姓名

1
2
3
Select Tname
From Teacher
Where (Salary < 1500 or Salary > 2000) and D# = ’03’;

例 2：求或者学过 001 号课程，或者学过 002 号课程的学生的学号

1
2
3
Select S#
From SC
Where C# = '001' OR C#='002';

例 3：求既学过 001 号课程，又学过 002 号课程的学生的学号

1
-- 见多表联合查询等值连接例3

例 4：在选课表中，检索成绩大于 80 分的所有学号

查询结果去重

1
2
3
4
-- DISTINCT 去重
Select DISTINCT S#
From SC
Where Score > 80;

查询结果排序

1
order by 列名 [asc | desc]

例 1：按学号由小到大的顺序显示出所有学生的学号及姓名

1
2
3
Select S#, Sname
From Student
Order By S# ASC ;

例 2：检索 002 号课大于 80 分的所有同学学号并按成绩由高到低顺序显示

1
2
Select S# From SC Where C# = ‘002’ and Score > 80
Order By Score DESC ;

模糊查询

1
列名 [not ] like '字符串'
  • 找出匹配给定字符串的字符串。其中给定字符串中可以出现匹配符。
  • 匹配规则：
    • %匹配零个或多个字符
    • _ 匹配任意单个字符
    • \ 转义字符，去掉特殊字符的特定含义，如用\%去匹配字符%，用\_去匹配字符_

例 1：检索所有姓张的学生学号及姓名

1
2
3
Select S#, Sname
From Student
Where Sname Like '张%' ;

例 2：检索名字为张某某的所有同学姓名

1
2
3
Select Sname
From Student
Where Sname Like '张_ _';

例 3：检索名字不姓张的所有同学姓名

1
2
3
Select Sname
From Student
Where Sname Not Like '张%';

3. 多表联合查询

1
2
3
Select 列名 [ [, 列名] … ]
From 表名1, 表名2, …
Where 检索条件 ;
  • 检索条件中要包含连接条件，通过不同的连接条件可以实现等值连接、不等值连接及各种θ- 连接

θ- 连接之等值连接

例 1：按“001”号课成绩由高到低顺序显示所有学生的姓名（二表连接）

1
2
3
Select Sname From Student, SC
Where Student.S# = SC.S# and SC.C# = ‘001’
Order By Score DESC;

例 2：按“数据库”课成绩由高到低顺序显示所有同学姓名（三表连接）

1
2
3
4
Select Sname From Student, SC, Course
Where Student.S# = SC.S# and SC.C# = Course.C#
and Cname = '数据库'
Order By Score DESC;

表更名与表别名

1
2
3
Select 列名 as 列别名 [ [, 列名 as 列别名] … ]
From 表名1 as 表别名1, 表名2 as 表别名2, …
Where 检索条件 ;
  • 上述定义中的 as 可以省略
  • 当定义了别名后，在检索条件中可以使用别名来限定属性

不等值连接

例 1：求有薪水差额的任意两位教师

1
2
3
Select T1.Tname as Teacher1, T2.Tname as Teacher2
From Teacher T1, Teacher T2
Where T1.Salary > T2.Salary ;

例 2：求年龄有差异的任意两位同学的姓名

1
2
3
Select S1.Sname as Stud1, S2.Sname as Stud2
From Student S1, Student S2
Where S1.Sage > S2.Sage ;

综合训练

例 1：求既学过“001”号课又学过“002”号课的所有学生的学号

1
2
3
Select S1.S# From SC S1, SC S2
Where S1.S# = S2.S# and S1.C#='001'
and S2.C#='002';

例 2：求“001”号课成绩比“002”号课成绩高的所有学生的学号

1
2
3
Select S1.S# From SC S1, SC S2
Where S1.S# = S2.S# and S1.C#='001'
and S2.C#='002' and S1.Score > S2.Score;

例 3：列出没学过李明老师讲授课程的所有同学的姓名

1
-- 见后面

4. 增删改的操作

增加（Insert）

1
2
3
4
5
6
7
8
-- 单一元组新增：插入一条指定元组值的元组
insert into 表名[(列名[，列名]…)]
values (值[，值]…);

-- 批数据新增命令形式：插入子查询结果中的若干条元组。待插入的元组由子查询给出。

insert into 表名[(列名[，列名]…)]
子查询;

例 1：新建 Table: St(S#, Sname), 将检索到的满足条件的同学新增到该表中

1
2
3
4
Insert Into St (S#, Sname)
Select S#, Sname
From Student
Where Sname like '%伟' ;

例 2：新建 Table: SCt(S#, C#, Score), 将检索到的成绩及格同学的记录新增到该表中

1
2
3
4
Insert Into SCt (S#, C#, Score)
Select S#, C#, Score
From SC
Where Score>=60 ;

例 3：新建 Table: St(S#, Sname, avgScore), 将检索到的同学的平均成绩新增到该表中

1
2
3
4
5
Insert Into St (S#, Sname, avgScore)
Select S#, Sname, Avg(Score)
From Student, SC
Where Student.S# = SC.S#
Group by Student.S# ;

删除（Delete）

1
Delete From 表名[ Where 条件表达式] ;
  • 如果 Where 条件省略，则删除所有的元组。

例 1：删除 98030101 号同学所选的所有课程

1
Delete From SC Where S# = '98030101' ;

例 2：删除自动控制系的所有同学

1
2
3
Delete From Student
Where D# In
(Select D# From Dept Where Dname = '自动控制');

例 3：删除有四门不及格课程的所有同学

1
2
3
4
5
6
7
Delete From Student
Where S# in (
    Select S#
    From SC
    Where Score < 60
    Group by S#
    Having Count(*)>= 4);

更改（Update）

1
2
3
4
Update 表名
Set 列名= 表达式| (子查询)
[ [ , 列名= 表达式| (子查询) ] … ]
[ Where 条件表达式] ;
  • 如果 Where 条件省略，则更新所有的元组。

例 1：将所有教师工资上调 5%

1
2
Update Teacher
Set Salary = Salary * 1.05 ;

例 2：将所有计算机系的教师工资上调 10%

1
2
3
4
5
6
Update Teacher
Set Salary = Salary * 1.1
Where D# in (
    Select D#
    From Dept
    Where Dname = '计算机');

例 4：当某同学 001 号课的成绩低于该课程平均成绩时，将该同学该门课成绩提高 5%

1
2
3
4
5
6
Update SC
Set Score = Score * 1.05
Where C# = '001' and Score < some(
    Select AVG(Score)
    From SC
    Where C# = '001') ;

例 5：将张三同学 001 号课的成绩置为其班级该门课的平均成绩

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Update SC
Set Score = (
    Select AVG(SC2.Score)
    From SC SC1, Student S1, SC SC2, Student S2
    Where S1.Sclass = S2.Sclass
        and SC1.S# = S1.S#
        and SC2.S# = S2.S#
        and S1.Sname='张三'
        and SC1.C# = '001'
        and SC1.C# = SC2.C#)
Where C# = ‘001’ and S# in (
    Select S#
    From Student
    Where Sname = ‘张三’ );

5. 修正和撤销数据库

修正基本表的定义

1
2
3
4
alter table tablename
[add {colname datatype, …}] 增加新列
[drop {完整性约束名}] 删除完整性约束
[modify {colname datatype, …}]

例 1：在学生表 Student(S#,Sname,Ssex,Sage,D#,Sclass) 基础上增加二列 Saddr, PID

1
Alter Table Student Add Saddr char[40], PID char[18] ;

例 2：将上例表中 Sname 列的数据类型增加两个字符

1
Alter Table Student Modify Sname char(10) ;

例 3：删除学生姓名必须取唯一值的约束

1
Alter Table Student Drop Unique( Sname );

撤销基本表

1
drop table 表名;
  • delete语句只是删除表中的元组，而drop table的操作是撤消包含表格式、表中所有元组、由该表导出的视图等相关的所有内容。

数据库操作

1
2
3
4
5
6
7
8
-- 撤销数据库
drop database 数据库名;

-- 使用当前数据库
use 数据库名;

-- 关闭当前数据库
close 数据库名;

二、复杂查询

1.（NOT）IN 子查询

1
表达式 [not ] in (子查询);

例 1：列出张三、王三同学的所有信息

1
2
3
Select *
From Student
Where Sname in (“张三”, “王三”);

例 2：列出选修了 001 号课程的学生的学号和姓名

1
2
3
4
5
6
Select S#, Sname
From Student
Where S# in (
    Select S#
    From SC
    Where C# = ‘001’ );

例 3：求既学过 001 号课程，又学过 002 号课程的学生的学号

1
2
3
4
5
6
7
Select S#
From SC
Where C# = '001'
    and S# in (
            Select S#
            From SC
            Where C# = '002') ;

例 4：列出没学过李明老师讲授课程的所有同学的姓名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Select Sname
From Student
Where S# not in (
            Select S#
            From SC, Course C, Teacher T
            Where T.Tname = '李明'
                and SC.C# = C.C#
                and T.T# = C.T# );

-- 错误写法
Select Sname
From Student S, SC, Course C, Teacher T
Where T.Tname <> '李明'
    and C.C# = SC.C#
    and SC.S# = S.S#
    and T.T# = C.T#;
  • 非相关子查询：内层查询独立进行，没有涉及任何外层查询相关信息的子查询。
  • 相关子查询：内层查询需要依靠外层查询的某些参量作为限定条件才能进行的子查询
    • 外层向内层传递的参量需要使用外层的表名或表别名来限定

例 5：求学过 001 号课程的同学的姓名

1
2
3
4
5
6
Select Sname
From Student Stud
Where S# in (
        Select S#
        From SC
        Where S# = Stud.S# and C# = '001');

2. θ-Some 与 θ-All 子查询

1
2
表达式 θ some (子查询)
表达式 θ all (子查询)
  • θ 是比较运算符： < , > , >= , <= , = , <>。
  • 如果表达式的值至少与子查询结果的某一个值相比较满足 θ 关系，则表达式 θ some (子查询)的结果便为真；
  • 如果表达式的值与子查询结果的所有值相比较都满足 θ 关系，则表达式 θ all (子查询)的结果便为真。

例 1：找出工资最低的教师姓名

1
2
3
4
5
Select Tname
From Teacher
Where Salary <= all (
                Select Salary
                From Teacher);

例 2：找出 001 号课成绩不是最高的所有学生的学号

1
2
3
4
5
6
7
Select S#
From SC
Where C# = '001'
        and Score < some (
                    Select Score
                    From SC
                    Where C# = '001');

例 3：找出所有课程都不及格的学生姓名（相关子查询）

1
2
3
4
5
6
Select Sname
From Student
Where 60 > all (
            Select Score
            From SC
            Where S# = Student.S#);

例 4：找出 001 号课成绩最高的所有学生的学号

1
2
3
4
5
6
7
Select S#
From SC
Where C# = '001'
    and Score >= all (
                Select Score
                From SC
                Where C# = '001');

例 5：找出 98030101 号同学成绩最低的课程号

1
2
3
4
5
6
7
Select C#
From SC
Where S# = '98030101'
    and Score <= all (
                Select Score
                From SC
                Where S# = '98030101');

例 6：找出张三同学成绩最低的课程号

1
2
3
4
5
6
7
Select C# From SC, Student S
Where Sname = '张三'
    and S.S#=SC.S#
    and Score <= all (
                Select Score
                From SC
                Where S#=S.S# );

等价关系

  • 表达式 in (子查询)等价于表达式 = some (子查询)；
  • 表达式 not in (子查询)等价于表达式 <> all (子查询)，**而不是表达式 <> some (子查询)**。

3.（NOT）EXISTS 子查询

1
[not] Exists (子查询);
  • 语义：子查询结果中有无元组存在。
  • 不加not形式的Exists谓词可以不用。

例 1：检索学过 001 号教师主讲的所有课程的所有同学的姓名

1
2
3
4
5
6
7
8
9
10
Select Sname From Student
Where not exists ( -- 不存在
        Select * -- 有一门001教师主讲课程
        From Course
        Where Course.T# = '001'
            and not exists ( -- 该同学没学过
                    Select *
                    From SC
                    Where S# = Student.S#
                        and C# = Course.C#));

例 2：列出没学过李明老师讲授任何一门课程的所有同学的姓名

1
2
3
4
5
6
7
8
9
Select Sname
From Student
Where not exists (
        Select *
        From Course, SC, Teacher
        Where Tname = '李明'
            and Course.T# = Teacher.T#
            and Course.C# = SC.C#
            and S# = Student.S# );

例 3：列出至少学过 98030101 号同学学过所有课程的同学的学号

1
2
3
4
5
6
7
8
9
10
11
Select DISTINC S#
From SC SC1
Where not exists (
        Select *
        From SC SC2
        Where SC2.S# = '98030101'
            and not exists (
                    Select *
                    From SC
                    Where C# = SC2.C#
                        and S# = SC1.S#));

例 4：已知 SPJ(Sno, Pno, Jno, Qty), 其中 Sno 供应商号，Pno 零件号，Jno 工程号，Qty 数量，列出至少用了供应商 S1 供应的全部零件的工程号

1
2
3
4
5
6
7
8
9
10
11
Select DISTINCT Jno
From SPJ SPJ1
Where not exists (
        Select *
        From SPJ SPJ2
        Where SPJ2.Sno = 'S1'
            and not exists (
                    Select *
                    From SPJ SPJ3
                    Where SPJ3.Pno = SPJ2.Pno
                        and SPJ3.Jno = SPJ1.Jno ) );

4. 结果计算

1
2
3
Select 列名 | expr | agfunc(列名) [[, 列名 | expr | agfunc(列名) ] … ]
From 表名1 [, 表名2 … ]
[ Where 检索条件 ] ;
  • expr 可以是常量、列名、或由常量、列名、特殊函数及算术运算符构成的算术运算式。
  • agfunc() 是一些聚集函数。

例 1：求有差额（差额》0) 的任意两位教师的薪水差额

1
2
3
Select T1.Tname as TR1, T2.Tname as TR2, T1.Salary – T2.Salary
From Teacher T1, Teacher T2
Where T1.Salary > T2.Salary;

例 2：依据学生年龄求学生的出生年份，当前是 2022 年

1
2
Select S.S#, S.Sname, 2022 – S.Sage+1 as Syear
From Student S;

聚集计算

SQL 提供了五个作用在简单列值集合上的内置聚集函数 agfunc, 分别是：COUNT、SUM、AVG、MAX、MIN。

  • 聚集函数是不允许用于 Where 子句中的：
    Where 子句是对每一元组进行条件过滤，而不是对集合进行条件过滤

例 1：求教师的工资总额

1
Select Sum(Salary) From Teacher;

例 2：求计算机系教师的工资总额

1
2
Select Sum(Salary) From Teacher T, Dept
Where Dept.Dname = '计算机' and Dept.D# = T.D#;

例 3：求数据库课程的平均成绩

1
2
Select AVG(Score) From Course C, SC
Where C.Cname = '数据库' and C.C# = SC.C#;

5. 分组查询

SQL 可以将检索到的元组按照某一条件进行分类，具有相同条件值的元组划到一个组或一个集合中，同时处理多个组或集合的聚集运算。

1
2
3
4
Select 列名 | expr | agfunc(列名) [[, 列名 | expr | agfunc(列名) ] … ]
From 表名1 [, 表名2 … ]
[ Where 检索条件 ]
[ Group by 分组条件 ];
  • 分组条件可以是列名1, 列名2, ...

例 1：求每一个学生的平均成绩

1
2
Select S#, AVG(Score) From SC
Group by S#;

例 2：求每一门课程的平均成绩

1
2
Select C#, AVG(Score) From SC
Group by C#;

分组过滤

若要对集合（即分组）进行条件过滤，即满足条件的集合 / 分组留下，不满足条件的集合 / 分组剔除。

1
2
3
4
Select 列名 | expr | agfunc(列名) [[, 列名 | expr | agfunc(列名) ] … ]
From 表名1 [, 表名2 … ]
[ Where 检索条件 ]
[ Group by 分组条件 [ Having 分组过滤条件] ] ;
  • 没有 Group by 子句，便不能有 Having 子句。

例 1：求不及格课程超过两门的同学的学号

1
2
3
4
5
Select S#
From SC
Where Score < 60
Group by S#
Having Count(*)>2;

例 2：求有 10 人以上不及格的课程号

1
2
3
4
5
Select C#
From SC
Where Score < 60
Group by C#
Having Count(*)>10;

例 3：

1
2
3
4
5
6
7
8
9
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#;

6. 关系代数操作