使用工具:
SQLDevelop工具,官网下载,用于编写sql语句,对oracle数据库进行操作管理。
Oracle的SQL:
DDL:数据定义语言
Create table
Create index
Drop table
Drop index
Truncate table
Alter table
Alter table add constraint
DML:数据操作语言,执行后,需要提交commit事务才能真正更新。
Insert
Update
Delete
Select
DCL:数据控制语言
Grant
Revoke
LOCK:数据库特定部分进行锁定
TCL:事务控制语言
COMMIT
ROLLBACK
SAVEPIONT:设置保存点
练习:
Sys:
--ddl:create user
--create user user03 identified by pass03;
--dcl:grant
grant connect,resource to user03;
--drop user user03;
--grant create session to user03;
User03:
--ddl:
--create table test(id number,name varchar2(20));
--dml
--insert into test(id,name) values(1,'test01');
--tcl
--commit;
--dml;
--select * from test;
sql的使用
简单创建表
1.学生信息表:学号,姓名,性别……
Create table tabename(
Column_name datatype,……
);
2.数据类型:
字符类型:char:定长(1--2000byte)
varchar2:变长(1-4000byte)
数值类型(整/浮点):number[(p[,s])]:p表示精度(最大38),s小数点位数
Eg:number(5,0),最多五位整数
Number(5,2),最大999.99
日期:date
LOB类型:CLOB:charcter LOB,存储大量字符数据
BLOB:Binary LOB:存储较大二进制对象,如图像,视频、声音……
3.学生表的创建:student
create table student(
sid number(8),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50)
);
oracle的约束:
1.约束类型:
Primary key constraint
Unique constraint
Default constraint
Not null constraint
Check constraint
Foreign key constraint
2.约束的作用:保证数据完整性
3.语法:constraint constraint_name <约束类型> (不指定名称,会有默认名称)
Primary key constraint:值不空,不重复
练习:
alter table student
add CONSTRAINT sid_pk PRIMARY key(sid);
create table student(
sid number(8),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50),
constraint sid_pk primary key(sid)
);
Not null constraint:一般不用指定约定名称
列级约束:跟在列定义后 column [Constraint constraint_name] constraint_type
表级约束:不在列后面,另起一行column,……,
[Constraint constraint_name] constraint_type
修改表添加约束:
alter table table_name add [Constraint constraint_name] constraint_type(column)]
修改表添加非空约束:
alter table table_name modify [column datatype not null) --不是add
删除约束:
禁用约束:disable|enable constraint constraint_name
彻底删除约束:drop constraint constraint_name
特有:
删除主键约束:drop primary key
删除非空约束:alter table table_name modify column_name datatype NULL;
alter table student
modify(name varchar2(20) not null);
create table student(
sid number(8),
name varchar2(20) not null,
sex char(2) constraint nn_sex not null,
birthday date,
address varchar2(50),
constraint sid_pk primary key(sid)
);
alter table student modify(sex char(2) null);
alter table student enable constraint sid_pk;
alter table student drop primary key;
Unique constraint
- 意义:列具有唯一性,防止重复值
- 注意:唯一性列值可有一个null
表可以有多个唯一性约束
create table student(
sid number(8),
name varchar2(20),
sex char(2),
birthday date,
address varchar2(50),
emial varchar2(50) unique,
cardid varchar2(18),
constraint uk_cardid unique(cardid)
);
alter table student add constraint un_cardid unique(cardid);
--禁用
alter table student disable constraint un_cardid;
--彻底删除
alter table student drop CONSTRAINT un_cardid;
Check constraint
- 列级约束
- Column check(……)|constraint constraint_name check(……)
create table student( sid number(8), name varchar2(20), sex char(2) , birthday date, address varchar2(50), constraint ck_sex check(sex='男' or sex='女') );
--创建 alter table student add constraint ck_sex check(sex='男'or sex='女'); create table student( sid number(8), name varchar2(20), sex char(2) check(sex='男' or sex='女'), birthday date, address varchar2(50) );
--禁用 alter table student disable constraint ck_sex; --删除 alter table student drop constraint ck_sex;
Foreign key constraint
-
列级约束
-
表级约束
create table department ( depid varchar2(10) primary key, depname varchar2(20) ); create table student( sid number(8), name varchar2(20), sex char(2), birthday date, address varchar2(50), depid varchar2(10) ); alter table student add constraint pk_depid FOREIGN key(depid) REFERENCES department(depid);
法二:
create table student( sid number(8), name varchar2(20), sex char(2), birthday date, address varchar2(50), depid varchar2(10) REFERENCES department(depid) on delete cascade ); drop table student; create table student( sid number(8), name varchar2(20), sex char(2), birthday date, address varchar2(50), depid varchar2(10), constraint pk_depid foreign key(depid) REFERENCES department(depid) on delete CASCADE ); --禁用 alter table student disable constraint pk_depid; --删除 alter table student drop constraint pk_depid;