首页 > 代码库 > 精通SQL结构化查询---学习笔记2
精通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;
精通SQL结构化查询---学习笔记2