Post

结构化查询语言

Posted
By lreverse
8 min read
结构化查询语言

1. SQL数据定义

SQL基本数据类型

  • 数值型
    • INT：整数，取值范围取决于DBMS实现
    • SMALLINT
    • BIGINT
    • FLOAT(n)：浮点数，精度至少为n位数字
  • 布尔型
    • BOOLEAN
  • 字符串型
    • CHAR(n)：定长字符串，长度为n
    • VARCHAR(n)：变长字符串，最大长度为n
  • 二进制串型
    • BINARY(n)：定长二进制串，长度为n个字节
    • VARBINARY(n)：
  • 日期时间型
    • DATE：日期，格式YYYY-MM-DD
    • TIME：时间，格式HH:MM:SS
  • 时间区间型

创建关系模式

  • 语句：
    1
2
3
4
5
6
7

    CREATE TABLE Student(
  Sno CHAR(6),
  Sname VARCHAR(10),
  Ssex CHAR,
  Sage INT,
  Sdept VARCHAR(20)
);
  • 声明主键
    1
2
3
4
5
6
7
8

    CREATE TABLE Student(
  Sno CHAR(6),
  Sname VARCHAR(10),
  Ssex CHAR,
  Sage INT,
  Sdept VARCHAR(20),
  PRIMARY KEY (Sno)
);
  • 声明外键（一个关系可以有多个外键）
    1
2
3
4
5
6
7

    CREATE TABLE SC(
  Sno CHAR(6)
  Cno CHAR(4)
  Grade INT,
  PRIMARY KEY (Sno, Cno),
  FOREIGN KEY (Sno) REFERENCES Student(Sno)
);
  • 声明用户定义完整性约束
    • 非空：NOT NULL
    • 不重复：UNIQUE
    • 定义缺省值：DEFAULT 缺省值
    • 属性必须满足表达式给出的条件：CHECK (表达式）
      1
2
3
4
5
6
7
8

      CREATE TABLE Student(
  Sno CHAR(6),
  Sname VARCHAR(10) NOT NULL,
  Ssex CHAR NOT NULL CHECK (Ssex IN ('M', 'F')),
  Sage INT DEFAULT 0 CHECK (Sage >= 0),
  Sdept VARCHAR(20),
  PRIMARY KEY (Sno)
);

删除关系

  • 语句：DROP TABLE 关系名1, 关系名2, …, 关系名n
    1

    DROP TABLE 关系名1, 关系名2, ..., 关系名n

修改关系模式

1

ALTER TABLE
  • 修改关系名
    1

    ALTER TABLE Student RENAME TO XUESHENG;
  • 增加、修改、删除属性
    1
2

    ALTER TABLE Student ADD Mno CHAR(6);
ALTER TABLE Student DROP Mno;
  • 增加、删除约束
    1
2
3

    ALTER TABLE Student
ADD CONSTRAINT fk_mon
FOREIGN KEY (Mno) REFERENCES Student(Sno);

    1

    ALTER TABLE Student DROP CONSTRAINT fk_mno

定义视图

创建视图

1
2
3
4

create view CS_Student_on_DB as
	select sno, sname, grade
	from student natural join sc
	where sdept = 'cs' and cno = '3006';

修改视图

1
2
3
4

alter view CS_Student_on_DB as
	select sno, sname, ssex, grade
	from student natural join sc
	where sdept = 'cs' and cno = '3006';

删除视图

1

drop view CS_Student_on_DB;

查询视图

1

select * from CS_Student_on_DB;

视图的作用

  • 一个关系数据库只有一个概念模式，但可以有多个外模式

2. SQL数据更新

插入数据

  • 直接插入元组
    1

    INSERT INTO Student VALUES ('MA-002', 'Cindy', 'F', 19, 'Math');

    1

    INSERT INTO Student (Sno, Sname, Sage, Ssex) VALUES ('MA-002', 'Cindy', 19, 'F');

修改数据

  • 基于本关系的数据修改
    1

    UPDATE student SET sage = 20 where sno = 'MA-002';

删除数据

1

DELETE FROM student where sno = 'MA-002';

3. SQL数据查询

单关系查询

投影查询

1

select [distinct] 属性名列表(或表示式列表) from 关系名;

1

select * from Student;

1

select Sname, (2023 - Sage) as bd from Student;

选择查询

1

select [distinct] 表达式列表 from 关系名 where 选择条件;
  • 选择条件
    • 表达式比较
      • 不等于：!= 或者 <>
    • 范围比较
      • 语法：表达式1 [NOT] between 表达式2 and 表达式3
    • 集合元素判断
      • 语法：表达式1 [not] in (表达式2, ..., 表达式n)
    • 字符串匹配
      • 语法：字符串表达式 [not] like 模式
      • 通配符_：匹配单个字符
      • 通配符%：匹配任意长度的字符串
      • 转移字符\
      • 也可以使用正则表达式
    • 空值判断
      • 语法：属性名 is [not] null
        1

        select Sno, Sname from Student where Sname like 'E___';

        1

        select Sno, Sname from Student where Sname regexp '^[EF].*';

        1

        select Sno from sc where Grade is null;

集合操作

  • 求并：查询语句1 union [all] 查询语句2
  • 求交：查询语句1 intersect 查询语句2
  • 求差：查询语句1 minus/except 查询语句2

排序

  • order by 属性名1[asc|desc], ..., 属性名n[asc|desc]

限制数量

  • limit 结果数量 [offset 偏移量]

聚集查询

  • 聚集函数：count、max、min、sum、avg
    1

    select 聚集函数([distinct] 表达式) from ... where...;
  • 聚集函数不能出现在where子句中

分组查询

1

select cno, count(*), avg(grade) from sc group by cno;
  • 筛选条件having
    1

    select sno, avg(grade) from sc where grade >= 80 group by sno having count(*) >= 2;

连接查询

笛卡尔积

1

select ... from 关系名1, ..., 关系名n;

1

select ... from 关系名1 cross join 关系名2

内连接

1

select ... from 关系名1 [inner] join 关系名2 on 连接条件
  • 当内连接是等值连接，且连接属性同名时，使用using
    1

    select student.sno, sname, cno, grade from student join sc using(sno);

自然连接

1

select ... from 关系名1 natural join 关系名2

外连接

  • 左外连接：关系名1 left [outer] join 关系名2 on 连接条件
  • 右外连接：关系名1 right [outer] join 关系名2 on 连接条件
  • 全外连接：关系名1 full [outer] join 关系名2 on 连接条件
  • 自然外连接：关系名1 natural left|right [outer] join 关系名2 on 连接条件

嵌套查询

  • 查询块：一个select-from-where语句为一个查询块
  • 内层查询块称为子查询
  • 先执行子查询，后执行父查询
  • 类型
    • 不相关子查询：子查询不依赖于外层查询
    • 相关子查询：子查询依赖于外层查询
  • 写法
    1. 表达式 [not] in (子查询)
    2. 表达式 比较运算符 [all|any|some] (子查询)
    • all：当表达式的值与子查询结果中任意的值都满足比较条件时，返回真；否则，返回假
    • any或some：当表达式的值与子查询结果中某个值满足比较条件时，返回真；否则，返回假
      1. [not] exists (子查询)
    • 判断子查询结果是否为空
      1. from (子查询)
    • 将子查询的结果当作关系放在外层查询的from子句中使用，称为派生表
      1. with 临时关系(属性列表) as (子查询)
    • with子句定义的临时关系只在包含with子句的查询中有效
      1
2
3

      with t as 
(select sno, count(*) as cnt from sc group by sno) 
select sno, cnt from t where cnt >= 2;
Course, 数据库系统
This post is licensed under CC BY 4.0 by the author.