数据库系统原理——第四章 SQL与数据库的基本操作（1）知识点总结（自考本）

当你想挣一百块的时候你就要把目标定成一千块，这样才挣得更多。

SQL概述

什么是SQL？

SQL是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL的特点

1. SQL 不是某个特定数据库供应商专有的语言。
2. SQL 简单易学。
3. SQL 尽管看上去很简单，但它实际上一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

SQL的四大功能

• 数据查询
• 数据定义
• 数据操纵
• 数据控制

数据定义语言

• 主要用于对数据库及数据库中的各种对象进行创建删除、修改等操作。
• 数据库对象主要有表、默认约束、规则、视图、触发器、存储过程等。
• SQL 语句有：
  1. CREATE：用于创建数据库或数据库对象。
  2. ALTER：用于对数据库或数据库对象进行修改。
  3. DROP：用于删除数据库或数据库对象。

数据操纵语言

• 主要用于操纵数据库中各种对象，检索和修改数据。
• SQL 语句主要有：
  1. SELECT：用于从表或视图中检索数据，使用最为频繁的语句之一。
  2. INSERT：用于将数据插入到表或视图中。
  3. UPDATE：用于修改表或视图中的数据，其既可修改表或视图中一行数据，也可同时修改多行或全部数据。

数据控制语言

• 主要用于安全管理。
• SQL 语句如下：
  1. GRANT：用于授权，把语句许可或对象许可的权限授予其他用户和角色。
  2. REVOKE：用于收回权限。

嵌入式和动态 SQL 规则

规定了 SQL 语句在高级程序设计语言中使用的规范方法，以便适应较为复杂的应用。

SQL 调用和会话规则

调用包括 SQL 例程和调用规则，以便提高 SQL 的灵活性、有效性、共享性以及使 SQL 具有更多的高级语言的特征。

MySQL预备知识

MySQL是什么？

是一个关系数据库管理系统（RDBMS），它具有客户/服务器体系结构。

MySQL的架构方式

1. LAMP：即使用 Linux 作为操作系统， Apache 作为 Web 服务器， MySQL 作为数据库管理系统， PHP、 Perl 或 Python 语言作为服务器端脚本解释器。
2. WAMP：即使用 Windows 作为操作系统， Apache 作为 Web 服务器， MySQL作为数据库管理系统， PHP、 Perl 或 Python 语言作为服务器端脚本解释器。

MySQL 中的 SQL

• 遵循 SQL 标准；
• 提供了对数据定义语言DDL、数据操纵语言 DML、数据控制语言 DCL 的支持；
• 支持关系数据库的三级模式结构。
• MySQL 中一个关系对应一个基本表， 一个或多个基本表对应一个存储文件，一个表可以有若干索引，索引也存放在存储文件中。

MySQL的语言要素

MySQL 在 SQL 标准的基础上增加了部分扩展的语言要素：包括常量、变量、运算符、表达式、函数、流程控制语句和注解等。

常量

• 常量是指在程序运行过程中值不变的量，也称为字面值或标量值。常量的使用格式取决于值的数据类型，可分为字符串常量、数值常量、十六进制常量、时间日期常量、位字段值、布尔值和 NULL 值。
• 字符串常量是指用单引号或双引号括起来的字符序列，分为 ASCII 字符串常量和 Unicode 字符串常量。
• 数据值常量可以分为整数常量和浮点数常量。整数常量是不带小数点的十进制数；浮点数常量则是使用小数点的数值常量。
• 一个十六进制值通常指定为一个字符串常量，每对十六进制数字被转换为一个字符，其最前面有一个大写字母 X 或小写字母 x。
• 日期和时间常量是用单引号将表示日期时间的字符串括起来构成的。
• 使用 b‘value’格式书写为字段值。 Value 是一个用 0 或 1 书写的二进制值。
• 布尔值的两个值： TRUE（数字值 1）和 FALSE（数字值 0）。
• NULL 值通常用于表示“没有值”“无数据”等意义，与数字类型的“0”或字符串类型的空字符串是完全不同的。

变量

在 MySQL 中，变量分为用户变量和系统变量。用户变量前常添加一个符号“@”，大多数系统变量前，添**加两个“@”**符号。

运算符

MySQL 几类编程语言中常用的运算符：

• 算术运算符： +（加）、 —（减）、 *（乘）、 /（除）和%（求模）
• 位运算符： &（位与）、 |（位或）、 ^（位异或）、 ~（位取反）、 >>（位右移）、 <<（位左移）。
• 比较运算符： =（等于）、 >（大于）、 <（小于）、 >=（大于等于）、 <=（小于等于）、 <>（不等于）、 !=（不等于）、 <=>（相等或都等于空） 。
• 逻辑运算符： NOT 或！（逻辑非）、 AND 或&&（逻辑与）、 OR 或||（逻辑或）、 XOR（逻辑异或）。

表达式

• 是常量、变量、列名、复杂计算、运算符和函数的组合。
• 表达式可分为字符表达式、数值型表达式和日期表达式。

内置函数

MySQL 包含了 100 多个函数，基本分类如下：

• 数学函数，例如 ABS()函数、 SORT()函数；
• 聚合函数，例如 COUNT()函数；
• 字符串函数，例如 ASCII()函数、 CHAR()函数；
• 日期和时间函数，例如 NOW()函数、 YEAR()函数；
• 加密函数，例如 ENCODE()函数、 ENCRYPT()函数;
• 控制流程函数，例如 IF()函数、 IFNULL()函数；
• 格式化函数，例如 FORMAT()函数；
• 类型转换函数，例如 CAST()函数；
• 系统信息函数，例如 USER()函数、 VERSION()函数。

数据定义

关系数据库系统支持三级模式结构，其模式、外模式和内模式中的基本对象有数据库模式、表、索引、视图等。
SQL 标准提供的数据定义语句如下表：

数据库模式定义

创建数据库

语法如下：

CREATE DATABASE [IF NOT EXISTS] <数据库名>
[[DEFAULT] CHARACTER SET <字符集名>]
[[DEFAULT] COLLATE <校对规则名>];

选择数据库

语法如下：

USE db_name;

修改数据库

语法如下：

ALTER DATABASE <数据库名> 
DEFAULT CHARACTER SET gb2312;

删除数据库

语法如下：

DROP {DATABASE|SCHEMA} [IF NOT EXISTS] <数据库名>;

查看数据库

SHOW DATABASES;

表定义

基本语法格式：

CREATE[TEMPORARY]TABLE tbl_name
    (
    字段名1 数据类型 [列级完整性约束条件][默认值]
    [，字段名2 数据类型 [列级完整性约束条件][默认值]]
    [，……]
      [，表级完整性约束条件]
     )[ENGINT=引擎类型]；

临时表和持久表

在创建表的 CREATE TABLE 语句中，若添加可选项**“TEMPORARY”** 关键字，则表示使用该语句创建的表为临时表，而若不选用该关键字创建的表则为持久表。

数据类型

• 指系统中所允许的数据的类型。
• 主要的数据类型包括数值类型、日期和时间类型、字符串类型、空间数据类型等。

关键字 AUTO_INCREMENT

可以为表中数据类型为整型的列设置自增属性。

指定默认值

是指在向表插入数据时，如果没有明确给出某个表列所对应的值，则 DBMS 此时允许为此表列指定的一个值。

NULL 值

NULL 值是指没有值或缺值。
不能将 NULL 值与空串相混淆， NULL 值是没有值，它不是空串。

主键

是通过 PRIMARY KEY 关键字来指定的。
主键值必须唯一，而且主键一定要为 NOT NULL。

更新表

• 可以使用 ALTER TABLE 语句来更改原有表的结构。
• 在 MySQL 中，可以使用 ALTER TABLE 语句来更改原有表的结构。例如可以增加或删减列、创建或取消索引、更改原有列的数据类型、重新命名列或表，还可以更改表的评注和表的引擎类型等，甚至还可以使用该语句为表重新创建触发器、存储过程、索引和外键等。

ADD[COLUMN]子句

增加新列，可同时增加多个列。

CHANGE[COLUMN]子句

修改表中列的名称或数据类型。

ALTER[COLUMN]子句

需要修改或删除表中指定列的默认值。

MODIFY[COLUMN]子句

只会修改指定列的数据类型，而不会干涉它的列名。

DROP[COLUMN]子句

卸除表中多余的列。

RENAME[TO]子句

为表重新赋予一个表名。

重命名表

语法格式：

RENAME TABLE tbl_name TO new_tal_name;

删除表

语法格式：

DROP [TEMPORARY] TABLE [IF EXISTS] <表名>;

查看表

查看表包括显示表的名称和显示表的结构两种情形。

显示表的名称

语法格式如下：

SHOW [FULL] TABLES [{FROM|IN}db_name]

显示表的结构

语法格式如下：

SHOW [FULL] COLUMNS {FROM|IN} tbl_name [{FROM|IN] db_name];

使用 DESCRIBE 语句来完成的，语法格式如下：

{DESCRIBE|DESC}tbl_name[col_name|wild];

索引定义

索引是 DBMS 根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，因而索引实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。

• 索引存在的弊端如下：

1. 索引是以文件的形式存储的， DBMS 会将一个表的所有索引保存在同一个索引文件中，索引文件需要占用磁盘空间。
2. 索引在提高查询速度的同时，却会降低更新表的速度。

索引分类

索引在逻辑上通常包含以下几类：

1. 普通索引：最基本的索引类型，没有任何限制。
2. 唯一性索引： 索引列中的所有值都只能出现一次，必须是唯一的。
3. 主键：一种唯一性索引。

索引的创建

三种方式：

1. 使用 CREATE INDEX 创建语句格式如下：
   
2. 使用 CREATE TABLE 语句创建索引
3. 使用 ALTER TABLE 语句创建索引

SHOW {INDEX|INDEXES|KEYS}
	{FROM|IN} tbl_name
	[[FROM|IN} db_name]
	[WHERE expr];

索引的查看

语法格式如下：

SHOW {INDEX|INDEXES|KEYS}
	{FROM|IN} tbl_name
	[{FROM|IN} db_name]
	[WHERE expr];

索引的删除

1. 使用 DROP INDEX，语法格式如下：

DROP INDEX index_name ON tbl_name

2. 使用 ALTER TABLE 语句删除索引

第四节 数据更新

• 数据更新操作： 添加若干行数据、修改表中的数据和删除表中的若干行数据。
• 对应三类语句：插入数据（INSERT）、修改数据（UPDATE）和删除数据（DELETE）。

插入数据

INSERT 语句有三种语法形式，分别对应的是 INSERT…VALUES 语句、INSERT…SET 语句和 INSERT…SELECT 语句。

• 使用 INSERT…VALUES 语句插入单行或多行元组数据
• 使用 INSERT…SET 语句插入部分列值数据
  语法格式如下：

INSERT[INTO]tbl_name
SET col_name={expr|DEFAULT},…

• 使用 INSERT…SELECT 语句插入子查询数据
  语法格式如下：

INSERT[INTO]tbl_name[(col_name,…)]
SELECT…

删除数据

语法格式如下：

DELETE FROM tbl_name
	[WHERE where_condition]
	[ORDER BY ...]
	[LIMIT row_count]

修改数据

UPDATE 语句修改语法格式如下：

UPDATE tbl_name
	SET col_namel={exprl|DEFAULT}[,col_name2={expr2={espr2|DEFAULT}]...
	[WHERE where_condition]
	[ORDER BY ...]
	[LIMT row_count]