永利酒店赌场:视图、序列、索引

约束

永利酒店赌场 1 

一、数据库完整性概述

iOS应用数据存储的常用方式

视图

视图(VIEW)也被称作虚表,即虚拟的表,是一组数据的逻辑表示。
视图对应于一个SELECT语句,结果集被賦予一个名字,即视图名字。
视图本身并不包含任何数据,它只包含映射到基表的一个查询语句,当基表数据发生变化,视图数据也随之变化。

使用视图的两种情况:

  1. 使用的查询基于非常复杂的查询之上。
  2. 查询的时候希望屏蔽数据库中真实的字段名和数据

CREATE [OR REPLAC] VIEW view_name[(alias[, alias...])] AS subquery [WITH CHECK OPTION];

视图创建后,主要是查询Subquery是SELECT查询语句,对应的表称为基表。
根据所对应的子查询种类分为:简单视图,复杂视图,连接视图。

先根据我们需要查询的视图所对应的SQL将数据检索出来,然后再根据我们对视图查询的需求,检索视图查询出来的数据,所以使用视图是两次查询。

极其不推荐对视图进行DML操作

视图中没有出现的字段,插入的时候都为NULL,所以有非空约束的时候,插入失败。
WITH CHECK OPTION具有检查约束的功能。

还可以创建只读视图。

CREATE [OR REPLAC] VIEW view_name[(alias[, alias...])] AS subquery [WITH CHECK OPTION];

创建复杂视图必须为子查询的表达式或函数定义别名。

删除视图:DROP VIEW view_name;

主关键字约束(Primary Key Constraint)

用来指定表中的一列或几列组合的值在表中具有唯一性。建立主键的目的是让外键来引用。

from Database Design to Physical Form

 

1. Plist存储(属性列表)

  • Plist存储(Documents)

// 1.Plist存储(生成plist文件)
// 在Plist文件中不能保存自定义对象*+
- (IBAction)saveBtn:(UIButton *)sender {
    // 参数:搜索的目录,搜索的范围,是否展开路径
    NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    NSLog(@"%@",path);

    // 1.把数组保存到沙盒
    NSArray *dataArray = @[@"LN",@10];
    NSString *filePath = [path stringByAppendingPathComponent:@"data.plist"];
    NSLog(@"%@",filePath);

    // 写入沙盒路径
    [dataArray writeToFile:filePath atomically:YES];

    // 2.把字典保存到沙盒
    NSDictionary *dict = @{@"name":@"LN",@"age":@10};
    NSString *dictPath = [path stringByAppendingPathComponent:@"dict.plist"];
    [dict writeToFile:dictPath atomically:YES];
}
  • Plist提取
  • 特点: 只能存储系统自带的数据类型, 比如NSDictory, NSArray等等.
    自定义的对象无法存储

// 2.Plist提取
- (IBAction)readBtn:(UIButton *)sender {
    NSString *path = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
    NSString *filePath = [path stringByAppendingPathComponent:@"data.plist"];
    NSArray *dataArray = [NSArray arrayWithContentsOfFile:filePath];
    NSLog(@"%@",dataArray);

    NSString *filePath1 = [path stringByAppendingPathComponent:@"dict.plist"];
    NSDictionary *dict = [NSDictionary dictionaryWithContentsOfFile:filePath1];
    NSLog(@"%@",dict);
}

通过查询user_views获取相关信息

和视图相关的数据字典:

  • USER_OBJECTS 保存用户的所有数据库对象,表,视图,索引,序列等
  • USER_VIEWS 保存了用户所创建的视图
  • USER_UPDATE_COLUMNS

MySQL的字典不同。

Primary Key的创建方式

在创建表时创建Primary Key

CREATE TABLE table1(
    t_id VARCHAR(12) ,
    t_name VARCHAR(20),
    t_phone VARCHAR(20),
    CONSTRAINT t_idss PRIMARY KEY(t_id)
);

对t_id列创建主键,约束名为t_idss。

CREATE TABLE

1.数据库的完整性:

2. preference(偏好设置)

  • 特点: 本质就是一个plist文件; 也是只能存储系统自带的数据类型,
    自定义的对象无法存储

  • NSUserDefaults:
    用来保存应用程序设置和属性、用户保存的数据。用户再次打开程序或开机后这些数据仍然存在。

  • NSUserDefaults可以存储的数据类型包括:NSData、NSString、NSNumber、NSDate、NSArray、NSDictionary。如果要存储其他类型,则需要转换为前
    面的类型(一般转换成NSData),才能用NSUserDefaults存储。

  • 偏好设置存储(Library/Preferences)

#define LNname @"name"
#define LNage @"age"

// 1.偏好设置存储(key存取,最好定义成宏以防写错)
// 偏好设置不能保存自定义对象*+
- (IBAction)saveBtn:(UIButton *)sender {
    // NSUserDefaults保存也是plist文件
    NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];
    [defaults setObject:@"LN" forKey:LNname];
    [defaults setObject:@10 forKey:LNage];

    // 写入文件当中
    [defaults synchronize];
    NSLog(@"%@",NSHomeDirectory());
}
  • 偏好设置提取

// 2.偏好设置提取
- (IBAction)readBtn:(UIButton *)sender {
    NSUserDefaults *defaults = [NSUserDefaults standardUserDefaults];
    NSString *name = [defaults objectForKey:LNname];
    NSInteger age = [defaults integerForKeyLNage];

    NSLog(@"%@ %ld",name,age);
}

序列

序列(SEQUENCE)是一种用来生成唯一数字值的数据库对象;通常我们用来生成主键。
序列是独立的数据库对象,和表是独立的对象,序列并不依附于表;
通常情况下,一个序列为一个表提供主键值,但一个序列也可以为多个表提供主键值。尽可能不要共用,避免主键断裂。

CREATE SEQUENCE [schema.]sequence_name
[START WITH i] [INCREMENT BY j]
[MAXVALUE m|NOMAXVALUE]
[MIN VALUE n|NOMIN VALUE]
[CYCLE|NOCYCLE][CACHE p|NOCACHE]

CREATE SEQUENCE emp_seq START WITH 100 INCREMENT BY 10;

sequence_name是序列名,将创建在schema方案下;
序列的第一个序列值是i ,步进是j;
如果j是正数,表示递增,如果是负数,表示递减。
START WITH默认值是1,INCREMENT
BY默认值是1,NOCYCLE默认,CACHE默认开启,默认值是20;

**MySQL自增长与Oracle序列的区别:
自增长只能用于表中的其中一个字段
自增长只能被分配给固定表的固定的某一字段,不能被多个表共用.
自增长会把一个未指定或NULL值的字段自动填上.
mysql的AUTO_INCREMENT可以设置起始值,但是不能设置步长,其步长默认就是1.
**

序列有两种伪列:

  • NEXTVAL:获取序列的下一个值;
  • CURRVAL:获取序列的当前值;

注意,序列创建以后,必须先执行一次NEXTVAL才能使用CURRVAL。

删除序列:
DROP SEQUENCE sequence_name;

删除Primary Key
ALTER TABLE table1
DROP CONSTRAINT t_idss;

约束名与列名不一致,此处填写约束名

integrity constraints (完整性约束)

 

3. NSKeyedArchiver归档(NSCoding协议)

  • 特点: 可以存储自己定义的数据类型, 但是都是一次性的全数据操作

Person

//  Person.h
#import <Foundation/Foundation.h>
@class Dog;
@interface Person : NSObject<NSCoding>

@property (nonatomic, strong) NSString *name;
@property (nonatomic, assign) int age;
@property (nonatomic, strong) Dog *dog;
@end

//  Person.m
#import "Person.h"
@implementation Person

// 归档存储会调用(在保存对象时要告诉保存对象那些属性)
- (void)encodeWithCoder:(NSCoder *)aCoder{
    [aCoder encodeObject:self.name forKey:@"name"];
    [aCoder encodeInt:self.age forKey:@"age"];
    [aCoder encodeObject:self.dog forKey:@"dog"];
}

// 归档提取(解析文件的时候)会调用,(告诉当前要解析文件当中哪些属性)
- (instancetype)initWithCoder:(NSCoder *)aDecoder{
    /*
        当只有遵守了NSCoding协议时,才有[super initWithCoder]
    @interface Person : NSObject (父类NSObject没有遵守NSCoding协议,继承UIView就可以)
    */
    if (self = [super init]) {
        self.name = [aDecoder decodeObjectForKey:@"name"];
        self.age = [aDecoder decodeIntForKey:@"age"];
        self.dog = [aDecoder decodeObjectForKey:@"dog"];
    }
    return self;
}
  • 归档存储(tmp,生成的文件不可以直接打开)

// 归档
// 保存自定义对象(模型类要遵守<NSCoding>协议)*+
- (IBAction)saveBtn:(UIButton *)sender {Person *person = [[Person alloc] init];
    person.name = @"LN";
    person.age = 10;

    Dog *dog = [[Dog alloc] init];
    dog.name = @"xiao Hui";
    person.dog = dog;

    // 获取沙盒目录
    NSString *tmpPath = NSTemporaryDirectory();
    NSString *filePath = [tmpPath stringByAppendingPathComponent:@"Person.data"];
    NSLog(@"%@",NSTemporaryDirectory());

    // 归档(archiveRootObject会调用encodeWithCoder:)
    [NSKeyedArchiver archiveRootObject:person toFile:filePath];
}
  • 归档提取

// 2.归档提取
- (IBAction)readBtn:(UIButton *)sender {
    // 获取沙盒目录
    NSString *tmpPath = NSTemporaryDirectory();
    NSString *filePath = [tmpPath stringByAppendingPathComponent:@"Person.data"];

    //归档提取 unarchiveObjectWithFile会调用initWithCoder:
    Person *person = [NSKeyedUnarchiver unarchiveObjectWithFile:filePath];
    NSLog(@"%@--%@",person.name,person.dog.name);
}
  • 注意点
    initWithCoderawakeFromNib

//从xib当加载的时候 (是加载完毕时调用)
-(void)awakeFromNib{
    NSLog(@"awakeFromNib==%@",self.btn);
    // awakeFromNib==<UIButton: 0x7ffd08c1e450; frame = (100 153; 46 30); opaque = NO; autoresize = RM+BM; layer = <CALayer: 0x7ffd08c1af60>>
}

//当解析一个文件的时候会调用initWithCoder (一开始加载Xib就调用)
-(instancetype)initWithCoder:(NSCoder *)aDecoder{
    if(self = [super initWithCoder:aDecoder]){
        NSLog(@"initWithCoder==%@",self.btn);
        // initWithCoder==(null)
    }
    return self;
}

索引

索引是用来加快对某一张表的查询效率的。

CREATE [UNIQUE] INDEX index.name ON table_name(column[,
column…]);
index_name表示索引名称
table_name表示表名
column表示列名,可以建立单列索引或复合索引,也可以是函数。
UNIQUES表示唯一索引

CREATE INDEX idx_emp_ename ON emp(ename);
CREATE INDEX idx_emp_job_sal ON emp(job,sal);
CREATE INDEX idx_emp_ename_upper ON emp(UPPER(ename));

查询的时候自动使用索引。

修改重建索引:
ALTER INDEX index_name REBUILD;

删除索引:
DROP INDEX index_name;

合理使用索引提升查询效率:

  • 为经常出现在WHERE子句中的列创建索引
  • 永利酒店赌场:视图、序列、索引。为经常出现在ORDER
    BY、DISTINCT后面的字段建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致
  • 为经常作为表的连接条件的列上创建索引
  • 不要在经常做DML操作的表上建立索引
  • 不要在小表上建立索引
  • 限制表上的索引数目,索引并不是越多越好
  • 删除很少被使用的、不合理的索引
  • 数据访问很频繁,检索所占的比重占2%~4%,宜加索引
  • 检索的字段有大量的null时
向已有表中添加Primary Key
ALTER TABLE table1
ADD CONSTRAINT t_idss
PRIMARY KEY(t_id);

CREATE VIEW

①数据库的完整性是指数据的正确性和相容性

4. SQLite3

  • 数据库(Database)是按照数据结构来组织、存储和管理数据的仓库

  • 数据库可以分为2大种类

    • 关系型数据库(主流):如,MySQL
    • 嵌入式/移动客户端 :如,SQLite 对象型数据库
  • 特点: 存储一些大批量的数据, 排序, 统计等操作

约束

约束(CONSTRAINT)的全称是约束条件,也称作完整性约束条件。

约束条件包括:

  • 非空约束(Not Null),简称NN
  • 唯一性约束(Unique),简称UK
  • 主键约束(Primary Key),简称PK
  • 外键约束(Foreign Key),简称FK
  • 检查约束(Check),简称CK
添加Primary Key的另一种示例
ALTER TABLE Products
ADD PRIMARY KEY(prod_id);

永利酒店赌场,虽然上述代码运行没问题,查看表格设计也可以看到Primary
Key设置成功,但是在删除Primary Key操作时会提示:
消息3728,级别16,状态1,第1 行
‘prod_id’ 不是约束。
消息3727,级别16,状态0,第1 行
未能删除约束。请参阅前面的错误信息。
原因是添加Primary
Key语句中没有用CONSTRAINT指明约束名,系统自动生成了主键名和约束名,要先查看主键名和约束名,删除时填写的也是约束名。
这种情况的正确删除方法

ALTER TABLE Products
DROP CONSTRAINT CK__Products__prod_p__1A14E395;
ALTER TABLE Products
DROP CONSTRAINT PK__Products__56958AB222AA2996;

Security

②数据库完整性是防止不合语义或不正确的数据进入数据库

什么是SQLite

  • SQLite是一款轻型的嵌入式数据库
  • 它占用资源非常的低,在嵌入式设备中,可能只需要几百K的内存就够了
  • 它的处理速度比Mysql、PostgreSQL这两款著名的数据库都还快

非空约束

建表是添加NOT NULL,NOT NULL只能在声明和修改时设置非空约束。
可以如下方法添加:

CREATE TABLE employees(
eid INT(6),
name VARCHAR(30) NOT NULL,
salary FLOAT(7,2),
hiredate DATE 
CONSTRAINT employees_hiredate_nn NOT NULL
);

NOT NULL是列级约束,在声明列的同时加的约束。

表级约束:单独定义的约束(定义列之后)。

修改表是添加非空约束:
可以通过修改表的定义,添加非空约束:

ALTER TABLE employees MODIFY eid INT(6) NOT NULL;

取消非空约束:

ALTER TABLE employees MODIFY eid INT(6) NULL;
多列组合添加主键约束
CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT ts_id PRIMARY KEY(t_id,s_id)
);

The GRANT & REVOKE statements

③完整性体现了是否真实地反映现实世界

如何存储数据到数据库

  • 数据库的存储结构和excel很像,以表(table)为单位

唯一性约束

唯一性(Unique)保证字段不出现重复值。
允许有NULL,唯一性可以是列级,也可以是表级。null不是任何值,可以有多个null。

CREATE TABLE employees(
--以下是列级约束
eid INT(6) UNIQUE,
name VARCHAR(30),
email VARCHAR(50),
salary FLOAT(7,2),
hiredate DATE,
--以下是表级约束
CONSTRAINT employees_email_uk UNIQUE(email) 
);

NOT NULL是列级约束,列级约束只能设置一个,所以列级的时候设置NOT
NULL,再将UNIQUE设置为表级约束

建表后添加唯一性约束条件:

ALTER TABLE employees ADD CONSTRAINT employees_name_uk UNIQUE(name);
外关键字约束(Foreign Key Constraint)

定义了表之间的关系,用来维护两个表之间的一致性的关系。
在创建表时创建Foreign Key Constraint

CREATE TABLE table2(
    s_id VARCHAR(20),
    s_name VARCHAR(12),
    s_tellphone VARCHAR(11),
    s_address VARCHAR(20),
    CONSTRAINT PK_s_id PRIMARY KEY(s_id),
);--首先新建table2,设置s_id为主键

CREATE TABLE table1(
    t_id VARCHAR(12),
    s_id VARCHAR(20),
    score FLOAT,
    CONSTRAINT pk_ts_id PRIMARY KEY(t_id,s_id),--新建table1,对t_id和s_id设置联合主键,键名pk_ts_id
    CONSTRAINT fk_s_id FOREIGN KEY(s_id)--对s_id设置外键fk_s_id
    REFERENCES table2(s_id)--外键fk_s_id外键关联table2的列s_id
    ON DELETE CASCADE--设置在table1的s_id删除时table2的s_id同时删除
    ON UPDATE CASCADE--设置在table1的s_id更新时table2的s_id同时更新
);

注:对table1设置外键关联table2,在插入数据时需要先插入table2的数据,才能成功插入table1的数据。更改table2.s_id数据,table1.s_id数据也会自动改变。但是更改table1.s_id数据,执行时报外键冲突。总之对table1设置外键关联table2后,table1的数据跟着table2走,不能反着来。

添加和删除外键约束同主键。

Catalogs

 

数据库存储数据的步骤

  • 新建数据库文件
  • 新建一张表(table)
  • 添加多个字段(column,列,属性)
  • 添加多行记录(row,每行存放多个字段对应的值)

Navicat 是一套适用于MySQL,
SQLite等多个数据库系统地图形化数据库管理、报告以及监控的工具。具有高性能的、商业智能的、强大的备份功能

主键约束

主键(Primary Key)约束条件非空 (NUT
NULL)且唯一(UNIQUE),一个表只允许一个主键约束。

主键应是对系统无意义的数据;

主键不应该包含动态变化的数据,如时间戳;

主键应自动生成,不要人为干预,以免使它带有除了唯一标识一行以外的意义;

常见的主键生成的形式:序列,UUID,

ORACLE生成UUID
SELECT sys_guid() FROM DUAL;
INSERT INTO emp VALUES(sys_guid(),'xxxx'....);

添加主键约束:

CREATE TABLE employees2(
eid INT(6) PRIMARY KEY,
name VARCHAR(30));
也可以采用表级约束:
CONSTRAINT employees2_eid_pk PRIMARY KEY(eid);

添加主键约束:

ALTER TABLE employees3 ADD CONSTRAINT
employees_eid_pk PRIMARY KEY(eid);
UNIQUE约束

除主键外另一种可以定义唯一约束的类型,允许空值。添加UNIQUE的方法同上,这里只简单举例。

USE test
GO
ALTER TABLE table2
ADD CONSTRAINT uk_s_tellphone
UNIQUE(s_tellphone);

Schemas

例: 

理论基础:

  • 表格组成: 行(记录)和列(属性)

  • “属性” 是用来标识这一列应该存放什么

  • “记录” 是用来存放一条数据

  • 属性类型

  • blob :二进制类型

  • integer : 整型

  • real : 浮点型

  • text :文本类型

null : 空

  • 主键

  • 主键(Primary Key,简称PK)用来唯一地标识某一条记录

例如t_student可以增加一个id字段作为主键,相当于人的身份证

  • 主键可以是一个字段或多个字段

    例如: 行和列

  • 主键的设计原则

    • 主键应当是对用户没有意义的
    • 永远也不要更新主键
    • 主键不应包含动态变化的数据
    • 主键应当由计算机自动生成

外键约束

外键保存的是另一张表的主键。
有外键的是子表或从表,对应的表就是父表或者成为主表。

CREATE TABLE employees4(
eid INT(6),
name VARCHAR(30),
deptno INT(4),
CONSTRAINT employees4_deptno_fk 
FOREIGN KEY(deptno) #本表的字段
 REFERENCES dept(deptno) #主表的主键
);

关联不一定需要外键约束:

  • 保证数据完整性可以由程序或触发器控制
  • 简化开发,维护数据时不用考虑外键约束
  • 大量数据DML操作时不需考虑外键耗费时间
CHECK约束

分为表约束和列约束,用于限制字段值在某个范围。

Introduction

学生的年龄必须是整数,取值范围为14-29;

SQL语言简介

  • 特点:不区分大小写(比如数据库认为user和UsEr是一样的)
  • SQL中的常用关键字(注意:数据库中不可以使用关键字来命名表、字段)

select、insert、update、delete、from、create、where、desc、order、by、group、table、alter、view、index等等

  • SQL语句的种类

    • 数据定义语句(DDL:Data Definition Language)

    包括create和drop, Alert等操作,在数据库中创建新表或删除表(create
    table或 drop table)

  • 数据操作语句(DML:Data Manipulation Language)

    包括insert、delete、update等操作,上面的3种操作分别用于添加、修改、删除表中的数据

  • 数据查询语句(DQL:Data Query Language)

可以用于查询获得表中的数据,关键字select是DQL(也是所有SQL)用得最多的操作,其他DQL常用的关键字有where,order
by,group by和having

检查约束

检查(Check)约束条件用来强制在字段上的每个值都要满足Check中定义的条件。
例如:

ALTER TABLE employees4
ADD CONSTRAINT employees4_salary_check
CHECK(salary > 2000);

CHECK括号中表达是必须是一个Boolean表达式

添加CHECK约束
ALTER TABLE table2
ADD sex CHAR(2);--在table表中添加sex,数据类型为CHAR,用来存放性别
GO
ALTER TABLE table2
ADD CONSTRAINT ck_sex CHECK(sex in('男','女'));

注:此时sex列数据类型不能是bit,如果填写bit,只能存储0和1,用CHECK约束限制结果为男和女就会报错。

  ->The DBA(数据库管理员) must begin by creating the tables
and constraints(约束) and loading the data. Impose(强加上) integrity constraints on the
columns 。

学生的性别只能是男或女;

DDL语句

  • 创表

 格式
 create table 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;
 示例

 create table t_student (id integer, name text, age inetger, score real) ;



 经验

 实际上SQLite是无类型的

 就算声明为integer类型,还是能存储字符串文本(主键除外)



 建表时声明啥类型或者不声明类型都可以,也就意味着创表语句可以这么写:



 create table t_student(name, age);



 为了保持良好的编程规范、方便程序员之间的交流,编写建表语句的时候最好加上每个字段的具体类型



 语句优化



 创建表格时, 最好加个表格是否已经存在的判断, 这个防止语句多次执行时发生错误



 create table if not exists 表名 (字段名1 字段类型1, 字段名2 字段类型2, …) ;
  • 删表

格式



 drop table 表名 ;



 drop table if exists 表名 ;



 示例



 drop table t_student ;



 语句优化



 删除表格时, 最好加个表格是否已经存在的判断, 这个防止语句多次执行时发生错误



 drop table if  exists 表名 ;
  • 修改表

注意: sqlite里面只能实现Alter Table的部分功能



 不能删除一列, 修改一个已经存在的列名



 修改表名



 ALTER TABLE 旧表名 RENAME TO 新表名



 新增属性



 ALTER TABLE 表名 ADD COLUMN 列名 数据类型 限定符
  • 约束

    • 简单约束

不能为空,not null :规定字段的值不能为null
不能重复,unique :规定字段的值必须唯一
默认值,default :指定字段的默认值

示例
`create table t_student (id integer, name text not null unique, age integer not null default 1) ;`

name字段不能为null,并且唯一
age字段不能为null,并且默认为1
  • 主键约束

添加主键约束的原因?
如果t_student表中就name和age两个字段,而且有些记录的name和age字段的值都一样时,那么就没法区分这些数据,造成数据库的记录不唯一,这样就不方便管理数据,良好的数据库编程规范应该要保证每条记录的唯一性,为此,增加了主键约束,也就是说,每张表都必须有一个主键,用来标识记录的唯一性

  • 主键的声明?

在创表的时候用primary key声明一个主键

create table t_student (id integer primary key, name text, age integer) ;

integer类型的id作为t_student表的主键

  • 主键字段

只要声明为primary key,就说明是一个主键字段

主键字段默认就包含了not null 和 unique 两个约束

如果想要让主键自动增长(必须是integer类型),应该增加autoincrement

create table t_student (id integer primary key autoincrement, name text, age integer) ;

多对多

两张表要想形成多对多关系,要引入一张关联关系表。关联关系表不需要主键。只需要两个字段,分别记录两张表的主键。

在创建表的时候添加CHECK 约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    CONSTRAINT ck_t3_type CHECK(t3_type in('类型1','类型2','类型3')) 
)

添加了约束后如果插入不符合约束的数据

INSERT INTO table3(
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038219',
'李建',
'社会与科学',
'任何数据'
);

消息547,级别16,状态0,第1 行
INSERT 语句与CHECK
约束”ck_t3_type”冲突。该冲突发生于数据库”test”,表”dbo.table3″, column
‘t3_type’。

语句已终止。

  ->Then provide views(视图) of the data, virtually
restructuring the physical tables into variant(不同的) table forms, to
simplify access to data.

学生的学号一定是唯一的;

DML语句

  • 插入数据(insert)

 格式



 insert into 表名 (字段1, 字段2, …) values (字段1的值, 字段2的值, …) ;



 示例



 insert into t_student (name, age) values (‘sz’, 10) ;



 注意



 数据库中的字符串内容应该用单引号 ’ 括住
  • 更新数据(update)

格式



 update 表名 set 字段1 = 字段1的值, 字段2 = 字段2的值, … ;



 示例



 update t_student set name = ‘wex’, age = 20 ;



 注意



 上面的示例会将t_student表中所有记录的name都改为wex,age都改为20
  • 删除数据(delete)

格式



 delete from 表名 ;



 示例



 delete from t_student ;



 注意



 上面的示例会将t_student表中所有记录都删掉
  • 条件语句

作用



 如果只想更新或者删除某些固定的记录,那就必须在DML语句后加上一些条件



 条件语句的常见格式



 where 字段 = 某个值 ;   // 不能用两个 =



 where 字段 is 某个值 ;   // is 相当于 =



 where 字段 != 某个值 ;



 where 字段 is not 某个值 ;   // is not 相当于 !=



 where 字段 > 某个值 ;



 where 字段1 = 某个值 and 字段2 > 某个值 ;  // and相当于C语言中的 &&



 where 字段1 = 某个值 or 字段2 = 某个值 ;  //  or 相当于C语言中的 ||
  • 条件语句练习

    示例

将t_student表中年龄大于10 并且 姓名不等于wex的记录,年龄都改为 5

删除t_student表中年龄小于等于10 或者 年龄大于30的记录

猜猜下面语句的作用

update t_student set score = age where name = ‘wex’ ;

删除CHECK约束
ALTER TABLE table3
DROP CONSTRAINT ck_t3_type;

  ->Provide security(安全性),
so that only authorized users are able to read or update certain
confidential(机密的) data.

学生所在的系必须是学校开设的系;

DQL

格式



 select 字段1, 字段2, … from 表名 ;



 select * from 表名;   //  查询所有的字段



 示例



 select name, age from t_student ;



 select * from t_student ;



 select * from t_student where age > 10 ;  //  条件查询
DEFAULT约束

通过定义列的默认值或使用数据库的默认值对象绑定表列,来指定列的默认值。

  ->The structure of the various tables, views, and other objects
of a database are made available to the DBA through a set of
system-defined tables, called system
catalogs(系统目录).

 

查询相关语句

统计



 count(X)



 select count(*) from t_student



 select count(age) from t_student



 avg(X)



 计算某个字段的平均值



 sum(X)



 计算某个字段的总和



 max(X)



 计算某个字段的最大值



 min(X)



 计算某个字段的最小值



 排序



 查询出来的结果可以用order by进行排序



 select 字段1, 字段2 from 表名 order by 字段 ;



 select * from t_student order by age ;



 默认是按照升序排序(由小到大),也可以变为降序(由大到小)



 select * from t_student order by age desc ;  //降序



 select * from t_student order by age asc ;   // 升序(默认)



 也可以用多个字段进行排序



 select * from t_student order by age asc, height desc ;



 先按照年龄排序(升序),年龄相等就按照身高排序(降序)



 limit分页



 使用limit可以精确地控制查询结果的数量,比如每次只查询10条数据



 格式



 select * from 表名 limit 数值1, 数值2 ;



 示例



 select * from t_student limit 4, 8 ;



 可以理解为:跳过最前面4条语句,然后取8条记录



 分页



 limit常用来做分页查询,比如每页固定显示5条数据,那么应该这样取数据



 第1页:limit 0, 5



 第2页:limit 5, 5



 第3页:limit 10, 5



 第n页:limit 5*(n-1), 5



 特殊案例



 select * from t_student limit 7 ;



 相当于select * from t_student limit 0, 7 ;



 表示取最前面的7条记录
在建表时添加DEFAULT约束
CREATE TABLE table3(
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12) DEFAULT '类型1' 
)

 

 

多表查询

select 字段1, 字段2, … from 表名1, 表名2 ;



 别名



 select



 别名1.字段1 as 字段别名1,



 别名2.字段2 as 字段别名2,



 …



 from



 表名1 as 别名1,



 表名2 as 别名2 ;



 可以给表或者字段单独起别名



 as 可以省略



 表连接查询



 select 字段1, 字段2, … from 表名1, 表名2  where 表名1.id = 表名2.id;



 外键



 如果表A的主关键字是表B中的字段,则该字段称为表B的外键



 保持数据一致性,完整性,主要目的是控制存储在外键表中的数据。 使两张表形成关联,外键只能引用外表中的列的值或使用空值。



12-(掌握)代码实现SQLite-DDL



 1. 创建一个Swift项目



 2. 导入系统框架sqlite3.tbd(sqlite3.dylib)



 3. 建立桥接文件, 导入头文件sqlite3.h



 1. 新建一个.h 头文件



 2. 设置为桥接文件
删除DEFAULT约束
ALTER TABLE table3
DROP CONSTRAINT DF__table3__t3_type__3D5E1FD2;
--DF__table3__t3_type__3D5E1FD2是DEFAULT约束的约束名

Integrity(完整性)

2.DBMS维护数据库完整性的机制:

代码实现

1. 打开数据库



 2. 使用打开的数据库, 执行DDL语句, 创建一个数据库表



 3.  使用打开的数据库, 执行DDL语句, 创建一个数据库表



 3. 将数据库操作封装成一个工具类



13-(掌握)代码实现DML语句-Insert



 1. 创建一个Student类



 属性



 name



 age



 构造方法



 init(name: String, age: Int)



 2. 创建数据库操作方法



 数据库中, 对Student对象的操作封装



 insertStudent()



14-(了解)代码实现DML语句-Insert绑定参数



 准备语句(prepared statement)对象



 准备语句(prepared statement)对象一个代表一个简单SQL语句对象的实例,这个对象通常被称为“准备语句”或者“编译好的SQL语句”或者就直接称为“语句”。



 操作历程



 1. 使用sqlite3_prepare_v2或相关的函数创建这个对象



 如果执行成功,则返回SQLITE_OK,否则返回一个错误码



 2. 使用sqlite3_bind_*()给宿主参数(host parameters)绑定值



 sqlite3_bind_text



 参数1:



 准备语句



 参数2:



 绑定的参数索引  (从1开始)



 参数3:



 绑定的参数内容



 参数4:



 绑定的参数长度  (-1代表自动计算长度)



 参数5:



 参数的处理方式



 SQLITE_TRANSIENT 会对字符串做一个 copy,SQLite 选择合适的机会释放



 SQLITE_STATIC / nil  把它当做全局静态变量, 不会字符串做任何处理,如果字符串被释放,保存到数据库的内容可能不正确!



 注意: swift中没有宏的概念



 // 替换 sqlite3.h 中的宏



 private let SQLITE_TRANSIENT = unsafeBitCast(-1, sqlite3_destructor_type.self)



 3.  通过调用sqlite3_step() 一次或多次来执行这个sql



 对于DML语句, 如果执行成功, 返回SQLITE_DONE



 对于DQL语句, 通过多次执行获取结果集, 继续执行的条件是返回值 SQLITE_ROW



 4.  使用sqlite3_reset()重置这个语句,然后回到第2步,这个过程做0次或多次



 5. 使用sqlite3_finalize()销毁这个对象, 防止内存泄露
添加约束不指定约束名
ALTER TABLE table3
ADD DEFAULT '类型2' FOR t3_type;
GO

  ->保证数据完整性(主要指数据的正确性与一致性)是数据库管理员的最重要任务之一。

 

DML语句-Insert插入数据优化

只要在执行多个SQL语句之前, 手动开启事务, 在执行完毕之后, 手动提交事务,
这样 再调用SQL方法执行语句时, 就不会再自动开启和提交事务

添加约束指定约束名
ALTER TABLE table3
ADD CONSTRAINT df_t3_type
DEFAULT '类型2' FOR t3_type;
GO

  ->可以通过约束(Constraint)、规则(Rule)或者缺省值保证数据的完整性,也可以在应用程序层保证数据完整性(这是应用程序设计的问题),或通过触发器保证。

①提供定义完整性约束条件的机制

事务

NOT NULL约束

约束字段值不为空。

  ->数据完整性类型包括:实体完整性、参照完整性和用户定义完整性  

  
DBMS应提供定义数据库完整性约束条件,并把它们存入数据库中。

5. Core Data

  • 特点: 对SQLite3的一层面向对象的包装,
    本质还是要转换成为对应的SQL语句去执行
建表时设置NOT NULL约束
CREATE TABLE table3(
    t3_id VARCHAR(12) NOT NULL,
    t3_name VARCHAR(20) NOT NULL,
    t3_class VARCHAR(12) NOT NULL,
    t3_type VARCHAR(12) NOT NULL 
)

    ->实体完整性(Entity Integrity)
:现实世界的实体是可区分的,即它们具有某种唯一性标识。相应地,关系模型中主键应作为唯一性标识。因此实体完整性规则规定基本关系的所有主键(Primary
Key)都不能取空值(NULL) 。

②提供完整性检查的方法

钥匙串

  • APP之间数据共享
  • 系统级别的加密, 安全性高
  • 当APP 被删除时, 存储的数据依然存在
为已存在的列添加NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NOT NULL;

   
 ->参照完整性(Referential Integrity)
:参照完整性维护表与表之间的相关性,通常用“主键(Primary
Key)/外键(Foreign Key)”保证,其中Foreign
Key可以取NULL值,或取其参照表中Primary Key或者候选键的取值。

  
检查数据是否满足完整性约束条件的机制称为完整性检查。一般在INSERT、UPDATE、DELETE语句执行后开始检查。

删除NOT NULL约束
ALTER TABLE table3
ALTER COLUMN t3_type VARCHAR(12) NULL;
GO

    ->用户定义的完整性(User_defined
Integrity
):针对某一具体数据的约束条件,由应用环境决定。例如:某个属性具有缺省值、某个属性必须取惟一值(UNIQUE)、某个非主属性不能取NULL值、某个属性的取值范围在0~100之间(CHECK)等等。

 

自定义默认值对象维护数据完整性

CREATE DEFAULT date_today AS GETDATE();
--新建默认值对象名date_today,默认值为getdate()函数,获取当前日期
GO
EXEC sp_addtype date_time,'date','NULL';
--利用存储过程新建自定义数据类型date_time,参照系统数据类型date
GO
EXEC sp_bindefault 'date_today','date_time';
--将默认值对象date_today绑定到自定义数据类型date_time上
GO
CREATE TABLE table3(--新建table3,设置字段t3_date的数据类型为date_time
    t3_id VARCHAR(12),
    t3_name VARCHAR(20),
    t3_class VARCHAR(12),
    t3_type VARCHAR(12),
    t3_date date_time
);
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038220',
'李建',
'社会与科学',
'类型1'
);
GO
SELECT * FROM table3;--查询table3数据,看t3_date是否有默认值为当前日期

查询结果如下
永利酒店赌场 2

ALTER TABLE table3
ADD t3_date1 DATE;--在table3表中新增一列t3_date1,数据类型为DATE
GO
EXEC sp_bindefault 'date_today','table3.t3_date1';
--直接将默认值对象date_today绑定到table3的t3_date1列
GO
INSERT INTO table3(--为新建表table3插入一条数据,不指定t3_date和t3_date1的值,看默认值是否有效
    t3_id,
    t3_name,
    t3_class,
    t3_type
)VALUES(
'2018038221',
'李建',
'社会与科学',
'类型'
);
GO
SELECT * FROM table3;
GO

查询结果如下
永利酒店赌场 3

  ->数据完整性具体实现包括两类

3.违约处理

存储过程查询默认值对象的所有者
USE test
EXEC sp_help date_today;
GO

结果如图所示
永利酒店赌场 4

    ->声明性数据完整性
:声明性数据完整性用约束(Constraint)、规则(Rule)
在数据库中提供保证,这是保证完整性的较好方法。它驻留在数据库内,编程简单,开销小,能更集中管理和保证数据的一致性

DBMS若发现用户的操作违背了完整性约束条件,就采取一定的动作以保证数据的完整性,如拒绝执行该操作,或级联执行其他操作。

删除默认值对象
DROP DEFAULT date_today;

删除不成功,提示以下信息:
消息3716,级别16,状态3,第1 行
无法删除默认值’date_today’,因为它已绑定到一个或多个列。
那么我们知道,当一个默认值对象绑定了列之后,就无法删除它,如果想要删除,就必须先解绑。在上面的操作中,我们的默认值对象date_today绑定了test数据库table3表的t3_date1字段。

    ->过程性数据完整性
:过程性数据完整性用触发器和应用程序代码保证,通常较为复杂、开销较大,但可以执行更多的业务规则。
通常,过程性数据完整性是声明性数据完整性的补充

 

对列解绑默认值对象
USE test
GO
EXEC sp_unbindefault 'table3.t3_date1';

此时我们再次尝试删除默认值对象,发现还是不行,此时要注意,在上面的操作中,默认值对象date_today不止绑定了t3_date1列,还绑定了自定义数据类型date_time,并将该数据类型定义给了t3_date列,我们还需要对date_time解绑默认值对象。

EXEC sp_unbindefault 'date_time';

此时再次删除默认值对象,就可以成功删除。删除默认值对象后,原先绑定的字段不会再有默认值。

           永利酒店赌场 5

永利酒店赌场 6

自定义规则维护数据完整性

规则是对列或自定义数据类型的值的规定和限制。自定义规则的表达式一定要返回布尔类型的值,并且表达式中不能包含多个变量。

CREATE RULE score_rule AS @math_score>=0;
GO--新建规则score_rule,参数@math_score
EXEC sp_addtype 'score_type','float','NULL';
GO--新建自定义数据类型score_type
CREATE TABLE table_score(--新建表table_score,预设mt_score和at_score字段用于绑定规则
s_id VARCHAR(4),
s_name VARCHAR(10),
mt_score float,--该字段将用于规则score_rule绑定到列
at_score score_type--该字段将用于规则score_rule绑定到自定义数据类型
);
GO
EXEC sp_bindrule 'score_rule','score_type';
GO--将score_rule规则绑定到自定义数据类型score_type
EXEC sp_bindrule 'score_rule','table_score.mt_score';
GO--将score_rule规则绑定到table_score表的mt_score列

----以下进行规则测试
INSERT INTO table_score(
s_id,
s_name,
mt_score,
at_score
)VALUES(
'0001',
'张华',
'-1',
'-1'
);
GO

进行违反规则的插入后,数据库报错
消息513,级别16,状态0,第1 行
列的插入或更新与先前的CREATE RULE
语句所指定的规则发生冲突。该语句已终止。冲突发生于数据库’test’,表’dbo.table_score’,列’mt_score’。

语句已终止。
很明显,mt_score的插入值为-1,违反了必须大于等于0的规则,数据库报错。将mt_score的插入值改成符合规则的数据,再次运行插入语句,数据库依然会报错,因为at_score字段的插入值也是违反规则的。将两个数据改成符合规则的返回,执行成功。

注:新建规则时表达式一定要是返回布尔类型的值,否则会报错

消息4145,级别15,状态1,过程sum_score,第1 行
在应使用条件的上下文(在’;’ 附近)中指定了非布尔类型的表达式。

                 永利酒店赌场 7

 

删除自定义规则

和自定义默认值对象一样,删除自定义规则要求该规则先与字段和自定义数据类型解绑。在上面的操作中,score_rule规则与自定义数据类型score_type以及列mt_score已绑定。因此执行以下语句:

EXEC sp_unbindrule 'score_type';
GO--解除规则score_rule与score_type之间的绑定
EXEC sp_unbindrule 'table_score.mt_score';
GO--解除规则score_rule与表table_score的mt_score列的绑定
DROP RULE score_rule;--删除score_rule规则

注:经过试验,一个列只能绑定1条规则,如果对一个列绑定2条规则,前一条规则会被后一条规则顶替。

           

 

查看自定义规则
EXEC sp_help 'score_rule';

结果如图所示
永利酒店赌场 8

-》》》完整性再阐述

二、缺省(默认值)和规则

查看自定义规则的定义信息
EXEC sp_helptext 'score_rule';
GO

结果如图所示
永利酒店赌场 9

     数据有着复杂的数据类型,用来满足不同的需求。实际应用中的数据也有着一定的取值范围,如年龄不小于0,性别只有男女等。而表之间的联系使相连的字段要保持一致和完整。但是,实际操作无法保证插入和删除的数据都符合要求,不符合要求的操作极可能会破坏数据的完整性,对数据库的可靠性和运行能力造成威胁。

 

因此存放数据集的数据库必须要对数据表和列有所限制和规范,为此SQLServer系统使用一系列的方法来维护数据完整性。

缺省和规则来源于由Sybase开发的S默认值QL
Server,在老版本的SQL Server或者升级版本中都有缺省和规则的使用。

数据的完整性:数据的完整性是指数据库中数据的正确性和一致性,表内的数据不相矛盾,表间的数据不相矛盾,关联性不被破坏。

缺省是为列提供数据的一种方式,如果用户进行INSERT操作时不为列输入数据,则使用缺省值。

为此有了以下实施完整性的途径:

规则是当用户进行INSERT或uPDATE操作时,对输入列中的数据设定的取值范围,是实现域完整性的方式之一。

1.        对列数据的控制:数据验证约束、默认值约束、规则。

 

2.        对列的控制:主键约束、唯一性约束、标识列。

缺省与规则有以下特点:

3.        对表之间、列之间关系的控制:外键约束、数据验证约束、触发器、存储过程。

(1)缺省与规则是数据库对象,它们是独立于表和列而建立的。

这些途径可以按照类型分为:约束、规则、默认值、触发器、存储过程。

(2)缺省与规则建立后与列或数据类型产生关联,列和数据类型就具有了缺省与规则的属性。

约束分为以下几点:

(3)缺省与规则定义后,可以重复使用,可以绑定到多个列或数据类型上。

l        主键约束

(4)缺省与规则不随表同时调入内存,当用到时才被调入内存,这可能会使程序执行出现延时。

主键:PRIMARY
KEY,主关键字,用来限制列的数据具有唯一性且不为空,即这一字段的数据没有重复的数据值且不能有空值。每个表只能有一个主键,一般用来做标识。

 

l        外键约束

缺省和规则对象通常只在它所创建的数据库中有效,不是ANSI标准,一般不提倡使用。

外键:FOREIGN
KEY,外键用来在两个表的数据之间建立连接,它可以是一列或者多列。一个表可以有一个或者多个外键。外键对应的是参照完整性,一个表的外键可以为空值,若不为空值则每一个外键值必须等于另一个表中主键的某个值。

应尽可能使用约束,任何可以使用缺省与规则的地方都有可以使用约束。

l        标识列

 

可以自动编号的列称为标识列或IDENTITY约束。IDENTITY约束就是为那些数值顺序递增的列准备的约束,自动完成数值的添加。每个表只能有一个标识列,标志数据不能由用户输入,用户只需要填写标志种子(标识列的第一个数据)和标志增量(依次增加的数),系统自动生成数据并填入表。标志种子和标志增量都是非零整数,位数小于等于10。默认两者均为1。

1.
缺省

l        唯一性约束

在SQL
Server中,有两种使用默认值的方法:

又叫UNIQUE约束,在主键约束中也用到了唯一性,不同的是一个表中可以有多个这样的唯一性列,却只能有一个主键。这里的唯一性列可以为空但是只能有一行数据为空。适用于不是主键但却仍旧要求具有唯一性的字段。

 

l        非空约束

①在创建表时,指定默认值。

一个列是否允许有空值,就是这里的空和非空约束,即NULL与NOT
NULL约束。NULL就是允许为空,NOT
NULL就是不允许为空。NULL不同于0和“”,0和“”都表示为该行有数据,而NULL是没有数据。

  用SQL
Server Management
Studio创建表时在设计表时指定默认值,可以在输入字段名称后,设定该字段的默认值。

l        数据验证约束

  或使用CREATE
TABLE语句中的DEFAULT子句指定默认值。

又称做CHECK约束,它通过给定条件(逻辑表达式)来检查输入数据是否符合要求,以此来维护数据完整性。

 

l        默认值约束

②使用CREATE
DEFAULT语句创建默认对象后,使用存储过程sp_bindefault将该默认对象绑定到列上。

又称作DEFAULT约束。将常用的数据值性以为默认值可以节省用户输入的时间,在非空字段中定义默认值可以减少错误发生。在实际应用中,默认值还可以是结果能变的函数。

 

 

默认值对象是单独存储的,删除表的时候,DEFAULT约束会自动删除,但是默认值对象不会被删除。

规则:

创建默认值对象后,需要将其绑定到某列或者用户自定义的数据类型上。

规则是独立的SQL
Server对象,跟表和视图一样是数据库的组成部分。规则的作用和CHECK约束类似,用于完成对数据值的检验。它可以关联到多个表,在数据库中有邮局插入、修改时,验证新数据是否符合规则,是实现域完整性的方式之一。规则在定义时并没有定义它的检测对象,而是在创建后绑定到对象来检测数据。

 

创建规则

主要操作:

CREATE RULE 规则名

 

AS

①创建默认值对象

条件表达式

②绑定默认值对象

在这里的条件表达式使用逻辑表达式,表达式中要有一个@开头的变量,代表用户的输入数据,可以看做是代替WHERE后面的列名。

③解除默认值对象的绑定

规则在绑定之后才可以使用,规则的绑定需要时用系统存储过程sp_bindrule.

④查看默认值对象

USE 数据库名

⑤删除默认值对象

Go

 

Sp_bindrule 规则名 表名.字段名

 

使用存储过程sp_help来查看规则。

①创建默认对象

规则的特点(与CHECK的不同)

可以使用CREATE
DEFAULT语句创建默认对象。其语法格式如下:

1)       规则是SQL Server的对象而CHECK是一种约束,是表定义的一部分。

CREATE
DEFAULT default  AS constant_expression 

2)       CHECK的优先级要高于规则。

例如: create default d_grade as 1 

3)       一个列只能使用一个规则却可以使用多个CHECK约束。

 

4)       规则可以应用于多个列,CHECK约束只针对它定义的列。

②绑定默认对象

5)       规则创建一次可以使用多次而CHECK约束需要多次创建。

默认对象创建后不能使用,必须首先将其绑定到某列或者用户自定义的数据类型上。其使用语法格式如下:

默认值:

sp_bindefault
[@defname = ] ‘default’, [@objname
= ] ‘object_name’   [,
[@futureonly = ] ‘futureonly_flag’]

CREATE DEFAULT 默认值名

其中: [,
[@futureonly = ]
‘futureonly_flag’]仅在此之后将默认值绑定到用户定义的数据类型时才使用。

AS 常亮表达式

例如: exec
sp_bindefault ‘d_grade’, ‘sc.grade’ 

与创建规则一样,默认值的定义不能包含列名,需要绑定到列或是其他数据库对象才能使用。一个列只能绑定一个默认值

 

 

③解除默认对象的绑定

Definition 7.1.2 Column Constraint

解除绑定可以使用sp_unbindefault存储过程。其语法格式如下:

       NOT NULL -If NOT NULL appears in a col_constr, then the DEFAULT
clause cannot specify NULL;

sp_unbindefault
[@objname = ] ‘object_name’

       CONSTRAINT-allows us to specify a name for each constraint other
than NOT NULL, so that we can later drop the constraint with an ALTER
Table statement.

例如: exec
sp_unbindefault ‘sc.grade’ 

       UNIQUE- It can be specified even if NOT NULL is not, and the
column is then constrained so that all non-null values in the table are
unique, but multiple nulls can exist for this column.       

 

       PRIMARY KEY -A column with the PRIMARY KEY column constraint is
implicitly defined to be NOT NULL and UNIQUE. The UNIQUE clause and
PRIMARY KEY clause cannot both be used for a column, although the
PRIMARY KEY clause and the NOT NULL clause can be used together.

④查看默认对象

       CHECK

 

       EXAMPLE

exec sp_help
d_grade 

       永利酒店赌场 10

 

              永利酒店赌场 11

exec
sp_helptext d_grade 

 

 

                      永利酒店赌场 12

 

                    触发器

⑤删除默认对象

                 

在删除默认对象之前,首先要确认默认对象已经解除绑定。删除默认对象使用DROP
DEFAULT语句。其语法格式如下:

触发器的介绍

 

  1. 1

    触发器(trigger)是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,当对一个表进行操作(
    insert,delete,
    update)时就会激活它执行,触发器经常用于加强数据的完整性约束和业务规则等。其实往简单了说,就是触发器就是一个开关,负责灯的亮与灭,你动了,它就亮了,就这个意思。

    永利酒店赌场 13

    END

DROP
DEFAULT {default} [,…n]

触发器的分类

 

  1. 1

    DML( 数据操纵语言 Data Manipulation
    Language)触发器:是指触发器在数据库中发生DML事件时将启用。DML事件即指在表或视图中修改数据的insert、update、delete语句。

  2. 2

    DDL(数据定义语言 Data Definition
    Language)触发器:是指当服务器或数据库中发生(DDL事件时将启用。DDL事件即指在表或索引中的create、alter、drop语句也。

  3. 3

    登陆触发器:是指当用户登录SQL SERVER实例建立会话时触发。

    END

例如: drop default d_grade 

DML触发器介绍

 

  1. 1

    在SQL SERVER
    2008中,DML触发器的实现使用两个逻辑表DELETED和INSERTED。这两个表是建立在数据库服务器的内存中,我们只有只读的权限。DELETED和INSERED表的结构和触发器所在的数据表的结构是一样的。当触发器执行完成后,它们也就会被自动删除:INSERED表用于存放你在操件insert、update、delete语句后,更新的记录。比如你插入一条数据,那么就会把这条记录插入到INSERTED表:DELETED表用于存放你在操作
    insert、update、delete语句前,你创建触发器表中数据。

  2. 2

    触发器可通过数据库中的相关表实现级联更改,可以强制比用CHECK约束定义的约束更为复杂的约束。与
    CHECK
    约束不同,触发器可以引用其它表中的列,例如触发器可以使用另一个表中的
    SELECT
    比较插入或更新的数据,以及执行其它操作。触发器也可以根据数据修改前后的表状态,再行采取对策。一个表中的多个同类触发器(INSERT、UPDATE
    或 DELETE)允许采取多个不同的对策以响应同一个修改语句。

  3. 3

    与此同时,虽然触发器功能强大,轻松可靠地实现许多复杂的功能,为什么又要慎用?过多触发器会造成数据库及应用程序的维护困难,同时对触发器过分的依赖,势必影响数据库的结构,同时增加了维护的复杂程序。

    END

 

触发器步骤详解

 

  1. 1

    首先,我们来尝试创建一个触发器,要求就是在AddTable这个表上创建一个Update触发器,语句为:

    create trigger mytrigger on AddTable
    for update

    永利酒店赌场 14

  2. 2

    然后就是sql语句的部分了,主要是如果发生update以后,要求触发器触发一个什么操作。这里的意思就是如果出现update了,触发器就会触发输出:the
    table was updated!—By 小猪也无奈。

    永利酒店赌场 15

  3. 3

    接下来我们来将AddTable表中的数据执行一个更改的操作:

    永利酒店赌场 16

  4. 4

    执行后,我们会发现,触发器被触发,输出了我们设置好的文本:

    永利酒店赌场 17

  5. 5

    那触发器创建以后呢,它就正式开始工作了,这时候我们需要更改触发器的话,只需要将开始的create创建变为alter,然后修改逻辑即可:

    永利酒店赌场 18

  6. 6

    如果我们想查看某一个触发器的内容,直接运行:exec sp_helptext
    [触发器名]

    永利酒店赌场 19

  7. 7

    如果我想查询当前数据库中有多少触发器,以方便我进行数据库维护,只需要运行:
    select * from sysobjects where xtype=’TR’

    永利酒店赌场 20

  8. 8

    我们如果需要关闭或者开启触发器的话,只需要运行:

    disable trigger [触发器名] on database –禁用触发器

    enable trigger [触发器名] on database –开启触发器

    永利酒店赌场 21

  9. 9

    那触发器的功能虽大,但是一旦触发,恢复起来就比较麻烦了,那我们就需要对数据进行保护,这里就需要用到rollback数据回滚~

    永利酒店赌场 22

  10. 10

    第九步的意思就是查询AddTable表,如果里面存在TableName=newTable的,数据就回滚,触发器中止,那我们再进行一下测试,对AddTable表进行更改,发现,触发update触发器之后,因为有数据保护,触发器中止:永利酒店赌场 23

    永利酒店赌场 24

  11.  

 永利酒店赌场 25

永利酒店赌场 26

永利酒店赌场 27

永利酒店赌场 28

永利酒店赌场 29

永利酒店赌场 30

永利酒店赌场 31

永利酒店赌场 32

永利酒店赌场 33

永利酒店赌场 34

永利酒店赌场 35

永利酒店赌场 36

永利酒店赌场 37

 

 永利酒店赌场 38

永利酒店赌场 39

永利酒店赌场 40

永利酒店赌场 41

永利酒店赌场 42

永利酒店赌场 43

永利酒店赌场 44

永利酒店赌场 45

永利酒店赌场 46

永利酒店赌场 47

永利酒店赌场 48

永利酒店赌场 49

永利酒店赌场 50

永利酒店赌场 51

永利酒店赌场 52

永利酒店赌场 53

永利酒店赌场 54

永利酒店赌场 55

永利酒店赌场 56

永利酒店赌场 57

永利酒店赌场 58

永利酒店赌场 59

永利酒店赌场 60

永利酒店赌场 61

永利酒店赌场 62

永利酒店赌场 63

永利酒店赌场 64

永利酒店赌场 65

 

2.规则

 

规则用以限制存储在表中或用户自定义数据类型的值,是独立的数据库对象。

将规则绑定到列或用户自定义数据类型时,规则才起作用。

表中的每列或每个用户定义数据类型只能和一个规则绑定。但每列可应用多个CHECK约束。

如果要删除规则,应确定规则已经解除绑定。

 

①创建规则

 

CREATE
RULE语句,其语法格式如下:

CREATE
RULE rulename AS condition_expression

 

其中各参数含义如下:

rulename  是新规则的名称。

condition_expression 
是定义规则的条件。

例如:create rule r_grade as @grade<=100 and @grade>=0 

 

②绑定规则

 

使用sp_bindrule存储过程,语法格式为:

sp_bindrule
[@rulename = ] ‘rulename’,  [@objname
= ]  ‘object_name’

例如: exec sp_bindrule ‘r_grade’,’sc.grade‘ 

 

注意:

规则不能绑定到text、image或timestamp列。

如果规则与绑定的列不兼容,SQL
Server将在插入值时返回错误信息。

未解除绑定的规则,如果再次将一个新的规则绑定到列,旧的规则将自动被解除,只有最近一次绑定的规则有效

如果列中包含CHECK约束,则CHECK约束优先。

 

③解除规则的绑定

 

使用sp_unbindrule存储过程。语法格式如下:

sp_unbindrule
[@objname = ] ‘object name’  [,[@futureonly
= ] ‘futureonly_ lag’]

例如:  

exec
sp_unbindrule ‘sc.grade’ 

 

④删除规则

首先要解除规则的绑定,然后才能删除绑定

例如:drop rule r_grade 

  

三、约束

 

SQL
Server 2008提供的强制数据完整性的机制:

 

①PRIMARY
KEY 约束

②FOREIGN
KEY 约束

③UNIQUE
约束

④CHECK
约束

⑤NOT
NULL(非空性)

⑥IDENTITY
约束

 

使用约束优先于使用触发器、规则和默认值

查询优化器使用约束定义生成高性能的查询执行计划

 

 

①PRIMARY
KEY 约束:

 

可以在下面情况下使用:

(1)作为表定义的一部分在创建表时创建。

(2)添加到尚没有PRIMARY
KEY约束的表中(一个表只能有一个PRIMARY KEY约束)。

(3)如果已有PRIMARY
KEY约束,则可对其进行修改或删除。

 

特点:

(1)每个表都应有一个主键,主键值唯一。

(2)主键内的任何列不能为空(null)。

(3)要使用TRansact-SQL修改PRIMARY
KEY,必须先删除现有的PRIMARY KEY约束,然后再重新创建。

(4)创建表时指定主键,sql
server会自动创建一个名为“PK_”且后跟表名的主键索引。如果不指定索引类型,则默认为聚集索引。该索引只能在删除与它保持联系的表或主键约束时才能删除。

 

 

②FOREIGN
KEY约束

 

标识表之间的关系,用于强制参照完整性,为表中一列或多列提供参照完整性。
FOREIGN
KEY约束也可以参照自身表中的其他列(例如:学生表中的“班长学号”列参照“学号”列),这种参照称为自参照。

 

FOREIGN
KEY约束可以在下面情况下使用:

 

(1)作为表定义的一部分在创建表时创建。

(2)如果现有表的某列与另一个表已有的PRIMARY
KEY约束或UNIQUE约束相关联,则可向现有表添加FOREIGN KEY约束。

(3)对已有的FOREIGN
KEY约束进行修改或删除。

 

使用FOREIGN
KEY约束,应注意的几个问题:

 

(1)每个表最多可以有253个FOREIGN
KEY约束。

(2)FOREIGN
KEY约束只能参照同一个数据库中的表,而不能参照其他数据库中的表。

(3)FOREIGN
KEY子句中的列数目和每个列指定的数据类型必须和REFERENCES子句中的列相同。

(4)FOREIGN
KEY约束不能自动创建索引。

(5)在临时表中,不能使用FOREIGN
KEY约束。

(6)如果一个外键没有对应的主键值,则不能插入带该值的行。

 

 

③UNIQUE约束 

 

(1)UNIQUE约束在列集内强制执行值的唯一性。

(2)对于UNIQUE约束中的列,表中不允许有两行包含相同的非空值。

(3)SQL
Server创建了UNIQUE约束后会自动创建UNIQUE索引来强制UNIQUE约束的唯一性要求。

(4)如果插入重复行,SQL
Server将返回错误信息。

(5)向表中的现有列添加UNIQUE约束时,默认情况下SQL
Server 2008检查列中的现有数据确保除NULL外的所有值均唯一。

(6)UNIQUE约束与主键约束的区别: 主键也强制执行唯一性,但主键不允许空值,而且每个表中主键只能有一个,但UNIQUE列可以有多个,可以取空值。

(7)UNIQUE约束优先于唯一索引。

 

 

④CHECK约束

 

(1)CHECK约束通过限制用户输入的值来加强域完整性。

(2)它指定应用于列中输入的所有值的布尔(取值为TRUE或FALSE)搜索条件,拒绝所有不取值为TRUE的值。

(3)可以为每列指定多个CHECK约束。

 

⑤IDENTITY约束 

 

自动编号约束又称作标识列,采用数字编号的方式依次增加一个增量。是为那些数字顺序递增的列准备的约束,可以自动完成数值添加。

 

(1)标识种子

(2)标识增量

(3)标识列的数据类型

 

 

四、完整性约束命名子句

 

完整性约束命名子句的格式:

CONSTRAINT
<完整性约束条件名>[PRIMARY KEY短语|FOREIGN KEY
短语|CHECK短语]

 

例:建立学生登记表Student2,要求学号在10000至99999之间,姓名不能取空值,年龄小于30,性别‘男’或‘女’:

1 CREATE TABLE Student2(
2        sno   int CONSTRAINT C1 CHECK (sno BETWEEN 10000 AND 99999),
3        sname  CHAR(8)  CONSTRAINT C2 NOT NULL,
4        sage  int  CONSTRAINT C3  CHECK (sage<30),
5        ssex   VARCHAR(2)  CONSTRAINT C4 CHECK (ssex IN ('男', '女')),
6        CONSTRAINT SK PRIMARY KEY(Sno)
7 );    

 

网站地图xml地图