Oracle T-SQL语法_oracle tsql

一、基本概念

(一)表

概念：表（实体，映射关系），列（属性），行（对象）

组成：表结构、表约束（保证数据完整性）、表数据

(二)表约束

1.实体完整性：保证表数据不"绝对"重复

    1）主键 primary key （最对只有一个主键，且不能为NULL）
    2）唯一键 unique （可以有多个，可以允许一个空值）

2.域完整性：保证表数据在一定范围内真实存在

    1）检查约束 check(条件表达式)
    2）默认值约束 default(默认值)
    3）非空约束 not null

3.引用完整性：保证子表数据在主表中真实存在

    1）外键 foreign key(关系型数据库核心)
    
    价值：减少冗余，但加大了查询的难度

4.自定义完整性：由实际的需求控制

    1）过程
    2）触发器

二、T-SQL语法

1.DDL：数据定义语言

1) create 创建

A. 创建表

    create table 表(
        列1 数据类型 约束,
        列2 数据类型 约束,
        ...
        列N 数据类型 约束,
    );

B. 创建序列

    create sequence 序列名 start with 启示值 increment by 增长值;

2) alter 修改

A. 修改约束

    设置主键
        alter table 表 add constraint pk_key primary key (列);
        
    设置唯一键
        alter table 表 add constraint uq_key unique (列);
        
    设置外键
        alter table 表 add constraint fk_key foreign key (列) references 主表 (主键);
    
    设置检查约束    
        alter table 表 add constraint ck_key check (规则);
 
    移除约束
        alter table 表 drop constraint 约束名;

B. 修改列

    增加一个列
        alter table 表 add (列 数据类型 约束);
    
    移除一个列
        alter table 表 drop (列);
        
    注意：新增或者移除一个列时，最好在添加数据之前

3) drop 移除

    移除表
        drop table 表;
        
     注意：移除表是将整个表全部删除，包括数据、 约束、 结构

4) truncate 格式化

    格式化表
        truncate table 表;
    
    注意：格式化表时将表中的数据清空, 删除表中所有的数据

2. DML：数据管理语言

1) insert 添加

A. 单行添加

    忽略有默认值或为null
        insert into 表(列1, 列2, ..., 列N) values(值1, 值2, ..., 值N);
    
    添加所有列数据
        insert into 表 values(值1, 值2, ..., 值N);

B. 批量添加

    insert into 表
    select 值列表 from dual union
    select 值列表 from dual union
    ...
    select 值列表 from dual;

C. 备份添加

    创建备份
        create table 备份表 as select * from 原始表;
    
    恢复备份
        insert into 原始表 select * from 备份表;
    

2) update 修改

    修改对应列的值
        update 表 set 列1 = 值1, 列2 = 值2, ..., 列; = 值N where 条件;

3) delete 删除

    删除表中所有数据
        delete from 表
 
    删除指定列的数据
        delete from 表 where 条件;

3. DQL：数据查询语言

1) select 查询

4. TCL：事务控制语言

1) commit 提交

    事务提交
        commit;

2) rollback 撤销

    撤销至保存点
        rollback to 保存点名;
    
    撤销至原始点
        rollback；

3) savepoint 保存点

    新增保存点
        savepoint 保存点名;

5. DCL：数据控制语言

1) grant 授予

    给用户授予角色
        grant 角色 to 用户;
    
    给用户授予操作权限
        grant 权限 to 用户;

2) revoke 收回

    revoke 权限/角色 from 用户

补充：

    角色：connect（连接）、resource（操作）、dba（管理员）  
    
    权限：select（查询）、update（更新）、insert（增加）、delete（删除）、all（所有操作权限）

三、实例

1. 创建表

创建表，并添加与约束
 
create table tbl_book
(
    bkisbn varchar2(20) not null primary key,   //设置主键
    bktitle varchar2(50) not null,              //非空约束
    bkauthor varchar2(50) not null,             //非空约束
    bkpublic date default(sysdate),		        //默认值
    bkprice number check(bkprice > 0),	        //检查约束
    bktpid number references tbl_type(tpid)	    //设置外键约束
);

2. 设置约束

主键
SQL> alter table tbl_type add constraint pk_tpid primary key (tpid);	
 
唯一键
SQL> alter table tbl_type add constraint uq_tpname unique (tpname);	
 
检查约束
SQL> alter table tbl_type add constraint ck_tpid check (tpid < 10);  
 
外键
SQL> alter table tbl_book add constraint fk_tpid foreign key (bktpid) references tbl_type (tpid); 
 

3. 批量添加数据

SQL> insert into tbl_book
     select '1001','红楼梦','曹雪芹',sysdate,99.9,3 from dual unio
     select '1002','西游记','吴承恩',sysdate,99.9,3 from dual unio
     select '1003','水浒传','施耐庵',sysdate,99.9,3 from dual unio
     select '1004','三国演义','罗贯中',sysdate,99.9,3 from dual;

4. 等差序列实现自增长

序列 提供等差数列
权限 角色：resources
 
添加序列
SQL> create sequence seq_tbl_book start with 1001 increment by 1;
 
使用序列
SQL> insert into tbl_book(bkisbn, bktitle, bkauthor, bkprice, bktpid) values('454' || seq_tbl_book.nextval, '稻米笔记'，'天下' , 40, 3); 

补充：Oracle运算符号

+ 只做加法运算
SQL> select 1 + '2' from dual;	
结果显示：3
 
|| 字符串拼接
SQL> select 1 || '2' from dual;
结果显示：12
 
说明：在实际项目中，实体编号不是数字，而是一组数字组成由特定意义的字符串

问题:

1.主键和唯一键的区别?

2.delete, truncate, drop有什么特点?

delete选择性删除数据, 可以指定删除某一行, 不能删除有外键引用的数据
truncate数据清空, 删除表中所有的数据; 速度快, 删除效率高, 只要有外键约束,就不能格式化
drop将整个表全部删除 (数据, 约束, 结构)