DBMS :数据库管理系统
数据库有如下几种:
其中关系数据库是理论最成熟,应用最广泛的,其最基本的数据存储单元是数据表。
数据表就是存储数据的逻辑单元,可以把数据表想象成由行和列组成的表格,其中每一行被称为记录,每一列被称为一个字段。
此外,还应该为每个数据表指定一个特殊列,该特殊列的值可以唯一地标识此行的记录,则该特殊列被称为主键列。
本文包含:
- 关系数据库概念和基本命令
- SQL 语句基础
- DDL 语句
- 数据库约束
- 索引
- 视图
- DML 语句语法
- 单表查询
- 数据库函数
- 分组和组函数
- 多表连接函数
- 子查询
- 集合运算
1. 关系数据库概念和基本命令
如果将多条MySQL 命令写在一份 SQL 脚本文件中,然后把这份 SQL 脚本的内容一次复制到该窗口里,则可以看到该命令行客户端一次性执行所有的 SQL 命令的效果。这种一次性执行多条SQL 命令的方式也被称为导入 SQL 脚本。
MySQL 数据库通常支持如下两种存储机制:
2. SQL 语句基础
SQL 全称是 Structured Query Language,即结构化查询语言。SQL 是操作和检索关系数据库的标准语言,标准的SQL 语句可用于操作任何关系数据库。
使用 SQL 语句,程序员和数据库管理员(DBA)可以完成如下任务:
在上面 5 个任务中,一般程序员可以管理前 3 个任务,后面 2 个任务通常由 DBA 来完成。
标准的 SQL 语句通常可分为如下几种类型:
SQL 语句不区分大小写。
SQL 语句标识符(用于定义表名、列名、变量等)这些标识符的命名规则如下:
3. DDL 语句
常见的数据库对象如下:
-
创建表的语句
create table [模式名.]表名
(
#可以有多个列定义
columeName1 datatype [default expr],
…
)
例如下面的建表语句:
如果使用子查询建表语句,则可以在建表的同时插入数据。
子查询建表语句的语法如下:
-
修改表的结构的语法
-
删除表的语法
-
truncate 表
4. 数据库约束
数据库约束可以更好地保证数据表里数据的完整性。约束是在表上强制执行的数据校验规则,用于保护数据库里数据的完整性。此外,当表中数据存在相互依赖性时,可以保护相关的数据不被删除。
大部分数据库支持下面 5 种完整性约束:
虽然大部分数据库都支持上面 5 种约束,但 MySQL 不支持 CHECK 约束。
约束也是数据库对象,并被存储在系统表中,也拥有自己的名字。
根据约束对数据列的限制,约束分为如下两类:
为数据表指定约束时有如下两个时机:
大部分约束都可以采用列级约束语法或表级约束语法。
下面介绍五种约束。
- NOT NULL 约束
- UNIQUE 约束
- PRIMARY KEY 约束
- FOREIGN KEY 约束
- CHECK 约束
5. 索引
创建索引有两种方式:
删除索引也有两种方式:
创建索引的用法是:
索引也有两个坏处:
有一定的系统开销
存储索引信息需要一定的磁盘空间
6. 视图
创建视图的语法如下:
视图的本质,就是一条被命名的 SQL 查询语句。
7. DML 语句语法
DML 主要操作数据表里的数据,使用DML 可以完成下面三个任务:
- insert into 语句
下图为运行结果:
上面语句的结果中 abc 记录的主键列的值是 2,而不是 SQL 语句插入的 null,因为该主键列是自增长的,系统会自动为该列分配值。
根据前面介绍的外键约束规则:外键列里的值必须是被参照列里已有的值,所以向从表中插入记录之前,通常应该先向主表中插入记录。否则从表记录的外键列只能为 null,现在主表 teacher_table2中已有了 2 条记录,现在可以向表 student_table2 中插入记录了,如下 SQL 语句所示:
在一些特别情况下,可以使用带子查询的插入语句,带子查询的插入语句一次可以插入多条记录,如下 SQL 语句所示:
- update 语句
8. 单表查询
MySQL 使用 concat 函数来进行字符串连接运算:
此外,SQL 还支持下表所示特殊运算符:
9. 数据库函数
根据函数对多行数据的处理方式,函数被分为单行函数和多行函数,单行函数对每行输入值单独计算,每行得到一个计算结果返回给用户,多行函数对多行输入值整体计算,最后只会得到一个结果。
如下图所示:
SQL 语句中的函数和 Java 语言中的方法有点类似,但 SQL 中的函数是独立的程序单元,也就是说,调用函数时无须使用任何类、对象作为调用者。而是直接执行函数。执行函数的语法如下:
多行函数也称为聚集函数、分组函数,主要用于完成一些统计功能,在大部分数据库中基本相同。
但不同数据库中的单行函数差别很大,MySQL 中的单行函数具有如下特征。
MySQL 的单行函数分类如下图所示:
MySQL 数据库的数据类型大致分为数值型、字符型和日期时间型,所以MySQL 分别提供了对应的函数。转换函数主要负责完成类型转换,其他函数大致分为如下几类。
MySQL 提供了如下几个处理 null 的函数。
MySQL 还提供了一个 case 函数,该函数是一个流程控制函数。case 函数有两个用法,case 函数第一个用法的语法格式如下:
case 函数用 value 和后面的 compare_value1、compare_value2、…依次进行比较,如果value 和指定的 compare_value1 相等,则返回对应的 result1,否则返回 else 后的 result。
例如如下 SQL 语句:
case 函数第二个用法的语法格式如下:
在第二个用法中,condition1、condition2 都是一个返回 boolean 值的条件表达式,因此这种用法更加灵活。
例如如下 SQL 语句:
10. 分组和组函数
组函数即前面提到的多行函数,组函数将一组记录作为整体计算,每组记录返回一个结果,而不是每条记录返回一个结果。
常用的组函数有如下 5 个:
在默认情况下,组函数会把所有记录当成一组,为了对记录进行显式分组,可以在 select 语句后使用 group by 子句,group by 子句后通常会跟一个或多个列名,表明查询结果根据一列或多列进行分组—当一列或多列组合的值完全相同时,系统会把这些记录当成一组。如下SQL 语句所示:
如果对多列进行分组,则要求多列的值完全相同才会被当成一组。如下 SQL 语句所示:
对于MySQL 来说,并没有上面的要求,如果某个数据列既没有出现在 group by 之后,也没有使用组函数包装起来,则 MySQL 会输出该列的第一条记录的值。
下图显示了 MySQL 的处理结果:
如果需要对分组进行过滤,则应该使用having 子句,having 子句后面也是一个条件表达式,只有满足该条件表达式的分组才会被选出来。having 子句和 where 子句非常容易混淆,它们都有过滤功能,但它们有如下区别:
如下 SQL 语句所示:
11. 多表连接函数
多表查询有两种规范。较早的 SQL 规范支持如下几种多表连接查询:
SQL规范提供了可读性更好的多表连接语法,并提供了更多类型的连接查询。SQL 支持如下几种多表连接查询:
- SQL 92 的连接查询
SQL 92 的多表连接语法比较简洁,这些语法把多个数据表都放在 from 之后,多个表直接以逗号隔开;连接条件放在 where 之后,与查询条件直接用 and 逻辑运算符连接。如果连接条件要求两列值相等,则称为等值连接,否则称为非等值连接;如果没有任何连接条件,则称为广义笛卡尔积。
SQL 92 中多表连接查询的语法格式如下:
多表连接查询可能出现两个或多个数据列具有相同的列名,则需要在这些同名列之间使用表名前缀或表别名前缀作为限制,避免系统混淆。
实际上,所有的列都可以增加表名前缀或表别名前缀。只是进行单表查询时,绝不可能出现同名列,所以系统不可能混淆,因此通常省略表名前缀。
如下 SQL 语句查询出所有学生的资料以及对应的老师姓名。
执行上面查询结果,将看到下图所示结果:
上面的查询结果正好满足要求,可以看到每个学生以及他对应的老师的名字。实际上,多表查询的过程可以理解为一个嵌套循环,这个嵌套循环的伪代码如下:
理解了上面的伪码之后,接下来可以轻易地理解多表连接查询的运行机制。如果求广义笛卡尔积,则 where 子句之后就没有任何连接,相当于没有上面的 if 语句,广义笛卡尔积的结果会有 n x m 条记录。只有把 where 后的连接条件去掉,就可以得到笛卡尔积,如下 SQL 语句所示:
与此类似的是,非等值连接的执行结果可以使用上面的循环嵌套来计算,如下 SQL 语句所示:
上面 SQL 语句的执行结果相当于 if 条件换成了 s.java_teacher > t.teacher_id。
如果还需要对记录进行过滤,则将过滤条件和连接条件使用 and 连接起来。如下 SQL 语句所示:
12. 子查询
13. 集合运算
- union 运算
- minus 运算
- intersect 运算