第3章 关系数据库标准语言SQL

SQL(Structed Query Language)是关系数据库的标准语言，它包含了关系代数的运算，并进行了扩充

1. SQL概述

1.1 SQL的组成

1.1.1 操作对象

  • 表和视图是SQL的操作对象
  • 表就是关系模型的关系。表由名称、结构（关系模式）和数据3部分组成。表的名称和结构存储在DBMS的数据字典，表的数据保存在数据库
  • 视图时一个特殊的表，基本上可以把它当作表使用

1.1.2 操作分类

1. 数据定义语言（DDL)

  1. 数据定义语言（DDL）
  • 定义数据库的逻辑结构，包括定义表、视图和索引
  • 数据定义语言只是定义结构，不涉及具体的数据
  • 数据定义语句的执行结果会保存在数据字典
    [] 中里面的内容意味着可写可不写
查询所有数据库：
SHOW DATABASES;
查询当前数据库：
SELECT DATABASE();
创建数据库：
CREATE DATABASE [ IF NOT EXISTS ] 数据库名 [ DEFAULT CHARSET 字符集] [COLLATE 排序规则 ];
删除数据库：
DROP DATABASE [ IF EXISTS ] 数据库名;
使用数据库：
USE 数据库名;

注意事项: UTF8字符集长度为3字节，有些符号占4字节，所以推荐用utf8mb4字符集 即:

create database if not exists itheima default charset utf8mb4;

查询当前数据库所有表：
SHOW TABLES;
查询表结构：
DESC 表名;
查询指定表的建表语句：
SHOW CREATE TABLE 表名;

创建表：

CREATE TABLE 表名(
	字段1 字段1类型 [COMMENT 字段1注释],
	字段2 字段2类型 [COMMENT 字段2注释],
	字段3 字段3类型 [COMMENT 字段3注释],
	...
	字段n 字段n类型 [COMMENT 字段n注释]
)[ COMMENT 表注释 ];
最后一个字段后面没有逗号

image
image

修改表

添加字段：
ALTER TABLE 表名 ADD 字段名 类型(长度) [COMMENT 注释] [约束];
例：ALTER TABLE emp ADD nickname varchar(20) COMMENT '昵称';

修改数据类型：
ALTER TABLE 表名 MODIFY 字段名 新数据类型(长度);
修改字段名和字段类型：
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
例：将emp表的nickname字段修改为username，类型为varchar(30)
ALTER TABLE emp CHANGE nickname username varchar(30) COMMENT '昵称';

删除字段：
ALTER TABLE 表名 DROP 字段名;

修改表名：
ALTER TABLE 表名 RENAME TO 新表名

删除表：
DROP TABLE [IF EXISTS] 表名;
删除表，并重新创建该表(清空数据)：
TRUNCATE TABLE 表名;

MySQL图形化界面: Navicat, Sqlyog, DataGrip
image

2. 数据操纵语言（DML）

  1. 数据操纵语言（DML）
  • 包括查询和更新两大类操作
  • 查询包括选择、投影、连接等运算
  • 更新包括插入、删除和修改操作
    INSERT, DELETE, UPDATE
  1. 添加数据
定字段：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...);
全部字段：
INSERT INTO 表名 VALUES (值1, 值2, ...);

批量添加数据：
INSERT INTO 表名 (字段名1, 字段名2, ...) VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);
INSERT INTO 表名 VALUES (值1, 值2, ...), (值1, 值2, ...), (值1, 值2, ...);

注意事项:
  字符串和日期类型数据应该包含在引号中
  插入的数据大小应该在字段的规定范围内
  1. 更新和删除数据
修改数据：
UPDATE 表名 SET 字段名1 = 值1, 字段名2 = 值2, ... [ WHERE 条件 ];
例：
UPDATE emp SET name = 'Jack' WHERE id = 1;

删除数据：
DELETE FROM 表名 [ WHERE 条件 ];
* DELETE语句不能删除某一个字段的值(可以使用UPDATE)

3. 数据查询语言(DQL)

  1. 数据查询语言(DQL, Data Query Language), 用来查询数据库中表的记录

  2. 语法

SELECT
	字段列表
FROM
	表名字段
WHERE
	条件列表
GROUP BY
	分组字段列表
HAVING
	分组后的条件列表
ORDER BY
	排序字段列表
LIMIT
	分页参数
  1. 基础查询
查询多个字段：
SELECT 字段1, 字段2, 字段3, ... FROM 表名;
SELECT * FROM 表名;

设置别名：
SELECT 字段1 [ AS 别名1 ], 字段2 [ AS 别名2 ], 字段3 [ AS 别名3 ], ... FROM 表名;
SELECT 字段1 [ 别名1 ], 字段2 [ 别名2 ], 字段3 [ 别名3 ], ... FROM 表名;

去除重复记录：
SELECT DISTINCT 字段列表 FROM 表名;

  1. 条件查询
    语法：
    SELECT 字段列表 FROM 表名 WHERE 条件列表;
    image

  2. 聚合查询
    聚合查询（聚合函数）
    语法：SELECT 聚合函数(字段列表) FROM 表名;
    常见聚合函数：

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

NULL值不参与所有聚合函数运算

  1. 分组查询
    语法：SELECT 字段列表 FROM 表名 [ WHERE 条件 ] GROUP BY 分组字段名 [ HAVING 分组后的过滤条件 ];
    where 和 having 的区别：
    执行时机不同：where是分组之前进行过滤，不满足where条件不参与分组；having是分组后对结果进行过滤。
    判断条件不同：where不能对聚合函数进行判断，而having可以。

注意事项:

  • 执行顺序：where > 聚合函数 > having
  • 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无任何意义
  1. 排序查询语法：
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1, 字段2 排序方式2;
排序方式：ASC: 升序（默认）DESC: 降序
注意事项: 如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序
  1. 分页查询语法：
SELECT 字段列表 FROM 表名 LIMIT 起始索引, 查询记录数;

例子：
-- 查询第一页数据，展示10条
SELECT * FROM employee LIMIT 0, 10;
-- 查询第二页
SELECT * FROM employee LIMIT 10, 10;
<font color=red>注意事项:
起始索引从0开始，起始索引 = （查询页码 - 1） * 每页显示记录数
分页查询是数据库的方言，不同数据库有不同实现，MySQL是LIMIT
如果查询的是第一页数据，起始索引可以省略，直接简写 LIMIT 10 </font>
  1. DQL执行顺序: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

4. 数据控制语言（DCL）

  1. 数据控制语言（DCL）
  • 包括对关系和视图的访问权限的描述，以及对事务的控制语句。
  1. 嵌入式SQL和动态SQL（Embeded SQL and Dynamic SQL ）
  • 规定了在诸如C、Fortran、Cobol等宿主语言使用SQL的规则
  1. 管理用户
查询用户：
USE mysql;
SELECT * FROM user;
创建用户:
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码：
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户：
DROP USER '用户名'@'主机名';

例子：
-- 创建用户test，只能在当前主机localhost访问
create user 'test'@'localhost' identified by '123456';
-- 创建用户test，能在任意主机访问
create user 'test'@'%' identified by '123456';
create user 'test' identified by '123456';
-- 修改密码
alter user 'test'@'localhost' identified with mysql_native_password by '1234';
-- 删除用户
drop user 'test'@'localhost';
  1. 权限控制
查询权限：
SHOW GRANTS FOR '用户名'@'主机名';

授予权限：
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';

撤销权限：
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

注意事项: 
    * 多个权限用逗号分隔
    * 授权时，数据库名和表名可以用 * 进行通配，代表所有

1.2 SQL特点

  1. 综合统一
  • SQL集数据定义语言、数据操纵语言、数据控制语言的功能于一身，语言风格统一，操作符统一，查询、插入、删除、更新等操作都只需一种操作符
  1. 高度非过程化
  • 只要提出“做什么”，而无须指明“怎么做”。“怎么做”由系统自动完成。用户是在数据库的逻辑结构(关系模型)层次上使用数据库，无须了解数据库的物理结构
  1. 面向集合的操作方式
  • 操作对象、操作结果都是关系。SQL采用集合操作方式
  1. 以同一种语法结构提供两种使用方式
  • SQL语句能够嵌入高级语言（如C、Cobol、Fortran）
  1. 语言简洁，易学易用
  • 核心概念9个动词

2. 数据类型和表的定义

  • SQL使用表（Table）指代关系模型的关系，使用列（Column）指代属性

2.1 数据类型

  • SQL:1999标准定义了3种数据类型:预定义数据类型、构造类型、用户自定义类型

2.1.1 预定义数据类型

2.1.1.1 串

  • 串分为字符串、字节串（8bit）、位串（单bit）
    image

2.1.1.2 数字

  • 数字有精确数值和近似数值
    image

2.1.1.3 布尔类型

  • 布尔运算也可能产生unknown值，一般用空值代表

2.1.1.4 日期时间和区间（Datetime and interval）

image

2.2 表的定义

2.2.1 定义表

  • SQL使用CREATE TABLE语句定义表

2.2.1.1 实体完整性



2.2.1.2 参照完整性


  • 参照完整性违约处理

    （1） 拒绝（NO ACTION）执行
    不允许该操作执行。该策略一般设置为默认策略

    （2）瀑布删除（CASCADE）操作
    当删除或修改被参照表（Student）的一个元组造成了与参照表（SC）的不一致，则删除或修改参照表中的所有造成不一致的元组

    （3）设置为空值（SET-NULL）
    当删除或修改被参照表的一个元组时造成了不一致，则将参照表中的所有造成不一致的元组的对应属性设置为空值。

2.2.1.3 列值完整性

2.2.1.4 用户自定义完整性

  • 用户定义的完整性是：针对某一具体应用的数据必须满足的语义要求
  • 可分为两类：
    • 属性上的约束条件
    • 元组上的约束条件
  1. 属性上的约束条件

  • CREATE TABLE时定义属性上的约束条件

    列值非空（NOT NULL）

    列值唯一（UNIQUE）

    检查列值是否满足一个条件表达式（CHECK）

  1. 元组上约束条件

  • 在CREATE TABLE时可以用CHECK短语定义元组上的约束条件，即元组级的限制

  • 同属性值限制相比，元组级的限制可以设置不同属性之间的取值的相互约束条件

2.2.2 修改表的定义

  • SQL用ALTER TABLE语句修改表，许多DBMS有独自的格式，其一般格式为：

    ALTER TABLE <表名>

    [ADD <新列名> <数据类型描述符> [完整性]]

    [DROP <完整性名>]

    [MODIFY <列名> <数据类型描述符>];


2.2.3 数据查询

  • 查询是指从数据库中找到感兴趣的数据。SQL的SELECT语句用于查询，它集成了关系代数的选择、投影、连接以及集合运算并增加了分组、排序等功能

2.2.3.1 SELECT语句

SELECT [ALL|DISTINCT] <目标列表达式> [别名] [, <目标列表达式> [别名] …]

FROM <表名或视图名> [别名] [,<表名或视图名> [别名] …]

[WHERE <条件表达式>]

[GROUP BY <列名表> [HAVING <条件表达式>]]

[ORDER BY <列名> [ASC|DESC][, <列名> [ASC|DESC]]；

*代表全部的列(次序同CREATE)，SELECT *

2.2.3.2 DISTINCT

  • 集合不允许有重复的元素

    允许有重复的元素的集合叫做包

    SELECT的默认结果是包

  • DISTINCT 去除重复的行，将包变换成集合

    DISTICT 必须在SELECT之后，第1个列之前

    只能有1个DISTICT

    比如：SELECT DISTINCT Sdept





2.2.3.4 ORDEY BY

2.2.4 聚集函数

  • 聚集函数接受一个集合(包)，返回一个值
  • COUNT（[DISTINCT|ALL] * ） 统计元组数
  • COUNT（[DISTINCT|ALL] <列名>） 统计某列的值的数量
  • SUM（[DISTINCT|ALL] <列名>） 计算某列的值的总和
  • AVG（[DISTINCT|ALL] <列名>） 计算某列的平均值
  • MAX（[DISTINCT|ALL] <列名>） 求某列的最大值
  • MIN（[DISTINCT|ALL] <列名>） 求某列的最小值

  • 聚集函数自动去除NULL

2.2.5 GROUP BY

  • 用于分组，分组列
  • 在分组列上有相同值的元组组成了一个分组
  • GROUP BY 列名, ..., 列名


2.2.6 HAVING

  • 选择符合条件的分组：HAVING <条件表达式>

3. 多表查询

  • 从概念上讲，FROM子句先对这些表做笛卡儿积运算，得到一个临时表，之后的选择、投影等运算都是针对这个临时表进行，从而将多表查询转换为单表查询。

3.1 内连接查询

  1. 内连接查询的是两张表交集的部分
隐式内连接：
SELECT 字段列表 FROM 表1, 表2 WHERE 条件 ...;

显式内连接：
SELECT 字段列表 FROM 表1 [ INNER ] JOIN 表2 ON 连接条件 ...;

显式性能比隐式高

3.2 外连接查询

左外连接：
查询左表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 LEFT [ OUTER ] JOIN 表2 ON 条件 ...;
相当于查询表1的所有数据，包含表1和表2交集部分数据

右外连接：
查询右表所有数据，以及两张表交集部分数据
SELECT 字段列表 FROM 表1 RIGHT [ OUTER ] JOIN 表2 ON 条件 ...;

3.3 自连接查询

  1. 当前表与自身的连接查询，自连接必须使用表别名

  2. 语法：
    SELECT 字段列表 FROM 表A 别名A JOIN 表A 别名B ON 条件 ...;

  3. 自连接查询，可以是内连接查询，也可以是外连接查询

3.4 联合查询 union, union all

  1. 把多次查询的结果合并，形成一个新的查询集
SELECT 字段列表 FROM 表A ...
UNION [ALL]
SELECT 字段列表 FROM 表B ...
  1. 注意事项
    • UNION ALL 会有重复结果，UNION 不会
    • 联合查询比使用or效率高，不会使索引失效












4. 集合运算

5. 子查询

  • 包含子查询的查询叫做嵌套查询，嵌套查询分为相关嵌套查询和不相关嵌套查询

5.1 定义

  1. SQL语句中嵌套SELECT语句，称谓嵌套查询，又称子查询。

  2. SELECT * FROM t1 WHERE column1 = ( SELECT column1 FROM t2);

  3. 子查询外部的语句可以是 INSERT / UPDATE / DELETE / SELECT 的任何一个

  4. 根据子查询结果可以分为：

    • 标量子查询（子查询结果为单个值）
    • 列子查询（子查询结果为一列）
    • 行子查询（子查询结果为一行）
    • 表子查询（子查询结果为多行多列）

  5. 根据子查询位置可分为：

    • WHERE 之后
    • FROM 之后
    • SELECT 之后

5.2 标量子查询

  1. 子查询返回的结果是单个值（数字、字符串、日期等）。
  2. 常用操作符：= <> > >= < <=
-- 查询销售部所有员工
select id from dept where name = '销售部';
-- 根据销售部部门ID，查询员工信息
select * from employee where dept = 4;
-- 合并（子查询）
select * from employee where dept = (select id from dept where name = '销售部');

-- 查询xxx入职之后的员工信息
select * from employee where entrydate > (select entrydate from employee where name = 'xxx');

5.3 列子查询

  1. 返回的结果是一列（可以是多行）。

  2. 常用操作符：
    image

-- 查询销售部和市场部的所有员工信息
select * from employee where dept in (select id from dept where name = '销售部' or name = '市场部');
-- 查询比财务部所有人工资都高的员工信息
select * from employee where salary > all(select salary from employee where dept = (select id from dept where name = '财务部'));
-- 查询比研发部任意一人工资高的员工信息
select * from employee where salary > any (select salary from employee where dept = (select id from dept where name = '研发部'));

5.4 行子查询

  1. 返回的结果是一行（可以是多列）。
  2. 常用操作符：=, <>, IN, NOT IN
-- 查询与xxx的薪资及直属领导相同的员工信息
select * from employee where (salary, manager) = (12500, 1);
select * from employee where (salary, manager) = (select salary, manager from employee where name = 'xxx');

5.4 表子查询

  1. 返回的结果是多行多列, 常用操作符：IN
-- 查询与xxx1，xxx2的职位和薪资相同的员工
select * from employee where (job, salary) in (select job, salary from employee where name = 'xxx1' or name = 'xxx2');
-- 查询入职日期是2006-01-01之后的员工，及其部门信息
select e.*, d.* from (select * from employee where entrydate > '2006-01-01') as e left join dept as d on e.dept = d.id;

5.1 WHERE子句中的子查询

5.1.1 比较运算符

子查询的结果是元组的集合，即一个表。如果子查询的结果是一个单列并且单行的表，则可以作为比较运算符的运算对象

5.1.1.1 SOME和ALL




  • 相关嵌套查询，因为子查询有一个变量x，当未确定x的值时，无法得到查询的结果，而x代表父查询的元组，与父查询相关。

  • 不相关嵌套查询的子查询先于父查询执行，并且只需要执行一次，而相关嵌套查询对父查询的每个元组都要执行一次子查询

5.1.2 谓词IN

  • 谓词IN是二元运算符，一般书写形式为A IN S，A是一个列名，S是一个集合


    本查询也可以利用连接运算实现

    SELECT Student.Sno, Sname

    FROM Student, SC, Course

    WHERE Studenet.Sno = SC.Sno AND

    SC.Cno = Course.Cno AND

    Course.Cname = '管理学';

5.1.3 谓词EXISTS

  • 谓词EXISTS是一元运算符，运算数是一个集合，如果该集合不是空集，则运算结果为真，否则为假


使用EXCEPT运算符和谓词EXISTS的SQL语句为：

使用谓词IN和EXISTS的SQL语句为：

只使用谓词EXISTS的SQL语句为：


5.2 FROM子句中的子查询

  • FROM子句指定查询要使用的表，一般情况下，这些表是数据库中实际存在的表。如示例数据数据库的Student、Course和SC表。
  • 子查询的结果是一个表，但只是一个中间结果，并没有存放在数据库。
  • 为了在 FROM 子句使用子查询，要给子查询生成的临时表命名，有时还要命名临时表的列。



5.3 外连接

  • 为了解决参与连接的表的某些元组没有出现在连接结果中的问题，需要使用左外连接、右外连接和全外连接运算。作为区分，前面介绍的连接叫做内连接

  • *= 代表左外连接，单新版本不再支持

  • A {FULL | LEFT | RiGHT} OUTER JOIN B ON Condition

    关键字JOIN的左、右是参与连接的表名，ON是连接条件，OUTER代表外连接运算

5.3.1 左外连接运算






5.3.2 右外连接运算

6. 数据更新

  • 数据更新操作有3个：向表插入数据、修改表的数据、删除表的数据

6.1 插入操作

  • INSERT

    INTO <表名> [(<列1>[, <列2>…])

    VALUES (<常量1> [, <常量2>]…);

  • INSERT

    INTO <表名> [(<属性列1> [, <属性列2>…])

    查询;


6.2 修改操作

  • 修改操作又称为更新操作

  • UPDATE  <表名>

    SET <列名>=<表达式>[, <列名>=<表达式>]…

    [WHERE <条件>];


6.3 删除操作

  • DELETE

    FROM <表名>

    [WHERE <条件>];

例3.71 删除学号为2000012的学生的记录。

DELETE

FROM Student

WHERE Sno='2000012';

7. 视图

7.1 视图的定义

  • 一个SELECT 语句(的查询结果)
  • 虚表，数据库管理系统只存储视图的定义(SELECT语句) ，而不存放视图的数据
  • 使用视图时，进行视图消隐，获取数据。
  • 更新操作受到一些限制。

7.2 建立视图

CREATE  VIEW  <视图名>[(<列名>[， <列名>]…)]

AS <查询>

[WITH CHECK OPTION]；



7.3 删除视图

  • DROP VIEW <视图名>
    例3.78 删除视图Student_CS。

    DROP VIEW Student_CS;

  • 执行DROP VIEW语句后，DBMS从数据字典中删除视图Student_CS的定义

7.4 查询视图

8. 索引

8.1 索引的概念

  • 索引是一个独立的、物理的数据库结构

  • 基于表的一列或多列建立，按照列值排序。

  • 索引不是关系模型的概念，它属于物理实现的范畴

  • 在关系上是否建立索引、建立什么样的索引要慎重考虑。因为索引虽然加快了查询速度，但维护索引也要付出代价。

  • 索引一般由DBA建立

  • 索引为查询的实现提供了更多的选择

  • 执行一个查询时，由DBMS的查询优化子系统决定是否使用索引、

  • 使用哪个索引，而用户无权干预

  • 按照索引列上的值是否唯一：
    唯一索引(UNIQUE)
    非唯一索引(NOT UNIQUE)

  • 按照索引的结构：

    1. 聚簇索引(Clustered Index)

    聚簇索引要求表中元组的存放次序和索引中索引项的存放次序相同,或者说表中的元组也是有序的，非聚簇索引则无此要求。

    由于表中的元组只能有一种物理存储顺序，因此一个表最多有一个聚簇索引。表的数据发生变化后，为了维护表的元组的有序性，要付出很大的代价。

    1. 非聚簇索引(Nonclustered Index)

8.2 建立、删除索引


9. 存取控制

9.1 授权

  • SQL使用GRANT语句向用户授予操作权限

    GRANT <权限>[，<权限>…]

    [ON <表名或视图名>]

    TO <用户>[，<用户>…]

    [WITH GRANT OPTION];


9.2 收回权限

9.3 角色

9.4 其他权限

10. 空值的处理

  • 构成主码的列不能取空值，有NOT NULL限制的列不能取空值
  • 空值与另一个值（包括另一个空值）的算术运算的结果为空值
  • 空值与另一个值（包括另一个空值）的比较运算的结果为UNKNOWN
  • 有了UNKNOWN后，传统的二值（TRUE、FALSE）逻辑就变成了三值逻辑
  • 只有使WHERE和HAVING子句的选择条件为TRUE的元组，才会被选中
  • 聚集函数除了COUNT(*)外，自动去除空值


11. 函数

11.1 字符串函数

image

11.2 数值函数

image

11.3 日期函数

image

11.4 流程函数

image

12. 约束

  1. 约束概念: 约束是作用于表中字段上的规则, 用于限制存储在表中的数据
  2. 目的: 保证数据库中数据的正确, 有效性和完整性
  3. 约束是作用于表中字段上的，可以再创建表/修改表的时候添加约束。
    image
create table user(
	id int primary key auto_increment,
	name varchar(10) not null unique,
	age int check(age > 0 and age < 120),
	status char(1) default '1',
	gender char(1)
);
  1. 外键约束: 外键用来让两张表的数据之间建立连接, 从而保证数据的一致性和完整性
添加外键：
CREATE TABLE 表名(
	字段名 字段类型,
	...
	[CONSTRAINT] [外键名称] FOREIGN KEY(外键字段名) REFERENCES 主表(主表列名)
);
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名) REFERENCES 主表(主表列名);

-- 例子
alter table emp add constraint fk_emp_dept_id foreign key(dept_id) references dept(id);

删除外键：
ALTER TABLE 表名 DROP FOREIGN KEY 外键名;

image

ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE 行为 ON DELETE 行为;
posted @ 2024-11-16 22:05  awei040519  阅读(250)  评论(0)    收藏  举报