数据库 — SQL 语言

2021-11-20 13:38:13

1 SQL 基本概念

1.1 SQL 概念及分类

  • 定义：SQL 是 Structured Query Language 的缩写，意思是结构化查询语言，是一种在数据库管理系统中查询或对数据库里面的数据进行更改的语言

  • 主流数据库管理系统

    • 关系型数据库
      • MySQL
      • Oracle
      • Postgre SQL
      • SQL Server
    • 非关系型数据库
      • redis
      • mongo DB

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

    • SQL 数据定义语言主要用来定义逻辑结构，包括定义基表，视图和索引删除表定义表修改表

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

    • SQL 的数据查询语言主要用来对数据库中的各种数据对象进行查询

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

    • SQL 的数据操纵语言，用于改变数据库中的数据，包括插入，删除，修改

  • 数据控制功能 DCL(Data Control Language)

    • SQL 的数据控制语言，对表和视图的授权，完整性规则的描述以及事务开始和结束等控制语句
1.2 SQL 语言的特点
  • 综合统一：独立完成数据库生命周期中的全部活动，包括定义关系模式、录入数据、建立数据库、査询、更新、维护、数据库重构、数据库安全
  • 高度非过程化：用户只需提出“做什么”，而不必指明“怎么做
  • 面向集合的操作方式：SQL 采用集合操作方式
  • 以同一种语法结构提供两种使用方式：SQL既是自含式语言，又是嵌入式语言，SQL语句能够嵌入到高级语言程序中
  • 语言简洁，易学易用：SQL 语言语法简单，接近英语口语
1.3 SQL 基本语法
1 SQL 数据类型
数据类型 含义
CHAR(n)
CHARACTER(n)		 长度为 n 的定长字符串
VARCHAR(n)
CHARACTERVARYING(n)		 最大长度为 n 的变长字符串
CLOB 字符串大对象
BLOB 二进制大对象
INT
INTEGER		 长整数（4 字节）
SMALLINT 短整数（2 字节）
BIGINT 大整数（8 字节）
数据类型 含义
NUMERIC(p, d) 定点数，由 p 位数字（不包括符号、小数点）组成，
小数点后边有 d 位数字
DECIMAL(p, d)
DEC(p, d)		 同 NUMERIC
REAL 取决于机器精度的单精度浮点数
DOUBLE PRECISION 取决于机器精度的双精度浮点数
FLOAT(n) 可选精度的浮点数，精度至少为 n 位数字
BOOLEAN 逻辑布尔值
DATE 日期，包含年、月、日，格式为 YYYY-MM-DD
TIME 时间，包含一日的时、分、秒，格式为 HH:MM:SS
TIMESTAMP 时间戳类型
TIMERVAL 时间间隔类型
2 模式的定义及删除

  • 模式定义

    • 语法：CREATE SCHEMA <模式名> AUTHORIZATION <用户名>
    • 例子：CREATE SCHEMA "S-T" AUTHORIZATION WANG;

  • 模式定义 + 视图

    • 语法：CREATE SCHEMA <模式名> AUTHORZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];

  • 模式删除

    • 语法：DROP SCHEMA <模式名><CASCADE|RESTRICT>;
    • CASCADE、RESTRICT 两者必须二选一
    • VASCADE(级联) ：删除模式的同时也把给模式的所有数据库对象删除
    • RESTRICT(限制) ：如果该模式下有下属对象（比如表视图），则拒绝该删除语句的执行
3 表的增删改查
  • 创建表
CREATE TABLE 表名 (字段名 类型 字段约束, 字段名 类型 字段约束, 字段名 类型 字段约束);

CREATE TABLE Student (
    name VARCHAR(20),
    age INT,
    sex CHAR(1),
);
  • 删除表
DROP TABLE <表名> [RESTRICT|CASCADE];

DROP TABLE Student RESTRICT;
  • 修改
ALTER TABLE <表名>
[ADD [COLUMN] <新列名><数据类型> [完整性约束]]
[ADD <表级完整性约束>]

ALTER TABLE Student ADD S_entrance DATE;

ALTER TABLE Student ALTER COLUMN S_age INT;
4 索引的增删改查
  • 当数据库中数据量巨大时，建立索引可以减少查询时间，索引可以建立在一列或多列上
  • 建立索引
    • CLUSTER ：聚簇索引，物理顺序与索引的逻辑顺序相同
    • UNIQUE ：唯一索引
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> 
ON <表名>(<列名>[<次序>][,<列名> [<次序>]] ...);

CREATE UNIQUE INDEX Stusno ON Student(Sno);
  • 修改索引
ALTER INDEX <旧索引名> RENAME TO <新索引名>;

ALTER INDEX SCno RENAME TO SCSno;
  • 删除索引
DROP INDEX <索引名>

DROP INDEX Stusno;

2 SQL 的查询

假设关系表 Student 、Course 、SC 如下：

  • Student
学号(Sno) 姓名(Sname) 性别(Ssex) 年龄(Sage) 院系(Sdept)
201215121 李勇 男 20 CS
201215122 刘晨 女 19 CS
201215123 王敏 女 18 MA
201215125 张力 男 19 ES
  • Course
课程号(Cno) 课程名(Cname) 先行课(Cpno) 学分(Ccredit)
1 数据库 5 4
2 数学 / 2
3 信息系统 1 4
4 操作系统 6 3
5 数据结构 7 4
6 数据处理 / 2
7 Java语言 6 4
  • SC
学号(Sno) 课程号(Cno) 成绩(Grade)
201215121 1 92
201215121 2 85
201215121 3 88
201215122 2 90
201515122 3 80
2.1 查询所有列

查询 Student 表中所有列

SELECT * FROM Student;
2.2 查询部分列

查询 Student 表中的 Sno 、Ssex 列

SELECT Sno, Ssex FROM Student;
2.3 列取别名

查询 Student 表中的 Sno 、Ssex 列，并将 Sno 命名为 “学号”，Ssex 命名为 “性别”

SELECT Sno 学号, Ssex 性别 FREOM Student;

SELECT Sno AS 学号, Ssex AS 性别 FROM Student;
2.4 表取别名

查询 Course 表中的 Cno 列，并将查询结果命名为 CC

SELECT Cno FROM Course AS CC;
2.5 查询结果去重

查询 SC 中的 Sno 列，并将结果去重

SELECT DISTINCT Sno AS 课程名 FROM SC;
2.6 查询结果加条件
  • 查询条件如下：
查询条件 谓词
比较 \(=、>、<、\le、\ge\)
!=、<>、!>、!<、NOT
确定范围 \(BETWEEN AND\)
\(NOT BETWEEN AND\)
确定集合 \(IN、NOT IN\)
字符匹配 \(LIKE、NOT LIKE\)
空值 \(IS NULL、IS NOT NULL\)
逻辑运算 \(AND、OR、NOT\)

查询 SC 表中 Grade 大于 88 的信息

SELECT * FROM SC WHERE Grade >= 88;

查询 SC 表中 Grade 在 85 ~ 95 之间的信息

SELECT * FROM  SC WHERE Geade BETWEEN 85 AND 95;

查询 1 是否包含在 (1,2) 之间

SELECT 1 FROM (1, 2);

查询 Course 表中包含 “系统” 的课程的课程号，课程名

"%" 可以近似多个字符，"_" 只能近似一个字符

SELECT Cno, Cname FROM Course WHERE Cname LIKE '%系统';

查询 Course 中先行课为空的信息

SELECT * FROM Course WHERE Cpno IS NULL;

查询 Course 中学分为 4 分并且先行课为 1 的课程的课程名

SELECT Cname FROM Course WHERE Ccredit = 3 AND Cpno = 1;

查询 Course 中学分为 4 分或 3 分的课程的课程名

SELECT Cname FROM Course WHERE Ccredit = 3 OR Ccredit = 4;
2.7 聚集函数
函数 含义
\(COUNT(*)\) 统计元组个数
\(COUNT([DISTINCT|ALL]<列名>)\) 统计一列中值的个数
\(SUM([DISTINCT|ALL]<列名>)\) 计算一列值的总和
\(AVG([DISTINCT|ALL]<列名>)\) 计算一列值的平均值
\(MAX([DISTINCT|ALL]<列名>)\) 计算一列值中的最大值
\(MIN([DISTINCT|ALL]<列名>)\) 计算一列值中的最小值

注：DISTINCT 是去除重复的值

查询 CS 表中 Grade 的个数（去重）、总和、平均值、最值

SELECT COUNT(*) FROM SC;

SELECT COUNT(DISTINCT Grade) FROM SC;

SELECT SUM(Grade) FROM SC;

SELECT AVG(Grade) FROM SC;

SELECT MAX(Grade) FROM SC;

SELECT MIN(Grade) FROM SC;
2.8 分组查询

将表 SC 按照 Sno 进行分组，然后筛选出分组中 Sno 为 "201215121" 的一组

SELECT Sno FROM SC GROUP BY Sno HAVING Sno = '201215121';
2.9 等值连接

将 Student 与 SC 以 Sno 进行等值连接

SELECT Student AS S, SC WHERE S.Sno = SC.Sno;
2.10 自身连接

将 Student 表与自生进行连接

SELECT Student AD S1, Student AS S2 WHERE S1.Sno = S2.Sno;
2.11 外连接

将 Course 与 SC 以 Cno 分别进行外连接、左外连接、右外连接

SELECT * FROM Course OUTER JOIN SC ON Course.Cno = SC.Cno; 

SELECT * FROM Course LEFT OUTER JOIN SC ON Course.Cno = SC.Cno; 

SELECT * FROM Course RIGHT OUTER JOIN SC ON Course.Cno = SC.Cno;
2.12 多表查询

将 Student 与 SC 以 Sno 进行连接，并且将 Course 与 SC 以 Cno 进行等值连接

SELECT * 
FROM Student JOIN SC ON Student.Sno = SC.Sno 
JOIN Course ON SC.Cno = Course.Cno;

SELECT *
FROM Student, Course, SC 
WHERE Student.Sno = SC.Sno AND Course.Cno = SC.Cno;
2.13 嵌套查询

查询 Grade > 90 的学生的学号（Sno）、姓名（Sname）、所在院系（Sdept）

SELECT Sno, Sname, Sdept
FROM Student
WHERE Sno IN (
    SELECT Sno 
    FROM SC
    WHERE Grade > 90;
);
2.14 带有 ANY 的查询

查询 Sage > 19 的所有学生的姓名（Sname）（结果满足子集中的任一值即可）

SELECT Sname
FROM Student
WHERE Sage > ANY (
    SELECT Sage
    FROM Student
    WHERE Sage > 19;
);
2.15 带有 ALL 的查询

查询 Sage > 19 的所有学生的学号（Sno）（结果要满足子集中的所有值）

SELECT Sno
FROM Student
WHERE Sage > ALL (
    SELECT Sage
    FROM Student
    WHERE Sage > 19;
);
2.16 带 EXISTS 的查询

查询没有一门课不选修的学生的姓名（Sname）

SELECT Sname
FROM Student
WHERE NOT EXISTS(
    SELECT *
    FROM Course
    WHERE NOT EXISTS (
        SELECT *
        FROM SC
        WHERE Sno = Student.Sno AND Cno = Course.Cno);
    );
)
2.17 集合查询
1 并集

查询所在系（Sdept）为 "CS" 的学生与年龄（Sage）大于 19 的学生的并集

SELECT * FROM Student WHERE Sdept = "CS"
UNION
SELECT * FROM Student WHERE Sage > 19;
2 交集

查询所在系（Sdept）为 "CS" 的学生与年龄（Sage）大于 19 的学生的交集

SELECT * FROM Student WHERE Sdept = "CS"
INTERSECT
SELECT * FROM Student WHERE Sage > 19;
3 差集

查询所在系（Sdept）为 "CS" 的学生与年龄（Sage）大于 19 的学生的差集

SELECT * FROM Student WHERE Sdept = "CS"
EXCEPT
SELECT * FROM Student WHERE Sage > 19;

3 SQL 的增删改

3.1 数据的插入
1 标准添加
INSERT INTO Student(Sno, Sname, Ssex, Sage, Sdept)
VALUES ('201215128', '石昊', '男', 25, 'IS');
2 指定部分字段
INSERT INTO Student (Sname, Sage, Sdept) VALUE ('叶凡', 22, 'CS');
3 不指定字段
-- 不指定字段时，需要一一对应添加且不能缺少项目
INSERT INTO VALUE Student ('201215128', '石昊', '男', 25, 'IS');
4 批量添加
INSERT INTO Student VALUES
('201215128', '石昊', '男', 25, 'IS'),
('201215129', '叶凡', '男', 22, 'CS'),
('201215130', '狠人', '女', 20, 'MX');
3.2 修改

将 Student 表中学号（Sno）为 "201215122" 的学生姓名（Sname）改为 “石昊”，年龄（Sage）改为 25

UPDATE Student SET Sname = "石昊", Sage = 25 WHERE Sno = "201215122";
3.3 删除

删除 Student 表中年龄（Sage）大于 18 的数据

DELETE FROM Student WHERE Sage > 18;

4 视图

4.1 视图定义
  • 视图（VIEW）也被称作虚表，即虚拟的表，是一组数据的逻辑表示，其本质是对应于一条 SQL 语句，结果集被赋予一个名字
  • 视图本身并不包含任何数据，它只包含映射到基表的一个查询语句，当基表数据发生变化，视图数据也随之变化
  • 目的：方便，简化数据操作
  • 当我们业务需求要查出多张表的数据，这时我们可能会关联多张表查询处理，如果这个查询 SQL 复杂的话也影响了查询效率，这个时候就可以创建视图，查询时候只需要 select * from view 就可以
4.2 创建视图
-- 末尾的 WITH CHECK OPTION 作用如下：
-- 当后续操作对视图进行增删改操作时，可能视图已经不满足子条件 S 的约束
-- 加上该语句之后，后续对视图进行增删改的时，系统会自动在该子条件 S 下进行操作
CREATE VIEW S_STU
AS <子查询>
[WITH CHECK OPTION];

将 Student 表中学生年龄（Sage）> 18 的学生创建一个名为 S_NEW 的视图，该视图包含学生的学号（Sno）、姓名（Sname）、性别（Ssex）、所在院系（Sdept）

CREATE VIEW S_NEW
AS
SELECT Sno, Sname, Ssex, Sdept
FROM Student
WHERE Sage > 18
WITH CHECK OPTION;
4.3 删除视图
-- CASCADE 的作用如下：
-- 当加上该语句时，会删除该视图以及其下的所有视图
-- 当不加该语句时，若该视图下该有其他视图，会删除失败
DROP VIEW <视图名> [CASCADE];

删除 4.2 创建的视图 S_NEW

DROP VIEW S_NEW CASCADE;
4.4 查询视图

查询 4.2 视图 S_NEW 中性别（Ssex）为 “女” 的学生的学号（Sno）

SELECT Sno
FROM S_NEW 
WHERE Ssex = "女";
4.5 视图更新
  • 视图是虚拟的，不存在的，所以对于视图的操作，最终会反映到基本表上

将 4.2 的视图 S_NEW 中所在系（Sdept）为 “CS” 学生的性别（Ssex）全改为 “男”

-- 更新视图
UPDATE S_NEW 
SET Ssex = "男"
WHERE Sdept = "CS";

-- 更新基本表
-- 因为 4.2 的视图创建条件是 Sage > 18，因此更新基本表时要加上该条件
UPDATE Student
SET Ssex = "男"
WHERE Sdept = "CS" AND Sage > 18;
上一篇：nginx 超时配置说明


下一篇：元素可视区 client 系列