SQL(Structured Query Language)即为结构化查询语言,它的主要功能就是同各种数据库建立联系,进行沟通。
目前,绝大多数流行的关系型数据库管理系统,如Oracle, Sybase, Microsoft SQL Server, DB2等都采用了ANSI-SQL 语言标准。
不同的类型的DBMS会根据实际需要,对标准SQL作适当的调整,形成自己的SQL。
SQL语言的组成
- DDL(数据定义语言) :维护(定义、修改、删除)SQL模式对象.
- DML (数据操纵语言) :包括数据查询和数据更新(增、删、改,查)
- DCL (数据控制语言) :对数据的访问控制(授予权限、取消权限)
- TCL (事务控制语言) :对事务的控制(提交、回滚、保存点)
Oracle 数据类型
数据库中主要的对象是表,数据存储在表中
创建表时,必须为各个列指定数据类型
以下是 Oracle 数据类型的类别:
Oracle 数据类型-字符型
当需要固定长度的字符串时,使用 CHAR 数据类型。
当存储不满时,用空格填充。
CHAR 数据类型的列长度可以是 1 到 2000 个字节。
VARCHAR2数据类型支持可变长度字符串。
VARCHAR2数据类型存储字母数字值。
VARCHAR2数据类型的大小在1至4000个字节范围内。
Oracle 数据类型-数值型
数值数据类型
可以存储整数、定点数和浮点数
最高精度为 38 位
范围-1*10130——9.999…99*10125
数值数据类型的声明语法
NUMBER [( p[, s])]
P表示精度,S表示小数点的位数
column_name number {p=38,s=0}
column_name number(p) {定点数}
column_name number(p,s) {浮点数}
Oracle 数据类型-日期时间型
日期时间数据类型存储日期和时间值,包括年、月、日, 小时、分钟、秒
主要的日期时间类型有
DATE - 存储日期和时间部分,精确到整个的秒
TIMESTAMP - 存储日期、时间和时区信息,秒值精确到小数点后6位
/*获取当前日期*/
Select sysdate from dual;
Select systimestamp from dual;
Oracle 数据类型-二进制类型
- RAW 数据类型用于存储二进制数据
- RAW 数据类型最多能存储 2000 字节
- LONG RAW 数据类型用于存储可变长度的二进制数据
- LONG RAW 数据类型最多能存储 2 GB
Oracle 数据类型 –大对象
LOB 称为“大对象”数据类型,可以存储多达 4GB 的 非结构化信息,例如声音剪辑和视频文件等
LOB 数据类型允许对数据进行高效、随机、分段的访问
Oracle 数据类型 –伪列
Oracle 中伪列就像一个表列,但是它并没有存储在表中
伪列可以从表中查询,但不能插入、更新和删除它们的值
常用的伪列有ROWID和ROWNUM
ROWID 是表中行的存储地址,该地址可以唯一地标识数据库中 的一行,可以使用 ROWID 伪列快速地定位表中的一行
ROWNUM 是查询返回的结果集中行的序号,可以使用它来限 制查询返回的行数
数据定义语言
数据定义语言用于改变数据库结构,包括创建、更改和删除数据库对象
用于操纵表结构的数据定义语言命令有:
- CREATE TABLE
- ALTER TABLE
- ALTER TABLE
- TRUNCATE TABLE
- DROP TABLE
DDL – Create Table命令
创建表——语法:
CREAT TABLE [模式名.]基本表名 ( 列名1 类型1, 列名2 类型2,......);
示例:
create table t_user(
name varchar(10),
sex varchar(2),
age int(2) /*请注意最后一项没有逗号*/
);
create table t_student (
SID char(5) not null primary key, --学号
Sname varchar2(10) not null, --姓名
Ssex char(1) not null, --性别
Sbirthday date null, --出生日期
Stel varchar2(13) null, --联系电话
);
DDL – Alter Table命令
修改表结构——语法:
ALTER TABLE 基本表名 ADD/MODIFY/DROP (增加/修 改/删除) 列名 类型名(增加/修改时写出)或增加/修改/删除 约束
- 增加字段:ALTER TABLE t_student Add f_address char(50);
- 修改字段:ALTER TABLE t_student Modify f_address varchar2(50);
- 删除字段:ALTER TABLE t_student Drop column f_address ;
- 添加约束:ALTER TABLE t_student Add constraint uk_Sname Unique(Sname);
- 重命名字段:ALTER TABLE t_student rename column f_address to f_add;
DDL – 截断及删除命令
截断——语法:
Truncate TABLE 基本表名
示例:
TRUNCATE TABLE t_student;
注:
1、截断表是将表中所有记录删除,但保留表结构,并且不写日志
2、trancate table 是DDL语言,delete from table_name 是DML语言
删除表——语法:
DROP TABLE 基本表名
示例:
DROP TABLE t_student;
DDL – 其它
RENAME table_name TO new_name :重命名
COMMENT ON:为表或字段添加注释
查看表或字段的注释:
RENAME t_student to test; --重命名
COMMENT ON TABLE t_student IS ‘用于存储学生信息 ’; --表添加注释
COMMENT ON COLUMN t_student .sname IS ‘姓名 ’; --字段添加注释
SELECT COMMENTS FROM USER_TAB_COMMENTS WHERE TABLE_NAME= ‘T_STUDENT’; ---查看表的注释
SELECT COMMENTS FROM USER_COL_COMMENTS WHERE TABLE_NAME= 'T_STUDENT'; ---查看字段的注释:
数据完整性
可靠性+准确性=数据完整性
根据数据完整性机制所作用的数据库对象和范围不同,数 据完整性可分为以下四种类型:
- 实体完整性
- 域完整性
- 参照完整性(引用完整性)
- 自定义完整性
实体完整性
约束方法:唯一约束、主键约束
域完整性
约束方法:限制数据类型、检查约束、默认值、非空约束
引用完整性
约束方法:外键约束
自定义完整性
约束方法:规则、存储过程、触发器
完整性总结
完整性实例--约束
五大约束
- 主键约束:PRIMARY KEY,唯一、且非空
- 外键约束:FOREIGN KEY ... REFERENCES
- 唯一约束:UNIQUE,唯一,允许为空
- 检查约束:CHECK
- 默认约束:DEFAULT
列级约束
- 约束作为列定义的一部分
表级约束
- 约束作为表定义的一部分
列级约束
create table class_table (
cid number(10) primary key,
cname varchar2(30) not null,
cdate date
);
create table student_table(
sid number(10) primary key ,
classid number(10) references class_table(cid),
sno varchar2(30) unique,
sname varchar2(30) not null,
sage number(3) check(sage>0 AND sage<120),
saddress varchar2(100) default ('地址不详')
);
/*注意:
列名、数据类型、缺省值的次序不能乱;
在列定义的最后,指定列类型的约束;
主键约束的列可以不指定非空约束(NOT NULL)。*/
表级约束
唯一约束:constraint name unique(column[,column...])
主键约束:constraint name primary key(column[,column...])
外键约束:constraint name foreign key(column[,column...]) references table(column[,column...])
条件约束:constraint name check(condition)
完整性实例--约束
定义表级约束实例:
SQL> create table account(
name varchar2(32) ,
acc_type number(1) not null,
acc_code varchar2 (32),
balance number(5,2) default(100),
sid number(10) , sid number(10) ,
constraint pk_account primary key (name),
constraint uk_account unique(acc_code),
constraint ck_account check(balance>=100 and balance<=1000),
constraint fk_acc_stu foreign key(sid) references student_table(sid)
);
SQL> alter table account drop constraint fk_acc_stu;
SQL> alter table account add constraint fk_acc_stu foreign key (sid) references student_table(sid);
Oracle数据库中的表
用户表
- 用户创建和维护的一系列表的集合包含用户的信息
数据字典
- Oracle 服务器创建和维护的一系列表和视图的集合,包含数据库的信息
- user_xxx 用户拥有的
- all_xx 用户有权查看
- dba_xxx(sys) 所有的信息
数据操纵语言
数据操纵语言用于检索、插入和修改数据
数据操纵语言是最常见的SQL命令
数据操纵语言命令包括:
- INSERT
- DELETE
- DELETE
- UPDATE
- SELECT
DML – INSERT命令
插入来自其它表中的记录
语法:
INSERT INTO <table_name> [(cloumn_list)];
SELECT column_names FROM <other_table_name>;
create table t_stutemp as select * from tt where 1=2;
insert into t_stutemp select * from tt where Sclass=1;
insert into t_stutemp(sid,sname,stel,ssex) select sid,sname,stel,ssex from tt where sclass=1;
DML – UPDATE命令
UPDATE命令的基本语法:
UPDATE <table_name> SET column_name = value [, column_name = value,……] [WHERE condition];
/*例:修改张三的出生日期为 88年1月8号*/
update tt set sbirthday = '08-1月-88' where sname ='张三';
DML – DELETE 命令
DELETE命令的基本语法:
DELETE [FROM] <table_name> [WHERE condition];
/*例:删除学号为10014的学生信息*/
Delete t_student where SID = ‘10014’;
数据控制语言
数据控制语言为用户提供权限控制命令
用于权限控制的命令有:
- GRANT 授予权限
- REVOKE 撤销已授予的权限
GRANT SELECT ON t_student GRANT SELECT ON t_student TO user1 WITH GRANT OPTION;
REVOKE SELECT, UPDATE ON t_student FROM user1;
事务控制语言
将一系列操作,作为一个逻辑单元整体进行工作
用于事务控制的语句有:
- COMMIT - 提交并结束事务处理
- ROLLBACK - 撤销事务中已完成的工作
- SAVEPOINT – 标记事务中可以回滚的点
UPDATE t_student SET Sbirthday = '30-8月-85‘ WHERE SID = ‘10012';
DELETE FROM t_student WHERE SID = ‘10012';
ROLLBACK;
COMMIT;
事务的四大特性
事务应该具有4个属性:原子性、一致性、隔离性、持续性。这四个属性通常称为ACID特性。
- 原子性(atomicity):一个事务是一个不可分割的工作单位,事 务中包括的诸操作要么都做,要么都不做。
- 一致性(consistency):事务必须是使数据库从一个一致性状 态变到另一个一致性状态。一致性与原子性是密切相关的。
- 隔离性(isolation) :一个事务的执行不能被其他事务干扰。即 一个事务内部的操作及使用的数据对并发的其他事务是隔离的,并 发执行的各个事务之间不能互相干扰。
- 持久性(durability) :持续性也称永久性(permanence),指一个事务一旦提交,它对数据库中数据的改变就应该是永久性的。 接下来的其他操作或故障不应该对其有任何影响。
本章总结
- SQL 是通用的数据库语言
- SQL 命令可分为数据定义语言、数据操纵语言、事务控 制语言和数据控制语言
- Oracle 支持的数据类型包括字符、数值、日期时间、RAW和LOB等
- 伪列:ROWID、ROWNUM
- 数据完整性:实体完整性、域完整性、引用完整性、自定义完整性
2018.8.26