数据库(二) SQL语言

本文最后更新于：2023年2月28日 下午

1. SQL概述

语言组成

结构化查询语言 (Structured Query Language, SQL) 大小写不敏感，有以下部分：

  • 数据定义语言 (Data Definition Language, DDL)

    创建、修改或删除数据库对象 (表、视图、模式、触发器、域、存储过程)

    TABLE, VIEW, SCHEMA, INDEX, TRIGGER, DOMAIN, PROCEDURE

    CREATE, ALTER, DROP

  • 数据查询语言 (Data Query Language, DQL)

    SELECT

  • 数据操纵语言 (Data Manipulation Language, DML)

    INSERT, DELETE, UPDATE

  • 数据控制语言 (Data Control Language, DCL)

    GRANT, DENY, REVOKE

  • 其他语言要素 (additional language elements)

    事务控制 COMMIT, ROLLBACK, SET TRANSACTION

    程序化SQL DECLAREEXPLAINLAN, OPEN, FETCH, CLOSE, PREPARE, EXECUTE, DESCRIBE

语句结构

一条SQL语句由多个子句构成 ，每个子句由一个关键字开始

2. 数据类型

字符型数据

数值型数据

日期型数据

3. DDL

数据库

1
2
3
4
5
/* 创建 */


/* 删除 */
DROP DATABASE

表

结构

表的行列结构

创建与约束

1
2
3
4
5
6
CREATE TABLE <table_name>
(
	<column_name1> data_type,
    <column_name2> data_type,
    ... ...
);

  1. 主键约束

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    /* 列级约束,作用于一列 */
    CREATE TABLE Course
    (
        Cno number(4) PRIMARY KEY,
    );
    CREATE TABLE Course
    (
        Cno number(4) CONSTRAINT <pk_name> PRIMARY KEY,
    );
    /* 表级约束,可作用于多列 */
    CREATE TABLE Course
    (
        Cno number(4),
        Class number(1),
        CONSTRAINT <pk_name> PRIMARY KEY (Cno,Class)
    );

  2. 外键约束

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    /* 自参照,外码约束指向本关系 */
    /* 课程表Course，由课程号Cno、课程名Cname、先行课程号Ccpno、学分Ccredit四个属性组成 */
    CREATE TABLE Course
    (
        Cno number(4) PRIMARY KEY,
    	Cname char(20),
    	Cpno number (4) CONSTRAINT <fk_name> REFERENCES Course(Cno),
    	Ccredit number(4)
    );
    /* 表级约束 */
    CREATE TABLE SC
    (
        Sno number(12),
    	Cno number(4),
    	Grade number(3),
    	CONSTRAINT pk_SC PRIMARY KEY (Sno,Cno),
    	CONSTRAINT fk_c FOREIGN KEY (Cno) REFERENCES Course(Cno)
    );

  3. 非空约束

    1
    2
    /* 只有列级约束 */
    Cname char(20) NOT NULL,

    缺省约束

    1
    2
    /* 只有列级约束 */
    Ssex char(2) DEFAULT ('男'),

  4. 唯一约束

    1
    2
    3
    4
    /* 列级约束 */
    Cname char(20) Constraint <u_name> UNIQUE,
    /* 表级约束 */
    CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName),

  5. 检查性约束

    1
    2
    3
    4
    /* 列级约束 */
    Grade number(3) CONSTRAINT <chk_name> check(Grade>=0 AND Grade<=100),  
    /* 表级约束 */
    CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

修改、增删与重命名

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
/* 基于列的操作，前提句 */
ALTER TABLE <table_name>

/* 增加列、增加约束 */
/* 1. 增加列 */
ADD <column_name> <data_type> [integrity constraint];
ADD Email char(15) NOT NULL DEFAULT 'No Email';
ADD (S_entrance date, S_sourse char(20));
/* 2. 增加约束 */
ADD CONSTRAINT < constraint_name > [integrity constraint];
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);

/* 删除列、增加约束 */
/* 1. 删除列(作为主键/外键/设有默认值的列不可删除) */
DROP COLUMN <column_name>;
DROP (S_entrance,S_sourse); /* 删除多列，省略COLUMN */
/* 2. 删除约束(若约束未命名可从报错信息中获得默认约束名) */
DROP CONSTRAINT <constraint_name>;
DROP UNIQUE(Sname);

/* MODIFY关键字/ALTER COLUMN关键字(SQL Server) */
/* 改变列的结构(数据类型、约束) */
MODIFY <column_name> <data_type> [integrity_constraint];
MODIFY sno Char(5) NOT NULL;
1
2
3
4
5
6
7
8
/* 基于表的操作 */
/* 1. 删除 */
DROP TABLE <table_name>;

/* 2. 重命名表/表中指定列*/
/* RENAME关键字(db2/MySQL/Oracle)/SP_RENAME关键字(SQL Server/Sybase) */
RENAME '<old_table_name>', '<new_table_name>';
RENAME '<table_name.old_column_name>', '<new_column_name>', 'COLUMN';

索引

索引是为了提高数据库检索效率，避免逐行遍历，可快速定位数据，以存储空间为代价的数据结构。

索引的结构

  • 簇索引 (clustered index)

    簇索引具有与表的数据结合的结构，其叶节点中存储的是实际的数据，对表的某一字段建立簇索引将对表数据重新排列储存，因此表中只能有一个簇索引。

  • 非簇索引 (nonclustered index)

    非簇索引具有与表的数据分离的结构，其叶节点中存储的是行定位器（指向数据行的指针）。

1
2
3
4
5
6
7
8
9
10
11
12
/* 索引的创建 */
/* 
UNIQUE: 惟一性索引，每个索引只允许与表中的唯一行相关
CLUSTER: 簇索引/非簇索引关键字(在不同的DBMS中关键字不同) 
ASC/DESC: ASC升序/AESC降序
*/
CREATE [UNIQUE | CLUSTER] INDEX <index_name>
ON <table_name> (<column_name> [ASC | DESC][, <column_name> [ASC | DESC ]]...); /* 多列及多字段簇索引 */


/* 索引的销毁 */
DROP INDEX <index_name>

视图

视图是结构和数据建立在对表的查询基础上的虚表。可以限制用户访问数据、简化操作。

1
2
3
4
5
6
7
8
9
/* 视图的创建 */
CREATE VIEW <view_name> [(column1, column2, ...)]
AS
SELECT <column_names>
FROM <table_name>
...

/* 视图的销毁 */
DROP VIEW <viw_name>

4. DQL

基本语句

语句结构

select语句结构

执行步骤

graph LR;
	FROM --创建并连接表---> WHERE --条件筛选---> 1[GROUP BY] --分组---> HAVING --条件筛选---> SELET --选择字段---> 2[ORDER BY] --排序---> DBMS;
	2 --排序--> 嵌入式SQL

SELECT子句

作用：选取字段

1
2
3
/* 可选关键字 */
/* DISTICT: 去除查询结果中的重复信息(对查询结果进行排序后去重,费时) */
SELECT DISTINCT <select_list>
1
2
3
4
5
6
7
/* 1. 单列查询 */
SELECT <column>
/* 2. 多列查询 */
/* 查询所有列 */
SELECT *
/* 查询多列 */
SELECT <column1>,<column2>,...

连接符

别名

考虑到语句的执行步骤，SELECT 定义的别名不能在 WHERE/GROUP BY/HAVING 中使用，能在 ORDER BY 中使用。

FROM子句

作用：创建并连接表

1
2
3
4
5
6
7
/* 单表 */
FROM <table>
/* 多表 */
FROM <table1>,<table2>,... /* 笛卡尔积连接 */
/* 别名 */
FROM <table> AS <table_alias>
FROM <table> <table_alias>

ORDER BY子句

作用：排序

1
2
3
4
/* 可选关键字 */
/* ASC: 升序,默认 */
/* DESC: 降序 */
ORDER BY <column1> DESC,<column2>
1
2
3
4
5
6
/* 单列排序 */
ORDER BY <column>
/* 多列排序 */
ORDER BY <column1>,<column2>,... /* 顺序决定优先级 */
/* 按序号排序 */
ORDER BY 1,2... /* ORDER BY col1,col2 */

WHERE子句

作用：条件筛选

运算符：

  • NOT
  • BETWEEN AND
  • IN
  • IS NULL
  • AND/OR
  • LIKE

单值条件

WHERE子句的单值比较运算符

范围条件

1
2
3
4
/* 数值范围 */
WHERE <col> BETWEEN <low_int> AND <up_int>
/* 列表范围 */
WHERE <col> IN (<col_value1>,<col_value2>,...)

NULL值

1
2
3
WHERE <col> IS NULL /* 筛选空项 */
WHERE <col> IS NOT NULL /* 筛选非空项 */
/* NOT运算符可对其后的表达式条件取反 */

组合条件

1
2
3
4
/* 通过布尔运算符AND/OR组合多个条件表达式 */
/* 同时运用时AND优先级高 */
WHERE <单值比较条件1>
AND <单值比较条件2>

模糊查询

根据关键词搜索相关信息，通过通配符 (%/_/[]/*) 实现，只能作用于字符型数据 (CHAR/VARCHAR/TEXT) 。

  • %

    表示匹配位置的通配符

    1. 开头匹配

      1
      WHERE cname LIKE '计算机%' /* 匹配以'计算机'开头的课程名 */

    2. 结尾匹配

      1
      WHERE cname LIKE '%基础' /* 匹配以'基础'结尾的课程名 */

    3. 开头/结尾同时匹配

      1
      WHERE cname LIKE '计算机%基础' /* 匹配以'计算机'开头,以'基础'结尾的课程名 */

  • _

    匹配任意字符的占位符

    1
    2
    3
    WHERE cname LIKE '_____' /* 匹配5个字的课程名 */
    WHERE cname LIKE '计算机__' /* 匹配以'计算机'开头,5个字的课程名 */
    WHERE cname LIKE '%软件__' /* 匹配倒数第3、4个字为'软件'的课程名 */

  • []

    指定一系列字符

    组合通配符及其匹配条件

  • ^

    不含指定字符

    1
    WHERE cname LIKE '[^计生]%' /* 匹配不以'计'或'生'开头的课程名 */

  • ESCAPE 转义关键字

    1
    2
    WHERE <col> LIKE '%M%' ESCAPE 'M' /* 定义'M'为转义符,其后的第一个字符'%'转义为实际值。匹配以'%'结尾的字符串 */
    WHERE <col> LIKE 'ab&_%' ESCAPE '&' /* 定义'&'为转义符,其后的第一个字符'_'转义为实际值。匹配以'ab_'开头的字符串 */

聚合分析

聚合函数

聚合函数及其功能

多表查询

graph LR;
	0[SQL_join] --- 1.1[内连接Inner_join] --- 2.1[条件连接Non-Equal_join];
	1.1 --- 2.2[等值连接Equal_join];
	0 --- 1.2[自连接Self_join];
	0 --- 1.3[交叉连接/笛卡尔积Cross_join];
	0 --- 1.4[外连接Outer_join] --- 2.3[左外连接];
	1.4 --- 2.4[右外连接]
	1.4 --- 2.5[全外连接]

语句结构

1
2
3
4
5
/* JOIN关键字 */
SELECT <column>
FROM <join_table1>
<JOIN_TYPE> <join_table2>
ON <join_condition>

例表-student

例表-teacher 例表-course

内连接

内连接返回与连接条件匹配的数据行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
/* 1. JOIN关键字 */
SELECT tname, dname, cname, ctest, T.cno, C.cno
FROM Teacher T 
JOIN Course C
ON T.cno = C.cno

SELECT tname, dname, cname, ctest
FROM Teacher T 
INNER JOIN Course C
ON T.cno = C.cno

/* 2. WHERE子句 */
SELECT tname, dname, cname, ctest
FROM Teacher AS T, Course AS C
WHERE T.cno = C.cno

外连接

外连接返回与连接条件匹配的及其它指定数据行。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
/* 左外连接 = 内连接 + 左表失配元组 */
/* 1. JOIN关键字 */
SELECT S.sno, S.cno, sname, mark, cname, ctest, C.cno
FROM Student S
LEFT OUTER JOIN Course C
ON S.cno = C.cno
/* 2. WHERE子句 */
SELECT S.sno, S.cno, sname, mark, cname, ctest, C.cno
FROM Student S, Course C
WHERE S.cno *= C.cno

/* 右外连接 = 内连接 + 右表失配元组 */
/* 1. JOIN关键字 */
SELECT S.sno, S.cno, sname, mark, cname, ctest, C.cno
FROM Student S
RIGHT OUTER JOIN Course C
ON S.cno = C.cno
/* 2. WHERE子句 */
SELECT S.sno, S.cno, sname, mark, cname, ctest, C.cno
FROM Student S, Course C
WHERE S.cno =* C.cno

/* 全外连接 = 内连接 + 左表失配元组 + 右表失配元组 */
/* 1. JOIN关键字 */
SELECT S.sno, S.cno, sname, mark, cname, ctest, C.cno
FROM Student S
FULL OUTER JOIN Course C
ON S.cno = C.cno
/* 2. WHERE子句 */
SELECT S.sno, S.cno, sname, mark, cname, ctest, C.cno
FROM Student S, Course C
WHERE S.cno = C.cno

自连接

自连接的运用 -- 先行课 
1
2
3
SELECT Fir.Cno, Sec.Cpno
FROM Course Fir, Course Sec
WHERE Fir.Cno = Sec.Cpno

交叉连接

笛卡尔积.

1
2
3
4
5
6
7
8
9
10
11
12
/* 不指定连接方式时的多表查询为交叉连接 */
/* 根据所有同学的及格课程查询其学号、姓名、课程代码、课程名称和成绩信息 */
SELECT S.cno, sno, sname, mark, C.cno, cname
FROM Student S, Course C
WHERE S.cno = C.cno
AND mark >= 60

SELECT S.cno, sno, sname, mark, C.cno, cname
FROM Student S
CROSS JOIN Course C
WHERE S.cno = C.cno
AND mark >= 60

多表连接

1
2
3
4
5
6
SELECT S.cno, sname, mark, T.cno, tname, C.cno, cname
FROM Student S
JOIN Course C
ON S.cno = C.cno
JOIN Teacher T
ON C.cno = T.cno

子查询

指结果用作另一个查询的参数的查询

单值子查询

多行子查询

相关子查询

5. DML

参考资料