精通SQL结构化查询---学习笔记2

1、 SQL中表的规定：
 * 每张表的表名必须以字母开头，最大长度为30个字符。
 * 一张表可以由若干列组成。同一张表中，列名惟一，列名也称为属性名或字段。
 * 同一列的数据必须有相同的数据类型。
 * 表中的每一列值必须是不可分割的基本数据项。
 * 表中的一行称为一个元组，相当于一条记录。
2、 SQL数据类型
2.1、字符类型：包括固定长度和可变长字符变量，固定长度字符变量少于定义的长度时，数据库会自动在字符右边以空格填补到20个字符；可变长字符串可以存储任意长度的字符（其最大存储长度取决于采用的数据类型和数据库管理系统）
固定长度字符变量相比于可变长度字符变量，有以下两点优势：
1> DBMS在进行排序或处理字符时，对固定长度字符变量的处理效率要远高于可变长度字符变量。
2>许多DBMS不允许为可变长度字符变量创建索引，而只允许对固定长度字符变量创建索引。
Char 固定长度的字符串型。最大不能超过255个字节。
Varchar 可变长字符串型。
NChar Char类型的拓展，支持多字节和Unicode字符。
NVarchar Varchar类型的拓展，支持多字节和Unicode字符。
2.2、数字型数据
Bit 单比特数据，只能取值’0’或’1‘。如果输入0或1意外的值将被视为1。Bit类型不能定义为NULL值。
TinyInt 1比特的整数，其数值范围为0~255
SmallInt 2比特的整数，其数值范围为-32768~32767
Int或Interger 4比特整数，数值范围-2147483648~2147483647
Real 4比特浮点数，可以指定精度，但没有范围。
Double Precision 双精度型浮点数。
Float 可精确到第15位小数，每个Float类型的数据占用8个字节的存储空间，可以知道精度，而不指定范围。
Number 可存放实型和整型，
注意：不同的数据库管理系统所支持的数据类型不尽相同，如在SQL Server中，Number数据类型被写成Decimal或Nurneric。
2.3、日期数据类型
DATE 使用10个字符保存4位数的年、两位数的月和两位数的日的日期值，格式为：2014-01-01。
TIME 时间数值型，指定时、分、秒的值。除非指定，否则秒不包含小数；如，TIME(3)表示秒含有3位小数。
TIMESTAMP 既包括日期也包括时间。使用26个字符，其中10个字符用于保存日期，后跟一个空格，在用15个字符表示时间，其中包括了默认的6个小数位的小于1秒的部分。通过加入参数，

如TIMESTAMP(3)，可以指定不同的小数数目。
TIME WITH TIME ZONE 指定的信息与TIME数据类型相同，只是值还包括专用于UTC和时区的信息。
TIMESTAMP WITH TIME ZONE 在TIMESTAMP的基础上包括了UTC和时区的信息。
在ORACLE数据库中，日期型数据用7个字节表示，每个日期型数据包含如下内容：
1> Century 世纪
2> Year 年
3> Month 月
4> Day 天
5> Hour 小时
6> Minute 分
7> Second 秒
 

2.4、二进制数据类型
BINARY 定长二进制数据，最大长度从255到8000个字节不等，要根据具体的DBMS的定义。
VARBINARY 可变长二进制数据，最大长度从255到8000个字节不等，要根据具体的DBMS的定义。
RAW 定长二进制数据，最大长度为255个字节。
VARRAW 可变长二进制数据，最大可达2GB。
3、 创建表
在创建表时可以指定列级的约束，如：NOT NULL、DEFAULT、UNIQUE等。
子查询语句作为创建表的数据源
CREATE TABLE destion_table as SELECT * FROM source_table;
4、 表的修改
修改表名：ALTER TABLE old_table_name RENAME TO new_table_name;
--添加列
ALTER TABLE Test_Tab1 ADD column_1 NUMBER;
--修改列(包括数据类型、默认值等)
ALTER TABLE test_tab1 MODIFY column_1 NUMBER(5 );
--删除列
ALTER TABLE test_tab1 DROP COLUMN column_1;
--添加约束
ALTER TABLE test_tab1 ADD CONSTRAINT p_key  PRIMARY KEY (column_1);
--添加非空约束
ALTER TABLE test_tab1 MODIFY column_1 NOT NULL ;
--删除约束
ALTER TABLE Test_Tab1 DROP CONSTRAINT p_key;

5、 索引
索引是一个单独的、物理的数据库结构，是数据库的一个表中所包含的值的列表，其中注明了表的各个值所在的存储位置。索引是依赖于表建立的，提供了编排表中数据的方法；
索引分为两类，簇索引和非簇索引；簇索引对表的物理数据页中的数据按列进行排序，然后再重新存储到磁盘上，即簇索引与数据是混为一体的，它的叶节点中存储的是实际的数据。簇索引对数据一一进行了排序，因此用簇索引查找数据很快。
表的数据行只能以一种排序方式存储在磁盘上，所以一个表只能有一个簇索引。
非簇索引具有与表的数据完全分离的结构；一个表最多可以建249个非簇索引。

使用非簇索引的例子：
SELECT * FROM StudentInfo;

5.1、创建索引
CREATE INDEX Name_Index ON StudentInfo(sname);
查询时强制使用某种索引
SELECT * FROM StudentInfo WITH (INDEX (Name_Index))

UNIQUE:唯一性索引，不允许表中的行在索引列上取相同值。若已有相同值存在，则系统给出相关信息，不建此索引。
CLUSTERED/NONCLUSTERED:聚集和非聚集索引，若为CLUSTERED，则为聚集索引，即表中元组按索引项的值排序，并聚集在一起。一个基本表上只能建一个聚集索引。NONCLUSTERED表示创建的索引为非聚集索引。缺省时，创建的为非聚集索引。
ASC/DESC:索引表总索引值的排序次序，缺省为ASC(递增)。

使用簇集索引的表将占用最小的磁盘空间，因为DBMS在插入新行时，会自动地重用以前分配给删除行的空间。
对基于簇集索引的列值进行查询时，会有更快捷的执行速度，因为所有值在物理磁盘上相互靠近。
基于簇集索引的列以升序显示数据查询，不再需要ORDER BY子句，因为表的数据本身已经以所要求的输出顺序排序。

删除索引
DROP INDEX Index_name;

5.3、使用索引的原则：
1> 对于小的数据表，不需要对其创建索引；因为并不能提高任何检索性能。
2> 当用户要检索的字段的数据包含有很多数据值或很多空值时，为该字段创建索引，可以大大提高检索效率。
3> 如果用户检索的数据少于数据总数的25%，使用索引会显著提高查询效率，反之索引的用处不大。
4> 索引列在WHERE子句中应频繁使用。
5> 先装数据，后建索引。对于大多数的表，总有一批初始数据需要装入。该原则说，建立表后，先将这些初始数据装入表，然后再建索引，这样就可以加快初始数据的录入。如果键表后就建索引，那么在输入初始数据时，每插入一个记录都要维护一次索引。
6> 索引提高了数据检索的速度，但也降低了数据更新的速度。如果要对表中的数据进行大量更新时，最好先销毁索引，等数据更新完毕再建索引，这样会提高效率。
7> 索引要占用数据库空间。在设计数据库时，要把需要的索引空间考虑在内。
8> 尽量要把表和它的索引存放在不同的磁盘上，这样会提高查询速度。

6、 视图
6.1、视图的优缺点，主要表现在简化操作、定制数据、合并分隔数据、安全性等方面。
     使用视图的两个主要缺点：
     影响查询效率、更新限制（目前SQL将可更新的视图限制为基于对单个表的，并且没有GROUP BY 或者HAVING子句的查询）。除此之外，为了使视图是可更新的，视图不能使用聚集函数，计算的列或SELECT DISTINCT子句。
6.2、视图创建后，只在数据字典中存放视图定义，其中的SELECT语句并不执行。只有当用户对视图进行操作时，才按视图的定义将数据从基本表中取出。
6.3、创建、删除视图
CREATE VIEW StudentInfo_View AS SELECT * FROM StudentInfo;
由于视图是一个“虚表”，当表被删除时，由该表创建的视图，或仕途的视图都不可用。
DROP VIEW view_name;