数据库（3-1）

关系数据库标准语言SQL

SQL的特点
1. 综合统一
2. 高度非过程化
3. 面向集合的操作方式
4. 以同一种语法结构提供两种使用方法
5. 语言简洁，易学易用

关于关系数据语言的说法正确的是B
A 关系语言是一种高度过程化的语言
B 关系操作的特点是集合操作
C 关系数据模型的数据操作方式是一次一记录
D 以上说法均错误

支持SQL语言的关系数据库三级模式结构：
外模式对应于视图和部分基本表
模式对应于基本表
内模式对应于存储文件

视图和基本表的区别和联系

用户可以用SQL语言对基本表和视图进行查询或者其他操作
基本表和视图都是关系
基本表是本身独立存在的表。在SQL中一个关系对应一个表。一个（或者多个）基本表对应一个存储文件。一个表可以带若干索引，索引存放在存储文件中
视图是从一个或者几个基本表导出的表。本身不独立存储在DB中。DB中存放视图的定义而不存放视图对应的数据，这些数据仍然存放在导出视图的基本表中。视图是虚表

模式的定义和删除

Create schema <模式名> authorization <用户名>

调用创建模式命令的用户必须拥有DBA的权限或者获得了DBA授予的 create schema的权限。

create database --> 创建一个数据库 create schema --> 创建一个构架 当你在 SQL Server 里面， 使用 create database 创建一个数据库以后。 你可以不必额外的去创建 schema 因为 SQL Server 会 自动的创建一个 名字叫 dbo 的 schema 

给用户wang定义一个学生－课程模式s-t

Create schema  s-t   authorization wang;

Create schema authorization wang;

没有指定模式名，默认模式名为用户名

删除模式

Drop schema <模式名> <cascade|restrict>
其中cascade和restrict任选其一。
选择cascade(级联)，表示在删除模式的同时把该模式中所有的数据库对象全部一起删掉
选择restrict(限制)，表示如果该模式中已经定义了下属的数据库对象（表或图等），则拒绝该删除语句的执行。
Drop schema zhang cascade；

基本表的定义语句格式

CREATE TABLE <表名>
      （<列名> <数据类型>[ <列级完整性约束条件> ]
      [，<列名> <数据类型>[ <列级完整性约束条件>] ] …
      [，<表级完整性约束条件> ] ）；

列级完整性约束有：（只涉及到一个列）

Create table account(
Account_number char(10) Primary key,
Branch_name char(15) references branch(branch_name),
Balance real Check (balance >=0));

NULL/NOT NULL：空值/非空值约束。注明每行上的该列值是否允许为空。对于非主属性，若不注明此项约束，则隐含为空值约束（允许任何行上的该列值为空）
Primary Key：主码约束。
Unique：单值约束（惟一值约束）。注明该列上的所有取值必须互不相同
References<父表名>（<主码>）：外码约束。注明该列为外码。并给出对应的父表以及父表中被参照的主码
Check(<逻辑表达式>)：检查约束。

Identity:定义此列为标识列，由系统自动生成此列的值
Default：此列的默认值。如Default  (‘男’)，插入数据时，若不指定该列的值，则采用默认值

Create table deposit(
Customer_name char(20) not null,
Account_number char(10) not null,
Primary key(customer_name,account_number),
Foreign key(customer_name) references customer(customer_name),
Foreign key(account_number) references account(account_number));

<表级完整性约束>在所有列定义后给出，包括四种：（涉及到多个列时）
Primary Key(<列名>，……)主码约束。注明一个或同时多个列为主码。如Primary（课程号，教师号）。
Unique（<列名>，……）单值约束。注明一个或同时多个列为单值。如Unique（姓名，性别）定义了相应关系种的姓名和性别属性称为组合的单值属性
Foreign key(<列名>，……) References<父表名>（<列名>，……）:注明一个或同时多个列为外码，并给出对应的父表以及父表种被参照的主码中的所有列。
Check（<逻辑表达式>）：检查约束。 

完整性约束条件被存入系统的数据字典中。当用户操作表中的数据时，RDBMS自动检查该操作是否违背这些完整性约束条件。

注意：
Primary Key最多只能在一个表中声明一次。如果涉及到多个属性列， Primary Key只能作为表级完整性约束条件出现

PRIMARY  KEY与 UNIQUE的区别？
Primary key=Not null + Unique
但是Not null + Unique不一定就是主码约束。
一个表只能有一个Primary key
Unique可以取空值，但是非空的值必须唯一

主要支持的数据类型

Char(n):长度为n的定长字符串
Varchar(n):最大长度为n的变长字符串
Int:长整数
Smallint：短整数
Numeric(p,d):定点数，p位数字，小数后面d位数字
Real：取决于机器精度的浮点数
Double precision：取决于机器精度的双精度浮点数
Float(n)：浮点数，精度至少位n位数字
Date：日期，yyyy-mm-dd
Time:时间，hh：mm：ss

第一大类:整数数据
bit:bit数据类型代表0,1或NULL,就是表示true,false.占用1byte. int:以4个字节来存储正负数.可存储范围为:-2^31至2^31-1. smallint:以2个字节来存储正负数.存储范围为:-2^15至2^15-1 tinyint: 是最小的整数类型,仅用1字节,范围:0-255
第二大类:精确数值数据
numeric:用于精确存储数值。表示固定精度和小数位数。-10^38-1至10^38-1. 
例如常量 12.345 被转换为 numeric 值精度为 5，小数位为3，numeric（5,3）。 
decimal:和numeric差不多
第三大类:近似浮点数值数据
float:用8个字节来存储数据.最多可为53位.范围为:-1.79E+308至1.79E+308. real:位数为24,用4个字节,数字范围:-3.04E+38至3.04E+38
第四大类:日期时间数据
datetime:表示时间范围可以表示从1753/1/1至9999/12/31,时间可以表示到3.33/1000秒.使用8个字节. smalldatetime:表示时间范围可以表示从1900/1/1至2079/12/31.使用4个字节.
第五大类:字符串数据
char:长度是设定的,最短为1字节,最长为8000个字节.不足的长度会用空白补上. varchar:长度也是设定的,最短为1字节,最长为8000个字节,尾部的空白会去掉. text:长宽也是设定的,最长可以存放2G的数据

第六大类:Unincode字符串数据(统一码)
nchar:长度是设定的,最短为1字节,最长为4000个字节.不足的长度会用空白补上.储存一个字符需要2个字节. nvarchar:长度是设定的,最短为1字节,最长为4000个字节.尾部的空白会去掉.储存一个字符需要2个字节. ntext:长度是设定的,最短为1字节,最长为2G.尾部的空白会去掉,储存一个字符需要2个字节.
第七大类:货币数据类型
money:记录金额范围为:-92233720368577.5808至92233720368577.5807.需要8 个字节. smallmoney:记录金额范围为:-214748.3648至214748.36487.需要4个字节.
第八大类:标记数据
timestamp:该数据类型在每一个表中是唯一的!当表中的一个记录更改时,该记录的timestamp字段会自动更新. uniqueidentifier:用于识别数据库里面许多个表的唯一一个记录.
第九大类:二进制码字符串数据
binary:固定长度的二进制码字符串字段,最短为1,最长为8000. varbinary:与binary差异为数据尾部是00时,varbinary会将其去掉 image:为可变长度的二进制码字符串,最长2G.

[例1]  建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。
 

  CREATE TABLE Student
        (Sno       CHAR(5)  NOT NULL  UNIQUE， //空格隔开
         Sname  CHAR(20)  UNIQUE，          
         Ssex      CHAR(2) ，
         Sage      INT，
         Sdept    CHAR(15))； 

模式与表

每一个基本表都属于某一个模式，一个模式包含多个基本表。
定义基本表时如何定义它所属的模式，一般有三种方法
1，在表名中明显给出模式名(模式已经存在)
Create table “s-t”.student（…）;

2，在创建模式语句中同时创建表
Create schema test authorization zhang
Create table student（sno char(7),sname char(10)…）;

3，设置所属的模式，这样在创建表的时候就不需要给出模式名。

修改基本表

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名或列名> ]
[ Alter column <列名> <数据类型> ]；


<表名>：要修改的基本表
ADD子句：增加新列和新的完整性约束条件
DROP子句：删除指定的列或完整性约束条件
Alter column子句：用于修改列名和数据类型

向Student表增加“入学时间” Scome列，其数据类型为日期型。

ALTER TABLE Student ADD Scome DATETIME；

不论基本表中原来是否已有数据，新增加的列一律为空值。
增加列不写column！ 

将年龄的数据类型由字符型改为整数

 ALTER TABLE Student ALTER COLUMN Sage INT；

注：修改原有的列定义有可能会破坏已有数据

增加课程名称必须取唯一值的约束条件

ALTER TABLE course add UNIQUE(cname)；

修改student表的sno约束，要求sno只能写为“s”+三位整数的形式如s001

alter table student add constraint stu_1 check(sno like 's[0-9][0-9][0-9]');

删除属性列

间接删除
把表中要保留的列及其内容复制到一个新表中
删除原表
再将新表重命名为原表名
直接删除属性列:(新)
例：ALTER TABLE Student  Drop column Scome；

删除基本表 

DROP TABLE <表名> [restrict|cascade]; 

1)选择restrict:该表的删除是有限制条件的。欲删除的基本表不能被其他表的约束所引用，不能有视图，不能有触发器，不能有存储过程或者函数等。否则不能删除
2)选择cascade：该表的删除没有限制，在删除基本表的同时，相关的依赖对象将被一起删除。
缺省情况是restrict

Create view is_student as select sno,sname,sage
From student 
Where sdept=‘is’;


Drop table student restrict;
--error:cannot drop table student because other objects depend on it


Drop table student cascade;
--notice:drop cascades to view is_student


Select * from is_student
--error:relation “is_student ” does not exist

建立与删除索引 

建立索引是加快查询速度的有效手段
建立索引
DBA或表的属主（即建立表的人）根据需要建立系统在存取数据时会自动选择合适的索引作为存取路径，有些DBMS自动建立以下列上的索引
 1）PRIMARY  KEY
 2)UNIQUE
维护索引
 DBMS自动完成 
使用索引
 DBMS自动选择是否使用索引以及使用哪些索引

建立索引

语句格式

CREATE [UNIQUE] [CLUSTER] INDEX <索引名>
ON <表名>(<列名>[<次序>][,<列名>[<次序>] ]…)；	

CREATE UNIQUE INDEX Stusno ON Student (Sno desc)

[例]为学生-课程数据库中的Student，Course，SC三个表建立索引。其中Student表按学号升序建唯一索引，Course表按课程号升序建唯一索引，SC表按学号升序和课程号降序建唯一索引。

CREATE UNIQUE INDEX  Stusno ON Student(Sno)；
CREATE UNIQUE INDEX Coucno ON Course(Cno)；
CREATE UNIQUE INDEX SCno ON SC(Sno ASC，Cno DESC)； 

唯一值索引——unique index

对于已含重复值的属性列不能建UNIQUE索引
对某个列建立UNIQUE索引后，插入新记录时DBMS会自动检查新记录在该列上是否取了重复值。这相当于增加了一个UNIQUE约束

聚簇索引——cluster index
聚簇索引是指索引项的顺序与表中记录的物理顺序一致的索引组织

[例]create cluster index stusname on student (sname)；
在Student表的Sname（姓名）列上建立一个聚簇索引，而
且Student表中的记录将按照Sname值的升序存放
建立聚簇索引后，基本表中数据也需要按指定的聚簇属性值的升序或降序存放。也即聚簇索引的索引项顺序与表中记录的物理顺序一致

用户可以在最经常查询的列上建立聚簇索引以提高查询效率。
一个基本表上最多只能建立一个聚簇索引。
建立聚簇索引之后，更新该索引列上的数据时，往往导致表中记录的物理顺序的变更代价比较大。所以，对于经常更新的列不适合做聚簇索引。

删除索引

DROP INDEX <索引名>on <表名>；
删除索引时，系统会从数据字典中删去有关该索引的描述。

[例]  删除Student表的Stusname索引。

DROP INDEX Stusname on student；

在RDBMS中，索引一般采用B+树、hash索引来实现。

B＋树索引具有动态平衡的特点
Hash索引具有查找速度快的特点。
索引是关系数据库的内部实现技术，属于内模式的范畴。

在sc中的cno创建唯一值索引cnox，按cno降序

Create unique index cnox on sc(cno desc);