Oracle简单指令学习

Oracle简单指令学习

用户及授权

查看当前用户：select * from user_users;
查看所有用户：

• select * from all_users;
• select * from dba_users;

创建用户及密码：create user xxx identified by 666666;
对用户进行解锁：alter user xxx account unlock;
修改用户密码：alter user xxx identified by 888888;
赋予session权限，实现登录：grant create session to xxx;
赋予创建表的权限：grant create table to xxx;
赋予用户管理员权限：grant dba to xxx;
撤销权限：revoke create table from xxx;
删除用户：drop user xxx;

表及表约束

查询sysdate，systimestamp：select sysdate,systimestamp from dual;
查询当前数据库字符集：select * from v$nls_parameters where parameter=‘NLS_CHARACTERSET’;
若value=ZHS16GBK,那么一个汉字占用2个字符，如果value=AL32UTF8，那么一个汉字占用3个字符
创建表

create table tb_student(
	stuId varchar2(7) not null,
	stuNamr varchar2(10) not null,
	sex varchar2(2) not null,
	age number(2) not null,
	stuAdress varchar2(50) default'地址不详'
);

查询当前用户所有表：select * from user_tables;
查询表名称：select tname from tab;
查询表结构：

• select * from user_tab_columns where table_name=‘TB_STUDENT’；
• desc TB_STUDENT;（命令窗口可以使用）

修改表中字段名：alter table tb_student rename column stuId to sid;
修改表中字段数据类型：alter table tb_student modify(sex varchar2(4));
修改表名：

• alter tb_student rename to tb_stu;
• rename tb_student to tb_stu;

删除表：drop table tb_student;

主键约束

添加主键约束：alter table tb_student constraint pk_stuid primary key(stuId);
查看主键约束：
select cu.*
from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name
and au.constraint_type = ‘P’
and au.table_name = ‘TB_STUDENT’;
禁用主键约束：alter table tb_student disable constraint pk_stuid;
启用主键约束：alter table tb_student enable constraint pk_stuid;
删除主键约束：alter table tb_student drop constraint pk_stuid;

唯一约束

添加唯一约束：alter table tb_student add constraint u_stuname unique(stuName);
查看唯一约束：
select column_name
from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name
and au.constraint_type = ‘U’
and au.table_name = ‘TB_STUDENT’;
禁用唯一约束：alter table tb_student disable constraint u_stuname;
启用唯一约束：alter table tb_student enable constraint u_stuname;
删除唯一约束：alter table tb_student drop constraint u_stuname;

检查约束

给学生性别添加约束：alter table tb_student add constraint ch_sex check(sex=‘男’ or sex=‘女’);
给学生年龄添加约束：alter table tb_student add constraint ch_age check(age>=0 and age<=100);
给学生座号添加约束：alter table tb_student add constraint ch_seat check(seat>=0 and seat<=50);
给学生班号添加约束：alter table tb_student add constraint ch_classno check((classNo>=‘1001’ and classNo<=‘1999’) or (classNo>=‘2001’ and classNo<=‘2999’));
删除检查约束alter table tb_student drop constraint ch_sex;

外键约束

创建学生成绩表：

create table tb_score(
   scoreId number primary key not null,-- 成绩编号
   term varchar2(2) check(term='S1' or term='S2'),-- 学期
   stuId varchar2(7) not null,-- 学号
   stuscore number(4,1) not null-- 成绩
);

添加外键：alter table tb_score add constraint fk_stuid foreign key(stuId) references tb_student(stuId);
查看外键 :select * from user_constraints c where c.constraint_type = ‘R’ and c.table_name = ‘TB_SCORE’;
删除外键 :alter table tb_score drop constraint fk_stuid;