sqlplus / as sysdba
conn sys/ as sysdba
alter user scott account unlock;--解锁scott 用户
alter user scott identified by tiger;--设置连接密码是 tiger
conn scott/tiger;--连接scott 用户
select table_name from user_tables;--查询用户下存在的表
set linesize 100;--每行100个字符
set pagesize 20;--每页20行
column all justify center;--设置所有的列居中对齐
column all format a10 ;--设置列字符间距为10字符
disconnect;--退出用户
grant create tablespace to scott;--授予Scott用户创建表空间的权限
grant create database link to scott;--授予scott 用户创建连接数据库的权限
recover create tablespace from scott;--撤销Scott用户创建表空间的权限
recover create database link from scott;--撤销scott 用户创建连接数据库的权限
创建用户：
create user student identified by password
default tablespace studentTablespace
temporary tablespace temp;
创建角色
create role studentRole;
授予角色创建会话，创建集群，创建数据库连接、创建索引，实现话视图，创建过程，
创建序列，创建同义词，创建表，创建触发器，创建类型、创建视图的角色的权限
grant create session ,create cluster,create datebase link,create any index,
create materialized view ,create procedure,create sequence,create synonym,
create table,create trigger,create type,create view to studentRole;
将角色授予用户
grant studentRole to student;
创建数据表空间
drop tablespace studentTablespace including countents;
create tablespace studentTablespace
datafile '%oracle_home%\database\studentTablespace.dbf' size 30M reuse
uniform size 128k;
更改表空间的大小
alter tablespace studentTablespace
datafile '%oracle_home%\database\studentTablespace.dbf' resize 10M;
删除表空间
drop tablespace temp including contents and datafiles cascade constratnts;
创建表
学生表
DROP TABLE student CASCADE CONSTRAINTS;--删除表
CREATE TABLE student(
sno VARCHAR2(10),
name VARCHAR2(20) NOT NULL,
sex VARCHAR2(4),
age NUMBER(2),
CONSTRAINT stu_pk_sno PRIMARY KEY (sno),
CONSTRAINT stu_unique_name UNIQUE(name),
CONSTRAINT stu_check_sex CHECK (sex IN('男','女')),
CONSTRAINT stu_check_age CHECK (age BETWEEN 16 AND 24));
tablespace studentTablespace;
课程表
DROP TABLE course CASCADE CONSTRAINTS;
CREATE TABLE course(
cno VARCHAR2(4),
cname VARCHAR2(16) NOT NULL,
remark VARCHAR2(200) DEFAULT '专业课',
CONSTRAINT course_pk_sno PRIMARY KEY (cno));
tablespace studentTablespace;
成绩表
DROP TABLE score;
CREATE TABLE score
( sno VARCHAR2(5),
cno VARCHAR2(3),
score NUMBER(5,2),
submit_time TIMESTAMP，
CONSTRAINT score_fk_sno FOREIGN KEY(sno)
REFERENCES student(sno) ON DELETE CASCADE,
CONSTRAINT score_fk_cno FOREIGN KEY(cno)
REFERENCES course(cno) ON DELETE CASCADE,
CONSTRAINT score_pk_sno_cno PRIMARY KEY(sno,cno));
tablespace studentTablespace;
更改表添加一列
ALTER TABLE student ADD(class VARCHAR2(7) NOT NULL);
修改字符类型
alter table student modify(sex varchar2(4));
插入数据
BEGIN
INSERT INTO student VALUES('01080', '张三', '男',19, '01-01');
INSERT INTO course(cno,cname) VALUES('1E23', 'Oracle数据库');
INSERT INTO score VALUES('01080', '1E23',89,SYSTIMESTAMP);
END;
commit;--提交事物
创建索引
CREATE INDEX student_index
ON student(name DESC)
TABLESPACE studentTablespace;
创建序列
CREATE SEQUENCE emp_sequence --序列名
INCREMENT BY 1 -- 每次加几个
START WITH 1 -- 从1开始计数
NOMAXVALUE -- 不设置最大值
NOCYCLE -- 一直累加，不循环
CACHE 10;
删除索引
DROP INDEX stu_year_index ;
删除约束
ALTER TABLE student DROP CONSTRAINT stu_unique_name;
截断表
TRUNCATE TABLE course;
删除表
DROP TABLE course CASCADE CONSTRAINTS;
表约束的启用与禁用
（一）
ALTER TABLE student MODIFY PRIMARY KEY DISABLE;--禁用
ALTER TABLE score MODIFY CONSTRAINT score_fk_sno DISABLE;
ALTER TABLE student MODIFY PRIMARY KEY DISABLE;
ALTER TABLE score MODIFY CONSTRAINT score_fk_sno ENABLE;--启用
ALTER TABLE student MODIFY PRIMARY KEY ENABLE;
ALTER TABLE score MODIFY CONSTRAINT score_fk_sno ENABLE;
（二）利用别名
ALTER TABLE student DISABLE stu_unique_name;
ALTER TABLE student ENABLE stu_unique_name;
