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 数据类型

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

• Course

• SC

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 查询结果加条件

• 查询条件如下：

查询 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 聚集函数

注：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;