数据库之SQL

本文总阅读量次

[TOC]

SQL初级

SQL概览

SQL最早版本是由IBM开发的，最初被叫做Sequel。其发展至今被称为SQL（结构化查询语言），最新的SQL标准是SQL：2008

SQL语言有以下部分：

  • 数据定义语言（DDL）
  • 数据操纵语言（DML）
  • 完整性 保存在数据库中的数据必须满足完整性约束
  • 视图定义
  • 事务控制
  • 嵌入SQL和动态SQL
  • 授权

SQL数据定义

基本类型

  • char(n) 固定长度字符串，用户指定长度。如果字符长度不到n，在字符串后面补空格使其至n个字符串长度

  • varchar(n) 变长字符串

  • int 整数类型

  • smallint 小整数类型

  • numeric(p,d) 定点数，精度有用户指定

  • float(n) 精度至少为n的浮点数

基本模式

1
2
3
4
5
create table department
(dept_name varchar(20),
building varchar(15),
budget numeric(12,2),
primary key(dept_name));

这里简单讨论几个完整性约束

  • primary key (A1,A2…Am) primary key 声明属性构成关系主码，主码必须非空且唯一。
  • foreign key(A1,A2…Am) reference : × foreign key 表示声明关系中的任意元组必须在对应的外键属性上取值
  • not null 非空

SQL禁止破坏完整性约束的任何数据库更新，例如主码上的空值和重复将会被SQL标记错误并阻止更新

插入、删除和修改 的基本模式：

1
2
3
4
5
6
7
8
9
10
11
12
13
# 删除模式,（保留关系，只删满足条件的数据）
delete from student
# 删除模式，（表结构整体删除）
drop table r
# 修改表结构
# 表增加一列
alter table r add A D
# 表删除一列
alter table r drop A
# 修改某一列属性（不改名）
alter table r modify A D <Y>
# 修改某一列属性 （改名）
alter table r change A1 A2 D <Y>

SQL查询基本结构

SQL的基本查询结构由三个基本的子句组成：select from where 我们对其进行一个总结

  • select 子句用于列出查询结果中需要的属性
  • from 子句是一个查询求值中需要访问的关系列表
  • where 子句一个作用在from关系中的谓词

其基本的查询有如下形式

1
2
3
select A1,A2,...,An
from r1,r2,...,rm
where P

为了较好的理解这个关系的顺序是 from –> where –> select

首先通过from子句定义了一个在子句中列出关系的笛卡尔积，一般来说这个结果可能是一个相当大的关系，这样的笛卡尔积一般是没有意义的。而where子句谓词用来限制from子句所建立的集合，只留下对答案有意义的集合，最后我们可以用select限制结果中需要的子集

自然连接

组合信息是一种通用的过程，SQL支持一种被称为自然连接的运算。自然连接运算作用于两个关系，并产生一个关系作为结果。不同于两个关系上的笛卡尔积将第一个关系的每个元组与第二个关系的所有元组都进行连接；自然连接只考虑两个关系模式中都出现且属性相同的元素对。

1
2
3
select A1,A2,...An 
from r1 join r2, ... join rn
where P;

附加基本运算

更名运算
字符串运算

字符串可以使用like来实现模式匹配

  • % 匹配任意子串
  • _ 匹配任意一个字符
排序运算
where子句谓词
集合运算

SQL在关系上的union、intersect 、except 分别对应了∪、∩ 及 -

聚集函数

基本聚集
分组聚集

有时候我们不仅希望聚集函数作用力在单个元组集上，也希望作用到一组元组集合上，可以采用group by子句

并利用 having子句限定元组的条件

嵌套查询

数据库修改

删除
1
2
delete from r
where P;

插入

1
2
insert into r
values (A1,A2,...)
更新
1
2
update instructor 
set salary  = salary * 1.05;

小节

SQL 语言分为数据定义语言（DDL）和数据操纵语言（DML）

  • DDL： 定义关系模式、删除、修改关系模式

    create alter drop

  • DML: 包括查询语言、插入、修改、删除元组的命令

    select insert delete update

连接表达式

参与连接的任何一个或两个关系中的某些元组可能会以某种方式丢失，外链接以创建包含空值元组的方式保留了在连接中丢失的元组。

一般的，外链接有三种类型：

  • 左外连接： 只保留出现在左外连接运算之前（左边）关系中的元组
  • 右外连接：只保留出现在右外连接之后（后边）关系中的元组
  • 全外连接：保留出现在两个关系中的元组

事务

事务 由查询和更新语句的序列组成，SQL标准规定当一条SQL语句被执行，就隐式的开始了一个事务，下列SQL之一会结束一个事务

  • Commit work: 提交当前事务，将该事务所做功能新在数据库中持久保存。事务被提交后，一个新的事务自动刚开始
  • Rollback work：回滚当前事务，即撤销事务中所有的SQL语句对数据库的更新。这样数据库就恢复到执行事务第一条语句前的状态

当系统检测到错误时，事务回滚是有用的。而事务提交就像存盘，一旦commit他的影响就不能用rollback来撤销。数据库保证在发生SQL语句错误、断电、系统崩溃这样的故障情况下，如果事务没有完成commit，其影响将被回滚。如果断电和系统崩溃，回滚会在重启后进行。

一个事务在完成所有步骤后的提交行为，或者在不完成所有动作情况下回滚其动作，这种方式数据库提供了了对事务的 原子性 抽象，原子性也就是不可分割性，事务的影响被反映到数据库中，或是任何影响都没有。