环境说明:
数据库:Mysql 5.5
连接软件:Navicat
SQL总结系列目录:
SQL(Structured Query Language),称为结构化查询语言,是关系数据库的标准语言。其功能不仅仅是查询,而是包括数据库模式创建、数据库数据的插入与修改、数据库安全性完整性控制等一系列功能。
目前没有一个关系数据库系统(RDBMS)能够支持SQL标准的所有概念和特性。大部分数据库系统能支持SQL/92标准的大部分功能以及SQL99、SQL2003中的部分新概念。同时许多软件厂商对SQL基本命令集还进行了不同程度的扩充和修改,又可以支持标准以外的一项功能特性。因此,使用具体数据库系统时还需要参考相应的官方文档。
SQL总共由以下几部分组成:
数据字典是关系型数据库内部的一组系统表,他记录数据库中所有的定义信息,包括关系模式定义、视图定义、索引定义、完整约束定义、各类用户对数据库的操作权限、统计信息等。
本篇只涉及到DDL,即数据定义语言。如无特别说明,本篇博客中方括号内容表示可选内容。
SQL中的数据定义功能包括模式定义、表定义、视图和索引定义。
CREATE SCHEMA <模式名> AUTHORIZATION <用户名>;
定义模式实际上定义了一个命名空间,用户在创建模式的同时可以在这个模式中创建基本表、视图、定义授权等。即:
CREATE SCHEMA <模式名> AUTHORIZATION <用户名> [<表定义子句>|<视图定义子句>|<授权定义子句>];
DROP SCHEMA <模式名> ;
# CASCADE(级联)和 RESTICT(限制)两者必选其一
数据类型 | 含义 |
---|---|
CHAR(n),CHARACTER(n) | 长度为n的定长字符串 |
VARCHAT(n),CHARACTERVARYING(n) | 最大长度为n的变长字符串 |
CLOB | 字符串大对象 |
BLOB | 二进制大对象 |
INT,INTEGER | 长整数(4字节) |
SMALLINT | 短整数(2字节) |
BIGINT | 大整数(8字节) |
NUMERIC(p,d) | 定点数,由p位数字(不包括小数点、符号)组成,小数点后面有d位数字 |
DECIMAL(p,d),DEC(p,d) | 同NUMERIC |
REAL | 取决于机器精度的单精度浮点数 |
DOUBLE PRECISION | 取决于机器精度的双精度浮点数 |
FLOAT(n) | 可选精度的浮点数,精度至少为n位数字 |
BOOLEAN | 布尔类型 |
DATE | 日期,包含年、月、日,格式为YYYY-MM-DD |
TIME | 时间,包含一日的时、分、秒,格式为HH:MM:SS |
TIMESTAMP | 时间戳 |
INTERVAL | 时间间隔类型 |
这里要说明的是,不同的数据库产品支持的数据类型并不完全相同,具体使用时还需参考官方文档。
CREATE TABLE <表名> (<列名1> <数据类型> [列级完整性约束条件1],
<列名2> <数据类型> [列级完整性约束条件2],
...
[<表级完整性约束条件>]);
附:常用到的与表有关的约束条件:
查看当前数据库有多少表:
# 选中某一个数据库
USE <数据库名>;
# 查看该数据库的所有表
SHOW TABLES;
DROP TABLE <表名> [RESTRICT|CASCADE];
RESTRICT
与CASCADE
的区别:RESTRICT
指限制删除,表示该表的删除是有限制条件的:即该表不能被其他表的约束所引用(如CHECK
,FOREIGN KEY
等约束),不能存在依赖于该表的对象,比如视图、触发器、存储过程或者函数等。只有当这些限制条件不存在时,才能允许删除。CASCADE
指级联删除,加上此参数之后则该表的删除没有限制条件。在删除基本表的同时,相关的对象,例如视图等,都将被一起删除。 如果不指定删除类型时,默认是RESTRICT
。
这里的修改针对的是基本表的结构(如添加删除列、或者修改数据类型),并不是基本表的数据。对于基本表数据的修改属于DML的范围,本篇博客只涉及到DDL。
添加新列
ALTER TABLE <表名> ADD [COLUMN] <新列名> <数据类型> [完整性约束];
# 给已存在的列添加列级完整性约束
ALTER TABLE <表名> ADD [列级完整性约束条件];
添加新的表级约束条件
ALTER TABLE <表名> ADD <表级完整性约束条件>;
删除指定列
ALTER TABLE <表名> DROP [COLUMN] <列名> [CASCADE|RESTRICT];
删除指定的完整性约束条件
ALTER TABLE <表名> DROP CONSTRAINT <完整性约束名> [RESTRICT|CASCADE];
修改表中已存在的列
ALTER TABLE <表名> ALTER COLUMN <列名> <数据类型>;
建立索引的目的:加快查询速度
缺点:索引虽然能够加速数据库查询,但需要占用一定的存储空间,并且当基本表更新时,索引也需要进行相应的维护。这些都会增加数据库的负担,因此要根据实际应用的需要有选择地创建索引。
目前SQL标准中没有涉及索引,但商用关系数据库系统一般都会支持索引机制,且不同数据库支持的索引类型不尽相同。
顺序文件上的索引:针对按指定属性值升序和降序存储的关系,在该属性上建立一个顺序索引文件,索引文件由属性值和相应的元组指针组成。
B+树索引:将索引属性组织成 B+树的形式,B+树的叶节点为属性值和相应的元组指针。B+树索引具有动态平衡的优点。
散列(hash)索引:建立若干个桶,将索引属性按照其散列函数映射到相应桶中,桶中存放索引属性和相应的元组指针。散列 索引具有查找速度快的特点。
位图索引:用位向量记录索引属性中可能出翔的值,每个位向量对应一个可能值。
CREATE [UNIQUE] [CLUSTER] INDEX <索引名> ON <表名> (<列名1>[<次序1>],···);
<表名>
是要建索引的基本表的名字<次序>
指定索引值的排列次序,可选ASC(升序)
或者DESC(降序)
。默认ASC
。UNIQUE
表明此索引的每一个索引值只对应唯一的数据记录。CLUSTER
此索引是聚簇索引。ALTER INDEX <旧索引名> RENAME TO <新索引名>;
要修改索引本身的话,建议删除再重建。
DROP INDEX <索引名>
视图是从一个或几个基本表(或者视图)导出的表。
视图一经定义,就可以和基本表一样被查询、被删除。也可以在一个视图上再定义新的视图,但对视图的更新(增、删、改)操作则有一定的限制。
数据库只存放视图的定义,而不存放视图中对应表的数据(否则数据库中便存放了很多相同的数据),这些数据仍存放在原来的基本表中。
归根到底,视图与“图”无关,其实质上还是表。只不过由于不存放数据,只存放定义,因此称其为“虚表”。
GROUP BY子句
的查询的视图。CREATE VIEW <视图名> (<列名>,<列名>,<列名> ...) AS <子查询> [WITH CHECK OPTION];
<子查询>
是针对基本表的SELECT语句,即从建立视图的基本表中选取部分数据,而不是全部数据[WITH CHECK OPTION]
是一个条件表达式,有这个条件表达式时,对视图进行UPDATE、INSERT和DELETE时如果要操作的行不满足这里的条件,则不允许进行CREATE VIEW语句
的结果只是把视图的定义存入数据字典,并不执行其中的SELECT语句
。只有在进行视图的查询时,才会执行SELECT语句
。以下三种情况必须要指明视图的列名:
视图其本质上还是表,因此可以对其进行查询。查询视图与查询表的语句基本相同。详见DQL。
在视图查询的过程中,会经过视图消解,将对视图的查询转换为对基本表的查询。
视图消解:关系型数据库执行视图的查询操作时,首先进行有效性检查,即确定查询中涉及到的表、视图等是否都存在。如果存在,则从数据字典中取出视图的定义,把定义中的子查询和用户的查询结合起来,转换成等价的对基本表的查询,然后再执行修正了的查询。这一转换过程称之为视图消解。
局限:目前多数关系数据库对行列子集视图都能正确地转换。但对非行列子集视图的查询就不一定能做转换了,因此这类查询应该直接对基本表进行。
非行列子集视图:图中的部分列由其他表的列经过运算得出。
视图查询与基于派生表的查询的区别:
视图的更新包括INSERT、DELETE、UPDATE,其操作语句与表的操作语句基本相同。此处不再详述。详见DML。
类似于视图的查询,对视图的更新同样是通过视图消解,转换为对基本表的更新操作。
目前各个关系数据库一般只允许对行列子集视图进行更新,而且不同的数据库对视图的更新还有更进一步的规定。由于各数据库系统实现方法上的差异,这些的规定也不尽相同。
DROP VIEW <视图名> [CASCADE];
CASCADE
是可选的CASCADE
参数之后将会把该视图导出的视图一块删除。SQL可以分为数据定义(DDL)、数据查询(DQL)、数据更新(DML)、数据控制(DCL)四大部分。
综上,DDL中的基本操作可以用表格简单总结一下:
操作对象 | 创建 | 删除 | 修改 |
---|---|---|---|
模式 | CREATE SCHEMA | DROP SCHEMA | |
表 | CREATE TABLE | DROP TABLE | ALTER TABLE |
视图 | CREATE VIEW | DROP VIEW | |
索引 | CREATE INDEX | DROP INDEX | ALTER INDEX |
主要参考资料:《数据库系统概论(第5版)》 王珊 萨师煊 编著